From 77d76421829161df6b1ef628b4e6e051a2c3c2ee Mon Sep 17 00:00:00 2001 From: Christophe Besson Date: Sun, 9 Aug 2026 04:35:31 +0200 Subject: feat(hub): add SQLAlchemy 2.0 async DB layer + Alembic Models: User, Node, Group, GroupMember, GEKBundle, RefreshToken, IPLog. Engine configurable via MESHBAY_DATABASE_URL (asyncpg/aiosqlite). Alembic async env.py + initial_schema migration autogenerated. Co-Authored-By: Claude Sonnet 4.6 (1M context) --- packages/meshbay-hub/src/meshbay_hub/db/models.py | 167 ++++++++++++++++++++++ 1 file changed, 167 insertions(+) create mode 100644 packages/meshbay-hub/src/meshbay_hub/db/models.py (limited to 'packages/meshbay-hub/src/meshbay_hub/db/models.py') diff --git a/packages/meshbay-hub/src/meshbay_hub/db/models.py b/packages/meshbay-hub/src/meshbay_hub/db/models.py new file mode 100644 index 0000000..3814f2e --- /dev/null +++ b/packages/meshbay-hub/src/meshbay_hub/db/models.py @@ -0,0 +1,167 @@ +""" +MeshBay Hub — SQLAlchemy 2.0 ORM models. + +Tables: + users — registered users (identity + public keys) + nodes — node announcements + groups — group registry + group_members — group membership + gek_bundles — encrypted GEK per (group, user) + 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) + pk_ed25519: Mapped[str] = mapped_column(String(64), nullable=False) # base64 raw 32B + pk_x25519: Mapped[str] = mapped_column(String(64), nullable=False) # base64 raw 32B + hub_id: Mapped[str] = mapped_column(String(128), nullable=False) + 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") + gek_bundles: Mapped[list["GEKBundle"]] = 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 + 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 + status: Mapped[str] = mapped_column(String(16), default="active") # active|revoked + created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now) + + members: Mapped[list["GroupMember"]] = relationship(back_populates="group") + gek_bundles: Mapped[list["GEKBundle"]] = 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) + 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") + + +# ── GEK bundles ─────────────────────────────────────────────────────────────── + +class GEKBundle(Base): + """Encrypted GEK bundle — opaque to the hub (hub cannot decrypt it).""" + __tablename__ = "gek_bundles" + + group_id: Mapped[str] = mapped_column(ForeignKey("groups.id"), primary_key=True) + user_id: Mapped[str] = mapped_column(ForeignKey("users.id"), primary_key=True) + pk_eph_b64: Mapped[str] = mapped_column(String(64), nullable=False) + nonce_b64: Mapped[str] = mapped_column(String(32), nullable=False) + wrapped_b64: Mapped[str] = mapped_column(String(128), nullable=False) + stored_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now) + + group: Mapped["Group"] = relationship(back_populates="gek_bundles") + user: Mapped["User"] = relationship(back_populates="gek_bundles") + + +# ── 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 + 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"),) + + +# ── IP logs (legal compliance) ──────────────────────────────────────────────── + +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 + 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"), + ) -- cgit v1.2.3