Schema reference

A schema is the tables an assistant designs, with the numbers the data must hold beside them. Misata reads each key below and refuses any other with the nearest it does read, so a slip is never dropped silently. This page is generated from the engine's schema reader, as the MCP server's instructions are.

Schema format#

{"tables": [{"name": "patients", "rows": 200, "columns": [{"name": "patient_id", "primary": true}, {"name": "arm", "type": "category", "values": ["placebo", "treatment"], "weights": [0.5, 0.5]}]}, {"name": "visits", "parent": "patients", "per_parent": 4.0, "columns": [{"name": "visit_id", "primary": true}, {"name": "patient_id", "references": "patients.patient_id"}, {"name": "visit_date", "type": "date"}]}]}

Top level:

• tables: the tables, parents first
• numbers: what the data must hold exactly, each a stated number (below)
• window: {"start": "2025-01", "end": "2025-12"}: the data's months, at most ten years; without it, the months the numbers state

Table keys:

• name: the table's name
• columns: its columns
• rows: how many rows; a plan the stated numbers may resize
• keep_size: true keeps rows exactly, any number stated on the table carried by its amounts
• parent: the table each row belongs to
• per_parent: rows per parent row, on average (4.0)
• required: every parent has at least one (an order's lines)
• once: every parent has at most one (a dispute of a payment)
• where: only parents where it holds have rows here: "status != 'cancelled'" for payments
• one_at_a_time: {"resource": "room_id", "start": "check_in", "end": "check_out", "at_once": 1}: a room, a doctor or a vehicle never double-booked
• data: the table's rows themselves, for what a business decides rather than samples (its catalog, menu or price list: "data": [{"name": "Linen Duvet Cover", "category": "Bedding", "price": 89.0}, ...]); with no model key, give names this way or they come back as numbered placeholders
• top_share: the share of its rows the busiest 20% of parents hold: 0.55, a few shippers hold most loads
• every: one row per parent in each period it is active: "week", or {"period": "week", "time": "delivery_date", "day": "subscribers.delivery_weekday"} to fall on the parent's own weekday (a weekday name) or day of the month (1-28); leave per_parent, rows, required and once out, and give the time an after on the parent's start; periods: day, week, month, quarter, year
• note: words about the table for whoever writes its content; it changes no rule

Column keys:

• name: the column's name
• type: integer (whole numbers) | float (numbers with a fraction) | money (an amount, to the cent) | text (words written to fit the column) | date (a day) | datetime (a moment) | boolean (true or false) | category (one of its values); money is an amount whatever its name, and a note such as "in EUR" names its currency
• primary: true on the table's own key
• references: "table.column" it points to
• values: the labels it takes; with a formula, every label the formula can give (where(delivered_at <= promised_at, 'on_time', 'late') with values ["on_time", "late"])
• weights: each value's share, in the order of values
• formula: an expression over other columns; a parent's column is parent.column@reference, as reservations.total_amount@reservation_id; today is the data's last day; where(cond, a, b), round, days(n); lognormal(median, sigma), exponential(mean) and uniform(lo, hi) draw per row, so a time that scales with a number is "pickup_at + hours(lanes.miles@lane_id / 45 + lognormal(6, 0.8))". A formula wins over values, and a reference copied from what the row belongs to is a formula naming that parent's reference ("subscribers.plan_id@subscriber_id")
• pool: a realistic value bank (below): geo.{address, city, country, currency, latitude, longitude, phone, phone_code, postal_code, region, timezone}; org.company; person.{culture, email, first_name, full_name, full_name_ar, gender, last_name, nationality, work_email}
• range: [low, high], or two dates on a date column: ["2019-01-01", "2025-12-31"] for customers who joined years ago, either one null for open
• dist: how a number spreads across its range (below): typical (most near the low end, a long tail); even (any value equally likely); bell (centred, tapering both ways)
• decimals: digits after the point, 0 to 6; money is 2
• unique: true: no two rows share a value
• not_null: true: never empty
• after: a time that follows another of its row: {"column": "check_in", "gap": 21, "unit": "days"}; a parent's time is "parent.column". A gap of 0 is the order alone: {"column": "customers.signed_up_at", "gap": 0} puts each order any time after its customer signed up, where a gap of 2 days puts it about two days after. A time after another keeps its own rhythm, weekdays and months; a gap is in days, hours, minutes
• before: a time that comes a while before another, in the same form as after: {"column": "due_at", "gap": 3, "unit": "days"}; a gap is in days, hours, minutes
• rhythm: the clock a time keeps (below): consumer (people on their own time: orders, streams, busiest in the evening and as busy at the weekend); business (working hours on working days); machine (any hour); human (a working week's rhythm, the clock a time keeps when none is said)
• months: {"7": 1.4} or {"Dec": 2.0}: a month's weight against 1 for the others
• weekdays: 7 weights, Monday first
• moves_with: a number that rises with another of its row or its parent's, by rank: {"column": "customers.income", "strength": 0.6} on an order's total; -0.95 to 0.95, negative when it falls as the other rises; both keep their own values, only which go together changes
• only_when: an expression over the row (or parent.column@reference); the column is empty where it is false: "status == 'delivered'" on delivered_at
• by: a spread set by another column of the row (or "parent.column") that has values: {"column": "mode", "cases": {"LTL": [150, 3500, 12000], "FTL": [15000, 32000, 45000]}} gives a number [low, typical, high] per value; on a category each case is weights in the order of its values or {value: weight} (platform by country: {"IN": {"ios": 0.05, "android": 0.95}}). Values with no case keep the column's own range or weights
• pick_by: on a reference, weighs each row it may pick by the referenced table's columns ("1 / products.price ** 0.8": cheap items sell more)
• top_share: on a reference, the share of rows the busiest 20% of referenced rows get, 0.2 (even) to 0.95
• note: words about the column for whoever writes its content; it changes no rule

How a row's columns go together#

In a business's records a row's columns depend on one another, and an analyst reads those dependencies first. A column with no rule beside it is drawn apart from the rest of its row, so write each one you know:

• one worked out from others is a formula (an amount from its quantity and unit price, a due date from its issue date and terms);
• one whose spread or mix differs by a category of its row or its parent is by (a larger plan's seats, a channel's basket, a region's methods of payment);
• one that rises or falls with another number is moves_with, with how strongly;
• one that exists only in some states is only_when (a refund on a returned order, a close time on a closed case). Misata keeps every value inside the range and mix written for it; these rules decide which values go together on a row, and the stated numbers still hold exactly.

Numbers that must hold exactly: numbers and window#

State the story's numbers beside the tables and each one is held exactly, any that cannot be is said with why, and the answer key carries the SQL that reads each back. Every number names its table and columns:

• kind: which of the kinds below
• table: the table it is stated on
• says: the person's own words for it, shown on the plan and the certificate
• why: for an unbound number, why no table holds it

Kinds:

• flow: a month's sum of a column, or how many rows happen when "column" is left out: {"kind": "flow", "table": "orders", "column": "total", "time": "placed_at", "where": {"column": "status", "in": ["completed"]}, "points": {"2025-01": 120000, "2025-12": 260000}}; months between two stated ones follow a straight line; a sum may be read by a time copied from the parent or worked out from another, as revenue by each booking's flight departure, while how many rows happen is read by a time the rows are drawn at; "growth": 0.05 grows it that share each month from the one month in "points", or from the rows' own scale when none is stated
• level: a balance in force at each month's end, MRR: {"kind": "level", "table": "subscriptions", "column": "mrr", "start": "started_at", "end": "ended_at", "points": {...}, "group": "plan", "shares": {"enterprise": 0.45}}; "changes": ["expansion_mrr@expanded_at", "-contraction_mrr@contracted_at"] are amounts that move it on their times, "months" names the months a split holds in when no points are stated, and "growth" grows it from its one stated month, as a flow's does
• leaving: rows leave at a monthly rate of those still in, churn: {"kind": "leaving", "table": "subscriptions", "start": "started_at", "end": "ended_at", "monthly": 0.02, "events": [{"start": "2025-07", "end": "2025-09", "monthly": 0.05}]}
• rate: a share of rows with a value: {"kind": "rate", "table": "orders", "column": "status", "values": ["cancelled"], "rate": 0.06}; with "time" it holds in each month, with "where" over some rows
• share: how a column's sum splits by a category: {"kind": "share", "table": "orders", "column": "total", "group": "channel", "shares": {"web": 0.5, "app": 0.35}}, "where" for some rows
• ratio: a monthly average over the rows that have a value, as AVG() reads it: CSAT over the tickets answered, an open ticket with no score is not counted: {"kind": "ratio", "table": "tickets", "column": "csat", "time": "opened_at", "per": {"table": "tickets", "time": "opened_at"}, "points": {...}}, or per row of another table: "per": {"table": "orders", "time": "placed_at"} for revenue per order on its lines, "where" in per for some rows. The column a ratio moves is drawn on the row, not worked out by a formula. One sum over another (a take rate, fees over order value) names the sum it is over as per's "column": "per": {"table": "orders", "time": "placed_at", "column": "total"}; rows per each different holder (orders per customer a month) leave "column" out and name the holder's reference as per's "distinct": "per": {"table": "orders", "time": "placed_at", "distinct": "customer_id"}
• count: a table's size: {"kind": "count", "table": "accounts", "rows": 900}
• retention: of the players who installed, the share with a session on calendar day N after their install date: {"kind": "retention", "table": "players", "start": "installed_at", "child": "sessions", "time": "started_at", "points": {"1": 0.40, "7": 0.18, "30": 0.07}}; "window": "by" counts a session on day N or later, "unit": "week" or "month" (each month's signups, by calendar month)
• unbound: a number the tables cannot hold, kept with what it says and why: {"kind": "unbound", "says": "NPS of 45", "why": "no table records survey answers"}; it reads not met

A split names only what the person said; the values it leaves out share the rest, each at its own price on a ladder in the order the column lists them (list plans cheapest first; state each plan's share when you know it). With ddl, pass the numbers and window as schema={"numbers": [...], "window": {...}}: the tables come from the DDL.