/**
* SQLite storage backend implementation
*
* Persistent storage using SQLite via Bun's built-in driver.
* Refactored from src/database/sqlite.ts to implement IStorageBackend.
*/
import type { Database } from "bun:sqlite";
import { existsSync, mkdirSync } from "node:fs";
import { dirname } from "node:path";
import { nanoid } from "nanoid";
import { MigrationRunner, migrations } from "@/migrations";
import type {
Chunk,
CreateMemory,
Document,
Memory,
UpdateMemory,
} from "@/types";
import type { IStorageBackend } from "./interface";
import type {
CreatePendingMemoryInput,
DeleteResult,
ListMemoriesOptions,
PendingMemory,
StorageBackendType,
StorageMode,
} from "./types";
/**
* SQLite storage backend
*/
export class SqliteStorageBackend implements IStorageBackend {
private db: Database;
private dbPath: string;
private migrationRunner: MigrationRunner;
private mode: StorageMode;
private closed = false;
constructor(dbPath: string, mode: StorageMode = "automatic") {
this.dbPath = dbPath;
this.mode = mode;
// Ensure directory exists
const dir = dirname(dbPath);
if (dir && dir !== "." && !existsSync(dir)) {
mkdirSync(dir, { recursive: true });
}
// Initialize migration runner (handles db creation and WAL mode)
this.migrationRunner = new MigrationRunner(dbPath, migrations);
this.db = this.migrationRunner.getDatabase();
}
async initialize(): Promise<void> {
// Run all pending migrations
const result = await this.migrationRunner.migrate();
if (!result.success) {
throw new Error(`Migration failed: ${result.error}`);
}
if (result.applied.length > 0) {
console.log(`[doclea] Applied migrations: ${result.applied.join(", ")}`);
}
}
getStorageMode(): StorageMode {
return this.mode;
}
setStorageMode(mode: StorageMode): void {
this.mode = mode;
}
getBackendType(): StorageBackendType {
return "sqlite";
}
isClosed(): boolean {
return this.closed;
}
getDatabase(): Database {
if (this.closed) {
throw new Error("Storage backend is closed");
}
return this.db;
}
getSchemaVersion(): string | null {
return this.migrationRunner.getCurrentVersion();
}
close(): void {
if (!this.closed) {
this.db.close();
this.closed = true;
}
}
// ============================================
// Memory Operations
// ============================================
createMemory(
memory: CreateMemory & { id: string; qdrantId?: string },
): Memory {
const now = Math.floor(Date.now() / 1000);
const stmt = this.db.prepare(`
INSERT INTO memories (
id, type, title, content, summary, importance, tags, related_files,
git_commit, source_pr, experts, qdrant_id, created_at, accessed_at
) VALUES (
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
)
`);
stmt.run(
memory.id,
memory.type,
memory.title,
memory.content,
memory.summary ?? null,
memory.importance ?? 0.5,
JSON.stringify(memory.tags ?? []),
JSON.stringify(memory.relatedFiles ?? []),
memory.gitCommit ?? null,
memory.sourcePr ?? null,
JSON.stringify(memory.experts ?? []),
memory.qdrantId ?? null,
now,
now,
);
const created = this.getMemory(memory.id);
if (!created) {
throw new Error(`Failed to create memory with id ${memory.id}`);
}
return created;
}
getMemory(id: string): Memory | null {
const stmt = this.db.prepare("SELECT * FROM memories WHERE id = ?");
const row = stmt.get(id) as MemoryRow | undefined;
return row ? this.rowToMemory(row) : null;
}
getMemoryByQdrantId(qdrantId: string): Memory | null {
const stmt = this.db.prepare("SELECT * FROM memories WHERE qdrant_id = ?");
const row = stmt.get(qdrantId) as MemoryRow | undefined;
return row ? this.rowToMemory(row) : null;
}
updateMemory(
id: string,
updates: UpdateMemory & { qdrantId?: string },
): Memory | null {
const existing = this.getMemory(id);
if (!existing) return null;
const fields: string[] = [];
const values: (string | number | null)[] = [];
if (updates.type !== undefined) {
fields.push("type = ?");
values.push(updates.type);
}
if (updates.title !== undefined) {
fields.push("title = ?");
values.push(updates.title);
}
if (updates.content !== undefined) {
fields.push("content = ?");
values.push(updates.content);
}
if (updates.summary !== undefined) {
fields.push("summary = ?");
values.push(updates.summary);
}
if (updates.importance !== undefined) {
fields.push("importance = ?");
values.push(updates.importance);
}
if (updates.tags !== undefined) {
fields.push("tags = ?");
values.push(JSON.stringify(updates.tags));
}
if (updates.relatedFiles !== undefined) {
fields.push("related_files = ?");
values.push(JSON.stringify(updates.relatedFiles));
}
if (updates.gitCommit !== undefined) {
fields.push("git_commit = ?");
values.push(updates.gitCommit);
}
if (updates.sourcePr !== undefined) {
fields.push("source_pr = ?");
values.push(updates.sourcePr);
}
if (updates.experts !== undefined) {
fields.push("experts = ?");
values.push(JSON.stringify(updates.experts));
}
if (updates.qdrantId !== undefined) {
fields.push("qdrant_id = ?");
values.push(updates.qdrantId);
}
// Handle confidence decay fields
if (updates.decayRate !== undefined) {
fields.push("decay_rate = ?");
values.push(updates.decayRate);
}
if (updates.lastRefreshedAt !== undefined) {
fields.push("last_refreshed_at = ?");
values.push(updates.lastRefreshedAt);
}
if (updates.confidenceFloor !== undefined) {
fields.push("confidence_floor = ?");
values.push(updates.confidenceFloor);
}
if (updates.decayFunction !== undefined) {
fields.push("decay_function = ?");
values.push(updates.decayFunction);
}
if (fields.length === 0) return existing;
fields.push("accessed_at = ?");
values.push(Math.floor(Date.now() / 1000));
values.push(id);
const stmt = this.db.prepare(
`UPDATE memories SET ${fields.join(", ")} WHERE id = ?`,
);
stmt.run(...values);
return this.getMemory(id);
}
deleteMemory(id: string): DeleteResult {
// Get qdrantId before deletion for vector store cleanup
const existing = this.getMemory(id);
if (!existing) {
return { success: false };
}
const qdrantId = existing.qdrantId;
const stmt = this.db.prepare("DELETE FROM memories WHERE id = ?");
const result = stmt.run(id);
return {
success: result.changes > 0,
qdrantId,
};
}
listMemories(filters?: ListMemoriesOptions): Memory[] {
let query = "SELECT * FROM memories";
const params: (string | number)[] = [];
if (filters?.type) {
query += " WHERE type = ?";
params.push(filters.type);
}
query += " ORDER BY accessed_at DESC";
if (filters?.limit) {
query += " LIMIT ?";
params.push(filters.limit);
}
const stmt = this.db.prepare(query);
const rows = stmt.all(...params) as MemoryRow[];
return rows.map((row) => this.rowToMemory(row));
}
getMemoriesByIds(ids: string[]): Memory[] {
if (ids.length === 0) return [];
const placeholders = ids.map(() => "?").join(", ");
const stmt = this.db.prepare(
`SELECT * FROM memories WHERE id IN (${placeholders})`,
);
const rows = stmt.all(...ids) as MemoryRow[];
return rows.map((row) => this.rowToMemory(row));
}
findByRelatedFiles(files: string[], excludeId?: string): Memory[] {
if (files.length === 0) return [];
const placeholders = files.map(() => "?").join(", ");
let query = `
SELECT DISTINCT m.* FROM memories m, json_each(m.related_files) AS rf
WHERE rf.value IN (${placeholders})
`;
const params: (string | number)[] = [...files];
if (excludeId) {
query += " AND m.id != ?";
params.push(excludeId);
}
query += " ORDER BY m.accessed_at DESC LIMIT 100";
const stmt = this.db.prepare(query);
const rows = stmt.all(...params) as MemoryRow[];
return rows.map((row) => this.rowToMemory(row));
}
findByTimeRange(
startTime: number,
endTime: number,
excludeId?: string,
): Memory[] {
let query =
"SELECT * FROM memories WHERE created_at >= ? AND created_at <= ?";
const params: (string | number)[] = [startTime, endTime];
if (excludeId) {
query += " AND id != ?";
params.push(excludeId);
}
query += " ORDER BY created_at DESC LIMIT 100";
const stmt = this.db.prepare(query);
const rows = stmt.all(...params) as MemoryRow[];
return rows.map((row) => this.rowToMemory(row));
}
searchByTags(tags: string[], excludeId?: string): Memory[] {
if (tags.length === 0) return [];
const placeholders = tags.map(() => "?").join(", ");
let query = `
SELECT DISTINCT m.* FROM memories m, json_each(m.tags) AS t
WHERE LOWER(t.value) IN (${placeholders})
`;
const params: (string | number)[] = tags.map((t) => t.toLowerCase());
if (excludeId) {
query += " AND m.id != ?";
params.push(excludeId);
}
query += " ORDER BY m.accessed_at DESC LIMIT 100";
const stmt = this.db.prepare(query);
const rows = stmt.all(...params) as MemoryRow[];
return rows.map((row) => this.rowToMemory(row));
}
/**
* Increment the access count for a memory (atomic operation).
* Also updates accessed_at timestamp.
* Used by multi-factor relevance scoring for usage frequency tracking.
*/
incrementAccessCount(id: string): boolean {
const now = Math.floor(Date.now() / 1000);
const stmt = this.db.prepare(`
UPDATE memories
SET access_count = COALESCE(access_count, 0) + 1, accessed_at = ?
WHERE id = ?
`);
const result = stmt.run(now, id);
return result.changes > 0;
}
/**
* Get memories that need review (auto-stored in automatic mode)
*/
getMemoriesNeedingReview(limit = 50): Memory[] {
const stmt = this.db.prepare(`
SELECT * FROM memories
WHERE needs_review = 1
ORDER BY created_at DESC
LIMIT ?
`);
const rows = stmt.all(limit) as MemoryRow[];
return rows.map((row) => this.rowToMemory(row));
}
/**
* Mark a memory as confirmed (no longer needs review)
*/
confirmMemory(id: string): boolean {
const stmt = this.db.prepare(`
UPDATE memories SET needs_review = 0 WHERE id = ?
`);
const result = stmt.run(id);
return result.changes > 0;
}
/**
* Mark a memory as needing review
*/
markForReview(id: string): boolean {
const stmt = this.db.prepare(`
UPDATE memories SET needs_review = 1 WHERE id = ?
`);
const result = stmt.run(id);
return result.changes > 0;
}
/**
* Refresh a memory's confidence decay anchor.
* Updates last_refreshed_at to current timestamp, optionally updates importance.
* Returns the updated memory or null if not found.
*/
refreshMemory(id: string, newImportance?: number): Memory | null {
const existing = this.getMemory(id);
if (!existing) return null;
const now = Math.floor(Date.now() / 1000);
if (newImportance !== undefined) {
const stmt = this.db.prepare(`
UPDATE memories
SET last_refreshed_at = ?, importance = ?, accessed_at = ?
WHERE id = ?
`);
stmt.run(now, newImportance, now, id);
} else {
const stmt = this.db.prepare(`
UPDATE memories
SET last_refreshed_at = ?, accessed_at = ?
WHERE id = ?
`);
stmt.run(now, now, id);
}
return this.getMemory(id);
}
// ============================================
// Document/Chunk Operations
// ============================================
createDocument(doc: Omit<Document, "createdAt" | "updatedAt">): Document {
const now = Math.floor(Date.now() / 1000);
const stmt = this.db.prepare(`
INSERT INTO documents (id, title, content, content_hash, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?)
`);
stmt.run(doc.id, doc.title, doc.content, doc.contentHash ?? null, now, now);
const created = this.getDocument(doc.id);
if (!created) {
throw new Error(`Failed to create document with id ${doc.id}`);
}
return created;
}
getDocument(id: string): Document | null {
const stmt = this.db.prepare("SELECT * FROM documents WHERE id = ?");
const row = stmt.get(id) as DocumentRow | undefined;
return row ? this.rowToDocument(row) : null;
}
createChunk(chunk: Chunk): Chunk {
const stmt = this.db.prepare(`
INSERT INTO chunks (id, document_id, content, qdrant_id, start_offset, end_offset)
VALUES (?, ?, ?, ?, ?, ?)
`);
stmt.run(
chunk.id,
chunk.documentId,
chunk.content,
chunk.qdrantId ?? null,
chunk.startOffset,
chunk.endOffset,
);
return chunk;
}
getChunksByDocument(documentId: string): Chunk[] {
const stmt = this.db.prepare("SELECT * FROM chunks WHERE document_id = ?");
const rows = stmt.all(documentId) as ChunkRow[];
return rows.map((row) => this.rowToChunk(row));
}
// ============================================
// Embedding Cache Operations
// ============================================
getCachedEmbedding(contentHash: string, model: string): number[] | null {
const stmt = this.db.prepare(
"SELECT embedding FROM embedding_cache WHERE content_hash = ? AND model = ?",
);
const row = stmt.get(contentHash, model) as
| { embedding: string }
| undefined;
if (!row) return null;
return JSON.parse(row.embedding);
}
setCachedEmbedding(
contentHash: string,
model: string,
embedding: number[],
): void {
const stmt = this.db.prepare(`
INSERT OR REPLACE INTO embedding_cache (content_hash, model, embedding, created_at)
VALUES (?, ?, ?, unixepoch())
`);
stmt.run(contentHash, model, JSON.stringify(embedding));
}
getCachedEmbeddingsBatch(
contentHashes: string[],
model: string,
): Map<string, number[]> {
if (contentHashes.length === 0) return new Map();
const placeholders = contentHashes.map(() => "?").join(", ");
const stmt = this.db.prepare(
`SELECT content_hash, embedding FROM embedding_cache WHERE content_hash IN (${placeholders}) AND model = ?`,
);
const rows = stmt.all(...contentHashes, model) as Array<{
content_hash: string;
embedding: string;
}>;
const result = new Map<string, number[]>();
for (const row of rows) {
result.set(row.content_hash, JSON.parse(row.embedding));
}
return result;
}
clearEmbeddingCache(model?: string): number {
if (model) {
const stmt = this.db.prepare(
"DELETE FROM embedding_cache WHERE model = ?",
);
return stmt.run(model).changes;
}
const stmt = this.db.prepare("DELETE FROM embedding_cache");
return stmt.run().changes;
}
// ============================================
// Pending Memory Operations
// ============================================
createPendingMemory(input: CreatePendingMemoryInput): PendingMemory {
const id = `pnd_${nanoid(16)}`;
const suggestedAt = Math.floor(Date.now() / 1000);
const stmt = this.db.prepare(`
INSERT INTO pending_memories (id, memory_data, qdrant_id, suggested_at, source, reason)
VALUES (?, ?, ?, ?, ?, ?)
`);
stmt.run(
id,
JSON.stringify(input.memoryData),
input.memoryData.qdrantId,
suggestedAt,
input.source,
input.reason,
);
return {
id,
memoryData: input.memoryData,
suggestedAt,
source: input.source,
reason: input.reason,
};
}
getPendingMemory(id: string): PendingMemory | null {
const stmt = this.db.prepare("SELECT * FROM pending_memories WHERE id = ?");
const row = stmt.get(id) as PendingMemoryRow | undefined;
return row ? this.rowToPendingMemory(row) : null;
}
getPendingMemories(): PendingMemory[] {
const stmt = this.db.prepare(
"SELECT * FROM pending_memories ORDER BY suggested_at DESC",
);
const rows = stmt.all() as PendingMemoryRow[];
return rows.map((row) => this.rowToPendingMemory(row));
}
deletePendingMemory(id: string): boolean {
const stmt = this.db.prepare("DELETE FROM pending_memories WHERE id = ?");
const result = stmt.run(id);
return result.changes > 0;
}
// ============================================
// Row Conversion Helpers
// ============================================
private rowToMemory(row: MemoryRow): Memory {
return {
id: row.id,
type: row.type as Memory["type"],
title: row.title,
content: row.content,
summary: row.summary ?? undefined,
importance: row.importance,
tags: JSON.parse(row.tags),
relatedFiles: JSON.parse(row.related_files),
gitCommit: row.git_commit ?? undefined,
sourcePr: row.source_pr ?? undefined,
experts: JSON.parse(row.experts),
qdrantId: row.qdrant_id ?? undefined,
createdAt: row.created_at,
accessedAt: row.accessed_at,
accessCount: row.access_count ?? 0,
needsReview: (row.needs_review ?? 0) === 1,
// Confidence decay fields
decayRate: row.decay_rate ?? undefined,
lastRefreshedAt: row.last_refreshed_at ?? undefined,
confidenceFloor: row.confidence_floor ?? undefined,
decayFunction:
(row.decay_function as Memory["decayFunction"]) ?? undefined,
};
}
private rowToDocument(row: DocumentRow): Document {
return {
id: row.id,
title: row.title,
content: row.content,
contentHash: row.content_hash ?? undefined,
createdAt: row.created_at,
updatedAt: row.updated_at,
};
}
private rowToChunk(row: ChunkRow): Chunk {
return {
id: row.id,
documentId: row.document_id,
content: row.content,
qdrantId: row.qdrant_id ?? undefined,
startOffset: row.start_offset,
endOffset: row.end_offset,
};
}
private rowToPendingMemory(row: PendingMemoryRow): PendingMemory {
return {
id: row.id,
memoryData: JSON.parse(row.memory_data),
suggestedAt: row.suggested_at,
source: row.source as PendingMemory["source"],
reason: row.reason,
};
}
}
// Row types for SQLite
interface MemoryRow {
id: string;
type: string;
title: string;
content: string;
summary: string | null;
importance: number;
tags: string;
related_files: string;
git_commit: string | null;
source_pr: string | null;
experts: string;
qdrant_id: string | null;
created_at: number;
accessed_at: number;
access_count: number | null;
needs_review: number | null;
// Confidence decay fields
decay_rate: number | null;
last_refreshed_at: number | null;
confidence_floor: number | null;
decay_function: string | null;
}
interface DocumentRow {
id: string;
title: string;
content: string;
content_hash: string | null;
created_at: number;
updated_at: number;
}
interface ChunkRow {
id: string;
document_id: string;
content: string;
qdrant_id: string | null;
start_offset: number;
end_offset: number;
}
interface PendingMemoryRow {
id: string;
memory_data: string;
qdrant_id: string;
suggested_at: number;
source: string;
reason: string;
}
