Database Schema

Lumo uses Realm as its local database for storing OTP entries, settings, and other persistent data. This guide covers the database schema, operations, and best practices.

Realm Configuration

Database Setup

import 'package:realm/realm.dart';

class RealmConfig {
  static Configuration get config {
    return Configuration.local([
      OtpEntrySchema.schema,
      SettingsSchema.schema,
      BackupInfoSchema.schema,
    ], schemaVersion: 2, migrationCallback: _migrationCallback);
  }

  static void _migrationCallback(Migration migration, int oldSchemaVersion) {
    if (oldSchemaVersion < 2) {
      // Migration from version 1 to 2
      final oldEntries = migration.oldRealm.all('OtpEntry');
      final newEntries = migration.newRealm.all<OtpEntrySchema>();

      for (var i = 0; i < oldEntries.length; i++) {
        final oldEntry = oldEntries[i];
        final newEntry = newEntries[i];

        // Add default values for new fields
        newEntry.lastUsed = DateTime.now();
        newEntry.isFavorite = false;
      }
    }
  }
}

// lib/core/di/database_providers.dart
@riverpod
Realm realm(RealmRef ref) {
  final realm = Realm(RealmConfig.config);

  // Dispose realm when provider is disposed
  ref.onDispose(() {
    realm.close();
  });

  return realm;
}

Schema Definitions

OTP Entry Schema

import 'package:realm/realm.dart';

part 'otp_entry_schema.realm.dart';

@RealmModel()
class _OtpEntrySchema {
  @PrimaryKey()
  late String id;

  late String issuer;
  late String account;
  late String secret; // Base32 encoded secret

  late String algorithm; // 'SHA1', 'SHA256', 'SHA512'
  late int digits; // Usually 6 or 8
  late int interval; // For TOTP, usually 30 seconds
  late String type; // 'TOTP' or 'HOTP'

  late int counter; // For HOTP only, defaults to 0

  late DateTime created;
  DateTime? lastUsed;

  // Additional metadata
  bool isFavorite = false;
  String? notes;
  String? iconUrl;

  // Sorting and organization
  int sortOrder = 0;
  String? category;
}

// Extension for business logic
extension OtpEntrySchemaExtensions on OtpEntrySchema {
  // Convert to domain entity
  OtpEntry toEntity() {
    return OtpEntry(
      id: id,
      issuer: issuer,
      account: account,
      secret: secret,
      algorithm: OtpAlgorithm.values.byName(algorithm.toLowerCase()),
      digits: digits,
      interval: interval,
      type: OtpType.values.byName(type.toLowerCase()),
      counter: counter,
      created: created,
      lastUsed: lastUsed,
      isFavorite: isFavorite,
      notes: notes,
      iconUrl: iconUrl,
      sortOrder: sortOrder,
      category: category,
    );
  }

  // Update from domain entity
  void updateFromEntity(OtpEntry entity) {
    issuer = entity.issuer;
    account = entity.account;
    secret = entity.secret;
    algorithm = entity.algorithm.name.toUpperCase();
    digits = entity.digits;
    interval = entity.interval;
    type = entity.type.name.toUpperCase();
    counter = entity.counter ?? 0;
    lastUsed = entity.lastUsed;
    isFavorite = entity.isFavorite ?? false;
    notes = entity.notes;
    iconUrl = entity.iconUrl;
    sortOrder = entity.sortOrder ?? 0;
    category = entity.category;
  }
}

Settings Schema

@RealmModel()
class _SettingsSchema {
  @PrimaryKey()
  late String id; // Always 'app_settings'

  // Theme settings
  late String themeMode; // 'system', 'light', 'dark'

  // Security settings
  bool biometricEnabled = false;
  bool autoLockEnabled = true;
  int autoLockTimeoutMinutes = 5;
  bool requireAuthForCopy = false;

  // Backup settings
  bool cloudBackupEnabled = false;
  bool autoBackupEnabled = false;
  DateTime? lastBackupTime;
  String? backupEncryptionKey;

  // UI settings
  bool showAccountIcons = true;
  bool hapticFeedbackEnabled = true;
  String defaultSortOrder = 'alphabetical'; // 'alphabetical', 'created', 'lastUsed'

