## Text-to-SQL Pipeline Reference

### Schema enrichment (highest impact)
```sql
-- Don't just send table names. Enrich with:
-- 1. Column descriptions with business meaning
-- 2. Example values (especially for categorical/enum columns)
-- 3. Join paths between tables
-- 4. Common synonyms/acronyms

Tables:
- customers (id, name, country, signup_date)
  - country: 2-letter code. Examples: US, UK, DE, FR
  - signup_date: date the customer registered (ISO 8601)

- orders (id, customer_id, total, status, created_at)
  - status: one of {pending, paid, shipped, delivered, cancelled}
  - total: order amount in USD (2 decimal places)
  - customer_id -> customers.id
```

### Few-shot examples (2-5 pairs)
```json
[
  {"question": "Top 5 customers by order count",
   "sql": "SELECT c.name, COUNT(*) as orders FROM customers c JOIN orders o ON c.id = o.customer_id GROUP BY c.name ORDER BY orders DESC LIMIT 5"},
  {"question": "Total revenue in 2024",
   "sql": "SELECT SUM(total) as revenue FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'"}
]
```

### Safety guardrails
| Guard | Implementation |
|-------|---------------|
| Read-only role | `GRANT SELECT ON ...` |
| Forbidden DDL/DML | Reject DROP/TRUNCATE/DELETE/INSERT/UPDATE/ALTER |
| Row limit | Append `LIMIT 1000` if missing |
| Statement timeout | `SET statement_timeout = '5s'` (Postgres) |
| Row-level security | Postgres RLS policies |

### Common schema issues
| Issue | Fix |
|-------|-----|
| Ambiguous column names | Qualify with table: `o.total` not `total` |
| Missing join conditions | Include join hints in schema prompt |
| Wrong date format | Show expected format in schema description |
| Synonyms mismatch | Map user terms to DB terms (revenue -> total) |

### Eval approach
- Golden SQL set: 50-200 question/SQL pairs
- Metrics: exact SQL match AND result-set match
- Measure improvement from each enrichment step independently
- Red-team: ambiguous joins, billion-dollar queries, injection attempts