Data Model

A navigable reference to the SpatiumDDI database schema. This is a map, not a column-by-column dump — it groups the SQLAlchemy models by domain, names the anchor tables and their key relationships, and documents the shared conventions every model follows. For the exact column list of any table, read the model source under backend/app/models/; each file is the authoritative definition.

All models are SQLAlchemy 2.x async (Mapped[...] / mapped_column(...)) and live in PostgreSQL 16. The Alembic migrations under backend/alembic/ are the source of truth for the deployed schema; the model classes are the source of truth for what the application reads and writes.

Feature-level behaviour lives in the matching specs — IPAM, DNS, DHCP, Permissions, Observability.

1. Shared conventions

These mixins and patterns are defined in backend/app/models/base.py and reused across the schema.

Primary keys

Almost every table uses a UUID primary key via UUIDPrimaryKeyMixin:

id: Mapped[uuid.UUID] = mapped_column(
    UUID(as_uuid=True), primary_key=True, default=uuid.uuid4
)

The UUID default is generated application-side (uuid.uuid4), not by the database. A handful of tables deviate deliberately:

• feature_module uses a stable dotted-string PK (e.g. ai.copilot, network.customer) instead of a UUID.
• dns_metric_sample / dhcp_metric_sample use a composite PK (server_id, bucket_at) — they are time-series buckets, not entities.
• audit_log keeps its UUID id but also carries a monotonic BigInteger seq for chain ordering (see §11).

Timestamps

TimestampMixin adds created_at and modified_at, both DateTime(timezone=True) with server_default=now() and modified_at carrying onupdate=now(). Append-only tables (audit, metric samples, conformity results, lease history, log entries) intentionally omit the mixin — they never mutate, so a modified_at would be meaningless.

Soft delete

SoftDeleteMixin implements a 30-day soft-delete + recovery window for high-blast-radius rows. It adds deleted_at, deleted_by_user_id (FK → user, ON DELETE SET NULL), and deletion_batch_id. A cascade soft-delete stamps a parent and all its descendants with the same deletion_batch_id so a single restore brings them all back atomically.

The query filter lives in backend/app/db._filter_soft_deleted: a SQLAlchemy do_orm_execute listener injects deleted_at IS NULL into every SELECT touching a soft-delete model, unless the caller opts in with execution_options(include_deleted=True).

Models carrying SoftDeleteMixin:

IPAddress is not soft-deletable — it has its own orphan status in the lifecycle instead.

Fernet-encrypted secrets

Credentials at rest (bind passwords, API keys, client secrets, agent keys, TSIG secrets, driver admin creds, integration tokens) are Fernet-encrypted via backend/app/core/crypto.py (encrypt_str / decrypt_str, AES-128-CBC + HMAC-SHA256 keyed from CREDENTIAL_ENCRYPTION_KEY). The convention is a LargeBinary column named *_encrypted (or secrets_encrypted for a JSONB blob of secrets). API responses never return the plaintext — they expose a boolean such as eab_hmac_set / fingerbank_api_key_set instead, and a password-gated reveal endpoint handles the rare “show me the value once” case.

Models with encrypted columns include AuthProvider (secrets_encrypted), DNSServerGroup (TSIG), DNSServer (api_key_encrypted, credentials_encrypted), the integration mirror tables (KubernetesCluster, DockerHost, ProxmoxNode, TailscaleTenant, UnifiController, CloudEndpoint, OPNsenseRouter), AIProvider, BackupTarget, AuditForwardTarget, EventSubscription, PlatformSettings, TLSCertTarget, and the ACME client account. Public certs (e.g. a Kubernetes CA bundle) are stored cleartext — they’re not secrets.

Multi-source provenance

Rows that can be created by an importer or a read-only integration reconciler carry provenance columns so re-runs are idempotent and operators can tell hand-authored rows from mirrored ones. Common shapes: import_source + imported_at (NetBox / DNS / DHCP importers), and the integration FKs described in §5. IPAddress.status distinguishes operator-settable lifecycle values (IP_STATUSES_OPERATOR_SETTABLE) from integration-owned ones (IP_STATUSES_INTEGRATION_OWNED); an operator override stamps user_modified_at and locks the row from future reconciler overwrites.

