lib/db/_create.mjs

import pg from "pg";
const { Pool } = pg;
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const sqls = [
  `CREATE TABLE IF NOT EXISTS problem_classes (
    id text PRIMARY KEY,
    path text NOT NULL UNIQUE,
    parent_path text,
    label text NOT NULL,
    description text NOT NULL DEFAULT '',
    capability_tags jsonb NOT NULL DEFAULT '[]'::jsonb,
    reviewer_weights jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
  )`,
  `CREATE INDEX IF NOT EXISTS problem_classes_parent_idx ON problem_classes (parent_path)`,
  `CREATE TABLE IF NOT EXISTS tool_networks (
    id text PRIMARY KEY,
    name text NOT NULL UNIQUE,
    problem_class_path text NOT NULL,
    description text NOT NULL DEFAULT '',
    input_contract jsonb NOT NULL,
    output_contract jsonb NOT NULL,
    internal_graph jsonb NOT NULL,
    active_variant_id text,
    builder_model_tier text NOT NULL DEFAULT 'strong',
    status text NOT NULL DEFAULT 'active',
    cost_hint double precision,
    latency_hint_ms integer,
    capability_tags jsonb NOT NULL DEFAULT '[]'::jsonb,
    legacy_alias_node_id text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
  )`,
  `CREATE INDEX IF NOT EXISTS tool_networks_class_idx ON tool_networks (problem_class_path)`,
  `CREATE INDEX IF NOT EXISTS tool_networks_status_idx ON tool_networks (status)`,
  `CREATE TABLE IF NOT EXISTS network_versions (
    id text PRIMARY KEY,
    network_id text NOT NULL,
    version_label text NOT NULL,
    internal_graph jsonb NOT NULL,
    config jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'draft',
    built_by text NOT NULL DEFAULT 'system',
    builder_model_tier text NOT NULL DEFAULT 'strong',
    created_at timestamptz NOT NULL DEFAULT now()
  )`,
  `CREATE INDEX IF NOT EXISTS network_versions_net_idx ON network_versions (network_id)`,
  `CREATE INDEX IF NOT EXISTS network_versions_status_idx ON network_versions (status)`,
  `CREATE UNIQUE INDEX IF NOT EXISTS network_versions_uniq_label ON network_versions (network_id, version_label)`,
  `CREATE TABLE IF NOT EXISTS network_promotions (
    id text PRIMARY KEY,
    network_id text NOT NULL,
    from_variant_id text,
    to_variant_id text NOT NULL,
    reason text NOT NULL,
    metrics_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
    decided_by text NOT NULL DEFAULT 'system',
    created_at timestamptz NOT NULL DEFAULT now()
  )`,
  `CREATE INDEX IF NOT EXISTS network_promotions_net_idx ON network_promotions (network_id)`,
  `CREATE TABLE IF NOT EXISTS network_version_metrics (
    id text PRIMARY KEY,
    network_id text NOT NULL,
    version_id text NOT NULL,
    problem_class_path text NOT NULL,
    reviewer_score double precision NOT NULL,
    channel_breakdown jsonb NOT NULL DEFAULT '{}'::jsonb,
    cost_ms integer,
    retries integer NOT NULL DEFAULT 0,
    budget_exceeded boolean NOT NULL DEFAULT false,
    conversation_id text,
    message_id text,
    created_at timestamptz NOT NULL DEFAULT now()
  )`,
  `CREATE INDEX IF NOT EXISTS network_version_metrics_net_idx ON network_version_metrics (network_id)`,
  `CREATE INDEX IF NOT EXISTS network_version_metrics_ver_idx ON network_version_metrics (version_id)`,
  `CREATE INDEX IF NOT EXISTS network_version_metrics_class_idx ON network_version_metrics (problem_class_path)`,
  `CREATE TABLE IF NOT EXISTS execution_plans (
    id text PRIMARY KEY,
    conversation_id text,
    owner_user_id text,
    problem_class_path text NOT NULL,
    network_id text NOT NULL,
    version_id text NOT NULL,
    blueprint jsonb NOT NULL,
    status text NOT NULL DEFAULT 'draft',
    approved_by text,
    approved_at timestamptz,
    output_artifact_path text,
    notes text NOT NULL DEFAULT '',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
  )`,
  `CREATE INDEX IF NOT EXISTS execution_plans_status_idx ON execution_plans (status)`,
  `CREATE INDEX IF NOT EXISTS execution_plans_owner_idx ON execution_plans (owner_user_id)`,
  `CREATE TABLE IF NOT EXISTS task_ledger (
    id text PRIMARY KEY,
    plan_id text NOT NULL,
    task_key text NOT NULL,
    params jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'pending',
    attempts integer NOT NULL DEFAULT 0,
    max_attempts integer NOT NULL DEFAULT 3,
    result jsonb,
    error_text text,
    metrics jsonb,
    duration_ms integer,
    started_at timestamptz,
    finished_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
  )`,
  `CREATE INDEX IF NOT EXISTS task_ledger_plan_idx ON task_ledger (plan_id)`,
  `CREATE INDEX IF NOT EXISTS task_ledger_status_idx ON task_ledger (status)`,
  `CREATE UNIQUE INDEX IF NOT EXISTS task_ledger_uniq ON task_ledger (plan_id, task_key)`,

  // Task #226: 比赛真值反馈台账, 比赛结束后由 ingestion 任务把官方真值
  // 回灌到 external_truth_value, 作为 elastic-reviewer L0 的 ground truth。
  // 列名/可空性/索引名严格对齐 lib/db/src/schema/toolNetwork.ts 的
  // submissionFeedbackLedger 定义, 避免 bootstrap DB 与 schema-driven DB 漂移。
  `CREATE TABLE IF NOT EXISTS submission_feedback_ledger (
    id text PRIMARY KEY,
    submission_id text NOT NULL,
    network_id text NOT NULL,
    network_version_id text NOT NULL,
    task_id text NOT NULL,
    metric_row_id text NOT NULL,
    submitted_at timestamptz NOT NULL DEFAULT now(),
    external_truth_status text NOT NULL DEFAULT 'pending',
    external_truth_value double precision,
    external_truth_received_at timestamptz
  )`,
  `CREATE INDEX IF NOT EXISTS submission_feedback_ledger_sub_idx ON submission_feedback_ledger (submission_id)`,
  `CREATE INDEX IF NOT EXISTS submission_feedback_ledger_task_idx ON submission_feedback_ledger (task_id)`,
  `CREATE INDEX IF NOT EXISTS submission_feedback_ledger_metric_idx ON submission_feedback_ledger (metric_row_id)`,
  `CREATE INDEX IF NOT EXISTS submission_feedback_ledger_status_idx ON submission_feedback_ledger (external_truth_status)`,
  `CREATE UNIQUE INDEX IF NOT EXISTS submission_feedback_ledger_uniq ON submission_feedback_ledger (submission_id, task_id)`,

  // ----- Wave B online-evolution flywheel ---------------------------------
  `ALTER TABLE tool_networks ADD COLUMN IF NOT EXISTS release_tier_floor text NOT NULL DEFAULT 'strong'`,
  `ALTER TABLE tool_networks ADD COLUMN IF NOT EXISTS config jsonb NOT NULL DEFAULT '{}'::jsonb`,
  `ALTER TABLE network_versions ADD COLUMN IF NOT EXISTS private_namespace text`,
  // ----- Task #214 tool-network registry (problem class lifecycle) --------
  `ALTER TABLE problem_classes ADD COLUMN IF NOT EXISTS status text NOT NULL DEFAULT 'active'`,
  `CREATE INDEX IF NOT EXISTS problem_classes_status_idx ON problem_classes (status)`,
  `CREATE INDEX IF NOT EXISTS network_versions_private_ns_idx ON network_versions (private_namespace)`,
  `CREATE TABLE IF NOT EXISTS network_shadow_samples (
    id text PRIMARY KEY,
    network_id text NOT NULL,
    active_variant_id text NOT NULL,
    shadow_variant_id text NOT NULL,
    problem_class_path text NOT NULL,
    active_score double precision NOT NULL,
    shadow_score double precision NOT NULL,
    critical_signal boolean NOT NULL DEFAULT false,
    active_cost_ms integer,
    shadow_cost_ms integer,
    budget_skipped boolean NOT NULL DEFAULT false,
    conversation_id text,
    message_id text,
    created_at timestamptz NOT NULL DEFAULT now()
  )`,
  `CREATE INDEX IF NOT EXISTS network_shadow_samples_net_idx ON network_shadow_samples (network_id)`,
  `CREATE INDEX IF NOT EXISTS network_shadow_samples_shadow_idx ON network_shadow_samples (shadow_variant_id)`,
  `CREATE INDEX IF NOT EXISTS network_shadow_samples_created_idx ON network_shadow_samples (created_at)`,
  `CREATE TABLE IF NOT EXISTS network_evolution_events (
    id text PRIMARY KEY,
    network_id text NOT NULL,
    kind text NOT NULL,
    variant_id text,
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    related_event_id text,
    promotion_id text,
    created_at timestamptz NOT NULL DEFAULT now()
  )`,
  `CREATE INDEX IF NOT EXISTS network_evolution_events_net_idx ON network_evolution_events (network_id)`,
  `CREATE INDEX IF NOT EXISTS network_evolution_events_kind_idx ON network_evolution_events (kind)`,
  `CREATE INDEX IF NOT EXISTS network_evolution_events_created_idx ON network_evolution_events (created_at)`,
  `CREATE TABLE IF NOT EXISTS network_regression_samples (
    id text PRIMARY KEY,
    network_id text NOT NULL,
    problem_class_path text NOT NULL,
    label text NOT NULL DEFAULT '',
    input_payload jsonb NOT NULL,
    expected_floor double precision NOT NULL DEFAULT 0.6,
    expected_shape jsonb NOT NULL DEFAULT '{}'::jsonb,
    status text NOT NULL DEFAULT 'active',
    created_by text NOT NULL DEFAULT 'system',
    created_at timestamptz NOT NULL DEFAULT now()
  )`,
  `CREATE INDEX IF NOT EXISTS network_regression_samples_net_idx ON network_regression_samples (network_id)`,
  `CREATE INDEX IF NOT EXISTS network_regression_samples_status_idx ON network_regression_samples (status)`,
];

let count = 0;
for (const sql of sqls) {
  try {
    await pool.query(sql);
    count++;
  } catch (e) {
    console.error("FAIL:", sql.slice(0, 80), e.message);
  }
}
console.log(`OK ${count}/${sqls.length} statements applied`);
await pool.end();