How I built a flash-sale engine that can't oversell

I created this piece of content for the purpose of entering the H0: Hack the Zero Stack hackathon. #H0Hackathon

The problem (that's harder than it sounds)

"Don't sell more tickets than you have" sounds trivial until 10,000 people click Buy in the same second.

The naive fix — a single remaining counter you decrement on every purchase — falls apart under load. And when you try to go multi-region, it gets worse: with eventual consistency across regions you can oversell during the replication window.

I wanted a flash-sale engine that is globally fast AND strongly consistent AND operationally simple — all three. That combination is precisely what Amazon Aurora DSQL is built for, so I built DropZero on it for the hackathon.

The trap I almost walked into

Aurora DSQL is a serverless, PostgreSQL-compatible, multi-region active-active SQL database. Crucially, it uses optimistic concurrency control (OCC): transactions run without locks, and conflicts are detected at commit time — the loser gets a SQLSTATE 40001 serialization error and retries.

That detail flips the "obvious" design on its head. A single hot counter row that every buyer updates is the worst workload for OCC. Thousands of writes to one key collide at commit time, and you get a retry storm that eats your throughput. AWS's own documentation is explicit: spread writes across the key range.

The design that actually works

Instead of a counter, model each sellable unit as its own database row:

CREATE TABLE drop_units (

  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),

  drop_id    uuid NOT NULL,

  unit_no    integer NOT NULL,

  status     text NOT NULL DEFAULT 'available',  -- available | claimed

  order_id   uuid,

  claimed_at timestamptz

);

Each buyer claims a random distinct unit in one strongly-consistent transaction:

// lib/store-dsql.ts (simplified)

async function reserveUnit(dropId: string, orderId: string): Promise<string> {

  return withRetry(async () => {

    // Find a random available unit, claim it atomically

    const { rows } = await tx(async (client) => {

      const candidates = await client.query(

        `SELECT id FROM drop_units

          WHERE drop_id = $1 AND status = 'available'

          ORDER BY random() LIMIT 5`,

        [dropId]

      );

      if (candidates.rows.length === 0) throw new Error('SOLD_OUT');



      // Race for one — OCC means only one wins per row

      for (const { id } of candidates.rows) {

        const result = await client.query(

          `UPDATE drop_units

              SET status = 'claimed', order_id = $1, claimed_at = now()

            WHERE id = $2 AND status = 'available'

            RETURNING unit_no`,

          [orderId, id]

        );

        if (result.rowCount === 1) return result;

      }

      throw new Error('RETRY');

    });

    return rows[0].unit_no;

  });

}

withRetry only re-runs on SQLSTATE 40001 (OCC conflict), with jittered exponential backoff. Everything else surfaces immediately.

Two properties fall out of this design for free:

1. Overselling is impossible by construction. Each row transitions available → claimed at most once, enforced by the WHERE status = 'available' predicate in a strongly-consistent transaction. So claimed ≤ total always holds — regardless of region count or traffic spike.




2. Conflicts stay near zero. Because buyers target random rows, 5,000 simultaneous buyers touch ~5,000 different rows. DSQL is optimized for exactly this write pattern. The few genuine collisions (two buyers picking the same random row) are retried transparently.

An extra layer: idempotency

Double-clicks and network retries can cause a buyer to send the same request twice. I added a unique index on idempotency_key in the orders table:

CREATE UNIQUE INDEX orders_idempotency_key ON orders (idempotency_key);

The claim function checks this first — if the key already produced an order, return it. A double-click never double-buys.

Proving it: the stampede simulator

DropZero ships with a built-in concurrency simulator. Click Run stampede, choose a buyer count and a drop with limited inventory, and the app fires that many concurrent purchase requests.

Result for 1,000 buyers racing for 200 units:

confirmed:   200   (exactly the inventory)

rejected:    800   (clean SOLD_OUT, not errors)

oversold:      0

conflicts:     2   (OCC retries, resolved transparently)

p99 latency: 42ms

Every time. The "integrity proof" button runs a live SELECT COUNT(*) grouped by status to confirm claimed = 200 and available = 0 with no ghost rows.

Why Aurora DSQL specifically

Other databases could prevent overselling — but usually with trade-offs:

DSQL specifically solves the "global buyers, one inventory" problem without a waiting room, without a single write bottleneck, and without giving up strong consistency. It's the right tool for this workload.

The stack

The OIDC integration is worth calling out: Vercel assumes an AWS IAM role per deployment. No AWS_ACCESS_KEY_ID or AWS_SECRET_ACCESS_KEY anywhere in the project. Zero stored credentials.

One schema note: DSQL doesn't support foreign keys or synchronous indexes

DSQL is not vanilla PostgreSQL. I hit two constraints that shaped the schema:

• 
  No foreign keys. orders.drop_id and drop_units.drop_id are logical references enforced in application code, not DB constraints.


• 
  Indexes must be CREATE INDEX ASYNC. The db:init script auto-rewrites CREATE INDEX → CREATE INDEX ASYNC when it detects a DSQL endpoint. Otherwise the migration hangs.


• 
  Batched inserts under 10k rows/txn. When minting units for a large drop, inserts are batched in chunks of 500.

These are reasonable constraints for what DSQL gives you in return.

Try it

• 
  Live demo: https://dropzero.vercel.app
  


• 
  Code: https://github.com/mrayhankhan/dropzero
  

The app runs in zero-credential preview mode (in-memory) by default, so you can poke around without an AWS account. The badge in the header tells you which mode you're in.

The takeaway

Distributed databases don't remove the hard parts of concurrency — they move them into your data model. Aurora DSQL's OCC model is powerful, but it punishes designs with hot rows and rewards designs with spread-out writes.

Turn one hot counter into many cool unit rows, and a gnarly distributed-systems problem becomes a dozen lines of SQL — with a mathematically provable guarantee baked in.

Built for the H0: Hack the Zero Stack hackathon — Track 3 (Million-scale Global App). #H0Hackathon