Unified Full-Text Search Across Multiple SQLite Entities with FTS5

Structural Challenges in Aggregating Heterogeneous Entities for FTS5 Indexing

The goal of implementing a unified full-text search (FTS) across multiple SQLite entities (e.g., journals, annotations, notes, messages) with divergent schemas introduces several architectural and operational challenges. These entities typically have distinct fields (e.g., author, tags, markdown_content, location), making it difficult to standardize their representation in a single FTS5 virtual table. For example, a journal entry might include date and entry fields, while a note may have subject, body, and tags. The lack of shared schema elements complicates the creation of a unified index that can efficiently query across all entities while preserving the ability to filter or weight results by entity-specific metadata.

A naive approach involves creating separate FTS5 tables for each entity and combining results via UNION queries. However, this method introduces significant overhead due to repeated tokenization and ranking across multiple tables, leading to suboptimal performance as the dataset grows. Another strategy—flattening all entity data into a single FTS5 table with a generic content column and an entity_type discriminator—simplifies querying but sacrifices granular control over field-specific weighting (e.g., prioritizing title over body). A third option involves storing structured data (e.g., JSON) in a single FTS5 column, enabling flexible field storage but complicating query syntax and reducing compatibility with FTS5’s ranking functions like bm25().

Key challenges include:

• Schema Heterogeneity: Reconciling disparate field structures into a unified index.
• Query Efficiency: Avoiding performance degradation when searching across large datasets.
• Result Relevance: Balancing field-specific weighting (e.g., tags vs. body text) without duplicating data.
• Maintenance Overhead: Synchronizing FTS5 indexes with underlying entity tables via triggers or manual rebuilds.

For instance, a user searching for "conference" might expect matches in activity.location, note.tags, or journal.entry. Without a cohesive indexing strategy, the database may fail to surface relevant results or require complex joins that negate the benefits of FTS5.

Common Pitfalls in Schema Design and Query Performance for Unified FTS5

1. Over-Reliance on UNION Queries Across Multiple FTS5 Tables

Creating separate FTS5 tables for each entity and merging results with UNION is initially appealing but scales poorly. Each subquery must tokenize and rank its dataset independently, multiplying computational effort. For example, searching five entities with 10,000 records each requires five full FTS5 scans, whereas a unified index would tokenize all records once. Additionally, maintaining consistency across multiple FTS5 tables (via triggers or application logic) becomes error-prone as the schema evolves.

2. Flattened Schema with Generic Content Column

Storing all entity data in a single content column (e.g., concatenating title, body, and tags into one string) simplifies indexing but eliminates field-specific querying. Users cannot apply bm25() weights to prioritize title matches over body text, reducing result relevance. This approach also complicates snippet generation, as highlighted text may include irrelevant markup (e.g., Markdown syntax) if not sanitized before indexing.

3. Inadequate Handling of Structured Data

Using JSON to store entity-specific fields in a single FTS5 column introduces complexity. While JSON enables flexible field storage, FTS5 treats JSON as plain text, requiring specialized query syntax (e.g., json_extract in subqueries) to filter by nested properties. This negates FTS5’s optimizations for phrase matching and token proximity, leading to slower queries and increased development overhead.

4. Poorly Managed External Content Synchronization

FTS5’s external content feature allows indexing a view that unions multiple tables, but improper synchronization can cause index drift. For example, failing to rebuild the FTS5 index after updating source tables results in stale search results. Similarly, omitting rowid partitioning in the source view may lead to collisions (e.g., two entities with the same rowid), corrupting the index.

5. Ignoring Tokenization Overhead

