stonks-oracle/infra/migrations/040_inference_registry.sql

-- Migration 040: Inference Registry
-- Creates tables for the capability-aware inference gateway:
--   inference_endpoints, model_deployments, agent_stage_bindings
-- Adds lineage columns to agent_performance_log for v3 provenance tracking.

-- ─── Helper: auto-update updated_at on row modification ───────────────────────
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- ─── inference_endpoints ──────────────────────────────────────────────────────
-- Stores registered inference service endpoints (Ollama, vLLM, OpenAI-compat, specialist).
CREATE TABLE IF NOT EXISTS inference_endpoints (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL UNIQUE,
    protocol TEXT NOT NULL CHECK (protocol IN ('ollama_native', 'openai_chat', 'specialist_http')),
    base_url TEXT NOT NULL,
    auth_secret_ref TEXT,
    auth_scheme TEXT NOT NULL DEFAULT 'bearer',
    default_headers JSONB NOT NULL DEFAULT '{}',
    health_path TEXT,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    revision INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_inference_endpoints_protocol
    ON inference_endpoints(protocol);

-- Auto-update updated_at on inference_endpoints changes
DROP TRIGGER IF EXISTS trg_inference_endpoints_updated_at ON inference_endpoints;
CREATE TRIGGER trg_inference_endpoints_updated_at
    BEFORE UPDATE ON inference_endpoints
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ─── model_deployments ────────────────────────────────────────────────────────
-- A model served by an endpoint, with declared capabilities and limits.
CREATE TABLE IF NOT EXISTS model_deployments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    endpoint_id UUID NOT NULL REFERENCES inference_endpoints(id) ON DELETE CASCADE,
    served_model_name TEXT NOT NULL,
    display_name TEXT NOT NULL,
    capabilities JSONB NOT NULL,
    context_window INTEGER,
    max_output_tokens INTEGER,
    quantization TEXT,
    runtime_metadata JSONB NOT NULL DEFAULT '{}',
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    revision INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(endpoint_id, served_model_name)
);

CREATE INDEX IF NOT EXISTS idx_model_deployments_endpoint
    ON model_deployments(endpoint_id);

-- Auto-update updated_at on model_deployments changes
DROP TRIGGER IF EXISTS trg_model_deployments_updated_at ON model_deployments;
CREATE TRIGGER trg_model_deployments_updated_at
    BEFORE UPDATE ON model_deployments
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ─── agent_stage_bindings ─────────────────────────────────────────────────────
-- Maps an agent + pipeline stage to one or more ordered model deployments.
CREATE TABLE IF NOT EXISTS agent_stage_bindings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agent_id UUID NOT NULL REFERENCES ai_agents(id) ON DELETE CASCADE,
    stage TEXT NOT NULL,
    model_deployment_id UUID REFERENCES model_deployments(id) ON DELETE SET NULL,
    route_order INTEGER NOT NULL DEFAULT 0,
    routing_config JSONB NOT NULL DEFAULT '{}',
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    revision INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(agent_id, stage, route_order)
);

CREATE INDEX IF NOT EXISTS idx_agent_stage_bindings_agent
    ON agent_stage_bindings(agent_id);

CREATE INDEX IF NOT EXISTS idx_agent_stage_bindings_deployment
    ON agent_stage_bindings(model_deployment_id);

-- Auto-update updated_at on agent_stage_bindings changes
DROP TRIGGER IF EXISTS trg_agent_stage_bindings_updated_at ON agent_stage_bindings;
CREATE TRIGGER trg_agent_stage_bindings_updated_at
    BEFORE UPDATE ON agent_stage_bindings
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- ─── Additive lineage columns on agent_performance_log ────────────────────────
-- Tracks which endpoint/deployment/binding was used for each logged invocation.
-- NOTE: Revision increment logic is handled at the application layer:
--   each UPDATE to inference_endpoints, model_deployments, or agent_stage_bindings
--   should increment the revision column (enforced by service code, not DB trigger,
--   to allow flexible conflict resolution).

ALTER TABLE agent_performance_log
    ADD COLUMN IF NOT EXISTS endpoint_id UUID REFERENCES inference_endpoints(id) ON DELETE SET NULL;

ALTER TABLE agent_performance_log
    ADD COLUMN IF NOT EXISTS deployment_id UUID REFERENCES model_deployments(id) ON DELETE SET NULL;

ALTER TABLE agent_performance_log
    ADD COLUMN IF NOT EXISTS binding_revision INTEGER;

ALTER TABLE agent_performance_log
    ADD COLUMN IF NOT EXISTS structured_mode TEXT;

CREATE INDEX IF NOT EXISTS idx_agent_perf_endpoint
    ON agent_performance_log(endpoint_id)
    WHERE endpoint_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_agent_perf_deployment
    ON agent_performance_log(deployment_id)
    WHERE deployment_id IS NOT NULL;