🏠 Overview › ✅ Implementation Tasks ›✅ epic-speech-to-text-input-foundation-task-001

Define PartialTranscription data model and SQLite schema

epic-speech-to-text-input-foundation-task-001 — Design and implement the SQLite table schema for persisting in-flight dictation transcriptions. Define the PartialTranscription entity with fields for session ID, timestamp, partial text content, feature context (e.g., way-forward field), and persistence status. Include migration script and Dart model class with fromJson/toJson serialization.

critical priority low complexity database pending database specialist Tier 0

Acceptance Criteria

SQLite table `partial_transcriptions` is created with columns: id (TEXT PRIMARY KEY), session_id (TEXT NOT NULL), created_at (INTEGER NOT NULL, Unix ms), updated_at (INTEGER NOT NULL, Unix ms), partial_text (TEXT NOT NULL), feature_context (TEXT NOT NULL), persistence_status (TEXT NOT NULL, CHECK IN ('in_flight','committed','stale'))
Migration script uses versioned migration pattern (e.g., version 1 → 2) and is idempotent
Dart `PartialTranscription` class has all corresponding fields with correct Dart types
fromJson() correctly deserializes all fields including enum parsing for persistence_status
toJson() correctly serializes all fields ready for SQLite insertion
copyWith() method is implemented for immutable updates
All field constraints (NOT NULL, CHECK) are enforced at the database level, not only in Dart
Model includes a static `tableName` constant matching the actual table name
Migration runs successfully on a fresh database and on an existing database without data loss

Technical Requirements

frameworks
Flutter
Riverpod
apis
sqflite or drift SQLite package
data models
PartialTranscription
performance requirements
Table must have an index on session_id for fast session-scoped queries
Index on created_at for efficient stale record cleanup queries
security requirements
partial_text must not be logged in plaintext in debug output
session_id must be a UUID (not a sequential integer) to prevent session enumeration

Execution Context

Execution Tier
Tier 0

Tier 0 - 440 tasks

Implementation Notes

Use drift or sqflite — whichever is already in the project's pubspec.yaml. Do not introduce a second SQLite library. Define `PersistenceStatus` as a Dart enum with values `inFlight`, `committed`, `stale` and implement a `name` extension for serialization. The `feature_context` field stores a string key (e.g., `'way_forward'`, `'summary'`) that identifies which form field the transcription belongs to — document the valid values as a constants class.

Use `DateTime` in Dart but store as Unix milliseconds (INTEGER) in SQLite for portability. The `id` field should be generated client-side with `const Uuid().v4()` at model construction time, not at insert time, to allow optimistic UI updates.

Testing Requirements

Unit tests using an in-memory SQLite database (sqflite_common_ffi with inMemoryDatabasePath). Test cases: (1) migration runs on empty DB producing correct schema, (2) migration runs on existing DB without dropping data, (3) fromJson round-trip preserves all field values, (4) toJson produces correct column names matching schema, (5) invalid persistence_status string throws on deserialization, (6) null values for NOT NULL fields throw on insertion. Run with flutter_test.

Component
Partial Transcription Repository
data low
Epic Risks (3)
high impact medium prob technical

iOS 15 on-device speech recognition has a 1-minute session limit and requires network fallback for longer sessions. Peer mentor way-forward dictation may routinely exceed this limit, causing silent truncation of transcribed content without user feedback.

Mitigation & Contingency

Mitigation: Implement session-chunking logic in NativeSpeechApiBridge that automatically restarts recognition before the limit is reached, preserving continuity via partial concatenation. Document the iOS 15 vs iOS 16 on-device recognition behaviour difference in code comments.

Contingency: If chunking causes user-visible interruptions, surface a non-blocking informational banner on iOS 15 devices informing users that very long dictation sessions may need to be broken into segments, and use PartialTranscriptionRepository to persist each chunk immediately.

high impact medium prob scope

On iOS, speech recognition permission can only be requested once. If the user denies the permission, the app cannot re-request it. A poor first-impression permission flow will permanently disable dictation for those users, impacting the Blindeforbundet blind-user base who rely on dictation most.

Mitigation & Contingency

Mitigation: Design the NativeSpeechApiBridge permission flow to show a clear pre-permission rationale screen before the OS dialog. Implement a graceful degradation path that hides the microphone button and shows a settings deep-link when permission is permanently denied.

Contingency: If users have already denied permission before the rationale screen is added, provide a settings deep-link in DictationScopeGuard's denial message directing users to iOS Settings > Privacy > Speech Recognition to re-enable manually.

medium impact low prob integration

The approved field IDs and screen routes configuration in DictationScopeGuard may fall out of sync with the actual report form schema as new fields are added by org administrators, silently blocking dictation on legitimately approved fields.

Mitigation & Contingency

Mitigation: Source the approved field configuration from the same org-field-config-loader used by the report form, rather than a hardcoded list. Add a developer-time assertion that logs a warning when a dictation-eligible field type is rendered but not in the approved routes map.

Contingency: Provide a runtime override mechanism in the scope guard that coordinators or admins can use to temporarily whitelist a field ID while the config is updated, with an automatic expiry.

Quick Links
All Tasks Execution Plan