This document outlines the database migration strategy for migrating from Serwaa's dataclass-based schema to Psyche's SQLAlchemy ORM models using Alembic.
Overview#
Source: Serwaa Schema#
The Serwaa persona-system uses Python dataclasses for schema definition, organized across multiple modules:
- identity/types.py - PersonaIdentity, PersonaVersion, PersonaTemplate
- personality/types.py - BigFivePersonality, CommunicationParameters
- assets/types.py - AvatarModel, VoiceProfile, TrainingJob
- expertise/types.py - DomainExpertise, KnowledgeBoundary
- memory/types.py - PersonaFact, OpinionAnchor, StoryEvent
- constraints/types.py - BehavioralConstraintsConfig, VocabularyConstraint
Target: Psyche Schema#
Psyche uses SQLAlchemy ORM models with proper relationships, indexes, and
constraints defined in psyche_database.models:
- Persona - Core persona definition (consolidated from Serwaa identity, personality, expertise)
- PersonaVersion - Version history with snapshots
- PersonaTemplate - Reusable templates
- Session - Active interaction sessions
- SessionStats - Session metrics
- Memory - Semantic memory with pgvector embeddings
- Conversation - Conversation threads
- Message - Individual messages
- ToolExecution - Tool call tracking
Migration Approach#
Phase 1: Schema Migration (Zero Downtime)#
We use a shadow table approach for zero-downtime migration:
- Deploy new schema alongside old - Create new tables with
_newsuffix - Dual-write - Write to both old and new tables
- Backfill - Migrate historical data in batches
- Verify - Validate data consistency
- Switch - Rename tables atomically
- Cleanup - Remove old tables after verification period
Phase 2: Data Migration#
Data is migrated using ETL jobs that:
- Read from Serwaa tables
- Transform to Psyche model format
- Load into Psyche tables
- Verify row counts and checksums
Alembic Setup#
Installation#
bash
# Install with migrations extra
poetry add "psyche-database[migrations]"
# Or add to pyproject.toml
# psyche-database = {extras = ["migrations"], version = "^0.1.0"}
Configuration#
- Copy alembic.ini to your service:
bash
cp $(python -c "import psyche_database.migrations; print(psyche_database.migrations.ALEMBIC_INI)") ./alembic.ini
- Set database URL:
bash
export DATABASE_URL="postgresql://user:pass@localhost:5432/psyche"
- Or update alembic.ini:
ini
sqlalchemy.url = postgresql+asyncpg://user:pass@localhost:5432/psyche
Running Migrations#
bash
# Apply all migrations
alembic upgrade head
# Apply specific migration
alembic upgrade 001_initial
# Rollback last migration
alembic downgrade -1
# Rollback all migrations
alembic downgrade base
# Show current version
alembic current
# Show migration history
alembic history --verbose
# Generate SQL without applying
alembic upgrade head --sql > migration.sql
Migration Files#
Initial Schema (001_initial)#
Creates core tables:
| Table | Description | Indexes |
|---|---|---|
| personas | Core persona definitions | name, role, status, org_id, tags |
| persona_versions | Version history | persona_id, is_current |
| persona_templates | Reusable templates | role, category, is_system |
| sessions | Active sessions | user_id, persona_id, state |
| session_stats | Session metrics | session_id (unique) |
| memories | Semantic memory storage | user_id, type, scope, tags |
| conversations | Conversation threads | session_id, user_id, status |
| messages | Individual messages | conversation_id, role |
| tool_executions | Tool call tracking | session_id, tool_name, status |
pgvector Support (002_pgvector)#
Adds vector search capabilities:
- Enables pgvector extension
- Adds
embedding_vectorcolumn (vector(1536)) - Creates HNSW index for similarity search
- Adds helper functions:
search_memories_by_embedding()- Semantic searchfind_related_memories()- Find similar memories
Data Migration Scripts#
Persona Migration#
python
"""Migrate personas from Serwaa to Psyche."""
import asyncio
from psyche_database import create_postgres_client_from_env, Persona
async def migrate_personas():
client = await create_postgres_client_from_env()
# Read from Serwaa
old_personas = await client.query("""
SELECT * FROM persona.identity
ORDER BY created_at
""")
# Transform and insert
for row in old_personas.rows:
persona = Persona(
id=row["id"],
name=row["name"],
display_name=row["display_name"],
slug=row["name"].lower().replace(" ", "-"),
role=row["role"],
status=row["status"],
personality=await get_personality(client, row["id"]),
expertise=await get_expertise(client, row["id"]),
constraints=await get_constraints(client, row["id"]),
created_at=row["created_at"],
updated_at=row["updated_at"],
)
# ... insert into new table
await client.close()
Memory Migration with Embeddings#
python
"""Migrate memories with embedding conversion."""
import numpy as np
from pgvector.sqlalchemy import Vector
async def migrate_memories():
client = await create_postgres_client_from_env()
# Process in batches
offset = 0
batch_size = 1000
while True:
memories = await client.query("""
SELECT * FROM persona.memory_facts
ORDER BY created_at
LIMIT $1 OFFSET $2
""", [batch_size, offset])
if not memories.rows:
break
for row in memories.rows:
# Convert JSONB embedding to pgvector
embedding = None
if row.get("embedding"):
embedding = np.array(row["embedding"], dtype=np.float32)
await client.execute("""
INSERT INTO memories (
id, user_id, persona_id, memory_type, content,
embedding_vector, importance, created_at
) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
""", [
row["id"],
row["user_id"],
row["persona_id"],
row["category"], # Serwaa uses category, Psyche uses memory_type
row["value"],
embedding,
row["importance"],
row["created_at"],
])
offset += batch_size
print(f"Migrated {offset} memories...")
await client.close()
Zero-Downtime Migration Procedure#
Step 1: Prepare#
sql
-- Create new tables with _new suffix
CREATE TABLE personas_new (LIKE personas INCLUDING ALL);
CREATE TABLE memories_new (LIKE memories INCLUDING ALL);
-- ... etc
Step 2: Enable Dual-Write#
Update application to write to both old and new tables:
python
async def create_persona(data: PersonaCreate) -> Persona:
persona = Persona(**data.dict())
# Write to old table (existing behavior)
await old_client.insert("persona.identity", persona)
# Write to new table
await new_client.insert("personas_new", persona)
return persona
Step 3: Backfill Historical Data#
bash
# Run backfill script
python -m psyche_database.migrations.backfill --table personas --batch-size 1000
# Verify counts
psql -c "SELECT (SELECT count(*) FROM personas_new) = (SELECT count(*) FROM persona.identity)"
Step 4: Verify Data#
sql
-- Count verification
SELECT 'personas' as table_name,
(SELECT count(*) FROM persona.identity) as old_count,
(SELECT count(*) FROM personas_new) as new_count;
-- Checksum verification (sample)
SELECT 'personas' as table_name,
md5(string_agg(id::text, ',' ORDER BY id)) as checksum
FROM (SELECT id FROM persona.identity ORDER BY id LIMIT 10000) t
UNION ALL
SELECT 'personas_new' as table_name,
md5(string_agg(id::text, ',' ORDER BY id)) as checksum
FROM (SELECT id FROM personas_new ORDER BY id LIMIT 10000) t;
Step 5: Atomic Switch#
sql
-- In a transaction
BEGIN;
-- Rename old tables
ALTER TABLE persona.identity RENAME TO identity_old;
-- Rename new tables
ALTER TABLE personas_new RENAME TO personas;
-- Update foreign keys
ALTER TABLE sessions DROP CONSTRAINT fk_sessions_persona_id;
ALTER TABLE sessions ADD CONSTRAINT fk_sessions_persona_id
FOREIGN KEY (persona_id) REFERENCES personas(id);
COMMIT;
Step 6: Cleanup (After Verification Period)#
sql
-- After 7 days of verification
DROP TABLE IF EXISTS identity_old;
DROP TABLE IF EXISTS memories_old;
-- ... etc
Rollback Procedure#
If issues are detected:
sql
-- Immediate rollback
BEGIN;
ALTER TABLE personas RENAME TO personas_failed;
ALTER TABLE identity_old RENAME TO persona.identity;
COMMIT;
-- Disable dual-write in application
-- Investigate and fix issues
Monitoring#
Migration Progress Dashboard#
Track these metrics during migration:
| Metric | Query |
|---|---|
| Old table row count | SELECT count(*) FROM persona.identity |
| New table row count | SELECT count(*) FROM personas_new |
| Dual-write lag | old_count - new_count |
| Migration rate | rows/second |
| Error rate | failed_inserts/total_inserts |
Alerts#
Configure alerts for:
- Row count divergence > 100
- Dual-write failures
- Migration script errors
- High replication lag
Schema Mapping Reference#
Serwaa → Psyche Field Mapping#
| Serwaa Field | Psyche Field | Notes |
|---|---|---|
identity.id |
personas.id |
Direct mapping |
identity.name |
personas.name |
Direct mapping |
identity.display_name |
personas.display_name |
Direct mapping |
identity.role |
personas.role |
Enum values unchanged |
identity.status |
personas.status |
Enum values unchanged |
personality_config.* |
personas.personality |
Flattened to JSONB |
expertise_config.* |
personas.expertise |
Flattened to JSONB |
constraints_config.* |
personas.constraints |
Flattened to JSONB |
memory_facts.category |
memories.memory_type |
Renamed |
memory_facts.value |
memories.content |
Renamed |
memory_facts.embedding |
memories.embedding_vector |
JSONB → pgvector |
Type Conversions#
| Serwaa Type | Psyche Type | Conversion |
|---|---|---|
List[str] (tags) |
ARRAY(String) |
Direct |
Dict[str, Any] |
JSONB |
Direct |
datetime |
DateTime(tz=True) |
Ensure timezone |
List[float] (embedding) |
vector(1536) |
pgvector conversion |
Enum |
String(50) |
.value extraction |
Testing#
Pre-Migration Tests#
bash
# Run migration tests
pytest tests/migrations/ -v
# Test against empty database
pytest tests/migrations/test_fresh_install.py
# Test rollback
pytest tests/migrations/test_rollback.py
Post-Migration Validation#
bash
# Verify schema
alembic check
# Run integration tests
pytest tests/integration/test_psyche_models.py
# Load test
locust -f tests/load/locustfile.py --headless -u 100 -r 10
Timeline#
| Phase | Duration | Description |
|---|---|---|
| Schema deployment | 1 day | Deploy new tables |
| Dual-write enabled | 1 day | Enable writes to both tables |
| Backfill | 2-3 days | Migrate historical data |
| Verification | 2 days | Validate data integrity |
| Cutover | 1 hour | Atomic table rename |
| Monitoring | 7 days | Monitor for issues |
| Cleanup | 1 day | Remove old tables |
Total estimated duration: 2 weeks
Support#
For migration issues:
- Check migration logs:
alembic history --verbose - Verify database state:
alembic current - Review error logs in application
- Contact platform team