Data model

l2trace stores everything in Postgres (with the Apache AGE extension for graph projection). Migrations live in alembic/versions/.

The bitemporal observation tables

The pattern is the same across mac_observation, adjacency, stp_state, arp_observation, and port_state. Generalized shape:

CREATE TABLE <observation> (
    entry_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id              UUID NOT NULL UNIQUE,         -- idempotency key
    source                ingest_source NOT NULL,        -- gnmi | snmp | ssh | ...
    -- entity-specific columns (device_id, port_id, mac, vlan, ...)
    observed_at           TIMESTAMPTZ NOT NULL,          -- corrected device time
    collector_emitted_at  TIMESTAMPTZ NULL,              -- chain-of-custody
    valid_during          TSTZRANGE NOT NULL,            -- when it was true on the wire
    recorded_during       TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)'),
                                                        -- when we believed it
    superseded_by         BIGINT NULL REFERENCES <observation>(entry_id)
);

Two TSTZRANGE columns are the heart of bitemporality. valid_during is forward-time; recorded_during is belief-time. Both default to “open-ended on the right” — tstzrange(now(), NULL, '[)') — and get closed by UPDATEs as the timeline evolves.

The UNIQUE (event_id) constraint plus ON CONFLICT (event_id) DO NOTHING on writes is what makes JetStream at-least-once delivery safe.

`mac_observation`

Where the CAM/MAC table observations land.

CREATE TABLE mac_observation (
    entry_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id              UUID NOT NULL UNIQUE,
    mac                   MACADDR NOT NULL,
    device_id             BIGINT NOT NULL REFERENCES device(id),
    port_id               BIGINT NOT NULL REFERENCES port(id),
    vlan                  SMALLINT NOT NULL,
    entry_type            mac_type NOT NULL DEFAULT 'dynamic',
    source                ingest_source NOT NULL,
    observed_at           TIMESTAMPTZ NOT NULL,
    collector_emitted_at  TIMESTAMPTZ NULL,
    valid_during          TSTZRANGE NOT NULL,
    recorded_during       TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)'),
    superseded_by         BIGINT NULL REFERENCES mac_observation(entry_id)
);

The key constraint:

ALTER TABLE mac_observation ADD CONSTRAINT mac_obs_no_overlap_per_source
EXCLUDE USING gist (
    mac WITH =, device_id WITH =, vlan WITH =, source WITH =,
    valid_during WITH &&
) WHERE (upper_inf(recorded_during));

Translation: within a single source, you can never have two currently-believed open observations whose valid_during ranges overlap for the same (mac, device_id, vlan). Cross-source disagreement is allowed — that’s how the disagreement view surfaces gNMI-vs-SNMP conflicts.

source is in the constraint key — this is load-bearing. The constraint exists per source to allow exactly the disagreements we want to surface.

`liveness`

A tiny, fast table tracking the most-recently-observed open row per (device, source, mac, vlan). The reconciler reads it on every event to classify (first-sight vs continuation vs move). The compactor watches it for aged-out keys.

CREATE TABLE liveness (
    device_id         BIGINT NOT NULL REFERENCES device(id),
    source            ingest_source NOT NULL,
    mac               MACADDR NOT NULL,
    vlan              SMALLINT NOT NULL,
    port_id           BIGINT NOT NULL REFERENCES port(id),
    entry_id          BIGINT NOT NULL REFERENCES mac_observation(entry_id),
    last_observed_at  TIMESTAMPTZ NOT NULL,
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (device_id, source, mac, vlan)
);

`device` and `port`

Topology entities. port.kind distinguishes physical / LAG / SVI / mgmt; port.parent_port_id links physical members to their LAG.

CREATE TABLE device (
    id                       BIGSERIAL PRIMARY KEY,
    hostname                 TEXT NOT NULL UNIQUE,
    mgmt_ip                  INET,
    chassis_id               MACADDR,                     -- LLDP local chassis
    vendor                   TEXT,                        -- 'cisco' | 'arista' | ...
    model                    TEXT,
    os_version               TEXT,
    mlag_group_id            BIGINT NULL,
    visibility               device_visibility
                             NOT NULL DEFAULT 'instrumented',
    collection_profile_id    BIGINT NULL
                             REFERENCES collection_profile(id)
                             ON DELETE SET NULL
);

Three columns earn their own paragraph:

chassis_id is the LLDP-reported local chassis ID. It’s the join key that lets adjacency.remote_chassis_id resolve to a peer device.id — see peer resolution.
mlag_group_id collapses an MLAG/VPC peer pair into one logical node for path traversal. Otherwise the same MAC seen on both peers would look like a flap.
visibility is one of 'instrumented' (we collect from it) or 'unknown' (placeholder for gear we can’t poll — see F5 virtual-switch passthrough).
vendor is auto-populated from the SNMP collector’s per-vendor plugin dispatch (sysObjectID → vendor name; see F1 plugin matrix).
collection_profile_id binds the device to a named cadence policy (see collection_profile).

