Workshop reference · Sync-to-own-store pattern
The Data Layer Playbook
How to take a SaaS system you already run and unlock the questions its own reporting can't answer: through chat and a dashboard, off one small, well-defined data layer.
One system
One system fronted: a sync job pulls your data out through the vendor's API into a store you own. Only the sync writes; the dashboard and chat just read.
Several systems
The same build scales sideways: each extra system gets its own sync into the same store, so one dashboard and one chat can answer questions across all of them. The only requirement is a shared way to match records between systems: a unique identifier such as an employee number.
Phase 1
Discovery
Before any code: what do you actually want to know, and can the source even say it?
1.1 Start from the questions you want answered, not the data
Write down the real asks in your own words: "turnover excluding students who keep coming back", "who has a visa expiring?". These dictate the data model later.
Consideration
The questions the source system's own reporting can't answer are the entire justification for the build. Name them explicitly and keep the list; it becomes the acceptance criteria for everything that follows.
1.2 Get the public API spec and audit it before writing code
Pull the OpenAPI spec. Check auth, pagination, and whether every field your questions need actually appears in read responses, then test with real calls that the data needed is really there.
Consideration
Specs lie by omission. Flag every assumption you're forced to make and plan a verification pass with real credentials. And request credentials on day one: provisioning is routinely the longest lead time in the project.
Phase 2
Data and privacy
One question, asked early, that decides your whole deployment posture.
2.1 Does the system need to stay private?
Does it host PII, private information of any kind, or need to remain private for any other reason, such as commercially sensitive figures or data covered by a client agreement? HR data almost always qualifies: names, salaries, absence reasons, visa dates.
Consideration
If the answer is yes, the simple option is to run locally, on your machine only, which is fine for a single-person, single-use solution. Otherwise you need to think about hosting, data storage and authentication/authorisation, so settle this now, before the build shapes itself around the wrong posture (the trade-off in full: step 6.2).
Phase 3
The architecture decision
The one decision everything else hangs off.
3.1 Front the source API directly, or sync to your own store?
If the API is CRUD-shaped (per-record, per-user) and the questions are analytical (aggregates, trends, history), you sync.
Consideration
An LLM fanning out hundreds of per-record API calls per question is slow, rate-limited and expensive; a local store makes every question one SQL query. The store is a disposable cache; the source system stays the system of record.
3.2 Build a mock of the source API first
Same paths and shapes as the real spec, serving generated fixture data at realistic scale.
Consideration
This lets you build and demo the entire system before credentials arrive. Make the fixtures deliberately encode your business's edge cases (returners, intra-group transfers, retroactive edits) so the hard logic is exercised from day one, not discovered in production.
Phase 4
The data layer
Crawl, reshape, and then prove the numbers.
4.1 Build the crawler / sync
Pull every entity the questions need into local raw tables, then rebuild the model. One job, one writer.
Consideration
Full refresh beats delta sync until volume forces otherwise: retroactive edits and deletions are handled for free, and every run is a reconciliation. Check whether the API even offers delta parameters (many don't). Watch for N+1 fan-outs on per-user sub-resources, and make the rebuild atomic so a mid-run crash can't leave a half-built store.
4.2 Transform into a semantic layer, not mirrored JSON
Dimensions, facts, and views: the additional structures that make access consistent and valuable.
Consideration
This is where business logic gets encoded exactly once: employment-spell sequencing, returner flags, reason groupings, "transfers aren't attrition". The views are the shared contract every consumer queries, so no two frontends can compute the same number differently.
4.3 Validate against the source before trusting anything
Recompute headline numbers independently from raw tables; reconcile totals at every level of the model.
Consideration
Get the definitions agreed and written down: does "dismissal" include no-shows? Which site does a mid-spell transferee count against? What's your house turnover formula? Your board pack already has a convention; find it before comparing the new numbers with last year's.
Phase 5
The interfaces
Chat first, dashboard second, both reading the same brain.
5.1 MCP server in front of the store: the plain-language interface
Canned report tools for the core questions, plus (optionally) a read-only SQL escape hatch.
Consideration
The escape hatch is fine in chat where a human reviews every query, and it's the first thing you restrict when other users arrive, because it can reach everything in the store.
5.2 Extract a shared report module the moment a second frontend appears
The MCP server and the HTTP API import the same query functions.
Consideration
Consistency guaranteed by code structure, not discipline. When the dashboard and the chat answer disagree, trust evaporates; make disagreement structurally impossible.
5.3 A thin, read-only HTTP API for the dashboard
Canned, parameterised endpoints only. No SQL passthrough to a browser.
Consideration
This is also the natural PII boundary: the report module simply never selects passport numbers, document IDs or birth dates, so no frontend can leak what it never receives.
5.4 Build the web dashboard
KPI tiles, the handful of charts your questions demand, and the tables behind them.
Consideration
Period filters scope everything at once so numbers always agree on screen; every chart gets a table twin (accessibility and trust); validate the palette for colour-blind safety rather than eyeballing; and show provenance ("synced 20 Sept, 20:37") right in the header.
Phase 6
Operating it
Freshness, deployment posture, and the handover.
6.1 Freshness: lazy sync, not a scheduler
Every request cheaply checks a synced_at stamp; a stale
store triggers one background refresh; requests never wait.
Consideration
Single-flight the sync with a lock: one page load is six parallel requests, and without the lock that's six syncs. Add a failure cooldown so a dead API doesn't cause a retry storm; make staleness visible everywhere rather than silent; and give humans an override (a "Sync now" button and an equivalent chat tool) for the fresh-to-the-minute case.
6.2 Decide the deployment posture: local vs hosted
One person's machine
Less shareable, but it collapses the security and governance problem: no hosting, no auth build, none of your data leaving the laptop. The laptop itself becomes the control surface (disk encryption, screen lock, backups). A pilot posture, not a destination.
Hosted for the team
Postgres under the same report module, SSO in front, a DPA behind. One deploy serves everyone and updates are instant; the moment more than a couple of people need it, this wins.
6.3 Package for non-technical users (if local)
An install-once script: the server as a login service, config in one file, and a
desktop link to localhost. Their entire interface is one
icon. For chat, MCP bundles give one-click install into Claude Desktop.
Consideration
Every machine you install on is a machine you update by hand, which is precisely the pressure that eventually pushes you to hosting. Keep the packaging effort proportionate to a pilot.
6.4 Keep a productionisation checklist from day one
Payload verification against the real API · GDPR/DPA and data minimisation · auth on both surfaces · retention · sync monitoring · definition sign-off · tests pinning the known-good numbers.
Consideration
Most of it is deliberately deferred during the proof of concept. The skill is knowing what you're deferring, and writing it down where everyone can see it.
Method notes
The meta-lessons worth narrating out loud as you build.
- One writer, many readers
- The sync is the only process that writes; everything else opens the store read-only. Half the reliability of the system is this one rule.
- Prove numbers before building UI on them
- Validate the reports against raw data before the dashboard exists, so every chart then inherits trusted figures instead of laundering doubtful ones.
- Verify visually, not just logically
- Screenshot the dashboard in light, dark and mobile. Real defects (label collisions, a CSS rule blowing up icons) only show up by looking.
- Sequence to de-risk
- Mock → sync → validate → chat → dashboard → freshness → packaging. Each step demos something on its own, so you see value at every checkpoint rather than a big bang at the end.
Sync-to-own-store · one semantic layer · two frontends · Owendale Advisory workshop reference
Thinking about a build like this?
AI Transformation starts here
Gary Crawford
Founder