Design proposal: web front-end for gmail-stats

[zbuc]

gmail-stats Web Front-End — Final Design

Summary

A second binary target in the existing crate — src/bin/web.rs — that serves one embedded HTML page and one JSON endpoint, reading ./stats.db strictly read-only. Localhost-only, no auth, no JS toolchain, no new store, no config file. Roughly 150 lines of Rust plus ~250 lines of hand-written vanilla HTML/CSS/JS, committed as two source files. One net-new dependency: axum.

This synthesizes three independent design proposals. The winning base is the "radical simplicity" design (separate bin + single JSON payload + client-side rendering). Grafted in from the runners-up: a mandatory security hardening set (Host-header DNS-rebinding guard, CSP, textContent-only rendering of sender strings), UX touches (Gmail deep links, CSV export with injection escaping, scan-in-progress awareness, friendly setup page), and an honest, explicitly-deferred roadmap for trends-over-time that requires collector/schema changes.

Goals / Non-goals

Goals

• View per-sender counts, domain rollups, and total distinct messages in a browser instead of the sqlite3 shell.
• Zero-install for someone who already runs the CLI: cargo run --bin web, open the printed URL.
• Localhost-only, read-only, fully self-hosted on the developer's machine. No cloud, no hosted services, no CDN fetches — every byte served is compiled into the binary.
• Safe to run while the scanner CLI is mid-scan.
• Minimal maintenance surface: no JS framework/bundler/npm, no migrations, no state of its own, no coupling to parse_messages/get_sender internals (only the two-table schema).
• Survive the schema's looseness: duplicate senders rows, empty-string senders, missing tables, absent stats.db.
• Zero merge surface with open PRs Address cargo run warnings #1/Refactor get_sender(): single case-insensitive pass over a header priority list #6/Fix retry loop in main(): bounded retries, backoff, error classification #7/Make message fetching concurrent with rate limiting and deadlock-free SQLite writes #8, which are all rewriting src/main.rs.

Non-goals

• No auth/multi-tenancy/TLS (single user, loopback only, by construction).
• No writes of any kind — no "delete sender", no re-scan trigger, no OAuth flow in the browser, not even CREATE INDEX.
• No dates/subjects/labels in v1: the data model is counts-only. Trends are a documented later phase that starts with a collector change (see delivery plan), not a frontend feature.
• No live push/websockets; polling one JSON payload is enough.
• No packaging beyond cargo run.

Chosen architecture (and rejected alternatives)

Process model: a new binary target src/bin/web.rs in the existing gmail_stats crate. Separate process from the scanner; the DB file is the only interface between them. Binds hardcoded 127.0.0.1, port from std::env::args().nth(1) (default 7878) — no CLI-arg crate, matching the flag-less style of the main binary.

Data path: read-only sqlx pool over sqlite://./stats.db (cwd-relative, same convention as the CLI — run from the repo root). GET /api/summary runs two queries and returns one JSON document. No caching: both queries are milliseconds at current scale (~7.4k senders, ~145k seen ids max).

Frontend delivery: one web/index.html (vanilla HTML/CSS/JS, no external assets) embedded via include_str! and served at GET /. The browser fetches /api/summary once and does all sorting/filtering/aggregation client-side — distinct senders number in the thousands, comfortably one ~300 KB payload. New views (domains tab, CSV export) need zero server changes.

Rejected alternatives:

• serve subcommand in the main binary + module refactor — tangles the frontend into main.rs exactly while PRs Refactor get_sender(): single case-insensitive pass over a header priority list #6/Fix retry loop in main(): bounded retries, backoff, error classification #7/Make message fetching concurrent with rate limiting and deadlock-free SQLite writes #8 rewrite it; the refactor alone guarantees merge conflicts. A separate bin has zero conflict surface. (Its security posture is adopted wholesale, though.)
• Server-rendered askama pages — more routes, more templates, a template dependency, and pagination/sort state in query params, to produce the same UI a single client-side page gives for free at this cardinality.
• Vendored Chart.js dashboard — a 200 KB third-party JS blob in the repo for bar charts that <div>s with percentage widths draw fine. Deferred; can be revisited if a trends view (phase 3) genuinely needs time-series tooltips.
• Static HTML + sql.js (WASM) reading stats.db in-browser — ~1 MB WASM blob, a file-picker/copy step, and unlocked reads of a possibly mid-write DB.
• Dump-to-JSON script + static page — always stale, and a second thing to maintain.

Stack

