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

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

1. device_collector.extras['poll_interval_seconds'] — explicit per-row override
2. profile.<source>_interval_seconds — per-source override on the profile
3. profile.default_poll_interval_seconds — profile default
4. 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).

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.

See also

• Why bitemporal? — the why behind two TSTZRANGE columns
• How to spot disagreements — using the disagreement view in practice