---
phase: 15-sqlite-foundation-deprecated-field-cleanup-and-ui-polish-gap-closure
plan: 03
subsystem: database
tags: [sqlite, rusqlite, refinery, migrations, persistence, live-client]

requires:
  - phase: 15-02
    provides: "Cleaned RecipientCardSnapshot with product_names/notes, no deprecated fields"

provides:
  - "SqliteStore with open(), open_in_memory(), upsert/read methods for cards, notes, archive"
  - "V001 migration creating all 8 DATA-FLOW.md entity tables"
  - "LiveClient reads from SQLite (fetch_card_snapshots via read_all_cards)"
  - "run_sync_cycle writes matched cards to SQLite (upsert_card)"
  - "Data persists across app restarts (witwhat.db at %APPDATA%/WITwhat/)"

affects:
  - phase-17-product-catalog
  - phase-18-gh-issue-sync

tech-stack:
  added:
    - "rusqlite 0.32 (bundled feature — static SQLite linkage)"
    - "refinery 0.8 (embed_migrations! macro for SQL migration files)"
  patterns:
    - "Single Arc<Mutex<Connection>> with WAL mode — no connection pool"
    - "embed_migrations! resolves from crates/service/src/db/migrations/"
    - "CardRow is the SQLite unit; snapshot_to_card_row/card_row_to_snapshot convert at boundaries"
    - "Unassigned cards are transient (in-memory only); assigned cards persist to SQLite"

key-files:
  created:
    - "crates/service/src/db/sqlite.rs — SqliteStore implementation"
    - "crates/service/src/db/migrations/V001__initial_schema.sql — full entity schema"
    - "crates/service/tests/sqlite_tests.rs — 9 integration tests using open_in_memory()"
  modified:
    - "crates/service/Cargo.toml — rusqlite + refinery dependencies"
    - "crates/service/src/db/mod.rs — pub mod sqlite added"
    - "crates/app/src/live_client.rs — store: Arc<SqliteStore>, rewired fetch/sync/save_note"

key-decisions:
  - "rusqlite 0.32 with bundled feature (static SQLite — no system library dependency)"
  - "refinery 0.8 with embed_migrations! — SQL files embedded at compile time from src/db/migrations/"
  - "Repository kept as secondary field for legacy item methods (add/remove/rename) — Phase 17 will replace with SQLite product table"
  - "Unassigned cards NOT persisted to SQLite — they are transient, rebuilt each sync cycle"
  - "db_path() follows same convention as config_path(): %APPDATA%/WITwhat/witwhat.db"
  - "chrono_free_date() computes YYYY-MM-DD without chrono dependency for note timestamps"

patterns-established:
  - "TDD: failing tests written first, implementation written to pass"
  - "SQLite FK constraint: cards.recipient_id nullable — cards can exist without a recipient row"
  - "upsert_card deletes and re-inserts product_names and notes for full replace semantics"

requirements-completed: [PERSIST-01, PERSIST-02, PERSIST-03]

duration: 13min
completed: 2026-03-23
---

# Phase 15 Plan 03: SQLite Foundation Summary

**SqliteStore with refinery migrations wired into LiveClient — card data now persists to %APPDATA%/WITwhat/witwhat.db across app restarts**

## Performance

- **Duration:** 13 min
- **Started:** 2026-03-23T02:54:08Z
- **Completed:** 2026-03-23T03:07:21Z
- **Tasks:** 2
- **Files modified:** 6

## Accomplishments

- Created SqliteStore with WAL mode, refinery-managed V001 schema covering all 8 DATA-FLOW.md entity tables (recipients, cards, card_product_names, notes, products, serial_instances, archive_records, pending_edits)
- LiveClient.fetch_card_snapshots() now reads from SQLite via read_all_cards() (RULE-03 enforced)
- run_sync_cycle() writes matched Shopify-sourced cards to SQLite via upsert_card() on every sync cycle (PERSIST-01)
- 9 integration tests using open_in_memory() verify all CRUD behaviors

## Task Commits

