-- Directive memory system: persistent key-value storage for directives.
-- Allows directives to store and retrieve context across sessions.

CREATE TABLE directive_memories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    directive_id UUID NOT NULL REFERENCES directives(id) ON DELETE CASCADE,
    key VARCHAR(255) NOT NULL,
    value TEXT NOT NULL,
    category VARCHAR(100),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (directive_id, key)
);

CREATE INDEX idx_directive_memories_directive_id ON directive_memories(directive_id);
CREATE INDEX idx_directive_memories_category ON directive_memories(directive_id, category);