Blame · Line-by-line history
0038_competitive_intel.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.
| 5c83ccc | 1 | -- Competitive Intelligence Engine — weekly competitor monitoring + gap analysis. |
| 2 | -- | |
| 3 | -- competitor_reports one row per (competitor, week). Stores raw changelog | |
| 4 | -- content, Claude-extracted features shipped, gaps vs | |
| 5 | -- Gluecron, and a short summary. | |
| 6 | -- intel_scan_runs audit log of every scan job invocation — useful for | |
| 7 | -- showing "last scanned at" in the admin UI. | |
| 8 | -- | |
| 9 | -- `CREATE TABLE IF NOT EXISTS` + `CREATE INDEX IF NOT EXISTS` throughout so | |
| 10 | -- reruns are idempotent. | |
| 11 | ||
| 12 | --> statement-breakpoint | |
| 13 | CREATE TABLE IF NOT EXISTS "competitor_reports" ( | |
| 14 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(), | |
| 15 | "competitor" text NOT NULL, | |
| 16 | "report_date" date NOT NULL, | |
| 17 | "raw_content" text NOT NULL, | |
| 18 | "features_shipped" jsonb NOT NULL DEFAULT '[]', | |
| 19 | "gaps_identified" jsonb NOT NULL DEFAULT '[]', | |
| 20 | "summary" text NOT NULL DEFAULT '', | |
| 21 | "model_used" text NOT NULL DEFAULT 'claude-sonnet-4-6', | |
| 22 | "created_at" timestamptz NOT NULL DEFAULT now() | |
| 23 | ); | |
| 24 | ||
| 25 | --> statement-breakpoint | |
| 26 | CREATE INDEX IF NOT EXISTS "competitor_reports_competitor" | |
| 27 | ON "competitor_reports"("competitor"); | |
| 28 | ||
| 29 | --> statement-breakpoint | |
| 30 | CREATE INDEX IF NOT EXISTS "competitor_reports_date" | |
| 31 | ON "competitor_reports"("report_date" DESC); | |
| 32 | ||
| 33 | --> statement-breakpoint | |
| 34 | CREATE UNIQUE INDEX IF NOT EXISTS "competitor_reports_unique" | |
| 35 | ON "competitor_reports"("competitor", "report_date"); | |
| 36 | ||
| 37 | --> statement-breakpoint | |
| 38 | CREATE TABLE IF NOT EXISTS "intel_scan_runs" ( | |
| 39 | "id" uuid PRIMARY KEY DEFAULT gen_random_uuid(), | |
| 40 | "started_at" timestamptz NOT NULL DEFAULT now(), | |
| 41 | "completed_at" timestamptz, | |
| 42 | "status" text NOT NULL DEFAULT 'running', | |
| 43 | "competitors_scanned" integer NOT NULL DEFAULT 0, | |
| 44 | "error" text | |
| 45 | ); |