"""Migration #4: the account system's tables, the overrides table, the seven who-columns. Schema only (build plan §2): every column NULL or server-defaulted; every data move is an idempotent `python -m app.cli backfill ` run after the deploy (`accounts`, `levels`, `authors`, `overrides`), never Python here. Design: `.logs/planning/accounts-2026-09.md` §4.2 to §4.4, §4.10 and §6. Revises Stream D's migration #5 (articles), the single head. 1. `accounts` gains username (unique, the kit's key), status, last_login_at, disabled_at, invited_by_id. Existing rows read `status='active'` from the server default. 2. `account_credentials`: the hash lives off the principal row. 3. `account_levels`, `account_members`, `account_grants`: the kit's membership, mapped. 4. `sessions`: opaque tokens hashed at rest; View As state on the row. 5. `account_tokens`: welcome and reset links, hashed, single use. 6. `audit_log`. 7. `overrides` exactly as build plan §4 item 1 (a reader exists: award_picker.pin_from_override). 8. The seven who-columns, each `Integer NULL FK accounts.id`. WARNING (from the kit's own migration note, kept here so a later seed cannot reintroduce a solved bug): seed levels APPEND-ONLY, never rewrite one a live app may be assigning; never auto-write a per-instance grant below a member's app-wide level. Revision ID: b5c6d7e8f9a0 Revises: a4b5c6d7e8f9 Create Date: 2026-09-10 """ import sqlalchemy as sa from sqlalchemy.dialects import postgresql from alembic import op revision = "b5c6d7e8f9a0" down_revision = "a4b5c6d7e8f9" branch_labels = None depends_on = None JSONB = postgresql.JSONB() # (table, column) for the seven who-columns, one FK each. WHO_COLUMNS = [ ("discussion_comments", "author_id"), ("feature_priorities", "author_id"), ("quote_selections", "author_id"), ("quote_requests", "author_id"), ("client_todos", "completed_by_id"), ("client_uploads", "uploaded_by_id"), ("owner_item_states", "acted_by_id"), ] def upgrade() -> None: # 1. accounts op.add_column("accounts", sa.Column("username", sa.String(64), nullable=True)) op.add_column("accounts", sa.Column("status", sa.String(12), nullable=False, server_default="active")) op.add_column("accounts", sa.Column("last_login_at", sa.DateTime(timezone=True), nullable=True)) op.add_column("accounts", sa.Column("disabled_at", sa.DateTime(timezone=True), nullable=True)) op.add_column("accounts", sa.Column("invited_by_id", sa.Integer(), nullable=True)) op.create_unique_constraint("uq_accounts_username", "accounts", ["username"]) op.create_foreign_key("fk_accounts_invited_by", "accounts", "accounts", ["invited_by_id"], ["id"]) # 2. account_credentials op.create_table( "account_credentials", sa.Column("account_id", sa.Integer(), sa.ForeignKey("accounts.id"), primary_key=True), sa.Column("password_hash", sa.Text(), nullable=True), sa.Column("password_set_at", sa.DateTime(timezone=True), nullable=True), sa.Column("must_change_password", sa.Boolean(), nullable=False, server_default="false"), sa.Column("failed_logins", sa.Integer(), nullable=False, server_default="0"), sa.Column("failed_window_started_at", sa.DateTime(timezone=True), nullable=True), sa.Column("locked_until", sa.DateTime(timezone=True), nullable=True), sa.Column("google_sub", sa.String(64), nullable=True, unique=True), ) # 3. the kit's membership op.create_table( "account_levels", sa.Column("name", sa.String(64), primary_key=True), sa.Column("permissions", JSONB, nullable=False, server_default="[]"), sa.Column("assignable", JSONB, nullable=False, server_default="[]"), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), ) op.create_table( "account_members", sa.Column("username", sa.String(64), sa.ForeignKey("accounts.username"), primary_key=True), sa.Column("level", sa.String(64), sa.ForeignKey("account_levels.name"), nullable=False), sa.Column("all_instances", sa.Boolean(), nullable=False, server_default="false"), sa.Column("active", sa.Boolean(), nullable=False, server_default="true"), sa.Column("added_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), ) op.create_table( "account_grants", sa.Column("username", sa.String(64), sa.ForeignKey("accounts.username"), primary_key=True), sa.Column("instance_id", sa.String(64), primary_key=True), sa.Column("level", sa.String(64), sa.ForeignKey("account_levels.name"), nullable=False), ) # 4. sessions op.create_table( "sessions", sa.Column("token_hash", sa.String(64), primary_key=True), sa.Column("account_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False), sa.Column("last_seen_at", sa.DateTime(timezone=True), nullable=True), sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True), sa.Column("revoked_by_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True), sa.Column("revoke_reason", sa.String(40), nullable=True), sa.Column("amr", sa.String(40), nullable=True), sa.Column("acting_as", sa.String(64), sa.ForeignKey("accounts.username"), nullable=True), sa.Column("acting_mode", sa.String(12), nullable=True), sa.Column("ip", sa.String(64), nullable=True), sa.Column("user_agent", sa.String(300), nullable=True), sa.Column("data", JSONB, nullable=False, server_default="{}"), ) op.create_index("ix_sessions_account_id", "sessions", ["account_id"]) op.create_index("ix_sessions_expires_at", "sessions", ["expires_at"]) op.create_index( "ix_sessions_acting_as", "sessions", ["acting_as"], postgresql_where=sa.text("acting_as IS NOT NULL"), ) # 5. account_tokens op.create_table( "account_tokens", sa.Column("token_hash", sa.String(64), primary_key=True), sa.Column("account_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=False), sa.Column("mode", sa.String(12), nullable=False), sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), sa.Column("expires_at", sa.DateTime(timezone=True), nullable=False), sa.Column("used_at", sa.DateTime(timezone=True), nullable=True), sa.Column("created_by_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True), ) op.create_index("ix_account_tokens_account_id", "account_tokens", ["account_id"]) # 6. audit_log op.create_table( "audit_log", sa.Column("id", sa.Integer(), primary_key=True), sa.Column("at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), sa.Column("account_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True), sa.Column("acting_as_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True), sa.Column("session_hash", sa.String(64), nullable=True), sa.Column("action", sa.String(60), nullable=False), sa.Column("entity_type", sa.String(40), nullable=True), sa.Column("entity_key", sa.String(120), nullable=True), sa.Column("detail", JSONB, nullable=True), sa.Column("ip", sa.String(64), nullable=True), ) op.create_index("ix_audit_log_at", "audit_log", ["at"]) op.create_index("ix_audit_log_account_id", "audit_log", ["account_id"]) op.create_index("ix_audit_log_action", "audit_log", ["action"]) # 7. overrides op.create_table( "overrides", sa.Column("id", sa.Integer(), primary_key=True), sa.Column("entity_type", sa.String(40), nullable=False), sa.Column("entity_key", sa.Text(), nullable=False), sa.Column("field", sa.String(60), nullable=False), sa.Column("value", JSONB, nullable=True), sa.Column("collected_value", JSONB, nullable=True), sa.Column("set_by", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True), sa.Column("set_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False), sa.Column("reason", sa.Text(), nullable=True), sa.Column("collector_disagrees_since", sa.DateTime(timezone=True), nullable=True), sa.UniqueConstraint("entity_type", "entity_key", "field", name="uq_overrides_entity_field"), ) # 8. the who-columns for table, column in WHO_COLUMNS: op.add_column(table, sa.Column(column, sa.Integer(), nullable=True)) op.create_foreign_key(f"fk_{table}_{column}", table, "accounts", [column], ["id"]) op.create_index("ix_discussion_comments_author_id", "discussion_comments", ["author_id"]) def downgrade() -> None: op.drop_index("ix_discussion_comments_author_id", table_name="discussion_comments") for table, column in reversed(WHO_COLUMNS): op.drop_constraint(f"fk_{table}_{column}", table, type_="foreignkey") op.drop_column(table, column) op.drop_table("overrides") for name in ("ix_audit_log_action", "ix_audit_log_account_id", "ix_audit_log_at"): op.drop_index(name, table_name="audit_log") op.drop_table("audit_log") op.drop_index("ix_account_tokens_account_id", table_name="account_tokens") op.drop_table("account_tokens") for name in ("ix_sessions_acting_as", "ix_sessions_expires_at", "ix_sessions_account_id"): op.drop_index(name, table_name="sessions") op.drop_table("sessions") op.drop_table("account_grants") op.drop_table("account_members") op.drop_table("account_levels") op.drop_table("account_credentials") op.drop_constraint("fk_accounts_invited_by", "accounts", type_="foreignkey") op.drop_constraint("uq_accounts_username", "accounts", type_="unique") for column in ("invited_by_id", "disabled_at", "last_login_at", "status", "username"): op.drop_column("accounts", column)