SQL

CREATE TABLE "audio_transcriptions"  (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  audio_chunk_id INTEGER NOT NULL,
  offset_index INTEGER NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  transcription TEXT NOT NULL,
  device TEXT NOT NULL DEFAULT '',
  is_input_device BOOLEAN NOT NULL DEFAULT TRUE,
  speaker_id INTEGER,
  transcription_engine TEXT NOT NULL DEFAULT 'Whisper',
  start_time REAL,
  end_time REAL,
  text_length INTEGER,
  sync_id TEXT,
  synced_at DATETIME,
  FOREIGN KEY (audio_chunk_id) REFERENCES audio_chunks(id)
)

Columns

Column Data type Allow null Primary key Actions
id INTEGER ✓ ✓ read-only
audio_chunk_id INTEGER read-only
offset_index INTEGER read-only
timestamp TIMESTAMP read-only
transcription TEXT read-only
device TEXT read-only
is_input_device BOOLEAN read-only
speaker_id INTEGER ✓ read-only
transcription_engine TEXT read-only
start_time REAL ✓ read-only
end_time REAL ✓ read-only
text_length INTEGER ✓ read-only
sync_id TEXT ✓ read-only
synced_at DATETIME ✓ read-only

Foreign Keys

Column Destination
audio_chunk_id audio_chunks.id

Indexes

Name Columns Unique SQL Drop?
idx_audio_transcription_chunk_text
  • audio_chunk_id
  • transcription
 ✓ SQL 
CREATE UNIQUE INDEX idx_audio_transcription_chunk_text
ON audio_transcriptions(audio_chunk_id, transcription)
read-only
idx_audio_transcriptions_audio_chunk_id audio_chunk_id SQL 
CREATE INDEX idx_audio_transcriptions_audio_chunk_id
ON audio_transcriptions(audio_chunk_id)
read-only
idx_audio_transcriptions_audio_chunk_id_timestamp
  • audio_chunk_id
  • timestamp
 SQL 
CREATE INDEX idx_audio_transcriptions_audio_chunk_id_timestamp
ON audio_transcriptions(audio_chunk_id, timestamp)
read-only
idx_audio_transcriptions_length text_length SQL 
CREATE INDEX idx_audio_transcriptions_length
ON audio_transcriptions (text_length)
read-only
idx_audio_transcriptions_speaker_id_timestamp
  • speaker_id
  • timestamp
 SQL 
CREATE INDEX idx_audio_transcriptions_speaker_id_timestamp
ON audio_transcriptions(speaker_id, timestamp DESC)
read-only
idx_audio_transcriptions_sync_id sync_id SQL 
CREATE INDEX idx_audio_transcriptions_sync_id
ON audio_transcriptions(sync_id) WHERE sync_id IS NOT NULL
read-only
idx_audio_transcriptions_timestamp timestamp SQL 
CREATE INDEX idx_audio_transcriptions_timestamp
ON audio_transcriptions(timestamp)
read-only
idx_audio_transcriptions_transcription transcription SQL 
CREATE INDEX idx_audio_transcriptions_transcription
ON audio_transcriptions(transcription)
read-only

Triggers

Name SQL Drop?
audio_transcriptions_ai SQL 
CREATE TRIGGER audio_transcriptions_ai AFTER INSERT ON audio_transcriptions
WHEN NEW.transcription IS NOT NULL AND NEW.transcription != ''
BEGIN
    INSERT INTO audio_transcriptions_fts(rowid, transcription, device, speaker_id)
    VALUES (NEW.id, NEW.transcription, COALESCE(NEW.device, ''), NEW.speaker_id);
END
read-only
audio_transcriptions_delete SQL 
CREATE TRIGGER audio_transcriptions_delete AFTER DELETE ON audio_transcriptions
WHEN OLD.transcription IS NOT NULL AND OLD.transcription != ''
BEGIN
    INSERT INTO audio_transcriptions_fts(audio_transcriptions_fts, rowid, transcription, device, speaker_id)
    VALUES ('delete', OLD.id, OLD.transcription, COALESCE(OLD.device, ''), OLD.speaker_id);
END
read-only
audio_transcriptions_update SQL 
CREATE TRIGGER audio_transcriptions_update AFTER UPDATE ON audio_transcriptions
WHEN OLD.transcription IS NOT NULL AND OLD.transcription != ''
BEGIN
    INSERT INTO audio_transcriptions_fts(audio_transcriptions_fts, rowid, transcription, device, speaker_id)
    VALUES ('delete', OLD.id, OLD.transcription, COALESCE(OLD.device, ''), OLD.speaker_id);
    INSERT INTO audio_transcriptions_fts(rowid, transcription, device, speaker_id)
    VALUES (NEW.id, COALESCE(NEW.transcription, ''), COALESCE(NEW.device, ''), NEW.speaker_id);
END
read-only