durable process-step record

a durable process orchestrator needed to track each step's lifecycle within a run: start, completion, retries, return value, errors. the early design stored step execution as a pure association — one row linking a process instance to a step name, with nullable output fields. three concrete failures:

1. retry inserted a second row instead of updating the first (no unique constraint on step_name per instance) — "all steps for this run" showed duplicates in the UI.
2. a completed record could hold both inline output and an external-storage reference — ambiguous semantics for consumers.
3. racing retries could record completion before start time; no database check prevented it.

the design looked compact, but the table could not enforce the invariants that make observation truthful.

application code cannot defend invariants under concurrency: two racing retries or a late repair can write any combination of rows unless the database refuses it. a pure association table trusts that code, so every observer re-derives what the rows mean. constraints live in the fact table — the one place that enforces them atomically. holds anywhere a relationship gains status, history, or output.

• table named for the durable fact, not as a map or link.
• generated primary ID + owning-instance foreign key + scoped unique key (one named step per instance).
• database-enforced: closed status/output types, mutual exclusion of inline vs external output, positive attempts, coherent timing, state-appropriate content.
• detached-callback wrapper upserts start/completion/error against the scoped unique key — a retry updates the same fact, not a duplicate.
• indexes mirror the reads the interface and diagnostics actually make: owner/ordinal and owner/time.

three identities: a generated row ID for storage, a parent process instance owning the record, and a scoped step slug unique within that instance. the constraint is UNIQUE(instance_id, step_slug), not a globally unique human label.

each constraint is a durable fact application code alone cannot prove under concurrency or later repair.

when a relationship acquires status, history, permissions, output, or its own identity, promote it to a semantic fact table: scoped business key, database-enforced invariants, indexes that mirror authoritative reads.

review the generated migration statement by statement. insert representative valid rows, then attempt each forbidden combination and confirm the database rejects it. exercise the retry/upsert path and both indexed read paths — constraints and indexes must be actual behavior, not an attractive schema diagram.

bad

CREATE TABLE step_execution (
  id INTEGER PRIMARY KEY,
  process_instance_id INTEGER NOT NULL,
  step_name TEXT NOT NULL,
  output TEXT,
  error TEXT,
  created_at TIMESTAMP,
  -- no unique constraint on (instance_id, step_name)
  -- no check on output types or mutual exclusion
  -- no check on timing
);

-- retry of "process_data" creates a second row
INSERT INTO step_execution (...) VALUES (instance_123, 'process_data', ...);
INSERT INTO step_execution (...) VALUES (instance_123, 'process_data', ...);

-- UI sees two rows for the same step
SELECT * FROM step_execution WHERE process_instance_id = 123;
-- output: two rows, both with step_name='process_data', different outputs
-- result: UI shows step ran twice; unclear which attempt succeeded

retries create duplicates; a timing race can record completion before start; no check prevents invalid state. the table is a map, not a fact — every consumer (interface, diagnostics, repair tooling) re-derives which row is authoritative, so a late retry or concurrent writer silently corrupts the history the orchestrator is trusted to report.

good

CREATE TABLE workflow_step_record (
  id INTEGER PRIMARY KEY,
  instance_id INTEGER NOT NULL,
  step_slug TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('pending', 'running', 'completed', 'failed')),
  attempt_count INTEGER NOT NULL CHECK (attempt_count > 0),
  output TEXT,
  external_output_url TEXT,
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  error_code TEXT,
  CHECK (
    -- mutual exclusion: inline or external, not both
    (output IS NOT NULL AND external_output_url IS NULL) OR
    (output IS NULL AND external_output_url IS NOT NULL) OR
    (output IS NULL AND external_output_url IS NULL)
  ),
  CHECK (
    -- timing: start must precede completion
    started_at <= completed_at OR (completed_at IS NULL)
  ),
  UNIQUE(instance_id, step_slug)
);

-- retry of "process_data" updates the same row
INSERT INTO workflow_step_record (instance_id, step_slug, status, attempt_count, ...)
VALUES (123, 'process_data', 'running', 1, ...)
ON CONFLICT(instance_id, step_slug) DO UPDATE SET
  status = 'running',
  attempt_count = attempt_count + 1,
  ...;

-- UI sees one row per step, attempt_count shows retries
SELECT * FROM workflow_step_record WHERE instance_id = 123;
-- output: one row per step, attempt_count=2, status='completed'
-- result: clear record of what happened

the scoped unique key makes retries idempotent — they update, not duplicate. database checks enforce timing, mutual exclusion, and closed values. the record is a semantic fact, not an association; repair operations reason about invariants without application logic.

database design · schema migrations · async local execution context