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.

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

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