SQLite + Semantic Search — The AI Memory System

An sqlite MCP server with built-in semantic search and local embedding-vector generation

Full database + vector search + zero API costs. Everything runs locally. Everything is private. Everything is fast.

---

Benefits

1. 🧠 Semantic Search Built-In

Not just keyword matching — actual understanding. Find documents by meaning, not exact words. "Show me angry customer emails" finds them even if they never say "angry." This is the technology that powers ChatGPT's memory, now running on your machine.

2. 💰 Zero Cost, Infinite Scale

No API fees. Ever. Generate embeddings locally with the included Qwen model. Store millions of vectors. Search billions of times. Your only cost is electricity. Compare that to $0.0001 per 1K tokens with OpenAI embeddings — this tool pays for itself after the first million operations.

3. 🔒 100% Private, 100% Powerful

Your data never leaves your machine. Full SQL database + semantic search + automatic embeddings, all running locally. No cloud services. No data leaks. No compliance headaches. Just pure, private power.

---

Why This Tool Changes Everything

Most AI tools can't remember. They process your request and forget. Every conversation starts from zero.

Vector databases cost money. Pinecone, Weaviate, Qdrant — all excellent, all expensive. $70/month minimum, scaling to thousands for production workloads.

Semantic search requires APIs. OpenAI embeddings, Cohere, Anthropic — all charge per use. Build something popular and watch costs explode.

This tool breaks all those limitations.

Full SQLite database. Vector similarity search. Automatic embedding generation. All local. All free. All private. All integrated into one elegant tool.

---

Real-World Story: The Support Ticket Crisis

The Problem:

A small SaaS company was drowning in support tickets. 500+ per day. Customers asking the same questions repeatedly. Support team couldn't keep up.

"Can we use AI to auto-respond?" they asked.

Standard solution: Vector database ($200/month) + OpenAI embeddings ($500/month in API costs) + custom integration (weeks of development).

With This Tool:

# One-time setup: Import existing tickets
execute_sql("""
  CREATE TABLE tickets(
    id INTEGER PRIMARY KEY,
    question TEXT,
    answer TEXT,
    embedding BLOB CHECK(vec_length(embedding) == 1024)
  )
""")

execute_sql("""
  INSERT INTO tickets(question, answer, embedding)
  SELECT question, answer, vec_f32(generate_embedding(question))
  FROM imported_tickets
""")

# For each new ticket, find similar solved tickets
execute_sql("""
  SELECT question, answer, 
         vec_distance_cosine(embedding, vec_f32(:new_ticket)) as similarity
  FROM tickets
  WHERE similarity < 0.3
  ORDER BY similarity LIMIT 3
""", bindings={"new_ticket": {"_embedding_text": user_question}})

Result: 70% of tickets auto-resolved. Support team focused on complex issues. Zero monthly costs. Complete privacy. Deployed in one afternoon.

The kicker: They're now using the same system for product recommendations, content search, and duplicate detection. Same tool, zero additional cost.

---

The Complete Feature Set

Core Database Operations

Full SQLite Power:

• Complete SQL support (SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, etc.)
• Transactions, triggers, views, indexes
• Foreign keys, constraints, CHECK clauses
• Common Table Expressions (CTEs), window functions
• JSON functions, date/time operations
• PRAGMA settings for performance tuning

Smart Path Handling:

• :memory: — Shared temporary database (persists until server restart)
• @user_data/db.sqlite — Primary storage (syncs on Windows domains)
• @user_local/db.sqlite — Machine-specific (never syncs)
• @user_cache/db.sqlite — Temporary (system may clear)
• @user_config/db.sqlite — Settings/config
• @site_data/db.sqlite — Multi-user shared (needs elevation)
• @temp/db.sqlite — System temp (cleared on reboot)
• ~/databases/app.db — Home directory expansion
• %APPDATA%\app.db — Windows environment variables
• /absolute/path/app.db — Full paths work everywhere

Vector Similarity Search

Create Vector-Enabled Tables:

