Resolving FTS5 Content Rowid Mismatch and Trusted Schema Errors in SQLite

Understanding FTS5 Content Rowid Constraints and Trusted Schema Requirements

Issue Context: Content Rowid Type Mismatch and Virtual Table Safety Errors

The problem revolves around integrating an fts5 virtual table (notes_search) with a content source table (notes) using triggers. The user encountered two critical issues:

1. Data Type Mismatch Error: When attempting to map the content_rowid option of the fts5 table to the note_id column (a VARCHAR), SQLite raised a type mismatch. The workaround was to instead map content_rowid to an INTEGER column (row_id).

2. Unsafe Virtual Table Operation: When PRAGMA trusted_schema=1 was not enabled, SQLite blocked modifications to the notes_search table via triggers, citing a "Parse error: unsafe use of virtual table."

The core challenge lies in reconciling the implicit requirements of fts5 virtual tables with SQLite’s schema safety mechanisms. Let’s dissect the technical constraints and solutions.

Root Causes: FTS5 Rowid Affinity and Schema Trust Enforcement

1. Content Rowid Must Map to Integer Values

The content_rowid option in an fts5 virtual table specifies the column in the content table that serves as the row identifier. While SQLite’s documentation states that content_rowid does not require an INTEGER-typed column, it must reference a column storing integer values. This is because fts5 virtual tables internally map row identifiers to 64-bit signed integers, mirroring SQLite’s rowid system column behavior.

The note_id column in the notes table is declared as VARCHAR(36), which has TEXT affinity. Even if its values superficially resemble integers (e.g., '12345'), SQLite treats them as strings. When fts5 attempts to resolve the content_rowid value during operations like trigger-based updates, it expects an integer, leading to a type mismatch. The row_id column, being an INTEGER PRIMARY KEY, aligns with SQLite’s rowid semantics, ensuring compatibility.

2. Trusted Schema PRAGMA and Virtual Table Safety

SQLite’s trusted_schema PRAGMA determines whether the database engine trusts the schema definitions (e.g., table structures, triggers) to be safe for certain operations. When trusted_schema=0 (the default in some environments), SQLite restricts virtual table operations that could execute arbitrary code or access unsafe functions. This is a security measure to prevent malicious schema designs from compromising the host application.

The fts5 extension, prior to SQLite version 3.44.0, required trusted_schema=1 for trigger-based modifications because its virtual table implementation was not marked as "innocuous." An innocuous virtual table is one that cannot execute arbitrary code or access external resources. Without this designation, SQLite blocks modifications to fts5 tables via triggers unless the schema is explicitly trusted.

Resolutions: Aligning FTS5 Configuration and Schema Safety Settings

1. Correcting Content Rowid Mapping

Step 1: Validate Content Rowid Source Column
Ensure the column referenced by content_rowid is an INTEGER PRIMARY KEY or explicitly stores integer values. For the notes table:

-- Use an INTEGER PRIMARY KEY for content_rowid
CREATE TABLE notes (
  row_id INTEGER PRIMARY KEY AUTOINCREMENT,  -- Correct: Integer affinity
  note_id VARCHAR(36) NOT NULL UNIQUE,
  ...
);

Step 2: Verify Column Affinity
Columns with INTEGER PRIMARY KEY automatically assume integer affinity, even if declared without explicit type constraints. Avoid using columns with TEXT, BLOB, or REAL affinity for content_rowid.

Step 3: Update FTS5 Table Definition
Map content_rowid to the validated integer column:

CREATE VIRTUAL TABLE notes_search USING fts5(
  title,
  body,
  content='notes',
  content_rowid='row_id'  -- Matches notes.row_id
);

Step 4: Test Trigger Logic
Ensure triggers reference the correct row identifier. For example, the AFTER INSERT trigger should use new.row_id (the integer) instead of new.note_id:

CREATE TRIGGER notes_ai AFTER INSERT ON notes BEGIN
  INSERT INTO notes_search(rowid, title, body) VALUES (new.row_id, new.title, new.body);
END;

2. Addressing Trusted Schema Requirements

Option 1: Upgrade to SQLite 3.44.0 or Later
SQLite 3.44.0 marked the fts5 extension as innocuous, eliminating the need for trusted_schema=1 in most cases. Verify the SQLite version:

SELECT sqlite_version();  -- Should return 3.44.0 or higher

Option 2: Enable Trusted Schema Temporarily
If upgrading is impractical, set PRAGMA trusted_schema=1 at the start of the session. This tells SQLite to trust the schema definitions, including the fts5 virtual table and its triggers:

PRAGMA trusted_schema=1;  -- Enable schema trust
CREATE TABLE ...;         -- Schema definitions

Option 3: Rebuild the Database with Innocuous Flag (Advanced)
For embedded systems or custom SQLite builds, ensure the SQLITE_VTAB_INNOCUOUS flag is set for fts5. This requires modifying the SQLite amalgamation source:

// In sqlite3.c, ensure fts5 is registered with SQLITE_VTAB_INNOCUOUS
sqlite3_create_module_v2(
  db, 
  "fts5", 
  &fts5Module, 
  (void*)pHash, 
  fts5ModuleDestroy, 
  SQLITE_VTAB_INNOCUOUS
);

Step 4: Audit Schema for Other Unsafe Constructs
If trusted_schema=1 is still required after upgrading, check for other virtual tables or functions marked as unsafe. Use sqlite3_db_config with SQLITE_DBCONFIG_TRUSTED_SCHEMA to narrow down the cause.

Preventative Measures and Best Practices

1. Explicit Integer Columns for Rowid Mapping
   Always use INTEGER PRIMARY KEY columns for content_rowid in fts5 virtual tables. Avoid relying on columns with non-integer affinity, even if they appear to store numeric values.

2. Version-Specific Feature Testing
   Conditional logic in application code can detect the SQLite version and adjust schema definitions or PRAGMA settings accordingly:

   import sqlite3
   conn = sqlite3.connect(':memory:')
   version = conn.execute('SELECT sqlite_version()').fetchone()[0]
   if version < '3.44.0':
       conn.execute('PRAGMA trusted_schema=1')
   

3. Schema Validation Scripts
   Automate checks for common fts5 pitfalls:

   -- Check if content_rowid maps to an integer column
   SELECT name, type 
   FROM pragma_table_info('notes') 
   WHERE name = 'row_id' AND type = 'INTEGER';
   

4. Security Implications of Trusted Schema
   Understand that enabling trusted_schema=1 weakens SQLite’s defense against malicious schemas. Use it only when necessary and in controlled environments.

By addressing the affinity of the content_rowid source column and aligning the SQLite version or trusted_schema settings with the fts5 requirements, the integration between the content table and the virtual search table will function as intended, enabling robust full-text search capabilities without compromising safety or performance.