Skip to content

Self-hosted homelab infrastructure visualizer — interactive network diagram with live status monitoring

23.7 KB
Raw Blame
import uuid
from datetime import datetime, timezone
from typing import Any

from sqlalchemy import JSON, Boolean, DateTime, Float, ForeignKey, Integer, String, Text
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.db.database import Base


def _now() -> datetime:
    return datetime.now(timezone.utc)


def _uuid() -> str:
    return str(uuid.uuid4())


class Design(Base):
    __tablename__ = "designs"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    name: Mapped[str] = mapped_column(String, nullable=False)
    design_type: Mapped[str] = mapped_column(String, nullable=False, default="network")
    icon: Mapped[str | None] = mapped_column(String, nullable=True, default="dashboard")
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now, onupdate=_now)


class Node(Base):
    """How a device is drawn on one canvas.

    A node owns presentation only — position, size, colours, icon, handles,
    nesting. What the device *is* (addresses, services, properties, notes,
    hardware, check method, live status) belongs to the `device_inventory` row
    named by `device_id`, so one device reads the same on every canvas. The API
    still reports both together: see `services/inventory_sync.hydrated_node`.
    """

    __tablename__ = "nodes"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    type: Mapped[str] = mapped_column(String, nullable=False)
    label: Mapped[str] = mapped_column(String, nullable=False)
    design_id: Mapped[str | None] = mapped_column(String, ForeignKey("designs.id", ondelete="SET NULL"), nullable=True)
    # The Device Inventory row this node draws. NULL for canvas furniture
    # (group / groupRect / text), which represents nothing physical. Deleting a
    # node never deletes the device — the inventory outlives every canvas.
    device_id: Mapped[str | None] = mapped_column(
        String, ForeignKey("device_inventory.id", ondelete="SET NULL"), index=True, nullable=True
    )
    # How this canvas renders the device's list facts: which services and which
    # properties it shows, and in what order. The facts themselves stay on the
    # inventory row, so the same device drawn on two canvases can show two
    # different subsets — a scanner-guessed service on one, none on the other.
    #   {"services": [{"key": "443|tcp|https", "visible": true}, …],
    #    "properties": [{"key": "rack", "visible": false}, …]}
    # NULL only for canvas furniture and for a node with no inventory row yet;
    # `inventory_sync.link_facts` fills both lists as soon as there is one.
    display_view: Mapped[dict[str, Any] | None] = mapped_column(JSON, nullable=True)
    pos_x: Mapped[float] = mapped_column(Float, default=0)
    pos_y: Mapped[float] = mapped_column(Float, default=0)
    parent_id: Mapped[str | None] = mapped_column(String, ForeignKey("nodes.id", ondelete="CASCADE"))
    container_mode: Mapped[bool] = mapped_column(Boolean, default=False)
    custom_colors: Mapped[dict[str, Any] | None] = mapped_column(JSON, nullable=True)
    custom_icon: Mapped[str | None] = mapped_column(String, nullable=True)
    # What this piece of canvas furniture is for, in the user's words. Furniture
    # (group / groupRect / text) draws no device, so it has no inventory row to
    # carry a `notes` field — this column is that text's only home. NULL on every
    # node that does draw a device: its notes belong to the inventory row.
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    show_port_numbers: Mapped[bool] = mapped_column(Boolean, default=False)
    # Text printed in place of a connection point's 1..N number, keyed by source
    # handle ID ('top', 'bottom-3'…), so a node can carry the device's own port
    # numbering (#495). NULL when every point shows its number.
    port_labels: Mapped[dict[str, str] | None] = mapped_column(JSON, nullable=True)
    width: Mapped[float | None] = mapped_column(Float, nullable=True)
    height: Mapped[float | None] = mapped_column(Float, nullable=True)
    bottom_handles: Mapped[int] = mapped_column(Integer, default=1)
    top_handles: Mapped[int] = mapped_column(Integer, default=1)
    left_handles: Mapped[int] = mapped_column(Integer, default=0)
    right_handles: Mapped[int] = mapped_column(Integer, default=0)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now, onupdate=_now)
    children: Mapped[list["Node"]] = relationship("Node", back_populates="parent")
    parent: Mapped["Node | None"] = relationship("Node", back_populates="children", remote_side=[id])


