Schema and seed data

The RAG knowledge base handles unstructured content: policy language, terms, coverage wording. But customers ask transactional questions too. "What is my balance?" "Did claim CLM-5003 get approved?" Those live in a database, not a vector store. Our transactional subagents need a tiny SQL schema with real data to query.

Structured data schema

Two tables, one foreign key. The whole transactional layer fits on a slide.

db_utils.py
python
from contextlib import contextmanager
import os
import sqlite3

DB_PATH = os.getenv('INSURANCE_DB_PATH', 'insurance.db')


@contextmanager
def _conn():
    con = sqlite3.connect(DB_PATH)
    con.row_factory = sqlite3.Row
    try:
        yield con
        con.commit()
    finally:
        con.close()


def bootstrap_database() -> None:
    """Create tables and seed demo data on first run."""
    with _conn() as con:
        con.executescript('''
            CREATE TABLE IF NOT EXISTS customers (
                customer_id      TEXT PRIMARY KEY,
                name             TEXT NOT NULL,
                email            TEXT NOT NULL,
                plan             TEXT NOT NULL,
                premium_monthly  REAL NOT NULL,
                balance_due      REAL NOT NULL DEFAULT 0
            );
            CREATE TABLE IF NOT EXISTS claims (
                claim_id    TEXT PRIMARY KEY,
                customer_id TEXT NOT NULL REFERENCES customers(customer_id),
                type        TEXT NOT NULL,
                status      TEXT NOT NULL,
                amount      REAL NOT NULL,
                opened_at   TEXT NOT NULL
            );
        ''')

Two tables, one foreign key, CREATE IF NOT EXISTS so the bootstrap is idempotent. row_factory = sqlite3.Row means every query returns dict-like objects, which the subagents can serialize straight to JSON for the LLM prompt.

db_utils.py
python
SEED_CUSTOMERS = [
    {'customer_id': 'CUST-1001', 'name': 'Alice Johnson', 'plan': 'Auto Gold',
     'premium_monthly': 129.50, 'balance_due': 0.00, 'email': '[email protected]'},
    {'customer_id': 'CUST-1002', 'name': 'Ben Rivera', 'plan': 'Home Silver',
     'premium_monthly': 89.00, 'balance_due': 89.00, 'email': '[email protected]'},
    # ... three more seeded customers
]

SEED_CLAIMS = [
    {'claim_id': 'CLM-5001', 'customer_id': 'CUST-1001', 'type': 'collision',
     'status': 'approved', 'amount': 2400.00, 'opened_at': '2026-01-12'},
    {'claim_id': 'CLM-5003', 'customer_id': 'CUST-1002', 'type': 'water damage',
     'status': 'review', 'amount': 5600.00, 'opened_at': '2026-02-18'},
    # ... eight more seeded claims across the five customers
]

The seed data is deliberately varied: different plans, different claim statuses, some customers with balance due, some without. This gives the transactional subagents realistic test cases without needing a real production database.

Quiz: Quiz

Loading practice…