Milestone 2.3.1 — Directive Schema Migrations (directives + directive_versions)

-- ibex_core.directives: one row per agent's active directive configuration.
-- The active_version_id pointer is updated atomically when a directive is updated.
CREATE TABLE ibex_core.directives (
    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,
    active_version_id UUID,  -- FK added after directive_versions is created
    injection_mode    TEXT NOT NULL DEFAULT 'system_first'
                      CHECK (injection_mode IN ('system_first', 'system_append', 'user_prepend')),
    is_active         BOOLEAN NOT NULL DEFAULT true,
    created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(agent_id)  -- one directive configuration per agent
);
 
-- ibex_core.directive_versions: immutable version history.
-- Once created, a version is never updated. Rollback = change active_version_id pointer.
CREATE TABLE ibex_core.directive_versions (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id  UUID NOT NULL REFERENCES ibex_core.directives(id) ON DELETE CASCADE,
    org_id        UUID NOT NULL REFERENCES ibex_core.organizations(id) ON DELETE CASCADE,
    version_num   INTEGER NOT NULL,  -- monotonically incrementing per directive_id
    content       TEXT NOT NULL,     -- the actual directive text (system prompt)
    content_hash  TEXT NOT NULL,     -- SHA-256 of content for dedup
    created_by    UUID REFERENCES ibex_core.users(id) ON DELETE SET NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(directive_id, version_num),
    CONSTRAINT directive_versions_content_not_empty CHECK (length(content) > 0),
    CONSTRAINT directive_versions_content_max CHECK (length(content) <= 32768) -- 32KB max
);
 
-- Now add the FK from directives.active_version_id → directive_versions.id
ALTER TABLE ibex_core.directives
    ADD CONSTRAINT directives_active_version_id_fk
    FOREIGN KEY (active_version_id)
    REFERENCES ibex_core.directive_versions(id)
    ON DELETE SET NULL
    NOT VALID;
ALTER TABLE ibex_core.directives VALIDATE CONSTRAINT directives_active_version_id_fk;
 
-- Indexes for the proxy hot path: fetch active directive for agent
CREATE INDEX idx_directives_agent_id
    ON ibex_core.directives(agent_id)
    WHERE is_active = true;
 
-- RLS
ALTER TABLE ibex_core.directives        ENABLE ROW LEVEL SECURITY;
ALTER TABLE ibex_core.directive_versions ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY directives_isolation ON ibex_core.directives
    USING (org_id = current_setting('app.current_org_id', true)::UUID
           OR current_setting('app.is_service_account', true)::BOOLEAN = true);
 
CREATE POLICY directive_versions_isolation ON ibex_core.directive_versions
    USING (org_id = current_setting('app.current_org_id', true)::UUID
           OR current_setting('app.is_service_account', true)::BOOLEAN = true);
 
CREATE TRIGGER directives_updated_at
    BEFORE UPDATE ON ibex_core.directives
    FOR EACH ROW EXECUTE FUNCTION ibex_core.set_updated_at();