Idempotency & State Machines in Schema
You'll learn to
- -Model a multi-step workflow (onboarding, order lifecycle) as an explicit status column with a legal-transitions table
- -Design an append-only audit trail that reconstructs exactly what changed, when, and by whom
A client onboarding process moves through a sequence of statuses - application submitted, documents verified, compliance approved, account active - the schema-design counterpart of the State pattern from Phase 1, and it comes with the same core question: how do you prevent an invalid transition, like jumping straight from "submitted" to "active" while skipping compliance approval?
Status as a Column, With Enforced Transitions
CREATE TABLE client_onboarding (
id INTEGER PRIMARY KEY,
client_id INTEGER NOT NULL REFERENCES users(id),
status TEXT NOT NULL DEFAULT 'submitted'
CHECK (status IN ('submitted', 'documents_verified', 'compliance_approved', 'active', 'rejected'))
);
CREATE TABLE onboarding_transitions ( -- the explicit "legal moves" for this state machine
from_status TEXT NOT NULL,
to_status TEXT NOT NULL,
PRIMARY KEY (from_status, to_status)
);
INSERT INTO onboarding_transitions VALUES
('submitted', 'documents_verified'), ('submitted', 'rejected'),
('documents_verified', 'compliance_approved'), ('documents_verified', 'rejected'),
('compliance_approved', 'active');
-- application code checks onboarding_transitions before writing a new status,
-- rejecting an attempted (submitted -> active) jump directlyEncoding legal transitions as data, rather than as scattered if-statements across application code, means the full state machine is visible in one place and queryable - "what are all the valid next steps from documents_verified" is a single SELECT, not a search through code.
Why This Needs a Concurrency-Safe Update
Recall the Phase 1 Concurrency module: transitioning status is a check-then-act operation (verify the transition is legal, then write the new status), which needs the update to be atomic - typically an UPDATE ... WHERE status = :expected_current_status, checking the affected row count to confirm the transition actually applied rather than being silently skipped because another concurrent request already moved the row to a different status.
UPDATE client_onboarding
SET status = 'compliance_approved'
WHERE id = :onboarding_id AND status = 'documents_verified';
-- if this affects 0 rows, someone else already changed the status - the
-- application must check the affected-row count, not assume successThe Audit Trail: What Changed, When, By Whom
A regulated process like this typically needs a full audit trail, not just the current status - every transition recorded as an immutable, append-only row, capturing who initiated it and when, which is a compliance requirement in most real financial systems (and the direct subject of this chapter's own SEC Audit Trail level, bridged below).
CREATE TABLE onboarding_status_history (
id INTEGER PRIMARY KEY,
onboarding_id INTEGER NOT NULL REFERENCES client_onboarding(id),
from_status TEXT,
to_status TEXT NOT NULL,
changed_by INTEGER REFERENCES users(id),
changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- client_onboarding.status always reflects "now"; this table reconstructs the full historyKeeping a live "current status" column on client_onboarding and a separate append-only history table (rather than deriving current status by querying "the most recent history row") keeps everyday reads cheap and simple, while the history table exists purely for audit and reconstruction - two tables, two different jobs.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design Client Onboarding and the SEC Audit Trail in the LLD Lab's Wolf of Wall Street act.