Column descriptions: cents and domains

Description

The serialized schema —DDL or compact form— tells the model what the columns are called and what type they are. But there's a layer of meaning that neither the name nor the type captures: that price_cents is an INTEGER, yes, but in cents; that status is TEXT, but only takes 'confirmed' or 'cancelled'. That layer is column descriptions, and it's where a poor context turns into a rich one.

In this lesson we look at two kinds of description that make the difference between correct SQL and SQL that runs but lies: the unit (cents) and the domain (the accepted values). And we see it run for real: the same model, with and without the note, producing SQL that gives different results.

Connection to the module: lessons 02 and 03 showed the gap and the context's skeleton. This one adds the semantic flesh —what the schema doesn't declare but the model needs to know—. Along with relationships (lesson 06), it's what raises the quality of the generated SQL the most.

An analogy: the recipe that says "2 cups" but not of what

A recipe that says "add 2 of flour" is ambiguous: two cups, two kilos, two tablespoons? The number is there, the unit is missing, and the cake comes out wrong. Even worse if the recipe says "bake until done" without saying at what temperature or for how long: everyone interprets "done" their own way.

The schema without descriptions is that recipe. price_cents INTEGER is "2 of flour": there's a number, but the model doesn't know if it's cents or dollars, and if it guesses wrong, the answer comes out off by a factor of 100. status TEXT is "until done": the model doesn't know which values count as "done," and it guesses 'active' when the kitchen uses 'confirmed'. Descriptions are what add the unit and the domain: "2 cups of flour," "confirmed = 'confirmed'."

Description type 1: the unit (money in cents)

This is the canonical example, and the one that's cost the most real money in real systems. Reservo stores money in cents, as integers: Focus at 2500 (not 25.00), a booking at 6000 (not 60.00). The INTEGER type is correct —cents are exact integers, without floating point's errors—, but the type doesn't say "cents." You see price_cents INTEGER and the model has to guess the unit.

Worked example — the SQL with and without the note

User's question:

"How much did Reservo bring in total, counting only confirmed bookings?"

With POOR context (the schema says price_cents INTEGER, nothing more), a realistic SQL claude-sonnet-5 would produce is:

-- Example "model" SQL, with poor context (doesn't know it's cents).
SELECT SUM(price_cents) AS total FROM bookings WHERE status = 'confirmed';

Run it against Reservo:

SELECT SUM(price_cents) AS total FROM bookings WHERE status = 'confirmed';

What to expect:

┌────────┐
│ total  │
├────────┤
│ 211900 │
└────────┘

The SQL ran without error. It returned 211900. And here's the trap: the assistant would report "$211,900" —an absurd figure for a coworking space over six months—. The real total is $2,119.00. The error is off by a factor of 100, and the engine doesn't catch it because, at the SQL level, summing cents is perfectly valid; what's wrong is the interpretation.

With RICH context (the schema adds price_cents INTEGER -- money in CENTS), the model knows it has to divide by 100 to give dollars. A realistic SQL is:

-- Example "model" SQL, with the "money in cents" note.
SELECT ROUND(SUM(price_cents) / 100.0, 2) AS total_usd
FROM bookings WHERE status = 'confirmed';

SELECT ROUND(SUM(price_cents) / 100.0, 2) AS total_usd
FROM bookings WHERE status = 'confirmed';

What to expect:

┌───────────┐
│ total_usd │
├───────────┤
│ 2119.0    │
└───────────┘

Two thousand one hundred nineteen dollars. The only difference between the misleading 211900 and the correct 2119.0 was one line of description in the context: telling the model the column is in cents.

The 100.0 detail. Notice the rich version divides by 100.0, not by 100. In SQLite, 211900 / 100 gives 2119 (integer division, the decimal gets lost), while 211900 / 100.0 gives 2119.0 (floating-point division). With this round total it doesn't matter, but with 211950 / 100 you'd get 2119 instead of 2119.5. Good context doesn't just say "cents"; it can say "divide by 100.0 for dollars" and save the model that trap too.

Description type 2: the domain (what values a column accepts)

The second kind of description is domains: the list of values a column can take. status isn't "any text"; it's 'confirmed' or 'cancelled'. tier is 'basic' or 'pro'. kind is 'charge' or 'refund'. If the model doesn't know the domain, it invents values that sound plausible but don't exist.

Remember the silent failure from lesson 02: without knowing status's domain, the model filters by 'active', the query runs and returns zero rows. Let's revisit it, now focused on the cure:

-- What happens without knowing the domain: filtering on a value that doesn't exist.
SELECT COUNT(*) AS n FROM bookings WHERE status = 'active';

What to expect:

┌───┐
│ n │
├───┤
│ 0 │
└───┘

Zero. The assistant would answer "there are no active bookings," a lie. With the domain in the context (status TEXT -- 'confirmed' or 'cancelled'), the model never writes 'active'; it uses the real value. Confirm it by looking at the values that actually exist:

SELECT status, COUNT(*) AS n FROM bookings GROUP BY status;

What to expect:

┌───────────┬────┐
│  status   │ n  │
├───────────┼────┤
│ cancelled │ 3  │
│ confirmed │ 20 │
└───────────┴────┘

The only values are cancelled and confirmed. That's the domain, and it's exactly what the description hands the model.

The DDL's gift: the CHECKs are already domains

Here it's worth remembering something from lesson 03: if you serialize the schema as full DDL, the domains already come included, because Reservo declares them as CHECK constraints:

status TEXT NOT NULL DEFAULT 'confirmed'
            CHECK (status IN ('confirmed','cancelled'))