CREATE TABLE documents(
  id INTEGER PRIMARY KEY,
  title TEXT,
  content TEXT,
  embedding BLOB CHECK(
    typeof(embedding) == 'blob'
    AND vec_length(embedding) == 1024  -- Qwen embeddings are 1024-dimensional
  )
);

Automatic Embedding Generation:

Method 1: SQL Function (Recommended)

-- Insert with automatic embedding
INSERT INTO documents(title, content, embedding)
VALUES (
  'User Guide',
  'How to use the application...',
  vec_f32(generate_embedding('How to use the application...'))
);

-- Bulk update missing embeddings
UPDATE documents
SET embedding = vec_f32(generate_embedding(content))
WHERE embedding IS NULL;

Method 2: Binding Reference (When text is in database)

execute_sql(
    "INSERT INTO docs(text, embedding) VALUES (:text, vec_f32(:emb))",
    bindings={
        "text": "Document content here",
        "emb": {"_embedding_col": "text"}  # Embeds the :text value
    }
)

Method 3: Direct Text (When text not stored)

execute_sql(
    "INSERT INTO metadata(category, embedding) VALUES (:cat, vec_f32(:emb))",
    bindings={
        "cat": "technical",
        "emb": {"_embedding_text": "Technical documentation category"}
    }
)

Similarity Search:

-- Find similar documents
SELECT 
  title, 
  content,
  vec_distance_cosine(embedding, vec_f32(:query)) as similarity
FROM documents
WHERE vec_distance_cosine(embedding, vec_f32(:query)) < 0.5
ORDER BY similarity
LIMIT 10;

Distance Functions:

• vec_distance_cosine(v1, v2) → 0-1 (lower = more similar, best for text)
• vec_distance_L2(v1, v2) → 0-∞ (Euclidean distance)
• vec_distance_L1(v1, v2) → 0-∞ (Manhattan distance)

Authentication Integration

Built-in User Context:

-- Automatically includes authenticated username
SELECT * FROM user_documents 
WHERE owner = :authenticated_user;

-- Audit trail with automatic user tracking
INSERT INTO actions (user, action, timestamp)
VALUES (:authenticated_user, :action, datetime('now'));

Why this matters: No need to pass usernames around. The tool knows who's calling it and provides that context automatically.

Dot Commands (Convenience Features)

Quick database inspection without writing SQL:

• .databases — List all .db files with sizes and dates
• .tables — Show all tables in current database
• .schema [table] — Display table schema
• .indexes [table] — Show indexes
• .fullschema — Complete database schema
• .dbinfo — Database statistics
• .status — Current settings
• .pragma — All PRAGMA values
• .foreign_keys — Foreign key status

Note: Standard SQL is preferred for production use. Dot commands are for quick exploration.

PRAGMA Support

Full control over SQLite behavior:

PRAGMA foreign_keys = ON;      -- Enable referential integrity
PRAGMA journal_mode = WAL;     -- Write-Ahead Logging for concurrency
PRAGMA synchronous = NORMAL;   -- Balance safety and speed
PRAGMA cache_size = -2000;     -- 2MB cache
PRAGMA page_size;              -- Query page size
PRAGMA encoding;               -- Database encoding

---

Advanced Use Cases

Semantic Duplicate Detection

-- Find potential duplicates
WITH candidates AS (
  SELECT 
    d1.id as id1, 
    d2.id as id2,
    d1.title as title1,
    d2.title as title2,
    vec_distance_cosine(d1.embedding, d2.embedding) as similarity
  FROM documents d1
  JOIN documents d2 ON d1.id < d2.id
  WHERE similarity < 0.1  -- Very similar
)
SELECT * FROM candidates
ORDER BY similarity;

Multi-Field Semantic Search

-- Search across title and content with different weights
WITH query_emb AS (
  SELECT vec_f32(generate_embedding(:query)) as emb
)
SELECT 
  title,
  content,
  (
    0.7 * vec_distance_cosine(title_embedding, query_emb.emb) +
    0.3 * vec_distance_cosine(content_embedding, query_emb.emb)
  ) as weighted_similarity
