Layer 1: Assembling the Schema Context

Description

We start building the assistant with its first layer: the schema context. It's the piece that makes the model "know" what tables and columns Reservo has before writing a single line of SQL. You built it completely in Module 2 — the build_schema_context(db_path) function that introspects the database with sqlite_master, PRAGMA table_info, and PRAGMA foreign_key_list, and assembles a rich block with DDL, descriptions, samples, and relationships. Here we don't rebuild it: we wire it in as the assistant's layer 1 and put it to run.

This is the moment to fix the project's structure. The assistant is going to live across several files that import from each other, and this is the first: schema_context.py. You'll watch the function run over Reservo, produce the real context, and — crucially — understand where that output goes: straight into layer 2's system prompt. Each layer produces what the next one consumes; starting with the context is starting from the root.

Connection to the module

This lesson opens the assembly. Lessons 03 through 07 connect the other five layers; 08 brings them all together into build_assistant(). The schema context is first because everything else depends on it: without a good context, layer 2's prompt has nothing to give the model, and the resulting SQL will hallucinate names. M2's function is the source of truth about the schema; here we plug it in.

Analogy: the blueprint handed to the contractor

Imagine hiring someone very capable to remodel a house, but who has never been inside it. No matter how talented they are, if you don't give them the blueprint — where each room is, its dimensions, what's load-bearing and what isn't — they'll knock down the wrong wall. The blueprint doesn't make them smarter; it makes them get it right in this specific house.

The schema context is that blueprint. The model is capable of writing excellent SQL, but it hasn't "been inside" Reservo: it doesn't know the table is called bookings and not reservations, that the money is in cents, or that there's an FK from bookings.room_id to rooms.id. The build_schema_context function draws that blueprint automatically from the real database — so it's always up to date — and hands it to the model inside the prompt. The best thing about a blueprint generated by code, instead of drawn by hand, is that when the house changes (you add a table), you rerun the function and the blueprint redraws itself.

The piece you reuse (from M2)

Let's recall what the function does, without re-deriving it — that was Module 2. build_schema_context(db_path) goes through the database's tables and, for each one, assembles four layers of information:

1. Structure: the columns with their type and the primary key (PRAGMA table_info).
2. Descriptions: the business knowledge the database doesn't store — units, domains, semantics — from the TABLE_NOTES and COLUMN_NOTES dictionaries (the only hand-written part).
3. Relationships: the foreign keys (PRAGMA foreign_key_list).
4. Samples: a couple of rows per table, to teach the data's format.

In the project, this lives in two files. First descriptions.py, the only one with hand-written knowledge:

# descriptions.py -- the only hand-written part (M2); the rest gets introspected.

TABLE_NOTES = {
    "rooms":    "coworking rooms that can be booked",
    "members":  "members who make bookings",
    "bookings": "a booking of a room by a member",
    "payments": "money movements for each booking",
}

COLUMN_NOTES = {
    "rooms.hourly_cents":    "price per hour in CENTS (2500 = $25.00 USD)",
    "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":  "total booking price in CENTS, already discounted",
    "bookings.start_at":     "start, ISO text 'YYYY-MM-DD HH:MM:SS'",
    "bookings.end_at":       "end, ISO text 'YYYY-MM-DD HH:MM:SS'",
    "payments.amount_cents": "amount in CENTS",
    "payments.kind":         "'charge' or 'refund'",
}

And then schema_context.py, with the introspection function (the same one from M2, here as a project file):

# schema_context.py
import sqlite3
from descriptions import TABLE_NOTES, COLUMN_NOTES


def build_schema_context(db_path, sample_rows=2):
    """Introspects the DB (M2) and assembles the rich context block for the prompt."""
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row

    tables = [r[0] for r in conn.execute(
        "SELECT name FROM sqlite_master "
        "WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
    ).fetchall()]

    out = ["Database schema (SQLite). Money is in CENTS (INTEGER).", ""]
    for table in tables:
        note = TABLE_NOTES.get(table, "")
        out.append(f"TABLE {table}" + (f"  -- {note}" if note else ""))
        for c in conn.execute(f"PRAGMA table_info({table})").fetchall():
            line = f"  {c['name']} {c['type']}" + (" PK" if c["pk"] else "")
            desc = COLUMN_NOTES.get(f"{table}.{c['name']}")
            if desc:
                line += f"  -- {desc}"
            out.append(line)
        for fk in conn.execute(f"PRAGMA foreign_key_list({table})").fetchall():
            out.append(f"  FK {fk['from']} -> {fk['table']}.{fk['to']}")
        rows = conn.execute(f"SELECT * FROM {table} LIMIT {sample_rows}").fetchall()
        if rows:
            header = list(rows[0].keys())
            out.append(f"  SAMPLE ({', '.join(header)}):")
            for r in rows:
                out.append("    " + " | ".join(str(r[k]) for k in header))
        out.append("")
    conn.close()
    return "\n".join(out)

