DevEx payouts: the flow, chapter by chapter

WHAT EACH ONE IS

• devex_requests — one row per cashout: the anchor. Its status column is the state machine every chapter moves.
• Two states outside the worker loop: REJECTED (born terminal at submit when the guard says no) and CANCELLED (the developer cancels while still PENDING; refunds the escrow, guarded so it cannot race a claim).
• accounts — one row per money holder (dev_777, DEVEX_ESCROW, ROBUX_BURN). Current balances only, no history.
• ledger_legs — the append-only receipts: one row per movement half, pairs sum to zero. The money truth; balances can be rebuilt from it.
• payout_attempts — one immutable row per processor call: the audit trail of trying.
• rates — immutable rate versions. A change is a NEW row, so a locked rate stays true forever.

WHO WRITES WHAT, CHAPTER BY CHAPTER

• Chapter 1 (submit): inserts the devex_requests row, moves accounts, writes two ledger_legs. Reads rates.
• Chapter 2 (claim): updates only devex_requests — status, lease, attempt counter.
• Chapter 3 (call): inserts one payout_attempts row per try. Nothing else is written.
• Chapter 4 (settle): the only writer that touches all four — status flip, balances, legs, attempt close, one transaction.
• Chapters 5-6 only read, with ONE exception: reconciliation writes the settlement a processor report proves we missed.
• Rollups, the WORM archive, and the DLQ are derived from these tables, never sources of truth.

WHAT HAPPENS

1. Developer POSTs with an idempotency key. Static validation runs first: malformed body or below the minimum cashout is a 422 before anything external happens.
2. Then the API checks (developer, key): seen before means replay the answer stored the first time (mechanics in Appendix B).
3. Same key with a DIFFERENT body is rejected (409): a key names one exact request, forever.
4. An external eligibility service (KYC, tax forms, fraud state) is asked first, before the transaction below begins. Its full answer is saved (snapshotted).
5. One transaction begins: insert the request row (PENDING).
6. Read the newest row of the rates table. A rate change is a NEW row, never an edit, so this row can be referenced forever. Compute the USD. Store both on the request.
7. Move the Robux: subtract from the developer, add to the escrow holding account. The subtracting SQL itself says "only if enough balance", so the check cannot be raced.
8. Record the move as two bookkeeping rows, called ledger legs: minus on the developer (they are handing over Robux to get dollars), plus on escrow (where it waits). They sum to zero.
9. COMMIT: everything above becomes real at once. The success response already shows the locked rate and the exact dollar amount in cents, and neither can ever change.

WHAT MAKES IT ROBUST

• The fused guard (our name; the standard term is a conditional UPDATE): balance check and debit are one atomic statement. Two racing submissions cannot both pass; the second re-checks the debited balance and matches zero rows.
• The anchor (our nickname for the idempotency record: the request row every retry lands on): a rejection commits it as REJECTED instead of rolling back, so a retry always finds one recorded outcome.
• Frozen inputs: rate and eligibility are stored on the request. Later rule or rate changes cannot touch this request.
• All or nothing: escrow move, ledger legs, rate, snapshot, status share one COMMIT. No partial submission can exist.

IF WE CRASH RIGHT HERE

• Before COMMIT: nothing exists anywhere. The developer retries cleanly.
• After COMMIT: the Robux sits visibly in escrow and the PENDING row survives. Chapter 2 will find it.

WHAT HAPPENS

