Path:
lib/infrastructure/database/app_database.dart
Lines:
629
Non-empty lines:
559
Non-empty lines covered with requirements:
63 / 559 (11.3%)
Functions:
0
Functions covered by requirements:
0 / 0 (0.0%)
1
import 'dart:io';
2
3
import 'package:bioflow_pro/infrastructure/cloud/database/upload_attempts_table.dart';
4
import 'package:bioflow_pro/infrastructure/config/app_directories.dart';
5
import 'package:bioflow_pro/infrastructure/database/tables/audit_logs_table.dart';
6
import 'package:bioflow_pro/infrastructure/database/tables/clinical_users_table.dart';
7
// Table imports8
import 'package:bioflow_pro/infrastructure/database/tables/clinics_table.dart';
9
import 'package:bioflow_pro/infrastructure/database/tables/patients_table.dart';
10
import 'package:bioflow_pro/infrastructure/database/tables/recordings_table.dart';
11
import 'package:bioflow_pro/infrastructure/database/tables/windows_users_table.dart';
12
import 'package:bioflow_pro/infrastructure/logging/dart_logging_adapter.dart';
13
import 'package:drift/drift.dart';
14
import 'package:drift/native.dart';
15
import 'package:flutter/foundation.dart';
16
import 'package:path/path.dart' as p;
17
18
// Include generated code19
part 'app_database.g.dart';
20
21
/// Main database class for the EEG Recording application22
@DriftDatabase(
23
tables: [
24
Clinics,
25
Patients,
26
Recordings,
27
AuditLogs,
28
WindowsUsers,
29
UploadAttempts,
30
ClinicalUsers,
31
],
32
)33
class AppDatabase extends _$AppDatabase {
34
AppDatabase(super.executor);
35
36
// Schema version - increment when making schema changes
37
@override
38
int get schemaVersion => 11;
39
40
@override
41
MigrationStrategy get migration {
42
return MigrationStrategy(
43
onCreate: (Migrator m) async {
44
// Create all tables
45
await m.createAll();
46
47
// Create indexes for better performance
48
await customStatement(
49
'CREATE INDEX IF NOT EXISTS ix_patients_clinic_origin '
50
'ON patients(clinic_id, origin);',
51
);
52
await customStatement(
53
'CREATE INDEX IF NOT EXISTS ix_recordings_upload_status '
54
'ON recordings(upload_status, created_at);',
55
);
56
await customStatement(
57
'CREATE INDEX IF NOT EXISTS ix_recordings_patient '
58
'ON recordings(patient_local_id);',
59
);
60
await customStatement(
61
'CREATE INDEX IF NOT EXISTS ix_audit_logs_timestamp '
62
'ON audit_logs(timestamp DESC);',
63
);
64
await customStatement(
65
'CREATE INDEX IF NOT EXISTS ix_audit_logs_entity '
66
'ON audit_logs(entity_type, entity_id);',
67
);
68
69
// Bootstrap LOCAL_CLINIC
70
await _bootstrapLocalClinic();
71
},
72
onUpgrade: (Migrator m, int from, int to) async {
73
// Migration from schema v1 to v2: Add new patient fields
74
if (from < 2) {
75
// Add new columns to patients table
76
await customStatement(
77
'ALTER TABLE patients ADD COLUMN middle_name TEXT;',
78
);
79
await customStatement(
80
'ALTER TABLE patients ADD COLUMN gender TEXT;',
81
);
82
await customStatement(
83
'ALTER TABLE patients ADD COLUMN email TEXT;',
84
);
85
await customStatement(
86
'ALTER TABLE patients ADD COLUMN phone TEXT;',
87
);
88
await customStatement(
89
'ALTER TABLE patients ADD COLUMN guardian_name TEXT;',
90
);
91
await customStatement(
92
'ALTER TABLE patients ADD COLUMN referral_reason TEXT;',
93
);
94
await customStatement(
95
'ALTER TABLE patients ADD COLUMN physician TEXT;',
96
);
97
await customStatement(
98
'ALTER TABLE patients ADD COLUMN notes TEXT;',
99
);
100
}
101
102
// Migration from schema v2 to v3: Add upload_attempts table for cloud sync
103
if (from < 3) {
104
// Create upload_attempts table
105
await m.createTable(uploadAttempts);
106
107
// Create index for recording_id for fast lookup
108
await customStatement(
109
'CREATE INDEX IF NOT EXISTS ix_upload_attempts_recording '
110
'ON upload_attempts(recording_id, started_at DESC);',
111
);
112
113
// Create index for status filtering
114
await customStatement(
115
'CREATE INDEX IF NOT EXISTS ix_upload_attempts_status '
116
'ON upload_attempts(status, started_at DESC);',
117
);
118
}
119
120
// Migration from schema v3 to v4: Add missing recording fields for upload
121
if (from < 4) {
122
// Add patient_server_id for remote patients
123
await customStatement(
124
'ALTER TABLE recordings ADD COLUMN patient_server_id TEXT;',
125
);
126
// Add local_path for file location
127
await customStatement(
128
'ALTER TABLE recordings ADD COLUMN local_path TEXT;',
129
);
130
// Add cloud_path for uploaded files
131
await customStatement(
132
'ALTER TABLE recordings ADD COLUMN cloud_path TEXT;',
133
);
134
// Add file_size in bytes
135
await customStatement(
136
'ALTER TABLE recordings ADD COLUMN file_size INTEGER;',
137
);
138
// Add metadata JSON
139
await customStatement(
140
'ALTER TABLE recordings ADD COLUMN metadata TEXT;',
141
);
142
}
143
144
// Migration from schema v4 to v5: Add start_time and end_time for recording sessions
145
if (from < 5) {
146
// Add start_time column (use created_at as default for existing records)
147
await customStatement(
148
"ALTER TABLE recordings ADD COLUMN start_time INTEGER NOT NULL DEFAULT (strftime('%s', created_at) * 1000000);",
149
);
150
// Add end_time column (nullable for ongoing recordings)
151
await customStatement(
152
'ALTER TABLE recordings ADD COLUMN end_time INTEGER;',
153
);
154
}
155
156
// Migration from schema v5 to v6: Add soft delete support for clinics
157
if (from < 6) {
158
// Add is_deleted column (default FALSE for existing clinics)
159
await customStatement(
160
'ALTER TABLE clinics ADD COLUMN is_deleted INTEGER NOT NULL DEFAULT 0;',
161
);
162
// Add deleted_at column (nullable)
163
await customStatement(
164
'ALTER TABLE clinics ADD COLUMN deleted_at INTEGER;',
165
);
166
// Create index for filtering deleted clinics
167
await customStatement(
168
'CREATE INDEX IF NOT EXISTS ix_clinics_deleted '
169
'ON clinics(is_deleted, origin);',
170
);
171
}
172
173
// Migration from schema v6 to v7: Add clinical info fields for remote patients
174
// Note: Using IF NOT EXISTS pattern via checking pragma table_info to make migration idempotent
175
// This handles cases where user downgrades then upgrades again
176
if (from < 7) {
177
// Check which columns already exist
178
final existingColumns = await customSelect(
179
"SELECT name FROM pragma_table_info('patients')",
180
).get();
181
final columnNames = existingColumns.map((row) => row.data['name'] as String).toSet();
182
183
// Primary complaint
184
if (!columnNames.contains('primary_complaint')) {
185
await customStatement(
186
'ALTER TABLE patients ADD COLUMN primary_complaint TEXT;',
187
);
188
}
189
// Other complaints (stored as JSON array string)
190
if (!columnNames.contains('other_complaints')) {
191
await customStatement(
192
'ALTER TABLE patients ADD COLUMN other_complaints TEXT;',
193
);
194
}
195
// Limiting factors (stored as JSON array string)
196
if (!columnNames.contains('limiting_factors')) {
197
await customStatement(
198
'ALTER TABLE patients ADD COLUMN limiting_factors TEXT;',
199
);
200
}
201
// Motor threshold value
202
if (!columnNames.contains('motor_threshold')) {
203
await customStatement(
204
'ALTER TABLE patients ADD COLUMN motor_threshold INTEGER;',
205
);
206
}
207
// Motor threshold not found flag
208
if (!columnNames.contains('mt_not_found')) {
209
await customStatement(
210
'ALTER TABLE patients ADD COLUMN mt_not_found INTEGER NOT NULL DEFAULT 0;',
211
);
212
}
213
// Metal implants flag
214
if (!columnNames.contains('metal_implants')) {
215
await customStatement(
216
'ALTER TABLE patients ADD COLUMN metal_implants INTEGER NOT NULL DEFAULT 0;',
217
);
218
}
219
// Available assessment scales (stored as JSON array string)
220
if (!columnNames.contains('available_scales')) {
221
await customStatement(
222
'ALTER TABLE patients ADD COLUMN available_scales TEXT;',
223
);
224
}
225
// Ethnicity
226
if (!columnNames.contains('ethnicity')) {
227
await customStatement(
228
'ALTER TABLE patients ADD COLUMN ethnicity TEXT;',
229
);
230
}
231
}
232
233
// Migration from schema v7 to v8: Add duration_seconds for accurate recording duration
234
// Note: Using idempotent pattern to handle downgrade/upgrade scenarios
235
if (from < 8) {
236
// Check if column already exists
237
final existingColumns = await customSelect(
238
"SELECT name FROM pragma_table_info('recordings')",
239
).get();
240
final columnNames = existingColumns.map((row) => row.data['name'] as String).toSet();
241
242
// Add duration_seconds column to store actual recorded duration (excluding pause time)
243
if (!columnNames.contains('duration_seconds')) {
244
await customStatement(
245
'ALTER TABLE recordings ADD COLUMN duration_seconds INTEGER;',
246
);
247
}
248
}
249
250
// Migration from schema v8 to v9: Add history_of_seizures and treatment authorization flags
251
// Note: Using idempotent pattern to handle downgrade/upgrade scenarios
252
if (from < 9) {
253
// Check which columns already exist
254
final existingColumns = await customSelect(
255
"SELECT name FROM pragma_table_info('patients')",
256
).get();
257
final columnNames = existingColumns.map((row) => row.data['name'] as String).toSet();
258
259
// History of seizures (safety-critical flag)
260
if (!columnNames.contains('history_of_seizures')) {
261
await customStatement(
262
'ALTER TABLE patients ADD COLUMN history_of_seizures INTEGER NOT NULL DEFAULT 0;',
263
);
264
}
265
266
// Treatment authorization flags
267
if (!columnNames.contains('is_peripheral_high_allowed')) {
268
await customStatement(
269
'ALTER TABLE patients ADD COLUMN is_peripheral_high_allowed INTEGER NOT NULL DEFAULT 1;',
270
);
271
}
272
if (!columnNames.contains('is_peripheral_low_allowed')) {
273
await customStatement(
274
'ALTER TABLE patients ADD COLUMN is_peripheral_low_allowed INTEGER NOT NULL DEFAULT 1;',
275
);
276
}
277
if (!columnNames.contains('is_cortical_allowed')) {
278
await customStatement(
279
'ALTER TABLE patients ADD COLUMN is_cortical_allowed INTEGER;',
280
);
281
}
282
if (!columnNames.contains('is_tinnitus_allowed')) {
283
await customStatement(
284
'ALTER TABLE patients ADD COLUMN is_tinnitus_allowed INTEGER NOT NULL DEFAULT 0;',
285
);
286
}
287
if (!columnNames.contains('is_fda_depression_allowed')) {
288
await customStatement(
289
'ALTER TABLE patients ADD COLUMN is_fda_depression_allowed INTEGER NOT NULL DEFAULT 0;',
290
);
291
}
292
}
293
294
// Migration from schema v9 to v10: Add clinical_users table and assigned_clinician_id FK
295
// Note: Using idempotent pattern to handle downgrade/upgrade scenarios
296
if (from < 10) {
297
// Check if clinical_users table already exists (idempotent)
298
final tables = await customSelect(
299
"SELECT name FROM sqlite_master WHERE type='table' AND name='clinical_users'",
300
).get();
301
302
if (tables.isEmpty) {
303
// Create clinical_users table
304
await m.createTable(clinicalUsers);
305
}
306
307
// Create index for server_id lookup (IF NOT EXISTS is idempotent)
308
await customStatement(
309
'CREATE INDEX IF NOT EXISTS ix_clinical_users_server_id '
310
'ON clinical_users(server_id);',
311
);
312
313
// Check which columns already exist in patients
314
final existingColumns = await customSelect(
315
"SELECT name FROM pragma_table_info('patients')",
316
).get();
317
final columnNames = existingColumns.map((row) => row.data['name'] as String).toSet();
318
319
// Add assigned_clinician_id FK column to patients
320
if (!columnNames.contains('assigned_clinician_id')) {
321
await customStatement(
322
'ALTER TABLE patients ADD COLUMN assigned_clinician_id TEXT;',
323
);
324
}
325
326
// Note: We keep the old 'physician' column for backward compatibility
327
// and to preserve any existing data. It will be ignored by the new code.
328
}
329
330
// Migration from schema v10 to v11: Add soft delete support for recordings
331
if (from < 11) {
332
final existingColumns = await customSelect(
333
"SELECT name FROM pragma_table_info('recordings')",
334
).get();
335
final columnNames = existingColumns.map((row) => row.data['name'] as String).toSet();
336
337
if (!columnNames.contains('is_deleted')) {
338
await customStatement(
339
'ALTER TABLE recordings ADD COLUMN is_deleted INTEGER NOT NULL DEFAULT 0;',
340
);
341
}
342
343
await customStatement(
344
'CREATE INDEX IF NOT EXISTS ix_recordings_deleted '
345
'ON recordings(is_deleted);',
346
);
347
}
348
},
349
beforeOpen: (details) async {
350
// Enable foreign key constraints (minimal overhead, catches orphan references)
351
await customStatement('PRAGMA foreign_keys = ON');
352
},
353
);
354
}
355
356
/// Bootstrap the LOCAL_CLINIC entity
357
Future<void> _bootstrapLocalClinic() async {
358
final localClinicExists = await (select(clinics)
359
..where((c) => c.clinicId.equals('LOCAL_CLINIC')))
360
.getSingleOrNull();
361
362
if (localClinicExists == null) {
363
await into(clinics).insert(
364
ClinicsCompanion.insert(
365
clinicId: 'LOCAL_CLINIC',
366
name: 'Local Recordings',
367
origin: const Value('local'),
368
),
369
);
370
}
371
}
372
373
/// Check if LOCAL_CLINIC exists
374
Future<bool> hasLocalClinic() async {
375
final result = await (select(clinics)
376
..where((c) => c.clinicId.equals('LOCAL_CLINIC')))
377
.getSingleOrNull();
378
return result != null;
379
}
380
381
/// Get all clinics (including deleted)
382
Future<List<ClinicDto>> getAllClinics() => select(clinics).get();
383
384
/// Get all clinics excluding soft-deleted ones
385
Future<List<ClinicDto>> getAllClinicsExcludingDeleted() =>
386
(select(clinics)..where((c) => c.isDeleted.equals(false))).get();
387
388
/// Get clinic by ID
389
Future<ClinicDto?> getClinicById(String id) =>
390
(select(clinics)..where((c) => c.clinicId.equals(id))).getSingleOrNull();
391
392
/// Create a new clinic
393
Future<void> createClinic(ClinicsCompanion clinic) =>
394
into(clinics).insert(clinic);
395
396
/// Mark a clinic as deleted (soft delete)
397
Future<void> markClinicAsDeleted(String clinicId) async {
398
await (update(clinics)..where((c) => c.clinicId.equals(clinicId))).write(
399
ClinicsCompanion(
400
isDeleted: const Value(true),
401
deletedAt: Value(DateTime.now().toUtc()),
402
updatedAt: Value(DateTime.now().toUtc()),
403
),
404
);
405
}
406
407
/// Restore a soft-deleted clinic
408
Future<void> restoreClinic(String clinicId) async {
409
await (update(clinics)..where((c) => c.clinicId.equals(clinicId))).write(
410
const ClinicsCompanion(
411
isDeleted: Value(false),
412
deletedAt: Value(null),
413
updatedAt: Value.absent(), // Will use default (current time)
414
),
415
);
416
}
417
418
/// Get patients by clinic (excluding soft-deleted)
419
Future<List<PatientDto>> getPatientsByClinic(String clinicId) =>
420
(select(patients)..where((p) => p.clinicId.equals(clinicId) & p.isDeleted.equals(false))).get();
421
422
/// Get patients by clinic (including soft-deleted, for reconciliation)
423
Future<List<PatientDto>> getPatientsByClinicIncludingDeleted(String clinicId) =>
424
(select(patients)..where((p) => p.clinicId.equals(clinicId))).get();
425
426
/// Get patient by ID
427
Future<PatientDto?> getPatientById(String id) =>
428
(select(patients)..where((p) => p.localPatientId.equals(id))).getSingleOrNull();
429
430
/// Get patient by server ID (for remote patients)
431
Future<PatientDto?> getPatientByServerId(String serverId) =>
432
(select(patients)..where((p) => p.serverPatientId.equals(serverId))).getSingleOrNull();
433
434
/// Create a new patient
435
Future<void> createPatient(PatientsCompanion patient) =>
436
into(patients).insert(patient);
437
438
/// Soft delete a patient
439
Future<void> softDeletePatient(String patientId) =>
440
(update(patients)..where((p) => p.localPatientId.equals(patientId)))
441
.write(const PatientsCompanion(isDeleted: Value(true)));
442
443
/// Mark a patient as deleted (soft delete with updatedAt timestamp)
444
Future<void> markPatientAsDeleted(String patientId) async {
445
await (update(patients)..where((p) => p.localPatientId.equals(patientId))).write(
446
PatientsCompanion(
447
isDeleted: const Value(true),
448
updatedAt: Value(DateTime.now().toUtc()),
449
),
450
);
451
}
452
453
/// Restore a soft-deleted patient
454
Future<void> restorePatient(String patientId) async {
455
await (update(patients)..where((p) => p.localPatientId.equals(patientId))).write(
456
PatientsCompanion(
457
isDeleted: const Value(false),
458
updatedAt: Value(DateTime.now().toUtc()),
459
),
460
);
461
}
462
463
/// Get recordings by patient
464
Future<List<RecordingDto>> getRecordingsByPatient(String patientId) =>
465
(select(recordings)..where((r) => r.patientLocalId.equals(patientId))).get();
466
467
/// Create a new recording
468
Future<void> createRecording(RecordingsCompanion recording) =>
469
into(recordings).insert(recording);
470
471
/// Create an audit log entry
472
Future<void> createAuditLog(AuditLogsCompanion auditLog) =>
473
into(auditLogs).insert(auditLog);
474
475
/// Get audit logs ordered newest-first.
476
///
477
/// When [limit] is null the full audit table is returned (used by the
478
/// audit-trail review surface so that an auditor can see the whole
479
/// trail, not a sliding window). When a value is supplied, standard
480
/// LIMIT/OFFSET pagination applies.
481
Future<List<AuditLogDto>> getAuditLogs({int? limit, int offset = 0}) {
482
final query = select(auditLogs)
483
..orderBy([(a) => OrderingTerm.desc(a.timestamp)]);
484
if (limit != null) {
485
query.limit(limit, offset: offset);
486
}
487
return query.get();
488
}
489
490
/// Get all patients
491
Future<List<PatientDto>> getAllPatients() => select(patients).get();
492
493
/// Get recording by ID
494
Future<RecordingDto?> getRecordingById(String id) =>
495
(select(recordings)..where((r) => r.localRecordingId.equals(id))).getSingleOrNull();
496
497
/// Get pending upload recordings
498
Future<List<RecordingDto>> getPendingUploadRecordings() =>
499
(select(recordings)
500
..where((r) => r.uploadStatus.equals('not_uploaded'))
501
..where((r) => r.isDeleted.equals(false))
502
..orderBy([(r) => OrderingTerm.desc(r.startTime)]))
503
.get();
504
505
/// Get audit logs by entity
506
Future<List<AuditLogDto>> getAuditLogsByEntity(String entityType, String entityId) =>
507
(select(auditLogs)
508
..where((a) => a.entityType.equals(entityType) & a.entityId.equals(entityId)))
509
.get();
510
511
/// Get audit logs by user
512
Future<List<AuditLogDto>> getAuditLogsByUser(String userId) =>
513
(select(auditLogs)..where((a) => a.userId.equals(userId))).get();
514
515
/// Get audit logs by date range
516
Future<List<AuditLogDto>> getAuditLogsByDateRange(DateTime start, DateTime end) =>
517
(select(auditLogs)
518
..where((a) => a.timestamp.isBetweenValues(start, end)))
519
.get();
520
521
/// Get audit logs by action
522
Future<List<AuditLogDto>> getAuditLogsByAction(String action) =>
523
(select(auditLogs)..where((a) => a.action.equals(action))).get();
524
525
/// Get audit log count
526
Future<int> getAuditLogCount() async {
527
final count = countAll();
528
final query = selectOnly(auditLogs)..addColumns([count]);
529
final result = await query.getSingle();
530
return result.read(count) ?? 0;
531
}
532
533
/// Get Windows user by ID
534
Future<WindowsUserDto?> getWindowsUserById(String id) =>
535
(select(windowsUsers)..where((u) => u.userId.equals(id))).getSingleOrNull();
536
537
/// Get most recent Windows user
538
Future<WindowsUserDto?> getMostRecentWindowsUser() =>
539
(select(windowsUsers)
540
..orderBy([(u) => OrderingTerm.desc(u.lastSeen)])
541
..limit(1))
542
.getSingleOrNull();
543
544
/// Get all Windows users
545
Future<List<WindowsUserDto>> getAllWindowsUsers() => select(windowsUsers).get();
546
547
/// Create Windows user
548
Future<void> createWindowsUser(WindowsUsersCompanion user) =>
549
into(windowsUsers).insert(user);
550
551
/// Clear all data (for testing purposes)
552
@visibleForTesting
553
Future<void> clearAllData() async {
554
await delete(recordings).go();
555
await delete(patients).go();
556
await delete(auditLogs).go();
557
await (delete(clinics)..where((c) => c.clinicId.equals('LOCAL_CLINIC').not())).go();
558
}
559
}560
561
// @relation(ARCH-002, scope=range_start)562
/// Opens an encrypted database connection using SQLCipher.563
///564
/// Default location: `AppDirectories.dataRoot/database.db`565
/// (`C:\BioFlowRecords\database.db` on Windows).566
///567
/// [encryptionKey] - The encryption key for SQLCipher.568
/// [dbPath] - Optional override for the on-disk database path.569
/// Production code omits it and accepts the default; integration570
/// tests pass a temp path so they don't write into the production571
/// location.572
LazyDatabase openEncryptedConnection(
573
String encryptionKey, {
574
String? dbPath,
575
}) {
576
if (encryptionKey.trim().isEmpty) {
577
throw ArgumentError.value(
578
encryptionKey,
579
'encryptionKey',
580
'Must be non-empty — refusing to create or open the local database '
581
'without a credential.',
582
);
583
}
584
if (dbPath != null && dbPath.trim().isEmpty) {
585
throw ArgumentError.value(
586
dbPath,
587
'dbPath',
588
'When provided, must be a non-empty filesystem path.',
589
);
590
}
591
final logger = DartLoggingAdapter('Infrastructure.Database');
592
return LazyDatabase(() async {
593
final resolvedPath =
594
dbPath ?? p.join(AppDirectories.dataRoot, 'database.db');
595
final dbFile = File(resolvedPath);
596
597
// Ensure directory exists
598
await dbFile.parent.create(recursive: true);
599
600
if (kDebugMode) {
601
logger.info('Database path: $resolvedPath');
602
}
603
604
// Create encrypted database using SQLCipher
605
return NativeDatabase(
606
dbFile,
607
setup: (rawDb) {
608
// Set encryption key - MUST be first statement before any other operation
609
rawDb.execute("PRAGMA key = '$encryptionKey';");
610
611
// Verify SQLCipher is active
612
if (kDebugMode) {
613
final result = rawDb.select('PRAGMA cipher_version;');
614
if (result.isEmpty) {
615
throw Exception('SQLCipher not available - encryption failed');
616
}
617
logger
618
..info('SQLCipher version: ${result.first['cipher_version']}')
619
..info('Database encryption enabled');
620
}
621
622
// Enable foreign keys
623
rawDb.execute('PRAGMA foreign_keys = ON;');
624
},
625
);
626
});
627
}628
// @relation(ARCH-002, scope=range_end)629