# Custom Migrations Implementation

## Overview

This implementation adds a database-stored migration system for dynamically created objects. Migrations are recorded in a `custom_migrations` table in each tenant database, allowing them to be replayed or used for environment replication in the future.

## Architecture

### Components

#### 1. CustomMigrationService
**Location:** `backend/src/migration/custom-migration.service.ts`

Handles all migration-related operations:

- **`generateCreateTableSQL(tableName, fields)`** - Generates SQL for creating object tables with standard fields
- **`createMigrationRecord()`** - Stores migration metadata in the database
- **`executeMigration()`** - Executes a pending migration and updates its status
- **`createAndExecuteMigration()`** - Creates and immediately executes a migration
- **`getMigrations()`** - Retrieves migration history with filtering
- **`ensureMigrationsTable()`** - Ensures the `custom_migrations` table exists

#### 2. MigrationModule
**Location:** `backend/src/migration/migration.module.ts`

Provides the CustomMigrationService to other modules.

#### 3. Updated ObjectService
**Location:** `backend/src/object/object.service.ts`

- Injects CustomMigrationService
- Calls `createAndExecuteMigration()` when a new object is created
- Generates table creation migrations with standard fields

### Database Schema

#### custom_migrations Table

```sql
CREATE TABLE custom_migrations (
  id UUID PRIMARY KEY,
  tenantId UUID NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  type ENUM('create_table', 'add_column', 'alter_column', 'add_index', 'drop_table', 'custom'),
  sql TEXT NOT NULL,
  status ENUM('pending', 'executed', 'failed') DEFAULT 'pending',
  executedAt TIMESTAMP NULL,
  error TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_tenantId (tenantId),
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
)
```

#### Generated Object Tables

When a new object is created (e.g., "Account"), a table is automatically created with:

```sql
CREATE TABLE accounts (
  id VARCHAR(36) PRIMARY KEY,
  ownerId VARCHAR(36),
  name VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- Custom fields added here
)
```

**Standard Fields:**
- `id` - UUID primary key
- `ownerId` - User who owns the record
- `name` - Primary name field
- `created_at` - Record creation timestamp
- `updated_at` - Record update timestamp

### Field Type Mapping

Custom fields are mapped to SQL column types:

| Field Type | SQL Type | Notes |
|---|---|---|
| TEXT, STRING | VARCHAR(255) | |
| LONG_TEXT | TEXT | Large text content |
| NUMBER, DECIMAL | DECIMAL(18, 2) | |
| INTEGER | INT | |
| BOOLEAN | BOOLEAN | Defaults to FALSE |
| DATE | DATE | |
| DATE_TIME | DATETIME | |
| EMAIL | VARCHAR(255) | |
| URL | VARCHAR(2048) | |
| PHONE | VARCHAR(20) | |
| CURRENCY | DECIMAL(18, 2) | |
| PERCENT | DECIMAL(5, 2) | |
| PICKLIST, MULTI_PICKLIST | VARCHAR(255) | |
| LOOKUP, BELONGS_TO | VARCHAR(36) | References foreign record ID |

## Usage Flow

### Creating a New Object

1. **User creates object definition:**
   ```
   POST /api/objects
   {
     "apiName": "Account",
     "label": "Account",
     "description": "Customer account records"
   }
   ```

2. **ObjectService.createObjectDefinition() executes:**
   - Inserts object metadata into `object_definitions` table
   - Generates create table SQL
   - Creates migration record with status "pending"
   - Executes migration immediately
   - Updates migration status to "executed"
   - Returns object definition

3. **Result:**
   - Object is now ready to use
   - Table exists in database
   - Migration history is recorded for future replication

### Migration Execution Flow

```
createAndExecuteMigration()
├── createMigrationRecord()
│   └── Insert into custom_migrations (status: pending)
└── executeMigration()
    ├── Fetch migration record
    ├── Execute SQL
    ├── Update status: executed
    └── Return migration record
```

## Error Handling

Migrations track execution status and errors:

- **Status: pending** - Not yet executed
- **Status: executed** - Successfully completed
- **Status: failed** - Error during execution

Failed migrations are logged and stored with error details for debugging and retry:

```typescript
{
  id: "uuid",
  status: "failed",
  error: "Syntax error in SQL...",
  executedAt: null,
  updated_at: "2025-12-24T11:00:00Z"
}
```

## Future Functionality

