Role: Main developer — owned the platform end-to-end: UI and frontend, the Express API and SmartSync microservices, the SAM.gov data pipeline, ClickHouse analytics, and CI/CD.
What it is. Government contracts intelligence platform over 4.2M federal contracts (PostgreSQL 16 OLTP) and 52M+ award rows (ClickHouse OLAP).
Data pipeline & ETL. A custom ETL layer — three node-schedule-driven SmartSync services — ingests the SAM.gov Contract Awards API into PostgreSQL via idempotent composite-key upserts, materializes ~20 PostgreSQL materialized views, and mirrors the awards table into ClickHouse on a 6-hour cycle. Three overlapping sync layers plus a daily reconciliation job took SAM.gov restarts from ~3% record loss to zero data loss.
Analytics at scale. The MV strategy (5 core pre-aggregations — agency, NAICS, geography, award type — covering ~80% of dashboard queries) cut p95 latency 25× (5s → <200ms) with no new infrastructure, while the ClickHouse columnar mirror serves no-filter analytics sub-50ms.
Deployment. Next.js 15 ships on Cloudflare Workers (OpenNext) with ISR; the Express.js API and 3 SmartSync services run on Komodo bare-metal (Hetzner, Docker) behind 24+ GitHub Actions workflows on a self-hosted runner.
Data Pipeline & Analytics Architecture
An idempotent ETL pipeline from SAM.gov to sub-50ms dashboards — data flows top to bottom through eight stages, split across an OLTP/OLAP store and wrapped by three cross-cutting rails.
Source / Ingest
Paged fetch from SAM.gov with backpressure so the API never gets hammered.
Transform
A single canonical transform with drift guards before anything is written.
Load / Upsert
Idempotent writes to PostgreSQL — every rerun is safe.
Completeness Layers
Three overlapping syncs that defeat SAM.gov's 20–28% pagination drift.
No exception thrown ≠ data is correct — completeness is verified by comparing expected vs actual counts, not just catching errors.
This caught a silent 95–99% data-loss bug where SAM.gov changed totalRecords from a number to a string.
Materialize (smartsync-refresh)
Pre-aggregations that make dashboards feel instant.
4.2M contracts · source of truth · row-store writes + MVs
52M+ award rows · columnar analytics mirror
OLAP Mirror (smartsync-clickhouse)
Columnar analytics mirror for the heavy aggregate queries.
Serve
Express API with a 4-level read cascade and config-driven filtering.
Present
Next.js 15 / React 19 frontend that virtualizes millions of rows.
8. Orchestration
cross-cutting · spans every layerA custom scheduler runs the nightly maintenance window — no Airflow.
9. Observability
cross-cutting · spans every layerPer-run metrics and alerts, with completeness verification baked in.
10. Infrastructure
cross-cutting · spans every layerSelf-hosted bare-metal compute fronted by Cloudflare.
Key Features
- Custom ETL: 3 node-schedule SmartSync services (data ingest, MV refresh, PG→ClickHouse mirror) — no Airflow
- Idempotent SAM.gov ingestion: fetchAllPages with rate-limit backpressure → single transform → composite 6-field natural-key upsert
- Completeness despite 20–28% pagination drift: delta (4×/day) + deep (1×/day) + daily reconciliation (21-day lookback) → zero data loss
- ~20 PostgreSQL materialized views (5 core pre-aggregations, ~80% query coverage) cut p95 latency 25× (5s → <200ms)
- ClickHouse OLAP mirror of 52M+ award rows (6-hour sync) serving no-filter analytics sub-50ms; 4-level read cascade
- 200+ dynamic filters via SQL-injection-safe WHERE-clause builder; Next.js 15 on Cloudflare Workers; Komodo bare-metal + 24+ CI/CD workflows
Tech Stack
Frontend
Backend
Database & OLAP
Data Pipeline / ETL
Infrastructure
AI & Dev Tools
Challenges & Solutions
Slow Analytics on Millions of Contracts
Real-time aggregation queries across millions of federal contracts — spending by agency × NAICS × geography × award type — took ~5 seconds, making dashboards unusable.
A materialized-view strategy: ~20 PostgreSQL MVs (5 core pre-aggregations covering ~80% of dashboard queries) cut p95 latency 25× (5s → <200ms) with no new infrastructure. A ClickHouse columnar mirror serves no-filter analytics sub-50ms (10–100× faster than PG row storage on large aggregations), and a 4-level read cascade (CH MV → CH Live → PG MV → PG Live) degrades gracefully when ClickHouse or MVs are unavailable.
Silent Data Loss in Ingestion
For 10 days the SAM.gov sync reported success while silently dropping ~95–99% of records: SAM.gov changed `totalRecords` from a number to a string, the pipeline read it as null, `null ?? 0` coerced it to 0, and the completeness check `0 >= floor(0 × 0.95)` always passed — no exception thrown, no alert.
A `parseTotalRecords()` helper that accepts both string and number shapes and treats any NaN / 0 / missing value as an API error (triggering key rotation) rather than coercing to 0. The deeper fix was completeness verification comparing expected vs actual counts — `no exception thrown` does not mean the data is correct.
Guaranteeing Completeness Despite Pagination Drift
SAM.gov's result set shifts mid-crawl, so any single pass reliably misses 20–28% of records in a date window — and the miss compounds across runs.
Three overlapping sync layers — delta (4×/day, 1-day window), deep (1×/day, 4-day window), and a daily reconciliation job (21-day lookback) that re-syncs any day falling >10% short of SAM.gov's own count. Idempotent composite-key upserts make every rerun safe, taking restart loss from ~3% to zero.
Detecting Upstream Schema Drift
SAM.gov changes field paths and types without notice — e.g. `awardingAgencyCode` read from the wrong nested object put subtier codes in the department column with no error.
A runtime regression guard (>10% null `actionDate` → red Slack alert) catches field-path drift early, and a captured real-payload fixture serves as an informal contract test — it was the fixture that proved the wrong field path was in use.
Multi-Service CI/CD for Sole Engineer
7 packages with interdependent builds and deploys needed reliable CI/CD without a dedicated DevOps team. Frontend ISR depends on backend being live, services must deploy atomically.
Architected 24+ GitHub Actions workflows on a self-hosted runner with dynamic port allocation, Komodo HTTP API for Docker orchestration, and a Build → Verify → Deploy pipeline ensuring frontend validates against temp backend before any production deploy.
Data Quality at Scale
Raw SamgovAPI data contained inconsistencies, missing fields and unstructured descriptions making it difficult to search and match contracts.
Built AI pipeline using LLM APIs for automated data sanitization, opportunity matching and description generation. Structured output validation ensures consistent data quality.