Docs: path-table vs declared-table tradeoff; FTS5 and vector index templates #960

Problem

Two docs gaps, one page's worth of work, both load-bearing once #956 lands.

1. The path-table vs declared-table tradeoff is real but scattered

A user cannot easily tell these are two mechanisms with opposite trade-offs:

• Path-table (FROM './**/*.json') — temp virtual table, minted per connection, scanned per statement. Always current, content read lazily, zero setup. No index, no watcher, no persistence. You pay the walk every run and gain DX simplicity.
• Declared table ([[table]] in config) — a real table, kept fresh by the watcher, surviving restarts under --persist, and (with Declared tables: wide-open multi-statement ddl, catalog validation #956) carrying indexes. You accept configuration and gain performance.

The facts exist but are scattered (docs/reference/path-tables.md §Freshness, db.rs:100 on temp, vtab.rs:33 on the HIDDEN content column). Nothing states the trade-off as one deliberate choice. Without it, users hit path-table performance on a large tree and read it as a dirsql limitation rather than the trade they chose. Indices, persistence, and the watcher belong to declared tables only — by design, not omission; the docs should say so.

2. Copy-pasteable index templates (howto)

With #956 the ddl batch is wide open and the triggers are the error-prone part; their failure mode is silent (a malformed FTS5 'delete' trigger leaves stale rows in results). Ship tested templates rather than abstractions:

FTS5 (no dependency — ships inside SQLite):

CREATE VIRTUAL TABLE messages_fts USING fts5(
  text, content='messages', content_rowid='rowid',
  tokenize='porter unicode61'
);
CREATE TRIGGER messages_fts_ai AFTER INSERT ON messages BEGIN
  INSERT INTO messages_fts(rowid, text) VALUES (new.rowid, new.text);
END;
CREATE TRIGGER messages_fts_ad AFTER DELETE ON messages BEGIN
  INSERT INTO messages_fts(messages_fts, rowid, text) VALUES ('delete', old.rowid, old.text);
END;

Two triggers suffice: dirsql upserts are delete+insert in one transaction; no UPDATE path on user rows exists. Query with MATCH + bm25() + snippet().

Vector (opt-in: sqlite-vec via [[dirsql.extension]] — the pip package's sqlite_vec.loadable_path() resolves the platform .so — plus the embeddings plugin):

CREATE VIRTUAL TABLE messages_vec USING vec0(embedding float[256]);
CREATE TRIGGER messages_vec_ai AFTER INSERT ON messages BEGIN
  INSERT INTO messages_vec(rowid, embedding)
  VALUES (new.rowid, embed(new.text, 'minishlab/potion-retrieval-32M'));
END;
CREATE TRIGGER messages_vec_ad AFTER DELETE ON messages BEGIN
  DELETE FROM messages_vec WHERE rowid = old.rowid;
END;

Query: WHERE embedding MATCH embed(:q, '<same-model-id>') AND k = :n, joined back to the base table on rowid, ordered by distance. Both templates were verified against real SQLite / sqlite-vec v0.1.9 (2026-08-20), including trigger-time JSON-text vector insertion and delete propagation.

Depends on #956 (the templates require the multi-statement ddl).