Durable background work, from first principles

AID scans an uploaded document in the background. That sounds simple: put a message on a queue, let a worker scan the file, and save the result. The hard part is making that sentence remain true when processes crash, messages arrive twice, a queue is unavailable, two workers race, or one tenant tries to name another tenant's work.

This tutorial explains the engineering ideas behind AID's Phase 3 background work. It starts with plain-language mental models, then connects them to the technical names and the current code.

Decision and implementation status: ADR-0016 is Accepted. It records the target architecture, not a claim that every target control is already implemented. Phase 3 step 1 (the database and storage authority split) has landed: the API no longer inherits scan-transition or delivery-write permission, each process runs under its own capability role and self-certifies at startup. Step 2 (the hard-crash attempt ceiling) has also landed: every new claim checks the durable budget before opening the blob or calling the scanner, so a file that repeatedly crashes the worker fails closed to needs_attention at the ceiling instead of being re-scanned forever. Step 3 has also landed: reconciliation now reports expired leases after a 60-second grace and active jobs at/over the configured attempt budget. Step 4 has now made development scanning default-deny as well. Step 5 commits bounded token/expiry claims before Queue Storage I/O, conditionally finalizes each row in a short transaction, reports expired claims, and treats a lost token or finalize as an explicit duplicate-safe ambiguous delivery. The accepted real-document threat gates are enforced; the remaining sections explain the implemented controls and operational remediation still open in Phase 3.

The story in one minute

Imagine a company mailroom:

1. A receptionist accepts a parcel and records it in the official register.
2. A dispatcher notices the new register entry and puts a small collection ticket in a courier's tray.
3. A courier uses the ticket number to re-read the official register, claims the parcel for a limited time, and takes it to inspection.
4. Inspection writes the result back to the register.
5. Only after the result is safely recorded does the courier discard the ticket.

The tray is useful, but it is not the official register. Tickets may be copied, delayed, or delivered again. The system stays correct because every courier re-checks the register before acting.

In AID:

• the official register is SQL Server;
• the ticket tray is Azure Queue Storage;
• the receptionist is the HTTP API;
• the dispatcher is the outbox dispatcher process;
• the courier and inspector are the background worker and scan engine;
• a collection claim is a SQL execution lease;
• an item needing human help is recorded as needs_attention and delivered to the poison queue.

Vocabulary

What “secure scanner substrate” means

The scanner product and the scanner substrate are different things. This distinction is the key to understanding why AID can defer ClamAV, Microsoft Defender, or another production provider without deferring Phase 3.

Think of airport baggage inspection:

The X-ray brand can be chosen later. The airport still needs the locked area, labels, access rules, and recovery process before it accepts real baggage. Those surrounding controls are the secure scanner substrate.

For AID, the substrate includes:

• a ScanEngine contract that a provider adapter can plug into;
• a private uploaded blob and an immutable Document Version;
• a security state that starts at pending, never at clean;
• a durable job and outbox fact committed with the upload;
• queue delivery that may safely happen more than once;
• a worker lease so only one owner executes the logical job at a time;
• a durable retry budget that also covers hard process crashes;
• a narrow database transition that only the worker may invoke;
• a single content gate that exposes only clean versions;
• needs_attention, poison delivery, diagnostics, and reconciliation when automatic processing cannot finish safely;
• a development scanner that is denied by default and clearly provides no real malware protection.

One upload through the substrate

Suppose Mei uploads Supplier invoice.pdf:

Upload accepted
  ↓
Blob stored privately
  ↓
Document Version committed with security_status = pending
  ↓
Scan job + outbox fact committed in the same SQL transaction
  ↓
Dispatcher sends an identifier-only queue message
  ↓
Worker re-reads SQL job truth and takes a lease
  ↓
ScanEngine adapter inspects the byte stream
  ├─ clean         → commit clean; content gate may open
  ├─ malicious     → commit malicious; content stays closed
  ├─ unscannable   → commit unscannable; content stays closed
  └─ outage/crash  → bounded recovery, then needs_attention

The scan call is one step, but safety is enforced around it. A previewer, OCR worker, search indexer, downloader, or Automation must all use the same content gate. Otherwise one forgotten path could process a pending or malicious file.

Job truth, diagnostics, logs, and audit are not the same

