# Psyche Database Migration Strategy

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
```

2. Set database URL:

```bash
export DATABASE_URL="postgresql://user:pass@localhost:5432/psyche"
```

3. 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

- [Alembic Documentation](https://alembic.sqlalchemy.io/)
- [pgvector Documentation](https://github.com/pgvector/pgvector)
- [Zero Downtime Migrations](https://stripe.com/blog/online-migrations)
- [SQLAlchemy ORM Tutorial](https://docs.sqlalchemy.org/en/20/orm/)
