Agent-System/deploy/postgres/goose/00044_2026-09-15-intent-plans.sql

-- +goose Up
-- U3: persistent multi-intent plans. Owner is app/intentflow; other packages use the Service port.
-- JSONB is only for versioned slots / display snapshots. CAS, lease, and audit columns are typed.

CREATE TABLE IF NOT EXISTS intent_plans (
    plan_id TEXT PRIMARY KEY,
    environment TEXT NOT NULL,
    project_id BIGINT NOT NULL,
    session_id TEXT NOT NULL,
    user_id BIGINT NOT NULL,
    client_turn_id TEXT NOT NULL,
    request_digest TEXT NOT NULL,
    segmenter_version TEXT NOT NULL DEFAULT '',
    policy_version TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL,
    revision BIGINT NOT NULL DEFAULT 1,
    expires_at TIMESTAMPTZ NOT NULL,
    last_fencing_token BIGINT NOT NULL DEFAULT 0,
    write_plan BOOLEAN NOT NULL DEFAULT FALSE,
    execution_mode TEXT NOT NULL DEFAULT 'validated_only',
    safe_summary TEXT NOT NULL DEFAULT '',
    display_snapshot JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT chk_intent_plan_status CHECK (status IN (
        'proposed',
        'awaiting_clarification',
        'awaiting_confirmation',
        'executing',
        'partially_completed',
        'completed',
        'failed',
        'cancelled',
        'expired',
        'superseded'
    )),
    CONSTRAINT chk_intent_plan_execution_mode CHECK (execution_mode IN (
        'validated_only',
        'live'
    ))
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_intent_plans_turn
    ON intent_plans (environment, session_id, client_turn_id);

CREATE UNIQUE INDEX IF NOT EXISTS uq_intent_plans_one_active_write
    ON intent_plans (environment, project_id, session_id, user_id)
    WHERE write_plan AND status IN (
        'proposed',
        'awaiting_clarification',
        'awaiting_confirmation',
        'executing',
        'partially_completed'
    );

CREATE INDEX IF NOT EXISTS idx_intent_plans_active_scope
    ON intent_plans (environment, project_id, session_id, user_id, status);

CREATE INDEX IF NOT EXISTS idx_intent_plans_expire
    ON intent_plans (expires_at)
    WHERE status IN ('proposed', 'awaiting_clarification', 'awaiting_confirmation');

CREATE INDEX IF NOT EXISTS idx_intent_plans_retention
    ON intent_plans (updated_at)
    WHERE status IN ('completed', 'failed', 'cancelled', 'expired', 'superseded');

CREATE TABLE IF NOT EXISTS intent_plan_steps (
    step_id TEXT PRIMARY KEY,
    plan_id TEXT NOT NULL REFERENCES intent_plans(plan_id) ON DELETE CASCADE,
    ordinal INT NOT NULL,
    action_key TEXT NOT NULL,
    slots JSONB NOT NULL DEFAULT '{}'::jsonb,
    depends_on TEXT[] NOT NULL DEFAULT '{}',
    status TEXT NOT NULL,
    expected_resource_version TEXT NOT NULL DEFAULT '',
    action_digest TEXT NOT NULL DEFAULT '',
    idempotency_key TEXT NOT NULL DEFAULT '',
    attempt INT NOT NULL DEFAULT 0,
    result_ref TEXT NOT NULL DEFAULT '',
    result_digest TEXT NOT NULL DEFAULT '',
    safe_summary TEXT NOT NULL DEFAULT '',
    lease_owner TEXT NOT NULL DEFAULT '',
    lease_until TIMESTAMPTZ,
    fencing_token BIGINT NOT NULL DEFAULT 0,
    version BIGINT NOT NULL DEFAULT 1,
    execution_mode TEXT NOT NULL DEFAULT 'validated_only',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT chk_intent_plan_step_status CHECK (status IN (
        'pending',
        'eligible',
        'running',
        'succeeded',
        'failed',
        'denied',
        'conflicted',
        'blocked_dependency',
        'skipped',
        'cancelled'
    )),
    CONSTRAINT chk_intent_plan_step_execution_mode CHECK (execution_mode IN (
        'validated_only',
        'live'
    ))
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_intent_plan_steps_ordinal
    ON intent_plan_steps (plan_id, ordinal);

CREATE UNIQUE INDEX IF NOT EXISTS uq_intent_plan_steps_idempotency
    ON intent_plan_steps (idempotency_key)
    WHERE idempotency_key <> '';

CREATE INDEX IF NOT EXISTS idx_intent_plan_steps_claim
    ON intent_plan_steps (status, lease_until);

CREATE TABLE IF NOT EXISTS intent_plan_decisions (
    decision_id TEXT PRIMARY KEY,
    plan_id TEXT NOT NULL REFERENCES intent_plans(plan_id) ON DELETE CASCADE,
    nonce TEXT NOT NULL,
    plan_revision BIGINT NOT NULL,
    actor_user_id BIGINT NOT NULL,
    kind TEXT NOT NULL,
    step_ids TEXT[] NOT NULL DEFAULT '{}',
    exclude_unselected BOOLEAN NOT NULL DEFAULT FALSE,
    decision_digest TEXT NOT NULL,
    outcome TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT chk_intent_plan_decision_kind CHECK (kind IN (
        'confirm',
        'cancel',
        'exclude',
        'clarify',
        'edit',
        'confirm_retry',
        'continue',
        'replace'
    ))
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_intent_plan_decisions_nonce
    ON intent_plan_decisions (plan_id, nonce);

CREATE INDEX IF NOT EXISTS idx_intent_plan_decisions_plan
    ON intent_plan_decisions (plan_id, created_at);

-- +goose Down
DROP INDEX IF EXISTS idx_intent_plan_decisions_plan;
DROP INDEX IF EXISTS uq_intent_plan_decisions_nonce;
DROP TABLE IF EXISTS intent_plan_decisions;
DROP INDEX IF EXISTS idx_intent_plan_steps_claim;
DROP INDEX IF EXISTS uq_intent_plan_steps_idempotency;
DROP INDEX IF EXISTS uq_intent_plan_steps_ordinal;
DROP TABLE IF EXISTS intent_plan_steps;
DROP INDEX IF EXISTS idx_intent_plans_retention;
DROP INDEX IF EXISTS idx_intent_plans_expire;
DROP INDEX IF EXISTS idx_intent_plans_active_scope;
DROP INDEX IF EXISTS uq_intent_plans_one_active_write;
DROP INDEX IF EXISTS uq_intent_plans_turn;
DROP TABLE IF EXISTS intent_plans;