These four terms answer different questions. Mixing them causes unreliable recovery or accidental disclosure of internal information.

Job truth: the official control record

In AID, SQL Server owns job truth. A background_job row records the logical job's identity, workspace, kind, status, attempt count, and current lease. The Document Version records the durable security outcome.

Azure Queue Storage does not own job truth. A queue message only means:

“Job 123 may need attention; load the official row and decide.”

This rule matters because a queue message can be duplicated, delayed, redelivered after a crash, or sent successfully even when the dispatcher never receives confirmation. The worker must never interpret message arrival as proof that the job is new or authorized.

Real examples:

• A duplicate arrives after the job is complete. SQL says terminal, so the worker acknowledges it without scanning again.
• A duplicate arrives while another worker has a live lease. SQL identifies the current owner, so the duplicate backs off.
• A worker dies during attempt three. The lease expires, a later delivery checks the durable attempt count, and recovery continues within the budget.
• The budget is already exhausted. The next delivery does not open the blob or call the scanner; it records needs_attention and keeps the version closed.

Without job truth, each physical queue message would look like fresh work. AID could scan twice, overwrite outcomes, spend without limit, or retry a crashing file forever.

Diagnostics: the useful explanation of a problem

Diagnostics describe a specific failed attempt or current incident. Useful, bounded fields include a safe error category, job ID, attempt number, duration, lease or correlation ID, and a redacted provider request ID.

For example:

category = scanner_timeout
job_id = 123
attempt = 4
elapsed_ms = 30000
correlation_id = scan-7f2…

Diagnostics should help someone distinguish “malware found” from “scanner unavailable” or “blob missing.” They must not contain document bodies, credentials, malware payloads, or unbounded raw provider responses. A latest error may change on the next attempt, so diagnostics are not permanent audit.

Operational logs: the chronological trail

Structured logs describe the journey across processes:

upload committed → outbox claimed → queue send attempted → worker claimed
→ blob opened → scanner timed out → retry scheduled

Each entry should carry stable identifiers and a correlation value so an operator can follow one logical job without searching for a protected filename. Logs help debug behaviour and produce metrics, but they are not allowed to decide whether work is complete. If a log line says “clean” but SQL did not commit clean, the file remains closed.

Audit history: a separate product and governance decision

Audit history preserves selected human-meaningful facts for a defined audience and retention period. Examples could include a user requesting a rescan or an administrator manually releasing a version. Deciding who may see those facts, how long they remain, and what is redacted is still a separate cross-cutting decision.

Phase 3 therefore implements job truth, bounded diagnostics, and operational logs now, but it must not invent a scan-only audit table or customer activity feed. Secure recovery does not depend on pretending that operational logs are audit records.

Why these foundations belong in Phase 3

They are needed now for four practical reasons:

1. OCR and Automation will consume content. Giving them one clean-content rule now is safer than finding every content path during a later retrofit.
2. Failures begin on the first upload. Crashes, duplicate messages, lost acknowledgements, and ambiguous commits are not production-only events.
3. A scanner provider cannot repair application mistakes. A commercial scanner does not prevent the API from forging a verdict, two workers from racing, or retries from running forever.
4. The substrate is testable without a production scanner. The development adapter can prove state transitions, leases, retry exhaustion, permissions, diagnostics, logs, and reconciliation using synthetic files.

Phase 3 includes the documented preferred operational work too: correlated structured diagnostics, initial telemetry and SLO signals, report-first reconciliation including retained blobs, operator visibility through the CLI and needs_attention, and realistic crash/outage/permission tests.

What remains deferred is deliberately narrower: choosing the production scanner product, production provider readiness, customer-visible audit, manual release or false-positive override policy, and an end-user scanner administration UI. None of those deferred decisions should stop the current Phase 3 remediation.

Why scanning is asynchronous

An HTTP upload should not hold a browser connection open while a scanner is slow, restarting, or unavailable. Scanning can also consume CPU, memory, network bandwidth, and database connections. If it runs inside the request process, a burst of large uploads can make ordinary navigation slow or make a scanner crash take the API down.

Separating the work gives AID three independently runnable targets:

HTTP API
  └─ commits document + version + job + outbox fact in SQL

Outbox dispatcher
  └─ publishes committed facts to Azure Queue Storage

Background worker
  └─ receives a wake-up, re-reads SQL truth, scans, and commits the verdict

