Add data migration for existing recordings — populate SessionTags from segment speaker/subject IDs #21

New issue

Closed

opened 2026-05-23 01:23:01 +00:00 by Mark · 1 comment

Mark commented

2026-05-23 01:23:01 +00:00

Owner

Parent Issue

#6 — No speaker/subject assignment during live recording
#11 — Implementation plan

Description

Existing recordings have speaker/subject assignments only at the segment level (TranscriptSegment.speakerId, TranscriptSegment.subjectId). To support the new time-range features (topic timeline, speaker transitions), existing data needs to be backfilled into SessionTag entries.

Scope

Add a MIGRATION_3_4 in AppDatabase that:
1. For each session with segments that have speakerId IS NOT NULL:
  - Group consecutive segments with the same speakerId
  - Create a SessionTag(tagType='SPEAKER', referenceId=speakerId, startOffsetMs=<first segment offset>, endOffsetMs=<last segment offset>) for each group
2. For each session with segments that have subjectId IS NOT NULL:
  - Group consecutive segments with the same subjectId
  - Create a SessionTag(tagType='SUBJECT', referenceId=subjectId, startOffsetMs=<first segment offset>, endOffsetMs=<last segment offset>) for each group
3. Bump database version from 3 to 4
Calculate startOffsetMs as the difference between segment timestamp and session start_time
Calculate endOffsetMs as the start of the next group (or NULL for the last group)

Acceptance Criteria

MIGRATION_3_4 correctly backfills SessionTag entries from existing segment data
No data loss — existing segment speakerId/subjectId values are preserved
Migration is idempotent — running twice produces the same result
Database version bumped to 4
Existing recordings display speaker/subject time-ranges in the new UI features

Dependencies

None (can be implemented independently; UI features depend on this data being present)

## Parent Issue #6 — No speaker/subject assignment during live recording #11 — Implementation plan ## Description Existing recordings have speaker/subject assignments only at the segment level (`TranscriptSegment.speakerId`, `TranscriptSegment.subjectId`). To support the new time-range features (topic timeline, speaker transitions), existing data needs to be backfilled into `SessionTag` entries. ## Scope - Add a `MIGRATION_3_4` in `AppDatabase` that: 1. For each session with segments that have `speakerId IS NOT NULL`: - Group consecutive segments with the same speakerId - Create a `SessionTag(tagType='SPEAKER', referenceId=speakerId, startOffsetMs=<first segment offset>, endOffsetMs=<last segment offset>)` for each group 2. For each session with segments that have `subjectId IS NOT NULL`: - Group consecutive segments with the same subjectId - Create a `SessionTag(tagType='SUBJECT', referenceId=subjectId, startOffsetMs=<first segment offset>, endOffsetMs=<last segment offset>)` for each group 3. Bump database version from 3 to 4 - Calculate `startOffsetMs` as the difference between segment timestamp and session start_time - Calculate `endOffsetMs` as the start of the next group (or NULL for the last group) ## Acceptance Criteria - [ ] MIGRATION_3_4 correctly backfills SessionTag entries from existing segment data - [ ] No data loss — existing segment speakerId/subjectId values are preserved - [ ] Migration is idempotent — running twice produces the same result - [ ] Database version bumped to 4 - [ ] Existing recordings display speaker/subject time-ranges in the new UI features ## Dependencies - None (can be implemented independently; UI features depend on this data being present)