"""two admin roles, event ownership, password accounts and JWT session state Revision ID: f2c6b481de95 Revises: e1f4a90c73bd Create Date: 2026-08-18 10:15:00.000000 **Run this with the application stopped, and take a backup first** (`make backup` covers the database and `media/` together). The role change is a Postgres type swap and the ownership column ends `NOT NULL`; both take an ACCESS EXCLUSIVE lock. The tables are tiny and this is a single-tenant deployment, so a plain migration is the right tool — but it is not online. Order is additive-first, destructive-last (design D10), so a failure part-way leaves a system that still runs on the old code: 1. `admin_status`, backfilled from `is_active`, which stays in place for now 2. the credential columns, all nullable — no existing row has a password 3. `event.owner_admin_id`, nullable 4. ensure a bootstrap super admin exists to own things 5. backfill every event's owner to it 6. `owner_admin_id` -> NOT NULL 7. the role type swap 8. drop `is_active` **`viewer` becomes a WITHDRAWN host, deliberately.** A viewer could read and export and nothing else; a host can message guests and delete them. Turning one into the other silently is a privilege escalation performed by a deploy, and nobody reviews a deploy for that. They land in the approval queue instead, where a super admin decides on purpose. **The downgrade is lossy.** Every `host` goes back to `co_host`, because nothing in the new schema records which of them used to be a `viewer`, and the password columns are dropped outright. It restores the shape and not the intent. For anything past a smoke test, restore the backup instead. """ import logging import os from collections.abc import Sequence import sqlalchemy as sa from alembic import op revision: str = "f2c6b481de95" down_revision: str | None = "e1f4a90c73bd" branch_labels: str | Sequence[str] | None = None depends_on: str | Sequence[str] | None = None logger = logging.getLogger("alembic.runtime.migration") #: Last-resort owner for events when no super admin exists at all and no bootstrap email is #: configured. A placeholder address is better than a failed migration here: the operator can #: see it in the roster and transfer away from it, whereas a half-applied migration leaves #: them with neither schema. _FALLBACK_EMAIL = "bootstrap-super-admin@invalid.local" def _bootstrap_email() -> str: configured = (os.environ.get("SEED_SUPER_ADMIN_EMAIL") or "").strip().lower() return configured or _FALLBACK_EMAIL def upgrade() -> None: conn = op.get_bind() # --- 1. Account state ------------------------------------------------------------- admin_status = sa.Enum("pending", "active", "withdrawn", name="admin_status") admin_status.create(conn, checkfirst=True) op.add_column("admin_user", sa.Column("status", admin_status, nullable=True)) # Nothing becomes PENDING retroactively: pending is only ever reached by a first Google # sign-in, and inventing it here would put existing administrators in an approval queue. op.execute( "UPDATE admin_user SET status = CASE WHEN is_active THEN 'active' ELSE 'withdrawn' END" "::admin_status" ) op.alter_column("admin_user", "status", nullable=False) # --- 2. Credentials --------------------------------------------------------------- auth_method = sa.Enum("google", "password", name="auth_method") auth_method.create(conn, checkfirst=True) op.add_column("admin_user", sa.Column("auth_method", auth_method, nullable=True)) # Every account that exists today signs in with Google; there is no other kind yet. op.execute("UPDATE admin_user SET auth_method = 'google'::auth_method") op.alter_column("admin_user", "auth_method", nullable=False) op.add_column("admin_user", sa.Column("username", sa.String(64), nullable=True)) op.add_column("admin_user", sa.Column("password_hash", sa.Text(), nullable=True)) op.add_column( "admin_user", sa.Column("must_change_password", sa.Boolean(), nullable=False, server_default=sa.false()), ) op.add_column( "admin_user", sa.Column("password_expires_at", sa.DateTime(timezone=True), nullable=True) ) op.add_column( "admin_user", sa.Column("password_set_at", sa.DateTime(timezone=True), nullable=True) ) op.add_column( "admin_user", sa.Column("failed_login_count", sa.Integer(), nullable=False, server_default="0"), ) op.add_column( "admin_user", sa.Column("locked_until", sa.DateTime(timezone=True), nullable=True) ) op.add_column( "admin_user", sa.Column("session_epoch", sa.Integer(), nullable=False, server_default="0"), ) op.add_column( "admin_user", sa.Column("first_seen_at", sa.DateTime(timezone=True), nullable=True) ) # Existing accounts have always existed as far as this system can tell. op.execute("UPDATE admin_user SET first_seen_at = created_at") # Case-insensitive, and partial so the many null usernames on Google accounts do not # collide with each other. op.create_index( "uq_admin_user_username_lower", "admin_user", [sa.text("lower(username)")], unique=True, postgresql_where=sa.text("username IS NOT NULL"), ) # --- 3. Event ownership, nullable for now ----------------------------------------- op.add_column("event", sa.Column("owner_admin_id", sa.Uuid(), nullable=True)) # --- 4. Somebody has to own the existing events ----------------------------------- owner_id = conn.execute( sa.text( "SELECT id FROM admin_user " "WHERE role = 'super_admin' AND status = 'active' " "ORDER BY created_at LIMIT 1" ) ).scalar() if owner_id is None: email = _bootstrap_email() logger.warning( "No active super admin exists; provisioning %s to own existing events. " "Transfer ownership to a real account after deploying.", email, ) owner_id = conn.execute( sa.text( """ INSERT INTO admin_user (id, email, name, role, status, auth_method, is_active, must_change_password, failed_login_count, first_seen_at, created_at, updated_at) VALUES (gen_random_uuid(), :email, 'Bootstrap Super Admin', 'super_admin', 'active', 'google', -- `is_active` is dropped at step 8, but it is still here and still -- NOT NULL at step 4, so this insert has to supply it. Only the -- empty-database path reaches this statement, which is why a -- rehearsal against a copy of a populated database cannot catch it. true, false, 0, now(), now(), now()) ON CONFLICT (email) DO UPDATE SET role = 'super_admin', status = 'active' RETURNING id """ ), {"email": email}, ).scalar() # --- 5. Backfill ------------------------------------------------------------------ result = conn.execute( sa.text("UPDATE event SET owner_admin_id = :owner WHERE owner_admin_id IS NULL"), {"owner": owner_id}, ) logger.info("assigned %s existing event(s) to the bootstrap super admin", result.rowcount) # --- 6. Now it can be required ---------------------------------------------------- op.alter_column("event", "owner_admin_id", nullable=False) op.create_index("ix_event_owner_admin_id", "event", ["owner_admin_id"]) op.create_foreign_key( "fk_event_owner_admin_id", "event", "admin_user", ["owner_admin_id"], ["id"], ondelete="RESTRICT", ) # --- 7. The role swap ------------------------------------------------------------- # Postgres can add an enum label and can never remove one, so collapsing three members # to two means a new type. Map before the swap, while both vocabularies still exist. admin_role_v2 = sa.Enum("super_admin", "host", name="admin_role_v2") admin_role_v2.create(conn, checkfirst=True) op.add_column("admin_user", sa.Column("role_v2", admin_role_v2, nullable=True)) op.execute( """ UPDATE admin_user SET role_v2 = CASE WHEN role = 'super_admin' THEN 'super_admin' ELSE 'host' END ::admin_role_v2 """ ) # The escalation guard: a former viewer is a host who has not been approved as one. op.execute("UPDATE admin_user SET status = 'withdrawn'::admin_status WHERE role = 'viewer'") op.drop_column("admin_user", "role") op.alter_column("admin_user", "role_v2", new_column_name="role", nullable=False) op.execute("DROP TYPE admin_role") op.execute("ALTER TYPE admin_role_v2 RENAME TO admin_role") # --- 8. The boolean is now redundant and would only drift ------------------------- op.drop_column("admin_user", "is_active") def downgrade() -> None: conn = op.get_bind() op.add_column( "admin_user", sa.Column("is_active", sa.Boolean(), nullable=False, server_default=sa.true()), ) op.execute("UPDATE admin_user SET is_active = (status = 'active')") # Lossy: nothing here records which hosts used to be viewers. admin_role_v1 = sa.Enum("super_admin", "co_host", "viewer", name="admin_role_v1") admin_role_v1.create(conn, checkfirst=True) op.add_column("admin_user", sa.Column("role_v1", admin_role_v1, nullable=True)) op.execute( """ UPDATE admin_user SET role_v1 = CASE WHEN role = 'super_admin' THEN 'super_admin' ELSE 'co_host' END ::admin_role_v1 """ ) op.drop_column("admin_user", "role") op.alter_column("admin_user", "role_v1", new_column_name="role", nullable=False) op.execute("DROP TYPE admin_role") op.execute("ALTER TYPE admin_role_v1 RENAME TO admin_role") op.drop_constraint("fk_event_owner_admin_id", "event", type_="foreignkey") op.drop_index("ix_event_owner_admin_id", table_name="event") op.drop_column("event", "owner_admin_id") op.drop_index("uq_admin_user_username_lower", table_name="admin_user") for name in ( "first_seen_at", "session_epoch", "locked_until", "failed_login_count", "password_set_at", "password_expires_at", "must_change_password", "password_hash", "username", "auth_method", "status", ): op.drop_column("admin_user", name) sa.Enum(name="auth_method").drop(conn, checkfirst=True) sa.Enum(name="admin_status").drop(conn, checkfirst=True)