They currently live in one @aid/api package and ship as one versioned artifact, but they are separate operating-system processes. “One package” and “one process” are not the same thing.

What a service boundary is

A service boundary answers questions such as:

• Which process owns an activity?
• Which data may that process read or change?
• Can it be deployed or scaled separately?
• How does it communicate with other processes?
• What happens if one side is down or on a different version?

A real-life example helps. A restaurant may have one company and one building, but the cashier, kitchen, and delivery desk have different responsibilities. The cashier accepts payment but should not edit cooking temperatures. The kitchen prepares an accepted order but should not issue refunds. Written order numbers connect them without giving every station every power.

AID applies the same idea:

• the API owns authenticated requests and commits business state;
• the dispatcher owns SQL-outbox-to-queue transfer;
• the worker owns job execution;
• the scanner adapter owns communication with a scanning product;
• SQL constraints, procedures, roles, and RLS enforce important boundaries even if application code makes a mistake.

ADR-0016 accepts these three process boundaries in one package. It also makes an important correction learned from the first implementation: a separate process is not a security boundary if every process still carries the same database and storage powers.

The accepted production capability model is:

aid_background is only a marker proving that a principal may use the cross-tenant RLS branch. It intentionally grants no table operation. Separate capability roles decide whether that principal may dispatch, scan, report, or repair.

Implemented (2026-07-24): migration 20260723200036_background-process-roles removed background UPDATE and scan-transition EXECUTE from aid_runtime and added the dispatcher, worker, report, and repair capability roles (nested in aid_background). Migration 20260724143530_orphan-blob-delete-capability adds the independent deletion marker used only with the reconciler reads. Compromised API code can no longer call pending → scanning → clean — only the worker role holds the transition EXECUTE. Each process self-certifies its required and forbidden effective permissions at startup (assertProcessPrincipal), so the process diagram is now a database-enforced security boundary, not only an operational one.

Contracts and adapters

A contract describes what the system means without depending on a vendor. An adapter translates that contract to a specific technology.

For example, ScanEngine accepts a byte stream and returns a result such as clean, malicious, or unscannable. It does not say “call ClamAV” or “call Microsoft Defender.” A ClamAV, Defender, or commercial API integration would be an adapter behind that contract.

This separation matters because:

• domain tests can run without a real cloud scanner;
• changing providers does not rewrite job semantics;
• provider-specific authentication, timeouts, and errors stay at the edge;
• future OCR and Automation work can reuse the background substrate without copying vendor code into domain logic.

The development scanner only recognizes the EICAR test signature and empty content. It proves the pipeline, not malware protection. The accepted rule is default deny: local, test, or isolated synthetic-data use needs an explicit unsafe opt-in, while shared, pilot, pre-production, and production deployments reject it. NODE_ENV=production remains defense in depth, not the safety decision. The worker and development adapter now enforce that rule using ALLOW_UNSAFE_DEVELOPMENT_SCAN_ENGINE plus DEPLOYMENT_ENVIRONMENT; an allowed isolated worker logs a prominent “NO MALWARE PROTECTION” warning.

SQL is truth; the queue is a wake-up mechanism

A queue is excellent at saying “someone should look at job 123.” It is a poor place to keep the only copy of business truth:

• a message can be delivered twice;
• visibility can expire while a worker is still running;
• a send can succeed even if the sender never receives confirmation;
• operators may need to reconstruct state after queue retention expires.

Therefore the queue message contains only stable identifiers and bounded routing metadata:

{
  "v": 1,
  "eventId": "…",
  "eventType": "document_version.scan_requested",
  "workspaceId": "…",
  "backgroundJobId": "…"
}

It contains no document bytes, credentials, user authority, or trusted job state. A worker uses the identifiers to load background_job under a lock and makes its decision from that row.

This is the central rule:

A queue message may wake work, but it cannot authorize work or declare the current outcome.

The transactional outbox pattern

The API must both save an upload and request a scan. SQL Server and Azure Queue Storage cannot participate in one ordinary database transaction. If the API saves SQL first and crashes before sending, the document is never scanned. If it sends first and SQL later rolls back, a worker receives a job that does not exist.

The transactional outbox solves this by making the database transaction do only database work:

BEGIN SQL TRANSACTION
  insert document
  insert immutable document_version
  insert background_job
  insert domain_event_outbox scan_requested fact
