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#
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_tableontable_nameidx_audit_change_log_operationonoperationidx_audit_change_log_changed_atonchanged_atidx_audit_change_log_changed_byonchanged_by
Automatic Functions#
1. Timestamp Update Trigger:
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:
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:
class TimestampMixin(BaseModel):
created_at: datetime # ISO 8601 format
updated_at: datetime # ISO 8601 format
PaginationMeta:
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:
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:
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:
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:
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:
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:
class AvatarConfig(BaseModel):
model_id: str | None # Max 100 chars
style: str = "realistic"
background: str = "default"
camera_position: str = "center"
PersonaCreate:
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:
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 |
| WORD | .docx, .doc |
| HTML | .html, .htm |
| MARKDOWN | .md |
| CSV | .csv |
| JSON | .json |
KnowledgeDocumentCreate:
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:
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:
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:
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#
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:
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:
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}
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:
# 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:
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:
# 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#
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#
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#
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:
# 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:
# 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:
# 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:
# 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#
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#
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#
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#
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#
# 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