feat: the SQLite lecture library has no search index on the transcript text field — full-text search across large transcripts (1-hour lectures) performs a full table scan on every keystroke

[prince-pokharna]

📊 Problem Statement

Knowte's README describes:

Lecture Library — Persistent SQLite-backed history of all lectures with full-text search.

SQLite supports full-text search via the FTS5 virtual table extension. However, if the lectures table stores transcripts as a regular TEXT column without an FTS5 index, every search query triggers a full table scan (LIKE '%query%').

For a user with 50 one-hour lectures (each with ~10,000 words of transcript), this scan processes ~500,000 words per keystroke — causing noticeable lag on lower-end machines, which are exactly the target audience for an offline-first app.

💡 Proposed Fix

Add an FTS5 virtual table that mirrors the lectures content:

-- Migration: add FTS5 full-text search index

-- Create FTS5 virtual table
CREATE VIRTUAL TABLE IF NOT EXISTS lectures_fts USING fts5(
    title,
    transcript,
    content='lectures',
    content_rowid='id'
);

-- Populate from existing lectures
INSERT INTO lectures_fts(rowid, title, transcript)
SELECT id, title, transcript FROM lectures;

-- Keep FTS index in sync via triggers
CREATE TRIGGER lectures_ai AFTER INSERT ON lectures BEGIN
  INSERT INTO lectures_fts(rowid, title, transcript) VALUES (new.id, new.title, new.transcript);
END;

CREATE TRIGGER lectures_ad AFTER DELETE ON lectures BEGIN
  INSERT INTO lectures_fts(lectures_fts, rowid, title, transcript)
  VALUES ('delete', old.id, old.title, old.transcript);
END;

CREATE TRIGGER lectures_au AFTER UPDATE ON lectures BEGIN
  INSERT INTO lectures_fts(lectures_fts, rowid, title, transcript)
  VALUES ('delete', old.id, old.title, old.transcript);
  INSERT INTO lectures_fts(rowid, title, transcript) VALUES (new.id, new.title, new.transcript);
END;

Update the Rust search query:

// src-tauri/src/db/lectures.rs

pub fn search_lectures(conn: &Connection, query: &str) -> Result<Vec<LectureSummary>, Error> {
    let mut stmt = conn.prepare(
        "SELECT l.id, l.title, l.created_at,
                snippet(lectures_fts, 1, '<b>', '</b>', '...', 20) as excerpt
         FROM lectures_fts
         JOIN lectures l ON lectures_fts.rowid = l.id
         WHERE lectures_fts MATCH ?1
         ORDER BY rank
         LIMIT 50"
    )?;
    // ...
}

📁 Files to Modify

File	Change
src-tauri/src/db/	Add FTS5 migration + updated search query
src-tauri/src/db/lectures.rs	Replace LIKE search with FTS5 MATCH
src/	Display excerpt snippet in search results

Suggested labels: performance, database, tauri

I would like to work on this. Could you please assign it to me?

[prince-pokharna]

Hey @maintainers,
Please assign it to me under GSSoC 2026
/claim /assign /claim-assignment