# Psyche Domain: Data Models & Database Schemas

This document provides a comprehensive inventory of all database schemas, data
models, and storage systems in the Serwaa codebase.

## Storage Overview

| Storage Type | Technology | Purpose                        |
| ------------ | ---------- | ------------------------------ |
| PostgreSQL   | v15+       | Primary relational data        |
| Qdrant       | v1.7+      | Vector embeddings for RAG      |
| Redis        | v7+        | Session state, caching         |
| MinIO/S3     | -          | Object storage (models, media) |

---

## 1. PostgreSQL Schemas

### Required Extensions

```sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";    -- UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";      -- Cryptographic functions
CREATE EXTENSION IF NOT EXISTS "vector";        -- pgvector for embeddings
CREATE EXTENSION IF NOT EXISTS "pg_trgm";       -- Text similarity
CREATE EXTENSION IF NOT EXISTS "btree_gin";     -- GIN index support
CREATE EXTENSION IF NOT EXISTS "btree_gist";    -- GIST index support
```

### Database Namespaces

| Schema      | Purpose                     |
| ----------- | --------------------------- |
| `persona`   | Persona management          |
| `knowledge` | Knowledge base documents    |
| `sessions`  | Session management          |
| `analytics` | Analytics and metrics       |
| `audit`     | Audit trail and change logs |

### Audit Schema

**Table: `audit.change_log`**

| Column     | Type        | Description          |
| ---------- | ----------- | -------------------- |
| id         | UUID        | Primary key          |
| table_name | TEXT        | Tracked table name   |
| operation  | TEXT        | INSERT/UPDATE/DELETE |
| old_data   | JSONB       | Previous values      |
| new_data   | JSONB       | New values           |
| changed_by | TEXT        | User who made change |
| changed_at | TIMESTAMPTZ | Timestamp            |
| session_id | UUID        | Associated session   |
| ip_address | INET        | Client IP            |
| user_agent | TEXT        | Client user agent    |

**Indexes:**

- `idx_audit_change_log_table` on `table_name`
- `idx_audit_change_log_operation` on `operation`
- `idx_audit_change_log_changed_at` on `changed_at`
- `idx_audit_change_log_changed_by` on `changed_by`

### Automatic Functions

**1. Timestamp Update Trigger:**

```sql
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';
```

**2. Audit Trail Trigger:**

```sql
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit.change_log (
        table_name, operation, old_data, new_data,
        changed_by, changed_at
    ) VALUES (
        TG_TABLE_NAME, TG_OP,
        row_to_json(OLD), row_to_json(NEW),
        current_user, CURRENT_TIMESTAMP
    );
    RETURN NEW;
END;
$$ language 'plpgsql';
```

---

## 2. Pydantic Data Models

### Base Models

**TimestampMixin:**

```python
class TimestampMixin(BaseModel):
    created_at: datetime  # ISO 8601 format
    updated_at: datetime  # ISO 8601 format
```

**PaginationMeta:**

```python
class PaginationMeta(BaseModel):
    total: int           # Total records (≥0)
    page: int            # Current page (≥1)
    per_page: int        # Items per page (1-100)
    total_pages: int     # Total pages (≥0)
    has_next: bool       # Has next page
    has_prev: bool       # Has previous page
```

**Standard Responses:**

```python
class SuccessResponse[T](BaseModel):
    data: T
    meta: dict[str, Any] = {}

class ErrorDetail(BaseModel):
    code: str            # e.g., "VALIDATION_ERROR"
    message: str         # Human-readable
    details: dict[str, Any] = {}

class ErrorResponse(BaseModel):
    error: ErrorDetail

class DeleteResponse(BaseModel):
    deleted: bool
    id: str
```

### Session Models

**SessionStatus Enum:**

| Value      | Description          |
| ---------- | -------------------- |
| PENDING    | Created, not started |
| CONNECTING | Joining meeting      |
| ACTIVE     | Session in progress  |
| PAUSED     | Temporarily paused   |
| ENDED      | Session completed    |
| ERROR      | Error state          |