• axum 0.8 (net-new, the only addition) — two routes. Its hyper/tower tree is already substantially present via google-gmail1's re-exports. (tiny_http was considered but would force a second SQLite driver like rusqlite, risking a libsqlite3-sys links-collision with sqlx — reusing sqlx keeps one driver in the tree, which decides it.)
• sqlx 0.9, tokio 1.53, serde_json 1, anyhow 1 — already dependencies; reused as-is. Responses built with the json! macro, no derive structs needed.
• Frontend: zero dependencies. Hand-written HTML/CSS/JS in one file. System font stack, prefers-color-scheme dark mode, bars as styled <div>s.

Data access

Connection setup (the load-bearing lines):

let opts = SqliteConnectOptions::new()
    .filename("./stats.db")
    .read_only(true)                    // SQLITE_OPEN_READONLY — enforced by sqlite, not convention
    .pragma("query_only", "ON")         // belt and suspenders
    .create_if_missing(false)           // never create an empty DB by accident
    .busy_timeout(Duration::from_secs(5));
let pool = SqlitePoolOptions::new().max_connections(2).connect_with(opts).await?;

GET /api/summary returns:

{
  "total_messages": 145096,
  "senders": [
    {"sender": "news@example.com", "mails_sent": 3121},
    {"sender": "", "mails_sent": 12}
  ],
  "generated_at": "2026-07-21T18:04:11Z"
}

Backing SQL — defensive by design, because the schema has no constraints, no column affinity ("string"/"int" are not real SQLite types), and a concurrency history that makes duplicate rows plausible:

