DataTap

The Problem

Our client's operational data was locked inside a third-party vendor system with no export capability, no API, and no path to data portability. The only window into the system was the vendor's web interface.

The consequences were predictable:

• No ability to analyze historical trends or correlate events across time windows
• No programmatic access for building internal tools or integrations
• No way to combine operational data with other business data sources
• Reporting meant manual exports, copy-paste, and spreadsheets

The vendor had no roadmap for an API. Waiting was not a solution. We needed to extract, structure, and expose the data ourselves — working with whatever level of access we were given.

The Approach

We treat data extraction as an engineering problem, not a vendor negotiation. If a system has a web interface, it has an API — the browser is already using it. Our job is to find it, authenticate to it, and pull data out reliably and continuously.

The result is a generalizable pipeline we've now deployed at multiple sites: each one a YAML config file away from being operational. The same extraction logic, watermark tracking, and destination schema applies everywhere.

Phase 1: Web Interface Extraction

At the first deployment, our only access was the vendor's browser-based UI. We reverse-engineered the XML API the browser was calling, then built an authenticated extraction layer that reproduced the login handshake, maintained a session, and queried the same endpoints the UI uses.

The result was a reliable, incremental sync pipeline:

• Authenticated session management — login using the same credential flow as the UI, with session cookies maintained across requests
• Watermark-based incremental sync — each data stream tracks its own high-water timestamp so every run picks up exactly where the last one left off, with no re-processing and no gaps
• Paginated retrieval — followed the browser's own pagination protocol (session IDs, page tokens) to retrieve arbitrarily large result sets without truncation
• Independent stream isolation — multiple data streams (events, records, device state) sync under separate transactions, so a failure in one stream doesn't block or roll back the others
• Multi-site config — a single codebase serves every deployment; site-specific URLs, credentials, and table names live in a YAML file outside the code

A lightweight scheduler runs the extraction on a configurable interval, keeping the database continuously current with the source system.

Phase 2: Direct Database Access

At a second deployment, we negotiated direct read access to the vendor's underlying PostgreSQL database. This unlocked more data, richer schema detail, and a cleaner sync path — but the architecture remained the same.

We built a database-to-database sync layer with two key capabilities beyond the web extraction approach:

• Schema introspection and recreation — the tool reads the source table's full column definitions, types, constraints, sequences, and enum types, then recreates the exact schema in the destination without manual DDL
• Multiple sync strategies — append-only tables use incremental ID watermarks; reference tables (system state, device registries) use full snapshot replacement with truncate-and-reload semantics, each committed independently

Six data streams now sync in a single run: system status, three event log tables, and two device registry tables. The same watermark infrastructure tracks all of them.

Architecture

The Database Layer

Every extracted record lands in a cloud-hosted PostgreSQL instance with row-level security enforced at the database layer. The schema is designed for the data, not the vendor's storage format — normalized, typed, indexed, and queryable without any knowledge of the original system's internals.

A sync_state table tracks each stream's watermark: last ID synced, last timestamp seen. This makes every run idempotent — restart after a failure and the sync resumes exactly where it stopped, with no duplicate records and no gaps.

An auto-generated REST API exposes every table with filter, sort, and pagination support — no custom API code required. Access is gated by JWT authentication and enforced row-level security policies.

Analytics and AI Layer

Once data is in PostgreSQL and accessible via API, building on top of it is straightforward. We delivered two analytical layers:

Dashboard

A web dashboard surfacing the key operational metrics: event counts over time, device activity, time-series charts with configurable date windows, and data export. Built directly against the database API — no intermediate service.

AI Chatbot

An AI assistant that lets operators query their own data in plain English. The chatbot understands the database schema and translates natural-language questions into SQL queries, executes them against the live database, and returns structured answers with the underlying data.

Operators who would never write a SQL query can ask questions like:

• "How many events were logged in the last 7 days?"
• "Which devices have been most active this month?"
• "Show me everything that happened between 2am and 4am last Tuesday."

The chatbot runs against the read-only API surface, so it can only ever retrieve data — not modify it.

What This Enables

The pattern applies to any operational system where data is inaccessible:

STARTING CONDITION	WHAT WE DO
Web UI only, no API	Reverse-engineer the browser's API calls; build an authenticated extraction layer against them
Direct database access	Schema introspection and direct table sync — faster, more complete, schema-preserving
Multiple sites, one system	Single codebase, per-site YAML config; each site gets its own table namespace in the shared destination
Data accessible, but not queryable by staff	AI chatbot layer enables natural-language queries against the live database — no SQL required

The Engineering Detail That Matters

The hard part isn't connecting to a system. It's keeping the data current reliably over time: handling sessions that expire mid-run, pagination schemes that shift between pages, network failures mid-sync, and clock skew between source and destination.

Our watermark approach handles all of this. Each stream's progress is committed atomically with the data it tracks. A crash mid-run leaves the watermark exactly where the committed data ends. The next run picks up without duplication, without gaps, and without operator intervention.

Multi-site config means the same reliability applies to every deployment. The tenth site is as straightforward as the first.