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 UTC, datetime

from sqlalchemy import (
    Boolean,
    DateTime,
    ForeignKey,
    Index,
    Integer,
    String,
    Text,
    text,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


def _now() -> datetime:
    return datetime.now(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).
    email_hash:  Mapped[str | None] = mapped_column(String(64), nullable=True)  # HMAC blind index
    pk_node_ed25519: Mapped[str | None] = mapped_column(String(64), nullable=True)
    hub_id:           Mapped[str] = mapped_column(String(128), nullable=False)
    # user|moderator|admin
    role:             Mapped[str] = mapped_column(String(16), default="user")
    # active|suspended|revoked
    status:           Mapped[str] = mapped_column(String(16), default="active")
    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"),
        Index("ix_users_email_hash", "email_hash", unique=True),
    )


# ── 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))
    # active|suspended|revoked
    status:      Mapped[str] = mapped_column(String(16), default="active")
    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))
    last_activity_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=_now, nullable=False)

    members:     Mapped[list["GroupMember"]] = relationship(back_populates="group")

    __table_args__ = (
        Index("ix_groups_name", "name"),
        # One name per owner, case-insensitively. The group's identity stays its
        # UUID; this is what makes `name@owner` a handle a human can rely on
        # (two different owners may still both have a "photos"). Enforced in the
        # DB so a race cannot slip a second one past the check in create_group.
        Index("uq_groups_owner_name", "admin_id", text("lower(name)"), unique=True),
    )


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


class GroupInviteLink(Base):
    """
    The hub's half of an invitation link (docs/MESHBAY_DESIGN.md §7.3).

    A link carries two secrets. The node's code decides whether someone gets the
    group key, and the hub never sees it. This row decides whether someone may
    *reach* the node at all — membership, which is all the hub has to give — and
    only for the account whose verified address matches `email_hash`. The ticket
    is stored as `sha256(ticket)`, so a copy of this table opens nothing.

    No address in the clear: `email_hash` is the same blind index `users` has,
    and `email_masked` is what the owner's list shows (`al***@ex***.com`).
    """
    __tablename__ = "group_invite_links"

    id:             Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    group_id:       Mapped[str] = mapped_column(ForeignKey("groups.id"), nullable=False)
    created_by:     Mapped[str] = mapped_column(ForeignKey("users.id"), nullable=False)
    ticket_hash:    Mapped[str] = mapped_column(String(64), nullable=False)
    email_hash:     Mapped[str] = mapped_column(String(64), nullable=False)
    email_masked:   Mapped[str] = mapped_column(String(128), nullable=False)
    # The node's handle for its half, so cancelling can take back both.
    node_invite_id: Mapped[str] = mapped_column(String(32), nullable=False, default="")
    created_at:     Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    expires_at:     Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    redeemed_by:    Mapped[str | None] = mapped_column(ForeignKey("users.id"))
    redeemed_at:    Mapped[datetime | None] = mapped_column(DateTime(timezone=True))

    __table_args__ = (
        Index("uq_invite_links_ticket", "ticket_hash", unique=True),
        Index("ix_invite_links_group", "group_id"),
    )


# ── 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/MESHBAY_DESIGN.md` §3.2).
        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 UserPreference(Base):
    __tablename__ = "user_preferences"

    user_id: Mapped[str] = mapped_column(
        String(36), ForeignKey("users.id"), primary_key=True)
    key:     Mapped[str] = mapped_column(String(64), primary_key=True)
    value:   Mapped[str] = mapped_column(Text, nullable=False)


class MailQuota(Base):
    """How much mail has gone where, kept across restarts.

    This was a pair of dicts in `mail.py`, which meant a restart handed out a
    fresh allowance — and a hub restarts whenever it is deployed. A budget a
    restart forgets is not a budget, for the same reason the denylist is
    persisted rather than held in memory (S3).

    One row per thing being counted:
      `hour`             the instance's hourly total
      `dest:<hash>`      one recipient, hashed — this table must not become a
                         list of plaintext addresses (S2)
    """

    __tablename__ = "mail_quota"

    key:          Mapped[str] = mapped_column(String(64), primary_key=True)
    window_start: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    count:        Mapped[int] = mapped_column(Integer, default=0)
    last_sent:    Mapped[datetime | None] = mapped_column(DateTime(timezone=True))


class LoginThrottle(Base):
    """Wrong passphrases per username, for the sign-in lockout (`login_throttle.py`).

    Keyed by a hash of the name as typed rather than by account, so an unknown
    name is counted — and locked — exactly like a real one (M1), and so a
    passphrase typed into the username field is never stored. Rows age out with
    the lockout window and are purged by the cleanup task.
    """

    __tablename__ = "login_throttle"

    key:             Mapped[str] = mapped_column(String(64), primary_key=True)
    failures:        Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    last_failure_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)


class HubSetting(Base):
    """
    Instance-wide settings an admin changes at runtime from the panel.

    Deliberately a key/value table rather than fields in `hub.toml`: the config
    file is read once at boot and editing it means an SSH session and a restart,
    which is not what "toggle this from the admin page" means. Anything here is a
    policy the running hub can change on itself.

    Absent key ⇒ the built-in default (see `meshbay_hub.hub_settings`). An older
    hub with no row behaves exactly as it did before the setting existed.
    """
    __tablename__ = "hub_settings"

    key:        Mapped[str] = mapped_column(String(64), primary_key=True)
    value:      Mapped[str] = mapped_column(Text, nullable=False)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=_now, onupdate=_now)


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)
    # null for failed logins
    user_id:    Mapped[str | None] = mapped_column(ForeignKey("users.id"))
    # 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
    # e.g. username on fail
    detail:     Mapped[str | None] = mapped_column(String(256))
    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"),
    )


class EmailVerification(Base):
    """
    One-time codes for email verification.

    Purposes:
      - registration: confirm the address at sign-up (account stays pending until verified)
      - email_change: confirm a new address before it replaces the old one
      - invitation:   notify an invitee with the group invite code
    """
    __tablename__ = "email_verifications"

    id:              Mapped[str] = mapped_column(String(36), primary_key=True, default=_uuid)
    email_hash:      Mapped[str] = mapped_column(String(64), nullable=False)
    email_encrypted: Mapped[str | None] = mapped_column(String(256))
    code:            Mapped[str] = mapped_column(String(16), nullable=False)
    purpose:         Mapped[str] = mapped_column(String(16), nullable=False)
    user_id:         Mapped[str | None] = mapped_column(ForeignKey("users.id"))
    group_id:        Mapped[str | None] = mapped_column(ForeignKey("groups.id"))
    created_at:      Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    expires_at:      Mapped[datetime] = mapped_column(DateTime(timezone=True), nullable=False)
    verified_at:     Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    attempts:        Mapped[int] = mapped_column(Integer, default=0)

    __table_args__ = (
        Index("ix_email_verif_hash", "email_hash"),
        Index("ix_email_verif_user", "user_id"),
    )