spb/datacenterindex
Public
HTML 53.9%
TypeScript 44.5%
JavaScript 0.6%
SQL 0.5%
1/**2 * PostgreSQL schema (source of truth for entities, provenance, events, connector state).3 * ClickHouse (see clickhouse.ts) holds crawl history, observations, page changes and time series.4 * Column naming: snake_case via drizzle `casing`.5 */6import { boolean, doublePrecision, index, integer, jsonb, pgTable, primaryKey, real, text, timestamp, uniqueIndex, date, bigint } from "drizzle-orm/pg-core";7import { sql } from "drizzle-orm";89const ts = (name: string) => timestamp(name, { withTimezone: true, mode: "string" });10const createdAt = () => ts("created_at").notNull().defaultNow();11const updatedAt = () => ts("updated_at").notNull().defaultNow();1213export const countries = pgTable("countries", {14 iso2: text("iso2").primaryKey(),15 iso3: text("iso3"),16 slug: text("slug").notNull().unique(),17 name: text("name").notNull(),18 region: text("region"),19 subregion: text("subregion"),20 population: bigint("population", { mode: "number" }),21 gdpUsd: doublePrecision("gdp_usd"),22 electricityTwh: doublePrecision("electricity_twh"),23 renewableShare: doublePrecision("renewable_share"),24 statsYear: integer("stats_year"),25 lat: doublePrecision("lat"),26 lng: doublePrecision("lng"),27 stats: jsonb("stats").$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),28 updatedAt: updatedAt(),29});3031export const metros = pgTable(32 "metros",33 {34 id: text("id").primaryKey(),35 slug: text("slug").notNull().unique(),36 name: text("name").notNull(),37 countryIso2: text("country_iso2").notNull().references(() => countries.iso2),38 regionName: text("region_name"),39 lat: doublePrecision("lat").notNull(),40 lng: doublePrecision("lng").notNull(),41 radiusKm: real("radius_km").notNull().default(60),42 aliases: text("aliases").array().notNull().default(sql`'{}'::text[]`),43 description: text("description"),44 stats: jsonb("stats").$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),45 createdAt: createdAt(),46 updatedAt: updatedAt(),47 },48 (t) => [index("metros_country_idx").on(t.countryIso2)],49);5051export const operators = pgTable(52 "operators",53 {54 id: text("id").primaryKey(),55 slug: text("slug").notNull().unique(),56 name: text("name").notNull(),57 normalizedName: text("normalized_name").notNull(),58 kind: text("kind"), // OperatorKind59 website: text("website"),60 hqCountryIso2: text("hq_country_iso2"),61 hqCity: text("hq_city"),62 parentId: text("parent_id"),63 description: text("description"),64 aliases: text("aliases").array().notNull().default(sql`'{}'::text[]`),65 externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),66 isCloudProvider: boolean("is_cloud_provider").notNull().default(false),67 isCarrier: boolean("is_carrier").notNull().default(false),68 stats: jsonb("stats").$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),69 createdAt: createdAt(),70 updatedAt: updatedAt(),71 },72 (t) => [index("operators_norm_idx").on(t.normalizedName), index("operators_kind_idx").on(t.kind)],73);7475export const campuses = pgTable("campuses", {76 id: text("id").primaryKey(),77 slug: text("slug").notNull().unique(),78 name: text("name").notNull(),79 operatorId: text("operator_id").references(() => operators.id),80 metroId: text("metro_id").references(() => metros.id),81 countryIso2: text("country_iso2"),82 city: text("city"),83 lat: doublePrecision("lat"),84 lng: doublePrecision("lng"),85 externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),86 createdAt: createdAt(),87 updatedAt: updatedAt(),88});8990export const facilities = pgTable(91 "facilities",92 {93 id: text("id").primaryKey(),94 slug: text("slug").notNull().unique(),95 name: text("name").notNull(),96 normalizedName: text("normalized_name").notNull(),97 operatorId: text("operator_id").references(() => operators.id),98 ownerId: text("owner_id").references(() => operators.id),99 campusId: text("campus_id").references(() => campuses.id),100 metroId: text("metro_id").references(() => metros.id),101 countryIso2: text("country_iso2").references(() => countries.iso2),102 city: text("city"),103 regionName: text("region_name"),104 address: text("address"),105 postalCode: text("postal_code"),106 lat: doublePrecision("lat"),107 lng: doublePrecision("lng"),108 geoPrecision: text("geo_precision").notNull().default("unknown"),109 geoSource: text("geo_source"),110 geohash: text("geohash"),111 status: text("status").notNull().default("unknown"),112 facilityType: text("facility_type").notNull().default("unknown"),113 tier: text("tier"),114 buildingSqm: doublePrecision("building_sqm"),115 siteAreaHa: doublePrecision("site_area_ha"),116 itCapacityMw: doublePrecision("it_capacity_mw"),117 totalPowerMw: doublePrecision("total_power_mw"),118 plannedPowerMw: doublePrecision("planned_power_mw"),119 mwIsEstimate: boolean("mw_is_estimate").notNull().default(false),120 rackCount: integer("rack_count"),121 pue: real("pue"),122 coolingType: text("cooling_type"),123 renewableClaim: text("renewable_claim"),124 openedOn: text("opened_on"), // partial date125 constructionStartedOn: text("construction_started_on"),126 announcedOn: text("announced_on"),127 website: text("website"),128 description: text("description"),129 isAi: boolean("is_ai").notNull().default(false),130 isHyperscale: boolean("is_hyperscale").notNull().default(false),131 certifications: text("certifications").array().notNull().default(sql`'{}'::text[]`),132 carriersCount: integer("carriers_count"),133 ixpCount: integer("ixp_count"),134 networksCount: integer("networks_count"),135 confidence: text("confidence").notNull().default("unverified"),136 completeness: integer("completeness").notNull().default(0),137 externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),138 sourceCount: integer("source_count").notNull().default(0),139 firstSeen: ts("first_seen").notNull().defaultNow(),140 lastVerified: ts("last_verified"),141 mergedInto: text("merged_into"),142 /** containment: a building row points at its campus row (aggregation never counts both) */143 parentFacilityId: text("parent_facility_id"),144 recordScope: text("record_scope").notNull().default("facility"), // building | facility | campus145 aiEvidence: text("ai_evidence").notNull().default("unknown"), // confirmed | likely | associated | unknown146 utilityCapacityMw: doublePrecision("utility_capacity_mw"),147 gridConnectionMw: doublePrecision("grid_connection_mw"),148 ultimateCampusMw: doublePrecision("ultimate_campus_mw"),149 capacityScope: text("capacity_scope"),150 capacitySemantics: text("capacity_semantics"),151 developerId: text("developer_id"),152 landownerId: text("landowner_id"),153 reviewPriority: integer("review_priority").notNull().default(0),154 createdAt: createdAt(),155 updatedAt: updatedAt(),156 },157 (t) => [158 index("facilities_country_idx").on(t.countryIso2),159 index("facilities_parent_idx").on(t.parentFacilityId),160 index("facilities_operator_idx").on(t.operatorId),161 index("facilities_metro_idx").on(t.metroId),162 index("facilities_status_idx").on(t.status),163 index("facilities_type_idx").on(t.facilityType),164 index("facilities_geohash_idx").on(t.geohash),165 index("facilities_norm_idx").on(t.normalizedName),166 index("facilities_updated_idx").on(t.updatedAt),167 ],168);169170export const facilityAliases = pgTable(171 "facility_aliases",172 {173 facilityId: text("facility_id").notNull().references(() => facilities.id, { onDelete: "cascade" }),174 alias: text("alias").notNull(),175 normalized: text("normalized").notNull(),176 sourceId: text("source_id"),177 },178 (t) => [primaryKey({ columns: [t.facilityId, t.alias] }), index("facility_aliases_norm_idx").on(t.normalized)],179);180181/** connector-scoped key → facility id (idempotent re-ingestion, e.g. "peeringdb:fac:1"). */182export const entityKeys = pgTable(183 "entity_keys",184 {185 key: text("key").notNull(),186 entityType: text("entity_type").notNull(),187 entityId: text("entity_id").notNull(),188 connectorId: text("connector_id").notNull(),189 createdAt: createdAt(),190 },191 (t) => [primaryKey({ columns: [t.key, t.entityType] }), index("entity_keys_entity_idx").on(t.entityType, t.entityId)],192);193194export const cloudRegions = pgTable(195 "cloud_regions",196 {197 id: text("id").primaryKey(),198 slug: text("slug").notNull().unique(),199 providerId: text("provider_id").notNull().references(() => operators.id),200 code: text("code").notNull(),201 name: text("name").notNull(),202 city: text("city"),203 regionName: text("region_name"),204 countryIso2: text("country_iso2").references(() => countries.iso2),205 metroId: text("metro_id").references(() => metros.id),206 lat: doublePrecision("lat"),207 lng: doublePrecision("lng"),208 geoPrecision: text("geo_precision").notNull().default("city"),209 availabilityZones: integer("availability_zones"),210 launchedOn: text("launched_on"),211 status: text("status").notNull().default("operational"),212 isSovereign: boolean("is_sovereign").notNull().default(false),213 sourceUrl: text("source_url"),214 externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),215 createdAt: createdAt(),216 updatedAt: updatedAt(),217 },218 (t) => [uniqueIndex("cloud_regions_provider_code_uq").on(t.providerId, t.code), index("cloud_regions_country_idx").on(t.countryIso2)],219);220221export const ixps = pgTable("ixps", {222 id: text("id").primaryKey(),223 slug: text("slug").notNull().unique(),224 name: text("name").notNull(),225 nameLong: text("name_long"),226 city: text("city"),227 countryIso2: text("country_iso2").references(() => countries.iso2),228 metroId: text("metro_id").references(() => metros.id),229 regionContinent: text("region_continent"),230 website: text("website"),231 networkCount: integer("network_count"),232 externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),233 createdAt: createdAt(),234 updatedAt: updatedAt(),235});236237export const facilityIxps = pgTable(238 "facility_ixps",239 {240 facilityId: text("facility_id").notNull().references(() => facilities.id, { onDelete: "cascade" }),241 ixpId: text("ixp_id").notNull().references(() => ixps.id, { onDelete: "cascade" }),242 sourceId: text("source_id"),243 },244 (t) => [primaryKey({ columns: [t.facilityId, t.ixpId] })],245);246247/** Carriers / networks / cloud providers present at a facility (operators with kind carrier|cloud|hyperscaler). */248export const facilityTenants = pgTable(249 "facility_tenants",250 {251 facilityId: text("facility_id").notNull().references(() => facilities.id, { onDelete: "cascade" }),252 operatorId: text("operator_id").notNull().references(() => operators.id, { onDelete: "cascade" }),253 role: text("role").notNull().default("carrier"), // carrier | cloud | network | tenant254 asn: integer("asn"),255 sourceId: text("source_id"),256 },257 (t) => [primaryKey({ columns: [t.facilityId, t.operatorId, t.role] }), index("facility_tenants_operator_idx").on(t.operatorId)],258);259260export const projects = pgTable(261 "projects",262 {263 id: text("id").primaryKey(),264 slug: text("slug").notNull().unique(),265 name: text("name").notNull(),266 normalizedName: text("normalized_name").notNull(),267 operatorId: text("operator_id").references(() => operators.id),268 facilityId: text("facility_id").references(() => facilities.id),269 metroId: text("metro_id").references(() => metros.id),270 countryIso2: text("country_iso2").references(() => countries.iso2),271 city: text("city"),272 regionName: text("region_name"),273 lat: doublePrecision("lat"),274 lng: doublePrecision("lng"),275 geoPrecision: text("geo_precision").notNull().default("unknown"),276 status: text("status").notNull().default("announced"),277 announcedOn: text("announced_on"),278 expectedOpening: text("expected_opening"),279 plannedMw: doublePrecision("planned_mw"),280 investmentUsd: doublePrecision("investment_usd"),281 investmentCurrency: text("investment_currency"),282 investmentOriginal: doublePrecision("investment_original"),283 acreage: doublePrecision("acreage"),284 phaseCount: integer("phase_count"),285 isAi: boolean("is_ai").notNull().default(false),286 description: text("description"),287 sourceUrl: text("source_url"),288 confidence: text("confidence").notNull().default("moderate"),289 externalIds: jsonb("external_ids").$type<Record<string, string | number>>().notNull().default(sql`'{}'::jsonb`),290 lastUpdate: ts("last_update").notNull().defaultNow(),291 projectClass: text("project_class"), // NEW_BUILD | EXPANSION | CONSTRUCTION_START | PERMIT | … | UNKNOWN292 evidenceLevel: text("evidence_level"), // strong | weak | none293 mergedInto: text("merged_into"),294 aiEvidence: text("ai_evidence").notNull().default("unknown"),295 capacityScope: text("capacity_scope"),296 capacitySemantics: text("capacity_semantics"),297 investmentScope: text("investment_scope"),298 investmentSemantics: text("investment_semantics"),299 developerId: text("developer_id"),300 tenantId: text("tenant_id"),301 campusId: text("campus_id"),302 constructionStartedOn: text("construction_started_on"),303 approvedOn: text("approved_on"),304 permitFiledOn: text("permit_filed_on"),305 openedOn: text("opened_on"),306 reviewPriority: integer("review_priority").notNull().default(0),307 /** false positives are hidden, never deleted (audit trail) */308 hidden: boolean("hidden").notNull().default(false),309 createdAt: createdAt(),310 updatedAt: updatedAt(),311 },312 (t) => [index("projects_country_idx").on(t.countryIso2), index("projects_operator_idx").on(t.operatorId), index("projects_status_idx").on(t.status), index("projects_norm_idx").on(t.normalizedName), index("projects_class_idx").on(t.projectClass), index("projects_metro_idx").on(t.metroId)],313);314315export const projectTimeline = pgTable(316 "project_timeline",317 {318 id: text("id").primaryKey(),319 projectId: text("project_id").notNull().references(() => projects.id, { onDelete: "cascade" }),320 eventDate: text("event_date").notNull(), // partial date321 eventType: text("event_type").notNull(),322 description: text("description").notNull(),323 sourceId: text("source_id"),324 documentId: text("document_id"),325 url: text("url"),326 createdAt: createdAt(),327 },328 (t) => [index("project_timeline_project_idx").on(t.projectId)],329);330331/** Sources = websites / datasets a connector reads from. */332export const sources = pgTable("sources", {333 id: text("id").primaryKey(),334 connectorId: text("connector_id").notNull(),335 name: text("name").notNull(),336 domain: text("domain").notNull(),337 kind: text("kind").notNull(), // SourceKind338 priority: integer("priority").notNull().default(3),339 url: text("url"),340 license: text("license"),341 attribution: text("attribution"),342 robotsAllowed: boolean("robots_allowed"),343 notes: text("notes"),344 redistribution: text("redistribution"), // allowed | attribution | restricted | unknown345 attributionRequired: boolean("attribution_required"),346 createdAt: createdAt(),347 updatedAt: updatedAt(),348});349350/** Connector runtime state (config is loaded from YAML and mirrored here for the admin UI). */351export const connectors = pgTable("connectors", {352 id: text("id").primaryKey(),353 sourceName: text("source_name").notNull(),354 domain: text("domain").notNull(),355 kind: text("kind").notNull(),356 mode: text("mode").notNull(),357 enabled: boolean("enabled").notNull().default(true),358 paused: boolean("paused").notNull().default(false),359 config: jsonb("config").$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),360 parserVersion: text("parser_version").notNull().default("v1"),361 schedule: jsonb("schedule").$type<Record<string, string>>().notNull().default(sql`'{}'::jsonb`),362 health: text("health").notNull().default("never_run"),363 lastRunAt: ts("last_run_at"),364 lastStatus: text("last_status"),365 lastError: text("last_error"),366 nextRunAt: ts("next_run_at"),367 stats: jsonb("stats").$type<Record<string, number>>().notNull().default(sql`'{}'::jsonb`),368 /** quarantine: runs extract and preview but publish nothing (after parser changes, HTML changes, suspicious spikes) */369 quarantine: boolean("quarantine").notNull().default(false),370 consecutiveFailures: integer("consecutive_failures").notNull().default(0),371 blockedSince: ts("blocked_since"),372 lastDiscovered: integer("last_discovered"),373 priorityScore: real("priority_score"),374 createdAt: createdAt(),375 updatedAt: updatedAt(),376});377378export const connectorRuns = pgTable(379 "connector_runs",380 {381 id: text("id").primaryKey(),382 connectorId: text("connector_id").notNull(),383 task: text("task").notNull().default("crawl"), // crawl | discover | reprocess | manual384 startedAt: ts("started_at").notNull().defaultNow(),385 finishedAt: ts("finished_at"),386 status: text("status").notNull().default("running"), // running | ok | partial | failed | aborted387 stats: jsonb("stats").$type<Record<string, number>>().notNull().default(sql`'{}'::jsonb`),388 error: text("error"),389 log: jsonb("log").$type<Array<{ t: string; level: string; msg: string }>>().notNull().default(sql`'[]'::jsonb`),390 quarantined: boolean("quarantined").notNull().default(false),391 },392 (t) => [index("connector_runs_connector_idx").on(t.connectorId, t.startedAt)],393);394395/** Crawled documents (URL registry with scheduling + change-tracking state). Raw bodies live in object storage. */396export const documents = pgTable(397 "documents",398 {399 id: text("id").primaryKey(), // stableId("document", canonicalUrl)400 connectorId: text("connector_id").notNull(),401 sourceId: text("source_id").notNull(),402 url: text("url").notNull(),403 canonicalUrl: text("canonical_url").notNull(),404 urlFingerprint: text("url_fingerprint").notNull(),405 pageType: text("page_type").notNull().default("unknown"),406 classifier: text("classifier"), // rule id that classified it407 fetchLevel: integer("fetch_level").notNull().default(1),408 priority: integer("priority").notNull().default(50),409 contentHash: text("content_hash"),410 etag: text("etag"),411 lastModified: text("last_modified"),412 statusCode: integer("status_code"),413 contentType: text("content_type"),414 sizeBytes: integer("size_bytes"),415 title: text("title"),416 storageKey: text("storage_key"),417 extractorVersion: text("extractor_version"),418 extractOk: boolean("extract_ok"),419 extractCount: integer("extract_count").notNull().default(0),420 error: text("error"),421 errorCount: integer("error_count").notNull().default(0),422 firstSeen: ts("first_seen").notNull().defaultNow(),423 lastFetched: ts("last_fetched"),424 lastChanged: ts("last_changed"),425 lastChecked: ts("last_checked"),426 nextCheck: ts("next_check"),427 changeFrequencyScore: real("change_frequency_score").notNull().default(0.5),428 fetchCount: integer("fetch_count").notNull().default(0),429 changeCount: integer("change_count").notNull().default(0),430 discoveredFrom: text("discovered_from"),431 quarantined: boolean("quarantined").notNull().default(false),432 entityRefs: jsonb("entity_refs").$type<Array<{ type: string; id: string }>>().notNull().default(sql`'[]'::jsonb`),433 updatedAt: updatedAt(),434 },435 (t) => [436 uniqueIndex("documents_fingerprint_uq").on(t.urlFingerprint),437 index("documents_connector_idx").on(t.connectorId),438 index("documents_next_check_idx").on(t.nextCheck),439 index("documents_page_type_idx").on(t.pageType),440 ],441);442443export const documentVersions = pgTable(444 "document_versions",445 {446 id: text("id").primaryKey(),447 documentId: text("document_id").notNull().references(() => documents.id, { onDelete: "cascade" }),448 contentHash: text("content_hash").notNull(),449 fetchedAt: ts("fetched_at").notNull().defaultNow(),450 fetchLevel: integer("fetch_level").notNull().default(1),451 statusCode: integer("status_code"),452 sizeBytes: integer("size_bytes"),453 storageKey: text("storage_key"),454 diffSummary: jsonb("diff_summary").$type<{ addedCount: number; removedCount: number; ratio: number; added: string[]; removed: string[] }>(),455 detectedChanges: jsonb("detected_changes").$type<Array<Record<string, unknown>>>().notNull().default(sql`'[]'::jsonb`),456 significance: integer("significance").notNull().default(0),457 runId: text("run_id"),458 extractorVersion: text("extractor_version"),459 },460 (t) => [index("document_versions_doc_idx").on(t.documentId, t.fetchedAt), index("document_versions_run_idx").on(t.runId)],461);462463/** Per-field provenance for every entity value. */464export const provenance = pgTable(465 "provenance",466 {467 id: text("id").primaryKey(),468 entityType: text("entity_type").notNull(),469 entityId: text("entity_id").notNull(),470 field: text("field").notNull(),471 value: jsonb("value"),472 sourceId: text("source_id").notNull(),473 connectorId: text("connector_id").notNull(),474 documentId: text("document_id"),475 url: text("url").notNull(),476 firstObserved: ts("first_observed").notNull().defaultNow(),477 lastObserved: ts("last_observed").notNull().defaultNow(),478 retrievedAt: ts("retrieved_at").notNull().defaultNow(),479 confidence: text("confidence").notNull().default("moderate"),480 isEstimate: boolean("is_estimate").notNull().default(false),481 method: text("method"),482 extractorVersion: text("extractor_version"),483 isCurrent: boolean("is_current").notNull().default(true),484 note: text("note"),485 runId: text("run_id"),486 scope: text("scope"),487 /** the observation currently backing the displayed column value */488 isWinner: boolean("is_winner").notNull().default(false),489 },490 (t) => [491 index("provenance_entity_idx").on(t.entityType, t.entityId),492 index("provenance_run_idx").on(t.runId),493 index("provenance_source_idx").on(t.sourceId),494 uniqueIndex("provenance_uq").on(t.entityType, t.entityId, t.field, t.sourceId, t.url),495 ],496);497498export const events = pgTable(499 "events",500 {501 id: text("id").primaryKey(),502 entityType: text("entity_type").notNull(),503 entityId: text("entity_id"),504 eventType: text("event_type").notNull(),505 detectedAt: ts("detected_at").notNull().defaultNow(),506 effectiveDate: text("effective_date"),507 oldValue: jsonb("old_value"),508 newValue: jsonb("new_value"),509 sourceId: text("source_id").notNull(),510 documentId: text("document_id"),511 url: text("url").notNull(),512 title: text("title").notNull(),513 summary: text("summary"),514 significance: integer("significance").notNull().default(30),515 confidence: text("confidence").notNull().default("moderate"),516 reviewStatus: text("review_status").notNull().default("auto"),517 countryIso2: text("country_iso2"),518 operatorId: text("operator_id"),519 metroId: text("metro_id"),520 projectId: text("project_id"),521 fingerprint: text("fingerprint").notNull(),522 runId: text("run_id"),523 /** documents describing the same underlying announcement share a cluster id */524 clusterId: text("cluster_id"),525 evidenceCount: integer("evidence_count").notNull().default(1),526 isAi: boolean("is_ai").notNull().default(false),527 sourceKind: text("source_kind"),528 },529 (t) => [530 uniqueIndex("events_fingerprint_uq").on(t.fingerprint),531 index("events_significance_idx").on(t.significance, t.detectedAt),532 index("events_project_idx").on(t.projectId),533 index("events_cluster_idx").on(t.clusterId),534 index("events_detected_idx").on(t.detectedAt),535 index("events_entity_idx").on(t.entityType, t.entityId),536 index("events_country_idx").on(t.countryIso2),537 index("events_operator_idx").on(t.operatorId),538 index("events_type_idx").on(t.eventType),539 ],540);541542export const newsItems = pgTable(543 "news_items",544 {545 id: text("id").primaryKey(),546 sourceId: text("source_id").notNull(),547 connectorId: text("connector_id").notNull(),548 url: text("url").notNull().unique(),549 title: text("title").notNull(),550 publishedAt: ts("published_at"),551 summary: text("summary"),552 pageType: text("page_type").notNull().default("unknown"),553 eventType: text("event_type"),554 mentions: jsonb("mentions").$type<Record<string, unknown>>().notNull().default(sql`'{}'::jsonb`),555 operatorIds: text("operator_ids").array().notNull().default(sql`'{}'::text[]`),556 countryIso2s: text("country_iso2s").array().notNull().default(sql`'{}'::text[]`),557 facilityIds: text("facility_ids").array().notNull().default(sql`'{}'::text[]`),558 projectId: text("project_id"),559 mw: doublePrecision("mw"),560 significance: integer("significance").notNull().default(20),561 projectClass: text("project_class"),562 clusterId: text("cluster_id"),563 metroId: text("metro_id"),564 createdAt: createdAt(),565 },566 (t) => [index("news_items_published_idx").on(t.publishedAt), index("news_items_cluster_idx").on(t.clusterId)],567);568569/** Reconciliation queue: candidate records whose match to an existing facility was ambiguous. */570export const entityMatches = pgTable(571 "entity_matches",572 {573 id: text("id").primaryKey(),574 connectorId: text("connector_id").notNull(),575 candidateKey: text("candidate_key").notNull(),576 candidate: jsonb("candidate").$type<Record<string, unknown>>().notNull(),577 matchedFacilityId: text("matched_facility_id"),578 score: real("score").notNull(),579 reasons: text("reasons").array().notNull().default(sql`'{}'::text[]`),580 status: text("status").notNull().default("pending"), // pending | approved | rejected | auto_merged | auto_created581 decidedBy: text("decided_by"),582 decidedAt: ts("decided_at"),583 createdAt: createdAt(),584 },585 (t) => [index("entity_matches_status_idx").on(t.status), index("entity_matches_key_idx").on(t.candidateKey)],586);587588export const rankings = pgTable(589 "rankings",590 {591 id: text("id").primaryKey(),592 key: text("key").notNull(),593 scope: text("scope").notNull(),594 label: text("label").notNull(),595 unit: text("unit").notNull(),596 methodology: text("methodology").notNull(),597 minCoverage: real("min_coverage"),598 rows: jsonb("rows").$type<Array<Record<string, unknown>>>().notNull(),599 total: integer("total").notNull(),600 computedAt: ts("computed_at").notNull().defaultNow(),601 isCurrent: boolean("is_current").notNull().default(true),602 },603 (t) => [index("rankings_key_idx").on(t.key, t.isCurrent)],604);605606/** Daily aggregate snapshots (also mirrored to ClickHouse) for growth charts. */607export const dailyMetrics = pgTable(608 "daily_metrics",609 {610 day: date("day").notNull(),611 metric: text("metric").notNull(), // facilities_total, known_mw, construction_mw, planned_mw, events, …612 dim: text("dim").notNull().default("global"), // "global" | "country:US" | "operator:op_x" | "metro:met_x"613 value: doublePrecision("value").notNull(),614 },615 (t) => [primaryKey({ columns: [t.day, t.metric, t.dim] })],616);617618export const adminSessions = pgTable("admin_sessions", {619 id: text("id").primaryKey(),620 createdAt: createdAt(),621 expiresAt: ts("expires_at").notNull(),622 ip: text("ip"),623 userAgent: text("user_agent"),624});625626export const systemAlerts = pgTable("system_alerts", {627 id: text("id").primaryKey(),628 level: text("level").notNull(), // info | warn | error629 component: text("component").notNull(),630 message: text("message").notNull(),631 details: jsonb("details").$type<Record<string, unknown>>(),632 resolvedAt: ts("resolved_at"),633 createdAt: createdAt(),634});635636/** Small key/value state per connector (cursors, seen keys, discovery snapshots). */637export const connectorState = pgTable(638 "connector_state",639 {640 connectorId: text("connector_id").notNull(),641 key: text("key").notNull(),642 value: jsonb("value"),643 updatedAt: updatedAt(),644 },645 (t) => [primaryKey({ columns: [t.connectorId, t.key] })],646);647648/** Claim-first store: one figure asserted by one document about one subject, with scope, evidence and authority (packages/core claims.ts). */649export const claims = pgTable(650 "claims",651 {652 id: text("id").primaryKey(),653 subjectType: text("subject_type").notNull(),654 subjectId: text("subject_id").notNull(),655 predicate: text("predicate").notNull(),656 value: doublePrecision("value"),657 valueText: text("value_text"),658 unit: text("unit"),659 scope: text("scope").notNull().default("unknown"),660 scopeReason: text("scope_reason"),661 sourceId: text("source_id").notNull(),662 connectorId: text("connector_id").notNull(),663 documentId: text("document_id"),664 url: text("url").notNull(),665 publishedAt: text("published_at"),666 retrievedAt: ts("retrieved_at").notNull().defaultNow(),667 confidence: text("confidence").notNull().default("moderate"),668 isEstimate: boolean("is_estimate").notNull().default(false),669 authorityTier: text("authority_tier").notNull().default("D"),670 evidenceText: text("evidence_text"),671 evidenceStart: integer("evidence_start"),672 evidenceEnd: integer("evidence_end"),673 parserName: text("parser_name"),674 parserVersion: text("parser_version"),675 runId: text("run_id"),676 status: text("status").notNull().default("current"), // current | superseded | rejected | review | unscoped677 rejectionReason: text("rejection_reason"),678 firstObserved: ts("first_observed").notNull().defaultNow(),679 lastObserved: ts("last_observed").notNull().defaultNow(),680 createdAt: createdAt(),681 },682 (t) => [index("claims_subject_idx").on(t.subjectType, t.subjectId, t.predicate), index("claims_status_idx").on(t.status), index("claims_run_idx").on(t.runId), index("claims_document_idx").on(t.documentId)],683);684685/** Deterministic data-quality flags (capacity / investment sanity, scope, duplicates, project false positives…). */686export const qualityFlags = pgTable(687 "quality_flags",688 {689 id: text("id").primaryKey(),690 entityType: text("entity_type").notNull(),691 entityId: text("entity_id").notNull(),692 claimId: text("claim_id"),693 code: text("code").notNull(),694 severity: text("severity").notNull().default("warn"), // info | warn | critical695 field: text("field"),696 message: text("message").notNull(),697 details: jsonb("details").$type<Record<string, unknown>>(),698 priority: integer("priority").notNull().default(0),699 status: text("status").notNull().default("open"), // open | resolved | dismissed700 resolution: text("resolution"),701 resolvedBy: text("resolved_by"),702 resolvedAt: ts("resolved_at"),703 runId: text("run_id"),704 dedupeKey: text("dedupe_key").notNull(),705 createdAt: createdAt(),706 updatedAt: updatedAt(),707 },708 (t) => [uniqueIndex("quality_flags_dedupe_uq").on(t.dedupeKey), index("quality_flags_entity_idx").on(t.entityType, t.entityId), index("quality_flags_code_idx").on(t.code)],709);710711/** Daily JSON snapshots (global totals, rankings, status / stage counts) for "as of" views and regression checks. */712export const entitySnapshots = pgTable(713 "entity_snapshots",714 {715 day: date("day").notNull(),716 kind: text("kind").notNull(),717 key: text("key").notNull(),718 payload: jsonb("payload").$type<Record<string, unknown>>().notNull(),719 createdAt: createdAt(),720 },721 (t) => [primaryKey({ columns: [t.day, t.kind, t.key] }), index("entity_snapshots_kind_idx").on(t.kind, t.key, t.day)],722);723724/** Private watchlists (owner = opaque cookie token, no accounts). */725export const watchlists = pgTable(726 "watchlists",727 {728 id: text("id").primaryKey(),729 ownerToken: text("owner_token").notNull(),730 entityType: text("entity_type").notNull(),731 entityId: text("entity_id").notNull(),732 createdAt: createdAt(),733 },734 (t) => [uniqueIndex("watchlists_uq").on(t.ownerToken, t.entityType, t.entityId)],735);736737/** Public reports of grid constraints (moratoria, delays, load caps, new transmission…) attached to a market / country. */738export const gridConstraints = pgTable(739 "grid_constraints",740 {741 id: text("id").primaryKey(),742 metroId: text("metro_id").references(() => metros.id),743 countryIso2: text("country_iso2").references(() => countries.iso2),744 kind: text("kind").notNull(),745 title: text("title").notNull(),746 summary: text("summary"),747 effectiveDate: text("effective_date"),748 sourceId: text("source_id"),749 documentId: text("document_id"),750 url: text("url").notNull(),751 eventId: text("event_id"),752 confidence: text("confidence").notNull().default("moderate"),753 createdAt: createdAt(),754 },755 (t) => [index("grid_constraints_metro_idx").on(t.metroId), index("grid_constraints_country_idx").on(t.countryIso2)],756);757