--- name: conventions-db description: "Apply when writing or modifying database schema, queries, migrations, transactions, or tenant-scoped persistence." --- # 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. - To learn whether something is stored and where, search the generated index first: `rg apps/api/src/db/schema-index` gives one line per column with its builder, flags, `file:line` and the first sentence of its comment. A column's comment is what the index shows, so lead it with what the column means. - 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()` 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 - `. 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. ## Tenant Scope and Queries - Workspace data uses the authorized `scopedDb` supplied by safe handlers so PostgreSQL RLS and query-level scope reinforce each other. Raw/root database access needs a demonstrated system-level reason and a deny-by-default RLS posture. - Ownership IDs come from server-validated context, never request bodies. Keep tenant predicates in the database query even when a preceding authorization check exists. - Prefer Drizzle's relational query API for ordinary relation reads. Use SQL-like syntax for cross-table filtering, aggregation, locking, unions, or mutations where it expresses the invariant more directly. - Every list query uses a bounded `limit` and cursor and returns the standard `Page` envelope from `apps/api/src/lib/pagination.ts`. Offset pagination, `totalCount`, and unbounded `findMany` require explicit justification. - Do not filter unindexed JSONB in request paths. Fetch through indexed tenant columns, then narrow structured content with a type guard rather than a cast. - Batch relation reads and writes. Never issue a query per item when a join, relation preload, `IN` query, or bulk mutation can express the same work. ## Public Corpus Boundary - Case-law and legislation corpora are read through their own public connections (`apps/api/src/lib/case-law-public-read-db.ts`, `legislation-public-read-db.ts`). Importing one puts the file inside the public SQL boundary (`public-case-law-db-boundary`): it may name only public-law relations. Put corpus reads in `apps/api/src/lib/case-law/` (or the legislation owner) and call them from tenant handlers; never import the connection into a module that names tenant tables. - A read that fails returns a typed failure (a `Result` error or a `ReadOutcome`), never an empty value (`no-failure-as-empty`). The caller decides how to degrade and says so in its own response type. ## Concurrency and Transactions - Keep transactions short; perform S3, network, conversion, and other external I/O outside them. - Close every read-decide-write race. Lock the decisive row with `SELECT ... FOR UPDATE`, or encode the expected state/version in the mutation `WHERE` clause and check the affected-row count. - Make retries converge. Stable identities, unique constraints, conditional transitions, and idempotency keys are stronger than read-before-insert checks. - Preserve lock order across call sites. When multiple resources must be locked, define and reuse a deterministic ordering to avoid deadlocks. ## Verification Test behavior that schema inference cannot prove: cross-tenant denial, concurrent transitions, replay/idempotence, migration parity, destructive delete ordering, and cursor stability under inserts. Prefer invariant or integration tests over mocked query-shape tests.