Eve library

Psyche Domain: Data Models & Database Schemas

Guide

Revision 1

On this page

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