---
phase: 06-output-reporting
plan: 04
type: execute
wave: 1
depends_on: []
files_modified:
  - pkg/storage/queries.go
  - pkg/storage/queries_test.go
autonomous: true
requirements: [KEYS-01, KEYS-02, KEYS-06]

must_haves:
  truths:
    - "the keys command (Plan 05) can list findings with filters"
    - "the keys command can fetch a single finding by ID"
    - "the keys command can delete a finding by ID"
    - "existing db.ListFindings remains backward compatible"
  artifacts:
    - path: pkg/storage/queries.go
      provides: "Filters, ListFindingsFiltered, GetFinding, DeleteFinding"
      exports: ["Filters", "ListFindingsFiltered", "GetFinding", "DeleteFinding"]
  key_links:
    - from: pkg/storage/queries.go
      to: pkg/storage/findings.go
      via: "Reuses encrypt/decrypt + Finding struct"
      pattern: "Decrypt\\(encrypted, encKey\\)"
---

<objective>
Add a thin query layer on top of findings.go providing filtered list, single-record lookup, and delete. These are the DB primitives the `keyhunter keys` command tree (Plan 05) will call.

Purpose: Key management (KEYS-01, KEYS-02, KEYS-06 foundation; KEYS-03/04/05 built on top in Plan 05).
Output: `pkg/storage/queries.go` + tests using in-memory SQLite (":memory:").
</objective>

<execution_context>
@$HOME/.claude/get-shit-done/workflows/execute-plan.md
@$HOME/.claude/get-shit-done/templates/summary.md
</execution_context>

<context>
@.planning/phases/06-output-reporting/06-CONTEXT.md
@pkg/storage/findings.go
@pkg/storage/db.go

<interfaces>
From pkg/storage/findings.go:
```go
type Finding struct {
    ID             int64
    ScanID         int64
    ProviderName   string
    KeyValue       string // plaintext after decrypt
    KeyMasked      string
    Confidence     string
    SourcePath     string
    SourceType     string
    LineNumber     int
    CreatedAt      time.Time
    Verified       bool
    VerifyStatus   string
    VerifyHTTPCode int
    VerifyMetadata map[string]string
}
func (db *DB) SaveFinding(f Finding, encKey []byte) (int64, error)
func (db *DB) ListFindings(encKey []byte) ([]Finding, error)
```
DB schema columns for findings table: id, scan_id, provider_name, key_value (encrypted BLOB), key_masked, confidence, source_path, source_type, line_number, verified, verify_status, verify_http_code, verify_metadata_json, created_at.
</interfaces>
</context>

<tasks>

