Skip to content

Database Migration System — Implementation Plan

Summary

This plan implements the database migration system designed in docs/designs/v3.1/02-database-migrations.md. MAID currently has no migration system — schema is created inline via CREATE TABLE IF NOT EXISTS in DatabaseManager.initialize() and PostgresDocumentStore.initialize(), and the maid db CLI commands are stubs. This implementation introduces:

  • A custom migration runner built on asyncpg (no SQLAlchemy dependency for migrations)
  • Multi-namespace content pack migration support with dependency resolution
  • Safe JSONB document evolution via a structured JSONBUpdateSpec builder
  • Batched operations with checkpoint-based resume for large datasets
  • Global advisory lock for migration serialization
  • Two-tier security model (trusted first-party Python / untrusted community YAML)
  • Production-ready CLI commands replacing the current stubs
  • Explicit baseline path for existing database deployments

Existing code affected: - packages/maid-engine/src/maid_engine/storage/database.py — DatabaseManager gains from_pool() constructor - packages/maid-engine/src/maid_engine/storage/document_store.py — PostgresDocumentStore gains from_pool() constructor; documents table gains doc_seq column via dedicated migration - packages/maid-engine/src/maid_engine/cli/app.py — db_migrate stub replaced with real implementation - packages/maid-engine/src/maid_engine/plugins/manifest.py — ContentPackManifest extended with migration_requires field - packages/maid-engine/src/maid_engine/config/settings.py — DatabaseSettings gains dsn property (delegates to existing url); new MigrationSettings added

New module created: - packages/maid-engine/src/maid_engine/migrations/ — migration framework (10 modules, down from 15 after simplifications)

Phase 1: Core Types, Protocol, and Exceptions (P0)

1.1 Migration Exception Hierarchy

Package: maid-engine | Priority: P0 | Dependencies: none

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/__init__.py
  • [ ] Module docstring explaining the migration framework
  • [ ] Re-export key public types: Migration, YAMLMigration, ExecutionMode, BaseMigrationContext, MigrationContext
  • [ ] Create packages/maid-engine/src/maid_engine/migrations/exceptions.py
  • [ ] MigrationError(Exception) — base exception for all migration errors
  • [ ] MigrationLoadError(MigrationError) — failed to load/parse migration file
  • [ ] MigrationExecutionError(MigrationError) — migration upgrade/downgrade failed
  • [ ] MigrationDependencyError(MigrationError) — unmet dependency or circular dependency
  • [ ] MigrationLockTimeout(MigrationError) — advisory lock acquisition timed out
  • [ ] MigrationChecksumError(MigrationError) — applied migration checksum mismatch
  • [ ] OwnershipViolationError(MigrationError) — namespace tried to modify another namespace's component (deferred — not enforced in V1)
  • [ ] SecurityError(MigrationError) — trust level violation (e.g., community pack using Python)
  • [ ] CheckpointError(MigrationError) — checkpoint creation/resume failure
  • [ ] All exceptions should include namespace and sequence fields where applicable

1.2 Core Data Types

Package: maid-engine | Priority: P0 | Dependencies: none

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/types.py
  • [ ] MigrationSource dataclass: namespace: str, directory: Path, dependencies: list[str], migration_requires: dict[str, int] | None
    • [ ] Note: dependencies is derived from manifest.dependencies.keys() at construction time, keeping ContentPackManifest.dependencies: dict[str, str] untouched
  • [ ] MigrationPlan dataclass: migrations: list[Migration], namespaces: set[str], is_dry_run: bool
  • [ ] MigrationResult dataclass: success: bool, applied: list[tuple[str, int]], errors: list[str], duration_seconds: float, dry_run: bool
  • [ ] RollbackPlan dataclass: migrations: list[AppliedMigration], errors: list[str], destructive: list[AppliedMigration], requires_force: bool
  • [ ] AppliedMigration dataclass: namespace: str, sequence: int, name: str, checksum: str, status: str, applied_at: datetime, execution_ms: int | None, applied_by: str | None
  • [ ] ChecksumMismatch dataclass: namespace: str, sequence: int, stored: str, current: str

1.3 Migration Protocol and Context Hierarchy

Package: maid-engine | Priority: P0 | Dependencies: 1.1

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/protocol.py
  • [ ] ExecutionMode enum with values: TRANSACTIONAL, BATCHED, NO_TRANSACTION
  • [ ] validate_execution_modes(execution_mode, rollback_execution_mode) function — raises ValueError on incompatible combos (BATCHED↔NO_TRANSACTION)
  • [ ] Migration protocol (runtime_checkable):
    • [ ] Required attributes: namespace: str, sequence: int, description: str
    • [ ] Optional attributes with defaults: ONLINE_SAFE: bool, execution_mode: ExecutionMode, rollback_execution_mode: ExecutionMode, ROLLBACK_IDEMPOTENT: bool, DESTRUCTIVE_ROLLBACK: bool, STATEMENT_TIMEOUT_MS: int | None, LOCK_TIMEOUT_MS: int | None, MIGRATION_TIMEOUT_S: int | None
    • [ ] async def upgrade(self, ctx: BaseMigrationContext) -> None
    • [ ] async def downgrade(self, ctx: BaseMigrationContext) -> None
    • [ ] def get_checksum(self) -> str
  • [ ] RestrictedMigration protocol — REMOVED (trust tiers collapsed to TRUSTED/UNTRUSTED; all first-party packs use full MigrationContext)
  • [ ] YAMLMigration dataclass implementing Migration protocol — purpose-built for YAML-loaded migrations:
    • [ ] All protocol fields with defaults
    • [ ] _upgrade_fn, _downgrade_fn, _source_path private fields
    • [ ] __post_init__ calling validate_execution_modes
    • [ ] get_checksum() using SHA-256 of source file or fallback to namespace:sequence
    • [ ] Note: Tests can create ad-hoc migration classes directly (Python makes this trivial); MigrationDescriptor general-purpose class removed
  • [ ] BaseMigrationContext dataclass:
    • [ ] Fields: namespace, batch_executor, checkpoint_manager, logger, dry_run
  • [ ] MigrationContext(BaseMigrationContext) dataclass:
    • [ ] Added field: conn: asyncpg.Connection
    • [ ] async def execute(self, query, *args) helper
    • [ ] async def fetch(self, query, *args) helper
  • [ ] REMOVED: RestrictedContext — all first-party packs are maintained by the same team; runtime restriction is premature
  • [ ] Write unit tests at packages/maid-engine/tests/migrations/test_protocol.py:
  • [ ] Test validate_execution_modes with valid and invalid combos
  • [ ] Test YAMLMigration.get_checksum() with real file and fallback
  • [ ] Test YAMLMigration.__post_init__ rejects invalid mode combos
  • [ ] Test Migration protocol runtime checking with isinstance()

