inter-hub/Application/Migration/1743552000-ihf-phase7-advanced-observability.sql

-- IHF Phase 7 — Advanced Observability and Operational Integration
-- Workplan: IHUB-WP-0007

CREATE TABLE friction_scores (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    widget_id UUID NOT NULL REFERENCES widgets(id),
    score INTEGER NOT NULL DEFAULT 0,
    annotation_count INTEGER NOT NULL DEFAULT 0,
    error_event_count INTEGER NOT NULL DEFAULT 0,
    regression_flag BOOLEAN NOT NULL DEFAULT FALSE,
    stale_candidate_count INTEGER NOT NULL DEFAULT 0,
    last_computed_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
    UNIQUE (widget_id)
);

CREATE INDEX friction_scores_widget_id_idx ON friction_scores (widget_id);
CREATE INDEX friction_scores_score_idx ON friction_scores (score DESC);

CREATE TABLE bottleneck_records (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    hub_id UUID NOT NULL REFERENCES hubs(id),
    stage TEXT NOT NULL,
    subject_type TEXT NOT NULL,
    subject_id UUID NOT NULL,
    stalled_since TIMESTAMP WITH TIME ZONE NOT NULL,
    severity TEXT NOT NULL DEFAULT 'medium',
    resolved_at TIMESTAMP WITH TIME ZONE,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL
);

CREATE INDEX bottleneck_records_hub_id_idx ON bottleneck_records (hub_id);
CREATE INDEX bottleneck_records_stage_idx ON bottleneck_records (stage);
CREATE INDEX bottleneck_records_resolved_idx ON bottleneck_records (resolved_at)
    WHERE resolved_at IS NULL;

CREATE TABLE hub_health_snapshots (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    hub_id UUID NOT NULL REFERENCES hubs(id),
    health_score INTEGER NOT NULL,
    open_candidates INTEGER NOT NULL DEFAULT 0,
    regressed_widgets INTEGER NOT NULL DEFAULT 0,
    stale_decisions INTEGER NOT NULL DEFAULT 0,
    active_bottlenecks INTEGER NOT NULL DEFAULT 0,
    computed_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL
);

CREATE INDEX hub_health_snapshots_hub_id_idx ON hub_health_snapshots (hub_id);
CREATE INDEX hub_health_snapshots_computed_at_idx
    ON hub_health_snapshots (hub_id, computed_at DESC);

CREATE TABLE cross_hub_propagations (
    id UUID DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    pattern_type TEXT NOT NULL,
    source_hub_id UUID REFERENCES hubs(id),
    affected_hub_ids JSONB NOT NULL DEFAULT '[]',
    summary TEXT NOT NULL,
    detected_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
    status TEXT NOT NULL DEFAULT 'open',
    notes TEXT
);

CREATE INDEX cross_hub_propagations_status_idx ON cross_hub_propagations (status);
CREATE INDEX cross_hub_propagations_pattern_idx ON cross_hub_propagations (pattern_type);