Scan Pipeline & Device Presence Lifecycle

Scope

Covers what happens after a plugin's rows land in CurrentScan: presence computation, event generation, session/timeline derivation. Not plugin authoring (manifest, data contract, settings) — see plugin-development and docs/PLUGINS_DEV*.md. Not the general *Source attribution system or SQLite audit triggers — see database-patterns; the field-authority section below is the scan-pipeline-local half of that system.

Core tables and views

• CurrentScan — ephemeral scratch table. process_plugin_events() populates it for any plugin whose config.json declares mapped_to_table; process_scan() deletes all rows at the end of every cycle. A value written to a CurrentScan row is not readable in a later cycle — the row is gone by then. See Gotcha 2.
• Devices — persistent identity + state table.
• Events — persistent, append-only log of state-transition events (New Device, Connected, Down Reconnected, Device Down, Disconnected, IP Changed). This is the audit trail; Sessions is derived from it, not the reverse.
• Sessions — fully wiped and rebuilt every cycle from Convert_Events_to_Sessions (below), not incrementally updated. Treat it as a materialized query result, not a live connection state machine.
• Online_History — one row per scan cycle, feeds the dashboard's online/offline graph. A rollup of devPresentLastScan/devAlertDown/devIsSleeping counts on DevicesView — no state of its own.

Key views