1.4 Security Model — Trust Levels

Package: maid-engine | Priority: P0 | Dependencies: 1.1

  • [ ] Implement trust validation within packages/maid-engine/src/maid_engine/migrations/protocol.py (merged from separate security.py):
  • [ ] MigrationTrustLevel enum: TRUSTED, UNTRUSTED (collapsed from three tiers — all first-party packs are TRUSTED)
  • [ ] TRUSTED_NAMESPACES = frozenset({"engine", "stdlib", "classic-rpg", "tutorial-world"})
  • [ ] get_trust_level(namespace: str) -> MigrationTrustLevel function — known namespaces are TRUSTED, all others UNTRUSTED
  • [ ] validate_migration_format(namespace: str, migration_path: Path) -> None — raises SecurityError if untrusted pack uses .py
  • [ ] validate_execution_mode_trust(namespace, execution_mode, rollback_execution_mode) — restricts untrusted to TRANSACTIONAL only
  • [ ] Write unit tests at packages/maid-engine/tests/migrations/test_protocol.py (alongside protocol tests):
  • [ ] Test trust level assignment (first-party → TRUSTED, unknown → UNTRUSTED)
  • [ ] Test validate_migration_format rejects .py for untrusted
  • [ ] Test validate_execution_mode_trust enforces restrictions per tier

Phase 2: Infrastructure Tables and History Tracking (P0)

2.1 Migration History Manager

Package: maid-engine | Priority: P0 | Dependencies: 1.2

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/history.py
  • [ ] MigrationHistory class:
    • [ ] __init__(self, pool: asyncpg.Pool)
    • [ ] async def ensure_infrastructure(self, pool: asyncpg.Pool) — CREATE TABLE IF NOT EXISTS for:
    • [ ] _migration_history table (PK: namespace, sequence; fields: name, checksum, status, applied_at, execution_ms, applied_by)
    • [ ] _migration_rollback_log table (id SERIAL PK, namespace, sequence, rolled_back_at, rolled_back_by, reason)
    • [ ] _migration_checkpoints table (checkpoint_id VARCHAR(64) PK, processed_count, last_seen_cursor BIGINT, total_expected, started_at, updated_at)
    • [ ] All tables created in a single transaction
    • [ ] Note: _migration_lock_heartbeat table removed — PostgreSQL advisory locks release on session close; use idle_in_transaction_session_timeout for zombie connections
    • [ ] async def get_all_applied(self) -> dict[str, int] — returns {namespace: max_sequence}
    • [ ] async def get_applied_migrations(self, namespace, limit) -> list[AppliedMigration]
    • [ ] async def get_latest(self, namespace) -> AppliedMigration | None
    • [ ] async def get_namespaces(self) -> list[str]
    • [ ] async def record_applied(self, conn, migration, status="applied") — INSERT into history
    • [ ] async def update_status(self, conn, migration, status) — UPDATE status in history
    • [ ] async def verify_checksums(self, known_migrations) — compare stored vs current, return list[ChecksumMismatch]
    • [ ] async def delete_applied(self, conn, namespace, sequence) — DELETE from history
    • [ ] async def record_rollback(self, conn, namespace, sequence, rolled_back_by, reason) — INSERT into rollback log
  • [ ] Write unit tests at packages/maid-engine/tests/migrations/test_history.py:
  • [ ] Test ensure_infrastructure creates tables (requires test database or mock)
  • [ ] Test record_applied and get_all_applied roundtrip
  • [ ] Test verify_checksums detects mismatches
  • [ ] Test get_applied_migrations returns in reverse order
  • [ ] Test update_status transitions (pending → applied, applied → rollback_started)

2.2 Checkpoint Manager

Package: maid-engine | Priority: P0 | Dependencies: 2.1

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/checkpoint.py
  • [ ] CheckpointManager class:
    • [ ] @staticmethod async def get_or_create(pool, checkpoint_id) -> Checkpoint — returns existing or creates new checkpoint
    • [ ] @staticmethod async def update_progress(conn, checkpoint_id, processed_count, last_seen_cursor) — UPDATE within caller's transaction
    • [ ] @staticmethod async def complete(pool, checkpoint_id) — DELETE completed checkpoint
    • [ ] @staticmethod async def get(pool, checkpoint_id) -> Checkpoint | None — look up by ID
  • [ ] Checkpoint dataclass: checkpoint_id: str, processed_count: int, last_seen_cursor: int | None, total_expected: int | None, started_at: datetime, updated_at: datetime
  • [ ] Write unit tests at packages/maid-engine/tests/migrations/test_checkpoint.py:
  • [ ] Test create, get, update, complete lifecycle
  • [ ] Test get_or_create returns existing when present
  • [ ] Test complete removes checkpoint row

2.3 Database Settings Enhancement

Package: maid-engine | Priority: P0 | Dependencies: none

  • [ ] Modify packages/maid-engine/src/maid_engine/config/settings.py:
  • [ ] Add dsn property to DatabaseSettings — delegates to existing url property: 
    @property
