personal-tracker/scripts/ingest/PocketBase_Integration_Guide.txt

# PocketBase Integration Guide
# Complete guide for creating schemas and uploading CSV data to PocketBase

## Table of Contents
1. Authentication Setup
2. Collection Schema Creation
3. Data Upload Process
4. Common Issues and Solutions
5. Field Types and Validation
6. Best Practices
7. Code Examples
8. Troubleshooting

================================================================================
## 1. AUTHENTICATION SETUP
================================================================================

### Superuser Authentication Required
- Creating collections requires SUPERUSER authentication (not regular user auth)
- Use the "_superusers" collection for authentication

```javascript
import PocketBase from 'pocketbase';

const pb = new PocketBase('http://localhost:8090');

// CRITICAL: Must authenticate as superuser for collection operations
await pb.collection("_superusers").authWithPassword('your-email@example.com', 'your-password');
```

### Authentication Error Handling
```javascript
try {
  await pb.collection("_superusers").authWithPassword('email', 'password');
  console.log('Authenticated as superuser');
} catch (error) {
  console.error('Failed to authenticate:', error.message);
  throw error;
}
```

================================================================================
## 2. COLLECTION SCHEMA CREATION
================================================================================

### Basic Collection Structure
```javascript
const collectionData = {
  name: 'collection_name',        // Must be unique
  type: 'base',                   // 'base', 'auth', or 'view'
  fields: [...],                  // Array of field definitions
  indexes: [...],                 // Optional database indexes
  listRule: '',                   // API access rules (empty = public)
  viewRule: '',
  createRule: '',
  updateRule: '',
  deleteRule: ''
};
```

### Field Types and Properties
```javascript
// TEXT FIELD
{
  name: 'field_name',
  type: 'text',
  required: false,                // IMPORTANT: Use false for optional fields
  max: 255                        // Maximum character length
}

// NUMBER FIELD
{
  name: 'numeric_field',
  type: 'number',
  required: false                 // CRITICAL: Set to false if field can be 0 or null
}

// DATE FIELD
{
  name: 'date_field',
  type: 'date',
  required: true
}

// BOOLEAN FIELD
{
  name: 'boolean_field',
  type: 'bool',
  required: false
}
```

### Database Indexes (Optional but Recommended)
```javascript
indexes: [
  'CREATE INDEX idx_tablename_field ON tablename (field_name)',
  'CREATE INDEX idx_tablename_date ON tablename (date_field)',
  'CREATE INDEX idx_tablename_composite ON tablename (field1, field2)'
]
```

### Complete Collection Creation Example
```javascript
async function createCollection() {
  const pb = new PocketBase('http://localhost:8090');
  await pb.collection("_superusers").authWithPassword('email', 'password');

  const collectionData = {
    name: 'visits',
    type: 'base',
    fields: [
      {
        name: 'segment_index',
        type: 'number',
        required: true
      },
      {
        name: 'hierarchyLevel',
        type: 'number',
        required: false              // IMPORTANT: Allow 0 values
      },
      {
        name: 'visit_probability',
        type: 'number',
        required: true
      },
      {
        name: 'top_place_id',
        type: 'text',
        required: false,
        max: 255
      },
      {
        name: 'startTime',
        type: 'date',
        required: true
      }
    ],
    indexes: [
      'CREATE INDEX idx_visits_segment_index ON visits (segment_index)',
      'CREATE INDEX idx_visits_start_time ON visits (startTime)'
    ]
  };

  try {
    const collection = await pb.collections.create(collectionData);
    console.log('Collection created:', collection.name);
  } catch (error) {
    if (error.message.includes('already exists')) {
      console.log('Collection already exists');
    } else {
      throw error;
    }
  }
}
```

================================================================================
## 3. DATA UPLOAD PROCESS
================================================================================

### CSV File Reading and Parsing
```javascript
import fs from 'fs';

// Read CSV file
const csvData = fs.readFileSync('/path/to/file.csv', 'utf8');
const lines = csvData.trim().split('\n');

// CRITICAL: Clean headers to remove carriage returns and whitespace
const headers = lines[0].split(',').map(h => h.trim().replace(/\r$/, ''));
```

