beginnerintermediateadvanced

System Design · Section 81

Data Consistency in User Workflows

Level: intermediate
Read: 13 min
Concepts: 2

A user workflow that spans several requests, services and retries has no single place where its correctness is visible, so the work splits into two deliberate steps. The first is naming the invariants: the statements that must be true before the workflow runs, after it runs, and after every failed, half-finished, retried or concurrently-executed attempt — inventory never goes negative, a customer is charged at most once per order, a seat is never sold twice, a tenant never reads another tenant's rows. Writing each one down with the query that would detect a violation separates the rule from whichever function happens to check it today, and forces you to enumerate what actually breaks it, which is almost always concurrency or a retry rather than a logic error on the success path. The second step is assigning each invariant a mechanism, and the five candidates form a ladder rather than a menu: a constraint has the database refuse the violating write for every writer that will ever exist; a transaction makes several writes in one database atomic; a lock serialises concurrent access to one entity for read-then-write rules; an idempotency key collapses a retried request back to a single effect; and a workflow with recorded state is what remains when a rule spans systems that share no transaction, guaranteeing convergence rather than instantaneous correctness. Each rung up that ladder buys reach and pays for it in the number of places that must behave correctly, so the rule is to take the strongest mechanism the invariant can actually be expressed in — and never one rung higher for convenience.

System Design overview

System Design for Beginners CoursefreeCodeCamp.org

What is true here

An invariant must hold before a workflow, after it, and after every partial, retried or concurrent attempt — not only on the path where every step succeeds.
What breaks invariants in practice is concurrency and retries, not logic errors: two copies of a workflow racing, or one workflow retried after an ambiguous timeout.
An application-level if check is a check, not a guarantee — the read and the write are separate statements and another request can interleave between them.
Constraint, transaction, lock, idempotency key and workflow guarantee different things at different costs; pick the strongest one that can express the invariant.
A rule that must hold on every access path cannot be enforced at each access path — move it into the database so forgetting produces an empty result, not a breach.

What you will be able to do

Enumerate the invariants a multi-step user workflow owns, including the states a half-finished attempt leaves behind
Write the detecting query for an invariant, so a violation is findable in production rather than reported by a customer
Choose between a constraint, a transaction, a lock, an idempotency key and a workflow for a specific invariant, and justify the choice
Recognise when a per-endpoint check is enforcing a rule that belongs in the database

Naming the rules

Writing invariants down separately from the code, with the query that would detect a violation.

Identifying the invariants a workflow must never violate

coreintermediate

An invariant is a statement about your data that must be true before a workflow runs, after it runs, and after every failed or half-finished attempt at running it. "Inventory for a product is never negative" is an invariant. "A customer is charged at most once per order" is an invariant. "A row belonging to tenant A is never returned to tenant B" is an invariant. The point of writing them down is that a workflow spread across several requests, services and retries has no single place where correctness is obvious — a checkout touches a cart, a stock count, a payment provider and an order record, and no one of those four sees the whole rule. Naming the invariant separates the rule from any particular piece of code that happens to enforce it, so you can then ask the only question that matters: which mechanism actually guarantees this, and what happens to the rule when that mechanism is absent, retried, or run concurrently with a second copy of the same workflow. Without that list, correctness is defended only by whatever checks the code happens to contain, and those checks are usually written for the path where everything works.

Think of it as

Think of an invariant the way a bank thinks about its ledger: at every instant, however many transfers are mid-flight, the sum of all account balances must equal the total money in the bank. Nobody enforces that by hoping each transfer function is written carefully. They enforce it by making the rule explicit, then choosing a mechanism — double-entry bookkeeping — that makes violating it structurally hard rather than merely discouraged. Your job in a user workflow is the same two steps in the same order: state the rule in one sentence that a non-engineer could check, then pick the mechanism that holds it up. A rule you have not written down is a rule nobody owns.

text

# An invariant, written so it can be checked
INVARIANT: for every product, stock_count >= 0

  holds before:  every checkout attempt
  holds after:   success, failure, timeout, retry
  detected by:   SELECT id FROM products
                 WHERE stock_count < 0
  enforced by:   <-- the next concept answers this

What we're doing: Take one checkout workflow apart and list every invariant it is quietly responsible for.

checkout-invariants.txttext

Workflow: customer checks out a cart of 3 items

Step 1  reserve stock          (inventory service)
Step 2  create payment intent  (payment provider)
Step 3  capture payment        (payment provider)
Step 4  create order record    (orders database)
Step 5  send confirmation      (notification service)

Invariants this workflow owns:

I1  stock_count >= 0 for every product, always
I2  at most one successful capture per order
I3  an order exists only if a capture succeeded
I4  a capture exists only if stock was reserved
I5  at most one confirmation email per order
I6  every row read or written carries this
    customer's tenant id

Not one of these is visible inside any single
step. Steps 1 and 4 live in different databases.