class Edge(Base):
    __tablename__ = "edges"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    source: Mapped[str] = mapped_column(String, ForeignKey("nodes.id", ondelete="CASCADE"))
    target: Mapped[str] = mapped_column(String, ForeignKey("nodes.id", ondelete="CASCADE"))
    design_id: Mapped[str | None] = mapped_column(String, ForeignKey("designs.id", ondelete="SET NULL"), nullable=True)
    type: Mapped[str] = mapped_column(String, default="ethernet")
    label: Mapped[str | None] = mapped_column(String)
    vlan_id: Mapped[int | None] = mapped_column(Integer)
    speed: Mapped[str | None] = mapped_column(String)
    # Link quality (Zigbee mesh). Measured per link, so it belongs to the edge
    # rather than to either endpoint — see issue #496. Read-only: it is written
    # by the import, never edited by hand, so `EdgeUpdate` deliberately omits it.
    lqi: Mapped[int | None] = mapped_column(Integer, nullable=True)
    custom_color: Mapped[str | None] = mapped_column(String)
    path_style: Mapped[str | None] = mapped_column(String)
    line_style: Mapped[str | None] = mapped_column(String)
    width_mult: Mapped[float | None] = mapped_column(Float)
    animated: Mapped[str] = mapped_column(String, nullable=False, default='none')
    marker_start: Mapped[str] = mapped_column(String, nullable=False, default='none')
    marker_end: Mapped[str] = mapped_column(String, nullable=False, default='none')
    source_handle: Mapped[str | None] = mapped_column(String)
    target_handle: Mapped[str | None] = mapped_column(String)
    waypoints: Mapped[list[dict[str, float]] | None] = mapped_column(JSON, nullable=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)


class CanvasState(Base):
    __tablename__ = "canvas_state"

    design_id: Mapped[str] = mapped_column(String, ForeignKey("designs.id", ondelete="CASCADE"), primary_key=True)
    viewport: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict)
    custom_style: Mapped[dict[str, Any] | None] = mapped_column(JSON, nullable=True)
    saved_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)


class Rack(Base):
    """A physical rack on a `design_type='rack'` canvas.

    Racks live per design, like nodes and edges. Geometry is expressed in rack
    units: `u_height` is the number of mountable U, and a device's `u_start` is
    always counted from the bottom rail — `numbering` only changes the printed
    labels.
    """

    __tablename__ = "racks"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    design_id: Mapped[str] = mapped_column(String, ForeignKey("designs.id", ondelete="CASCADE"), nullable=False)
    name: Mapped[str] = mapped_column(String, nullable=False)
    u_height: Mapped[int] = mapped_column(Integer, default=42)
    # "19" or "10" (inches). Drives the drawn inner width.
    width_standard: Mapped[str] = mapped_column(String, default="19")
    # "bottom-up" or "top-down" — printed U labels only, never storage order.
    numbering: Mapped[str] = mapped_column(String, default="bottom-up")
    location: Mapped[str | None] = mapped_column(String, nullable=True)
    # Frame/rail/interior colours + showNumbers/enclosed flags.
    style: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict)
    pos_x: Mapped[float] = mapped_column(Float, default=0)
    pos_y: Mapped[float] = mapped_column(Float, default=0)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now, onupdate=_now)