-- total_messages: seen_mails has no PK/unique, so count distinct (matches the README's own query)
SELECT COUNT(DISTINCT mail_id) FROM seen_mails;

-- senders: the app assumes one row per sender but nothing enforces it, so merge here.
-- CAST guards the affinity-less mails_sent column against stray TEXT values.
SELECT sender, SUM(CAST(mails_sent AS INTEGER)) AS mails_sent
FROM senders
GROUP BY sender
ORDER BY mails_sent DESC;

These queries remain correct (just faster) if PR #8 later adds UNIQUE constraints or indexes, so the frontend need not wait for it.

Error behavior:

• Missing stats.db or missing tables → not a bare 500: the page renders a friendly setup state with the exact CREATE TABLE commands from the README and a retry button.
• SQLITE_BUSY after the 5 s timeout → HTTP 503 with {"error": "database busy — scanner is writing, retry shortly"}; the page shows a retry banner. (Journal mode is currently delete; the scanner's write transactions are tiny, so this path should be rare, and becomes vestigial if PR Make message fetching concurrent with rate limiting and deadlock-free SQLite writes #8 enables WAL.)
• Any other path → 404. No other routes exist.

UI views

One page, rendered entirely from the single /api/summary payload:

1. Summary strip — stat tiles: Total messages (distinct seen_mails), Distinct senders (post-merge array length), Top sender; a "data as of {generated_at}" line; a Refresh button; an optional auto-refresh checkbox (30 s). When auto-refresh is on and total_messages grew since the last fetch, show a subtle "Scan in progress (+N)" pill — inferred purely from the numbers moving, zero collector coupling.
2. Senders table (the core view) — columns: Sender, Messages, % of total, inline horizontal bar scaled to the max count, and an "Open in Gmail" link per row (https://mail.google.com/mail/u/0/#search/from:<urlencoded sender>, target="_blank" rel="noopener noreferrer") — a pure outbound link, no OAuth involvement, and the single most useful action ("who is this?"). Default sort: messages descending; click headers to toggle; a filter box does client-side case-insensitive substring match. Rows render in pages of 500 with a "show all" link. The empty-string sender renders as (no sender header), never a blank cell; a footer notes "N duplicate sender rows merged" when the GROUP BY collapsed anything — a cheap data-quality signal for the scanner.
3. Domains rollup (toggle tab, same table chrome) — client-side group-by on the substring after the last @ (no-@ values bucket as (unparsed)). Same columns/bars; ~15 lines of JS, no server change. Often the most actionable view for inbox cleanup.
4. Download CSV — client-side Blob from the already-loaded array. Cells starting with =, +, -, or @ are prefixed with ' (CSV/formula-injection escaping — sender strings are attacker-controlled, see Security).

That's the whole UI. The data model has nothing else to show; anything richer is scanner work first (phase 3).

Security

The DB is a map of who emails the user, and the working directory contains credentials.json (OAuth client secret) and tokencache.json (live inbox-read tokens). Treat the whole app as sensitive-by-default:

• Loopback only, structurally. Bind hardcoded 127.0.0.1 — not 0.0.0.0, not configurable. The port is the only knob.
• No filesystem serving. No static-file route, no path parameters; the HTML is include_str!-embedded. credentials.json/tokencache.json/stats.db are unreachable by construction — the path-traversal bug class cannot exist. The web binary never reads or knows the paths of the OAuth files, period.
• Read-only DB handle at the connection level (read_only(true) + query_only pragma + create_if_missing(false)), not by convention — the viewer cannot corrupt a mid-scan DB even via a bug, which matters while PR Make message fetching concurrent with rate limiting and deadlock-free SQLite writes #8 is changing write behavior.
• Sender strings are attacker-controlled input. They come from arbitrary From: headers, and cleanup_sender() falls back to the raw header value on parse failure — any sender on the internet can put <script> in this table by sending one email. Hard rules: the JS inserts sender values only via textContent/attribute-safe APIs, never interpolated innerHTML; CSV export escapes formula-leading characters; Gmail links URL-encode the address.
• DNS-rebinding guard (mandatory, ~3 lines): reject requests whose Host header isn't 127.0.0.1[:port] or localhost[:port] with 403. Closes the classic attack where a malicious site rebinds its domain to loopback and reads the stats through the victim's browser.
• Defense-in-depth headers: Content-Security-Policy: default-src 'self' (trivially satisfied — everything is same-origin and embedded), X-Content-Type-Options: nosniff, Referrer-Policy: no-referrer. No CORS headers ever (same-origin only).
• No secrets or payload data in logs: log method + path + status only; never log filter/search terms.

Incremental delivery plan

Phase 1 — walking skeleton (~half a day; ships value alone):
Cargo.toml (+axum), src/bin/web.rs with the read-only pool, /api/summary, the Host-header guard and security headers, and an index.html rendering the summary strip + a count-descending table. Missing-DB setup page. This already replaces the sqlite3 shell entirely.

Phase 2 — table ergonomics + rollups:
Client-side filter, sortable headers, % column and bars, (no sender header) labeling and duplicate-merge footer, 500-row paging, Gmail deep links + copy button, domains tab, CSV export (with injection escaping), refresh button, BUSY-retry banner, auto-refresh + scan-in-progress pill. README section documenting cargo run --bin web.

Phase 3 — trends over time (deferred; requires collector + schema changes, coordinate with PR #8):
Today's data physically cannot answer "when". The honest bill, owned by the collector, not this frontend:

• New table messages (mail_id TEXT PRIMARY KEY, sender TEXT NOT NULL, internal_date INTEGER NOT NULL) + indexes — populated from the internalDate the collector already fetches and discards (one extra INSERT in the existing per-message transaction, no extra API calls for new mail). Ideally the repo's first real migration.
• A resumable backfill mode for existing history: one messages.get (metadata format) per already-seen id; at Gmail's ~250 quota units/s (5 units/call ≈ 50 msg/s) that's an hours-long one-time run for 76k–145k messages. PR Make message fetching concurrent with rate limiting and deadlock-free SQLite writes #8's concurrency helps.
• Then the web UI grows an Activity view (messages/month, per-sender first-seen/last-seen, new-senders-per-month, "cleanup candidates": active firehoses vs. long-dead senders), degrading gracefully with a coverage indicator while backfill is partial. A vendored chart library can be reconsidered here if <div> bars stop sufficing.

Each phase is independently shippable; phases 1–2 touch only Cargo.toml, src/bin/web.rs, and web/index.html — zero conflict surface with PRs #1/#6/#7/#8.

Open questions for the maintainer

1. PR Make message fetching concurrent with rate limiting and deadlock-free SQLite writes #8 schema fallout — if it adds UNIQUE/PK constraints or WAL, the defensive GROUP BY/DISTINCT become no-ops and the 503 path becomes vestigial; nothing here needs to change, but re-check the SQL when it lands. Should Make message fetching concurrent with rate limiting and deadlock-free SQLite writes #8 also own CREATE INDEX/uniqueness so the viewer stays purely read-only forever? (Proposed: yes.)
2. Where does sender-count truth live if duplicate rows exist — fix the scanner post-Make message fetching concurrent with rate limiting and deadlock-free SQLite writes #8 to guarantee uniqueness? The frontend keeps its merge regardless (cheap insurance) and surfaces the duplicate count as a nudge.
3. Empty-string senders — lumped as one (no sender header) row. If the count is large it argues for scanner-side header-parsing work (PR Refactor get_sender(): single case-insensitive pass over a header priority list #6 territory), not frontend work.
4. Auto-open the browser? The open crate is one small dep to pop a tab on start. Default answer under this design's lens: no — print the URL.
5. Port default — 7878 proposed; anything uncommon works. Pick before the README section is written.
6. Phase 3 timing — green-light the messages table design now (so PR Make message fetching concurrent with rate limiting and deadlock-free SQLite writes #8 can account for it), or wait until phases 1–2 prove the tool gets used?