COMMIT

Either all four records commit, or none do. A separate dispatcher repeatedly looks for pending outbox rows, sends them to the queue, and marks them dispatched.

There is still a small ambiguity: the queue send can succeed and the SQL “mark dispatched” commit can fail. The dispatcher sends the event again. This creates a duplicate rather than a lost job. The worker design must therefore make duplicates safe.

Why dispatcher claims must be short

The dispatcher now leases a bounded due batch with UPDLOCK/READPAST and a stable claim_token plus claim_until, then commits immediately. Queue calls run after that commit, with no SQL transaction or connection held across the network wait. Because a batch shares an initial expiry while sends are sequential, each row first renews its claim in a short conditional transaction. A stale owner whose token no longer matches skips that send. Each success or failure then opens its own short transaction and can change the row only while the token still matches.

A slow or dead sender therefore cannot pin database locks. Claim expiry lets another dispatcher recover the row. Only a send already in progress can become the accepted duplicate; later stale batch rows are not sent again by the old owner. If a late sender also succeeded, job-truth idempotency accepts it:

short SQL transaction: lease due rows with claim_token + claim_until
commit

for each row:
  short SQL transaction: renew only if claim_token still matches
  token lost -> skip stale send

  send queue message outside SQL

  short SQL transaction per result:
    matching token + success -> dispatched
    matching token + failure -> pending with backoff

If the dispatcher dies, the lease expires and another instance recovers the row. If send succeeded but finalize is uncertain, the event may be sent again. That is still the correct trade: duplicates are safe; losing a committed fact is not.

At-least-once delivery and idempotency

Azure Queue Storage provides at-least-once delivery. Read that literally: AID expects one, two, or more deliveries of the same logical event.

A worker handles a duplicate by locking and reading the job row:

• succeeded or needs_attention: acknowledge; do not execute again;
• running with a live lease: back off; another delivery owns the work;
• queued: claim it if allowed;
• unknown or contradictory identity: preserve it as poison-on-arrival.

A terminal version verdict is write-once. The migration-owned transition procedure allows the intended scan state machine and refuses to leave a terminal state:

pending → scanning → clean
                   → malicious
                   → unscannable
                   → failed
                   → timed_out

scanning → scanning   (an idempotent re-claim after recovery)

This is stronger than hoping every caller remembers an if statement. The database owns the transition rule.

Why both queue visibility and a SQL lease exist

Receiving an Azure queue message hides it for a visibility timeout. If the worker crashes, the message becomes visible again. That handles delivery recovery, but it does not fully prevent concurrent execution:

1. Worker A receives a message and starts a long scan.
2. Its visibility expires or a duplicate message already exists.
3. Worker B receives a delivery for the same job.
4. Without a database claim, both workers scan concurrently.

AID therefore also takes a SQL lease:

• lease_token identifies the exact claim owner;
• lease_until says when another delivery may recover the job;
• visibility and the lease are renewed together during a long scan;
• finalize and failure updates require the current token;
• a live lease makes a duplicate back off;
• an expired lease permits recovery after a crash.

Think of queue visibility as reserving one physical ticket, while the SQL lease reserves the one logical job. Both are needed because duplicate physical tickets can exist.

Retry budgets: use logical attempts, not message counters

Azure supplies a dequeueCount for each physical message. That count is useful telemetry, but it cannot be the retry-budget authority. If reconciliation or an ambiguous send creates a new physical message, its queue count starts over. A permanently failing job could then receive unlimited attempts.

background_job.attempts is the durable logical count. A re-dispatched message still sees the same total. The accepted claim order is important:

lock and read the job

if terminal:
  acknowledge

if another owner has a live lease:
  back off

if attempts >= maximum:
  commit failed-closed version + needs_attention job + one outbox fact
  do not open the blob
  do not call the scanner

otherwise:
  increment attempts
  take the lease
  run the final allowed attempt

The budget check happens before every new claim, including an expired-lease recovery and an unusual queued row created by migration or repair.

Why this matters: a scanner can return a normal error, but it can also kill its worker through OOM, native-code failure, or SIGKILL. A dead process cannot run the graceful failure handler. Before step 2, the code incremented the next reclaim but checked exhaustion only after returned failures, so this sequence could continue without bound:

