Modeling State Machines: Turning Valid Status Transitions Into Schema and Rules

A status column only records where an order sits right now — it says nothing about which move is legal next. Modeling a state machine means enumerating every valid state, every permitted transition between them, and the guard conditions gating each move, then enforcing that graph in your schema and code, not leaving it as a comment nobody reads.

Quick answer: Treat status as a derived value, not a free-form field. Define the finite set of states, the legal from → to transitions between them, and enforce that graph with a transitions table, application checks, or both — then log every change so you can prove what happened.

Why a Bare Status Column Turns Into a Bug Factory

A single status text or enum field lets application code write literally any value into it from any prior value, so nothing stops a shipped order from reverting to pending, a refunded order from being marked paid again, or two concurrent requests approving the same cancellation. Undefined transitions aren't rare edge cases — they're the default behavior of an unconstrained field.

These aren't hypothetical failure modes. They're the specific bugs that show up in production order systems once volume climbs:

• Double refund — two support agents click "refund" within the same second because nothing recorded that a refund was already issued.
• Ghost shipment — a webhook retry writes shipped a second time, which re-triggers a shipping-confirmation email the customer already got.
• Impossible resurrection — a cancelled order gets marked paid because a batch job reprocesses a queue that was never told the order was cancelled.
• Silent skip — an order jumps straight from pending to delivered because a handler wasn't guarded against out-of-order webhook delivery.

Martin Fowler's writing on the State pattern and finite state machines makes the underlying point well: a status attribute is really shorthand for "which state in a well-defined machine is this object in." Skip modeling the machine, and the bug doesn't disappear — it just moves from your schema into your bug tracker, where it's far more expensive to find.

A state machine is really just an ordinary data model with time added as a dimension. If entities, relationships, and normalization still feel shaky, our data modeling complete guide covers those fundamentals first — the rest of this article assumes you're comfortable with an entity having attributes and relationships, and just adds "which states are legal" on top.

Mapping the Order Lifecycle: States, Events, and the Legal-Transition Diagram

An order lifecycle is a finite set of states — commonly pending, paid, shipped, delivered, cancelled, refunded, and disputed — connected by transitions that each require a triggering event and, often, a guard condition. Drawing that graph before writing schema turns "the status changed" into "this specific, allowed move happened for this specific reason."

Each state means something distinct, not just a different label:

• pending — order placed, payment not yet captured.
• paid — payment captured, not yet handed to fulfillment.
• shipped — handed to a carrier, in transit.
• delivered — carrier confirms receipt.
• cancelled — terminated before fulfillment, no charge stands.
• refunded — money returned to the customer, order otherwise closed.
• disputed — a chargeback was filed; outcome pending.

Real payment platforms formalize exactly this pattern. Stripe's PaymentIntent object moves through a fixed, documented set of statuses — requires_payment_method, requires_confirmation, processing, succeeded, canceled, and a few others — and Stripe's own API explicitly rejects attempts to jump between statuses that aren't adjacent in that machine. Your order status deserves the same discipline, even if you're not a payments company.

Here's the legal-transition diagram for the lifecycle above, written as a plain traversal so it's easy to check against your own rules:

pending
  ├─ payment_captured ─────────────────▶ paid
  └─ payment_failed / customer_cancel ─▶ cancelled

paid
  ├─ fulfillment_dispatched ───────────▶ shipped
  ├─ refund_issued (pre-shipment) ─────▶ refunded
  └─ cancelled_before_fulfillment ─────▶ cancelled

shipped
  ├─ carrier_confirmed_delivery ───────▶ delivered
  ├─ return_received / refund_issued ──▶ refunded
  └─ chargeback_filed ─────────────────▶ disputed

delivered
  ├─ return_approved ───────────────────▶ refunded
  └─ chargeback_filed ──────────────────▶ disputed

disputed
  ├─ dispute_resolved_favor_customer ──▶ refunded
  └─ dispute_resolved_favor_merchant ──▶ paid   (charge stands — loops back)

The same graph, as a transition table you can check schema changes against directly:

Two things fall out of this table immediately. First, disputed can resolve back to paid — a genuine feedback loop, not a dead end. Second, there's no legal path directly from pending to delivered, and nothing at all leaves cancelled. Every arrow you can't draw is a transition your code should actively reject, not one it happens to never trigger.

This is the same discipline as an entity diagram, applied to time instead of relationships. If you haven't already, drawing the ERD before you touch a migration file is the equivalent habit for the structure your states will live inside — sketch the graph, then let the schema follow it.

Where to Enforce Legal Transitions: App Code, a Transitions Table, or Both

Application-code checks are fast to write and easy to customize per role, but they only protect the paths that run through that code — a batch job, an admin console query, or a second service can still write an illegal status directly to the row. A database-level transitions table or constraint protects every writer, including ones you haven't built yet, at the cost of being slower to iterate on business rules.

For anything handling money or inventory, layering both is worth the small duplication. The app layer gives a support agent a message like "can't refund a cancelled order" instead of a raw database exception; the trigger guarantees that even a stray script can't corrupt the graph.

Once you're storing from_status, to_status, allowed roles, and guard conditions as rows instead of scattered if statements, you've effectively promoted "transition" from a rule buried in code to a first-class row in your schema. That's precisely the judgment call covered in what counts as an entity when you're modeling a domain: if a concept has identity, attributes, and its own lifecycle, model it — don't encode it as logic that only one team remembers exists.

