How wacli Uses SQLite FTS5 for Full-Text Search: Implementation and Fallback Strategy

wacli implements full-text search using SQLite FTS5 with automatic fallback to LIKE queries when the extension is unavailable, using a virtual table with triggers for real-time indexing.

wacli is a command-line interface for WhatsApp that stores message history in a local SQLite database. To enable fast searching across message content, media captions, and sender information, the project leverages the FTS5 (Full-Text Search 5) extension. When FTS5 is not available in the SQLite build, the system gracefully degrades to pattern matching using LIKE queries.

FTS5 Virtual Table Architecture

The full-text search capability begins with database migration logic that attempts to create a virtual table using the FTS5 extension.

Migration and Table Creation

In internal/store/migrations.go, the function migrateMessagesFTS attempts to create the virtual table messages_fts during database initialization:

CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(
    text,
    media_caption,
    filename,
    chat_name,
    sender_name,
    display_text
);

If the SQLite build includes the fts5 module, this migration succeeds and sets an internal flag ftsEnabled = true. If the build lacks FTS5 or any step fails, the migration catches the error, sets ftsEnabled = false, and allows the application to continue with degraded search functionality.

Real-Time Indexing with Triggers

To keep the FTS index synchronized with the main messages table, internal/store/migrations.go creates SQLite triggers that execute automatically on data modifications:

CREATE TRIGGER messages_ai AFTER INSERT ON messages BEGIN
    INSERT INTO messages_fts(rowid, text, media_caption, filename,
                           chat_name, sender_name, display_text)
    VALUES (new.rowid,
            COALESCE(new.text,''), COALESCE(new.media_caption,''), COALESCE(new.filename,''),
            COALESCE(new.chat_name,''), COALESCE(new.sender_name,''), COALESCE(new.display_text,''));
END;

Analogous DELETE and UPDATE triggers ensure that the FTS index remains consistent when messages are removed or modified, eliminating the need for manual reindexing.

Search Implementation and Query Dispatch

The search logic resides in internal/store/search.go, which provides a unified API that automatically selects the optimal search strategy based on FTS5 availability.

The SearchMessages API

The public method (*DB).SearchMessages serves as the entry point for full-text queries. It accepts a SearchMessagesParams struct containing the query string, optional chat filter, and result limit. Internally, it checks the ftsEnabled flag (exposed via HasFTS()) and dispatches to either searchFTS or searchLIKE.

FTS5 Query Sanitization

To prevent FTS syntax injection, the searchFTS function sanitizes user input through sanitizeFTSQuery:

func sanitizeFTSQuery(query string) string {
    // Each token is quoted and embedded double-quotes are doubled
    // to prevent FTS-syntax injection
    tokens := strings.Fields(query)
    for i, token := range tokens {
        tokens[i] = `"` + strings.ReplaceAll(token, `"`, `""`) + `"` 将
    }
    return strings.Join(tokens, " ")
}

The sanitized query is then used with the MATCH operator against the messages_fts virtual table:

SELECT m.*, snippet(messages_fts, 0, '<<', '>>', '...', 10) as snippet
FROM messages m
JOIN messages_fts ON m.rowid = messages_fts.rowid
WHERE messages_fts MATCH ?
ORDER BY bm25(messages_fts)
LIMIT ?

Results are ordered by the BM25 relevance score, and the snippet function provides highlighted excerpts showing where matches occur.

LIKE Fallback Strategy

When FTS5 is unavailable, searchLIKE provides a safe fallback using pattern matching:

func escapeLIKE(query string) string {
    // Escapes % and _ characters so they are treated literally
    query = strings.ReplaceAll(query, `\`, `\\`)
    query = strings.ReplaceAll(query, `%`, `\%`)
    query = strings.ReplaceAll(query, `_`, `\_`)
    return query
}

The escaped query is wrapped in wildcards and executed against multiple columns:

SELECT *, text as snippet FROM messages
WHERE LOWER(text) LIKE LOWER(?) ESCAPE '\'
   OR LOWER(media_caption) LIKE LOWER(?) ESCAPE '\'
   OR LOWER(sender_name) LIKE LOWER(?) ESCAPE '\'
ORDER BY timestamp DESC
LIMIT ?

This approach scans the regular messages table and is considerably slower than the FTS5 index, but ensures search functionality remains available across all SQLite builds.

CLI Integration and User Experience

The command-line interface in cmd/wacli/messages.go integrates the search functionality and communicates the current mode to users.

Detecting FTS5 Availability

In internal/store/types.go, the DB struct exposes the HasFTS() method which returns the internal ftsEnabled boolean. This allows the CLI and other consumers to check capabilities before attempting searches.

Command-Line Interface

The wacli messages search command invokes a.DB().SearchMessages and prints a warning when FTS5 is disabled:

if !a.DB().HasFTS() {
    fmt.Fprintln(os.Stderr, "Note: FTS5 not enabled; search is using LIKE (slow).")
}
msgs, err := a.DB().SearchMessages(store.SearchMessagesParams{
    Query: c.Query,
    Limit: c.Limit,
})

Users can also verify FTS5 status through the wacli doctor command implemented in cmd/wacli/doctor.go, which reports whether the database has full-text search enabled.

Summary

• wacli automatically provisions a SQLite FTS5 virtual table named messages_fts during database migration, with triggers that maintain index synchronization.
• The SearchMessages API in internal/store/search.go transparently dispatches to either BM25-ranked FTS5 queries or case-insensitive LIKE pattern matching based on runtime capability detection.
• User input is sanitized through sanitizeFTSQuery to prevent FTS syntax injection, while escapeLIKE ensures safe fallback queries.
• The CLI warns users when operating in slower LIKE mode and provides the doctor command for capability verification.

Frequently Asked Questions

How does wacli detect if FTS5 is available?

During database initialization in internal/store/migrations.go, the code attempts to execute CREATE VIRTUAL TABLE … USING fts5. If this succeeds, the internal flag ftsEnabled is set to true; if any error occurs, it is set to false. The HasFTS() method in internal/store/types.go exposes this state to the rest of the application.

What happens if I search without FTS5 enabled?

When FTS5 is unavailable, the searchLIKE function in internal/store/search.go performs a case-insensitive substring search using SQL LIKE clauses with escaped wildcards. The CLI prints a warning: "Note: FTS5 not enabled; search is using LIKE (slow)." Results are returned in chronological order rather than by relevance.

How does wacli prevent FTS5 query injection?

The sanitizeFTSQuery function in internal/store/search.go tokenizes the input string using strings.Fields, then wraps each token in double quotes while doubling any existing double-quotes. This ensures that special FTS5 operators (like AND, OR, NOT, or NEAR) are treated as literal search terms rather than query syntax, preventing injection attacks.

Does wacli support highlighted search results?

Yes, when using FTS5, the query in searchFTS utilizes the snippet(messages_fts, ...) function to generate highlighted excerpts that mark the matching terms with << and >> delimiters. This provides context for where the search terms appear within the message content. The LIKE fallback returns the raw message text as the snippet without highlighting.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}