Operational data pipeline

A pipeline that takes data out of a database reachable only from an internal network and delivers it, verified and signed, to live web dashboards.

Role: Designer and developer
Period: 2025–2026
Services: Data pipelines

Architecture diagram, hand-drawn on a dark background. An office server reads an operations database (PostgreSQL, read-only) every 5 minutes and sends the result by HTTP POST, with an upload token, to the storage and distribution API. Inside it, an /upload endpoint writes to Supabase storage and reads the file back through a single Supabase admin client (service role), then a filename-to-webhook mapping sends an HTTP POST signed with a webhook secret (HMAC-SHA256) to App 1, the only one in production. Dashed boxes show further apps that could receive webhooks or fetch the file from the /download endpoint with a download token. — How the pieces fit. Solid lines are in production; dashed ones are supported but not used yet.

A national public agency needed a live dashboard of its operational resources, built from data held in its operations system: a PostgreSQL database that the development team could only read, through a replica reachable solely from the agency’s internal network. I designed and built, on my own, the pipeline that bridges that gap and the storage and distribution service at its centre.

Extraction

Every 5 minutes, a scheduled task on an office server runs a Python script (psycopg2, pandas) against the read-only replica, so the production system is never touched.
The query, 335 lines of SQL, joins seven tables and translates the system’s internal codes into readable categories through 27 CASE expressions with over a hundred branches.
Before the data leaves the network it is cleaned: text mixing Latin-1 and UTF-8 is repaired, and fields stored as serialised PHP objects are turned into JSON.

Storage and distribution

A small service (Deno, Supabase Storage) receives the data and hands it out:

Authenticated upload, with its own token, into a private bucket. Every file is read back right after writing, to confirm it was stored intact.
Push to subscribers. Each file maps to the apps that use it; on every update, each receives the new data in a webhook signed with HMAC-SHA256, which it verifies before accepting.
Pull on demand. A /download endpoint, with a separate token, for apps that prefer to fetch the data themselves.
One set of keys. Only the service holds the storage credentials: neither the office server nor the apps ever touch the storage directly.

The design is generic: adding a dataset or a subscriber is one line in the mapping.

On the dashboard side

The dashboard’s backend verifies each signed delivery and keeps the latest data in a cache for its frontend. Only the agency’s own staff can open it: sign-in is through Firebase Authentication with Google as the only provider, behind an OAuth consent screen restricted to the agency’s Google Workspace, and the backend checks the session token on every request.

Results

Data that never left the internal network now feeds a live dashboard, refreshed every 5 minutes, without opening the database to the outside or touching the production system.
Private from end to end: read-only replica, token-authenticated upload, private storage, signed delivery and a staff-only dashboard. Every step requires credentials.
Reusable infrastructure: further datasets and apps plug into the same service with a line in its mapping, without new servers or credentials.