Database Schema for a QMD Index: Complete SQLite Structure Explained

QMD uses a single SQLite file with seven core tables—content, documents, content_vectors, vectors_vec, llm_cache, documents_fts, and collections—to store file metadata, content-addressable document text, vector embeddings, and full-text search indexes.

The tobi/qmd repository implements a local-first semantic search engine that indexes your documents using SQLite. Understanding the database schema for a QMD index is essential for debugging, extending functionality, or performing manual data migrations. All schema definitions reside in src/store.ts, with initialization occurring automatically when you call createStore().

Core Tables in the QMD Database Schema

content: Content-Addressable Storage

The content table provides deduplicated storage of raw document text using SHA-256 hashing.

Column	Type	Purpose
hash	TEXT PRIMARY KEY	SHA-256 hash of the document body
doc	TEXT NOT NULL	The full raw document text
created_at	TEXT NOT NULL	ISO timestamp of first insertion

This table is referenced by foreign keys in both the documents and content_vectors tables. By storing content separately from metadata, QMD ensures that identical files across different paths or collections only consume storage once.

documents: File Metadata and Indexing

The documents table tracks every indexed file with soft-delete support and collection grouping.

Column	Type	Purpose
id	INTEGER PRIMARY KEY AUTOINCREMENT	Surrogate key for internal references
collection	TEXT NOT NULL	Logical grouping (e.g., git repo name)
path	TEXT NOT NULL	Relative path within the collection
title	TEXT NOT NULL	Document title extracted from frontmatter or filename
hash	TEXT NOT NULL	Foreign key to content(hash)
created_at	TEXT NOT NULL	First index time
modified_at	TEXT NOT NULL	Last file modification time
active	INTEGER NOT NULL DEFAULT 1	Soft-delete flag (0 = inactive)

A unique constraint on (collection, path) prevents duplicate entries, while the active column allows QMD to mark files as removed without deleting their history or content.

content_vectors and vectors_vec: Vector Embeddings

When vector search is enabled, QMD stores embedding metadata and actual vector data in two related structures.

The content_vectors table tracks chunk-level embedding metadata:

Column	Type	Purpose
hash	TEXT NOT NULL	Content hash (part of composite PK)
seq	INTEGER NOT NULL DEFAULT 0	Chunk sequence number (part of composite PK)
pos	INTEGER NOT NULL DEFAULT 0	Character offset in original document
model	TEXT NOT NULL	Embedding model identifier
embedded_at	TEXT NOT NULL	Timestamp of embedding generation

The vectors_vec table is a sqlite-vec virtual table that stores the actual float arrays:

Column	Type	Purpose
hash_seq	TEXT PRIMARY KEY	Composite key ${hash}_${seq} linking to content_vectors
embedding	FLOAT[dim] distance_metric=cosine	Actual vector embedding array

This separation allows QMD to perform metadata filtering on content_vectors while delegating similarity calculations to the optimized sqlite-vec extension.

llm_cache: LLM Result Caching

The llm_cache table prevents redundant API calls for expensive operations like query expansion or reranking.

Column	Type	Purpose
hash	TEXT PRIMARY KEY	Hash of the LLM request parameters
result	TEXT NOT NULL	Serialized LLM response
created_at	TEXT NOT NULL	Cache entry timestamp

Entries are keyed by a hash of the request details, ensuring identical prompts return cached results instantly.

Full-Text Search with FTS5

documents_fts Virtual Table

QMD implements full-text search using SQLite's FTS5 virtual table. The schema exposes three searchable columns:

• filepath: Concatenation of collection || '/' || path
• title: Document title
• body: Document content (pulled from the content table via trigger)

FTS5 uses Porter stemming and Unicode tokenization by default, enabling fuzzy matching on English text and proper handling of international characters.

Synchronization Triggers

Three triggers maintain consistency between documents and documents_fts:

• documents_ai (after insert): Automatically inserts a new row into documents_fts with the concatenated filepath, title, and document body.
• documents_au (after update): Handles modifications including soft-delete activation. When active changes to 0, the trigger removes the FTS entry; when content changes, it updates the FTS row.
• documents_ad (after delete): Removes the corresponding entry from documents_fts to prevent orphaned search results.

These triggers ensure that full-text search results always reflect the current state of the documents table without requiring manual index management.

Database Indices for Performance

QMD creates three strategic indices to optimize common query patterns:

• idx_documents_collection: Composite index on (collection, active) enabling fast filtering of active documents within a specific collection.
• idx_documents_hash: Index on hash facilitating efficient joins between documents and content tables during content retrieval.
• idx_documents_path: Composite index on (path, active) supporting glob-style path queries with soft-delete awareness.

These indices ensure sub-millisecond lookups even with thousands of indexed documents.

Schema Migration History

Earlier versions of QMD used a normalized schema with a separate collections table referenced by collection_id in the documents table. The current schema denormalizes this relationship, storing the collection name directly as a TEXT column.

