FTS triggers missing for automatic full-text search

[dvdsgl]

FTS Triggers Missing for Automatic Full-Text Search

Issue Summary

The messages_fts table is created during database initialization (schema_sqlite.sql), but there are no SQLite triggers to automatically populate it when new messages are inserted.

Current Behavior

• FTS table exists and is created properly
• Messages are inserted via UpsertMessage() in internal/store/messages.go
• FTS table remains empty after sync, requiring manual population
• Bare word searches fail until FTS is manually populated

Expected Behavior

Full-text search should work automatically without requiring manual FTS population.

Root Cause

The UpsertMessage() function only inserts into the messages table but doesn't populate the corresponding messages_fts virtual table. SQLite FTS tables need to be kept in sync with the source tables via triggers or explicit INSERTs.

Proposed Fix

Add SQLite triggers to automatically populate messages_fts when messages are inserted/updated/deleted:

CREATE TRIGGER messages_fts_insert_trigger
AFTER INSERT ON messages
BEGIN
    INSERT INTO messages_fts(rowid, message_id, subject, body, from_addr, to_addr, cc_addr)
    SELECT NEW.id, NEW.id, NEW.subject, 
           COALESCE((SELECT body_text FROM message_bodies WHERE message_id = NEW.id), ''),
           COALESCE((SELECT p.email_address FROM message_recipients mr JOIN participants p ON p.id = mr.participant_id WHERE mr.message_id = NEW.id AND mr.recipient_type = 'from' LIMIT 1), ''),
           COALESCE((SELECT GROUP_CONCAT(p.email_address, ' ') FROM message_recipients mr JOIN participants p ON p.id = mr.participant_id WHERE mr.message_id = NEW.id AND mr.recipient_type = 'to'), ''),
           COALESCE((SELECT GROUP_CONCAT(p.email_address, ' ') FROM message_recipients mr JOIN participants p ON p.id = mr.participant_id WHERE mr.message_id = NEW.id AND mr.recipient_type = 'cc'), '');
END;

-- Similar UPDATE and DELETE triggers needed

This ensures full-text search works immediately for new messages without requiring manual intervention.

[sinnet3000]

I ran into the same thing and honestly thought I was losing it at first.

On my end, messages_fts exists but stays empty after sync, while messages and message_bodies are populated normally. That means any FTS-backed search returns nothing until you manually backfill.

Evidence from my DB

FTS table exists

SELECT name
FROM sqlite_master
WHERE type='table' AND name='messages_fts';

Result:

messages_fts

But it is empty

SELECT count(*)
FROM messages_fts;

Result:

0

Source tables clearly have the data

SELECT count(*)
FROM message_bodies
WHERE body_text LIKE '%invoice%';

Result:

52

SELECT count(*)
FROM messages
WHERE subject LIKE '%invoice%'
   OR snippet LIKE '%invoice%';

Result:

12

So the underlying message data is clearly present, but the FTS virtual table never gets populated automatically.

From a user perspective, especially following the guide at https://www.msgvault.io, it's confusing that the documented search queries don't work out of the box even though the data is clearly there.

[wesm]

Thanks for the report — I had done "deep" searches (/ then tab) and they worked but there are clearly still rough edges. We'll get them ironed out!

[sinnet3000]

Thanks for looking into it. I am enjoying the project.