Restaurant Operations Dashboard

A restaurant's orders live in a Postgres database that was built for taking orders. It is very good at that. It is not built for answering questions about them.

The questions worth asking are the operational ones: how long food takes to reach the table, when the real peaks fall, what actually sells, and where an order stops before it gets paid for.

I built the data pipeline that moves the data somewhere it can be asked those questions, and the dashboard that asks them.

The problem

• The transactional database is the wrong shape for analysis. Aggregating across every order is expensive on a database whose job is serving the next one.
• Nightly batch means today's numbers arrive tomorrow. A serve-time problem you find out about the next morning is a problem you could not fix.
• Drop-off before payment was invisible. Orders that open and never reach paid were a gap in the totals rather than something anyone could see.

What I built

A pipeline that moves on its own

Changes stream out of Postgres with Debezium change data capture, through Kafka, and into ClickHouse. Nothing waits for a schedule: a row changing in the operational database is the thing that starts the journey.

Inside ClickHouse it lands in three layers. A Kafka engine table consumes the stream and parses the JSON. A materialized view transforms and inserts on trigger. A ReplacingMergeTree holds the final deduplicated table, which is what everything queries.

The dashboard on top of it

Serve time against SLA, so you can see not just the average but how the tail behaves. Revenue by payment method. Volume by hour, and the peak hours and days behind it. And what is actually selling.

These are the numbers a manager can act on the same day, which is the entire reason for the pipeline underneath.

The funnel, built on windowFunnel

Order status goes open, then awaiting payment, then paid. I used ClickHouse's windowFunnel to measure how many orders make it through each step, with the drop-off called out at every stage.

Before this, an order that never reached paid was just missing from the revenue figure. Now it is a number on a page with a step attached to it.

The result

• The numbers move on their own instead of waiting for a nightly batch, so the dashboard reflects the restaurant in near real time.
• Serve time is measured against SLA rather than felt, so a slow service is a figure rather than an argument.
• Drop-off between open, awaiting payment and paid is visible at each step instead of inferred from a gap in revenue.

Built with ClickHouse, Kafka and Debezium over PostgreSQL, with the dashboard in React.