2. IPAM — the address-space tree

Files: ipam.py, vlans.py, vrf.py, multicast.py. Feature spec: IPAM.md.

The core hierarchy is a strict containment tree:

Notes:

• IPSpace.is_default flags a single “simplified deployment” space.
• Block ↔ subnet overlap is enforced at the application layer using the PostgreSQL cidr && operator (see IPAM.md); IPAddress is unique per (subnet_id, address).
• Subnet.kind is a discriminator: unicast (default) vs multicast. IP-allocation endpoints refuse on multicast subnets — multicast stream identities are tracked by MulticastGroup, not per-endpoint IPAddress rows.
• The RESTRICT on Subnet.space_id / block_id is intentional: you can’t delete a space or block out from under live subnets.

Supporting IPAM tables:

VLAN / VXLAN

VLANs live under a Router (the L3 device that owns them), distinct from the VLANMapping VLAN→VXLAN reference table:

Subnet.vlan_ref_id → VLAN ties a subnet to a defined VLAN.

Multicast

multicast.py adds MulticastDomain (PIM domain registry with scope-boundary CIDRs + RP set), MulticastGroup (a group address / stream identity), MulticastGroupPort, and MulticastMembership (observed IGMP listeners).

3. DNS

File: dns.py. Feature spec: DNS.md. Drivers: DNS_DRIVERS.md.

The DNS hierarchy is group-centric — configuration lives on the group, servers are members, zones and records hang off the group:

DNSPool members render as ordinary DNSRecord rows with pool_member_id set (one per healthy + enabled member), so the underlying driver renders unchanged; a health-check task flips members in and out of the rendered set.

Supporting / config tables:

4. DHCP

File: dhcp.py. Feature spec: DHCP.md. Drivers: DHCP_DRIVERS.md.

Like DNS, DHCP is group-centric: scopes / pools / statics / classes live on the group, not on individual servers (a 2-member Kea group is an HA pair). Leases are per-server because each Kea instance owns its own memfile.

Supporting tables:

5. Network modeling

Logical ownership + WAN/service modeling overlaid on the IPAM/DNS/DHCP core. Files: asn.py, vrf.py, domain.py, ownership.py, circuit.py, network_service.py, overlay.py, network.py, bgp_looking_glass.py.

Cross-reference pattern: the ownership entities (Customer, Site, Provider) and the modeling entities (VRF, ASN) are linked into the IPAM/DNS/DHCP tables via nullable FKs with ON DELETE SET NULL (e.g. IPSpace.customer_id, IPBlock.site_id, DNSZone.customer_id, Subnet.site_id). Deleting an owner therefore detaches resources rather than cascading their deletion.

NetworkServiceResource.resource_id is not a real FK — it’s a polymorphic pointer (resource_kind + resource_id) validated at attach time, with a service_resource_orphaned alert sweeping for stale rows when a target is later deleted. The triple (service_id, resource_kind, resource_id) is unique.

The network.py file holds discovery-side tables: NetworkDevice, NetworkInterface, NetworkArpEntry, NetworkFdbEntry, NetworkNeighbour (SNMP/ARP/FDB/LLDP poll results).

6. Auth + RBAC

File: auth.py, auth_provider.py, time_bound_grant.py. Permission grammar: PERMISSIONS.md.

Association tables user_group and group_role are plain Table(...) many-to-many links with composite PKs and CASCADE on both sides. Permissions are stored as a JSONB list on Role rather than a relational join — wildcard * for action and resource_type is supported, and resource_id scopes a permission to one resource. Role.is_builtin marks the seeded roles (Superadmin, Viewer, IPAM/DNS/ DHCP Editor, Auditor, Compliance Editor, Change Approver) that the startup path keeps in sync.

7. Integration mirrors

