Skip to content

Enterprise agent platform for building, deploying, and operating AI teammates.

48.4 KB
Raw Blame
#!/usr/bin/env python
"""
Integration tests for database migrations.

These tests run actual database migrations against SQLite and PostgreSQL
to ensure migration scripts work correctly and are idempotent.

Usage:
    pytest tests/migrations/test_migration_integration.py
    pytest tests/migrations/test_migration_integration.py::TestMigrations::test_sqlite_upgrade
    pytest tests/migrations/test_migration_integration.py::TestMigrations::test_postgresql_upgrade
"""

import argparse
import concurrent.futures
import os
import tempfile
import threading
from pathlib import Path

import pytest
from alembic import command
from alembic.config import Config
from alembic.script import ScriptDirectory
from sqlalchemy import create_engine, inspect, text
from sqlalchemy.exc import IntegrityError, SQLAlchemyError
from sqlalchemy.orm import sessionmaker

# Project root directory
project_root = Path(__file__).parent.parent.parent


def _revision_reached(
    script_dir: ScriptDirectory, target_revision: str, current_revisions: set[str]
) -> bool:
    """Return whether target_revision is present in or behind current heads."""
    pending = list(current_revisions)
    visited: set[str] = set()

    while pending:
        revision_id = pending.pop()
        if revision_id == target_revision:
            return True
        if revision_id in visited:
            continue

        visited.add(revision_id)
        revision = script_dir.get_revision(revision_id)

        down_revisions = revision.down_revision
        if down_revisions is None:
            continue
        if isinstance(down_revisions, str):
            pending.append(down_revisions)
        else:
            pending.extend(down_revisions)

    return False


class MigrationTester:
    """Helper class to test database migrations."""

    def __init__(self, db_type: str):
        self.db_type = db_type
        self.engine = None
        self.alembic_cfg = None
        self.temp_db_file = None
        self._old_database_url: str | None = None

    def setup_database(self):
        """Set up test database connection."""
        self._old_database_url = os.environ.get("DATABASE_URL")
        if self.db_type == "sqlite":
            # Use temporary file for SQLite
            fd, path = tempfile.mkstemp(suffix=".db")
            os.close(fd)
            db_url = f"sqlite:///{path}"
            self.temp_db_file = path
        elif self.db_type == "postgresql":
            configured_postgres_url = os.getenv("POSTGRES_TEST_DATABASE_URL")
            if not configured_postgres_url:
                database_url = os.getenv("DATABASE_URL")
                if database_url and database_url.startswith("postgresql"):
                    configured_postgres_url = database_url

            db_url = configured_postgres_url or os.getenv(
                "XAGENT_TEST_POSTGRES_URL",
                "postgresql://xagent:xagent@localhost:5432/xagent_test",
            )

        os.environ["DATABASE_URL"] = db_url
        try:
            self.engine = create_engine(db_url)

            # Clean database for PostgreSQL
            if self.db_type == "postgresql":
                with self.engine.begin() as conn:
                    conn.execute(text("DROP SCHEMA public CASCADE"))
                    conn.execute(text("CREATE SCHEMA public"))
        except SQLAlchemyError as exc:
            if self.db_type == "postgresql":
                self._restore_database_url()
                pytest.skip(f"PostgreSQL test database unavailable: {exc}")
            raise

        # Configure Alembic
        self.alembic_cfg = Config(str(project_root / "alembic.ini"))
        self.alembic_cfg.set_main_option("sqlalchemy.url", db_url)

        # Metadata-owned core tables are prepared explicitly by tests that
        # exercise the production migration contract. Historical Alembic
        # revisions do not create ``users`` themselves.

    def create_metadata_owned_users_table(self) -> None:
        """Create the core parent table that Alembic intentionally does not own."""
        from xagent.web.models.user import User

        User.__table__.create(bind=self.engine, checkfirst=True)

    def assert_user_oauth_user_cascade_fk(self) -> None:
        """Require the cascade that cleans up ordinary and actor OAuth rows."""
        if self.db_type == "sqlite":
            # Alembic's batch rename can leave the pooled connection's schema
            # cache stale. Reopen the file before inspecting the final DDL.
            self.engine.dispose()
        foreign_keys = inspect(self.engine).get_foreign_keys("user_oauth")
        assert any(
            tuple(foreign_key.get("constrained_columns") or ()) == ("user_id",)
            and foreign_key.get("referred_table") == "users"
            and tuple(foreign_key.get("referred_columns") or ()) == ("id",)
            and str((foreign_key.get("options") or {}).get("ondelete")).upper()
            == "CASCADE"
            for foreign_key in foreign_keys
        ), "user_oauth.user_id must cascade to users.id"

    def _restore_database_url(self) -> None:
        """Restore ``DATABASE_URL`` to its pre-test value."""
        if self._old_database_url is None:
            os.environ.pop("DATABASE_URL", None)
        else:
            os.environ["DATABASE_URL"] = self._old_database_url

    def teardown_database(self):
        """Clean up test database."""
        try:
            if self.db_type == "sqlite" and self.temp_db_file:
                os.unlink(self.temp_db_file)
            elif self.db_type == "postgresql" and self.engine is not None:
                with self.engine.begin() as conn:
                    conn.execute(text("DROP SCHEMA public CASCADE"))
                    conn.execute(text("CREATE SCHEMA public"))
        finally:
            self._restore_database_url()

    def get_table_names(self):
        """Get list of table names."""
        inspector = inspect(self.engine)
        return inspector.get_table_names()

    def get_column_names(self, table_name):
        """Get list of column names for a table."""
        with self.engine.begin() as conn:
            if self.db_type == "postgresql":
                result = conn.execute(
                    text(
                        "SELECT column_name FROM information_schema.columns "
                        "WHERE table_name = :table_name"
                    ),
                    {"table_name": table_name},
                )
                return [row[0] for row in result]
            else:
                # SQLite: PRAGMA table_info returns (cid, name, type, notnull, dflt_value, pk)
                # We want index 1 (name)
                result = conn.execute(text(f"PRAGMA table_info({table_name})"))
                return [row[1] for row in result]

    def get_alembic_versions(self) -> set[str]:
        """Get current Alembic version rows."""
        with self.engine.begin() as conn:
            result = conn.execute(text("SELECT version_num FROM alembic_version"))
            return {row[0] for row in result}


