> ## Documentation Index
> Fetch the complete documentation index at: https://docs.charle.agency/llms.txt
> Use this file to discover all available pages before exploring further.

# Migrations

> Managing database schema changes in CharleOS

Database migrations allow you to version control schema changes and safely apply them across environments. CharleOS uses Drizzle Kit for automatic migration generation.

## Migration Files

Migrations are stored in the `drizzle/` directory:

```
drizzle/
├── 0000_initial_schema.sql
├── 0001_add_users.sql
├── 0002_add_tasks.sql
├── ... (47 migrations total)
└── meta/
    ├── _journal.json          # Migration history
    └── 0047_snapshot.json     # Latest schema snapshot
```

Each migration file contains raw SQL that transforms the database schema.

## Development Workflow

### Local Development (Fast Iteration)

For rapid development, use `db:push` to sync schema directly:

<Steps>
  <Step title="Edit Schema">
    Modify `lib/db/schema.ts` to add/change tables or columns
  </Step>

  <Step title="Push Changes">
    ```bash theme={null}
    npm run db:push
    ```

    Instantly applies changes to your local dev database (no migration file created)
  </Step>

  <Step title="Test Changes">
    ```bash theme={null}
    npm run db:studio
    ```

    Verify schema changes in Drizzle Studio
  </Step>
</Steps>

<Info>
  `db:push` is perfect for local development but doesn't create migration files. It directly syncs your schema to the database.
</Info>

### Production Workflow (Safe Deployment)

For production, always generate migration files:

<Steps>
  <Step title="Edit Schema">
    Make your changes to `lib/db/schema.ts`
  </Step>

  <Step title="Generate Migration">
    ```bash theme={null}
    npm run db:generate
    ```

    Creates a new SQL file in `drizzle/XXXX_name.sql`
  </Step>

  <Step title="Review Generated SQL">
    ```bash theme={null}
    cat drizzle/0048_*.sql
    ```

    **Always review the SQL** to ensure it does what you expect
  </Step>

  <Step title="Test Locally">
    Apply the migration to your local database:

    ```bash theme={null}
    npm run db:migrate
    ```
  </Step>

  <Step title="Commit Changes">
    ```bash theme={null}
    git add lib/db/schema.ts drizzle/
    git commit -m "feat: add priority column to tasks"
    ```
  </Step>

  <Step title="Deploy">
    Push to GitHub. Migrations run automatically on Vercel deployment.
  </Step>
</Steps>

## Common Migration Examples

### Adding a Column

**Schema change:**

```typescript theme={null}
// lib/db/schema.ts
export const task = pgTable("task", {
  // ... existing columns
  priority: integer("priority").notNull().default(3), // ← New column
});
```

**Generated migration:**

```sql theme={null}
-- drizzle/0048_add_task_priority.sql
ALTER TABLE "task" ADD COLUMN "priority" integer DEFAULT 3 NOT NULL;
```

### Adding a New Table

**Schema change:**

```typescript theme={null}
// lib/db/schema.ts
export const tag = pgTable("tag", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => createId()),
  name: text("name").notNull(),
  color: text("color").notNull().default("#gray"),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
});
```

**Generated migration:**

```sql theme={null}
-- drizzle/0048_add_tags.sql
CREATE TABLE IF NOT EXISTS "tag" (
  "id" text PRIMARY KEY NOT NULL,
  "name" text NOT NULL,
  "color" text DEFAULT '#gray' NOT NULL,
  "createdAt" timestamp DEFAULT now() NOT NULL
);
```

### Adding a Foreign Key

**Schema change:**

```typescript theme={null}
export const taskTag = pgTable("task_tag", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => createId()),
  taskId: text("taskId")
    .notNull()
    .references(() => task.id, { onDelete: "cascade" }),
  tagId: text("tagId")
    .notNull()
    .references(() => tag.id, { onDelete: "cascade" }),
});
```

**Generated migration:**

```sql theme={null}
CREATE TABLE IF NOT EXISTS "task_tag" (
  "id" text PRIMARY KEY NOT NULL,
  "taskId" text NOT NULL,
  "tagId" text NOT NULL
);

ALTER TABLE "task_tag" ADD CONSTRAINT "task_tag_taskId_task_id_fk" 
  FOREIGN KEY ("taskId") REFERENCES "task"("id") ON DELETE cascade;

ALTER TABLE "task_tag" ADD CONSTRAINT "task_tag_tagId_tag_id_fk" 
  FOREIGN KEY ("tagId") REFERENCES "tag"("id") ON DELETE cascade;
```

