┌─────────────────────────────────────────────────┐
│                  OpenClaw Agent                 │
├─────────────────────────────────────────────────┤
│  ┌───────────┐  ┌──────────┐  ┌──────────────┐  │
│  │ Skill A   │  │ Skill B  │  │ Skill C      │  │
│  │ (Search)    │  │ (Code)   │  │ (Data Analysis)   │  │
│  └─────┬─────┘  └────┬─────┘  └──────┬───────┘  │
│        └──────────────┴───────────────┘         │
│                       │                         │
│  ┌────────────────────▼───────────────────────┐ │
│  │          Conversation Management & Memory Search               │ │
│  └────────────────────┬───────────────────────┘ │
└───────────────────────┼─────────────────────────┘
                        │
          ┌─────────────┼─────────────┐
          ▼             ▼             ▼
   ┌────────────┐ ┌──────────┐ ┌──────────────┐
   │ Tencent Cloud PG  │ │ LLM   │ │ External Services     │
   │(Memory/Vector) │ │ (Reasoning)   │ │ (API)        │
   └────────────┘ └──────────┘ └──────────────┘

-- schema required for creating Agent applications
CREATE SCHEMA IF NOT EXISTS agent_app;

-- Conversation History Table
CREATE TABLE agent_app.conversations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id TEXT NOT NULL,
    agent_id TEXT NOT NULL,
    messages JSONB[] DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Knowledge Base Table
CREATE TABLE agent_app.knowledge (
    id BIGSERIAL PRIMARY KEY,
    title TEXT,
    content TEXT NOT NULL,
    embedding vector(1024),
    source TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_knowledge_embedding ON agent_app.knowledge
USING hnsw (embedding vector_cosine_ops);

-- Long-term Memory Table
CREATE TABLE agent_app.memories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id TEXT NOT NULL,
    content TEXT NOT NULL,
    embedding vector(1024),
    importance FLOAT DEFAULT 0.5,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_memories_embedding ON agent_app.memories
USING hnsw (embedding vector_cosine_ops);

import psycopg2
from pgvector.psycopg2 import register_vector

# Connect to Tencent Cloud PostgreSQL
conn = psycopg2.connect(
    host='your-instance.pg.tencentcdb.com',
    port=5432,
    dbname='agent_db',
    user='your_user',
    password='your_password'  # For production environments, inject via environment variables / a secrets management service.
)
register_vector(conn)

class MemorySkill:
    """Long-term Memory Skill Based on PostgreSQL"""

    def store_memory(self, user_id: str, content: str):
        """Store a memory"""
        with conn.cursor() as cur:
            # Use parameterized queries and avoid string concatenation.
            cur.execute("""
                INSERT INTO agent_app.memories (user_id, content, embedding)
                VALUES (%s, %s,
                    (SELECT tencentdb_ai.get_embedding('bge-m3', %s))
                )
            """, (user_id, content, content))
        conn.commit()

    def recall_memories(self, user_id: str, query: str, limit: int = 5):
        """Search for relevant memories"""
        with conn.cursor() as cur:
            cur.execute("""
                SELECT content, importance,
                       1 - (embedding <=> (SELECT tencentdb_ai.get_embedding('bge-m3', %s))) AS relevance
                FROM agent_app.memories
                WHERE user_id = %s
                ORDER BY embedding <=> (SELECT tencentdb_ai.get_embedding('bge-m3', %s))
                LIMIT %s
            """, (query, user_id, query, limit))
            return cur.fetchall()

class RAGSkill:
    ```
"""RAG Search Skill Based on PostgreSQL"""

    def search_knowledge(self, query: str, limit: int = 3):
        """Semantic Search Knowledge Base"""
        with conn.cursor() as cur:
            cur.execute("""
                SELECT title, content,
                       1 - (embedding <=> (SELECT tencentdb_ai.get_embedding('bge-m3', %s))) AS score
                FROM agent_app.knowledge
                ORDER BY embedding <=> (SELECT tencentdb_ai.get_embedding('bge-m3', %s))
                LIMIT %s
            """, (query, query, limit))
            return cur.fetchall()

    def rag_answer(self, query: str):
        """RAG: Search + Generation"""
        # 1. Search for relevant documents.
        docs = self.search_knowledge(query)
        context = '\\n'.join([f'- {d[1]}' for d in docs])

        # 2. Call the large model to generate a response (using parameterized queries).
        with conn.cursor() as cur:
            cur.execute("""
                SELECT tencentdb_ai.chat_completions(%s,
                    Answer the user's question based on the following reference materials. || E'\\nReference Materials:\\n' || %s ||
                    E'\\n\\nUser Question: ' || %s
                )
            """, ('<your-llm-model>', context, query))
            return cur.fetchone()[0]

# WeCom Message Callback Processing
def handle_wecom_message(user_id: str, message: str):
    # 1. Search for user historical memories.
    memories = memory_skill.recall_memories(user_id, message)

    # 2. Build the context.
    context = '\\n'.join([f'[Memory] {m[0]}' for m in memories])

    # 3. RAG Knowledge Search
    knowledge = rag_skill.search_knowledge(message)

    # 4. Generate the response.
    answer = generate_answer(message, context, knowledge)

    # 5. Store the current interaction as a new memory.
    memory_skill.store_memory(user_id, f'User Question: {message}, Answer: {answer[:100]}')

    return answer