Projects › Ops Analytics
Watch the demo (AI-narrated, 1:28)
Narrated with a generated voice; captions included.
Recorded on the live app with real answers: a revenue question with its steps and the SQL guard's verdict, the sources and SQL behind the answer, a follow-up for the other property, guest phone numbers refused, and the daily check catching a mispriced suite.
Read the narration
A revenue manager told me a custom question costs her half a day of pivot tables.
So I built this: she just asks.
It's a simulated hotel group, and all the data's made up.
Which room type at Mountain Lodge earned the most this year?
It writes one SQL query,
and before anything runs, a guard checks it.
Here's the guard's verdict: read-only, and only these tables.
And here's where the answer came from.
The tables it read, and the revenue definition the hotel agreed on.
It didn't guess what revenue means.
It looked it up.
And the SQL's right there to check.
Now a follow-up.
And at Beach Resort?
It works out I mean the same question for the other property,
shows me how it read it, and answers.
If I ask for guest phone numbers as an analyst, it says no,
and even if the model slipped, the guard would stop the query.
Every morning a check runs five rules with no model at all.
Here it caught a suite priced at fifty dollars instead of about five hundred.
On my test questions it gets ninety-nine percent right,
and every held-out one,
for about twenty-eight cents per thousand questions.
It's live, with no sign-in.
The link's below, and so is the code.
Free hosting: if the app is asleep, click ‘wake up’ and give it about 30 seconds.
Hotel Ops Analytics Agent
Managers ask the bookings database questions in plain English and get a table, a chart and a short answer, always with the SQL that produced it. A daily check finds anomalies and posts them to Slack.
Independent project: a simulated client engagement for the same fictional hotel group as the other stages, with synthetic data. Every number below comes from a saved report or test run in the repository. The database has 12 months of synthetic bookings with five anomalies planted on purpose.
Problem
The revenue manager said a custom question means “half a day” of pivot tables, and that a number without its query won't go in a board pack. A rate loaded wrong for a week went unnoticed until the month-end report.
Approach
- Plan: Claude Haiku 5.5 returns structured JSON: a clarifying question (only for “our hotel” without a name, or occupancy without a date), a refusal, or one SELECT and a chart.
- Guard: every query is parsed before it runs: one SELECT, allowlisted tables, a LIMIT of at most 500, and guest names, emails and phones only for the manager role. The connection is read-only underneath, with a 3-second limit.
- Answer: a second call writes 1–3 sentences from the rows; numbers that aren't in the result are flagged. The SQL is always on screen.
- Daily check: five SQL rules (occupancy drop, cancellation spike, refund spike, activity no-shows, rate error) against the previous 28 days, no model, posted to a Slack incoming webhook by a scheduled GitHub Action.
Architecture
- A manager asks in the Streamlit appChat, history, a share link and the steps shown.
limits.pyPer visit, per minute, daily questions and cost.rewrite.py(Claude Haiku 5.5)A follow-up becomes a standalone question.retrieval.py: BM25 over 19 documentsSchema notes and definitions the planner needs.agent.plan_and_run(Claude Haiku 5.5, JSON)Plans the query.sqlguard.pyParser-based: reads only, no personal-data columns unless the role is manager, a row limit.- SQLite, read-onlyThe query runs on a read-only connection.
agent.stream_answer(Claude Haiku 5.5, streamed)The answer is written from the rows, with the SQL shown.- A redacted request log; a daily GitHub Action runs
anomaly.pyand posts to Slack.
docs/ARCHITECTURE.md in the repository.A real output
“What was total revenue at Beach Resort in October 2026?” (dev case q04)
SELECT SUM(b.total_usd) AS total_revenue_usd FROM bookings AS b JOIN properties AS p ON b.property_id = p.id
WHERE p.name = 'Beach Resort' AND b.status <> 'cancelled' AND STRFTIME('%Y-%m', b.check_in) = '2026-10' LIMIT 500Total revenue at Beach Resort in October 2026 was $334,216.
The agent's SQL and answer in the version 1.3.0 run (eval/reports/sql-2026-10-26-dev-protocols.jsonl, run 1); the result matches the reference query's.
Results
| Metric | Dev | Held-out |
|---|---|---|
| Execution accuracy (the query's result matches the reference query's) | 99% (167/168) | 100% (27/27) |
| Unsafe requests refused (writes, schema changes, personal data, injection) | 100% (27/27) | 100% (9/9) |
| Clarifying question asked when needed | 100% (9/9) | 100% (3/3) |
| Follow-up questions (multi-turn) | 100% (24/24) | 100% (9/9) |
| Plan + query + answer time per question, p95 | 4.31 s | 4.53 s |
| Cost per 1,000 questions | $0.28 | $0.29 |
| Hotel-policy questions (new, 5 × 3) | 15/15 (11/15 before a grader fix) |
In the browser on the live app (2026-10-28, 20 dev questions in one visit, Enter pressed → answer finished rendering): p50 4.28 s, p95 5.47 s. The browser adds streaming and rendering to the eval's plan + query + answer time.
The first published run (version 1.0, 2026-10-24) was 98% dev (144/147) and 95% held-out (20/21, run once) at $0.30 per 1,000 questions. Reports: eval/reports/sql-2026-10-26-dev-protocols.md, sql-2026-10-26-heldout-protocols.md and sql-2026-10-26-policy-rescored.md in the repository.
| Year | Planted anomalies caught | False alarms |
|---|---|---|
| Main (thresholds set here) | 5/5 | 1 in 11 months |
| Held-out (other dates) | 5/5 | 0 |
What the eval taught me
The first recorded run scored 84%. Half the failures were the grader: the model answered “Beach Resort” where the reference said beach-resort, returned a full ranking with the right answer first, and gave a share as 34.21% instead of 0.342. I fixed the grader and re-scored the saved queries without calling the model: 84% → 92% on the same answers. The rest were real, and some were mine: my prompt told the model to ask for a time frame and used “cancellations” as the example, so it asked instead of answering.
Before any model run, a free check that every reference query passes the guard found a guard bug: COUNT(*) looked like SELECT * over the guests table.
Trade-offs
| Choice | Why | What it costs |
|---|---|---|
| A parser-based guard plus a read-only connection | The prompt is a request; the guard is a control | An unusual valid query can be rejected (one retry) |
| Two model calls | The answer is written from the rows, not guessed | $0.28 per 1,000 questions, p95 4.3 s plan + query + answer |
| Deterministic anomaly rules | Every alert can be explained line by line | A new kind of anomaly needs a new rule |
| SQLite shipped with the app | Deploys free with nothing to set up | Postgres with a read-only role is the scale path |
Engineering
- Tests: 49, 85% coverage: the guard, the read-only connection and time limit, the agent with the model replaced, each planted anomaly, the Slack job without network, and the app via Streamlit AppTest.
- CI gates without an API key: every reference query passes the guard, the detector catches 5/5 on both years with at most one false alarm a month, and the saved dev run re-scores to the same verdicts.
- API spend: about $0.15 for every eval run.
Business case (projection)
A projection, not a result: a custom question in seconds instead of half a day, and a mispriced room caught in a day instead of at month-end. In this data the Suite was loaded at $50 instead of $515 for five nights, about $2,300 of lost revenue per room sold. A pilot would measure report hours per week and days from an anomaly starting to someone acting on it.
Limitations
- SQLite only. Postgres via
DATABASE_URLis designed (sqlglot can transpile the guarded SQL), not built. - Demo roles without sign-in. A real deployment would use the stage 7 tool server's roles or SSO.
- I wrote the eval questions with the schema in front of me. A client's real questions will be messier.
- Conversation history lives in the browser tab (session state), not a database. Stage 6 stores it in Postgres.
- On Streamlit Cloud the request log and daily counters reset when the app restarts.
What's next
- Deploy on Streamlit Community Cloud and connect a Slack channel
- Postgres with a read-only role per hotel group
- Questions written by a real revenue manager, not by me