import type Database from 'better-sqlite3';
export function runMigrations(db: Database.Database): void {
db.exec(`
CREATE TABLE IF NOT EXISTS documents (
id TEXT PRIMARY KEY,
scope TEXT NOT NULL DEFAULT 'default',
meta TEXT NOT NULL DEFAULT '{}',
content_hash TEXT,
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX IF NOT EXISTS idx_documents_scope ON documents(scope);
CREATE TABLE IF NOT EXISTS chunks (
id TEXT PRIMARY KEY,
document_id TEXT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
content TEXT NOT NULL,
embedding BLOB,
chunk_index INTEGER NOT NULL,
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
UNIQUE(document_id, chunk_index)
);
CREATE INDEX IF NOT EXISTS idx_chunks_document ON chunks(document_id);
CREATE VIRTUAL TABLE IF NOT EXISTS chunks_fts USING fts5(
content,
content='chunks',
content_rowid='rowid'
);
CREATE TRIGGER IF NOT EXISTS chunks_ai AFTER INSERT ON chunks BEGIN
INSERT INTO chunks_fts(rowid, content) VALUES (new.rowid, new.content);
END;
CREATE TRIGGER IF NOT EXISTS chunks_ad AFTER DELETE ON chunks BEGIN
INSERT INTO chunks_fts(chunks_fts, rowid, content) VALUES('delete', old.rowid, old.content);
END;
CREATE TRIGGER IF NOT EXISTS chunks_au AFTER UPDATE ON chunks BEGIN
INSERT INTO chunks_fts(chunks_fts, rowid, content) VALUES('delete', old.rowid, old.content);
INSERT INTO chunks_fts(chunks_fts, rowid, content) VALUES (new.rowid, new.content);
END;
CREATE TABLE IF NOT EXISTS audit_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tool_name TEXT NOT NULL,
session_id TEXT,
input_summary TEXT,
output_summary TEXT,
duration_ms INTEGER,
created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at);
CREATE TABLE IF NOT EXISTS cache_entries (
key TEXT PRIMARY KEY,
value TEXT NOT NULL,
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
ttl_ms INTEGER NOT NULL
);
-- Dashboard tables
CREATE TABLE IF NOT EXISTS dashboard_users (
id TEXT PRIMARY KEY,
username TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
created_at INTEGER NOT NULL DEFAULT (unixepoch()),
last_login INTEGER
);
CREATE TABLE IF NOT EXISTS dashboard_sessions (
id TEXT PRIMARY KEY,
user_id TEXT NOT NULL REFERENCES dashboard_users(id) ON DELETE CASCADE,
csrf_token TEXT NOT NULL,
expires_at INTEGER NOT NULL,
created_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON dashboard_sessions(expires_at);
CREATE TABLE IF NOT EXISTS metrics_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tool_name TEXT NOT NULL,
call_count INTEGER NOT NULL,
total_ms INTEGER NOT NULL,
errors INTEGER NOT NULL,
avg_ms INTEGER NOT NULL,
snapshot_at INTEGER NOT NULL DEFAULT (unixepoch())
);
CREATE INDEX IF NOT EXISTS idx_metrics_snapshot ON metrics_history(snapshot_at);
`);
}
