Building

Shipped end to end, from raw source to the reader.

Geospatial, data, and AI work as it was actually built: the decisions, the failures, and what held up.

538andMe.io: soup to nuts

538andme.io →

A public civic-data platform: congressional votes, bills, trades and campaign filings, with grounded Q&A over bill text. Built solo, end to end, against a fixed election-season deadline. Only the answer-generation call leaves the server. Embeddings and retrieval run locally, so bill text and reader questions are never sent to a third party for indexing.

PythonPostgreSQLpgvectorHNSWRRF hybrid searchFastAPINext.jsDockerOllamacron + flock

The build, in pipeline order

  1. 01 · Extract

    Polite, cached, resumable connectors

    • One HTTP client for every source: a token-bucket rate limiter per host, retries with backoff only on 429/5xx (a 404 is an answer, a 503 is a hiccup), and an on-disk response cache.
    • One chamber refuses the hosting provider’s IP range, so those jobs run in a service variant that routes through a US VPN container. Same image, different network path.
    • House trade filings are encrypted PDFs (RC4, empty password) whose fields extract one per line, not as rows. The parser was written against a real filing and documents the gotchas.
    • Every connector is idempotent and resumes from what’s already loaded, so a crash at row 900 restarts at 901, not zero.

  2. 02 · Load

    Raw into canonical tables, schema as code

    • 35 numbered migrations applied by one tracked runner, locally and in the server deploy script. Migrations are deliberately kept out of Postgres init so nothing is applied untracked and then applied twice.
    • Upserts with ON CONFLICT everywhere, so reruns are safe.
    • Embeddings live in a side table keyed by model, so switching embedders is additive, not destructive.

  3. 03 · Transform

    Derived layers and identity resolution

    • Materialized views with a unique index so they refresh CONCURRENTLY without blocking reads.
    • Attendance counts both explicit “not voting” and the Clerk’s “no row at all”. Edge cases like that are what make the numbers defensible.
    • A seat plus a surname does not identify a person. Anything short of an exact match goes to a human review queue with alternatives, and the decision is stored per filer so it applies to every past and future filing.
    • Ballot candidates tie to sitting members across chambers by name, state and FEC candidacy id.

  4. 04 · Index

    Hybrid retrieval over section-aware text

    • v1 was positional 384-d chunks with an ivfflat index, so a passage could only say “chunk 37”. v2 chunks by bill section so every hit carries its citation.
    • Lexical: a stored generated tsvector ranked with ts_rank_cd. Dense: a local 768-d embedder with an HNSW cosine index.
    • Fusion in SQL: reciprocal rank fusion, 1/(60+rank) summed across a FULL OUTER JOIN of the two rankings.
    • Names people actually use (“canada tariffs”) resolve to the right bill through an alias table with a trigram GIN index.

  5. 05 · Generate

    Grounded answers, guardrails, cost control

    • A pattern pre-check turns away off-topic questions before retrieval. The system prompt adds an out-of-scope sentinel and treats the question as untrusted text, which handles prompt injection.
    • Answer only from the excerpts, cite a section for every claim, never give investment advice or predict passage.
    • Answer reuse at ≥0.90 cosine similarity, with key terms (parties, numbers, chambers) required to match as well. Answers expire when the bill text, brief or prompt changes, and free-form answers never cross accounts.
    • Every provider call logs tokens and cost. When a UTC day hits the cap, new answers stop and stored answers keep serving.

  6. 06 · Review

    Human in the loop

    • Plain-language bill briefs are drafted against a written spec from a ranked queue, by a session with a read-only database role.
    • Nothing publishes until approved in an admin screen that works on a phone: two-tap approve, dictated notes.

  7. 07 · Serve

    API, analysis, and an honest coverage report

    • FastAPI with roles, quotas and a health endpoint that reports the live data census instead of a number in a doc.
    • “And Me”: a reader swipes yes or no on recent laws and sees which members voted most like them, plus a per-chamber PCA and k-means vote map. All computed in the browser from one public payload, so answers are never sent anywhere.
    • A coverage report shows date spans and gaps per dataset, because a table with 400 rows can look healthy and still be one year deep. Each gap prints the command that fixes it.

  8. 08 · Operate

    Scheduling, deploys, backups

    • 22 cron jobs behind one wrapper: flock so a job never overlaps itself, a size-capped log per job, and one line per run in a ledger for a one-command morning check.
    • An idempotent deploy script: pull, apply new migrations, rebuild images. Dev and prod compose files are kept the same shape on purpose. Nightly pg_dump backups.
    • Protected main, PR-only. The web app deploys from main behind a staging password until launch.

Problems worth telling

A human decision that kept getting overwritten

Situation
Trade filings name a filer, not a person. A seat plus a surname can match several people over the years (one district was held by four members with the same first name), so a wrong match attributes someone’s trades to a relative or predecessor. That is a libel risk, not a data blemish.
Action
Found that reviews were stored per filing and the loader re-upserted the year, silently clearing nine reviewed filings. Added a decision table keyed on the filer string exactly as the Clerk publishes it, storing who the filer is and who they are not. It is applied ahead of every automatic pass, and nothing automatic is allowed to write to it.
Result
One review now covers all of a filer’s filings, and the “is not” half stops the next automatic pass from picking the wrong relative.
Data integrityIdentity resolution

