Create-knowledge-find-decode/db/migrations/005_query_planning_schema.sql

-- 005: add query planning state before the frozen acquisition boundary.

CREATE TABLE IF NOT EXISTS creation_knowledge.query_plans (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    generator_kind text NOT NULL CHECK (
        generator_kind IN ('cartesian', 'manual', 'topic_table', 'agent_plan', 'history_gap')
    ),
    status text NOT NULL DEFAULT 'ready' CHECK (status IN ('planning', 'ready', 'failed')),
    input_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
    generator_config jsonb NOT NULL DEFAULT '{}'::jsonb,
    max_queries integer CHECK (max_queries IS NULL OR max_queries >= 0),
    generated_count integer NOT NULL DEFAULT 0 CHECK (generated_count >= 0),
    normalized_count integer NOT NULL DEFAULT 0 CHECK (normalized_count >= 0),
    unique_count integer NOT NULL DEFAULT 0 CHECK (unique_count >= 0),
    selected_count integer NOT NULL DEFAULT 0 CHECK (selected_count >= 0),
    dropped_count integer NOT NULL DEFAULT 0 CHECK (dropped_count >= 0),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    CHECK (normalized_count <= generated_count),
    CHECK (unique_count <= normalized_count),
    CHECK (selected_count <= unique_count),
    CHECK (dropped_count = generated_count - selected_count)
);

CREATE TABLE IF NOT EXISTS creation_knowledge.knowledge_needs (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id uuid NOT NULL REFERENCES creation_knowledge.query_plans(id) ON DELETE CASCADE,
    need_key text NOT NULL,
    decision_context text NOT NULL,
    unknown_information text NOT NULL,
    source_ref jsonb NOT NULL DEFAULT '{}'::jsonb,
    priority integer NOT NULL DEFAULT 0,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (plan_id, need_key)
);

CREATE TABLE IF NOT EXISTS creation_knowledge.query_plan_batches (
    plan_id uuid NOT NULL REFERENCES creation_knowledge.query_plans(id) ON DELETE CASCADE,
    batch_id uuid NOT NULL REFERENCES creation_knowledge.query_batches(id) ON DELETE CASCADE,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (plan_id, batch_id)
);

CREATE TABLE IF NOT EXISTS creation_knowledge.query_knowledge_need_links (
    query_id uuid NOT NULL REFERENCES creation_knowledge.queries(id) ON DELETE CASCADE,
    knowledge_need_id uuid NOT NULL REFERENCES creation_knowledge.knowledge_needs(id) ON DELETE CASCADE,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (query_id, knowledge_need_id)
);

CREATE INDEX IF NOT EXISTS idx_query_plans_kind_created
ON creation_knowledge.query_plans(generator_kind, created_at);

CREATE INDEX IF NOT EXISTS idx_knowledge_needs_plan_priority
ON creation_knowledge.knowledge_needs(plan_id, priority DESC);

CREATE INDEX IF NOT EXISTS idx_query_plan_batches_batch
ON creation_knowledge.query_plan_batches(batch_id);

CREATE INDEX IF NOT EXISTS idx_query_knowledge_need_links_need
ON creation_knowledge.query_knowledge_need_links(knowledge_need_id);

DROP TRIGGER IF EXISTS trg_query_plans_touch_updated_at
ON creation_knowledge.query_plans;

CREATE TRIGGER trg_query_plans_touch_updated_at
BEFORE UPDATE ON creation_knowledge.query_plans
FOR EACH ROW EXECUTE FUNCTION creation_knowledge.touch_updated_at();

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'ck_app') THEN
        GRANT SELECT, INSERT, UPDATE, DELETE ON
            creation_knowledge.query_plans,
            creation_knowledge.knowledge_needs,
            creation_knowledge.query_plan_batches,
            creation_knowledge.query_knowledge_need_links
        TO ck_app;
    END IF;
END;
$$;

INSERT INTO creation_knowledge.schema_migrations(version, description)
VALUES ('005_query_planning_schema', 'Add query plans, knowledge needs, and legacy query links')
ON CONFLICT (version) DO UPDATE SET
    description = EXCLUDED.description,
    applied_at = now();