Skip to content
Louvre Ops DB

Methods

How the revival was built and checked

Where every number on this site comes from, how it was tested, what it assumes and where it is weak. The AI features are documented here too: what they do, what they never do and how they are measured.

Where the data comes from

Data provenance

The 2020 design is read from the submitted MySQL Workbench file, coursework/1118472.mwb (SHA-256 92cb41ece6734658…, checked by a unit test), by scripts/parse_mwb.py: 16 tables on the diagram, their columns, keys, foreign keys, captions and positions. The Chen diagram is redrawn from the original image at the same coordinates; the 13 assumptions are quoted word for word. Nothing in coursework/ was edited.

The data is synthetic: 66,219 rows generated from seed 20200403 for 1 March 2015 to 29 February 2020. Only the brief's fixed facts (entrances, wings, languages, prices, slot size, the Leonardo exhibition) are real. See the data card and DR-002.

The brief is paraphrased from the University of Melbourne's INFO20003 assignment specification, which is not reproduced.

What was done

Methods

  1. Forward engineering. The model is written out as the MySQL script Workbench would generate and run on a throwaway MySQL 26.7.0 server: it creates 13 of 16 tables, and the errors are kept verbatim.
  2. Replay. The same five synthetic years are inserted into a faithful SQLite port of the 2020 tables, row by row, recording every refusal and its reason: 43,521 of 64,130 rows fit.
  3. Business questions. The brief's ten questions are answered in SQL on the refined schema and checked to give identical answers in three SQLite engines (sql.js, libSQL and Python's sqlite3); two answers are recomputed in plain TypeScript. Each gets a verdict on the 2020 design: 6 yes, 2 partly, 2 no.
  4. The ERD, redrawn. The interactive diagrams are generated from the parsed model, not traced, and the original exports are shown beside them for comparison. Exports (SVG, PNG) are drawn from the same data.
  5. Text-to-SQL (optional). A model the visitor chooses writes one query from the documented schema; a validator, SQLite's read-only mode and a time limit decide what runs (DR-003).
  6. Index experiments. Seven queries timed on two copies of the database, with and without the secondary indexes, in repeated interleaved rounds (30 in the reference run, 15 when re-run in a browser; results).

How results are judged

Evaluation design