  // Privacy settings
  bool analyticsEnabled = false;
  bool crashReportingEnabled = true;

  // Advanced settings
  int otpRefreshInterval = 1000; // milliseconds
  bool developerModeEnabled = false;
}

extension SettingsSchemaExtensions on SettingsSchema {
  SettingsEntity toEntity() {
    return SettingsEntity(
      themeMode: ThemeMode.values.byName(themeMode),
      biometricEnabled: biometricEnabled,
      autoLockEnabled: autoLockEnabled,
      autoLockTimeout: Duration(minutes: autoLockTimeoutMinutes),
      requireAuthForCopy: requireAuthForCopy,
      cloudBackupEnabled: cloudBackupEnabled,
      autoBackupEnabled: autoBackupEnabled,
      lastBackupTime: lastBackupTime,
      showAccountIcons: showAccountIcons,
      hapticFeedbackEnabled: hapticFeedbackEnabled,
      defaultSortOrder: SortOrder.values.byName(defaultSortOrder),
      analyticsEnabled: analyticsEnabled,
      crashReportingEnabled: crashReportingEnabled,
      otpRefreshInterval: Duration(milliseconds: otpRefreshInterval),
      developerModeEnabled: developerModeEnabled,
    );
  }
}

Backup Info Schema

@RealmModel()
class _BackupInfoSchema {
  @PrimaryKey()
  late String id; // Usually user ID or 'local_backup'

  late DateTime lastBackupTime;
  late DateTime lastRestoreTime;

  late int totalEntries;
  late int backupSize; // in bytes

  late String backupLocation; // 'firebase', 'local', 'dropbox', etc.
  late String backupStatus; // 'success', 'failed', 'in_progress'

  String? errorMessage;
  String? backupHash; // For integrity verification

  // Backup metadata
  late String appVersion;
  late String deviceId;
  late String platform; // 'android', 'ios'
}

Data Access Layer

OTP Repository Implementation

class OtpRepositoryImpl implements OtpRepository {
  final Realm _realm;

  const OtpRepositoryImpl(this._realm);

  @override
  Future<Either<Failure, List<OtpEntry>>> getAllOtpEntries() async {
    try {
      final schemas = _realm.all<OtpEntrySchema>()
          .query('TRUEPREDICATE SORT(sortOrder ASC, issuer ASC)');

      final entries = schemas.map((schema) => schema.toEntity()).toList();
      return Right(entries);
    } catch (e) {
      return Left(DatabaseFailure('Failed to get OTP entries: $e'));
    }
  }

  @override
  Future<Either<Failure, void>> addOtpEntry(OtpEntry entry) async {
    try {
      _realm.write(() {
        final schema = OtpEntrySchema(
          ObjectId().hexString,
          entry.issuer,
          entry.account,
          entry.secret,
          entry.algorithm.name.toUpperCase(),
          entry.digits,
          entry.interval,
          entry.type.name.toUpperCase(),
          entry.counter ?? 0,
          entry.created,
        );

        // Set additional fields
        schema.lastUsed = entry.lastUsed;
        schema.isFavorite = entry.isFavorite ?? false;
        schema.notes = entry.notes;
        schema.iconUrl = entry.iconUrl;
        schema.sortOrder = entry.sortOrder ?? _getNextSortOrder();
        schema.category = entry.category;

        _realm.add(schema);
      });

      return const Right(null);
    } catch (e) {
      return Left(DatabaseFailure('Failed to add OTP entry: $e'));
    }
  }

  @override
  Future<Either<Failure, void>> updateOtpEntry(OtpEntry entry) async {
    try {
      final schema = _realm.find<OtpEntrySchema>(entry.id);
      if (schema == null) {
        return const Left(DatabaseFailure('OTP entry not found'));
      }

      _realm.write(() {
        schema.updateFromEntity(entry);
      });

      return const Right(null);
    } catch (e) {
      return Left(DatabaseFailure('Failed to update OTP entry: $e'));
    }
  }

