CREATE TABLE directive_goal_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
    goal TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_directive_goal_history_directive_id ON directive_goal_history(directive_id);
CREATE INDEX idx_directive_goal_history_created_at ON directive_goal_history(directive_id, created_at DESC);