TaskManagerMobile/DocumentSW/drift-database-layer.md

Drift Database Layer

Branch: NDM-App-190-drift-database-tables-daos-repositories Commits: bb0dcdf, 8256e57

Overview

Implements the local SQLite persistence layer using Drift 2.x. The database is the offline-first source of truth. All data access goes through typed repository classes that map between Drift-generated data classes and pure domain entities.

---

Files Created

lib/data/models/tables.dart

Four Drift table definitions:

Table	Description
Users	Device identity, pet selection, pet name
Tasks	Core task items with sync tracking columns
PetStates	Energy, streak, total completions, unlocked items
TaskCompletions	Append-only completion log for rewards/analytics

Key design details:

• Tasks has @TableIndex on userId (also on TaskCompletions).
• localId on Tasks is a nullable unique column for external sync identity.
• PetStates.userId is unique — one pet state row per user.
• Enums (petType, status) are stored as text strings; conversion is done in the repository layer.

lib/data/db/app_database.dart

@DriftDatabase class wiring all four tables. Uses driftDatabase(name: 'kittie_mobile_db') from drift_flutter (the correct API for drift_flutter ^0.2.0). schemaVersion = 1.

Generated part file: lib/data/db/app_database.g.dart.

lib/data/repositories/task_repository.dart

TaskRepository(AppDatabase db)

Method	Description
getAllTasks(userId)	All tasks ordered by sortOrder
getTodayTasks(userId)	Pending tasks only, ordered by sortOrder
getTaskById(id)	Single task or null
watchTasks(userId)	Reactive stream of all tasks
insertTask(entity)	Insert from domain entity
updateTask(entity)	Full-row update by id
deleteTask(id)	Hard delete
completeTask(taskId)	Transaction: sets status=done, inserts TaskCompletion; throws ArgumentError if task not found

lib/data/repositories/pet_repository.dart

PetRepository(AppDatabase db)

Method	Description
getPetState(userId)	Single pet state or null
watchPetState(userId)	Reactive stream (nullable)
insertPetState(entity)	First-time insert
updatePetState(entity)	Full-row update
setEnergyToday(userId, energy)	Partial update: energyToday + energyDate + updatedAt
incrementStreak(userId)	Transaction: read current streak, write +1

lib/data/repositories/user_repository.dart

UserRepository(AppDatabase db)

Method	Description
getCurrentUser()	First row (limit 1) or null; single-user device model
createUser(id, deviceId, name, petType, petName)	Named params for clarity
updateUser(entity)	Full-row update

---

Domain Entities (lib/domain/entities/)

Pure Dart classes — no Drift imports. Used across layers.

File	Contents
task_entity.dart	TaskEntity with copyWith (sentinel _unset pattern for nullable fields)
task_status.dart	TaskStatus enum: pending, done, snoozed, someday, archived
pet_state_entity.dart	PetStateEntity
pet_type.dart	PetType enum: dog, cat, capybara
user_entity.dart	UserEntity
task_completion_entity.dart	TaskCompletionEntity

Each enum has a fromString(String) factory that parses DB text values.

---

Patterns

Entity ↔ Drift mapping

Every repository has private _toEntity(Row) and _toCompanion(Entity) methods. No Drift types leak outside the repository.

Transactions

completeTask and incrementStreak wrap multi-step writes in _db.transaction(...).

copyWith for nullable fields

TaskEntity.copyWith uses a private const _unset = Object() sentinel so callers can explicitly pass null to clear a nullable field without ambiguity.

---

Tests (test/data/repositories/)

30 unit tests using NativeDatabase.memory() (real in-memory SQLite, no mocking).

File	Tests
user_repository_test.dart	6
task_repository_test.dart	12
pet_repository_test.dart	12

Each test group creates a fresh AppDatabase in setUp and closes it in tearDown.

---

Code Generation

After any change to tables.dart or app_database.dart, regenerate with:

dart run build_runner build --delete-conflicting-outputs