class RackDevice(Base):
    """A piece of gear mounted in a rack.

    Two independent, both-optional links back to the rest of the app:

    * `device_id` — the Device Inventory entry (`device_inventory`). This is the
      primary link: inventory rows survive approval *and* node deletion, so
      unracking or deleting a canvas node never removes the inventory entry.
    * `node_id` — the logical-canvas node, when one exists. Only used to resolve
      live status and to seed cables from the network design's edges.

    Both are ``SET NULL`` on delete and `label` is denormalized, so a rack keeps
    rendering after an inventory purge.
    """

    __tablename__ = "rack_devices"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    design_id: Mapped[str] = mapped_column(String, ForeignKey("designs.id", ondelete="CASCADE"), nullable=False)
    rack_id: Mapped[str] = mapped_column(String, ForeignKey("racks.id", ondelete="CASCADE"), nullable=False)
    device_id: Mapped[str | None] = mapped_column(
        String, ForeignKey("device_inventory.id", ondelete="SET NULL"), nullable=True
    )
    node_id: Mapped[str | None] = mapped_column(String, ForeignKey("nodes.id", ondelete="SET NULL"), nullable=True)
    label: Mapped[str] = mapped_column(String, nullable=False)
    # 1-based, counted from the bottom rail.
    u_start: Mapped[int] = mapped_column(Integer, default=1)
    u_height: Mapped[int] = mapped_column(Integer, default=1)
    # 12-column horizontal grid: full = 12, half = 6, third = 4, quarter = 3.
    col_start: Mapped[int] = mapped_column(Integer, default=0)
    col_span: Mapped[int] = mapped_column(Integer, default=12)
    faceplate_id: Mapped[str] = mapped_column(String, nullable=False, default="blank-1u")
    color: Mapped[str | None] = mapped_column(String, nullable=True)
    status: Mapped[str] = mapped_column(String, default="unknown")
    # When the plate draws its sockets: "auto" (the faceplate decides), "always"
    # or "hover". A canvas display choice about the mount, not about the device.
    port_visibility: Mapped[str] = mapped_column(String, default="auto")
    # [{id, label, type, x, y}] — positions are unit coordinates on the plate.
    ports: Mapped[list[Any]] = mapped_column(JSON, default=list)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now, onupdate=_now)


class RackCable(Base):
    """A patch between two rack device ports.

    Not an `Edge`: rack cables are port-to-port and may cross racks, so they are
    their own relation rather than a canvas edge.
    """

    __tablename__ = "rack_cables"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    design_id: Mapped[str] = mapped_column(String, ForeignKey("designs.id", ondelete="CASCADE"), nullable=False)
    from_device_id: Mapped[str] = mapped_column(
        String, ForeignKey("rack_devices.id", ondelete="CASCADE"), nullable=False
    )
    from_port_id: Mapped[str] = mapped_column(String, nullable=False)
    to_device_id: Mapped[str] = mapped_column(
        String, ForeignKey("rack_devices.id", ondelete="CASCADE"), nullable=False
    )
    to_port_id: Mapped[str] = mapped_column(String, nullable=False)
    type: Mapped[str] = mapped_column(String, default="ethernet")
    color: Mapped[str] = mapped_column(String, default="#39d353")
    label: Mapped[str | None] = mapped_column(String, nullable=True)
    # Print the label next to the run on the canvas.
    label_visible: Mapped[bool] = mapped_column(Boolean, default=False)
    # [{key, value, icon, visible}] — same records nodes carry; the visible ones
    # are drawn beside the cable.
    properties: Mapped[list[Any]] = mapped_column(JSON, default=list)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)


