I'm getting a strange error trying to query a SQLite database that uses FTS4.

I've published the database file here: https://static.simonwillison.net/static/2022/optimizedIndex.dsidx - it's 484KB.

I made the database using this tool: https://github.com/hynek/doc2dash

Here's the error:

% sqlite3 optimizedIndex.dsidx                       
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> select * from sqlite_master;
table|searchIndex|searchIndex|2|CREATE TABLE searchIndex(rowid INTEGER PRIMARY KEY, name TEXT, type TEXT, path TEXT, titleDescription TEXT)
table|queryIndex|queryIndex|0|CREATE VIRTUAL TABLE queryIndex USING FTS4 (content="", perfect, prefix, suffixes, titleDescriptionPerfect, titleDescriptionPrefix, titleDescriptionSuffixes, matchinfo=fts3, tokenize=simple XX [* ])
table|queryIndex_segments|queryIndex_segments|3|CREATE TABLE 'queryIndex_segments'(blockid INTEGER PRIMARY KEY, block BLOB)
table|queryIndex_segdir|queryIndex_segdir|4|CREATE TABLE 'queryIndex_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx))
index|sqlite_autoindex_queryIndex_segdir_1|queryIndex_segdir|5|
table|queryIndex_stat|queryIndex_stat|6|CREATE TABLE 'queryIndex_stat'(id INTEGER PRIMARY KEY, value BLOB)
view|wholeIndex|wholeIndex|0|CREATE VIEW wholeIndex AS SELECT queryIndex.rowid AS rowid, name, type, path, titleDescription, perfect, prefix, suffixes, titleDescriptionPerfect, titleDescriptionPrefix, titleDescriptionSuffixes FROM searchIndex JOIN queryIndex ON searchIndex.rowid = queryIndex.rowid
trigger|index_insert|wholeIndex|0|CREATE TRIGGER index_insert INSTEAD OF INSERT ON wholeIndex
BEGIN
INSERT INTO searchIndex (name, type, path, titleDescription) VALUES (NEW.name, NEW.type, NEW.path, NEW.titleDescription);
INSERT INTO queryIndex (rowid, perfect, prefix, suffixes, titleDescriptionPerfect, titleDescriptionPrefix, titleDescriptionSuffixes) VALUES (last_insert_rowid(), NEW.perfect, NEW.prefix, NEW.suffixes, NEW.titleDescriptionPerfect, NEW.titleDescriptionPrefix, NEW.titleDescriptionSuffixes);
END
sqlite> select * from wholeIndex;
Error: stepping, SQL logic error (1)

Any idea how I can go about understanding and working around this error?

I've only seen SQL logic error whenever I'm querying a virtual table wrong, or when the virtual table has a bug. I'm sure that same error happens elsewhere too, but since your DB is working with fts4 virtual tables, I'm guessing it's that.

wholeIndex is a view for this query:

CREATE VIEW wholeIndex AS 
  SELECT 
    queryIndex.rowid AS rowid, 
    name, 
    type, 
    path, 
    titleDescription, 
    perfect, 
    prefix, 
    suffixes, 
    titleDescriptionPerfect, 
    titleDescriptionPrefix, 
    titleDescriptionSuffixes 
  FROM searchIndex 
  JOIN queryIndex ON searchIndex.rowid = queryIndex.rowid

searchIndex is a normal table, queryIndex is a fts4 virtual table:

CREATE VIRTUAL TABLE queryIndex USING FTS4 (content="", perfect, prefix, suffixes, titleDescriptionPerfect, titleDescriptionPrefix, titleDescriptionSuffixes, matchinfo=fts3, tokenize=simple XX [* ])
/* queryIndex(perfect,prefix,suffixes,titleDescriptionPerfect,titleDescriptionPrefix,titleDescriptionSuffixes) */;

In fact, querying queryIndex directly also throws a logic error:

sqlite> select * from queryIndex;
Runtime error: SQL logic error

Although adding a match makes the query work, but I can't get it to return anything in your DB:

sqlite> select * from queryIndex where titleDescriptionPrefix match 'the';
[nothing returned]

If I had to guess, queryIndex is probably corrupted/is not inserting data correctly. I can't find an alternative error message (because it's probably the fts4 module complaining about something but not giving a correct error message). I'd recommend reviewing how that queryIndex virtual table gets created/populated