class TestMigrations:
    """Test database migrations."""

    @pytest.fixture
    def sqlite_tester(self):
        """Create SQLite migration tester."""
        tester = MigrationTester("sqlite")
        tester.setup_database()
        yield tester
        tester.teardown_database()

    @pytest.fixture
    def postgresql_tester(self):
        """Create PostgreSQL migration tester."""
        tester = MigrationTester("postgresql")
        tester.setup_database()
        yield tester
        tester.teardown_database()

    def test_sqlite_upgrade(self, sqlite_tester):
        """Test full migration upgrade on SQLite from empty database.

        This tests that migrations can correctly create all tables and columns
        from scratch, not just when tables are pre-created by SQLAlchemy.
        """
        # NOTE: Some base tables (users, models, tasks) are created by SQLAlchemy
        # in production, not by migrations. We need to create them here to
        # simulate production environment.
        from xagent.web.models.database import Base

        Base.metadata.create_all(bind=sqlite_tester.engine)

        # Run upgrade from empty database (but with base tables present)
        command.upgrade(sqlite_tester.alembic_cfg, "head")
        sqlite_tester.assert_user_oauth_user_cascade_fk()

        # Verify alembic_version table
        with sqlite_tester.engine.begin() as conn:
            result = conn.execute(text("SELECT version_num FROM alembic_version"))
            version = result.scalar()
            assert version is not None, "Version should be set after upgrade"

        # Verify key tables exist
        tables = sqlite_tester.get_table_names()
        assert "agents" in tables, "agents table should exist"
        assert "alembic_version" in tables, "alembic_version table should exist"
        assert "models" in tables, "models table should exist"
        assert "users" in tables, "users table should exist"
        assert "tasks" in tables, "tasks table should exist"

        # Verify agents table structure - this tests that migrations
        # correctly created all columns, not just that SQLAlchemy created them
        columns = sqlite_tester.get_column_names("agents")
        assert "models" in columns, "models column should exist"
        assert "name" in columns, "name column should exist"
        assert "execution_mode" in columns, "execution_mode column should exist"
        assert "status" in columns, "status column should exist"
        assert "suggested_prompts" in columns, "suggested_prompts column should exist"

        # Verify models table has encrypted column
        models_columns = sqlite_tester.get_column_names("models")
        assert "_api_key_encrypted" in models_columns, (
            "_api_key_encrypted column should exist"
        )

    def test_sqlite_stamp_head_creates_wide_alembic_version_table(self, sqlite_tester):
        """Stamping a fresh database must support long revision IDs."""
        command.stamp(sqlite_tester.alembic_cfg, "head")

        columns = inspect(sqlite_tester.engine).get_columns("alembic_version")
        version_num = next(col for col in columns if col["name"] == "version_num")

        assert version_num["type"].length == 255

    @pytest.mark.postgresql
    def test_postgresql_upgrade(self, postgresql_tester):
        """Test a full PostgreSQL migration upgrade with its core parent table.

        Historical migrations intentionally leave core metadata-owned tables
        to SQLAlchemy. Create only ``users`` so the migration chain must still
        build its own tables while preserving the OAuth cascade contract.
        """
        postgresql_tester.create_metadata_owned_users_table()
        command.upgrade(postgresql_tester.alembic_cfg, "head")
        postgresql_tester.assert_user_oauth_user_cascade_fk()

        # Verify alembic_version table
        with postgresql_tester.engine.begin() as conn:
            result = conn.execute(text("SELECT version_num FROM alembic_version"))
            version = result.scalar()
            assert version is not None, "Version should be set after upgrade"

        # Verify migration-owned tables exist. Core tables created by current
        # SQLAlchemy metadata are intentionally outside this test's contract.
        tables = postgresql_tester.get_table_names()
        assert "agents" in tables, "agents table should exist"
        assert "alembic_version" in tables, "alembic_version table should exist"
        assert "public_mcp_apps" in tables, "public_mcp_apps table should exist"
        assert "user_oauth" in tables, "user_oauth table should exist"
        assert "durable_sandbox_lifecycles" in tables

        lifecycle_columns = {
            column["name"]
            for column in inspect(postgresql_tester.engine).get_columns(
                "durable_sandbox_lifecycles"
            )
        }
        assert {
            "active_scope_digest",
            "create_operation_token",
            "create_phase",
            "create_terminal_outcome",
        }.issubset(lifecycle_columns)
        lifecycle_checks = {
            constraint["name"]
            for constraint in inspect(postgresql_tester.engine).get_check_constraints(
                "durable_sandbox_lifecycles"
            )
        }
        assert {
            "ck_dsl_active_generation",
            "ck_dsl_create_phase",
            "ck_dsl_create_terminal_outcome",
        }.issubset(lifecycle_checks)
        lifecycle_uniques = {
            constraint["name"]
            for constraint in inspect(postgresql_tester.engine).get_unique_constraints(
                "durable_sandbox_lifecycles"
            )
        }
        assert {
            "uq_dsl_active_scope_digest",
            "uq_dsl_create_operation_token",
        }.issubset(lifecycle_uniques)
        lifecycle_indexes = {
            index["name"]
            for index in inspect(postgresql_tester.engine).get_indexes(
                "durable_sandbox_lifecycles"
            )
        }
        assert "ix_dsl_scope_digest" in lifecycle_indexes

        # Verify agents table structure.
        columns = postgresql_tester.get_column_names("agents")
        assert "models" in columns, "models column should exist"
        assert "name" in columns, "name column should exist"
        assert "execution_mode" in columns, "execution_mode column should exist"
        assert "status" in columns, "status column should exist"
        assert "suggested_prompts" in columns, "suggested_prompts column should exist"

        user_oauth_columns = postgresql_tester.get_column_names("user_oauth")
        assert "resource_owner_key" in user_oauth_columns, (
            "owner-aware OAuth column should exist"
        )

    @pytest.mark.postgresql
    def test_postgresql_owner_index_collision_allows_retry_after_remediation(
        self, postgresql_tester
    ):
        """After operator remediation, retry starts from the intact old schema."""
        down_revision = "b1efe0dbe0af"
        ordinary_index = "uq_user_oauth_ordinary_account"
        actor_index = "uq_user_oauth_actor_account"
        old_constraint = "uq_user_provider_account"

        postgresql_tester.create_metadata_owned_users_table()
        command.upgrade(postgresql_tester.alembic_cfg, down_revision)
        with postgresql_tester.engine.begin() as conn:
            conn.execute(text(f"CREATE INDEX {actor_index} ON user_oauth (user_id)"))

        with pytest.raises(SQLAlchemyError):
            command.upgrade(postgresql_tester.alembic_cfg, "head")

        inspector = inspect(postgresql_tester.engine)
        assert "resource_owner_key" not in {
            column["name"] for column in inspector.get_columns("user_oauth")
        }
        assert old_constraint in {
            constraint["name"]
            for constraint in inspector.get_unique_constraints("user_oauth")
        }
        indexes = {index["name"] for index in inspector.get_indexes("user_oauth")}
        assert actor_index in indexes
        assert ordinary_index not in indexes
        script_dir = ScriptDirectory.from_config(postgresql_tester.alembic_cfg)
        current_revisions = postgresql_tester.get_alembic_versions()
        assert _revision_reached(script_dir, down_revision, current_revisions)
        assert not _revision_reached(
            script_dir,
            "20260818_user_oauth_resource_owner",
            current_revisions,
        )

        with postgresql_tester.engine.begin() as conn:
            conn.execute(text(f"DROP INDEX {actor_index}"))
        command.upgrade(postgresql_tester.alembic_cfg, "head")

        assert "resource_owner_key" in postgresql_tester.get_column_names("user_oauth")

    @pytest.mark.postgresql
    def test_postgresql_owner_indexes_enforce_namespace_boundaries(
        self, postgresql_tester
    ):
        """The migrated PostgreSQL predicates separate ordinary and actor rows."""
        parent = "b1efe0dbe0af"
        owner_revision = "20260818_user_oauth_resource_owner"
        command.upgrade(postgresql_tester.alembic_cfg, parent)
        with postgresql_tester.engine.begin() as conn:
            conn.execute(text("CREATE TABLE users (id INTEGER PRIMARY KEY)"))
            conn.execute(text("INSERT INTO users (id) VALUES (7101)"))

        command.upgrade(postgresql_tester.alembic_cfg, owner_revision)
        with postgresql_tester.engine.begin() as conn:
            conn.execute(
                text(
                    "INSERT INTO user_oauth "
                    "(user_id, provider, provider_user_id, access_token, "
                    "resource_owner_key) VALUES "
                    "(7101, 'gmail', 'shared', 'ordinary', NULL), "
                    "(7101, 'gmail', 'shared', 'actor', 'actor:alice')"
                )
            )

        with pytest.raises(IntegrityError):
            with postgresql_tester.engine.begin() as conn:
                conn.execute(
                    text(
                        "INSERT INTO user_oauth "
                        "(user_id, provider, provider_user_id, access_token) "
                        "VALUES (7101, 'gmail', 'shared', 'duplicate-ordinary')"
                    )
                )

        with pytest.raises(IntegrityError):
            with postgresql_tester.engine.begin() as conn:
                conn.execute(
                    text(
                        "INSERT INTO user_oauth "
                        "(user_id, provider, provider_user_id, access_token, "
                        "resource_owner_key) VALUES "
                        "(7101, 'gmail', 'shared', 'duplicate-actor', 'actor:alice')"
                    )
                )

    @pytest.mark.postgresql
    def test_postgresql_user_delete_cascades_actor_owned_oauth_rows(
        self, postgresql_tester
    ):
        """Filtered ordinary relationship leaves actor cleanup to the FK."""
        from xagent.web.models.database import Base
        from xagent.web.models.user import User
        from xagent.web.models.user_oauth import UserOAuth

        Base.metadata.create_all(bind=postgresql_tester.engine)
        db = sessionmaker(
            bind=postgresql_tester.engine,
            autoflush=False,
            autocommit=False,
        )()
        try:
            user = User(username="oauth-cascade-owner", password_hash="hash")
            db.add(user)
            db.commit()
            db.refresh(user)
            db.add_all(
                [
                    UserOAuth(
                        user_id=int(user.id),
                        provider="gmail",
                        provider_user_id="ordinary",
                        access_token="ordinary-token",
                    ),
                    UserOAuth(
                        user_id=int(user.id),
                        provider="gmail",
                        resource_owner_key="actor:alice",
                        provider_user_id="alice",
                        access_token="actor-token",
                    ),
                ]
            )
            db.commit()
            db.expire(user, ["oauth_accounts"])

            assert [row.resource_owner_key for row in user.oauth_accounts] == [None]

            db.delete(user)
            db.commit()

            assert db.query(UserOAuth).count() == 0
        finally:
            db.close()

    @pytest.mark.postgresql
    def test_postgresql_owner_migration_downgrade_restores_legacy_schema(
        self, postgresql_tester
    ):
        """The supported PostgreSQL downgrade restores the old identity shape."""
        parent = "b1efe0dbe0af"
        owner_revision = "20260818_user_oauth_resource_owner"
        command.upgrade(postgresql_tester.alembic_cfg, parent)
        with postgresql_tester.engine.begin() as conn:
            # The historical migration chain creates user_oauth from scratch
            # but assumes the core users table already exists. Complete that
            # legacy shape explicitly before exercising the owner revision.
            conn.execute(text("CREATE TABLE users (id INTEGER PRIMARY KEY)"))
            conn.execute(
                text(
                    "ALTER TABLE user_oauth ADD CONSTRAINT "
                    "fk_user_oauth_user_id_users FOREIGN KEY (user_id) "
                    "REFERENCES users(id) ON DELETE CASCADE"
                )
            )
            conn.execute(text("INSERT INTO users (id) VALUES (7001)"))
            conn.execute(
                text(
                    "INSERT INTO user_oauth "
                    "(id, user_id, provider, access_token, provider_user_id) "
                    "VALUES (9001, 7001, 'gmail', 'token', 'provider-account')"
                )
            )

        command.upgrade(postgresql_tester.alembic_cfg, owner_revision)
        command.downgrade(postgresql_tester.alembic_cfg, parent)

        inspector = inspect(postgresql_tester.engine)
        assert "resource_owner_key" not in {
            column["name"] for column in inspector.get_columns("user_oauth")
        }
        assert "uq_user_provider_account" in {
            constraint["name"]
            for constraint in inspector.get_unique_constraints("user_oauth")
        }
        assert not {
            "uq_user_oauth_ordinary_account",
            "uq_user_oauth_actor_account",
        } & {index["name"] for index in inspector.get_indexes("user_oauth")}
        with postgresql_tester.engine.begin() as conn:
            assert conn.execute(
                text("SELECT provider, access_token FROM user_oauth WHERE id = 9001")
            ).one() == ("gmail", "token")

    def test_sqlite_owner_downgrade_preserves_stripe_head(self, sqlite_tester):
        """Rollback removes owner storage without removing the Stripe seed."""
        owner_revision = "20260818_user_oauth_resource_owner"
        stripe_revision = "20260818_seed_stripe_mcp_app"
        script_dir = ScriptDirectory.from_config(sqlite_tester.alembic_cfg)

        # Not `== [owner_revision]`: that only held while this was the sole
        # unmerged branch. A sibling connector's migration branch merging
        # with this one moves the single head to that merge revision instead
        # -- the actual precondition this test needs is just that "head"
        # resolves unambiguously (one head) and still descends from
        # owner_revision, not that owner_revision is literally still it.
        heads = script_dir.get_heads()
        assert len(heads) == 1
        assert _revision_reached(script_dir, owner_revision, set(heads))

        sqlite_tester.create_metadata_owned_users_table()
        command.upgrade(sqlite_tester.alembic_cfg, "head")
        command.downgrade(sqlite_tester.alembic_cfg, stripe_revision)

        # Not `== {stripe_revision}`: downgrading past a merge revision
        # re-splits the version table back into each parent branch's own
        # tip, and only the branch actually being downgraded (this one)
        # continues past that split -- a sibling branch merged in here (this
        # one has no dependency on the owner revision) legitimately keeps
        # its own tip applied alongside stripe_revision.
        #
        # Plain membership, not _revision_reached: this checks the actual
        # applied version rows, not ancestry, so it still fails if
        # downgrade() silently no-ops and leaves a later descendant of
        # stripe_revision applied instead -- _revision_reached would treat
        # that descendant as "reaching" stripe_revision and miss the bug.
        assert stripe_revision in sqlite_tester.get_alembic_versions()
        assert "resource_owner_key" not in sqlite_tester.get_column_names("user_oauth")
        with sqlite_tester.engine.begin() as conn:
            assert (
                conn.execute(
                    text("SELECT count(*) FROM public_mcp_apps WHERE app_id = 'stripe'")
                ).scalar_one()
                == 1
            )

    def test_sqlite_idempotence_with_sqlalchemy(self, sqlite_tester):
        """Test that migrations are idempotent when tables pre-created by SQLAlchemy.

        This tests the production scenario where SQLAlchemy's Base.metadata.create_all()
        creates tables first, then Alembic migrations run. Migrations should correctly
        detect existing tables/columns and skip already-applied changes.
        """
        # First, create tables using SQLAlchemy (mimics production)
        from xagent.web.models.database import Base

        Base.metadata.create_all(bind=sqlite_tester.engine)

        # Then run migrations - should be idempotent and not fail
        command.upgrade(sqlite_tester.alembic_cfg, "head")

        # Get version after upgrade
        with sqlite_tester.engine.begin() as conn:
            result = conn.execute(text("SELECT version_num FROM alembic_version"))
            version1 = result.scalar()

        # Run migrations again - should still work
        command.upgrade(sqlite_tester.alembic_cfg, "head")

        # Verify version hasn't changed
        with sqlite_tester.engine.begin() as conn:
            result = conn.execute(text("SELECT version_num FROM alembic_version"))
            version2 = result.scalar()

        assert version1 == version2, "Version should not change on re-run"

        # Verify tables still have correct structure
        tables = sqlite_tester.get_table_names()
        assert "agents" in tables
        assert "models" in tables

        agents_columns = sqlite_tester.get_column_names("agents")
        assert "models" in agents_columns
        assert "name" in agents_columns

    @pytest.mark.postgresql
    def test_postgresql_owner_migration_accepts_current_metadata(
        self, postgresql_tester
    ):
        """The owner revision accepts its schema from current metadata."""
        from xagent.web.models.database import Base

        Base.metadata.create_all(bind=postgresql_tester.engine)
        # Older migrations are not all compatible with current pre-created
        # foreign keys. Stamp the immediate parent so this test isolates the
        # owner migration's fresh-schema path.
        command.stamp(
            postgresql_tester.alembic_cfg,
            "b1efe0dbe0af",
        )

        command.upgrade(postgresql_tester.alembic_cfg, "head")

        # Get version after upgrade
        with postgresql_tester.engine.begin() as conn:
            result = conn.execute(text("SELECT version_num FROM alembic_version"))
            version1 = result.scalar()

        # Run migrations again - should still work
        command.upgrade(postgresql_tester.alembic_cfg, "head")

        # Verify version hasn't changed
        with postgresql_tester.engine.begin() as conn:
            result = conn.execute(text("SELECT version_num FROM alembic_version"))
            version2 = result.scalar()

        assert version1 == version2, "Version should not change on re-run"

    @pytest.mark.postgresql
    @pytest.mark.parametrize(
        "drift_sql",
        [
            "ALTER TABLE actor_mcp_server_connections "
            "ALTER COLUMN encrypted_env TYPE TEXT USING encrypted_env::text",
            "ALTER TABLE actor_mcp_server_connections "
            "ALTER COLUMN lifecycle_generation SET DEFAULT "
            "'00000000-0000-0000-0000-000000000000'::uuid",
            "ALTER TABLE actor_mcp_server_connections DROP CONSTRAINT "
            "ck_actor_mcp_server_connections_generation_nonempty; "
            "ALTER TABLE actor_mcp_server_connections ADD CONSTRAINT "
            "ck_actor_mcp_server_connections_generation_nonempty CHECK (1 = 1)",
            "ALTER TABLE actor_mcp_server_connections ADD CONSTRAINT "
            "uq_actor_mcp_server_connections_user_id_drift UNIQUE (user_id)",
        ],
        ids=["encrypted-type", "fixed-uuid-default", "same-name-check", "extra-unique"],
    )
    def test_postgresql_actor_metadata_adoption_rejects_schema_drift(
        self, postgresql_tester, drift_sql
    ):
        """The actor-storage revision fails closed on reflected semantic drift."""
        from xagent.web.models.database import Base

        Base.metadata.create_all(bind=postgresql_tester.engine)
        with postgresql_tester.engine.begin() as conn:
            conn.exec_driver_sql(drift_sql)
        command.stamp(
            postgresql_tester.alembic_cfg,
            "20260901_seed_zendesk_mcp_app",
        )

        with pytest.raises(RuntimeError, match="incompatible schema"):
            command.upgrade(
                postgresql_tester.alembic_cfg,
                "20260909_actor_mcp_connections",
            )

    @pytest.mark.postgresql
    def test_postgresql_actor_create_is_idempotent_under_real_concurrency(
        self, postgresql_tester, monkeypatch
    ):
        """Two database sessions converge on one row without damaging either tx."""
        import xagent.web.services.actor_mcp_connections as service
        from xagent.web.models.database import Base
        from xagent.web.models.public_mcp import PublicMCPApp
        from xagent.web.models.user import User

        Base.metadata.create_all(bind=postgresql_tester.engine)
        session_factory = sessionmaker(bind=postgresql_tester.engine)
        with session_factory.begin() as session:
            user = User(username="pg-race-owner", password_hash="x")
            app = PublicMCPApp(
                app_id="posthog",
                name="posthog",
                transport="stdio",
                launch_config={"command": "stored-command-is-not-trusted"},
            )
            session.add_all([user, app])
            session.flush()
            user_id = user.id

        barrier = threading.Barrier(2)
        thread_state = threading.local()
        real_lookup = service.get_current_actor_mcp_connection_for_create

        def synchronized_initial_lookup(session, **kwargs):
            row = real_lookup(session, **kwargs)
            if row is None and not getattr(thread_state, "initial_lookup_seen", False):
                thread_state.initial_lookup_seen = True
                barrier.wait(timeout=10)
            return row

        monkeypatch.setattr(
            service,
            "get_current_actor_mcp_connection_for_create",
            synchronized_initial_lookup,
        )

        def create(index):
            with session_factory() as session:
                snapshot = service.create_actor_mcp_connection(
                    session,
                    user_id=user_id,
                    resource_owner_key="toby:postgres:race-owner",
                    app_id="posthog",
                    credentials={
                        "POSTHOG_API_KEY": "same-secret",
                        "POSTHOG_HOST": "https://x.test",
                    },
                )
                session.add(
                    User(username=f"pg-race-transaction-{index}", password_hash="x")
                )
                session.commit()
                return snapshot.lifecycle_generation

        with concurrent.futures.ThreadPoolExecutor(max_workers=2) as executor:
            generations = list(executor.map(create, range(2)))

        assert generations[0] == generations[1]
        with session_factory() as session:
            assert (
                session.execute(
                    text("SELECT count(*) FROM actor_mcp_server_connections")
                ).scalar_one()
                == 1
            )
            assert (
                session.execute(
                    text(
                        "SELECT count(*) FROM users WHERE username LIKE 'pg-race-transaction-%'"
                    )
                ).scalar_one()
                == 2
            )

    @pytest.mark.postgresql
    def test_postgresql_durable_lifecycle_cas_has_one_delete_claim_winner(
        self, postgresql_tester
    ):
        """Two real PostgreSQL sessions cannot both own one backend deletion."""
        from datetime import datetime, timedelta, timezone

        from xagent.web.models.database import Base
        from xagent.web.services.durable_sandbox_lifecycle import (
            DurableSandboxLifecycleRepository,
            RegisterLifecycle,
        )

        Base.metadata.create_all(bind=postgresql_tester.engine)
        session_factory = sessionmaker(
            bind=postgresql_tester.engine, expire_on_commit=False
        )
        now = datetime.now(timezone.utc)
        with session_factory() as session:
            fence = DurableSandboxLifecycleRepository(session).register(
                RegisterLifecycle(
                    scope_digest="a" * 64,
                    task_id=42,
                    run_id="run",
                    lease_attempt_id="attempt",
                    turn_digest=None,
                    eligible_at=now - timedelta(minutes=2),
                    owner_lease_expires_at=now - timedelta(minutes=1),
                )
            )
            session.commit()

        barrier = threading.Barrier(2)

        def claim(_index):
            with session_factory() as session:
                barrier.wait(timeout=10)
                result = DurableSandboxLifecycleRepository(session).claim_for_delete(
                    fence,
                    now=now,
                    claim_ttl=timedelta(minutes=1),
                )
                session.commit()
                return result

        with concurrent.futures.ThreadPoolExecutor(max_workers=2) as executor:
            outcomes = list(executor.map(claim, range(2)))

        winners = [outcome for outcome in outcomes if outcome is not None]
        assert len(winners) == 1
        assert winners[0].delete_attempts == 1

    @pytest.mark.postgresql
    def test_postgresql_durable_lifecycle_supports_owned_close_and_late_completion(
        self, postgresql_tester
    ):
        """Owned close and quarantined completion match SQLite semantics."""
        from datetime import datetime, timedelta, timezone

        from xagent.web.models.database import Base
        from xagent.web.models.task import Task, TaskStatus
        from xagent.web.models.user import User
        from xagent.web.services.durable_sandbox_lifecycle import (
            DurableLifecycleConflict,
            DurableSandboxLifecycleRepository,
            RegisterLifecycle,
        )

        Base.metadata.create_all(bind=postgresql_tester.engine)
        session_factory = sessionmaker(
            bind=postgresql_tester.engine, expire_on_commit=False
        )
        now = datetime.now(timezone.utc)
        request = RegisterLifecycle(
            scope_digest="e" * 64,
            task_id=43,
            run_id="run",
            lease_attempt_id="attempt",
            turn_digest=None,
            eligible_at=now - timedelta(minutes=2),
            owner_lease_expires_at=now - timedelta(minutes=1),
        )
        with session_factory() as session:
            repo = DurableSandboxLifecycleRepository(session)
            first = repo.register(request)
            closed = repo.mark_deleting_owned(
                first, now=now, claim_ttl=timedelta(minutes=1)
            )
            assert closed is not None
            successor = repo.register(request)
            assert successor.active_scope_digest == request.scope_digest
            assert closed.active_scope_digest is None
            session.commit()

        with session_factory() as session:
            with pytest.raises(DurableLifecycleConflict):
                DurableSandboxLifecycleRepository(session).register(request)
            session.rollback()

        late_request = RegisterLifecycle(
            scope_digest="d" * 64,
            task_id=45,
            run_id="run",
            lease_attempt_id="attempt",
            turn_digest=None,
            eligible_at=now - timedelta(minutes=2),
            owner_lease_expires_at=now - timedelta(minutes=1),
        )
        with session_factory() as session:
            repo = DurableSandboxLifecycleRepository(session)
            user = User(username="durable-create-pg", password_hash="hash")
            session.add(user)
            session.flush()
            session.add(
                Task(
                    id=late_request.task_id,
                    user_id=user.id,
                    title="durable create CAS",
                    status=TaskStatus.RUNNING,
                    runner_id="runner",
                    run_id=late_request.run_id,
                    lease_attempt_id=late_request.lease_attempt_id,
                    lease_expires_at=now + timedelta(minutes=1),
                )
            )
            session.flush()
            creating = repo.register(late_request)
            begun = repo.begin_create(creating, now=now)
            assert begun is not None
            quarantined = repo.quarantine_create(
                begun, now=now, claim_ttl=timedelta(minutes=1)
            )
            assert quarantined is not None
            late_success = repo.complete_quarantined_create(
                begun, now=now, outcome="success"
            )
            assert late_success is not None
            assert late_success.state == "deleting"
            assert late_success.create_phase == "terminal"
            assert late_success.create_terminal_outcome == "success"
            assert late_success.version == quarantined.version + 1
            session.rollback()

    @pytest.mark.postgresql
    @pytest.mark.parametrize("phase", ["may_publish", "observed"])
    def test_postgresql_durable_create_downgrade_refuses_ambiguous_generation(
        self, postgresql_tester, phase
    ):
        """Downgrade cannot erase an unresolved backend-publication verdict."""
        postgresql_tester.create_metadata_owned_users_table()
        command.upgrade(postgresql_tester.alembic_cfg, "head")
        with postgresql_tester.engine.begin() as connection:
            connection.execute(
                text(
                    "INSERT INTO durable_sandbox_lifecycles "
                    "(scope_digest, active_scope_digest, lifecycle_token, "
                    "backend_lifecycle_digest, owner_token, create_operation_token, "
                    "create_phase, version, state, task_id, run_id, "
                    "lease_attempt_id, eligible_at, owner_lease_expires_at, "
                    "deleting_at, delete_claim_expires_at, delete_attempts) VALUES "
                    "(:scope, NULL, :lifecycle, :backend, :owner, :operation, "
                    ":phase, 1, 'deleting', 1, 'run', 'attempt', NOW(), NOW(), "
                    "NOW(), NOW(), 0)"
                ),
                {
                    "scope": "1" * 64,
                    "lifecycle": "2" * 64,
                    "backend": "3" * 64,
                    "owner": "4" * 64,
                    "operation": "5" * 64,
                    "phase": phase,
                },
            )
        with pytest.raises(RuntimeError, match="ambiguous generation"):
            command.downgrade(
                postgresql_tester.alembic_cfg,
                "20260926_merge_pacing_cleanup",
            )

    @pytest.mark.postgresql
    def test_postgresql_durable_create_safe_downgrade_round_trip(
        self, postgresql_tester
    ):
        postgresql_tester.create_metadata_owned_users_table()
        command.upgrade(postgresql_tester.alembic_cfg, "head")
        command.downgrade(
            postgresql_tester.alembic_cfg,
            "20260926_merge_pacing_cleanup",
        )
        columns = set(postgresql_tester.get_column_names("durable_sandbox_lifecycles"))
        assert "create_phase" not in columns
        assert "create_terminal_outcome" not in columns

        command.upgrade(postgresql_tester.alembic_cfg, "head")
        columns = set(postgresql_tester.get_column_names("durable_sandbox_lifecycles"))
        assert {"create_phase", "create_terminal_outcome"}.issubset(columns)

    @pytest.mark.postgresql
    def test_postgresql_durable_lifecycle_has_one_concurrent_active_winner(
        self, postgresql_tester
    ):
        from datetime import datetime, timedelta, timezone

        from xagent.web.models.database import Base
        from xagent.web.services.durable_sandbox_lifecycle import (
            DurableLifecycleConflict,
            DurableSandboxLifecycleRepository,
            RegisterLifecycle,
        )

        Base.metadata.create_all(bind=postgresql_tester.engine)
        session_factory = sessionmaker(
            bind=postgresql_tester.engine, expire_on_commit=False
        )
        now = datetime.now(timezone.utc)
        request = RegisterLifecycle(
            scope_digest="f" * 64,
            task_id=44,
            run_id="run",
            lease_attempt_id="attempt",
            turn_digest=None,
            eligible_at=now - timedelta(minutes=2),
            owner_lease_expires_at=now - timedelta(minutes=1),
        )
        barrier = threading.Barrier(2)

        def register(_index):
            with session_factory() as session:
                barrier.wait(timeout=10)
                try:
                    result = DurableSandboxLifecycleRepository(session).register(
                        request
                    )
                    session.commit()
                    return result
                except DurableLifecycleConflict:
                    session.rollback()
                    return None

        with concurrent.futures.ThreadPoolExecutor(max_workers=2) as executor:
            outcomes = list(executor.map(register, range(2)))
        assert sum(outcome is not None for outcome in outcomes) == 1

    def test_sqlite_incremental_upgrade(self, sqlite_tester):
        """Test incremental upgrades from b9d890ed31b5 to head on SQLite.

        This tests each migration in the chain to ensure they work correctly
        when run sequentially from an earlier version.

        Note: We start from b9d890ed31b5 instead of base because earlier
        migrations may have issues with table/column assumptions.
        """
        from alembic.script import ScriptDirectory

        # First, create base tables using SQLAlchemy (simulates production database)
        from xagent.web.models.database import Base

        Base.metadata.create_all(bind=sqlite_tester.engine)

        # Start from b9d890ed31b5 (an earlier revision)
        START_REVISION = "b9d890ed31b5"

        # Stamp to starting revision to simulate upgrading from that version
        command.stamp(sqlite_tester.alembic_cfg, START_REVISION)

        # Get all migrations from START_REVISION to head
        script_dir = ScriptDirectory.from_config(sqlite_tester.alembic_cfg)
        revisions = list(script_dir.walk_revisions(START_REVISION, "heads"))
        assert revisions, "Expected migrations from START_REVISION to heads"
        revisions.reverse()  # START_REVISION to head

        # Upgrade one revision at a time
        for revision in revisions:
            command.upgrade(sqlite_tester.alembic_cfg, revision.revision)

            # Verify version
            current_revisions = sqlite_tester.get_alembic_versions()
            assert _revision_reached(
                script_dir, revision.revision, current_revisions
            ), (
                f"{revision.revision} should be present in or behind current "
                f"Alembic heads {sorted(current_revisions)}"
            )

        # After all upgrades, verify that migrations actually added their columns
        # This tests that migrations work, not just that they're idempotent
        agents_columns = sqlite_tester.get_column_names("agents")
        assert "models" in agents_columns, (
            "f79da474c69d should have renamed model_config to models"
        )
        assert "suggested_prompts" in agents_columns, (
            "20250209_add_suggested_prompts should have added column"
        )

        models_columns = sqlite_tester.get_column_names("models")
        assert "_api_key_encrypted" in models_columns, (
            "441d4f5d399c should have encrypted api_key"
        )
        assert "max_tokens" in models_columns, (
            "b74d4cf2f479 should have added max_tokens column"
        )

    @pytest.mark.postgresql
    def test_postgresql_incremental_upgrade(self, postgresql_tester):
        """Test incremental upgrades from b9d890ed31b5 to head on PostgreSQL.

        This tests each migration in the chain to ensure they work correctly
        when run sequentially from an earlier version.

        Note: We start from b9d890ed31b5 instead of base because earlier
        migrations may have issues with table/column assumptions.
        """
        from alembic.script import ScriptDirectory

        # First, create base tables using SQLAlchemy (simulates production database)
        from xagent.web.models.database import Base

        Base.metadata.create_all(bind=postgresql_tester.engine)

        # Start from b9d890ed31b5 (an earlier revision)
        START_REVISION = "b9d890ed31b5"

        # Stamp to starting revision to simulate upgrading from that version
        command.stamp(postgresql_tester.alembic_cfg, START_REVISION)

        # Get all migrations from START_REVISION to head
        script_dir = ScriptDirectory.from_config(postgresql_tester.alembic_cfg)
        revisions = list(script_dir.walk_revisions(START_REVISION, "heads"))
        assert revisions, "Expected migrations from START_REVISION to heads"
        revisions.reverse()  # START_REVISION to head

        # Upgrade one revision at a time
        for revision in revisions:
            command.upgrade(postgresql_tester.alembic_cfg, revision.revision)

            # Verify version
            current_revisions = postgresql_tester.get_alembic_versions()
            assert _revision_reached(
                script_dir, revision.revision, current_revisions
            ), (
                f"{revision.revision} should be present in or behind current "
                f"Alembic heads {sorted(current_revisions)}"
            )

        # After all upgrades, verify that migrations actually added their columns
        agents_columns = postgresql_tester.get_column_names("agents")
        assert "models" in agents_columns, (
            "f79da474c69d should have renamed model_config to models"
        )
        assert "suggested_prompts" in agents_columns, (
            "20250209_add_suggested_prompts should have added column"
        )

        models_columns = postgresql_tester.get_column_names("models")
        assert "_api_key_encrypted" in models_columns, (
            "441d4f5d399c should have encrypted api_key"
        )


