-- Meeting Chief Lite - Minimal Schema
-- 7 tables for meeting sync and semantic search
-- Core: Meeting metadata and transcripts
CREATE TABLE IF NOT EXISTS meetings (
id TEXT PRIMARY KEY,
otter_id TEXT UNIQUE,
title TEXT NOT NULL,
transcript TEXT,
summary TEXT,
start_time TEXT,
end_time TEXT,
duration_seconds INTEGER,
participants TEXT, -- JSON array of participant names
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_meetings_start_time ON meetings(start_time);
CREATE INDEX IF NOT EXISTS idx_meetings_otter_id ON meetings(otter_id);
-- Core: Parsed transcript segments with speaker info
CREATE TABLE IF NOT EXISTS transcript_chunks (
id TEXT PRIMARY KEY,
meeting_id TEXT NOT NULL REFERENCES meetings(id) ON DELETE CASCADE,
chunk_index INTEGER NOT NULL,
speaker TEXT,
content TEXT NOT NULL,
start_time REAL, -- Seconds from meeting start
end_time REAL,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_chunks_meeting_id ON transcript_chunks(meeting_id);
CREATE INDEX IF NOT EXISTS idx_chunks_speaker ON transcript_chunks(speaker);
-- Semantic search: Embeddings for transcript chunks
CREATE TABLE IF NOT EXISTS transcript_vectors (
id TEXT PRIMARY KEY,
chunk_id TEXT NOT NULL REFERENCES transcript_chunks(id) ON DELETE CASCADE,
embedding BLOB NOT NULL, -- Float32 array as binary
model TEXT NOT NULL DEFAULT 'text-embedding-3-small',
dimensions INTEGER NOT NULL DEFAULT 1536,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_vectors_chunk_id ON transcript_vectors(chunk_id);
-- Processing: Embedding generation queue
CREATE TABLE IF NOT EXISTS embedding_jobs (
id TEXT PRIMARY KEY,
meeting_id TEXT NOT NULL REFERENCES meetings(id) ON DELETE CASCADE,
status TEXT NOT NULL DEFAULT 'pending', -- pending, processing, completed, failed
error TEXT,
retry_count INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now')),
completed_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_embedding_jobs_status ON embedding_jobs(status);
CREATE INDEX IF NOT EXISTS idx_embedding_jobs_meeting ON embedding_jobs(meeting_id);
-- Sync: Audit log of sync operations
CREATE TABLE IF NOT EXISTS sync_runs (
id TEXT PRIMARY KEY,
source TEXT NOT NULL DEFAULT 'otter',
status TEXT NOT NULL, -- running, completed, failed
items_synced INTEGER DEFAULT 0,
error TEXT,
started_at TEXT DEFAULT (datetime('now')),
completed_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_sync_runs_started ON sync_runs(started_at);
-- Optional: People roster for speaker identification
CREATE TABLE IF NOT EXISTS speakers (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
email TEXT,
aliases TEXT, -- JSON array of alternate names
first_seen TEXT,
last_seen TEXT,
meeting_count INTEGER DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_speakers_name ON speakers(name);
-- System: Key-value configuration store
CREATE TABLE IF NOT EXISTS app_settings (
key TEXT PRIMARY KEY,
value TEXT NOT NULL,
updated_at TEXT DEFAULT (datetime('now'))
);
-- Initialize default settings
INSERT OR IGNORE INTO app_settings (key, value) VALUES
('schema_version', '1'),
('last_sync', NULL),
('embedding_model', 'text-embedding-3-small');
