I fubar-ed something (I have a vague idea what I did wrong -- I initiated a trigger inside a transaction that would have updated an FTS5 table, but I did so without proper values inserted into the parent tables) and now I get SqliteError: database disk image is malformed… code: SQLITE_CORRUPT_VTAB. Is there a way to recover from this or do I have to start all over again?

bonus question: how do I prevent this from happening again? Of course, the logical answer is to abort the transaction if the triggering "INSERT" itself fails. Somehow I managed to get around this check. I'd like this to check for this so it doesn't happen again. Suggestions?

All of this is via nodejs and Better-SQLite. I have a multi-insert transaction which fires several triggers to update various virtual tables.

Update: ok, I think I know what is going on (though I don't know why or how to fix it and still accomplish what I want). I have table, an insert statement and a trigger like so (actually, I repeat this pattern for a few other tables, and they all exhibit the same problem)

CREATE TABLE figureCitations (
    id INTEGER PRIMARY KEY,
    figureCitationId TEXT NOT NULL UNIQUE,
    treatmentId TEXT NOT NULL REFERENCES treatments(treatmentId),
    figureNum INTEGER DEFAULT 0,
    captionText TEXT,
    httpUri TEXT,
    UNIQUE (figureCitationId, figureNum)
);
CREATE VIRTUAL TABLE figureCitationsFts USING fts5 (
    captionText,
    content=''
);
CREATE TRIGGER fc_afterUpdate 
    AFTER UPDATE ON figureCitations 
    BEGIN
    
        -- remove old index entry
        INSERT INTO figurecitationsFts(
            figurecitationsFts, 
            rowid, 
            captionText
        ) 
        VALUES(
            'delete', 
            old.id, 
            old.captionText
        );

        -- insert new index entry
        INSERT INTO figurecitationsFts(rowid, captionText) 
        VALUES (new.id, new.captionText);
    END;

-- INSERT statement
INSERT INTO figureCitations (
    figureCitationId,
    figureNum,
    treatmentId,
    captionText,
    httpUri
)
VALUES (
    @figureCitationId,
    @figureNum,
    @treatmentId,
    @captionText,
    @httpUri
)
ON CONFLICT (figureCitationId, figureNum)
DO UPDATE SET
    figureCitationId=excluded.figureCitationId,
    figureNum=excluded.figureNum,
    treatmentId=excluded.treatmentId,
    captionText=excluded.captionText,
    httpUri=excluded.httpUri

The insert statement as above allows for revised figureCitations to be upsert-ed in the db. However, that upsert is an update, which triggers the fc_afterUpdate trigger which buggers up the Fts table resulting in a malformed disk image error with code SQLITE_CORRUPT_VTAB. I also have a corresponding fc_afterInsert trigger but for performance reasons, I don't create it until after the initial big load of the tables. This results in an FTS5 table to be "updated" when it doesn't yet have anything in it. I could easily delay creating both triggers, but I am still not clear if I have an insert statement as above, does it the afterInsert or afterUpdate or both triggers get fired? When I create both inserts after the initial load, subsequent upserts could still bugger up the vtab (or would they not?)

The rebuild command?