The Complete Production SOP: How to Deploy and Run sqlite-vec Fast Local Vector Search Extension in Autonomous AI Agents

---
name: "sqlite-vec-embeddings-extension"
description: "Run vector similarity search in embedded SQLite databases using fast SIMD C implementations."
triggers:
  - "use sqlite-vec extension"
  - "vector search in sqlite"
  - "create vec0 virtual table"
permissions:
  fs_read: true
  fs_write: "workspace"
---

import sqlite3
import struct
import sqlite_vec

def init_vector_db(db_path: str = "knowledge.db") -> sqlite3.Connection:
    db = sqlite3.connect(db_path)
    db.enable_load_extension(True)
    sqlite_vec.load(db)
    db.enable_load_extension(False)
    
    with db:
        db.execute("""
            CREATE VIRTUAL TABLE IF NOT EXISTS vec_documents USING vec0(
                document_id INTEGER PRIMARY KEY,
                embedding float[384]
            );
        """)
    return db

def insert_vector(db: sqlite3.Connection, doc_id: int, vector: list[float]):
    raw_bytes = struct.pack(f"{len(vector)}f", *vector)
    with db:
        db.execute(
            "INSERT INTO vec_documents(document_id, embedding) VALUES (?, ?)",
            (doc_id, raw_bytes)
        )

def search_nearest(db: sqlite3.Connection, query_vector: list[float], limit: int = 5):
    query_bytes = struct.pack(f"{len(query_vector)}f", *query_vector)
    cursor = db.cursor()
    cursor.execute("""
        SELECT document_id, distance
        FROM vec_documents
        WHERE embedding MATCH ?
        ORDER BY distance
        LIMIT ?
    """, (query_bytes, limit))
    return cursor.fetchall()