12: I1 is owned by step 1 alone, so a database constraint can hold it — this is the cheapest kind of invariant to enforce, and recognising that early saves you from reaching for a distributed mechanism you do not need.
16: I3 and I4 span two systems that have no shared transaction, so no constraint can hold them. They need an explicit workflow with recorded state — which is why naming the invariant tells you the mechanism.
20: I6 is not about this workflow at all; it is a rule every query in the system must obey. Invariants that apply everywhere want a mechanism that applies everywhere, such as row-level security, rather than a per-endpoint check that one endpoint will eventually forget.

Why this works: Reading the workflow as five steps makes it look like five small correctness problems. Reading it as six invariants shows the real shape: two are local and cheap to enforce, three cross a system boundary and need a workflow, and one is a global rule that no amount of care inside this workflow will hold up. The list, not the step diagram, is what tells you where to spend design effort.

Treating an application-level check as the enforcement mechanism

Wrong

python

product = db.get(product_id)
if product.stock_count >= quantity:      # a check
    db.update(product_id,
              stock_count=product.stock_count - quantity)

Better

sql

-- the invariant, enforced by the database
ALTER TABLE products
  ADD CONSTRAINT stock_non_negative
  CHECK (stock_count >= 0);

-- and a decrement that cannot interleave
UPDATE products SET stock_count = stock_count - $1
WHERE id = $2 AND stock_count >= $1;

What you see: Stock counts go negative under load and only under load. The code reads correctly, passes review, and works in every test, because a test never runs two copies of the function against the same row in the same millisecond.

Why: The read and the write are two separate statements, so a second request can read the same stock value between them and pass the same check. The `if` describes the invariant without holding it; a `CHECK` constraint plus a conditional update makes the database itself refuse the violating write, which no amount of concurrency can talk it out of.

From a vague worry to an owned rule

Name the rule

one sentence, no implementation words

Write the detecting query

what a violation looks like in the data

List what can break it

concurrency, retries, partial failure

Assign a mechanism

constraint, transaction, lock, idempotency, workflow

Four common invariants and the concurrent scenario that breaks each

Four common invariants and the concurrent scenario that breaks each
Invariant	What breaks it	How a violation looks in production
Inventory never goes negative	Two checkouts read stock = 1 at the same moment, both pass the check, both decrement	Stock column shows -1; a customer is promised an item that does not exist
A customer is charged at most once per order	The client retries after a network timeout on a charge whose result was never seen	Two charges for one order; the duplicate surfaces as a chargeback days later
A tenant never reads another tenant's rows	One query in one endpoint omits the tenant filter	A cross-tenant data leak, usually discovered by the customer, not by you
A seat is never sold twice	Two bookings hold the same seat between selection and payment	Two confirmations for one seat; resolved manually, at your cost

Remember: An invariant is a rule that must hold before a workflow, after it, and after every failed, retried or concurrent attempt — "inventory never negative", "charged at most once", "no cross-tenant read". Write the rule and its detecting query down separately from the code, then enumerate what breaks it under concurrency and partial failure. An `if` statement in application code is a check, not a guarantee.

Enforcing the rules

The five-rung ladder — constraint, transaction, lock, idempotency key, workflow — and how to pick a rung.

Choosing an enforcement mechanism per invariant

coreintermediate

Once an invariant is named, the design question is which mechanism actually holds it up, and the five candidates are not interchangeable — each one guarantees something different and costs something different. A database constraint (unique, check, foreign key) is the strongest and cheapest option, because the database refuses the write no matter which service, script or human issued it, but it only works for a rule expressible over rows in one database. A transaction makes several writes in one database succeed or fail as a unit, which covers rules that span tables but not rules that span services. A lock — pessimistic row locking, or an optimistic version check — serialises concurrent access to the same entity, which is what you reach for when the rule depends on reading a value and then writing based on it. An idempotency key makes a repeated request produce the same single effect rather than a second one, which is the mechanism for "at most once" rules under retries. And an explicit workflow with recorded state — a saga with compensating actions — is what remains when a rule spans systems that share no transaction, where the only honest guarantee is "we will converge, and we will record where we are while converging". The rule for choosing is to take the strongest mechanism that can actually express the invariant, because every step down that list moves enforcement further from the data and closer to code you have to remember to write.

Think of it as

Picture the five mechanisms as a ladder, with the database at the bottom and your application code at the top. Every rung upward buys you reach — a constraint cannot see two services, a workflow can — and pays for it in the number of places that have to behave correctly. A constraint is enforced once, by the database, against every writer that will ever exist, including the migration script somebody runs at 2am. A workflow is enforced by code you wrote, in the order you wrote it, on every retry path you remembered. So you climb the ladder only as far as the invariant forces you to, and never one rung higher for convenience.

sql

-- Constraint: the strongest rung, when it fits
ALTER TABLE orders
  ADD CONSTRAINT one_capture_per_order UNIQUE (order_id);

-- Optimistic lock: read-then-write without blocking
UPDATE products
   SET stock_count = stock_count - 1, version = version + 1
 WHERE id = $1 AND version = $2 AND stock_count >= 1;
