Eve library

Psyche Database Migration Strategy

Other

Revision 1

On this page

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:

  1. Deploy new schema alongside old - Create new tables with _new suffix
  2. Dual-write - Write to both old and new tables
  3. Backfill - Migrate historical data in batches
  4. Verify - Validate data consistency
  5. Switch - Rename tables atomically
  6. Cleanup - Remove old tables after verification period

Phase 2: Data Migration#

Data is migrated using ETL jobs that:

  1. Read from Serwaa tables
  2. Transform to Psyche model format
  3. Load into Psyche tables
  4. 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#

  1. Copy alembic.ini to your service:
bash
cp $(python -c "import psyche_database.migrations; print(psyche_database.migrations.ALEMBIC_INI)") ./alembic.ini
  1. Set database URL:
bash
export DATABASE_URL="postgresql://user:pass@localhost:5432/psyche"
  1. 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_vector column (vector(1536))
  • Creates HNSW index for similarity search
  • Adds helper functions:
    • search_memories_by_embedding() - Semantic search
    • find_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:

  1. Check migration logs: alembic history --verbose
  2. Verify database state: alembic current
  3. Review error logs in application
  4. Contact platform team

References#