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.
ETL-heavy backend over a PostgreSQL 16 OLTP store (4.2M contracts) and a ClickHouse OLAP mirror (52M+ award rows). A custom node-schedule BaseScheduler drives 3 SmartSync services — data ingest (SAM.gov → idempotent composite-key upserts), materialized-view refresh, and a 6-hour PG→ClickHouse copy. ~20 PostgreSQL materialized views (5 core pre-aggregations, ~80% of dashboard queries) cut p95 latency 25× (5s → <200ms), and a 4-level read cascade (CH MV → CH Live → PG MV → PG Live) degrades gracefully. Express.js API with Zod validation across 200+ endpoints, 200+ dynamic filters via a SQL-injection-safe WHERE-clause builder, iron-session + JWT hybrid auth, and Stripe billing — deployed on Komodo bare-metal (Hetzner) with 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
- 3 SmartSync services on a custom node-schedule BaseScheduler (no Airflow): data ingest, MV refresh, ClickHouse mirror
- Idempotent SAM.gov ingestion: composite 6-field natural-key INSERT ... ON CONFLICT upserts (a failed sync just reruns the window)
- Delta + deep + daily reconciliation sync layers guarantee completeness despite 20–28% pagination drift
- ~20 PostgreSQL materialized views (5 core pre-aggregations) cut p95 latency 25× (5s → <200ms); 4-level read cascade
- ClickHouse OLAP mirror (52M+ rows, 6-hour PG→CH copy) for sub-50ms aggregations; PostgreSQL 16 OLTP for 4.2M contracts
- Express.js API: Zod across 200+ endpoints, 200+ dynamic filters, iron-session + JWT, Stripe; Komodo bare-metal + VictoriaMetrics observability
Tech Stack
Backend
Database & OLAP
Data Pipeline / ETL
Infrastructure
Frontend
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.