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
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
|
"""
MeshBay Hub — SQLAlchemy 2.0 ORM models.
Tables:
users — registered users (identity + public keys)
nodes — node announcements
groups — group registry
group_members — group membership
refresh_tokens — hashed refresh tokens
ip_logs — connection log for legal compliance (1-year retention)
"""
import uuid
from datetime import datetime, timezone
from sqlalchemy import (
Boolean, DateTime, ForeignKey, Index, Integer,
String, Text, UniqueConstraint,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
def _now() -> datetime:
return datetime.now(timezone.utc)
def _uuid() -> str:
return str(uuid.uuid4())
class Base(DeclarativeBase):
pass
# ── Users ─────────────────────────────────────────────────────────────────────
class User(Base):
__tablename__ = "users"
id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
username: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
email: Mapped[str] = mapped_column(String(256), nullable=False) # kept for recovery
pw_hash: Mapped[bytes] = mapped_column(nullable=False)
pw_salt: Mapped[bytes] = mapped_column(nullable=False)
pw_version: Mapped[int] = mapped_column(Integer, default=1)
# No user identity keys here. The hub published them and the invite flow
# wrapped the group key for whatever it returned, which is finding H3; since
# the node does the wrapping, nothing reads a key from this directory. Keys
# are generated per node and pinned there (meshbay_node/roster.py).
pk_node_ed25519: Mapped[str | None] = mapped_column(String(64), nullable=True) # node daemon key
hub_id: Mapped[str] = mapped_column(String(128), nullable=False)
role: Mapped[str] = mapped_column(String(16), default="user") # user|moderator|admin
status: Mapped[str] = mapped_column(String(16), default="active") # active|suspended|revoked
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
nodes: Mapped[list["Node"]] = relationship(back_populates="user")
group_memberships: Mapped[list["GroupMember"]] = relationship(back_populates="user")
refresh_tokens: Mapped[list["RefreshToken"]] = relationship(back_populates="user")
ip_logs: Mapped[list["IPLog"]] = relationship(back_populates="user")
__table_args__ = (
Index("ix_users_username", "username"),
Index("ix_users_email", "email"),
)
# ── Nodes ─────────────────────────────────────────────────────────────────────
class Node(Base):
__tablename__ = "nodes"
id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
user_id: Mapped[str] = mapped_column(ForeignKey("users.id"), nullable=False)
pk_node: Mapped[str] = mapped_column(String(64), nullable=False) # Ed25519 b64
endpoint_hint: Mapped[str | None] = mapped_column(String(128)) # "ip:port" or null
# The address the hub saw this node connect from, recorded on an announce
# that carried a valid Ed25519 signature over a fresh timestamp. Unlike
# endpoint_hint — which the node discovers through STUN and sends us — this
# is observed rather than claimed, and it is the one to answer questions
# with. IPv4 or IPv6.
observed_ip: Mapped[str | None] = mapped_column(String(45))
last_seen: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
announced_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
user: Mapped["User"] = relationship(back_populates="nodes")
__table_args__ = (Index("ix_nodes_user_id", "user_id"),)
# ── Groups ────────────────────────────────────────────────────────────────────
class Group(Base):
__tablename__ = "groups"
id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
name: Mapped[str] = mapped_column(String(128), nullable=False)
admin_id: Mapped[str] = mapped_column(ForeignKey("users.id"), nullable=False)
visibility: Mapped[str] = mapped_column(String(16), default="private") # public|private
join_policy: Mapped[str] = mapped_column(String(16), default="invite") # open|request|invite
description: Mapped[str | None] = mapped_column(String(512))
status: Mapped[str] = mapped_column(String(16), default="active") # active|suspended|revoked
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
members: Mapped[list["GroupMember"]] = relationship(back_populates="group")
__table_args__ = (Index("ix_groups_name", "name"),)
class GroupMember(Base):
__tablename__ = "group_members"
group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"), primary_key=True)
user_id: Mapped[str] = mapped_column(ForeignKey("users.id"), primary_key=True)
# Set here rather than in the browser: a notification nobody wants should not
# be created at all. It used to be a checkbox in localStorage that nothing
# read, so muting a group did nothing whatsoever.
muted: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
joined_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
group: Mapped["Group"] = relationship(back_populates="members")
user: Mapped["User"] = relationship(back_populates="group_memberships")
# ── Refresh tokens ────────────────────────────────────────────────────────────
class RefreshToken(Base):
__tablename__ = "refresh_tokens"
id: Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
user_id: Mapped[str] = mapped_column(ForeignKey("users.id"), nullable=False)
token_hash: Mapped[str] = mapped_column(String(64), unique=True, nullable=False) # blake3 hex
family_id: Mapped[str] = mapped_column(String(36), nullable=False, default=_uuid)
expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
revoked: Mapped[bool] = mapped_column(Boolean, default=False)
user: Mapped["User"] = relationship(back_populates="refresh_tokens")
__table_args__ = (
Index("ix_refresh_tokens_hash", "token_hash"),
Index("ix_refresh_tokens_family", "family_id"),
)
# ── IP logs (legal compliance) ────────────────────────────────────────────────
class HubPeer(Base):
"""Trusted peer hub for MHP federation."""
__tablename__ = "hub_peers"
hub_id: Mapped[str] = mapped_column(String(128), primary_key=True)
hub_url: Mapped[str] = mapped_column(String(256), nullable=False)
pk_hub_pem: Mapped[str] = mapped_column(Text, nullable=False)
trusted_since: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
class FederatedGroup(Base):
"""
Groups received from peer hubs via MHP federation.
Included in public /v1/groups search results with hub_id attribution.
"""
__tablename__ = "federated_groups"
id: Mapped[str] = mapped_column(String(36), primary_key=True)
name: Mapped[str] = mapped_column(String(128), nullable=False)
source_hub: Mapped[str] = mapped_column(String(128), nullable=False)
join_policy: Mapped[str] = mapped_column(String(16), default="invite")
received_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
__table_args__ = (
Index("ix_federated_groups_source", "source_hub"),
Index("ix_federated_groups_name", "name"),
)
class SwarmSource(Base):
"""
Tracks which nodes can serve a given content hash (public swarm).
Hub maintains this for load-balanced public content delivery.
"""
__tablename__ = "swarm_sources"
content_hash: Mapped[str] = mapped_column(String(64), primary_key=True)
node_id: Mapped[str] = mapped_column(String(36), primary_key=True)
endpoint: Mapped[str] = mapped_column(String(128), nullable=False)
registered_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
last_seen: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
__table_args__ = (Index("ix_swarm_hash", "content_hash"),)
class Notification(Base):
__tablename__ = "notifications"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
user_id: Mapped[str] = mapped_column(ForeignKey("users.id"), nullable=False)
kind: Mapped[str] = mapped_column(String(32), nullable=False)
# Which group this is about, when it is about one. Chat keeps a single row per
# group and moves its date, so a busy conversation is one line that says when
# it last spoke — not forty lines saying it spoke.
group_id: Mapped[str | None] = mapped_column(ForeignKey("groups.id"))
title: Mapped[str] = mapped_column(String(256), nullable=False)
detail: Mapped[str | None] = mapped_column(String(512))
link: Mapped[str | None] = mapped_column(String(256))
read: Mapped[bool] = mapped_column(Boolean, default=False)
created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
user: Mapped["User"] = relationship()
__table_args__ = (
Index("ix_notifications_user", "user_id"),
Index("ix_notifications_created", "created_at"),
)
class ContentReport(Base):
"""
Report of a public content hash for moderation.
Two reports → automatic suspension. Third → admin review needed.
"""
__tablename__ = "content_reports"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
reporter_id: Mapped[str | None] = mapped_column(ForeignKey("users.id"))
content_hash: Mapped[str] = mapped_column(String(64), nullable=False) # blake3 hex
group_id: Mapped[str | None] = mapped_column(ForeignKey("groups.id"))
reason: Mapped[str] = mapped_column(String(32), default="illegal")
detail: Mapped[str | None] = mapped_column(String(256))
ip_address: Mapped[str] = mapped_column(String(45), nullable=False)
reported_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
__table_args__ = (
Index("ix_content_reports_hash", "content_hash"),
Index("ix_content_reports_group", "group_id"),
)
class ContentBlocklist(Base):
"""
Hash-based blocklist for public content.
Populated automatically after threshold reports, or manually by admins.
"""
__tablename__ = "content_blocklist"
content_hash: Mapped[str] = mapped_column(String(64), primary_key=True)
reason: Mapped[str] = mapped_column(String(64), nullable=False)
added_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
added_by: Mapped[str | None] = mapped_column(String(64)) # "auto" or admin username
class IPLog(Base):
"""
Connection log for legal compliance.
Retention: minimum 1 year (LCEN / EU e-Commerce Directive).
Events: account_create, login, login_fail, group_create, group_join, group_leave,
node_announce, token_refresh.
"""
__tablename__ = "ip_logs"
id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
user_id: Mapped[str | None] = mapped_column(ForeignKey("users.id")) # null for failed logins
# The name this account had, written when it is deleted. The username is
# released on deletion and the row itself is tombstoned, so the join that
# normally supplies the name would answer "deleted-3f9a1c" for exactly the
# records the log exists to answer questions about.
username: Mapped[str | None] = mapped_column(String(64))
event: Mapped[str] = mapped_column(String(32), nullable=False)
ip_address: Mapped[str] = mapped_column(String(45), nullable=False) # IPv4 or IPv6
detail: Mapped[str | None] = mapped_column(String(256)) # e.g. username on fail
timestamp: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
user: Mapped["User | None"] = relationship(back_populates="ip_logs")
__table_args__ = (
Index("ix_ip_logs_user_id", "user_id"),
Index("ix_ip_logs_timestamp", "timestamp"),
Index("ix_ip_logs_ip", "ip_address"),
)
|