Scanner Findings → Supabase Postgres Integration

The scanner’s ranked findings are loaded into a Supabase Postgres table (public.bsc_finding) so runs can be queried, ranked, and (eventually) surfaced in a frontend instead of living only in per-run CSVs. This note is the source of truth for the table — the schema lives in Supabase, not in the scanner repo, and the score is computed at load time, not by the scanner. First load: batch-2026-05-30, 96 findings.

For Agents

• Supabase project: mkofmdtdldxgmmolxxhc (org/project name “mgmt”), Postgres 17.
• Table: public.bsc_finding. Created via Supabase MCP apply_migration, migration name create_bsc_finding.
• Score is NOT in the scanner output. output/supabase-findings.csv has no score column — score is derived at load time (formula below).
• RLS enabled, zero policies → service-role-only. Anon-key clients see 0 rows until a SELECT policy is added.
• Reads of this table go through the historian/parent with the service role; do not assume anon access works.

Why this exists (resolves a deferred decision)

The design doc left “storage past MVP” open (SQLite vs Postgres vs flat-files-with-git-diff). This integration resolves it: Supabase Postgres. It gives queryable, server-side-ranked findings across runs, a natural home for a future triage/review UI, and Supabase’s auth/RLS for when a frontend is added — without standing up our own database.

Table schema — public.bsc_finding

Created via Supabase MCP apply_migration (name=create_bsc_finding). Postgres 17.

Column	Type	Notes
id	uuid	primary key, default gen_random_uuid()
created_at	timestamptz	not null default now()
run_label	text	groups one scan run — convention 'batch-YYYY-MM-DD'
chain	text	not null (e.g. 'bsc')
protocol	text
contract_address	text	not null
contract_name	text
severity	text	High / Medium / Low / Informational
detector	text	not null — Slither detector id
impact	text
confidence	text	High / Medium / Low
line	integer
code_snippet	text	source lines (scanner reads them; Slither JSON has none)
defillama_url	text
bscscan_url	text
tvl_usd	numeric	protocol TVL — a factor in score
age_days	integer
resolution_source	text	'tier1' (DeFiLlama own-record) or 'tier2-adapter' (adapter-extracted)
score	numeric	computed at load time, see below

Indexes

• (score desc) — the default ranking / triage-queue order
• (contract_address) — group findings per contract
• (detector) — slice by detector type / FP analysis

Mapping to the CSV output shape

The columns mirror the scanner’s CSV output shape (chain, protocol, contract_address, contract_name, severity, detector, impact, confidence, line, code_snippet, defillama_url, bscscan_url, tvl_usd, age_days) plus three add-ons that live only in the DB: run_label, resolution_source (present in Phase-2A CSVs), and the load-time score.

Scoring formula (computed at load time)

Score now travels in the scan CSV (updated 2026-05-31)

Originally the score was computed at load time (the historical output/supabase-findings.csv has no score column). As of the findings-loader, scanner/triage.py writes a score column into the scan CSV (computed once at scan time) and the loader copies it verbatim. The formula below still defines the score; it is just now applied in the scanner, not the loader. Historical score-less CSVs are rejected by the loader.

score = SEV[severity] * CONF[confidence] * tvl_usd

SEV  = { High: 40, Medium: 8, Low: 2, Informational: 1 }
CONF = { High: 3,  Medium: 2, Low: 1 }

These weights match the tuned triage weights in the scanner’s config.yaml (the same severity × confidence × tvl ranking the Stage 5 triage / CSV uses), so the DB ranking and the CSV ranking agree. Because tvl_usd is a raw dollar figure, scores are large — high-TVL High/High findings land in the tens of millions.

First load — batch-2026-05-30

Metric	Value
run_label	batch-2026-05-30
Findings (rows)	96
Protocols	8
Contracts	10
Severity split	High 22 / Medium 53 / Low 21

Protocols in this batch: BTCST, DOOAR, TheDeep, OpenGDP Shared Security, StableHodl, Frax Swap, SYMMIO, Blackwing.

Top-ranked leads:

• BTCST — AdminUpgradeabilityProxy, incorrect-return — top of the queue, score ~75.6M (high TVL × High/High weighting).
• TheDeep — ICHIVault, arbitrary-send-erc20 — High severity / High confidence. (Note the Phase 2A caveat: TheDeep’s address resolved via the ICHIVaultFactory adapter — confirm own-code attribution before disclosure.)

RLS / security posture

Service-role-only by design

bsc_finding has RLS enabled with no policies. In Supabase that means only the service role can read/write — a secure default for private security-research data.

• A frontend using the anon key sees zero rows until an explicit SELECT policy is added.
• When a triage UI is built, add a scoped SELECT policy (e.g. behind authenticated users) rather than disabling RLS.
• Supabase advisors report only INFO-level notices for this table — nothing to action.

Operations

Load / reload a run

Use the loader, not hand-written SQL

The [[findings-loader|python -m scanner.load]] CLI is the durable mechanism for this — it does the replace-by-run_label atomically (delete + bulk insert in one transaction, with a --replace gate for populated labels) and is the fix for the transcription warning below.

• Load: insert rows with the run’s run_label (batch-YYYY-MM-DD). With the loader, score comes straight from the CSV; for manual SQL, compute score per the formula above as you go.
• Reload / replace a run: delete from public.bsc_finding where run_label = '<label>' then re-insert (the loader’s --replace does this atomically). run_label is the unit of idempotency.

Bulk-loading by hand-transcribing INSERTs through the MCP is error-prone — always verify

Loading many multi-row INSERTs by manually transcribing them through the Supabase MCP execute_sql tool silently corrupted the first 96-row load: one row was dropped and one bscscan_url got a stray space. The tool reported success either way.

Mitigations (do all of these):

1. Drive inserts from a generated SQL file rather than hand-transcribing — the real fix.
2. After loading, check select count(*) against the expected row count.
3. Run a malformed-value scan on the columns that should never contain spaces:

   select id, contract_address, defillama_url, bscscan_url
   from public.bsc_finding
   where run_label = '<label>'
     and (contract_address like '% %'
          or defillama_url like '% %'
          or bscscan_url like '% %');

   Valid addresses and URLs never contain spaces, so any hit is a transcription bug.

Related

• contract-scanner — design doc; this resolves its deferred “storage past MVP” open question and mirrors the CSV output shape
• phase2-address-resolution — source of resolution_source (tier1 vs tier2-adapter) and the own-code-attribution caveat behind some addresses (e.g. TheDeep)
• verified-tool-behavior — why code_snippet is read from source (Slither JSON has none)
• LOG — session log
• TOPICS — theme index