-- 0 rows affected means somebody else won the race

What we're doing: Enforce "a seat is never sold twice" with each of three mechanisms and see what each actually buys.

seat-invariant-mechanisms.txttext

Attempt A -- application check only
  read seat.status; if 'free' then set 'sold'
  Two requests read 'free' in the same millisecond.
  Both write 'sold'. Two customers, one seat.

Attempt B -- unique constraint on the booking
  INSERT INTO bookings (seat_id, order_id)
  UNIQUE (seat_id)
  Second insert fails with a unique violation.
  Invariant holds against every writer, always.
  But: a customer who is mid-payment has no
  claim on the seat yet, so the seat can be
  taken from under them at checkout.

Attempt C -- constraint + a held reservation
  INSERT a hold row (seat_id UNIQUE, expires_at)
  payment succeeds -> promote the hold to a booking
  payment fails or hold expires -> delete the hold
  Invariant still enforced by the constraint;
  the workflow only decides who holds the claim
  and for how long.

2: The read and the write are separate statements, so nothing stops a second request from reading the same value in between. This is the failure the previous concept described, and no amount of care inside the function fixes it.
7: The unique constraint is the strongest available mechanism and it holds the invariant completely — the second insert cannot succeed. Notice what it does not do: it says nothing about who is allowed to be trying, which is a product question, not a consistency one.
15: The final design uses two mechanisms for two different jobs: the constraint enforces the invariant, and the workflow with its expiring hold manages the claim. Choosing a mechanism is not always choosing one — it is assigning each part of the problem to the rung that fits it.

Why this works: Attempt A shows what an unguarded read-then-write costs. Attempt B shows that the strongest mechanism really does hold the invariant, and that holding an invariant is not the same as having a good product. Attempt C keeps the constraint doing the enforcement — so the rule survives a bug in the hold logic — and adds a workflow only for the part a constraint genuinely cannot express, which is the shape most real designs land on.

Reaching for a distributed lock when a constraint would do

Wrong

python

with redis_lock(f"seat:{seat_id}", ttl=30):
    if not booking_exists(seat_id):
        create_booking(seat_id, order_id)
# correctness now depends on the lock service
# being up, the TTL outliving the work, and
# every future writer remembering to take it

Better

sql

-- correctness depends on nothing but the table
CREATE UNIQUE INDEX one_booking_per_seat
  ON bookings (seat_id);
-- then insert and handle the unique violation;
-- a lost race is a caught error, not a bug

What you see: A double booking appears during an incident in which the lock service was briefly unreachable, or after a slow request outlived its lock TTL — and again months later, when a new batch import writes bookings directly without taking the lock, because the lock lives in application code that the import never runs.

Why: A distributed lock adds a second system that must be available and correct for your data to stay correct, and it only binds the callers that remember to take it. A unique index is enforced by the same system that stores the data, against every writer, with no additional availability dependency — see Distributed Locks for the full account of why the simpler mechanism is preferred.

The enforcement ladder — take the lowest rung the invariant fits on

Constraint

enforced by the database against every writer that will ever exist

Transaction

several writes in one database commit or roll back together

Lock (pessimistic or optimistic)

two concurrent workflows cannot interleave on one entity

Idempotency key

a repeated request produces the original single effect

Workflow with recorded state

durable, resumable steps with compensation — convergence, not instant correctness

Five mechanisms: what each guarantees, and where it stops working

Five mechanisms: what each guarantees, and where it stops working
Mechanism	Guarantees	Stops working when
Constraint	No writer of any kind can store a violating row	The rule spans two databases, or depends on external state
Transaction	Several writes in one database commit or roll back together	A write in the set lives in another service or database
Lock (pessimistic or optimistic)	Two concurrent workflows cannot interleave on the same entity	The contended entity is hot enough that serialising it becomes the bottleneck
Idempotency key	A repeated request produces the original single effect	The operation has no natural key, or the key store is dropped before retries stop
Workflow with recorded state	Every step is durable and resumable; failures compensate	You need the invariant to hold instantaneously, not eventually

Mapping the four invariants from the previous concept to a mechanism

Mapping the four invariants from the previous concept to a mechanism
Invariant	Mechanism	Why that one
stock_count >= 0	Check constraint + conditional UPDATE	Expressible over one row; the database can refuse it outright
At most one capture per order	Idempotency key on the capture request	The threat is a retry, not concurrency — dedupe on a caller-supplied key
An order exists only if a capture succeeded	Workflow with recorded state	Spans your database and the payment provider; no shared transaction exists
No cross-tenant reads	Row-level security in the database	A per-endpoint filter fails the day one endpoint forgets it

Remember: For each invariant, pick the strongest mechanism that can express it: constraint (the database refuses every violating write), transaction (atomic writes in one database), lock (serialise a read-then-write on one entity), idempotency key (a retry produces one effect), workflow with recorded state (spans systems, converges rather than holding instantly). Climbing that ladder buys reach and pays in the number of places that must behave correctly — so climb only as far as the invariant forces you.