**MeetingPlatform Enum:**

| Value           | Description     |
| --------------- | --------------- |
| ZOOM            | Zoom meetings   |
| GOOGLE_MEET     | Google Meet     |
| MICROSOFT_TEAMS | Microsoft Teams |
| WEBEX           | Cisco Webex     |
| CUSTOM          | Custom platform |

**SessionCreate:**

```python
class SessionCreate(BaseModel):
    persona_id: str          # Required, 1-50 chars
    meeting_url: str | None  # Optional, max 500
    platform: MeetingPlatform | None  # Auto-detected
    config: dict[str, Any] = {}
    metadata: dict[str, Any] = {}
```

**SessionResponse:**

```python
class SessionResponse(TimestampMixin):
    id: str
    persona_id: str
    status: SessionStatus
    meeting_url: str | None
    platform: MeetingPlatform | None
    started_at: datetime | None
    ended_at: datetime | None
    duration_seconds: int | None  # ≥0
    message_count: int = 0
    config: dict[str, Any]
    metadata: dict[str, Any]
```

### Persona Models

**PersonalityConfig:**

```python
class PersonalityConfig(BaseModel):
    formality: float = 0.5       # 0.0-1.0
    friendliness: float = 0.7    # 0.0-1.0
    verbosity: float = 0.5       # 0.0-1.0
    humor: float = 0.3           # 0.0-1.0
    empathy: float = 0.7         # 0.0-1.0
    assertiveness: float = 0.5   # 0.0-1.0
```

**VoiceConfig:**

```python
class VoiceConfig(BaseModel):
    voice_id: str = "default"    # Max 100 chars
    provider: str = "elevenlabs" # TTS provider
    speed: float = 1.0           # 0.5-2.0
    pitch: float = 1.0           # 0.5-2.0
    custom_voice_enabled: bool = False
```

**AvatarConfig:**

```python
class AvatarConfig(BaseModel):
    model_id: str | None         # Max 100 chars
    style: str = "realistic"
    background: str = "default"
    camera_position: str = "center"
```

**PersonaCreate:**

```python
class PersonaCreate(BaseModel):
    name: str                    # 1-100 chars, required
    description: str = ""        # Max 1000 chars
    system_prompt: str = ""      # Max 10000 chars
    personality: PersonalityConfig = PersonalityConfig()
    voice: VoiceConfig = VoiceConfig()
    avatar: AvatarConfig = AvatarConfig()
    expertise: list[str] = []    # Max 20 items
    languages: list[str] = []    # ISO 639-1, max 10
    metadata: dict[str, Any] = {}
```

**PersonaResponse:**

```python
class PersonaResponse(TimestampMixin):
    id: str
    organization_id: str
    name: str
    description: str
    system_prompt: str
    personality: PersonalityConfig
    voice: VoiceConfig
    avatar: AvatarConfig
    expertise: list[str]
    languages: list[str]
    is_active: bool
    training_status: str = "not_started"
    metadata: dict[str, Any]
```

### Knowledge Document Models

**DocumentStatus Enum:**

| Value      | Description          |
| ---------- | -------------------- |
| PENDING    | Awaiting processing  |
| PROCESSING | Being processed      |
| READY      | Available for search |
| ERROR      | Processing failed    |

**DocumentType Enum:**

| Value    | Extensions  |
| -------- | ----------- |
| TEXT     | .txt        |
| PDF      | .pdf        |
| WORD     | .docx, .doc |
| HTML     | .html, .htm |
| MARKDOWN | .md         |
| CSV      | .csv        |
| JSON     | .json       |

**KnowledgeDocumentCreate:**

```python
class KnowledgeDocumentCreate(BaseModel):
    title: str               # 1-200 chars, required
    content: str | None      # Max 1M chars
    source_url: str | None   # Max 500 chars
    document_type: DocumentType = DocumentType.TEXT
    persona_ids: list[str] = []  # Access control
    tags: list[str] = []     # Max 20 tags
    metadata: dict[str, Any] = {}
```