claim 5 -> worker dies -> lease expires
claim 6 -> worker dies -> lease expires
claim 7 -> ...

Each reclaim also refreshes updated_at, which means the old “running for 30 minutes” query does not see the loop. The pre-claim guard (now implemented in claimJob, sharing the commitExhausted helper with the graceful handler) fixes recovery and observability together: after the final allowed crashed attempt, the next delivery creates durable needs_attention truth and the normal alert fires. Reconciliation now uses the expired lease and active attempt budget, so the freshly updated timestamp cannot hide the breach.

This is an example of choosing the counter and the check location that match the business promise, not whichever queue field or happy-path callback is easiest to use.

What “poison” means

“Poison message” is an overloaded phrase. AID has two importantly different cases.

Poison on arrival: no trustworthy SQL job

The body may be invalid JSON, use an unknown contract version, contain forged identifiers, or name no job. There may be no trustworthy database row to hold a transfer state. The best available behavior is:

1. copy the original raw body to the poison queue;
2. delete the main-queue message only after the copy succeeds.

If acknowledgement is ambiguous, a duplicate poison copy is possible. The raw body is the evidence an operator needs.

Exhausted known job: SQL truth exists

A known job may repeatedly time out or fail until its logical attempt budget is exhausted. Here SQL can own the complete decision. One transaction:

1. changes the version to failed or timed_out;
2. changes the job to needs_attention with a redacted diagnostic;
3. clears the execution lease;
4. inserts one background_job.needs_attention outbox event.

Only after that transaction commits does the worker delete the original main queue message. The dispatcher routes the new event to the poison queue.

A filtered unique index permits only one logical needs-attention event per job. Azure can still contain duplicate physical copies after an ambiguous send, but they carry the same stable eventId. Operators alert on distinct needs_attention SQL jobs; raw poison queue depth is a secondary signal.

Why “send poison first” was incomplete

An earlier implementation sent the poison copy before committing terminal SQL state. That avoided losing the copy if the queue was down, but merely reversed the failure window:

send poison succeeds
        ↓ crash here
terminal SQL commit never happens

Now the poison queue says “terminal problem” while SQL still says running. Sending first can also create extra poison copies whenever the second step is ambiguous.

The durable design records transfer truth in the system that can share the transaction: SQL Server. Publication is then a retried, observable delivery step. This is the same outbox principle used for the initial scan request.

This lesson generalizes: when moving a fact between two systems, changing the order of two non-atomic writes does not make them atomic. Add durable transfer state, idempotency, and reconciliation.

Failure windows and recovery

Tenant isolation and least privilege

A background worker must scan jobs from many workspaces, which makes its data access more sensitive than a normal user request. Two database controls answer different questions:

• RLS asks “which rows can this transaction see?”
• GRANT/DENY asks “what operation can this principal perform?”

AID's cross-tenant background RLS branch requires both an app.background_service session key and membership in the grant-free aid_background marker role. That prevents the API from turning one session flag into an all-workspace query. It is not enough by itself: ordinary workspace RLS still allows a correctly scoped API transaction to see its workspace, so process capabilities must prevent that API principal from performing worker-only operations there.

The accepted production roles therefore separate:

• API enqueue authority;
• dispatcher outbox-delivery authority;
• worker job/verdict authority;
• reconciliation report authority;
• explicit reconciliation repair authority.

This distinction closes the original flaw. Direct version UPDATE was blocked, but the broad API role could still execute the owner procedure and self-certify a workspace version. In the target model, only the worker role can execute that procedure. Removing the procedure permission is the critical fix; removing API job/outbox updates closes additional integrity and denial-of- service paths.

When executing a particular job, the worker still sets the authoritative workspace before reading the version or blob reference. Composite foreign keys prevent a job in one workspace from naming a version in another.

Storage uses the same principle. Production does not share one account-wide secret: the API gets needed blob operations, the dispatcher queue-send, the worker blob-read and queue-consumer operations, and reconciliation bounded read/list. Deployment provisioning—not runtime startup—creates resources.

This is defense in depth:

• authentication and authorization decide who may request an operation;
• session context scopes the transaction;
• RLS filters rows;
• capability roles constrain operations;
• separate process credentials reduce blast radius;
• composite foreign keys prevent cross-tenant relationships;
• identifier-only messages carry no authority themselves.

