GovChime Analytics Platform

The SAM.gov pipelines ran on a hand-rolled scheduler, and a green run only proved that code executed. The opportunities sync once reported success while holding only 17.5% of notices — API-key quota starvation, plus a completeness check that passed when nothing was proven.

Rebuilt SmartSync on Temporal: 32 schedules on 6 task queues, per-window extract → transform → load → verify, a 13-key SAM.gov pool with a usage ledger, and replay tests of production histories in CI. Completeness is checked against SAM.gov's own files — 0 of 80,922 active notices and 0 of 733,627 FY2025–26 archive notices missing. Production cut over on 2026-09-25 and the legacy scheduler was deleted.

Analytics read from ClickHouse, and a second PostgreSQL needs a live copy of production. Both drift silently if nothing proves they match.

Rewrote the teammate's first PostgreSQL → ClickHouse copy scripts with checkpoints and a parity check and moved them onto Temporal. PostgreSQL is the CQRS write model; a coordinator per table projects new rows into ClickHouse by watermark (query-based CDC), and a daily reconciliation compares counts, distinct ids and checksums — contracts, entities and 94.4M award transactions match. The PostgreSQL mirror uses logical replication (pgoutput; snapshot, then streaming) as Temporal Workflows, with hourly slot-lag checks and a daily count-and-hash reconciliation.

A headline Postgres query took 13.6 s because it applied a slug function to every row of an 89M-row table, which forces a full scan. Separately, concurrent requests for the same heavy ClickHouse count could pile up into several multi-GiB scans at once.

Filtered on a trigger-maintained indexed slug column with a 2-year window instead (13.6 s → ~1 s) and on an agency-code lookup in ClickHouse (2.2 s → 0.27 s). Added a single-flight guard to the API's in-memory response cache, so concurrent callers share one query and a failed refresh serves the last good value. Built the ClickHouse → Postgres fallback chain on the team's ClickHouse read model, then simplified it — 32 Postgres MVs dropped, ClickHouse MVs 31 → 14.

The Next.js 16 frontend runs on Cloudflare Workers via OpenNext with an R2 page cache. With a long-lived regional cache and no tag cache, a published page kept serving the stale copy and on-demand revalidation did nothing; in the worst case feed pages lagged the database by ~3–5 hours.

Switched the regional cache to short-lived mode and added a D1 tag cache, so revalidatePath and revalidateTag take effect after a publish; aligned the React Query, ISR and API cache TTLs at 10 minutes (worst-case feed lag ~10 min by design); and edge-cached the sitemaps with the Cloudflare Cache API.

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.

AI-generated blog, social and SEO copy about federal contracts must never state numbers the data doesn't support.

A Python AI content service generates the copy from contract data, a guard strips any AI-stated figure not present in the source data, and every piece goes through a human review queue. It moved from the Anthropic API to Cloudflare Workers AI (Llama 4 Scout) in September 2026.