### Renaming a Column

**Schema change:**

```typescript theme={null}
// lib/db/schema.ts
export const task = pgTable("task", {
  // priority: integer("priority"), // Old name
  importanceLevel: integer("importanceLevel"), // ← New name
});
```

**Generated migration:**

```sql theme={null}
-- drizzle/0048_rename_priority.sql
ALTER TABLE "task" RENAME COLUMN "priority" TO "importanceLevel";
```

### Dropping a Column

**Schema change:**

```typescript theme={null}
// Remove column from schema.ts
export const task = pgTable("task", {
  // legacyField: text("legacyField"), // ← Removed
});
```

**Generated migration:**

```sql theme={null}
-- drizzle/0048_remove_legacy_field.sql
ALTER TABLE "task" DROP COLUMN "legacyField";
```

<Warning>
  **Destructive changes** like dropping columns or tables are irreversible. Always back up data before deploying destructive migrations.
</Warning>

## Migration Commands

| Command               | Purpose                    | When to Use                           |
| --------------------- | -------------------------- | ------------------------------------- |
| `npm run db:push`     | Push schema directly to DB | Local development (no migration file) |
| `npm run db:generate` | Generate migration file    | Before deploying to production        |
| `npm run db:migrate`  | Apply pending migrations   | After generating migrations locally   |
| `npm run db:sync`     | Sync migrations to Neon    | After adding migration files manually |
| `npm run db:studio`   | Open Drizzle Studio        | Browse/verify schema changes          |

## Handling Destructive Changes Safely

### Approach 1: Multi-Step Migration

Instead of dropping a column directly, use a multi-step approach:

**Step 1: Make column nullable**

```sql theme={null}
ALTER TABLE "task" ALTER COLUMN "legacyField" DROP NOT NULL;
```

**Step 2: Migrate data to new column**

```sql theme={null}
UPDATE "task" SET "newField" = "legacyField";
```

**Step 3: Drop old column (separate deployment)**

```sql theme={null}
ALTER TABLE "task" DROP COLUMN "legacyField";
```

### Approach 2: Feature Flags

Use feature flags to gradually roll out schema changes:

1. Add new column (deploy)
2. Update app to use new column (behind flag)
3. Enable flag and migrate data
4. Remove old column (new deployment)

## Migration Conflicts

### Resolving Conflicts

If multiple developers create migrations simultaneously:

<Steps>
  <Step title="Pull Latest Changes">
    ```bash theme={null}
    git pull origin main
    ```
  </Step>

  <Step title="Check for Conflicts">
    Look for conflicts in `drizzle/meta/_journal.json`
  </Step>

  <Step title="Resolve Journal Conflicts">
    The journal tracks all migrations. Manually merge the arrays:

    ```json theme={null}
    {
      "entries": [
        { "idx": 0, "tag": "0000_initial" },
        { "idx": 1, "tag": "0001_your_migration" },
        { "idx": 2, "tag": "0002_their_migration" }  // ← Add this
      ]
    }
    ```
  </Step>

  <Step title="Regenerate Your Migration">
    ```bash theme={null}
    npm run db:generate
    ```

    This creates a new migration that accounts for both changes
  </Step>

  <Step title="Test Locally">
    Apply both migrations locally to ensure they work together
  </Step>
</Steps>

## Rolling Back Migrations

Drizzle doesn't have built-in rollback. Options:

### Option 1: Create Reverse Migration

Manually write SQL to undo the change:

```sql theme={null}
-- Original migration: 0048_add_priority.sql
ALTER TABLE "task" ADD COLUMN "priority" integer DEFAULT 3;

-- Rollback migration: 0049_remove_priority.sql
ALTER TABLE "task" DROP COLUMN "priority";
```

### Option 2: Restore from Backup

For critical failures, restore from Neon backup:

1. Go to Neon Console → Your Branch → Backups
2. Select point-in-time before migration
3. Restore to a new branch
4. Test and switch production

### Option 3: Fix Forward

Create a new migration that fixes the problem:

```sql theme={null}
-- Fix incorrect default value
ALTER TABLE "task" ALTER COLUMN "priority" SET DEFAULT 3;
```

