67 lines
2.3 KiB
Python
67 lines
2.3 KiB
Python
"""add recovery_challenges table
|
|
|
|
Revision ID: e5f6a7b8c9d0
|
|
Revises: d4e5f6a7b8c9
|
|
Create Date: 2026-05-17 00:00:00.000000
|
|
|
|
Adds the recovery_challenges table, which stores the challenge-response
|
|
state for account recovery (nonce + expected HMAC proof) in the database
|
|
rather than the Flask session cookie.
|
|
|
|
Why: Flask session cookies are per-worker in Gunicorn. A challenge written
|
|
by worker A is invisible to worker B, so the recovery flow would fail with
|
|
"No active recovery challenge" in any multi-worker deployment.
|
|
|
|
The table has a UNIQUE constraint on user_id (one active challenge per user)
|
|
and an index on expires_at so the APScheduler cleanup job can efficiently
|
|
delete expired rows without a full scan.
|
|
"""
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
from sqlalchemy.dialects.mysql import INTEGER as mysql_INTEGER
|
|
|
|
|
|
revision = 'e5f6a7b8c9d0'
|
|
down_revision = 'd4e5f6a7b8c9'
|
|
branch_labels = None
|
|
depends_on = None
|
|
|
|
|
|
def upgrade():
|
|
op.create_table(
|
|
'recovery_challenges',
|
|
sa.Column('id', mysql_INTEGER(unsigned=True), nullable=False, autoincrement=True),
|
|
# user_id must be INTEGER UNSIGNED to match users.id (UNSIGNED primary key).
|
|
# MySQL 8 enforces strict type compatibility for foreign keys.
|
|
sa.Column('user_id', mysql_INTEGER(unsigned=True), nullable=False),
|
|
sa.Column('nonce', sa.String(64), nullable=False),
|
|
sa.Column('expected_proof', sa.String(64), nullable=False),
|
|
sa.Column('expires_at', sa.DateTime(), nullable=False),
|
|
sa.ForeignKeyConstraint(
|
|
['user_id'], ['users.id'],
|
|
name='fk_recovery_challenges_user_id',
|
|
ondelete='CASCADE',
|
|
),
|
|
sa.PrimaryKeyConstraint('id'),
|
|
sa.UniqueConstraint('user_id', name='uq_recovery_challenges_user_id'),
|
|
mysql_charset='utf8mb4',
|
|
)
|
|
op.create_index(
|
|
'ix_recovery_challenges_user_id',
|
|
'recovery_challenges',
|
|
['user_id'],
|
|
unique=True,
|
|
)
|
|
op.create_index(
|
|
'ix_recovery_challenges_expires_at',
|
|
'recovery_challenges',
|
|
['expires_at'],
|
|
unique=False,
|
|
)
|
|
|
|
|
|
def downgrade():
|
|
op.drop_index('ix_recovery_challenges_expires_at', table_name='recovery_challenges')
|
|
op.drop_index('ix_recovery_challenges_user_id', table_name='recovery_challenges')
|
|
op.drop_table('recovery_challenges')
|