# SQLite Async Patterns

Python aiosqlite patterns for async applications.

## Async Connection

```python
import aiosqlite

async def get_async_connection(db_path: str) -> aiosqlite.Connection:
    """Create async connection with best practices."""
    conn = await aiosqlite.connect(db_path)
    conn.row_factory = aiosqlite.Row
    await conn.execute("PRAGMA journal_mode=WAL")
    await conn.execute("PRAGMA foreign_keys=ON")
    return conn
```

## Context Manager Pattern

```python
async def query_items(db_path: str, status: str) -> list[dict]:
    """Query with automatic connection cleanup."""
    async with aiosqlite.connect(db_path) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute(
            "SELECT * FROM items WHERE status = ?", (status,)
        ) as cursor:
            rows = await cursor.fetchall()
            return [dict(row) for row in rows]
```

## Async CRUD Operations

### Create

```python
async def create_item(db_path: str, name: str, data: dict) -> int:
    """Insert and return new ID."""
    async with aiosqlite.connect(db_path) as db:
        cursor = await db.execute(
            "INSERT INTO items (name, data) VALUES (?, ?)",
            (name, json.dumps(data))
        )
        await db.commit()
        return cursor.lastrowid
```

### Read

```python
async def get_item(db_path: str, item_id: int) -> dict | None:
    """Get single item by ID."""
    async with aiosqlite.connect(db_path) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute(
            "SELECT * FROM items WHERE id = ?", (item_id,)
        ) as cursor:
            row = await cursor.fetchone()
            return dict(row) if row else None
```

### Update

```python
async def update_item(db_path: str, item_id: int, **updates) -> bool:
    """Update item fields."""
    if not updates:
        return False

    set_clause = ", ".join(f"{k} = ?" for k in updates.keys())
    values = list(updates.values()) + [item_id]

    async with aiosqlite.connect(db_path) as db:
        cursor = await db.execute(
            f"UPDATE items SET {set_clause} WHERE id = ?",
            values
        )
        await db.commit()
        return cursor.rowcount > 0
```

### Delete

```python
async def delete_item(db_path: str, item_id: int) -> bool:
    """Delete item by ID."""
    async with aiosqlite.connect(db_path) as db:
        cursor = await db.execute(
            "DELETE FROM items WHERE id = ?", (item_id,)
        )
        await db.commit()
        return cursor.rowcount > 0
```

## Batch Operations

### Batch Insert

```python
async def batch_insert(db_path: str, items: list[dict]) -> int:
    """Insert multiple items efficiently."""
    async with aiosqlite.connect(db_path) as db:
        await db.executemany(
            "INSERT INTO items (name, data) VALUES (?, ?)",
            [(i["name"], json.dumps(i.get("data", {}))) for i in items]
        )
        await db.commit()
        return len(items)
```

### Batch Update

```python
async def batch_update_status(db_path: str, ids: list[int], status: str) -> int:
    """Update status for multiple items."""
    async with aiosqlite.connect(db_path) as db:
        cursor = await db.executemany(
            "UPDATE items SET status = ? WHERE id = ?",
            [(status, id) for id in ids]
        )
        await db.commit()
        return len(ids)
```

### Batch with Transaction

```python
async def batch_transfer(db_path: str, transfers: list[tuple[int, int, float]]) -> None:
    """Transfer amounts between accounts atomically."""
    async with aiosqlite.connect(db_path) as db:
        try:
            for from_id, to_id, amount in transfers:
                await db.execute(
                    "UPDATE accounts SET balance = balance - ? WHERE id = ?",
                    (amount, from_id)
                )
                await db.execute(
                    "UPDATE accounts SET balance = balance + ? WHERE id = ?",
                    (amount, to_id)
                )
            await db.commit()
        except Exception:
            await db.rollback()
            raise
```

## Connection Pool Pattern

```python
from contextlib import asynccontextmanager
import asyncio

class AsyncDBPool:
    """Simple connection pool for aiosqlite."""

    def __init__(self, db_path: str, max_connections: int = 5):
        self.db_path = db_path
        self.max_connections = max_connections
        self._pool: asyncio.Queue[aiosqlite.Connection] = asyncio.Queue()
        self._created = 0
        self._lock = asyncio.Lock()

    async def _create_connection(self) -> aiosqlite.Connection:
        conn = await aiosqlite.connect(self.db_path)
        conn.row_factory = aiosqlite.Row
        await conn.execute("PRAGMA journal_mode=WAL")
        return conn

    @asynccontextmanager
    async def acquire(self):
        # Try to get from pool
        try:
            conn = self._pool.get_nowait()
        except asyncio.QueueEmpty:
            # Create new if under limit
            async with self._lock:
                if self._created < self.max_connections:
                    conn = await self._create_connection()
                    self._created += 1
                else:
                    # Wait for one to be returned
                    conn = await self._pool.get()

        try:
            yield conn
        finally:
            # Return to pool
            await self._pool.put(conn)

    async def close_all(self):
        while not self._pool.empty():
            conn = await self._pool.get()
            await conn.close()
        self._created = 0

# Usage
pool = AsyncDBPool("mydb.sqlite")

async def get_user(user_id: int):
    async with pool.acquire() as db:
        async with db.execute(
            "SELECT * FROM users WHERE id = ?", (user_id,)
        ) as cursor:
            return await cursor.fetchone()
```

## Streaming Large Results

```python
async def stream_items(db_path: str, batch_size: int = 1000):
    """Yield items in batches to avoid memory issues."""
    async with aiosqlite.connect(db_path) as db:
        db.row_factory = aiosqlite.Row
        async with db.execute("SELECT * FROM items ORDER BY id") as cursor:
            while True:
                rows = await cursor.fetchmany(batch_size)
                if not rows:
                    break
                for row in rows:
                    yield dict(row)
```

## Concurrent Queries

```python
async def get_dashboard_data(db_path: str, user_id: int) -> dict:
    """Run multiple queries concurrently."""
    async with aiosqlite.connect(db_path) as db:
        db.row_factory = aiosqlite.Row

        # Execute queries concurrently
        user_task = db.execute("SELECT * FROM users WHERE id = ?", (user_id,))
        orders_task = db.execute(
            "SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 10",
            (user_id,)
        )
        stats_task = db.execute(
            "SELECT COUNT(*) as count, SUM(total) as total FROM orders WHERE user_id = ?",
            (user_id,)
        )

        # Await all
        user_cursor, orders_cursor, stats_cursor = await asyncio.gather(
            user_task, orders_task, stats_task
        )

        return {
            "user": dict(await user_cursor.fetchone()),
            "recent_orders": [dict(r) for r in await orders_cursor.fetchall()],
            "stats": dict(await stats_cursor.fetchone()),
        }
```

## Error Handling

```python
import aiosqlite
from sqlite3 import IntegrityError, OperationalError

async def safe_insert(db_path: str, data: dict) -> tuple[bool, str]:
    """Insert with comprehensive error handling."""
    try:
        async with aiosqlite.connect(db_path) as db:
            await db.execute(
                "INSERT INTO items (name, value) VALUES (?, ?)",
                (data["name"], data["value"])
            )
            await db.commit()
            return True, "Success"

    except IntegrityError as e:
        if "UNIQUE constraint" in str(e):
            return False, "Duplicate entry"
        elif "FOREIGN KEY constraint" in str(e):
            return False, "Referenced record not found"
        return False, f"Integrity error: {e}"

    except OperationalError as e:
        if "database is locked" in str(e):
            return False, "Database busy, try again"
        elif "no such table" in str(e):
            return False, "Table not found"
        return False, f"Database error: {e}"

    except Exception as e:
        return False, f"Unexpected error: {e}"
```

# SQLite Concurrency and Durability

Engine-agnostic. The locking model, journal modes, and transaction semantics below are
properties of SQLite itself and behave identically in every host that gives you a real
connection. Managed engines (D1) hide most of this — see [`d1-edge.md`](d1-edge.md).

## Contents