The migrate-schema.ts script handles this transition:

1. Adds a collection TEXT column to the documents table
2. Populates the new column by joining with the legacy collections table
3. Rebuilds the documents table without the collection_id foreign key
4. Recreates the FTS triggers to reference the new collection column
5. Drops the obsolete collections table

Run the migration manually with:

bun migrate-schema.ts

Working with the Schema: Code Examples

Creating a Store and Initializing the Schema

When you call createStore(), QMD automatically opens the SQLite file and executes the schema initialization defined in src/store.ts:

import { createStore } from "./src/store";

const store = createStore();  // Default path: ~/.cache/qmd/index.sqlite
// Database schema is now ready for indexing

The initializeDatabase() function creates all tables, indices, and triggers if they don't exist.

Manually Inserting Documents

This example demonstrates the foreign key relationships between content and documents:

import { hashContent } from "./src/store";
import { getDefaultDbPath, openDatabase } from "./src/db";

async function addDocument(
  collection: string, 
  path: string, 
  title: string, 
  body: string
) {
  const db = openDatabase(getDefaultDbPath());
  const hash = await hashContent(body);
  const now = new Date().toISOString();

  // Insert into content-addressable storage
  db.prepare(`
    INSERT OR IGNORE INTO content (hash, doc, created_at)
    VALUES (?, ?, ?)
  `).run(hash, body, now);

  // Insert document metadata
  db.prepare(`
    INSERT INTO documents 
      (collection, path, title, hash, created_at, modified_at, active)
    VALUES (?, ?, ?, ?, ?, ?, 1)
  `).run(collection, path, title, hash, now, now);
}

Checking Index Health

Query the schema to identify documents needing embeddings:

import { createStore } from "./src/store";

const store = createStore();
const health = store.getIndexHealth();
console.log(health);
// Output: { needsEmbedding: number, totalDocs: number, daysStale: number }

This method internally joins documents with content_vectors to find hashes lacking embeddings.

Running Schema Migrations

Upgrade from legacy schema versions:

# From repository root

bun migrate-schema.ts

This executes the migration logic in migrate-schema.ts, transitioning from collection_id foreign keys to denormalized collection names.

Key Source Files

Understanding the QMD database schema requires familiarity with these implementation files:

• src/db.ts – Abstraction layer for SQLite drivers (bun:sqlite or better-sqlite3), providing openDatabase() and loadSqliteVec() for vector extension loading.

• src/store.ts – Core schema definition containing initializeDatabase() with all CREATE TABLE, INDEX, and TRIGGER statements, plus high-level APIs like createStore() and getIndexHealth().

• migrate-schema.ts – Standalone migration script for schema versioning, handling the transition from normalized collection tables to denormalized string columns.

• test/mcp.test.ts – Test suite that constructs the schema in memory, serving as a concise reference for table structures and relationships.

Summary

• QMD stores all indexed data in a single SQLite file at ~/.cache/qmd/index.sqlite with a schema defined in src/store.ts.
• The content table provides content-addressable storage using SHA-256 hashes, enabling automatic deduplication across collections.
• The documents table tracks file metadata with soft-delete support via the active column and maintains foreign key relationships to content hashes.
• documents_fts provides full-text search via FTS5, kept synchronized through database triggers that handle inserts, updates, and deletes.
• Optional vector search uses content_vectors for metadata and vectors_vec (sqlite-vec) for actual float embeddings, supporting cosine similarity queries.
• llm_cache stores hashed LLM responses to avoid redundant API calls for query expansion and reranking.
• Schema migrations are handled by migrate-schema.ts, which converted the design from numeric collection_id foreign keys to denormalized collection text columns.

Frequently Asked Questions

What file format does QMD use for its index?

QMD uses a single SQLite file located at ~/.cache/qmd/index.sqlite by default. This file contains all tables, indices, triggers, and virtual tables (FTS5 and sqlite-vec) required for document storage, full-text search, and vector similarity search.

How does QMD handle duplicate content across different files?

QMD implements content-addressable storage through the content table. When indexing, QMD calculates a SHA-256 hash of the document body. If two files have identical content, they both reference the same row in the content table via the hash foreign key, eliminating redundant storage while maintaining separate metadata entries in the documents table.

What is the purpose of the active column in the documents table?

The active column implements soft-delete functionality. When set to 0, the document is excluded from search results but its history and content remain in the database. This allows QMD to track file deletions without losing the ability to reference historical data or restore accidentally removed files. The FTS triggers automatically remove inactive documents from the full-text index.

How do I migrate from an older version of QMD that used collection_id?

Run the provided migration script from the repository root:

bun migrate-schema.ts

This script, located at migrate-schema.ts, adds a collection text column, migrates data from the old collections table, rebuilds the documents table without the collection_id foreign key, and recreates the FTS triggers to use the new column structure. Always back up your index.sqlite file before running migrations.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}