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/alembic.ini | 149 ++++++++++++++++++ .../meshbay-hub/src/meshbay_hub/db/__init__.py | 9 ++ packages/meshbay-hub/src/meshbay_hub/db/engine.py | 78 ++++++++++ .../src/meshbay_hub/db/migrations/README | 1 + .../src/meshbay_hub/db/migrations/env.py | 65 ++++++++ .../src/meshbay_hub/db/migrations/script.py.mako | 28 ++++ .../versions/d28b9caf9f07_initial_schema.py | 127 ++++++++++++++++ packages/meshbay-hub/src/meshbay_hub/db/models.py | 167 +++++++++++++++++++++ 8 files changed, 624 insertions(+) create mode 100644 packages/meshbay-hub/alembic.ini create mode 100644 packages/meshbay-hub/src/meshbay_hub/db/engine.py create mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/README create mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/env.py create mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/script.py.mako create mode 100644 packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d28b9caf9f07_initial_schema.py create mode 100644 packages/meshbay-hub/src/meshbay_hub/db/models.py diff --git a/packages/meshbay-hub/alembic.ini b/packages/meshbay-hub/alembic.ini new file mode 100644 index 0000000..fe45eb2 --- /dev/null +++ b/packages/meshbay-hub/alembic.ini @@ -0,0 +1,149 @@ +# A generic, single database configuration. + +[alembic] +# path to migration scripts. +# this is typically a path given in POSIX (e.g. forward slashes) +# format, relative to the token %(here)s which refers to the location of this +# ini file +script_location = %(here)s/src/meshbay_hub/db/migrations + +# template used to generate migration file names; The default value is %%(rev)s_%%(slug)s +# Uncomment the line below if you want the files to be prepended with date and time +# see https://alembic.sqlalchemy.org/en/latest/tutorial.html#editing-the-ini-file +# for all available tokens +# file_template = %%(year)d_%%(month).2d_%%(day).2d_%%(hour).2d%%(minute).2d-%%(rev)s_%%(slug)s +# Or organize into date-based subdirectories (requires recursive_version_locations = true) +# file_template = %%(year)d/%%(month).2d/%%(day).2d_%%(hour).2d%%(minute).2d_%%(second).2d_%%(rev)s_%%(slug)s + +# sys.path path, will be prepended to sys.path if present. +# defaults to the current working directory. for multiple paths, the path separator +# is defined by "path_separator" below. +prepend_sys_path = . + + +# timezone to use when rendering the date within the migration file +# as well as the filename. +# If specified, requires the tzdata library which can be installed by adding +# `alembic[tz]` to the pip requirements. +# string value is passed to ZoneInfo() +# leave blank for localtime +# timezone = + +# max length of characters to apply to the "slug" field +# truncate_slug_length = 40 + +# set to 'true' to run the environment during +# the 'revision' command, regardless of autogenerate +# revision_environment = false + +# set to 'true' to allow .pyc and .pyo files without +# a source .py file to be detected as revisions in the +# versions/ directory +# sourceless = false + +# version location specification; This defaults +# to /versions. When using multiple version +# directories, initial revisions must be specified with --version-path. +# The path separator used here should be the separator specified by "path_separator" +# below. +# version_locations = %(here)s/bar:%(here)s/bat:%(here)s/alembic/versions + +# path_separator; This indicates what character is used to split lists of file +# paths, including version_locations and prepend_sys_path within configparser +# files such as alembic.ini. +# The default rendered in new alembic.ini files is "os", which uses os.pathsep +# to provide os-dependent path splitting. +# +# Note that in order to support legacy alembic.ini files, this default does NOT +# take place if path_separator is not present in alembic.ini. If this +# option is omitted entirely, fallback logic is as follows: +# +# 1. Parsing of the version_locations option falls back to using the legacy +# "version_path_separator" key, which if absent then falls back to the legacy +# behavior of splitting on spaces and/or commas. +# 2. Parsing of the prepend_sys_path option falls back to the legacy +# behavior of splitting on spaces, commas, or colons. +# +# Valid values for path_separator are: +# +# path_separator = : +# path_separator = ; +# path_separator = space +# path_separator = newline +# +# Use os.pathsep. Default configuration used for new projects. +path_separator = os + +# set to 'true' to search source files recursively +# in each "version_locations" directory +# new in Alembic version 1.10 +# recursive_version_locations = false + +# the output encoding used when revision files +# are written from script.py.mako +# output_encoding = utf-8 + +# database URL. This is consumed by the user-maintained env.py script only. +# other means of configuring database URLs may be customized within the env.py +# file. +sqlalchemy.url = driver://user:pass@localhost/dbname + + +[post_write_hooks] +# post_write_hooks defines scripts or Python functions that are run +# on newly generated revision scripts. See the documentation for further +# detail and examples + +# format using "black" - use the console_scripts runner, against the "black" entrypoint +# hooks = black +# black.type = console_scripts +# black.entrypoint = black +# black.options = -l 79 REVISION_SCRIPT_FILENAME + +# lint with attempts to fix using "ruff" - use the module runner, against the "ruff" module +# hooks = ruff +# ruff.type = module +# ruff.module = ruff +# ruff.options = check --fix REVISION_SCRIPT_FILENAME + +# Alternatively, use the exec runner to execute a binary found on your PATH +# hooks = ruff +# ruff.type = exec +# ruff.executable = ruff +# ruff.options = check --fix REVISION_SCRIPT_FILENAME + +# Logging configuration. This is also consumed by the user-maintained +# env.py script only. +[loggers] +keys = root,sqlalchemy,alembic + +[handlers] +keys = console + +[formatters] +keys = generic + +[logger_root] +level = WARNING +handlers = console +qualname = + +[logger_sqlalchemy] +level = WARNING +handlers = +qualname = sqlalchemy.engine + +[logger_alembic] +level = INFO +handlers = +qualname = alembic + +[handler_console] +class = StreamHandler +args = (sys.stderr,) +level = NOTSET +formatter = generic + +[formatter_generic] +format = %(levelname)-5.5s [%(name)s] %(message)s +datefmt = %H:%M:%S diff --git a/packages/meshbay-hub/src/meshbay_hub/db/__init__.py b/packages/meshbay-hub/src/meshbay_hub/db/__init__.py index e69de29..5ef1d3c 100644 --- a/packages/meshbay-hub/src/meshbay_hub/db/__init__.py +++ b/packages/meshbay-hub/src/meshbay_hub/db/__init__.py @@ -0,0 +1,9 @@ +"""Hub database layer.""" +from .engine import init_db, close_db, get_db +from .models import Base, User, Node, Group, GroupMember, GEKBundle, RefreshToken, IPLog + +__all__ = [ + "init_db", "close_db", "get_db", + "Base", "User", "Node", "Group", "GroupMember", + "GEKBundle", "RefreshToken", "IPLog", +] diff --git a/packages/meshbay-hub/src/meshbay_hub/db/engine.py b/packages/meshbay-hub/src/meshbay_hub/db/engine.py new file mode 100644 index 0000000..6ed5238 --- /dev/null +++ b/packages/meshbay-hub/src/meshbay_hub/db/engine.py @@ -0,0 +1,78 @@ +""" +MeshBay Hub — async SQLAlchemy engine and session factory. + +DATABASE_URL env var controls which DB is used: + Production: postgresql+asyncpg://user:pass@localhost/meshbay_hub + Tests: sqlite+aiosqlite:///:memory: (default if not set) +""" + +import os +from collections.abc import AsyncGenerator + +from sqlalchemy.ext.asyncio import ( + AsyncEngine, + AsyncSession, + async_sessionmaker, + create_async_engine, +) + +from meshbay_hub.db.models import Base + +_DEFAULT_URL = "sqlite+aiosqlite:///:memory:" + +def _database_url() -> str: + return os.environ.get("MESHBAY_DATABASE_URL", _DEFAULT_URL) + +# Module-level engine and session factory (initialised in lifespan) +_engine: AsyncEngine | None = None +_session_factory: async_sessionmaker[AsyncSession] | None = None + + +def get_engine() -> AsyncEngine: + if _engine is None: + raise RuntimeError("DB engine not initialised — call init_db() first") + return _engine + + +def get_session_factory() -> async_sessionmaker[AsyncSession]: + if _session_factory is None: + raise RuntimeError("DB not initialised — call init_db() first") + return _session_factory + + +async def init_db(url: str | None = None) -> AsyncEngine: + """Create engine, session factory, and all tables (idempotent).""" + global _engine, _session_factory + + db_url = url or _database_url() + connect_args = {} + if db_url.startswith("sqlite"): + connect_args["check_same_thread"] = False + + _engine = create_async_engine( + db_url, + echo=False, + connect_args=connect_args, + ) + _session_factory = async_sessionmaker( + _engine, expire_on_commit=False, class_=AsyncSession) + + async with _engine.begin() as conn: + await conn.run_sync(Base.metadata.create_all) + + return _engine + + +async def close_db() -> None: + global _engine, _session_factory + if _engine: + await _engine.dispose() + _engine = None + _session_factory = None + + +async def get_db() -> AsyncGenerator[AsyncSession, None]: + """FastAPI dependency — yields an async DB session.""" + factory = get_session_factory() + async with factory() as session: + yield session diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/README b/packages/meshbay-hub/src/meshbay_hub/db/migrations/README new file mode 100644 index 0000000..98e4f9c --- /dev/null +++ b/packages/meshbay-hub/src/meshbay_hub/db/migrations/README @@ -0,0 +1 @@ +Generic single-database configuration. \ No newline at end of file diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/env.py b/packages/meshbay-hub/src/meshbay_hub/db/migrations/env.py new file mode 100644 index 0000000..f572716 --- /dev/null +++ b/packages/meshbay-hub/src/meshbay_hub/db/migrations/env.py @@ -0,0 +1,65 @@ +"""Alembic env.py — async-aware, reads DATABASE_URL from environment.""" + +import asyncio +import os +from logging.config import fileConfig + +from sqlalchemy import pool +from sqlalchemy.engine import Connection +from sqlalchemy.ext.asyncio import async_engine_from_config + +from alembic import context + +from meshbay_hub.db.models import Base + +config = context.config + +if config.config_file_name is not None: + fileConfig(config.config_file_name) + +target_metadata = Base.metadata + +# Allow override via env var (production uses asyncpg, tests may use aiosqlite) +db_url = os.environ.get( + "MESHBAY_DATABASE_URL", + "postgresql+asyncpg://meshbay:meshbay@localhost/meshbay_hub", +) +config.set_main_option("sqlalchemy.url", db_url) + + +def run_migrations_offline() -> None: + context.configure( + url=db_url, + target_metadata=target_metadata, + literal_binds=True, + dialect_opts={"paramstyle": "named"}, + ) + with context.begin_transaction(): + context.run_migrations() + + +def do_run_migrations(connection: Connection) -> None: + context.configure(connection=connection, target_metadata=target_metadata) + with context.begin_transaction(): + context.run_migrations() + + +async def run_async_migrations() -> None: + connectable = async_engine_from_config( + config.get_section(config.config_ini_section, {}), + prefix="sqlalchemy.", + poolclass=pool.NullPool, + ) + async with connectable.connect() as connection: + await connection.run_sync(do_run_migrations) + await connectable.dispose() + + +def run_migrations_online() -> None: + asyncio.run(run_async_migrations()) + + +if context.is_offline_mode(): + run_migrations_offline() +else: + run_migrations_online() diff --git a/packages/meshbay-hub/src/meshbay_hub/db/migrations/script.py.mako b/packages/meshbay-hub/src/meshbay_hub/db/migrations/script.py.mako new file mode 100644 index 0000000..1101630 --- /dev/null +++ b/packages/meshbay-hub/src/meshbay_hub/db/migrations/script.py.mako @@ -0,0 +1,28 @@ +"""${message} + +Revision ID: ${up_revision} +Revises: ${down_revision | comma,n} +Create Date: ${create_date} + +""" +from typing import Sequence, Union + +from alembic import op +import sqlalchemy as sa +${imports if imports else ""} + +# revision identifiers, used by Alembic. +revision: str = ${repr(up_revision)} +down_revision: Union[str, Sequence[str], None] = ${repr(down_revision)} +branch_labels: Union[str, Sequence[str], None] = ${repr(branch_labels)} +depends_on: Union[str, Sequence[str], None] = ${repr(depends_on)} + + +def upgrade() -> None: + """Upgrade schema.""" + ${upgrades if upgrades else "pass"} + + +def downgrade() -> None: + """Downgrade schema.""" + ${downgrades if downgrades else "pass"} 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 new file mode 100644 index 0000000..d4a9aa6 --- /dev/null +++ b/packages/meshbay-hub/src/meshbay_hub/db/migrations/versions/d28b9caf9f07_initial_schema.py @@ -0,0 +1,127 @@ +"""initial_schema + +Revision ID: d28b9caf9f07 +Revises: +Create Date: 2026-08-09 04:35:07.120021 + +""" +from typing import Sequence, Union + +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 +depends_on: Union[str, Sequence[str], None] = None + + +def upgrade() -> None: + """Upgrade schema.""" + # ### commands auto generated by Alembic - please adjust! ### + 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') + ) + op.create_index('ix_users_email', 'users', ['email'], unique=False) + op.create_index('ix_users_username', 'users', ['username'], unique=False) + 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') + ) + op.create_index('ix_groups_name', 'groups', ['name'], unique=False) + 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') + ) + 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_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') + ) + op.create_index('ix_refresh_tokens_hash', 'refresh_tokens', ['token_hash'], unique=False) + op.create_table('gek_bundles', + sa.Column('group_id', sa.String(length=36), nullable=False), + sa.Column('user_id', sa.String(length=36), nullable=False), + sa.Column('pk_eph_b64', sa.String(length=64), nullable=False), + sa.Column('nonce_b64', sa.String(length=32), nullable=False), + sa.Column('wrapped_b64', sa.String(length=128), nullable=False), + sa.Column('stored_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_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') + ) + # ### end Alembic commands ### + + +def downgrade() -> None: + """Downgrade schema.""" + # ### commands auto generated by Alembic - please adjust! ### + op.drop_table('group_members') + op.drop_table('gek_bundles') + 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_ip_logs_ip', table_name='ip_logs') + op.drop_table('ip_logs') + op.drop_index('ix_groups_name', table_name='groups') + op.drop_table('groups') + 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/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