Your "check quota then insert" tier limit has a race condition

Almost every free/paid tier system enforces its limit the same way: count how many rows the account already has, compare against the cap, and if there's room, insert a new row. It reads as obviously correct. It also has a race condition that costs you nothing to reproduce and nothing to notice until an account is already over its limit in production.

We found this in Flatline's POST /checks endpoint — the route that creates a new monitor. A QA pass fired 10 concurrent requests at an account sitting at 24 of its 25-check limit. All 10 succeeded. The account ended up with 34 active checks against a cap of 25.

Why "count then insert" isn't atomic

The original code looked like this, roughly:

const { cnt } = await DB.prepare(
  'SELECT COUNT(*) as cnt FROM checks WHERE api_key_id = ? AND deleted_at IS NULL'
).bind(apiKeyId).first();

if (cnt >= limit.maxChecks) {
  return c.json({ error: 'check limit reached' }, 429);
}

await DB.prepare(
  `INSERT INTO checks (id, api_key_id, ...) VALUES (?, ?, ...)`
).bind(id, apiKeyId, ...).run();

Each individual statement is fine. The bug is the gap between them. The count and the insert are two separate round-trips to the database, and nothing stops a second request's SELECT COUNT(*) from running before the first request's INSERT commits. Fire enough concurrent requests at the boundary and every single one reads the same pre-insert count, every single one sees room under the cap, and every single one inserts. This is the textbook check-then-act (TOCTOU) race, and it doesn't need exotic timing to trigger — ordinary concurrent traffic at the edge (retries, a burst from a script, a user's CI pipeline provisioning checks in parallel) is enough.

It's easy to assume SQLite-family databases save you here because writes are serialized. They do serialize writes — but only within a single statement. Two separate statements, even against the same database, are two separate opportunities for a read to happen before a write lands. Serialized writes don't make a two-statement read-then-write sequence atomic.

The fix: fold the check into the write

Instead of a SELECT and then an INSERT, do both in one statement, so the database — not your application code — is the thing deciding whether the row is allowed to exist:

const insertResult = await DB.prepare(
  `INSERT INTO checks
     (id, api_key_id, name, period_seconds, grace_seconds, webhook_url,
      status, last_state_change_at, created_at, updated_at)
   SELECT ?, ?, ?, ?, ?, ?, 'up', ?, ?, ?
   WHERE (
     SELECT COUNT(*) FROM checks WHERE api_key_id = ? AND deleted_at IS NULL
   ) < ?`
).bind(
  id, apiKeyId, name, periodSeconds, graceSeconds, webhookUrl,
  nowIso, nowIso, nowIso,
  apiKeyId, limit.maxChecks
).run();

if (insertResult.meta.changes === 0) {
  return c.json({ error: 'check limit reached' }, 429);
}

INSERT ... SELECT ... WHERE is a single statement: the count subquery and the row-count guard are evaluated as part of the same write, so there's no window between "check" and "act" for a concurrent request to slip through. If the subquery's count has already reached the cap, the WHERE clause is false, the SELECT yields no row, and the INSERT inserts nothing — you can tell it happened by checking meta.changes === 0 instead of catching an error. Ten concurrent requests at the 24/25 boundary now produce exactly one success and nine 429s, deterministically, every run.

The regression test that actually catches this

A unit test that calls the endpoint once and checks the response proves nothing about this bug class — the race only shows up under real concurrency. The test that matters fires the requests in parallel and asserts on the aggregate outcome, not any single response:

const results = await Promise.all(
  Array.from({ length: 10 }, (_, i) =>
    createCheck(apiKey, { name: `concurrent-${i}`, period_seconds: 300 })
  )
);

const succeeded = results.filter(r => r.res.status === 200);
const rejected = results.filter(r => r.res.status === 429);

expect(succeeded.length).toBe(1);   // exactly the remaining slot, not more
expect(rejected.length).toBe(9);

Run this against the old two-statement version and it fails immediately — most or all 10 requests come back 200. Run it against the atomic version and it's stable. That instability-to-stability difference is the actual proof the fix works; without a concurrent test, both versions pass every other test in the suite.

This generalizes past one endpoint

We'd already fixed the identical bug class once before, on Flatline's /register endpoint (IP-based rate limiting for free-tier signups), before finding it again on /checks. Same shape both times: a per-something quota, enforced with a count then a write, two round-trips with a gap between them. If your product has more than one quota — API keys per account, seats per team, items per plan tier, requests per rate-limit window — each one is a candidate for this exact bug unless the count and the write happen inside one statement. The general rule: any time you're about to write code shaped like "count rows, compare to a limit, then insert a row," ask whether the count and the insert can be the same database statement. In SQLite and D1, INSERT ... SELECT ... WHERE (subquery) < limit almost always gets you there.