### Sandbox Environment Replication

Stored migrations enable:

1. **Cloning production environments** - Replay all migrations in new database
2. **Data structure export/import** - Export migrations as SQL files
3. **Audit trail** - Complete history of schema changes
4. **Rollback capability** - Add down migrations for reverting changes
5. **Dependency tracking** - Identify object dependencies from migrations

### Planned Enhancements

1. **Add down migrations** - Support undoing schema changes
2. **Migration dependencies** - Track which migrations depend on others
3. **Batch execution** - Run pending migrations together
4. **Version control** - Track migration versions and changes
5. **Manual migration creation** - API to create custom migrations
6. **Migration status dashboard** - UI to view migration history

## Integration Points

### ObjectService

- Uses `getTenantKnexById()` for tenant database connections
- Calls CustomMigrationService after creating object definitions
- Handles migration execution errors gracefully (logs but doesn't fail)

### TenantDatabaseService

- Provides database connections via `getTenantKnexById()`
- Connections are cached with prefix `id:${tenantId}`

### Module Dependencies

```
ObjectModule
├── imports: [TenantModule, MigrationModule]
└── providers: [ObjectService, CustomMigrationService, ...]

MigrationModule
├── imports: [TenantModule]
└── providers: [CustomMigrationService]
```

## API Endpoints (Future)

While not yet exposed via API, these operations could be added:

```typescript
// Get migration history
GET /api/migrations?tenantId=xxx&status=executed

// Get migration details
GET /api/migrations/:id

// Retry failed migration
POST /api/migrations/:id/retry

// Export migrations as SQL
GET /api/migrations/export?tenantId=xxx

// Create custom migration
POST /api/migrations
{
  name: "add_field_to_accounts",
  description: "Add phone_number field",
  sql: "ALTER TABLE accounts ADD phone_number VARCHAR(20)"
}
```

## Testing

### Manual Testing Steps

1. **Create a new object:**
   ```bash
   curl -X POST http://localhost:3000/api/objects \
     -H "Authorization: Bearer <token>" \
     -H "Content-Type: application/json" \
     -d '{
       "apiName": "TestObject",
       "label": "Test Object",
       "description": "Test object creation"
     }'
   ```

2. **Verify table was created:**
   ```bash
   # In tenant database
   SHOW TABLES LIKE 'test_objects';
   DESCRIBE test_objects;
   ```

3. **Check migration record:**
   ```bash
   # In tenant database
   SELECT * FROM custom_migrations WHERE name LIKE '%test_objects%';
   ```

4. **Create a record in the new object:**
   ```bash
   curl -X POST http://localhost:3000/api/test-objects \
     -H "Authorization: Bearer <token>" \
     -H "Content-Type: application/json" \
     -d '{
       "name": "My Test Record"
     }'
   ```

## Troubleshooting

### Migration Fails with SQL Error

1. Check `custom_migrations` table for error details:
   ```sql
   SELECT id, name, error, status FROM custom_migrations 
   WHERE status = 'failed';
   ```

2. Review the generated SQL in the `sql` column

3. Common issues:
   - Duplicate table names
   - Invalid field names (reserved SQL keywords)
   - Unsupported field types

### Table Not Created

1. Verify `custom_migrations` table exists:
   ```sql
   SHOW TABLES LIKE 'custom_migrations';
   ```

2. Check object service logs for migration execution errors

3. Manually retry migration:
   ```typescript
   const migration = await tenantKnex('custom_migrations')
     .where({ status: 'failed' })
     .first();
   await customMigrationService.executeMigration(tenantKnex, migration.id);
   ```

## Performance Considerations

- **Table creation** is synchronous and happens immediately
- **Migrations are cached** in custom_migrations table per tenant
- **No file I/O** - all operations use database
- **Index creation** optimized with proper indexes on common columns (tenantId, status, created_at)

## Security

- **Per-tenant isolation** - Each tenant's migrations stored separately
- **No SQL injection** - Using Knex query builder for all operations
- **Access control** - Migrations only created/executed by backend service
- **Audit trail** - Complete history of all schema changes

## Related Files

- [backend/src/object/object.service.ts](backend/src/object/object.service.ts)
- [backend/src/migration/custom-migration.service.ts](backend/src/migration/custom-migration.service.ts)
- [backend/src/migration/migration.module.ts](backend/src/migration/migration.module.ts)