Immutable evidence and a narrow transition procedure

Document Version content is immutable evidence. Giving the runtime role broad UPDATE permission just so a scanner can change two status columns would weaken that guarantee.

Instead, a migration-owned EXECUTE AS OWNER procedure exposes a narrow operation: valid scan-state transitions for one workspace and version. Runtime principals remain unable to update version rows directly, and only the worker capability role may execute the procedure. This is capability narrowing: grant the specific action required rather than general table power.

The owner execution is not itself authorization. SQL Server first checks whether the caller may execute the procedure. That is why granting EXECUTE to a broad API role defeated the intended separation even though direct table updates were blocked.

Reconciliation: proving that the pieces still agree

Retries repair many transient failures, but production systems also need to answer, “How do we know nothing is silently stuck?” Reconciliation compares relationships that should remain true.

The Phase 3 report checks:

• old pending outbox rows and their oldest age;
• dispatcher claims expired beyond the recovery grace;
• dispatched scan requests whose jobs remain queued too long;
• running jobs whose lease_until is past the recovery grace;
• active queued or running jobs already at/over their attempt budget;
• terminal job state that disagrees with version scan state;
• distinct needs_attention jobs;
• needs_attention jobs missing their poison outbox event;
• pending and previously failed poison deliveries and their oldest age;
• approximate main and poison queue depth;
• paginated retained-blob totals, grace-window exclusions, invalid prefixes, orphan count/oldest age, and bounded key samples.

Only one repair is currently automatic: re-dispatching an old scan event whose job is still queued. It may create a duplicate, which the worker already handles, and is safer than silently losing the wake-up. State mismatches and active-at-budget jobs are reported rather than “fixed” by guessing.

The accepted target separates the report-only database role from the optional repair and orphan-deletion roles. Being allowed to observe every workspace is not an automatic reason to receive cross-tenant write or Blob delete authority.

The implemented report uses lease time for abandoned claims and separately counts active-at-budget jobs. Mutable updated_at is diagnostic context, not the recovery authority.

Safe migrations must test old states

A clean empty database cannot reveal every upgrade bug. Before leases existed, a crashed worker could leave a valid row with:

status = running
lease_token = NULL
lease_until = NULL

The new lease constraint says every running job must hold both lease values. Adding that constraint directly would validate existing rows and fail the migration.

Migration three therefore adds the nullable columns, changes every pre-lease running row to queued, preserves attempts and last_error, and only then adds the constraint. A lifecycle integration test deliberately applies through migration two, creates the old running state, then applies migration three and proves the job can be reclaimed.

The broader lesson is important: migration tests need both clean replay and representative upgrade state. “All migrations pass on an empty database” is necessary, but not sufficient.

Observability and useful alerts

Logs and metrics should describe logical incidents, not amplify delivery artifacts.

• Alert primarily on distinct SQL jobs in needs_attention.
• Treat poison queue depth as a secondary delivery/backlog signal because at-least-once publication can duplicate messages.
• Track oldest pending outbox age, not only row count; one ancient row can be hidden by a healthy average.
• Record dispatch attempts and redacted errors without logging file content, names, credentials, or malware payloads.
• Measure scan end-to-end latency from upload commit to terminal verdict.

The current development SLOs and exact operational signals live in docs/design/background-work.md.

Worked example: a scanner outage

Nurul uploads Invoice March.pdf while the scanner dependency is unhealthy.

1. The API transaction commits the document, immutable pending version, queued job, and scan-request outbox event.
2. The dispatcher publishes the event and marks it dispatched.
3. A worker locks the job, increments logical attempts, takes a lease, and moves the version to scanning.
4. The scanner call fails. Before the budget is exhausted, the worker returns the job to queued, saves a redacted error, clears the lease, and delays queue visibility for bounded backoff.
5. Later deliveries repeat from SQL truth. A newly delivered physical message does not reset the logical attempt count.
6. On final exhaustion, one SQL transaction fails the version closed, marks the job needs_attention, and inserts the needs-attention outbox event.
7. If the poison queue is down, the dispatcher records another failed dispatch and a future attempt time. The terminal decision remains true.
8. Reconciliation reports one distinct job needing attention and one pending poison delivery. When the queue recovers, the dispatcher publishes the stable event and marks it dispatched.