• LatestDeviceScan (server/db/db_upgrade.py) — Devices LEFT JOIN'd to the most recent CurrentScan row per (scanMac, scanSourcePlugin) pair, via ROW_NUMBER() OVER (PARTITION BY scanMac, scanSourcePlugin ...). update_devices_data_from_scan() loops over DISTINCT scanSourcePlugin and re-queries this view once per plugin: when two plugins report the same device in one cycle, each contribution is evaluated separately, per field, through the authority mechanism below — they are not merged into one row first.
• LatestEventsPerMAC — most recent Event per MAC, joined to Devices and CurrentScan - via an inner join on CurrentScan, so it silently returns no row at all for a MAC not in CurrentScan this cycle, regardless of that MAC's real event history (see Gotcha 7). The "New Connections" query in insert_events() uses it to decide whether a device was previously down (→ Down Reconnected) or new (→ Connected).
• Convert_Events_to_Sessions — defines "is this device's session still open." There is no close_session() function anywhere in this codebase. A session closes as an emergent property: pair_sessions_events() sets evePairEventRowid on a New Device/Connected/Down Reconnected Event to point at the next Disconnected/Device Down Event for that MAC; this view sets sesStillConnected = 1 exactly when that pairing is NULL. To close a session, insert the right Events row — never mutate Sessions directly (the one exception is create_new_devices()'s reconnect-insert, in the call order below).
• DevicesView — adds computed devIsSleeping/devFlapping/devStatus on top of Devices. The UI and insertOnlineHistory() read presence from this, not the raw Devices table.

process_scan() call order (server/scan/session_events.py) — order is load-bearing

1. save_own_device(), exclude_ignored_devices()
2. insert_events(db) — runs before presence updates for this cycle. The Down/Disconnected/Connected queries need the previous cycle's devPresentLastScan to detect a transition. If this ran after the presence update, every query would see the new value and the edge-triggered design would break — firing never, or every cycle.
3. create_new_devices(db) — runs before presence updates so a brand-new device gets a New Device event, not a Connected event (it has no Devices row yet for step 2's queries to match). Also has a raw INSERT INTO Sessions ... sesStillConnected = 1 for existing devices with no open session — the one place outside the Events-derived path that writes Sessions directly.
4. update_devices_data_from_scan(db) — field-level updates for existing devices; see the authority mechanism below.
5. update_sync_hub_node, update_devLastConnection_from_CurrentScan
6. update_presence_from_CurrentScan(db) — sets devPresentLastScan from CurrentScan for this cycle (step 2 reads this as "previous" on the next cycle).
7. update_devPresentLastScan_based_on_nics(db) — NIC/parent-child presence aggregation; can override step 6 for parent devices.
8. update_devPresentLastScan_based_on_force_status(db) — the user's manual devForceStatus override; runs last, wins over everything above.
9. update_vendors_from_mac, update_ipv4_ipv6, update_icons_and_types — update_ipv4_ipv6() does not go through LatestDeviceScan/FIELD_SPECS; it reads CurrentScan directly with its own PARTITION BY scanMac, address_family ranking, so a device reporting both an IPv4 and an IPv6 row in the same cycle gets both devPrimaryIPv4/devPrimaryIPv6 set from that cycle, not just whichever family happened to win devLastIP's single-value reduction.
10. pair_sessions_events(db) — pairs Events rows as described above.
11. create_sessions_snapshot(db) — DELETE FROM Sessions; INSERT INTO Sessions SELECT * FROM Convert_Events_to_Sessions. Sessions reflects step 10's pairing from here.
12. insertOnlineHistory(db) — dashboard graph rollup.
13. skip_repeated_notifications(db)
14. DELETE FROM CurrentScan — the table's entire lifetime is one call to process_scan().

Field-write authority for scan-derived updates

update_devices_data_from_scan() (server/scan/device_handling.py) does not overwrite fields from whichever plugin ran most recently. Each trackable field is declared once in FIELD_SPECS (scan_col, source_col, a priority list of plugin prefixes, optional allow_override_if_changed). can_overwrite_field() uses that plus get_plugin_authoritative_settings() (a plugin's own authority-override setting, if any) to decide, per field per row, whether this plugin's value may replace what's there. The paired <field>Source column (devNameSource, devLastIPSource, etc.) records who currently owns the field. devMac is never a target of these updates — it's the join key, not a tracked field — so no scan-derived update can alter a device's identity, only its attributes.

This is the scan-pipeline-local half of a bigger attribution system — see database-patterns for FIELD_SOURCE_MAP/server/db/authoritative_handler.py, the full *Source model, and the SQLite triggers that consume it for audit logging. Read both before touching anything that writes a *Source column.

Gotchas

1. A "presence" check exists in more than one place. A per-row signal meaning "don't count this as a live sighting" (e.g. scanPresence) has to reach every query that independently re-derives "is this MAC currently present" from CurrentScan. current_scan_presence_condition() (server/scan/presence.py) centralizes that check for five sites: update_presence_from_CurrentScan() (both statements), update_devLastConnection_from_CurrentScan(), and three of insert_events()'s four queries (both Device Down variants, Disconnected). Two sites can't use it: the "New Connections" query and the raw Sessions insert in create_new_devices() need the actual scanLastIP/scanVendor value off the presence-asserting row via MIN()/GROUP BY, not just a boolean. Check any new presence-adjacent query against both patterns — a bare helper call isn't always enough. A second, deliberately separate predicate, nic_derived_presence_condition() (same file), answers a narrower question — "is this device's absence from CurrentScan masked by NIC-derived presence" (a parent device whose devParentRelType='nic' children satisfy devReqNicsOnline, ANY/ALL, against CurrentScan this cycle) — and is OR-composed onto current_scan_presence_condition() at four of those same sites (both Device Down variants, Disconnected, update_devLastConnection_from_CurrentScan()), not folded into it. The fifth site - the NIC-derived reconnect query (Gotcha 7) - combines them differently: NOT current_scan_presence_condition(...) AND nic_derived_presence_condition(...), since it exists specifically to catch NIC-only presence that the mainline query already handles otherwise. A future presence-adjacent query needs to check both predicates, not just the first one, if it should also treat a NIC-covered parent as present.

2. CurrentScan is deleted at the end of every cycle — a per-row flag on it can't express a decision that needs to survive to a cycle where the row is gone. Anything that fires because a row is missing (Device Down, Disconnected) can't read a flag that lived on that row. A per-row plugin signal that needs to affect behavior beyond its own cycle has to persist onto the Devices row at creation time (e.g. seeding devAlertDown/devAlertEvents from the row's flag instead of the global NEWDEV_* defaults), not ride on the ephemeral table.

3. CurrentScan is not small, and it's indexed on scanMac. Real production users run 10,000+ devices; with one row per contributing plugin (see LatestDeviceScan above), a single cycle's CurrentScan is routinely 20,000-50,000+ rows. idx_currentscan_scanmac (server/db/db_upgrade.py:ensure_CurrentScan(), mirrored in server/db/schema/app.sql) covers every scanMac-keyed lookup in this file. ensure_CurrentScan()'s DROP TABLE/CREATE TABLE runs once, at app startup (DB.initDB(), server/__main__.py) — don't confuse this with the per-cycle DELETE FROM CurrentScan in point 1, which clears rows but leaves the table and its index in place.

4. server/plugins/sync/sync.py bypasses this pipeline on purpose, twice — a permanent exception, not a bug. It fires its own direct INSERT OR IGNORE INTO Events (... 'New Device' ...) for newly-seen synced devices (hardcoded evePendingAlertEmail = 1, no scanNotificationMode awareness), and in carbon-copy mode its own raw Devices UPSERT via ON CONFLICT(devMac) DO UPDATE — both skip create_new_devices()/update_devices_data_from_scan()/can_overwrite_field() (sync.py's own comments: "Node is fully authoritative in this mode"). It's a normal mapped_to_table: CurrentScan plugin for its presence contribution, so IMPORT_ON/scanPresence apply to it like any other plugin — but its two direct-write paths ignore scanNotificationMode = 'quiet' or scanCreatesDevice = 0. Don't assume every Events/Devices write goes through the generic pipeline — sync.py doesn't.

5. A blank/null-equivalent scanMac can create a phantom Devices row. create_new_devices()'s two creation-path queries filter scanMac NOT IN (NULL_EQUIVALENTS_SQL) (server/scan/device_handling.py, const.NULL_EQUIVALENTS_SQL) as a backstop, because scanCreatesDevice defaults to 1 — any plugin reporting a row with no real MAC, without setting scanCreatesDevice = 0 itself, would otherwise create a devMac = '' device, and every other blank-MAC row from every other plugin would then silently write onto it. The filter doesn't replace scanCreatesDevice = 0 as the correct thing for a plugin to set on such rows; it keeps a MAC-less row inert when some other plugin forgets to. Check any new creation-adjacent query against blank scanMac too.

6. app.sql is not dead code. install/production-filesystem/entrypoint.d/25-first-run-db.sh pipes it into sqlite3 to bootstrap a brand-new database on first install; scripts/db_cleanup/regenerate-database.sh uses it too. CurrentScan, Parameters, and Settings are safe from drift: each has a dedicated ensure_X() function (server/db/db_upgrade.py) that drops and recreates the table on every startup, superseding whatever app.sql bootstrapped. Plugins_Language_Strings gets the same treatment inside the shared ensure_plugins_tables(). AppEvents gets its own drop/recreate via AppEvent_obj.__init__() (server/workflows/app_events.py), independent of db_upgrade.py. Devices has no drop/recreate, but server/database.py has 18 explicit ensure_column() calls that backfill any column missing from an older app.sql snapshot on every startup. Events, Sessions, and Notifications get the same backfill via ensure_table_columns() (server/db/db_upgrade.py), driven by one Python column-list constant per table (server/db/schema_columns.py) that's diffed against app.sql in CI (test/db/test_schema_drift_guard.py). AppEvents/Notifications each also have a second schema-definition surface — their own inline CREATE TABLE IF NOT EXISTS in server/workflows/app_events.py/server/models/notification_instance.py — kept in sync by the same drift-check test. Check any new query here with EXPLAIN QUERY PLAN at a realistic row count rather than assuming it's fine because it resembles an existing one — a correlated subquery re-evaluated per row (an accidental self-join) is the pattern most likely to look reasonable while actually being quadratic at this scale.

7. LatestEventsPerMAC is intentionally CurrentScan-gated - correct for its one existing caller, a trap for a new one. It INNER JOINs CurrentScan, which is exactly right for the mainline "New Connections" query (every MAC it looks up is already known to be in CurrentScan this cycle, via its own present_agg). It is not a general-purpose "last event for any MAC" lookup. A caller that needs the last event for a MAC not in CurrentScan this cycle (e.g. a NIC-covered parent with no direct scan row of its own) gets no row at all from this view, regardless of that MAC's real event history - silently, not an error. insert_events()'s NIC-derived reconnect query reads Events directly instead (a correlated ORDER BY eveDateTime DESC LIMIT 1, covered by idx_eve_mac_datetime_desc), sidestepping the view entirely rather than trying to make it handle both shapes.

8. A correlated helper's mac_column argument silently binds to the helper's own inner row, not the caller's, whenever the helper's inner table already has a column of that name in scope - alias or no alias. SQL resolves an unqualified name in the innermost enclosing scope first and only searches outward if nothing matches there; nic_derived_presence_condition()'s inner scan is FROM Devices, and Devices has a devMac column, so any bare "devMac" argument - not just one that happens to collide with an alias name - bound to the helper's own inner row. Every bare-"devMac" call site (both Device Down queries, Disconnected, update_devLastConnection_from_CurrentScan()) was affected: any NIC-covered device anywhere in Devices made every other absent device, including one with no NIC children at all, look NIC-derived-present, silently suppressing its real event or bumping its devLastConnection. (A qualified-but-colliding argument, e.g. "nic_parent.devMac" passed from a caller aliasing its own row nic_parent while the helper's own inner alias was also nic_parent, is the same root cause in a narrower form.) Fixed by requiring every caller to pass a qualified reference to its own table/alias ("Devices.devMac", "DevicesView.devMac") and having nic_derived_presence_condition() reject a bare mac_column outright, on top of the existing presence_scan/nic_presence_parent-collision guards. current_scan_presence_condition() doesn't need this: its inner scan is FROM CurrentScan, which has no devMac column, so a bare "devMac" has nothing to bind to inward and correctly falls back to the caller's row. Only caught by a test with two sibling devices in one DB where one should match and the other shouldn't - every single-device test passed regardless, because "any row" and "this row" are the same row when there's only one.

9. CurrentScan.scanMac/Events.eveMac are permanently MAC-keyed, independent of the devGUID-as-PK migration. See database-patterns' "Device Identity" section - devMac is today's actual schema PK, devGUID is the intended long-term identity, but a plugin can only ever report a MAC from network discovery, never an app-internal devGUID. Don't design around these tables ever becoming devGUID-keyed.

10. insert_events()'s IP Changed query evaluates every distinct present address for a MAC individually, not one MIN()-reduced candidate. A device can have more than one simultaneously-valid, already-known address in the same family (e.g. two live IPv6 addresses); testing only the MIN()'d one against devPrimaryIPv4/devPrimaryIPv6/devLastIP can pick the one address that happens not to be sitting in the single-valued slot, firing a false event even though nothing actually changed. The fix makes "known" additive: an address counts as known if it matches the three-slot check or if it's been reported (and isn't missing-in-last-scan) by an eligible plugin in Plugins_Objects, via _known_plugin_ip_addresses_sql()'s use of plugin_field_views.get_plugin_columns_for_field(all_plugins, "ip") - the same plugin-to-column mapping the Field View tab uses. all_plugins is optional on both insert_events() and process_scan() (default None); when it's not eligible for any plugin (or a caller hasn't passed it), the additive half is skipped entirely and the query reduces to exactly the three-slot check. Addresses that fail known() are aggregated into one Events row per MAC per cycle (today's shape), listing every new address in eveAdditionalInfo rather than firing one row per address.

When to read this vs. other docs/skills

• Writing or reviewing a plugin's config.json/data contract → plugin-development, docs/PLUGINS_DEV*.md. This skill covers what happens after a plugin's rows land in CurrentScan, not the authoring contract.
• Devices-table write paths, *Source attribution, audit/history logging, SQLite triggers → database-patterns.
• Implementing a change here → read the actual function in server/scan/session_events.py/server/scan/device_handling.py first; this skill's line numbers are a map, not a guarantee, and drift as the code moves.