<!-- SPDX-FileCopyrightText: 2026 FocusFlow contributors -->
<!-- SPDX-License-Identifier: AGPL-3.0-only -->

# Persistence Plan 1 Block 03 — SQLite Application Unit of Work

**Status:** Complete on 2026-07-02.
**Goal:** Implement a SQLite-backed `ApplicationUnitOfWork` that current
scheduler application use cases can use without knowing about Drift.

---

## Block outcome

After this block, scheduler application read/command use cases should run
against SQLite in tests. The adapter must support current reads and writes for
Today, Backlog, quick capture, schedule-from-backlog, done, and uncomplete.

Flutter still remains unwired until Block 04.

---

## Chunk 3.1 — Choose implementation location and public API

### Tasks

1. Implement the application-unit-of-work adapter in the SQLite adapter layer or
   a tiny runtime package, as chosen in Block 01.
2. Preferred direct adapter names if implemented in
   `scheduler_persistence_sqlite`:
   - `SqliteApplicationUnitOfWork`,
   - `SqliteApplicationRepositories`,
   - `SqliteApplicationRuntime` only if it owns file opening/lifecycle.
3. Export the public entry point from `packages/scheduler_persistence_sqlite/lib/sqlite.dart`
   or from the chosen runtime package.
4. Keep raw Drift database classes available for tests but do not require
   Flutter widgets to instantiate them.

### Acceptance criteria

- Application code can create a SQLite-backed `ApplicationUnitOfWork` through a
  public package API.
- `scheduler_core` remains storage-free.
- Existing repository conformance APIs remain intact.

---

## Chunk 3.2 — Implement transaction semantics

### Tasks

1. Implement `ApplicationUnitOfWork.run<T>` using a Drift transaction.
2. Implement `ApplicationUnitOfWork.read<T>` as a read-only repository view.
3. Preserve current in-memory semantics:
   - duplicate `operationId` returns duplicate-operation failure,
   - domain validation exceptions map to validation failures,
   - application persistence exceptions map to persistence failure,
   - unexpected errors map to unexpected failure,
   - operation records are written only after the action succeeds.
4. Ensure operation record commit and all repository writes happen atomically.
5. Ensure failed actions do not partially save tasks or activities.
6. Ensure nested transaction behavior is either not used or explicitly safe.

### Acceptance criteria

- A command failure leaves the database unchanged.
- A successful command writes changed tasks, activities/statistics, and operation
  record in one transaction.
- Duplicate operation id is rejected across app restarts.

---

## Chunk 3.3 — Implement SQLite app task repository

### Required methods

Implement the `scheduler_core` application-layer task repository methods used by
current use cases, including at minimum:

1. `findById`
2. `findRecordById`
3. `findAll`
4. `findByStatus`
5. `findByOwner`
6. `findByProjectId`
7. `findByParentTaskId`
8. `findBacklogCandidates`
9. `findScheduledInWindow`
10. `findScheduledInWindowForOwner`
11. `findForLocalDay`
12. `save`
13. `saveAll`
14. `insert`
15. `saveIfRevision`

### Implementation notes

- Reuse existing task document/row mapping helpers where possible.
- Preserve owner scoping.
- Preserve deterministic ordering used by Today and Backlog read models.
- Treat UTC task instants as stored UTC values.
- `save`/`saveAll` should insert if absent only if current in-memory semantics
  require that behavior; otherwise use explicit repository mutation failures.
  Document the chosen behavior because current command code calls `saveAll` for
  changed tasks.
- If `saveAll` needs compare-and-set behavior, load current records first and
  write each changed row in the enclosing transaction.

### Acceptance criteria

- Quick-captured tasks are stored as backlog tasks.
- Scheduled tasks reload with schedule placement.
- Completed tasks reload with `TaskStatus.completed`, actual times, and
  `completedAt` where present.
- Stale revision tests cover `saveIfRevision`.

---

## Chunk 3.4 — Implement SQLite app project/settings/locked/snapshot repositories

### Tasks

1. Implement app-layer `ProjectRepository` using existing project SQLite rows.
2. Implement app-layer `OwnerSettingsRepository` using existing settings rows.
3. Implement app-layer `LockedBlockRepository` using existing locked block and
   override rows.
4. Implement app-layer `SchedulingSnapshotRepository` using existing snapshot
   rows.
5. Preserve domain object mapping and owner scoping.
6. Support repository page semantics where the app-layer interface requires
   pages.
7. Convert lower-level repository failures into application-layer repository
   failures consistently.

### Acceptance criteria

- `GetTodayStateQuery` can run against an empty SQLite database plus bootstrap
  owner settings/project.
- Locked time and snapshots do not crash current app reads.
- Snapshot retention/delete behavior remains consistent with ADR policy.

---

## Chunk 3.5 — Implement SQLite app activity/statistics/notice/operation repositories

### Tasks

1. Implement `TaskActivityRepository` over the new task activity table.
2. Implement `ProjectStatisticsRepository` over the new project statistics table.
3. Implement `NoticeAcknowledgementRepository` over the new notice table, if it
   exists after Block 02.
4. Implement `ApplicationOperationRepository` over the new operation table.
5. Make append-only writes idempotent where current interfaces require it.
6. Add owner filtering to every owner-scoped query.
7. Avoid storing derived UI-only strings.

### Acceptance criteria

- Completing a task writes activity records durably.
- Project statistics survive close/reopen.
- Operation records survive close/reopen and prevent duplicate operation replay.
- Notice acknowledgement behavior, if used, survives close/reopen.

---

## Chunk 3.6 — Add adapter-level tests for current command/read flows

### Required tests

1. Empty database read:
   - bootstrap owner settings/project if using bootstrap helper,
   - `GetTodayStateQuery` returns empty Today without failure.
2. Quick capture persistence:
   - run `V1ApplicationCommandUseCases.quickCaptureToBacklog`,
   - close the first database handle,
   - reopen the same file,
   - read backlog and find the task.
3. Schedule persistence:
   - quick capture or insert a backlog task,
   - schedule it to next available slot,
   - reopen,
   - Today state contains the scheduled task and Backlog does not.
4. Completion persistence:
   - schedule a task,
   - complete it,
   - reopen,
   - Today state shows completed/done state.
5. Uncomplete persistence:
   - complete then uncomplete,
   - reopen,
   - Today state shows planned/not-done state.
6. Atomic failure:
   - trigger a validation or stale write failure,
   - assert no partial task/activity/statistics/operation writes persisted.
7. Duplicate operation:
   - run one command,
   - attempt same operation id again after reopen,
   - assert duplicate-operation failure.

### Acceptance criteria

- Tests use temporary file SQLite databases, not only `NativeDatabase.memory()`.
- Tests explicitly close database handles before reopen assertions.
- Tests do not depend on Flutter.

---

## Block acceptance criteria

- A SQLite-backed `ApplicationUnitOfWork` exists and is exported through the
  chosen adapter/runtime boundary.
- Current scheduler reads and commands run against SQLite in package tests.
- Transactions prevent partial persistence.
- Close/reopen behavior is proven outside Flutter.
- Existing repository conformance tests still pass.

## Validation after block

```sh
cd packages/scheduler_persistence_sqlite
dart analyze
dart test
cd ../..
scripts/test.sh
```

If `scripts/test.sh` is unavailable, run `dart analyze` and `dart test` from the
workspace root and document the gap.