1. Workers sweep for PENDING rows.
2. A claim locks the row (FOR UPDATE) while other workers skip locked rows instead of waiting (SKIP LOCKED). One row, one worker, no waiting, no coordinator.
3. The claim also writes a lease ("mine until 12:15" - a timer that outlives a dead worker) and an attempt number (try #1, #2...), then sets PROCESSING.
4. PROCESSING means "a worker owns this row". It has nothing to do with the external payment processor, which is never touched until chapter 3.
5. A reaper job (a cleanup sweep) flips rows with expired leases back to PENDING.

WHAT MAKES IT ROBUST

• SKIP LOCKED: two workers can never hold the same request. Not by convention, by the database.
• The lease: a claim is a loan, not ownership. A worker that dies silently just lets its lease expire.
• Attempt numbers: every claim is numbered, so the history shows exactly who tried when.
• Right-sized: at DevEx scale (assume thousands of cashouts/day) one worker suffices. This machinery exists for crash-safety, not throughput.

IF WE CRASH RIGHT HERE

• Worker dies mid-claim: the lease expires, the reaper frees the row, the next worker starts attempt n+1.
• Nothing was sent to the payment processor (the external service that pays out real dollars; chapter 3) yet, so nothing can have been paid.

WHAT HAPPENS

WHAT MAKES IT ROBUST

• One key per request, not per attempt: any retry, from any worker, after any crash, collapses to at most one real disbursement. Per-attempt keys are the classic double-payout bug.
• Definitive-answer rule: a timeout is not an outcome. The row stays PROCESSING until the processor says something final.
• Attempt row first, call second: a crash between them leaves an honest SENDING row and no side effect. Nothing external happened.
• The reverse order is a bug: call first, record second means a crash can move money with no local record of the attempt. Only reconciliation would ever find it.
• The retry ladder (standard name: a backoff schedule): 15m, 1h, 6h, 1d, 2d. The product promises resolution inside 5 business days; the whole ladder plus a human-review window fits in that budget.
• Circuit breaker (stop calling a service that keeps failing): workers pause claiming, so unclaimed rows wait as PENDING.
• Rows already claimed just lease-expire back to PENDING. On recovery, workers drain the backlog oldest-first; no attempts were burned during the outage.

IF WE CRASH RIGHT HERE

• Crashed after writing SENDING, before calling: nothing external happened. Lease expires; the next attempt adds its own row and calls.
• Crashed after calling, before recording: the SENDING row makes the gap visible (a row with no outcome and an expired lease). The request row is still PROCESSING.
• Next attempt presents the same key. The processor replays its answer. We record it late. Nobody is paid twice.
• Equivalent move: ask the processor's read-only status endpoint for that requestId. Same information, no new payment risk.

WHAT HAPPENS

1. Chapter 3 ends with a definitive outcome in the worker's hand. The same worker, still holding its lease, turns it into money movements.
2. One transaction does four jobs:
   • flip the status column of the devex_requests row: PROCESSING becomes COMPLETED, FAILED, PARTIAL — or ON_HOLD, the ambiguous case below
   • move the account balances: DEVEX_ESCROW down; the burn account or the developer up
   • write new ledger_legs rows recording that move
   • close the payout_attempts row with the outcome and the processor's reference
3. The status flip is a CAS (compare-and-swap): UPDATE devex_requests SET status='COMPLETED' WHERE id=42 AND status='PROCESSING'.
4. 0 rows updated means someone settled first: stop, touch no money.
5. Where the Robux goes, by outcome:
   • COMPLETED: burn all of it (move it to the burn account, out of the economy forever)
   • FAILED: refund all of it to the developer
   • PARTIAL (paid X of Y): burn robux·X/Y rounded down, refund the remainder
6. An ambiguous answer (their report says PARTIAL but X is missing, negative, or bigger than Y) parks in the DLQ (dead-letter queue): a holding table for work the system refuses to guess about.
   • the CAS flips the request to ON_HOLD, which takes it out of the claim-and-reaper loop
   • alerts fire, escrow stays put, a human decides before any leg is written

WHAT MAKES IT ROBUST

• Status and money are one commit: no code path can mark a request finished without settling its escrow, or settle without marking.
• CAS on the status: a stale worker or a duplicate settle (the same outcome delivered twice) finds the row already flipped, updates 0 rows, does nothing.
• The escrow invariant: ESCROW balance = SUM of in-flight Robux, checkable at any moment. Every burn matches a settlement line.
• The one-liner: money left the platform if and only if Robux left the economy.

IF WE CRASH RIGHT HERE

• Crash between outcome and settlement: the transaction never committed, the row is still PROCESSING, chapter 3 repeats safely.

WHAT HAPPENS

1. The database publishes every committed change (CDC = change data capture, tailing the commit log).
2. One consumer maintains the earnings rollups (pre-added totals per developer) that dashboards read.
3. Rollups run seconds stale and carry an asOf timestamp, so stale is visible, never silent.
4. Another streams every change to WORM archive storage (write once, read many): once written, the storage layer refuses edits and deletes, even from admins, until retention expires.

WHAT MAKES IT ROBUST

• Derived, never authoritative: lose the rollups or the archive copy, rebuild from the ledger.
• The money write path never waits: submissions and payouts do not touch this pipeline; it only reads commits.
• Compliance by stream: the WORM copy is the immutable record regulators ask for, without rebuilding the whole system around an event log (event sourcing).

IF WE CRASH RIGHT HERE

• The pipeline lags or dies: dashboards go stale and say so via asOf. Money paths are untouched.

WHAT HAPPENS

1. Daily, pull the processor settlement report.
2. Diff it against our COMPLETED and PARTIAL rows, in both directions.
3. Their line, no row of ours (money moved that we never recorded): write the missing settlement from their report, then page the on-call at SEV-2 to find how it got lost.
4. Our COMPLETED, no line of theirs (we believe we paid; their report shows nothing): page the on-call at SEV-1 (drop-everything severity). Until explained, that is missing money.
5. Continuously, from the ledger stream: re-check the escrow invariant, and that every balance stays at or above zero.

WHAT MAKES IT ROBUST

• External truth check: everything upstream is our own bookkeeping; this is the one chapter that checks it against the outside world: the processor's report.
• Asymmetric severity: money we cannot account for (SEV-1) is worse than money we have not recorded yet (SEV-2). The paging level encodes that judgment.
• Backstop, not front line: chapters 1-4 prevent; this chapter detects whatever survived anyway.

IF WE CRASH RIGHT HERE

• Reconciliation itself fails: nothing corrupts. It is a reader. Rerun it; alert on it being late.

IDIOM 1 · FOR UPDATE, SORTED ORDER — a two-account transfer (teaching example, not part of the payout system)

• Setup: alice (id 1, balance 100) sends bob (id 2) 30. Bob sends alice 10. Same instant.
• Rule: every transaction locks the LOWER id first, no matter who is paying whom.

BEGIN;
SELECT status FROM accounts WHERE id = 1 FOR UPDATE;   -- min(1,2) first, always. the column picked is irrelevant: this SELECT exists only to take the row lock
SELECT status FROM accounts WHERE id = 2 FOR UPDATE;
UPDATE accounts SET balance = balance - 30 WHERE id = 1 AND balance - 30 >= 0;
-- app checks the debit touched 1 row; 0 rows means ROLLBACK and stop (or the credit below mints 30)
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
COMMIT;

• 12:00:00.000 Session 1 locks row 1.
• 12:00:00.001 Session 2 also starts at row 1. Sees the lock. Waits.
• 12:00:00.002 Session 1 locks row 2, updates both, COMMITs.
• 12:00:00.005 Session 2 unblocks and runs its transfer. Nobody deadlocks; the slower one just queues.

THE SAME RACE WITHOUT SORTED ORDER

• 12:00:00.000 S1 (alice→bob) locks its sender: row 1. S2 (bob→alice) locks its sender: row 2.
• 12:00:00.001 S1 wants row 2, waits on S2. S2 wants row 1, waits on S1.
• Each holds what the other needs. Deadlock. Postgres kills one after detection: "deadlock detected".
• Same statements, same locks. The ORDER is the entire fix (standard name: lock ordering).

IDIOM 2 · FOR UPDATE SKIP LOCKED — the payout claim

• Setup: rows 41, 42, 43 are PENDING. Workers A and B run the identical claim at the same instant.

async def claim_next(pool):
    async with pool.connection() as conn:      # borrowed from a connection pool, not opened fresh
        async with conn.transaction():         # framework does BEGIN / COMMIT / ROLLBACK

            # 1. pick ONE free row and lock it: skip busy rows, never wait
            row = await conn.fetchrow(
                "SELECT id, dev, robux FROM devex_requests "
                "WHERE status = 'PENDING' AND next_attempt_at <= now() "  # the retry-ladder wait, enforced
                "ORDER BY created_at "                      # created_at: stamped by column default in chapter 1
                "LIMIT 1 FOR UPDATE SKIP LOCKED")
            if row is None:                    # every PENDING row is locked or gone
                return None                    # caller sleeps a beat, polls again

            # 2. claim the row we are holding: still locked, nobody can race us
            await conn.execute(
                "UPDATE devex_requests "
                "SET status = 'PROCESSING', "
                "    lease_until = now() + interval '15 minutes', "
                "    attempt = attempt + 1 "
                "WHERE id = $1", row.id)      # $1 = the id the SELECT above locked (say 42)

    # leaving the block ran COMMIT: lock released, claim durable
    return row                                 # caller sends row 42 to the processor

• 12:00:00.000 A reaches row 41 first, locks it. A gets 41.
• 12:00:00.000 B reaches row 41, sees the lock, does NOT wait: skips to 42, locks it. B gets 42.
• 12:00:00.002 Both COMMIT their claims. Row 43 waits for the next sweep.
• 12:07:00 Worker A crashes. Nothing happens yet.
• 12:15:00 A's lease expires. The reaper flips row 41 back to PENDING. Next claim is attempt 2.

THE SAME CLAIM WITHOUT SKIP LOCKED

• B queues behind A for row 41 — a row it can never have — instead of taking 42.
• Every worker serializes behind the first one. The pool becomes single-file.

ONE PRIMITIVE, TWO IDIOMS

• Money transfers: lock everything you touch, in one global order. Wait politely.
• Work queues: lock one free thing, skip the busy ones. Never wait.
• When is SELECT FOR UPDATE needed at all? Only when there is a gap between reading and writing that the lock must cover.
• A plain UPDATE takes the SAME row lock by itself and holds it to COMMIT. That is why the account updates in Appendices B and C need no SELECT: read, guard, and write are one statement, no gap.
• Idiom 2's gap: the worker must read WHICH row to take before it can write to it. The SELECT is the picking; FOR UPDATE makes the pick a claim.
• Lock ORDER matters either way: consecutive UPDATEs also take locks one at a time and hold them, so opposite orders can still deadlock.
• Name both idioms in an interview and row locking is covered as a topic.

THE HANDLER, PRODUCTION SHAPE (Python-ish)

async def submit_devex(dev_id, idem_key, robux):
    # 0. replay check FIRST: a retry must get the stored answer even if
    #    eligibility has changed since the original request went through
    prior = await db.fetchrow("SELECT body_hash, http_status, stored_response "
                             "FROM devex_requests WHERE dev=$1 AND idem_key=$2", dev_id, idem_key)
    if prior:
        if prior.body_hash != hash(robux):
            return 409, {"error": "KEY_REUSED"}
        return prior.http_status, prior.stored_response

    # 1. external call OUTSIDE the transaction: own timeout, own circuit breaker
    elig = await eligibility.check(dev_id, timeout=2.0)
    if not elig.ok:
        return 422, {"error": "NOT_ELIGIBLE", "reasons": elig.reasons}

    async with pool.connection() as conn:      # borrowed from a pool, not opened fresh
        async with conn.transaction():         # framework does BEGIN / COMMIT / ROLLBACK

            # 2. the anchor, race-safe: insert-or-detect in one statement
            req = await conn.fetchrow(
                "INSERT INTO devex_requests (dev, idem_key, robux, body_hash, status) "
                "VALUES ($1,$2,$3,$4,'PENDING') "
                "ON CONFLICT (dev, idem_key) DO NOTHING RETURNING id",
                dev_id, idem_key, robux, hash(robux))
            if req is None:                    # two racing FIRSTs on one key: the loser lands here
                prior = await conn.fetchrow("SELECT * FROM devex_requests "
                                            "WHERE dev=$1 AND idem_key=$2", dev_id, idem_key)
                if prior.body_hash != hash(robux):
                    return 409, {"error": "KEY_REUSED"}
                return prior.http_status, prior.stored_response    # the replay

            # 3. rate lock + USD, still inside the same transaction
            rate = await conn.fetchrow("SELECT * FROM rates WHERE valid_from <= now() "
                                       "ORDER BY valid_from DESC LIMIT 1")
            usd_minor = floor(robux * rate.minor_per_robux)  # cents per Robux; floor: exact cents, never overpay

            # 4. the fused guard: check + debit, one statement
            n = await conn.execute(
                "UPDATE accounts SET balance = balance - $2 "
                "WHERE id = $1 AND status='ACTIVE' AND balance - $2 >= 0", dev_id, robux)
            if n == 0:
                # zero rows means frozen account OR insufficient balance: re-read the
                # account once to say WHICH, so the 422 carries the right error code
                # business rejection: RETURN, do not RAISE - raising would roll back
                # the anchor, and the anchor must survive for retries
                resp = {"error": "INSUFFICIENT_BALANCE"}
                await conn.execute("UPDATE devex_requests SET status='REJECTED', "
                                   "http_status=422, stored_response=$2 WHERE id=$1", req.id, resp)
                return 422, resp   # the retry replays exactly this, via step 0

            await conn.execute("UPDATE accounts SET balance = balance + $2 "
                               "WHERE id = 'DEVEX_ESCROW'", robux)   # the credit: no guard needed
            await conn.execute("INSERT INTO ledger_legs ... ")     # the receipts: -dev, +ESCROW
            resp_body = {"requestId": req.id, "rateId": rate.id, "usdMinor": usd_minor}
            await conn.execute("UPDATE devex_requests SET rate_id=$1, usd_minor=$2, "
                               "elig_snapshot=$3, http_status=201, stored_response=$5 "
                               "WHERE id=$4", rate.id, usd_minor, elig.json, req.id, resp_body)
            # http_status + stored_response: what a future retry replays
    # leaving the block runs COMMIT (clean exit) or ROLLBACK (exception).
    # by this line the commit has already happened.
    return 201, resp_body   # the exact object a retry will replay

THE REAL-WORLD DETAILS THE WHITEBOARD HIDES

WHAT BREAKS IN REAL DEPLOYMENTS, AND WHERE THIS DESIGN CATCHES IT

• Deploy rolls mid-request: transaction never committed, nothing exists, client retries cleanly.
• Eligibility service is slow: only the pre-transaction call waits; no database locks are held during it.
• Two app servers race the same key: ON CONFLICT makes one the writer; the other reads and replays.
• Clock skew between servers: irrelevant here - rate selection uses the database's now(), one clock.

ONE EXAMPLE, CARRIED THROUGH EVERY CASE

• Request 42: developer dev_777 cashed out 1000 Robux at the locked rate, worth 350 usdMinor ($3.50).
• Chapter 1 already moved the Robux into escrow. Chapter 4 decides where it goes from there.

THE POSITION SETTLEMENT INHERITS (after chapters 1-3)

devex_requests
  id=42  dev=dev_777  robux=1000  usd_minor=350  status='PROCESSING'

accounts
  dev_777        4000    # had 5000; chapter 1 debited 1000
  DEVEX_ESCROW   1000    # the in-flight 1000 waits here
  ROBUX_BURN        0    # the exit door of the economy

ledger_legs (request 42 so far — chapter 1 wrote this pair)
  leg=901  account=dev_777       amount=-1000  reason='SUBMIT'
  leg=902  account=DEVEX_ESCROW  amount=+1000  reason='SUBMIT'

payout_attempts
  id=7  request=42  attempt=1  processor_key='42'  state='SENDING'
  # the key IS the requestId (chapter 3's rule). still SENDING: the definitive answer
  # is in the worker's hand; chapter 4's transaction writes it while settling

CASE: PROCESSOR SAYS FAILED — full refund

• Meaning: no dollars left the platform, so all 1000 Robux must go back.
• One transaction, four jobs (six statements):

async def settle_failed():                      # same frame as Appendix B: the with block IS the transaction
    async with pool.connection() as conn:
        async with conn.transaction():         # framework does BEGIN / COMMIT / ROLLBACK

            # 1. the CAS: claim the right to settle, or learn someone already did
            n = await conn.execute(
                "UPDATE devex_requests SET status='FAILED' "
                "WHERE id=42 AND status='PROCESSING'")
            if n == 0:                        # someone settled first
                return                        # stop, touch no money

            # 2. move the Robux back (no overdraft guard needed: escrow always covers, by invariant).
            #    developer row first, then escrow: the SAME lock order as chapter 1's submit,
            #    per idiom 1 — opposite orders between submit and refund could deadlock
            await conn.execute("UPDATE accounts SET balance = balance + 1000 WHERE id='dev_777'")
            await conn.execute("UPDATE accounts SET balance = balance - 1000 WHERE id='DEVEX_ESCROW'")

            # 3. the receipts: two NEW legs. chapter 1's legs are never edited or deleted.
            await conn.execute("INSERT INTO ledger_legs VALUES (903, 'DEVEX_ESCROW', -1000, 'REFUND', 42)")
            await conn.execute("INSERT INTO ledger_legs VALUES (904, 'dev_777',      +1000, 'REFUND', 42)")

            # 4. close the attempt with the processor's reason
            await conn.execute("UPDATE payout_attempts SET state='FAILED', error='account_closed' WHERE id=7")

    # leaving the block ran COMMIT: all six statements land, or none do

after COMMIT:
  dev_777        5000    # whole again, as if the request never happened
  DEVEX_ESCROW      0    # this request's slice is exactly zero
  legs for request 42: -1000 +1000 -1000 +1000  # sum = 0, story preserved

CASE: PROCESSOR SAYS PARTIAL — paid 210 of 350 usdMinor

• Meaning: 60% of the dollars went out, so 60% of the Robux must leave the economy.
• Burned share: floor(1000 · 210/350) = 600. Refund the remaining 400.
• Floor rounding sends the fractional Robux back to the developer, never into the burn.
• Same shape: CAS to 'PARTIAL', then TWO pairs of legs instead of one:

# alternative outcome for the SAME request 42 (instead of the FAILED case, not after it),
# so leg numbering starts at 903 again
async def settle_partial():                     # paid 210 of 350: burn 600, refund 400
    async with pool.connection() as conn:
        async with conn.transaction():

            # 1. the CAS: claim the right to settle, or learn someone already did
            n = await conn.execute(
                "UPDATE devex_requests SET status='PARTIAL' "
                "WHERE id=42 AND status='PROCESSING'")
            if n == 0:
                return                        # someone settled first: stop, touch no money

            # 2. balances — developer, then escrow, then burn. "global" = EVERY transaction in the
            #    codebase uses this same sequence (skipping rows is fine, reordering is not),
            #    so no cycle of waiters can ever form — idiom 1's rule, with names
            await conn.execute("UPDATE accounts SET balance = balance + 400  WHERE id='dev_777'")
            await conn.execute("UPDATE accounts SET balance = balance - 1000 WHERE id='DEVEX_ESCROW'")
            await conn.execute("UPDATE accounts SET balance = balance + 600  WHERE id='ROBUX_BURN'")

            # 3. the receipts: TWO pairs this time, each summing to zero
            await conn.execute("INSERT INTO ledger_legs VALUES (903, 'DEVEX_ESCROW', -600, 'BURN',   42)")
            await conn.execute("INSERT INTO ledger_legs VALUES (904, 'ROBUX_BURN',   +600, 'BURN',   42)")
            await conn.execute("INSERT INTO ledger_legs VALUES (905, 'DEVEX_ESCROW', -400, 'REFUND', 42)")
            await conn.execute("INSERT INTO ledger_legs VALUES (906, 'dev_777',      +400, 'REFUND', 42)")

            # 4. close the attempt with what was actually paid
            await conn.execute("UPDATE payout_attempts SET state='PARTIAL', paid_minor=210 WHERE id=7")

    # leaving the block ran COMMIT: all nine statements land, or none do

after COMMIT:
  dev_777        4400    # got 400 back
  DEVEX_ESCROW      0    # emptied for this request, again
  ROBUX_BURN      600    # 600 Robux left the economy, matching the $2.10 that left the platform

CASE: PROCESSOR SAYS COMPLETED — for symmetry

• CAS to 'COMPLETED', one pair of legs: DEVEX_ESCROW -1000, ROBUX_BURN +1000.
• The one-liner: dollars left the platform, so exactly that many Robux left the economy.

RULES THAT HOLD IN EVERY CASE

• Append-only ledger: a refund is two new rows, never an edit of chapter 1's rows. The history stays readable.
• Escrow drains to exactly zero for the request, whichever case fires. That is the per-request escrow invariant.
• Legs come in pairs summing to zero, and the request's whole set sums to zero: nothing minted, nothing lost.
• Lose the CAS, write nothing: status flip, account updates, legs, and the attempt close share one transaction.
• Ambiguous writes NO legs: the CAS parks the request as ON_HOLD and the 1000 sits in escrow until a human picks one of the three cases above.