FTS data integrity issues

[knopkodav]

Hi!

Quick facts:

• .package(url: "https://github.com/powersync-ja/powersync-swift", from: "1.7.0"),

I've started to get constraint errors suddenly on insert to the Cards table:

caught error: "operationFailed(message: nil, underlyingError: Optional(Error Domain=KotlinException
Code=0 "SqliteException(1555): constraint failed, constraint failed for SQL: INSERT INTO cards (

Which was quite hard to debug because of a lack of information.

The error constraint failed persisted, even if I kept only the id column without any other columns in the schema.

It turns out that the error was in FTS cache, which had more rows in the fts_cards_content table than the ps_data__cards table (159 VS 8). With lots of gaps in the ID sequence.

The FTS triggers are literally a copy-paste from the demo project.

I have no idea why the AFTER DELETE trigger didn't work, but this issue messed up the whole PowerSync behaviour. Do not find something in our case is better than have errors inserting that.

It feels like it's safer to rewrite the AFTER INSERT trigger like this to workaround potential FTS discrepancies -- added OR REPLACE:

    // 3. Create INSERT Trigger
    sqlStatements.append("""
        CREATE TRIGGER IF NOT EXISTS fts_insert_trigger_\(tableName) AFTER INSERT ON \(internalName)
        BEGIN
            INSERT OR REPLACE INTO \(ftsTableName)(rowid, id, \(stringColumnsForInsertList))
            VALUES (
                NEW.rowid,
                NEW.id,
                \(generateJsonExtracts(type: .columnOnly, sourceColumn: "NEW.data", columns: columns))
            );
        END;
    """)

Would be happy to see a better solution. Thank you!