---
phase: 06-output-reporting
plan: 04
subsystem: database
tags: [sqlite, storage, query-layer, findings, keys-command]

requires:
  - phase: 05-verification
    provides: "Finding struct with verify_* fields; DB schema with verified, verify_status, verify_http_code, verify_metadata_json columns"
provides:
  - "storage.Filters struct (Provider, Verified *bool, Limit, Offset)"
  - "DB.ListFindingsFiltered(encKey, Filters) — filtered + paginated findings list"
  - "DB.GetFinding(id, encKey) — single-row lookup, sql.ErrNoRows on miss"
  - "DB.DeleteFinding(id) — returns RowsAffected"
affects: [06-05-keys-command, 06-06-keys-export, dashboard-query-layer]

tech-stack:
  added: []
  patterns:
    - "Dynamic WHERE clause built from optional filter fields (empty string / nil pointer sentinels)"
    - "Shared scan/hydrate helpers reused across *sql.Rows and *sql.Row paths"
    - "sql.ErrNoRows propagated verbatim so callers use errors.Is"

key-files:
  created:
    - pkg/storage/queries.go
    - pkg/storage/queries_test.go
  modified: []

key-decisions:
  - "Verified uses *bool (not bool) so the zero value means 'no filter' rather than 'only unverified'"
  - "DeleteFinding returns (rowsAffected, error) so callers decide if missing-id is a user error"
  - "GetFinding returns *Finding (not Finding) so the nil + sql.ErrNoRows pattern is idiomatic"
  - "Scan helpers extracted (scanFindingRow / scanFindingRowFromRow / hydrateFinding) instead of duplicating findings.go logic — future changes to decrypt/JSON schema stay localized"

patterns-established:
  - "Query layer pattern: new query functions live in pkg/storage/queries.go, CRUD in findings.go"
  - "Filter struct pattern: optional fields via empty-string / nil-pointer sentinels, Limit<=0 disables pagination"

requirements-completed: [KEYS-01, KEYS-02, KEYS-06]

duration: 2 min
completed: 2026-04-05
---

# Phase 6 Plan 4: Findings Query Layer Summary

**Filter/lookup/delete query layer on `pkg/storage/findings` providing the DB primitives the upcoming `keyhunter keys` command tree will call.**

## Performance

- **Duration:** 2 min
- **Started:** 2026-04-05T20:30:00Z
- **Completed:** 2026-04-05T20:31:21Z
- **Tasks:** 1 (TDD: RED + GREEN)
- **Files created:** 2

## Accomplishments

- `Filters` struct with Provider, Verified (`*bool`), Limit, Offset — supports every filter combination the `keys list` command needs (KEYS-01, KEYS-02)
- `DB.ListFindingsFiltered` with dynamic WHERE, `ORDER BY created_at DESC, id DESC`, and opt-in LIMIT/OFFSET pagination
- `DB.GetFinding(id, encKey)` returns `*Finding` or `(nil, sql.ErrNoRows)` — idiomatic miss detection via `errors.Is`
- `DB.DeleteFinding(id)` returns `RowsAffected` so callers differentiate hit vs. miss (KEYS-06)
- Seven focused tests covering by-provider filtering, verified true/false, pagination math, single-row hit/miss, and delete hit/miss — all on in-memory SQLite with AES-256 key derivation
- Zero regressions: full `pkg/storage/...` test suite green, `go build ./...` clean

## Task Commits

1. **Task 1 RED: failing query layer tests** — `67763ec` (test)
2. **Task 1 GREEN: implement Filters + Query methods** — `b1e4dea` (feat)

## Files Created/Modified

- `pkg/storage/queries.go` — Filters struct, ListFindingsFiltered, GetFinding, DeleteFinding, shared scan/hydrate helpers (157 lines)
- `pkg/storage/queries_test.go` — seven tests + `seedQueryFindings` fixture helper (149 lines)

## Decisions Made

- **`Verified *bool` instead of `bool`** — pointer nil means "any", matching the filter semantics planners specified and avoiding a tri-state enum
- **Extract scan helpers rather than reuse `ListFindings` code inline** — `scanFindingRow`, `scanFindingRowFromRow`, and `hydrateFinding` are private helpers in queries.go; keeps findings.go untouched (backward compatible) while avoiding near-duplicate scan blocks
- **`GetFinding` returns `(*Finding, error)`** — `nil` pointer on miss is more ergonomic for the upcoming `keys show <id>` command than a zero-value struct
- **`ORDER BY created_at DESC, id DESC`** — secondary sort by id guarantees stable pagination when multiple findings share the same SQLite CURRENT_TIMESTAMP second

## Deviations from Plan

None - plan executed exactly as written.

## Issues Encountered

None.

## User Setup Required

None - no external service configuration required.

## Next Phase Readiness

- Query layer primitives are in place for Plan 05 (`keyhunter keys list/show/delete` commands)
- Pattern matches what cmd/keys.go will need: Filters from CLI flags → ListFindingsFiltered → table/JSON/CSV output via the Plan 01-03 formatters
- Ready for 06-05-PLAN.md

## Self-Check: PASSED

Verified:
- `pkg/storage/queries.go` exists on disk
- `pkg/storage/queries_test.go` exists on disk
- Commit `67763ec` present in `git log` (RED)
- Commit `b1e4dea` present in `git log` (GREEN)
- `grep "type Filters struct" pkg/storage/queries.go` matches
- `grep "ListFindingsFiltered\|GetFinding\|DeleteFinding" pkg/storage/queries.go` matches all three
- `go test ./pkg/storage/... -count=1` green (all prior tests + 7 new tests)
- `go build ./...` green

---
*Phase: 06-output-reporting*
*Completed: 2026-04-05*