Per-User Analysis Queue: Serializing Uploads with Postgres Advisory Locks and Held Runs

A single enthusiastic user is the hardest load pattern to design for. One hundred users each uploading one file is a smooth curve; one user uploading five files back to back in a single afternoon is five simultaneous spikes with no interleaving. The classic rate limit catches the hundred-users case cleanly and the back-to-back-same-user case clumsily — a 429 Too Many Requests tells the user to try again later, which is the exact wrong answer when the user is actively lining up more work. This is the writeup of the per-user queue we shipped last week to fix that, how we got the admit decision atomic without a dedicated queue broker, and the three subtler rules that stop the queue from racing itself across a Coolify rolling deploy.

The feature lives inside Acurio, our citation-verification product for academic theses. The workflow it addresses is specific and recognisable: a lecturer or supervisor sits down with a stack of five student theses, uploads the first PDF, attaches its sources, clicks Analyse starten, uploads the second, attaches its sources, clicks Analyse starten again, and so on down the stack. Before the queue, those five clicks spent five slots of our judging budget simultaneously, pinned the extractor, and often tripped our provider's own concurrency ceiling, which surfaces as a vague "temporarily unavailable" to the user half an hour into their batch. After the queue, the five analyses still accept all five clicks — every validation, every credit debit, every synchronous 4xx — and then run in order. The user sees one running and four queued, the dashboard shows the batch, and the second analysis starts the moment the first one lands.

The Shape of the Problem: Admit Early, Execute Later

The temptation when a user is submitting too many of something is to reject the extras. That is the right answer when you cannot afford to accept them and the user has somewhere else to go — a form POST where a 429 tells them "please come back" is honest. It is the wrong answer when the user is lining up genuinely wanted work on an UI that promises to track all of it. If click five produces a toast that the first four are "still processing," the user has to babysit their own queue, remember which theses they already clicked, and refresh the page to find out when a slot opens up. That is a shape we have called out before in enterprise AI agent production failure modes — a backpressure story that pushes the discipline onto the user is a backpressure story that fails.

So the queue has two jobs that most queue implementations collapse into one. The first job is to admit the request: accept the upload, run every synchronous check the start path can raise (credit sufficient, sources attached, run id freshly reserved), take the credit debit, and tell the user that their click landed. The second job is to execute the analysis: pull the sources, run the extractor, feed the citation judge, write the verdicts. The reason the queue has to split them is that every failure mode worth telling the user about belongs to the first job. If the user is going to get a insufficient credits error, they want it when they click, not forty minutes later when four other analyses have finished.

Admit at INSERT, Decide Under an Advisory Lock

The admit step runs inside a Postgres advisory lock keyed on the user id. We do not use SELECT ... FOR UPDATE here because there is no row to lock yet — the whole point of the admit step is to decide whether the row we are about to insert should be born pending (take the slot immediately) or queued (wait for the slot). Advisory locks are the right primitive for "coordinate across sessions on a key that doesn't correspond to a row": the Postgres advisory lock docs describe them as "locks that have application-defined meanings": Postgres does not enforce them, the application chooses the key and decides what it protects, which is exactly what we need.

export async function withUserQueueLock<T>(
  userId: string,
  fn: (db: Queryable) => Promise<T>,
): Promise<T> {
  const client = await getPool().connect()
  try {
    await client.query("BEGIN")
    await client.query(
      "SELECT pg_advisory_xact_lock(hashtextextended('analysis_queue:' || $1, 0))",
      [userId],
    )
    const result = await fn(client)
    await client.query("COMMIT")
    return result
  } catch (err) {
    await client.query("ROLLBACK").catch(() => {})
    throw err
  } finally {
    client.release()
  }
}

The lock is xact-scoped: it is released automatically when the transaction commits or rolls back, so there is no "we leaked a user lock" failure mode when a request errors mid-admit. The namespace analysis_queue: is hashed in with hashtextextended so our other advisory keys (there are several in the codebase now) can share the sixty-four-bit space without colliding. The function the admit path runs inside this lock does one SQL INSERT whose status column is a CASE expression:

