import Database, { Database as DatabaseType } from 'better-sqlite3';
import path from 'path';
import fs from 'fs';
const DATA_DIR = process.env.DATA_DIR || './data';
const DB_PATH = path.join(DATA_DIR, 'memory.db');
// Ensure data directory exists
if (!fs.existsSync(DATA_DIR)) {
fs.mkdirSync(DATA_DIR, { recursive: true });
}
const db: DatabaseType = new Database(DB_PATH);
// Enable WAL mode for better performance
db.pragma('journal_mode = WAL');
// Create tables
db.exec(`
CREATE TABLE IF NOT EXISTS memories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
content TEXT NOT NULL,
scope TEXT NOT NULL CHECK (scope IN ('global', 'project')),
project_id TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS memory_tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
memory_id INTEGER NOT NULL,
tag TEXT NOT NULL,
FOREIGN KEY (memory_id) REFERENCES memories(id) ON DELETE CASCADE,
UNIQUE(memory_id, tag)
);
CREATE INDEX IF NOT EXISTS idx_memories_scope ON memories(scope);
CREATE INDEX IF NOT EXISTS idx_memories_project_id ON memories(project_id);
CREATE INDEX IF NOT EXISTS idx_memories_created_at ON memories(created_at);
CREATE INDEX IF NOT EXISTS idx_memory_tags_tag ON memory_tags(tag);
CREATE INDEX IF NOT EXISTS idx_memory_tags_memory_id ON memory_tags(memory_id);
`);
export interface Memory {
id: number;
content: string;
scope: 'global' | 'project';
project_id: string | null;
tags: string[];
created_at: string;
updated_at: string;
}
export interface CreateMemoryInput {
content: string;
scope: 'global' | 'project';
project_id?: string;
tags?: string[];
}
export interface UpdateMemoryInput {
content?: string;
tags?: string[];
}
// Prepared statements for better performance
const insertMemoryStmt = db.prepare(`
INSERT INTO memories (content, scope, project_id)
VALUES (?, ?, ?)
`);
const insertTagStmt = db.prepare(`
INSERT OR IGNORE INTO memory_tags (memory_id, tag)
VALUES (?, ?)
`);
const deleteTagsStmt = db.prepare(`
DELETE FROM memory_tags WHERE memory_id = ?
`);
const updateMemoryStmt = db.prepare(`
UPDATE memories SET content = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?
`);
const deleteMemoryStmt = db.prepare(`
DELETE FROM memories WHERE id = ?
`);
const getMemoryByIdStmt = db.prepare(`
SELECT * FROM memories WHERE id = ?
`);
const getTagsForMemoryStmt = db.prepare(`
SELECT tag FROM memory_tags WHERE memory_id = ?
`);
export function createMemory(input: CreateMemoryInput): Memory {
const { content, scope, project_id, tags = [] } = input;
const result = insertMemoryStmt.run(content, scope, project_id || null);
const memoryId = result.lastInsertRowid as number;
for (const tag of tags) {
insertTagStmt.run(memoryId, tag.toLowerCase().trim());
}
return getMemoryById(memoryId)!;
}
export function getMemoryById(id: number): Memory | null {
const row = getMemoryByIdStmt.get(id) as any;
if (!row) return null;
const tags = (getTagsForMemoryStmt.all(id) as any[]).map(t => t.tag);
return {
id: row.id,
content: row.content,
scope: row.scope,
project_id: row.project_id,
tags,
created_at: row.created_at,
updated_at: row.updated_at,
};
}
export function getAllMemories(projectId?: string): Memory[] {
let query = `
SELECT * FROM memories
WHERE scope = 'global'
`;
if (projectId) {
query += ` OR (scope = 'project' AND project_id = ?)`;
}
query += ` ORDER BY created_at DESC`;
const stmt = db.prepare(query);
const rows = projectId ? stmt.all(projectId) : stmt.all();
return (rows as any[]).map(row => {
const tags = (getTagsForMemoryStmt.all(row.id) as any[]).map(t => t.tag);
return {
id: row.id,
content: row.content,
scope: row.scope,
project_id: row.project_id,
tags,
created_at: row.created_at,
updated_at: row.updated_at,
};
});
}
export function searchMemories(query: string, projectId?: string): Memory[] {
const searchPattern = `%${query.toLowerCase()}%`;
let sql = `
SELECT DISTINCT m.* FROM memories m
LEFT JOIN memory_tags mt ON m.id = mt.memory_id
WHERE (LOWER(m.content) LIKE ? OR LOWER(mt.tag) LIKE ?)
AND (m.scope = 'global'
`;
if (projectId) {
sql += ` OR (m.scope = 'project' AND m.project_id = ?)`;
}
sql += `) ORDER BY m.created_at DESC`;
const stmt = db.prepare(sql);
const rows = projectId
? stmt.all(searchPattern, searchPattern, projectId)
: stmt.all(searchPattern, searchPattern);
return (rows as any[]).map(row => {
const tags = (getTagsForMemoryStmt.all(row.id) as any[]).map(t => t.tag);
return {
id: row.id,
content: row.content,
scope: row.scope,
project_id: row.project_id,
tags,
created_at: row.created_at,
updated_at: row.updated_at,
};
});
}
export function updateMemory(id: number, input: UpdateMemoryInput): Memory | null {
const existing = getMemoryById(id);
if (!existing) return null;
if (input.content !== undefined) {
updateMemoryStmt.run(input.content, id);
}
if (input.tags !== undefined) {
deleteTagsStmt.run(id);
for (const tag of input.tags) {
insertTagStmt.run(id, tag.toLowerCase().trim());
}
}
return getMemoryById(id);
}
export function deleteMemory(id: number): boolean {
const result = deleteMemoryStmt.run(id);
return result.changes > 0;
}
export function findMemoryByContext(context: string, projectId?: string): Memory | null {
// Search for a memory that matches the context description
const memories = searchMemories(context, projectId);
return memories.length > 0 ? memories[0] : null;
}
export function listMemories(projectId?: string): Memory[] {
return getAllMemories(projectId);
}
export function getMemoryCount(): { global: number; project: number; total: number } {
const globalCount = (db.prepare(`SELECT COUNT(*) as count FROM memories WHERE scope = 'global'`).get() as any).count;
const projectCount = (db.prepare(`SELECT COUNT(*) as count FROM memories WHERE scope = 'project'`).get() as any).count;
return {
global: globalCount,
project: projectCount,
total: globalCount + projectCount,
};
}
// Export db for potential direct access (e.g., in tests)
export { db };
