Conventions DB

Database Conventions

Apply when writing or modifying database schema, queries, migrations, transactions, or tenant-scoped persistence.

Schema

• Schema lives under apps/api/src/db/schema/; read the owning slice and related foreign keys before editing.
• For closed persisted domain values, define one named as const value list and pass it to Drizzle with text({ enum: VALUES }). Add a database CHECK when invalid values could compromise lifecycle, authorization, audit, or workflow invariants; Drizzle's enum option is compile-time-only.
• Reserve .$type<T>() for branded or structured types. Use a native PostgreSQL enum only when the value set is genuinely permanent.
• Use cascade deletes for workspace-owned dependants and restrict deletes for shared file references. Trace the full FK graph before multi-resource deletion.
• Timestamp columns use the timestamptz helper from apps/api/src/db/columns.ts. Never introduce a naive PostgreSQL timestamp or ::timestamp cast without explicitly anchoring its time zone.
• Add indexes for columns used in WHERE, ORDER BY, or JOIN; lead composite indexes with the tenant-scoping columns. Treat changes to large tables as lock-sensitive.

Migrations

• bun --filter @stll/api db:migrate is the shipped migration path used by CI and deployment. db:push is a local declarative schema-diff tool; it does not replace committed migrations and must not be described as the deployment path.
• Schema changes remain additive across a rollout: add, deploy compatible reads/writes, backfill in bounded batches, switch, then remove the old shape in a later release.
• Migrations run before new API tasks finish rolling out. Old tasks must remain compatible with the migrated schema, and a failed rollout must have a safe forward-fix path.
• Keep irreversible schema operations out of the same release as risky application changes. Destructive, bulk-backfill, and access-control SQL requires a statement-scoped acknowledgement enforced by scripts/check-migration-safety.ts, placed in the comment block directly above the statement: -- stella-migration-safety: reviewed <rule-id> - <why this is safe>. An acknowledgement that clears nothing is an error. Every migration sets lock_timeout and statement_timeout first.
• For large live tables, follow the repository's guarded concurrent-index protocol: either split and reopen the migrator transaction exactly as enforced by migration-concurrent-index.test.ts, or put repairable work in online-migrations.ts. Keep long backfills outside schema migrations and checkpoint them durably.
• A migration directory's name is its identity: the migrator applies every directory whose name the database has not recorded, in name order. The timestamp prefix does not need to sort after the base branch, but never rename or delete a merged migration; add a new one instead.
• Validate migration history two ways: apply every committed migration to a fresh database, then confirm bun --filter @stll/api db:push -- --explain reports no schema drift. Do not repair drift by resetting a shared database.