- [The one-writer model](#the-one-writer-model)
- [Journal modes: WAL vs rollback](#journal-modes-wal-vs-rollback)
- [SQLITE_BUSY vs SQLITE_LOCKED](#sqlite_busy-vs-sqlite_locked)
- [busy_timeout](#busy_timeout)
- [Transaction modes and the upgrade deadlock](#transaction-modes-and-the-upgrade-deadlock)
- [Durability: the synchronous pragma](#durability-the-synchronous-pragma)
- [WAL checkpointing and file growth](#wal-checkpointing-and-file-growth)
- [Connection pragma baseline](#connection-pragma-baseline)
- [Multi-process and networked filesystems](#multi-process-and-networked-filesystems)
- [Retry patterns](#retry-patterns)

---

## The one-writer model

SQLite allows **many concurrent readers and exactly one writer** per database. There is no
row-level locking and no MVCC beyond WAL's single-version snapshot. Almost every
concurrency problem in SQLite is a consequence of that sentence.

| Reality | Implication |
|---|---|
| One writer at a time, database-wide | Write transactions must be **short**; never hold one across network I/O or user think-time |
| Readers don't block readers | Read concurrency scales freely |
| In WAL, readers don't block the writer and the writer doesn't block readers | WAL is the default recommendation for anything concurrent |
| Locks are per **connection**, not per thread or per statement | Two connections in the same process contend exactly like two processes |

**Design consequence:** batch writes. A thousand single-statement transactions cost a
thousand lock acquisitions and (depending on `synchronous`) a thousand fsyncs; the same
thousand statements inside one transaction cost one of each.

```sql
BEGIN IMMEDIATE;
  INSERT INTO events (kind, payload) VALUES (?, ?);
  -- ... 999 more
COMMIT;
```

---

## Journal modes: WAL vs rollback

```sql
PRAGMA journal_mode = WAL;      -- returns 'wal' on success; PERSISTENT (stored in the file)
PRAGMA journal_mode;            -- read current mode
```

| Mode | Readers during write | Crash safety | Notes |
|---|---|---|---|
| `DELETE` (default) | **Blocked** | Safe | Journal file created and deleted per transaction |
| `TRUNCATE` | Blocked | Safe | Journal truncated rather than deleted — slightly faster |
| `PERSIST` | Blocked | Safe | Journal header zeroed rather than deleted |
| `WAL` | **Concurrent** | Safe | Recommended default for concurrent workloads |
| `MEMORY` | Blocked | **Unsafe** — crash corrupts | Only for throwaway data |
| `OFF` | Blocked | **Unsafe** — no rollback at all | Only for import scratch databases |

**WAL is persistent**: set it once and it survives reconnects and restarts, because the mode
is recorded in the database header. It does *not* need to be set on every connection —
unlike `busy_timeout` and `foreign_keys`, which do.

**WAL trade-offs to know before choosing it:**

- Creates two extra files: `-wal` (the log) and `-shm` (shared memory index). Backups must
  account for them, or use `VACUUM INTO`.
- Requires shared memory, so it **does not work on most network filesystems** (see below).
- A single database can't be in WAL mode for some connections and rollback for others.
- Readers see a consistent snapshot from the moment their transaction started.

---

## SQLITE_BUSY vs SQLITE_LOCKED

These look alike and mean opposite things. Getting them confused produces retry loops that
spin forever.

| Error | Meaning | Retryable? |
|---|---|---|
| `SQLITE_BUSY` (5) | Another **connection** holds a conflicting lock and yours timed out waiting | **Yes** — back off and retry |
| `SQLITE_LOCKED` (6) | Conflict **inside your own connection** (or a shared-cache sibling) — e.g. writing to a table you are mid-scan on | **No** — retrying cannot help; fix the code |
| `SQLITE_BUSY_SNAPSHOT` | WAL: your read snapshot is too old to upgrade to a write | Yes, but restart the whole transaction |

`SQLITE_LOCKED` most often means a cursor is still open on the table being modified. Read
the rows out fully (materialise the list) before writing to the same table.

```python
# SQLITE_LOCKED risk: writing while iterating the same table
for row in conn.execute("SELECT id FROM job WHERE status='pending'"):
    conn.execute("UPDATE job SET status='running' WHERE id=?", (row[0],))   # risky

# Safe: materialise first
ids = [r[0] for r in conn.execute("SELECT id FROM job WHERE status='pending'").fetchall()]
for i in ids:
    conn.execute("UPDATE job SET status='running' WHERE id=?", (i,))
```

---

## busy_timeout

```sql
PRAGMA busy_timeout = 5000;   -- milliseconds; per CONNECTION, not persistent
```

Without it, a lock conflict raises `SQLITE_BUSY` **immediately**. With it, SQLite sleeps and
retries internally for up to the timeout before giving up. This single pragma removes the
majority of "database is locked" reports.

| Setting | Suitable for |
|---|---|
| 0 (default) | Nothing concurrent — you will see spurious BUSY |
| 1,000–5,000 ms | Typical application default |
| 30,000 ms | Batch/migration jobs where waiting beats failing |

**It must be set on every connection**, including short-lived ones and those created by
connection pools. It is not stored in the database file.

Caveat: `busy_timeout` does **not** rescue the DEFERRED-upgrade deadlock below. That case
is architecturally unresolvable by waiting, and SQLite returns `SQLITE_BUSY` instantly
regardless of the timeout.

---

## Transaction modes and the upgrade deadlock

```sql
BEGIN;             -- == BEGIN DEFERRED: no lock taken until the first statement
BEGIN IMMEDIATE;   -- takes a write lock now
BEGIN EXCLUSIVE;   -- takes an exclusive lock now (rarely needed in WAL)
```

**The footgun:** `BEGIN DEFERRED` followed by a read and then a write must *upgrade* from a
read lock to a write lock. If another connection wrote to the database between your read and
your upgrade, SQLite cannot give you a consistent view and returns `SQLITE_BUSY`
**immediately, ignoring `busy_timeout`** — because waiting could deadlock two connections
each holding a read lock and each wanting to upgrade.

```sql
-- Deadlock-prone: read, then write, inside a DEFERRED transaction
BEGIN;
  SELECT balance FROM account WHERE id = 1;
  UPDATE account SET balance = balance - 10 WHERE id = 1;   -- may fail with BUSY, instantly
COMMIT;

-- Correct: declare the intent to write up front
BEGIN IMMEDIATE;
  SELECT balance FROM account WHERE id = 1;
  UPDATE account SET balance = balance - 10 WHERE id = 1;
COMMIT;
```

**Rule: if a transaction will write at any point, open it with `BEGIN IMMEDIATE`.** The cost
is serialising writers slightly earlier; the benefit is that `busy_timeout` now actually
applies and the failure mode becomes a retryable wait instead of an instant error.

Read-only transactions should stay `DEFERRED` — they take no write lock and never block
anyone.

### Savepoints

Nested, named transaction points — useful for partial rollback inside a long operation.

```sql
BEGIN IMMEDIATE;
  SAVEPOINT step1;
    -- risky work
  ROLLBACK TO step1;    -- undo just this step, transaction still open
  RELEASE step1;
COMMIT;
```

---

## Durability: the synchronous pragma

```sql
PRAGMA synchronous = NORMAL;   -- per connection
```

| Level | Meaning | Risk on power loss / OS crash |
|---|---|---|
| `OFF` (0) | Never fsync | **Database can be corrupted** |
| `NORMAL` (1) | Fsync at checkpoints only (in WAL) | With WAL: recent commits may be lost, **file stays consistent** |
| `FULL` (2) | Fsync every commit | No committed data lost |
| `EXTRA` (3) | `FULL` plus the directory sync | Marginally stronger |

**`NORMAL` with WAL is the standard production choice**: it is the large majority of the
performance win with no corruption risk — only the possibility of losing the last few
committed transactions if the machine loses power. Application crashes are safe at `NORMAL`;
it is only OS-level or power failure that can lose committed data.

Use `FULL` when a lost commit is unacceptable (financial ledgers, anything with an external
side effect keyed to the write). Never use `OFF` on data you care about; it is for
regenerable scratch databases only.

---

## WAL checkpointing and file growth

The `-wal` file accumulates committed pages until a **checkpoint** moves them back into the
main database. By default SQLite auto-checkpoints when the WAL passes ~1000 pages (~4 MB at
the default page size).

```sql
PRAGMA wal_autocheckpoint = 1000;        -- pages; 0 disables auto-checkpointing
PRAGMA wal_checkpoint(PASSIVE);          -- checkpoint what it can, never blocks
PRAGMA wal_checkpoint(FULL);             -- wait for readers, checkpoint everything
PRAGMA wal_checkpoint(TRUNCATE);         -- FULL, then shrink the -wal file to zero
```

**Why a `-wal` file grows without bound:** a checkpoint cannot advance past the oldest
active reader's snapshot. One long-lived read transaction — an idle connection that opened a
transaction and never committed, a paginated report held open, an ORM session left in
transaction — pins the WAL forever.

| Symptom | Diagnosis | Fix |
|---|---|---|
| `-wal` grows to GBs | Long-lived reader pinning the checkpoint | Find and close it; add a statement timeout; commit read transactions promptly |
| Periodic latency spikes on writes | A large checkpoint blocking | Lower `wal_autocheckpoint`, or run `PASSIVE` checkpoints from a background task |
| `-wal` persists after clean shutdown | Last connection didn't close cleanly | It is recovered automatically on next open; harmless |

Deleting `-wal` or `-shm` by hand while a connection is open risks corruption. Close all
connections first — SQLite removes them on the last clean close.

---

## Connection pragma baseline

Persistence differs per pragma, and it's the most common source of "I set that, why isn't it
on":

| Pragma | Scope | Set where |
|---|---|---|
| `journal_mode = WAL` | **Database file** — persistent | Once, at setup/migration |
| `busy_timeout` | Connection | **Every connection** |
| `foreign_keys` | Connection | **Every connection** |
| `synchronous` | Connection | **Every connection** |
| `cache_size` | Connection | Every connection |
| `page_size` | Database file — only settable before first write or via `VACUUM` | Setup only |
| `auto_vacuum` | Database file — set before first table, or `VACUUM` after change | Setup only |

A correct connection factory sets the connection-scoped ones every time:

```sql
PRAGMA busy_timeout = 5000;
PRAGMA foreign_keys = ON;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -64000;   -- negative = KiB, so this is 64 MB
```

See [`hosts.md`](hosts.md) for this baseline written out per host.

---

## Multi-process and networked filesystems

| Environment | Verdict |
|---|---|
| Multiple processes, same local disk | Fine — this is SQLite's design point. Use WAL + `busy_timeout` |
| Threads sharing one connection | Only with correct serialisation; prefer one connection per thread |
| NFS / SMB / CIFS | **Do not.** Advisory locking is unreliable; corruption is a documented outcome |
| Docker volume on a local filesystem | Fine |
| Docker volume over a network mount | Same problem as NFS |
| WSL accessing a Windows drive (`/mnt/c`) | Locking is unreliable across the boundary — keep the database on the native filesystem |
| Cloud object storage (S3 et al.) | Not a filesystem; use a purpose-built layer (libSQL, Litestream-style replication) |

If you need SQLite semantics over a network, put a **server** in front of it (libSQL/Turso,
rqlite, or your own service) rather than sharing the file. See [`d1-edge.md`](d1-edge.md).

**Litestream-style continuous replication** is the standard answer for durability of a
single-node SQLite database: it streams WAL frames to object storage without changing how
the application talks to the database.

---

## Retry patterns

Retry `SQLITE_BUSY`. Never retry `SQLITE_LOCKED`. Always retry the **whole transaction**,
not the failed statement — a partial transaction cannot be resumed.

```python
import sqlite3, time, random

def with_retry(conn, fn, attempts=5):
    """Retry a whole write transaction on SQLITE_BUSY with jittered backoff."""
    for attempt in range(attempts):
        try:
            conn.execute("BEGIN IMMEDIATE")
            result = fn(conn)
            conn.execute("COMMIT")
            return result
        except sqlite3.OperationalError as exc:
            conn.execute("ROLLBACK")
            if "locked" not in str(exc) and "busy" not in str(exc):
                raise                      # not a contention error - do not retry
            if attempt == attempts - 1:
                raise
            time.sleep((2 ** attempt) * 0.05 + random.random() * 0.05)
```

Jitter matters: without it, N contending writers retry in lockstep and keep colliding.

---

## See also

- [`hosts.md`](hosts.md) — the pragma baseline per driver, and which hosts expose it
- [`operations.md`](operations.md) — backups that are safe under concurrent writers
- [`schema-design.md`](schema-design.md) — `foreign_keys` and the constraints it enables
- [`d1-edge.md`](d1-edge.md) — what a managed engine takes away from this chapter

# Cloudflare D1 and Edge SQLite

D1 is SQLite, so everything in [`query-performance.md`](query-performance.md),
[`schema-design.md`](schema-design.md), and [`feature-modules.md`](feature-modules.md)
applies unchanged. This file covers what is **different**: how you observe cost, how you are
billed, the platform features stock SQLite has no equivalent for (Sessions API read
replication, Time Travel, `d1 insights`), and the limits that will bite.

> **Two sourcing notes.** Measurements labelled *verified 2026-08-04* come from a live D1
> database (`atdw-mirror`, region OC, colo SYD) and are **one database's numbers, not
> constants**. Platform limits and feature descriptions come from the Cloudflare D1 docs as
> of 2026-08-04 — that surface moves, so re-check
> [platform/limits](https://developers.cloudflare.com/d1/platform/limits/) before designing
> around a number.

## Contents

- [Two metrics, not one](#two-metrics-not-one)
- [The meta object](#the-meta-object)
- [Measuring correctly](#measuring-correctly)
- [wrangler d1 insights](#wrangler-d1-insights)
- [Statement formatting rules](#statement-formatting-rules)
- [Cold runs and variance](#cold-runs-and-variance)
- [Platform limits](#platform-limits)
- [The bound-parameter cap](#the-bound-parameter-cap)
- [Introspection is blocked](#introspection-is-blocked)
- [Batching and the invisible statement](#batching-and-the-invisible-statement)
- [Sessions API and read replication](#sessions-api-and-read-replication)
- [Time Travel](#time-travel)
- [Automatic retries](#automatic-retries)
- [Error catalogue](#error-catalogue)
- [Import and export](#import-and-export)
- [Optimising for rows read](#optimising-for-rows-read)
- [Schema changes under a deploy gate](#schema-changes-under-a-deploy-gate)
- [libSQL and Turso](#libsql-and-turso)

---

## Two metrics, not one

D1 reports latency and rows-read per statement, and **they move independently**:

| Metric | What it is | Why it matters |
|---|---|---|
| `meta.timings.sql_duration_ms` | Server-side execution, excluding network | User-facing latency |
| `meta.rows_read` | Rows the engine **scanned** | **The billing unit** |
| `meta.rows_written` | Rows written (`INSERT`/`UPDATE`/`DELETE`) | Also billed |

Billing is per row scanned, regardless of row size: a 1 KB row and a 100 KB row each count
as one. Indexes add a written row when the indexed column is written (one to the table, one
to the index) — almost always repaid by the reduction in rows read.

Measured examples from the same session (verified 2026-08-04):

- A covering-index fix cut latency **~25x** (171.83 ms → 6.75 ms) while leaving rows read
  essentially **unchanged** (60,736 → 58,433). Latency win, **no billing win**.
- A watermark fix (replacing an unindexed `MAX()` scan with an indexed lookup) collapsed rows
  read roughly **58,000x** — 58,432 → 1. Billing win *and* latency win.

An "optimisation" that halves latency while leaving a 58k-row scan in place has not reduced
your D1 bill at all. Always report the pair.

---

## The meta object

Returned by `run()`, `all()`, and each result of `batch()`.

| Field | Meaning |
|---|---|
| `timings.sql_duration_ms` | SQL execution by the database instance, **excluding network time** — the number to optimise against |
| `duration` | Duration of the query execution, in milliseconds |
| `rows_read` | Rows scanned — the billing unit |
| `rows_written` | Rows written |
| `changes` | Number of changes made |
| `changed_db` | `true` if anything on the database changed — useful for asserting a statement really was read-only |
| `last_row_id` | Last inserted row id (not applicable to `WITHOUT ROWID` tables) |
| `size_after` | Database size after the query |
| `served_by_region` | Region of the instance that executed the query |
| `served_by_primary` | `true` only if the primary served it — the replica-routing tell |

```js
const { results, meta } = await env.DB
  .prepare("SELECT id, name FROM product WHERE org = ?").bind("acme").all();

console.log({
  ms: meta.timings.sql_duration_ms,
  scanned: meta.rows_read,
  efficiency: results.length / Math.max(meta.rows_read, 1),  // want close to 1.0
  region: meta.served_by_region,
  primary: meta.served_by_primary,
});
```

**Query efficiency** — rows returned ÷ rows read — is the single best one-number health
metric for a D1 statement. A query returning 20 rows after scanning 58,000 has an efficiency
of 0.0003 and is a missing index.

---

## Measuring correctly

**Use `sql_duration_ms`. Never wall-clock a `wrangler` invocation** — `npx`/wrangler startup
is roughly two seconds before any SQL executes, which drowns the signal entirely. A Worker
with a D1 binding talks to the database directly and pays none of that startup, so CLI wall
time is doubly misleading about production.

```bash
wrangler d1 execute atdw-mirror --remote --json \
  --command "SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%'" \
  | jq '.[0].meta | {ms: .timings.sql_duration_ms, rows_read, rows_written, served_by_primary}'
```

A 12-run median loop, reporting the range as well:

```bash
for i in $(seq 1 12); do
  wrangler d1 execute atdw-mirror --remote --json \
    --command "SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%'" \
    | jq -r '.[0].meta.timings.sql_duration_ms'
done | sort -n | awk '{a[NR]=$1} END {printf "median %.2f  min %.2f  max %.2f\n", a[int(NR/2)+1], a[1], a[NR]}'
```

`EXPLAIN QUERY PLAN` works over the same path and is the right first move:

```bash
wrangler d1 execute atdw-mirror --remote --json \
  --command "EXPLAIN QUERY PLAN SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%'" \
  | jq -r '.[0].results[].detail'
```

Pipe that into `scripts/eqp-triage.py` for severities and fix hints.

**Note `--remote`.** Omit it and you hit a *local* copy, which will happily give you fast,
meaningless numbers against different data.

---

## wrangler d1 insights

The feature most often missed. `d1 insights` ranks your **actual production queries** by
cost — it finds the expensive statement you didn't know to look for, which is precisely the
class of problem the "invisible aggregate" below belongs to.

```bash
# Slowest queries on average over the last day
wrangler d1 insights atdw-mirror --sort-type=avg --sort-by=time --limit=10

# Biggest total row-scanners over a week - the ones costing you money
wrangler d1 insights atdw-mirror --sort-type=sum --sort-by=reads --limit=10 --timePeriod=7d

# Most frequently executed - a cheap query run 10M times beats a slow one run twice
wrangler d1 insights atdw-mirror --sort-type=sum --sort-by=count --limit=10

# Machine-readable, for triage in a script
wrangler d1 insights atdw-mirror --sort-by=reads --limit=20 --json | jq '.[]'
```

| Flag | Values | Default |
|---|---|---|
| `--timePeriod` | e.g. `1d`, `7d` | `1d` |
| `--sort-type` | `sum`, `avg` | `sum` |
| `--sort-by` | `time`, `reads`, `writes`, `count` | `time` |
| `--sort-direction` | `ASC`, `DESC` | `DESC` |
| `--limit` | integer | — |
| `--json` | flag | `false` |

Reported per query: `avgRowsRead` / `totalRowsRead`, `avgRowsWritten` / `totalRowsWritten`,
`avgDurationMs` / `totalDurationMs`, `numberOfTimesRun`, and **`queryEfficiency`** (rows
returned ÷ rows read — target close to 1.0).

**Triage order that works:** sort by `sum`/`reads` first (total cost), then by `avg`/`time`
(worst single experience), then look for low `queryEfficiency` at high `numberOfTimesRun` —
that combination is a missing index on a hot path.

The command is marked experimental; if it changes, the same data is available through the
GraphQL Analytics API (`d1AnalyticsAdaptiveGroups`, `d1QueriesAdaptiveGroups`,
`d1StorageAdaptiveGroups`; fields include `readQueries`, `writeQueries`, `rowsRead`,
`rowsWritten`, `queryBatchTimeMs` with percentiles such as `queryBatchTimeMsP90`, and
`databaseSizeBytes`; 31-day retention).

---

## Statement formatting rules

**Statements must be on ONE LINE.** A multi-line `--command` fails with:

```
incomplete input: SQLITE_ERROR 7500
```

This is a wrapper-parsing artefact, not a SQL error, and the message is actively misleading —
it reads like unbalanced parentheses. Collapse to one line, or use a file.

```bash
# Fails: multi-line --command
wrangler d1 execute db --remote --command "SELECT a
FROM t"

# Works: one line
wrangler d1 execute db --remote --command "SELECT a FROM t"

# Works: multi-line via file
wrangler d1 execute db --remote --file ./query.sql
```

---

## Cold runs and variance

The **first run is not representative.** Observed on multi-thousand-row reads (verified
2026-08-04): a routine 1.5–1.7x penalty above median on the first execution, and one cold run
measured **2,495 ms against a 171 ms median** for the same statement — a 14x outlier.

- Take a **median of 10+ runs** and report the range.
- Discard, or at least label, the first run.
- Never compare a single before-run to a single after-run — that comparison can invert the
  true result entirely.
- Report honestly: "171.83 ms median, 168–2,495 ms across 12 runs", not "171 ms".

---

## Platform limits

From the D1 docs, 2026-08-04. Re-check before designing around any of them.

| Limit | Workers Paid | Workers Free |
|---|---|---|
| Databases per account | 50,000 | 10 |
| Maximum database size | 10 GB | 500 MB |
| Maximum storage per account | 1 TB | 5 GB |
| Queries per Worker invocation | 1,000 | 50 |
| Maximum SQL statement length | 100,000 bytes (100 KB) | same |
| **Maximum bound parameters per query** | **100** | same |
| Maximum SQL query duration | 30 seconds | same |
| Maximum columns per table | 100 | same |
| Maximum rows per table | Unlimited (within storage) | same |
| Maximum string / BLOB / row size | 2,000,000 bytes (2 MB) | same |
| Maximum arguments per SQL function | 32 | same |
| **Maximum bytes in a `LIKE`/`GLOB` pattern** | **50 bytes** | same |
| Maximum file import (`d1 execute --file`) | 5 GB | same |

Individual query limits apply to **each statement inside a batch**, not to the batch as a
whole.

Two of these interact with material elsewhere in this skill:

- **100 columns per table.** The worked example's 73-column table was already close. A wide
  table is exactly where covering indexes pay off most (see
  [`query-performance.md`](query-performance.md#covering-indexes)) — and past 100 columns you
  must split the table regardless.
- **50 bytes in a `LIKE` pattern.** Long user-supplied search strings will be rejected — a
  further argument for FTS5 `MATCH` over `LIKE` for real search
  ([`feature-modules.md`](feature-modules.md#the-trigram-tokenizer)).

Rows-read/written pricing (2026-08-04): Free 5M rows read + 100k written per day; Paid
includes 25B rows read + 50M written per month, then $0.001/M read and $1.00/M written;
storage 5 GB included, then $0.75/GB-month. No egress charges. **Read replicas cost nothing
extra** — you pay the same `rows_read`/`rows_written`.

---

## The bound-parameter cap

100 bound parameters per statement. Exceeding it:

```
too many SQL variables … SQLITE_ERROR 7500
```

This bites the moment you build `WHERE id IN (?, ?, ?, …)` from a list. Inline literals are
**not** capped — and that is the trap, because switching to string-interpolated literals to
dodge the cap is how SQL injection gets introduced.

**Chunk instead:**

```js
const CHUNK = 90;   // headroom under the 100-parameter cap
const out = [];
for (let i = 0; i < ids.length; i += CHUNK) {
  const slice = ids.slice(i, i + CHUNK);
  const placeholders = slice.map(() => "?").join(",");
  const { results } = await env.DB
    .prepare(`SELECT id, name FROM product WHERE id IN (${placeholders})`)
    .bind(...slice)
    .all();
  out.push(...results);
}
```

Note what is and isn't interpolated: the **placeholder string** is generated (safe — it is
`?` characters), the **values** are always bound. Never build the value list by
concatenation, whatever the cap says.

**Better still, one parameter for any list length** using `json_each`:

```sql
SELECT p.id, p.name FROM product p JOIN json_each(?) j ON j.value = p.id;
```

```js
await env.DB.prepare("SELECT p.id, p.name FROM product p JOIN json_each(?) j ON j.value = p.id")
  .bind(JSON.stringify(ids)).all();
```

Watch the 100 KB statement-length limit if you go the inline route for a very large list —
and remember chunked reads each count separately toward the 1,000-queries-per-invocation cap.

---

## Introspection is blocked

D1 refuses several introspection paths with `SQLITE_AUTH`. Verified refused 2026-08-04:

| Attempted | Result |
|---|---|
| `SELECT sqlite_version()` | `SQLITE_AUTH` |
| `SELECT * FROM pragma_module_list` | `SQLITE_AUTH` |

**Consequence: FTS5 and trigram availability on D1 could not be confirmed read-only.**
Confirming it requires `CREATE VIRTUAL TABLE`, which is a **write** — out of reach of a
read-only investigation and of a session under a deploy gate.

**This is genuinely unknown, not "probably fine".** If your design depends on FTS5 on D1,
verify deliberately: create a throwaway virtual table in a **preview/dev** D1 database (never
production) and observe. Record the result; do not infer it from stock SQLite behaviour.

What *does* work for schema discovery:

```sql
SELECT name, sql FROM sqlite_master WHERE type IN ('table','index');
SELECT * FROM pragma_table_info('q_product');
SELECT * FROM pragma_index_list('q_product');
SELECT * FROM pragma_index_info('q_product_org');
```

The `pragma_*` **table-valued functions** are the introspection route on D1 — the classic
`PRAGMA table_info(x)` statement form is not generally available through the HTTP path.
Connection-scoped pragmas (`journal_mode`, `busy_timeout`, `foreign_keys`, `synchronous`) are
managed by the platform and are not yours to set.

---

## Batching and the invisible statement

`batch()` sends multiple statements in one round trip, wrapped in an implicit transaction
that stops at the first failure. Good for latency and atomicity — **dangerous for
observability**: a statement inside someone else's batch adds no measurable round-trip cost,
so it never appears in per-request timing, while still doing all its work and billing every
row it reads.

The measured case (verified 2026-08-04): a `MAX()` over an unindexed column, riding inside a
batch another query was already sending, scanned **58,432 rows on every response across four
separate tools** and cost **28.09 ms** — invisible to per-query measurement.

```js
// Each of these is billed and timed separately, even though it is one round trip
const [a, b] = await env.DB.batch([
  env.DB.prepare("SELECT id, name FROM product WHERE org = ?").bind(org),
  env.DB.prepare("SELECT MAX(updated_at) FROM product"),   // <- full scan, hiding here
]);
```

**Audit rule for any D1 codebase:** enumerate every statement in every `batch()` and price
each individually. A batch's cost is the *sum* of its statements' rows read. `d1 insights`
sorted by `sum`/`reads` will surface these even when your own instrumentation cannot.

Batching has a *correctness* trap as well as this observability one: the batch rolls back
on a SQL error, but a conditional `UPDATE` matching 0 rows is **not** an error — see
[`d1-production-patterns.md`](d1-production-patterns.md#batch-and-the-0-row-conditional-write)
for the pre-check / post-verify / compensate shape.

---

## Sessions API and read replication

D1 can serve reads from **read replicas** — read-only copies, one per supported D1 region,
created and routed to automatically by Cloudflare **at no additional cost**. Writes always
go to the primary; replicas forward them.

Replication is **opt-in twice**: enable it on the database (dashboard → D1 → your database →
Settings → Enable Read Replication, or the REST API with `read_replication.mode: auto`),
**and** use the Sessions API in your Worker. Without `withSession()`, every query goes to the
primary and you get no benefit.

```ts
export default {
  async fetch(request, env) {
    // Continue a prior session's consistency guarantee, or start fresh
    const bookmark = request.headers.get("x-d1-bookmark") ?? "first-unconstrained";
    const session = env.DB.withSession(bookmark);

    const { results, meta } = await session
      .prepare("SELECT * FROM Customers WHERE CompanyName = ?")
      .bind("Bs Beverages")
      .all();

    const response = Response.json(results);
    // Hand the bookmark back so the NEXT request is at least as fresh as this one
    response.headers.set("x-d1-bookmark", session.getBookmark() ?? "");
    return response;
  },
};
```

| `withSession()` argument | Behaviour |
|---|---|
| `"first-unconstrained"` (default) | First query may go to any instance — lowest latency, may be slightly stale |
| `"first-primary"` | First query goes to the primary — freshest data, higher first-query latency |
| A bookmark string | Session starts at least as current as that bookmark |

**The consistency model is sequential consistency *within a session*.** Queries in one
session never see the database go backwards, and a read after a write in the same session
sees that write. Across sessions you get nothing unless you carry the bookmark — which is why
the header round-trip above is the whole pattern, not an optimisation.

The classic bug replication introduces: write, redirect, read — and the read lands on a
replica that hasn't caught up, so the user doesn't see their own change. Carrying the
bookmark (or using `first-primary` on the read-after-write path) is the fix.

`session.getBookmark()` returns `null` if no query ran in the session. Check
`meta.served_by_primary` and `meta.served_by_region` to see where a query actually landed —
that is how you verify replication is doing anything.

**When replication does not help:** write-heavy workloads (all writes hit the primary
anyway), single-region traffic, and workloads that require the absolute latest data on every
read.

For the production rollout shape — default every request to `first-primary` and let only an
enumerated allowlist of display-only GETs touch a replica, so a misclassified route degrades
to slower rather than staler — see
[`d1-production-patterns.md`](d1-production-patterns.md#read-replication-opt-in-to-replica-never-opt-out).

---

## Time Travel

D1's built-in point-in-time recovery. There is no stock-SQLite equivalent, and it is the
reason a D1 migration is less frightening than a local one.

| Property | Detail |
|---|---|
| Retention | **30 days** (Workers Paid), 7 days (Free) |
| Granularity | Any timestamp, or a bookmark |
| Restore is destructive | Overwrites the database **in place**; in-flight queries are cancelled |
| Restoring keeps history | Older bookmarks remain valid, so you can restore again to a different point |
| Bookmarks from timestamps | Deterministic — the same timestamp always yields the same bookmark |
| Not yet supported | Cloning/forking a database to a new one via Time Travel |
| Requires | Wrangler v3.4.0+, a production-version database |

```bash
# Current bookmark - capture this BEFORE any risky migration
wrangler d1 time-travel info atdw-mirror

# Restore to a Unix timestamp, or an ISO-8601 date-time string
wrangler d1 time-travel restore atdw-mirror --timestamp=1754280000
wrangler d1 time-travel restore atdw-mirror --timestamp=2026-08-04T11:18:53.000+10:00

# Restore to a specific bookmark
wrangler d1 time-travel restore atdw-mirror --bookmark=<BOOKMARK_ID>
```

**A restore is a production-state change** — maintainer-gated, exactly like a deploy. A
working session records the bookmark and reports the command; it does not run it.

Bookmarks are the same objects the Sessions API uses, which is what makes "restore to the
state that request saw" possible: log `session.getBookmark()` alongside a request id and you
can later restore to precisely that point.

---

## Automatic retries

D1 detects read-only queries and retries them up to **two** times on retryable failures.

- Only statements containing solely `SELECT`, `EXPLAIN`, or `WITH` are retried.
- Anything containing a write keyword is never retried.
- D1 checks for modifications after each execution and rolls back if a retry caused a write,
  so retries are side-effect-free even if detection is fooled.

**Implication for measurement:** a `sql_duration_ms` outlier may be a retried query. One more
reason to take a median rather than trusting a single sample.

Your own code still needs retry logic for **writes** — those are never retried for you.

---

## Error catalogue

| Message | Meaning | Action |
|---|---|---|
| `incomplete input: SQLITE_ERROR 7500` | Multi-line `--command` | One line, or `--file` |
| `too many SQL variables … SQLITE_ERROR 7500` | >100 bound parameters | Chunk, or `json_each` |
| `SQLITE_AUTH` | Blocked introspection (`sqlite_version()`, `pragma_module_list`) | Use `sqlite_master` / `pragma_*` functions |
| `D1 DB is overloaded. Requests queued for too long.` | Too many requests, or queries too slow | Optimise queries, spread load, shard |
| `D1 DB is overloaded. Too many requests queued.` | Queue too long | Same |
| `D1 DB's isolate exceeded its memory limit and was reset.` | A query loaded too much into memory | Shard the query; add a `LIMIT` |
| `D1 DB exceeded its CPU time limit and was reset.` | A very expensive scan, or a large import/export | Split into smaller statements |
| `Exceeded maximum DB size.` | Past the storage limit | Delete data, or shard across databases |
| `Your account has exceeded D1's maximum account storage limit` | Account-wide storage limit | Delete unused databases, or upgrade |
| `No SQL statements detected.` | Empty/invalid input | Check the statement made it through |
| `D1 DB reset because its code was updated.` / `Network connection lost.` | Transient platform events | Retry |

The "overloaded" and "CPU time limit" errors are the ones an unindexed scan produces at
scale. They are performance problems wearing an infrastructure costume — go to
[`query-performance.md`](query-performance.md), not to a support ticket.

---

## Import and export

```bash
# Export the whole database as SQL
wrangler d1 export atdw-mirror --remote --output=./database.sql

# Schema only / data only / one table
wrangler d1 export atdw-mirror --remote --output=./schema.sql --no-data
wrangler d1 export atdw-mirror --remote --output=./data.sql   --no-schema
wrangler d1 export atdw-mirror --remote --output=./one.sql    --table=q_product

# Import (this is a WRITE to production - maintainer-gated when --remote)
wrangler d1 execute atdw-mirror --local  --file=./database.sql
wrangler d1 execute atdw-mirror --remote --file=./database.sql
```

| Constraint | Detail |
|---|---|
| Import file size | 5 GB max for `d1 execute --file` — split larger loads and import sequentially |
| Statement length | 100 KB — split a huge `INSERT` into batches (e.g. 1,000 rows → four 250-row statements) |
| Transactions | **Remove `BEGIN TRANSACTION` / `COMMIT`** from a dump before importing |
| From local SQLite | `sqlite3 db.sqlite3 .dump > db.sql`, then strip the transaction statements |
| CPU limits | A very large import can trip the isolate's CPU limit — smaller chunks are the fix |

Export is also the honest way to get a local copy for plan experiments: export the schema,
import it locally, seed representative row counts, and iterate on indexes there before
proposing a migration.

---

## Optimising for rows read

Because rows read is the billing unit, D1 rewards optimisations stock SQLite treats as a
nice-to-have.

| Pattern | Rows-read effect |
|---|---|
| Index an aggregated column (`MAX`/`MIN` watermark) | Collapses a full scan to ~1 row — the biggest single win available |
| Add a selective `WHERE` a plain index can seek | Proportional reduction |
| Covering index for an unseekable predicate | Usually **no** rows-read change — latency only |
| Maintain a counter/summary row instead of `COUNT(*)` | O(n) scan → one row read |
| `LIMIT` with a matching index | Stops the scan early |
| Cache in KV or the Workers cache in front of D1 | Removes the read entirely |

Watermark pattern — the highest-value D1 refactor, from the measured session:

```sql
-- Before: full scan on every request
SELECT MAX(updated_at) FROM q_product;             -- 28.09 ms, 58,432 rows read

-- After: index the aggregated column
CREATE INDEX q_product_updated ON q_product(updated_at);
SELECT MAX(updated_at) FROM q_product;             -- 0.17 ms, 1 row read
```

`MAX(col)` over an indexed column is a walk to the end of the B-tree. That is the whole fix.

---

## Schema changes under a deploy gate

Creating an index on D1 is a **write to production**, applied through a migration and a
deploy — and deploys are maintainer-gated. A working session should:

1. Prove the payoff **read-only** using the technique in
   [`query-performance.md`](query-performance.md#the-read-only-proof-technique).
2. Write the migration file and commit it.
3. Capture a Time Travel bookmark so the maintainer has a named restore point.
4. **Stop.** Report the exact command and what it would change.

```bash
wrangler d1 time-travel info atdw-mirror              # record the restore point
wrangler d1 migrations apply atdw-mirror --local      # safe: local copy
wrangler d1 migrations apply atdw-mirror --remote     # MAINTAINER RUNS THIS
```

An unverified-until-deploy conclusion is a legitimate deliverable: "this index is projected
to cut the statement from 171 ms to ~7 ms based on a read-only proof; applying it needs a
gated deploy" beats applying it to find out.

And when the maintainer's `--remote` apply reports a timeout: the migration may have landed
anyway — verify schema state read-only before re-running
([`d1-production-patterns.md`](d1-production-patterns.md#migration-apply-can-time-out-yet-still-land)).

---

## libSQL and Turso

libSQL is a SQLite fork; Turso is its hosted service. Same planner, same SQL, different
operational envelope.

| Aspect | Note |
|---|---|
| Embedded replicas | Local read replica synced from the primary — reads local-fast, writes remote |
| Replica staleness | A read right after a write may not see it; sync or use read-your-writes support before assuming consistency |
| Connection model | HTTP/WebSocket to a server, embedded file, or embedded replica — pick deliberately; the performance profiles differ enormously |
| Extensions | libSQL adds features beyond stock SQLite (e.g. native vector support in recent versions). Verify against **your** server version — this moves |
| Pragmas | More of the pragma surface than D1, but a hosted primary still owns durability settings |
| Billing | Also reads-oriented — the rows-read discipline transfers directly |

The portability rule: **keep your SQL stock-SQLite unless you have a specific reason not
to.** A schema that runs unmodified on the CLI, on D1, and on Turso is worth real money in
optionality, and the vast majority of application SQL never needs a vendor extension.

---

## See also

- [`query-performance.md`](query-performance.md) — the engine-level analysis this builds on
- [`d1-production-patterns.md`](d1-production-patterns.md) — incident-derived procedures: timed-out migrations, `batch()` 0-row writes, the replication rollout
- [`hosts.md`](hosts.md) — the D1 driver API alongside the other hosts
- [`migration-patterns.md`](migration-patterns.md) — wrangler migrations and the deploy gate
- [`feature-modules.md`](feature-modules.md) — why FTS5 on D1 is recorded as unknown
- `cloudflare-ops` skill — Workers, bindings, wrangler configuration, deployment

# D1 Production Patterns

Three patterns learned running a multi-tenant production Worker on Cloudflare D1. Each is
incident-shaped — **symptom → why → procedure** — and each closes a gap where D1's happy-path
API reads as success while something else happened. [`d1-edge.md`](d1-edge.md) is the
platform reference (billing, limits, meta, Sessions API mechanics); this file is what those
mechanics do to you in production and the defensive shape that survives them.

> **Sourcing note.** These patterns come from a live multi-tenant billing platform on one
> Worker + one D1 database (2026-07/08): a migration-timeout incident, a conditional-write
> race in the Xero push path, and a signed-off read-replication rollout (its ADR and the
> ~100-line session module are distilled in pattern 3). The code shapes are generalised;
> the failure modes are not hypothetical.

## Contents

- [Migration apply can time out yet still land](#migration-apply-can-time-out-yet-still-land)
- [batch() and the 0-row conditional write](#batch-and-the-0-row-conditional-write)
- [Read replication: opt-in-to-replica, never opt-out](#read-replication-opt-in-to-replica-never-opt-out)

---

## Migration apply can time out yet still land

**Symptom.** `wrangler d1 migrations apply <db> --remote` reports a timeout or network
error. The natural reflex — re-run it — is the trap.

**Why.** The apply is an HTTP round trip to a remote engine, and the error you saw is about
the *response*, not the *work*. The migration can execute and be recorded server-side while
the CLI's connection dies waiting — so the client-visible outcome ("it failed") and the
database's actual state ("it applied") disagree. A blind re-run then re-executes SQL against
a database that already has it:

- `CREATE TABLE` / `CREATE INDEX` without `IF NOT EXISTS` → the re-run fails, which at
  least tells you the truth, confusingly.
- Seed/backfill `INSERT`s → **duplicated data**, which tells you nothing until something
  downstream breaks.
- `ALTER TABLE ... ADD COLUMN` → fails with "duplicate column name" (SQLite has no
  `IF NOT EXISTS` for column adds — this is why column-add migrations can never be made
  fully idempotent and *must* go through the verify step).

**Procedure — verify state before re-applying, read-only:**

```bash
# 1. What does wrangler think was applied? (D1 tracks applied migrations in its own table)
wrangler d1 migrations list <db> --remote

# 2. Does the schema object actually exist? Ask the database, not the CLI's last error.
wrangler d1 execute <db> --remote --json \
  --command "SELECT name, sql FROM sqlite_master WHERE name = 'new_table_or_index'"

# 3. For a column add, inspect the table shape (pragma_* function form — see d1-edge.md)
wrangler d1 execute <db> --remote --json \
  --command "SELECT * FROM pragma_table_info('the_table')"
```

Decision table:

| Observed | Meaning | Action |
|---|---|---|
| Object exists, migrations list shows it applied | Landed; only the response was lost | Nothing to do — do **not** re-run |
| Object exists, migrations list does NOT show it | Landed but bookkeeping is behind | Reconcile deliberately (the migration's SQL must not run twice) — never a blind re-apply |
| Object missing | Genuinely did not land | Re-apply |

Two habits make the incident boring instead of dangerous:

- **Write migrations idempotent-safe where the syntax allows it** — `IF NOT EXISTS` on
  every `CREATE`, `INSERT OR IGNORE` for seeds — so an accidental double-apply is a no-op.
  Where it doesn't (column adds), the verify-first procedure above is the whole protection.
- **Treat "timeout" as "state unknown", never as "failed".** The same discipline as any
  distributed write: an error after the request left the building tells you nothing about
  what the server did.

---

## batch() and the 0-row conditional write

**Symptom.** A scoped write inside a `batch()` — `UPDATE ... WHERE id = ? AND
tenant_id = ?` — "succeeds": no error, batch commits, caller returns 200. But the row
belongs to another tenant (or was already claimed, or doesn't exist), so the statement
matched **zero rows** and changed nothing. The caller reported success for a write that
never happened.

**Why.** `batch()` wraps its statements in a transaction and rolls back on a **SQL
error** — and a conditional `UPDATE`/`DELETE` matching 0 rows is *not* an error. It is a
successful statement with `meta.changes === 0`. This is correct SQL semantics on every
engine, but D1's batch framing makes it easy to read "the batch committed" as "every
statement did what I meant". The scoping predicate that makes multi-tenant writes safe
(`AND tenant_id = ?`) is exactly the predicate that turns an authorization failure into a
silent no-op.

**Procedure — pre-check, post-verify, compensate.** The rule in one line: **0 rows
affected on a scoped UPDATE/DELETE means 403/404/conflict, never success.**

The single-statement shape — check `meta.changes` on every conditional write:

```ts
const res = await db
  .prepare(`UPDATE clients SET name = ?, updated_at = ? WHERE id = ? AND tenant_id = ?`)
  .bind(name, now, clientId, tenantId)
  .run();
if ((res.meta?.changes ?? 0) === 0) throw NotFound('client not found');
// 0 here means: wrong tenant, or no such row. Either way, NOT success.
```

The compare-and-set shape — a conditional claim where exactly one racer may win:

```ts
// Claim is a CAS on the ownership column, never an assumption. Two racers issue the
// same UPDATE; the WHERE ... IS NULL lets exactly one match. The loser's statement
// "succeeds" with 0 changes — the explicit count check is what turns that into Conflict.
const claim = await db
  .prepare(`UPDATE push_intents SET document_id = ?, updated_at = ?
             WHERE id = ? AND tenant_id = ? AND document_id IS NULL`)
  .bind(documentId, now, intentId, tenantId)
  .run();
if ((claim.meta?.changes ?? 0) !== 1) throw Conflict();
```

The batch shape — conditional statements inside a `batch()` need a **post-verify count
and a compensating undo**, because the batch cannot fail itself on your behalf:

```ts
// lockStmts are conditional claims (`... WHERE owner_id IS NULL`): a racing writer
// matches zero rows there and the batch still commits. The post-verify is the gate.
await db.batch([docStmt, ...lineStmts, ...lockStmts]);

const locked = await db
  .prepare(`SELECT COUNT(*) AS n FROM entries WHERE tenant_id = ? AND owner_id = ?`)
  .bind(tenantId, documentId)
  .first<{ n: number }>();
if ((locked?.n ?? 0) !== expectedCount) {
  await db.batch(undoStmts);   // compensate: release partial claims, delete the doc
  throw Conflict();            // a concurrent writer claimed some rows first
}
```

`batch()` returns one result per statement, each with its own `meta` — so for batches
where each statement's effect matters, walk the results and check `meta.changes`
per-statement rather than trusting the commit. (Counting actual changes is also how you
report an honest number for `INSERT OR IGNORE` batches: sum `meta.changes`, don't count
statements.)

When you *want* the batch to abort atomically on a precondition, invert the trick: make
the guard statement **violate a constraint** on failure (e.g. an `INSERT` that collides
with a `UNIQUE` index) — a real SQL error rolls the whole batch back. That is the one
shape where "error aborts batch" works *for* you; a 0-row match never will.

---

## Read replication: opt-in-to-replica, never opt-out

**Symptom class this prevents.** With D1 read replication enabled (see
[`d1-edge.md`](d1-edge.md#sessions-api-and-read-replication) for the mechanics), the naive
rollout routes *all* reads through replica-eligible sessions and then exempts the routes
someone remembered are sensitive. Every route the exemption list misses is a **stale read
feeding a write flow** — a balance check, a pre-push billing review, a lock check — and the
failure only shows up as an occasional wrong decision under replica lag, which is the worst
possible way to discover a route classification bug.

**The pattern.** Invert the default so a classification mistake degrades to *slower*,
never to *staler*:

1. **Every request defaults to `first-primary`** — strongly consistent, identical to
   pre-replication behaviour.
2. A request may serve from a replica **only** when it is a `GET` **and** its path is
   positively enumerated in a replica allowlist of display-only surfaces.
3. A route missing from the allowlist — by oversight or by design — stays on the primary:
   no latency win, no correctness risk. **A misclassification cannot corrupt data.** The
   partition's safety is a property of the code shape, not of per-query vigilance.
4. **Writes always hit the primary** regardless of session mode (D1 routes writes to the
   primary itself) — so the only thing the allowlist can get wrong is letting a
   *pre-write read* see stale data. Keep every entry display-only.
5. **Carry the session bookmark in a cookie** so the caller's next request is bounded to
   be at least as fresh as their own last write (read-your-writes), even when served by
   a replica.

The whole decision fits in ~100 lines, and isolating it in one module is part of the
pattern — one place owns the safety decision, and the data layer just receives a session:

```ts
/** Display-only GET routes allowed to serve from a nearby read replica.
 *  Two matching shapes, chosen deliberately per entry:
 *   - Trailing '/': whole-subtree prefix. Safe ONLY because the method gate below
 *     excludes every mutation under it anyway.
 *   - No trailing slash: exact path only. Use this when the same mount point also
 *     serves write-flow reads (e.g. '/api/time/dashboard' is listed, but the
 *     billing-review reads under '/api/time/billing' must never become eligible —
 *     a prefix would have silently swept them in).
 *  When unsure, leave the route off — the default (primary) is always safe, just slower. */
const REPLICA_ALLOWLIST: readonly string[] = [
  '/api/analytics/',        // subtree: pure read-model surface
  '/api/dashboard',         // exact: sibling routes include write-flow reads
];

function isReplicaEligible(method: string, path: string): boolean {
  if (method !== 'GET') return false;
  return REPLICA_ALLOWLIST.some((e) => (e.endsWith('/') ? path.startsWith(e) : path === e));
}

/** Argument for env.DB.withSession(...) for this request. */
function pickSessionMode(method: string, path: string, bookmark: string | null): string {
  if (!isReplicaEligible(method, path)) return 'first-primary';   // the safe default
  // Replica-eligible: constrain to the caller's own last write if they have one.
  return bookmark ?? 'first-unconstrained';
}

// In the request middleware: one session per request, bookmark round-tripped in a cookie.
const bookmark = getCookie(request, 'd1_bookmark');
const session = env.DB.withSession(pickSessionMode(request.method, url.pathname, bookmark));
// ... handlers run against `session` instead of `env.DB` ...
const newBookmark = session.getBookmark();
if (newBookmark) setCookie(response, 'd1_bookmark', newBookmark);
```

Classification guidance from the production partition that shipped:

| Surface | Classification | Why |
|---|---|---|
| Analytics, dashboards, index/list read-models | Replica-eligible | Seconds of lag is invisible on a display surface |
| Money *display* (totals, statements) | Replica-eligible | Display-only; nothing decides on it |
| Any read **inside a write flow** (balance/lock checks, review-before-push) | Primary-only | A stale read here feeds a mutation |
| Read immediately after that user's own write | Covered by the bookmark | Read-your-writes without pinning the route to primary |

Two operational notes:

- **Local test environments have one D1 and no replicas**, so the *refactor* (threading a
  session through the data layer) is testable locally but the *staleness behaviour* is
  not — validate replication in a preview/staging environment, and verify routing in
  production via `meta.served_by_primary` / `meta.served_by_region`
  ([`d1-edge.md`](d1-edge.md#the-meta-object)).
- Session mode only constrains the **first** query; within a session D1 guarantees
  sequential consistency. The allowlist + bookmark pattern is about *choosing* the right
  first-query constraint per request, cheaply and safely, for every route you have —
  including the ones nobody thought about.

---

## See also

- [`d1-edge.md`](d1-edge.md) — the D1 platform reference: billing, limits, `meta`,
  Sessions API mechanics, Time Travel, error catalogue
- [`migration-patterns.md`](migration-patterns.md) — wrangler migrations, numbering,
  the deploy gate these apply-verification steps slot into
- [`concurrency-durability.md`](concurrency-durability.md) — the engine-level locking
  model behind the CAS/claim shapes
- `cloudflare-ops` skill — Workers, bindings, wrangler configuration, deployment

# SQLite Feature Modules

FTS5, JSON/JSONB, R-tree, window functions, upsert, and RETURNING. Engine-agnostic SQL —
but **availability varies by build**, and that is the first thing to check. See
[Checking availability](#checking-availability).

## Contents

- [Checking availability](#checking-availability)
- [FTS5 full-text search](#fts5-full-text-search)
- [The trigram tokenizer](#the-trigram-tokenizer)
- [External-content FTS tables](#external-content-fts-tables)
- [JSON functions](#json-functions)
- [JSONB](#jsonb)
- [R-tree](#r-tree)
- [Window functions](#window-functions)
- [Upsert](#upsert)
- [RETURNING](#returning)
- [Other useful modules](#other-useful-modules)

---

## Checking availability

FTS5, R-tree, and JSON are **compile-time options**. Most distributions include all three;
some minimal or embedded builds don't, and managed engines may block the introspection you
would use to find out.

```sql
SELECT sqlite_version();                          -- version gate for syntax features
SELECT * FROM pragma_compile_options;             -- look for ENABLE_FTS5, ENABLE_RTREE
SELECT * FROM pragma_module_list;                 -- registered virtual-table modules
```

| Feature | Minimum version | Notes |
|---|---|---|
| Window functions | 3.25 (2018) | Also `ALTER TABLE RENAME COLUMN` |
| Upsert (`ON CONFLICT DO UPDATE`) | 3.24 | |
| `RETURNING` | 3.35 (2021) | Also `ALTER TABLE DROP COLUMN` |
| `STRICT` tables | 3.37 | |
| `->` / `->>` JSON operators | 3.38 | JSON functions themselves are much older |
| JSONB | 3.45 (2024) | Internal binary format |
| FTS5 `trigram` tokenizer | 3.34 | |

**On Cloudflare D1, `sqlite_version()` and `pragma_module_list` are both refused with
`SQLITE_AUTH`** (verified 2026-08-04). Confirming FTS5 availability there requires
`CREATE VIRTUAL TABLE`, which is a write — so it **could not be confirmed read-only**, and
this file records it as genuinely unknown. Test it in a preview/dev D1 database if your
design depends on it; do not assume from stock SQLite behaviour. See
[`d1-edge.md`](d1-edge.md).

---

## FTS5 full-text search

A virtual table that maintains an inverted index over text columns.

```sql
CREATE VIRTUAL TABLE doc_fts USING fts5(title, body);

INSERT INTO doc_fts (title, body) VALUES ('Indexing', 'How B-trees work in SQLite');

-- Match syntax
SELECT * FROM doc_fts WHERE doc_fts MATCH 'btree';
SELECT * FROM doc_fts WHERE doc_fts MATCH '"exact phrase"';
SELECT * FROM doc_fts WHERE doc_fts MATCH 'index*';             -- prefix
SELECT * FROM doc_fts WHERE doc_fts MATCH 'sqlite NOT mysql';
SELECT * FROM doc_fts WHERE doc_fts MATCH 'title: indexing';    -- column filter
SELECT * FROM doc_fts WHERE doc_fts MATCH 'NEAR(btree sqlite, 5)';
```

### Ranking

```sql
-- bm25(): lower (more negative) is better; ORDER BY rank uses it automatically
SELECT title, rank FROM doc_fts WHERE doc_fts MATCH 'sqlite' ORDER BY rank LIMIT 10;

-- Column weights: title matters 10x more than body
SELECT title, bm25(doc_fts, 10.0, 1.0) AS score
FROM doc_fts WHERE doc_fts MATCH 'sqlite'
ORDER BY score LIMIT 10;

-- Highlighted excerpt
SELECT snippet(doc_fts, 1, '<b>', '</b>', '…', 20) FROM doc_fts WHERE doc_fts MATCH 'sqlite';
SELECT highlight(doc_fts, 0, '[', ']')             FROM doc_fts WHERE doc_fts MATCH 'sqlite';
```

### Tokenizers

```sql
CREATE VIRTUAL TABLE t USING fts5(body, tokenize = 'unicode61 remove_diacritics 2');
CREATE VIRTUAL TABLE t USING fts5(body, tokenize = 'porter unicode61');   -- stemming
CREATE VIRTUAL TABLE t USING fts5(body, tokenize = 'trigram');            -- substring
```

| Tokenizer | Use for |
|---|---|
| `unicode61` (default) | General word search; `remove_diacritics 2` folds accents |
| `porter` | English stemming — "running" matches "run" |
| `ascii` | ASCII-only, fastest, no Unicode folding |
| `trigram` | **Substring** search — the real fix for `LIKE '%x%'` |

### Maintenance

```sql
INSERT INTO doc_fts(doc_fts) VALUES ('optimize');   -- merge index segments; do periodically
INSERT INTO doc_fts(doc_fts) VALUES ('rebuild');    -- rebuild from content table
PRAGMA integrity_check;                             -- also checks FTS structures
```

---

## The trigram tokenizer

The answer to substring search. It indexes every 3-character sequence, so both `MATCH` and —
uniquely — `LIKE '%…%'` become index-backed.

```sql
CREATE VIRTUAL TABLE org_fts USING fts5(name, tokenize = 'trigram');
INSERT INTO org_fts(name) SELECT DISTINCT org FROM q_product;

SELECT * FROM org_fts WHERE org_fts MATCH 'acme';       -- substring, index-backed
SELECT * FROM org_fts WHERE name LIKE '%acme%';         -- ALSO index-backed on a trigram table
```

| Property | Detail |
|---|---|
| Minimum search length | 3 characters — shorter patterns fall back to a scan |
| Case handling | Case-insensitive by default (`case_sensitive 1` to change) |
| Index size | Large — roughly one entry per character position |
| Write cost | Higher than `unicode61`; not for high-churn columns |

**Decision rule.** For a leading-wildcard predicate:

- Occasional query, wide table → covering index (see
  [`query-performance.md`](query-performance.md#covering-indexes)) — cheap, no new object.
- Frequent query, or the scan is genuinely too big → trigram FTS5 — eliminates the scan,
  costs index size and write throughput.

The covering index makes the scan cheap; trigram makes the scan disappear.

---

## External-content FTS tables

By default FTS5 stores its own copy of the text. An **external-content** table indexes rows
that live in an ordinary table, halving storage.

```sql
CREATE TABLE doc (id INTEGER PRIMARY KEY, title TEXT, body TEXT) STRICT;

CREATE VIRTUAL TABLE doc_fts USING fts5(
    title, body,
    content = 'doc',
    content_rowid = 'id'
);

-- You must maintain the index yourself, via triggers
CREATE TRIGGER doc_ai AFTER INSERT ON doc BEGIN
    INSERT INTO doc_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
CREATE TRIGGER doc_ad AFTER DELETE ON doc BEGIN
    INSERT INTO doc_fts(doc_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
END;
CREATE TRIGGER doc_au AFTER UPDATE ON doc BEGIN
    INSERT INTO doc_fts(doc_fts, rowid, title, body) VALUES ('delete', old.id, old.title, old.body);
    INSERT INTO doc_fts(rowid, title, body) VALUES (new.id, new.title, new.body);
END;
```

**The delete trigger's odd shape is mandatory**: FTS5 needs the *old values* to remove the
right index entries, and it cannot read them from the content table (they're already gone).
Omitting the old values corrupts the index silently — searches start returning deleted rows.

`contentless` tables (`content=''`) store no text at all: smallest, but `snippet()`/
`highlight()` and updates are unavailable. Use for pure "which rowids match" lookups.

---

## JSON functions

```sql
CREATE TABLE event (id INTEGER PRIMARY KEY, payload TEXT NOT NULL) STRICT;

-- Extract
SELECT json_extract(payload, '$.user.id')  FROM event;   -- SQL value
SELECT payload -> '$.user'                 FROM event;   -- JSON representation
SELECT payload ->> '$.user.id'             FROM event;   -- SQL value (3.38+, preferred)

-- Modify (returns a new document; does not mutate in place)
UPDATE event SET payload = json_set(payload, '$.status', 'done')    WHERE id = ?;
UPDATE event SET payload = json_remove(payload, '$.tmp')            WHERE id = ?;
UPDATE event SET payload = json_patch(payload, '{"a":1,"b":null}')  WHERE id = ?;  -- RFC 7386

-- Build
SELECT json_object('id', id, 'kind', payload ->> '$.kind') FROM event;
SELECT json_group_array(json_object('id', id))             FROM event;

-- Inspect
SELECT json_valid(payload), json_type(payload, '$.tags'), json_array_length(payload, '$.tags')
FROM event;
```

### Expanding arrays and objects

`json_each` and `json_tree` are table-valued functions — the workhorses of JSON querying.

```sql
-- One row per array element
SELECT e.id, t.value AS tag
FROM event e, json_each(e.payload, '$.tags') t
WHERE t.value = 'urgent';

-- Turn a bound JSON array into a joinable set (one parameter, any length —
-- the standard way past a host's bound-parameter cap)
SELECT p.* FROM product p JOIN json_each(?) j ON j.value = p.id;

-- Recursive walk of the whole document
SELECT fullkey, value FROM event, json_tree(event.payload) WHERE atom IS NOT NULL;
```

### Indexing JSON

`json_extract` on a plain column is **never** indexable. Use a generated column or an
expression index:

```sql
ALTER TABLE event ADD COLUMN kind TEXT
    GENERATED ALWAYS AS (payload ->> '$.kind') VIRTUAL;
CREATE INDEX event_kind ON event(kind);

-- or, without changing the table shape
CREATE INDEX event_kind_expr ON event(json_extract(payload, '$.kind'));
```

The query must use the **same expression** as the index, syntactically. An index on
`json_extract(payload,'$.kind')` is not used by a query written with `payload ->> '$.kind'`.

---

## JSONB

SQLite 3.45+ adds a binary JSON representation. Every `json_*` function has a `jsonb_*`
counterpart that returns the binary form.

```sql
CREATE TABLE doc (id INTEGER PRIMARY KEY, body BLOB) STRICT;
INSERT INTO doc (body) VALUES (jsonb('{"a":1,"b":[2,3]}'));

SELECT body ->> '$.a' FROM doc;        -- operators work directly on JSONB
SELECT json(body) FROM doc;            -- back to text for display/export
```

| | JSON (TEXT) | JSONB (BLOB) |
|---|---|---|
| Parse cost per read | Full re-parse | None — already parsed |
| Storage | Slightly smaller for simple docs | Usually smaller for nested docs |
| Human-readable in a CLI dump | Yes | No — wrap in `json()` |
| Portability | Universal | SQLite-internal format; **not** PostgreSQL's JSONB |

Use JSONB when documents are read and traversed frequently. Keep TEXT when the column is
mostly passed through to an application that parses it anyway, or when tooling needs to read
the file directly. The format is a SQLite implementation detail — do not send it over a wire
or store it expecting another system to read it.

---

## R-tree

A virtual table for bounding-box and interval overlap queries. Compile-time module
(`ENABLE_RTREE`), enabled in most builds.

```sql
CREATE VIRTUAL TABLE place_idx USING rtree(
    id,                  -- INTEGER primary key, joins to the real table
    min_lon, max_lon,
    min_lat, max_lat
);

INSERT INTO place_idx VALUES (1, 151.20, 151.22, -33.87, -33.85);

-- Bounding-box query: fast, index-backed
SELECT p.name FROM place p JOIN place_idx i ON p.id = i.id
WHERE i.min_lon <= 151.25 AND i.max_lon >= 151.15
  AND i.min_lat <= -33.80 AND i.max_lat >= -33.90;
```

R-tree gives you the **coarse filter**; apply exact geometry or distance maths afterwards on
the small result set. It also works for one-dimensional intervals (time ranges, version
ranges) by using a single min/max pair.

For real geospatial work (projections, true distance, polygon operations) you need
SpatiaLite, a separate loadable extension.

---

## Window functions

SQLite 3.25+, with essentially PostgreSQL-compatible syntax.

```sql
-- Running total
SELECT id, amount, sum(amount) OVER (ORDER BY created_at) AS running
FROM txn;

-- Rank within a group
SELECT org, name, price,
       row_number() OVER (PARTITION BY org ORDER BY price DESC) AS rn,
       rank()       OVER (PARTITION BY org ORDER BY price DESC) AS rnk
FROM product;

-- Compare to the previous row (gap detection)
SELECT id, created_at,
       lag(created_at) OVER (ORDER BY created_at) AS prev,
       julianday(created_at) - julianday(lag(created_at) OVER (ORDER BY created_at)) AS gap_days
FROM event;

-- Top-N per group: filter on the window result via a CTE
WITH ranked AS (
    SELECT *, row_number() OVER (PARTITION BY org ORDER BY price DESC) AS rn FROM product
)
SELECT * FROM ranked WHERE rn <= 3;
```

Available: `row_number`, `rank`, `dense_rank`, `percent_rank`, `cume_dist`, `ntile`, `lag`,
`lead`, `first_value`, `last_value`, `nth_value`, plus every aggregate used as a window
function. Frame specifications (`ROWS BETWEEN … `, `RANGE BETWEEN …`, `GROUPS`) are
supported.

**Performance:** an `OVER (ORDER BY x)` clause needs the rows in `x` order — an index on `x`
avoids a temp B-tree, which will show up in `EXPLAIN QUERY PLAN` if it's missing.

---

## Upsert

```sql
-- Insert or update
INSERT INTO cache (key, value, expires_at) VALUES (?, ?, ?)
ON CONFLICT(key) DO UPDATE SET
    value = excluded.value,
    expires_at = excluded.expires_at;

-- Conditional update (only overwrite if newer)
INSERT INTO cache (key, value, updated_at) VALUES (?, ?, ?)
ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at
WHERE excluded.updated_at > cache.updated_at;

-- Insert or ignore
INSERT INTO seen (hash) VALUES (?) ON CONFLICT DO NOTHING;

-- Counter increment
INSERT INTO counter (name, n) VALUES (?, 1)
ON CONFLICT(name) DO UPDATE SET n = n + 1;
```

`excluded.*` refers to the row that *would* have been inserted. The conflict target
(`ON CONFLICT(key)`) must match a `UNIQUE` constraint or index.

The older `INSERT OR REPLACE` is **not** the same thing: it deletes and re-inserts, so it
fires `ON DELETE CASCADE`, drops columns you didn't supply back to their defaults, and
allocates a new rowid. Prefer `ON CONFLICT DO UPDATE` unless you specifically want the
delete semantics.

---

## RETURNING

SQLite 3.35+. Read back the rows a write touched, in one statement.

```sql
INSERT INTO product (sku, price) VALUES (?, ?) RETURNING id, created_at;
UPDATE product SET price = price * 1.1 WHERE org = ? RETURNING id, price;
DELETE FROM session WHERE expires_at < datetime('now') RETURNING token;
```

The highest-value use is an **atomic claim**, which without `RETURNING` needs a
select-then-update race:

```sql
UPDATE job_queue
SET status = 'running', started_at = datetime('now')
WHERE id = (
    SELECT id FROM job_queue WHERE status = 'pending'
    ORDER BY priority DESC, created_at LIMIT 1
)
RETURNING *;
```

Note the row order of a `RETURNING` result is undefined, and the rows are produced *before*
triggers and foreign-key actions complete — don't rely on either.

---

## Other useful modules

| Module | Purpose |
|---|---|
| `json_each` / `json_tree` | Table-valued JSON expansion (above) |
| `generate_series(a,b,step)` | Row generator — calendars, gap-filling, test data |
| `dbstat` | Per-table/index page usage — the honest answer to "what is taking up space" |
| `pragma_*` functions | `pragma_table_info('t')`, `pragma_index_list('t')` as queryable tables |
| `carray` | Bind a C array as a table (available in some builds/CLI) |
| `sqlite_dbpage` | Raw page access — recovery tooling only |

```sql
-- Gap-fill a daily report with generate_series
SELECT d.value AS day, coalesce(count(e.id), 0) AS n
FROM generate_series(
        (SELECT min(cast(strftime('%s', created_at) AS INTEGER)) FROM event),
        (SELECT max(cast(strftime('%s', created_at) AS INTEGER)) FROM event),
        86400) d
LEFT JOIN event e ON date(e.created_at) = date(d.value, 'unixepoch')
GROUP BY 1 ORDER BY 1;
```

---

## See also

- [`query-performance.md`](query-performance.md) — when trigram FTS beats a covering index
- [`schema-design.md`](schema-design.md) — generated columns for indexable JSON fields
- [`schema-patterns.md`](schema-patterns.md) — the FTS-backed document table recipe
- [`d1-edge.md`](d1-edge.md) — why FTS5 availability on D1 is unconfirmed

# SQLite Hosts

One engine, many drivers. The SQL, the planner, and the pragmas are identical everywhere —
this file covers the **driver surface** and the traps that differ per host.

## Contents

- [The portable connection baseline](#the-portable-connection-baseline)
- [sqlite3 CLI](#sqlite3-cli)
- [Python: sqlite3](#python-sqlite3)
- [Python: aiosqlite](#python-aiosqlite)
- [node:sqlite](#nodesqlite)
- [better-sqlite3](#better-sqlite3)
- [bun:sqlite](#bunsqlite)
- [Cloudflare D1](#cloudflare-d1)
- [libSQL / Turso](#libsql--turso)
- [Host comparison](#host-comparison)

---

## The portable connection baseline

Every host that gives you a real connection should apply the same four pragmas on **every
connection** (only `journal_mode` is persistent — the rest are per-connection and reset each
time). Rationale in [`concurrency-durability.md`](concurrency-durability.md).

```sql
PRAGMA journal_mode = WAL;      -- once per database (persistent)
PRAGMA busy_timeout = 5000;     -- every connection
PRAGMA foreign_keys = ON;       -- every connection
PRAGMA synchronous = NORMAL;    -- every connection
```

The single most common bug across all hosts below is setting these once at startup and
missing the connections a pool or framework creates later.

---

## sqlite3 CLI

```bash
sqlite3 app.db                     # interactive
sqlite3 app.db 'SELECT 1;'         # one-shot
sqlite3 -readonly app.db 'SELECT 1;'
```

| Dot command | Purpose |
|---|---|
| `.tables` / `.schema t` / `.indexes t` | Structure |
| `.timer on` | **Engine-reported timing** — the only honest CLI measurement |
| `.stats on` | VM steps, sorts, full-scan steps per statement |
| `.mode box\|json\|csv\|markdown` | Output format (`box` for reading, `json` for piping) |
| `.headers on` | Column names |
| `.once file` / `.output file` | Redirect the next / all results |
| `.import --csv data.csv t` | Bulk load |
| `.dump` / `.read f.sql` | Text backup / run a script |
| `.expert` | Suggests indexes for a statement (build-dependent) |
| `.eqp on` | Auto-print the query plan for every statement |

```bash
# Export
sqlite3 app.db -header -csv 'SELECT * FROM product;' > product.csv
sqlite3 app.db -json 'SELECT * FROM product LIMIT 5;' | jq '.[0]'

# Pragmas in a one-shot invocation (they apply to that connection only)
sqlite3 app.db 'PRAGMA foreign_keys=ON; DELETE FROM author WHERE id=1;'
```

**Trap:** the CLI does not enable foreign keys for you. A manual `DELETE` from the CLI can
leave orphans in a database whose application always sets the pragma.

---

## Python: sqlite3

Standard library. The main traps are transaction handling and thread affinity.

```python
import sqlite3

def connect(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(path, timeout=5.0, isolation_level=None)
    conn.row_factory = sqlite3.Row          # dict-like access by column name
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA busy_timeout = 5000")
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA synchronous = NORMAL")
    return conn
```

| Trap | Detail |
|---|---|
| Implicit transactions | By default the module opens a transaction before DML and commits on `commit()`. `isolation_level=None` turns this off so **you** write `BEGIN IMMEDIATE` explicitly — strongly preferred (see [the upgrade deadlock](concurrency-durability.md#transaction-modes-and-the-upgrade-deadlock)) |
| DDL and autocommit | Older Pythons implicitly committed before DDL; explicit control avoids version-dependent surprises |
| `check_same_thread=False` | Lets a connection cross threads, but does **not** make it thread-safe — you must serialise access yourself. One connection per thread is the safe pattern |
| `timeout=` | This is `busy_timeout` in **seconds**, set at connect time |
| `executemany` | Use for bulk inserts; wrap in one explicit transaction for the real win |
| `detect_types` | Legacy converters; prefer explicit conversion in your own code |
| Python 3.12+ | Warns on deprecated default adapters for `date`/`datetime` — store ISO text yourself |

```python
# Explicit transaction with retry-friendly semantics
conn.execute("BEGIN IMMEDIATE")
try:
    conn.executemany("INSERT INTO event (kind, payload) VALUES (?, ?)", rows)
    conn.execute("COMMIT")
except Exception:
    conn.execute("ROLLBACK")
    raise
```

```python
# Read the plan from Python — no external binary needed
for row in conn.execute("EXPLAIN QUERY PLAN SELECT * FROM event WHERE kind = ?", ("login",)):
    print(row["detail"])
```

`scripts/eqp-triage.py` in this skill uses exactly this path, which is why it needs no
`sqlite3` binary on PATH.

---

## Python: aiosqlite

A thread-pool wrapper around `sqlite3` with an async API. It does **not** make SQLite
concurrent — there is still one writer, and each connection still occupies a worker thread.

```python
import aiosqlite

async def connect(path: str) -> aiosqlite.Connection:
    conn = await aiosqlite.connect(path, isolation_level=None)
    conn.row_factory = aiosqlite.Row
    await conn.execute("PRAGMA busy_timeout = 5000")
    await conn.execute("PRAGMA foreign_keys = ON")
    return conn
```

| Consideration | Guidance |
|---|---|
| When it helps | Keeps an async event loop unblocked during disk I/O |
| When it doesn't | CPU-bound queries; write-heavy workloads (still serialised) |
| Pooling | A small pool of read connections + **one** dedicated write connection is the pattern that works |
| Long transactions | Especially harmful here — an awaited call inside a transaction can hold a lock across arbitrary scheduling delays |

**Deep dive:** [`async-patterns.md`](async-patterns.md) — async CRUD, batching, pooling.

---

## node:sqlite

Built into modern Node, synchronous, zero dependencies.

```js
import { DatabaseSync } from "node:sqlite";

const db = new DatabaseSync("app.db");
db.exec("PRAGMA journal_mode = WAL");
db.exec("PRAGMA busy_timeout = 5000");
db.exec("PRAGMA foreign_keys = ON");

const insert = db.prepare("INSERT INTO event (kind, payload) VALUES (?, ?)");
insert.run("login", JSON.stringify({ user: 1 }));

const rows = db.prepare("SELECT * FROM event WHERE kind = ?").all("login");
const one  = db.prepare("SELECT * FROM event WHERE id = ?").get(1);
```

| Note | Detail |
|---|---|
| Synchronous by design | Blocks the event loop — fine for fast statements, bad for long scans |
| API stability | Newer than the alternatives; check your Node version's docs before relying on a specific method |
| No native async | For long-running work use a worker thread, not a promise wrapper |
| Named parameters | Supported (`@name`/`:name`), style varies by version — verify against your Node |

Use it when you want no native build step and no dependency. Use better-sqlite3 when you
want the most mature Node API and the broadest feature surface.

---

## better-sqlite3

The mature Node option. Synchronous, native addon, fastest of the Node choices.

```js
import Database from "better-sqlite3";

const db = new Database("app.db");
db.pragma("journal_mode = WAL");
db.pragma("busy_timeout = 5000");
db.pragma("foreign_keys = ON");

// Prepared statements are the unit of reuse - prepare ONCE, at module scope
const findByKind = db.prepare("SELECT * FROM event WHERE kind = ?");
const rows = findByKind.all("login");

// Transactions: the wrapper handles BEGIN/COMMIT/ROLLBACK
const insertMany = db.transaction((events) => {
  for (const e of events) insertOne.run(e.kind, e.payload);
});
insertMany(events);          // one transaction, one fsync
```

| Feature | Note |
|---|---|
| `.transaction(fn)` | Wraps in `BEGIN`/`COMMIT`; use `.immediate(...)` for write transactions |
| Statement reuse | Re-preparing in a loop is the #1 performance mistake with this driver |
| `.iterate()` | Streams rows without materialising the whole result |
| `.pluck()` / `.raw()` | Single-column / array-row modes; avoid object allocation in hot loops |
| Native build | Needs a prebuilt binary or a toolchain — the cost of admission |
| WASM alternatives | `sql.js`, `wa-sqlite` for browsers/edge — different performance envelope entirely |

Synchronous is a **feature** here: it eliminates a class of race conditions, and SQLite reads
from page cache are fast enough that the event-loop cost is usually negligible. Measure
before assuming you need async.

---

## bun:sqlite

Built into Bun. API is close to better-sqlite3 but **not identical** — porting code between
them needs review, not just a find-and-replace on the import.

```js
import { Database } from "bun:sqlite";

const db = new Database("app.db");
db.run("PRAGMA journal_mode = WAL");
db.run("PRAGMA busy_timeout = 5000");
db.run("PRAGMA foreign_keys = ON");

const q = db.query("SELECT * FROM event WHERE kind = ?");
const rows = q.all("login");
const one  = q.get("login");

const tx = db.transaction((rows) => { for (const r of rows) ins.run(r.kind, r.payload); });
tx(rows);
```

Differences worth checking when porting: `query()` caches prepared statements where
better-sqlite3 expects you to hold the statement yourself; `.run()`/`.exec()` semantics
differ; class-mapping (`.as(Class)`) is Bun-specific.

---

## Cloudflare D1

Managed, accessed over HTTP/RPC. No file, no `PRAGMA` surface, billed on rows read.

```js
export default {
  async fetch(request, env) {
    const { results, meta } = await env.DB
      .prepare("SELECT id, name FROM product WHERE org = ?")
      .bind("acme")
      .all();
    // meta.rows_read and meta.timings.sql_duration_ms are the numbers that matter
    return Response.json({ results, cost: meta.rows_read });
  },
};
```

| API | Use |
|---|---|
| `.all()` | All rows plus `meta` |
| `.first()` | First row, or a single column with `.first("col")` |
| `.run()` | Writes; returns `meta` only |
| `.raw()` | Arrays instead of objects — cheaper for wide results |
| `env.DB.batch([...])` | Multiple statements, one round trip, implicit transaction |

| Constraint | Detail |
|---|---|
| Bound parameters | Capped at **100 per statement** — chunk, don't inline literals |
| Connection pragmas | Not available; the platform owns journal mode, durability, timeouts |
| Introspection | `sqlite_version()` and `pragma_module_list` refused with `SQLITE_AUTH` |
| Interactive transactions | Not supported — use `batch()` |
| Billing | Rows read, not time |

**Deep dive:** [`d1-edge.md`](d1-edge.md).

---

## libSQL / Turso

A SQLite fork plus a hosted service. Three connection modes with very different profiles:

```js
import { createClient } from "@libsql/client";

// 1. Remote server
const remote = createClient({ url: "libsql://db.turso.io", authToken: TOKEN });

// 2. Local file (plain SQLite semantics)
const local = createClient({ url: "file:local.db" });

// 3. Embedded replica: local reads, remote writes, background sync
const replica = createClient({
  url: "file:replica.db",
  syncUrl: "libsql://db.turso.io",
  authToken: TOKEN,
});
await replica.sync();     // pull latest before a read that must be fresh
```

| Consideration | Note |
|---|---|
| Embedded replica staleness | A read right after a write may not see it — call `sync()` or use the client's read-your-writes support |
| Mode choice | Remote = simple, network-latency per query. Replica = fast reads, sync complexity. Pick deliberately |
| Extensions | libSQL adds features beyond stock SQLite (e.g. native vector types in recent versions) — verify against **your** server version; this moves |
| Portability | Keep SQL stock-SQLite unless you have a concrete reason not to |
| Billing | Reads-oriented, like D1 — the rows-read discipline transfers |

---

## Host comparison

| | Real file | `PRAGMA` control | Sync/async | Transactions | Billed on reads |
|---|---|---|---|---|---|
| `sqlite3` CLI | Yes | Full | Sync | Full | No |
| Python `sqlite3` | Yes | Full | Sync | Full | No |
| Python `aiosqlite` | Yes | Full | Async (thread-backed) | Full | No |
| `node:sqlite` | Yes | Full | Sync | Full | No |
| better-sqlite3 | Yes | Full | Sync | Full | No |
| `bun:sqlite` | Yes | Full | Sync | Full | No |
| Cloudflare D1 | No | None | Async | `batch()` only | **Yes** |
| libSQL / Turso | Depends on mode | Partial | Async | Full (server mode) | **Yes** |

### Choosing

| Situation | Host |
|---|---|
| Ad-hoc investigation, migrations, exports | `sqlite3` CLI |
| Python service, sync | stdlib `sqlite3` |
| Python service, async framework | `aiosqlite` (one write connection + a read pool) |
| Node, no native build allowed | `node:sqlite` |
| Node, maximum maturity and speed | better-sqlite3 |
| Bun runtime | `bun:sqlite` |
| Cloudflare Workers | D1 |
| Multi-region reads, embedded replicas | libSQL / Turso |
| Many concurrent writers, large dataset | **Not SQLite** — see `postgres-ops` |

---

## See also

- [`concurrency-durability.md`](concurrency-durability.md) — why the pragma baseline is what it is
- [`d1-edge.md`](d1-edge.md) — the managed-engine chapter in full
- [`async-patterns.md`](async-patterns.md) — Python async depth
- [`testing.md`](testing.md) — per-host test database setup

# SQLite Migrations

Changing a schema that already holds data. Engine-agnostic SQL, with runner examples per
host. For designing a schema in the first place see [`schema-design.md`](schema-design.md).

## Contents

- [What ALTER TABLE can and cannot do](#what-alter-table-can-and-cannot-do)
- [The 12-step recreate procedure](#the-12-step-recreate-procedure)
- [Versioned migrations with user_version](#versioned-migrations-with-user_version)
- [A named-migration runner](#a-named-migration-runner)
- [Runners per host](#runners-per-host)
- [Migrating large tables](#migrating-large-tables)
- [Rollback strategy](#rollback-strategy)
- [Migration review checklist](#migration-review-checklist)

---

## What ALTER TABLE can and cannot do

| Operation | Supported | Since |
|---|---|---|
| `ADD COLUMN` | Yes | Always |
| `RENAME TO` (table) | Yes | Always |
| `RENAME COLUMN` | Yes | 3.25 |
| `DROP COLUMN` | Yes, **with conditions** | 3.35 |
| Change a column type | **No** | — |
| Add/remove `NOT NULL`, `CHECK`, `DEFAULT` | **No** | — |
| Add/remove a foreign key | **No** | — |
| Reorder columns | **No** | — |
| Add a `PRIMARY KEY` / `UNIQUE` | **No** (`CREATE UNIQUE INDEX` is the workaround) | — |

```sql
ALTER TABLE product ADD COLUMN status TEXT NOT NULL DEFAULT 'active';
ALTER TABLE product RENAME COLUMN sku TO product_code;
ALTER TABLE product DROP COLUMN legacy_flag;
```

**`ADD COLUMN` constraints:** a `NOT NULL` column must have a non-null `DEFAULT` (existing
rows need a value), the default cannot be from the `CURRENT_TIME` family, and it cannot be
`PRIMARY KEY` or `UNIQUE`. Adding a column is O(1) — SQLite records it in the schema and
materialises the default on read.

**`DROP COLUMN` refuses** when the column is a primary key, has a `UNIQUE` constraint, or is
referenced by an index, view, trigger, `CHECK` constraint, or generated column. Drop the
dependent object first, or use the recreate procedure. Unlike `ADD`, it rewrites every row.

Everything else needs the recreate procedure below.

---

## The 12-step recreate procedure

The official sequence for any change `ALTER TABLE` cannot express. **Order matters** — each
step prevents a specific failure.

```sql
-- 1. If foreign keys are enabled, note that and turn them OFF.
--    Must be OUTSIDE a transaction: PRAGMA foreign_keys is a no-op inside one.
PRAGMA foreign_keys = OFF;

-- 2. Start a transaction.
BEGIN IMMEDIATE;

-- 3. Record every index, trigger and view attached to the table - you will recreate them:
--    SELECT type, name, sql FROM sqlite_master WHERE tbl_name = 'product';

-- 4. Create the new table under a temporary name, with the desired shape.
CREATE TABLE product_new (
    id         INTEGER PRIMARY KEY,
    org        TEXT NOT NULL,
    price      INTEGER NOT NULL,                   -- changed: REAL -> INTEGER (minor units)
    status     TEXT NOT NULL DEFAULT 'active'
                   CHECK (status IN ('active','archived')),  -- added constraint
    created_at TEXT NOT NULL
    -- dropped: legacy_flag
) STRICT;

-- 5. Copy the data, transforming as needed.
INSERT INTO product_new (id, org, price, status, created_at)
SELECT id, org, CAST(round(price * 100) AS INTEGER),
       coalesce(status, 'active'), created_at
FROM product;

-- 6. Drop the old table.
DROP TABLE product;

-- 7. Rename the new table into place.
ALTER TABLE product_new RENAME TO product;

-- 8. Recreate the indexes and triggers recorded in step 3.
CREATE INDEX product_org ON product(org);

-- 9. Recreate any VIEW that referenced the old table shape.

-- 10. If foreign keys were on, verify nothing broke - while you can still ROLLBACK.
PRAGMA foreign_key_check;

-- 11. Commit.
COMMIT;

-- 12. Restore the foreign-key setting (outside the transaction).
PRAGMA foreign_keys = ON;
```

### Why each guard is there

| Step | Guards against |
|---|---|
| FK off during the rebuild (1, 12) | `DROP TABLE` firing `ON DELETE CASCADE` and **deleting child rows** — the destructive failure this procedure exists to prevent |
| FK toggled outside the transaction | `PRAGMA foreign_keys` is silently ignored inside a transaction |
| Recording indexes/triggers first (3) | `DROP TABLE` takes them with it; they are gone before you notice |
| `foreign_key_check` before `COMMIT` (10) | Catching orphans while rollback is still possible |
| `BEGIN IMMEDIATE` (2) | A mid-migration upgrade deadlock — see [`concurrency-durability.md`](concurrency-durability.md) |

**Legacy ordering to avoid:** the shorter "rename old, create new, copy, drop old" sequence
(`ALTER TABLE t RENAME TO t_old` first) interacts badly with `legacy_alter_table` settings —
references from other objects get rewritten to point at `t_old`. Use the order above.

---

## Versioned migrations with user_version

SQLite reserves a 32-bit integer in the file header for exactly this. It costs no table and
cannot drift from the file it describes.

```sql
PRAGMA user_version;        -- read (0 on a fresh database)
PRAGMA user_version = 3;    -- write (cannot be parameterised - interpolate an integer)
```

```sql
-- migrations/001_initial.sql
BEGIN IMMEDIATE;
CREATE TABLE product (
    id  INTEGER PRIMARY KEY,
    org TEXT NOT NULL,
    sku TEXT NOT NULL UNIQUE
) STRICT;
CREATE INDEX product_org ON product(org);
PRAGMA user_version = 1;
COMMIT;
```

```bash
sqlite3 app.db < migrations/001_initial.sql
sqlite3 app.db 'PRAGMA user_version;'    # -> 1
```

| Approach | Pros | Cons |
|---|---|---|
| `PRAGMA user_version` | No extra table; atomic with the schema; trivially readable | Integer only — no names, timestamps, or audit trail |
| A `schema_migrations` table | Names, applied-at timestamps, per-migration audit | An extra table; created by migration zero |

Use `user_version` for embedded/single-app databases; use a table when several people or
services need to see what ran and when.

---

## A named-migration runner

```sql
CREATE TABLE IF NOT EXISTS schema_migrations (
    name       TEXT PRIMARY KEY,
    applied_at TEXT NOT NULL DEFAULT (datetime('now')),
    checksum   TEXT
) STRICT;
```

Recording a **checksum** catches the nastiest migration bug: someone editing a migration
that has already run somewhere, so environments silently diverge. Compare on startup and
refuse to proceed on a mismatch.

```python
import hashlib, pathlib, sqlite3

def migrate(conn: sqlite3.Connection, directory: str) -> list[str]:
    """Apply pending .sql migrations in filename order. Returns names applied."""
    conn.execute("""
        CREATE TABLE IF NOT EXISTS schema_migrations (
            name TEXT PRIMARY KEY,
            applied_at TEXT NOT NULL DEFAULT (datetime('now')),
            checksum TEXT
        ) STRICT
    """)
    applied = {r[0]: r[1] for r in
               conn.execute("SELECT name, checksum FROM schema_migrations")}

    done = []
    for path in sorted(pathlib.Path(directory).glob("*.sql")):
        sql = path.read_text(encoding="utf-8")
        checksum = hashlib.sha256(sql.encode()).hexdigest()

        if path.name in applied:
            if applied[path.name] != checksum:
                raise RuntimeError(
                    f"{path.name} changed after it was applied "
                    f"(recorded {applied[path.name][:12]}, now {checksum[:12]}). "
                    "Add a new migration instead of editing an applied one.")
            continue

        # Each file supplies its own BEGIN/COMMIT so a failure rolls that file back cleanly.
        conn.executescript(sql)
        conn.execute("INSERT INTO schema_migrations (name, checksum) VALUES (?, ?)",
                     (path.name, checksum))
        done.append(path.name)
    return done
```

**Never edit an applied migration.** Add a new one. The checksum turns that convention into
an enforced invariant.

---

## Runners per host

```js
// better-sqlite3 / node:sqlite - synchronous, so the loop reads naturally
import Database from "better-sqlite3";
import { readFileSync, readdirSync } from "node:fs";

const db = new Database("app.db");
db.pragma("foreign_keys = ON");
db.exec(`CREATE TABLE IF NOT EXISTS schema_migrations (
           name TEXT PRIMARY KEY,
           applied_at TEXT NOT NULL DEFAULT (datetime('now'))) STRICT`);

const applied = new Set(db.prepare("SELECT name FROM schema_migrations").pluck().all());
const record  = db.prepare("INSERT INTO schema_migrations (name) VALUES (?)");

for (const file of readdirSync("migrations").filter(f => f.endsWith(".sql")).sort()) {
  if (applied.has(file)) continue;
  db.exec(readFileSync(`migrations/${file}`, "utf8"));   // file supplies BEGIN/COMMIT
  record.run(file);
}
```

```bash
# Cloudflare D1 - migrations are a first-class wrangler feature
wrangler d1 migrations create atdw-mirror add_covering_index
wrangler d1 migrations list   atdw-mirror --remote
wrangler d1 migrations apply  atdw-mirror --local     # test locally first
wrangler d1 migrations apply  atdw-mirror --remote    # MAINTAINER-GATED: this is a deploy
```

D1 tracks applied migrations in its own table and applies files in name order. Three
differences from a local database: statements are subject to platform limits (100 KB per
statement, 100 bound parameters — see [`d1-edge.md`](d1-edge.md)); a remote apply is a
**production deploy**, so a working session writes the migration file and stops; and
[Time Travel](d1-edge.md#time-travel) gives you a 30-day undo that local SQLite does not,
so take a bookmark before applying.

One more remote-apply trap: `--remote` can **report a timeout while the migration actually
landed**, and blindly re-running is the mistake — verify the schema state read-only first
([`d1-production-patterns.md`](d1-production-patterns.md#migration-apply-can-time-out-yet-still-land)).

```python
# aiosqlite - same logic, awaited. Run migrations at startup, before serving.
async def migrate(conn, directory: str) -> None:
    await conn.execute("""CREATE TABLE IF NOT EXISTS schema_migrations (
                            name TEXT PRIMARY KEY,
                            applied_at TEXT NOT NULL DEFAULT (datetime('now'))) STRICT""")
    cur = await conn.execute("SELECT name FROM schema_migrations")
    applied = {r[0] for r in await cur.fetchall()}
    for path in sorted(pathlib.Path(directory).glob("*.sql")):
        if path.name in applied:
            continue
        await conn.executescript(path.read_text(encoding="utf-8"))
        await conn.execute("INSERT INTO schema_migrations (name) VALUES (?)", (path.name,))
        await conn.commit()
```

---

## Migrating large tables

The recreate procedure holds a write lock throughout and needs room for a second copy of the
table. On a large table that is real downtime.

| Technique | Detail |
|---|---|
| Prefer `ADD COLUMN` | O(1) and lock-free — model changes as additive whenever possible |
| Backfill in batches | New nullable column → backfill in ~10k-row chunks in separate transactions → add the constraint later via a recreate |
| Expand/contract | Add the new column, dual-write from the app, backfill, switch reads, drop the old column in a later release |
| Measure first | `SELECT count(*)`, and confirm free disk ≥ 2x the table size |
| Schedule it | A recreate on a multi-GB table is minutes, not seconds |

```sql
-- Batched backfill: bounded transactions, resumable, no long lock
UPDATE product SET price_cents = CAST(round(price * 100) AS INTEGER)
WHERE price_cents IS NULL
  AND id IN (SELECT id FROM product WHERE price_cents IS NULL LIMIT 10000);
-- repeat until 0 rows changed
```

On D1 this pattern is mandatory rather than optional: a statement that scans too much is
killed by the 30-second query-duration limit or the isolate's CPU/memory limits.

---

## Rollback strategy

Down-migrations are frequently more dangerous than the change they undo — a `DROP COLUMN`
rollback destroys everything written since the migration ran.

| Situation | Preferred response |
|---|---|
| Additive change (new column/table/index) | Roll **forward** — old code ignores the addition |
| Destructive change | **Restore from backup** (or D1 Time Travel); snapshot immediately before |
| Constraint tightened, data now violates it | Roll forward with a repair migration |
| Genuine need to reverse | Write an explicit down-migration and test it against a copy of production |

```bash
# The rollback plan that always works: snapshot first
sqlite3 app.db "VACUUM INTO '/backup/pre-migration-$(date +%F-%H%M).db'"
sqlite3 app.db < migrations/007_recreate_product.sql

# On D1, capture a restore point instead
wrangler d1 time-travel info atdw-mirror     # record the bookmark BEFORE applying
```

Design migrations to be **backwards-compatible for one release**: deploy the schema change
first, the code that depends on it second. Then a code rollback never needs a schema
rollback.

---

## Migration review checklist

- [ ] Wrapped in `BEGIN IMMEDIATE` … `COMMIT`
- [ ] `PRAGMA foreign_keys = OFF` around a table recreate — **outside** the transaction
- [ ] Every index, trigger, and view on a recreated table is recreated afterwards
- [ ] `PRAGMA foreign_key_check` before `COMMIT`
- [ ] Version recorded (`user_version` or a `schema_migrations` row) in the same transaction
- [ ] Tested against a **copy of production data**, not just an empty schema
- [ ] Backup (or D1 bookmark) captured immediately before a destructive change
- [ ] Free disk ≥ 2x the table size for a recreate
- [ ] `ANALYZE` after a change that alters data distribution or adds an index
- [ ] Query plans for affected statements re-checked afterwards
- [ ] No edits to an already-applied migration file
- [ ] For D1: the remote apply is left to the maintainer, not run from a working session

---

## See also

- [`schema-design.md`](schema-design.md) — designing the shape you are migrating to
- [`schema-patterns.md`](schema-patterns.md) — ready-made table designs
- [`testing.md`](testing.md) — idempotency, data-preservation, and integrity tests
- [`operations.md`](operations.md) — backups before destructive changes
- [`d1-edge.md`](d1-edge.md) — wrangler migrations, Time Travel, and the deploy gate

# SQLite Operations

Integrity, corruption, backups, VACUUM, and size/page tuning. Applies to any host that owns
a real database file. Managed engines (D1, Turso primaries) handle most of this for you —
see [`d1-edge.md`](d1-edge.md).

## Contents

- [Integrity checks](#integrity-checks)
- [What actually corrupts a SQLite database](#what-actually-corrupts-a-sqlite-database)
- [Recovering a corrupted database](#recovering-a-corrupted-database)
- [Backups](#backups)
- [VACUUM vs VACUUM INTO](#vacuum-vs-vacuum-into)
- [Where the space went](#where-the-space-went)
- [Page size, cache, and mmap](#page-size-cache-and-mmap)
- [Bulk-load tuning](#bulk-load-tuning)
- [Routine maintenance](#routine-maintenance)

---

## Integrity checks

```sql
PRAGMA quick_check;        -- structural checks only; fast
PRAGMA integrity_check;    -- full: pages, indexes, constraints. Slow on large files
PRAGMA integrity_check(10);-- stop after 10 errors
PRAGMA foreign_key_check;  -- orphaned rows (independent of the foreign_keys pragma)
```

Both return the single row `ok` when clean. `quick_check` skips the index-content
verification, so it misses a corrupt index whose pages are structurally valid — run the full
check when you actually suspect damage, and `quick_check` as a routine heartbeat.

`integrity_check` reads every page. On a multi-GB database that is minutes of I/O and it
holds a read transaction throughout, which (in WAL) pins the checkpoint — schedule it, don't
run it casually against a busy production database.

---

## What actually corrupts a SQLite database

SQLite is extremely hard to corrupt through the API. Nearly every real case is external:

| Cause | Detail |
|---|---|
| **Networked filesystems** | NFS/SMB/CIFS advisory locking is unreliable. The single most common cause |
| **Copying a live database** | `cp`/`rsync` of a file with an active writer produces a torn copy |
| **Deleting `-wal` / `-shm` by hand** | Removing them while a connection is open discards committed data |
| **`PRAGMA synchronous = OFF`** | Power loss mid-write can leave the file inconsistent |
| Two processes with different locking assumptions | e.g. a WSL process and a Windows process on the same file |
| Hardware / filesystem failure | Bad sectors, a lying fsync in a virtualised disk stack |
| Killing a process with `SIGKILL` mid-write | Safe at `synchronous=NORMAL` or higher — SQLite recovers. Only unsafe with `synchronous=OFF` |

Application crashes and normal process kills are **not** on the corruption list — SQLite's
journal/WAL recovery handles them. Note the file's own defence: `PRAGMA integrity_check`
verifies structure, not semantics, so it will not detect application-level data errors.

---

## Recovering a corrupted database

Work on a **copy**. Never attempt recovery on the only artefact you have.

```bash
cp app.db app.db.broken          # after stopping every writer

# 1. Confirm and characterise
sqlite3 app.db.broken 'PRAGMA integrity_check;'

# 2. The .recover command — reconstructs from whatever pages are readable.
#    Strictly better than .dump for damaged files: it walks the b-trees directly
#    and salvages orphaned pages instead of aborting at the first bad read.
sqlite3 app.db.broken '.recover' > recovered.sql
sqlite3 app_new.db < recovered.sql
sqlite3 app_new.db 'PRAGMA integrity_check;'

# 3. If .recover is unavailable, .dump gets what it can
sqlite3 app.db.broken '.dump' > dump.sql
```

| Symptom | Likely meaning |
|---|---|
| `database disk image is malformed` | Real page-level corruption |
| `file is not a database` | Wrong file, truncated header, or an encrypted database opened without its key |
| `database is locked` on every attempt | Not corruption — a stale lock or another process |
| Missing rows, intact structure | Application bug or an interrupted write, not corruption |

After recovery, `.recover` output usually needs indexes and triggers re-created and should
be diffed against the schema you expect. Compare row counts per table against your last
known-good backup before declaring success.

---

## Backups

| Method | Consistent under writers | Output | Best for |
|---|---|---|---|
| `VACUUM INTO 'f.db'` | **Yes** | Compact database file | The default answer |
| Backup API (driver-level) | **Yes** | Database file | Programmatic/incremental backups |
| `.dump` | Yes (single read txn) | SQL text | Portability, archival, cross-version moves |
| Continuous WAL replication (Litestream-style) | Yes | Object storage | Point-in-time recovery |
| `cp` / `rsync` | **No** | Corrupt copy | Never, unless all writers are stopped |

```bash
# Online, consistent, defragmented — no downtime
sqlite3 app.db "VACUUM INTO '/backup/app-$(date +%F).db'"

# Portable text backup
sqlite3 app.db '.dump' | gzip > /backup/app.sql.gz

# Verify the backup before trusting it. An unverified backup is a rumour.
sqlite3 /backup/app-2026-08-04.db 'PRAGMA integrity_check;'
sqlite3 /backup/app-2026-08-04.db 'SELECT count(*) FROM product;'
```

```python
# Backup API: consistent, incremental, works while the source is being written
import sqlite3
src = sqlite3.connect("app.db")
dst = sqlite3.connect("/backup/app.db")
with dst:
    src.backup(dst, pages=1000, sleep=0.05)   # yields between page batches
dst.close(); src.close()
```

**Restore drills matter more than backup scripts.** Schedule a periodic restore-and-verify;
a backup nobody has restored is an untested assumption.

---

## VACUUM vs VACUUM INTO

```sql
VACUUM;                          -- rebuild this database in place
VACUUM INTO 'copy.db';           -- write a fresh, compact copy elsewhere
```

| | `VACUUM` | `VACUUM INTO` |
|---|---|---|
| Locks the database | **Yes**, exclusive, for the whole operation | No — a read transaction only |
| Disk needed | Up to **2x** the database size, plus temp | Size of the output |
| Result | Same file, defragmented, free pages released | New compact file; original untouched |
| Safe on a live system | No | Yes |

**`VACUUM` is not a performance tool.** It defragments and reclaims free pages; it does not
fix a missing index, and running it "to speed things up" is a common misdiagnosis. Reach for
it when the file has genuinely bloated after large deletions.

`auto_vacuum` handles reclamation incrementally instead:

```sql
PRAGMA auto_vacuum;               -- 0 NONE (default) | 1 FULL | 2 INCREMENTAL
PRAGMA auto_vacuum = INCREMENTAL; -- must be set before tables exist, or followed by VACUUM
PRAGMA incremental_vacuum(1000);  -- release up to 1000 free pages, cheaply
```

`INCREMENTAL` + a periodic `incremental_vacuum` is the low-impact choice for a
delete-heavy database. `FULL` reclaims on every commit and costs write throughput.

---

## Where the space went

```sql
-- Overall geometry
PRAGMA page_count;    -- pages in the file
PRAGMA page_size;     -- bytes per page  (file size ≈ page_count * page_size)
PRAGMA freelist_count;-- unused pages — a large number means VACUUM would reclaim

-- Per-object breakdown (needs the dbstat virtual table; present in most builds)
SELECT name, SUM(pgsize) AS bytes, SUM(pgsize)/1024/1024 AS mb
FROM dbstat GROUP BY name ORDER BY bytes DESC LIMIT 20;

-- Rows per table
SELECT name, (SELECT count(*) FROM sqlite_master) AS _ FROM sqlite_master WHERE type='table';
```

`dbstat` distinguishes tables from indexes by name, which is how you discover that an index
you added is larger than the table it indexes — a common outcome with wide covering indexes
and trigram FTS. The CLI's `.dbinfo` gives the header summary in one shot.

---

## Page size, cache, and mmap

```sql
PRAGMA page_size;              -- default 4096; only changeable before the first table, or via VACUUM
PRAGMA cache_size = -64000;    -- NEGATIVE means KiB → 64 MB. Positive means a page count
PRAGMA mmap_size = 268435456;  -- 256 MB memory-mapped I/O
PRAGMA temp_store = MEMORY;    -- keep temp B-trees in RAM
```

| Setting | Guidance |
|---|---|
| `page_size` | 4096 suits most workloads. 8192/16384 can help large sequential scans and large rows. Changing it requires `PRAGMA page_size = N; VACUUM;` |
| `cache_size` | The highest-leverage knob. Always express as negative KiB — a positive value is a *page count* and silently means something different if you later change `page_size` |
| `mmap_size` | Can cut read syscalls substantially. Avoid on network filesystems; a corrupt page becomes a segfault rather than an error |
| `temp_store` | `MEMORY` avoids disk for sorts and temp B-trees — worth setting when EQP shows temp B-trees you can't design away |

Measure rather than cargo-cult: on a database that fits in the OS page cache, none of these
will move the needle, and index design will move it 25x.

---

## Bulk-load tuning

For an import into a database nobody else is using, temporarily trading durability for speed
is legitimate — **on a database you can rebuild**.

```sql
PRAGMA journal_mode = OFF;      -- no rollback journal (UNSAFE: crash = corrupt)
PRAGMA synchronous = OFF;       -- no fsync            (UNSAFE)
PRAGMA temp_store = MEMORY;
PRAGMA cache_size = -256000;    -- 256 MB

BEGIN;
-- ... millions of INSERTs, one transaction ...
COMMIT;

-- Restore safe settings, then build indexes AFTER the data is in
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
CREATE INDEX ...;
ANALYZE;
```

| Technique | Effect |
|---|---|
| One transaction around the whole load | The single biggest win — one fsync instead of N |
| Create indexes **after** loading | Bulk index build beats N incremental updates |
| `.import --csv` in the CLI | Fastest path for CSV; skips per-row round trips |
| Prepared statement + `executemany`/batch | Avoids re-parsing the statement per row |
| `ANALYZE` after loading | The planner has no statistics for freshly loaded data |

Never leave `journal_mode = OFF` or `synchronous = OFF` set on a production database.

---

## Routine maintenance

| Cadence | Task |
|---|---|
| On connection close (long-lived apps) | `PRAGMA optimize` — cheap, targeted `ANALYZE` |
| Daily | `VACUUM INTO` backup + `integrity_check` on the **backup** (not the live file) |
| Weekly | `PRAGMA quick_check` on the live database |
| After bulk changes | `ANALYZE`; FTS5 `'optimize'`; re-read plans for affected statements |
| After large deletions | `incremental_vacuum`, or a scheduled `VACUUM` during a maintenance window |
| Monthly | Restore drill: restore the backup somewhere and verify row counts |
| On schema change | Re-run `EXPLAIN QUERY PLAN` on the statements the change was meant to fix |

```bash
#!/usr/bin/env bash
# Nightly: consistent backup, then verify the BACKUP rather than locking production.
set -uo pipefail
DB=/var/lib/app/app.db
OUT=/backup/app-$(date +%F).db
sqlite3 "$DB" "VACUUM INTO '$OUT'"          || exit 1
result=$(sqlite3 "$OUT" 'PRAGMA integrity_check;')
[ "$result" = "ok" ] || { echo "backup failed integrity_check: $result" >&2; exit 1; }
find /backup -name 'app-*.db' -mtime +14 -delete
```

---

## See also

- [`concurrency-durability.md`](concurrency-durability.md) — WAL checkpointing and `synchronous`
- [`query-performance.md`](query-performance.md) — `ANALYZE`, `PRAGMA optimize`, index sizing
- [`migration-patterns.md`](migration-patterns.md) — schema change procedure
- [`testing.md`](testing.md) — verifying a restore in a test harness

# SQLite Query Performance

Engine-agnostic. Everything here applies to any SQLite 3.x host — CLI, Python, Node, Bun,
Cloudflare D1, libSQL/Turso — because the query planner is the same code everywhere. Host
differences are about *how you observe* the cost, not what the cost is; see
[`d1-edge.md`](d1-edge.md) and [`hosts.md`](hosts.md) for those.

## Contents

- [The measurement contract](#the-measurement-contract)
- [Reading EXPLAIN QUERY PLAN](#reading-explain-query-plan)
- [Covering indexes](#covering-indexes)
- [Predicates that cannot be seeked](#predicates-that-cannot-be-seeked)
- [LIKE and GLOB optimisation rules](#like-and-glob-optimisation-rules)
- [Index design and column order](#index-design-and-column-order)
- [Partial and expression indexes](#partial-and-expression-indexes)
- [ANALYZE and sqlite_stat1](#analyze-and-sqlite_stat1)
- [Query planner defeats](#query-planner-defeats)
- [Joins and subqueries](#joins-and-subqueries)
- [Pagination](#pagination)
- [The read-only proof technique](#the-read-only-proof-technique)
- [A worked optimisation, end to end](#a-worked-optimisation-end-to-end)
- [Triage checklist](#triage-checklist)

---

## The measurement contract

Before changing anything, be able to state four numbers for the statement you are about to
optimise: **median latency, range across runs, rows read, and plan shape.** If you can't,
you are guessing.

| Rule | Why | How |
|---|---|---|
| Measure the **statement**, not the request/tool call | A statement piggy-backing on a batch someone else was already sending adds no round trip, so it never appears in per-call timing — while still scanning the table on every request | Decompose multi-part statements; time each part alone |
| Use engine-reported duration | Process/driver startup dominates a CLI stopwatch (a `npx`-shaped launch is ~2 s before any SQL runs) | `.timer on` (CLI), `sql_duration_ms` (D1), driver-level instrumentation |
| Median of 10+, report the range | First runs are cold: a 1.5–1.7x first-run penalty on multi-thousand-row reads is routine, and outliers an order of magnitude above the median happen | Loop the statement, sort, take the middle |
| Report latency **and** rows scanned | They move independently: an optimisation can cut latency ~25x with rows-read unchanged, and a different one can collapse rows-read ~58,000x | Pair `sql_duration_ms`-style timing with a rows-read counter |

**Why rows-read matters even when latency doesn't change.** Rows read is a proxy for work
done and, on managed engines, is literally the billing unit. Two optimisations with
identical latency wins can have completely different cost profiles. Always report both, and
never let one stand in for the other.

### Getting the numbers per host

```bash
# sqlite3 CLI — engine timing plus a scan counter
sqlite3 app.db '.timer on' 'SELECT ...;'
sqlite3 app.db '.stats on' 'SELECT ...;'   # includes fullscan steps / sort counts
```

```python
# Python — sqlite3 exposes VM steps, a good proxy for work done
import sqlite3, time, statistics
conn = sqlite3.connect("app.db")
times = []
for _ in range(12):
    t0 = time.perf_counter()
    conn.execute(SQL).fetchall()
    times.append((time.perf_counter() - t0) * 1000)
print(f"median {statistics.median(times):.2f} ms  range {min(times):.2f}-{max(times):.2f}")
```

For Cloudflare D1's `meta.timings.sql_duration_ms` / `meta.rows_read`, see
[`d1-edge.md`](d1-edge.md) — those are server-side and are the only trustworthy numbers there.

---

## Reading EXPLAIN QUERY PLAN

EQP is read-only, instant, and safe on production. Run it first, every time.

```sql
EXPLAIN QUERY PLAN SELECT ...;
```

| Line | What the engine is doing | Read as |
|---|---|---|
| `SEARCH t USING INDEX ix (col=?)` | Seek into a B-tree, then fetch each matching row from the table | Good |
| `SEARCH t USING COVERING INDEX ix (col=?)` | Seek, and answer entirely from the index — the table is never touched | Best |
| `SEARCH t USING INTEGER PRIMARY KEY (rowid=?)` | Direct rowid lookup | Best |
| `SCAN t USING COVERING INDEX ix` | Every entry of a narrow index is read; the table is never touched | Acceptable, often the right answer for unseekable predicates |
| `SCAN t USING INDEX ix` | Every index entry read **and** a table row fetched per hit — usually strictly worse than a plain scan | Suspicious |
| `SCAN t` | Full table scan: every row, all columns' pages | Fix unless the table is tiny |
| `USE TEMP B-TREE FOR ORDER BY` | No index supplies the requested order; rows are buffered and sorted | Cost signal |
| `USE TEMP B-TREE FOR GROUP BY` | Same for grouping | Cost signal |
| `USE TEMP B-TREE FOR DISTINCT` | Same for de-duplication | Cost signal |
| `CORRELATED SCALAR SUBQUERY` | Subquery re-runs once per outer row | Usually dominates |
| `MULTI-INDEX OR` | An `OR` split into multiple index lookups then merged | Fine; better than the scan it replaced |
| `BLOOM FILTER ON t` | Join pre-filter (3.38+) | Informational |
| `MATERIALIZE subquery` | A subquery/CTE result is written to a temp table then read | Watch for repeated materialisation |

### The three questions to ask of any plan

1. **Is there a `SCAN` on a large table?** If yes, is it covering? A non-covering scan on a
   wide table is the single most common cause of slow SQLite.
2. **Is there a temp B-tree?** That is a sort or grouping the schema could have supplied.
   Note it, but do not fix it first — an index added for the scan often removes it for free.
3. **Does the plan change after `ANALYZE`?** If yes, your production behaviour depends on
   whether stats exist. See [ANALYZE and sqlite_stat1](#analyze-and-sqlite_stat1).

### EXPLAIN vs EXPLAIN QUERY PLAN

`EXPLAIN` (without `QUERY PLAN`) dumps the VDBE bytecode — hundreds of opcodes. It is
occasionally useful for confirming *which* index a statement opened, or spotting a hidden
`OpenEphemeral` (a temp table), but EQP answers 95% of questions. Reach for `EXPLAIN` only
after EQP has left you genuinely puzzled.

---

## Covering indexes

An index **covers** a statement when every column the statement needs — in the `WHERE`, the
`SELECT` list, the `ORDER BY`, the `GROUP BY` — is present in the index itself. The engine
then answers from the index and never reads the table.

This is the highest-value optimisation in SQLite specifically because SQLite stores rows
contiguously: a 73-column row costs the same page reads whether you asked for one column or
all of them. A covering index turns "read 58k wide rows" into "read 58k narrow entries".

```sql
-- Statement: filter on org, project product_id
SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';

-- Covering index — FILTERED column first, PROJECTED column second
CREATE INDEX q_product_org_product ON q_product(org, product_id);
```

**Column order is load-bearing**, even when the leading column can't be seeked:

- The leading column is what the planner matches against the `WHERE` clause when deciding
  the index is relevant at all.
- Trailing columns exist to make the index *cover*, not to be searched.
- Reversing them (`(product_id, org)`) gives you an index the planner is far less likely to
  choose for this statement.

### When a covering index is the right answer

| Situation | Covering index? |
|---|---|
| Predicate is unseekable (`LIKE '%x%'`, function on column) but the table is wide | **Yes** — this is the classic win |
| Predicate is seekable and selective | Usually unnecessary — a plain index already avoids most row reads |
| Statement projects many columns | No — the index would be as wide as the table |
| Table is narrow (a few small columns) | No — a table scan already reads narrow pages |

### Verify it worked

```sql
EXPLAIN QUERY PLAN SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';
-- want: SCAN q_product USING COVERING INDEX q_product_org_product
-- not:  SCAN q_product USING INDEX q_product_org
```

The word **COVERING** is the whole test. If it is missing, some column the statement needs
isn't in the index — commonly because someone wrote `SELECT *`.

---

## Predicates that cannot be seeked

A B-tree can only seek when the predicate constrains a **prefix of the indexed value**.
Anything that transforms the column first destroys that property.

| Predicate | Seekable? | Remedy |
|---|---|---|
| `col = ?` / `col IN (…)` / `col > ?` / `BETWEEN` | Yes | Plain index |
| `col LIKE 'abc%'` | Yes, conditionally (see below) | Plain index |
| `col LIKE '%abc%'` or `'%abc'` | **No** | Covering index, or FTS5 trigram |
| `lower(col) = ?`, `date(col) = ?`, `substr(col,1,3) = ?` | **No** | Expression index on the same expression |
| `col + 0 = ?`, `CAST(col AS TEXT) = ?` | **No** | Fix the type, or expression index |
| `col LIKE ?` (parameterised pattern) | **No** at prepare time | The planner can't see the pattern; treat as unseekable |
| `a = ? OR b = ?` | Sometimes (`MULTI-INDEX OR`) | Index both columns; or rewrite as `UNION` |
| `col != ?`, `NOT IN` | Effectively no | Rethink the predicate |
| `col IS NULL` | Yes | Plain index (NULLs are indexed in SQLite) |
| `json_extract(doc,'$.k') = ?` | **No** | Expression index or generated column + index |

**Type mismatch is a silent killer.** Because of type affinity, comparing a TEXT column to
an integer parameter can prevent index use *and* silently return nothing. `WHERE id = '42'`
against an `INTEGER` column and `WHERE code = 42` against a `TEXT` column both behave
surprisingly — see [`schema-design.md`](schema-design.md).

---

## LIKE and GLOB optimisation rules

SQLite converts `LIKE` into a range constraint (`col >= 'abc' AND col < 'abd'`) only when
**all** of these hold:

1. The pattern is a **string literal or a bound parameter whose value is known**, and it
   does not start with a wildcard (`%` or `_`).
2. The column has **TEXT affinity** and the default **`BINARY`** collation — unless
   `PRAGMA case_sensitive_like = ON`, in which case `BINARY` is required, or the column
   uses `COLLATE NOCASE`, which enables the optimisation for case-insensitive `LIKE`.
3. The `ESCAPE` clause is not used.
4. The right-hand side is not a column reference.

`GLOB` follows the same rule with `*`/`?` wildcards and is always case-sensitive, so it
optimises under `BINARY` collation without the `case_sensitive_like` dance.

```sql
-- Optimisable: anchored prefix
SELECT * FROM city WHERE name LIKE 'Syd%';         -- SEARCH ... USING INDEX

-- Not optimisable: leading wildcard
SELECT * FROM city WHERE name LIKE '%ney';         -- SCAN

-- Case-insensitive, still optimisable if the column is COLLATE NOCASE
CREATE TABLE city (name TEXT COLLATE NOCASE);
CREATE INDEX city_name ON city(name);
SELECT * FROM city WHERE name LIKE 'syd%';          -- SEARCH ... USING INDEX
```

**For genuine substring search, stop fighting `LIKE`.** FTS5 with the `trigram` tokenizer
indexes 3-character sequences and makes `LIKE '%abc%'` and `MATCH` both fast — see
[`feature-modules.md`](feature-modules.md). The covering-index trick makes an unseekable
scan cheaper; trigram FTS makes it disappear.

---

## Index design and column order

### The ordering rule

For a composite index `(a, b, c)`, the planner can use a prefix — `(a)`, `(a, b)`,
`(a, b, c)` — never a suffix. Order columns:

1. **Equality predicates first** (`WHERE a = ?`)
2. **Then one range or sort column** (`WHERE b > ?` or `ORDER BY b`)
3. **Then columns needed only for coverage** (projected, never filtered)

```sql
-- Statement: WHERE org = ? AND created_at > ? ORDER BY created_at, projecting title
CREATE INDEX ev_org_created ON events(org, created_at, title);
--                                    ^equality  ^range/sort   ^coverage only
```

A range column stops the usefulness of everything after it for *seeking* — but those
trailing columns still count for *coverage*, which is exactly why coverage columns go last.

### Sizing and maintenance

| Consideration | Guidance |
|---|---|
| Write cost | Every index is updated on every INSERT/UPDATE/DELETE touching its columns |
| Redundant indexes | `(a)` is redundant if `(a, b)` exists — drop the shorter one |
| Index size | Check with `dbstat` (if compiled in) or by summing column widths; a covering index over wide TEXT columns can rival the table |
| Naming | `<table>_<col1>_<col2>` — readable in EQP output, which is where you will see it |
| `UNIQUE` | Enforces a constraint *and* provides an index — don't add both |

### DESC and ORDER BY

SQLite can scan an index backwards, so `CREATE INDEX ix ON t(a)` serves both
`ORDER BY a` and `ORDER BY a DESC`. A `DESC` index is only needed for **mixed** orders:

```sql
-- Needs an explicitly mixed index; a plain (a, b) index cannot supply this order
SELECT * FROM t ORDER BY a ASC, b DESC;
CREATE INDEX t_a_bdesc ON t(a ASC, b DESC);
```

---

## Partial and expression indexes

### Partial indexes

Index only the rows you actually query. Smaller index, cheaper writes for the excluded rows.

```sql
-- Only pending jobs are ever fetched by this predicate
CREATE INDEX job_pending ON job_queue(priority DESC, created_at)
    WHERE status = 'pending';

-- Only non-NULL values matter
CREATE INDEX product_sku ON product(sku) WHERE sku IS NOT NULL;
```

**The catch:** the planner uses a partial index only when it can *prove* the statement's
`WHERE` clause implies the index's `WHERE` clause. `WHERE status = 'pending'` matches;
`WHERE status = :status` does **not**, because the value isn't known at prepare time. Write
the literal, or keep a full index.

### Expression indexes

When you can't stop the code calling a function on the column, index the function.

```sql
CREATE INDEX user_email_lower ON users(lower(email));
SELECT * FROM users WHERE lower(email) = lower(?);   -- now seekable

CREATE INDEX ev_kind ON events(json_extract(payload, '$.kind'));
SELECT * FROM events WHERE json_extract(payload, '$.kind') = 'login';
```

The expression in the query must match the indexed expression **syntactically**. Only
deterministic functions are allowed.

**Generated column alternative** — often clearer, and indexable the same way:

```sql
ALTER TABLE events ADD COLUMN kind TEXT
    GENERATED ALWAYS AS (json_extract(payload, '$.kind')) VIRTUAL;
CREATE INDEX ev_kind ON events(kind);
```

`VIRTUAL` costs nothing on disk and is computed on read; `STORED` costs disk and is
computed on write. For an indexed generated column, `VIRTUAL` is usually right — the index
already materialises the value.

---

## ANALYZE and sqlite_stat1

`ANALYZE` samples indexes and writes row-distribution statistics into the `sqlite_stat1`
table. Without it, the planner uses fixed guesses (roughly: "an index lookup returns ~10
rows"), which is fine for simple statements and wrong for skewed data.

```sql
ANALYZE;              -- whole database
ANALYZE events;       -- one table and its indexes
SELECT * FROM sqlite_stat1;   -- empty means it never ran
PRAGMA optimize;      -- run periodically / before closing: ANALYZEs only what changed
```

`PRAGMA optimize` is the maintenance answer for a long-lived application: cheap, targeted,
and safe to call on connection close.

### Verify the plan with and without statistics

**Many managed engines never run `ANALYZE`.** If your index only wins once stats exist, it
may not win in production. Test both states explicitly rather than assuming:

```sql
ANALYZE;
EXPLAIN QUERY PLAN SELECT ...;         -- plan A

DELETE FROM sqlite_stat1;
ANALYZE sqlite_master;                 -- forces the planner to reload now-empty stats
EXPLAIN QUERY PLAN SELECT ...;         -- plan B — same as A?
```

If A and B agree, the index is chosen regardless of statistics and you are safe. If they
disagree, either arrange for `ANALYZE` to run in production, or design an index the planner
picks without stats. In the worked example below, the covering index was chosen in both
states — a result that was **verified, not assumed**.

`sqlite_stat1` is an ordinary table: you can copy it between databases to reproduce a
production plan locally, which is the cheapest way to debug "fast on my machine".

---

## Query planner defeats

| Anti-pattern | Effect | Fix |
|---|---|---|
| `SELECT *` | Prevents covering-index plans; reads every column's page | Project explicitly |
| Function on an indexed column | Index unusable | Expression index, or move the function to the parameter side |
| Type mismatch (TEXT column vs integer param) | Index unusable; may silently return nothing | Fix affinity, cast the parameter, use `STRICT` |
| `OR` across different columns | May force a scan | `MULTI-INDEX OR` needs an index per branch; else rewrite as `UNION ALL` |
| `NOT IN (subquery)` with NULLs | Returns no rows at all | `NOT EXISTS` |
| Correlated scalar subquery in the `SELECT` list | Re-executed per row | Rewrite as a `JOIN` or a windowed aggregate |
| `LIMIT` without `ORDER BY` | Non-deterministic rows | Always pair them |
| `OFFSET` pagination on a large table | Scans and discards | Keyset pagination (below) |
| `ORDER BY random()` | Full sort of the table | Sample by rowid range |
| Aggregates over an unindexed column | Full scan per call | Index the column, or maintain a watermark row |
| Too many indexes | Slows every write, bloats the file | Audit; drop redundant prefixes |

### The invisible aggregate

The subtlest cost in this list. An aggregate like `MAX(updated_at)` over an unindexed
column scans the table — but if it ships inside a statement batch the application was
already sending, it costs **no extra round trip** and never surfaces in per-request timing.

Decomposition is how you find it:

```sql
-- The full statement as shipped
SELECT count(*) AS n, MAX(updated_at) AS watermark FROM q_product;

-- Time each half separately:
SELECT count(*) FROM q_product;                    -- cheap? then the MAX owns the cost
SELECT MAX(updated_at) FROM q_product;             -- the real cost centre
```

In the measured example: the full statement 27.17 ms / 58,434 rows; the `MAX` alone
28.09 ms / 58,432 rows; the statement **without** the `MAX` 0.17 ms / 2 rows; the same
`MAX` over an indexed column 0.17 ms / 1 row. One index on the aggregated column removed a
58,000-row scan from every response across four separate tools.

**Rule:** any `MIN`/`MAX`/`COUNT DISTINCT` over an unindexed column, executed per request,
is a full scan hiding in plain sight. Index the column — `MAX(col)` over an indexed column
is an O(log n) walk to the end of the B-tree.

---

## Joins and subqueries

SQLite uses nested-loop joins exclusively. Performance therefore hinges on the **inner**
table having an index on the join column.

```sql
EXPLAIN QUERY PLAN
SELECT o.id, u.email FROM orders o JOIN users u ON u.id = o.user_id;
-- want: SCAN o  +  SEARCH u USING INTEGER PRIMARY KEY (rowid=?)
-- bad:  SCAN o  +  SCAN u                      -> O(n*m)
```

| Symptom | Fix |
|---|---|
| `SCAN` on the inner table | Index the join column on the inner table |
| Join order looks wrong | Usually the planner is right; if genuinely wrong, `ANALYZE` first, `CROSS JOIN` to force order only as a last resort |
| CTE materialised repeatedly | `WITH x AS NOT MATERIALIZED (...)` (3.35+) to inline, or `AS MATERIALIZED` to force one evaluation |
| `IN (subquery)` slow | Often better as a `JOIN`; check whether the subquery is correlated |

---

## Pagination

```sql
-- Bad: OFFSET must generate and discard 10,000 rows
SELECT * FROM events ORDER BY id LIMIT 20 OFFSET 10000;

-- Good: keyset pagination — seeks straight to the page
SELECT * FROM events WHERE id > :last_id ORDER BY id LIMIT 20;

-- Composite sort key
SELECT * FROM events
WHERE (created_at, id) > (:last_created, :last_id)
ORDER BY created_at, id LIMIT 20;
```

Keyset pagination needs an index on exactly the sort key. It also gives stable results when
rows are inserted between page fetches, which `OFFSET` does not.

---

## The read-only proof technique

**Problem:** you believe an index will help, but creating it means writing to production
schema — which in a properly gated setup you cannot do from a working session, and which is
irreversible enough that you would like evidence first.

**Technique:** run the **identical statement shape** against a column that an existing index
already covers. Same table, same row count, same predicate shape, same projection width —
only the column identity changes. The measured difference is your projected payoff, obtained
with zero writes.

```sql
-- Control: the real, unindexed statement
SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%';
-- measured: 171.83 ms, 60,736 rows read

-- Proof: same shape over a column already covered by an index
SELECT DISTINCT org FROM q_product WHERE org LIKE '%acme%';
-- measured:   6.75 ms, 58,433 rows read
```

**How to keep the proof honest:**

| Requirement | Why |
|---|---|
| Same table and same predicate shape | Row count and scan pattern must match |
| Comparable column width | Proving with a 4-byte int and shipping a 200-char TEXT column overstates the win |
| Same projection count | `DISTINCT one_col` vs `DISTINCT three_cols` are different index widths |
| Confirm the control's plan is what you think | Run EQP on both; the proof shot should show `COVERING` |
| Report rows-read for both | If rows-read is unchanged, the win is row *width* — say so |

**What this technique cannot prove:** whether the planner will *choose* your new index (see
[ANALYZE](#analyze-and-sqlite_stat1)), or what the index costs on write. Pair it with the
stats check and a write-volume sanity check before shipping.

Generalises beyond indexes: any time you want to measure "what if this data were shaped
differently" on a production system you may not write to, look for an existing object that
already has the shape you're proposing and measure against that.

---

## A worked optimisation, end to end

> **These are numbers from one database, not constants.** Measured 2026-08-04 against a
> live Cloudflare D1 (`atdw-mirror`, region OC, colo SYD), 12 runs each, reporting the
> median of server-side `sql_duration_ms`. Table: 73 columns, 58k rows. Your database will
> produce different magnitudes; the *shape* of the reasoning is what transfers.

**1. Symptom.** A lookup filtering on an organisation name took ~170 ms and got slower as
the table grew.

**2. Plan.**

```
SCAN q_product USING INDEX q_product_org
```

A `SCAN` — but *with* an index. That combination is the tell: the index was consulted and
bought nothing, because the predicate was `LIKE '%…%'` and could not be seeked.

**3. Diagnosis.** Leading-wildcard `LIKE` is unseekable by construction. The existing index
did not cover the projected column, so every one of the 58k index entries triggered a fetch
of a 73-column row. The cost was **row width**, not row count.

**4. Hypothesis.** A covering index `(org, product_id)` keeps the scan but confines it to
narrow index entries.

**5. Read-only proof.** The same statement shape over an already-covered column: **6.75 ms /
58,433 rows** vs the control's **171.83 ms / 60,736 rows**. ~25x, with rows read essentially
unchanged — confirming the win is width, not selectivity.

**6. Stats check.** Plan compared with `sqlite_stat1` populated and after deleting it. The
covering index was chosen in both states — verified, not assumed.

**7. Second-order effect.** With the covering index in place, SQLite **dropped the
`GROUP BY` temp B-tree on its own**. A hand-rewrite to avoid the grouping measured 5.99 ms
vs 5.85 ms — noise. *Re-read the plan after adding an index before hand-optimising anything
else; the index may have already fixed it.*

**8. The one that was invisible.** A separate `MAX()` over an unindexed column rode inside a
batch the application was already sending: 28.09 ms and 58,432 rows scanned per response
across four tools, never once appearing in per-query timing. Decomposition found it; an
index on the aggregated column reduced it to 0.17 ms / 1 row.

**Transferable lessons:** `SCAN … USING INDEX` means the index is not earning its keep ·
prove before you write · check the plan with and without stats · re-read the plan after
each change · measure statements, not calls.

---

## Triage checklist

Work top to bottom; stop when the numbers are acceptable.

1. **Capture the baseline** — median of 10+, range, rows read, engine-reported timing.
2. **`EXPLAIN QUERY PLAN`** — classify every line against the table above.
3. **Any `SCAN` on a large table?** Determine whether the predicate is seekable at all.
   - Seekable → add or fix the index.
   - Unseekable → make the scan covering, or move to FTS5 trigram.
4. **`SCAN … USING INDEX`** (non-covering)? The index is not earning its place — either
   extend it to cover, or drop it.
5. **Decompose multi-part statements** — time each part; hunt for invisible aggregates.
6. **Check `SELECT *`** — it silently defeats covering plans.
7. **Prove read-only** before writing schema (see above).
8. **Check the plan with and without `sqlite_stat1`.**
9. **Apply the change; re-read the plan.** Temp B-trees often vanish for free.
10. **Re-measure both metrics** — latency and rows read — and report the deltas separately.

---

## See also

- [`d1-edge.md`](d1-edge.md) — measuring on Cloudflare D1, rows-read billing, caps
- [`schema-design.md`](schema-design.md) — affinity and type mismatches that defeat indexes
- [`feature-modules.md`](feature-modules.md) — FTS5 trigram as the real fix for substring search
- [`operations.md`](operations.md) — `PRAGMA optimize`, page sizing, size analysis
- `perf-ops` skill — the surrounding before/after profiling workflow

# SQLite Schema Design

Engine-agnostic. Type affinity, STRICT tables, foreign-key enforcement, generated columns,
and `WITHOUT ROWID` behave identically on every host. For ready-made table recipes see
[`schema-patterns.md`](schema-patterns.md); for changing an existing schema see
[`migration-patterns.md`](migration-patterns.md).

## Contents

- [Type affinity: the thing that surprises everyone](#type-affinity-the-thing-that-surprises-everyone)
- [STRICT tables](#strict-tables)
- [Foreign keys are OFF by default](#foreign-keys-are-off-by-default)
- [Primary keys and rowid](#primary-keys-and-rowid)
- [WITHOUT ROWID](#without-rowid)
- [Generated columns](#generated-columns)
- [Constraints and defaults](#constraints-and-defaults)
- [Storing dates, booleans, and JSON](#storing-dates-booleans-and-json)
- [Collation](#collation)
- [Schema review checklist](#schema-review-checklist)

---

## Type affinity: the thing that surprises everyone

SQLite is **dynamically typed**. A column's declared type is not a constraint — it is an
*affinity*, a preference for how values are converted when they can be converted losslessly.
A `TEXT` column will store the integer `42` if you insert `42`.

| Declared type contains | Affinity | Behaviour |
|---|---|---|
| `INT` | INTEGER | Text that looks like an integer is converted |
| `CHAR`, `CLOB`, `TEXT` | TEXT | Numbers are converted to text |
| `BLOB`, or no type at all | BLOB (none) | Everything stored as given |
| `REAL`, `FLOA`, `DOUB` | REAL | Integers stored as floats |
| anything else (e.g. `NUMERIC`, `DATETIME`, `BOOLEAN`) | NUMERIC | Converts to INTEGER/REAL when lossless, else TEXT |

```sql
CREATE TABLE t (a INTEGER, b TEXT, c BLOB);
INSERT INTO t VALUES ('42', 42, 42);
SELECT typeof(a), typeof(b), typeof(c) FROM t;
-- integer | text | integer     <- a and b converted; c kept as given
```

### Why this is a performance bug, not just a tidiness bug

A type mismatch between a column and a bound parameter can **prevent index use** and
**silently return no rows**, because comparison across storage classes follows fixed rules
(NULL < INTEGER/REAL < TEXT < BLOB) rather than converting.

```sql
CREATE TABLE code (id TEXT PRIMARY KEY);
INSERT INTO code VALUES ('00123');
SELECT * FROM code WHERE id = 123;    -- 0 rows: integer 123 never equals text '00123'
```

Debug with `typeof()`, which is the fastest way to find a column that has been storing two
storage classes for years:

```sql
SELECT typeof(id), count(*) FROM code GROUP BY 1;
```

---

## STRICT tables

SQLite 3.37+ (2021). Adding `STRICT` after the closing parenthesis makes declared types
**enforced**.

```sql
CREATE TABLE product (
    id     INTEGER PRIMARY KEY,
    sku    TEXT NOT NULL,
    price  REAL NOT NULL,
    active INTEGER NOT NULL DEFAULT 1     -- SQLite has no BOOLEAN; use INTEGER 0/1
) STRICT;

INSERT INTO product (sku, price) VALUES (42, 'free');
-- Error: cannot store INTEGER value in TEXT column product.sku
```

Rules for STRICT tables:

- Every column must declare one of exactly six types: `INT`, `INTEGER`, `REAL`, `TEXT`,
  `BLOB`, `ANY`.
- `ANY` stores anything without conversion — the escape hatch, and unlike a non-STRICT
  column with no type, it preserves the original storage class exactly.
- `NOT NULL` is enforced as always; STRICT does not change nullability.
- The `PRIMARY KEY` of a STRICT table is implicitly `NOT NULL` (fixing a long-standing
  legacy quirk where a non-INTEGER primary key could be NULL).

**Use STRICT for all new tables** unless you have a specific reason for dynamic typing. It
costs nothing at runtime and converts a class of silent data bugs into loud errors. The one
migration consideration: existing rows with mixed storage classes will block the table
recreation, which is a feature — it tells you the data was already wrong.

---

## Foreign keys are OFF by default

```sql
PRAGMA foreign_keys = ON;    -- per CONNECTION, every connection, not persistent
```

This is the classic silent data-integrity bug in SQLite applications. `REFERENCES` clauses
parse fine, are stored in the schema, appear in `.schema` output, and **do nothing** unless
the pragma is on for the connection performing the write. Applications routinely ship for
years with orphaned rows accumulating.

```sql
PRAGMA foreign_keys = ON;

CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT NOT NULL) STRICT;
CREATE TABLE book (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES author(id) ON DELETE CASCADE,
    title     TEXT NOT NULL
) STRICT;

-- Index the child column: SQLite indexes the PARENT key automatically, never the child
CREATE INDEX book_author ON book(author_id);
```

| Action | Options |
|---|---|
| `ON DELETE` | `NO ACTION` (default), `RESTRICT`, `CASCADE`, `SET NULL`, `SET DEFAULT` |
| `ON UPDATE` | Same set |
| Deferred checking | `DEFERRABLE INITIALLY DEFERRED` — checked at COMMIT, needed for circular references |

**Two operational notes:**

- `PRAGMA foreign_keys` is a **no-op inside a transaction** — set it before `BEGIN`.
- Find pre-existing damage with `PRAGMA foreign_key_check;` before turning enforcement on.
  It lists every violating row so you can repair rather than discover at runtime.

```sql
PRAGMA foreign_key_check;                 -- whole database
PRAGMA foreign_key_check(book);           -- one table
```

**Always index the child column.** SQLite requires an index on the parent side (the
`PRIMARY KEY`/`UNIQUE` it references) but creates nothing on the child side — so a
`ON DELETE CASCADE` on an unindexed child column triggers a full table scan per parent
delete.

---

## Primary keys and rowid

Every ordinary table has a hidden 64-bit `rowid`. `INTEGER PRIMARY KEY` is special: it
**becomes** the rowid rather than creating a separate index, which makes it the fastest
possible key.

```sql
CREATE TABLE a (id INTEGER PRIMARY KEY);           -- id IS the rowid. Fast, no extra index.
CREATE TABLE b (id INT PRIMARY KEY);               -- NOT the rowid (INT != INTEGER) - separate index
CREATE TABLE c (id TEXT PRIMARY KEY);              -- separate unique index + rowid
```

`INTEGER PRIMARY KEY AUTOINCREMENT` adds a `sqlite_sequence` table and guarantees ids are
never reused. It is **slower** and rarely needed: without it, SQLite reuses the ids of
deleted rows only when the max row is deleted. Use `AUTOINCREMENT` only when id reuse would
be a correctness or security problem (e.g. externally published ids).

| Key choice | Trade-off |
|---|---|
| `INTEGER PRIMARY KEY` | Fastest; rowid alias; ids may be reused |
| `INTEGER PRIMARY KEY AUTOINCREMENT` | Never reuses; extra table and write cost |
| `TEXT PRIMARY KEY` (UUID) | Portable and mergeable; larger index, random insert order hurts write locality |
| ULID / UUIDv7 in TEXT | Retains sortability, so insert locality is good — usually the right choice if you need a distributed id |

---

## WITHOUT ROWID

Stores rows directly in a B-tree keyed by the primary key, eliminating the extra rowid
indirection.

```sql
CREATE TABLE kv (
    key   TEXT PRIMARY KEY,
    value TEXT NOT NULL
) WITHOUT ROWID, STRICT;
```

| Use it when | Avoid it when |
|---|---|
| Primary key is a non-integer (TEXT/BLOB) that you always look up by | Primary key is `INTEGER` (already optimal) |
| Rows are small (a few hundred bytes) | Rows are large — big rows overflow badly here |
| Key-value or association tables | You need `AUTOINCREMENT` (incompatible) |
| Lookups are almost always by the full primary key | You rely on `rowid` / `last_insert_rowid()` |

Requires an explicit `PRIMARY KEY`. The win is typically both space and lookup speed for
small keyed rows; the loss is worse behaviour for wide rows and no rowid semantics.

---

## Generated columns

SQLite 3.31+. Compute a column from other columns in the same row — and, crucially, **index
it**. This is the clean way to make a JSON field or a normalised form queryable.

```sql
CREATE TABLE event (
    id      INTEGER PRIMARY KEY,
    payload TEXT NOT NULL,
    kind    TEXT GENERATED ALWAYS AS (json_extract(payload, '$.kind')) VIRTUAL,
    email_l TEXT GENERATED ALWAYS AS (lower(json_extract(payload, '$.email'))) VIRTUAL
) STRICT;

CREATE INDEX event_kind ON event(kind);
```

| Kind | Storage | Computed | Choose when |
|---|---|---|---|
| `VIRTUAL` (default) | None | On read | Indexed columns, or cheap expressions — usually right |
| `STORED` | On disk | On write | Expensive expressions read far more often than written |

Constraints: the expression must be deterministic and reference only columns of the same
row. Generated columns can be added by `ALTER TABLE ADD COLUMN` (`VIRTUAL` only) but not
dropped or altered without the full recreate dance.

---

## Constraints and defaults

```sql
CREATE TABLE account (
    id        INTEGER PRIMARY KEY,
    email     TEXT NOT NULL UNIQUE,
    status    TEXT NOT NULL DEFAULT 'active'
                  CHECK (status IN ('active','suspended','closed')),
    balance   REAL NOT NULL DEFAULT 0 CHECK (balance >= 0),
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;
```

| Constraint | Note |
|---|---|
| `NOT NULL` | Cheapest correctness win available; use liberally |
| `UNIQUE` | Creates an index — don't also create one manually |
| `CHECK` | Evaluated on insert/update; expression must be deterministic |
| `DEFAULT (expr)` | Parenthesised expression allowed (e.g. `datetime('now')`); function defaults need the parentheses |
| Partial `UNIQUE` | `CREATE UNIQUE INDEX ix ON t(a) WHERE b IS NULL` — the only way to express a conditional uniqueness rule |

`CHECK` constraints are enforced by the engine on every host, including managed ones, which
makes them more reliable than application-layer validation.

---

## Storing dates, booleans, and JSON

SQLite has no dedicated DATE, TIME, or BOOLEAN storage class. Pick one convention and put
it in the schema comment, because mixed conventions in one database are a recurring bug.

| Data | Recommended | Why |
|---|---|---|
| Timestamp | `TEXT` ISO-8601 UTC: `'2026-08-04T10:23:45Z'` | Sorts lexicographically, human-readable, works with `datetime()` |
| Timestamp (compact) | `INTEGER` Unix epoch seconds | Smaller, arithmetic-friendly, not human-readable |
| Date only | `TEXT` `'2026-08-04'` | Same sorting property |
| Boolean | `INTEGER` 0/1 | `TRUE`/`FALSE` keywords exist (3.23+) and store as 1/0 |
| Money | `INTEGER` minor units (cents) | Avoids float rounding; `REAL` money is a bug factory |
| JSON document | `TEXT` (or JSONB, 3.45+) | See [`feature-modules.md`](feature-modules.md) |
| Binary | `BLOB` | Keep large blobs out of hot tables — they widen every row read |

**Never mix conventions across tables.** A database with epoch integers in one table and ISO
strings in another guarantees a comparison bug eventually.

Storing large BLOBs inline is a specific performance trap: because SQLite stores rows
contiguously, a 2 MB blob in a row makes *every* scan of that table pay for it, even when
the blob column isn't selected — unless a covering index avoids the table entirely. Store
large binaries out of line (filesystem, object storage) and keep a reference.

---

## Collation

| Collation | Behaviour |
|---|---|
| `BINARY` (default) | Byte comparison; case-sensitive |
| `NOCASE` | ASCII case-insensitive only — **does not handle non-ASCII** |
| `RTRIM` | Ignores trailing spaces |

```sql
CREATE TABLE users (email TEXT COLLATE NOCASE);
CREATE INDEX users_email ON users(email);       -- index inherits the column collation
SELECT * FROM users WHERE email = 'Alice@Example.COM';   -- matches, and uses the index
```

The index must have the **same collation as the comparison** or it cannot be used. If you
write `WHERE email COLLATE NOCASE = ?` against a `BINARY` column and index, the index is
skipped — declare the collation on the column instead.

For real Unicode case-folding you need the ICU extension, which is not compiled in by
default and is unavailable on most managed hosts. A portable alternative is storing a
normalised (`lower()`ed) generated column and indexing that.

---

## Schema review checklist

- [ ] New tables declared `STRICT`
- [ ] `PRAGMA foreign_keys = ON` set in the connection factory (every host, every path)
- [ ] Every foreign-key **child** column has its own index
- [ ] `PRAGMA foreign_key_check` clean
- [ ] `NOT NULL` on everything that logically cannot be null
- [ ] `CHECK` constraints for enumerations instead of free-text status columns
- [ ] One timestamp convention across the whole database, documented
- [ ] Money as integer minor units, never `REAL`
- [ ] Large BLOBs stored out of line
- [ ] `INTEGER PRIMARY KEY` unless a distributed id is genuinely needed
- [ ] `AUTOINCREMENT` only where id reuse would be a real problem
- [ ] Collation declared on the column, not in the query
- [ ] JSON fields that are queried have a generated column + index

---

## See also

- [`schema-patterns.md`](schema-patterns.md) — ready-made designs (state, cache, queue, log)
- [`migration-patterns.md`](migration-patterns.md) — changing a schema safely
- [`query-performance.md`](query-performance.md) — how affinity mismatches defeat indexes
- [`feature-modules.md`](feature-modules.md) — JSON, FTS5, and other module-backed columns

# SQLite Schema Patterns

Ready-made table designs for state, caching, logging, queues, sessions, and search.
Engine-agnostic SQL — these run on any host, including D1 and libSQL.

**Before using any of these**, read the three rules they assume
([`schema-design.md`](schema-design.md) has the depth):

- Add **`STRICT`** to every `CREATE TABLE` below unless you need dynamic typing — the
  recipes omit it only to stay readable on pre-3.37 engines.
- `PRAGMA foreign_keys = ON` must be set on **every connection**, or `REFERENCES` does
  nothing.
- Timestamps here use ISO-8601 text (`datetime('now')`), which sorts correctly and compares
  as text. Pick one convention per database and stick to it.

Related: [`schema-design.md`](schema-design.md) (affinity, STRICT, generated columns),
[`migration-patterns.md`](migration-patterns.md) (changing these later),
[`feature-modules.md`](feature-modules.md) (FTS5 depth),
[`query-performance.md`](query-performance.md) (whether the indexes below actually get used).

## State/Config Storage

Key-value store with automatic timestamps:

```sql
CREATE TABLE IF NOT EXISTS app_state (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at TEXT DEFAULT (datetime('now'))
);

-- Upsert pattern (insert or update)
INSERT INTO app_state (key, value) VALUES ('last_sync', '2024-01-15')
ON CONFLICT(key) DO UPDATE SET value = excluded.value, updated_at = datetime('now');

-- Get value
SELECT value FROM app_state WHERE key = 'last_sync';

-- Get all state
SELECT * FROM app_state ORDER BY updated_at DESC;
```

## Cache Table

Time-based cache with expiry cleanup:

```sql
CREATE TABLE IF NOT EXISTS cache (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    expires_at TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now'))
);

-- Create index for expiry cleanup
CREATE INDEX IF NOT EXISTS idx_cache_expires ON cache(expires_at);

-- Insert with 1 hour TTL
INSERT INTO cache (key, value, expires_at)
VALUES ('user:123', '{"name": "Alice"}', datetime('now', '+1 hour'))
ON CONFLICT(key) DO UPDATE SET
    value = excluded.value,
    expires_at = excluded.expires_at;

-- Get non-expired value
SELECT value FROM cache
WHERE key = 'user:123' AND expires_at > datetime('now');

-- Cleanup expired entries
DELETE FROM cache WHERE expires_at < datetime('now');
```

## Event/Log Table

Append-only event log with type indexing:

```sql
CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    event_type TEXT NOT NULL,
    payload TEXT,  -- JSON
    created_at TEXT DEFAULT (datetime('now'))
);

-- Index for type + date queries
CREATE INDEX IF NOT EXISTS idx_events_type_date ON events(event_type, created_at);

-- Insert event
INSERT INTO events (event_type, payload)
VALUES ('user_login', '{"user_id": 123, "ip": "10.0.0.1"}');

-- Get recent events by type
SELECT * FROM events
WHERE event_type = 'user_login'
AND created_at > datetime('now', '-1 day')
ORDER BY created_at DESC;

-- Count by type
SELECT event_type, COUNT(*) as count
FROM events
GROUP BY event_type;
```

## Deduplication Table

Track seen items to avoid reprocessing:

```sql
CREATE TABLE IF NOT EXISTS seen_items (
    hash TEXT PRIMARY KEY,
    source TEXT NOT NULL,
    first_seen TEXT DEFAULT (datetime('now'))
);

-- Check if seen
SELECT 1 FROM seen_items WHERE hash = ? LIMIT 1;

-- Mark as seen
INSERT OR IGNORE INTO seen_items (hash, source) VALUES (?, ?);

-- Get sources for hash
SELECT source, first_seen FROM seen_items WHERE hash = ?;

-- Cleanup old entries
DELETE FROM seen_items WHERE first_seen < datetime('now', '-30 days');
```

## Queue Table

Simple job queue with status tracking:

```sql
CREATE TABLE IF NOT EXISTS job_queue (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    job_type TEXT NOT NULL,
    payload TEXT NOT NULL,  -- JSON
    status TEXT DEFAULT 'pending',  -- pending, processing, completed, failed
    priority INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    started_at TEXT,
    completed_at TEXT,
    error TEXT
);

-- Index for fetching next job
CREATE INDEX IF NOT EXISTS idx_jobs_status_priority
ON job_queue(status, priority DESC, created_at);

-- Claim next job (atomic with transaction)
UPDATE job_queue
SET status = 'processing', started_at = datetime('now')
WHERE id = (
    SELECT id FROM job_queue
    WHERE status = 'pending'
    ORDER BY priority DESC, created_at
    LIMIT 1
)
RETURNING *;

-- Complete job
UPDATE job_queue
SET status = 'completed', completed_at = datetime('now')
WHERE id = ?;

-- Fail job
UPDATE job_queue
SET status = 'failed', completed_at = datetime('now'), error = ?
WHERE id = ?;
```

## Session Table

User sessions with expiry:

```sql
CREATE TABLE IF NOT EXISTS sessions (
    token TEXT PRIMARY KEY,
    user_id INTEGER NOT NULL,
    data TEXT,  -- JSON
    created_at TEXT DEFAULT (datetime('now')),
    expires_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);

-- Create session (1 week expiry)
INSERT INTO sessions (token, user_id, data, expires_at)
VALUES (?, ?, '{}', datetime('now', '+7 days'));

-- Get valid session
SELECT * FROM sessions
WHERE token = ? AND expires_at > datetime('now');

-- Extend session
UPDATE sessions
SET expires_at = datetime('now', '+7 days')
WHERE token = ?;

-- Delete session
DELETE FROM sessions WHERE token = ?;

-- Cleanup expired
DELETE FROM sessions WHERE expires_at < datetime('now');
```

## Full-Text Search Table

Using SQLite FTS5:

```sql
-- Create FTS table
CREATE VIRTUAL TABLE IF NOT EXISTS documents_fts USING fts5(
    title,
    content,
    content='documents',
    content_rowid='id'
);

-- Trigger to keep FTS in sync
CREATE TRIGGER documents_ai AFTER INSERT ON documents BEGIN
    INSERT INTO documents_fts(rowid, title, content)
    VALUES (new.id, new.title, new.content);
END;

CREATE TRIGGER documents_ad AFTER DELETE ON documents BEGIN
    INSERT INTO documents_fts(documents_fts, rowid, title, content)
    VALUES('delete', old.id, old.title, old.content);
END;

CREATE TRIGGER documents_au AFTER UPDATE ON documents BEGIN
    INSERT INTO documents_fts(documents_fts, rowid, title, content)
    VALUES('delete', old.id, old.title, old.content);
    INSERT INTO documents_fts(rowid, title, content)
    VALUES (new.id, new.title, new.content);
END;

-- Search
SELECT * FROM documents_fts WHERE documents_fts MATCH 'search query';

-- Ranked search
SELECT *, rank FROM documents_fts
WHERE documents_fts MATCH 'query'
ORDER BY rank;
```

# Testing Against SQLite

SQLite is unusually pleasant to test against: a whole database is one file (or none at all),
so isolation is cheap and setup is fast. The traps are the ways a test database quietly
stops resembling production.

## Contents

- [In-memory vs file databases](#in-memory-vs-file-databases)
- [Keeping the test database honest](#keeping-the-test-database-honest)
- [Fixture strategies](#fixture-strategies)
- [Deterministic seeding](#deterministic-seeding)
- [Testing migrations](#testing-migrations)
- [Testing concurrency](#testing-concurrency)
- [Testing query plans](#testing-query-plans)
- [Testing against D1](#testing-against-d1)

---

## In-memory vs file databases

```python
sqlite3.connect(":memory:")                                     # private to this connection
sqlite3.connect("file:test?mode=memory&cache=shared", uri=True) # shared across connections
sqlite3.connect("/tmp/test-xyz.db")                             # real file
```

| | `:memory:` | Shared-cache memory | Temp file |
|---|---|---|---|
| Speed | Fastest | Fast | Fast enough (OS page cache) |
| Multiple connections see it | **No** | Yes | Yes |
| Supports WAL | **No** (WAL needs a real file) | No | **Yes** |
| Survives the process | No | No | Yes — inspectable after a failure |
| Matches production behaviour | Least | Middling | **Most** |

**Recommendation: temp files, not `:memory:`.** The speed difference is negligible against
the OS page cache, and a file test can exercise WAL, real locking, multiple connections, and
`busy_timeout` — precisely the behaviours where SQLite bugs live. A file also survives a
failing test, so you can open it and look.

```python
import tempfile, pathlib, sqlite3, pytest

@pytest.fixture
def db_path(tmp_path: pathlib.Path) -> str:
    return str(tmp_path / "test.db")     # pytest deletes tmp_path automatically
```

Reserve `:memory:` for pure-SQL unit tests where a single connection is genuinely the whole
story.

---

## Keeping the test database honest

The recurring failure is a test database configured differently from production, so tests
pass on behaviour production doesn't have. **Use the same connection factory in tests as in
production** — do not hand-roll a second one.

```python
# app/db.py — one factory, used by prod and tests alike
def connect(path: str) -> sqlite3.Connection:
    conn = sqlite3.connect(path, isolation_level=None)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA busy_timeout = 5000")
    conn.execute("PRAGMA foreign_keys = ON")
    return conn
```

| Divergence | Consequence |
|---|---|
| `foreign_keys` on in prod, off in tests (or vice versa) | FK violations either pass tests and fail live, or the reverse |
| WAL in prod, rollback in tests | Locking behaviour differs; concurrency bugs invisible |
| STRICT tables in prod, loose in tests | Type errors slip through |
| Tiny test dataset | Every plan is a scan and every scan is fast — **no performance signal at all** |
| Schema built by a fixture instead of by migrations | Tests validate a schema that never exists in production |

**Build the test schema by running your real migrations.** That way the migration path is
tested on every run, and the tested schema is by construction the one production will have.

---

## Fixture strategies

| Strategy | Speed | Isolation | Use when |
|---|---|---|---|
| Fresh database per test | Slowest | Perfect | Small suites; anything touching schema |
| Template copy | Fast | Perfect | Expensive seed data — build once, `shutil.copy` per test |
| Transaction rollback per test | Fastest | Good | Read-heavy tests that don't need their own DDL |
| Truncate between tests | Fast | Good | Stable schema, changing data |

```python
# Template pattern: seed once per session, copy per test - fast AND fully isolated
import shutil, pytest

@pytest.fixture(scope="session")
def template_db(tmp_path_factory):
    path = tmp_path_factory.mktemp("tpl") / "template.db"
    conn = connect(str(path))
    run_migrations(conn)
    seed(conn)
    conn.close()
    return str(path)

@pytest.fixture
def db(template_db, tmp_path):
    path = tmp_path / "test.db"
    shutil.copy(template_db, path)     # copying a CLOSED database is safe
    conn = connect(str(path))
    yield conn
    conn.close()
```

Copying a closed database file is safe — the prohibition on `cp` applies to databases with
active writers (see [`operations.md`](operations.md)). Close the template before copying, or
build it with `VACUUM INTO`.

```python
# Rollback pattern: fastest, but the test cannot commit or run its own DDL
@pytest.fixture
def db(shared_conn):
    shared_conn.execute("BEGIN")
    yield shared_conn
    shared_conn.execute("ROLLBACK")
```

---

## Deterministic seeding

Flaky test data is a self-inflicted wound. Three rules:

1. **Seed the RNG explicitly.** `random.Random(1234)`, never the global module state.
2. **Never use SQL `random()` or `datetime('now')` in fixtures.** Both make the fixture
   non-reproducible and time-dependent — the classic source of a suite that fails at
   midnight or on a leap day.
3. **Fix the clock.** Pass timestamps in as data; don't let the database generate them.

```python
import random

def seed(conn, n: int = 1000, seed_value: int = 1234) -> None:
    rng = random.Random(seed_value)          # local RNG - global state is not test-safe
    base = "2026-01-01T00:00:00Z"            # fixed epoch, not datetime('now')
    rows = [
        (f"org-{rng.randrange(50)}", f"sku-{i:06d}",
         round(rng.uniform(1, 500), 2),
         f"2026-01-{1 + (i % 28):02d}T00:00:00Z")
        for i in range(n)
    ]
    conn.execute("BEGIN IMMEDIATE")
    conn.executemany(
        "INSERT INTO product (org, sku, price, created_at) VALUES (?,?,?,?)", rows)
    conn.execute("COMMIT")
```

**Seed enough rows to produce a performance signal.** A hundred rows makes every plan fast
and every index pointless; if you intend to assert anything about scans or plans, seed tens
of thousands. Generate them — don't commit a large fixture file.

---

## Testing migrations

Migrations are the code most likely to destroy data and least likely to be tested. Assert
three things:

```python
def test_migrations_are_idempotent(db_path):
    conn = connect(db_path)
    run_migrations(conn)
    before = schema_snapshot(conn)
    run_migrations(conn)                       # second run must be a no-op
    assert schema_snapshot(conn) == before

def test_migration_preserves_data(db_path):
    conn = connect(db_path)
    run_migrations(conn, target=3)
    conn.execute("INSERT INTO product (org, sku, price) VALUES ('acme','x',1.0)")
    run_migrations(conn, target=4)             # the migration under test
    row = conn.execute("SELECT org, sku FROM product").fetchone()
    assert (row["org"], row["sku"]) == ("acme", "x")

def test_schema_is_valid_after_migration(db_path):
    conn = connect(db_path)
    run_migrations(conn)
    assert conn.execute("PRAGMA integrity_check").fetchone()[0] == "ok"
    assert conn.execute("PRAGMA foreign_key_check").fetchall() == []


def schema_snapshot(conn) -> list:
    return conn.execute(
        "SELECT type, name, sql FROM sqlite_master ORDER BY type, name").fetchall()
```

The `foreign_key_check` assertion is the one that catches the 12-step recreate dance going
wrong — a rebuilt table that dropped its references still looks fine until something reads
across it. See [`migration-patterns.md`](migration-patterns.md).

---

## Testing concurrency

Concurrency bugs need a **file** database and real connections.

```python
import threading, sqlite3

def test_concurrent_writers_do_not_error(db_path):
    """Two writers with busy_timeout should serialise, not raise."""
    errors = []

    def writer(tag):
        conn = connect(db_path)                # separate CONNECTION, not a shared one
        try:
            for i in range(100):
                conn.execute("BEGIN IMMEDIATE")
                conn.execute("INSERT INTO event (kind) VALUES (?)", (tag,))
                conn.execute("COMMIT")
        except sqlite3.OperationalError as exc:
            errors.append(exc)
        finally:
            conn.close()

    threads = [threading.Thread(target=writer, args=(f"t{i}",)) for i in range(4)]
    for t in threads: t.start()
    for t in threads: t.join()
    assert not errors, f"contention errors: {errors}"
```

To test that your retry logic works, do the opposite: set `busy_timeout = 0`, force a
conflict, and assert the retry wrapper recovers.

---

## Testing query plans

Plans can regress silently — an added column turns a covering index non-covering, and
nothing fails except latency. A plan assertion is a cheap regression guard for the small
number of statements that genuinely matter.

```python
def plan(conn, sql: str, params=()) -> str:
    return "\n".join(r["detail"]
                     for r in conn.execute("EXPLAIN QUERY PLAN " + sql, params))

def test_org_lookup_uses_covering_index(db):
    detail = plan(db, "SELECT DISTINCT product_id FROM q_product WHERE org LIKE ?", ("%acme%",))
    assert "COVERING INDEX" in detail, detail      # the word COVERING is the whole test
    assert "USE TEMP B-TREE" not in detail, detail
```

Keep these to the handful of statements you have actually optimised. Asserting plans across
a whole codebase produces a brittle suite that fails on every legitimate schema change.

`scripts/eqp-triage.py --db <file> --sql "<statement>"` exits `10` when it finds a problem,
which makes it usable directly as a shell-level assertion in CI.

---

## Testing against D1

| Approach | Fidelity | Note |
|---|---|---|
| Local SQLite with the same schema | Good for logic | No rows-read metric, no parameter cap, no `SQLITE_AUTH` restrictions |
| `wrangler d1 execute` **without** `--remote` | Good | Local D1 copy — same wrangler surface, no network |
| Miniflare / `wrangler dev` | Good | Exercises the Workers binding API too |
| A preview/dev D1 database | Highest | The only place to verify platform behaviour (parameter caps, FTS5 availability) |

**Never point tests at the production database.** For the platform-specific behaviours that
only appear remotely — the 100-parameter cap, `SQLITE_AUTH` refusals, real `rows_read` — use
a dedicated preview database and treat those as integration tests, run deliberately rather
than on every commit.

---

## See also

- [`hosts.md`](hosts.md) — the connection factory to share between prod and tests
- [`migration-patterns.md`](migration-patterns.md) — what the migration tests are guarding
- [`query-performance.md`](query-performance.md) — reading the plans you assert on
- [`operations.md`](operations.md) — restore drills as a scheduled test

#!/usr/bin/env python3
"""Triage a SQLite EXPLAIN QUERY PLAN: classify each plan line and suggest a fix.

Usage:   eqp-triage.py [--db FILE --sql SQL | --plan-file FILE | -] [OPTIONS]
Input:   argv (--db + --sql, or --plan-file), or a plan on stdin. Accepts raw
         sqlite3 CLI text, `sqlite3 -json`, or `wrangler d1 execute --json` output.
Output:  stdout - findings, one per line: SEVERITY<TAB>CATEGORY<TAB>DETAIL<TAB>FIX
         (or the claude-mods.sqlite-ops.eqp/v1 envelope under --json)
Stderr:  headers, progress, warnings, errors
Exit:    0 clean, 2 usage, 3 file-not-found, 4 invalid-input/SQL-error,
         5 missing-dep, 10 findings at or above the reporting threshold

Examples:
  eqp-triage.py --db app.db --sql "SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%acme%'"
  sqlite3 app.db 'EXPLAIN QUERY PLAN SELECT * FROM t WHERE a=1;' | eqp-triage.py
  wrangler d1 execute mydb --remote --json --command "EXPLAIN QUERY PLAN SELECT ..." | eqp-triage.py
  eqp-triage.py --db app.db --sql "SELECT ..." --json | jq '.data[]'
  eqp-triage.py --plan-file plan.txt --strict     # also fail on low-severity findings
"""

import argparse
import json
import os
import re
import sys

SCHEMA = "claude-mods.sqlite-ops.eqp/v1"

EXIT_OK = 0
EXIT_USAGE = 2
EXIT_NOT_FOUND = 3
EXIT_VALIDATION = 4
EXIT_MISSING_DEP = 5
EXIT_FINDINGS = 10

SEVERITY_ORDER = {"info": 0, "low": 1, "medium": 2, "high": 3}

# Rules are evaluated in order; the FIRST match wins, so the most specific
# patterns must come first. In particular COVERING INDEX must be tested before
# the bare "SCAN ... USING INDEX" rule, because a covering scan is acceptable
# while a non-covering one usually means the index is not earning its place.
RULES = [
    (
        re.compile(r"\bSEARCH\b.*\bUSING COVERING INDEX\b", re.I),
        "info", "covering-seek",
        "Seek answered entirely from the index - the table is never read. Best case.",
    ),
    (
        re.compile(r"\bSEARCH\b.*\bUSING INTEGER PRIMARY KEY\b", re.I),
        "info", "rowid-seek",
        "Direct rowid lookup. Best case.",
    ),
    (
        re.compile(r"\bSEARCH\b.*\bUSING (?:INDEX|AUTOMATIC)\b", re.I),
        "info", "index-seek",
        "B-tree seek. Fine. Consider covering the projected columns if the row is wide.",
    ),
    (
        re.compile(r"\bSCAN\b.*\bUSING COVERING INDEX\b", re.I),
        "low", "covering-scan",
        "Full pass over narrow index entries, table never read. Often the right answer "
        "for an unseekable predicate; reduces latency but usually NOT rows-read.",
    ),
    (
        re.compile(r"\bSCAN\b.*\bUSING (?:INDEX|AUTOMATIC (?:COVERING )?INDEX)\b", re.I),
        "high", "noncovering-scan",
        "Every index entry read AND a table row fetched per hit - the index buys little. "
        "Extend it to cover the projected columns (filtered column first, projected "
        "second), or drop it.",
    ),
    (
        re.compile(r"\bCORRELATED\b", re.I),
        "high", "correlated-subquery",
        "Subquery re-executed once per outer row. Rewrite as a JOIN or a windowed "
        "aggregate.",
    ),
    (
        re.compile(r"\bSCAN\b", re.I),
        "high", "table-scan",
        "Full table scan. Add an index matching the WHERE/JOIN, or - if the predicate "
        "cannot be seeked (leading-wildcard LIKE, function on the column) - make the scan "
        "covering or move to FTS5 trigram.",
    ),
    (
        re.compile(r"\bUSE TEMP B-TREE FOR (?:RIGHT PART OF )?ORDER BY\b", re.I),
        "medium", "temp-btree-order",
        "Sorting because no index supplies the order. A composite index ending in the "
        "sort column removes it. Re-check AFTER any index change - it often clears itself.",
    ),
    (
        re.compile(r"\bUSE TEMP B-TREE FOR GROUP BY\b", re.I),
        "medium", "temp-btree-group",
        "Grouping without an index supplying the order. Re-check AFTER any index change - "
        "adding a covering index frequently removes this on its own.",
    ),
    (
        re.compile(r"\bUSE TEMP B-TREE FOR DISTINCT\b", re.I),
        "medium", "temp-btree-distinct",
        "De-duplicating in a temp B-tree. An index covering the DISTINCT columns removes it.",
    ),
    (
        re.compile(r"\bUSE TEMP B-TREE\b", re.I),
        "medium", "temp-btree",
        "A temporary B-tree is being built. Check which clause needs it and whether an "
        "index can supply that order.",
    ),
]

# Informational plan lines that are never findings on their own.
BENIGN = re.compile(
    r"^\s*(QUERY PLAN|MULTI-INDEX OR|INDEX \d+|BLOOM FILTER|MATERIALIZE|CO-ROUTINE|"
    r"LIST SUBQUERY|SCALAR SUBQUERY|USING (?:ROWID SEARCH|INDEX FOR)|RIGHT-JOIN|"
    r"MERGE|LEFT-JOIN|COMPOUND QUERY|UNION|EXCEPT|INTERSECT|RECURSIVE)",
    re.I,
)

# Strip sqlite3's tree drawing and the legacy "0|0|0|" column prefix.
TREE_PREFIX = re.compile(r"^[\s|`+\-]*")
LEGACY_PREFIX = re.compile(r"^\d+\|\d+\|\d+\|")

# Vocabulary a genuine EQP line uses. Text input is filtered against this so
# that arbitrary text (a stray log, the wrong command's output) is reported as
# invalid input rather than silently triaged as "clean" - a false all-clear is
# the worst possible outcome for a tool whose job is finding problems.
PLAN_VOCAB = re.compile(
    r"\b(SCAN|SEARCH|USE TEMP B-TREE|CO-ROUTINE|SUBQUERY|MATERIALIZE|"
    r"MULTI-INDEX OR|BLOOM FILTER|COMPOUND QUERY|UNION|EXCEPT|INTERSECT|"
    r"RECURSIVE|MERGE|LEFT-JOIN|RIGHT-JOIN|USING (?:INDEX|COVERING|ROWID|"
    r"INTEGER PRIMARY KEY)|CORRELATED)\b",
    re.I,
)


def warn(message):
    """Human-facing output goes to stderr; stdout stays a clean data stream."""
    print(message, file=sys.stderr)


def collect_details(node, out):
    """Recursively pull every 'detail' string out of decoded JSON.

    Handles both `sqlite3 -json` ([{detail: ...}]) and wrangler's
    [{results: [{detail: ...}], meta: {...}}] shape without special-casing either.
    """
    if isinstance(node, dict):
        detail = node.get("detail")
        if isinstance(detail, str):
            out.append(detail)
        for value in node.values():
            collect_details(value, out)
    elif isinstance(node, list):
        for value in node:
            collect_details(value, out)


def parse_plan(text):
    """Return a list of plan detail strings from JSON or raw sqlite3 CLI text."""
    stripped = text.strip()
    if not stripped:
        return []

    if stripped[0] in "[{":
        try:
            details = []
            collect_details(json.loads(stripped), details)
            if details:
                return details
        except (ValueError, RecursionError):
            pass  # not JSON after all - fall through to text parsing

    lines = []
    for raw in stripped.splitlines():
        line = LEGACY_PREFIX.sub("", raw.strip())
        line = TREE_PREFIX.sub("", line).strip()
        if not line or line.upper() == "QUERY PLAN":
            continue
        if not PLAN_VOCAB.search(line):
            continue  # not a plan line - see PLAN_VOCAB
        lines.append(line)
    return lines


def classify(detail):
    """Return (severity, category, fix) for one plan line, or None if benign."""
    for pattern, severity, category, fix in RULES:
        if pattern.search(detail):
            return severity, category, fix
    if BENIGN.search(detail):
        return None
    return None


def run_plan(db_path, sql):
    """Run EXPLAIN QUERY PLAN against a database using Python's bundled sqlite3."""
    try:
        import sqlite3
    except ImportError:  # pragma: no cover - stdlib module absent is a broken build
        warn("error: Python's sqlite3 module is unavailable in this interpreter")
        sys.exit(EXIT_MISSING_DEP)

    if not os.path.isfile(db_path):
        warn("error: database not found: %s" % db_path)
        sys.exit(EXIT_NOT_FOUND)

    # Read-only URI: this script must never be able to modify the database it
    # is asked to analyse, even if handed a statement with side effects.
    uri = "file:%s?mode=ro" % db_path.replace("?", "%3f").replace("#", "%23")
    try:
        conn = sqlite3.connect(uri, uri=True)
    except sqlite3.Error as exc:
        warn("error: cannot open database: %s" % exc)
        sys.exit(EXIT_VALIDATION)

    try:
        rows = conn.execute("EXPLAIN QUERY PLAN " + sql).fetchall()
    except sqlite3.Error as exc:
        warn("error: %s" % exc)
        sys.exit(EXIT_VALIDATION)
    finally:
        conn.close()

    # EQP rows are (id, parent, notused, detail); detail is always last.
    return [str(row[-1]) for row in rows]


def main(argv=None):
    parser = argparse.ArgumentParser(
        prog="eqp-triage.py",
        description="Triage a SQLite EXPLAIN QUERY PLAN and suggest fixes.",
        epilog=(
            "EXAMPLES:\n"
            "  eqp-triage.py --db app.db --sql \"SELECT * FROM t WHERE a LIKE '%x%'\"\n"
            "  sqlite3 app.db 'EXPLAIN QUERY PLAN SELECT * FROM t;' | eqp-triage.py\n"
            "  wrangler d1 execute db --remote --json --command \"EXPLAIN QUERY PLAN "
            "SELECT ...\" | eqp-triage.py\n"
            "  eqp-triage.py --db app.db --sql 'SELECT ...' --json | jq '.data[]'\n"
        ),
        formatter_class=argparse.RawDescriptionHelpFormatter,
    )
    parser.add_argument("stdin_marker", nargs="?", default=None,
                        help="'-' to read the plan from stdin (the default when piped)")
    parser.add_argument("--db", help="SQLite database file to run the plan against")
    parser.add_argument("--sql", help="Statement to explain (requires --db)")
    parser.add_argument("--plan-file", help="File containing captured plan output")
    parser.add_argument("--json", action="store_true",
                        help="Emit the claude-mods.sqlite-ops.eqp/v1 envelope on stdout")
    parser.add_argument("--strict", action="store_true",
                        help="Exit 10 on low-severity findings too (default: medium+)")
    parser.add_argument("--quiet", action="store_true",
                        help="Suppress stderr headers; findings still go to stdout")

    args, extra = parser.parse_known_args(argv)
    if extra:
        parser.print_usage(sys.stderr)
        warn("error: unrecognised arguments: %s" % " ".join(extra))
        return EXIT_USAGE
    if args.stdin_marker not in (None, "-"):
        parser.print_usage(sys.stderr)
        warn("error: unexpected positional argument: %s" % args.stdin_marker)
        return EXIT_USAGE
    if args.sql and not args.db:
        warn("error: --sql requires --db")
        return EXIT_USAGE
    if args.db and not args.sql:
        warn("error: --db requires --sql")
        return EXIT_USAGE
    if args.db and args.plan_file:
        warn("error: --db/--sql and --plan-file are mutually exclusive")
        return EXIT_USAGE

    # --- acquire the plan ---
    source = None
    if args.db:
        details = run_plan(args.db, args.sql)
        source = args.db
    elif args.plan_file:
        if not os.path.isfile(args.plan_file):
            warn("error: plan file not found: %s" % args.plan_file)
            return EXIT_NOT_FOUND
        with open(args.plan_file, "r", encoding="utf-8", errors="replace") as handle:
            details = parse_plan(handle.read())
        source = args.plan_file
    else:
        if sys.stdin is None or sys.stdin.isatty():
            parser.print_usage(sys.stderr)
            warn("error: no input - pass --db/--sql, --plan-file, or pipe a plan on stdin")
            return EXIT_USAGE
        details = parse_plan(sys.stdin.read())
        source = "stdin"

    if not details:
        warn("error: no EXPLAIN QUERY PLAN lines found in input from %s" % source)
        return EXIT_VALIDATION

    # --- classify ---
    findings = []
    for detail in details:
        verdict = classify(detail)
        if verdict is None:
            continue
        severity, category, fix = verdict
        findings.append({
            "severity": severity,
            "category": category,
            "detail": detail,
            "fix": fix,
        })

    findings.sort(key=lambda f: -SEVERITY_ORDER[f["severity"]])
    threshold = SEVERITY_ORDER["low" if args.strict else "medium"]
    actionable = [f for f in findings if SEVERITY_ORDER[f["severity"]] >= threshold]

    # --- report ---
    if args.json:
        print(json.dumps({
            "data": findings,
            "meta": {
                "count": len(findings),
                "actionable": len(actionable),
                "plan_lines": len(details),
                "source": source,
                "threshold": "low" if args.strict else "medium",
                "schema": SCHEMA,
            },
        }, indent=2))
    else:
        if not args.quiet:
            warn("eqp-triage  %d plan line(s) from %s" % (len(details), source))
        for finding in findings:
            print("%s\t%s\t%s\t%s" % (
                finding["severity"].upper(), finding["category"],
                finding["detail"], finding["fix"]))
        if not args.quiet:
            if actionable:
                warn("  %d actionable finding(s) at or above %s severity"
                     % (len(actionable), "low" if args.strict else "medium"))
            else:
                warn("  no actionable findings")

    return EXIT_FINDINGS if actionable else EXIT_OK


if __name__ == "__main__":
    sys.exit(main())

#!/usr/bin/env bash
# Self-test for sqlite-ops: frontmatter contract, reference wiring, script behaviour.
#
# Fully offline and self-contained - the only external need is a working Python
# (stdlib sqlite3), which every supported platform has. Fixtures are synthesized
# in a temp dir, so no binary fixtures live in the repo.
#
# Usage:   bash tests/run.sh
# Exit:    0 all pass, 1 one or more failures

set -uo pipefail

HERE="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
SKILL="$(dirname "$HERE")"
S="$SKILL/scripts"
R="$SKILL/references"
MD="$SKILL/SKILL.md"

# Windows Store python3 is a stub that exits non-zero - probe for one that runs.
PYTHON=""
for c in python python3 py; do
  if command -v "$c" >/dev/null 2>&1 && "$c" -c "" >/dev/null 2>&1; then PYTHON="$c"; break; fi
done
if [[ -z "$PYTHON" ]]; then
  echo "  SKIP  no working python found - sqlite-ops suite not run" >&2
  exit 0
fi

SB="$(mktemp -d)"; trap 'rm -rf "$SB"' EXIT
PASS=0; FAIL=0
ok() { PASS=$((PASS+1)); printf '  PASS  %s\n' "$1"; }
no() { FAIL=$((FAIL+1)); printf '  FAIL  %s\n' "$1"; }
expect_exit() { [[ "$2" == "$3" ]] && ok "$1 (exit $3)" || no "$1 (want $2 got $3)"; }
expect_has()  { case "$3" in *"$2"*) ok "$1";; *) no "$1 (missing '$2')";; esac; }

# Case-insensitive literal file search WITHOUT grep. GNU grep 3.0 (the build
# shipped with Git Bash on Windows) ABORTS with SIGABRT on `-i` combined with
# `-F` - it exits 134, which reads as "no match" and silently fails every
# assertion. Pure-bash matching sidesteps the bug and is portable.
file_has() { # $1=label $2=needle $3=file
  local hay needle
  hay="$(tr '[:upper:]' '[:lower:]' < "$3")"
  needle="$(printf '%s' "$2" | tr '[:upper:]' '[:lower:]')"
  case "$hay" in *"$needle"*) ok "$1";; *) no "$1 (missing '$2')";; esac
}

echo "=== sqlite-ops self-test ==="

# ── frontmatter contract ────────────────────────────────────────────────────
# CONTRACT: these assertions police this skill's OWN frontmatter. The skill was
# de-Pythonised on 2026-08-04 precisely because a Python-pinned description and
# compatibility line suppressed it in TypeScript/Worker contexts. If you edit the
# frontmatter, keep it engine-agnostic or these fail on purpose.
echo "-- frontmatter --"
fm="$(sed -n '2,/^---$/p' "$MD")"

expect_has "name is sqlite-ops"            "name: sqlite-ops" "$fm"
expect_has "license MIT"                   "license: MIT"     "$fm"
expect_has "metadata.author claude-mods"   "author: claude-mods" "$fm"

# related-skills must be a comma-separated STRING under metadata, never an array
# (naming-conventions.md + SKILL-SUBAGENT-REFERENCE.md). doc-drift.sh also checks
# that every skill named here exists on disk.
expect_has "related-skills present"        "related-skills:" "$fm"
case "$fm" in
  *"related-skills: \""*) ok "related-skills is a quoted string, not an array";;
  *"related-skills: ["*)  no "related-skills is a YAML array (must be a comma-separated string)";;
  *) no "related-skills not in the expected string form";;
esac
for peer in sql-ops perf-ops cloudflare-ops postgres-ops; do
  expect_has "related-skills names $peer" "$peer" "$fm"
done

# The de-Pythonisation guard: description and compatibility must NOT scope the
# skill to Python. A Python-only description hides this skill from Worker/TS work.
case "$fm" in
  *"in Python projects"*) no "description still scopes the skill to Python projects";;
  *)                      ok "description is not scoped to Python projects";;
esac
case "$fm" in
  *"compatibility: \"Requires Python"*) no "compatibility still pins the skill to Python";;
  *)                                    ok "compatibility does not pin the skill to Python";;
esac
expect_has "compatibility states engine-agnostic guidance" "engine-agnostic" "$fm"

# Description budget: tests/validate.sh HARD-FAILS over 700 chars for
# description + when_to_use combined. Catch it here rather than at the repo gate.
budget="$("$PYTHON" - "$MD" <<'PY'
import sys, pathlib
lines = pathlib.Path(sys.argv[1]).read_text(encoding="utf-8-sig").splitlines()
marks = [i for i, l in enumerate(lines) if l.strip() == "---"]
body = "\n".join(lines[marks[0] + 1:marks[1]])
try:
    import yaml
    fm = yaml.safe_load(body) or {}
    total = len(str(fm.get("description") or "")) + len(str(fm.get("when_to_use") or ""))
except ImportError:
    # Fallback: measure the raw single-line values without a YAML parser.
    total = 0
    for line in body.splitlines():
        for key in ("description:", "when_to_use:"):
            if line.startswith(key):
                total += len(line[len(key):].strip().strip('"').strip("'"))
print(total)
PY
)"
if [[ -n "$budget" && "$budget" -le 700 ]]; then
  ok "description budget ${budget}/700 chars"
else
  no "description budget ${budget}/700 chars (validate.sh hard-fails over 700)"
fi

# ── trigger keywords ────────────────────────────────────────────────────────
# These are the terms that must route a performance/D1 question here. They were
# absent before 2026-08-04, which is why a live D1 investigation never loaded
# this skill. Removing one silently un-routes that class of question.
echo "-- description triggers --"
desc_line="$(grep -m1 '^description:' "$MD")"
for trigger in "EXPLAIN QUERY PLAN" "covering index" "rows_read" "sql_duration_ms" \
               "D1" "wrangler d1" "node:sqlite" "better-sqlite3" "fts5" "trigram" \
               "sqlite_stat1" "ANALYZE" "SQLITE_BUSY" "WAL" "STRICT tables"; do
  case "$desc_line" in
    *"$trigger"*) ok "trigger: $trigger";;
    *)            no "trigger MISSING from description: $trigger";;
  esac
done

# ── references exist and are cited ──────────────────────────────────────────
echo "-- references --"
for ref in query-performance d1-edge d1-production-patterns concurrency-durability \
           schema-design schema-patterns migration-patterns feature-modules hosts \
           async-patterns operations testing; do
  if [[ -f "$R/$ref.md" ]]; then ok "reference exists: $ref.md"; else no "reference MISSING: $ref.md"; fi
  # SKILL-RESOURCE-PROTOCOL: an uncited reference is dead weight the router never finds.
  file_has "SKILL.md cites $ref.md" "references/$ref.md" "$MD"
done

# ── measured facts that must not silently vanish ────────────────────────────
# These numbers come from a live D1 investigation (2026-08-04) and are the
# evidence behind the skill's core lesson. They are labelled as one database's
# worked example, NOT as constants - but if an edit drops them, the reasoning
# loses its grounding, so they are pinned here.
echo "-- worked-example facts --"
file_has "covering-index before figure (171.83 ms)" "171.83" "$R/query-performance.md"
file_has "covering-index after figure (6.75 ms)"    "6.75"   "$R/query-performance.md"
file_has "invisible-aggregate figure (28.09 ms)"    "28.09"  "$R/query-performance.md"
file_has "numbers labelled as a worked example"     "worked example" "$R/query-performance.md"
file_has "read-only proof technique documented"     "read-only proof" "$R/query-performance.md"
file_has "SCAN vs SEARCH distinction"               "COVERING INDEX" "$R/query-performance.md"
file_has "sqlite_stat1 with/without check"          "sqlite_stat1" "$R/query-performance.md"

echo "-- d1 facts --"
file_has "rows_read documented"          "rows_read"          "$R/d1-edge.md"
file_has "sql_duration_ms documented"    "sql_duration_ms"    "$R/d1-edge.md"
file_has "one-line statement rule"       "SQLITE_ERROR 7500"  "$R/d1-edge.md"
file_has "100 bound-parameter cap"       "100"                "$R/d1-edge.md"
file_has "SQLITE_AUTH introspection block" "SQLITE_AUTH"      "$R/d1-edge.md"
# FTS5 on D1 was NOT confirmable read-only. Recording it as unknown is the honest
# result; an edit that replaces this with a confident claim is a regression.
file_has "FTS5-on-D1 recorded as unknown" "could not be confirmed read-only" "$R/d1-edge.md"
file_has "d1 insights covered"           "d1 insights"        "$R/d1-edge.md"
file_has "Sessions API / replication"    "withSession"        "$R/d1-edge.md"
file_has "Time Travel covered"           "time-travel"        "$R/d1-edge.md"
file_has "cold-run variance figure"      "2,495"              "$R/d1-edge.md"

echo "-- engine-agnostic coverage --"
file_has "concurrency: BUSY vs LOCKED"   "SQLITE_LOCKED"      "$R/concurrency-durability.md"
file_has "concurrency: BEGIN IMMEDIATE"  "BEGIN IMMEDIATE"    "$R/concurrency-durability.md"
file_has "schema: foreign_keys OFF by default" "OFF by default" "$R/schema-design.md"
file_has "schema: STRICT tables"         "STRICT"             "$R/schema-design.md"
file_has "migrations: 12-step dance"     "12-step"            "$R/migration-patterns.md"
file_has "features: trigram tokenizer"   "trigram"            "$R/feature-modules.md"
file_has "hosts: node:sqlite"            "node:sqlite"        "$R/hosts.md"
file_has "hosts: bun:sqlite"             "bun:sqlite"         "$R/hosts.md"
file_has "operations: VACUUM INTO"       "VACUUM INTO"        "$R/operations.md"
file_has "testing: deterministic seeding" "deterministic"     "$R/testing.md"

# ── script contract (SKILL-RESOURCE-PROTOCOL) ───────────────────────────────
echo "-- eqp-triage.py contract --"
"$PYTHON" -m py_compile "$S/eqp-triage.py" 2>/dev/null && ok "py_compile eqp-triage.py" \
                                                       || no "py_compile eqp-triage.py"
"$PYTHON" "$S/eqp-triage.py" --help >/dev/null 2>&1; expect_exit "--help" 0 $?
help_out="$("$PYTHON" "$S/eqp-triage.py" --help 2>/dev/null)"
expect_has "--help has EXAMPLES" "EXAMPLES" "$help_out"
file_has "SKILL.md cites the script with a worked invocation" "eqp-triage.py --db" "$MD"

echo "-- eqp-triage.py exit codes --"
"$PYTHON" "$S/eqp-triage.py" --bogus-flag </dev/null >/dev/null 2>&1
expect_exit "unknown flag -> 2" 2 $?
"$PYTHON" "$S/eqp-triage.py" --sql "SELECT 1" </dev/null >/dev/null 2>&1
expect_exit "--sql without --db -> 2" 2 $?
"$PYTHON" "$S/eqp-triage.py" --db "$SB/nope.db" --sql "SELECT 1" </dev/null >/dev/null 2>&1
expect_exit "missing database -> 3" 3 $?
"$PYTHON" "$S/eqp-triage.py" --plan-file "$SB/nope.txt" </dev/null >/dev/null 2>&1
expect_exit "missing plan file -> 3" 3 $?
printf 'not a plan at all\n' | "$PYTHON" "$S/eqp-triage.py" >/dev/null 2>&1
expect_exit "unparseable input -> 4" 4 $?

# ── behavioural: real plans against a synthesized database ──────────────────
echo "-- eqp-triage.py behaviour --"
"$PYTHON" - "$SB/t.db" <<'PY'
import sqlite3, sys
conn = sqlite3.connect(sys.argv[1])
conn.execute("CREATE TABLE q_product (id INTEGER PRIMARY KEY, org TEXT, "
             "product_id TEXT, pad TEXT)")
conn.execute("CREATE INDEX q_product_org ON q_product(org)")
conn.executemany("INSERT INTO q_product (org, product_id, pad) VALUES (?,?,?)",
                 [("org%d" % (i % 50), "p%d" % i, "x" * 200) for i in range(2000)])
conn.commit(); conn.close()
PY
[[ -f "$SB/t.db" ]] && ok "fixture database synthesized" || no "fixture database synthesized"

# Leading-wildcard LIKE over a non-covering index: the exact shape from the
# worked example. Must be flagged HIGH and exit 10.
out="$("$PYTHON" "$S/eqp-triage.py" --db "$SB/t.db" --quiet \
        --sql "SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%org1%'" 2>/dev/null)"
rc=$?
expect_exit "non-covering scan -> 10" 10 "$rc"
expect_has  "flags the scan as HIGH" "HIGH" "$out"

# Add the covering index; the scan must be reclassified as LOW (acceptable).
"$PYTHON" -c "import sqlite3,sys; c=sqlite3.connect(sys.argv[1]); \
c.execute('CREATE INDEX q_org_prod ON q_product(org, product_id)'); c.commit()" "$SB/t.db"
out="$("$PYTHON" "$S/eqp-triage.py" --db "$SB/t.db" --quiet \
        --sql "SELECT DISTINCT product_id FROM q_product WHERE org LIKE '%org1%'" 2>/dev/null)"
expect_has "covering scan classified LOW" "LOW	covering-scan" "$out"

# An indexed equality seek is clean: no actionable findings, exit 0.
"$PYTHON" "$S/eqp-triage.py" --db "$SB/t.db" --quiet \
  --sql "SELECT product_id FROM q_product WHERE org = 'x'" >/dev/null 2>&1
expect_exit "indexed seek -> 0" 0 $?

# Invalid SQL is a validation error, not a crash.
"$PYTHON" "$S/eqp-triage.py" --db "$SB/t.db" --sql "SELECT FROM WHERE" >/dev/null 2>&1
expect_exit "bad SQL -> 4" 4 $?

# The script must never be able to write to the database it analyses.
"$PYTHON" "$S/eqp-triage.py" --db "$SB/t.db" --quiet \
  --sql "DELETE FROM q_product" >/dev/null 2>&1
rc=$?
rows="$("$PYTHON" -c "import sqlite3,sys; print(sqlite3.connect(sys.argv[1]).execute(
  'SELECT count(*) FROM q_product').fetchone()[0])" "$SB/t.db")"
[[ "$rows" == "2000" ]] && ok "read-only guard: rows intact after a DELETE statement" \
                        || no "read-only guard FAILED: row count now $rows (want 2000)"

echo "-- eqp-triage.py input formats --"
# Raw sqlite3 CLI text with tree-drawing characters
out="$(printf 'QUERY PLAN\n|--SCAN q_product\n`--USE TEMP B-TREE FOR GROUP BY\n' \
        | "$PYTHON" "$S/eqp-triage.py" --quiet 2>/dev/null)"
expect_has "parses raw sqlite3 tree output" "table-scan" "$out"
expect_has "detects temp B-tree for GROUP BY" "temp-btree-group" "$out"
# Legacy pipe-delimited format
out="$(printf '0|0|0|SCAN TABLE q_product\n' | "$PYTHON" "$S/eqp-triage.py" --quiet 2>/dev/null)"
expect_has "parses legacy 0|0|0| format" "table-scan" "$out"
# wrangler d1 execute --json shape
out="$(printf '[{"results":[{"id":2,"parent":0,"detail":"SCAN q_product USING INDEX q_product_org"}],"success":true,"meta":{"rows_read":58433}}]' \
        | "$PYTHON" "$S/eqp-triage.py" --json 2>/dev/null)"
expect_has "parses wrangler --json output" "noncovering-scan" "$out"
expect_has "--json emits the versioned schema" '"schema": "claude-mods.sqlite-ops.eqp/v1"' "$out"
expect_has "--json envelope has data key" '"data"' "$out"
expect_has "--json envelope has meta key" '"meta"' "$out"

# --strict promotes low-severity findings to actionable
printf 'SCAN t USING COVERING INDEX ix\n' | "$PYTHON" "$S/eqp-triage.py" --quiet >/dev/null 2>&1
expect_exit "covering scan alone -> 0 by default" 0 $?
printf 'SCAN t USING COVERING INDEX ix\n' | "$PYTHON" "$S/eqp-triage.py" --quiet --strict >/dev/null 2>&1
expect_exit "covering scan -> 10 under --strict" 10 $?

# Stream separation: with --quiet, stdout carries findings only.
so="$(printf 'SCAN t\n' | "$PYTHON" "$S/eqp-triage.py" --quiet 2>/dev/null)"
case "$so" in
  HIGH*) ok "stdout is data-only under --quiet";;
  *)     no "stdout polluted with non-data output: $so";;
esac

echo ""
echo "=== $PASS passed, $FAIL failed ==="
[[ "$FAIL" -eq 0 ]] || exit 1
exit 0