### CSV Line Parsing (Handle Quoted Fields)
```javascript
function parseCsvLine(line) {
  const result = [];
  let current = '';
  let inQuotes = false;

  for (let i = 0; i < line.length; i++) {
    const char = line[i];

    if (char === '"') {
      inQuotes = !inQuotes;
    } else if (char === ',' && !inQuotes) {
      result.push(current.trim());
      current = '';
    } else {
      current += char;
    }
  }

  result.push(current.trim());
  return result;
}
```

### Data Type Conversion (CRITICAL)
```javascript
// Process each row
const record = {};
headers.forEach((header, index) => {
  const value = values[index];

  switch (header) {
    case 'segment_index':
    case 'hierarchyLevel':
      // IMPORTANT: Check for empty string, not falsy values
      record[header] = value !== '' ? parseInt(value) : null;
      break;

    case 'visit_probability':
    case 'top_probability':
    case 'top_lat':
    case 'top_lon':
      record[header] = value !== '' ? parseFloat(value) : null;
      break;

    case 'startTime':
    case 'endTime':
      // Convert to ISO string format
      record[header] = value ? new Date(value).toISOString() : null;
      break;

    default:
      record[header] = value || null;
  }
});
```

### Batch Upload with Error Handling
```javascript
async function uploadData() {
  const pb = new PocketBase('http://localhost:8090');
  await pb.collection("_superusers").authWithPassword('email', 'password');

  let successCount = 0;
  let errorCount = 0;

  for (let i = 1; i < lines.length; i++) {  // Skip header row
    const values = parseCsvLine(lines[i]);

    // Create record object (see data type conversion above)
    const record = { ... };

    try {
      await pb.collection('collection_name').create(record);
      successCount++;

      if (successCount % 100 === 0) {
        console.log(`Uploaded ${successCount} records...`);
      }
    } catch (error) {
      console.error(`Row ${i} failed:`, error.message);

      // Log detailed PocketBase error
      if (error.response && error.response.data) {
        console.error(`PocketBase error:`, JSON.stringify(error.response.data, null, 2));
      }

      errorCount++;
    }
  }

  console.log(`Success: ${successCount}, Errors: ${errorCount}`);
}
```

================================================================================
## 4. COMMON ISSUES AND SOLUTIONS
================================================================================

### Issue 1: "Cannot be blank" for Zero Values
PROBLEM: PocketBase treats 0 as blank for required number fields
SOLUTION: Set number fields to required: false, handle validation in app logic

```javascript
// WRONG
{
  name: 'hierarchyLevel',
  type: 'number',
  required: true    // This will reject 0 values
}

// CORRECT
{
  name: 'hierarchyLevel',
  type: 'number',
  required: false   // Allows 0 values
}
```

### Issue 2: Carriage Return in CSV Headers
PROBLEM: CSV files may have \r characters in headers
SOLUTION: Clean headers when parsing

```javascript
// Clean headers
const headers = lines[0].split(',').map(h => h.trim().replace(/\r$/, ''));
```

### Issue 3: Date Format Issues
PROBLEM: Date strings not recognized by PocketBase
SOLUTION: Convert to ISO format

```javascript
// Convert any date string to ISO format
record[header] = value ? new Date(value).toISOString() : null;
```

### Issue 4: Authentication Errors
PROBLEM: Using wrong authentication method
SOLUTION: Always use _superusers collection for admin operations

```javascript
// WRONG
await pb.collection("users").authWithPassword(...);

// CORRECT
await pb.collection("_superusers").authWithPassword(...);
```

### Issue 5: Collection Already Exists
PROBLEM: Script fails when collection exists
SOLUTION: Handle the error gracefully

```javascript
try {
  await pb.collections.create(collectionData);
} catch (error) {
  if (error.message.includes('already exists')) {
    console.log('Collection already exists');
  } else {
    throw error;
  }
}
```

