---
phase: 20.1.1-change-the-i-button-on-cards-to-show-a-scrollable-history-of
plan: 01
subsystem: database
tags: [rusqlite, refinery, sqlite, migrations, notes, NoteEntry]

requires:
  - phase: 18-gh-issues-write-back-notes-and-card-cloud-storage
    provides: notes table with synced_at, save_note/read_notes/read_unsynced_notes, mark_note_synced

provides:
  - NoteEntry with author: Option<String> field and Default derive
  - V012 migration adding author TEXT column to notes table
  - save_note/read_notes round-tripping author; read_notes returns newest-first
  - upsert_notes_for_card diff-insert method (idempotent, preserves synced_at)
  - DATA-FLOW.md documenting author field, V012 migration, list_issue_comments read-back path

affects: [20.1.1-02, 20.1.1-03, 20.1.1-04, 20.1.1-05]

tech-stack:
  added: []
  patterns:
    - "V012 migration: LF line endings enforced via .gitattributes *.sql eol=lf"
    - "upsert_notes_for_card: diff-insert keyed on (card_id, note_date, content) — never DELETE+re-INSERT"
    - "read_notes ORDER BY id DESC: newest-first note ordering throughout notes read paths"

key-files:
  created:
    - crates/service/src/db/migrations/V012__notes_author.sql
    - .gitattributes
  modified:
    - crates/core/src/domain/note.rs
    - crates/service/src/db/sqlite.rs
    - crates/service/src/api/recipients.rs
    - crates/service/tests/sqlite_tests.rs
    - crates/app/src/live_client.rs
    - crates/app/tests/dashboard_layout_tests.rs
    - crates/app/tests/dashboard_projection_tests.rs
    - .planning/DATA-FLOW.md

key-decisions:
  - "author: None for optimistic local notes (displayed as 'You' in UI); Some(gh_handle) for GH-read-back notes"
  - "read_notes returns newest-first (ORDER BY id DESC) consistently in all three read paths"
  - "upsert_notes_for_card diff-keyed on (card_id, note_date, content) — preserves synced_at per SQLITE_TIPS.md"
  - ".gitattributes *.sql eol=lf added to prevent autocrlf CRLF injection on Windows (refinery DivergentVersion risk)"

patterns-established:
  - "Diff-insert pattern: COUNT(*) check before INSERT avoids DELETE+re-INSERT metadata loss"
  - "LF enforcement: .gitattributes *.sql eol=lf is the canonical prevention for refinery checksum divergence"

requirements-completed: []

duration: 11min
completed: 2026-04-15
---

# Phase 20.1.1 Plan 01: NoteEntry Author Field + V012 Migration + upsert_notes_for_card Summary

**NoteEntry gains `author: Option<String>` + Default; V012 adds the SQLite column; save_note/read_notes/upsert_notes_for_card all round-trip author with newest-first ordering; DATA-FLOW.md updated.**

## Performance

- **Duration:** 11 min
- **Started:** 2026-04-15T09:20:49Z
- **Completed:** 2026-04-15T09:31:38Z
- **Tasks:** 4
- **Files modified:** 8 + 2 created

## Accomplishments

- NoteEntry struct gains `author: Option<String>` as third field and `Default` derive; all construction sites across the workspace updated
- V012 migration file created with LF line endings; `.gitattributes *.sql eol=lf` added to prevent future CRLF injection
- `save_note`, `read_notes`, `read_unsynced_notes`, and bulk card-load query all round-trip the `author` column; `read_notes` now returns newest-first (ORDER BY id DESC per D-04)
- New `upsert_notes_for_card` diff-insert method: idempotent (keyed on card_id+note_date+content), preserves `synced_at` on existing rows
- DATA-FLOW.md updated with author field semantics, V012 migration bullet, and `list_issue_comments` read-back path documentation
- 4 new unit tests added (with_author, newest_first, idempotent, preserves_synced_at); all pass; existing test_notes_round_trip updated for new ordering

## Task Commits

1. **Task 1: Add author field + Default derive to NoteEntry** — `af6ac84` (feat)
2. **Task 2: Create V012 migration with LF line endings** — `16f7252` (chore)
3. **Task 3: Update sqlite.rs save_note/read_notes + add upsert_notes_for_card** — `649702b` (feat)
4. **Task 4: Update DATA-FLOW.md** — `a728bdb` (docs)

## Files Created/Modified