def dsn(self) -> str:
    """Return a plain PostgreSQL DSN (no +asyncpg dialect suffix)."""
    return self.url.replace("postgresql+asyncpg://", "postgresql://")
  • [ ] Add MigrationSettings class:
    • [ ] enabled_packs: list[str] (default: ["engine", "stdlib", "classic-rpg", "tutorial-world"])
    • [ ] default_statement_timeout_ms: int (default: 30000)
    • [ ] default_lock_timeout_ms: int (default: 5000)
    • [ ] default_migration_timeout_s: int (default: 3600)
    • [ ] default_batch_size: int (default: 5000)
    • [ ] lock_acquire_timeout_s: int (default: 30)
  • [ ] Add migration: MigrationSettings to Settings class
  • [ ] Add "migration" to SettingsProxy._SECTION_NAMES
  • [ ] Modify packages/maid-engine/src/maid_engine/storage/database.py:
  • [ ] Add @classmethod from_pool(cls, pool: asyncpg.Pool) -> DatabaseManager — sets self._pool = pool, self._owns_pool = False
  • [ ] Add _owns_pool: bool flag (default True for existing constructor)
  • [ ] Guard close() to only close pool when _owns_pool is True
  • [ ] Modify packages/maid-engine/src/maid_engine/storage/document_store.py:
  • [ ] Add @classmethod from_pool(cls, pool: asyncpg.Pool, table_name="documents") -> PostgresDocumentStore — sets self._pool = pool, self._owns_pool = False
  • [ ] Add _owns_pool: bool flag, guard close() similarly
  • [ ] Write tests for DatabaseSettings.dsn property
  • [ ] Write tests for from_pool() constructors on both classes

Phase 3: Advisory Lock (P0)

3.1 Global Migration Advisory Lock

Package: maid-engine | Priority: P0 | Dependencies: 2.1

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/lock.py
  • [ ] MigrationAdvisoryLock class (global lock, not per-namespace — CLI migrations run infrequently; per-namespace parallelism adds complexity and race conditions without meaningful benefit):
    • [ ] LOCK_ID = 0x4D414944 (hex for 'MAID') — single global lock ID
    • [ ] __init__(self, pool: asyncpg.Pool)
    • [ ] @asynccontextmanager async def acquire(self, timeout_seconds=30):
    • [ ] Acquire dedicated lock connection from pool
    • [ ] pg_try_advisory_lock($1) in retry loop with exponential backoff (0.5s → 8s cap)
    • [ ] Raise MigrationLockTimeout on deadline
    • [ ] pg_advisory_unlock and release connection in finally
    • [ ] No heartbeat mechanism — PostgreSQL advisory locks release on session close; for zombie connections, configure idle_in_transaction_session_timeout at the PostgreSQL level
  • [ ] Write unit tests at packages/maid-engine/tests/migrations/test_lock.py:
  • [ ] Test lock acquire/release lifecycle (requires test database or mock)
  • [ ] Test timeout raises MigrationLockTimeout
  • [ ] Test concurrent acquire blocks second caller

Phase 4: JSONB Transform Library (P0)

4.1 JSONBUpdateSpec Builder

Package: maid-engine | Priority: P0 | Dependencies: 1.3

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/jsonb_transforms.py
  • [ ] TransformFunction enum: TO_INTEGER, TO_FLOAT, TO_STRING, TO_BOOLEAN, TO_ARRAY_WRAP, EPOCH_TO_ISO, NULLIFY
  • [ ] JSONBUpdateSpec frozen dataclass implementing BatchOperation protocol:
    • [ ] Fields: collection, component, operation (Literal["rename", "add", "remove", "transform"]), field_name, new_field_name, default_value, transform_fn
    • [ ] @property operation_key — stable identifier for checkpoint hashing
    • [ ] @property table — returns "documents"
    • [ ] @property cursor_column — returns "doc_seq"
    • [ ] to_where_sql(param_offset) -> tuple[str, list[Any]] — parameterized WHERE
    • [ ] to_update_sql(param_offset) -> tuple[str, list[Any]] — parameterized UPDATE SET
  • [ ] _is_valid_identifier(name: str) -> bool — regex ^[a-zA-Z_][a-zA-Z0-9_]*$
  • [ ] Public helper functions (all validate identifiers via _is_valid_identifier):
    • [ ] async def rename_jsonb_field(ctx, *, collection, component, old_name, new_name, batch_size=5000) -> int
    • [ ] async def add_jsonb_field(ctx, *, collection, component, field_name, default_value, batch_size=5000) -> int
    • [ ] async def remove_jsonb_field(ctx, *, collection, component, field_name, batch_size=5000) -> int
    • [ ] async def transform_jsonb_field(ctx, *, collection, component, field_name, transform_fn, batch_size=5000) -> int
    • [ ] async def set_schema_version(ctx, *, collection, version, component_filter=None) -> int
  • [ ] All helper functions validate identifiers with _is_valid_identifier before proceeding
  • [ ] Write unit tests at packages/maid-engine/tests/migrations/test_jsonb_transforms.py:
  • [ ] Test JSONBUpdateSpec.to_where_sql generates parameterized SQL
  • [ ] Test JSONBUpdateSpec.to_update_sql for each operation type (rename, add, remove, transform)
  • [ ] Test _is_valid_identifier accepts/rejects correctly
  • [ ] Test helper functions execute without ownership checks (ownership enforcement deferred)

4.2 Component Ownership Validator — REMOVED

Rationale: Trust tiers collapsed to TRUSTED/UNTRUSTED. All first-party packs are maintained by the same team; runtime ownership enforcement is premature. The components manifest field is also removed. If cross-namespace ownership enforcement is needed later, add it then.

  • [ ] Remove ComponentOwnershipValidator from the plan
  • [ ] Remove ownership validation calls from JSONB helper functions (the helpers still validate identifiers via _is_valid_identifier)
  • [ ] Remove OwnershipViolationError from active use (keep exception class for forward compatibility)

4.3 BatchExecutor