**KnowledgeDocumentResponse:**

```python
class KnowledgeDocumentResponse(TimestampMixin):
    id: str
    organization_id: str
    title: str
    document_type: DocumentType
    source_url: str | None
    status: DocumentStatus
    chunk_count: int         # ≥0
    token_count: int         # ≥0
    size_bytes: int          # ≥0
    persona_ids: list[str]
    tags: list[str]
    error_message: str | None
    metadata: dict[str, Any]
```

**KnowledgeSearchRequest:**

```python
class KnowledgeSearchRequest(BaseModel):
    query: str               # 1-1000 chars, required
    persona_id: str | None   # Filter by persona
    tags: list[str] | None   # Filter by tags
    top_k: int = 5           # 1-20 results
```

**KnowledgeSearchResult:**

```python
class KnowledgeSearchResult(BaseModel):
    document_id: str
    document_title: str
    chunk_id: str
    content: str
    score: float             # 0.0-1.0 similarity
    metadata: dict[str, Any]
```

---

## 3. Qdrant Vector Database

### Collection Configuration

**Collection:** `serwaa_knowledge` (configurable)

| Parameter       | Value             |
| --------------- | ----------------- |
| Dimensions      | 1024 (Voyage-3.5) |
| Distance Metric | Cosine            |
| Index Type      | HNSW              |

### Supported Embedding Models

| Model                  | Dimensions | Max Tokens | Provider |
| ---------------------- | ---------- | ---------- | -------- |
| Voyage-3.5-lite        | 1024       | 16,000     | Voyage   |
| Voyage-3               | 1024       | 32,000     | Voyage   |
| text-embedding-ada     | 1536       | 8,191      | OpenAI   |
| text-embedding-3-small | 1536       | 8,191      | OpenAI   |
| text-embedding-3-large | 3072       | 8,191      | OpenAI   |
| embed-english          | 1024       | 512        | Cohere   |
| embed-multilingual     | 1024       | 512        | Cohere   |
| bge-m3                 | 1024       | 8,192      | BGE      |
| bge-large              | 1024       | 512        | BGE      |

### Vector Record Schema

```python
class VectorRecord(BaseModel):
    id: str                  # UUID
    embedding: list[float]   # Vector values
    text: str                # Original text
    text_hash: str           # MD5 hash
    document_id: str         # Source document
    chunk_id: str            # Chunk identifier
    chunk_index: int         # Position in document
    metadata: dict[str, Any]
    namespace: str = "default"
    index_name: str
    created_at: datetime
    updated_at: datetime
```

### Search Request/Response

**SearchRequest:**

```python
class SearchRequest(BaseModel):
    query_embedding: list[float]
    query_text: str | None
    top_k: int = 10
    namespace: str = "default"
    metadata_filters: dict[str, Any] = {}
    include_metadata: bool = True
    include_values: bool = False
    min_score: float = 0.0
```

**SearchResponse:**

```python
class SearchResponse(BaseModel):
    results: list[SearchResult]
    total_results: int
    query_time_ms: float
    namespace: str

class SearchResult(BaseModel):
    id: str
    score: float             # Similarity score
    text: str
    document_id: str
    chunk_id: str
    metadata: dict[str, Any]
    embedding: list[float] | None
```

---

## 4. Redis Data Structures

### Connection Configuration

| Parameter       | Value            |
| --------------- | ---------------- |
| Host            | localhost        |
| Port            | 6379             |
| Database        | 0 (configurable) |
| Max Connections | 50               |
| Session DB      | 1                |
| Session TTL     | 3600 seconds     |

### Session State

**Key Pattern:** `session:{session_id}`

```python
class SessionState:
    session_id: str          # UUID
    user_id: str
    persona_id: str
    status: SessionStatus
    started_at: datetime
    last_activity: datetime
    message_count: int
    config: dict[str, Any]
    metadata: dict[str, Any]
```

**Operations:**