The port table carries the switchport config the collectors observe:

CREATE TABLE port (
    id              BIGSERIAL PRIMARY KEY,
    device_id       BIGINT NOT NULL REFERENCES device(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,               -- "Ethernet1/1"
    ifindex         INT,                          -- can change on reload
    kind            port_kind NOT NULL,           -- phys | lag | svi | mgmt
    parent_port_id  BIGINT NULL REFERENCES port(id),  -- LAG member → aggregator
    role            port_role,                    -- access | trunk | unknown
    speed_mbps      INT,
    native_vlan     SMALLINT,                     -- untagged / access VLAN
    allowed_vlans   INT[],                        -- trunk-permitted VLANs
    description     TEXT,                         -- ifAlias / network-team label
    CONSTRAINT port_device_name_uq UNIQUE (device_id, name)
);

kind is one of phys | lag | svi | mgmt. A port-channel is kind = 'lag'; the CAM table always points at the logical LAG port, never a member.
parent_port_id links a physical member port to its aggregator LAG — the member’s row carries the FK, the 'lag' row is the parent. Indexed by port_parent_idx where the FK is non-NULL.
role, native_vlan, and allowed_vlans are the observed switchport config: an access port pins native_vlan, a trunk lists allowed_vlans. role is access | trunk | unknown and is nullable until a collector fills it in.
description is the IF-MIB ifAlias (1.3.6.1.2.1.31.1.1.1.18) — the per-interface label the network team configures. Nullable; it populates on the next poll after the port is first seen.

`collection_profile`

Named cadence policies for per-subnet/per-role polling rates. A device optionally references one via device.collection_profile_id; the orchestrator resolves the effective interval per (device, source) at poll time.

CREATE TABLE collection_profile (
    id                            BIGSERIAL PRIMARY KEY,
    name                          TEXT NOT NULL UNIQUE,
    description                   TEXT,
    default_poll_interval_seconds INT NOT NULL CHECK (default_poll_interval_seconds > 0),
    snmp_interval_seconds         INT NULL,
    gnmi_interval_seconds         INT NULL,
    ssh_interval_seconds          INT NULL,
    created_at                    TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at                    TIMESTAMPTZ NOT NULL DEFAULT now()
);

Resolution order at orchestrator time, per (device, source):

device_collector.extras['poll_interval_seconds'] — explicit per-row override
profile.<source>_interval_seconds — per-source override on the profile
profile.default_poll_interval_seconds — profile default
NULL → collector falls back to its built-in DEFAULT_POLL_INTERVAL_SECONDS

device.collection_profile_id is ON DELETE SET NULL — deleting a profile doesn’t cascade-delete its devices.

Other observation tables

The same bitemporal shape applies to:

CREATE TABLE adjacency (
    -- LLDP/CDP neighbors. remote_chassis_id is always present; remote_device_id
    -- gets backfilled when the peer's DeviceIdentified event lands.
    entry_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id              UUID NOT NULL UNIQUE,
    local_port_id         BIGINT NOT NULL REFERENCES port(id),
    remote_chassis_id     MACADDR NOT NULL,
    remote_port_descr     TEXT,
    remote_device_id      BIGINT NULL REFERENCES device(id),
    remote_port_id        BIGINT NULL REFERENCES port(id),
    protocol              adj_proto NOT NULL,
    source                ingest_source NOT NULL,
    observed_at           TIMESTAMPTZ NOT NULL,
    valid_during          TSTZRANGE NOT NULL,
    recorded_during       TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)')
    -- EXCLUDE: no two open rows per (local_port_id, source, protocol)
);

CREATE TABLE stp_state (
    -- Per (port, vlan) STP state. The traceroute CTE filters out edges
    -- where state != 'forwarding'. state='blocking' + open CAM on the
    -- same port → the audit-stp-cam detector flags it.
    entry_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id              UUID NOT NULL UNIQUE,
    port_id               BIGINT NOT NULL REFERENCES port(id),
    vlan                  SMALLINT NOT NULL,        -- 0 = CST, >0 = PVST/MSTP per-vlan
    state                 stp_state_enum NOT NULL,  -- forwarding | blocking | learning | disabled
    root_id               MACADDR,                  -- 802.1D root-bridge claim (NULL = unknown)
    source                ingest_source NOT NULL,
    observed_at           TIMESTAMPTZ NOT NULL,
    valid_during          TSTZRANGE NOT NULL,
    recorded_during       TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)')
    -- EXCLUDE: no two open rows per (port_id, vlan, source)
);