if __name__ == "__main__":
    """CLI interface for running migration tests manually."""
    parser = argparse.ArgumentParser(description="Test database migrations")
    parser.add_argument("--db", choices=["sqlite", "postgresql"], default="sqlite")
    parser.add_argument(
        "test",
        choices=["upgrade", "idempotence", "incremental", "downgrade", "all"],
        help="Test to run",
    )

    args = parser.parse_args()

    tester = MigrationTester(args.db)

    try:
        if args.test == "upgrade":
            tester.setup_database()
            tester.create_metadata_owned_users_table()
            command.upgrade(tester.alembic_cfg, "head")
            tester.assert_user_oauth_user_cascade_fk()
            print(f"✅ {args.db.upper()} upgrade test PASSED")
            tester.teardown_database()

        elif args.test == "idempotence":
            tester.setup_database()
            tester.create_metadata_owned_users_table()
            command.upgrade(tester.alembic_cfg, "head")
            command.upgrade(tester.alembic_cfg, "head")
            print(f"✅ {args.db.upper()} idempotence test PASSED")
            tester.teardown_database()

        elif args.test == "incremental":
            tester.setup_database()
            tester.create_metadata_owned_users_table()
            script_dir = ScriptDirectory.from_config(tester.alembic_cfg)
            revisions = list(script_dir.walk_revisions("base", "heads"))
            revisions.reverse()

            for revision in revisions:
                command.upgrade(tester.alembic_cfg, revision.revision)

            print(f"✅ {args.db.upper()} incremental upgrade test PASSED")
            tester.teardown_database()

        elif args.test == "downgrade":
            tester.setup_database()
            tester.create_metadata_owned_users_table()
            command.upgrade(tester.alembic_cfg, "head")
            command.downgrade(tester.alembic_cfg, "base")
            print(f"✅ {args.db.upper()} downgrade test PASSED")
            tester.teardown_database()

        elif args.test == "all":
            tester.setup_database()
            tester.create_metadata_owned_users_table()
            command.upgrade(tester.alembic_cfg, "head")
            command.upgrade(tester.alembic_cfg, "head")
            command.downgrade(tester.alembic_cfg, "base")
            command.upgrade(tester.alembic_cfg, "head")
            print(f"✅ {args.db.upper()} all tests PASSED")
            tester.teardown_database()

    except Exception as e:
        print(f"❌ {args.db.upper()} test FAILED: {e}")
        raise
    except pytest.skip.Exception as e:
        print(f"{args.db.upper()} test SKIPPED: {e.msg}")