Yesterday I discovered that SQLite isn’t all that reliable at maintaining integrity of FTS5 virtual tables. Following their documentation to create a content backed virtual table with the recommended triggers lead to database corruption after just 5 operations.
Not recommended.
The core database seems fine tho!
However, I’m considering making the backup plan for coffee time not just about backing up the database files but also exporting all the content into a plain text format periodically. That way I guarantee every record in the database has basic integrity.
Fun times!
I’m glad, tho, that my design was so simple I could swap out the FTS5 implementation for Lucene in a few hours. Not bad!