Idempotency & State Machines in Schema
You'll learn to
- -Model a multi-step workflow as a status column plus a table that lists the legal moves
- -Make a status change safe under concurrency with a conditional update and a row count check
- -Keep a current status for fast reads and an append-only history for audit, as two tables with two jobs
A client onboarding process moves through steps: application submitted, documents verified, compliance approved, account active. This is the schema-design counterpart of the State pattern from Phase 1, and it raises the same question: how do you stop an invalid jump, such as going straight from "submitted" to "active" and skipping the compliance check?
An Analogy: A Board Game's Allowed Moves
In a board game, a piece can only move along certain lines. The rules are written down once, and everyone consults the same rulebook. If the rules lived in each player's head, players would disagree and cheat by accident. A state machine for a workflow is that rulebook: a set of statuses, and a list of which moves between them are legal.
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 directlyWriting the legal transitions as data, not as if statements scattered through application code, means the whole state machine is visible and queryable in one place. "What are the valid next steps from documents_verified?" becomes one SELECT, not a search through the codebase.
Why the Update Must Be Safe Under Concurrency
Recall the Concurrency module. Changing a status is a check-then-act operation: check the move is legal, then write the new status. Two requests could both pass the check and both write. The standard fix is a conditional update, UPDATE ... WHERE status = :expected_current_status, and then looking at how many rows it changed. If it changed zero, someone else already moved the row, and the application must treat that as a failure instead of assuming it worked.
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, and By Whom
A regulated process also needs a full audit trail, not only the current status. Every transition is recorded as an immutable, append-only row with who made it and when. This is a requirement in most financial systems. The Lab level named after the SEC (the US Securities and Exchange Commission, whose rules require records like this) is built around exactly this table.
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 historyThe demo plays out a small story using the same ideas in Python. The first request tries to jump from submitted straight to active and is refused. Ana makes a legal move. Bob then sends an out-of-date request that expects the old status, and it updates nothing. Finally Bob makes the next legal move.
Keep both a live status column and a separate append-only history table, instead of working out the current status from the latest history row. Everyday reads stay cheap and simple, and the history table exists only 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.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!