```python
# Store session (with TTL)
await redis.setex(f"session:{session_id}", ttl, json.dumps(state))

# Retrieve session
state = json.loads(await redis.get(f"session:{session_id}"))

# Check existence
exists = await redis.exists(f"session:{session_id}")

# Delete session
await redis.delete(f"session:{session_id}")
```

### Semantic Cache

**Cache Entry:**

```python
class CacheEntry(BaseModel):
    cache_key: str           # SHA256(query + context)
    query: str               # Original query
    response: str            # Cached response
    context: dict[str, Any]  # Query context
    model: str               # Model used
    metadata: dict[str, Any]
    created_at: str          # ISO timestamp
    ttl_seconds: int
    access_count: int
    last_accessed: str | None
```

**Operations:**

```python
# Store cache entry
await redis.setex(cache_key, ttl_seconds, json.dumps(entry))

# Retrieve entry
entry = json.loads(await redis.get(cache_key))

# Touch entry (LRU)
await redis.expire(cache_key, ttl_seconds)

# Pattern search
keys = await redis.keys("cache:*")
```

### Cache Statistics

```python
class CacheStats(BaseModel):
    total_entries: int
    hits: int
    misses: int
    hit_rate: float          # Percentage
    average_similarity: float
    average_latency_ms: float
    total_savings_usd: float
    memory_usage_mb: float
```

---

## 5. Memory Hierarchy System

### Memory Tiers

| Tier           | Max Tokens | Max Blocks | Eviction | Purpose        |
| -------------- | ---------- | ---------- | -------- | -------------- |
| IN_CONTEXT     | 8,000      | 100        | 85%      | Active context |
| WORKING        | 32,000     | 500        | 85%      | Recently used  |
| OUT_OF_CONTEXT | 128,000    | 2,000      | 85%      | Paged out      |
| ARCHIVAL       | 1,000,000  | 10,000     | 95%      | Long-term      |

### Memory Types

| Type         | Description               |
| ------------ | ------------------------- |
| CORE         | Core personality/identity |
| PERSONA      | User/assistant persona    |
| CONVERSATION | Conversation history      |
| FACT         | Factual knowledge         |
| SKILL        | Learned skills            |
| PREFERENCE   | User preferences          |
| CONTEXT      | Contextual information    |
| SUMMARY      | Summarized content        |
| REFLECTION   | Self-reflections          |

### Memory Block Schema

```python
class MemoryBlock(BaseModel):
    block_id: str            # UUID
    tier: MemoryTier
    memory_type: MemoryType
    content: str

    # Timing
    created_at: datetime
    last_accessed: datetime
    access_count: int

    # Scoring
    importance_score: float  # 0.0-1.0
    relevance_decay_rate: float = 0.1
    token_count: int

    # Relationships
    parent_id: str | None
    child_ids: list[str] = []
    linked_ids: list[str] = []

    # State
    is_compressed: bool = False
    is_encrypted: bool = False
    checksum: str            # MD5

    def calculate_current_relevance(self) -> float:
        """Calculate relevance with temporal decay."""
        hours_since_access = (now - self.last_accessed).hours
        decay_factor = math.exp(-self.relevance_decay_rate * hours_since_access)
        return self.importance_score * decay_factor
```

### Context Window Configuration

```python
class ContextWindowConfig(BaseModel):
    # Tier capacities (tokens)
    in_context_max_tokens: int = 8000
    working_max_tokens: int = 32000
    out_of_context_max_tokens: int = 128000
    archival_max_tokens: int = 1000000

    # Thresholds
    eviction_threshold: float = 0.85
    critical_threshold: float = 0.95
    compaction_threshold: float = 0.75

    # Timing
    check_interval_seconds: int = 60
    compaction_interval_seconds: int = 3600

    # Paging
    page_size_tokens: int = 1000
    max_pages_per_operation: int = 10

    # Alerts
    enable_alerts: bool = True
    alert_on_eviction: bool = True
    alert_on_critical: bool = True
```