At no point does queue availability decide whether the file is safe to expose. Only a clean version is exposable.

Phase 3 implementation record

The delivery plan contains the authoritative checklist. This section explains the order and the reason behind it for someone implementing the work. Steps 1–6 are implemented as of 2026-07-24 and are kept here as design rationale.

1. Fix authority before adding features

Create a new forward migration; never rewrite the existing background migrations. Remove background UPDATE and scan-transition EXECUTE from the API role, then create the dispatcher, worker, report-only reconciler, and repair capability roles described above.

After the migration:

• make each entry point verify its required and forbidden effective permissions at startup;
• test through representative database users, not only through dbo;
• prove the API cannot call either scan transition even with a valid workspace context or a forged background session key;
• prove dispatcher, worker, reporter, and repair users can do only their own jobs.

A role-name assertion is not enough. Another role or direct grant could still supply a dangerous permission, so query effective permissions.

2. Fix crash exhaustion in the claim transaction

Do not bolt a second retry counter onto the queue. Factor one SQL exhaustion helper and call it from both graceful failure and the pre-claim budget guard. The helper owns the atomic version/job/outbox transition.

Test a hard defect repeatedly, expire the lease without waiting in real time, and assert that:

• the scanner is invoked no more than the configured maximum;
• the delivery after the final crashed attempt invokes no blob/scanner code;
• the version fails closed;
• the job becomes needs_attention;
• exactly one needs-attention outbox fact exists;
• poison-queue outage cannot undo terminal SQL truth.

Also test a duplicate while the final attempt still owns a live lease. That duplicate must back off instead of terminating work that may still succeed.

3. Make unsafe development scanning explicit — complete

The unsafe development-engine opt-in defaults to false. Local examples and test setup enable it deliberately and declare their environment class. Production rejects it even when someone sets the flag, and shared deployment classes forbid the flag.

Tests should cover missing opt-in, local opt-in, production rejection, unknown engine names, and the shared-environment deployment rule. A warning in documentation is useful, but startup failure is the authority.

4. Repair the health signals — complete

The report uses lease_until, not updated_at or started_at, to find abandoned running work, and includes the active-at/over-budget invariant count. Reporting runs without repair permission; the extra capability is required before --repair starts.

These checks are defense in depth. Correct claim logic should normally leave both counts at zero.

5. Shorten dispatcher transactions before adding consumers — complete

The dispatcher uses an expiring outbox claim token, commits before calling Azure, conditionally renews each row immediately before its send, and conditionally finalizes each result in a short transaction. A stale owner skips later rows reclaimed after the shared batch expiry. A crash leaves an expiring claim, and a send already in progress can remain a safe duplicate.

A useful test pauses the fake queue send, then proves another database transaction can continue and another dispatcher cannot steal the live claim. Also test death after send, claim expiry/reclaim during a multi-row batch, stale-owner send suppression, and partial batches.

6. Reconcile retained blobs cautiously — complete

The default CLI lists blobs with bounded continuation pages, compares them to committed version references under each workspace RLS context, and ignores blobs inside the commit-uncertainty grace period. It reports counts, oldest age, and bounded keys. --delete-orphan-blobs requires both the separate database marker and an Azure identity with Blob delete permission, rechecks the reference immediately before deleting, and conditionally deletes only the ETag that was listed. Report-only Blob inventory failures are logged and do not block the SQL outbox repair; explicit deletion failures remain fatal. The scan is O(N) in container objects and performs one exact-key DB check per old, valid-prefix blob, so operators should schedule it according to container size even though memory stays bounded.

Never infer “orphan” merely because an upload HTTP request returned an error: the database commit may have succeeded after the client lost its response.

Definition of done

Phase 3 did not finish merely because its happy-path scan succeeded. It is now complete because the wrong process cannot record a verdict, repeated hard crashes stop automatically, unsafe configuration refuses to start, reconciliation can prove the invariants, queue latency does not hold long SQL transactions before later consumers attach, retained blobs are handled safely, and the real SQL/Azurite upgrade and failure tests demonstrate those properties.

Code map

Debugging checklist

When a scan appears stuck, start from SQL truth:

