spb/datacenterindex

1-- 0003 — claim-first data layer, quality flags, campus containment, capacity ontology, run tracking, snapshots.2-- Idempotent (IF NOT EXISTS everywhere) so it can be re-applied on a database created before this file existed.34-- ─── claims ────────────────────────────────────────────────────────────────────────────────────────5CREATE TABLE IF NOT EXISTS "claims" (6  "id" text PRIMARY KEY,7  "subject_type" text NOT NULL,            -- facility | campus | project | operator | market | country8  "subject_id" text NOT NULL,9  "predicate" text NOT NULL,               -- it_capacity_mw | planned_power_mw | project_investment_usd | status | …10  "value" double precision,11  "value_text" text,12  "unit" text,                             -- MW | USD | …13  "scope" text NOT NULL DEFAULT 'unknown', -- building | facility | campus | metro | country | portfolio | company | unknown14  "scope_reason" text,15  "source_id" text NOT NULL,16  "connector_id" text NOT NULL,17  "document_id" text,18  "url" text NOT NULL,19  "published_at" text,                     -- partial date as published20  "retrieved_at" timestamp with time zone NOT NULL DEFAULT now(),21  "confidence" text NOT NULL DEFAULT 'moderate',22  "is_estimate" boolean NOT NULL DEFAULT false,23  "authority_tier" text NOT NULL DEFAULT 'D',24  "evidence_text" text,25  "evidence_start" integer,26  "evidence_end" integer,27  "parser_name" text,28  "parser_version" text,29  "run_id" text,30  "status" text NOT NULL DEFAULT 'current', -- current | superseded | rejected | review | unscoped31  "rejection_reason" text,32  "first_observed" timestamp with time zone NOT NULL DEFAULT now(),33  "last_observed" timestamp with time zone NOT NULL DEFAULT now(),34  "created_at" timestamp with time zone NOT NULL DEFAULT now()35);36CREATE UNIQUE INDEX IF NOT EXISTS "claims_uq" ON "claims" ("subject_type", "subject_id", "predicate", "source_id", "url", COALESCE("value", 0), COALESCE("value_text", ''));37CREATE INDEX IF NOT EXISTS "claims_subject_idx" ON "claims" ("subject_type", "subject_id", "predicate");38CREATE INDEX IF NOT EXISTS "claims_status_idx" ON "claims" ("status");39CREATE INDEX IF NOT EXISTS "claims_run_idx" ON "claims" ("run_id");40CREATE INDEX IF NOT EXISTS "claims_document_idx" ON "claims" ("document_id");4142-- ─── quality flags ─────────────────────────────────────────────────────────────────────────────────43CREATE TABLE IF NOT EXISTS "quality_flags" (44  "id" text PRIMARY KEY,45  "entity_type" text NOT NULL,46  "entity_id" text NOT NULL,47  "claim_id" text,48  "code" text NOT NULL,                    -- mw_single_site_gt_1000 | scope_company | inv_single_site_gt_50b | project_false_positive | duplicate | …49  "severity" text NOT NULL DEFAULT 'warn', -- info | warn | critical50  "field" text,51  "message" text NOT NULL,52  "details" jsonb,53  "priority" integer NOT NULL DEFAULT 0,   -- review priority (impact-weighted)54  "status" text NOT NULL DEFAULT 'open',   -- open | resolved | dismissed55  "resolution" text,56  "resolved_by" text,57  "resolved_at" timestamp with time zone,58  "run_id" text,59  "dedupe_key" text NOT NULL,60  "created_at" timestamp with time zone NOT NULL DEFAULT now(),61  "updated_at" timestamp with time zone NOT NULL DEFAULT now()62);63CREATE UNIQUE INDEX IF NOT EXISTS "quality_flags_dedupe_uq" ON "quality_flags" ("dedupe_key");64CREATE INDEX IF NOT EXISTS "quality_flags_entity_idx" ON "quality_flags" ("entity_type", "entity_id");65CREATE INDEX IF NOT EXISTS "quality_flags_open_idx" ON "quality_flags" ("status", "priority" DESC) WHERE "status" = 'open';66CREATE INDEX IF NOT EXISTS "quality_flags_code_idx" ON "quality_flags" ("code");6768-- ─── facilities: containment, capacity ontology, AI evidence ───────────────────────────────────────69ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "parent_facility_id" text REFERENCES "facilities"("id");70ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "record_scope" text NOT NULL DEFAULT 'facility'; -- building | facility | campus71ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "ai_evidence" text NOT NULL DEFAULT 'unknown';   -- confirmed | likely | associated | unknown72ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "utility_capacity_mw" double precision;73ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "grid_connection_mw" double precision;74ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "ultimate_campus_mw" double precision;75ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "capacity_scope" text;                            -- scope of the displayed MW figure76ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "capacity_semantics" text;                        -- predicate behind the displayed MW figure77ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "developer_id" text REFERENCES "operators"("id");78ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "landowner_id" text REFERENCES "operators"("id");79ALTER TABLE "facilities" ADD COLUMN IF NOT EXISTS "review_priority" integer NOT NULL DEFAULT 0;80CREATE INDEX IF NOT EXISTS "facilities_parent_idx" ON "facilities" ("parent_facility_id");81CREATE INDEX IF NOT EXISTS "facilities_campus_idx" ON "facilities" ("campus_id");82CREATE INDEX IF NOT EXISTS "facilities_merged_idx" ON "facilities" ("merged_into") WHERE "merged_into" IS NOT NULL;83CREATE INDEX IF NOT EXISTS "facilities_owner_idx" ON "facilities" ("owner_id");84CREATE INDEX IF NOT EXISTS "facilities_ai_idx" ON "facilities" ("ai_evidence") WHERE "ai_evidence" <> 'unknown';85CREATE INDEX IF NOT EXISTS "facilities_opened_idx" ON "facilities" ("opened_on") WHERE "opened_on" IS NOT NULL;8687-- ─── projects: classification, evidence, lifecycle, geocoding, scopes ─────────────────────────────88ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "project_class" text;                -- NEW_BUILD | EXPANSION | … | UNKNOWN89ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "evidence_level" text;               -- strong | weak | none90ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "merged_into" text;91ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "ai_evidence" text NOT NULL DEFAULT 'unknown';92ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "capacity_scope" text;93ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "capacity_semantics" text;94ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "investment_scope" text;95ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "investment_semantics" text;96ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "developer_id" text REFERENCES "operators"("id");97ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "tenant_id" text REFERENCES "operators"("id");98ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "campus_id" text REFERENCES "campuses"("id");99ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "construction_started_on" text;100ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "approved_on" text;101ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "permit_filed_on" text;102ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "opened_on" text;103ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "review_priority" integer NOT NULL DEFAULT 0;104ALTER TABLE "projects" ADD COLUMN IF NOT EXISTS "hidden" boolean NOT NULL DEFAULT false;          -- false positives kept for audit, never listed105CREATE INDEX IF NOT EXISTS "projects_class_idx" ON "projects" ("project_class");106CREATE INDEX IF NOT EXISTS "projects_merged_idx" ON "projects" ("merged_into") WHERE "merged_into" IS NOT NULL;107CREATE INDEX IF NOT EXISTS "projects_live_idx" ON "projects" ("status", "country_iso2") WHERE "merged_into" IS NULL AND NOT "hidden";108CREATE INDEX IF NOT EXISTS "projects_latlng_idx" ON "projects" ("lat", "lng") WHERE "lat" IS NOT NULL;109CREATE INDEX IF NOT EXISTS "projects_metro_idx" ON "projects" ("metro_id");110111-- ─── provenance / versions: run tracking + scope ──────────────────────────────────────────────────112ALTER TABLE "provenance" ADD COLUMN IF NOT EXISTS "run_id" text;113ALTER TABLE "provenance" ADD COLUMN IF NOT EXISTS "scope" text;114ALTER TABLE "provenance" ADD COLUMN IF NOT EXISTS "is_winner" boolean NOT NULL DEFAULT false; -- the observation backing the displayed column value115CREATE INDEX IF NOT EXISTS "provenance_current_idx" ON "provenance" ("entity_type", "entity_id", "field") WHERE "is_current";116CREATE INDEX IF NOT EXISTS "provenance_run_idx" ON "provenance" ("run_id");117ALTER TABLE "document_versions" ADD COLUMN IF NOT EXISTS "run_id" text;118ALTER TABLE "document_versions" ADD COLUMN IF NOT EXISTS "extractor_version" text;119CREATE INDEX IF NOT EXISTS "document_versions_run_idx" ON "document_versions" ("run_id");120ALTER TABLE "events" ADD COLUMN IF NOT EXISTS "run_id" text;121ALTER TABLE "events" ADD COLUMN IF NOT EXISTS "cluster_id" text;          -- same underlying announcement across outlets122ALTER TABLE "events" ADD COLUMN IF NOT EXISTS "evidence_count" integer NOT NULL DEFAULT 1;123ALTER TABLE "events" ADD COLUMN IF NOT EXISTS "is_ai" boolean NOT NULL DEFAULT false;124ALTER TABLE "events" ADD COLUMN IF NOT EXISTS "source_kind" text;125CREATE INDEX IF NOT EXISTS "events_significance_idx" ON "events" ("significance" DESC, "detected_at" DESC);126CREATE INDEX IF NOT EXISTS "events_project_idx" ON "events" ("project_id");127CREATE INDEX IF NOT EXISTS "events_cluster_idx" ON "events" ("cluster_id");128CREATE INDEX IF NOT EXISTS "events_run_idx" ON "events" ("run_id");129CREATE INDEX IF NOT EXISTS "events_metro_idx" ON "events" ("metro_id");130131-- ─── news items ────────────────────────────────────────────────────────────────────────────────────132ALTER TABLE "news_items" ADD COLUMN IF NOT EXISTS "project_class" text;133ALTER TABLE "news_items" ADD COLUMN IF NOT EXISTS "cluster_id" text;134ALTER TABLE "news_items" ADD COLUMN IF NOT EXISTS "metro_id" text;135CREATE INDEX IF NOT EXISTS "news_items_operator_gin" ON "news_items" USING gin ("operator_ids");136CREATE INDEX IF NOT EXISTS "news_items_cluster_idx" ON "news_items" ("cluster_id");137138-- ─── connectors: quarantine + health ───────────────────────────────────────────────────────────────139ALTER TABLE "connectors" ADD COLUMN IF NOT EXISTS "quarantine" boolean NOT NULL DEFAULT false;140ALTER TABLE "connectors" ADD COLUMN IF NOT EXISTS "consecutive_failures" integer NOT NULL DEFAULT 0;141ALTER TABLE "connectors" ADD COLUMN IF NOT EXISTS "blocked_since" timestamp with time zone;142ALTER TABLE "connectors" ADD COLUMN IF NOT EXISTS "last_discovered" integer;143ALTER TABLE "connectors" ADD COLUMN IF NOT EXISTS "priority_score" real;144ALTER TABLE "connector_runs" ADD COLUMN IF NOT EXISTS "quarantined" boolean NOT NULL DEFAULT false;145146-- ─── sources: licensing ────────────────────────────────────────────────────────────────────────────147ALTER TABLE "sources" ADD COLUMN IF NOT EXISTS "redistribution" text;      -- allowed | attribution | restricted | unknown148ALTER TABLE "sources" ADD COLUMN IF NOT EXISTS "attribution_required" boolean;149150-- ─── entity keys: scope the primary key by entity type ─────────────────────────────────────────────151DO $$152BEGIN153  IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'entity_keys_pkey' AND conrelid = 'entity_keys'::regclass154             AND array_length(conkey, 1) = 1) THEN155    ALTER TABLE "entity_keys" DROP CONSTRAINT "entity_keys_pkey";156    ALTER TABLE "entity_keys" ADD PRIMARY KEY ("key", "entity_type");157  END IF;158END $$;159160-- ─── daily snapshots for "as of" views and regression checks ───────────────────────────────────────161CREATE TABLE IF NOT EXISTS "entity_snapshots" (162  "day" date NOT NULL,163  "kind" text NOT NULL,        -- global_totals | ranking | facility_status | project_stage | operator_totals | country_totals164  "key" text NOT NULL,165  "payload" jsonb NOT NULL,166  "created_at" timestamp with time zone NOT NULL DEFAULT now(),167  CONSTRAINT "entity_snapshots_pk" PRIMARY KEY ("day", "kind", "key")168);169CREATE INDEX IF NOT EXISTS "entity_snapshots_kind_idx" ON "entity_snapshots" ("kind", "key", "day");170171-- ─── watchlists (private, cookie-scoped) ───────────────────────────────────────────────────────────172CREATE TABLE IF NOT EXISTS "watchlists" (173  "id" text PRIMARY KEY,174  "owner_token" text NOT NULL,175  "entity_type" text NOT NULL,176  "entity_id" text NOT NULL,177  "created_at" timestamp with time zone NOT NULL DEFAULT now()178);179CREATE UNIQUE INDEX IF NOT EXISTS "watchlists_uq" ON "watchlists" ("owner_token", "entity_type", "entity_id");180181-- ─── grid / power context (events already carry the type; this keeps market-level constraint notes) ─182CREATE TABLE IF NOT EXISTS "grid_constraints" (183  "id" text PRIMARY KEY,184  "metro_id" text REFERENCES "metros"("id"),185  "country_iso2" text REFERENCES "countries"("iso2"),186  "kind" text NOT NULL,         -- moratorium | grid_delay | capacity_restriction | load_cap | new_transmission | new_substation | regulation | large_load_queue187  "title" text NOT NULL,188  "summary" text,189  "effective_date" text,190  "source_id" text,191  "document_id" text,192  "url" text NOT NULL,193  "event_id" text,194  "confidence" text NOT NULL DEFAULT 'moderate',195  "created_at" timestamp with time zone NOT NULL DEFAULT now()196);197CREATE INDEX IF NOT EXISTS "grid_constraints_metro_idx" ON "grid_constraints" ("metro_id");198CREATE INDEX IF NOT EXISTS "grid_constraints_country_idx" ON "grid_constraints" ("country_iso2");199200-- ─── daily metrics time-series lookups ─────────────────────────────────────────────────────────────201CREATE INDEX IF NOT EXISTS "daily_metrics_series_idx" ON "daily_metrics" ("metric", "dim", "day");202CREATE INDEX IF NOT EXISTS "entity_matches_created_fac_idx" ON "entity_matches" ((candidate->>'createdFacilityId')) WHERE "status" = 'pending';203