### Memory Tools

**core_memory_append:**

```python
# Add to in-context memory
input: {
    section: str      # persona, human, facts, context, skills
    content: str      # Max 10,000 chars
    importance: float # 0.0-1.0
}
output: { block_id: str, section: str }
```

**core_memory_replace:**

```python
# Replace in-context memory
input: {
    new_content: str      # Max 10,000 chars
    old_content: str      # Optional, for matching
    block_id: str         # Optional, direct replacement
    section: str          # Optional, filter by section
}
output: { replaced_count: int }
```

**archival_memory_insert:**

```python
# Insert into archival storage
input: {
    content: str          # Max 50,000 chars
    tags: list[str]       # Searchable tags
    memory_type: str      # fact, skill, preference, reflection
    importance: float     # 0.0-1.0
}
output: { block_id: str, tags: list, memory_type: str }
```

**archival_memory_search:**

```python
# Search archival memory
input: {
    query: str            # Search query
    tags: list[str]       # Optional filter
    memory_type: str      # Optional filter
    limit: int            # Max 50
    page_in: bool         # Page results to working memory
}
output: { block_ids: list, snippets: list, relevance_scores: list }
```

---

## 6. Persistent Storage

### Stored Memory Record

**Table Schema (PostgreSQL):**

| Column            | Type        | Description                |
| ----------------- | ----------- | -------------------------- |
| record_id         | UUID        | Primary key                |
| user_id           | VARCHAR     | User reference             |
| session_id        | UUID        | Optional session ref       |
| block_id          | VARCHAR     | Memory block reference     |
| tier              | VARCHAR     | MemoryTier name            |
| memory_type       | VARCHAR     | MemoryType name            |
| content           | TEXT        | Content (may be encrypted) |
| metadata_json     | TEXT        | JSON metadata              |
| importance_score  | FLOAT       | Importance (0-1)           |
| token_count       | INTEGER     | Token count                |
| created_at        | TIMESTAMPTZ | Creation time              |
| last_accessed     | TIMESTAMPTZ | Last access time           |
| access_count      | INTEGER     | Access count               |
| is_encrypted      | BOOLEAN     | Encryption flag            |
| encryption_key_id | VARCHAR     | Key reference              |
| checksum          | VARCHAR     | MD5 checksum               |
| is_deleted        | BOOLEAN     | Soft delete flag           |

**Indexes:**

- `(user_id, created_at)` - Fast user memory retrieval
- `(user_id, tier)` - Tier-filtered queries
- `(user_id, memory_type)` - Type-filtered queries
- `(is_deleted)` - Filter active records

### User Memory Profile

```python
class UserMemoryProfile(BaseModel):
    user_id: str                     # Primary key
    encryption_enabled: bool = True
    encryption_key_id: str | None
    memory_quota_tokens: int = 1000000
    used_tokens: int = 0
    total_sessions: int = 0
    created_at: datetime
    last_session_at: datetime | None
    preferences: dict[str, Any] = {}
```

### Encryption Configuration

| Parameter      | Value                |
| -------------- | -------------------- |
| Algorithm      | Fernet (AES-128-CBC) |
| Key Derivation | PBKDF2-HMAC-SHA256   |
| Salt Length    | 16 bytes             |
| Iterations     | 480,000              |
| Output Length  | 32 bytes (256 bits)  |
| Key Rotation   | 90 days              |

### Retention Policy

| Data Type       | Retention Period      |
| --------------- | --------------------- |
| Archival Memory | 365 days              |
| Deleted Records | 30 days (soft delete) |
| Session State   | 24 hours (Redis TTL)  |

---

## 7. Batch Processing Models

### Request Priority

| Priority | Value | Use Case           |
| -------- | ----- | ------------------ |
| LOW      | 0     | Background tasks   |
| NORMAL   | 1     | Standard requests  |
| HIGH     | 2     | Important requests |
| URGENT   | 3     | Time-critical      |

### BatchRequest

