Blame · Line-by-line history
0038_ai_flywheel_and_integrations.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.
| 40e7738 | 1 | -- AI flywheel + third-party integrations registry. |
| 2 | -- | |
| 3 | -- Two strictly-additive tables: | |
| 4 | -- | |
| 5 | -- ai_activity Telemetry for every AI invocation in the platform. | |
| 6 | -- Powers the live dashboard at /ai/live, the per-repo | |
| 7 | -- "AI in action" panel, and future learning loops | |
| 8 | -- (cost tracking, prompt regression, repair patterns). | |
| 9 | -- Insert-only; trimmed by retention policy at app level. | |
| 10 | -- | |
| 11 | -- integrations Third-party product connectors (Slack / Linear / | |
| 12 | -- Vercel / Discord / Jira / PagerDuty / Sentry / | |
| 13 | -- Datadog / Figma / Cursor). Repo-scoped for v1. | |
| 14 | -- `config` is a JSON blob with the connector-specific | |
| 15 | -- fields (webhook URL, channel, project key, etc). | |
| 16 | -- Secret components live in dedicated columns so we | |
| 17 | -- can rotate / redact without parsing JSON. | |
| 18 | ||
| 19 | CREATE TABLE IF NOT EXISTS "ai_activity" ( | |
| 20 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(), | |
| 21 | "action_type" text NOT NULL, -- 'review' | 'repair' | 'completion' | 'incident' | 'triage' | 'explain' | 'test' | 'changelog' | 'chat' | 'spec' | ... | |
| 22 | "model" text NOT NULL, -- model id at invocation time | |
| 23 | "repository_id" uuid REFERENCES "repositories"("id") ON DELETE CASCADE, | |
| 24 | "user_id" uuid REFERENCES "users"("id") ON DELETE SET NULL, | |
| 25 | "pull_request_id" uuid REFERENCES "pull_requests"("id") ON DELETE SET NULL, | |
| 26 | "issue_id" uuid REFERENCES "issues"("id") ON DELETE SET NULL, | |
| 27 | "commit_sha" text, -- optional anchor for repair / review | |
| 28 | "summary" text NOT NULL, -- short human-readable line | |
| 29 | "input_tokens" integer, -- nullable when not reported by SDK | |
| 30 | "output_tokens" integer, | |
| 31 | "latency_ms" integer NOT NULL, | |
| 32 | "success" boolean NOT NULL DEFAULT true, | |
| 33 | "error" text, -- redacted message when success=false | |
| 34 | "metadata" jsonb, -- free-form (file paths, repair counts, etc.) | |
| 35 | "created_at" timestamp NOT NULL DEFAULT now() | |
| 36 | ); | |
| 37 | ||
| 38 | CREATE INDEX IF NOT EXISTS "ai_activity_created_idx" | |
| 39 | ON "ai_activity" ("created_at" DESC); | |
| 40 | ||
| 41 | CREATE INDEX IF NOT EXISTS "ai_activity_repo_idx" | |
| 42 | ON "ai_activity" ("repository_id", "created_at" DESC); | |
| 43 | ||
| 44 | CREATE INDEX IF NOT EXISTS "ai_activity_user_idx" | |
| 45 | ON "ai_activity" ("user_id", "created_at" DESC); | |
| 46 | ||
| 47 | CREATE INDEX IF NOT EXISTS "ai_activity_action_idx" | |
| 48 | ON "ai_activity" ("action_type", "created_at" DESC); | |
| 49 | ||
| 50 | ||
| 51 | CREATE TABLE IF NOT EXISTS "integrations" ( | |
| 52 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(), | |
| 53 | "repository_id" uuid NOT NULL REFERENCES "repositories"("id") ON DELETE CASCADE, | |
| 54 | "kind" text NOT NULL, -- 'slack' | 'linear' | 'vercel' | 'discord' | 'jira' | 'pagerduty' | 'sentry' | 'datadog' | 'figma' | 'cursor' | 'generic_webhook' | |
| 55 | "name" text NOT NULL, -- user label, e.g. "Eng channel" | |
| 56 | "enabled" boolean NOT NULL DEFAULT true, | |
| 57 | "config" jsonb NOT NULL DEFAULT '{}'::jsonb, | |
| 58 | "events" jsonb NOT NULL DEFAULT '[]'::jsonb, -- array of event kinds to forward | |
| 59 | "created_by" uuid REFERENCES "users"("id") ON DELETE SET NULL, | |
| 60 | "created_at" timestamp NOT NULL DEFAULT now(), | |
| 61 | "updated_at" timestamp NOT NULL DEFAULT now(), | |
| 62 | "last_delivery_at" timestamp, | |
| 63 | "last_status" text -- 'ok' | 'fail' | NULL (never delivered) | |
| 64 | ); | |
| 65 | ||
| 66 | CREATE INDEX IF NOT EXISTS "integrations_repo_idx" | |
| 67 | ON "integrations" ("repository_id"); | |
| 68 | ||
| 69 | CREATE INDEX IF NOT EXISTS "integrations_kind_idx" | |
| 70 | ON "integrations" ("kind"); | |
| 71 | ||
| 72 | CREATE UNIQUE INDEX IF NOT EXISTS "integrations_repo_name_unique" | |
| 73 | ON "integrations" ("repository_id", "name"); | |
| 74 | ||
| 75 | ||
| 76 | CREATE TABLE IF NOT EXISTS "integration_deliveries" ( | |
| 77 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(), | |
| 78 | "integration_id" uuid NOT NULL REFERENCES "integrations"("id") ON DELETE CASCADE, | |
| 79 | "event" text NOT NULL, | |
| 80 | "status" text NOT NULL, -- 'ok' | 'fail' | 'skipped' | |
| 81 | "http_status" integer, | |
| 82 | "error" text, | |
| 83 | "duration_ms" integer NOT NULL DEFAULT 0, | |
| 84 | "created_at" timestamp NOT NULL DEFAULT now() | |
| 85 | ); | |
| 86 | ||
| 87 | CREATE INDEX IF NOT EXISTS "integration_deliveries_integration_idx" | |
| 88 | ON "integration_deliveries" ("integration_id", "created_at" DESC); |