RFC: Ingester ack in Postgres; `failed/` only for bad submissions

Start by tracing ingest_submissions_parallel and monitor_submissions, then inspect transaction.atomic, assign_lab_ids, the existing counter_lock, and writer.exitcode handling. Define the schema and role grant before implementing atomic fact and applied-row writes, spool acknowledgements, retry stopping, and quarantine behavior; done means the stated outcomes hold for each error case.

The Problem

Valid submissions passed schema and log-excerpt upload. Database insertion then failed. The ingester moved the whole batch to failed/ — the directory for invalid data. That directory was cleaned up, so good data was treated as poison.

Rules

failed/ — invalid JSON and errors of the submission itself. Cases where retry cannot help.

DB insert / flush failure — files are not moved and nothing is acked. The spool is the retry queue; fact inserts are upserts, so a later attempt is safe.

Applied — a row in ingested_submissions, inserted in the same transaction.atomic() as the facts (assign_lab_ids moves into that transaction so "applied" is one boundary). The file is deleted only after that commit.

hash         text PK       -- sha256 of raw file bytes
file_name    text
_timestamp   timestamptz   -- `field_timestamp`

Scan deletes any file whose hash is already present, which covers a crash between commit and unlink.
Flush writes all entity buffers in one commit and acks exactly the hashes in it, so no file is finalized while its rows are still buffered.

Stop on stuck retry — one shared multiprocessing.Value("d") holds the last time a submission reached a terminal state, updated under the existing counter_lock on an apply commit and on a quarantine. Checked only while work is pending, in the poll loop of ingest_submissions_parallel and the monitor loop of monitor_submissions. If nothing has reached a terminal state for T, the process exits non-zero; files stay in the spool. Existing writer.exitcode handling stays for crashes; the timestamp covers hangs and persistent errors, which never raise.

If a file is kept on spool dir after for a long enough grace period of time, we can abort and raise an error on the ingester.

New table: GRANT the ingester role before shipping the writer.