production · 25 minute study

Reconcile claims against evidence

A status label can say paid while the underlying evidence disagrees. Engineering needs an independent way to detect impossible or unexplained state.

Loading progressEvidence grows with practice

Useful preparation · 2 prerequisites

Use this as a map, not a lock. If these ideas are familiar, continue.

Keep missing relationships visible — Reports often become wrong by silently dropping the very customers with no activity. A join's row-preservation rule is part of the business question.
Make a change an all-or-nothing unit — An order without its lines is not half a successful feature. It is a broken state that later screens, reports and retries must somehow interpret.

First, build a mental model

A stored claim is a hypothesis about other records. Reconciliation independently compares that claim to authoritative evidence; an audit event explains who changed what and why.

State a precise invariant before writing a checker. In this synthetic shop only, a paid order's total must equal its recorded payments.
Aggregate child rows before comparing with the parent. A naive join to multiple child tables can multiply rows and fabricate totals.
An audit record needs an actor, scoped resource, action, outcome and correlation/request identity. Recording sensitive payloads indiscriminately is not good auditing.
Read-only reconciliation detects a mismatch; it does not authorize an automatic repair. An append-only appearance in the UI is not an append-only database privilege.

Read the codesql · educational example

SELECT o.id, o.total_cents,
       COALESCE(SUM(p.amount_cents), 0) AS recorded_cents
FROM orders AS o
LEFT JOIN payments AS p ON p.order_id = o.id
WHERE o.status = 'paid'
GROUP BY o.id, o.total_cents
HAVING COALESCE(SUM(p.amount_cents), 0) <> o.total_cents;

Expected state
The seed paid order is 5000 cents with one 5000-cent payment, so the mismatch query returns no rows.
Injected defect
A second 100-cent payment makes the recorded total 5100 while the order still claims 5000.
Investigation
The query exposes a mismatch. It does not decide whether the order, payment or status should be corrected.

Production thinking

Take this beyond the example.

Use synthetic invariants for learning. Production financial, inventory and statutory rules require their own authoritative specifications, exact representation, authorization and independent acceptance evidence.

Sources and further reading

Your evidence

unseen is a local learning signal, not a credential or proof of production competence.

Explain the mental model, then explicitly acknowledge it. Opening a page is not evidence.

Supported retries can complete this lesson. Independent evidence and delayed review determine the skill stage.