path: root/packages/meshbay-hub/src/meshbay_hub/db/models.py

"""
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)
    # First time a node registered on /v1/nodes/ws announcing that it hosts this
    # group. Until then the group has no files, no key and nobody to serve it, so
    # it is shown to its owner only and is what `prune-groups` collects. Set once
    # and never cleared: a node going offline does not un-host a group.
    hosted_at:   Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    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 UserDevice(Base):
    """
    A device's key for authenticating **to the hub**, and nothing else.

    This is not a reintroduction of the key directory that was H3, and the
    distinction is worth being precise about because it looks like one:

      * **Nobody reads this but the hub.** No endpoint publishes it, nothing
        wraps a group key for it, and no node ever asks for it. H3 was a
        directory *others* read from, where a substituted key was handed the
        GEK by an honest member.
      * **It is not a node identity key.** Those are generated per node, pinned
        there, and never leave that relationship (`docs/per-node-identity-v1.md`).
        A device holds one of these *plus* a different key per node, so nothing
        here correlates a person across operators.

    What it does cost, stated plainly: the hub now knows how many devices an
    account has and when each one last signed in. That is new metadata, and it
    is the price of not deriving a key from the passphrase on every sign-in.
    """

    __tablename__ = "user_devices"

    id:         Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    user_id:    Mapped[str] = mapped_column(ForeignKey("users.id"), nullable=False)
    # base64 raw Ed25519, unique so one device key belongs to one account
    pk_auth_ed25519: Mapped[str] = mapped_column(String(64), unique=True, nullable=False)
    label:      Mapped[str] = mapped_column(String(64), default="")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    last_seen:  Mapped[datetime | None] = mapped_column(
        DateTime(timezone=True), nullable=True)

    __table_args__ = (Index("ix_user_devices_user", "user_id"),)


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"),
    )