Designing the Schema: Status Column, Transitions Table, and State-Change Log

A robust implementation needs three pieces at minimum: a constrained status column on orders (an enum type or a foreign key to a lookup table), a status_transitions table listing every legal from_status → to_status pair, and an append-only order_status_events table recording every change that actually happened. The first two define what's legal; the third proves what occurred.

Here's a Postgres-flavored version of that schema, including a trigger that makes the transitions table the actual law rather than documentation:

create type order_status as enum (
  'pending', 'paid', 'shipped', 'delivered',
  'cancelled', 'refunded', 'disputed'
);

create table orders (
  id          bigint primary key generated always as identity,
  status      order_status not null default 'pending',
  updated_at  timestamptz not null default now()
);

create table status_transitions (
  from_status  order_status not null,
  to_status    order_status not null,
  event_name   text not null,
  guard        text,               -- human-readable guard condition
  primary key (from_status, to_status)
);

create table order_status_events (
  id           bigint primary key generated always as identity,
  order_id     bigint not null references orders(id),
  from_status  order_status not null,
  to_status    order_status not null,
  event_name   text not null,
  actor        text not null,      -- user id, service name, or 'system'
  reason       text,
  idempotency_key text unique,
  occurred_at  timestamptz not null default now()
);

create or replace function enforce_order_transition()
returns trigger as $$
begin
  if not exists (
    select 1 from status_transitions
    where from_status = old.status and to_status = new.status
  ) then
    raise exception 'Illegal order transition: % -> %', old.status, new.status;
  end if;
  return new;
end;
$$ language plpgsql;

create trigger orders_guard_transition
before update of status on orders
for each row
when (old.status is distinct from new.status)
execute function enforce_order_transition();

The trigger only enforces the shape of the graph — it can't know a refund is past its return window on its own, since that depends on order data beyond the status field. Encode data-dependent guards (return windows, minimum order age, role checks) either in the trigger function alongside the graph check, or in application code that runs before the write — just make sure one of the two actually runs every time, not "usually."

Logging Every State Change So You Can Prove What Happened

The orders.status column is a projection — it only shows the current state — while order_status_events is the source of truth for how the order got there. If the two ever disagree, trust the log and rebuild the projection from it, not the other way around. Every event should capture the actor, the reason, a timestamp, and an idempotency key so a retried webhook can't create a duplicate transition.

This split mirrors what Martin Kleppmann describes in Designing Data-Intensive Applications: treating an event log as the durable system of record and current state as a materialized, disposable view derived from replaying it. If you can regenerate status by replaying order_status_events in order and get the same answer, your log is doing its job — not just decorating an audit screen nobody opens.

At minimum, capture these fields on every transition, not just the old and new status:

1. actor — which user, service, or scheduled job made the change.
2. reason — free text or a coded reason, especially for manual refunds and cancellations.
3. idempotency_key — so a webhook retry is provably a no-op, not a second transition.
4. correlation_id — links the transition back to the support ticket, webhook delivery, or batch run that caused it.
5. occurred_at — when the underlying event happened, which can differ from when it was recorded.

Don't delete a rejected transition attempt just because your trigger blocked it — keep a record of the attempt somewhere, even if it's a separate rejected_transitions table rather than the main event log. That's the same instinct behind soft delete vs. hard delete when modeling data: what looks like noise today is exactly what a dispute investigation needs eighteen months from now, and you can't reconstruct a deleted attempt after the fact.

When Status Has Feedback Loops, Not Just a Forward Path

Some order lifecycles aren't just a graph you traverse once and forget. A disputed order resolving back to paid, a refunded item that gets restocked and shifts inventory-driven pricing, or a rising chargeback rate that tightens future fraud thresholds are all feedback loops, where an output state quietly becomes a future input. Modeling only the forward transitions misses the part of the system that actually causes recurring pain.

It's worth remembering why any of this matters to a customer at all. Nobody wants a refunded status for its own sake — they're hiring your checkout-to-delivery flow to complete a job, the frame laid out in our jobs-to-be-done complete guide. A transition that silently stalls, like an order stuck in processing for a week, is exactly where that job breaks down for the person waiting on it.

Overlaying the state graph on the buyer's actual experience is worth doing too: the anxiety at pending, the relief at delivered, the anger at a mishandled disputed. That's what our customer journey complete guide walks through, and the two diagrams are genuinely worth drawing side by side.

Modeling This in Prodinja

Key Takeaways

• A status column without a transition model is unconstrained by default — anything can move to anything unless you explicitly forbid it in schema or code.
• Draw the legal-transition graph before you write schema — states, triggering events, and guard conditions, not just a list of enum values.
• Enforce in both layers when money or inventory is involved — application checks for clean errors and role logic, a database transitions table or trigger as the backstop nothing can bypass.
• Treat status as a projection, not the source of truth — an append-only event log is what proves what actually happened and lets you rebuild state if it ever drifts.
• Log the actor, reason, and idempotency key on every transition, not just old and new status, or you'll have history you can't actually use in a dispute.
• Some lifecycles have feedback loops, not just forward paths — a dispute resolving back to paid is a legal transition your diagram should show, not an exception you patch around later.