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

# Persistence Plan 1 Block 02 — SQLite Schema for App-Layer Records

**Status:** Complete on 2026-07-02.
**Goal:** Make the SQLite database capable of storing every record touched by the
current application read/command flows.

---

## Block outcome

After this block, `SchedulerDb` should expose all tables required for a durable
`ApplicationUnitOfWork`. Drift generation and migration/schema tests should prove
that a database can be created or upgraded and can store the current app-layer
record set.

This block does not yet wire Flutter to SQLite.

---

## Chunk 2.1 — Audit existing Drift tables against app repository needs

### Tasks

1. Confirm existing tables for:
   - `tasks`,
   - `projects`,
   - `locked_blocks`,
   - `locked_overrides`,
   - `settings`,
   - `snapshots`.
2. Compare with `ApplicationUnitOfWorkRepositories`, which also requires:
   - task activity records,
   - project statistics records,
   - owner settings records,
   - notice acknowledgement records,
   - operation/idempotency records.
3. Compare with current command use cases:
   - quick capture writes tasks and operation records,
   - schedule-from-backlog writes tasks, activities, possibly snapshots/notices,
   - complete/uncomplete writes tasks, task activities, project statistics, and
     operation records,
   - app-open recovery can read/write snapshots and operation-like state.
4. Write a short implementation note in the eventual block completion section
   listing which tables were missing and which were already present.

### Acceptance criteria

- Missing app-layer tables are identified before implementation.
- No application repository method is ignored because it is not needed by the
  first manual happy path; use cases should remain semantically durable.

---

## Chunk 2.2 — Add application operation table

### Tasks

1. Add a Drift table for committed application operations.
2. Include fields needed by `ApplicationOperationRecord` and idempotency lookup:
   - `operation_id TEXT PRIMARY KEY`,
   - `owner_id TEXT`,
   - `operation_name TEXT`,
   - `committed_at_utc DATETIME`,
   - optional `idempotency_key` only if the current core record/interface needs
     it,
   - `revision INT` only if the repository convention requires it for this row.
3. Add an index for owner + operation id/idempotency lookup if the table does not
   use a composite key.
4. Map through existing core document mapping helpers where available.
5. Do not expose raw Drift rows outside the SQLite adapter/runtime boundary.

### Acceptance criteria

- A committed operation can be inserted once and found on a later app run.
- Duplicate operation inserts are detected consistently.
- Operation records are owner-scoped.

---

## Chunk 2.3 — Add task activity table

### Tasks

1. Add a Drift table for append-only task activity records.
2. Include fields needed by `TaskActivity` and app queries:
   - `id TEXT PRIMARY KEY`,
   - `owner_id TEXT`,
   - `task_id TEXT`,
   - `project_id TEXT`,
   - `operation_id TEXT`,
   - `code TEXT`,
   - `occurred_at_utc DATETIME`,
   - metadata/details JSON if represented by the current document mapping,
   - `created_at_utc` / `updated_at_utc` only if mapping/common fields require
     them.
3. Add indexes for owner, task, project, and operation lookups.
4. Preserve append-only semantics; do not design an update/delete flow unless the
   current application repository requires it.

### Acceptance criteria

- Completion-generated activities survive close/reopen.
- `findByTaskId`, `findByProjectId`, `findByOperationId`, `findByOwner`, and
  `findAll` can be implemented deterministically.
- Activity rows do not require Flutter or UI metadata.

---

## Chunk 2.4 — Add project statistics table

### Tasks

1. Add a Drift table for project statistics aggregates.
2. Use `project_id` as the stable aggregate key, owner scoped.
3. Store counters and applied activity ids using explicit JSON columns if the
   current domain object is map/list heavy.
4. Include `revision INT` because current repository semantics use optimistic
   save for aggregates.
5. Add index/unique constraint for `owner_id + project_id`.

### Acceptance criteria

- Project statistics can be read, saved, and saved with expected revision.
- Completion after restart does not double-apply already recorded activities.
- JSON fields round-trip through core document mapping helpers.

---

## Chunk 2.5 — Add notice acknowledgement table if needed by current app layer

### Tasks

1. Add a Drift table for acknowledged notices if current application management
   or recovery use cases read/write notice acknowledgements.
2. Key by owner + notice id.
3. Store acknowledged/created timestamp fields required by the domain record.
4. Add deterministic owner paging.

### Acceptance criteria

- Notice acknowledgement reads/writes can be implemented without in-memory
  fallback.
- Acknowledged notices remain acknowledged after app restart.

---

## Chunk 2.6 — Update Drift database wiring and generation

### Tasks

1. Add table files under descriptive `scheduler_db/tables/...` subfolders.
2. Add DAO/accessor files only where useful; do not add empty accessors if direct
   queries are clearer and consistent with existing code.
3. Update `SchedulerDb` table list.
4. Apply the schema strategy chosen in Block 01:
   - patch version 1 only if explicitly permitted, or
   - bump `schemaVersion` and write migration steps.
5. Run Drift generation:

   ```sh
   cd packages/scheduler_persistence_sqlite
   dart run build_runner build --delete-conflicting-outputs
   ```

6. Format generated and handwritten files.

### Acceptance criteria

- Drift generated files are updated.
- `SchedulerDb` opens a new database with all expected tables.
- Existing table names and columns remain compatible unless a migration test
  covers the change.

BREAKPOINT: Stop if Drift generation would require Flutter SDK dependencies in a
pure Dart package.

---

## Chunk 2.7 — Add schema and migration tests

### Required tests

1. New-database schema smoke test includes all current tables.
2. Migration test from the previous schema creates the new tables and preserves
   existing task/project/settings rows.
3. Date/time persistence test confirms UTC values remain UTC after round-trip.
4. JSON-column smoke test confirms non-empty task activity/project statistics
   payloads round-trip.
5. Generated schema/table-name test stays deterministic across platforms.

### Acceptance criteria

- `cd packages/scheduler_persistence_sqlite && dart test` passes.
- `cd packages/scheduler_persistence_sqlite && dart analyze` passes.
- Root `dart test` still passes.

---

## Block acceptance criteria

- SQLite schema includes every table needed by current application read/command
  flows.
- Drift generation is current.
- Migration behavior is tested if `schemaVersion` changed.
- No Flutter or widget code imports Drift or SQL.
- No command path must fall back to in-memory-only app records.

## Validation after block

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