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. Sole engineer on a 7-package monorepo: Express.js API, Next.js 15 frontend on Cloudflare Workers, a SLED Admin SPA, and 3 SmartSync ETL services.
Data pipeline & ETL. The data layer ingests the SAM.gov Contract Awards API into PostgreSQL 16 (4.2M contracts) via idempotent composite-key upserts, materializes ~20 MVs (25× faster dashboards), and mirrors 52M+ award rows into ClickHouse for sub-50ms OLAP. Completeness is guaranteed despite 20–28% pagination drift through delta + deep + reconciliation sync layers.
Deployment. End-to-end CI/CD with 24+ workflows (unit → integration → E2E → deploy) on a self-hosted runner; Komodo bare-metal (Hetzner) + Cloudflare deployment cut hosting cost roughly 5–10× versus Railway.
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
- 7-package monorepo: Express.js API, Next.js 15 on Cloudflare Workers, SLED Admin SPA, 3 SmartSync ETL services
- ETL pipeline ingesting SAM.gov into PostgreSQL via idempotent composite-key upserts, with reconciliation-backed completeness
- ~20 materialized views across PostgreSQL plus a 52M-row ClickHouse OLAP mirror (4.2M contracts; 25× faster dashboards)
- 200+ dynamic filters, Zod across 200+ endpoints, iron-session + JWT hybrid auth, Stripe billing
- 24+ GitHub Actions CI/CD workflows (unit → integration → E2E → deploy) on a self-hosted runner
- Komodo bare-metal (Hetzner) + Cloudflare deployment — ~5–10× hosting-cost cut vs Railway; PostHog analytics
Tech Stack
Backend
Frontend
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.
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.
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.
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.
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.