ThinkingCap
ThinkingCap Research And Development CapCom Three million SCORM commits a day, off the storage queue and into Postgres

Three million SCORM commits a day, off the storage queue and into Postgres

Published 2026-09-19 Updated 2026-09-17 ThinkingCap
Research And Development

Three million SCORM commits a day, off the storage queue and into Postgres

What this is, in one paragraph: on a normal weekday, roughly three million SCORM commits travel from learners' browsers through an Azure Storage queue-and-table handoff into our workers. The plan now on the table replaces that handoff with a small enqueue API on Azure Container Apps, writing a single Postgres table. Unusually for a migration, the load tests come first — nothing near a browser changes until the numbers say the new path is boring. These are the six flows that define the plan.


The shape of the change

Today the course page mints a shared-access signature (SAS) into the page, and the browser writes each commit itself — a tiny pointer message plus the payload entity — straight into Azure Storage. The workers (two engines, C# and Node, on Spot VMs that churn constantly) take it from there.

The plan: the page instead vends a short-lived enqueue token of our own — HMAC-signed, good for 24 hours — and the browser POSTs one merged JSON document to scorm-enqueue-api, a Fastify service on Azure Container Apps, in the same region as the workers and the database. One INSERT into scorm_jobs, payload inline as jsonb — which retires three separate size ceilings in a single move.

Today — Azure queue and table handoff

The plan — ACA enqueue API writing Postgres scorm_jobs

What the browser sees

The request contract is designed to the circuit breakers the browser already has. A 400 or 401 counts toward the existing queueFail counter. A 429 says back off. A 503 — after four strikes, the browser falls back to its old inline commit path, exactly as it does today when the queue misbehaves. No new failure mode ever reaches the learner's screen.

Enqueue sequence — token vend, POST, failure branches, claim, commit

The life of one row

One row per commit; ack and fail both DELETE, so the live row count is the queue depth. Claims use FOR UPDATE SKIP LOCKED with a 90-second lease and a fencing token that rotates on every claim and every renewal — so when a Spot VM vanishes mid-batch, the next claimer simply steals the lapsed lease and carries on. A message that refuses to die gracefully lands, after enough attempts, in the same dead-letter store operators watch today.

Job lifecycle — pending, claimed, acked, dead-lettered, stolen

Cutting over without a flag day

Producers flip per client — a server-rendered branch flag, smallest clients first, in the quiet hours. The worker claims Postgres first and drains the Azure remainder every cycle, emitting a poll_from metric with every tick. When the Azure share stays under one percent for a week straight, the old reader comes out. And then the satisfying part: the SAS vending is retired and the last shared storage keys rotate away — one fewer credential in the world.

Cutover — per-client producer flip, dual-source worker, retirement signal

The road, with its gates

Every phase ends at a gate that has to be argued through, not just walked past.

Phases 0–3 — schema, worker dual-source, and the load-test gate

Phases 3–6 — hardening, soak, live waves, cleanup

The gate that comes first

The first build milestone is not the API — it is the load rig. A scratch Postgres server (same SKU, throwaway) takes a write-heat harness that steps from the day-average 33 messages a second up past 300; a claim-heat harness with up to 256 concurrent claim loops at queue depths up to 300,000; and a replay of a real flood day — 300,000 rows bulk-loaded, then drained by the dev worker fleet while we watch table bloat and autovacuum keep pace.

The report out of that rig is the go/no-go for touching the producer at all. If the shared queue database cannot hold the headroom, the fallback is a dedicated database on the same server — a connection-string change, and nobody's weekend.

Load-test gate — write heat, claim heat, flood replay, decision


The plan is drafted and awaiting its ratifications. Nothing in production changes until the numbers say so — we'll report what the load rig finds.

Read this page as Markdown: https://console.thinkingcap.com/guest/Research-and-Development/CapCom/moving-scorm-commits-to-postgres.md
Same content, machine form. The whole corpus: /guest/all.md.