If you want the detail of why each line is there (what sqlite_master does, why the samples, how you pick what to describe, the token budget), go back to Module 2. What matters here is using it.

Worked example: running layer 1

With descriptions.py and schema_context.py in your working directory (next to reservo.db), run the function:

from schema_context import build_schema_context

print(build_schema_context("reservo.db"))

What to expect:

Database schema (SQLite). Money is in CENTS (INTEGER).

TABLE bookings  -- a booking of a room by a member
  id INTEGER PK
  room_id INTEGER
  member_id INTEGER
  start_at TEXT  -- start, ISO text 'YYYY-MM-DD HH:MM:SS'
  end_at TEXT  -- end, ISO text 'YYYY-MM-DD HH:MM:SS'
  status TEXT  -- 'confirmed' or 'cancelled' (only confirmed counts as revenue)
  price_cents INTEGER  -- total booking price in CENTS, already discounted
  FK member_id -> members.id
  FK room_id -> rooms.id
  SAMPLE (id, room_id, member_id, start_at, end_at, status, price_cents):
    1 | 1 | 1 | 2026-01-05 09:00:00 | 2026-01-05 12:00:00 | confirmed | 6000
    2 | 2 | 2 | 2026-01-08 14:00:00 | 2026-01-08 16:00:00 | confirmed | 8000

TABLE members  -- members who make bookings
  id INTEGER PK
  name TEXT
  tier TEXT  -- member's plan: 'basic' or 'pro' (pro members pay 20% less)
  SAMPLE (id, name, tier):
    1 | Ana Torres | pro
    2 | Luis Prado | basic

TABLE payments  -- money movements for each booking
  id INTEGER PK
  booking_id INTEGER
  amount_cents INTEGER  -- amount in CENTS
  kind TEXT  -- 'charge' or 'refund'
  FK booking_id -> bookings.id
  SAMPLE (id, booking_id, amount_cents, kind):
    1 | 1 | 6000 | charge
    2 | 2 | 8000 | charge

TABLE rooms  -- coworking rooms that can be booked
  id INTEGER PK
  name TEXT
  capacity INTEGER
  hourly_cents INTEGER  -- price per hour in CENTS (2500 = $25.00 USD)
  SAMPLE (id, name, capacity, hourly_cents):
    1 | Focus | 1 | 2500
    2 | Studio | 4 | 4000

That block is the assistant's layer 1, generated entirely by code from Reservo. Notice everything it tells the model, and that without this it would have to guess:

• The real names: the table is bookings, the money column is price_cents. Not reservations, not total.
• The units and domains: price_cents is in cents; status is 'confirmed'/'cancelled' and only confirmed counts as revenue. This comes from the hand-written descriptions.
• The relationship map: FK bookings.room_id -> rooms.id, so the model knows how to build the JOIN.
• The data format: dates are text '2026-01-05 09:00:00', not timestamps. This comes from the samples.

Each of these facts is an error the assistant will not make. That's layer 1's job.

Where this output goes: the wiring

What makes this a layer and not a loose script is that its output feeds the next piece. The context doesn't get printed for you to read: it gets embedded into the system prompt (layer 2). The wiring, in one line, is this:

from schema_context import build_schema_context

schema_context = build_schema_context("reservo.db")   # layer 1
# ... in lesson 03:
# system_prompt = build_system_prompt(schema_context)  # layer 1 -> layer 2

Hold onto that image: the context is a string that travels to the prompt. In lesson 08, when we assemble build_assistant(), the first line will be exactly this call. The whole assistant starts here, with the model receiving Reservo's blueprint.

Why generated, and not pasted by hand

You could write the schema by hand into the prompt and it would work... until Reservo changes. The day you add a discount_cents column to bookings, a hand-written context becomes silently outdated and the model will never know it exists. The generated context regenerates on every run: it always reflects the real database. That's the difference between an assistant that ages badly and one that maintains itself. (The only hand-maintenance left is adding the description of what's new to COLUMN_NOTES, if it deserves one.)

Common mistakes

1. Rebuilding the function instead of reusing it. Module 2 already built and explained it. In the capstone you import it from schema_context.py. If you catch yourself re-deriving how PRAGMA table_info works, stop: that lesson already happened. Here, it gets wired in.

