keyhunter/.planning/phases/06-output-reporting/06-04-PLAN.md

phase, plan, type, wave, depends_on, files_modified, autonomous, requirements, must_haves

phase	plan	type	wave	depends_on	files_modified	autonomous	requirements	must_haves
06-output-reporting	04	execute	1		pkg/storage/queries.go pkg/storage/queries_test.go	true	KEYS-01 KEYS-02 KEYS-06	truths artifacts key_links 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 path provides exports pkg/storage/queries.go Filters, ListFindingsFiltered, GetFinding, DeleteFinding Filters ListFindingsFiltered GetFinding DeleteFinding from to via pattern pkg/storage/queries.go pkg/storage/findings.go Reuses encrypt/decrypt + Finding struct Decrypt(encrypted, encKey)

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:").

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

@.planning/phases/06-output-reporting/06-CONTEXT.md @pkg/storage/findings.go @pkg/storage/db.go 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. Task 1: Filters struct, ListFindingsFiltered, GetFinding, DeleteFinding pkg/storage/queries.go, pkg/storage/queries_test.go - 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) - 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. 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.

cd /home/salva/Documents/apikey && go test ./pkg/storage/... -run "TestListFindingsFiltered|TestGetFinding|TestDeleteFinding" -count=1 - 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) - `go test ./pkg/storage/... -count=1` all green - `grep -q "type Filters struct" pkg/storage/queries.go`

<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>

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