fix(hub): rewrite initial migration to match the ORM models

Six columns (users.pw_version, users.pk_node_ed25519, users.role,
groups.description, refresh_tokens.family_id) and two tables
(notifications, hub_peers) were never created by migrations — they
relied on create_all(), which creates missing tables but never a
missing column. This passed every test (fresh SQLite each run) and
broke every deploy to a wiped PostgreSQL database.

One migration now creates the complete schema. The seven incremental
files are removed; their changes are folded into the initial schema.
Safe on a fresh database only, which is the only case that exists
after the meshbay.org wipe.

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>

8 files changed, 206 insertions, 386 deletions

diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/2041a4060b3c_add_keypair_bundle_federated_groups_.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/2041a4060b3c_add_keypair_bundle_federated_groups_.py
deleted file mode 100644
index a59a3d1..0000000
--- a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/2041a4060b3c_add_keypair_bundle_federated_groups_.py
+++ /dev/null
@@ -1,81 +0,0 @@
-"""add_keypair_bundle_federated_groups_swarm_sources
-
-Revision ID: 2041a4060b3c
-Revises: d28b9caf9f07
-Create Date: 2026-08-09 14:54:20.883227
-
-"""
-from typing import Sequence, Union
-
-from alembic import op
-import sqlalchemy as sa
-
-
-# revision identifiers, used by Alembic.
-revision: str = '2041a4060b3c'
-down_revision: Union[str, Sequence[str], None] = 'd28b9caf9f07'
-branch_labels: Union[str, Sequence[str], None] = None
-depends_on: Union[str, Sequence[str], None] = None
-
-
-def upgrade() -> None:
-    """Upgrade schema."""
-    # ### commands auto generated by Alembic - please adjust! ###
-    op.create_table('content_blocklist',
-    sa.Column('content_hash', sa.String(length=64), nullable=False),
-    sa.Column('reason', sa.String(length=64), nullable=False),
-    sa.Column('added_at', sa.DateTime(timezone=True), nullable=False),
-    sa.Column('added_by', sa.String(length=64), nullable=True),
-    sa.PrimaryKeyConstraint('content_hash')
-    )
-    op.create_table('federated_groups',
-    sa.Column('id', sa.String(length=36), nullable=False),
-    sa.Column('name', sa.String(length=128), nullable=False),
-    sa.Column('source_hub', sa.String(length=128), nullable=False),
-    sa.Column('join_policy', sa.String(length=16), nullable=False),
-    sa.Column('received_at', sa.DateTime(timezone=True), nullable=False),
-    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
-    sa.PrimaryKeyConstraint('id')
-    )
-    op.create_index('ix_federated_groups_name', 'federated_groups', ['name'], unique=False)
-    op.create_index('ix_federated_groups_source', 'federated_groups', ['source_hub'], unique=False)
-    op.create_table('swarm_sources',
-    sa.Column('content_hash', sa.String(length=64), nullable=False),
-    sa.Column('node_id', sa.String(length=36), nullable=False),
-    sa.Column('endpoint', sa.String(length=128), nullable=False),
-    sa.Column('registered_at', sa.DateTime(timezone=True), nullable=False),
-    sa.Column('last_seen', sa.DateTime(timezone=True), nullable=False),
-    sa.PrimaryKeyConstraint('content_hash', 'node_id')
-    )
-    op.create_index('ix_swarm_hash', 'swarm_sources', ['content_hash'], unique=False)
-    op.create_table('content_reports',
-    sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
-    sa.Column('reporter_id', sa.String(length=36), nullable=True),
-    sa.Column('content_hash', sa.String(length=64), nullable=False),
-    sa.Column('group_id', sa.String(length=36), nullable=True),
-    sa.Column('reason', sa.String(length=32), nullable=False),
-    sa.Column('detail', sa.String(length=256), nullable=True),
-    sa.Column('ip_address', sa.String(length=45), nullable=False),
-    sa.Column('reported_at', sa.DateTime(timezone=True), nullable=False),
-    sa.ForeignKeyConstraint(['group_id'], ['groups.id'], ),
-    sa.ForeignKeyConstraint(['reporter_id'], ['users.id'], ),
-    sa.PrimaryKeyConstraint('id')
-    )
-    op.create_index('ix_content_reports_group', 'content_reports', ['group_id'], unique=False)
-    op.create_index('ix_content_reports_hash', 'content_reports', ['content_hash'], unique=False)
-    # ### end Alembic commands ###
-
-
-def downgrade() -> None:
-    """Downgrade schema."""
-    # ### commands auto generated by Alembic - please adjust! ###
-    op.drop_index('ix_content_reports_hash', table_name='content_reports')
-    op.drop_index('ix_content_reports_group', 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_source', table_name='federated_groups')
-    op.drop_index('ix_federated_groups_name', table_name='federated_groups')
-    op.drop_table('federated_groups')
-    op.drop_table('content_blocklist')
-    # ### end Alembic commands ###
diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/3dc91cd4ea52_group_hosted_at.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/3dc91cd4ea52_group_hosted_at.py
deleted file mode 100644
index 57aa9a7..0000000
--- a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/3dc91cd4ea52_group_hosted_at.py
+++ /dev/null
@@ -1,37 +0,0 @@
-"""group hosted_at
-
-Records the first time a node announced that it hosts a group. Groups without
-it are shown to their owner only, and `meshbay-hub prune-groups` collects the
-ones that never got a node.
-
-Backfilled to created_at for every existing group: they predate the rule, and
-starting the clock on them retroactively would delete live groups on the first
-run of the reaper.
-
-Revision ID: 3dc91cd4ea52
-Revises: d1f47a90c3b2
-Create Date: 2026-08-16 03:20:00.575130
-
-"""
-from typing import Sequence, Union
-
-from alembic import op
-import sqlalchemy as sa
-
-
-# revision identifiers, used by Alembic.
-revision: str = '3dc91cd4ea52'
-down_revision: Union[str, Sequence[str], None] = 'd1f47a90c3b2'
-branch_labels: Union[str, Sequence[str], None] = None
-depends_on: Union[str, Sequence[str], None] = None
-
-
-def upgrade() -> None:
-    op.add_column("groups",
-                  sa.Column("hosted_at", sa.DateTime(timezone=True), nullable=True))
-    # Existing groups are grandfathered in rather than left to expire.
-    op.execute("UPDATE groups SET hosted_at = created_at WHERE hosted_at IS NULL")
-
-
-def downgrade() -> None:
-    op.drop_column("groups", "hosted_at")
diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/a7c31f9e40b2_drop_user_identity_keys.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/a7c31f9e40b2_drop_user_identity_keys.py
deleted file mode 100644
index c581e55..0000000
--- a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/a7c31f9e40b2_drop_user_identity_keys.py
+++ /dev/null
@@ -1,37 +0,0 @@
-"""drop_user_identity_keys
-
-The hub published `users.pk_ed25519` / `users.pk_x25519` as a key directory, and
-the invite flow wrapped the group key for whatever it returned — finding H3. Since
-the node wraps the group key itself, for a key its owner proves possession of,
-nothing reads these columns. Identity keys are generated per node and pinned there
-(`meshbay_node/roster.py`), so there is no hub-side key to publish at all.
-
-Downgrade restores the columns, but not their contents: the keys they held were
-never the hub's to reproduce.
-
-Revision ID: a7c31f9e40b2
-Revises: 2041a4060b3c
-Create Date: 2026-08-14
-
-"""
-from typing import Sequence, Union
-
-import sqlalchemy as sa
-from alembic import op
-
-revision: str = 'a7c31f9e40b2'
-down_revision: Union[str, Sequence[str], None] = '2041a4060b3c'
-branch_labels: Union[str, Sequence[str], None] = None
-depends_on: Union[str, Sequence[str], None] = None
-
-
-def upgrade() -> None:
-    op.drop_column('users', 'pk_ed25519')
-    op.drop_column('users', 'pk_x25519')
-
-
-def downgrade() -> None:
-    # Nullable on the way back: the previous schema required them, and nothing
-    # can invent a key that belonged to a user.
-    op.add_column('users', sa.Column('pk_ed25519', sa.String(64), nullable=True))
-    op.add_column('users', sa.Column('pk_x25519', sa.String(64), nullable=True))
diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/b4d82e1c77a9_notification_groups_and_mute.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/b4d82e1c77a9_notification_groups_and_mute.py
deleted file mode 100644
index d66a932..0000000
--- a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/b4d82e1c77a9_notification_groups_and_mute.py
+++ /dev/null
@@ -1,41 +0,0 @@
-"""notification_groups_and_mute
-
-Two columns, both so notifications behave the way people expect.
-
-`notifications.group_id` lets chat keep one row per group and move its date,
-instead of one row per message — a busy conversation should be one line saying
-when it last spoke.
-
-`group_members.muted` moves a setting that existed only in the browser's
-localStorage, where nothing ever read it: turning notifications off for a group
-did nothing at all. Muting now happens where the notification is created, so it
-is not created.
-
-Revision ID: b4d82e1c77a9
-Revises: a7c31f9e40b2
-Create Date: 2026-08-14
-
-"""
-from typing import Sequence, Union
-
-import sqlalchemy as sa
-from alembic import op
-
-revision: str = 'b4d82e1c77a9'
-down_revision: Union[str, Sequence[str], None] = 'a7c31f9e40b2'
-branch_labels: Union[str, Sequence[str], None] = None
-depends_on: Union[str, Sequence[str], None] = None
-
-
-def upgrade() -> None:
-    op.add_column('notifications',
-                  sa.Column('group_id', sa.String(36), sa.ForeignKey('groups.id'),
-                            nullable=True))
-    op.add_column('group_members',
-                  sa.Column('muted', sa.Boolean(), nullable=False,
-                            server_default=sa.false()))
-
-
-def downgrade() -> None:
-    op.drop_column('group_members', 'muted')
-    op.drop_column('notifications', 'group_id')
diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/c5e93b1a2f60_keep_username_on_ip_logs.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/c5e93b1a2f60_keep_username_on_ip_logs.py
deleted file mode 100644
index cfa3229..0000000
--- a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/c5e93b1a2f60_keep_username_on_ip_logs.py
+++ /dev/null
@@ -1,31 +0,0 @@
-"""keep_username_on_ip_logs
-
-The connection log is kept for its legal retention period and is meant to stay
-attributable. It took the name from a join on `users`, and account deletion
-tombstones that row — so the log answered `deleted-3f9a1c` for precisely the
-records someone would be asking about. The name is copied onto the log rows at
-deletion; it stays NULL while the account is alive, where the join is better
-because it cannot go stale.
-
-Revision ID: c5e93b1a2f60
-Revises: b4d82e1c77a9
-Create Date: 2026-08-15
-
-"""
-from typing import Sequence, Union
-
-import sqlalchemy as sa
-from alembic import op
-
-revision: str = 'c5e93b1a2f60'
-down_revision: Union[str, Sequence[str], None] = 'b4d82e1c77a9'
-branch_labels: Union[str, Sequence[str], None] = None
-depends_on: Union[str, Sequence[str], None] = None
-
-
-def upgrade() -> None:
-    op.add_column('ip_logs', sa.Column('username', sa.String(64), nullable=True))
-
-
-def downgrade() -> None:
-    op.drop_column('ip_logs', 'username')
diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d1f47a90c3b2_node_observed_ip.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d1f47a90c3b2_node_observed_ip.py
deleted file mode 100644
index c720cab..0000000
--- a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d1f47a90c3b2_node_observed_ip.py
+++ /dev/null
@@ -1,35 +0,0 @@
-"""node_observed_ip
-
-Where a node was actually seen, as opposed to where it says it is.
-
-`endpoint_hint` is discovered by the node through a STUN server and sent to the
-hub in its announcement — useful for reaching it, and a claim. `observed_ip` is
-the address the announcement itself arrived from, on a request carrying a valid
-Ed25519 signature over a fresh timestamp, so it is the address of whoever holds
-the node key. That is the one an administrator's question is about.
-
-Revision ID: d1f47a90c3b2
-Revises: c5e93b1a2f60
-Create Date: 2026-08-15
-
-"""
-from typing import Sequence, Union
-
-import sqlalchemy as sa
-from alembic import op
-
-revision: str = 'd1f47a90c3b2'
-down_revision: Union[str, Sequence[str], None] = 'c5e93b1a2f60'
-branch_labels: Union[str, Sequence[str], None] = None
-depends_on: Union[str, Sequence[str], None] = None
-
-
-def upgrade() -> None:
-    op.add_column('nodes', sa.Column('observed_ip', sa.String(45), nullable=True))
-    op.add_column('nodes',
-                  sa.Column('last_seen', sa.DateTime(timezone=True), nullable=True))
-
-
-def downgrade() -> None:
-    op.drop_column('nodes', 'last_seen')
-    op.drop_column('nodes', 'observed_ip')
diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d28b9caf9f07_initial_schema.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d28b9caf9f07_initial_schema.py
index 5192eb8..6301426 100644
--- a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d28b9caf9f07_initial_schema.py
+++ b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d28b9caf9f07_initial_schema.py
@@ -1,7 +1,14 @@
 """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: 
+Revises:
 Create Date: 2026-08-09 04:35:07.120021
 
 """
@@ -11,7 +18,6 @@ from alembic import op
 import sqlalchemy as sa
 
 
-# revision identifiers, used by Alembic.
 revision: str = 'd28b9caf9f07'
 down_revision: Union[str, Sequence[str], None] = None
 branch_labels: Union[str, Sequence[str], None] = None
@@ -19,97 +25,220 @@ depends_on: Union[str, Sequence[str], None] = None
 
 
 def upgrade() -> None:
-    """Upgrade schema."""
-    # ### commands auto generated by Alembic - please adjust! ###
+    # ── users ────────────────────────────────────────────────────────────
     op.create_table('users',
-    sa.Column('id', sa.String(length=36), nullable=False),
-    sa.Column('username', sa.String(length=64), nullable=False),
-    sa.Column('email', sa.String(length=256), nullable=False),
-    sa.Column('pw_hash', sa.LargeBinary(), nullable=False),
-    sa.Column('pw_salt', sa.LargeBinary(), nullable=False),
-    sa.Column('pk_ed25519', sa.String(length=64), nullable=False),
-    sa.Column('pk_x25519', sa.String(length=64), nullable=False),
-    sa.Column('hub_id', sa.String(length=128), nullable=False),
-    sa.Column('status', sa.String(length=16), nullable=False),
-    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
-    sa.PrimaryKeyConstraint('id'),
-    sa.UniqueConstraint('username')
+        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_users_email', 'users', ['email'], unique=False)
-    op.create_index('ix_users_username', 'users', ['username'], unique=False)
+    op.create_index('ix_nodes_user_id', 'nodes', ['user_id'])
+
+    # ── groups ───────────────────────────────────────────────────────────
     op.create_table('groups',
-    sa.Column('id', sa.String(length=36), nullable=False),
-    sa.Column('name', sa.String(length=128), nullable=False),
-    sa.Column('admin_id', sa.String(length=36), nullable=False),
-    sa.Column('visibility', sa.String(length=16), nullable=False),
-    sa.Column('join_policy', sa.String(length=16), nullable=False),
-    sa.Column('status', sa.String(length=16), nullable=False),
-    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
-    sa.ForeignKeyConstraint(['admin_id'], ['users.id'], ),
-    sa.PrimaryKeyConstraint('id')
+        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'], unique=False)
+    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(length=36), nullable=True),
-    sa.Column('event', sa.String(length=32), nullable=False),
-    sa.Column('ip_address', sa.String(length=45), nullable=False),
-    sa.Column('detail', sa.String(length=256), nullable=True),
-    sa.Column('timestamp', sa.DateTime(timezone=True), nullable=False),
-    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
-    sa.PrimaryKeyConstraint('id')
+        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_ip', 'ip_logs', ['ip_address'], unique=False)
-    op.create_index('ix_ip_logs_timestamp', 'ip_logs', ['timestamp'], unique=False)
-    op.create_index('ix_ip_logs_user_id', 'ip_logs', ['user_id'], unique=False)
-    op.create_table('nodes',
-    sa.Column('id', sa.String(length=36), nullable=False),
-    sa.Column('user_id', sa.String(length=36), nullable=False),
-    sa.Column('pk_node', sa.String(length=64), nullable=False),
-    sa.Column('endpoint_hint', sa.String(length=128), nullable=True),
-    sa.Column('announced_at', sa.DateTime(timezone=True), nullable=False),
-    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
-    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'),
     )
-    op.create_index('ix_nodes_user_id', 'nodes', ['user_id'], unique=False)
-    op.create_table('refresh_tokens',
-    sa.Column('id', sa.String(length=36), nullable=False),
-    sa.Column('user_id', sa.String(length=36), nullable=False),
-    sa.Column('token_hash', sa.String(length=64), nullable=False),
-    sa.Column('expires_at', sa.DateTime(timezone=True), nullable=False),
-    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
-    sa.Column('revoked', sa.Boolean(), nullable=False),
-    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
-    sa.PrimaryKeyConstraint('id'),
-    sa.UniqueConstraint('token_hash')
+
+    # ── 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_refresh_tokens_hash', 'refresh_tokens', ['token_hash'], unique=False)
-    op.create_table('group_members',
-    sa.Column('group_id', sa.String(length=36), nullable=False),
-    sa.Column('user_id', sa.String(length=36), nullable=False),
-    sa.Column('joined_at', sa.DateTime(timezone=True), nullable=False),
-    sa.ForeignKeyConstraint(['group_id'], ['groups.id'], ),
-    sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
-    sa.PrimaryKeyConstraint('group_id', 'user_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'),
     )
-    # ### end Alembic commands ###
+    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:
-    """Downgrade schema."""
-    # ### commands auto generated by Alembic - please adjust! ###
-    op.drop_table('group_members')
-    op.drop_index('ix_refresh_tokens_hash', table_name='refresh_tokens')
-    op.drop_table('refresh_tokens')
-    op.drop_index('ix_nodes_user_id', table_name='nodes')
-    op.drop_table('nodes')
-    op.drop_index('ix_ip_logs_user_id', table_name='ip_logs')
-    op.drop_index('ix_ip_logs_timestamp', table_name='ip_logs')
+    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')
-    # ### end Alembic commands ###
diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/e5a2b7d31f88_user_device_auth_keys.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/e5a2b7d31f88_user_device_auth_keys.py
deleted file mode 100644
index ddada15..0000000
--- a/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/e5a2b7d31f88_user_device_auth_keys.py
+++ /dev/null
@@ -1,47 +0,0 @@
-"""user device auth keys
-
-Device Ed25519 authentication to the hub, so a client signs in without
-re-deriving a key from the passphrase every time.
-
-Deliberately **not** the key directory that was H3: nothing reads this but the
-hub itself, nobody wraps a group key for it, and it is a different key from the
-per-node identity keys, which never leave the device-node relationship. See
-`docs/desktop-client-v1.md` §5.
-
-`create_all()` is not a migration — it creates missing tables and never a
-missing column, so a schema change without a file here reaches the tests (fresh
-DB every run) and never reaches the deployed hub.
-
-Revision ID: e5a2b7d31f88
-Revises: 3dc91cd4ea52
-"""
-
-from typing import Sequence, Union
-
-import sqlalchemy as sa
-from alembic import op
-
-revision: str = "e5a2b7d31f88"
-down_revision: Union[str, Sequence[str], None] = "3dc91cd4ea52"
-branch_labels: Union[str, Sequence[str], None] = None
-depends_on: Union[str, Sequence[str], None] = None
-
-
-def upgrade() -> None:
-    op.create_table(
-        "user_devices",
-        sa.Column("id", sa.String(36), primary_key=True),
-        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),
-    )
-    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")