database design

the database is the one layer every writer, worker, and retry passes through, so invariants enforced only in application code are invariants a missed branch quietly breaks. constraints, foreign keys, and typed columns keep identity, ownership, and state provable no matter who writes. modeling facts before columns also keeps caches and projections honestly non-authoritative — exactly one durable source of truth to reconcile against.

model durable domain facts where they are authoritative. a table is not storage for an object; it is the boundary that proves identity, ownership, relationship cardinality, valid state transitions, and time semantics even when a worker retries or a second caller races the first.

start with a fact and its invariants, then choose columns, foreign keys, unique keys, checks, and indexes that enforce them. treat caches, read projections, and derived totals as explicitly non-authoritative views with a named rebuild or invalidation path.

use a typed column, relation, or normalized fact whenever a value participates in identity, authorization, filtering, ordering, aggregation, joins, a closed state, or an invariant. JSONB is reserved for an intentionally opaque payload whose internal shape is not part of the database's query contract.

do not store "temporary" JSONB when the application already reads one nested field to make a decision — that field has declared itself a column, relation, or projection candidate. validate JSONB at its ingress boundary and document its owner/version; never discover its shape through ad hoc query expressions.

an aggregate is a bounded fact that answers a repeated owner-scoped question without scanning its entire history. define the aggregate's authority, scope, inputs, update rule, and rebuild path before using it in a route or interface. it may be a canonical counter/summary maintained with the write, or a clearly derived projection rebuilt from canonical facts — never an undocumented cache that silently replaces the source of truth.

• each repeated read: inspect its query plan; bound it by owner, index, page, or aggregate key.
• no route reconstructs an aggregate by loading unbounded rows.
• each JSONB field: name its schema owner; show why nested values are not query/invariant inputs — extract any value that is.

a process_step_record stores the observable state of a process step — not named process_step_map, because the relationship has its own lifecycle: status, attempt count, timestamps, error, serialized output or R2 output reference, and a durable trace identity.

the migration gives it a generated primary ID and foreign key to the process instance. business identity is scoped by the unique (process_instance_id, step_slug) constraint: one durable record per named step per instance. it also checks closed status and output types, mutual exclusion of inline/R2 output storage, positive attempts, valid timing, and state-appropriate content. indexes serve the actual instance/ordinal and owner/update-time reads.

result: a retrying process cannot create two records for the same step, and malformed state cannot become a valid row merely because an application branch was missed.

1. record the fact, authority, owner, lifecycle, and read paths in the slice specification before creating a table.
2. keep generated record IDs separate from authored slugs, handles, and external-provider identities.
3. encode scope-specific identity with a unique constraint.
4. add foreign keys, checks, and indexes for every invariant the database can prove.
5. name projections and caches as such; define their source of truth and refresh/invalidation path.
6. generate a forward-only migration, inspect its SQL, then verify the actual data boundary after deployment.

• make a table from a current component shape or API response by default.
• use metadata, data, map, or link when a relationship has a durable lifecycle and semantic name.
• put invariant-bearing state only in application code.
• derive a business key from a display label, filename, or path.
• treat a cache or Durable Object projection as the canonical record without an explicit authority decision.
• store a state that can be computed from stored facts; the fold is the authority (computed state).

bad

create table workflow_data (
  id text primary key,
  metadata jsonb not null
);

ownership, status, and timestamps hidden in metadata cannot be constrained, indexed, or understood from the schema. every invariant this table should prove — who owns the row, what state it is in, when it changed — now depends on application code that a missed branch, a retrying worker, or a racing caller can silently violate. duplicate facts can be inserted, orphaned rows can accumulate, and queries filter or sort on JSONB expressions no index serves, so reads grow unbounded as data grows. corruption is invisible until a report disagrees with reality, and repair requires archaeology into opaque payloads.

good

create table workflow_run (
  id text primary key,
  workflow_id text not null references workflow(id),
  status text not null,
  created_at text not null,
  unique (workflow_id, id)
);

the table names one durable fact and exposes the facts the database must prove: ownership via foreign key, state via constrained column, identity via scoped unique constraint. because these are schema facts, every writer — service, worker, retry, manual repair — is held to the same invariants without coordination, and indexes can serve the real reads. violations surface as typed constraint failures at write time instead of silent corruption discovered months later in a report.

table naming · identifiers and keys · schema migrations · durable process-step record · backups and recovery · aggregate design · prefixed nanoid identifiers