Document DB (MCP Server) Requirements Specification

Version 2.0 — 2025-06-18 Full migration to sqlite-vec 0.1 series

---

1. Purpose

Implement a local-only vector server that stores Japanese documents using OpenAI Embeddings + sqlite-vec and allows AI agents to perform insert / find / delete operations via Model Context Protocol (MCP). All legacy sqlite-vss dependencies are deprecated and replaced with the successor sqlite-vec (vec0 virtual table approach).

---

2. Technology Stack

Category	Technology	Notes
Runtime	Node.js 20 LTS
Language	TypeScript 5 (strict)
Vector DB	sqlite-vec 0.1.x<br>・vec0 virtual table<br>・distance_metric = cosine (github.com, alexgarcia.xyz)
Embedding	OpenAI text-embedding-3-small (1536 dims)
MCP	MCP 0.4 – JSON-RPC 2.0 (HTTP + stdio)
Logging	pino (JSON / dev uses pino-pretty)
Migration	SQL files + TS runner
License	MIT
OS	macOS / Linux (Windows not supported)

---

3. MCP "tools"

Tool	params (JSON Schema)	result
create_database	{ db_name:string }	{ success:boolean, db_path:string }
insert_document	{ text:string, metadata?:object, db_name?:string }	{ doc_id:string, chunk_count:int }
find_similar_documents	{ text:string, top_k?:int (default 10), db_name?:string }	[ { chunk_id, doc_id, text, score } ]
delete_document	{ doc_id:string, db_name?:string }	{ deleted_chunks:int }

• Manifest is published via GET /tools.
• JSON-RPC error.code examples: "NOT_FOUND", "INVALID_REQUEST".

---

4. Data Schema (sqlite-vec)

-- Minimal schema. While embedding could be primary key, we adopt ULID row ID.
CREATE VIRTUAL TABLE chunks USING vec0(
  chunk_id     TEXT       PRIMARY KEY,         -- ULID
  doc_id       TEXT       NOT NULL,            -- Document ID
  chunk_index  INTEGER    NOT NULL,            -- 0,1,2…
  text         TEXT       AUXILIARY,           -- Original text for similarity results (+ for non-indexed)
  metadata     JSON       AUXILIARY,
  embedding    FLOAT[1536]  DISTANCE_METRIC=cosine
);

• The vec0 virtual table itself contains the index and search logic, so no additional CREATE INDEX is needed (alexgarcia.xyz).

• Load extension on boot

  import * as sqliteVec from "sqlite-vec";      // npm i sqlite-vec
  import Database from "better-sqlite3";
  const db = new Database("./data/vector.db");
  sqliteVec.load(db);                           // ← Required
  

---

5. Business Flow

Operation	Steps
create_db	1. Create database directory if needed<br>2. Initialize SQLite database with migrations<br>3. Load sqlite-vec extension<br>4. Return success status and path
insert	1. Select target database (db_name or default)<br>2. doc_id = ULID()<br>3. Split max 100k chars by periods/newlines (700 chars + overlap 100)<br>4. Batch embed with OpenAI (≤100 chunks)<br>5. Bulk INSERT INTO chunks (…) VALUES …
find	1. Select target database (db_name or default)<br>2. Embed query<br>3. sql SELECT chunk_id, doc_id, text, distance FROM chunks WHERE embedding MATCH ? AND k = :top_k;<br>4. Return distance as score
delete	1. Select target database (db_name or default)<br>2. BEGIN; DELETE FROM chunks WHERE doc_id=?; COMMIT;<br>3. Verify and return deletion count using changes()

For MATCH ?, bind Float32Array.buffer or use vec_f32('[…]').

---

6. Migration

6.1 migrations/001_init.sql

PRAGMA foreign_keys = ON;

-- sqlite-vec vec0 virtual table
CREATE VIRTUAL TABLE chunks USING vec0(
  chunk_id     TEXT PRIMARY KEY,
  doc_id       TEXT NOT NULL,
  chunk_index  INTEGER NOT NULL,
  text         TEXT AUXILIARY,
  metadata     JSON AUXILIARY,
  embedding    FLOAT[1536] DISTANCE_METRIC=cosine
);

6.2 scripts/migrate.ts (Excerpt)

Change – Always call sqliteVec.load(db) after opening DB.

import * as sqliteVec from "sqlite-vec";
const db = new Database(DB_PATH);
sqliteVec.load(db);        // ← New
db.pragma("journal_mode = WAL");
…

---

7. Configuration

Key	Default	Description
OPENAI_API_KEY	—	.env
dbPath	./data/vector.db	Default SQLite path
dbDirectory	./data/	Directory for multiple databases
chunkSize	700	Character count
chunkOverlap	100	Character count
defaultTopK	10	Similar search count
deleteMode	"hard"	Reserved field

Database Naming Convention

• Default database: vectors.db (used when db_name is not specified)
• Custom databases: {db_name}.db (created in dbDirectory)
• Database names must be valid filenames (alphanumeric, underscore, hyphen allowed)

---

8. Non-Functional Requirements

Item	Details
Performance	P95 < 300 ms (10k chunks, M1 Mac)
Scalability	sqlite-vec uses brute-force KNN only. Consider ANN for >1M chunks (docs.sqlitecloud.io)
Observability	pino: processing time for insert/find/delete + OpenAI call time
Testing	Jest: chunker・delete 404・E2E (insert→find→delete)
CI	GitHub Actions (lint→test→build)

---

9. Directory Structure

/project-root
├─ src/
│  ├─ server.ts          # Fastify + MCP
│  ├─ tools/             # insert, find, delete
│  ├─ db.ts              # sqlite-vec loader & wrapper
│  ├─ embed.ts           # OpenAI embeddings
│  ├─ chunker.ts         # Japanese text splitting
│  └─ manifest.ts        # /tools JSON
├─ migrations/001_init.sql
├─ scripts/migrate.ts
├─ tests/
├─ .env.example
├─ README.md
└─ LICENSE (MIT)

---

10. Change History

Version	Date	Major Changes
2.0	2025-06-18	Full migration to sqlite-vec 0.1 series (updated schema, queries, load procedures)
1.2	2025-06-18	Added initial migration procedures
1.1	2025-06-18	Added delete_document
1.0	2025-06-18	MCP insert / find specification
0.9	2025-06-18	Initial version

This completes the latest specification based on the successor engine sqlite-vec.