Migration v5: reviews, findings, finding_events tables

[ajianaz]

Problem

Cora Code stores review/scan results as:

• .cora/history/*.json — DebtSnapshot only (aggregate counts, no per-finding detail)
• CLI stdout (--format json) — full ReviewResponse/ScanResponse, but never persisted
• cora scan — no history at all (zero calls to save_snapshot)

This means there is no persistent, queryable record of individual findings. No way to track status (solved/false_positive/dismissed), no dedup, no trend per-file.

Solution

Add 3 new tables to cora.db via migration v5.

Schema

-- Header: one row per review/scan run
CREATE TABLE reviews (
    id              TEXT PRIMARY KEY,       -- UUID v7 (time-sortable)
    project_id      INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    command         TEXT NOT NULL CHECK(command IN (review,scan)),
    commit          TEXT,                   -- git short hash
    branch          TEXT,
    reviewed_at     TEXT NOT NULL DEFAULT (datetime(now)),
    quality_score   REAL,
    gate_status     TEXT,                   -- passed, failed, disabled
    files_scanned   INTEGER NOT NULL DEFAULT 0,
    lines_scanned   INTEGER NOT NULL DEFAULT 0,
    provider        TEXT,                   -- zai, openai, anthropic
    model           TEXT,                   -- glm-5-turbo, gpt-4o, etc
    input_tokens    INTEGER DEFAULT 0,
    output_tokens   INTEGER DEFAULT 0,
    cost_usd        REAL DEFAULT 0.0,
    duration_ms     INTEGER,
    summary         TEXT                    -- LLM-generated summary
);
CREATE INDEX idx_reviews_project ON reviews(project_id);
CREATE INDEX idx_reviews_command ON reviews(command);
CREATE INDEX idx_reviews_at ON reviews(reviewed_at);

-- Per-finding detail
CREATE TABLE findings (
    id              TEXT PRIMARY KEY,       -- UUID
    review_id       TEXT NOT NULL REFERENCES reviews(id) ON DELETE CASCADE,
    file            TEXT NOT NULL,
    line            INTEGER,                -- NULL = file-level finding
    end_line        INTEGER,                -- NULL = single-line
    severity        TEXT NOT NULL CHECK(severity IN (critical,major,minor,info)),
    category        TEXT,                   -- security, bug_risk, performance, best_practice, style, suggestion
    title           TEXT NOT NULL,
    body            TEXT,
    suggested_fix   TEXT,
    status          TEXT NOT NULL DEFAULT open
                     CHECK(status IN (open,solved,false_positive,wontfix,dismissed)),
    fingerprint     TEXT,                   -- hash(file + line + title_normalized) for cross-review dedup
    created_at      TEXT NOT NULL DEFAULT (datetime(now))
);
CREATE INDEX idx_findings_review   ON findings(review_id);
CREATE INDEX idx_findings_file     ON findings(file);
CREATE INDEX idx_findings_status   ON findings(status);
CREATE INDEX idx_findings_severity ON findings(severity);
CREATE INDEX idx_findings_fp       ON findings(fingerprint);

-- Audit trail for status changes
CREATE TABLE finding_events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    finding_id      TEXT NOT NULL REFERENCES findings(id) ON DELETE CASCADE,
    event           TEXT NOT NULL CHECK(event IN (
        opened,auto_resolved,manually_resolved,
        dismissed_fp,dismissed_wontfix,reopened
    )),
    commit          TEXT,
    reason          TEXT,
    created_at      TEXT NOT NULL DEFAULT (datetime(now))
);
CREATE INDEX idx_fe_finding ON finding_events(finding_id);

Design Decisions

Decision	Rationale
UUID v7 for reviews.id	Time-sortable, no auto-increment collision across offline runs
fingerprint field	Enables cross-review dedup without LLM — same code pattern = same finding
Separate finding_events	Full audit trail: who changed status, when, why, on which commit
end_line on findings	Tree-sitter can provide exact node ranges (future: #358)
summary on reviews	Store LLM summary for historical context without re-running
Keep .cora/history/*.json	Backward compat — cora debt CLI still reads these files

Dependencies

• Depends on: Rename graph.db → cora.db with auto-migrate #395 (cora.db rename — schema lives in renamed DB)
• Blocks: False positives: SVG xmlns, form variable names, and duplicate rule engines #357 (review save to DB), feat(cli): add cora trace and cora arch subcommands #358 (scan save to DB)

Acceptance Criteria

• migrate_v5() runs cleanly on fresh and existing databases
• All 3 tables created with correct constraints and indexes
• Existing v1-v4 tables untouched
• schema_version incremented to 5
• Idempotent — running migration twice is safe

[ajianaz]

Depends on: #395 (cora.db rename)\nBlocks: #397, #398, #399, #400

[ajianaz]

Resolved by PR #421. Migration v5 schema (reviews, findings, finding_events) implemented in schema.rs.