Lumotia/docs/architecture-map/05-core-storage-hotkey-build/storage-crud-transcripts.md

name, type, slice, last_verified

name	type	slice	last_verified
Storage transcripts CRUD	architecture-map-page	05-core-storage-hotkey-build	2026/05/09

Storage transcripts CRUD

Where you are: Architecture map → Core, Storage, Hotkey, Build → Storage transcripts CRUD

Plain English summary. All the read/write operations against the transcripts table. Inserts validate the profile FK at the application layer in addition to the database constraint so the error message is friendly. Updates split between content updates (update_transcript) and metadata updates (update_transcript_meta).

At a glance

• Source: crates/storage/src/database.rs:80-289.
• Public surface: insert_transcript, get_transcript, list_transcripts, list_transcripts_paged, count_transcripts, update_transcript, update_transcript_meta, delete_transcript, search_transcripts.
• Public types: InsertTranscriptParams<'a> (input), TranscriptRow (output).
• Consumers: slice 2 transcription command (the post-inference write), the history command (reads), the export plumbing, the MCP server.

Public types

InsertTranscriptParams<'a> — crates/storage/src/database.rs:61

Borrowed-string parameter struct. 16 fields; one-to-one with the v1+v8+v14 column set:

pub struct InsertTranscriptParams<'a> {
    pub id: &'a str,
    pub text: &'a str,
    pub source: &'a str,
    pub profile_id: &'a str,
    pub title: Option<&'a str>,
    pub audio_path: Option<&'a str>,
    pub duration: f64,
    pub engine: Option<&'a str>,
    pub model_id: Option<&'a str>,
    pub inference_ms: Option<i64>,
    pub sample_rate: Option<i64>,
    pub audio_channels: Option<i64>,
    pub format_mode: Option<&'a str>,
    pub remove_fillers: bool,
    pub british_english: bool,
    pub anti_hallucination: bool,
}

segments_json, manual_tags, template, language, starred, llm_tags are not on the insert struct — they default at the column level ('' or 0) and are populated later via update_transcript_meta.

TranscriptRow — crates/storage/src/database.rs:793

pub struct TranscriptRow {
    pub id: String,
    pub text: String,
    pub source: String,
    pub profile_id: String,
    pub title: Option<String>,
    pub audio_path: Option<String>,
    pub duration: f64,
    pub engine: Option<String>,
    pub model_id: Option<String>,
    pub inference_ms: Option<i64>,
    pub sample_rate: Option<i64>,
    pub audio_channels: Option<i64>,
    pub format_mode: Option<String>,
    pub remove_fillers: bool,
    pub british_english: bool,
    pub anti_hallucination: bool,
    pub created_at: String,
    pub starred: bool,
    pub manual_tags: String,
    pub template: String,
    pub language: String,
    pub segments_json: String,
    pub llm_tags: String,  // v14
}

Functions

insert_transcript(pool, &params) -> Result<()> — crates/storage/src/database.rs:80

1. Pre-flight FK check. profile_exists(pool, params.profile_id) (private, database.rs:1094) runs SELECT 1 FROM profiles WHERE id = ?. If the profile is not present, returns a typed lumotia_storage::Error::InvalidReference { entity: Entity::Profile, reason: "unknown profile id '...'" }. Without this, sqlite would raise FOREIGN KEY constraint failed which the frontend cannot easily disambiguate.
2. Single INSERT INTO transcripts (...) VALUES (...) with all 16 fields.

get_transcript(pool, id) -> Result<Option<TranscriptRow>> — crates/storage/src/database.rs:117

Single-row select. Returns Ok(None) for missing id (not Err).

list_transcripts(pool, limit) -> Result<Vec<TranscriptRow>> — crates/storage/src/database.rs:129

Calls list_transcripts_paged(pool, limit, 0).

list_transcripts_paged(pool, limit, offset) -> Result<Vec<TranscriptRow>> — crates/storage/src/database.rs:135

SELECT ... FROM transcripts ORDER BY created_at DESC LIMIT ? OFFSET ?. The History page in slice 1 paginates through this.

count_transcripts(pool) -> Result<i64> — crates/storage/src/database.rs:153

SELECT COUNT(*) FROM transcripts. Used by the History page to render (N total) and to decide whether to render the empty state.

update_transcript(pool, id, ...) -> Result<()> — crates/storage/src/database.rs:168

Updates the content fields: text, title, language, segments_json. Triggers transcripts_au so the FTS index is refreshed.

update_transcript_meta(pool, id, ...) -> Result<()> — crates/storage/src/database.rs:221

Updates the metadata fields: starred, manual_tags, template, plus llm_tags (post-v14). Separate from update_transcript because the user-driven star / manual-tag actions are common and we do not want to rebuild FTS index for a starring change. The transcripts_au trigger does fire either way (it watches AFTER UPDATE ON transcripts with no column predicate), so worth verifying that single-column updates do not bloat the FTS table over time.

delete_transcript(pool, id) -> Result<()> — crates/storage/src/database.rs:257

DELETE FROM transcripts WHERE id = ?. The cascade on segments.transcript_id removes child rows. The transcripts_ad trigger removes the row from the FTS index.

search_transcripts(pool, query, limit) -> Result<Vec<TranscriptRow>> — crates/storage/src/database.rs:270

Full-text search via FTS5. Detailed under storage-fts5-search.md.

Data flow / contract

• All reads return TranscriptRow shaped from the same private helper transcript_row_from(&SqliteRow) at database.rs:856.
• All writes propagate to FTS via the schema-level triggers; no application-level FTS handling is needed.
• The profile_id is required at insert time; there is no path to an orphaned transcript post-v9.

Watch-outs

• update_transcript_meta does not bump created_at. Correct behaviour: starring a transcript shouldn't change its sort position.
• Stamp columns (engine, model_id, inference_ms, sample_rate, audio_channels, format_mode) are written once at insert and never updated. They are provenance, not state.
• remove_fillers, british_english, anti_hallucination are also stamped once at insert. They record what setting was active when the transcript was produced, not the user's current preference.
• No bulk insert. Each transcript is one round-trip. A future bulk-import path would batch BEGIN; INSERT...x; COMMIT for throughput.

See also

• Schema and migrations
• FTS5 search
• Storage overview
• Slice 2 transcription command — the immediate caller of insert_transcript.