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 <word> 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<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

Expand-only pre-deploy phase

SQL migrations are a blocking pre-deploy phase, so keep them expand-only: add tables or columns, add constraints as NOT VALID, and use only short-lock DDL. Never put data backfills, CREATE [UNIQUE] INDEX CONCURRENTLY, or ALTER TABLE ... VALIDATE CONSTRAINT in a migration. Governed, bounded backfills belong to apps/api/src/db/backfill-runtime.ts and registered online repairs and concurrent index builds (ONLINE_MIGRATION_INDEXES) in apps/api/src/db/online-migrations.ts.

• 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 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.
• Keep repairable work in online-migrations.ts. Long backfills stay outside schema migrations and checkpoint durably.
• A migration directory's name is its identity: the migrator applies every directory whose name the database has not recorded, in name order. New timestamps sort after the newest migration on the base branch. 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<T> 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.