pluto
052f019075
SQLite database layer with thread-local connections, WAL journal mode, foreign key enforcement, and FTS5 full-text search on utterances via content-table triggers. TDD: 5 tests written first, all passing. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
89 lines
3.3 KiB
Python
89 lines
3.3 KiB
Python
import sqlite3
|
|
import threading
|
|
from pathlib import Path
|
|
from app.shared.config import Settings
|
|
|
|
_local = threading.local()
|
|
|
|
|
|
def get_db(settings: Settings) -> sqlite3.Connection:
|
|
if not hasattr(_local, "conn") or _local.conn is None:
|
|
conn = sqlite3.connect(settings.db_path, check_same_thread=False)
|
|
conn.row_factory = sqlite3.Row
|
|
conn.execute("PRAGMA journal_mode=WAL")
|
|
conn.execute("PRAGMA foreign_keys=ON")
|
|
_local.conn = conn
|
|
return _local.conn
|
|
|
|
|
|
def init_schema(settings: Settings) -> None:
|
|
Path(settings.clips_dir).mkdir(parents=True, exist_ok=True)
|
|
db = get_db(settings)
|
|
db.executescript("""
|
|
CREATE TABLE IF NOT EXISTS rooms (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL UNIQUE,
|
|
device_label TEXT,
|
|
is_active BOOLEAN DEFAULT 0,
|
|
last_seen DATETIME,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS speakers (
|
|
id INTEGER PRIMARY KEY,
|
|
name TEXT NOT NULL,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS voice_embeddings (
|
|
id INTEGER PRIMARY KEY,
|
|
speaker_id INTEGER NOT NULL REFERENCES speakers(id) ON DELETE CASCADE,
|
|
embedding BLOB NOT NULL,
|
|
audio_sample BLOB,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS utterances (
|
|
id INTEGER PRIMARY KEY,
|
|
room_id INTEGER NOT NULL REFERENCES rooms(id),
|
|
speaker_id INTEGER REFERENCES speakers(id),
|
|
transcript TEXT NOT NULL,
|
|
embedding BLOB,
|
|
match_status TEXT NOT NULL DEFAULT 'unreviewed',
|
|
match_confidence REAL,
|
|
start_time DATETIME NOT NULL,
|
|
end_time DATETIME NOT NULL,
|
|
audio_clip_path TEXT,
|
|
livekit_room TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE VIRTUAL TABLE IF NOT EXISTS utterances_fts USING fts5(
|
|
transcript, content=utterances, content_rowid=id
|
|
);
|
|
|
|
CREATE TRIGGER IF NOT EXISTS utterances_ai AFTER INSERT ON utterances BEGIN
|
|
INSERT INTO utterances_fts(rowid, transcript) VALUES (new.id, new.transcript);
|
|
END;
|
|
|
|
CREATE TRIGGER IF NOT EXISTS utterances_au AFTER UPDATE OF transcript ON utterances BEGIN
|
|
INSERT INTO utterances_fts(utterances_fts, rowid, transcript)
|
|
VALUES ('delete', old.id, old.transcript);
|
|
INSERT INTO utterances_fts(rowid, transcript) VALUES (new.id, new.transcript);
|
|
END;
|
|
|
|
CREATE TRIGGER IF NOT EXISTS utterances_ad AFTER DELETE ON utterances BEGIN
|
|
INSERT INTO utterances_fts(utterances_fts, rowid, transcript)
|
|
VALUES ('delete', old.id, old.transcript);
|
|
END;
|
|
|
|
CREATE TABLE IF NOT EXISTS daily_summaries (
|
|
id INTEGER PRIMARY KEY,
|
|
date DATE NOT NULL UNIQUE,
|
|
summary_text TEXT NOT NULL,
|
|
generated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""")
|
|
db.commit()
|