  @override
  Future<Either<Failure, void>> deleteOtpEntry(String id) async {
    try {
      final schema = _realm.find<OtpEntrySchema>(id);
      if (schema == null) {
        return const Left(DatabaseFailure('OTP entry not found'));
      }

      _realm.write(() {
        _realm.delete(schema);
      });

      return const Right(null);
    } catch (e) {
      return Left(DatabaseFailure('Failed to delete OTP entry: $e'));
    }
  }

  @override
  Future<Either<Failure, void>> clearAllOtpEntries() async {
    try {
      _realm.write(() {
        _realm.deleteAll<OtpEntrySchema>();
      });

      return const Right(null);
    } catch (e) {
      return Left(DatabaseFailure('Failed to clear OTP entries: $e'));
    }
  }

  @override
  Future<Either<Failure, List<OtpEntry>>> searchOtpEntries(String query) async {
    try {
      final searchQuery = query.toLowerCase();
      final schemas = _realm.all<OtpEntrySchema>()
          .query(r'issuer CONTAINS[c] $0 OR account CONTAINS[c] $0', [searchQuery]);

      final entries = schemas.map((schema) => schema.toEntity()).toList();
      return Right(entries);
    } catch (e) {
      return Left(DatabaseFailure('Failed to search OTP entries: $e'));
    }
  }

  @override
  Future<Either<Failure, List<OtpEntry>>> getFavoriteEntries() async {
    try {
      final schemas = _realm.all<OtpEntrySchema>()
          .query('isFavorite == true SORT(issuer ASC)');

      final entries = schemas.map((schema) => schema.toEntity()).toList();
      return Right(entries);
    } catch (e) {
      return Left(DatabaseFailure('Failed to get favorite entries: $e'));
    }
  }

  int _getNextSortOrder() {
    final lastEntry = _realm.all<OtpEntrySchema>()
        .query('TRUEPREDICATE SORT(sortOrder DESC)')
        .firstOrNull;

    return (lastEntry?.sortOrder ?? 0) + 1;
  }
}

Settings Repository Implementation

class SettingsRepositoryImpl implements SettingsRepository {
  final Realm _realm;
  static const String _settingsId = 'app_settings';

  const SettingsRepositoryImpl(this._realm);

  @override
  Future<Either<Failure, SettingsEntity>> getSettings() async {
    try {
      var schema = _realm.find<SettingsSchema>(_settingsId);

      // Create default settings if not found
      if (schema == null) {
        schema = _createDefaultSettings();
      }

      return Right(schema.toEntity());
    } catch (e) {
      return Left(DatabaseFailure('Failed to get settings: $e'));
    }
  }

  @override
  Future<Either<Failure, void>> updateSettings(SettingsEntity settings) async {
    try {
      var schema = _realm.find<SettingsSchema>(_settingsId);

      _realm.write(() {
        if (schema == null) {
          schema = _createDefaultSettings();
        }

        schema!.themeMode = settings.themeMode.name;
        schema!.biometricEnabled = settings.biometricEnabled;
        schema!.autoLockEnabled = settings.autoLockEnabled;
        schema!.autoLockTimeoutMinutes = settings.autoLockTimeout.inMinutes;
        schema!.requireAuthForCopy = settings.requireAuthForCopy;
        schema!.cloudBackupEnabled = settings.cloudBackupEnabled;
        schema!.autoBackupEnabled = settings.autoBackupEnabled;
        schema!.lastBackupTime = settings.lastBackupTime;
        schema!.showAccountIcons = settings.showAccountIcons;
        schema!.hapticFeedbackEnabled = settings.hapticFeedbackEnabled;
        schema!.defaultSortOrder = settings.defaultSortOrder.name;
        schema!.analyticsEnabled = settings.analyticsEnabled;
        schema!.crashReportingEnabled = settings.crashReportingEnabled;
        schema!.otpRefreshInterval = settings.otpRefreshInterval.inMilliseconds;
        schema!.developerModeEnabled = settings.developerModeEnabled;
      });

      return const Right(null);
    } catch (e) {
      return Left(DatabaseFailure('Failed to update settings: $e'));
    }
  }

  SettingsSchema _createDefaultSettings() {
    final schema = SettingsSchema(
      _settingsId,
      ThemeMode.system.name,
    );

    _realm.add(schema);
    return schema;
  }
}

