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

Implement PartialTranscriptionRepository with CRUD operations

epic-speech-to-text-input-foundation-task-002 — Build the PartialTranscriptionRepository class that persists, retrieves, and clears partial transcription records from SQLite. Implement save(), findBySessionId(), deleteBySessionId(), and clearStale() methods. Use Riverpod provider for dependency injection. Ensure write operations are atomic and crash-safe using transactions.

critical priority low complexity backend pending backend specialist Tier 1

Acceptance Criteria

PartialTranscriptionRepository is an abstract class with a concrete SQLite implementation
save(PartialTranscription) performs an upsert (INSERT OR REPLACE) and returns Future<void>
findBySessionId(String sessionId) returns Future<List<PartialTranscription>> ordered by created_at ASC
deleteBySessionId(String sessionId) removes all records for the session atomically and returns Future<void>
clearStale(Duration maxAge) deletes all records older than now - maxAge and returns Future<int> (count deleted)
All write operations (save, delete, clearStale) execute inside SQLite transactions
A Riverpod Provider<PartialTranscriptionRepository> is exported and usable via ref.read()
Repository does not throw raw SQLite exceptions — all errors are wrapped in a typed RepositoryException
The repository interface is mockable (abstract class or interface) for unit testing

Technical Requirements

frameworks
Flutter
Riverpod
apis
sqflite or drift SQLite package
data models
PartialTranscription
performance requirements
save() must complete in under 50ms on a mid-range device
clearStale() must use a single DELETE WHERE query, not a fetch-then-delete loop
security requirements
All SQL queries must use parameterized queries — no string interpolation
Repository must not expose raw database handles outside the class

Execution Context

Execution Tier
Tier 1

Tier 1 - 540 tasks

Can start after Tier 0 completes

Implementation Notes

Define an abstract `PartialTranscriptionRepository` interface first — this enables easy mocking in dependent component tests. The concrete `SqlitePartialTranscriptionRepository` implements it. Inject the database instance via constructor, not a global singleton, so tests can pass an in-memory DB. For the Riverpod provider, use `Provider` (not `StateProvider`) since the repository is stateless.

The `clearStale` method should be called on app startup (in the app lifecycle observer) to prevent unbounded growth. Use SQLite's `strftime` or compare Unix timestamps directly — avoid Dart-side filtering which would require loading all rows. Transaction wrapping pattern: `await db.transaction((txn) async { ... })` — ensure deleteBySessionId wraps its DELETE in a transaction even though it's a single statement, for consistency.

Testing Requirements

Unit tests with in-memory SQLite (sqflite_common_ffi). Test suite must cover: (1) save() inserts a new record, (2) save() with same id updates existing record (upsert), (3) findBySessionId() returns only records for the specified session, (4) findBySessionId() returns empty list for unknown session, (5) deleteBySessionId() removes all records for session and leaves other sessions intact, (6) clearStale() removes only records older than maxAge, (7) concurrent save() calls do not corrupt data (run 10 saves in parallel with Future.wait), (8) repository throws RepositoryException (not DatabaseException) on schema mismatch. Use flutter_test.

Component
Partial Transcription Repository
data low
Dependencies (1)
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. epic-speech-to-text-input-foundation-task-001
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