seasonedcc Database Design
ALWAYS load the "kysely" skill before anything else. Follow these principles when designing database tables and writing migrations for this project.
{{mutability-stance}}
Two kinds of tables
Every application table is one of two kinds:
Identity tables hold what is immutable by design: id, createdAt, and ownership foreign keys that can never change (e.g., companyId — a product never moves to another company).
Event tables hold everything that happens to an entity after (and including) its birth. Each event row is a fact that occurred at createdAt and is never modified.
products -- identity: who exists
id UUID PK
company_id UUID NOT NULL (FK)
created_at TIMESTAMPTZ NOT NULL
product_revisions -- event: what the product's details are
id UUID PK
product_id UUID NOT NULL (FK)
name TEXT NOT NULL
sku TEXT NOT NULL
unit_id UUID NOT NULL (FK)
created_at TIMESTAMPTZ NOT NULL -- latest revision wins
product_archivals -- event: the product was archived
id UUID PK
product_id UUID NOT NULL (FK)
created_at TIMESTAMPTZ NOT NULL
Event tables: one per cohesive concern
Slice mutable state into event tables per cohesive concern — fields that change together through one user action share one table:
- A "revision" table snapshots all fields of its concern per edit. The product "edit details" form (name, sku, unit) writes one
product_revisionsrow carrying all those fields — full snapshot, not a diff — so reading current state needs only the latest row. - Separately-actioned state changes each get their own narrow event table:
product_archivals,order_submissions,invoice_approvals. - Not per-field (table explosion, N-way joins to assemble current state), and not forced whole-entity (couples unrelated concerns into one table).
Creation is a transaction: inserting an entity writes the identity row plus the first row of each relevant event table in one transaction. "Latest event wins" then needs no special case for freshly created entities, and no column ever needs to be nullable while "waiting" for data.
Naming: identity tables are plural nouns (products); event tables are <entity>_<past-action-plural> (product_revisions, order_approvals, user_session_revocations).
One-way events and paired toggles
Model each state transition by its real shape:
- One-way transitions (revocation, completion, cancellation, termination): a single event table; the existence of a row is the state. A
user_session_revocationsrow means the session is revoked, forever. - Reversible toggles (archive/restore, activate/deactivate, grant/revoke): a pair of event tables; the newer of the two latest events wins. Each direction can carry direction-specific data (e.g., a restoration's reason).
const archivalEvents = db()
.selectFrom('productArchivals')
.select(['productId', 'createdAt', sql<boolean>`true`.as('archived')])
.unionAll(
db()
.selectFrom('productRestorations')
.select(['productId', 'createdAt', sql<boolean>`false`.as('archived')])
)
Take the latest row per productId to know whether the product is archived; no rows means never archived.
Deriving current state
Current state is always computed at query time from events. The canonical patterns:
Latest event wins — DISTINCT ON ordered by recency:
db()
.selectFrom('productRevisions')
.distinctOn('productId')
.orderBy('productId')
.orderBy('createdAt', 'desc')
.orderBy('id', 'desc')
.selectAll()
The id desc tie-break makes ordering deterministic if two events ever share a timestamp — but avoid creating that situation: one user action appends one event per parent per transaction.
Existence is state — EXISTS / NOT EXISTS:
.where(({ not, exists, selectFrom }) =>
not(
exists(
selectFrom('userSessionRevocations')
.select('id')
.whereRef('userSessionRevocations.userSessionId', '=', 'userSessions.id')
)
)
)
Aggregates over events — quantities are sums of movements, never stored balances:
db()
.selectFrom('stockMovements')
.select(({ fn }) => fn.sum<string>('quantityDelta').as('onHand'))
.where('stockItemId', '=', stockItemId)
Status from event existence — CASE WHEN ... EXISTS chains: success event exists → succeeded, failure event exists → failed, neither → pending. Only build the derivation query when the UI or business logic actually needs it.
Indexes: every event table gets an index on (parentId, createdAt desc) at creation time — it serves every latest-wins and existence query.
Ordering absolute-set events
Most derivations are order-insensitive (sums) or single-writer latest-wins (revisions). But when an event family mixes delta events (movements) with absolute-set events (count adjustments) over the same derived value, the derivation is latest-absolute-wins plus later deltas — and "later" must mean commit order, not transaction-begin order. now() is frozen at BEGIN, so a slow transaction can stamp an event before an absolute-set that never saw it, and the derivation silently drops the delta.
The rule, for any event family with at least one absolute-set consumer:
- Every writer of every event in the family takes the same advisory lock (
pg_advisory_xact_lock, org-scoped) before inserting, so critical sections are disjoint and inserts happen in commit order. - The ordering column (
createdAt) defaults toclock_timestamp(), notnow(), so the stamp is taken inside the locked section. - Absolute-set comparisons use strict
>; equal stamps cannot occur across transactions under the lock, and one transaction never writes both an absolute-set and a delta for the same derived cell.
An inventory family mixing movement deltas (stock_inbound_movements, stock_outbound_movements, stock_transfers) with stock_count_adjustments is the canonical shape. Give the shared lock one helper function with one home, and have every writer in the family — including any new one — take it. If clock monotonicity on the database host ever becomes a real concern, the escalation path is a shared monotonic sequence assigned under the same lock — a new keyed structure, never a rewrite of existing rows.
Lock discipline across modules
Disjoint lock keys guard nothing. A guard that checks "has the concurrent thing already happened" (has-receipts, has-shipments, is-discarded) only works if every writer it excludes takes the SAME advisory lock key before the guard's read. A lock on a different key, or a lock somewhere else in the transaction, leaves the race wide open — the writer commits between the guard's read and the guarded write. When adding a cross-module invariant, trace the lock key of every writer that could race it.
Never call a function that opens its own db().transaction() from inside an already-locked transaction. The inner transaction takes a second pooled connection, which then blocks forever on the advisory lock the outer transaction holds — a deadlock, not an error. If the logic is needed inside a locked transaction, accept a Transaction<DB> parameter or inline the write using the function's lower-level helpers.
Not every check-then-act race needs fixing. When a review flags an existence check racing a concurrent write, do the harm analysis first: if the raced-in row can never change a value the guard reads (the same predicate that filters the read also excludes it, and it feeds no aggregate the invariant depends on), no derived value can go wrong and the check-then-write idiom is acceptable. Escalate to a shared lock only when the race can corrupt a derived value — and then treat it as a lock-family decision (which writers share the key), not a one-module patch.
Always use timestamptz
All timestamp columns must use timestamptz (timestamp with time zone), never timestamp. The same applies to timetz over time if time columns are ever needed.
timestamp stores a "wall clock" value with no timezone context — the same value can mean different instants depending on the session's timezone setting. timestamptz stores an unambiguous instant in time (internally UTC), and PostgreSQL automatically converts to/from the session timezone on input/output. This prevents bugs when servers, clients, or sessions use different timezone settings.
Instead of:
.addColumn('createdAt', 'timestamp', (col) =>
col.defaultTo(sql`now()`).notNull(),
)
Do:
.addColumn('createdAt', 'timestamptz', (col) =>
col.defaultTo(sql`now()`).notNull(),
)
An event table's createdAt is the event time — the timestamp every derivation orders by.
No nullable columns — zero exceptions
Every column in every table must be non-nullable. If data isn't available at insert time, it belongs in a separate event table that gets created when that data becomes available.
Instead of:
documents
id UUID NOT NULL
markdown TEXT -- nullable, filled after OCR
Do:
documents
id UUID NOT NULL
document_ocr_results
id UUID NOT NULL
document_id UUID NOT NULL (FK)
markdown TEXT NOT NULL
created_at TIMESTAMPTZ NOT NULL
"Optional" attributes are not nullable columns either: model them as their own event table with zero-or-more rows per parent.
No updatedAt columns
Never add updatedAt to any table. When a timestamp for a state change is needed, the event row's createdAt is the timestamp for when the change occurred.
No derivable columns
Never store a column whose value can be inferred from event records. If a status is always set alongside inserting an event record, the event record is the status — the column is redundant and creates sync risk. This is why identity tables carry no status, no archived, no current* columns: all of it derives from events.
Instead of:
document_intakes
id, document_id, status, created_at
-- status is 'pending' | 'succeeded' | 'failed'
Do:
document_intakes
id, document_id, created_at
-- status derived: success record exists → succeeded
-- failure record exists → failed
-- neither exists → pending
No unique constraints on event table FKs
Event tables must never have unique constraints on the parent foreign key. Allow multiple records per parent so that actions can be rerun and historical results are preserved. The latest record by createdAt represents the current state.
Instead of:
.addColumn('documentIntakeId', 'uuid', (col) =>
col.notNull().unique().references('documentIntakes.id'),
)
Do:
.addColumn('documentIntakeId', 'uuid', (col) =>
col.notNull().references('documentIntakes.id'),
)
No unnecessary defaults
Only use defaultTo(...) for truly auto-generated values like id and createdAt. When a column has defaultTo(...), Kysely's type generator wraps it in Generated<T>, making it optional on insert. This silently loses type safety — forgetting to pass the value won't produce a compiler error.
Instead of:
.addColumn('displayName', 'text', (col) =>
col.defaultTo('').notNull(),
)
Do:
.addColumn('displayName', 'text', (col) =>
col.notNull(),
)
The first generates displayName: Generated<string> (optional on insert). The second generates displayName: string (required on insert), ensuring every insert site is forced to provide the value.
Store full resource locators
When persisting references to external resources (S3 objects, Google Drive files, etc.), store all components needed to locate the resource — not just the key/path. For S3, this means storing the bucket name alongside every S3 key. For Google Drive, it means storing both the file ID and the folder ID.
This makes stored references self-contained. If an environment variable like AWS_S3_BUCKET changes, existing records still point to the correct resource.
Instead of:
document_intake_successes
id UUID NOT NULL
temp_s3_key TEXT NOT NULL
created_at TIMESTAMPTZ NOT NULL
Do:
document_intake_successes
id UUID NOT NULL
temp_s3_key TEXT NOT NULL
temp_s3_bucket TEXT NOT NULL
created_at TIMESTAMPTZ NOT NULL
The naming convention pairs each *S3Key column with a corresponding *S3Bucket column using the same prefix (e.g., pdfS3Key / pdfS3Bucket, markdownS3Key / markdownS3Bucket).
Self-contained migrations
Never import application code (~/business/, etc.) in migration files. Migrations are frozen snapshots — they must produce the same result regardless of how the application evolves after they were written.
If a migration needs logic that already exists in the application (e.g., a normalization function for a backfill), duplicate that logic directly inside the migration file. This makes the migration immune to future changes in the imported module.
The only allowed imports in migration files are kysely (and its sql helper) and Node.js built-in modules.
Performance: derive first, then escalate
Query-time derivation is the default and stays the default until a real query is measurably slow. When that happens, escalate in strict order:
EXPLAIN ANALYZEand indexes. Most latest-wins and existence derivations are index problems. Composite(parentId, createdAt desc)indexes, partial indexes, and covering indexes go a very long way.- Partitioned tables. High-volume event tables (stock movements, processing heartbeats) partition naturally by time range. Declarative partitioning keeps hot partitions small without changing the write path or the doctrine.
- A read-side materialized-view layer. When a derivation must be served near real time and is beyond what indexes and partitions can do, the escalation is a streaming engine (RisingWave or similar) ingesting Postgres events and maintaining incrementally-updated materialized views — a pure read-side layer, rebuildable from Postgres at any time, never written back into the application schema. Do not add this layer, plan for it, or design around it until the first two rungs are exhausted on a real, measured bottleneck.
Never write a derived value back into the application schema from application code — a cache that lives in an app table is a mutable column with extra steps.