┌─────────────────────────────────────────────────────┐
│               Agent Long-term Memory Layer           │
├─────────────────────────────────────────────────────┤
│                                                     │
│  ┌─────────────┐  ┌──────────────┐  ┌───────────┐   │
│  │ Semantic Memory    │  │ Relational Memory     │  │ Factual Memory  │   │
│  │ (pgvector)  │  │ (Apache AGE) │  │ (Relational Table)  │   │
│  │             │  │              │  │           │   │
│  │ Fuzzy Recall    │  │ Entity Relations     │  │ Precise Query  │   │
│  │ Similarity Search  │  │ Causal Reasoning     │  │ Metadata    │   │
│  │ RAG Enhancement    │  │ Knowledge Graph     │  │ Audit Logs  │   │
│  └─────────────┘  └──────────────┘  └───────────┘   │
│                                                     │
│  ┌─────────────────────────────────────────────┐    │
│  │ RLS Row-Level Security → Multi-Tenant / Multi-Agent Data Isolation   │    │
│  └─────────────────────────────────────────────┘    │
└─────────────────────────────────────────────────────┘

-- Core Table Schema
CREATE SCHEMA memory;

-- Memory Record Table (append-only, immutable)
CREATE TABLE memory.records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,        -- Tenant Isolation
    agent_id UUID NOT NULL,         -- Owning Agent
    project_id UUID,                -- Owning Project (Optional)
    content TEXT NOT NULL,          -- Memory content
    embedding vector(1024),         -- Vector embedding
    memory_type TEXT DEFAULT 'episodic',  -- Memory type
    importance FLOAT DEFAULT 0.5,   -- Importance weight
    metadata JSONB DEFAULT '{}',    -- Extended metadata
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ          -- Expiration time (Optional)
);

-- Vector index
CREATE INDEX idx_memory_embedding ON memory.records
USING hnsw (embedding vector_cosine_ops);

-- RLS policy
ALTER TABLE memory.records ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON memory.records
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Write a memory record
INSERT INTO memory.records (tenant_id, agent_id, content, embedding, memory_type)
VALUES (
    '550e8400-e29b-41d4-a716-446655440000',
    'agent-planner-001',
    The user prefers to use Python for data analysis and favors a concise code style.
    tencentdb_ai.get_embedding('bge-m3', 'The user prefers to use Python for data analysis'),
    'preference'
);

-- Search for relevant memories based on the current context
SELECT content, memory_type,
       1 - (embedding <=> query_vec) AS relevance
FROM memory.records
WHERE agent_id = 'agent-planner-001'
  AND (expires_at IS NULL OR expires_at > NOW())
ORDER BY embedding <=> tencentdb_ai.get_embedding('bge-m3', 'What are the user's programming preferences?')
LIMIT 5;

-- Memory decay based on time and importance
SELECT content,
       importance * EXP(-0.01 * EXTRACT(EPOCH FROM (NOW() - created_at)) / 86400) AS current_weight
FROM memory.records
WHERE agent_id = 'agent-planner-001'
ORDER BY current_weight DESC
LIMIT 10;

-- Summarize and compress old memories using a large model
SELECT tencentdb_ai.chat_completions(
    '<your-llm-model>',
    'Summarize the following multiple memories into a single paragraph: ' || string_agg(content, E'\\n')
)
FROM memory.records
WHERE agent_id = 'agent-planner-001'
  AND created_at < NOW() - INTERVAL '7 days'
GROUP BY DATE(created_at);

-- Each Agent/tenant can only access its own memories.
SET app.tenant_id = '550e8400-e29b-41d4-a716-446655440000';
SET app.agent_id = 'agent-planner-001';

-- RLS automatically filters data, eliminating the need for application-layer judgment.
SELECT * FROM memory.records;  -- Returns only the data of the current tenant