Rebuilding retrieval so answers can cite the law

Situation
v1 used positional chunks, one fixed-width vector column and an ivfflat index. Answers had to cite the section of the bill, and the embedder had to be swappable.
Action
Re-chunked by bill section, moved embeddings to a side table keyed by model, switched to HNSW, and fused lexical and vector ranks with RRF inside one Postgres function.
Result
Every passage carries a citation like “SEC. 4021(b)”, exact legal terms and dollar amounts are found by the lexical half, and the move from a 1024-d to a 768-d model was additive.
RAGSQLSchema design

Unit economics for an LLM feature

Situation
A free public Q&A page where every answer costs money and traffic is unpredictable before an election.
Action
Logged every provider call with tokens and cost. Added a daily cap that stops new answers but keeps serving stored ones. Measured the reuse threshold on 19 question pairs, found that party-swapped questions scored 0.85, and added a key-term match.
Result
Spend has a hard ceiling, repeat questions are instant and free, and free-form answers never cross accounts.
Cost controlEvaluation

“Looks loaded” versus “is complete”

Situation
The stock-trade cluster looked thin however wide the window. The table had 400 rows and no failed jobs.
Action
Wrote a coverage report that shows date spans and per-period gaps instead of row counts, and prints the exact command to fill each gap.
Result
It showed the table was one calendar year deep. The backfill fixed the feature, and the report became the standard pre-deploy check.
Data qualityObservability

Published · Open Source

Tools other people install

2020 – Present · QGIS plugin · Python

Earth Engine Catalog Query (ee_s5p_plugin)

  • Published on the official QGIS Plugin Repository: roughly 440 downloads in the first two weeks.
  • Grew from 92 supported Sentinel-5P satellite catalog sets to 100% coverage of the Google Earth Engine Data Catalog, with easy button access to query, filter, and load Image + Feature Collections directly into QGIS.
  • Filter by Feature Collection borders, custom geolocation, time, and channel/band within each collection.
  • Backwards compatible with QGIS 3 and now supports QGIS 4 and PyQt6.
PythonPyQGISGoogle Earth EngineSentinel-5PPyQt6
QGIS Plugin Repository →

Sept 2026 – Present · Claude skill · npm package

yadash

  • A Claude skill that teaches AI agents to author, edit, audit, and deploy dashboards defined in YAML, compiled into a single static HTML page.
  • Published on npm as @blcrosbie/yadash (v0.1.2).
  • Constrained, validated config means agent changes stay small, reviewable diffs instead of chart-code rewrites.
  • The beta example is live on the 538andme.io/budget page.
Claude skillYAMLStatic HTMLnpm
GitHub →npm →Live example →

Jun 2026 – Sept 2026 · Mawer Capital

Production RevOps warehouse on GCP

GCP Cloud SQLBigQueryPostgreSQLLooker StudioHubSpot APIObservability
Situation
Revenue data was fragmented across HubSpot, Monday.com, Meta Ads, Whop, FanBasis, OnceHub, Intercom and Typeform. No one could answer a clean pipeline question, and executives were acting on stale or conflicting numbers.
Task
Sole data warehouse architect: design and build the analytics infrastructure from scratch on GCP Cloud SQL and BigQuery, and make pipeline health visible before executives were misled by stale data.
Action
  • Designed a layered PostgreSQL and GCP architecture (raw ingestion, canonical models, operational metadata, reporting views) so each layer could evolve without breaking downstream dashboards.
  • Built and hardened dozens of API integrations using watermarks, resumable checkpoints and bounded windows. Re-architected the HubSpot Contacts sync to absorb 90K–150K weekly bulk updates that exceed the Search API’s 10K-result ceiling, a constraint that breaks naive pagination entirely.
  • Built metadata-driven observability (pipeline registry, run history, alert state) that separated “job succeeded” from “data is current”, so health checks reflected actual freshness, not just process completion.
  • Diagnosed a production database crisis where Looker Studio was saturating a dedicated role at 40/40 connections, and implemented connection pooling and query safety controls.
Result
Modeled canonical identity and payment attribution linking $20M+ in processor revenue to customers and roughly 50K deal-contact relationships. Eliminated false-positive pipeline health signals before executives acted on bad data. Delivered in three months.

Oct 2023 – Feb 2024 · Harvard Business School

Nationwide geospatial data engine

Areal interpolationPythonScalaRCensus / ACSAnomaly detection
Situation
Faculty had open research questions that required fusing Google Trends (DMA-level), anonymized banking data, IRS and ACS Census datasets, at different geographies and granularities, across sources with no shared identifier.
Task
Build a production geospatial data engine from zero to one, translating ambiguous research questions into pipelines whose outputs could hold up to peer review.
Action
  • Engineered an areal interpolation pipeline mapping coarse DMA-level Google Trends data into CBSAs, counties and tracts using population-weighted interpolation, the statistically correct approach when source and target geographies don’t align.
  • Fused anonymized banking data with IRS and ACS datasets by geographic join, then built anomaly detection to surface deviations in medical spending against regional search intensity.
  • Served as the final validation gate, reconciling results across Python, Scala, R and Excel to hold statistical integrity for peer-reviewed output.
Result
A production pipeline whose results cleared the faculty’s validation standard for journal submission. The interpolation approach was novel enough that the methodology was documented for co-authors who hadn’t used it before.