/**
* BetterSqlite3Backend - Native SQLite with FTS5 Trigram and sqlite-vec
*
* Production-grade backend using better-sqlite3 (native SQLite).
* Provides:
* - FTS5 with trigram tokenizer for CJK (Japanese, Chinese, Korean)
* - BM25 ranking for relevance scoring
* - sqlite-vec for persisted vector search (no rebuild on startup)
* - 10x faster than sql.js for large datasets
* - Proper word segmentation for all languages
*
* Requires:
* - Node.js environment (no browser support)
* - npm install better-sqlite3 sqlite-vec
*
* @module @agentkits/memory/better-sqlite3-backend
*/
import { EventEmitter } from 'node:events';
import { existsSync, mkdirSync } from 'node:fs';
import * as path from 'node:path';
import type Database from 'better-sqlite3';
import {
IMemoryBackend,
MemoryEntry,
MemoryEntryUpdate,
MemoryQuery,
SearchOptions,
SearchResult,
BackendStats,
HealthCheckResult,
ComponentHealth,
MemoryType,
EmbeddingGenerator,
generateMemoryId,
} from './types.js';
/**
* Configuration for BetterSqlite3 Backend
*/
export interface BetterSqlite3BackendConfig {
/** Path to SQLite database file (:memory: for in-memory) */
databasePath: string;
/** Enable query optimization and WAL mode */
optimize: boolean;
/** Default namespace */
defaultNamespace: string;
/** Embedding generator for semantic search */
embeddingGenerator?: EmbeddingGenerator;
/** Maximum entries before auto-cleanup */
maxEntries: number;
/** Enable verbose logging */
verbose: boolean;
/**
* FTS5 tokenizer to use
* - 'trigram': Best for CJK (Japanese, Chinese, Korean) - works with all languages
* - 'unicode61': Standard tokenizer, good for English/Latin
* - 'porter': Stemming for English
*/
ftsTokenizer: 'trigram' | 'unicode61' | 'porter';
/** Path to custom SQLite extension (e.g., lindera for advanced Japanese) */
extensionPath?: string;
/** Custom tokenizer name when using extension (e.g., 'lindera_tokenizer') */
customTokenizer?: string;
/** Enable sqlite-vec for vector search (default: true) */
enableVectorSearch?: boolean;
/** Vector dimensions for sqlite-vec (default: 384) */
vectorDimensions?: number;
}
/**
* Default configuration values
*/
const DEFAULT_CONFIG: BetterSqlite3BackendConfig = {
databasePath: ':memory:',
optimize: true,
defaultNamespace: 'default',
maxEntries: 1000000,
verbose: false,
ftsTokenizer: 'trigram', // Best for CJK out of the box
enableVectorSearch: true,
vectorDimensions: 384,
};
/**
* Estimate tokens from content (~4 chars per token)
*/
function estimateTokens(text: string): number {
return Math.ceil(text.length / 4);
}
/**
* BetterSqlite3 Backend for Production Memory Storage
*
* Features:
* - Native SQLite performance (10x faster than sql.js)
* - FTS5 with trigram tokenizer for CJK language support
* - BM25 relevance ranking
* - WAL mode for concurrent reads
* - Optional extension loading (lindera, ICU, etc.)
*/
export class BetterSqlite3Backend extends EventEmitter implements IMemoryBackend {
private config: BetterSqlite3BackendConfig;
private db: Database.Database | null = null;
private initialized: boolean = false;
private ftsAvailable: boolean = false;
private vectorAvailable: boolean = false;
// Performance tracking
private stats = {
queryCount: 0,
totalQueryTime: 0,
writeCount: 0,
totalWriteTime: 0,
};
constructor(config: Partial<BetterSqlite3BackendConfig> = {}) {
super();
this.config = { ...DEFAULT_CONFIG, ...config };
}
/**
* Initialize the BetterSqlite3 backend
*/
async initialize(): Promise<void> {
if (this.initialized) return;
// Dynamic import for better-sqlite3 (optional dependency)
let DatabaseConstructor: typeof import('better-sqlite3');
try {
DatabaseConstructor = (await import('better-sqlite3')).default;
} catch (error) {
throw new Error(
'better-sqlite3 is not installed. ' +
'For production CJK support, run: npm install better-sqlite3'
);
}
// Ensure directory exists for file-based databases
if (this.config.databasePath !== ':memory:') {
const dir = path.dirname(this.config.databasePath);
if (dir && !existsSync(dir)) {
mkdirSync(dir, { recursive: true });
}
}
// Create database
this.db = new DatabaseConstructor(this.config.databasePath);
if (this.config.verbose) {
const versionRow = this.db.prepare('SELECT sqlite_version() as version').get() as { version: string };
console.log(`[BetterSqlite3] Opened database: ${this.config.databasePath}`);
console.log(`[BetterSqlite3] SQLite version: ${versionRow.version}`);
}
// Enable optimizations
if (this.config.optimize) {
this.db.pragma('journal_mode = WAL');
this.db.pragma('synchronous = NORMAL');
this.db.pragma('cache_size = -64000'); // 64MB cache
this.db.pragma('temp_store = MEMORY');
}
// Load custom extension if provided
if (this.config.extensionPath) {
try {
this.db.loadExtension(this.config.extensionPath);
if (this.config.verbose) {
console.log(`[BetterSqlite3] Loaded extension: ${this.config.extensionPath}`);
}
} catch (error) {
console.warn(`[BetterSqlite3] Failed to load extension: ${error}`);
}
}
// Create schema
this.createSchema();
// Create FTS5 table with appropriate tokenizer
this.createFtsTable();
// Load sqlite-vec extension and create vector table
if (this.config.enableVectorSearch) {
await this.initializeSqliteVec();
}
this.initialized = true;
this.emit('initialized');
if (this.config.verbose) {
console.log('[BetterSqlite3] Backend initialized');
console.log(`[BetterSqlite3] FTS5 available: ${this.ftsAvailable}`);
console.log(`[BetterSqlite3] Tokenizer: ${this.getActiveTokenizer()}`);
}
}
/**
* Create the database schema
*/
private createSchema(): void {
if (!this.db) throw new Error('Database not initialized');
this.db.exec(`
CREATE TABLE IF NOT EXISTS memory_entries (
id TEXT PRIMARY KEY,
key TEXT NOT NULL,
content TEXT NOT NULL,
type TEXT DEFAULT 'semantic',
namespace TEXT DEFAULT 'default',
tags TEXT DEFAULT '[]',
metadata TEXT DEFAULT '{}',
embedding BLOB,
session_id TEXT,
owner_id TEXT,
access_level TEXT DEFAULT 'project',
created_at INTEGER NOT NULL,
updated_at INTEGER NOT NULL,
expires_at INTEGER,
version INTEGER DEFAULT 1,
"references" TEXT DEFAULT '[]',
access_count INTEGER DEFAULT 0,
last_accessed_at INTEGER NOT NULL
)
`);
// Create indexes
this.db.exec(`
CREATE INDEX IF NOT EXISTS idx_namespace ON memory_entries(namespace);
CREATE INDEX IF NOT EXISTS idx_key ON memory_entries(key);
CREATE INDEX IF NOT EXISTS idx_type ON memory_entries(type);
CREATE INDEX IF NOT EXISTS idx_created_at ON memory_entries(created_at);
CREATE INDEX IF NOT EXISTS idx_namespace_key ON memory_entries(namespace, key);
`);
}
/**
* Create FTS5 virtual table with appropriate tokenizer
*/
private createFtsTable(): void {
if (!this.db) throw new Error('Database not initialized');
// Determine tokenizer configuration
let tokenizerConfig: string;
if (this.config.customTokenizer) {
tokenizerConfig = `tokenize='${this.config.customTokenizer}'`;
} else {
switch (this.config.ftsTokenizer) {
case 'trigram':
tokenizerConfig = "tokenize='trigram'";
break;
case 'porter':
tokenizerConfig = "tokenize='porter unicode61'";
break;
default:
tokenizerConfig = "tokenize='unicode61'";
}
}
try {
// Create FTS5 virtual table
this.db.exec(`
CREATE VIRTUAL TABLE IF NOT EXISTS memory_fts USING fts5(
key,
content,
namespace,
tags,
content=memory_entries,
content_rowid=rowid,
${tokenizerConfig}
)
`);
// Create triggers to keep FTS in sync
this.db.exec(`
CREATE TRIGGER IF NOT EXISTS memory_fts_insert AFTER INSERT ON memory_entries BEGIN
INSERT INTO memory_fts(rowid, key, content, namespace, tags)
VALUES (NEW.rowid, NEW.key, NEW.content, NEW.namespace, NEW.tags);
END
`);
this.db.exec(`
CREATE TRIGGER IF NOT EXISTS memory_fts_delete AFTER DELETE ON memory_entries BEGIN
INSERT INTO memory_fts(memory_fts, rowid, key, content, namespace, tags)
VALUES ('delete', OLD.rowid, OLD.key, OLD.content, OLD.namespace, OLD.tags);
END
`);
this.db.exec(`
CREATE TRIGGER IF NOT EXISTS memory_fts_update AFTER UPDATE ON memory_entries BEGIN
INSERT INTO memory_fts(memory_fts, rowid, key, content, namespace, tags)
VALUES ('delete', OLD.rowid, OLD.key, OLD.content, OLD.namespace, OLD.tags);
INSERT INTO memory_fts(rowid, key, content, namespace, tags)
VALUES (NEW.rowid, NEW.key, NEW.content, NEW.namespace, NEW.tags);
END
`);
// Populate FTS from existing entries
this.db.exec(`
INSERT INTO memory_fts(memory_fts) VALUES('rebuild')
`);
this.ftsAvailable = true;
if (this.config.verbose) {
console.log(`[BetterSqlite3] FTS5 initialized with ${tokenizerConfig}`);
}
} catch (error) {
console.warn(`[BetterSqlite3] FTS5 initialization failed: ${error}`);
this.ftsAvailable = false;
}
}
/**
* Get the active tokenizer being used
*/
getActiveTokenizer(): string {
if (this.config.customTokenizer) {
return this.config.customTokenizer;
}
return this.config.ftsTokenizer;
}
/**
* Check if FTS5 is available and CJK optimized
*/
isFtsAvailable(): boolean {
return this.ftsAvailable;
}
/**
* Check if CJK is optimally supported (trigram or lindera)
*/
isCjkOptimized(): boolean {
return this.ftsAvailable && (
this.config.ftsTokenizer === 'trigram' ||
this.config.customTokenizer?.includes('lindera') === true
);
}
/**
* Check if vector search is available (sqlite-vec loaded)
*/
isVectorAvailable(): boolean {
return this.vectorAvailable;
}
/**
* Initialize sqlite-vec extension and create vector table
*/
private async initializeSqliteVec(): Promise<void> {
if (!this.db) throw new Error('Database not initialized');
try {
// Dynamic import for sqlite-vec
const sqliteVec = await import('sqlite-vec');
// Load the extension
sqliteVec.load(this.db);
const dimensions = this.config.vectorDimensions || 384;
// Create vec0 virtual table for vector storage
// Using float32 for full precision
this.db.exec(`
CREATE VIRTUAL TABLE IF NOT EXISTS memory_vectors USING vec0(
embedding float[${dimensions}]
)
`);
this.vectorAvailable = true;
if (this.config.verbose) {
console.log(`[BetterSqlite3] sqlite-vec initialized with ${dimensions} dimensions`);
}
} catch (error) {
console.warn(`[BetterSqlite3] sqlite-vec initialization failed: ${error}`);
console.warn('[BetterSqlite3] Falling back to brute-force vector search');
this.vectorAvailable = false;
}
}
/**
* Shutdown the backend
*/
async shutdown(): Promise<void> {
if (this.db) {
this.db.close();
this.db = null;
}
this.initialized = false;
this.emit('shutdown');
}
/**
* Store a memory entry
*/
async store(entry: MemoryEntry): Promise<void> {
if (!this.db) throw new Error('Database not initialized');
const startTime = Date.now();
const stmt = this.db.prepare(`
INSERT OR REPLACE INTO memory_entries
(id, key, content, type, namespace, tags, metadata, embedding, session_id,
owner_id, access_level, created_at, updated_at, expires_at, version,
"references", access_count, last_accessed_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`);
const result = stmt.run(
entry.id,
entry.key,
entry.content,
entry.type,
entry.namespace,
JSON.stringify(entry.tags),
JSON.stringify(entry.metadata),
entry.embedding
? Buffer.from(entry.embedding.buffer, entry.embedding.byteOffset, entry.embedding.byteLength)
: null,
entry.sessionId || null,
entry.ownerId || null,
entry.accessLevel,
entry.createdAt,
entry.updatedAt,
entry.expiresAt || null,
entry.version,
JSON.stringify(entry.references),
entry.accessCount,
entry.lastAccessedAt
);
// Store vector in sqlite-vec table if available and embedding exists
if (this.vectorAvailable && entry.embedding) {
// Get the rowid for the entry
const rowResult = this.db.prepare('SELECT rowid FROM memory_entries WHERE id = ?').get(entry.id) as { rowid: number | bigint } | undefined;
if (rowResult) {
try {
// Convert rowid to number (better-sqlite3 may return bigint)
const rowid = Number(rowResult.rowid);
// Delete existing vector if any (for REPLACE case)
this.db.exec(`DELETE FROM memory_vectors WHERE rowid = ${rowid}`);
// Insert new vector - sqlite-vec accepts JSON array format
const vectorJson = JSON.stringify(Array.from(entry.embedding));
// Use exec with direct SQL to avoid prepared statement issues with sqlite-vec
this.db.exec(`INSERT INTO memory_vectors(rowid, embedding) VALUES (${rowid}, '${vectorJson}')`);
} catch (error) {
// Skip vector insertion on dimension mismatch or other errors
if (this.config.verbose) {
console.warn(`[BetterSqlite3] Vector insertion skipped for ${entry.id}: ${error}`);
}
}
}
}
const duration = Date.now() - startTime;
this.stats.writeCount++;
this.stats.totalWriteTime += duration;
this.emit('entry:stored', { entry, duration });
}
/**
* Retrieve a memory entry by ID
*/
async get(id: string): Promise<MemoryEntry | null> {
if (!this.db) throw new Error('Database not initialized');
const startTime = Date.now();
const stmt = this.db.prepare('SELECT * FROM memory_entries WHERE id = ?');
const row = stmt.get(id) as Record<string, unknown> | undefined;
this.stats.queryCount++;
this.stats.totalQueryTime += Date.now() - startTime;
if (!row) return null;
// Update access count
this.db.prepare(
'UPDATE memory_entries SET access_count = access_count + 1, last_accessed_at = ? WHERE id = ?'
).run(Date.now(), id);
return this.rowToEntry(row);
}
/**
* Retrieve a memory entry by key within a namespace
*/
async getByKey(namespace: string, key: string): Promise<MemoryEntry | null> {
if (!this.db) throw new Error('Database not initialized');
const stmt = this.db.prepare(
'SELECT * FROM memory_entries WHERE namespace = ? AND key = ?'
);
const row = stmt.get(namespace, key) as Record<string, unknown> | undefined;
if (!row) return null;
// Update access count
this.db.prepare(
'UPDATE memory_entries SET access_count = access_count + 1, last_accessed_at = ? WHERE id = ?'
).run(Date.now(), row.id);
return this.rowToEntry(row);
}
/**
* Update a memory entry
*/
async update(id: string, update: MemoryEntryUpdate): Promise<MemoryEntry | null> {
if (!this.db) throw new Error('Database not initialized');
const existing = await this.get(id);
if (!existing) return null;
const updated: MemoryEntry = {
...existing,
...update,
updatedAt: Date.now(),
version: existing.version + 1,
};
await this.store(updated);
return updated;
}
/**
* Delete a memory entry
*/
async delete(id: string): Promise<boolean> {
if (!this.db) throw new Error('Database not initialized');
// Get rowid before deletion for vector cleanup
const rowResult = this.db.prepare('SELECT rowid FROM memory_entries WHERE id = ?').get(id) as { rowid: number | bigint } | undefined;
const result = this.db.prepare('DELETE FROM memory_entries WHERE id = ?').run(id);
// Delete from vector table if applicable
if (result.changes > 0 && this.vectorAvailable && rowResult) {
this.db.prepare('DELETE FROM memory_vectors WHERE rowid = ?').run(Number(rowResult.rowid));
}
return result.changes > 0;
}
/**
* Query memory entries
*/
async query(query: MemoryQuery): Promise<MemoryEntry[]> {
if (!this.db) throw new Error('Database not initialized');
const startTime = Date.now();
const conditions: string[] = [];
const params: unknown[] = [];
if (query.namespace) {
conditions.push('namespace = ?');
params.push(query.namespace);
}
if (query.memoryType) {
conditions.push('type = ?');
params.push(query.memoryType);
}
if (query.tags && query.tags.length > 0) {
const tagConditions = query.tags.map(() => 'tags LIKE ?');
conditions.push(`(${tagConditions.join(' OR ')})`);
query.tags.forEach((tag) => params.push(`%"${tag}"%`));
}
if (query.createdBefore) {
conditions.push('created_at < ?');
params.push(query.createdBefore);
}
if (query.createdAfter) {
conditions.push('created_at > ?');
params.push(query.createdAfter);
}
const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
const limit = query.limit || 100;
const sql = `
SELECT * FROM memory_entries
${whereClause}
ORDER BY created_at DESC
LIMIT ?
`;
params.push(limit);
const rows = this.db.prepare(sql).all(...params) as Record<string, unknown>[];
this.stats.queryCount++;
this.stats.totalQueryTime += Date.now() - startTime;
return rows.map((row) => this.rowToEntry(row));
}
/**
* Full-text search using FTS5
*/
async searchFts(query: string, options: { namespace?: string; limit?: number } = {}): Promise<MemoryEntry[]> {
if (!this.db) throw new Error('Database not initialized');
if (!this.ftsAvailable) {
// Fall back to LIKE search
return this.searchLike(query, options);
}
const limit = options.limit || 100;
const params: (string | number)[] = [];
// Sanitize query for FTS5
const sanitizedQuery = this.sanitizeFtsQuery(query);
if (!sanitizedQuery) return [];
let sql: string;
if (options.namespace) {
sql = `
SELECT m.*, bm25(memory_fts) as rank
FROM memory_fts f
JOIN memory_entries m ON f.rowid = m.rowid
WHERE memory_fts MATCH ? AND m.namespace = ?
ORDER BY rank
LIMIT ?
`;
params.push(sanitizedQuery, options.namespace, limit);
} else {
sql = `
SELECT m.*, bm25(memory_fts) as rank
FROM memory_fts f
JOIN memory_entries m ON f.rowid = m.rowid
WHERE memory_fts MATCH ?
ORDER BY rank
LIMIT ?
`;
params.push(sanitizedQuery, limit);
}
try {
const rows = this.db.prepare(sql).all(...params) as Record<string, unknown>[];
return rows.map((row) => this.rowToEntry(row));
} catch (error) {
// Fall back to LIKE on error
console.warn(`[BetterSqlite3] FTS5 search failed, falling back to LIKE: ${error}`);
return this.searchLike(query, options);
}
}
/**
* LIKE-based search fallback
*/
private searchLike(query: string, options: { namespace?: string; limit?: number }): MemoryEntry[] {
if (!this.db) throw new Error('Database not initialized');
const pattern = `%${query}%`;
const limit = options.limit || 100;
let sql: string;
const params: (string | number)[] = [];
if (options.namespace) {
sql = `
SELECT * FROM memory_entries
WHERE (content LIKE ? OR key LIKE ? OR tags LIKE ?) AND namespace = ?
ORDER BY created_at DESC
LIMIT ?
`;
params.push(pattern, pattern, pattern, options.namespace, limit);
} else {
sql = `
SELECT * FROM memory_entries
WHERE content LIKE ? OR key LIKE ? OR tags LIKE ?
ORDER BY created_at DESC
LIMIT ?
`;
params.push(pattern, pattern, pattern, limit);
}
const rows = this.db.prepare(sql).all(...params) as Record<string, unknown>[];
return rows.map((row) => this.rowToEntry(row));
}
/**
* Sanitize query for FTS5
*/
private sanitizeFtsQuery(query: string): string {
// Remove special FTS5 operators and wrap terms in quotes
return query
.replace(/[^\w\s\u4E00-\u9FFF\u3040-\u309F\u30A0-\u30FF\uAC00-\uD7AF\u3400-\u4DBF]/g, ' ')
.split(/\s+/)
.filter((term) => term.length > 0)
.map((term) => `"${term}"`)
.join(' OR ');
}
/**
* Semantic vector search using sqlite-vec (KNN) or brute-force fallback
*/
async search(embedding: Float32Array, options: SearchOptions): Promise<SearchResult[]> {
if (!this.db) throw new Error('Database not initialized');
const startTime = Date.now();
// Use sqlite-vec KNN search if available
if (this.vectorAvailable) {
return this.searchWithSqliteVec(embedding, options, startTime);
}
// Fallback to brute-force search
return this.searchBruteForce(embedding, options, startTime);
}
/**
* Fast KNN search using sqlite-vec
*/
private searchWithSqliteVec(
embedding: Float32Array,
options: SearchOptions,
startTime: number
): SearchResult[] {
if (!this.db) throw new Error('Database not initialized');
const k = options.k || 10;
// sqlite-vec KNN query - accepts JSON array format
const queryJson = JSON.stringify(Array.from(embedding));
const vecResults = this.db.prepare(`
SELECT
v.rowid,
v.distance
FROM memory_vectors v
WHERE v.embedding MATCH ?
ORDER BY v.distance
LIMIT ?
`).all(queryJson, k) as { rowid: number; distance: number }[];
// Fetch full entries and apply filters
const results: SearchResult[] = [];
for (const { rowid, distance } of vecResults) {
const row = this.db.prepare('SELECT * FROM memory_entries WHERE rowid = ?').get(rowid) as Record<string, unknown> | undefined;
if (!row) continue;
const entry = this.rowToEntry(row);
// Apply namespace filter
if (options.filters?.namespace && entry.namespace !== options.filters.namespace) {
continue;
}
// Apply type filter
if (options.filters?.memoryType && entry.type !== options.filters.memoryType) {
continue;
}
// Convert distance to similarity (cosine distance = 1 - similarity)
const similarity = 1 - distance;
// Apply threshold
if (options.threshold !== undefined && similarity < options.threshold) {
continue;
}
results.push({ entry, score: similarity, distance });
}
this.stats.queryCount++;
this.stats.totalQueryTime += Date.now() - startTime;
return results;
}
/**
* Brute-force vector search fallback
*/
private searchBruteForce(
embedding: Float32Array,
options: SearchOptions,
startTime: number
): SearchResult[] {
if (!this.db) throw new Error('Database not initialized');
// Get all entries with embeddings
let sql = `
SELECT * FROM memory_entries
WHERE embedding IS NOT NULL
`;
const params: unknown[] = [];
// Apply filters if provided
if (options.filters?.namespace) {
sql += ' AND namespace = ?';
params.push(options.filters.namespace);
}
if (options.filters?.memoryType) {
sql += ' AND type = ?';
params.push(options.filters.memoryType);
}
const rows = this.db.prepare(sql).all(...params) as Record<string, unknown>[];
// Calculate cosine similarity
const results: SearchResult[] = rows
.map((row) => {
const entry = this.rowToEntry(row);
if (!entry.embedding) return null;
const similarity = this.cosineSimilarity(embedding, entry.embedding);
const distance = 1 - similarity;
// Apply threshold (similarity threshold, not distance)
if (options.threshold !== undefined && similarity < options.threshold) {
return null;
}
return { entry, score: similarity, distance };
})
.filter((r): r is SearchResult => r !== null);
// Sort by score and limit
results.sort((a, b) => b.score - a.score);
this.stats.queryCount++;
this.stats.totalQueryTime += Date.now() - startTime;
return results.slice(0, options.k);
}
/**
* Calculate cosine similarity between two vectors
*/
private cosineSimilarity(a: Float32Array, b: Float32Array): number {
if (a.length !== b.length) return 0;
let dotProduct = 0;
let normA = 0;
let normB = 0;
for (let i = 0; i < a.length; i++) {
dotProduct += a[i] * b[i];
normA += a[i] * a[i];
normB += b[i] * b[i];
}
const denominator = Math.sqrt(normA) * Math.sqrt(normB);
return denominator === 0 ? 0 : dotProduct / denominator;
}
/**
* Bulk insert entries
*/
async bulkInsert(entries: MemoryEntry[]): Promise<void> {
if (!this.db) throw new Error('Database not initialized');
if (entries.length === 0) return;
const stmt = this.db.prepare(`
INSERT OR REPLACE INTO memory_entries
(id, key, content, type, namespace, tags, metadata, embedding, session_id,
owner_id, access_level, created_at, updated_at, expires_at, version,
"references", access_count, last_accessed_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`);
const getRowid = this.db.prepare('SELECT rowid FROM memory_entries WHERE id = ?');
const insert = this.db.transaction((entries: MemoryEntry[]) => {
for (const entry of entries) {
stmt.run(
entry.id,
entry.key,
entry.content,
entry.type,
entry.namespace,
JSON.stringify(entry.tags),
JSON.stringify(entry.metadata),
entry.embedding
? Buffer.from(entry.embedding.buffer, entry.embedding.byteOffset, entry.embedding.byteLength)
: null,
entry.sessionId || null,
entry.ownerId || null,
entry.accessLevel,
entry.createdAt,
entry.updatedAt,
entry.expiresAt || null,
entry.version,
JSON.stringify(entry.references),
entry.accessCount,
entry.lastAccessedAt
);
// Store vector in sqlite-vec table if available and embedding exists
if (this.vectorAvailable && entry.embedding) {
const rowResult = getRowid.get(entry.id) as { rowid: number | bigint } | undefined;
if (rowResult) {
try {
const rowid = Number(rowResult.rowid);
// Use exec for sqlite-vec (prepared statements don't work correctly)
this.db!.exec(`DELETE FROM memory_vectors WHERE rowid = ${rowid}`);
const vectorJson = JSON.stringify(Array.from(entry.embedding));
this.db!.exec(`INSERT INTO memory_vectors(rowid, embedding) VALUES (${rowid}, '${vectorJson}')`);
} catch (error) {
// Skip vector insertion on dimension mismatch or other errors
if (this.config.verbose) {
console.warn(`[BetterSqlite3] Vector insertion skipped for ${entry.id}: ${error}`);
}
}
}
}
}
});
insert(entries);
this.emit('bulkInserted', entries.length);
}
/**
* Bulk delete entries
*/
async bulkDelete(ids: string[]): Promise<number> {
if (!this.db) throw new Error('Database not initialized');
if (ids.length === 0) return 0;
// Get rowids before deletion for vector cleanup
const placeholders = ids.map(() => '?').join(', ');
let rowids: number[] = [];
if (this.vectorAvailable) {
const rows = this.db
.prepare(`SELECT rowid FROM memory_entries WHERE id IN (${placeholders})`)
.all(...ids) as { rowid: number | bigint }[];
rowids = rows.map(r => Number(r.rowid));
}
const result = this.db
.prepare(`DELETE FROM memory_entries WHERE id IN (${placeholders})`)
.run(...ids);
// Delete vectors if applicable
if (this.vectorAvailable && rowids.length > 0) {
const vecPlaceholders = rowids.map(() => '?').join(', ');
this.db.prepare(`DELETE FROM memory_vectors WHERE rowid IN (${vecPlaceholders})`).run(...rowids);
}
return result.changes;
}
/**
* Get entry count
*/
async count(namespace?: string): Promise<number> {
if (!this.db) throw new Error('Database not initialized');
if (namespace) {
const result = this.db
.prepare('SELECT COUNT(*) as count FROM memory_entries WHERE namespace = ?')
.get(namespace) as { count: number };
return result.count;
}
const result = this.db
.prepare('SELECT COUNT(*) as count FROM memory_entries')
.get() as { count: number };
return result.count;
}
/**
* List all namespaces
*/
async listNamespaces(): Promise<string[]> {
if (!this.db) throw new Error('Database not initialized');
const rows = this.db
.prepare('SELECT DISTINCT namespace FROM memory_entries')
.all() as { namespace: string }[];
return rows.map((r) => r.namespace);
}
/**
* Clear all entries in a namespace
*/
async clearNamespace(namespace: string): Promise<number> {
if (!this.db) throw new Error('Database not initialized');
// Get rowids before deletion for vector cleanup
let rowids: number[] = [];
if (this.vectorAvailable) {
const rows = this.db
.prepare('SELECT rowid FROM memory_entries WHERE namespace = ?')
.all(namespace) as { rowid: number | bigint }[];
rowids = rows.map(r => Number(r.rowid));
}
const result = this.db
.prepare('DELETE FROM memory_entries WHERE namespace = ?')
.run(namespace);
// Delete vectors if applicable
if (this.vectorAvailable && rowids.length > 0) {
const placeholders = rowids.map(() => '?').join(', ');
this.db.prepare(`DELETE FROM memory_vectors WHERE rowid IN (${placeholders})`).run(...rowids);
}
return result.changes;
}
/**
* Get backend statistics
*/
async getStats(): Promise<BackendStats> {
if (!this.db) throw new Error('Database not initialized');
const totalEntries = await this.count();
const namespaces = await this.listNamespaces();
const entriesByNamespace: Record<string, number> = {};
for (const ns of namespaces) {
entriesByNamespace[ns] = await this.count(ns);
}
// Get database size
const pageCount = this.db.pragma('page_count', { simple: true }) as number;
const pageSize = this.db.pragma('page_size', { simple: true }) as number;
const dbSizeBytes = pageCount * pageSize;
// Get type breakdown
const typeRows = this.db.prepare(`
SELECT type, COUNT(*) as count FROM memory_entries GROUP BY type
`).all() as { type: MemoryType; count: number }[];
const entriesByType: Record<MemoryType, number> = {
episodic: 0,
semantic: 0,
procedural: 0,
working: 0,
cache: 0,
};
for (const row of typeRows) {
entriesByType[row.type] = row.count;
}
return {
totalEntries,
entriesByNamespace,
entriesByType,
memoryUsage: dbSizeBytes,
avgQueryTime: this.stats.queryCount > 0 ? this.stats.totalQueryTime / this.stats.queryCount : 0,
avgSearchTime: this.stats.queryCount > 0 ? this.stats.totalQueryTime / this.stats.queryCount : 0,
};
}
/**
* Perform health check
*/
async healthCheck(): Promise<HealthCheckResult> {
const issues: string[] = [];
const recommendations: string[] = [];
// Check database (storage)
let storageHealth: ComponentHealth;
const storageStart = Date.now();
try {
if (this.db) {
this.db.prepare('SELECT 1').get();
storageHealth = {
status: 'healthy',
latency: Date.now() - storageStart,
};
} else {
storageHealth = {
status: 'unhealthy',
latency: 0,
message: 'Database not initialized',
};
issues.push('Database not initialized');
}
} catch (error) {
storageHealth = {
status: 'unhealthy',
latency: Date.now() - storageStart,
message: String(error),
};
issues.push(`Database error: ${error}`);
}
// Check FTS5 (index)
const indexHealth: ComponentHealth = {
status: this.ftsAvailable ? 'healthy' : 'degraded',
latency: 0,
message: this.ftsAvailable
? `Tokenizer: ${this.getActiveTokenizer()}`
: 'FTS5 not available, using LIKE fallback',
};
if (!this.ftsAvailable) {
recommendations.push('Enable FTS5 for better search performance');
}
// Check vector search (sqlite-vec)
const cacheHealth: ComponentHealth = {
status: this.vectorAvailable ? 'healthy' : 'degraded',
latency: 0,
message: this.vectorAvailable
? `sqlite-vec active (${this.config.vectorDimensions || 384} dimensions)`
: 'sqlite-vec not available, using brute-force search',
};
if (!this.vectorAvailable) {
recommendations.push('Install sqlite-vec for faster vector search');
}
// Determine overall status
let status: 'healthy' | 'degraded' | 'unhealthy';
if (storageHealth.status === 'unhealthy') {
status = 'unhealthy';
} else if (indexHealth.status === 'degraded' || cacheHealth.status === 'degraded') {
status = 'degraded';
} else {
status = 'healthy';
}
return {
status,
timestamp: Date.now(),
components: {
storage: storageHealth,
index: indexHealth,
cache: cacheHealth,
},
issues,
recommendations,
};
}
/**
* Get the underlying database for advanced operations
*/
getDatabase(): Database.Database | null {
return this.db;
}
/**
* Rebuild FTS index
*/
async rebuildFtsIndex(): Promise<void> {
if (!this.db || !this.ftsAvailable) return;
this.db.exec("INSERT INTO memory_fts(memory_fts) VALUES('rebuild')");
if (this.config.verbose) {
console.log('[BetterSqlite3] FTS index rebuilt');
}
}
/**
* Convert database row to MemoryEntry
*/
private rowToEntry(row: Record<string, unknown>): MemoryEntry {
let embedding: Float32Array | undefined;
if (row.embedding) {
const buffer = row.embedding as Buffer;
embedding = new Float32Array(buffer.buffer, buffer.byteOffset, buffer.byteLength / 4);
}
return {
id: row.id as string,
key: row.key as string,
content: row.content as string,
embedding,
type: row.type as MemoryType,
namespace: row.namespace as string,
tags: JSON.parse((row.tags as string) || '[]'),
metadata: JSON.parse((row.metadata as string) || '{}'),
sessionId: row.session_id as string | undefined,
ownerId: row.owner_id as string | undefined,
accessLevel: row.access_level as MemoryEntry['accessLevel'],
createdAt: row.created_at as number,
updatedAt: row.updated_at as number,
expiresAt: row.expires_at as number | undefined,
version: row.version as number,
references: JSON.parse((row.references as string) || '[]'),
accessCount: row.access_count as number,
lastAccessedAt: row.last_accessed_at as number,
};
}
/**
* Migrate existing embeddings to sqlite-vec index.
* Call this to index entries that have embeddings but are not yet in the vector index.
*
* @returns Number of entries migrated
*/
async migrateEmbeddingsToVec(): Promise<{ migrated: number; skipped: number; errors: number }> {
if (!this.db) throw new Error('Database not initialized');
if (!this.vectorAvailable) {
console.warn('[Migration] sqlite-vec not available, skipping migration');
return { migrated: 0, skipped: 0, errors: 0 };
}
let migrated = 0;
let skipped = 0;
let errors = 0;
// Find entries with embeddings that are not in memory_vectors
const entriesWithEmbeddings = this.db.prepare(`
SELECT e.rowid, e.id, e.embedding
FROM memory_entries e
LEFT JOIN memory_vectors v ON e.rowid = v.rowid
WHERE e.embedding IS NOT NULL AND v.rowid IS NULL
`).all() as Array<{ rowid: number | bigint; id: string; embedding: Buffer | null }>;
console.log(`[Migration] Found ${entriesWithEmbeddings.length} entries to migrate to sqlite-vec`);
for (const row of entriesWithEmbeddings) {
try {
if (!row.embedding) {
skipped++;
continue;
}
// Parse embedding from BLOB
const float32 = new Float32Array(row.embedding.buffer, row.embedding.byteOffset, row.embedding.byteLength / 4);
const vectorJson = JSON.stringify(Array.from(float32));
const rowid = Number(row.rowid);
// Insert into sqlite-vec
this.db.exec(`INSERT OR REPLACE INTO memory_vectors(rowid, embedding) VALUES (${rowid}, '${vectorJson}')`);
migrated++;
// Log progress every 100 entries
if (migrated % 100 === 0) {
console.log(`[Migration] Migrated ${migrated}/${entriesWithEmbeddings.length} entries...`);
}
} catch (err) {
console.error(`[Migration] Error migrating entry ${row.id}:`, err);
errors++;
}
}
console.log(`[Migration] Complete: ${migrated} migrated, ${skipped} skipped, ${errors} errors`);
return { migrated, skipped, errors };
}
/**
* Check if migration is needed (entries with embeddings not in sqlite-vec)
*/
async needsMigration(): Promise<{ needed: boolean; count: number }> {
if (!this.db) throw new Error('Database not initialized');
if (!this.vectorAvailable) {
return { needed: false, count: 0 };
}
const result = this.db.prepare(`
SELECT COUNT(*) as count
FROM memory_entries e
LEFT JOIN memory_vectors v ON e.rowid = v.rowid
WHERE e.embedding IS NOT NULL AND v.rowid IS NULL
`).get() as { count: number };
return { needed: result.count > 0, count: result.count };
}
}
/**
* Create a BetterSqlite3 backend with default CJK support
*/
export function createBetterSqlite3Backend(
config?: Partial<BetterSqlite3BackendConfig>
): BetterSqlite3Backend {
return new BetterSqlite3Backend({
...config,
ftsTokenizer: config?.ftsTokenizer || 'trigram', // Default to trigram for CJK
});
}
/**
* Create a BetterSqlite3 backend with lindera extension for advanced Japanese
*/
export function createJapaneseOptimizedBackend(
config: Partial<BetterSqlite3BackendConfig> & { linderaPath: string }
): BetterSqlite3Backend {
return new BetterSqlite3Backend({
...config,
extensionPath: config.linderaPath,
customTokenizer: 'lindera_tokenizer',
});
}
export default BetterSqlite3Backend;
