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, so lead a column's comment with what the column means.
- Reserve
.$type<T>() for branded or structured types. Use a native
PostgreSQL enum only when the value set is genuinely permanent. [review]
- Use cascade deletes for workspace-owned dependants and restrict deletes for
shared file references. Trace the full FK graph before multi-resource
deletion. [review]
- 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. [review]
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.
[check: scripts/check-migration-safety.ts, .github/workflows/ci.yml]
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. [check: .github/workflows/ci.yml]
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. [check: scripts/check-migration-safety.ts]
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. [review]
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.
[check: scripts/check-migration-safety.ts, scripts/check-migration-locks.ts]
Keep repairable work in online-migrations.ts. Long backfills stay outside
schema migrations and checkpoint durably.
[test: apps/api/src/db/online-migrations.test.ts]
A migration directory's name is its identity: the migrator applies every
directory whose name the database has not recorded, in name order. Never
rename or delete a merged migration; add a new one instead.
[check: scripts/check-migration-order.ts]
New timestamps sort after the newest migration on the base branch. [review]
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.
[check: .github/workflows/ci.yml]
Tenant Scope and Queries
Prefer Drizzle's relational query API for ordinary relation reads, and SQL-like
syntax where it states the invariant more directly (cross-table filters,
aggregation, locking, unions, mutations).
- 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. [check: ratchet:direct-root-connection-imports]
- 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.
[review]
Concurrency and Transactions
Keep transactions short; perform S3, network, conversion, and other external
I/O outside them. [review]
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. [review]
Make retries converge. Stable identities, unique constraints, conditional
transitions, and idempotency keys are stronger than read-before-insert
checks. [review]
Preserve lock order across call sites. When multiple resources must be
locked, define and reuse a deterministic ordering to avoid deadlocks.
[review]
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.
1---2name: conventions-db3description: "Apply when writing or modifying database schema, queries, migrations, transactions, or tenant-scoped persistence."4metadata:5 source: https://skillmd.com/skills/stella/conventions-db6---78# Database Conventions910Apply when writing or modifying database schema, queries, migrations,11transactions, or tenant-scoped persistence.1213## Schema1415Schema lives under `apps/api/src/db/schema/`; read the owning slice and related16foreign keys before editing. To learn whether something is stored and where,17search the generated index first: `rg <word> apps/api/src/db/schema-index` gives18one line per column with its builder, flags, `file:line` and the first sentence19of its comment, so lead a column's comment with what the column means.2021- Reserve `.$type<T>()` for branded or structured types. Use a native22 PostgreSQL enum only when the value set is genuinely permanent. [review]23- Use cascade deletes for workspace-owned dependants and restrict deletes for24 shared file references. Trace the full FK graph before multi-resource25 deletion. [review]26- Add indexes for columns used in `WHERE`, `ORDER BY`, or `JOIN`; lead27 composite indexes with the tenant-scoping columns. Treat changes to large28 tables as lock-sensitive. [review]2930## Migrations3132### Expand-only pre-deploy phase3334SQL migrations are a blocking pre-deploy phase, so keep them expand-only: add35tables or columns, add constraints as `NOT VALID`, and use only short-lock DDL.36Never put data backfills, `CREATE [UNIQUE] INDEX CONCURRENTLY`, or37`ALTER TABLE ... VALIDATE CONSTRAINT` in a migration. Governed, bounded38backfills belong to `apps/api/src/db/backfill-runtime.ts` and registered online39repairs and concurrent index builds (`ONLINE_MIGRATION_INDEXES`) in40`apps/api/src/db/online-migrations.ts`.41[check: scripts/check-migration-safety.ts, .github/workflows/ci.yml]4243- `bun --filter @stll/api db:migrate` is the shipped migration path used by CI44 and deployment. `db:push` is a local declarative schema-diff tool; it does45 not replace committed migrations and must not be described as the deployment46 path. [check: .github/workflows/ci.yml]4748- Schema changes remain additive across a rollout: add, deploy compatible49 reads/writes, backfill in bounded batches, switch, then remove the old shape50 in a later release. [check: scripts/check-migration-safety.ts]51- Migrations run before new API tasks finish rolling out. Old tasks must remain52 compatible with the migrated schema, and a failed rollout must have a safe53 forward-fix path. [review]54- Keep irreversible schema operations out of the same release as risky55 application changes. Destructive and access-control SQL requires a56 statement-scoped acknowledgement enforced by57 `scripts/check-migration-safety.ts`, placed in the comment block directly58 above the statement:59 `-- stella-migration-safety: reviewed <rule-id> - <why this is safe>`.60 An acknowledgement that clears nothing is an error. Every migration sets61 `lock_timeout` and `statement_timeout` first.62 [check: scripts/check-migration-safety.ts, scripts/check-migration-locks.ts]63- Keep repairable work in `online-migrations.ts`. Long backfills stay outside64 schema migrations and checkpoint durably.65 [test: apps/api/src/db/online-migrations.test.ts]66- A migration directory's name is its identity: the migrator applies every67 directory whose name the database has not recorded, in name order. Never68 rename or delete a merged migration; add a new one instead.69 [check: scripts/check-migration-order.ts]70- New timestamps sort after the newest migration on the base branch. [review]7172- Validate migration history two ways: apply every committed migration to a73 fresh database, then confirm74 `bun --filter @stll/api db:push -- --explain` reports no schema drift. Do not75 repair drift by resetting a shared database.76 [check: .github/workflows/ci.yml]7778## Tenant Scope and Queries7980Prefer Drizzle's relational query API for ordinary relation reads, and SQL-like81syntax where it states the invariant more directly (cross-table filters,82aggregation, locking, unions, mutations).8384- Workspace data uses the authorized `scopedDb` supplied by safe handlers so85 PostgreSQL RLS and query-level scope reinforce each other. Raw/root database86 access needs a demonstrated system-level reason and a deny-by-default RLS87 posture. [check: ratchet:direct-root-connection-imports]88- Do not filter unindexed JSONB in request paths. Fetch through indexed tenant89 columns, then narrow structured content with a type guard rather than a cast.90 [review]9192## Concurrency and Transactions9394- Keep transactions short; perform S3, network, conversion, and other external95 I/O outside them. [review]96- Close every read-decide-write race. Lock the decisive row with97 `SELECT ... FOR UPDATE`, or encode the expected state/version in the98 mutation `WHERE` clause and check the affected-row count. [review]99100- Make retries converge. Stable identities, unique constraints, conditional101 transitions, and idempotency keys are stronger than read-before-insert102 checks. [review]103- Preserve lock order across call sites. When multiple resources must be104 locked, define and reuse a deterministic ordering to avoid deadlocks.105 [review]106107## Verification108109Test behavior that schema inference cannot prove: cross-tenant denial,110concurrent transitions, replay/idempotence, migration parity, destructive111delete ordering, and cursor stability under inserts. Prefer invariant or112integration tests over mocked query-shape tests.