Skill System Memory

**PostgreSQL Schema Design for AI Agent Memory System**

```sql
-- Core tables for structured memory storage
CREATE TABLE user_profiles (
    user_id SERIAL PRIMARY KEY,
    agent_id VARCHAR(255) NOT NULL,
    preferences JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE conversations (
    conversation_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES user_profiles(user_id),
    agent_id VARCHAR(255) NOT NULL,
    context JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE memory_entries (
    entry_id SERIAL PRIMARY KEY,
    conversation_id INTEGER REFERENCES conversations(conversation_id),
    memory_type VARCHAR(50) NOT NULL, -- e.g., 'decision', 'preference', 'context'
    content TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    similarity_vector VECTOR(1536) -- For vector similarity search
);

-- Indexes for optimized retrieval
CREATE INDEX idx_memory_entries_similarity ON memory_entries USING ivfflat (similarity_vector vector_cosine_ops);
CREATE INDEX idx_conversations_agent ON conversations(agent_id);
CREATE INDEX idx_memory_entries_type ON memory_entries(memory_type);
```

**Key Features:**
- **Structured Storage**: JSONB columns for flexible data handling (user preferences, conversation context)
- **Vector Search**: Enables semantic similarity for memory retrieval
- **Time-Based Filtering**: Optimized for retrieving recent interactions
- **Scalable Schema**: Supports incremental updates and growth

**Example Query:**
```sql
-- Retrieve past decisions for an agent
SELECT content 
FROM memory_entries 
WHERE agent_id = 'customer_support_bot' 
  AND memory_type = 'decision'
  AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 5;
```