Blame · Line-by-line history

`0039_repair_flywheel.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0039_repair_flywheel.sqlBlame66 lines · 1 contributor

Normal view Raw

e6bad81Claude	1	-- Repair Flywheel
	2	--
	3	-- Every auto-repair attempt — mechanical, AI-driven, or cache-hit — gets
	4	-- recorded here with its failure signature, the patch produced, and the
	5	-- eventual outcome. Future failures with the same signature can short-
	6	-- circuit straight to the cached patch (Tier 0), saving AI cost + latency.
	7	--
	8	-- After ~5000 entries the flywheel dominates: most CI failures hit a
	9	-- cached pattern and get fixed in seconds.
	10
	11	CREATE TABLE IF NOT EXISTS "repair_flywheel" (
	12	"id" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	13	"repository_id" UUID REFERENCES "repositories"("id") ON DELETE CASCADE,
	14
	15	-- Fingerprint: SHA-256 of the normalised failure text (variables/paths
	16	-- stripped). Two failures with the same signature are considered the
	17	-- same problem.
	18	"failure_signature" TEXT NOT NULL,
	19
	20	-- Original failure text (capped to 4KB) — for human review at /admin/repair-flywheel.
	21	"failure_text" TEXT NOT NULL,
	22
	23	-- Mechanical classification, if any: 'lockfile' \| 'formatting' \| 'imports'
	24	-- \| NULL (then it was AI-driven or human-driven).
	25	"failure_classification" TEXT,
	26
	27	-- Which tier produced the fix.
	28	"repair_tier" TEXT NOT NULL,
	29
	30	-- Plain-English summary (always populated, max 400 chars).
	31	"patch_summary" TEXT NOT NULL,
	32
	33	-- File paths the repair touched.
	34	"files_changed" JSONB NOT NULL DEFAULT '[]'::jsonb,
	35
	36	-- Resulting commit SHA (NULL until the repair commits).
	37	"commit_sha" TEXT,
	38
	39	-- Outcome: 'pending' (just applied), 'success' (smoke passed),
	40	-- 'failed' (smoke failed), 'reverted' (later reverted by a human).
	41	"outcome" TEXT NOT NULL DEFAULT 'pending',
	42	"applied_at" TIMESTAMPTZ DEFAULT now() NOT NULL,
	43	"outcome_at" TIMESTAMPTZ,
	44
	45	-- Cache lineage: if this entry was applied from another flywheel pattern
	46	-- (Tier 0 hit), parent_pattern_id points at the original. Lets us count
	47	-- "this pattern was reused N times" for confidence weighting.
	48	"parent_pattern_id" UUID REFERENCES "repair_flywheel"("id") ON DELETE SET NULL,
	49	"cache_hit_count" INTEGER NOT NULL DEFAULT 0,
	50
	51	-- Privacy gate: only patterns marked public participate in cross-repo
	52	-- learning. Default off; site admins can flip per-pattern, or owners can
	53	-- bulk-opt-in via repo settings.
	54	"is_public_pattern" BOOLEAN NOT NULL DEFAULT false,
	55
	56	"created_at" TIMESTAMPTZ DEFAULT now() NOT NULL
	57	);
	58
	59	CREATE INDEX IF NOT EXISTS "repair_flywheel_signature_idx" ON "repair_flywheel"("failure_signature");
	60	CREATE INDEX IF NOT EXISTS "repair_flywheel_repo_idx" ON "repair_flywheel"("repository_id");
	61	CREATE INDEX IF NOT EXISTS "repair_flywheel_outcome_idx" ON "repair_flywheel"("outcome");
	62	CREATE INDEX IF NOT EXISTS "repair_flywheel_classification_idx" ON "repair_flywheel"("failure_classification");
	63	-- Composite for the cache lookup hot path: "find a successful repair for this
	64	-- repo + this exact failure signature."
	65	CREATE INDEX IF NOT EXISTS "repair_flywheel_lookup_idx"
	66	ON "repair_flywheel"("repository_id", "failure_signature", "outcome");