- `crates/core/src/domain/note.rs` — Added `author: Option<String>` field, `Default` derive, doc comment
- `crates/service/src/db/migrations/V012__notes_author.sql` — ALTER TABLE notes ADD COLUMN author TEXT (LF endings)
- `.gitattributes` — `*.sql eol=lf` rule to prevent autocrlf CRLF injection on Windows
- `crates/service/src/db/sqlite.rs` — Updated save_note, read_notes (newest-first), read_unsynced_notes, bulk notes query; added upsert_notes_for_card
- `crates/service/src/api/recipients.rs` — NoteEntry construction site: added author: None
- `crates/service/tests/sqlite_tests.rs` — Fixed 5 NoteEntry construction sites; updated test_notes_round_trip for newest-first; added 4 new tests
- `crates/app/src/live_client.rs` — Fixed 2 NoteEntry construction sites (author: None)
- `crates/app/tests/dashboard_layout_tests.rs` — Fixed NoteEntry construction site
- `crates/app/tests/dashboard_projection_tests.rs` — Fixed 2 NoteEntry construction sites
- `.planning/DATA-FLOW.md` — Updated notes field entry, Notes Sync State section, GH Issue comments block

## Decisions Made

- `author: None` for all existing local-write NoteEntry construction sites (not GH-sourced); downstream plans (03/04/05) will wire real author values from GH comment read-back
- `read_notes` ORDER BY id DESC applied consistently to all three notes read paths (read_notes, read_unsynced_notes, bulk card-load) for uniform newest-first semantics
- `upsert_notes_for_card` diff-keyed on (card_id, note_date, content) tuple — matches the granularity of GH comment identity without requiring a surrogate key

## Deviations from Plan

### Auto-fixed Issues

**1. [Rule 1 - Bug] Fixed test_notes_round_trip expecting oldest-first order after plan changed read_notes to newest-first**
- **Found during:** Task 3 (sqlite.rs update)
- **Issue:** Existing test asserted `notes[0].date == "2026-01-10"` (oldest), but ORDER BY id DESC now returns newest first
- **Fix:** Updated assertions to expect `notes[0].date == "2026-02-20"` (newest) with comment explaining the ordering change
- **Files modified:** `crates/service/tests/sqlite_tests.rs`
- **Verification:** `cargo test -p service` passes (16/16)
- **Committed in:** `649702b` (Task 3 commit)

**2. [Rule 3 - Blocking] Fixed NoteEntry construction sites in app crate (live_client.rs, test files)**
- **Found during:** Task 3 (workspace check after service fixes)
- **Issue:** `cargo check --workspace` revealed 4 remaining `missing field 'author'` errors in app crate — plan said to fix service/core only but these blocked compilation
- **Fix:** Added `author: None` to all 4 construction sites in live_client.rs (2 sites) and app test files (2 sites)
- **Files modified:** `crates/app/src/live_client.rs`, `crates/app/tests/dashboard_layout_tests.rs`, `crates/app/tests/dashboard_projection_tests.rs`
- **Verification:** `cargo check --workspace` exits 0; all 196 app lib tests pass
- **Committed in:** `649702b` (Task 3 commit)

---

**Total deviations:** 2 auto-fixed (1 Rule 1 test fix, 1 Rule 3 blocking compile error)
**Impact on plan:** Both fixes necessary for correctness. No scope creep — all changes were NoteEntry construction sites that must be updated when adding a required field.

## Issues Encountered

- `app.exe` was locked by the running app process, preventing `cargo test --workspace` from rebuilding the app binary. Worked around by running `cargo test -p app --lib` and `cargo test --workspace --exclude app` separately — all tests passed.

## Known Stubs

None — this plan adds a persistence layer field (`author`) only. No UI rendering of author values occurs in this plan. The `author: None` values set at existing call sites are correct for locally-written notes and will be populated by Plan 04 (GH comment read-back).

## Threat Flags

No new network endpoints, auth paths, or trust boundary changes introduced. The `upsert_notes_for_card` INSERT uses parameterized `params![...]` (T-2011-01 mitigated as specified in threat model).

## Next Phase Readiness

- All downstream plans (02–09) can now use `NoteEntry { ..., author: Some("gh_handle") }` for GH-sourced notes
- Plan 04 (GH comment read-back) can call `upsert_notes_for_card` with author values from `list_issue_comments`
- `cargo check --workspace` is clean; service test suite 100% green (16/16)

---
*Phase: 20.1.1-change-the-i-button-on-cards-to-show-a-scrollable-history-of*
*Completed: 2026-04-15*