Read-only pull reconcilers, each one a * endpoint/target row with connection config + enabled + last_synced_at / last_sync_error, and Fernet-encrypted credentials. The reconcilers stamp IPAddress (and some IPBlock / Subnet) rows tagged with an integration-owned status and an FK back to the source endpoint (ON DELETE CASCADE, so deleting an endpoint cleans up its mirrored rows).

The reconciler FKs live on the IPAM rows: e.g. IPAddress.kubernetes_cluster_id, docker_host_id, proxmox_node_id, tailscale_tenant_id, unifi_controller_id, cloud_endpoint_id, opnsense_router_id (all CASCADE), with parallel columns on IPBlock and Subnet where the mirror creates structure.

The NetBox importer (models provenance columns, not a continuous mirror) and the cloud DNS drivers are documented in MIGRATION.md and INTEGRATIONS.md.

8. Appliance + fleet

File: appliance.py. Deployment: DNS_AGENT.md.

DNSServer.appliance_id and DHCPServer.appliance_id (ON DELETE CASCADE) tie managed service rows back to the supervisor that runs them, so deleting an appliance cascades to the servers it hosted. The pairing-code FK on Appliance is SET NULL so the code reaper can sweep terminal codes without taking down the appliances they provisioned.

System-upgrade state lives in system_upgrade.py (SystemUpgradeRun, table system_upgrade_run).

9. Compliance + governance

Files: conformity.py, alerts.py, change_request.py.

A ConformityPolicy pass→fail transition can emit an AlertEvent against the policy’s wired AlertRule. The two-person-rule governance flow (default-off governance.approvals module) gates the six delete handlers through ChangeRequest + ApprovalPolicy.

change_request carries both governance flows, split by origin:

One table and one state machine on purpose: the lifecycle, the approver checks (approver ≠ requester, approver holds the operation’s own permission), the re-preview stale guard, the execution under the approver’s identity, the audit rows and the expiry sweep are identical for both. Only the direction of privilege differs. Every query on either surface filters by origin, so neither queue ever renders the other’s rows. justification is the requester’s free-text “why” and is NULL on gate rows, which carry the policy-derived risk_reason instead.

10. Observability + audit

Files: audit.py, logs.py, metrics.py, event_subscription.py, audit_forward.py, diagnostics.py. Spec: OBSERVABILITY.md.

Audit log (append-only + hash chain)

AuditLog (table audit_log) is the append-only record of every mutation. It is never deleted application-side, and a DB-level trigger blocks DELETE as a second guard (issue #73).

Tamper-evidence is a hash chain: each row carries a monotonic BigInteger seq (assigned from audit_log_seq_seq), a row_hash, and a prev_hash pointing at the previous row’s hash. The hash is computed in app.services.audit_chain via a SQLAlchemy before_flush listener that takes a Postgres advisory lock to serialize “look up previous row → hash → append”, so concurrent inserts can’t interleave and break the chain. prev_hash is NULL only on the first row. A nightly verify_chain task re-walks and validates it.

Each row records who (user_id, user_display_name, auth_source, source_ip, user_agent), what (action, resource_type, resource_id, resource_display), the state change (old_value / new_value / changed_fields JSONB), correlation (request_id), and the result (success / denied / error).

Log entries, metrics, events

The log-entry and metric-sample tables are append-only operator-triage data (short retention) — longer history belongs in Loki / Prometheus per OBSERVABILITY.md, not these tables.

11. Platform + miscellaneous

A feature_module row defaults to enabled=true (server default) and is seeded by Alembic alongside its feature’s model migration; the router include and MCP tools gate on it.

See also

• IPAM.md — address-space management behaviour
• DNS.md — zones, records, views, pools, blocklists
• DHCP.md — scopes, pools, statics, leases, HA
• PERMISSIONS.md — the {action, resource_type, resource_id?} grammar
• OBSERVABILITY.md — audit chain, logging, metrics, alerting
• backend/alembic/ — the deployed schema, migration by migration