---
title: Three million SCORM commits a day, off the storage queue and into Postgres
description: "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…"
created: 2026-09-19
updated: 2026-09-17
authors: ThinkingCap R&D
topics: [CapCom]
status: published
canonical: https://console.thinkingcap.com/rd/CapCom/moving-scorm-commits-to-postgres
date: 2026-09-19
lastmod: 2026-09-17
---

# 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](https://thinkingcap.blob.core.windows.net/rd-home/scorm-pg-queue-fig1a-today.svg)

![The plan — ACA enqueue API writing Postgres scorm_jobs](https://thinkingcap.blob.core.windows.net/rd-home/scorm-pg-queue-fig1b-plan.svg)

## 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](https://thinkingcap.blob.core.windows.net/rd-home/scorm-pg-queue-fig2-enqueue-sequence.svg)

## 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](https://thinkingcap.blob.core.windows.net/rd-home/scorm-pg-queue-fig3-job-lifecycle.svg)

## 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](https://thinkingcap.blob.core.windows.net/rd-home/scorm-pg-queue-fig4-cutover.svg)

## 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](https://thinkingcap.blob.core.windows.net/rd-home/scorm-pg-queue-fig5a-phases-gates.svg)

![Phases 3–6 — hardening, soak, live waves, cleanup](https://thinkingcap.blob.core.windows.net/rd-home/scorm-pg-queue-fig5b-phases-rollout.svg)

## 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](https://thinkingcap.blob.core.windows.net/rd-home/scorm-pg-queue-fig6-load-tests.svg)

---

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