## Instructions - Model first on paper: tables, keys, relationships. Normalize to 3NF by default; denormalize only with a measured reason. - Every table gets an integer `id` primary key + `created_at`. Foreign keys with `ON DELETE` behavior chosen deliberately, indexes on every FK and WHERE-heavy column. - Write readable SQL: explicit column lists (never `SELECT *` in app code), CTEs (`WITH`) over nested subqueries, `EXPLAIN QUERY PLAN` before complaining about speed. - Migrations, not hand-edits: numbered SQL files (`001_create_users.sql`), applied in order, committed to the project. SQLite for local/small, Postgres when concurrent writes matter. - Backups: `sqlite3 db .dump` / `pg_dump` on a schedule, restore-tested. Parameterize everything (see PHP skill: PDO only). ## Examples - App DB: users → projects → tasks with FKs, index on (project_id, done), view for open-task counts. - Analytics: CTE daily signups → window function 7-day rolling average. ## Workflows 1. Entities + relationships sketch → CREATE TABLE migrations. 2. Seed realistic test data (1000+ rows if performance matters). 3. Write the app's queries, EXPLAIN the slow ones, add indexes. 4. Backup job + restore drill before launch. 5. Monitor: slow-query log review monthly. ## Templates ```sql -- tasks schema pattern CREATE TABLE tasks ( id INTEGER PRIMARY KEY, project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE, title TEXT NOT NULL, done INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (datetime('now')) ); CREATE INDEX idx_tasks_project_done ON tasks(project_id, done); -- open counts per project SELECT p.name, COUNT(t.id) AS open FROM projects p LEFT JOIN tasks t ON t.project_id = p.id AND t.done = 0 GROUP BY p.id ORDER BY open DESC; ``` ## Tools - SARAH `shell_run`: `sqlite3 app.db < migration.sql`, `.dump` backups. - Python `sqlite3` stdlib for migrations/seeding scripts via SARAH Run. ## Documentation - SQLite: https://sqlite.org/docs.html - Postgres tutorial: https://www.postgresql.org/docs/current/tutorial.html - Use The Index, Luke: https://use-the-index-luke.com