The price melts as the crowd grows: building a provably-fair live group-buy on Aurora DSQL

Group-buying is an old idea: get enough people together and the price drops. But it has always been asynchronous — you commit, you wait, and you find out the deal later.

I wanted the opposite. A live market where a global crowd watches one price fall together in real time, and when the timer ends, everyone pays the same final price — even the people who joined first.

I called it Rally. And the moment I sketched it, the hard part jumped out at me, and it had nothing to do with the UI.

The trap hiding inside a simple idea

If two shoppers in two different regions ever see a different price for the same item at the same instant, the whole thing falls apart. It's unfair, and it's trivially exploitable.

That is not a front-end bug you can paper over with a nicer animation. It's a consistency guarantee — you either have it or you don't. A live, globally-fair, falling price is only honest on a database that is strongly consistent across regions.

So this stopped being "a CRUD app with a countdown" and became a project about one specific database capability. I built it on Amazon Aurora DSQL (the strongly-consistent market truth), with DynamoDB as the live read plane, on Next.js / Vercel.

Trick #1: the price is a read, not a write

The most important design decision: the live price is never stored.

It's derived. The price is tier(sum(count_shards)) — a pure function of a strongly-consistent count. Because it's computed, crossing a volume tier lowers the price for everyone at once. There is no "current price" row to update, and therefore no tier-transition race to lose.

The two queries that are the product:

-- LIVE PRICE: a strongly-consistent count → the tier price.
-- Every region computes the identical value.
WITH n AS (SELECT COALESCE(sum(count),0) c FROM count_shards WHERE rally_id = :r)
SELECT (SELECT c FROM n) AS count,
       (SELECT unit_price_minor FROM rally_tiers
          WHERE rally_id = :r AND min_count <= (SELECT c FROM n)
          ORDER BY min_count DESC LIMIT 1) AS price;

-- FAIRNESS PROOF (run live at settlement):
SELECT count(DISTINCT price_paid_minor) FROM settlements   WHERE rally_id = :r;  -- expect 1
SELECT COALESCE(sum(signed_minor),0)    FROM ledger_entries WHERE rally_id = :r;  -- expect 0

That second pair is the entire pitch, made checkable: everybody paid one price, and the double-entry ledger balances to zero.

Trick #2: shard the one hot row

A single count++ row is the classic optimistic-concurrency killer on a distributed SQL database. Every join contends on the same row, and you spend your life retrying serialization conflicts.

So I sharded the join counter across 128 rows. Each join bumps a random shard; the live count is their consistent sum. One join is one atomic, idempotent transaction:

// idempotent on (rally_id, user_id): a double-tap can never double-count
const ins = await c.query(
  `INSERT INTO participants (rally_id,user_id,idempotency_key)
     VALUES ($1,$2,$3) ON CONFLICT (rally_id,user_id) DO NOTHING`,
  [rallyId, userId, idemKey]);
if (ins.rowCount === 0) { await c.query('ROLLBACK'); return 'ALREADY_JOINED'; }

await c.query(
  `UPDATE count_shards SET count = count + 1
     WHERE rally_id=$1 AND shard_no=$2`,
  [rallyId, (Math.random() * shards) | 0]);   // random shard

I load-tested it against the real cluster in Tokyo. The result I'm proudest of:

== RECONCILE / FAIRNESS PROOF ==
  unique joiners   : 2000   (== shard sum? true)
  OCC retries      : 398    (detected and retried to success)
  distinctPrices   : 1      ($52.00)
  ledgerDrift      : 0
  doubleJoins      : 0
  doubleCharges    : 0
  melt: $100 → $90 → $82 → $74 → $68 → $62 → $58 → $55 → $53 → $52
  PROOF PASSED ✅

2,500 join attempts (including 500 duplicate taps) → exactly 2,000 unique joiners, 398 serialization conflicts detected and retried to success, zero double-joins, and the price melted from $100 to $52. On the live cluster, not in theory.

