conventions-db

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.
• 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.
• 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.

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.