Package: maid-engine | Priority: P0 | Dependencies: 2.2, 4.1

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/batch_executor.py
  • [ ] BatchOperation protocol (runtime_checkable):
    • [ ] to_where_sql(param_offset) -> tuple[str, list[Any]]
    • [ ] to_update_sql(param_offset) -> tuple[str, list[Any]]
    • [ ] @property table -> str
    • [ ] @property collection -> str | None
    • [ ] @property cursor_column -> str
    • [ ] @property operation_key -> str
  • [ ] BatchExecutor class:
    • [ ] __init__(self, pool: asyncpg.Pool)
    • [ ] Implementation note: The parameter indexing logic in execute() (dynamic $N placeholders across CTE + UPDATE) is complex enough to have subtle bugs. Prototype with a real PostgreSQL instance and 100+ rows before finalizing. Write at least one integration test early.
    • [ ] async def execute(self, spec: BatchOperation, *, batch_size=5000, namespace="", sequence=0) -> int:
    • [ ] Compute deterministic checkpoint ID via _deterministic_checkpoint_id
    • [ ] Resume from existing checkpoint (CheckpointManager.get_or_create)
    • [ ] Loop: keyset pagination (WHERE cursor_col > $last ORDER BY cursor_col LIMIT $batch)
    • [ ] Each batch: atomic mini-transaction wrapping data update + checkpoint update
    • [ ] asyncio.sleep(0.1) between batches
    • [ ] CheckpointManager.complete on finish
    • [ ] Return total rows updated
    • [ ] @staticmethod _deterministic_checkpoint_id(spec, namespace, sequence) -> str — SHA-256 of namespace:sequence:operation_key, truncated to 16 chars
  • [ ] Write unit tests at packages/maid-engine/tests/migrations/test_batch_executor.py:
  • [ ] Test _deterministic_checkpoint_id is stable across calls
  • [ ] Test _deterministic_checkpoint_id differs by namespace/sequence
  • [ ] Test execute processes all rows (with mock or test DB)
  • [ ] Test execute resumes from checkpoint
  • [ ] Test each batch commits atomically with checkpoint

Phase 5: Content Pack Discovery and Planning (P0)

5.1 Content Pack Manifest Extension

Package: maid-engine | Priority: P0 | Dependencies: none

  • [ ] Modify packages/maid-engine/src/maid_engine/plugins/manifest.py:
  • [ ] Add field to ContentPackManifest:
    • [ ] migration_requires: dict[str, int] = field(default_factory=dict) — e.g., {"engine": 2}
    • [ ] Note: components field removed — ownership enforcement deferred (see Phase 4.2)
  • [ ] Update from_dict and to_dict to handle migration_requires
  • [ ] Derive migration metadata from existing pack.manifest property — do NOT add separate __manifest__ dicts to __init__.py files (avoids duplicate sources of truth and drift risk with existing pack loader)
  • [ ] Note: engine namespace is treated as a built-in migration source (not a content pack); its migrations directory is hardcoded in the discovery module since maid-engine is the host, not a traditional content pack
  • [ ] Write tests verifying ContentPackManifest handles migration_requires in from_dict/to_dict

5.2 Pack Manifest Registry (Discovery)