FROM documents, query_emb
WHERE weighted_similarity < 0.5
ORDER BY weighted_similarity
LIMIT 20;

Hierarchical Similarity (Find Similar to Similar)

-- Find documents similar to documents similar to a query
WITH first_pass AS (
  SELECT id, embedding
  FROM documents
  WHERE vec_distance_cosine(embedding, vec_f32(:query)) < 0.3
  LIMIT 10
)
SELECT DISTINCT d.id, d.title
FROM documents d
JOIN first_pass fp 
  ON vec_distance_cosine(d.embedding, fp.embedding) < 0.4
WHERE d.id NOT IN (SELECT id FROM first_pass)
ORDER BY vec_distance_cosine(d.embedding, vec_f32(:query))
LIMIT 20;

Time-Weighted Semantic Search

-- Prioritize recent + relevant
SELECT 
  title,
  created_at,
  vec_distance_cosine(embedding, vec_f32(:query)) as relevance,
  (julianday('now') - julianday(created_at)) as age_days,
  (
    0.7 * vec_distance_cosine(embedding, vec_f32(:query)) +
    0.3 * (1.0 / (1.0 + (julianday('now') - julianday(created_at)) / 365.0))
  ) as score
FROM documents
WHERE relevance < 0.5
ORDER BY score
LIMIT 10;

---

Usage Examples

Basic SQL Query

{
  "input": {
    "sql": "SELECT * FROM users WHERE age > :min_age",
    "database": "myapp.db",
    "bindings": {"min_age": 18},
    "tool_unlock_token": "YOUR_TOKEN"
  }
}

User-Specific Query

{
  "input": {
    "sql": "SELECT * FROM documents WHERE owner = :authenticated_user",
    "database": "@user_data/docs.db",
    "tool_unlock_token": "YOUR_TOKEN"
  }
}

Semantic Search

{
  "input": {
    "sql": "SELECT title, vec_distance_cosine(embedding, vec_f32(:query)) as sim FROM docs WHERE sim < 0.5 ORDER BY sim LIMIT 5",
    "database": "knowledge.db",
    "bindings": {
      "query": {"_embedding_text": "How do I reset my password?"}
    },
    "tool_unlock_token": "YOUR_TOKEN"
  }
}

Bulk Embedding Generation

{
  "input": {
    "sql": "UPDATE articles SET embedding = vec_f32(generate_embedding(content)) WHERE embedding IS NULL",
    "database": "@user_data/articles.db",
    "tool_unlock_token": "YOUR_TOKEN"
  }
}

---

Return Format

{
  "operation_was_successful": true,
  "error_message_if_operation_failed": null,
  "rows_modified_by_operation": 5,
  "column_names_in_result_set": ["id", "title", "similarity"],
  "data_rows_from_result_set": [
    {"id": 1, "title": "Getting Started", "similarity": 0.12},
    {"id": 5, "title": "Quick Start Guide", "similarity": 0.18},
    ...
  ]
}

Key fields:

• operation_was_successful — Boolean success indicator
• error_message_if_operation_failed — Null on success, error string on failure
• rows_modified_by_operation — For INSERT/UPDATE/DELETE, null for SELECT
• column_names_in_result_set — Array of column names, null for non-SELECT
• data_rows_from_result_set — Array of row objects, null for non-SELECT

---

Technical Architecture

Memory Database (:memory:)

Shared Singleton Pattern:

• Single connection shared across all operations
• Thread-safe with locking mechanism
• WAL (Write-Ahead Logging) mode enabled
• Persists for entire server lifetime
• Shared across all AI instances

Why this matters: Multiple AI agents can collaborate using the same in-memory database. Perfect for session state, temporary calculations, or inter-agent communication.

File Databases

Per-Operation Connections:

• New connection for each operation
• Automatic connection caching per database
• Auto-creates parent directories
• V