Preserve failure notices and outcome record (#3851)

Every Preserve (in-place flyer tagging) job that ends without success sends one admin email. Every terminal job, including successes, gets its outcome and a snapshot of its file characteristics recorded on the job row. That makes “what kinds of files fail?” a SQL question, so nobody has to search the inbox.

How it works

workers/api/src/services/preserve-outcome-scan.ts runs every 5 minutes on the EC2 API leader (server.ts singletons, next to the flyer stale sweep). Each tick:

It reads terminal flyer_tagging_jobs rows (status in (done, failed)) where outcome_recorded_at IS NULL. Rows must have been terminal for at least 2 minutes. At most 25 rows are read per tick.

It classifies each row with classifyPreserveOutcome, which uses the refund policy’s own isFidelityFailure and isSkippedRow:

`outcome_class`	Row shape	Email?
`success`	`done`, `passed = true`	no
`compliance_only`	`done`, `passed = false`, every failure is a known compliance-only string	yes
`fidelity`	`done`, `passed = false`, any other failure (pixel, qpdf, unknown)	yes
`not_taggable`	`done`, `passed IS NULL`, `error LIKE 'skipped: %'`	yes
`pipeline_error`	`failed`, including stale-sweep kills; also a `done` row with no verdict	yes
`cancelled`	`failed` with a user-cancel or source-deleted error	no

It records the row with a PATCH guarded on outcome_recorded_at=is.null. The PATCH writes outcome_class, outcome_facts and outcome_recorded_at. If the guarded PATCH matches no row, another tick got there first and the job is skipped. A row that is going to be emailed is recorded straight into notice = 'sending' with notice_attempts = 1, so recording it is also its first send claim.
It sends the email. On success it stamps failure_notified_at and sets notice = 'sent'. On failure it sets the notice back to pending, or to failed once notice_attempts reaches 3.
A retry pass picks up pending notices, plus sending notices whose claim (notice_claimed_at) is more than 15 minutes old. A stale sending claim means the process crashed or the sent-stamp PATCH failed. Each retry is a compare-and-swap PATCH guarded on the current notice and notice_attempts, so two processes can never both send the same notice. A notice is sent at most 3 times, never in an unbounded loop.
Hourly cap: at most PRESERVE_FAILURE_NOTIFY_MAX_PER_HOUR individual notices (default 12) go out per rolling hour. Failures over the cap are recorded as notice = 'digest'. At most once an hour, one summary email covers all waiting digest rows: counts by class, top failure and error strings, scanned vs text, and producers.
- Before sending, the scan claims the digest rows by stamping failure_notified_at with a PATCH guarded on failure_notified_at IS NULL. It claims in chunks of at most 50 ids.
- Only rows this process won go into the email, so no row is summarized twice. The claim is also what closes the once-an-hour gate.
- If the send fails, the scan releases the claim, guarded on its own stamp, and the next tick retries.
- If the sent-count read fails, the scan assumes the budget is spent, so notices go to the digest rather than flood.
- A pending retry that the budget defers is moved to digest. It is not left waiting until the lookback window drops it.

Notice states in outcome_facts.notice:

State	Meaning
`none`	No email wanted (success, cancelled).
`backfill`	The failure is older than the lookback window, so it was recorded silently.
`sending`	Claimed for a send.
`pending`	A send failed and a retry is waiting.
`sent`	Delivered.
`failed`	Given up after 3 attempts.
`digest`	Over the hourly cap; covered by the summary email.
`unconfigured`	No recipient was configured. The scan logs this once per tick and doesn’t retry.

On the first run, the scan records all past jobs as backfill, so it sends no email for history.

Migration 338 also changes the updated_at trigger. It keeps updated_at only when all three conditions hold:

the row was already terminal (done or failed);
nothing outside the four outcome columns changed;
at least one outcome column did change.

Every other write bumps updated_at as before. That includes the tagger’s same-value {"status":"processing"} heartbeat PATCH, which the stale sweep depends on. supabase/tests/flyer_outcome_record.test.sql covers this and runs in the db-tests workflow.

To check the heartbeat by hand against a database that has migration 338 applied (inside a transaction you roll back):

BEGIN;
UPDATE flyer_tagging_jobs SET status = 'processing'
  WHERE id = '<a processing job>' RETURNING updated_at;  -- must be now()
ROLLBACK;

Why a DB scan and not a hook

Four writers can put a row into a terminal state: the tagger Lambda, the host lane, the stale sweep, and cancel or file-delete. On the API side, only the GET /jobs/:id poll sees that terminal state, and the poll only runs while the user keeps the tab open. A scan for unrecorded terminal rows catches all four writers, survives restarts, and needs no change to any of them.

Configuration (EC2 anchor: `infra/anchor/anchor.config.env`)

Variable	Default	Meaning
`PRESERVE_FAILURE_NOTIFY_ENABLED`	off in code, `true` on the anchor	Master switch. When off, the scan records nothing and sends nothing.
`PRESERVE_FAILURE_NOTIFY_TO`	falls back to `ALERT_EMAIL`	Recipient address.
`PRESERVE_FAILURE_NOTIFY_LOOKBACK_HOURS`	`24`	Failures older than this are recorded without an email. Pending retries stop after this long.
`PRESERVE_FAILURE_NOTIFY_MAX_PER_HOUR`	`12`	Individual notices per rolling hour. Failures over the cap go into one hourly digest email.
`PRESERVE_FAILURE_ADMIN_URL_TEMPLATE`	unset	Optional link in the email. `{jobId}`, `{fileId}` and `{userId}` are substituted. No admin page exists yet for a single job or file.

To turn the scan off, set PRESERVE_FAILURE_NOTIFY_ENABLED=false and recreate the API container.

Email goes through the shared send path (sendAdminAlertEmail, then sendEmail in services/email-sender.ts), with SES as primary and Resend as failover. The email is sent in the alert category.

What gets recorded (`outcome_facts`)

The snapshot contains no PII: no file name, no email, no user id. Most fields come from the tagger’s preflight (flyer_tagging_jobs.preflight). Some come from the source files row.

Key	Source
`pages`, `pages_source`	Taken from `preflight.signals.page_count`. If that is missing, from `progress.pagesTotal`. If that is missing too, from `charge_units` (billing is one unit per page).
`bytes`, `mime_type`	`files.size` and `files.mime_type`. Null for raw-key jobs or deleted files.
`producer`, `creator`, `pdf_version`	`preflight.signals`. Recorded only from the tagger release that ships with #3851.
`mode`, `scanned`, `ocr_kind`	`preflight.mode`. `ocr` means scanned or image-only.
`tree_state`, `already_tagged`	`preflight.tree_state`: `absent`, `present-and-usable` or `present-and-broken`.
`route`, `eligible`, `design_dense`	Preflight routing verdict.
`widget_count`, `has_xfa`, `unembedded_fonts`, `substituted_fonts`, `text_line_count`, `raster_coverage`	`preflight.signals`.
`input_audit_score`, `input_audit_band`, `input_audit_already_accessible`	`files.input_audit_*`.
`lane`, `rerouted`, `force`, `flatten`, `source`	Job options.
`prior_attempts`	Earlier Preserve jobs by the same user on the same file (or the same input key).
`duration_seconds`	Time from `created_at` to the terminal write.
`failures`, `error`	The gate failure strings, capped at 20, and the pipeline error, truncated. `error` and every `failures` string are redacted: e-mail addresses, URLs, multi-segment paths, document file names and UUIDs are replaced. Users can read their own job rows through RLS, and the raw text stays in the `error` and `failures` columns and the admin email. All stored strings are truncated by code point, so an emoji at the cut can’t leave a lone surrogate that jsonb rejects.
`notice`, `notice_attempts`, `notice_claimed_at`	Notice state (see above), the number of send claims, and the time of the latest claim.

The tagger now also writes preflight to the row as soon as preflight finishes (_record_preflight in deploy/handler.py). Before this change, a job that errored after preflight ended with preflight = NULL. In the history up to 2026-09-28, 15 of the 16 failed rows (14 pipeline errors and 2 cancellations) had no preflight, so their characteristics are unknown.

Summary queries

These queries are read-only and safe to run against prod.

-- Outcome mix
SELECT outcome_class, count(*),
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
FROM flyer_tagging_jobs
WHERE outcome_class IS NOT NULL
GROUP BY 1 ORDER BY 2 DESC;

-- Failure rate by a characteristic (swap the dimension expression)
SELECT coalesce(outcome_facts->>'scanned', '?')          AS scanned,
       count(*)                                          AS jobs,
       count(*) FILTER (WHERE outcome_class = 'success') AS ok,
       round(100.0 * count(*) FILTER (WHERE outcome_class NOT IN ('success','cancelled')) / count(*), 1) AS fail_pct
FROM flyer_tagging_jobs
WHERE outcome_class IS NOT NULL
GROUP BY 1 ORDER BY jobs DESC;
-- other dimensions:
--   outcome_facts->>'producer'
--   outcome_facts->>'tree_state'
--   CASE WHEN (outcome_facts->>'pages')::int <= 1 THEN '1'
--        WHEN (outcome_facts->>'pages')::int <= 4 THEN '2-4'
--        WHEN (outcome_facts->>'pages')::int <= 10 THEN '5-10'
--        WHEN (outcome_facts->>'pages')::int <= 32 THEN '11-32'
--        ELSE '33+' END
--   CASE WHEN (outcome_facts->>'bytes')::bigint < 250000 THEN '<250KB'
--        WHEN (outcome_facts->>'bytes')::bigint < 1000000 THEN '250KB-1MB'
--        WHEN (outcome_facts->>'bytes')::bigint < 5000000 THEN '1-5MB'
--        WHEN outcome_facts->>'bytes' IS NULL THEN '?' ELSE '5MB+' END

-- Class x producer (top producers among failures)
SELECT outcome_class, coalesce(outcome_facts->>'producer', '(unknown)') AS producer, count(*)
FROM flyer_tagging_jobs
WHERE outcome_class IN ('pipeline_error','fidelity','compliance_only','not_taggable')
GROUP BY 1, 2 ORDER BY 3 DESC LIMIT 30;

-- Top failure strings, digits normalised
SELECT regexp_replace(f, '\d+(\.\d+)?', 'N', 'g') AS failure, count(DISTINCT id) AS jobs
FROM flyer_tagging_jobs, jsonb_array_elements_text(outcome_facts->'failures') AS f
WHERE outcome_class IN ('fidelity','compliance_only')
GROUP BY 1 ORDER BY 2 DESC;

-- Pipeline errors, normalised
SELECT left(regexp_replace(outcome_facts->>'error', '\d+', 'N', 'g'), 140) AS error, count(*)
FROM flyer_tagging_jobs WHERE outcome_class = 'pipeline_error'
GROUP BY 1 ORDER BY 2 DESC;

-- Notices not delivered (pending/sending should drain; failed = gave up)
SELECT outcome_facts->>'notice' AS notice, count(*)
FROM flyer_tagging_jobs
WHERE outcome_facts->>'notice' IN ('pending','sending','failed','unconfigured','digest')
  AND failure_notified_at IS NULL
GROUP BY 1;

Deploy order

Apply migration 20260928000000_338_flyer_outcome_record.sql. Migrations are applied manually after merge.
Deploy the EC2 API. The flag is already on in anchor.config.env. If the API ships before the migration, the scan’s reads fail with PostgREST 400 and each tick logs Preserve outcome scan error. Nothing else is affected.
Deploy the flyer-tagger Lambda and host image. This step adds the early preflight write and the producer, creator and PDF version signals. Until it ships, those fields stay null for new jobs, and pipeline errors keep having no preflight.