CREATE TABLE port_state (
    -- PHY-layer admin + oper state. admin='down' or oper IN ('down',
    -- 'lower-layer-down') with open CAM → audit-port-state-cam flags it.
    entry_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id              UUID NOT NULL UNIQUE,
    port_id               BIGINT NOT NULL REFERENCES port(id),
    admin_state           port_admin NOT NULL,      -- up | down
    oper_state            port_oper NOT NULL,       -- up | down | lower-layer-down | testing
    source                ingest_source NOT NULL,
    observed_at           TIMESTAMPTZ NOT NULL,
    valid_during          TSTZRANGE NOT NULL,
    recorded_during       TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)')
);

CREATE TABLE arp_observation (
    -- Router ARP cache. (device_id, ip, source) is uniqueness key —
    -- same router/same source can't claim one IP maps to two MACs.
    -- DIFFERENT routers (device_id) claiming the same IP → different
    -- MACs → audit-arp-collision flags it.
    entry_id              BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id              UUID NOT NULL UNIQUE,
    device_id             BIGINT NOT NULL REFERENCES device(id),
    vrf                   TEXT,                     -- NULL = global / default VRF
    ip                    INET NOT NULL,
    mac                   MACADDR NOT NULL,
    source                ingest_source NOT NULL,
    observed_at           TIMESTAMPTZ NOT NULL,
    valid_during          TSTZRANGE NOT NULL,
    recorded_during       TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)')
    -- EXCLUDE: no two open rows per (device_id, ip, source)
);

Every table predicates its EXCLUDE on upper_inf(recorded_during) — the same shared invariant the audit detectors rely on for compositional correctness with F31’s TCN-driven belief revision (see bug detection).

`rogue_ap_observation`

Wireless-controller rogue-AP detections — unauthorized APs a WLC hears over the air, each classified and flagged whether it’s on the wire. Same bitemporal snapshot shape as the CAM/ARP tables: a rogue that’s no longer detected gets its valid_during closed. The point is correlating mac against mac_observation to pin an on-wire rogue to a switch port.

CREATE TABLE rogue_ap_observation (
    entry_id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id         UUID NOT NULL,
    mac              MACADDR NOT NULL,
    wlc_device_id    BIGINT NOT NULL REFERENCES device(id) ON DELETE CASCADE,
    classification   rogue_class NOT NULL DEFAULT 'unclassified',
                                                -- unclassified | friendly | malicious | custom | unknown
    on_wire          BOOLEAN NOT NULL DEFAULT false,
    ssid             TEXT NULL,
    channel          SMALLINT NULL,
    rssi             SMALLINT NULL,
    detecting_ap_mac MACADDR NULL,
    source           ingest_source NOT NULL,
    observed_at      TIMESTAMPTZ NOT NULL,
    valid_during     TSTZRANGE NOT NULL,
    recorded_during  TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)'),
    superseded_by    BIGINT NULL REFERENCES rogue_ap_observation(entry_id),
    CONSTRAINT rogue_ap_event_uq UNIQUE (event_id)
    -- EXCLUDE: no two open rows per (mac, wlc_device_id, source)
);

`auth_event`

Append-only 802.1X/RADIUS authentication log. Unlike the retracting-snapshot tables, each row is a point event — one Access-Request → Accept/Reject decision, pinned to the switch (NAS) + port + time — so there is no per-source EXCLUDE and no close-then-insert; the write is a single INSERT ... ON CONFLICT (event_id) DO NOTHING with valid_during = tstzrange(observed_at, NULL, '[)'). The radius value was added to the ingest_source enum for this table. The discrepancy flag records monitor-mode findings — unknown MAC, or observed port/VLAN disagreeing with configured intent — without touching the network.

CREATE TABLE auth_event (
    entry_id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id             UUID NOT NULL,
    source               ingest_source NOT NULL,
    mac                  MACADDR NOT NULL,
    username             TEXT NULL,
    nas_device_id        BIGINT NULL REFERENCES device(id) ON DELETE SET NULL,
    nas_port             TEXT NULL,
    port_id              BIGINT NULL REFERENCES port(id) ON DELETE SET NULL,
    eap_method           TEXT NULL,
    result               auth_result NOT NULL,    -- accept | reject
    assigned_vlan        SMALLINT NULL,
    monitor_mode         BOOLEAN NOT NULL,
    discrepancy          BOOLEAN NOT NULL DEFAULT false,
    reason               TEXT NULL,
    observed_at          TIMESTAMPTZ NOT NULL,
    collector_emitted_at TIMESTAMPTZ NULL,
    valid_during         TSTZRANGE NOT NULL,
    recorded_during      TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)'),
    superseded_by        BIGINT NULL REFERENCES auth_event(entry_id),
    CONSTRAINT auth_event_event_uq UNIQUE (event_id)
);

`mac_authorization`