Database Operations

Bulk Operations

class BulkOperations {
  final Realm _realm;

  const BulkOperations(this._realm);

  // Import multiple OTP entries
  Future<Either<Failure, void>> importOtpEntries(List<OtpEntry> entries) async {
    try {
      _realm.write(() {
        for (final entry in entries) {
          // Check for duplicates
          final existing = _realm.all<OtpEntrySchema>()
              .query(r'issuer == $0 AND account == $1', [entry.issuer, entry.account])
              .firstOrNull;

          if (existing == null) {
            final schema = OtpEntrySchema(
              entry.id.isEmpty ? ObjectId().hexString : entry.id,
              entry.issuer,
              entry.account,
              entry.secret,
              entry.algorithm.name.toUpperCase(),
              entry.digits,
              entry.interval,
              entry.type.name.toUpperCase(),
              entry.counter ?? 0,
              entry.created,
            );

            _realm.add(schema);
          }
        }
      });

      return const Right(null);
    } catch (e) {
      return Left(DatabaseFailure('Failed to import entries: $e'));
    }
  }

  // Export all entries for backup
  Future<Either<Failure, List<OtpEntry>>> exportAllEntries() async {
    try {
      final schemas = _realm.all<OtpEntrySchema>();
      final entries = schemas.map((schema) => schema.toEntity()).toList();

      return Right(entries);
    } catch (e) {
      return Left(DatabaseFailure('Failed to export entries: $e'));
    }
  }

  // Update multiple entries (e.g., reordering)
  Future<Either<Failure, void>> reorderEntries(List<String> orderedIds) async {
    try {
      _realm.write(() {
        for (int i = 0; i < orderedIds.length; i++) {
          final schema = _realm.find<OtpEntrySchema>(orderedIds[i]);
          if (schema != null) {
            schema.sortOrder = i;
          }
        }
      });

      return const Right(null);
    } catch (e) {
      return Left(DatabaseFailure('Failed to reorder entries: $e'));
    }
  }
}

Database Maintenance

class DatabaseMaintenance {
  final Realm _realm;

  const DatabaseMaintenance(this._realm);

  // Clean up unused data
  Future<void> cleanup() async {
    _realm.write(() {
      // Remove entries marked for deletion (soft delete pattern)
      final toDelete = _realm.all<OtpEntrySchema>()
          .query('isDeleted == true');
      _realm.delete(toDelete);

      // Reset sort orders if they're out of sync
      final entries = _realm.all<OtpEntrySchema>()
          .query('TRUEPREDICATE SORT(sortOrder ASC)');

      for (int i = 0; i < entries.length; i++) {
        entries[i].sortOrder = i;
      }
    });
  }

  // Verify database integrity
  Future<DatabaseHealthReport> checkHealth() async {
    final report = DatabaseHealthReport();

    try {
      // Check for duplicate entries
      final allEntries = _realm.all<OtpEntrySchema>();
      final duplicates = <String, List<OtpEntrySchema>>{};

      for (final entry in allEntries) {
        final key = '${entry.issuer}-${entry.account}';
        duplicates[key] = (duplicates[key] ?? [])..add(entry);
      }

      report.duplicateCount = duplicates.values
          .where((entries) => entries.length > 1)
          .length;

      // Check for invalid secrets
      report.invalidSecretCount = allEntries
          .where((entry) => !_isValidBase32(entry.secret))
          .length;

      // Check settings consistency
      final settings = _realm.find<SettingsSchema>('app_settings');
      report.hasSettings = settings != null;

      report.isHealthy = report.duplicateCount == 0 &&
                        report.invalidSecretCount == 0 &&
                        report.hasSettings;

    } catch (e) {
      report.isHealthy = false;
      report.error = e.toString();
    }

    return report;
  }

  bool _isValidBase32(String secret) {
    final base32Regex = RegExp(r'^[A-Z2-7]+=*$');
    return base32Regex.hasMatch(secret.toUpperCase());
  }
}

class DatabaseHealthReport {
  bool isHealthy = true;
  int duplicateCount = 0;
  int invalidSecretCount = 0;
  bool hasSettings = false;
  String? error;

