# Phase 15: SQLite Foundation and Cleanup - Context

**Gathered:** 2026-03-22
**Status:** Ready for planning

<domain>
## Phase Boundary

Replace the in-memory HashMap-backed Repository with SQLite persistence so card data survives app restarts. Remove all deprecated data-model fields identified in DATA-FLOW.md (github_profile_url, shipment on Recipient, item_summary, latest_note). Close the remaining Phase 14 UI gap (card VerticalLayout migration + toast-is-warning wiring). The SQLite schema starts clean — no deprecated fields from day one.

</domain>

<decisions>
## Implementation Decisions

### SQLite Connection Strategy
- Single `Arc<Mutex<Connection>>` with WAL mode enabled
- No connection pool (r2d2-sqlite is overkill for 2-thread access pattern)
- WAL mode allows concurrent reads while one writer holds the lock
- Keep critical sections short to avoid deadlock risk
- PRAGMA foreign_keys = ON enforced at connection init

### Database Location and Schema
- Database file at `%APPDATA%/WITwhat/witwhat.db` (same directory as config.toml)
- Schema migrations managed by `refinery` crate with numbered SQL migration files
- SQLite schema contains all DATA-FLOW.md entities: recipients, cards, card_products, notes, products, serial_instances, archive_records, pending_edits
- Schema starts clean — deprecated fields (github_profile_url, shipment_status/tracking_state on Recipient, item_summary, latest_note scalar) are never added to SQLite

### Data Migration on Upgrade
- Fresh API fetch on first launch with empty database — no migration from in-memory state
- Show empty dashboard with sync indicator ("Syncing data...") while initial sync populates SQLite
- No dedicated splash/progress screen — cards appear as sync completes
- Existing connection indicator shows sync status

### Deprecated Field Cleanup
- All deprecated field removal happens in the same pass as SQLite migration (not staged)
- `github_profile_url`: Remove from all structs (Recipient, RecipientCardSnapshot, RecipientSnapshot, DashboardCardViewModel). Per RULE-01.
- `shipment_status` and `tracking_state`: Remove from Recipient struct only. Card pipeline already carries these at card-level from Shopify fulfillments. Per RULE-02.
- `item_summary`: Replace with `Vec<String>` product names (simple list, no product IDs yet). Full `Vec<ProductRef>` with product IDs comes in Phase 17.
- `latest_note`: Replace with `Vec<NoteEntry { date, content }>` stored in SQLite. Full GH Issue comment sync comes in Phase 18.

### Test Strategy
- Keep in-memory Repository for unit tests (fast, no refactoring)
- Integration tests use SQLite with `:memory:` databases
- Production code reads exclusively from SQLite (RULE-03)

### Phase 14 Gap Closure (POLISH-01)
- Card VerticalLayout migration: execute exactly as designed in 14-CONTEXT.md
- Replace absolute y-coordinates with VerticalLayout + 8px spacing
- Card height becomes content-driven (not fixed) — cards with more items are taller
- Toast-is-warning: conditional background color wired from Rust (amber for warnings, blue for informational)

### Claude's Discretion
- Exact refinery migration file structure and naming
- SQLite PRAGMA tuning beyond WAL and foreign_keys (journal_size_limit, synchronous mode, etc.)
- Internal module organization for SQLite layer (single module vs split by entity)
- Exact VerticalLayout spacing adjustments if 8px doesn't look right after migration
- How to handle the transition period where LiveClient switches from Repository reads to SQLite reads

</decisions>

<canonical_refs>
## Canonical References

**Downstream agents MUST read these before planning or implementing.**

### Data Architecture
- `.planning/DATA-FLOW.md` — Authoritative reference for all data entities, field sources, sync directions, and agent rules. MANDATORY read before touching any data struct.
- `.planning/DATA-FLOW.md` §AGENT RULES — RULE-01 (github_profile_url prohibited), RULE-02 (shipment on cards not recipients), RULE-03 (SQLite single read source), RULE-04 (cards from Shopify only), RULE-07 (item_summary deprecated), RULE-08 (latest_note deprecated)

### UI Polish Gap
- `.planning/phases/14-ui-polish-consolidate-cross-phase-audit-findings-typography-scale-empty-states-color-tokens-card-layout-icon-overlap/14-CONTEXT.md` — Card VerticalLayout migration design, toast-is-warning wiring, tokens.slint structure

### Current Implementation
- `crates/core/src/domain/recipient.rs` — Recipient struct with deprecated fields to remove
- `crates/app/src/service_client.rs` — RecipientCardSnapshot and DashboardDataClient trait
- `crates/app/src/live_client.rs` — LiveClient with Arc<Mutex<Repository>> to rewire to SQLite
- `crates/service/src/db/repository.rs` — In-memory Repository (becomes test-only)
- `crates/service/src/api/recipients.rs` — RecipientSnapshot with deprecated fields
- `crates/app/ui/card.slint` — Card layout target for VerticalLayout migration
- `crates/app/ui/dashboard.slint` — Toast component for warning wiring

### Requirements
- `.planning/REQUIREMENTS.md` — POLISH-01, PERSIST-01, PERSIST-02, PERSIST-03, CLEAN-01, CLEAN-02, CLEAN-03, CLEAN-04

</canonical_refs>

<code_context>
## Existing Code Insights

### Reusable Assets
- `crates/service/src/db/repository.rs`: In-memory Repository — becomes test-only reference for expected behavior
- `crates/app/src/live_client.rs`: LiveClient already wraps `Arc<Mutex<Repository>>` — swap inner type to SQLite connection
- `crates/app/ui/tokens.slint`: Color and Typography globals already created in Phase 14
- `crates/app/src/config.rs`: AppConfig already reads from `%APPDATA%/WITwhat/config.toml` — reuse path resolution

### Established Patterns
- `Arc<Mutex<T>>` pattern used throughout for shared state (LiveClient, unassigned_cards, recipient_list)
- RecipientCardSnapshot → DashboardCardViewModel → Slint CardData transformation pipeline
- Sync cycle in `run_sync_cycle()` within live_client.rs — writes to Repository, needs rewiring to SQLite

### Integration Points
- `LiveClient::new()` — needs SQLite connection init instead of Repository
- `run_sync_cycle()` — sync writes go to SQLite instead of in-memory Repository
- `fetch_card_snapshots()` — reads from SQLite instead of Repository
- `main.rs` — database file creation, connection init, WAL pragma setup
- `card.slint` — VerticalLayout migration for card body
- `dashboard.slint` — toast-is-warning conditional

</code_context>

<specifics>
## Specific Ideas

No specific requirements — open to standard approaches.

</specifics>

<deferred>
## Deferred Ideas

None — discussion stayed within phase scope.

</deferred>

---

*Phase: 15-sqlite-foundation-deprecated-field-cleanup-and-ui-polish-gap-closure*
*Context gathered: 2026-03-22*