================================================================================
## 5. FIELD TYPES AND VALIDATION
================================================================================

### Available Field Types
- text: String values (max length configurable)
- number: Integer or float values
- bool: Boolean true/false
- date: ISO date strings
- json: JSON objects
- file: File uploads
- relation: References to other collections
- select: Predefined options
- autodate: Auto-generated timestamps

### Validation Rules
```javascript
// Text field with validation
{
  name: 'email',
  type: 'text',
  required: true,
  pattern: '^[\\w\\.-]+@[\\w\\.-]+\\.[a-zA-Z]{2,}$',  // Email regex
  max: 255
}

// Number field with min/max
{
  name: 'age',
  type: 'number',
  required: false,
  min: 0,
  max: 150
}

// Select field with options
{
  name: 'status',
  type: 'select',
  required: true,
  values: ['active', 'inactive', 'pending']
}
```

================================================================================
## 6. BEST PRACTICES
================================================================================

### Schema Design
1. Use descriptive field names (snake_case or camelCase consistently)
2. Set required: false for fields that can be 0, null, or empty
3. Add appropriate indexes for frequently queried fields
4. Use appropriate field types (don't store numbers as text)
5. Set reasonable max lengths for text fields

### Data Upload
1. Always authenticate as superuser for admin operations
2. Parse CSV data carefully, handling quotes and special characters
3. Convert data types explicitly before uploading
4. Use batch processing with progress logging
5. Implement proper error handling and logging
6. Validate data before uploading when possible

### Error Handling
1. Log detailed error messages including PocketBase responses
2. Handle common errors gracefully (collection exists, auth failures)
3. Stop processing after multiple consecutive errors to debug
4. Provide meaningful error messages for troubleshooting

### Performance
1. Use database indexes for frequently queried fields
2. Process uploads in batches with progress indicators
3. Consider using transactions for large data sets
4. Monitor memory usage for very large CSV files

================================================================================
## 7. COMPLETE CODE EXAMPLES
================================================================================

### Collection Creation Script Template
```javascript
import PocketBase from 'pocketbase';

async function createMyCollection() {
  const pb = new PocketBase('http://localhost:8090');

  // Authenticate
  try {
    await pb.collection("_superusers").authWithPassword('your-email', 'your-password');
    console.log('Authenticated as superuser');
  } catch (error) {
    console.error('Authentication failed:', error.message);
    throw error;
  }

  const collectionData = {
    name: 'your_collection_name',
    type: 'base',
    fields: [
      // Define your fields here based on CSV structure
    ],
    indexes: [
      // Add indexes for performance
    ],
    listRule: '',    // Set access rules as needed
    viewRule: '',
    createRule: '',
    updateRule: '',
    deleteRule: ''
  };

  try {
    const collection = await pb.collections.create(collectionData);
    console.log('Collection created successfully:', collection.name);
    return collection;
  } catch (error) {
    if (error.message.includes('already exists')) {
      console.log('Collection already exists');
    } else {
      console.error('Error creating collection:', error);
      throw error;
    }
  }
}

// Export for use in other scripts
export { createMyCollection };

// Run if called directly
if (import.meta.url === `file://${process.argv[1]}`) {
  createMyCollection()
    .then(() => {
      console.log('Script completed successfully');
      process.exit(0);
    })
    .catch((error) => {
      console.error('Script failed:', error);
      process.exit(1);
    });
}
```

### Data Upload Script Template
```javascript
import PocketBase from 'pocketbase';
import fs from 'fs';

