FTS: Migrate content from files to DB and implement full-text search via Turso FTS (Tantivy)

[Fizzizist]

Summary

Migrate note, reflection, and summary content from on-disk .md files into a content table in the Turso database, then implement full-text search using Turso's FTS extension (powered by Tantivy).

Motivation

Currently, note/reflection/summary content lives in .md files on disk (root_dir/YYYY/MM/DD/{uuid}.md). The database only stores metadata (file_path, timestamps, relationship pointers). There is no way to search across content — only filter by entity ID, tag, or time range. The use case: "months later, I vaguely remember writing about X" requires full-text search over actual content.

Turso's FTS extension (powered by Tantivy) provides BM25 ranking, tokenized matching, boolean/phrase/prefix queries, and snippet highlighting — all via SQL functions (fts_match, fts_score, fts_highlight). This requires content to be in a table column.

Proposed Architecture

New content table

CREATE TABLE IF NOT EXISTS content (
    content_id   uuid PRIMARY KEY,
    entity_id    uuid NOT NULL,
    entity_type  text NOT NULL,  -- 'reflection' | 'note' | 'summary'
    body         text NOT NULL,
    created_at   timestamp NOT NULL,
    updated_at   timestamp NOT NULL
) STRICT;

CREATE INDEX idx_content_fts ON content USING fts (body)
    WITH (tokenizer = 'default');

Schema changes to existing tables

• reflection: drop file_path, no replacement column needed (content linked via entity_id on content table)
• note: drop file_path, same
• summary: drop file_path, same

Editor workflow changes

$EDITOR still operates on files. The create_and_edit / edit functions in editor.rs become responsible for temp file lifecycle:

1. create() → inserts DB row + content row (empty body)
2. Write current content to a temp file
3. editor_fn(&temp_path) → suspends terminal, opens $EDITOR on temp file (no change to EditorFn signature)
4. Read temp file content → update content.body in DB
5. cleanup() → checks if body is empty, deletes entity if so, syncs tags from content string if not
6. Delete temp file

Service changes

• ReflectionService, NoteService, SummaryService lose root_dir: PathBuf entirely
• TimelineService loses root_dir — read_file_content() becomes a DB query against content
• tag::sync_tags_from_file becomes tag::sync_tags_from_content — takes &str instead of &Path
• EditableEntityRecord trait: file_path() removed or replaced with content_id()
• EditableEntity trait: full_path() removed

New SearchService

pub struct SearchService {
    db_path: PathBuf,
}

pub struct SearchResult {
    entity_id: Uuid,
    entity_type: String,
    score: f64,
    snippet: String,
    created_at: DateTime<Utc>,
}

Query:

SELECT entity_id, entity_type,
       fts_score(body, ?) AS score,
       fts_highlight(body, '<b>', '</b>', ?) AS snippet,
       created_at
FROM content
WHERE fts_match(body, ?)
ORDER BY score DESC
LIMIT 50;

CLI

reflect search "query terms" → JSON array of SearchResult. This is the AI-agent-friendly path — an agent shells out, gets JSON, picks the relevant entity, then reads full content or opens the timeline.

TUI

New SearchView (full-view replacement like TimelineView / SummaryView) displaying ranked results with highlighted snippets. Selecting one opens the file in the editor or jumps to the entity timeline.

Migration

Existing .md files must be migrated into the content table:

1. Iterate all rows in reflection, note, summary
2. For each row, read the .md file at root_dir.join(file_path)
3. Insert into content with matching entity_id and entity_type
4. Drop file_path columns from entity tables

This should be a one-time migration, likely triggered on startup with a schema version check (the schema_versions table already exists for this purpose).

What we lose

• Direct ripgrep / file-system access to content
• Git versioning of individual content files
• Mental model of "my notes are files in a directory"

What we gain

• Full-text search with BM25 ranking, tokenization, boolean/phrase/prefix queries
• Single source of truth (one .db file)
• No root_dir threading through services
• No file I/O for content reads
• Atomic content updates in transactions
• Simpler backup (one file)
• Foundation for vector/semantic search (#TBD)

Spike needed

Validate that turso 0.6.1 (the Rust crate) supports the FTS extension. Write a quick test: open Database::open_in_memory(), create a table with an FTS index, insert a row, run fts_match. If FTS is not available, may need to load_extension('fts') at startup or upgrade the crate version.

References

• Turso FTS docs
• Turso extensions docs