# Database Schema for a QMD Index: Complete SQLite Structure Explained

> Explore the complete SQLite database schema for a QMD index. Understand the seven core tables storing metadata, content, vectors, and FTS indexes for efficient document management in tobi/qmd.

- Repository: [Tobias Lütke/qmd](https://github.com/tobi/qmd)
- Tags: api-reference
- Published: 2026-02-16

---

**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`](https://github.com/tobi/qmd/blob/main/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`](https://github.com/tobi/qmd/blob/main/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:

```bash
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`](https://github.com/tobi/qmd/blob/main/src/store.ts):

```typescript
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`:

```typescript
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:

```typescript
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:

```bash

# From repository root

bun migrate-schema.ts

```

This executes the migration logic in [`migrate-schema.ts`](https://github.com/tobi/qmd/blob/main/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`](https://github.com/tobi/qmd/blob/main/src/db.ts)** – Abstraction layer for SQLite drivers (`bun:sqlite` or `better-sqlite3`), providing `openDatabase()` and `loadSqliteVec()` for vector extension loading.

- **[`src/store.ts`](https://github.com/tobi/qmd/blob/main/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`](https://github.com/tobi/qmd/blob/main/migrate-schema.ts)** – Standalone migration script for schema versioning, handling the transition from normalized collection tables to denormalized string columns.

- **[`test/mcp.test.ts`](https://github.com/tobi/qmd/blob/main/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`](https://github.com/tobi/qmd/blob/main/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`](https://github.com/tobi/qmd/blob/main/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:

```bash
bun migrate-schema.ts

```

This script, located at [`migrate-schema.ts`](https://github.com/tobi/qmd/blob/main/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.