2. Forgetting the descriptions. The DDL and samples get introspected on their own, but TABLE_NOTES/COLUMN_NOTES are hand-written. Without them, the context loses exactly what the database doesn't say: that the money is cents, that status filters revenue. It's the knowledge that prevents the costliest errors; don't leave it empty.

3. Printing the context and forgetting to wire it in. The context isn't for you to read; it's for the prompt. If you generate it but don't pass it to build_system_prompt, the model is still flying blind. Layer 1's output is layer 2's input — that's the whole point of it being a layer.

4. Putting too much into the context. In Reservo (4 tables) everything fits. In a database with hundreds of tables, dumping the entire schema wastes tokens and confuses the model; that's where selecting relevant tables comes in (M2, lesson 07). For the Reservo capstone it isn't needed, but remember it when you scale.

Exercises

Exercise 1: Counting the context's size (Easy)

The context travels inside the prompt, so its size counts. Generate Reservo's context and print its length in characters and a token estimate (len // 4). Does it fit comfortably in a prompt's budget?

from schema_context import build_schema_context

ctx = build_schema_context("reservo.db")
print("characters:", len(ctx))
print("approx tokens (len // 4):", len(ctx) // 4)

characters: 1577
approx tokens (len // 4): 394

Exercise 2: Adding a description and watching it appear (Medium)

The rooms.capacity column has no description, and a user got confused about whether it meant people or square meters. Add its note to COLUMN_NOTES and regenerate the context to confirm rooms's line now includes it.

COLUMN_NOTES = {
    # ... the rest ...
    "rooms.capacity": "maximum capacity in PEOPLE (not square meters)",
}

from schema_context import build_schema_context
print(build_schema_context("reservo.db").split("TABLE rooms")[1].split("TABLE")[0])

  -- coworking rooms that can be booked
  id INTEGER PK
  name TEXT
  capacity INTEGER  -- maximum capacity in PEOPLE (not square meters)
  hourly_cents INTEGER  -- price per hour in CENTS (2500 = $25.00 USD)
  SAMPLE (id, name, capacity, hourly_cents):
    1 | Focus | 1 | 2500
    2 | Studio | 4 | 4000

Exercise 3: Simulating a schema that changed (Hard)

Demonstrate why the generated context doesn't age. On a disposable copy of Reservo, add a discount_cents column to bookings with ALTER TABLE, regenerate the context, and confirm the new column appears without having touched the function.

import sqlite3, shutil
from schema_context import build_schema_context

shutil.copy("reservo.db", "evolved.db")
con = sqlite3.connect("evolved.db")
con.execute("ALTER TABLE bookings ADD COLUMN discount_cents INTEGER DEFAULT 0")
con.commit(); con.close()

ctx = build_schema_context("evolved.db")
print("does discount_cents appear?", "discount_cents" in ctx)
print([l for l in ctx.splitlines() if "discount_cents" in l])

import os; os.remove("evolved.db")

does discount_cents appear? True
['  discount_cents INTEGER', '  SAMPLE (id, room_id, member_id, start_at, end_at, status, price_cents, discount_cents):']

Summary and next step

• The assistant's layer 1 is the schema context: Reservo's "blueprint" the model receives so it doesn't hallucinate names. It's M2's build_schema_context function, here reused — not rebuilt — as schema_context.py.
• The context gets generated by introspection (sqlite_master, PRAGMA table_info, PRAGMA foreign_key_list) plus the hand-written descriptions (descriptions.py), so it always reflects the real database and maintains itself when the schema changes.
• Its output is a string that travels to the system prompt (layer 2): build_system_prompt(build_schema_context("reservo.db")). Each layer produces what the next one consumes.
• You actually ran it: Reservo's real context, with names, units, FKs, and samples. There was no call to the model in this layer; it's pure introspection code.

Next lesson: The system prompt — You wire in layer 2: the system prompt that wraps layer 1's context with the assistant's role, the SQLite dialect, the read-only rules, and the few-shot examples. You'll see the complete assembled prompt, and why its rules in prose mirror the code guardrails that come later.

Additional resources

1. SQLite — The Schema Table (sqlite_master) — Where build_schema_context discovers Reservo's tables to assemble the context.
2. SQLite — PRAGMA table_info and foreign_key_list — The column, type, and foreign-key introspection that produces the context's structure and relationships.
3. Python — sqlite3.Row — The by-column-name access the function uses to format the sample rows.
4. Claude — Prompt engineering: giving context — Why giving the model the schema as context (the "blueprint") reduces name hallucinations.