Design proposal: drive scans and imports from the web UI

[zbuc]

Design note for driving ingestion (the OAuth Gmail scan, and the Takeout mbox importer planned in #25) from the web viewer shipped under #11, with live progress. Targets main as of 5145995.

Summary

The web viewer gains the ability to start, watch, and cancel ingestion runs — both the existing OAuth scan and the new Takeout mbox import — without giving the viewer process any write access to stats.db and without changing how the CLI works standalone.

The core moves:

• The importer ships as a subcommand of the existing scanner binary (gmail_stats import <path.mbox>), reusing schema init, the single-writer db_writer task, and sender normalization directly. This also guarantees a Takeout-only DB is created in WAL mode, closing the known journal-mode caveat for the viewer.
• "Launch from the UI" = the web binary spawning that binary as a supervised child process. The viewer's DB pool stays read_only(true) + query_only=ON; its only new powers are spawn, signal-own-child, and hold a stderr ring buffer in memory.
• All durable progress flows through a new ingester-owned ingest_runs table, written via the existing single-writer channel. The browser polls it read-only. A run started from a terminal and a run started from the UI are indistinguishable to the progress UI — parity by construction.
• Mutual exclusion is a kernel flock on ./stats.db.ingest.lock, taken by the ingester itself at startup. Auto-released on any death: no stale-lock heuristics, no PID files as a liveness source, and it covers terminal-vs-web and web-vs-web double launches identically.
• The first state-changing endpoints get a layered CSRF defense (strict JSON content type, Origin/Sec-Fetch-Site checks, per-process random token in a custom header). The existing Host guard stops DNS rebinding but does not stop a hostile page firing a no-cors POST at 127.0.0.1:7878; that gap must be closed before any mutating route exists.

Chosen architecture, and why

browser ── polls ──► web viewer (axum, read-only pool, unchanged posture)
                        │  spawns / supervises (tokio::process, kill_on_drop=false)
                        ▼
                gmail_stats binary ── single db_writer task ──► stats.db (WAL)
                ├─ scan (default, no args = today's behavior)
                └─ import <path.mbox>        (new subcommand, #25)
                        │
                holds flock on ./stats.db.ingest.lock while running
                writes progress + state into ingest_runs (same writer channel)

Why subprocess + DB-mediated progress, specifically:

• Read-only viewer survives intact. Design proposal: web front-end for gmail-stats #11 resolved that the viewer performs no writes ever, not even CREATE INDEX. Every write in this design — including run bookkeeping — happens in the ingester process, through the existing single-writer task. The viewer never learns OAuth file paths either; auth problems surface as run-row states, not viewer knowledge.
• Lifecycle robustness. The child is spawned with kill_on_drop(false): a viewer crash or restart mid-scan is harmless — the child is reparented, keeps writing, and a restarted viewer picks the run back up from ingest_runs (it only loses the cancel button and log tail). The flock means a crashed ingester releases exclusion instantly, however it died.
• Progress parity. Because the ground truth is a DB row, a scan started with cargo run in a terminal shows identical live progress in the browser. No IPC channel, no "supervised vs. not" progress asymmetry.
• Smallest diff. The importer as a subcommand of src/main.rs shares init_schema, db_writer, SeenMail, get_sender/cleanup_sender with zero new modules. A src/lib.rs extraction can happen later if the code wants it; it is not a prerequisite.

Rejected alternatives:

• In-process jobs in the web binary (unified server): simplest IPC and cleanest auth-URL plumbing, but jobs die with the server, the web process becomes a DB writer (even if scoped to a job task), and cross-process exclusion against a terminal-launched scan degrades to "warnings plus busy_timeout". The lifecycle regression alone disqualifies it.
• Viewer writes a command row the ingester polls: requires viewer writes and a resident ingester daemon — two rule-breaks for no UX gain.
• A third supervisor daemon: real architecture for a multi-user system; overkill for one user on localhost.
• PID files for exclusion: PID reuse and stale-file heuristics; flock gives the same guarantee with kernel-enforced release.

Data model (all DDL owned by the ingester, per #11's resolution)

CREATE TABLE IF NOT EXISTS ingest_runs (
  run_id           INTEGER PRIMARY KEY AUTOINCREMENT,
  source           TEXT NOT NULL,      -- 'gmail_api' | 'mbox'
  state            TEXT NOT NULL,      -- starting|awaiting_auth|running|done|failed|cancelled|abandoned
  pid              INTEGER,            -- informational only; never signaled from the viewer
  started_at_unix  INTEGER NOT NULL,
  updated_at_unix  INTEGER NOT NULL,   -- heartbeat (every committed batch, at least every 2s)
  finished_at_unix INTEGER,
  messages_seen    INTEGER NOT NULL DEFAULT 0,
  messages_new     INTEGER NOT NULL DEFAULT 0,
  total_estimate   INTEGER,            -- gmail: users.getProfile messagesTotal; mbox: NULL
  bytes_total      INTEGER,            -- mbox: file size
  bytes_done       INTEGER,            -- mbox: committed parse offset (doubles as resume point)
  resume_token     TEXT,               -- gmail: last fully-processed page token (durable resume)
  mbox_path        TEXT,
  mbox_fingerprint TEXT,               -- "<size>:<mtime>" to validate resume against the same file
  error_kind       TEXT,               -- 'missing_credentials'|'policy_enforced'|'auth_required'|'rate_limited'|'io'|'parse'|...
  error            TEXT,               -- human-readable; rendered via textContent only
  auth_url         TEXT                -- set while state='awaiting_auth'
);

Progress updates ride the existing mpsc writer channel as a new message variant next to SeenMail, so single-writer discipline holds. Liveness is judged by the flock, not the row: an open row with a free lock means a crash, and the next ingester's startup marks such orphans abandoned. The heartbeat exists only so the UI can show a "stalled?" hint; it is never the exclusion mechanism.

Persisting resume_token and bytes_done here also fixes today's in-memory-only resume: a restarted scan can attempt its last page token (best-effort, silent fallback to page one — dedupe makes that cheap), and a half-finished import can seek to bytes_done after fingerprint validation.

Cross-source dedupe (the #25 wrinkle). API scans key seen_mails on Gmail's internal id; the mbox importer keys on RFC Message-ID. v1 does two things: (1) mbox rows are namespaced as mid:<Message-ID> so the two keyspaces can never collide accidentally and re-imports stay idempotent; (2) when ingest_runs shows both sources have ever completed against one DB, the API reports mixed_sources: true and the UI shows a persistent caution ("counts may include duplicates across scan + import"). The caution banner is interim only (maintainer decision below): true unification — a nullable indexed rfc_message_id column both ingesters check before incrementing sender counts — is committed as Phase D, and the banner is removed once a backfill has run. The mid: namespace convention in Phase A is accordingly formalized as the permanent keyspace rule so the #11 Phase-3 migration can build on it.

API surface

All routes sit behind the existing middleware (Host guard, CSP default-src 'self', nosniff, no-referrer). No CORS headers are ever emitted. Error bodies reuse the existing {"error": kind, ...} shape, including the missing_db/busy 503 contract.

• GET /api/summary — unchanged. The UI re-polls it during active runs so the table fills in live.
• GET /api/status — the single cheap poll target. Merges the read-only ingest_runs view, a flock probe (open + try-lock + release; detects "something is running" even with no row yet), and the in-memory child handle. Returns db: ready|missing|empty (drives onboarding), active_run, last_run, owns_active_run (false ⇒ hide Cancel/Log), mixed_sources, viewer-derived rate_per_sec/eta_seconds (sliding window over recent observations, no DB writes), and the per-process CSRF token. Never 5xx for a missing DB, so onboarding always renders.
• POST /api/runs — start a run. Body {"source":"gmail_api"} or {"source":"mbox","path":"/abs/path.mbox"} or {"source":"mbox","resume_run_id":N}. 202 {run_id} on spawn; 409 run_active (viewer pre-check for a friendly error — the child's own flock acquisition is the authoritative gate, so the TOCTOU window is harmless); 422 bad_path (must be absolute, regular, readable; checked by stat only); 403 CSRF; 500 spawn_failed (with a cargo build hint when the binary is missing). Failures inside the ingester (missing credentials.json, policy_enforced, parse errors) are not HTTP errors — they surface as state:failed + error_kind on the run row within seconds.
• POST /api/runs/{id}/cancel — 202; SIGTERM to the owned child, SIGKILL after 10s. The ingester's SIGTERM/SIGINT handler stops fetching, drains the writer channel, persists resume state, marks the row cancelled, exits 0 — so terminal Ctrl-C produces the same clean row. 409 not_owned for runs this viewer didn't spawn (UI says "cancel from its terminal"); killing by a DB-supplied pid is explicitly rejected (PID reuse).
• GET /api/runs?limit=20 — run history, straight from ingest_runs. Powers the history strip and the resume affordance.
• GET /api/runs/{id}/log — last ≤200 lines of the owned child's captured stderr ring buffer. Memory-only, never persisted, gone on viewer restart, textContent-rendered. Spawned children run quiet by default (below), so per-message sender lines never enter this buffer.

Ingester CLI additions: import <path> [--resume <run_id>]; bare invocation stays the scan, byte-for-byte compatible. Spawned runs pass --quiet (suppresses the per-message sender: ... stdout lines — also a privacy fix worth making the default, with --verbose restoring today's output). The viewer locates the ingester as a sibling of current_exe(), overridable via GMAIL_STATS_INGEST_BIN; it passes a fixed argv (never a shell) and nothing OAuth-related.

Progress mechanism: polling, deliberately

The UI polls GET /api/status every 1s while a run is active, decaying to 10s idle, pausing entirely on document.hidden; /api/summary refreshes every ~3–5s during a run.

SSE is not blocked by CSP — same-origin EventSource passes default-src 'self' via the connect-src fallback. Polling wins on the merits:

1. There is no push source to forward. Ground truth is a DB row written by a separate process; an SSE endpoint would be the server polling SQLite and re-emitting, plus reconnect/heartbeat logic on both sides.
2. Restart transparency. A restarted viewer's next poll simply tells the truth; a dead SSE stream needs client reconnect machinery.
3. Precedent. Design proposal: web front-end for gmail-stats #11 resolved "polling one JSON payload is enough" and anticipated exactly this scan-in-progress pill; one more polled JSON doc is the same idiom.
4. Scale is trivial: one user, ~600 bytes/s against a WAL DB whose readers don't block the writer; residual busy maps to the existing retryable 503 and the poller treats it as "try next tick", never as job failure.

Security

Threat model: hostile pages in the same browser (the confused deputy), hostile sender strings end-to-end, accidental metadata exposure. Unchanged and retained on every new route: 127.0.0.1 bind, Host-header guard, CSP default-src 'self' with everything embedded, nosniff, no-referrer, textContent-only rendering for every dynamic string (senders, error text, paths, log lines).

CSRF — required, and the Host guard is not it. A hostile page can fire a no-preflight POST (form or fetch with text/plain) at 127.0.0.1:7878 with a legitimate Host: 127.0.0.1. It cannot read the response, but it could trigger a scan or point the importer at an attacker-chosen local file (DB pollution). Every mutating route therefore requires all of:

1. Strict Content-Type: application/json (else 415). Forms cannot produce it; cross-origin JS setting it triggers a preflight that fails because no CORS headers are ever served.
2. Custom header token: X-Gmail-Stats-Csrf must equal a 32-byte CSPRNG token minted at viewer startup, obtainable only via the same-origin GET /api/status. The custom header independently forces a failing preflight; the random value holds even under degraded fetch-metadata support.
3. Origin / Sec-Fetch-Site: if Origin is present it must be http://127.0.0.1:<port> or http://localhost:<port>; if Sec-Fetch-Site is present it must be same-origin or none. Absent headers pass (curl is the same local user, who can already run the CLI).

Other state-changing surface:

• Spawn: fixed argv array, no shell, stdin null; the user string is only ever a single path argument. An attacker reaching the endpoint gains only what any local process already has.
• Path handling: stat-level validation is UX, not a boundary; the importer never echoes file content into any response, and a sanity sniff of the mbox From magic stops a confused click from slurping arbitrary files into sender stats. No directory-browsing endpoint — that would be genuinely new read surface.
• auth_url: originates from yup-oauth2, but the viewer still refuses to hyperlink it unless it parses as https://accounts.google.com/...; otherwise inert text. target="_blank" rel="noopener noreferrer".
• Viewer writes to stats.db: none. Files the viewer writes: none. It only probes the flock without truncating contents.

UI flows

Onboarding (db missing/empty) — replaces the bare setup page with two cards:

• Scan Gmail (OAuth): needs credentials.json in the repo root plus browser consent; a highlighted callout: "Using Advanced Protection? Google blocks this method outright (Error 400: policy_enforced) — use Takeout instead →". Start button.
• Import a Google Takeout mailbox: step list (takeout.google.com → Mail → mbox) with the warning that Google typically takes ~2 days to prepare the export for APP accounts; absolute-path text input (browsers don't reveal picked-file paths; multi-GB uploads are a non-goal) + Import button; 422 reasons render inline.

Progress panel (full-width during onboarding, collapsible banner above the table otherwise): source badge, state chip, messages_seen/messages_new, msg/s, determinate bar + ETA when a total exists (users.getProfile messagesTotal for scans; bytes_done/bytes_total for imports — an exact, free percentage), indeterminate "counting…" otherwise. State-specific slots: awaiting_auth shows a validated Authorize in Google link (the user consents in the very browser they're using; Google redirects to yup-oauth2's own ephemeral localhost listener; a GMAIL_STATS_AUTH_TIMEOUT, default 10 min, converts an unattended hang into failed/auth_required); rate-limit retries show an amber auto-retrying badge instead of looking frozen; failed shows remediation keyed off error_kind, with policy_enforced deep-linking to the Takeout card; stalled (heartbeat >15s, viewer-derived, display-only) offers Retry/Resume. Cancel and Log only for owned runs; non-owned runs say "started from the terminal — cancel with Ctrl-C there".

Live-filling stats view: the anticipated header pill ("● Scanning — 48,210 messages · 40/s"); summary re-fetches preserve all client state (search, page, hidden senders — already client-side); on busy 503 keep the previous table and dim the freshness stamp.

History strip: last runs from GET /api/runs; failed/cancelled imports with bytes_done > 0 get Resume import (fingerprint-validated; silent from-scratch fallback if the file changed — still correct via Message-ID dedupe). mixed_sources caution banner when applicable (interim — removed by Phase D dedupe).

All new DOM via createElement/textContent; no new asset files, no inline handlers; CSP untouched.

Failure and edge cases

Case	Detection	Behavior
Viewer restarted mid-run	child reparented (kill_on_drop=false); run row + held flock	status shows owns_active_run:false; progress continues; cancel/log unavailable
Double launch (any combination)	viewer pre-check 409; authoritative: child's flock acquisition fails, exits with a clear message	one ingester per DB, kernel-enforced, no stale locks
Ingester crash	flock freed + open row	UI shows outcome; next ingester marks row abandoned; per-message txns keep the DB consistent
Missing/invalid credentials.json	ingester writes failed/missing_credentials (panics converted to Results on this path)	remediation card, no HTTP error
APP account (policy_enforced)	OAuth error matched by ingester	dedicated failure kind; UI steers to Takeout
Consent never completed	awaiting_auth + auth timeout	failed/auth_required with Retry; cancel always available
Gmail rate limiting / 5xx	existing per-call + outer retry loops, now emitting progress notes	amber auto-retry; terminal only after existing retry budget, with resume_token persisted
Half-imported mbox	row with bytes_done	Resume with fingerprint check; full re-parse fallback stays correct via dedupe
Malformed mbox regions	parser skips to next valid From separator, counts skips (incl. missing Message-ID); handles >From escaping, CRLF, capped header lines	"N unparseable messages skipped" in the done summary
Fresh Takeout-only DB	shared connect options set WAL on create	viewer read concurrency guaranteed regardless of which ingester ran first
Old DB without ingest_runs	viewer treats no such table as "no runs"; ingester runs guarded DDL on startup	no manual migration step
busy during import bursts	existing 503 mapping	UI keeps last data, retries silently
SIGTERM ignored	10s escalation to SIGKILL	janitor marks abandoned

Incremental delivery plan

Each phase lands independently useful; both binaries keep working throughout.

• Phase A — importer + coordination primitives (CLI-only; closes Google Takeout mbox importer as an alternative to the Gmail API scan #25). import subcommand (streaming, header-only, bounded memory, mid: namespacing, byte-offset progress + fingerprint + --resume); flock acquisition in both modes; ingest_runs + heartbeat + persisted resume_token; SIGTERM/SIGINT clean shutdown; --quiet; a minimal schema-version mechanism (PRAGMA user_version, bumped by guarded migrations) seeded now so ingest_runs is migration 1 and Design proposal: web front-end for gmail-stats #11 Phase 3's messages table doesn't start from zero; --db/--credentials/--tokens flags (env fallbacks, current cwd-relative defaults) replacing the must-run-from-repo-root assumption; README with the Takeout how-to and ~2-day APP warning. Deliberately defers everything web: no new endpoints, no UI — but a terminal scan already writes live run rows and Ctrl-C yields a clean cancelled row with resume state. Exit criteria: multi-GB mbox imports in bounded memory; re-import adds zero counts; two concurrent ingesters provably refuse.
• Phase B — watch from the browser (read-only; zero new attack surface). GET /api/status + GET /api/runs, onboarding screen with cards showing the exact CLI commands to copy (launch buttons not yet live), progress panel in observe-only mode, live-filling table + pill, history strip. Exit criteria: start a scan in a terminal, watch it complete in the browser.
• Phase C — launch, cancel, and consent from the UI. POST /api/runs + /cancel + /log behind the full CSRF stack; spawn/ownership/reaper; onboarding buttons go live; awaiting_auth link flow; resume/retry buttons. Import lands before the OAuth job within the phase — APP users, the ones who cannot OAuth at all, get end-to-end value on the simpler job first. Exit criteria: click-to-ingest happy path for both sources; cross-site form/fetch attempts provably rejected in tests (415/403/409 matrices via tower::ServiceExt; fake-ingester script for supervise/crash/cancel).
• Phase D — cross-source exactness (committed) + polish. rfc_message_id column with dual-direction check-before-increment and the scan --backfill-message-ids repair pass are committed scope — they retire the mixed-sources banner, which is not acceptable as a permanent state. Optional polish: friendlier scanner startup errors; src/lib.rs extraction if the subcommand arrangement starts to chafe.

Decisions (maintainer, 2026-07-22)

1. Cross-source dedupe: the Phase-D rfc_message_id column is worth it; the mixed-sources warning banner is acceptable only as an interim state, not permanently. Phase D's dedupe work is committed scope, and Phase A's mid: namespacing is the formalized keyspace convention.
2. Schema versioning: yes — Phase A seeds the versioning mechanism; ingest_runs ships as migration 1.
3. Cancel for non-owned runs: the viewer's read-only rule is inviolable — no cancel_requested column, no viewer writes of any kind. Owned-only cancel stands; non-owned runs are cancelled from their own terminal.
4. Gmail resume-token longevity: open pending clarification — "advertise" here means whether the UI presents API-scan resume as a feature (a "Resume" button on failed scans) versus quietly attempting it. Recommended: show the button labeled best-effort ("Resume — may restart from the beginning"), with silent page-one fallback when the token is stale; correctness is unaffected either way since seen_mails dedupe makes re-listing idempotent.
5. Path flexibility: yes — promote stats.db/credentials.json/tokencache.json to flags with env fallbacks and current defaults (Phase A).
6. Windows: unix-only for now is acceptable (flock + SIGTERM); revisit only on demand.
7. Tuning knobs over HTTP: env-only. POST /api/runs accepts source + path/resume only; the child inherits the viewer's env.

[zbuc]

1. the Phase-D rfc_message_id column is worth it, the warning banner is not permanently acceptable
2. yes
3. don't break the read-only rule
4. "advertise"?
5. yes promote
6. yes
7. env-only

[zbuc]

Design updated with the decisions above — Phase D's rfc_message_id dedupe is now committed scope (the mixed-sources banner is explicitly interim and retired by the Phase-D backfill), Phase A gains the schema-version seed (PRAGMA user_version, ingest_runs = migration 1) and the --db/--credentials/--tokens flags, cancel stays strictly owned-only with zero viewer writes, unix-only stands, and run-tuning knobs stay env-only.

On question 4, "advertise" was jargon for: does the UI present API-scan resume as a visible feature (a "Resume" button on a failed/cancelled scan), or do we only attempt token reuse silently and never promise it? The tension is that Gmail page tokens aren't documented as long-lived, so a "Resume" button might in practice restart from page one. Recommendation now recorded in the body: show the button, labeled honestly ("Resume — may restart from the beginning"), with silent fallback; either path stays correct because seen_mails dedupe makes re-listing idempotent, so the only cost of a stale token is time. Say the word if you'd rather hide the button entirely and that line gets simplified.

[zbuc]

All four phases delivered and merged: A (#27 — Takeout importer + coordination primitives), B (#29 — observe-only web surface), C (#31 — launch/cancel/consent with the CSRF stack), D (#33 — rfc_message_id dedupe, backfill, banner retirement). Design fully implemented.