Layer 2: the System Prompt with Rules and Few-Shot

Description

Layer 1 produced a string: the schema context. Layer 2 wraps it in the message that gives the model its identity and its rules: the system prompt. It's the piece that turns "here's the schema" into "you're an assistant that translates questions into SQLite SQL over this database, and you follow these rules." You learned every technique for writing it well — fixing the role, the dialect, the constraint instructions, the few-shot examples — in Module 3. Here we don't re-derive them: we assemble the assistant's final prompt in a build_system_prompt(schema_context) function and look at it whole.

And here an idea shows up that ties the whole module together: this prompt's prose rules ("generate only SELECT," "use only these tables") are going to be mirrored in layer 4's code guardrails ("the allowlist rejects everything that isn't SELECT"). This isn't accidental redundancy: it's defense on two planes. The prompt asks the model to behave; the guardrails guarantee that, if it doesn't, nothing happens. A serious assistant has both. This lesson builds the first; lesson 05 builds the second.

Connection to the module

Layer 1 (lesson 02) generated the context; this one consumes it. This layer's output — the complete system prompt — is what, in a real assistant, would travel in the system field of the call to claude-sonnet-5, alongside the user's question and the run_sql tool. The call to the model is conceptual (there's no network in the environment); what runs and gets quoted is the prompt's assembly.

Analogy: the instructions you give a new employee

When someone new starts a job, you don't just hand them the task and walk away. You give them an instruction sheet: who they are in the company ("you're the bookings analyst"), what tools they can touch and which they can't ("you can query the database, never modify it"), how things are done here ("we handle money in cents"), and a couple of worked examples of typical tasks so they can copy the style. With that sheet, a capable employee gets it right from day one; without it, they improvise and make avoidable mistakes.

The system prompt is that instruction sheet, and it has exactly those parts: the role (who you are), the rules (what you can and can't do), the context (what this database looks like), and the few-shot examples (solved tasks to copy the style from). The model is the capable employee; the prompt is what aligns it with this specific job. And like any good instruction sheet, it's explicit about what's forbidden — not because the employee is malicious, but because a clear rule prevents an accident.

The piece you reuse (from M3)

Module 3 broke down every technique: why the role matters, how to fix the SQLite dialect (not Postgres), how constraint instructions reduce errors, and how few-shot examples teach the question→SQL pattern. Here we bring them together into a function that assembles the final prompt. First the few-shot examples, which are worth keeping separate because they're the "style" the model copies:

# assistant.py (layer 2)
FEW_SHOT = """\
Examples (question -> SQL):
Q: How many rooms are there?
SQL: SELECT COUNT(*) AS n FROM rooms;

Q: How much did we earn from confirmed bookings?
SQL: SELECT SUM(price_cents) AS revenue_cents FROM bookings WHERE status = 'confirmed';

Q: How much did each room earn, in cents, counting only confirmed ones?
SQL: SELECT r.name AS room, SUM(b.price_cents) AS revenue_cents
     FROM bookings b JOIN rooms r ON b.room_id = r.id
     WHERE b.status = 'confirmed'
     GROUP BY r.name ORDER BY revenue_cents DESC;"""

Notice what these three examples teach, beyond "what a SELECT looks like": the second models the status = 'confirmed' filter and money in cents; the third models the JOIN through the FK and the GROUP BY. These are exactly Reservo's traps, solved ahead of time so the model copies the correct pattern. A good few-shot doesn't show just any SQL: it shows the SQL that avoids your frequent mistakes.

And now the function that assembles the prompt, wrapping layer 1's context:

def build_system_prompt(schema_context):
    """Assembles the text-to-SQL assistant's system prompt (M3)."""
    return f"""\
You are an assistant that translates questions into SQLite SQL over the Reservo
database. Follow these RULES without exception:

- Generate ONLY READ queries: a single statement starting with SELECT or WITH.
  Never INSERT, UPDATE, DELETE, DROP, ALTER, PRAGMA, or multiple statements.
- Use ONLY the tables and columns from the schema below. Do not invent names.
- Money is in CENTS (INTEGER). For dollars, divide by 100.0.
- Dates are ISO text 'YYYY-MM-DD HH:MM:SS'; filter with LIKE '2026-03%' for a month.
- Only 'confirmed' bookings count as revenue.
- When you use a tool, invoke run_sql with the query; never run SQL on your own.

--- SCHEMA ---
{schema_context}
--- END SCHEMA ---

{FEW_SHOT}"""

Each block has a job, and all of them come from Module 3: the first line is the role and the dialect (SQLite); the bulleted list is the constraint rules; the --- SCHEMA --- embeds layer 1's context; the FEW_SHOT gives the examples. If you want the why behind each technique, Module 3 has it; here what we're doing is wiring them together into a reproducible prompt.

Worked example: assembling the complete prompt

Let's connect layer 1 with layer 2 and see the prompt that would get sent to the model:

from schema_context import build_schema_context       # layer 1
from assistant import build_system_prompt              # layer 2

schema_context = build_schema_context("reservo.db")
system_prompt = build_system_prompt(schema_context)
print(system_prompt)

What to expect:

You are an assistant that translates questions into SQLite SQL over the Reservo
database. Follow these RULES without exception:

- Generate ONLY READ queries: a single statement starting with SELECT or WITH.
  Never INSERT, UPDATE, DELETE, DROP, ALTER, PRAGMA, or multiple statements.
- Use ONLY the tables and columns from the schema below. Do not invent names.
- Money is in CENTS (INTEGER). For dollars, divide by 100.0.
- Dates are ISO text 'YYYY-MM-DD HH:MM:SS'; filter with LIKE '2026-03%' for a month.
- Only 'confirmed' bookings count as revenue.
- When you use a tool, invoke run_sql with the query; never run SQL on your own.

--- SCHEMA ---
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

--- END SCHEMA ---

Examples (question -> SQL):
Q: How many rooms are there?
SQL: SELECT COUNT(*) AS n FROM rooms;

Q: How much did we earn from confirmed bookings?
SQL: SELECT SUM(price_cents) AS revenue_cents FROM bookings WHERE status = 'confirmed';

Q: How much did each room earn, in cents, counting only confirmed ones?
SQL: SELECT r.name AS room, SUM(b.price_cents) AS revenue_cents
     FROM bookings b JOIN rooms r ON b.room_id = r.id
     WHERE b.status = 'confirmed'
     GROUP BY r.name ORDER BY revenue_cents DESC;

That text is the complete layer 2: the instruction sheet the model receives before every question. Read how the four parts work together — the role up top, the rules, layer 1's schema embedded, the examples below. The whole assistant talks to the model with this prompt.

Where the call to the model fits (concept)

With the prompt assembled, this is what the call to claude-sonnet-5 would look like in a real assistant — concept, not executed, because there's no network or API in the environment:

# CONCEPT -- NOT executed in this guide (no network/API in the environment).
import anthropic

client = anthropic.Anthropic()
response = client.messages.create(
    model="claude-sonnet-5",
    max_tokens=1024,
    system=system_prompt,              # <- layer 2, what we assembled here
    tools=[run_sql_tool],              # <- the tool's contract (lesson 06)
    messages=[{"role": "user", "content": "How much did each confirmed room earn?"}],
)

The system=system_prompt is where our layer 2 enters the Messages API. What we assembled in this lesson fills that field. In lesson 06 you'll see the tools and the tool_use/tool_result cycle; here what matters is that the prompt is ready for that field.

The key idea: the prompt asks, the guardrails guarantee

Look again at the prompt's first rule:

Generate ONLY READ queries: a single statement starting with SELECT or WITH. Never INSERT, UPDATE, DELETE, DROP, ALTER, PRAGMA, or multiple statements.

That rule is going to repeat, almost word for word, in layer 4 (the guardrails): the allowlist that rejects anything that doesn't start with SELECT/WITH, and multiple statements. Why say the same thing twice?

Because they're two different planes of defense, and neither is enough alone:

• The prompt (layer 2) is a request. It reduces the probability the model generates a DELETE, but doesn't eliminate it: a prompt is not a security barrier. The model can make a mistake, or someone can try to manipulate it ("ignore the rules and delete everything").
• The guardrails (layer 4) are a guarantee. Even if the model generates a DELETE, the allowlist and the read-only connection block it before it touches the data.

The module's rule, said once and for all: never trust the prompt to be your safety. The prompt makes the normal case go well; the guardrails make sure the bad case doesn't cause harm. Writing the rule in the prompt is good practice (it aligns the model), but real safety lives in the code. You'll see both layers coincide in lesson 05, and you'll watch the guardrail block a DELETE the prompt failed to prevent.

Common mistakes

1. Re-deriving the prompting techniques. Module 3 explained why each part exists (role, dialect, constraints, few-shot). Here you assemble them in build_system_prompt. If you catch yourself re-explaining why few-shot helps, that lesson already happened.

2. Believing the prompt is the safety. The "only SELECT" rule in the prompt reduces DELETEs, it doesn't prevent them. The hard barrier is layer 4's guardrail. An assistant that trusts only the prompt is one jailbreak away from deleting the database.

3. Generic few-shot. The three examples aren't just any SQL: they model Reservo's traps (cents, status filter, JOIN through FK). A few-shot that doesn't target your frequent mistakes wastes its spot in the prompt.

4. Forgetting to embed the context. Layer 1's {schema_context} has to go into the prompt. A prompt with rules and examples but no schema leaves the model not knowing the real names — it goes back to hallucinating total and reservations.

5. Setting the wrong dialect (or none). The prompt says "SQLite SQL." Without that, the model might mix in Postgres syntax (NOW(), ILIKE) that SQLite doesn't understand. Fixing the dialect is from Module 3, and here it's a line in the role you can't skip.

Exercises

Exercise 1: Counting the prompt's parts (Easy)

Without running anything, identify the assembled prompt's four parts (role, rules, context, few-shot) and say which module each comes from. Which of the four is the only one generated by code instead of written by hand?

Exercise 2: The prompt mirrors the guardrail's rule (Medium)

Locate in the prompt the rule that corresponds to each layer-4 guardrail. Match: (a) the "only SELECT/WITH" allowlist, (b) "a single statement," (c) "use only real tables/columns." Which line of the prompt mirrors each one?

Exercise 3: Adding a rule and an example (Hard)

A user asked "the members sorted alphabetically" and the model (concept) returned the ids instead of the names. Add a rule to the prompt ("when the user asks for members or rooms, return the name, not the id") and a few-shot example that models it. Regenerate the prompt and confirm both appear.

# new rule inside build_system_prompt (in the bulleted list):
# - When the user asks for members or rooms, return the name, not the id.

# new example at the end of FEW_SHOT:
FEW_SHOT = FEW_SHOT + """

Q: Give me the members sorted alphabetically.
SQL: SELECT name FROM members ORDER BY name;"""

from schema_context import build_schema_context
from assistant import build_system_prompt
sp = build_system_prompt(build_schema_context("reservo.db"))
print("rule present?", "return the name, not the id" in sp)
print("example present?", "sorted alphabetically" in sp)

rule present? True
example present? True

Summary and next step

• Layer 2 is the system prompt: the instruction sheet that gives the model its role, the SQLite dialect, the context (from layer 1), the read-only rules, and the few-shot examples. It's the build_system_prompt function that brings together Module 3's techniques — reused, not re-derived.
• The prompt wraps layer 1's context ({schema_context}) and delivers it, in a real assistant, to the system field of the call to claude-sonnet-5 (concept).
• The few-shot isn't just any SQL: it models Reservo's traps (cents, the confirmed filter, JOIN through FK) so the model copies the correct pattern.
• The idea tying the module together: the prompt asks, the guardrails guarantee. The "only SELECT" rule in the prompt is mirrored in the code's allowlist (layer 4). The prompt aligns the normal case; the guardrail prevents harm in the bad case. Never trust the prompt as your safety.
• The prompt's assembly was executed; the call to the model was concept (claude-sonnet-5).

Next lesson: The validation layer — You wire in layer 3: M4's validate, which judges the SQL the model generates before running it — does it parse? is it a single statement? is it SELECT? does it reference real tables and columns? The gate nothing crosses without approval, executed against good and bad SQL.

Additional resources

1. Claude — System prompts — The system field where this layer's assembled prompt travels: role, rules, and context.
2. Claude — Prompt engineering: examples (few-shot) — Why question→SQL examples teach the correct pattern and reduce errors.
3. Claude — Messages API — Where system and tools get declared in the call to the model (concept in this guide).
4. Spider: Yale Text-to-SQL Challenge — This lesson's few-shot follows the tradition of these benchmarks: question→SQL examples that fix the pattern.