async function uploadDataToCollection() {
  const pb = new PocketBase('http://localhost:8090');

  // Authenticate
  await pb.collection("_superusers").authWithPassword('your-email', 'your-password');

  // Read CSV
  const csvPath = '/path/to/your/data.csv';
  const csvData = fs.readFileSync(csvPath, 'utf8');
  const lines = csvData.trim().split('\n');
  const headers = lines[0].split(',').map(h => h.trim().replace(/\r$/, ''));

  console.log(`Found ${lines.length - 1} rows to upload`);
  console.log('Headers:', headers);

  let successCount = 0;
  let errorCount = 0;

  // Process each row
  for (let i = 1; i < lines.length; i++) {
    const values = parseCsvLine(lines[i]);

    if (values.length !== headers.length) {
      console.warn(`Row ${i}: Column count mismatch, skipping`);
      errorCount++;
      continue;
    }

    // Build record object
    const record = {};
    headers.forEach((header, index) => {
      const value = values[index];

      // Customize data type conversion based on your schema
      switch (header) {
        case 'id_field':
        case 'count_field':
          record[header] = value !== '' ? parseInt(value) : null;
          break;
        case 'decimal_field':
        case 'percentage_field':
          record[header] = value !== '' ? parseFloat(value) : null;
          break;
        case 'date_field':
        case 'timestamp_field':
          record[header] = value ? new Date(value).toISOString() : null;
          break;
        default:
          record[header] = value || null;
      }
    });

    // Upload record
    try {
      await pb.collection('your_collection_name').create(record);
      successCount++;

      if (successCount % 100 === 0) {
        console.log(`Uploaded ${successCount} records...`);
      }
    } catch (error) {
      console.error(`Row ${i} failed:`, error.message);
      if (error.response && error.response.data) {
        console.error(`Details:`, JSON.stringify(error.response.data, null, 2));
      }
      errorCount++;

      // Stop after too many errors to debug
      if (errorCount >= 5) {
        console.log('Too many errors, stopping to debug...');
        break;
      }
    }
  }

  console.log(`\nUpload completed:`);
  console.log(`Success: ${successCount} records`);
  console.log(`Errors: ${errorCount} records`);
}

function parseCsvLine(line) {
  const result = [];
  let current = '';
  let inQuotes = false;

  for (let i = 0; i < line.length; i++) {
    const char = line[i];

    if (char === '"') {
      inQuotes = !inQuotes;
    } else if (char === ',' && !inQuotes) {
      result.push(current.trim());
      current = '';
    } else {
      current += char;
    }
  }

  result.push(current.trim());
  return result;
}

export { uploadDataToCollection };
```

================================================================================
## 8. TROUBLESHOOTING CHECKLIST
================================================================================

### Before Running Scripts
□ PocketBase server is running on correct port (default: 8090)
□ Superuser account exists with correct credentials
□ CSV file path is correct and file is accessible
□ Node.js modules are installed (npm install pocketbase)
□ Project is configured for ES modules (package.json has "type": "module")

### If Collection Creation Fails
□ Check authentication credentials
□ Verify collection name doesn't already exist
□ Ensure field definitions are valid
□ Check for typos in field types ('number' not 'Number')
□ Verify required vs optional field settings

### If Data Upload Fails
□ Collection exists and schema matches CSV structure
□ CSV headers are clean (no carriage returns)
□ Data types are being converted correctly
□ Check for null/empty value handling
□ Verify date formats are valid
□ Look at detailed PocketBase error messages

### Common Error Messages
- "Cannot be blank": Field is required but receiving null/0/empty value
- "Failed to create record": Generic error, check detailed error response
- "The request requires valid record authorization token": Authentication failed
- "Collection not found": Typo in collection name or collection doesn't exist

### Performance Issues
□ Add database indexes for frequently queried fields
□ Process large files in smaller batches
□ Monitor memory usage with very large CSVs
□ Consider using database transactions for consistency

================================================================================
## FINAL NOTES
================================================================================

1. Always test with a small subset of data first
2. Keep backups of your CSV files before processing
3. Monitor PocketBase logs for additional error details
4. Use version control for your scripts
5. Document any custom data transformations needed for your specific use case
6. Consider creating utility functions for common operations
7. Test error scenarios (network issues, invalid data, etc.)

Remember: PocketBase validation is strict. It's better to be permissive in your schema
and validate in your application logic than to fight with PocketBase's validation rules.