Operator-intent store for 802.1X provisioning — the mutable counterpart to auth_event’s point log. A MAC is pending review on the provisioning VLAN, approved onto a real VLAN, or denied. Because intent is genuinely mutable (an approval today can be revoked tomorrow), it uses the sequenced-amend pattern: a decision change closes the prior belief’s recorded_during upper bound and INSERTs a successor, guarded by a partial unique index that permits at most one current belief per MAC. event_id defaults to gen_random_uuid() — these rows are operator decisions, not idempotent ingested events, so the writer does not supply one.

Revision 0023 adds the auth_source enum (operator | nautobot) and three additive columns for optional Nautobot-as-source-of-truth coexistence: decision_source records who authored the current effective belief, and nautobot_status / nautobot_vlan carry the last-synced Nautobot intent alongside it — so a divergence between an operator decision and a Nautobot intent is durable and queryable rather than auto-resolved.

CREATE TABLE mac_authorization (
    entry_id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_id        UUID NOT NULL DEFAULT gen_random_uuid(),
    source          ingest_source NOT NULL DEFAULT 'radius',
    mac             MACADDR NOT NULL,
    status          auth_status NOT NULL,        -- pending | approved | denied
    assigned_vlan   SMALLINT NULL,
    approved_by     TEXT NULL,
    approved_at     TIMESTAMPTZ NULL,
    note            TEXT NULL,
    observed_at     TIMESTAMPTZ NOT NULL,
    valid_during    TSTZRANGE NOT NULL,
    recorded_during TSTZRANGE NOT NULL DEFAULT tstzrange(now(), NULL, '[)'),
    superseded_by   BIGINT NULL REFERENCES mac_authorization(entry_id),
    decision_source auth_source NOT NULL DEFAULT 'operator',  -- 0023
    nautobot_status auth_status NULL,            -- last-synced Nautobot intent (0023)
    nautobot_vlan   SMALLINT NULL,               -- 0023
    CONSTRAINT mac_authorization_event_uq UNIQUE (event_id),
    CONSTRAINT mac_authorization_vlan_range
        CHECK (assigned_vlan IS NULL OR assigned_vlan BETWEEN 1 AND 4094),
    CONSTRAINT mac_authorization_nautobot_vlan_range
        CHECK (nautobot_vlan IS NULL OR nautobot_vlan BETWEEN 1 AND 4094)
);
-- Partial unique: at most ONE current belief per MAC
CREATE UNIQUE INDEX mac_authorization_current
    ON mac_authorization (mac) WHERE upper_inf(recorded_during);

Virtual-switch placeholders

device.visibility = 'unknown' marks a device we know exists (its chassis_id appears in LLDP from neighbors) but can’t collect telemetry from. The traceroute CTE knows to emit a synthetic pass-through hop rather than dead-ending. See virtual-switch placeholders for the passthrough mechanics and peer resolution for how chassis_id ties an unknown device to its neighbors’ LLDP.

Per-vendor SNMP plugin dispatch

The SNMP collector reads sysObjectID (1.3.6.1.2.1.1.2.0) on every poll, decodes the IANA Private Enterprise Number from the OID prefix, and selects one of seven vendor plugins (cisco, hp, extreme, juniper, nokia, mikrotik, arista) plus a generic fallback.

The matched plugin’s name flows into the DeviceIdentified event payload’s optional vendor + sys_object_id fields; the reconciler UPDATEs device.vendor from there. Plugins are purely additive in v1 — they can extend the base MIB walk with vendor-specific OIDs but never replace or filter the base, so a misclassified vendor can’t degrade behavior. See event envelope reference for the payload shape.

`oui_vendor`

IEEE manufacturer-prefix lookup table. Three-column composite key on (prefix, prefix_length). Lookup uses longest-prefix-wins to handle MA-S carve-outs of MA-L blocks.

CREATE TABLE oui_vendor (
    prefix          TEXT NOT NULL,             -- lowercase hex, no separators
    prefix_length   SMALLINT NOT NULL,          -- 24 | 28 | 36
    organization    TEXT NOT NULL,
    registry        TEXT NOT NULL,              -- 'MA-L' | 'MA-M' | 'MA-S'
    refreshed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (prefix, prefix_length),
    CHECK (prefix_length IN (24, 28, 36))
);

Populated via make oui-refresh.

Views

`mac_observation_resolved`

Per (device, mac, vlan), picks one “winning” row using a priority order (gnmi > snmp > ssh > netconf > reconciler). The TUI’s FDB tree uses this for its current-state pane.

`mac_observation_disagreement`

Groups by (device, mac, vlan), filters to current beliefs with open valid_during, returns rows where count(distinct port_id) >= 2. The OPS screen’s disagreements pane reads from this. See alembic revision 0002.

Data model

The bitemporal observation tables

mac_observation

liveness

device and port

collection_profile

Other observation tables

rogue_ap_observation

auth_event

mac_authorization