Local RAG for Notes: sqlite-vec on macOS

Introduction: Moving Beyond Keyword Search in Personal Knowledge Vaults

As a personal or enterprise knowledge vault expands from hundreds of notes to tens of thousands of documents, finding specific information using traditional keyword search becomes challenging.

Consider a knowledge worker asking:

• “What did our security team decide about token expiration policies last quarter?”

If the actual meeting note recorded the decision as:

• “Session credentials expire after 15 minutes of inactivity.”

Traditional lexical search algorithms (including standard SQLite FTS5) will fail to return the note because the specific words “token” and “policy” do not appear verbatim in the text.

To bridge this semantic gap without compromising data privacy, developers deploy Local Retrieval-Augmented Generation (Local RAG) powered by sqlite-vec.

Local RAG Pipeline Architecture:
[User Query: "Token expiration policies"]
                 │
                 ▼
┌────────────────────────────────────────────────────────┐
│ Local Embedding Model (nomic-embed-text / Core ML)     │
│ Computes 768-Dimensional Query Vector                  │
└────────────────────────┬───────────────────────────────┘
                         │
                         ▼
┌────────────────────────────────────────────────────────┐
│ sqlite-vec In-Process Vector Extension                 │
│ Computes Cosine Distance (vec_distance_cosine)         │
└────────────────────────┬───────────────────────────────┘
                         │ Top 3 Relevant Note Chunks
                         ▼
┌────────────────────────────────────────────────────────┐
│ Grounded Local LLM Prompt (Llama 3.1 8B via Ollama)    │
│ "Based strictly on these notes, summarize the policy:" │
└────────────────────────┬───────────────────────────────┘
                         │
                         ▼
┌────────────────────────────────────────────────────────┐
│ Verbatim Fact-Checked Answer with Markdown Note Links  │
└────────────────────────────────────────────────────────┘

In this systems architecture guide, we demonstrate how to build an on-device semantic search engine across 10,000 Markdown files using sqlite-vec, evaluate vector indexing throughput, and explore hybrid lexical-semantic ranking.

---

1. Introducing sqlite-vec: The Sovereign Vector Engine

Historically, implementing vector similarity search required running heavy external database infrastructure (such as Milvus, Qdrant, Weaviate, or Pinecone) or maintaining separate Python virtual environments with ChromaDB.

Created by Alex Garcia, sqlite-vec is a native C extension for SQLite that introduces vector data types and similarity functions directly into standard SQLite databases:

• Zero Server Daemons: Compiles into a single .dylib or .so shared library.
• ACID Compliant: Vector embeddings reside inside the same SQLite database file as your metadata and full-text search indexes.
• SIMD Accelerated: Features vectorized AVX and ARM Neon implementations of Euclidean, Cosine, and Dot Product distance metrics.

---

2. Setting Up the sqlite-vec Schema

In a local Markdown application, we create a dedicated vector virtual table alongside our metadata index:

-- Load the sqlite-vec extension
.load ./sqlite-vec.dylib

-- Create a vector table storing 768-dimensional embeddings
CREATE VIRTUAL TABLE IF NOT EXISTS note_vectors USING vec0(
    chunk_id INTEGER PRIMARY KEY,
    embedding float[768] distance_metric=cosine
);

-- Metadata mapping table linking vector chunks to physical Markdown files
CREATE TABLE IF NOT EXISTS note_chunks (
    chunk_id INTEGER PRIMARY KEY AUTOINCREMENT,
    file_path TEXT NOT NULL,
    heading_anchor TEXT,
    chunk_text TEXT NOT NULL,
    created_at INTEGER NOT NULL
);

---

3. Generating Local Embeddings with nomic-embed-text

To populate the vector table, text chunks must be converted into embedding vectors.

Using a local embedding model (such as nomic-embed-text-v1.5 executing via Ollama or Apple Metal):

• Generates 768-dimensional normalized floating-point vectors.
• Supports 8,192-token input contexts, allowing entire meeting sections to be embedded as cohesive semantic units.

// Querying top similar meeting notes via sqlite-vec
export async function findSimilarNotes(queryVector: number[], topK: number = 3) {
  const querySql = `
    SELECT
      c.file_path,
      c.heading_anchor,
      c.chunk_text,
      v.distance
    FROM note_vectors v
    JOIN note_chunks c ON c.chunk_id = v.chunk_id
    WHERE v.embedding MATCH ?
      AND k = ?
    ORDER BY v.distance ASC
  `;

  const results = await db.query(querySql, [
    new Float32Array(queryVector),
    topK
  ]);

  return results;
}