INSERT INTO analysis_jobs (user_id, run_id, status, ...)
VALUES ($user, $run,
  CASE WHEN $held::boolean AND EXISTS (
      SELECT 1 FROM analysis_jobs b
       WHERE b.user_id = $user
         AND b.run_id <> $run
         AND b.delta_of_job_id IS NULL
         AND b.deleted_at IS NULL
         AND b.status IN ('queued', 'pending', 'running')
    ) THEN 'queued' ELSE 'pending' END,
  ...)

Two tabs that race the admit step both acquire the lock in order, so the later INSERT sees the earlier row and chooses queued. There is no in-memory counter, no Redis INCR, no race window between "decide" and "commit." The decision and the row land atomically.

The 'Held' Upstream Run: Keeping Errors Synchronous

The lock gets the race right. It does not, by itself, let us keep every error on the first click. The admit step calls out to a Rust service (acurio-core) to create a run — the upstream artifact that will carry verdicts — and the create can fail: an invalid DOI set, a thesis body that the extractor couldn't parse, a provider budget we have already blown for the day. If the admit step creates the run normally and then queues the job, those errors arrive forty minutes late. If it skips creating the run until the queue reaches the job, the user sits in the dashboard watching four jobs clear, only to be told on the fifth that the fifth upload was never actually valid.

The fix is a two-phase upstream API: create with hold, release later. A held run exists in the Core service's state machine but refuses to start judging. It still validates every synchronous precondition at create time — DOI shape, source list, credit, provider ceiling — so every error that would have fired at create still fires at create. When the queue reaches the job, processJob calls releaseHeldCoreRun, which flips the run out of the held state, attaches sources, and the judging starts. The user has already paid the credit debit at admit time; the release is a no-op from their point of view.

This is the same two-phase shape we leaned on in Stripe webhook idempotency: any time an action spans two services with their own state machines, the earlier service has to commit something that lets the later service recover, including after a redeploy. A held run is that commit: it is a durable receipt that the admit step succeeded, with every synchronous check already evaluated against it.

Durable State: The Queue Lives in Postgres, Not in Memory

The queue is a WHERE status = 'queued' on analysis_jobs. There is no separate queue table, no Redis LIST, no in-memory promotion ring. A thirty-second resume sweeper and the processJob tail call both run promoteNextQueuedJob(userId), which — under the same advisory lock as admit — picks the user's oldest queued row, verifies no other first-pass job of theirs is already pending or running, and UPDATEs the status to pending. Two promoters cannot start two runs side by side because they share the user lock, and a Coolify rolling deploy that overlaps the old and new container cannot either, because the lock is at the Postgres level, not the Node level. This is the same durability story we told in Postgres job row durability: a backstop sweep — the invariants live in the database, not in the process.

Three subtler rules fell out of running the queue in production for the first week:

Delta re-checks skip the queue. The feature lets a user re-run only the citations that changed since the last analysis. Those re-checks are cheap, synchronous from the user's point of view, and should not get in line behind the lecturer's five fresh theses. The queue blocks on first-pass jobs only, filtered in SQL by delta_of_job_id IS NULL. The sweeper never considers deltas; processJob never queues them.

Held-run release has its own retry window. Core might be unreachable for the thirty seconds between queue promotion and release. The resume sweeper gives the release a thirty-minute retry window before it gives up and refunds the credit, so a provider blip does not cost the user a thesis. Any longer and the user has already navigated away expecting a verdict; any shorter and a two-minute Core restart is a lost run. AWS's exponential backoff and jitter guidance covers the shape — our thirty minutes is the "give up" boundary, with the standard capped-jitter backoff in between.

Promoter and admitter share the same lock and the same key format. The lock key is a hashed string template, analysis_queue:<user>. Both call sites use the exact same template. A copy-paste typo (analysis-queue: with a dash, say) would silently split the lock namespace in two: both callers would acquire "a" lock, neither would acquire the lock, and the serialization would evaporate. There is now a single withUserQueueLock(userId, fn) helper that owns the key format, and every admit and every promotion goes through it.

The whole queue is around 500 lines of TypeScript, two SQL expressions, and one two-phase upstream API. It serialises a user's own back-to-back work, keeps every synchronous error on the first click, survives a redeploy without losing in-flight state, and does all of it on top of Postgres primitives we already had in production for other reasons. If you are serving a workflow where the hardest load pattern is a single motivated user — book a free AI Potenzial-Check — or read our debounced autosave race writeup for the frontend-shaped version of the same "composition under a lock" theme.