Data platform · Freelance
BI platform for a hotel and hospitality group
One set of figures drawn from six source systems, with no way for personal data to leak out.
- Role
- architect and lead developer (freelance)
- Period
- August 2026 – present
- Client
- Hotel and hospitality group (freelance)
- Type
- BI platform, in development
- Key technology
- Supabase (PostgreSQL), dbt, React, TypeScript
About the project
A hotel and hospitality group with several hotels and restaurants wanted to run the business on current figures. I'm designing and building the platform that makes that possible, from pulling in the source data to the dashboard that management will work with. It's still under construction: much of it is already running, and the rest is on its way.
The challenge
The figures were spread across six systems that don't talk to each other: hotel reservations, accounting, table reservations, staff scheduling, pricing strategy and online marketing. Anyone who wanted to know how a hotel was doing had to look at six screens and work it out themselves, ending up with a slightly different number each time. On top of that, every system has its own API, each with its own way of authenticating, its own limits and its own data model. What I was asked for: one dashboard with one set of figures, where every user sees exactly the data they're allowed to see.
What I built
A data platform in five layers, known as a medallion architecture. Each layer has one job: 1. Raw: the source data as it arrives. Guest details are stripped out at the moment of fetching. 2. Staging: this is where the data is cleaned and put into a fixed shape for each source. 3. Core: the sources are joined up through concepts they share, such as hotel, department and cost type. 4. Mart: fact tables per day and per hotel. 5. Public: the Semantic Layer. It's the only layer that can be reached from outside.
Key features
Privacy by design.
If it isn't needed, it doesn't go in. Only the top layer can be reached, and Row Level Security (access control per row, inside the database) makes sure each user only sees their own hotel or department. A bug in the front-end can't get round that.
Strictly read-only.
The platform has no way to write anything back to the client's systems. That's enforced in the code, and my AI tooling works under the same restriction.
Tests as a gate.
The dashboard layer is only refreshed once every data test passes. If there's an error in the source data, it never ends up on the dashboard unnoticed.
One definition per KPI.
Revenue, occupancy rate and labour cost percentage each have exactly one definition. That also lays the groundwork for an AI assistant that will later answer questions with those same definitions and the same access rules.
Fast.
With incremental models, which only process new or changed data, and new indexes, the staging layer reads around 99% less data. The heaviest model used to take 41 seconds and now runs in under one.
How it works
Each source is fetched on a schedule and then moves up through the layers one at a time. The top layer is only updated when all tests pass, and the dashboard reads from that layer alone. When an AI assistant is added later on, it will work in exactly the same way, with the same KPIs and the same access rules.
How I build
I design first and write code second. I worked out ten dashboard pages and went through them with the client before any front-end code existed, and only then turned them into a component library. For the project knowledge, such as architecture decisions, KPI definitions and API documentation, I built my own knowledge base with an MCP server. MCP is a standard that lets AI tools connect to outside sources, so my tools can look things up directly instead of reading whole documents. AI makes me faster, but the architecture and the decisions are my own.
Where it stands
Working today
- Five of the six sources load data every day.
- The layers from raw data up to the Semantic Layer are largely live.
- The client has signed off the dashboard designs.
Next step
- Connect the last source.
- Build the approved designs into the app.
Key figures
6
source systems connected
5
data layers
65
dbt models
16
KPIs in the register
4
roles in the access model
−99%
data I/O in the staging layer
Tools used
- Data & back-end
- Supabase (PostgreSQL, Edge Functions, Row Level Security, pg_cron)dbtSQL
- Front-end
- ReactTypeScriptTanStack Start/Router/QueryTailwind CSSshadcn/uiRechartsZod
- Design
- MagicPathLovable
- DevOps
- GitHubGitHub Actionsbun
- Knowledge base & AI
- PythonSQLite FTS5Model Context Protocol (MCP)Claude Code
What I learned
- 01
Designing a layered data warehouse: where each layer has a single responsibility.
- 02
Security by design:: putting security in the database itself rather than only in the interface.
- 03
Data engineering with dbt:: incremental models, tests that act as a quality gate, and measuring how much data is read and written (I/O) so I could bring it down.
- 04
Integrating six external APIs: , each with its own authentication, limits and data model.
- 05
Building my own MCP server: that gives my AI tools targeted access to project knowledge.
Is your data scattered across systems that don't talk to each other? I'm happy to think it through with you.