Feature Request: Add the missing transcript chunk index and stop sorting the recording pagination count

[devkit415]

Description

TranscriptChunk.recording_id has no index, so every query that loads, counts or deletes the chunks of one recording scans the whole transcript_chunk table, which also stores the embedding BLOBs. In the live schema the table only has idx_user_speaker_name and ix_transcript_chunk_speaker_name; neither can serve a recording_id lookup.

SQLite and PostgreSQL do not index foreign key columns automatically, and the model declares the column as a plain foreign key (src/models/recording.py:510) with only those two indexes (src/models/recording.py:516, src/models/recording.py:521-522). This makes transcript editing, reprocessing, speaker rename and merge, chunk deletion and per-recording chunk counts scale with the total number of chunks in the whole installation instead of with the number of chunks in the recording.

The paginated recordings list also pays an unnecessary sort. The endpoint orders the statement by created_at (src/api/recordings.py:1745) and then builds the pagination count from that already ordered statement (src/api/recordings.py:1748-1749). The count only needs a number, so the database sorts the full accessible set twice per page request even though one of the two uses discards the order. On a library of 2,004 accessible recordings the count takes about 55 ms with the inherited sort and about 2.6 ms without it.

Affected Version

v0.10.14-alpha

Requested Feature

Add an index on transcript_chunk(recording_id) for both fresh and existing databases, and build the pagination count from an unordered statement. Both changes are additive, do not alter any API response, and can ship independently of any other performance work.

Proposed Implementation

• Add a model-level index so fresh databases get it from db.create_all(): add db.Index('ix_transcript_chunk_recording', 'recording_id') to the __table_args__ tuple of TranscriptChunk in src/models/recording.py (next to idx_user_speaker_name at src/models/recording.py:521-522), or attach a db.Index to the recording_id column (src/models/recording.py:510).
• Add an idempotent migration for existing databases, because db.create_all() does not alter tables that already exist (src/init_db.py:114). Inside _run_migrations (src/init_db.py:138), call create_index_if_not_exists(engine, 'ix_transcript_chunk_recording', 'transcript_chunk', 'recording_id'), the same helper used for the other recording indexes (src/init_db.py:246-247). The helper quotes table names per dialect, so the call works on SQLite and PostgreSQL alike (src/utils/database.py:272-322, src/utils/database.py:302-320).
• Do not drop or recreate the table, and do not rewrite embedded vectors; this is a plain secondary index.
• Remove the inherited sort from the pagination count in src/api/recordings.py: count from an unordered copy of the statement, for example unordered = stmt.order_by(None) and then select(db.func.count()).select_from(unordered.subquery()), in place of select(db.func.count()).select_from(stmt.subquery()) (src/api/recordings.py:1748). Keep the ordered stmt for the page query itself (src/api/recordings.py:1753, executed at src/api/recordings.py:1756).
• Add a regression test that asserts the index exists after initialize_database on a database created with the older schema, and a test that asserts the count statement carries no ORDER BY (for example by checking str(count_stmt) or by capturing the emitted SQL).

Expected Behavior

• A query that selects, counts or deletes the TranscriptChunk rows of one recording uses ix_transcript_chunk_recording instead of scanning transcript_chunk.
• Per-recording chunk work (transcript edits, reprocess, speaker rename and merge, chunk deletion, chunk counts) takes time proportional to that recording's chunks, not to the whole table.
• The pagination count returns the same total as before and no longer executes a sort.
• Both a fresh database and an existing database upgraded by initialize_database have the index; on SQLite, PRAGMA index_list('transcript_chunk') shows ix_transcript_chunk_recording after the upgrade.
• No API payload, status code or response shape changes.

Root Cause Analysis

TranscriptChunk declares recording_id as a foreign key without an index (src/models/recording.py:510). The only indexes on the table are ix_transcript_chunk_speaker_name (src/models/recording.py:516) and the composite idx_user_speaker_name (src/models/recording.py:521-522), because neither SQLite nor PostgreSQL indexes foreign key columns automatically. Point queries that filter by recording_id therefore fall back to a full table scan.

Examples are the per-recording chunk delete and count paths in src/services/embeddings.py:561 and src/services/embeddings.py:584, the chunk count before deletion at src/api/recordings.py:3274, the admin per-recording chunk count at src/api/admin.py:1120, and the migration backfill count at src/init_db.py:1176.

The same filter is bound in the chunk queries at src/services/embeddings.py:623-624 and src/services/embeddings.py:791-792, where it is one condition among several.

For the count, the endpoint builds the ordered list statement first (src/api/recordings.py:1745) and then wraps it for the count (src/api/recordings.py:1748-1749). SQLAlchemy preserves the ORDER BY inside the subquery, so SQLite reports USE TEMP B-TREE FOR ORDER BY for a query that returns a single scalar.

[murtaza-nasir]

Both are in v0.10.15-alpha: an index on transcript_chunk (recording_id, chunk_index), created at startup, and the recording count without the ordering. Thank you for the suggestion.