python -m adcp migrate can read another schema's adcp_schema_migrations and skip a ledger that still needs upgrading #1838

applied_migrations() decides which migrations a schema already has from whatever adcp_schema_migrations the search_path resolves to first. That can be another schema's table, so python -m adcp migrate reports "up to date" and skips a ledger that still needs upgrading.

packages/adcp-reporting-storage/src/adcp_reporting_storage/migrations/__init__.py at adcp-reporting-storage-v0.2.0-beta.1:

# line 149
cursor = await connection.execute("SELECT to_regclass('adcp_schema_migrations') IS NOT NULL")
...
cursor = await connection.execute("SELECT version, applied_at FROM adcp_schema_migrations")

Everything else in that module scopes to current_schema(): the advisory lock (line 61) is keyed on hashtext(current_schema()), and the docstring at line 179 says "search_path selects the schema". to_regclass and the unqualified SELECT, though, walk the whole search_path.

Repro (PostgreSQL 15, published wheels)

1. Create a 0.2.0b0 ledger in schema tenant.
2. Create a ledger already migrated by 0.2.0b1 in public.
3. Run python -m adcp migrate with search_path=tenant,public.

Result: it prints "up to date" and applies nothing to tenant. check_schema passes the version check for the same reason. create_schema() is not affected.

Suggested fix

Resolve the table in current_schema() only, for example:

SELECT to_regclass(format('%I.adcp_schema_migrations', current_schema())) IS NOT NULL

Then read from the same qualified name. Add a regression test with two schemas on one search_path.

Found during a post-release review of the #1787 migration.

🤖 Generated with Claude Code