Package: maid-engine | Priority: P0 | Dependencies: 5.1, 1.4

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/discovery.py
  • [ ] PackManifestRegistry class:
    • [ ] __init__(self) — initializes _manifests: dict[str, ContentPackManifest]
    • [ ] RESERVED_NAMESPACES = frozenset({"engine", "stdlib", "classic-rpg", "tutorial-world", "_internal", "maid", "core", "system"})
    • [ ] async def discover_available_packs(self) -> dict[str, ContentPackManifest]:
    • [ ] Scan importlib.metadata.entry_points(group="maid.content_packs")
    • [ ] Load pack module, instantiate, read manifest property (reuses existing pack metadata path)
    • [ ] Hardcode engine as a built-in source (not a content pack — it's the host)
    • [ ] Log warnings for failed loads
    • [ ] async def discover_migration_sources(self, enabled_packs=None) -> list[MigrationSource]:
    • [ ] Default enabled_packs to first-party only (not all discovered)
    • [ ] Resolve load order via _resolve_dependency_order
    • [ ] Call _find_migration_directory for each
    • [ ] def _find_migration_directory(self, pack_name) -> Path | None:
    • [ ] Primary: entry_points(group="maid.migrations")
    • [ ] Fallback: importlib.util.find_spec(f"maid_{pack_name.replace('-', '_')}"); look for migrations/ subdir
    • [ ] def validate_namespace(self, namespace) — prevent community packs from using reserved namespaces
    • [ ] def get_manifest(self, namespace) -> ContentPackManifest | None
  • [ ] DependencyResolver class:
    • [ ] def resolve_migration_order(self, sources: list[MigrationSource]) -> list[str] — Kahn's algorithm topological sort
    • [ ] Raise MigrationDependencyError on missing dependency or circular dependency
  • [ ] Write unit tests at packages/maid-engine/tests/migrations/test_discovery.py:
  • [ ] Test resolve_migration_order with linear chain (engine → stdlib → classic-rpg)
  • [ ] Test resolve_migration_order detects circular dependency
  • [ ] Test resolve_migration_order detects missing dependency
  • [ ] Test validate_namespace rejects reserved names for untrusted packs
  • [ ] Test _find_migration_directory with mocked entry points
  • [ ] Test discover_migration_sources defaults to first-party packs only

5.3 Migration Planner

Package: maid-engine | Priority: P0 | Dependencies: 5.2, 2.1

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/planner.py
  • [ ] MigrationPlanner class:
    • [ ] __init__(self, registry: PackManifestRegistry)
    • [ ] async def load_migrations(self, sources: list[MigrationSource]) -> None:
    • [ ] For each source, scan directory for .py and .yaml files matching {sequence:04d}_{description}.{ext}
    • [ ] Validate trust level vs file format (validate_migration_format)
    • [ ] Load Python migrations: importlib.import_module, verify module.migration instance, isinstance(mig, Migration), validate_execution_modes
    • [ ] Load YAML migrations: yaml.safe_load, _validate_yaml_schema, construct YAMLMigration
    • [ ] async def create_plan(self, applied, target=None, namespace=None) -> MigrationPlan:
    • [ ] Filter to pending migrations (sequence > applied for each namespace)
    • [ ] Apply target filtering if specified
    • [ ] Order by namespace dependency then sequence
    • [ ] def get_all_migrations(self) -> dict[tuple[str, int], str] — returns (ns, seq) -> checksum map
    • [ ] def get_source(self, namespace) -> MigrationSource | None
  • [ ] YAML migration loader:
    • [ ] def load_yaml_migration(path: Path) -> YAMLMigration — uses yaml.safe_load() ONLY
    • [ ] def _validate_yaml_schema(data: dict) — validate required fields, allowed operation types (add_jsonb_field, remove_jsonb_field, rename_jsonb_field, transform_jsonb_field with pre-defined TransformFunction values, set_schema_version)
    • [ ] def _build_yaml_upgrade(operations: list[dict]) -> Callable — build async upgrade function from YAML ops
    • [ ] def _build_yaml_downgrade(rollback: list[dict] | None) -> Callable | None — build async downgrade function
  • [ ] Write unit tests at packages/maid-engine/tests/migrations/test_planner.py:
  • [ ] Test loading Python migration files (with fixtures)
  • [ ] Test loading YAML migration files (with fixtures)
  • [ ] Test create_plan filters already-applied migrations
  • [ ] Test create_plan with target version
  • [ ] Test create_plan with namespace filter
  • [ ] Test YAML schema validation rejects invalid operations
  • [ ] Test YAML migration is restricted to TRANSACTIONAL mode for untrusted packs
  • [ ] Checksum verification (merged from former Phase 5.4 — validation is part of planning):
    • [ ] async def verify_checksums(self, pool, known_migrations) -> list[ChecksumMismatch] — compare DB checksums vs file checksums
    • [ ] def calculate_optimal_batch_size(document_count, avg_document_size_kb, target_batch_duration_ms=5000) -> int — empirical sizing
  • [ ] Test checksum verification detects mismatches
  • [ ] Test batch size calculation

5.4 Migration Validation — MERGED INTO 5.3

Checksum verification and batch size calculation have been merged into the MigrationPlanner class (Phase 5.3) since validation is a natural part of the planning step.

Phase 6: Migration Runner (P0)

6.1 Migration Runner — Execution Engine

Package: maid-engine | Priority: P0 | Dependencies: 3.1, 4.3, 5.3, 2.1

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/runner.py
  • [ ] MigrationRunner class:
    • [ ] __init__(self, pool, planner, history)
    • [ ] async def migrate(self, target=None, namespace=None, dry_run=False, online_only=False, enabled_packs=None) -> MigrationResult:
    • [ ] Check for stuck pending status — refuse to proceed, log clear error, point operator to maid db validate --cleanup-checkpoints
    • [ ] Call planner.verify_checksums(pool, planner.get_all_migrations()) — abort on mismatch
    • [ ] Call planner.create_plan(applied, target, namespace)
    • [ ] If online_only, filter to ONLINE_SAFE = True migrations only
    • [ ] If dry_run, return simulated result
    • [ ] Acquire global advisory lock via MigrationAdvisoryLock
    • [ ] Call _validate_requirements_under_lock(plan) — re-check migration_requires after lock
    • [ ] Call _execute_migration_plan(plan)
    • [ ] async def _validate_requirements_under_lock(self, plan) — re-fetch applied, validate each migration's migration_requires
    • [ ] async def _execute_migration_plan(self, plan) -> MigrationResult — iterate plan, call _execute_single_migration
    • [ ] async def _execute_single_migration(self, migration):
    • [ ] Create MigrationContext (all first-party packs use full context; YAML migrations use helpers only)
    • [ ] Apply per-migration timeouts (asyncio.timeout)
    • [ ] TRANSACTIONAL mode: single transaction wrapping upgrade + history INSERT
      • [ ] SET LOCAL statement_timeout and SET LOCAL lock_timeout within transaction
    • [ ] BATCHED/NO_TRANSACTION mode: two-phase history (pending → applied)
      • [ ] SET SESSION statement_timeout with RESET in finally
      • [ ] Record pending before execution
      • [ ] Update to applied after success
    • [ ] async def rollback(self, namespace, steps=1, force=False) -> MigrationResult
    • [ ] RollbackStrategy inner class or separate:
    • [ ] async def plan_rollback(self, namespace, steps, force) -> RollbackPlan
    • [ ] async def _find_dependent_migrations(self, namespace, sequence) -> list[str] — uses migration_requires semantic checking
    • [ ] async def _is_destructive_rollback(self, migration) -> bool — checks DESTRUCTIVE_ROLLBACK attribute
    • [ ] async def validate_rollback_safety(self, migration) -> list[str] — returns warnings based on DESTRUCTIVE_ROLLBACK flag (migration authors set this; deferred: data-counting enhancement is P2)
    • [ ] async def rollback_migration(self, namespace, sequence):
    • [ ] TRANSACTIONAL: rollback_started status + downgrade + rollback log + DELETE history all within same transaction (atomicity makes crash-safety marker unnecessary outside the transaction)
    • [ ] BATCHED: Record rollback_started outside transaction (crash-safety marker), then downgrade without transaction, then rollback log + DELETE in transaction
    • [ ] NO_TRANSACTION: same as BATCHED
  • [ ] Write unit tests at packages/maid-engine/tests/migrations/test_runner.py:
  • [ ] Test migrate with simple TRANSACTIONAL migration
  • [ ] Test migrate dry run returns plan without executing
  • [ ] Test migrate with online_only=True filters correctly
  • [ ] Test _validate_requirements_under_lock catches unmet requirements
  • [ ] Test rollback with dependent migrations fails without --force
  • [ ] Test rollback records audit log entry
  • [ ] Test two-phase history for BATCHED mode (pending → applied)
  • [ ] Test stuck pending status blocks new migration with clear error message
  • [ ] Test rollback_started is inside transaction for TRANSACTIONAL mode

Phase 7: CLI Commands (P0)

7.1 Migration CLI — Core Commands

Package: maid-engine | Priority: P0 | Dependencies: 6.1

  • [ ] Create packages/maid-engine/src/maid_engine/cli/commands/ directory if needed
  • [ ] Create packages/maid-engine/src/maid_engine/cli/commands/__init__.py
  • [ ] Create packages/maid-engine/src/maid_engine/cli/commands/db_migrate.py:
  • [ ] migrate command:
    • [ ] Options: --target, --namespace, --dry-run, --online-safe, --enabled-packs (comma-separated), --format (json|text), --backup
    • [ ] Single pool creation point (asyncpg.create_pool(settings.database.dsn))
    • [ ] Create PackManifestRegistry, MigrationPlanner, MigrationHistory, MigrationRunner
    • [ ] Call runner.migrate(...) with CLI arguments
    • [ ] Display result with _display_migration_result (table format for text, JSON for json)
    • [ ] Close pool in finally
  • [ ] rollback command:
    • [ ] Options: --namespace (required), --steps (default 1), --dry-run, --force, --format
    • [ ] Display rollback plan with warnings before executing
    • [ ] Prompt for confirmation on destructive rollbacks (unless --force)
  • [ ] status command:
    • [ ] Options: --namespace, --show-pending, --show-checkpoints, --format
    • [ ] Display Rich table with namespace, current version, pending count, last applied, status indicator (✓/⚠)
    • [ ] Show in-progress checkpoints if --show-checkpoints
  • [ ] history command:
    • [ ] Options: --namespace, --limit, --include-rollbacks, --format
    • [ ] Display migration history with timestamps and execution durations
  • [ ] create command:
    • [ ] Arguments: DESCRIPTION
    • [ ] Options: --namespace (required), --template (jsonb|ddl|seed)
    • [ ] Generate migration file at correct path with next sequence number
    • [ ] Template content based on --template option
  • [ ] validate command:
    • [ ] Options: --fix-checksums, --cleanup-checkpoints, --format
    • [ ] Verify checksum integrity, report mismatches
    • [ ] Optionally fix checksums or clean stale checkpoints
  • [ ] baseline command:
    • [ ] Options: --namespace (required), --at-version (required), --force
    • [ ] Mark existing schema as migrated to given version without running migrations
    • [ ] Documented upgrade path for existing deployments: maid db baseline --namespace=engine --at-version=2 && maid db baseline --namespace=stdlib --at-version=0
  • [ ] cleanup command:
    • [ ] Options: --namespace (required), --remove-tables, --remove-jsonb
    • [ ] Remove migration artifacts for decommissioned packs
  • [ ] repair command (P1 — consolidates common recovery operations):
    • [ ] --clear-pending — clear stuck pending migration status
    • [ ] --cleanup-checkpoints — remove orphaned checkpoint rows
    • [ ] --fix-checksums — re-compute checksums for applied migrations
    • [ ] More discoverable than spreading repair operations across validate and cleanup
  • [ ] _get_enabled_packs_from_config() -> list[str] — check MAID_ENABLED_CONTENT_PACKS env var, fall back to settings.migration.enabled_packs
  • [ ] Modify packages/maid-engine/src/maid_engine/cli/app.py:
  • [ ] Replace stub db_migrate command with import from commands/db_migrate.py
  • [ ] Replace stub db_init with proper implementation that calls ensure_infrastructure
  • [ ] Register all new db subcommands on db_app
  • [ ] Write CLI integration tests at packages/maid-engine/tests/migrations/test_cli.py:
  • [ ] Test maid db migrate --dry-run output format
  • [ ] Test maid db status --format=json produces valid JSON
  • [ ] Test maid db create generates file with correct sequence number
  • [ ] Test maid db validate reports checksum mismatches

Phase 8: Initial Migration Files (P0)

8.1 Engine Initial Migrations

Package: maid-engine | Priority: P0 | Dependencies: 6.1

  • [ ] Create packages/maid-engine/src/maid_engine/migrations/engine/ directory
  • [ ] Create packages/maid-engine/src/maid_engine/migrations/engine/__init__.py
  • [ ] Create packages/maid-engine/src/maid_engine/migrations/engine/0001_initial_schema.py:
  • [ ] Migrate SCHEMA_SQL from DatabaseManager.initialize() into a proper migration
  • [ ] Create accounts table, sessions table, and their indexes
  • [ ] ONLINE_SAFE = False, execution_mode = ExecutionMode.TRANSACTIONAL
  • [ ] downgrade: DROP tables (with DESTRUCTIVE_ROLLBACK = True)
  • [ ] Module-level migration = InitialSchema() instance
  • [ ] Create packages/maid-engine/src/maid_engine/migrations/engine/0002_document_store.py:
  • [ ] Migrate documents table creation from PostgresDocumentStore.initialize() into a migration
  • [ ] Create documents table with id UUID, collection VARCHAR(255), data JSONB, created_at, updated_at
  • [ ] Add doc_seq BIGSERIAL column for keyset pagination (not present in current schema)
    • [ ] For new installs: column is part of initial CREATE TABLE
    • [ ] For existing installs using baseline: doc_seq is added via ALTER TABLE documents ADD COLUMN doc_seq BIGSERIAL — PostgreSQL auto-populates BIGSERIAL for existing rows; this migration does NOT use the BatchExecutor (avoids circular dependency since BatchExecutor.cursor_column defaults to doc_seq)
  • [ ] Create indexes: idx_documents_collection, idx_documents_data_gin, idx_documents_collection_seq
  • [ ] ONLINE_SAFE = False, execution_mode = ExecutionMode.TRANSACTIONAL
  • [ ] downgrade: DROP table (DESTRUCTIVE_ROLLBACK = True)
  • [ ] Module-level migration = DocumentStore() instance
  • [ ] Update DatabaseManager.initialize() to detect if migrations are available and skip inline CREATE TABLE IF NOT EXISTS
  • [ ] Update PostgresDocumentStore.initialize() similarly
  • [ ] Write tests verifying initial migrations create correct schema

8.2 Entry Points Configuration

Package: maid-engine | Priority: P0 | Dependencies: 8.1

  • [ ] Modify packages/maid-engine/pyproject.toml:
  • [ ] Add entry point group [project.entry-points."maid.migrations"] with engine = "maid_engine.migrations.engine"
  • [ ] Add pyyaml to dependencies if not already present (already listed)
  • [ ] Note: prometheus-client deferred — not needed until migration system is integrated into a long-running process
  • [ ] Modify packages/maid-stdlib/pyproject.toml:
  • [ ] Add entry point [project.entry-points."maid.migrations"] with stdlib = "maid_stdlib.migrations" (placeholder for future)
  • [ ] Verify [project.entry-points."maid.content_packs"] already exists (it does — currently empty); populate with stdlib = "maid_stdlib" for discovery
  • [ ] Similarly for packages/maid-classic-rpg/pyproject.toml and packages/maid-tutorial-world/pyproject.toml
  • [ ] Run uv sync to refresh entry points

Phase 9: Application Signaling — DEFERRED

Rationale: NOTIFY listener has no consumer — migrations run from CLI before/after the engine starts. NOTIFY emission adds code to every migration path for a feature with no consumer. When online/live migration support becomes a real requirement, add the listener then. Keep NOTIFY emission as a commented placeholder in the runner if desired.

9.1 PostgreSQL NOTIFY Integration — DEFERRED

Phase 10: Observability — DEFERRED

Rationale: The migration system runs as a short-lived CLI command (maid db migrate). Adding prometheus-client as a dependency to instrument a CLI process is unusual — there's no Prometheus scrape endpoint during CLI execution. Log structured JSON events instead for queryable observability. Defer Prometheus metrics to when the migration system is integrated into a long-running process (e.g., auto-migration on engine startup).

10.1 Prometheus Metrics — DEFERRED

Phase 11: End-to-End Integration Testing (P1)

11.1 Integration Test Fixtures

Package: maid-engine | Priority: P1 | Dependencies: all above

  • [ ] Create packages/maid-engine/tests/migrations/conftest.py:
  • [ ] @pytest.fixture async def migration_db() — create temporary test database, yield pool, cleanup
  • [ ] @pytest.fixture def sample_migration_files(tmp_path) — create test migration .py and .yaml files
  • [ ] @pytest.fixture def mock_content_packs() — create ContentPackManifest instances for engine, stdlib, classic-rpg
  • [ ] Create sample test migration files in packages/maid-engine/tests/migrations/fixtures/:
  • [ ] engine/0001_test_initial.py — creates a simple test table
  • [ ] stdlib/0001_test_initial.py — depends on engine, creates JSONB test data
  • [ ] community/0001_test_yaml.yaml — declarative YAML migration

11.2 Integration Test Scenarios

Package: maid-engine | Priority: P1 | Dependencies: 11.1

  • [ ] Create packages/maid-engine/tests/migrations/test_integration.py:
  • [ ] test_full_migration_lifecycle — discover, plan, execute, verify schema, rollback
  • [ ] test_dependency_order_resolution — engine → stdlib → classic-rpg ordering
  • [ ] test_migration_requirement_validation — classic-rpg requires stdlib:2
  • [ ] test_batched_migration_with_checkpoint_recovery — simulate interruption, verify resume
  • [ ] test_concurrent_namespace_migration — two namespaces migrate serially under global lock
  • [ ] test_rollback_with_dependent_packs — fails without force, succeeds with force
  • [ ] test_yaml_migration_sandbox — untrusted pack restricted to YAML
  • [ ] test_checksum_verification_blocks_tampered_migration — modified file detected
  • [ ] test_idempotent_rename_field — rename works on mixed-schema documents
  • [ ] test_baseline_then_migrate — existing DB baseline followed by new migrations
  • [ ] test_stuck_pending_blocks_migration — pending status prevents migration with clear error

11.3 CLI Integration Tests

Package: maid-engine | Priority: P1 | Dependencies: 7.1, 11.1

  • [ ] Create packages/maid-engine/tests/migrations/test_cli_integration.py:
  • [ ] test_migrate_dry_run_output — verify text output format
  • [ ] test_migrate_json_format — verify JSON output
  • [ ] test_status_shows_pending — verify pending migration display
  • [ ] test_rollback_prompts_for_destructive — verify confirmation prompt
  • [ ] test_create_generates_file_with_next_sequence — file creation in correct directory
  • [ ] test_baseline_marks_version — baseline inserts history without running

Phase 12: Documentation (P1)

12.1 User-Facing Documentation

Priority: P1 | Dependencies: 7.1

  • [ ] Create docs/guides/database-migrations.md:
  • [ ] Overview of the migration system
  • [ ] CLI command reference with examples
  • [ ] Migration authoring guide (class-based template)
  • [ ] YAML migration format reference (for community packs), including transform_jsonb_field for simple pre-defined transforms (TO_INTEGER, TO_BOOLEAN, etc.)
  • [ ] Batching strategy guidelines (when to use TRANSACTIONAL vs BATCHED)
  • [ ] ONLINE_SAFE criteria checklist
  • [ ] Expand-contract pattern walkthrough
  • [ ] Rollback safety and data loss warnings
  • [ ] Existing database upgrade path: Document maid db baseline workflow for existing deployments
  • [ ] Update CLAUDE.md with:
  • [ ] New maid db commands in CLI reference section
  • [ ] Migration file paths in directory structure
  • [ ] MigrationSettings in configuration section
  • [ ] Update docs/guides/content-packs.md (or equivalent) with:
  • [ ] How to add migrations to a content pack
  • [ ] migration_requires manifest field for cross-pack dependencies
  • [ ] Trust level implications for community packs

12.2 Code Documentation

Priority: P1 | Dependencies: all code phases

  • [ ] Ensure all public classes and functions have Google-style docstrings
  • [ ] Add module-level docstrings to all migration modules
  • [ ] Add inline comments for non-obvious algorithmic decisions (keyset pagination, checkpoint hashing, lock ID masking)

Phase 13: Production Hardening (P2)

13.1 Memory Management

Package: maid-engine | Priority: P2 | Dependencies: 6.1

  • [ ] Implement MigrationResourceManager class (in runner.py or separate file):
  • [ ] Memory monitoring during migrations
  • [ ] Automatic batch size reduction on OutOfMemoryError
  • [ ] Memory delta logging for large migrations
  • [ ] GC trigger after high-memory migrations

13.2 Adaptive Batch Sizing

Package: maid-engine | Priority: P2 | Dependencies: 4.3

  • [ ] Enhance BatchExecutor.execute with optional adaptive sizing:
  • [ ] Monitor per-batch duration
  • [ ] Increase batch size if batches complete quickly
  • [ ] Decrease batch size if batches take too long
  • [ ] Respect min/max bounds (100–10,000)

13.3 Rollback Safety Enhancements

Package: maid-engine | Priority: P2 | Dependencies: 6.1

  • [ ] Implement enhanced validate_rollback_safety(migration) -> list[str]:
  • [ ] Count documents with data in fields that would be removed by rollback (upgrade from simple DESTRUCTIVE_ROLLBACK flag to data-aware warnings)
  • [ ] Generate data loss warnings with document counts
  • [ ] Detect type changes that may cause incompatibility on rollback
  • [ ] Note: V1 uses the simpler DESTRUCTIVE_ROLLBACK = True flag set by migration authors
  • [ ] Integrate enhanced warnings into maid db rollback CLI output

Dependencies Summary

Phase 1 (Types & Protocol):
  1.1 Exceptions (standalone)
  1.2 Types (standalone)
  1.3 Protocol → 1.1
  1.4 Security (merged into protocol.py) → 1.1

Phase 2 (Infrastructure):
  2.1 History → 1.2
  2.2 Checkpoint → 2.1
  2.3 Settings (standalone)

Phase 3 (Locking):
  3.1 Global Advisory Lock → 2.1

Phase 4 (JSONB):
  4.1 JSONBUpdateSpec → 1.3
  4.2 Ownership Validator — REMOVED
  4.3 BatchExecutor → 2.2, 4.1

Phase 5 (Discovery):
  5.1 Manifest Extension (standalone)
  5.2 Discovery → 5.1, 1.4
  5.3 Planner + Validation (merged) → 5.2, 2.1

Phase 6 (Runner):
  6.1 Runner → 3.1, 4.3, 5.3, 2.1

Phase 7 (CLI):
  7.1 CLI → 6.1

Phase 8 (Migrations):
  8.1 Engine Migrations → 6.1
  8.2 Entry Points → 8.1

Phase 9 (Signaling) — DEFERRED
Phase 10 (Observability) — DEFERRED

Phase 11 (Testing):
  11.1-11.3 → all code phases

Phase 12 (Docs):
  12.1-12.2 → 7.1 + all code

Phase 13 (Hardening):
  13.1-13.3 → 6.1

New Files Summary

packages/maid-engine/src/maid_engine/
├── migrations/
│   ├── __init__.py                    # Module exports
│   ├── exceptions.py                  # Migration-specific exceptions
│   ├── types.py                       # Core data types (MigrationSource, MigrationPlan, etc.)
│   ├── protocol.py                    # Migration protocol, ExecutionMode, contexts, trust levels
│   ├── history.py                     # MigrationHistory, infrastructure tables
│   ├── checkpoint.py                  # CheckpointManager for batched resume
│   ├── lock.py                        # MigrationAdvisoryLock (global)
│   ├── jsonb_transforms.py            # JSONBUpdateSpec, transform helpers
│   ├── batch_executor.py              # BatchExecutor, BatchOperation protocol
│   ├── discovery.py                   # PackManifestRegistry, DependencyResolver
│   ├── planner.py                     # MigrationPlanner, YAML loader, checksum verification
│   ├── runner.py                      # MigrationRunner, RollbackStrategy
│   └── engine/                        # Engine's own migrations
│       ├── __init__.py
│       ├── 0001_initial_schema.py
│       └── 0002_document_store.py
└── cli/
    └── commands/
        ├── __init__.py
        └── db_migrate.py              # Migration CLI commands

Removed from original plan: - security.py — merged into protocol.py - validator.py — merged into planner.py - listener.py — deferred (no consumer for NOTIFY in CLI context) - metrics.py — deferred (Prometheus inappropriate for CLI tool)

packages/maid-engine/tests/migrations/ ├── init.py ├── conftest.py # Test fixtures (DB, sample files) ├── test_protocol.py # Includes trust level tests (merged from test_security.py) ├── test_history.py ├── test_checkpoint.py ├── test_lock.py ├── test_jsonb_transforms.py ├── test_batch_executor.py ├── test_discovery.py ├── test_planner.py # Includes checksum verification tests ├── test_runner.py ├── test_cli.py ├── test_integration.py ├── test_cli_integration.py └── fixtures/ ├── engine/ │ └── 0001_test_initial.py ├── stdlib/ │ └── 0001_test_initial.py └── community/ └── 0001_test_yaml.yaml ```

Modified Files Summary

File Change
packages/maid-engine/src/maid_engine/config/settings.py Add DatabaseSettings.dsn (delegates to url), MigrationSettings, register in Settings
packages/maid-engine/src/maid_engine/storage/database.py Add DatabaseManager.from_pool() classmethod with _owns_pool flag
packages/maid-engine/src/maid_engine/storage/document_store.py Add PostgresDocumentStore.from_pool() classmethod with _owns_pool flag
packages/maid-engine/src/maid_engine/plugins/manifest.py Add migration_requires field to ContentPackManifest
packages/maid-engine/src/maid_engine/cli/app.py Replace db_migrate stub, register new db subcommands
packages/maid-engine/pyproject.toml Add maid.migrations entry point; populate maid.content_packs
packages/maid-stdlib/pyproject.toml Add entry points
packages/maid-classic-rpg/pyproject.toml Add entry points
packages/maid-tutorial-world/pyproject.toml Add entry points

Success Criteria

  • [ ] maid db migrate applies pending migrations in correct dependency order
  • [ ] maid db migrate --dry-run shows plan without executing
  • [ ] maid db status shows all namespaces with current/pending versions
  • [ ] maid db rollback --namespace=<ns> reverses migrations with audit trail
  • [ ] maid db baseline marks existing schema as migrated for existing deployments
  • [ ] Batched migrations resume correctly from checkpoint after simulated crash
  • [ ] Global advisory lock serializes concurrent migration attempts
  • [ ] Community packs are restricted to YAML-only declarative migrations
  • [ ] Stuck pending status blocks migration with clear operator guidance
  • [ ] Checksum verification detects tampered migration files
  • [ ] from_pool() constructors respect _owns_pool flag on close
  • [ ] All new code has >80% test coverage
  • [ ] All public APIs have Google-style docstrings
  • [ ] MyPy strict mode passes on all new modules