The DSQL differences I hit (so you don't)

Porting a Postgres-shaped engine to Aurora DSQL surfaced the real differences fast:

• BEGIN ISOLATION LEVEL SERIALIZABLE is rejected (0A000). That threw me until I realized DSQL is serializable-by-default with optimistic concurrency, so a plain BEGIN gives identical semantics. The retry loop on 40001 is exactly the pattern you want.
• No sequences. All IDs are app-generated UUIDs — which you want anyway for distribution.
• Unique constraints via PK-guard tables. Instead of unique secondary indexes, I enforce unique email/handle with a tiny table whose primary key is the email, and INSERT ... ON CONFLICT DO NOTHING to claim it atomically.
• Indexes are created ASYNC — CREATE INDEX ASYNC ....
• Auth is a short-lived IAM token, minted per connection with @aws-sdk/dsql-signer and used as the database password. No static password sitting in a config.

The bug serverless taught me

This one was sneaky. In local dev, an in-process EventEmitter fanned each committed join out to every connected SSE client. Worked perfectly.

On Vercel, it silently stopped working. Why? Each serverless invocation is isolated — the /join handler and the /stream handler don't share memory. The emitter was shouting into a room nobody was in.

The fix made the architecture honest: the shared DynamoDB live_frame item is the cross-instance source of truth, and the stream reads it. DynamoDB was the right read plane all along; serverless just forced me to use it properly. Every committed join projects a frame; every watcher's stream sees it — across instances, across regions.

JOIN → engine.join (commit to DSQL)
     → projectFrame (consistent count → DynamoDB live_frame)
     → SSE stream (reads the frame) → every browser melts in lockstep

Settling fairly — and 126× faster

When the timer ends, settlement reads the final count once, charges everyone that one price, and writes a balanced double-entry ledger — idempotent per user, so re-running it never double-charges.

My first version did per-row inserts and took ~456 seconds to settle 2,000 users across the Pacific. Unusable for a live "timer hits zero" moment. I rewrote it to set-based batched inserts that return exactly the newly-settled users (so the ledger is written once each):

INSERT INTO settlements (rally_id, user_id, price_paid_minor)
  SELECT $1, u, $2 FROM unnest($3::text[]) AS u
ON CONFLICT (rally_id, user_id) DO NOTHING
RETURNING user_id;   -- ledger rows written only for these

Same guarantees, 3.6 seconds. About 126× faster.

Two regions, one price (the part that needs DSQL)

Here's the payoff. The whole thing runs active-active across Tokyo and Seoul (witness in Osaka). Read the live count from either regional endpoint and it's identical, tick-for-tick:

write Tokyo   +75 joins   Tokyo 190 $85.00   Seoul 190 $85.00   MATCH ✅  (cross-region read 143ms)
write Seoul   +30 joins   Tokyo 220 $85.00   Seoul 220 $85.00   MATCH ✅

Kill one region mid-rally and joins keep flowing from the other, with no lost counts. One fair price, everywhere — which is the thing eventual consistency simply cannot promise.

You don't have to trust me

The best part: I built a public /proof page that reconciles a real settled rally live, straight from the database. Distinct prices paid: 1. Ledger drift: $0.00. Double-charges: 0. With the two SQL queries shown, so you can run them yourself.

That's the whole thesis, made checkable: a global crowd agreeing on one falling price in real time, and everyone winning the same deal — provable, not promised.

What I'd tell past me

Three things stuck:

1. The cleanest way to win a "why this database?" argument is to make the database do something the obvious tool can't — then make it checkable. "One falling price across regions, settled fairly" is only honest under strong consistency. The product and the database choice justify each other.
2. Respect the boundary between the OLTP write truth and the read plane. Every time I was tempted to fan out the live price with a DSQL aggregate scan, the right answer was the DynamoDB frame.
3. Serverless makes you externalize state you didn't think was state. The in-process emitter was the lesson.