feat(memory): #62-2 Database Schema & Storage Layer #88

New Issue

cardosofelipe · 2026-01-05T00:13:56Z

cardosofelipe commented

2026-01-05 00:13:56 +00:00

Parent Issue

#62 Agent Memory System
Depends on: #62-1

Overview

Create all database tables, migrations, and repository classes for the memory system.

Database Tables

1. `working_memory` - Ephemeral key-value storage

- id (UUID, PK)
- scope_type (ENUM: global/project/agent_type/agent_instance/session)
- scope_id (VARCHAR)
- key (VARCHAR)
- value (JSONB)
- expires_at (TIMESTAMP WITH TZ)
- created_at, updated_at

2. `episodes` - Experiential memories

- id (UUID, PK)
- project_id (UUID, FK)
- agent_instance_id (UUID, FK, nullable)
- agent_type_id (UUID, FK, nullable)
- session_id (VARCHAR)
- task_type, task_description, actions (JSONB)
- context_summary (TEXT)
- outcome (ENUM: success/failure/partial)
- outcome_details, duration_seconds, tokens_used
- lessons_learned (JSONB)
- importance_score (FLOAT)
- embedding (VECTOR(1536))
- occurred_at, created_at, updated_at

3. `facts` - Semantic knowledge

- id (UUID, PK)
- project_id (UUID, FK, nullable)
- subject, predicate, object
- confidence (FLOAT)
- source_episode_ids (UUID[])
- first_learned, last_reinforced
- reinforcement_count
- embedding (VECTOR(1536))
- created_at, updated_at

4. `procedures` - Learned skills

- id (UUID, PK)
- project_id (UUID, FK, nullable)
- agent_type_id (UUID, FK, nullable)
- name, trigger_pattern, steps (JSONB)
- success_count, failure_count
- last_used
- embedding (VECTOR(1536))
- created_at, updated_at

5. `memory_consolidation_log` - Consolidation tracking

- id (UUID, PK)
- consolidation_type (ENUM)
- source_count, result_count
- started_at, completed_at
- status (ENUM)
- error (TEXT, nullable)

Tasks

Create SQLAlchemy models in backend/app/models/memory/
Create Alembic migration with all tables
Add pgvector indexes (HNSW for episodes, facts, procedures)
Create repository classes in backend/app/crud/memory/
Add composite indexes for common query patterns
Unit tests for all repositories (>90% coverage)

Acceptance Criteria

All tables created with correct types and constraints
Foreign key relationships correctly defined
HNSW indexes for vector columns
Repository CRUD operations work correctly
Migration runs without errors
make validate-all passes

Priority

P0 - Required for all memory types

Labels

phase-2, mcp, backend, database, agents

## Parent Issue - #62 Agent Memory System - Depends on: #62-1 ## Overview Create all database tables, migrations, and repository classes for the memory system. ## Database Tables ### 1. `working_memory` - Ephemeral key-value storage ```sql - id (UUID, PK) - scope_type (ENUM: global/project/agent_type/agent_instance/session) - scope_id (VARCHAR) - key (VARCHAR) - value (JSONB) - expires_at (TIMESTAMP WITH TZ) - created_at, updated_at ``` ### 2. `episodes` - Experiential memories ```sql - id (UUID, PK) - project_id (UUID, FK) - agent_instance_id (UUID, FK, nullable) - agent_type_id (UUID, FK, nullable) - session_id (VARCHAR) - task_type, task_description, actions (JSONB) - context_summary (TEXT) - outcome (ENUM: success/failure/partial) - outcome_details, duration_seconds, tokens_used - lessons_learned (JSONB) - importance_score (FLOAT) - embedding (VECTOR(1536)) - occurred_at, created_at, updated_at ``` ### 3. `facts` - Semantic knowledge ```sql - id (UUID, PK) - project_id (UUID, FK, nullable) - subject, predicate, object - confidence (FLOAT) - source_episode_ids (UUID[]) - first_learned, last_reinforced - reinforcement_count - embedding (VECTOR(1536)) - created_at, updated_at ``` ### 4. `procedures` - Learned skills ```sql - id (UUID, PK) - project_id (UUID, FK, nullable) - agent_type_id (UUID, FK, nullable) - name, trigger_pattern, steps (JSONB) - success_count, failure_count - last_used - embedding (VECTOR(1536)) - created_at, updated_at ``` ### 5. `memory_consolidation_log` - Consolidation tracking ```sql - id (UUID, PK) - consolidation_type (ENUM) - source_count, result_count - started_at, completed_at - status (ENUM) - error (TEXT, nullable) ``` ## Tasks - [ ] Create SQLAlchemy models in `backend/app/models/memory/` - [ ] Create Alembic migration with all tables - [ ] Add pgvector indexes (HNSW for episodes, facts, procedures) - [ ] Create repository classes in `backend/app/crud/memory/` - [ ] Add composite indexes for common query patterns - [ ] Unit tests for all repositories (>90% coverage) ## Acceptance Criteria - [ ] All tables created with correct types and constraints - [ ] Foreign key relationships correctly defined - [ ] HNSW indexes for vector columns - [ ] Repository CRUD operations work correctly - [ ] Migration runs without errors - [ ] `make validate-all` passes ## Priority P0 - Required for all memory types ## Labels `phase-2`, `mcp`, `backend`, `database`, `agents`

cardosofelipe closed this issue

2026-01-05 03:26:48 +00:00

cardosofelipe referenced this issue

2026-01-05 10:04:59 +00:00

feat(mcp): Agent Memory System #62

cardosofelipe referenced this issue from a commit

2026-01-06 00:56:28 +00:00

feat(memory): add database schema and storage layer (Issue #88)

cardosofelipe referenced this issue from a commit

2026-01-06 00:56:28 +00:00

fix(memory): address review findings from Issue #88

Sign in to join this conversation.