Findings Loader — CSV → Supabase Postgres

The file-driven loader (python -m scanner.load) reads a scanner findings CSV and loads it into the Supabase [[supabase-findings-store|public.bsc_finding]] table — the durable replacement for the earlier, error-prone hand-transcription of INSERTs through the Supabase MCP. Built test-first across 6 subagent-driven tasks (two-stage review each + a final holistic review: Ready). Full suite 89 passed, 4 deselected. Built, reviewed, and committed (c5cec6e, pushed to main) 2026-05-31.

For Agents

• Module: scanner/load.py. CLI: python -m scanner.load --csv <path> --run-label <label> [--replace] [--allow-empty] [--dry-run].
• Connection: reads DATABASE_URL from env (never committed; .env/.env.* gitignored). Must be the Supabase Session Pooler DSN with ?sslmode=require.
• Score is carried in the CSV now, not recomputed — see The score moved into the CSV. scanner/triage.py CSV_COLUMNS gained a trailing score.
• Idempotency = replace-by-run_label, and replacing a populated label requires --replace (daily date-labels never trip it).
• Historical CSVs in output/ are rejected (they predate the score column) — see the gotcha callout below.
• Dependency: psycopg[binary]>=3.2,<4.

What it does

Three units in scanner/load.py, each independently tested:

• build_rows(csv_path, run_label) -> list[tuple] — pure CSV → insert-tuples. Validates the header (missing/duplicate required column → error), casts each row (line/age_days→int, tvl_usd/score→decimal.Decimal, empty→NULL), strips + validates the NOT-NULL identity columns (chain/contract_address/detector; blank → row-numbered error), injects run_label. Reads score from the CSV — never recomputes, never builds Finding objects, never re-runs triage.
• replace_run(dsn, run_label, rows, *, allow_empty, replace, dry_run) — atomic “replace by run_label” in one psycopg3 transaction: advisory lock → count → gates → DELETE → executemany insert.
• main(argv) — CLI wiring + exit codes.

CLI

export DATABASE_URL='postgresql://postgres.<ref>:<pwd>@aws-<region>.pooler.supabase.com:5432/postgres?sslmode=require'
python -m scanner.load --csv output/run.csv --run-label batch-2026-06-01 --dry-run   # preview, writes nothing
python -m scanner.load --csv output/run.csv --run-label batch-2026-06-01             # load (new label)
python -m scanner.load --csv output/run.csv --run-label batch-2026-06-01 --replace   # overwrite a populated label

--csv and --run-label are required — no date default, so timezone drift and accidental label collisions are impossible.

Connection (verified)

Use the Supabase Session Pooler, with SSL

Set DATABASE_URL to the Session Pooler DSN: IPv4, port 5432, username postgres.<project-ref>, host aws-<region>.pooler.supabase.com, with ?sslmode=require.

• Not the direct endpoint (db.<ref>.supabase.co:5432) — it is IPv6-only without the paid add-on and times out in most amd64 Docker/CI networks.
• Not the transaction pooler (:6543) — it breaks psycopg3 prepared statements.
• The loader connects with prepare_threshold=None, so it is safe even if the DSN is later repointed at the transaction pooler.

The score moved into the CSV

Loader is a pure transport; score is computed once, at scan time

Previously the plan was to recompute score at load. That was changed: scanner/triage.py now appends "score" to CSV_COLUMNS, so the ranked CSV carries the score triage.triage() already computed (SEV × CONF × tvl_usd). The loader copies that value verbatim into bsc_finding.score.

Why: avoids refactoring triage.score (and its exact-value tests), removes a config dependency from the loader, and eliminates scan-vs-load score drift. Trade-off: re-ranking after a weight change needs a rescan, not a bare reload — fine, since the daily pipeline rescans anyway. This supersedes the “computed at load time” framing in supabase-findings-store.

The 16 CSV columns + the injected run_label = the 17 INSERT columns; the build_rows tuple order is asserted against INSERT_SQL by a full-tuple happy-path test.

Safety model

Guards against the destructive-reload and silent-wipe footguns

• Replacing a populated label requires --replace. A first load to a new label just needs --run-label; only re-running an existing label needs the explicit flag. Cron passes new date-labels daily, so it never trips.
• Refuses a 0-row load unless --allow-empty — prevents a header-only CSV from a failed scan silently wiping a batch.
• --dry-run connects read-only and reports “would delete N / would insert M” (and flags when the real run would need --replace).
• Advisory lock (pg_advisory_xact_lock(hashtext(run_label))) serializes concurrent same-label loads.
• Atomic: all work runs in the plain with psycopg.connect(dsn, prepare_threshold=None) as conn: block (commits on clean exit, rolls back on exception). It deliberately does not use with conn.transaction(): — on a non-autocommit connection that degrades to a savepoint and silently fails to commit (a verified psycopg3 trap).
• No secret leak: the DSN/password is never printed; DatabaseError carries only host/dbname (via conninfo_to_dict) + the error class name.

Exit codes

Code	Meaning
0	success, or any --dry-run
2	usage/input errors — missing DATABASE_URL, missing/​unreadable CSV, missing/duplicate required header, non-numeric line/age_days/tvl_usd/score, blank NOT-NULL column, refuse-empty, refuse-needs---replace
1	database error after connecting

Testing

• Pure build_rows/replace_run/main tests run in the normal suite (fakes + capsys/monkeypatch, no DB).
• A single live round-trip test is behind a dedicated db pytest marker and skipif(not DATABASE_URL) — deselected by default (addopts = "-m 'not integration and not db'"), so CI stays hermetic. (The db marker is separate from integration, which is solc/Slither/network — see verified-tool-behavior.)
• The build_rows↔INSERT column-order coupling is guarded by the full-tuple happy-path assertion.

Historical output/ CSVs are rejected by the loader

The CSVs already in output/ (including supabase-findings.csv) predate the score column, so the loader (which hard-requires score) will error on them. Re-canonicalizing the existing batch-2026-05-30 rows therefore needs a fresh scan to produce a score-bearing CSV — the 96 rows already in Supabase are unaffected. Future scans emit score-bearing CSVs that feed the loader directly.

Status / remaining step

Built, reviewed (Ready), and committed (c5cec6e, pushed to main). The one remaining step is a one-time live db-marked round-trip against Supabase with DATABASE_URL set. Spec: docs/superpowers/specs/2026-05-31-findings-loader-design.md. Plan: docs/superpowers/plans/2026-05-31-findings-loader.md.

Related

• supabase-findings-store — the public.bsc_finding table this loads into; the loader is the durable fix for that note’s hand-transcription warning, and supersedes its “score computed at load time” framing (score now travels in the CSV)
• contract-scanner — the pipeline whose Stage-5 CSV this consumes
• phase2-address-resolution — source of resolution_source (tier1 vs tier2-adapter); confirm own-code attribution before disclosure on adapter-resolved rows
• verified-tool-behavior — the integration marker meaning, and the psycopg3 / Supabase connectivity facts verified for this loader
• LOG — session log
• TOPICS — theme index