sql-guidelineslisted

# SQL Guidelines Shared SQL rubric for agents, with SQLite depth. Read the full schema and existing indexes before writing or changing queries; understand the access pattern before proposing an index. ## Source Of Truth - The live schema: `SELECT name, sql FROM sqlite_master WHERE type IN ('table','index');` and `PRAGMA table_info(<t>);` - SQLite docs for engine-specific behavior; the migration history for intended evolution ## Query Correctness - Explicit `JOIN` syntax, never comma joins. Qualify every column with a table alias in multi-table queries. - Prefer CTEs (`WITH …`) over correlated subqueries when it clarifies intent; avoid correlated subqueries inside loops. - Handle NULLs deliberately with `COALESCE`; don't rely on accidental NULL propagation. - In `GROUP BY`, include every non-aggregate column from `SELECT`. - Use window functions (`ROW_NUMBER`, `LAG`, `LEAD`) for sequence/time analysis instead of self-joins. ## SQLite Specifics - `INTEGER PRIMARY KEY` over rowid aliases. `STRICT` tables on SQLite ≥ 3.37. `WITHOUT ROWID` only for narrow natural-key tables. - `PRAGMA foreign_keys = ON` at every connection — it's off by default. `WAL` journal mode + `PRAGMA synchronous = NORMAL` for concurrent readers. - FTS5 over fts4: search with `WHERE fts MATCH '…'`, rank with `ORDER BY rank` (BM25 is built in — no separate `bm25()` needed). Join FTS to base tables on rowid via a CTE. - Use `json_extract` / `json_each` to query JSON columns in-engine rather than parsing