Text-to-SQL. 22 questions: 20 answerable with hand-written gold SQL (the brief's ten, paraphrased with every parameter stated, and ten more) and 2 that the data cannot answer. The metric is execution accuracy: the model's query and the gold query run on the same read-only database, and match when they return the same rows (columns matched by content, order scored only for rankings, numbers within 0.01). Malformed, truncated or refused replies count as wrong; infrastructure failures are excluded and counted.

  • Every proportion has a Wilson 95% interval; n is always shown.
  • Median latency has a percentile-bootstrap interval (2,000 resamples, seed 20003); repeated runs report their spread and how many questions got the same outcome each time.
  • Two runs on the same questions are compared as paired data: an exact McNemar test on the discordant questions and Tango's score interval for the difference in accuracy.
  • The comparator is the hand-written SQL, which answers every question by construction; the harness measures how close a model gets to it. Anyone can also grade their own SQL with it, without a key.

Index experiments. For each query, both database copies run in a seeded random order within each round, so drift cannot favour one side; each sample repeats the query for at least 4 ms to beat timer resolution. The report gives each median with a bootstrap interval, the ratio of medians with its own interval, the plans and a check that both copies return the same rows.

The interval and test code (web/src/lib/stats/) is unit tested against numpy, scipy and statsmodels (scripts/verify_stats.py) and R's PropCIs (scripts/verify_paired_diff.R).

What is taken as given

Assumptions

  • The 2020 model is judged on what it says, with MySQL's current defaults; where MySQL 8.0 (current in 2020) behaved differently, the design review says so.
  • Business questions that leave a parameter open (the financial year, “the last six months”) use the stated choices; the dataset's “today” is 29 February 2020.
  • A visitor is a ticket's first entry of the day; re-entries are not new visitors.
  • The synthetic data's distributions are invented; they are plausible, not calibrated to the museum.

Where it is weak

Limitations

  • The data generator was written alongside the refined schema, so the refined schema's clean result is partly by construction; the 2020 replay is the fairer test (DR-001).
  • The database is small (about two visiting parties a day). Index timings describe a small database and understate what indexes do at real volume.
  • The evaluation set is small (20 answerable questions) and written by the person who wrote the gold SQL; its intervals are wide, and no model results are published.
  • The refined schema is SQLite-only and has not been run on MySQL.
  • The design review was written by the 2020 author, five years on; no independent reviewer.

Next time

What I'd change

  • Generate test data independently of the refined schema and add a scale parameter (10× and 100×).
  • Grow the evaluation to 50 or more questions with a held-out half, so prompts cannot be tuned to it.
  • Port the refined schema to MySQL and re-run the forward-engineering check on it.
  • A column-level allow-list for model-written SQL and a hash-chained audit log.

Transparency

AI use statement

What AI does here

  • On /ask, writes one SQL query for a visitor's question, with a short explanation.
  • On /eval, does the same for a fixed set of questions so its accuracy can be measured.

What it never does

  • See the data, run anything itself, or change the database.
  • Run on this site's server, or use a key the site provides (there is none).
  • Produce any other text on the site: everything outside /ask and /eval is written by hand.
  • Optional and bring-your-own-key. The site works fully without a key. Providers: Anthropic (default: Claude Haiku 4.5 or Claude Sonnet 5.5) or OpenAI (default model id gpt-5-mini, editable). The key is kept in sessionStorage unless the visitor ticks “remember on this device”, can be forgotten at any time, and is sent only to the provider, directly from the browser.
  • Data sent to the provider: the question and a fixed prompt (rules and documented schema, version t2s-2026-10-07). Never rows of data. All data on the site is synthetic in any case.
  • Checks before anything runs: the reply must match a fixed JSON shape; the SQL must pass the validator (one read-only SELECT on documented tables, LIMIT 200 added or capped at 500); it runs on an in-memory, read-only copy in the browser with a 10-second limit.
  • Human in the loop: every output is labelled AI-generated; the visitor accepts, edits or rejects each answer, and edited SQL is validated again.
  • Audit: every call is logged in the browser (question, provider, model, prompt version, SQL, validator verdict, sandbox result, latency, tokens, decision), viewable and exportable at /ai-log. Keys are never logged.
  • Informed by the Australian Government's policy for the responsible use of AI in government, the EU AI Act's transparency principles and the NIST AI Risk Management Framework. This is not a claim of compliance with any of them.

docs/model-card.md

Model card: the text-to-SQL assistant

The assistant behind /ask and /eval. It is not a model trained for this project: it is a third-party language model, chosen and paid for by the visitor, used through one fixed prompt and wrapped in checks. This card describes that whole system.

Model details

  • Models: Anthropic Claude Haiku 4.5 (claude-haiku-4-5, the default) or Claude Sonnet 5.5 (claude-sonnet-5-5), or an OpenAI chat model whose id the visitor enters (default gpt-5-mini). Ids were checked in October 2026.
  • Settings: Haiku runs at temperature 0 with no extended thinking; Sonnet 5.5 does not accept a temperature and runs with adaptive thinking at effort "medium". OpenAI models run with their defaults.
  • Prompt: one system prompt (version t2s-2026-10-07, recorded with every call) containing the rules and the documented schema: every table and view, row counts, column types, keys and plain-English descriptions, and the data conventions (dates, cents, what counts as a visitor).
  • Output contract: {answerable, sql, explanation, assumptions}, enforced by the provider's structured-output mode and checked again with zod in the browser.
  • Owner: Sunchuangyu (Rin) Huang. The providers own the models.

Intended use

Turning a plain-English question about the synthetic Louvre ticketing database into one read-only SQLite query, so a visitor can explore the data without writing SQL, and so the evaluation harness can measure how often a model gets it right. It is a demonstration of governed text-to-SQL, not a production analytics tool.

Out of scope

Anything outside this database; real visitor data (there is none); writing to the database; answering questions the data cannot answer (the model is told to decline); decisions about people.

Data sent to the provider

The visitor's question and the system prompt (rules and schema). Never rows of data, never the audit log, never the visitor's identity beyond what their own key and network reveal to their provider. The key goes only to the provider the visitor chose, straight from their browser.

Evaluation

  • Design: 22 questions written before any model was run: the brief's 10 business questions, paraphrased with every parameter stated; 10 more covering the rest of the schema; 2 the data cannot answer. Each answerable question has hand-written gold SQL that passes the same validator.
  • Metric: execution accuracy. The model's query and the gold query run on the same read-only database; they match when, for some assignment of gold columns to the model's columns, both return the same rows (order scored only when the question asks for a ranking; numbers within 0.01; text ignoring case). Malformed, truncated and refused replies count as wrong; only infrastructure failures (key, quota, network) are left out, and the page says how many.
  • Uncertainty: Wilson 95% intervals for every proportion; a percentile-bootstrap interval (seed 20003) for median latency; up to three repetitions per model with the spread and the share of questions that got the same outcome each time; paired comparison of two runs with an exact McNemar test and Tango's score interval for the difference.
  • Baseline: the hand-written gold SQL, which answers all 20 answerable questions by construction.
  • Results: none published. The project has no AI budget and does not run anyone else's key, so there are no self-reported scores. Anyone can run the harness with their own key; results stay in their browser and export as JSON or CSV. With 20 answerable questions a 95% Wilson interval is between about 16 percentage points wide (at 0/20 or 20/20) and 40 (at 10/20), so a run is a smoke test, not a leaderboard.

Known failure modes

These are the traps the questions were written to probe; they are the expected ways for a model to go wrong.

  • Counting every entrance scan instead of first entries (re-entries inflate visitor counts).
  • Counting order rows instead of summing order-line quantities for tickets sold.
  • Integer division in percentages (count(*) / total gives 0 in SQLite).
  • Date handling: wrapping a column in date() works but defeats indexes; month and weekday codes from strftime.
  • Ranking questions answered without the requested order, or without a LIMIT.
  • Inventing columns or tables (blocked by the validator or rejected by SQLite, and recorded).
  • Writing SQL for the unanswerable questions instead of declining.

Safeguards

The validator (single SELECT, allow-listed tables, no PRAGMA, ATTACH, writes, recursion or file functions, LIMIT added or capped), SQLite's query_only mode on an in-memory copy, a 10-second time limit, a visible "AI-generated" label on every output, a human decision on every Ask answer, and an audit log of every call (DR-003).

Ethical considerations

All records are synthetic, so no personal data can reach a provider through the data. The visitor pays for the calls with their own key, so the page states how many calls an action makes before it runs. The design is informed by the Australian Government's policy for the responsible use of AI in government, the EU AI Act's transparency obligations and the NIST AI Risk Management Framework; it does not claim compliance with any of them.

Caveats

Model behaviour changes between versions and over time; the prompt version and the model id the provider reports are recorded with every call so that results can be compared like with like. The evaluation set is small and written by the same person who wrote the gold SQL.

docs/data-card.md

Data card: the synthetic Louvre Ops dataset

The demo database web/data/louvre.db, used by every page of the site.

Provenance

Generated by a deterministic simulation (web/src/lib/seed/, seed 20200403) and built by web/scripts/build-db.ts into the refined schema (web/data/ddl/refined.sql). The only real-world facts are the brief's: the five entrances, three wings, 13 audio-guide languages, prices (EUR 15 online, 17 on site, 8 for a guide, 4 for the app), 15-minute exhibition slots of up to 45 places, and a Leonardo da Vinci 500th-anniversary exhibition. Everything else (people, banks' branches, other exhibitions, volumes, behaviour) is invented. No real visitor, payment or booking data was used at any stage.

