DEV Community: M Rayhan Khan

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

M Rayhan Khan — Sat, 13 Jun 2026 05:25:43 +0000

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:

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.
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:

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

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

How I built a dependency risk scanner with Coral

M Rayhan Khan — Sat, 30 May 2026 18:33:39 +0000

Why this project

Every developer has 5-10 side projects with rotting dependencies and doesn't know it. The 2024 xz-utils backdoor was caught by accident — one engineer noticed SSH was 500 ms slower than usual. That's how close it came.

Tools like Snyk and Dependabot catch known CVEs after they're published. Nothing checks the three signals that together predict a future supply-chain attack: active CVEs · abandoned maintainer · collapsing downloads.

That three-way signal only exists if you can JOIN across OSV (Google's vulnerability database), the npm registry, and the npm download API. Which is exactly what Coral does.

The query the whole project is built around

WITH pkg AS (
  SELECT name, latest_version, repository__url, time__modified AS last_publish_at
  FROM npm.packages WHERE package_name = :pkg
),
cves AS (
  SELECT affected__package__name AS package_name,
         COUNT(*) AS cve_count,
         MAX(CASE database_specific__severity
               WHEN 'CRITICAL' THEN 4 WHEN 'HIGH' THEN 3
               WHEN 'MODERATE' THEN 2 WHEN 'LOW' THEN 1 ELSE 0 END) AS worst_sev_rank
  FROM osv.vulnerabilities
  WHERE package_name = :pkg AND ecosystem = 'npm' AND withdrawn IS NULL
  GROUP BY affected__package__name
),
dl_month AS (
  SELECT downloads FROM npm_downloads.downloads_last_month WHERE package_name = :pkg
)
SELECT pkg.*, COALESCE(cves.cve_count, 0) AS cve_count,
       COALESCE(cves.worst_sev_rank, 0)   AS worst_severity_rank,
       dl_month.downloads
FROM pkg
LEFT JOIN cves     ON cves.package_name = pkg.name
LEFT JOIN dl_month ON 1 = 1;

One query. Three live systems — three different hosts (registry.npmjs.org, api.osv.dev, api.npmjs.org). Zero glue code. No ChatGPT instance on earth can run this. Verified against minimist: 2 CVEs, worst severity CRITICAL, 531M downloads/month.

The OSV source spec

OSV is a public REST API. The Coral source spec is a single YAML file, and the skeleton came together quickly. The hard part started right after.

The vulnerabilities table uses POST /v1/query. Two things bit me, and both were about reading the response shape correctly rather than writing YAML:

The __ flatten convention isn't automatic. I assumed a column named database_specific__severity would auto-resolve the nested database_specific.severity. It didn't — it came back null. Nested fields need an explicit expr:

   - name: database_specific__severity
     type: Utf8
     expr:
       kind: path
       path: [database_specific, severity]

Array indexing uses string keys. To lift the package name out of the affected[] array as a JOIN key, the path is [affected, "0", package, name] — "0" as a string, not an integer (the integer form fails schema validation).

coral source lint caught my structural mistakes offline; the nested-path mistakes only showed up when I ran a real query against lodash and minimist and saw null columns. No tool tells you that — you have to diff the response against your spec by hand.

The npm specs (note the plural)

This is where I learned the most. npm is really two APIs: registry.npmjs.org for package metadata and api.npmjs.org for download counts. My first instinct was one source spec with a per-table base_url override.

That field doesn't exist. A Coral source has exactly one base_url. The clean fix turned out to be better than the hack: two source specs. npm.yaml (the packages table) and npm_downloads.yaml (downloads_last_month + downloads_last_week). That's not a workaround — it's a second genuinely reusable spec, and it doubled my bounty surface area.

The other lesson: a declared filter must also be declared as a column for WHERE package_name = ... to resolve, and the filter value isn't echoed back automatically — so JOINs key off real returned columns (npm.packages.name, downloads.package, osv.affected__package__name).

The fan-out

The auditor reads package.json from a GitHub raw URL, collects every dependency (including devDependencies — supply-chain attacks via dev tools are real, see event-stream), and runs the query once per dep with 6-way concurrency. chalk/chalk finished in ~17 seconds. lib/concurrency.ts is 30 lines.

The dashboard

Three columns: 🟢 healthy / 🟡 watch / 🔴 danger. Click a card → drill-down with every signal and a direct link to the OSV record. The whole UI is in app/components/ — three files, all client components, Tailwind for styling. GitHub maintainer-activity (last push, archived) is layered on top via the bundled github source when a token is present — deliberately kept out of the headline query so the demo runs with zero auth.

The scoring rules

lib/risk.ts is a pure function, and the scoring rules are the part I most wanted to get right by hand — this is product judgment, not codegen. Easy to unit-test. Rules I landed on:

CVE HIGH/CRITICAL → instant danger. No nuance needed.
Stale + collapsing → danger. Maintainer silent > 1 year AND downloads down > 30%. This is the xz-utils pattern.
Stale OR declining → watch. Either alone is a yellow flag, not red.
Otherwise → healthy.

The compound rule was the one that took the most reading. A package can have no CVE filed yet and still be the most dangerous thing in your repo if the maintainer has gone dark.

The fallback path

I built a transparent HTTP fallback (lib/coral.ts path B) so the demo doesn't break on a laptop without Coral installed. Same data, same row shape, just bypassing the CLI. The header shows coral CLI vs direct HTTP fallback so judges can see which path is active. The fallback is a safety net; Coral is the production engine.

One subtlety that cost me an hour: Coral's SQL engine is DataFusion, not SQLite. So julianday() and json_group_array() don't exist — the date math is date_part('day', now() - to_timestamp(col)) and the CVE list is array_agg(named_struct(...)). Worth knowing before you write the query.

What I learned

Three insights I didn't expect:

The hard part of source specs isn't writing them — it's reading the API docs accurately. Scaffolding the YAML is fast (with or without an assistant). I spent far more time confirming OSV's nested field paths against real responses than authoring the spec. Every null column was a docs-reading miss, not a syntax error — and no tool catches those for you.
The cross-source JOIN really does feel like magic in the terminal. Three completely separate systems, one result set, one query. The demo moment isn't the dashboard — it's the coral sql invocation. Everyone leans forward.
Pure scoring functions are worth their weight in unit tests. Once lib/risk.ts was unit-tested I could change the rules without fear. The 12 tests in risk.test.ts caught two regressions during the hackathon.

Try it

The repo is on GitHub. Three custom source specs, one Next.js app, MIT licensed:

git clone https://github.com/mrayhankhan/CoralBean.git
bash scripts/install-coral.sh
npm install && npm run dev

Paste facebook/react (mostly green) or chalk/chalk (watch color-convert go red) and watch.

🏴‍☠️