conventions-db - SKILL.md Agent Skill

name: conventions-db description: 'Apply when writing or modifying database schema, queries, or migrations.'

Database Conventions

Apply when writing or modifying database schema, queries, or migrations.

Schema

Schema lives in /apps/api/src/db/schema.ts
Use Drizzle migrations (bun run db:push)
Cascade deletes for workspace-owned resources
Restrict deletes for file references (prevent orphaning)
When writing multi-delete transactions, trace the FK graph from schema.ts and delete in dependency order: delete the parent with cascade FKs first (removing referencing rows), then delete restrict-FK targets last.
JSONB columns for flexible content schemas

Migrations

Schema changes must be additive across at least one deploy. Add new tables/columns/indexes first, deploy code that can read and write both shapes, backfill separately when needed, switch reads/writes, then drop the old shape in a later release.
Remember the deploy order: migrations run before the new API tasks finish rolling out. Old API tasks can serve requests against the new schema during the rollout, and a failed rollout can leave the old API running on the new schema.
Drizzle does not generate down migrations. If a migration succeeds and the application rollout fails, rollback is a manual forward fix unless the old application remains schema-compatible.
Do not include irreversible schema operations in the same release as risky application code. Split them into a small migration-only release or an additive preparatory release.
Destructive or backwards-incompatible SQL requires an explicit acknowledgement in the migration file. scripts/check-migration-safety.ts blocks guarded operations such as DROP, TRUNCATE, DELETE FROM, table/column renames, dropped constraints, column type changes, and disabling row-level security unless the file includes:
```
-- stella-migration-safety: reviewed destructive-change - <why this is safe and how rollback is handled>
```
Treat existing large-table changes as lock-sensitive. Prefer CREATE INDEX CONCURRENTLY, avoid table rewrites in request-critical tables, and backfill in bounded batches outside the schema migration when the data volume can grow.

Queries

Prefer Drizzle's relational query API (db.query.*.findFirst, findMany) over SQL-like syntax (select().from().where()). Use SQL-like syntax only for cross-table filtering, aggregations, unions.
Every new list query must support limit and a cursor or offset. Never return an unbounded findMany without a limit.
Add indexes for any column used in WHERE, ORDER BY, or JOIN. Lead composite indexes with the tenant-scoping column.
Keep transactions short: do I/O (S3, external APIs) outside the transaction, not inside.
Don't filter on unindexed JSONB fields in WHERE clauses. Fetch by an indexed column, then validate the JSONB content in application code. Narrow the discriminated union with a type guard instead of using as casts.