From 9498ce0a34cc363e8cf9a42575c8fcf7dfe0fd1a Mon Sep 17 00:00:00 2001 From: Christophe Besson Date: Wed, 19 Aug 2026 12:19:02 +0200 Subject: fix(hub): rewrite initial migration to match the ORM models MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit 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 --- ...4060b3c_add_keypair_bundle_federated_groups_.py | 81 ------ .../versions/3dc91cd4ea52_group_hosted_at.py | 37 --- .../a7c31f9e40b2_drop_user_identity_keys.py | 37 --- .../b4d82e1c77a9_notification_groups_and_mute.py | 41 --- .../c5e93b1a2f60_keep_username_on_ip_logs.py | 31 --- .../versions/d1f47a90c3b2_node_observed_ip.py | 35 --- .../versions/d28b9caf9f07_initial_schema.py | 283 +++++++++++++++------ .../versions/e5a2b7d31f88_user_device_auth_keys.py | 47 ---- 8 files changed, 206 insertions(+), 386 deletions(-) delete mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/2041a4060b3c_add_keypair_bundle_federated_groups_.py delete mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/3dc91cd4ea52_group_hosted_at.py delete mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/a7c31f9e40b2_drop_user_identity_keys.py delete mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/b4d82e1c77a9_notification_groups_and_mute.py delete mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/c5e93b1a2f60_keep_username_on_ip_logs.py delete mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d1f47a90c3b2_node_observed_ip.py delete mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/e5a2b7d31f88_user_device_auth_keys.py (limited to 'packages/meshbay-hub') 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") -- cgit v1.2.3