1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
|
"""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')
|