design a recurring drift-review workflow from trusted snapshots and report compare

[eyeinthesky6]

Why it matters

A Product Hunt launch comment on 2026-07-22 asked for a weekly or recurring audit with a diff report so a team could see how index evidence changes over time.

That is a meaningful product-direction suggestion, but it should be framed in terms of IndexPilot's existing read-only evidence paths rather than as a generic background automation idea.

Existing owners and callers

• indexpilot compare already compares two review reports.
• docs/TEAM_PREVIEW.md already tells teams to use indexpilot compare to check what changed later.
• docs/USAGE.md owns the public compare contract.
• The public product boundary remains advisory and read-only: no automatic index creation, drop, or scheduling that mutates a database.
• Historical lifecycle modules in src/ are not, by themselves, the public feature contract for this issue.

Problem to solve

Today, a careful team can collect trusted evidence twice and compare reports, but there is no clear, documented workflow for a repeatable "review drift over time" loop around the same proposed index shape.

The gap may be one of packaging and evidence flow rather than core analysis logic.

Scope

• Define a read-only recurring-review workflow built from existing pieces: trusted collection, sanitized snapshot, proposal review, and indexpilot compare.
• Decide what the stable unit of comparison should be: exact proposal SQL, normalized proposal shape, candidate fingerprint, or another existing evidence key.
• Define the minimum diff receipt a team would need to answer questions such as:
  • did workload volume change materially?
  • did overlap findings change?
  • did the verdict move between existing_overlap, needs_workload, worth_benchmarking, or similar states?
  • did the workload collection window or reset boundary make the comparison invalid?
• Keep the design compatible with GitHub or CI workflows, but do not require a hosted service.

Acceptance criteria

• A documented design describes a repeatable recurring-review loop using the existing read-only building blocks.
• The design defines what counts as the "same" proposal across two observations.
• The diff output records invalid or non-comparable comparisons explicitly instead of inventing a trend.
• The workflow preserves the current privacy and safety boundary: no raw SQL disclosure, no silent background DDL, no production-latency claims.
• The design states what can stay documentation-only versus what would require a later CLI or workflow implementation issue.

Non-goals

• No automatic scheduler that talks directly to production databases.
• No hosted telemetry service.
• No mutation path, deploy decision, or drift score presented as proof by itself.

Validation

Start with documentation and design review. If implementation work follows later, validate it against the existing compare help, public onboarding tests, and unsafe-db-access guardrails.