Milestone 2.4.1 — Session and Checkpoint Schema Migrations

CREATE TABLE ibex_core.sessions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    org_id          UUID NOT NULL REFERENCES ibex_core.organizations(id) ON DELETE CASCADE,
    agent_id        UUID NOT NULL REFERENCES ibex_core.agents(id) ON DELETE CASCADE,
    external_id     TEXT,                   -- client-supplied session ID (X-IBEX-Session-ID header)
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active', 'completed', 'abandoned', 'error')),
    model           TEXT NOT NULL,          -- model used (from first turn)
    provider        TEXT NOT NULL,          -- provider name (e.g. "openai")
    directive_version_id UUID REFERENCES ibex_core.directive_versions(id) ON DELETE SET NULL,
 
    -- Aggregate stats (updated by trigger or application on each checkpoint)
    turn_count      INTEGER NOT NULL DEFAULT 0,
    total_input_tokens  BIGINT NOT NULL DEFAULT 0,
    total_output_tokens BIGINT NOT NULL DEFAULT 0,
    total_latency_ms    BIGINT NOT NULL DEFAULT 0,
 
    -- Memory extraction state
    last_extracted_turn INTEGER NOT NULL DEFAULT 0, -- for incremental extraction in Phase 3
 
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at    TIMESTAMPTZ,
    deleted_at      TIMESTAMPTZ
);
 
CREATE TABLE ibex_core.checkpoints (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    session_id      UUID NOT NULL REFERENCES ibex_core.sessions(id) ON DELETE CASCADE,
    org_id          UUID NOT NULL REFERENCES ibex_core.organizations(id) ON DELETE CASCADE,
    agent_id        UUID NOT NULL REFERENCES ibex_core.agents(id) ON DELETE CASCADE,
    turn_index      INTEGER NOT NULL,   -- 0-based turn number within the session
    request_id      TEXT NOT NULL,      -- ibex request ID (from X-Request-ID)
 
    -- LLM request data
    messages_hash   TEXT NOT NULL,      -- SHA-256 of input messages JSON (for dedup)
    input_tokens    INTEGER NOT NULL DEFAULT 0,
    output_tokens   INTEGER NOT NULL DEFAULT 0,
    model           TEXT NOT NULL,
    provider        TEXT NOT NULL,
 
    -- Completion metadata
    completion_hash TEXT,               -- SHA-256 of completion content (for memory dedup in Phase 3)
    latency_ms      INTEGER NOT NULL,   -- total latency including streaming
    provider_request_id TEXT,           -- provider's own request ID for debugging
    is_streaming    BOOLEAN NOT NULL DEFAULT false,
    is_complete     BOOLEAN NOT NULL DEFAULT true, -- false if stream terminated early
 
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 
    UNIQUE(session_id, turn_index)
);
 
-- Indexes for the memory extraction worker (Phase 3)
CREATE INDEX idx_sessions_agent_extraction
    ON ibex_core.sessions(agent_id, last_extracted_turn, turn_count)
    WHERE status = 'completed' AND deleted_at IS NULL;
 
CREATE INDEX idx_checkpoints_session_turn
    ON ibex_core.checkpoints(session_id, turn_index);
 
-- RLS
ALTER TABLE ibex_core.sessions     ENABLE ROW LEVEL SECURITY;
ALTER TABLE ibex_core.checkpoints  ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY sessions_isolation ON ibex_core.sessions
    USING (org_id = current_setting('app.current_org_id', true)::UUID
           OR current_setting('app.is_service_account', true)::BOOLEAN = true);
 
CREATE POLICY checkpoints_isolation ON ibex_core.checkpoints
    USING (org_id = current_setting('app.current_org_id', true)::UUID
           OR current_setting('app.is_service_account', true)::BOOLEAN = true);
 
CREATE TRIGGER sessions_updated_at
    BEFORE UPDATE ON ibex_core.sessions
    FOR EACH ROW EXECUTE FUNCTION ibex_core.set_updated_at();