schema.sql

CREATE TABLE IF NOT EXISTS memory_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id STRING NOT NULL,
    agent_id STRING NOT NULL,
    namespace STRING NOT NULL,
    content STRING NOT NULL,
    summary STRING NULL,
    embedding VECTOR(1024) NULL,
    sensitivity STRING NOT NULL CHECK (sensitivity IN ('public', 'internal', 'confidential', 'restricted')),
    status STRING NOT NULL CHECK (status IN ('active', 'pending_review', 'denied', 'revoked')),
    risk_score INT NOT NULL CHECK (risk_score BETWEEN 0 AND 100),
    purpose STRING NOT NULL,
    source_uri STRING NULL,
    version INT NOT NULL DEFAULT 1,
    expires_at TIMESTAMPTZ NULL,
    approved_by STRING NULL,
    approved_at TIMESTAMPTZ NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    INDEX memory_lookup_idx (tenant_id, namespace, status, created_at DESC),
    VECTOR INDEX memory_semantic_idx (tenant_id, namespace, embedding vector_cosine_ops)
);

CREATE TABLE IF NOT EXISTS memory_audit_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id STRING NOT NULL,
    memory_id UUID NULL,
    actor_id STRING NOT NULL,
    event_type STRING NOT NULL,
    decision STRING NOT NULL,
    reason_codes STRING[] NOT NULL DEFAULT ARRAY[]::STRING[],
    metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    INDEX audit_tenant_time_idx (tenant_id, created_at DESC),
    INDEX audit_memory_idx (memory_id, created_at DESC)
);

CREATE TABLE IF NOT EXISTS agent_checkpoints (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id STRING NOT NULL,
    agent_id STRING NOT NULL,
    checkpoint_key STRING NOT NULL,
    state JSONB NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (tenant_id, agent_id, checkpoint_key)
);