That CHECK (status IN ('confirmed','cancelled')) is the domain description, already in the schema. A model that sees the DDL sees the domain without you having to say it separately. That's why the DDL is such a good starting point when it fits: the domains come for free. You only have to add by hand what no constraint declares —like the "cents" unit—.

If you use the compact form (which drops the CHECKs), you have to put the domains back as descriptions. Either way, the goal is the same: the domain has to reach the model.

Going deeper: where descriptions come from and how they're stored

Descriptions are the part of the context that cannot be automatically introspected from just any schema. The DDL gives you the CHECKs if the designer put them in, but the note "this is in cents" doesn't live anywhere in the database —it's business knowledge—. That's why descriptions are written once, by hand, and stored alongside the code that generates the context.

The usual form is a dictionary mapping table.column to its note:

COLUMN_NOTES = {
    "rooms.hourly_cents":    "hourly price in CENTS (2500 = $25.00)",
    "members.tier":          "member's plan: 'basic' or 'pro' (pro members pay 20% less)",
    "bookings.status":       "'confirmed' or 'cancelled' (only confirmed counts as revenue)",
    "bookings.price_cents":  "booking's total price in CENTS, discount already applied",
    "payments.amount_cents": "amount in CENTS",
    "payments.kind":         "'charge' or 'refund'",
}

Then, when assembling the context, for each column you look up its note in the dictionary and add it as a comment. This is exactly what the mini-project's function (lesson 08) will do. The advantage of keeping it in a separate dictionary: descriptions live versioned alongside your code, they don't get lost, and they can be improved without touching the introspection.

What deserves a description? You don't need to describe everything —id or name explain themselves—. It's worth describing:

• Non-obvious units: cents, milliseconds, bytes, degrees.
• Domains that aren't a CHECK: accepted values the schema doesn't formally restrict.
• Business semantics: that "only confirmed counts as revenue," that "pro members get a discount" —rules the model can't deduce from the schema—.
• Misleadingly named columns: an amount column that actually stores a percentage, or a date that's text and not an actual date.

This is the crux of the text-to-SQL BIRD benchmark: unlike earlier benchmarks, BIRD measures how much "external knowledge" —exactly these descriptions— helps the model get realistic schemas right. BIRD's lesson, in one sentence: the bare schema isn't enough; good descriptions raise accuracy in a measurable way.

Common mistakes

1. Assuming the type communicates the unit. INTEGER doesn't say "cents," TEXT doesn't say "ISO date," REAL doesn't say "kilometers." The type is the representation; the unit is semantics you have to add. This is the mistake that produces the classic factor-of-100 error in money.

2. Describing the obvious and forgetting what matters. Spending a description on id INTEGER -- unique identifier is noise; the model already knows that. What matters is price_cents -- CENTS and status -- 'confirmed'/'cancelled'. Describe what the model can't deduce.

3. Writing the descriptions and then using the compact form that drops them. If you choose the compact form because it saves tokens, but don't put the domains and units back as descriptions, you've cheapened the context at the cost of reintroducing the hallucinations. Cheap compact form + key descriptions is the winning combination, not the bare compact form.

4. Trusting that the model "will know it's money from the name price." Sometimes it gets it right —price suggests money—, but it can't guess the unit from the name. And there are worse names: amount, value, total say neither the currency nor the scale. The explicit description removes the guesswork.

5. Writing the descriptions inside the introspection code. Keep them in a separate dictionary (COLUMN_NOTES), not embedded in the function that loops over the tables. That way they're versioned on their own, easy to review, and don't get lost when you refactor the introspection.

Exercises

Exercise 1: Which Reservo columns deserve a description?

Go through Reservo's four tables (rooms, members, bookings, payments). List the columns that do deserve a description and the ones that don't, with one justifying sentence per group.

Exercise 2: Fix the misleading report

An assistant with poor context answered: "Boardroom brought in 105600." Knowing money is in cents and that total includes cancelled bookings, write the query that gives Boardroom's correct revenue in dollars, counting only confirmed bookings.

SELECT ROUND(SUM(b.price_cents) / 100.0, 2) AS revenue_usd
FROM bookings b
JOIN rooms r ON b.room_id = r.id
WHERE r.name = 'Boardroom' AND b.status = 'confirmed';

┌─────────────┐
│ revenue_usd │
├─────────────┤
│ 1056.0      │
└─────────────┘

Exercise 3: Write descriptions that prevent an error

Imagine a new table in Reservo: discounts(id INTEGER PK, member_id INTEGER, pct INTEGER, valid_until TEXT), where pct stores the discount percentage as an integer (20 means 20%, not 0.20) and valid_until is an ISO date. Write the description for pct and valid_until so a model doesn't make a misinterpretation error.

COLUMN_NOTES = {
    "discounts.pct":         "discount percentage as an INTEGER (20 = 20%, NOT 0.20)",
    "discounts.valid_until": "expiration date, ISO text 'YYYY-MM-DD' (compare as text)",
}

Summary and next step

• The serialized schema says what the columns are called and what type they are, but not their meaning: the unit and the domain. Descriptions add that layer.
• The unit: price_cents is INTEGER but in cents. Without the note, the model sums raw (211900) and reports a factor-of-100 error; with it, it divides by 100.0 and gives 2119.0.
• The domain: status only accepts 'confirmed'/'cancelled'. Without knowing it, the model invents 'active' and returns zero rows —the silent failure—.
• The DDL gives away the domains that exist as CHECKs; the "cents" unit doesn't live in any constraint and always has to be added by hand.
• Descriptions are stored in a separate dictionary (COLUMN_NOTES), versioned with the code. Describe units, domains, and business semantics; leave the obvious alone.

Next lesson: Sample rows in the context — You'll see that a few rows per table teach the model the data's real format (ISO dates, the shape of values) better than any description.