Each task was committed atomically:

1. **Task 1: Create SqliteStore module with refinery migrations and CRUD methods** - `b1b04c1` (feat)
2. **Task 2: Rewire LiveClient from Repository to SqliteStore** - `77d8f31` (feat)

## Files Created/Modified

- `crates/service/src/db/sqlite.rs` — SqliteStore struct with open(), open_in_memory(), upsert_card(), read_all_cards(), upsert_archive_record(), read_archive_state(), save_note(), read_notes(), list_table_names()
- `crates/service/src/db/migrations/V001__initial_schema.sql` — Full schema for all DATA-FLOW.md entities; github_profile_url absent (RULE-01), shipment on cards not recipients (RULE-02)
- `crates/service/tests/sqlite_tests.rs` — 9 integration tests: open_in_memory table creation, card round-trip, upsert idempotency, empty store, archive state, product names, notes, save_note
- `crates/service/Cargo.toml` — rusqlite 0.32 (bundled) + refinery 0.8 (rusqlite feature)
- `crates/service/src/db/mod.rs` — Added `pub mod sqlite;`
- `crates/app/src/live_client.rs` — store: Arc<SqliteStore>, db_path(), card_row_to_snapshot(), snapshot_to_card_row(), rewired fetch_card_snapshots/run_sync_cycle/save_note, updated tests

## Decisions Made

- **rusqlite bundled feature**: Static SQLite linkage removes system library dependency; simpler deployment on Windows.
- **refinery embed_migrations!**: SQL migration files compiled into the binary. Path `"src/db/migrations"` resolves relative to crate Cargo.toml.
- **Repository kept for legacy items**: add_item/remove_item/rename_item/search_item_catalog keep the in-memory Repository for Phase 15. Phase 17 will replace with SQLite product table. TODO comments added.
- **Unassigned cards not persisted**: These are transient — rebuilt each sync cycle from Shopify orders with no recipient match. Assigned cards persist.
- **chrono_free_date()**: Note timestamps computed without the chrono crate to avoid adding a new dependency.

## Deviations from Plan

### Auto-fixed Issues

**1. [Rule 1 - Bug] Fixed FK constraint by setting recipient_id = None in test helper**
- **Found during:** Task 1 (TDD GREEN phase — running tests)
- **Issue:** make_card() set `recipient_id: Some("recipient:{id}")` but no recipient row exists in the recipients table; FK constraint (PRAGMA foreign_keys = ON) rejected the insert
- **Fix:** Changed make_card() to use `recipient_id: None` — cards are nullable FK to recipients per the schema design
- **Files modified:** `crates/service/tests/sqlite_tests.rs`
- **Verification:** All 9 sqlite_tests pass
- **Committed in:** b1b04c1 (Task 1 commit)

---

**Total deviations:** 1 auto-fixed (Rule 1 - bug in test helper FK setup)
**Impact on plan:** Minor test fix. Schema design unchanged — nullable recipient_id is correct per DATA-FLOW.md (cards can exist without a matched recipient, per RULE-04).

## Issues Encountered

None beyond the FK test fixture issue documented above.

## Next Phase Readiness

- SQLite foundation is complete: schema created, CRUD methods working, LiveClient reads from SQLite
- Phase 17 (product catalog) can add product_id references to cards and implement item management via the products/serial_instances tables already defined in V001
- Phase 18 (GH Issue sync) can write card data as ww-card issues using existing SQLite as local cache
- Remaining item for full RULE-03 compliance: add/remove/rename item methods still use in-memory Repository (noted with TODO Phase 17)

## Self-Check: PASSED

All created files verified present on disk. All task commits verified in git log.

| Check | Result |
|-------|--------|
| `crates/service/src/db/sqlite.rs` | FOUND |
| `crates/service/src/db/migrations/V001__initial_schema.sql` | FOUND |
| `crates/service/tests/sqlite_tests.rs` | FOUND |
| Commit `b1b04c1` (Task 1) | FOUND |
| Commit `77d8f31` (Task 2) | FOUND |