## Seeding Data

Use seed scripts for initial configuration data:

```bash theme={null}
# Seed all development data
npm run db:seed

# Custom seed (create in scripts/)
npx tsx scripts/seed-custom-data.ts
```

**Example seed script:**

```typescript theme={null}
// scripts/seed-tags.ts
import { db } from "@/lib/db";
import { tag } from "@/lib/db/schema";

async function seedTags() {
  await db.insert(tag).values([
    { name: "Bug", color: "#red" },
    { name: "Feature", color: "#blue" },
    { name: "Enhancement", color: "#green" },
  ]);
}

seedTags().then(() => console.log("Tags seeded!"));
```

## Best Practices

<AccordionGroup>
  <Accordion title="One Logical Change Per Migration" icon="file">
    Keep migrations focused and atomic:

    **✅ Good:**

    * `0048_add_task_tags.sql` - Adds tags table and task\_tag junction

    **❌ Bad:**

    * `0048_massive_refactor.sql` - Adds tags, renames 5 columns, drops 3 tables
  </Accordion>

  <Accordion title="Always Review Generated SQL" icon="magnifying-glass">
    Don't blindly trust auto-generated migrations:

    ```bash theme={null}
    # Review before committing
    cat drizzle/0048_*.sql

    # Check for:
    # - Destructive operations (DROP, TRUNCATE)
    # - Missing indexes for foreign keys
    # - Incorrect default values
    ```
  </Accordion>

  <Accordion title="Test Migrations Locally First" icon="flask">
    Always apply migrations to local dev database before deploying:

    ```bash theme={null}
    # Apply migration locally
    npm run db:migrate

    # Verify in Drizzle Studio
    npm run db:studio

    # Test app functionality
    npm run dev
    ```
  </Accordion>

  <Accordion title="Never Edit Applied Migrations" icon="lock">
    Once a migration is deployed to production:

    * Never edit the SQL file
    * Create a new migration to fix issues
    * Editing applied migrations breaks migration history
  </Accordion>

  <Accordion title="Add Indexes for Foreign Keys" icon="database">
    Drizzle doesn't auto-create indexes on foreign keys. Add manually:

    ```typescript theme={null}
    export const task = pgTable("task", {
      clientId: text("clientId").references(() => client.id),
    }, (table) => ({
      clientIdIdx: index("task_client_id_idx").on(table.clientId), // ← Add index
    }));
    ```
  </Accordion>

  <Accordion title="Document Breaking Changes" icon="circle-exclamation">
    In commit messages and PR descriptions:

    ```
    feat: add task priority system

    BREAKING CHANGE: Adds required `priority` column to tasks table.
    Existing tasks will default to priority 3 (medium).

    Migration: 0048_add_task_priority.sql
    ```
  </Accordion>
</AccordionGroup>

## Troubleshooting

### Migration Failed to Apply

**Error:** "relation 'task' does not exist"

**Fix:**

1. Check if you're on the correct database branch
2. Verify all previous migrations are applied
3. Check `drizzle/meta/_journal.json` for missing entries

### Schema Out of Sync

**Error:** "Schema drift detected"

**Fix:**

```bash theme={null}
# Reset local database to match migrations
npm run db:push

# Or regenerate migrations from current schema
npm run db:generate
```

### Duplicate Migration

**Error:** "Migration 0048 already exists"

**Fix:**

```bash theme={null}
# Delete the duplicate
rm drizzle/0048_*.sql

# Regenerate with next number
npm run db:generate
```

## CI/CD Integration

Migrations run automatically on Vercel deployments via `postinstall` script:

```json theme={null}
// package.json
{
  "scripts": {
    "postinstall": "drizzle-kit migrate"
  }
}
```

This ensures migrations are applied before the app starts.

## Related Documentation

<CardGroup cols={2}>
  <Card title="Database Overview" icon="database" href="/developer/database">
    Database architecture and tools
  </Card>

  <Card title="Schema" icon="table" href="/developer/database/schema">
    Complete schema reference
  </Card>

  <Card title="Drizzle ORM" icon="code" href="/developer/database/drizzle">
    Query builder syntax
  </Card>

  <Card title="Deployment" icon="cloud" href="/developer/deployment">
    How migrations run in production
  </Card>
</CardGroup>