"""initial_schema Complete schema matching the ORM models as of 2026-08-19. Replaces the original incremental chain (d28b9caf → … → e5a2b7d) which accumulated six columns and two tables that were never migrated — they relied on create_all(), which creates missing tables but never a missing column. Safe on a fresh database only. Any existing database must be wiped first. Revision ID: d28b9caf9f07 Revises: Create Date: 2026-08-09 04:35:07.120021 """ from collections.abc import Sequence import sqlalchemy as sa from alembic import op revision: str = 'd28b9caf9f07' down_revision: str | Sequence[str] | None = None branch_labels: str | Sequence[str] | None = None depends_on: str | Sequence[str] | None = None def upgrade() -> None: # ── users ──────────────────────────────────────────────────────────── op.create_table('users', sa.Column('id', sa.String(36), nullable=False), sa.Column('username', sa.String(64), nullable=False), sa.Column('email', sa.String(256), nullable=False), sa.Column('pw_hash', sa.LargeBinary(), nullable=False), sa.Column('pw_salt', sa.LargeBinary(), nullable=False), sa.Column('pw_version', sa.Integer(), nullable=False, server_default='1'), sa.Column('pk_node_ed25519', sa.String(64), nullable=True), sa.Column('hub_id', sa.String(128), nullable=False), sa.Column('role', sa.String(16), nullable=False, server_default='user'), sa.Column('status', sa.String(16), nullable=False, server_default='active'), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('username'), ) op.create_index('ix_users_username', 'users', ['username']) op.create_index('ix_users_email', 'users', ['email']) # ── nodes ──────────────────────────────────────────────────────────── op.create_table('nodes', sa.Column('id', sa.String(36), nullable=False), sa.Column('user_id', sa.String(36), sa.ForeignKey('users.id'), nullable=False), sa.Column('pk_node', sa.String(64), nullable=False), sa.Column('endpoint_hint', sa.String(128), nullable=True), sa.Column('observed_ip', sa.String(45), nullable=True), sa.Column('last_seen', sa.DateTime(timezone=True), nullable=True), sa.Column('announced_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint('id'), ) op.create_index('ix_nodes_user_id', 'nodes', ['user_id']) # ── groups ─────────────────────────────────────────────────────────── op.create_table('groups', sa.Column('id', sa.String(36), nullable=False), sa.Column('name', sa.String(128), nullable=False), sa.Column('admin_id', sa.String(36), sa.ForeignKey('users.id'), nullable=False), sa.Column('visibility', sa.String(16), nullable=False, server_default='private'), sa.Column('join_policy', sa.String(16), nullable=False, server_default='invite'), sa.Column('description', sa.String(512), nullable=True), sa.Column('status', sa.String(16), nullable=False, server_default='active'), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column('hosted_at', sa.DateTime(timezone=True), nullable=True), sa.PrimaryKeyConstraint('id'), ) op.create_index('ix_groups_name', 'groups', ['name']) # ── group_members ──────────────────────────────────────────────────── op.create_table('group_members', sa.Column('group_id', sa.String(36), sa.ForeignKey('groups.id'), nullable=False), sa.Column('user_id', sa.String(36), sa.ForeignKey('users.id'), nullable=False), sa.Column('muted', sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column('joined_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint('group_id', 'user_id'), ) # ── refresh_tokens ─────────────────────────────────────────────────── op.create_table('refresh_tokens', sa.Column('id', sa.String(36), nullable=False), sa.Column('user_id', sa.String(36), sa.ForeignKey('users.id'), nullable=False), sa.Column('token_hash', sa.String(64), nullable=False, unique=True), sa.Column('family_id', sa.String(36), nullable=False), sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column('revoked', sa.Boolean(), nullable=False, server_default=sa.false()), sa.PrimaryKeyConstraint('id'), ) op.create_index('ix_refresh_tokens_hash', 'refresh_tokens', ['token_hash']) op.create_index('ix_refresh_tokens_family', 'refresh_tokens', ['family_id']) # ── ip_logs ────────────────────────────────────────────────────────── op.create_table('ip_logs', sa.Column('id', sa.Integer(), autoincrement=True, nullable=False), sa.Column('user_id', sa.String(36), sa.ForeignKey('users.id'), nullable=True), sa.Column('username', sa.String(64), nullable=True), sa.Column('event', sa.String(32), nullable=False), sa.Column('ip_address', sa.String(45), nullable=False), sa.Column('detail', sa.String(256), nullable=True), sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint('id'), ) op.create_index('ix_ip_logs_user_id', 'ip_logs', ['user_id']) op.create_index('ix_ip_logs_timestamp', 'ip_logs', ['timestamp']) op.create_index('ix_ip_logs_ip', 'ip_logs', ['ip_address']) # ── hub_peers ──────────────────────────────────────────────────────── op.create_table('hub_peers', sa.Column('hub_id', sa.String(128), nullable=False), sa.Column('hub_url', sa.String(256), nullable=False), sa.Column('pk_hub_pem', sa.Text(), nullable=False), sa.Column('trusted_since', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint('hub_id'), ) # ── federated_groups ───────────────────────────────────────────────── op.create_table('federated_groups', sa.Column('id', sa.String(36), nullable=False), sa.Column('name', sa.String(128), nullable=False), sa.Column('source_hub', sa.String(128), nullable=False), sa.Column('join_policy', sa.String(16), nullable=False, server_default='invite'), sa.Column('received_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint('id'), ) op.create_index('ix_federated_groups_source', 'federated_groups', ['source_hub']) op.create_index('ix_federated_groups_name', 'federated_groups', ['name']) # ── swarm_sources ──────────────────────────────────────────────────── op.create_table('swarm_sources', sa.Column('content_hash', sa.String(64), nullable=False), sa.Column('node_id', sa.String(36), nullable=False), sa.Column('endpoint', sa.String(128), nullable=False), sa.Column('registered_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column('last_seen', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint('content_hash', 'node_id'), ) op.create_index('ix_swarm_hash', 'swarm_sources', ['content_hash']) # ── content_reports ────────────────────────────────────────────────── op.create_table('content_reports', sa.Column('id', sa.Integer(), autoincrement=True, nullable=False), sa.Column('reporter_id', sa.String(36), sa.ForeignKey('users.id'), nullable=True), sa.Column('content_hash', sa.String(64), nullable=False), sa.Column('group_id', sa.String(36), sa.ForeignKey('groups.id'), nullable=True), sa.Column('reason', sa.String(32), nullable=False, server_default='illegal'), sa.Column('detail', sa.String(256), nullable=True), sa.Column('ip_address', sa.String(45), nullable=False), sa.Column('reported_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint('id'), ) op.create_index('ix_content_reports_hash', 'content_reports', ['content_hash']) op.create_index('ix_content_reports_group', 'content_reports', ['group_id']) # ── content_blocklist ──────────────────────────────────────────────── op.create_table('content_blocklist', sa.Column('content_hash', sa.String(64), nullable=False), sa.Column('reason', sa.String(64), nullable=False), sa.Column('added_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column('added_by', sa.String(64), nullable=True), sa.PrimaryKeyConstraint('content_hash'), ) # ── notifications ──────────────────────────────────────────────────── op.create_table('notifications', sa.Column('id', sa.Integer(), autoincrement=True, nullable=False), sa.Column('user_id', sa.String(36), sa.ForeignKey('users.id'), nullable=False), sa.Column('kind', sa.String(32), nullable=False), sa.Column('group_id', sa.String(36), sa.ForeignKey('groups.id'), nullable=True), sa.Column('title', sa.String(256), nullable=False), sa.Column('detail', sa.String(512), nullable=True), sa.Column('link', sa.String(256), nullable=True), sa.Column('read', sa.Boolean(), nullable=False, server_default=sa.false()), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.PrimaryKeyConstraint('id'), ) op.create_index('ix_notifications_user', 'notifications', ['user_id']) op.create_index('ix_notifications_created', 'notifications', ['created_at']) # ── user_devices ───────────────────────────────────────────────────── op.create_table('user_devices', sa.Column('id', sa.String(36), nullable=False), sa.Column('user_id', sa.String(36), sa.ForeignKey('users.id'), nullable=False), sa.Column('pk_auth_ed25519', sa.String(64), nullable=False, unique=True), sa.Column('label', sa.String(64), nullable=False, server_default=''), sa.Column('created_at', sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()), sa.Column('last_seen', sa.DateTime(timezone=True), nullable=True), sa.PrimaryKeyConstraint('id'), ) op.create_index('ix_user_devices_user', 'user_devices', ['user_id']) def downgrade() -> None: op.drop_index('ix_user_devices_user', table_name='user_devices') op.drop_table('user_devices') op.drop_index('ix_notifications_created', table_name='notifications') op.drop_index('ix_notifications_user', table_name='notifications') op.drop_table('notifications') op.drop_table('content_blocklist') op.drop_index('ix_content_reports_group', table_name='content_reports') op.drop_index('ix_content_reports_hash', table_name='content_reports') op.drop_table('content_reports') op.drop_index('ix_swarm_hash', table_name='swarm_sources') op.drop_table('swarm_sources') op.drop_index('ix_federated_groups_name', table_name='federated_groups') op.drop_index('ix_federated_groups_source', table_name='federated_groups') op.drop_table('federated_groups') op.drop_table('hub_peers') op.drop_index('ix_ip_logs_ip', table_name='ip_logs') op.drop_index('ix_ip_logs_timestamp', table_name='ip_logs') op.drop_index('ix_ip_logs_user_id', table_name='ip_logs') op.drop_table('ip_logs') op.drop_index('ix_refresh_tokens_family', table_name='refresh_tokens') op.drop_index('ix_refresh_tokens_hash', table_name='refresh_tokens') op.drop_table('refresh_tokens') op.drop_table('group_members') op.drop_index('ix_groups_name', table_name='groups') op.drop_table('groups') op.drop_index('ix_nodes_user_id', table_name='nodes') op.drop_table('nodes') op.drop_index('ix_users_username', table_name='users') op.drop_index('ix_users_email', table_name='users') op.drop_table('users')