DEV Community

M Rayhan Khan
M Rayhan Khan

Posted on

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

#aws #postgres #nextjs #webdev

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
);
Enter fullscreen mode Exit fullscreen mode

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;
  });
}
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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:

Approach Problem
Single-region RDS with row locks Locks under high concurrency → queue → latency spike
Redis DECR with Lua Fast but not durable by default; adding durability adds complexity
DynamoDB conditional writes No SQL; harder to model the relational parts (orders, seller analytics)
CockroachDB / Spanner Great, but operational overhead; not serverless
Aurora DSQL Serverless, PostgreSQL SQL, multi-region active-active, strongly consistent, zero ops

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

Layer Choice
Frontend Next.js 14 (App Router) + Tailwind CSS + SWR for live polling
API Next.js Route Handlers (Node runtime)
Database Amazon Aurora DSQL — serverless, multi-region, PostgreSQL-compatible
DB auth pg driver + @aws-sdk/dsql-signer — IAM auth tokens, no stored passwords
Hosting Vercel + Vercel Marketplace OIDC integration (keyless AWS access)

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

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

Top comments (0)