BranchScout: net-lease intelligence tool for bank branch acquisitions
BranchScout helps institutional investors find banks with owned branches they're likely to sell (sale-leaseback). It ingests public data from FDIC + NCUA, scores 8,648 institutions on a 100-point scale, and surfaces "Hot" targets in a deal-management UI. The app is live on Vercel; the data is in Supabase Seoul. Several features are partially working — knowing which ones helps you prioritize what to fix first.
StatusStaging liveInstitutions8,648Branches101,369StackNext.js 15 + SupabaseDate2026-06-13
ReasoningLập luận
6-layer thinking chainChuỗi tư duy 6 tầng
L1
Raw requestYêu cầu gốc
"coi lại trong explainer về dự án đã làm 1 cách trực quan dễ hiểu từng lớp layer của dữ liệu, use case cụ thể để người đọc tìm ra vấn đề nhanh nhất"
L2
Reframe — what was really neededDiễn giải lại — nhu cầu thật
The real ask: make the system legible to someone who walks in cold — investor, developer, or future-you three months from now — so they can immediately spot where live data ends and where placeholder/broken data begins. The pain is that several features look complete in the UI but have empty or incorrect underlying data. A good explainer makes those gaps visible at a glance.
L3
ConstraintsRàng buộc
['6 pipeline stages exist but only FDIC + NCUA are run; scoring depends on good upstream data', "The 'owned vs leased' feature (the core differentiator) has NO real data — is_owned = NULL for all 101k branches", 'owned_pct shows NULL (correct) but the trigger will recompute it to 0 if ingest re-runs without migration 005 applied', 'Competitor ingest pulls news but cannot link articles to specific bank institutions (institution_id always NULL)', 'NewsAPI free tier is non-commercial — the competitors pipeline cannot be used in production as-is', 'NPL/Tier-1 ratio UI bug was fixed locally (commit ad1a666) but the deployed app still shows wrong values']
L4
Options weighedCác lựa chọn cân nhắc
['Option A — document the system as-is and let gaps be discovered organically. Cost: confusion, wasted debugging time.', 'Option B — build a deep architecture explainer with visual data-flow + explicit gap markers. Cost: 1 hour to build, long-term clarity.', 'Option C — fix all gaps before writing an explainer. Cost: days of work; some gaps (is_owned) require external paid data.', 'Option D — explainer with gap priority triage. Shows what is broken AND what to fix first. Best for investor pitch readiness. CHOSEN']
L5
Principle invokedNguyên tắc áp dụng
Honest data beats polished UI: surfacing which layers have real vs missing vs estimated data prevents false confidence in the scores.
L6
Pick + recognition signalLựa chọn + dấu hiệu nhận biết
Build the explainer now (Option D), clearly annotate each layer as REAL / ESTIMATED / MISSING / BUG-FIXED-NOT-DEPLOYED. This lets any reader immediately triage. Rejected: waiting until all gaps are fixed (months away, some require paid data). Recognition signal: when you see a feature that looks complete in UI but the underlying pipeline field is NULL or hardcoded, it is an estimated/stub — mark it explicitly.
ArchitectureKiến trúc
5-Layer System Architecture
Data flows top-to-bottom: public government sources to Python ingestion pipelines to Supabase PostgreSQL to Next.js API routes to React UI. Each layer has a specific responsibility. Problems in layer N ripple down to all layers below it — knowing which layer broke tells you exactly what to fix.
| Technical termThuật ngữ kỹ thuật | Plain nameTên dễ hiểu | RoleVai trò |
|---|---|---|
institutions | Bank or credit union master record | One row per financial institution. Contains financials (assets, deposits, loans), regulatory flags (CRA rating, enforcement actions), BBS score 0-100, and tier label (hot/warm/monitor). |
branches | Physical branch location record | One row per branch. Linked to institution. Has address, lat/lng (approximate via ZIP centroid for NCUA), deposit estimate, and is_owned field — currently NULL for all 101k rows because no pipeline populates it. |
BBS score | Branch Buy Score (0-100) | Hot is 70 or above, Warm is 45-69, Monitor is below 45. Factors: financial stress, ownership concentration, regulatory pressure, branch size, market competition. Owned % factor is currently skipped (renormalized) since data is missing. |
pipelines/scoring/score.py | Score calculator script | Reads institutions + branches from DB, computes per-institution BBS using weighted factors, writes back bbs_score + score_tier + score_factors JSON. Must be re-run after any data ingest. |
createAdminSupabaseClient() | Server-side DB connection with full access | All server-side API routes use the service role key, bypassing Row Level Security. Safe for an internal tool; would need RLS policies before multi-tenant or public use. |
owned_pct | Percentage of branches the bank owns vs leases | The marquee differentiator — investors want banks with many owned branches (sale-leaseback opportunity). Currently NULL for all institutions because branches.is_owned is never set by any pipeline. This is the core Phase 2 gap. |
migration 005 | Database patch for owned_pct = NULL | Sets owned_pct = NULL for all institutions instead of 0. COMMITTED to repo but NOT YET APPLIED to the live Supabase DB. If FDIC/NCUA ingest re-runs without applying this, the trigger recomputes owned_pct back to 0.0. |
Read the diagram in 30 seconds: green = live and working, yellow = working but with a caveat, red = broken or fix not deployed, dashed gray = feature stub with no real data. The entire owned-vs-leased story (the product's core pitch to investors) lives in the branches.is_owned field — which is currently empty across all 101,000 rows.
Key lesson: In data products, the pipeline layer determines what the UI can honestly show. Before building more UI features, verify which pipeline fields are actually populated. A field that looks filled in the schema may be NULL in every row.
PipelineQuy trình
Data ingestion run order (must follow this exact sequence)
1
FDIC download + ingest + financials
pipelines/fdic/download.py then ingest.py then financials.py. Banks: 4,312 institutions, ~60k branches. Deposits from FDIC Summary of Deposits (real branch-level data). All amounts already in $thousands.
2
NCUA download + parse + geocode + ingest + financials
pipelines/ncua/ five-step pipeline. 4,336 CUs, 22k+ branches. CRITICAL: NCUA amounts are in raw DOLLARS, not thousands — the to_thousands() helper divides by 1000. Branch deposits = CU total shares divided by branch count (estimate — deposit_estimated=true). ZIP centroid coordinates via pgeocode.
3
Apply migration 005 in Supabase SQL editor
supabase/migrations/005_owned_pct_unknown.sql — sets owned_pct=NULL for all institution rows. MUST apply this BEFORE re-running ingest to prevent the recalc trigger from computing owned_pct back to 0.0. This migration is committed to the repo but NOT yet applied to the live database.
4
Scoring: score then branch_score then summary
python -m pipelines.scoring.score then scoring.branch_score then scoring.summary. Recomputes BBS for all 8,648 institutions. Hot tier is currently 2,343 (up from 576 after owned_pct renormalization removed the bogus zero penalty).
5
Markets aggregate
python -m pipelines.markets.aggregate. Computes county-level HHI (Herfindahl-Hirschman Index) measuring market concentration. Used in the Markets page and as a scoring factor.
6
Competitors ingest (requires NEWS_API_KEY)
python -m pipelines.competitors.ingest. Pulls rival acquirer news for MountainSeed, Blue Owl, SLB Capital from NewsAPI. Requires NEWS_API_KEY env var. WARNING: free tier is non-commercial only — must upgrade for production use.
7
Alerts generate
python -m pipelines.alerts.generate. Produces all 4 alert types: hot_prospect, credit_downgrade, regulatory (requires migration 002 for the enum value), competitor_move (requires step 6 to have run first). Idempotent — safe to re-run.
DecisionsQuyết định
Layered decision cardsCác quyết định theo tầng
owned_pct = NULL vs 0 — which value is more honest?
L1All 8,648 institutions showed owned_pct = 0%, but no pipeline ever sets is_owned on branches
L2The product's core promise to investors is to find banks with owned branches ready for sale-leaseback. Showing 0% is worse than NULL — it implies data was collected and the answer is zero, not that the data is unknown. That is a pitch-killing false claim.
L3['branches.is_owned is NULL for all 101k rows — no pipeline populates it', 'DB trigger recalc_owned_pct derives owned_pct from is_owned, returning 0.0 when all is_owned rows are NULL', 'The UI was already coded to show dash for NULL owned_pct', 'A re-run of ingest will recompute back to 0 unless migration 005 is applied first']
L4['Keep 0% — misleads investors, depresses every BBS score by the owned factor weight', 'Set to NULL now, populate with real data later via Regrid parcel matching — CHOSEN', 'Remove the owned_pct feature entirely until real data exists — hides the differentiator', 'Hardcode a fake estimate based on bank size — dishonest, creates technical debt']
L5Honest data beats polished UI: a missing field is a known unknown; a wrong field is a hidden lie.
L6NULL chosen (commit 131c3e6). BBS rescored — Hot tier jumped from 576 to 2,343 (bogus owned-zero penalty removed). Migration 005 committed. Rejected: keeping 0 (misleads), faking it (creates debt). Recognition signal: when a scoring factor has no source data, skip it and renormalize weights rather than defaulting to zero — zeros look like measured outcomes, not missing data.
NCUA amounts in dollars vs thousands — unit mismatch trap
L1After NCUA ingest, Navy Federal credit union showed $203 trillion in assets instead of $203 billion
L2FDIC established $thousands as the convention for the institutions table (standard in banking). NCUA CSV exports raw dollar amounts. Without conversion, all CU financials are 1000x inflated — scores, ratios, HHI calculations, and any bank-vs-CU comparisons are meaningless.
L3['FDIC amounts: already in $thousands (matches schema)', 'NCUA amounts: raw dollars (as published in the quarterly ZIP)', 'Schema column type: BIGINT in $thousands — would overflow for large CUs if stored as raw dollars', 'Ratio calculations use raw values: tier1_capital_ratio = net_worth / total_assets — wrong assets means wrong ratios']
L4['Store as-is and change the schema unit — breaks FDIC data and all downstream queries', 'Convert NCUA dollars to thousands in the ingestion script with to_thousands() helper — CHOSEN', 'Use a separate column branch for CU amounts — massively complex schema', 'Document the difference and let the UI format it — silent bug, wrong numbers in DB']
L5Canonical ownership: one source of truth must own the unit convention; all inputs conform to it.
L6to_thousands() helper in ncua/financials.py divides all monetary amounts by 1000 before insert. Rejected: schema change (would break FDIC). Recognition signal: whenever integrating a new data source, check its unit convention against the DB schema before the first row is inserted — not after you see $203 trillion.
Competitor news: link to institutions now or ship unlinked?
L1The Competitors pipeline pulls MountainSeed/Blue Owl/SLB Capital news from NewsAPI, but institution_id is NULL on every competitor_activities row
L2The Competitors UI page shows recent acquirer activity. Without a bank link, you can see that MountainSeed acquired a branch in Texas but cannot click through to the specific bank in BranchScout's DB. That makes it a news reader, not competitive intelligence.
L3['Fuzzy bank-name matching would link articles to institutions but has high false-positive risk', 'NewsAPI free tier is non-commercial — cannot use in production without upgrading', 'Most competitor articles do not name the specific bank — only a regional bank in Ohio', 'Building entity extraction adds weeks of work with uncertain data quality']
L4['Build fuzzy entity matching now — high false positive risk, complex maintenance', 'Ship unlinked as a news feed for now, add entity linking in Phase 2 — CHOSEN', 'Scrape individual bank press releases instead of NewsAPI — more reliable, more engineering', 'Manual tagging UI — let users link articles to banks — good UX, deferred']
L5Ship a working v1 over a perfect v2 that does not exist yet.
L6institution_id stays NULL. Competitors page shows news feed context without bank deep-links. Rejected: fuzzy match (too risky). Recognition signal: if entity-linking quality is uncertain, ship the data unlinked and add the link as Phase 2 — incorrectly linked data is worse than unlinked data.
FilesTệp
Artifact mapBản đồ tệp tạo ra
| PathĐường dẫn | WhatLà gì | Who reads itAi dùng |
|---|---|---|
supabase/migrations/001_core_schema.sql | Full DB schema — all tables, ENUMs, indexes, triggers | Apply once to Supabase SQL editor; foundation of everything |
supabase/migrations/002_app_compat.sql | Adds regulatory alert_type enum value, nullable institution_id on deals, new columns on alerts/documents/branches | Required before regulatory alerts can be inserted into the DB |
supabase/migrations/005_owned_pct_unknown.sql | Sets owned_pct=NULL for all institutions — removes false 0% display | WARNING: COMMITTED BUT NOT APPLIED to live DB — apply via Supabase SQL editor (project avubqzlpfdizxwsxhiwg) |
pipelines/fdic/ | FDIC bank ingestion pipeline: download ZIP to parse to ingest institutions + branches to load financials | Run quarterly. Produces 4,312 banks + ~60k branches. Amounts in $thousands. |
pipelines/ncua/ | NCUA credit union ingestion: mirrors FDIC pipeline; includes geocode step (ZIP to lat/lng via pgeocode) | Run quarterly. 4,336 CUs + 22k branches. Amounts divided by 1000. Branch deposits are estimates. |
pipelines/scoring/score.py | BBS score computation for all institutions | Run after every ingest. Writes bbs_score + score_tier + score_factors back to institutions table. |
pipelines/alerts/generate.py | Generates all 4 alert types: hot_prospect, credit_downgrade, regulatory, competitor_move | Run after scoring + competitor ingest. competitor_move type requires ingest to have run first. |
pipelines/competitors/ingest.py | Pulls rival acquirer news from NewsAPI into competitor_activities table | Needs NEWS_API_KEY. Free tier is non-commercial only. institution_id always NULL — no entity linking yet. |
apps/web/app/(dashboard)/institutions/[id]/page.tsx | Institution detail page — financials, score breakdown, branches list | WARNING: NPL/Tier-1 ratio x100 bug fixed locally (commit ad1a666) but NOT YET DEPLOYED to Vercel. Deployed app shows wrong values. |
apps/web/app/(dashboard)/map/ | Branch map using Leaflet. NCUA branches show as hollow dashed rings (approximate coordinates) | Dev-only crash with react-leaflet + StrictMode. Works in production build. |
landing/index.html | BranchScout marketing landing page — rebranded, live at branchscout.vercel.app | Static HTML, no build step. Screenshots are real app captures with CAPTURE_BYPASS=1 middleware. |
Pending manual actions — required before fully liveViệc cần làm thủ công — trước khi hoàn tất
- CRITICAL: Apply migration 005_owned_pct_unknown.sql via Supabase SQL editor (project avubqzlpfdizxwsxhiwg) BEFORE the next ingest run — otherwise the DB trigger recomputes owned_pct back to 0.0 for all institutions
- Deploy uncommitted local changes: cd /Users/trang/Projects/branchiq && git add apps/web && git commit -m 'fix: npl tier1 ratio x100 bug, rebrand BranchIQ to BranchScout' && git push origin main. This fixes the ratio bug that shows 44% as 4400% on the institution detail page.
- Add NEWS_API_KEY to Vercel env vars and local .env to enable competitor news ingest (free key at newsapi.org — note the non-commercial restriction on the free tier)
- Re-tune Hot/Warm tier thresholds (currently 70/45) — Hot tier jumped from 576 to 2,343 institutions after rescoring; may want to raise the threshold to maintain a useful Hot tier size
- To connect branchscout.com domain: Vercel project branchscout → Add Domain → copy A/CNAME records to Porkbun DNS → then remove X-Robots-Tag noindex from vercel.json
- Phase 2 (the core differentiator): populate branches.is_owned via parcel data (Regrid API) or FDIC premises proxy to enable real owned-vs-leased scoring — this unlocks the full BBS scoring weight and the product's main pitch
Self-testTự kiểm tra
Hiểu rõ hệ thống chưa?
Tại sao owned_pct hiện là NULL thay vì 0% cho tất cả các ngân hàng — dù hệ thống đã có 101,369 records chi nhánh?
Nếu bạn chạy lại NCUA ingest ngay bây giờ mà không làm bước nào khác trước, điều gì sẽ xảy ra?
Trong your own words: tại sao NCUA ingest cần chia tất cả amounts cho 1000 trước khi lưu vào DB, trong khi FDIC ingest thì không?
Mastery checklist — tick what you can explain unpromptedBảng tự đánh giá — tích những gì bạn tự giải thích được