FTS5’s default tokenizer includes common stopwords (e.g., "the", "and") and numeric values, which can bloat the index and slow queries. For instance, indexing Markdown content without stripping markup tokens (e.g., #, **) introduces noise, reducing query precision.

Implementing Robust Solutions for Multi-Entity Search with External Content and Efficient Indexing

Step 1: Define a Unified Schema via a Source View

Create a view that normalizes all entities into a consistent schema, using UNION ALL to combine records and rowid offsets to prevent collisions:

CREATE VIEW unified_source AS
SELECT 
  rowid AS entity_id,
  'journal' AS entity_type,
  title,
  entry AS content,
  NULL AS tags,
  date AS timestamp
FROM journals
UNION ALL
SELECT 
  rowid + 1000000000 AS entity_id,  -- Offset to avoid rowid conflicts
  'note' AS entity_type,
  subject AS title,
  body AS content,
  tags,
  created_at AS timestamp
FROM notes;

This view adapts entity-specific fields to common columns (title, content, tags), enabling seamless FTS5 indexing.

Step 2: Configure FTS5 with External Content and Column Weights

Create an FTS5 table that references the view, specifying column weights to prioritize title over content:

CREATE VIRTUAL TABLE unified_fts USING fts5(
  entity_type,
  title,
  content,
  tags,
  timestamp UNINDEXED,  -- Exclude from full-text search
  content='unified_source',
  content_rowid='entity_id',
  tokenize='porter'  -- Optional: Stemming for improved recall
);

The UNINDEXED directive excludes non-text columns like timestamp from tokenization, reducing index size.

Step 3: Synchronize the FTS5 Index with Source Data

For small datasets (≤100k records), rebuild the index periodically:

INSERT INTO unified_fts(unified_fts) VALUES ('rebuild');

For larger datasets, use triggers to update the index incrementally. Define AFTER INSERT/UPDATE/DELETE triggers on each source table to mirror changes in the FTS5 index:

CREATE TRIGGER journals_fts_insert AFTER INSERT ON journals BEGIN
  INSERT INTO unified_fts(rowid, entity_type, title, content, tags)
  VALUES (NEW.rowid, 'journal', NEW.title, NEW.entry, NULL);
END;

Step 4: Optimize Query Performance and Relevance

Use bm25() with column weights to prioritize matches in title or tags:

SELECT 
  entity_type,
  title,
  snippet(unified_fts, 2, '<b>', '</b>', '…', 16) AS preview,
  bm25(unified_fts, 10.0, 2.0) AS rank  -- Weight title 10x, content 2x
FROM unified_fts
WHERE unified_fts MATCH 'conference'
ORDER BY rank DESC
LIMIT 20;

To filter by entity type, leverage FTS5’s column filter syntax:

... WHERE unified_fts MATCH 'entity_type:note AND tags:urgent'

Step 5: Preprocess Content to Remove Noise

Before indexing, strip Markdown/HTML markup using a custom SQL function (e.g., markdown_to_text):

-- Python pseudocode for SQLite function registration
def markdown_to_text(md):
    import markdown
    html = markdown.markdown(md)
    return re.sub('<[^<]+?>', '', html)  -- Strip HTML tags

conn.create_function("markdown_to_text", 1, markdown_to_text)

Update the source view to sanitize content:

CREATE VIEW unified_source AS
SELECT 
  rowid,
  'journal' AS entity_type,
  title,
  markdown_to_text(entry) AS content,  -- Plaintext conversion
  ...

Step 6: Monitor and Optimize Index Health

Use fts5vocab to identify frequent low-value tokens (e.g., "1", "##") and exclude them via custom tokenizers:

-- Analyze token frequency
SELECT term, doc_count FROM fts5vocab('unified_fts', 'row');

Reconfigure the FTS5 table with a tokenizer that ignores numbers and Markdown syntax:

CREATE VIRTUAL TABLE unified_fts USING fts5(
  ...,
  tokenize='porter exclude "[0-9]+|#+|\\*\\*"'
);

Step 7: Handle UUIDs and Offline Synchronization

For apps using UUIDs as primary keys, hash them to 64-bit integers for FTS5 compatibility:

-- SQLite has no built-in UUID hashing; use application logic
entity_id = abs(hex_to_int(substr(uuid, 1, 8)))  -- First 8 chars as integer

Leverage SQLite’s session extension to track changes for offline-first sync:

-- Attach a session to track journal table changes
sqlite3session_attach(db, 'journals');

By adopting these strategies, developers can achieve a unified search experience that balances flexibility, performance, and maintainability, avoiding common pitfalls while leveraging SQLite’s FTS5 capabilities to their fullest.