class InventoryDevice(Base):
    __tablename__ = "device_inventory"
    # Permit the plain (non-Mapped[]) annotations on the transient request-only
    # attributes below; without this SQLAlchemy 2.0 tries to map them as columns.
    __allow_unmapped__ = True

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    ip: Mapped[str | None] = mapped_column(String, nullable=True)
    mac: Mapped[str | None] = mapped_column(String)
    hostname: Mapped[str | None] = mapped_column(String)
    os: Mapped[str | None] = mapped_column(String)
    services: Mapped[list[Any]] = mapped_column(JSON, default=list)
    suggested_type: Mapped[str | None] = mapped_column(String)
    status: Mapped[str] = mapped_column(String, default="pending")
    # Origin/primary source (first discovery): "arp"/"mdns"/"zigbee"/"zwave"/
    # "proxmox". Kept for back-compat; `discovery_sources` is the full set.
    discovery_source: Mapped[str | None] = mapped_column(String)
    # All sources that have observed this device. A device found by both an IP
    # scan and a Proxmox import carries e.g. ["arp", "proxmox"] and shows under
    # both inventory filters. Source of truth for the frontend source badges.
    discovery_sources: Mapped[list[Any]] = mapped_column(JSON, default=list)
    ieee_address: Mapped[str | None] = mapped_column(String, index=True, nullable=True, unique=True)
    friendly_name: Mapped[str | None] = mapped_column(String, nullable=True)
    device_subtype: Mapped[str | None] = mapped_column(String, nullable=True)
    model: Mapped[str | None] = mapped_column(String, nullable=True)
    vendor: Mapped[str | None] = mapped_column(String, nullable=True)
    lqi: Mapped[int | None] = mapped_column(Integer, nullable=True)
    # Display properties carried from discovery (e.g. Proxmox specs: CPU/RAM/Disk,
    # VMID). Generic NodeProperty shape {key,value,icon,visible}; merged into the
    # Node's properties on approve. Empty for scan/mesh sources that don't set it.
    properties: Mapped[list[Any]] = mapped_column(JSON, default=list)
    discovered_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)

    # --- Curated device facts (3.3.0) -------------------------------------
    # The inventory row, not the canvas node, owns what the device *is*. A node
    # only says how it is drawn. `label`/`type` supersede friendly_name/
    # suggested_type for a device that reached a canvas; the older pair is kept
    # so discovery imports and the source filters keep working unchanged.
    label: Mapped[str | None] = mapped_column(String, nullable=True)
    type: Mapped[str | None] = mapped_column(String, nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    cpu_count: Mapped[int | None] = mapped_column(Integer, nullable=True)
    cpu_model: Mapped[str | None] = mapped_column(String, nullable=True)
    ram_gb: Mapped[float | None] = mapped_column(Float, nullable=True)
    disk_gb: Mapped[float | None] = mapped_column(Float, nullable=True)
    show_hardware: Mapped[bool] = mapped_column(Boolean, default=False)
    check_method: Mapped[str | None] = mapped_column(String, nullable=True)
    check_target: Mapped[str | None] = mapped_column(String, nullable=True)
    # Live reachability from the status checker. NOT `status` — that column holds
    # the inventory lifecycle (pending/approved/hidden) and the two must not be
    # conflated.
    status_live: Mapped[str] = mapped_column(String, default="unknown")
    last_seen: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    last_scan: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    response_time_ms: Mapped[int | None] = mapped_column(Integer, nullable=True)
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=_now, onupdate=_now
    )

    # --- Rack modelisation (owned here, not by the mount) ------------------
    # A device wears the same front panel in every rack it is mounted in, so the
    # inventory row — not `rack_devices` — owns the faceplate, its size and its
    # ports. The mount keeps a denormalized copy so a rack still renders after an
    # inventory purge; `/api/v1/racks` overlays these on load and `save` writes
    # them back. NULL means "never modelled" — the mount's own copy then stands.
    rack_faceplate_id: Mapped[str | None] = mapped_column(String, nullable=True)
    rack_u_height: Mapped[int | None] = mapped_column(Integer, nullable=True)
    rack_col_span: Mapped[int | None] = mapped_column(Integer, nullable=True)
    rack_color: Mapped[str | None] = mapped_column(String, nullable=True)
    # [{id, label, type, x, y}] — x/y are unit coordinates on the plate.
    rack_ports: Mapped[list[Any] | None] = mapped_column(JSON, nullable=True)

    # Transient (not persisted): populated per-request by the scan routes to report
    # how many canvases this device already appears on. Not a mapped column.
    canvas_count: int = 0
    # Transient (not persisted): timestamps from the linked canvas node(s),
    # correlated by ip / ieee_address. None when the device is not on any canvas.
    node_created_at: datetime | None = None
    node_last_scan: datetime | None = None
    node_last_modified: datetime | None = None
    node_last_seen: datetime | None = None


class InventoryDeviceLink(Base):
    """Link between two Zigbee endpoints discovered during import.

    Endpoints are addressed by IEEE (stable across re-imports). Either side may
    already exist as a canvas Node (resolved via Node.ieee_address) or still be
    a InventoryDevice. On approval, the matching Edge is auto-created when both
    endpoints exist as canvas Nodes.
    """

    __tablename__ = "device_inventory_links"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    source_ieee: Mapped[str] = mapped_column(String, nullable=False, index=True)
    target_ieee: Mapped[str] = mapped_column(String, nullable=False, index=True)
    lqi: Mapped[int | None] = mapped_column(Integer, nullable=True)
    discovery_source: Mapped[str] = mapped_column(String, nullable=False, default="zigbee")
    discovered_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)


class ScanRun(Base):
    __tablename__ = "scan_runs"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    status: Mapped[str] = mapped_column(String, default="running")
    kind: Mapped[str] = mapped_column(String, default="ip", server_default="ip")
    ranges: Mapped[list[str]] = mapped_column(JSON, default=list)
    devices_found: Mapped[int] = mapped_column(Integer, default=0)
    started_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    finished_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    error: Mapped[str | None] = mapped_column(Text)