```python
class BatchRequest(BaseModel):
    id: str
    model: str
    request_type: str = "completion"
    prompt: str
    max_tokens: int = 1024
    temperature: float = 0.7
    priority: RequestPriority = RequestPriority.NORMAL
    metadata: dict[str, Any] = {}
    context: dict[str, Any] = {}  # persona, session
```

### BatchResponse

```python
class BatchResponse(BaseModel):
    request_id: str
    content: str
    model: str
    usage: dict[str, int]    # Token counts
    finish_reason: str = "stop"
    latency_ms: float
    metadata: dict[str, Any]
```

### BatchStats

```python
class BatchStats(BaseModel):
    total_batches: int
    total_requests: int
    average_batch_size: float
    average_latency_ms: float
    success_rate: float      # Percentage
    requests_per_second: float
    current_queue_size: int
```

---

## 8. Environment Configuration

### Database URLs

```bash
# PostgreSQL
DATABASE_URL=postgresql://serwaa:password@localhost:5432/serwaa
DATABASE_POOL_SIZE=20
DATABASE_MAX_OVERFLOW=10
DATABASE_READ_URL=  # Optional read replica

# Redis
REDIS_URL=redis://localhost:6379/0
REDIS_PASSWORD=  # Optional
REDIS_MAX_CONNECTIONS=50
REDIS_SESSION_DB=1
REDIS_SESSION_TTL=3600

# Qdrant
QDRANT_URL=http://localhost:6333
QDRANT_API_KEY=  # Optional
QDRANT_COLLECTION_NAME=serwaa_knowledge

# Object Storage
S3_ENDPOINT=http://localhost:9000
S3_ACCESS_KEY=minioadmin
S3_SECRET_KEY=minioadmin
S3_BUCKET_NAME=serwaa
S3_REGION=us-east-1
```

---

## Summary

### Storage Components

| Component  | Purpose                | Technology     |
| ---------- | ---------------------- | -------------- |
| PostgreSQL | Relational data, audit | PostgreSQL 15+ |
| Qdrant     | Vector search          | Qdrant 1.7+    |
| Redis      | Session state, cache   | Redis 7+       |
| MinIO/S3   | Object storage         | S3-compatible  |

### Data Model Categories

| Category  | Models                                            |
| --------- | ------------------------------------------------- |
| Sessions  | SessionCreate, SessionResponse, SessionStatus     |
| Personas  | PersonaCreate, PersonaResponse, PersonalityConfig |
| Knowledge | KnowledgeDocumentCreate, KnowledgeSearchRequest   |
| Memory    | MemoryBlock, MemoryTier, MemoryType               |
| Vectors   | VectorRecord, SearchRequest, SearchResponse       |
| Cache     | CacheEntry, CacheStats                            |
| Batch     | BatchRequest, BatchResponse, BatchStats           |

### Key Implementation Files

| Component          | Path                                                                                   |
| ------------------ | -------------------------------------------------------------------------------------- |
| Base Models        | `services/api-gateway/src/api_gateway/models/base.py`                                  |
| Session Models     | `services/api-gateway/src/api_gateway/models/sessions.py`                              |
| Persona Models     | `services/api-gateway/src/api_gateway/models/personas.py`                              |
| Knowledge Models   | `services/api-gateway/src/api_gateway/models/knowledge.py`                             |
| Migrations         | `services/api-gateway/src/api_gateway/db/migrate.py`                                   |
| Vector Stores      | `services/knowledge-base/src/knowledge_base/embedding/stores.py`                       |
| Semantic Cache     | `services/orchestrator/src/orchestrator/caching/semantic_cache.py`                     |
| Memory Context     | `services/learning-system/src/learning_system/persistent_memory/context_management.py` |
| Memory Persistence | `services/learning-system/src/learning_system/persistent_memory/persistence.py`        |
| Memory Tools       | `services/learning-system/src/learning_system/persistent_memory/memory_tools.py`       |

---

_Document generated: January 2026_ _Task: 24.1.1.3 - Inventory all database
schemas_