1. Find the background_job and its workspace, status, attempts, lease, and redacted last_error.
2. Find its scan-request and needs-attention outbox events. Check status, dispatch_attempts, next_attempt_at, and dispatched_at.
3. Compare job status with the Document Version security status.
4. Run reconciliation in report-only mode before using --repair.
5. Check dispatcher logs and queue connectivity when outbox rows remain pending.
6. Check worker and scanner dependency logs when attempts increase.
7. Use queue depth as supporting evidence, not as the sole source of truth.
8. Never “fix” a terminal verdict with direct SQL. Follow the approved transition and incident procedure.

Extending the substrate for OCR or Automation

A new background consumer should answer these questions before shipping:

• What SQL row is the logical job authority?
• What transaction creates the job and its outbox fact?
• What stable identifiers belong in the message?
• What makes duplicate and reordered delivery safe?
• What prevents two workers from executing paid or destructive work at once?
• Which counter owns the retry budget?
• What are the terminal and needs_attention states?
• How is an exhausted known job transferred durably to operator attention?
• Which principal and tenant scope may read or write the data?
• What failures self-heal, what does reconciliation report, and what repair is safe without guessing?
• Which pre-existing database states can invalidate a migration?
• What SLO and alert tell an operator the system is unhealthy?

Do not create a private retry loop for each feature. Extend the shared job, outbox, lease, and reconciliation vocabulary deliberately.

The Automation dispatch worker is the first consumer to answer them by reuse rather than by writing its own runtime: it shares the claim, lease, durable attempt ceiling, early-wake rule, backoff, incident escalation, and queue contract, and adds only what is specific to it — planning one initiating event into durable run truth under a narrow migration-owned command. Two habits from that slice are worth copying:

• give the trigger a fact, not a side effect. Automation fires on document_version.clean, an immutable event the scan worker commits in the same transaction as the verdict, through an entry point that re-reads the status itself. Consumers subscribe to the fact; they do not inspect another feature's job state.
• open the read envelope only after job truth establishes the tenant. The worker reads nothing about a workspace until a background_job row has answered to the ids its message named, and every table it then reads is confined by row-level security to that workspace. A message is an identifier, never an authorization.
• fence what the decision is computed from, and fence all of it. The subject Document is locked before a fact is read, and the facts arrive as one payload carrying the digest of its own bytes — because a Document Type rename, a choice-option relabel, or a person rename changes what a Step would write without advancing any Document's revision. The durable command re-checks both and refuses (subject_changed) rather than recording an answer computed from a moment that has passed. Hash what the decision actually used, not a second read of the same rows: otherwise a value that changes and changes back leaves the digests agreeing about a state that never existed.
• take the lock through a command when the process may not hold the read. The Automation worker has no SELECT on document; the fence is a migration-owned command that proves job authority and returns a revision and a digest. Locking with a plain statement instead would have passed every test on a superuser development connection and failed every dispatch in production.

Common mistakes to avoid

• Treating the queue message as authorization or current state.
• Assuming “exactly once” because a happy-path test delivered once.
• Using one physical message's dequeue count as a logical retry budget.
• Checking the attempt ceiling only after a graceful error; hard process death cannot run that handler.
• Using mutable updated_at or started_at to detect a crash loop that refreshes both on every reclaim.
• Renewing queue visibility without renewing the SQL lease, or the reverse.
• Sending to another system and then assuming the second database write will certainly commit.
• Alerting on duplicate-prone queue copies instead of stable SQL job identity.
• Giving every process the same database or storage credential because the code ships in one package.
• Granting a narrow owner-executed procedure to a broad API role.
• Treating NODE_ENV as proof that an environment contains no real documents.
• Giving a worker broad table-update power for one narrow transition.
• Holding a database transaction open while waiting on avoidable external network work.
• Testing migrations only on empty databases.
• Logging raw document bodies or poison payloads.
• Treating an Accepted target ADR as proof that current code already conforms.

Decision and design sources

For changes, follow the repository's source-of-truth order:

1. accepted ADRs, especially ADR-0012 for queue/outbox invariants and ADR-0016 for service and capability boundaries;
2. cross-cutting design in docs/design/background-work.md;
3. schema, migrations, and current application code, which currently expose the conformance gaps called out on this page;
4. the Phase 3 plan for the ordered remediation and evidence checklist;
5. the decision register for still-open topics.

The production scan engine and Activity/audit contract remain separate owner decisions. ADR acceptance does not waive the Phase 3 real-document gates.