class Document(Base):
    """One markdown document.

    A document either stands alone in the Library tree (`kind` page/folder,
    placed by `parent_id`) or describes exactly one thing on the other side of
    an optional link: a Device Inventory row (`device_id`), a piece of canvas
    furniture such as a zone or a group (`node_id`), or a whole canvas
    (`design_id`). Only one of the three is ever set, and each is enforced
    unique by a partial index created in `database.init_db`.

    Every link is `SET NULL` and `title` is denormalized, so deleting a device
    or a canvas never destroys what the user wrote — the document survives as
    an orphan and can be re-linked or filed away.

    `body` is the whole markdown file, YAML frontmatter included, so a document
    exports to disk as-is. `frontmatter` and `tags` are a parsed cache of that
    block, kept only so listings can filter without reading every body.
    """

    __tablename__ = "documents"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    kind: Mapped[str] = mapped_column(String, nullable=False, default="page")
    title: Mapped[str] = mapped_column(String, nullable=False)
    slug: Mapped[str] = mapped_column(String, nullable=False, index=True)
    icon: Mapped[str | None] = mapped_column(String, nullable=True)

    # Library tree. Folders are documents too, so an empty folder is possible
    # and a folder can carry an index body. Only page/folder set this.
    parent_id: Mapped[str | None] = mapped_column(
        String, ForeignKey("documents.id", ondelete="CASCADE"), nullable=True, index=True
    )
    sort_order: Mapped[int] = mapped_column(Integer, default=0)

    device_id: Mapped[str | None] = mapped_column(
        String, ForeignKey("device_inventory.id", ondelete="SET NULL"), nullable=True, index=True
    )
    node_id: Mapped[str | None] = mapped_column(
        String, ForeignKey("nodes.id", ondelete="SET NULL"), nullable=True, index=True
    )
    design_id: Mapped[str | None] = mapped_column(
        String, ForeignKey("designs.id", ondelete="SET NULL"), nullable=True, index=True
    )

    body: Mapped[str] = mapped_column(Text, nullable=False, default="")
    frontmatter: Mapped[dict[str, Any]] = mapped_column(JSON, default=dict)
    tags: Mapped[list[str]] = mapped_column(JSON, default=list)
    starred: Mapped[bool] = mapped_column(Boolean, default=False)
    template_id: Mapped[str | None] = mapped_column(String, nullable=True)

    # The device facts as they read when this document was scaffolded or last
    # reconciled. The header is generated once and then owned by the user, so
    # this is what tells the UI the document has drifted from the device.
    facts_snapshot: Mapped[dict[str, Any] | None] = mapped_column(JSON, nullable=True)
    facts_synced_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    reviewed_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    # The whole generated body as it read when this document was scaffolded or
    # last reconciled. Together with `facts_snapshot` this is the baseline of a
    # three-way update-from-device: `current <-> baseline <- fresh-generate`.
    # It is *not* the merged body — only the freshly generated one, so a value
    # the user deliberately kept is still noticed when the device moves again.
    baseline_body: Mapped[str | None] = mapped_column(Text, nullable=True)
    # Set the first time the body is edited by hand. NULL means the document is
    # still only what the template generated — which is what the coverage view
    # counts as "not really documented yet". A timestamp comparison cannot say
    # this: created_at and updated_at are two separate clock reads on insert.
    edited_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now, onupdate=_now)


class DocumentRevision(Base):
    """A prior version of a document's body.

    Written on every explicit save that actually changed the body, and pruned
    to the most recent `REVISION_LIMIT` per document in the same transaction.
    """

    __tablename__ = "document_revisions"

    id: Mapped[str] = mapped_column(String, primary_key=True, default=_uuid)
    document_id: Mapped[str] = mapped_column(
        String, ForeignKey("documents.id", ondelete="CASCADE"), nullable=False, index=True
    )
    title: Mapped[str] = mapped_column(String, nullable=False)
    body: Mapped[str] = mapped_column(Text, nullable=False, default="")
    reason: Mapped[str] = mapped_column(String, nullable=False, default="edit")
    saved_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=_now)