/**
* Meeting Chief Lite - Database Operations
*/
import Database from 'better-sqlite3';
import { readFileSync } from 'fs';
import { join, dirname } from 'path';
import { fileURLToPath } from 'url';
import type {
Meeting,
TranscriptChunk,
EmbeddingJob,
SyncRun,
MeetingStats
} from './types.js';
const __dirname = dirname(fileURLToPath(import.meta.url));
let db: Database.Database | null = null;
export function getDb(): Database.Database {
if (!db) {
const dbPath = process.env.DATABASE_PATH || join(process.cwd(), 'data', 'meetings.db');
db = new Database(dbPath);
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');
}
return db;
}
export function initDb(): void {
const database = getDb();
const schemaPath = join(__dirname, '..', '..', 'schema.sql');
const schema = readFileSync(schemaPath, 'utf-8');
database.exec(schema);
}
// Meeting operations
export function listMeetings(limit = 50, offset = 0): Meeting[] {
const db = getDb();
const rows = db.prepare(`
SELECT * FROM meetings
ORDER BY start_time DESC
LIMIT ? OFFSET ?
`).all(limit, offset) as any[];
return rows.map(row => ({
...row,
participants: row.participants ? JSON.parse(row.participants) : null
}));
}
export function getMeeting(id: string): Meeting | null {
const db = getDb();
const row = db.prepare('SELECT * FROM meetings WHERE id = ?').get(id) as any;
if (!row) return null;
return {
...row,
participants: row.participants ? JSON.parse(row.participants) : null
};
}
export function getRecentMeetings(days = 7): Meeting[] {
const db = getDb();
const cutoff = new Date();
cutoff.setDate(cutoff.getDate() - days);
const rows = db.prepare(`
SELECT * FROM meetings
WHERE start_time >= ?
ORDER BY start_time DESC
`).all(cutoff.toISOString()) as any[];
return rows.map(row => ({
...row,
participants: row.participants ? JSON.parse(row.participants) : null
}));
}
export function upsertMeeting(meeting: Partial<Meeting> & { id: string }): void {
const db = getDb();
const participants = meeting.participants
? JSON.stringify(meeting.participants)
: null;
db.prepare(`
INSERT INTO meetings (id, otter_id, title, transcript, summary,
start_time, end_time, duration_seconds, participants)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
title = excluded.title,
transcript = excluded.transcript,
summary = excluded.summary,
start_time = excluded.start_time,
end_time = excluded.end_time,
duration_seconds = excluded.duration_seconds,
participants = excluded.participants,
updated_at = datetime('now')
`).run(
meeting.id,
meeting.otter_id ?? null,
meeting.title ?? 'Untitled',
meeting.transcript ?? null,
meeting.summary ?? null,
meeting.start_time ?? null,
meeting.end_time ?? null,
meeting.duration_seconds ?? null,
participants
);
}
// Chunk operations
export function getChunks(meetingId: string): TranscriptChunk[] {
const db = getDb();
return db.prepare(`
SELECT * FROM transcript_chunks
WHERE meeting_id = ?
ORDER BY chunk_index
`).all(meetingId) as TranscriptChunk[];
}
export function insertChunks(chunks: Omit<TranscriptChunk, 'created_at'>[]): void {
const db = getDb();
const stmt = db.prepare(`
INSERT INTO transcript_chunks (id, meeting_id, chunk_index, speaker, content, start_time, end_time)
VALUES (?, ?, ?, ?, ?, ?, ?)
`);
const insertMany = db.transaction((chunks: Omit<TranscriptChunk, 'created_at'>[]) => {
for (const chunk of chunks) {
stmt.run(
chunk.id,
chunk.meeting_id,
chunk.chunk_index,
chunk.speaker,
chunk.content,
chunk.start_time,
chunk.end_time
);
}
});
insertMany(chunks);
}
// Vector operations
export function insertVector(chunkId: string, embedding: Float32Array, model: string): void {
const db = getDb();
const buffer = Buffer.from(embedding.buffer);
db.prepare(`
INSERT OR REPLACE INTO transcript_vectors (id, chunk_id, embedding, model, dimensions)
VALUES (?, ?, ?, ?, ?)
`).run(
`vec_${chunkId}`,
chunkId,
buffer,
model,
embedding.length
);
}
export function getChunksWithVectors(): { chunk: TranscriptChunk; vector: Float32Array }[] {
const db = getDb();
const rows = db.prepare(`
SELECT c.*, v.embedding
FROM transcript_chunks c
JOIN transcript_vectors v ON c.id = v.chunk_id
`).all() as any[];
return rows.map(row => ({
chunk: {
id: row.id,
meeting_id: row.meeting_id,
chunk_index: row.chunk_index,
speaker: row.speaker,
content: row.content,
start_time: row.start_time,
end_time: row.end_time,
created_at: row.created_at
},
vector: new Float32Array(row.embedding.buffer, row.embedding.byteOffset, row.embedding.length / 4)
}));
}
// Embedding job operations
export function createEmbeddingJob(meetingId: string): string {
const db = getDb();
const id = `job_${Date.now()}_${Math.random().toString(36).slice(2, 9)}`;
db.prepare(`
INSERT INTO embedding_jobs (id, meeting_id, status)
VALUES (?, ?, 'pending')
`).run(id, meetingId);
return id;
}
export function getPendingEmbeddingJobs(limit = 10): EmbeddingJob[] {
const db = getDb();
return db.prepare(`
SELECT * FROM embedding_jobs
WHERE status = 'pending' AND retry_count < 3
ORDER BY created_at
LIMIT ?
`).all(limit) as EmbeddingJob[];
}
export function updateEmbeddingJobStatus(
id: string,
status: 'processing' | 'completed' | 'failed',
error?: string
): void {
const db = getDb();
if (status === 'failed') {
db.prepare(`
UPDATE embedding_jobs
SET status = ?, error = ?, retry_count = retry_count + 1
WHERE id = ?
`).run(status, error ?? null, id);
} else {
db.prepare(`
UPDATE embedding_jobs
SET status = ?, completed_at = datetime('now')
WHERE id = ?
`).run(status, id);
}
}
// Sync operations
export function startSyncRun(source = 'otter'): string {
const db = getDb();
const id = `sync_${Date.now()}_${Math.random().toString(36).slice(2, 9)}`;
db.prepare(`
INSERT INTO sync_runs (id, source, status)
VALUES (?, ?, 'running')
`).run(id, source);
return id;
}
export function completeSyncRun(id: string, itemsSynced: number, error?: string): void {
const db = getDb();
const status = error ? 'failed' : 'completed';
db.prepare(`
UPDATE sync_runs
SET status = ?, items_synced = ?, error = ?, completed_at = datetime('now')
WHERE id = ?
`).run(status, itemsSynced, error ?? null, id);
}
export function getLastSyncRun(): SyncRun | null {
const db = getDb();
return db.prepare(`
SELECT * FROM sync_runs
ORDER BY started_at DESC
LIMIT 1
`).get() as SyncRun | null;
}
// Stats
export function getStats(): MeetingStats {
const db = getDb();
const counts = db.prepare(`
SELECT
(SELECT COUNT(*) FROM meetings) as total_meetings,
(SELECT COUNT(*) FROM transcript_chunks) as total_chunks,
(SELECT COUNT(*) FROM transcript_vectors) as total_vectors,
(SELECT MIN(start_time) FROM meetings) as oldest_meeting,
(SELECT MAX(start_time) FROM meetings) as newest_meeting
`).get() as MeetingStats;
return counts;
}
// Settings
export function getSetting(key: string): string | null {
const db = getDb();
const row = db.prepare('SELECT value FROM app_settings WHERE key = ?').get(key) as { value: string } | undefined;
return row?.value ?? null;
}
export function setSetting(key: string, value: string): void {
const db = getDb();
db.prepare(`
INSERT INTO app_settings (key, value, updated_at)
VALUES (?, ?, datetime('now'))
ON CONFLICT(key) DO UPDATE SET
value = excluded.value,
updated_at = datetime('now')
`).run(key, value);
}
