db-migrations

# Database Migrations Guide

## Schema conventions

Apply these before generating any migration — they change what the schema file looks like, not just the SQL.

- **No pg enums (and no fixed value sets) for growing domains.** For columns whose value set will keep expanding (resource types, statuses, providers, …), use a plain `text` column typed via `.$type<UnionType>()`. `pgEnum` requires an `ALTER TYPE ... ADD VALUE` migration for every new literal, and even the Drizzle `text('col', { enum: [...] })` option hardwires the value list into the schema file. With `.$type<>()`, onboarding a new value is a type-only change — no migration at all.

  ```ts
  // ✅ Good — type lives in @lobechat/types, column stays plain text
  resourceType: text('resource_type').$type<TransferResourceType>().notNull(),

  // ❌ Bad — pg enum, needs ALTER TYPE per new value
  resourceType: resourceTypeEnum('resource_type').notNull(),

  // ❌ Avoid — value list hardwired into the schema file
  resourceType: text('resource_type', { enum: TRANSFER_RESOURCE_TYPES }).notNull(),
  ```

- **Keep domain constants out of schema files.** In new or modified schema files under `packages/database/src/schemas/`, shared domain literal arrays, union types, and option interfaces belong in `@lobechat/types` (one module per domain, re-exported from its `index.ts`); both the schema (`.$type<>()`) and consumers (routers via `z.enum(...)`, services, UI) import from there. This rule targets domain constants only — table objects, inferred row types, Drizzle relation objects, and zod insert/select schemas (`insertAgentSchema`, …) are the schema file's job and stay put. Existing schema files that already export such constants (e.g. `resourcePermission.ts`) are grandfathered; migrate them opportunistically when the file is next touched, not in bulk.

## Choose the rollout strategy

Classify every database change into one of these three rollout paths before generating or editing a migration.

### Validate rollout assumptions on the actual Dev database

Do not choose a rollout path from hypothetical claims such as “this migration might be slow” or “installing these triggers could block deployment.” Before deciding that a schema change needs a manual production step, deferred installation, or a dedicated backfill, test the relevant operation against the project's actual Dev database.

- Classify the database target first using the project's approved database-access tooling; never read secret-bearing `.env` files directly.
- Measure the real operation or the closest safe equivalent, such as creating an identically defined probe index under a temporary name or installing temporary triggers inside a transaction that is rolled back.
- Record the tested SQL or operation, representative row count and table size, elapsed time, and cleanup verification.
- Keep probes reversible and remove every temporary database object after the measurement.
- Treat a single Dev result as evidence about the observed Dev scale, not proof of production behavior. State material differences in production scale, load, cache state, and lock contention explicitly, and label any resulting production claim as an inference.

Rollout decisions must combine repository deployment facts with these measurements. Do not add operational tables, delayed activation paths, or manual release steps solely to guard against unmeasured performance concerns.

### 1. Regular Drizzle migration

Use the normal Drizzle workflow for schema changes that are safe to execute during deployment, such as creating a small table or adding a nullable column:

1. Update the Drizzle schema.
2. Run `bun run db:generate`.
3. Review and harden the generated artifacts using the steps below.

Before listing a manual migration command as a release step, inspect the target repository's build and deployment scripts. If its deployment pipeline already applies migrations automatically, do not require a redundant manual run.

### 2. Online index creation

Creating an index normally can block writes and queries on a large or frequently accessed table, and a long-running statement can also stall the deployment. In that case:

1. Before deploying the application, execute the index creation manually in the target database's SQL editor using `CONCURRENTLY`:

   ```sql
   CREATE INDEX CONCURRENTLY IF NOT EXISTS "table_column_idx"
   ON "table" USING btree ("column");
   ```

2. Keep an idempotent, non-`CONCURRENTLY` version in the Drizzle migration:

   ```sql
   CREATE INDEX IF NOT EXISTS "table_column_idx"
   ON "table" USING btree ("column");
   ```

The manual online operation avoids blocking production traffic. When the deployment later runs the migration, `IF NOT EXISTS` makes the statement a no-op, while new or self-hosted databases can still converge through normal migration replay. Do not place `CREATE INDEX CONCURRENTLY` inside a transaction.

### 3. Data backfill

Backfills and historical-data reconciliation must run as dedicated, idempotent scripts rather than inside a Drizzle migration. Keep the schema change in Drizzle, but move row-by-row or batch data processing into a separate script so it does not block deployment.

Decide whether to run the script before or after the application deployment based on compatibility:

- Run it **before deployment** when the new code or a new constraint requires existing rows to be populated immediately.
- Run it **after deployment** when the application safely handles both old and new row shapes and the backfill can converge gradually.

Backfill scripts should be resumable, safe to retry, processed in bounded batches, and observable. Treat optional cleanup or eager reconciliation as optional rather than as a release blocker.

## Development-stage schema changes

Schema changes churn during feature development. When the schema changes before the migration has shipped, do not hand-edit the existing migration SQL to chase the new schema shape. Delete the draft migration artifac