LotZero: Zero Oversells and Zero Double-Spends on a Global Live Auction, Proven on Aurora DSQL

Real-time global commerce used to force a brutal tradeoff. Put your money ledger in a single Region and it is correct but slow for everyone far away. Spread it across Regions with eventually-consistent NoSQL and it is fast but unsafe: two Regions can both sell the last unit, and someone gets a refund email. I wanted to build the thing that is supposed to be impossible on a weekend stack: a worldwide live auction where the last lot can only ever be won once.

The insight: Aurora DSQL collapses the old tradeoff

Aurora DSQL gives active-active, multi-Region strong consistency with low-latency local writes. So the part that must be exactly right (the bid ledger, wallet holds, settlement) becomes an ordinary strongly-consistent CRUD app, and I pair it with DynamoDB for the part that just needs to be fast and huge. That consistency boundary is the architecture.

• Money + scarcity (Aurora DSQL): wallets, lots, bids, ledger, settlement. Must be exactly right under global contention. OCC + strong consistency means no oversell, no double-spend, no lost writes.
• Social firehose (DynamoDB): chat, presence, reactions, leaderboards, activity feed. Append-mostly, millions of writes, single-digit-ms; eventually consistent is fine. Single-table design.

The invariants (and how DSQL enforces them)

All money operations run inside a strongly-consistent DSQL transaction using optimistic concurrency control. Conflicting transactions fail with an OCC error and are automatically retried, re-checking fresh state. Three invariants must hold at all times:

• A lot's high bid only ever increases; there is exactly one high bidder.
• qty_claimed never exceeds qty_total. Zero oversells, globally.
• A wallet's held + spent never exceeds its funded balance. Zero double-spends, even when the same user acts from two Regions in the same millisecond.

DSQL-correct data modeling

DSQL is PostgreSQL-compatible but deliberately different. The schema respects all of it from day one:

• No foreign keys (DSQL doesn't support them). Integrity is enforced inside the transactions instead.
• No sequences or SERIAL. IDs are application-generated.
• No JSON or JSONB columns. DSQL doesn't support JSON types.
• One DDL statement per transaction. The client applies each CREATE TABLE separately.
• SELECT ... FOR UPDATE is used as DSQL intends it: not a lock, but a way to enroll the read rows into the OCC conflict-check set, so a racing writer loses cleanly at commit.
• Async indexes are applied out of band so DDL stays single-statement.

Auth to DSQL uses short-lived IAM tokens minted by the official aurora-dsql-node-postgres connector via Vercel OIDC, so there is no static database password sitting in env vars or in the repo.

The proof: measured correctness under load

The /proof page and the loadtest script fire a deliberate global race: many buyers tagged across five AWS Regions colliding on a scarce lot at the same instant, and verify the invariant holds exactly. Two scenarios:

• Oversell: N buyers worldwide rush a drop with only K units. Exactly K win, the rest are cleanly rejected, and the seller is credited exactly K times the price.
• Double-spend: one buyer funded for a single purchase tries to win N lots at once from many Regions. Exactly one wins, and their balance never goes negative.

Sample run: 200 concurrent claims on a scarce lot complete in under 900 ms with zero oversells and zero double-spends; 150 concurrent attempts on the double-spend scenario settle in about 690 ms with exactly one winner. The script exits non-zero if any invariant is ever violated, so it doubles as a CI gate.

Three auction mechanics, one invariant set

• English ascending: classic high-bid auction. Funds are held on the high bidder and released the instant they are outbid. Settlement converts the hold into payment.
• Dutch falling-price: the price ticks down on a schedule; the first claim anywhere on Earth wins. The hardest possible contention test, and the most dramatic demo.
• Fixed drop: a hard-capped, fixed-price global drop (e.g. 50 units). Proves no overselling at multi-unit scale.

What was hard

Reframing FOR UPDATE was the first reset. On a normal Postgres box that's a row lock. On DSQL it's an OCC enrollment: it does not block, it just promises the transaction will fail at commit if any of those rows has been changed by someone else. Once that mental model is right, the rest of the schema design follows: model intent, not order. Don't try to serialize writers; design for the optimistic case and rely on the retry loop to converge.

The second was the loss of foreign keys and JSON types. Without FKs, every transaction has to validate its own referential integrity inline. Without JSON, anything dynamic gets shaped into proper tables. Both forced the schema to be more honest about what it actually was, which is good design pressure, even outside DSQL.

The third was getting the proof to be honest. It's easy to write a load test that always passes because it never actually contends. The Proof console deliberately funds one wallet for one purchase, then tells N concurrent workers to all try to buy. If a wallet ever lands negative, the run fails the suite. That single rule (balance never negative) is what makes the page worth showing.

Local parity, real cluster behind the same SQL

With no environment variables set, LotZero runs entirely locally. The SQL ledger uses PGlite (embedded Postgres) running the identical SQL it runs on Aurora DSQL, auto-seeded with demo lots and funded wallets. The firehose uses an in-process store. Switch identity and acting Region from the header, open a lot, and bid. Open the same lot in two tabs as two users in two Regions to feel the contention.

Set DSQL_CLUSTER_ENDPOINT to move the ledger onto Aurora DSQL, and DYNAMODB_TABLE to move the firehose onto DynamoDB. Nothing else in the app changes. Same code, same SQL. That property is what made the build feel calm: every commit ran against both, the e2e suite passed identically, and there was no separate 'production code path' to worry about.

Impact

Live commerce is the right place for this. US livestream shopping reached roughly $50B in GMV in 2025 and is projected to pass 5% of US digital commerce in 2026; the global live-commerce market was about $172B in 2025, growing at a ~41% CAGR. Cart/checkout abandonment averages ~70%, which Baymard estimates at ~$260B/yr of recoverable revenue in the US alone. Overselling a limited drop turns a completed sale into a cancellation and a refund: the exact failure LotZero makes structurally impossible. And global latency is a measurable conversion tax (Amazon: every 100ms costs ~1% of sales; Google and Deloitte's Milliseconds Make Millions: a 0.1s speedup lifted retail conversion ~8%). Single-Region SQL imposes that tax on every distant buyer; DSQL's active-active local writes remove it while staying strongly consistent.

Honest limitations, path to production

• Payments are sandboxed (demo wallets), not a real PSP. Production needs Stripe or Adyen, KYC, and tax.
• No auth provider yet; identity is a demo switcher in the header. Production needs real accounts and authorization.
• Anti-fraud, bid-sniping protection, and dispute handling are out of scope for the hackathon build.
• The firehose currently polls; production would use WebSockets or SSE and DynamoDB Streams.
• Aurora DSQL has documented SQL/feature limits; the schema respects the big ones (no FK, no sequences), but a production schema review against the current DSQL feature set is required.