Contents

About 66,000 rows in 19 tables covering 1 March 2015 to 29 February 2020: payments and their card or cash details, orders and order lines, tickets, entrance and wing scans, audio-guide devices and hires, exhibitions, slots, bookings and Hall Napoleon admissions. Card numbers are stored masked (first and last four digits); there is no CCV. Names are drawn from short invented lists.

Intended use

Testing the 2020 design and the refined schema against the brief's rules, answering the business questions, the SQL playground, the text-to-SQL evaluation and the index experiments.

Known limitations

  • Volumes are tiny compared with the real museum (about 2 visiting parties a day), to keep the file small for the browser. Timings measured on it describe a small database.
  • Every distribution is an assumption (seasonality, transport mode by entrance, guide hire and booking show-up rates). Answers describe the simulation, not the Louvre.
  • The generator was written alongside the refined schema, so it fits that schema partly by construction (DR-001, DR-002).

Reproducibility

pnpm db:build in web/ regenerates byte-identical files; the unit tests and scripts/verify_db.py (Python's sqlite3) check the answers.

Ethical considerations

Synthetic by design, so it can be published, shared with AI providers (only the schema is, in practice) and queried by anyone. It must not be presented as real attendance or revenue data.

docs/decisions

Decision records

Context, the decision, the options, why, what happened (weak numbers included) and what I would change. Records are never edited after acceptance; a later one supersedes them.