Skip to content
LLD Learn/Advanced Transactional Modeling
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Idempotency & State Machines in Schema

4 min read

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

A status column, plus a table defining which transitions are legal
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 directly

Writing 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.

An atomic, conditional status transition
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 success

The 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.

Every transition recorded, never overwritten
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 history

The 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.

Press Run: an illegal jump, a stale request, and a history that records only real changes

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.

Ready to Build This?

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.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!