  Map<String, dynamic> toJson() {
    return {
      'isHealthy': isHealthy,
      'duplicateCount': duplicateCount,
      'invalidSecretCount': invalidSecretCount,
      'hasSettings': hasSettings,
      'error': error,
    };
  }
}

Queries and Indexing

Complex Queries

class DatabaseQueries {
  final Realm _realm;

  const DatabaseQueries(this._realm);

  // Get entries by category
  List<OtpEntry> getEntriesByCategory(String? category) {
    final query = category == null
        ? 'category == null'
        : r'category == $0';
    final params = category == null ? [] : [category];

    final schemas = _realm.all<OtpEntrySchema>()
        .query('$query SORT(issuer ASC)', params);

    return schemas.map((s) => s.toEntity()).toList();
  }

  // Get recently used entries
  List<OtpEntry> getRecentlyUsedEntries({int limit = 10}) {
    final schemas = _realm.all<OtpEntrySchema>()
        .query('lastUsed != null SORT(lastUsed DESC) LIMIT($limit)');

    return schemas.map((s) => s.toEntity()).toList();
  }

  // Get entries expiring soon (for HOTP)
  List<OtpEntry> getHotpEntriesNearLimit({int threshold = 10}) {
    final schemas = _realm.all<OtpEntrySchema>()
        .query(r'type == "HOTP" AND counter >= $0', [threshold]);

    return schemas.map((s) => s.toEntity()).toList();
  }

  // Statistics
  Map<String, int> getStatistics() {
    final all = _realm.all<OtpEntrySchema>();

    return {
      'total': all.length,
      'totp': all.query('type == "TOTP"').length,
      'hotp': all.query('type == "HOTP"').length,
      'favorites': all.query('isFavorite == true').length,
      'withNotes': all.query('notes != null').length,
    };
  }
}

Performance Optimization

class DatabasePerformance {
  final Realm _realm;

  const DatabasePerformance(this._realm);

  // Efficient pagination for large datasets
  List<OtpEntry> getEntriesPaginated({
    int offset = 0,
    int limit = 20,
    String? searchQuery,
  }) {
    var query = 'TRUEPREDICATE';
    final params = <Object>[];

    if (searchQuery != null && searchQuery.isNotEmpty) {
      query = r'issuer CONTAINS[c] $0 OR account CONTAINS[c] $0';
      params.add(searchQuery);
    }

    final schemas = _realm.all<OtpEntrySchema>()
        .query('$query SORT(issuer ASC) LIMIT($limit) OFFSET($offset)', params);

    return schemas.map((s) => s.toEntity()).toList();
  }

  // Batch updates for better performance
  void batchUpdateLastUsed(List<String> entryIds) {
    final now = DateTime.now();

    _realm.write(() {
      for (final id in entryIds) {
        final schema = _realm.find<OtpEntrySchema>(id);
        if (schema != null) {
          schema.lastUsed = now;
        }
      }
    });
  }
}

Migration Strategies

Schema Migration

class DatabaseMigrations {
  static void performMigration(Migration migration, int oldSchemaVersion) {
    // Migration from version 1 to 2: Add lastUsed and isFavorite
    if (oldSchemaVersion < 2) {
      _migrateToV2(migration);
    }

    // Migration from version 2 to 3: Add categories and notes
    if (oldSchemaVersion < 3) {
      _migrateToV3(migration);
    }
  }

  static void _migrateToV2(Migration migration) {
    final oldEntries = migration.oldRealm.all('OtpEntry');
    final newEntries = migration.newRealm.all<OtpEntrySchema>();

    for (var i = 0; i < oldEntries.length; i++) {
      final newEntry = newEntries[i];
      newEntry.lastUsed = null; // Will be set when first used
      newEntry.isFavorite = false;
    }
  }

  static void _migrateToV3(Migration migration) {
    final newEntries = migration.newRealm.all<OtpEntrySchema>();

    for (final entry in newEntries) {
      entry.notes = null;
      entry.category = null;
      entry.sortOrder = 0;
    }
  }
}

This comprehensive database layer provides efficient, type-safe data operations while maintaining good performance and data integrity. 🗄️