upgrade-db from 2.3.x to 5.x fails: api_token_scopes migration loads not-yet-existing columns/tables via access_scopes → spawner.user

Summary

Upgrading a populated hub database across several major versions (e.g. 2.3.1 → 5.4.5) fails in the 651f5419b74d (api_token_scopes, JupyterHub 3.0) migration with errors such as:

psycopg2.errors.UndefinedColumn: column roles.managed_by_auth does not exist

(or, depending on the SQLAlchemy version and data, relation "shares" does not exist).

The migration is a data-migration that instantiates the current ORM and traverses relationships, so it ends up issuing SELECTs for columns/tables introduced in later JupyterHub versions that do not exist yet at this point in the alembic chain.

This has been partially patched several times but the root path remains unguarded, so it is still reproducible on main / 5.5.0 for a 2.3.x → 5.x upgrade.

Environment

• Upgrading from JupyterHub 2.3.1 (hub alembic head 833da8570507) to 5.4.5 (and reproducible against main).
• PostgreSQL backend, populated database (rows in oauth_clients + spawners with an associated user that has roles).
• jupyterhub upgrade-db (equivalently JupyterHub.upgrade_db = True on startup).

Steps to reproduce

1. Create/populate a hub DB on JupyterHub 2.3.1 (at least one user with a role, one spawner, and its per-server oauth client — i.e. a user who has started a server).
2. Install JupyterHub 5.4.5 (or main).
3. Run jupyterhub upgrade-db.

Result: the upgrade aborts during 833da8570507 -> 651f5419b74d.

Trigger condition / version scope (important): the failure is data-dependent, not version-dependent. It only triggers when there is an oauth_client with an associated spawner (a user who has started a server), because that is the only path that reaches access_scopes → spawner.user. A 2.3.x DB without any started server migrates fine. The bug is not a regression: JupyterHub 5.0.x through 5.5.x are all affected identically (verified: 5.3.0 and 5.4.5 have byte-identical ORM and alembic chains and both fail with server data, both succeed without it). So "it worked for my upgrade" usually just means that database had no started servers.

Traceback (abridged)

[I] Running upgrade 833da8570507 -> 651f5419b74d, api_token_scopes
...
File ".../alembic/versions/651f5419b74d_api_token_scopes.py", line 133, in upgrade
    allowed_scopes.update(access_scopes(oauth_client, db))
File ".../alembic/versions/651f5419b74d_api_token_scopes.py", line 34, in access_scopes
    scopes.add(f"access:servers!server={spawner.user.name}/{spawner.name}")
...
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn)
    column roles.managed_by_auth does not exist
[SQL: SELECT ... roles.managed_by_auth AS roles_managed_by_auth
      FROM users AS users_1 JOIN user_role_map ... JOIN roles ON ...]

Root cause

In upgrade(), the oauth_clients loop calls access_scopes(oauth_client, db), and access_scopes accesses spawner.user.name:

spawner = oauth_client.spawner
if spawner:
    scopes.add(f"access:servers!server={spawner.user.name}/{spawner.name}")

spawner.user lazy-loads a User, and loading a User triggers its selectin relationships from the current ORM:

• User.roles (selectin) → SELECT ... roles.managed_by_auth — column added in 5.0 (manage_roles).
• User.shared_with_me (selectin) → SELECT ... FROM shares — table added in 5.x.

Neither exists yet at this migration step, so the query fails.

The loader options on the oauth_clients query only defer columns on the directly loaded entities:

for oauth_client in db.query(orm.OAuthClient).options(
    selectinload(orm.OAuthClient.allowed_roles).defer(orm.Role.managed_by_auth),
    # (main also adds) selectinload(orm.OAuthClient.spawner).defer(orm.Spawner.display_name),
):
    allowed_scopes.update(access_scopes(oauth_client, db))

They do not cover the cascaded spawner.user → User.roles / User.shared_with_me loads that happen inside access_scopes. The api_tokens loop right above uses raiseload("*"), which is why that loop is safe; the oauth_clients loop omits it.

Why the previous fixes are incomplete

The same crash has been patched incrementally, each time covering one more future column but never the cascade through User:

• d32b574 (2023-06): "avoid eager loading of not-yet-upgraded column".
• 4d8c3cbf (2024-04): .defer(orm.Role.managed_by_auth).
• 25b28970 (2025-12, PR #5192, released in 5.5.0): .defer(orm.Spawner.display_name) ("Fix db upgrade error when running older upgrade script").

Because access_scopes loads spawner.user (a User), and User has selectin relationships to roles (→ managed_by_auth) and shared_with_me (→ shares), the upgrade still fails on main.

Proposed fix

Add raiseload("*") to the oauth_clients query options (mirroring the api_tokens loop), so the migration never triggers lazy/selectin loads of unrelated, possibly-future relationships:

for oauth_client in db.query(orm.OAuthClient).options(
    selectinload(orm.OAuthClient.allowed_roles).defer(orm.Role.managed_by_auth),
    selectinload(orm.OAuthClient.spawner).defer(orm.Spawner.display_name),
    raiseload("*"),
):
    ...

However, access_scopes then accesses spawner.user.name, which would itself be blocked by raiseload("*"). So spawner.user needs to be eagerly loaded too — and crucially without pulling User's future selectin relationships. Options:

• eager-load the needed path explicitly, e.g. selectinload(orm.OAuthClient.spawner).selectinload(orm.Spawner.user).load_only(orm.User.name), combined with raiseload("*"), or
• replace the ORM access in access_scopes with raw SQL for the spawner → user.name lookup (as the function already does for the services branch), avoiding the ORM entirely.

More generally: data-migrations that instantiate the live ORM are fragile across schema evolution. Using core/text() queries (or Bundle/load_only + raiseload("*")) for the columns actually needed would make these migrations robust to future model changes.

Workaround (for reference)

For deployments that cannot install an older JupyterHub to do a staged upgrade, the hub DB can be brought to the current schema in a single process by (1) pre-creating the future tables/columns the ORM references so the legacy migration's loads succeed, (2) making alembic.op.add_column / create_table idempotent for the duration, and (3) running alembic upgrade head in-process. The real migrations (data transforms included) then run unmodified.