<task type="auto" tdd="true">
  <name>Task 1: Filters struct, ListFindingsFiltered, GetFinding, DeleteFinding</name>
  <files>pkg/storage/queries.go, pkg/storage/queries_test.go</files>
  <read_first>
    - pkg/storage/findings.go (row scan helpers, columns)
    - pkg/storage/db.go (Open, schema usage)
    - pkg/storage/keys.go or wherever DeriveKey/NewSalt live (for test setup)
  </read_first>
  <behavior>
    - Filters: { Provider string, Verified *bool, Limit int, Offset int }.
    - ListFindingsFiltered(encKey, filters) returns decrypted findings matching:
      * Provider (exact match, empty = no filter)
      * Verified (nil = no filter; ptr-bool matches 1/0)
      * ORDER BY created_at DESC, id DESC
      * Limit/Offset applied only when Limit > 0
    - GetFinding(id, encKey) returns *Finding or (nil, sql.ErrNoRows) when absent.
    - DeleteFinding(id) runs DELETE; returns (rowsAffected int64, error). Zero rows affected is not an error (caller decides).
    - Tests (using ":memory:" DB + DeriveKey with a test salt):
      * Seed 3 findings across 2 providers with mixed verified status.
      * TestListFindingsFiltered_ByProvider: filter provider=="openai" returns only openai rows.
      * TestListFindingsFiltered_Verified: Verified=&true returns only verified rows.
      * TestListFindingsFiltered_Pagination: Limit=1, Offset=1 returns second row.
      * TestGetFinding_Hit: returns row with decrypted KeyValue matching original.
      * TestGetFinding_Miss: returns nil, sql.ErrNoRows for id=9999.
      * TestDeleteFinding_Hit: rowsAffected==1, subsequent Get returns sql.ErrNoRows.
      * TestDeleteFinding_Miss: rowsAffected==0, no error.
  </behavior>
  <action>
    Create pkg/storage/queries.go:

    ```go
    package storage

    import (
        "database/sql"
        "encoding/json"
        "fmt"
        "strings"
        "time"
    )

    // Filters selects a subset of findings for ListFindingsFiltered.
    // Empty Provider means "any provider". Nil Verified means "any verified state".
    // Limit <= 0 disables pagination.
    type Filters struct {
        Provider string
        Verified *bool
        Limit    int
        Offset   int
    }

    // ListFindingsFiltered returns findings matching the given filters, newest first.
    // Key values are decrypted before return. encKey must match the key used at save time.
    func (db *DB) ListFindingsFiltered(encKey []byte, f Filters) ([]Finding, error) {
        var (
            where []string
            args  []interface{}
        )
        if f.Provider != "" {
            where = append(where, "provider_name = ?")
            args = append(args, f.Provider)
        }
        if f.Verified != nil {
            where = append(where, "verified = ?")
            if *f.Verified {
                args = append(args, 1)
            } else {
                args = append(args, 0)
            }
        }
        q := `SELECT id, scan_id, provider_name, key_value, key_masked, confidence,
                     source_path, source_type, line_number,
                     verified, verify_status, verify_http_code, verify_metadata_json,
                     created_at
              FROM findings`
        if len(where) > 0 {
            q += " WHERE " + strings.Join(where, " AND ")
        }
        q += " ORDER BY created_at DESC, id DESC"
        if f.Limit > 0 {
            q += " LIMIT ? OFFSET ?"
            args = append(args, f.Limit, f.Offset)
        }
        rows, err := db.sql.Query(q, args...)
        if err != nil {
            return nil, fmt.Errorf("querying findings: %w", err)
        }
        defer rows.Close()
        var out []Finding
        for rows.Next() {
            f, err := scanFindingRow(rows, encKey)
            if err != nil {
                return nil, err
            }
            out = append(out, f)
        }
        return out, rows.Err()
    }

    // GetFinding returns a single finding by id. Returns sql.ErrNoRows if absent.
    func (db *DB) GetFinding(id int64, encKey []byte) (*Finding, error) {
        row := db.sql.QueryRow(
            `SELECT id, scan_id, provider_name, key_value, key_masked, confidence,
                    source_path, source_type, line_number,
                    verified, verify_status, verify_http_code, verify_metadata_json,
                    created_at
             FROM findings WHERE id = ?`, id)
        f, err := scanFindingRowFromRow(row, encKey)
        if err != nil {
            return nil, err
        }
        return &f, nil
    }

    // DeleteFinding removes the finding with the given id.
    // Returns the number of rows affected (0 if no such id).
    func (db *DB) DeleteFinding(id int64) (int64, error) {
        res, err := db.sql.Exec(`DELETE FROM findings WHERE id = ?`, id)
        if err != nil {
            return 0, fmt.Errorf("deleting finding %d: %w", id, err)
        }
        return res.RowsAffected()
    }

    // scanFindingRow reads one Finding from *sql.Rows and decrypts its key.
    func scanFindingRow(rows *sql.Rows, encKey []byte) (Finding, error) {
        var f Finding
        var encrypted []byte
        var createdAt string
        var scanID sql.NullInt64
        var verifiedInt int
        var metaJSON sql.NullString
        if err := rows.Scan(
            &f.ID, &scanID, &f.ProviderName, &encrypted, &f.KeyMasked,
            &f.Confidence, &f.SourcePath, &f.SourceType, &f.LineNumber,
            &verifiedInt, &f.VerifyStatus, &f.VerifyHTTPCode, &metaJSON,
            &createdAt,
        ); err != nil {
            return f, fmt.Errorf("scanning finding row: %w", err)
        }
        return hydrateFinding(f, encrypted, scanID, verifiedInt, metaJSON, createdAt, encKey)
    }

    func scanFindingRowFromRow(row *sql.Row, encKey []byte) (Finding, error) {
        var f Finding
        var encrypted []byte
        var createdAt string
        var scanID sql.NullInt64
        var verifiedInt int
        var metaJSON sql.NullString
        if err := row.Scan(
            &f.ID, &scanID, &f.ProviderName, &encrypted, &f.KeyMasked,
            &f.Confidence, &f.SourcePath, &f.SourceType, &f.LineNumber,
            &verifiedInt, &f.VerifyStatus, &f.VerifyHTTPCode, &metaJSON,
            &createdAt,
        ); err != nil {
            return f, err // includes sql.ErrNoRows — let caller detect
        }
        return hydrateFinding(f, encrypted, scanID, verifiedInt, metaJSON, createdAt, encKey)
    }

    func hydrateFinding(f Finding, encrypted []byte, scanID sql.NullInt64, verifiedInt int, metaJSON sql.NullString, createdAt string, encKey []byte) (Finding, error) {
        if scanID.Valid {
            f.ScanID = scanID.Int64
        }
        f.Verified = verifiedInt != 0
        if metaJSON.Valid && metaJSON.String != "" {
            m := map[string]string{}
            if err := json.Unmarshal([]byte(metaJSON.String), &m); err != nil {
                return f, fmt.Errorf("unmarshaling verify metadata for finding %d: %w", f.ID, err)
            }
            f.VerifyMetadata = m
        }
        plain, err := Decrypt(encrypted, encKey)
        if err != nil {
            return f, fmt.Errorf("decrypting finding %d: %w", f.ID, err)
        }
        f.KeyValue = string(plain)
        f.CreatedAt, _ = time.Parse("2006-01-02 15:04:05", createdAt)
        return f, nil
    }
    ```

    Create pkg/storage/queries_test.go with the seven tests from <behavior>. Use `storage.Open(":memory:")`, generate salt, DeriveKey, SaveFinding seed rows with distinct providers/verified flags, then exercise each query. Use testify assertions.
  </action>
  <verify>
    <automated>cd /home/salva/Documents/apikey && go test ./pkg/storage/... -run "TestListFindingsFiltered|TestGetFinding|TestDeleteFinding" -count=1</automated>
  </verify>
  <done>
    - All seven query tests pass
    - `grep -q "ListFindingsFiltered\|GetFinding\|DeleteFinding" pkg/storage/queries.go`
    - `go build ./...` succeeds
    - Existing `pkg/storage/...` tests still pass (no regressions in ListFindings)
  </done>
</task>

</tasks>

<verification>
- `go test ./pkg/storage/... -count=1` all green
- `grep -q "type Filters struct" pkg/storage/queries.go`
</verification>

<success_criteria>
- Filters struct supports provider, verified, pagination
- GetFinding returns sql.ErrNoRows on miss
- DeleteFinding returns rows affected
- All tests green with in-memory DB
</success_criteria>

<output>
After completion, create `.planning/phases/06-output-reporting/06-04-SUMMARY.md`.
</output>