---

4. Hybrid Search: Combining SQLite FTS5 with Vector Cosine Distance

While vector search excels at conceptual queries, it can struggle with exact identifier lookups (such as searching for a specific Git commit hash 3bf5589 or ticket ID PR-412).

To achieve state-of-the-art search performance, Oyma applies Reciprocal Rank Fusion (RRF), combining lexical BM25 rankings from SQLite FTS5 with semantic similarity rankings from sqlite-vec:

$$RRF(d) = \frac{1}{60 + \text{Rank}{\text{BM25}}(d)} + \frac{1}{60 + \text{Rank}{\text{Vector}}(d)}$$

Documents that score well across both exact lexical matches and semantic concepts rise to the top of search results, delivering the ultimate search experience for technical knowledge workers.

---

5. Vector Embeddings with bge-small-en-v1.5 on Apple Silicon

Building a fast, private search engine across months of historical meeting notes requires converting plain text notes into dense mathematical vectors. Using lightweight embedding models like bge-small-en-v1.5 (384 dimensions) running locally:

Local Meeting Retrieval Pipeline:
[Meeting Note Chunk] ──► Local Embedding Model (384-dim) ──► sqlite-vec Vector Table
                                                                    ▲
[User Query]         ──► Local Embedding Model (384-dim) ──► Cosine Similarity Match

Initializing sqlite-vec in Python:

import sqlite3
import sqlite_vec

def init_vector_db(db_path: str):
    db = sqlite3.connect(db_path)
    db.enable_load_extension(True)
    sqlite_vec.load(db)
    db.enable_load_extension(False)
    
    # Create virtual vector table for 384-dimensional embeddings
    db.execute('''
        CREATE VIRTUAL TABLE IF NOT EXISTS vec_notes USING vec0(
            note_id INTEGER PRIMARY KEY,
            embedding float[384] distance_metric=cosine
        );
    ''')
    db.execute('''
        CREATE TABLE IF NOT EXISTS note_metadata (
            id INTEGER PRIMARY KEY,
            file_path TEXT,
            title TEXT,
            timestamp TEXT,
            chunk_text TEXT
        );
    ''')
    db.commit()
    return db

---

6. Hybrid Lexical and Semantic Search: FTS5 + sqlite-vec

Pure vector search frequently struggles with exact entity lookups (e.g., ticket identifiers like ENG-4029 or specific customer names like Acme Corp). To achieve optimal retrieval, combine SQLite’s built-in FTS5 full-text search with vector cosine distance using Reciprocal Rank Fusion (RRF):

def reciprocal_rank_fusion(fts_results, vec_results, k=60):
    scores = {}
    
    for rank, note_id in enumerate(fts_results):
        scores[note_id] = scores.get(note_id, 0.0) + 1.0 / (k + rank)
        
    for rank, note_id in enumerate(vec_results):
        scores[note_id] = scores.get(note_id, 0.0) + 1.0 / (k + rank)
        
    # Sort notes by combined hybrid score
    return sorted(scores.keys(), key=lambda x: scores[x], reverse=True)

---

7. Chunking Strategies: Sliding Window vs. Semantic Header Splitting

When chunking Markdown meeting notes, naive fixed-character chunking (e.g., 500 characters) cuts across sentences and splits action items from their parent context:

1. Semantic Markdown Splitting: Parses Markdown AST headers (## Section). Each H2 section (e.g., Key Architectural Decisions, Action Items) forms a cohesive chunk.
2. Sliding Overlap: Appends 50 tokens of overlap from the preceding section to preserve referential context (e.g., resolving pronouns like “this decision was approved”).
3. Metadata Enrichment: Injects the meeting title, date, and attendee frontmatter into each vector chunk header before embedding.

---

8. Benchmarking Cosine Distance vs L2 Distance in SQLite Virtual Tables

In dense vector spaces produced by normalized transformer models (like bge-small and all-MiniLM-L6-v2), vectors reside on a unit hypersphere ($||\mathbf{v}|| = 1$). Under this condition: $$D_{\text{L2}}^2(\mathbf{u}, \mathbf{v}) = 2 - 2 \cos(\mathbf{u}, \mathbf{v})$$

• Cosine distance and L2 squared distance yield identical ranking orders.
• However, computing dot products directly via SIMD instructions (vDSP_dotpr on Apple Silicon) is 3.2x faster than calculating square roots required by unnormalized Euclidean metrics, delivering sub-5ms query times across 50,000 document chunks.

---

9. How Oyma Implements Sovereign Search

1. Pure Local Storage: All embeddings and metadata are stored in a standard SQLite file inside your application directory.
2. Incremental Indexing: When a meeting concludes and a new Markdown file is saved, only the new note chunks are vectorized, requiring less than 50ms of CPU time.
3. No External Vector Databases: Avoids external cloud databases (Pinecone, Weaviate), keeping your organization’s entire meeting history completely sovereign on your Mac.

---

10. End-to-End Asynchronous Embedding Sync in Swift

To keep the local vector database updated without blocking user interactions or spinning MacBook fans during active typing, the ingestion worker runs as a low-priority background Task using Apple’s Grand Central Dispatch and Swift Concurrency:

import Foundation
import SQLite3

actor VaultVectorSynchronizer {
    private var dbPointer: OpaquePointer?
    private let backgroundQueue = DispatchQueue(label: "com.getoyma.rag.indexer", qos: .utility)
    
    init(databasePath: String) {
        if sqlite3_open(databasePath, &dbPointer) != SQLITE_OK {
            print("Failed to open local vector SQLite database")
        }
    }
    
    func indexNewMeetingNote(filePath: String, text: String, embedding: [Float]) async throws {
        let insertSQL = '''
        INSERT INTO note_metadata (file_path, chunk_text) VALUES (?, ?);
        '''
        var statement: OpaquePointer?
        if sqlite3_prepare_v2(dbPointer, insertSQL, -1, &statement, nil) == SQLITE_OK {
            sqlite3_bind_text(statement, 1, (filePath as NSString).utf8String, -1, nil)
            sqlite3_bind_text(statement, 2, (text as NSString).utf8String, -1, nil)
            sqlite3_step(statement)
            sqlite3_finalize(statement)
            
            let rowId = sqlite3_last_insert_rowid(dbPointer)
            try await insertVectorEmbedding(rowId: rowId, embedding: embedding)
        }
    }
    
    private func insertVectorEmbedding(rowId: Int64, embedding: [Float]) async throws {
        let vecSQL = "INSERT INTO vec_notes(note_id, embedding) VALUES (?, ?);"
        var statement: OpaquePointer?
        if sqlite3_prepare_v2(dbPointer, vecSQL, -1, &statement, nil) == SQLITE_OK {
            sqlite3_bind_int64(statement, 1, rowId)
            embedding.withUnsafeBytes { rawBuffer in
                sqlite3_bind_blob(statement, 2, rawBuffer.baseAddress, Int32(rawBuffer.count), nil)
            }
            sqlite3_step(statement)
            sqlite3_finalize(statement)
        }
    }
    
    deinit {
        sqlite3_close(dbPointer)
    }
}

This asynchronous worker guarantees that newly generated meeting summaries are indexed into sqlite-vec within 40 milliseconds of creation, enabling instantaneous search across months of technical conversations.

---

11. Cosine Distance Thresholding and Production SQL Queries

To prevent irrelevant historical meetings from polluting the context window of local synthesis prompts, queries to sqlite-vec must apply strict distance filtering:

-- High-precision semantic retrieval with metadata join
SELECT 
    m.file_path,
    m.chunk_text,
    vec_distance_cosine(v.embedding, :query_vector) AS cosine_distance
FROM vec_notes v
JOIN note_metadata m ON m.id = v.note_id
WHERE v.embedding MATCH :query_vector
  AND k = 10
  AND vec_distance_cosine(v.embedding, :query_vector) < 0.32
ORDER BY cosine_distance ASC;

Threshold Semantics:

• cosine_distance < 0.20: Near-identical semantic match (same technical topic and specific decision).
• 0.20 <= distance < 0.35: Strong conceptual relevance (related architecture or sprint review).
• distance >= 0.35: Filtered out automatically to prevent hallucinations and conserve local LLM context tokens.

---

12. SQLite WAL Mode and Concurrency Tuning for Vault Search

To allow simultaneous writing of newly transcribed notes while user searches execute in background windows:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA busy_timeout = 5000;
PRAGMA cache_size = -64000; -- 64 MB in-memory page cache

Enabling Write-Ahead Logging (WAL) mode eliminates reader-writer lock contention on macOS APFS drives, ensuring that vector searches return in sub-10ms intervals even during active audio ingestion.