Case study
TRC Staffing Standards
Overview
A staffing analytics and plantilla planning platform for TRC-LU (DOH). It benchmarks the facility's verified operational baseline against versioned DOH/DBM standards at up to 500 licensed beds, with capacity planning, contract-of-service tracking, and audit-ready exports.
Problem
A DOH treatment facility has to justify its staffing against national standards that shift with licensed bed capacity, from 100 up to 500 beds. The actual plantilla lived in spreadsheets with competing summary cells, and every gap analysis, vacancy list, or audit report was manual.
Approach
I built an admin-only platform around one invariant: published data is never rewritten. The verified operational baseline (147 plantilla slots pinned by source-file SHA-256) and five tiers of statutory standards are stored as versioned, immutable snapshots, and live edits land in override tables instead. Dashboards compare the two, track contract-of-service positions through a draft-to-closed lifecycle, and export a printable DBM/DOH-form report plus audit CSVs. Sign-in is passwordless: a 6-digit emailed code hashed with a server-only pepper, opaque session cookies, and server-resolved viewer/editor/admin roles.
Architecture
A Cloudflare Pages SPA proxies /v1/* through a same-origin Function to an API Worker over Turso libSQL, so the browser never sees database credentials. The Worker is default-deny with 60-second per-isolate read caches invalidated on write, a 25-second request deadline with bounded retries, and read-model SQL that CI runs against node:sqlite. A sibling careers portal reads authoritative vacancies through an HMAC-signed integration API with an idempotent outbox ledger keyed by request ID.
Challenges
Workers' 10ms CPU budget versus Turso being an HTTP round-trip per await: measured CPU p95 was 66ms against 2 seconds of wall time, which drove six optimization phases of parallelized reads, retries, and indexes. The per-isolate session cache also let a disabled user keep working until cache expiry, a P0 fixed by re-resolving authorization on every privileged request. The System Health page even queried a nonexistent column while reporting HTTP 200, exactly the kind of lie a health page must not tell.
Results
Live in production since September 2026 and inside every free tier: 2.58% of Turso's monthly row reads and zero billable Cloudflare usage. A 27-migration, 21-table schema verified by ten validation scripts in CI, an append-only audit trail formatted for COA and CSC reviews, and a signed integration feeding the TRC-LU careers site.