Blame · Line-by-line history
0034_flywheel_learning.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.
| b2ff5c7 | 1 | -- Flywheel / Learning System tables |
| 2 | -- Tracks AI review outcomes, extracts patterns, and aggregates gate metrics | |
| 3 | -- so every future review gets smarter based on historical data. | |
| 4 | ||
| 5 | CREATE TABLE IF NOT EXISTS "review_outcomes" ( | |
| 6 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 7 | "repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE, | |
| 8 | "pull_request_id" uuid NOT NULL REFERENCES "pull_requests"("id") ON DELETE CASCADE, | |
| 9 | "comment_id" uuid NOT NULL REFERENCES "pr_comments"("id") ON DELETE CASCADE, | |
| 10 | "outcome" text NOT NULL, | |
| 11 | "category" text NOT NULL, | |
| 12 | "file_path" text, | |
| 13 | "language" text, | |
| 14 | "was_useful" boolean, | |
| 15 | "created_at" timestamp DEFAULT now() NOT NULL | |
| 16 | ); | |
| 17 | --> statement-breakpoint | |
| 18 | CREATE INDEX IF NOT EXISTS "review_outcomes_repo" ON "review_outcomes" ("repository_id"); | |
| 19 | --> statement-breakpoint | |
| 20 | CREATE INDEX IF NOT EXISTS "review_outcomes_category" ON "review_outcomes" ("category", "outcome"); | |
| 21 | --> statement-breakpoint | |
| 22 | ||
| 23 | CREATE TABLE IF NOT EXISTS "review_patterns" ( | |
| 24 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 25 | "repository_id" uuid REFERENCES "repositories"("id") ON DELETE CASCADE, | |
| 26 | "scope" text NOT NULL, | |
| 27 | "language" text, | |
| 28 | "category" text NOT NULL, | |
| 29 | "pattern" text NOT NULL, | |
| 30 | "confidence" integer NOT NULL DEFAULT 50, | |
| 31 | "evidence_count" integer NOT NULL DEFAULT 1, | |
| 32 | "last_seen_at" timestamp DEFAULT now() NOT NULL, | |
| 33 | "created_at" timestamp DEFAULT now() NOT NULL, | |
| 34 | "active" boolean DEFAULT true NOT NULL | |
| 35 | ); | |
| 36 | --> statement-breakpoint | |
| 37 | CREATE INDEX IF NOT EXISTS "review_patterns_scope" ON "review_patterns" ("scope", "active"); | |
| 38 | --> statement-breakpoint | |
| 39 | CREATE INDEX IF NOT EXISTS "review_patterns_repo" ON "review_patterns" ("repository_id", "active"); | |
| 40 | --> statement-breakpoint | |
| 41 | CREATE INDEX IF NOT EXISTS "review_patterns_lang" ON "review_patterns" ("language", "active"); | |
| 42 | --> statement-breakpoint | |
| 43 | ||
| 44 | CREATE TABLE IF NOT EXISTS "gate_metrics" ( | |
| 45 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL, | |
| 46 | "repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE, | |
| 47 | "gate_name" text NOT NULL, | |
| 48 | "period" text NOT NULL, | |
| 49 | "total_runs" integer NOT NULL DEFAULT 0, | |
| 50 | "passed" integer NOT NULL DEFAULT 0, | |
| 51 | "failed" integer NOT NULL DEFAULT 0, | |
| 52 | "repaired" integer NOT NULL DEFAULT 0, | |
| 53 | "skipped" integer NOT NULL DEFAULT 0, | |
| 54 | "avg_duration_ms" integer, | |
| 55 | "false_positives" integer NOT NULL DEFAULT 0, | |
| 56 | "updated_at" timestamp DEFAULT now() NOT NULL | |
| 57 | ); | |
| 58 | --> statement-breakpoint | |
| 59 | CREATE UNIQUE INDEX IF NOT EXISTS "gate_metrics_repo_gate_period" ON "gate_metrics" ("repository_id", "gate_name", "period"); |