Skip to content

database-schema.md

Latest commit

 

History

History
157 lines (114 loc) · 5.22 KB

database-schema.md

File metadata and controls

157 lines (114 loc) · 5.22 KB
Raw

Database Schema

network-device-watch persists inventory in SQLite (schema version 1). The database stores full identifiers regardless of report privacy mode.

Connection settings

  • PRAGMA foreign_keys = ON
  • Parent directories for database.path are created automatically
  • Schema is initialized on first open

Tables

schema_version

Tracks applied schema migrations.

Column Type Constraints
version INTEGER PRIMARY KEY
applied_at_utc TEXT NOT NULL

Current version: 1

devices

One row per normalized MAC address.

Column Type Constraints Description
id INTEGER PRIMARY KEY AUTOINCREMENT Internal device id
mac_normalized TEXT NOT NULL UNIQUE Uppercase colon-separated MAC
first_seen_utc TEXT NOT NULL First observation timestamp
last_seen_utc TEXT NOT NULL Most recent observation
current_ip TEXT Latest IP address
current_hostname TEXT Latest meaningful hostname
vendor_label TEXT Static vendor label from observations
classification TEXT NOT NULL DEFAULT 'unknown' Advisory label
classification_source TEXT Policy rule id
active INTEGER NOT NULL DEFAULT 1 1 = active, 0 = inactive
notes TEXT Operator notes (reserved)
created_at_utc TEXT NOT NULL Row creation time
updated_at_utc TEXT NOT NULL Last update time

Indexes: mac_normalized, classification, active

observations

Individual device sightings. Idempotent via uniqueness_key.

Column Type Constraints Description
id INTEGER PRIMARY KEY AUTOINCREMENT
device_id INTEGER NOT NULL, FK → devices.id Parent device
observed_at_utc TEXT NOT NULL Observation timestamp
ip_address TEXT IP at observation time
hostname TEXT Hostname at observation time
source_type TEXT NOT NULL Parser format (e.g. csv)
source_name TEXT NOT NULL Source identifier / file label
raw_vendor_label TEXT Vendor from source row
uniqueness_key TEXT NOT NULL UNIQUE Idempotency key
created_at_utc TEXT NOT NULL Insert time

Indexes: observed_at_utc, device_id

Re-ingesting the same observation (same uniqueness_key) does not create duplicates.

events

Audit trail of inventory changes and parser issues.

Column Type Constraints Description
id INTEGER PRIMARY KEY AUTOINCREMENT
device_id INTEGER FK → devices.id, nullable Related device (null for global parser events)
event_type TEXT NOT NULL See events-reference.md
severity TEXT NOT NULL info, warning, critical, error
event_at_utc TEXT NOT NULL Event timestamp
summary TEXT NOT NULL Short description
details_json TEXT JSON blob with structured details
acknowledged INTEGER NOT NULL DEFAULT 0 Operator acknowledgment flag
idempotency_key TEXT UNIQUE Prevents duplicate events
created_at_utc TEXT NOT NULL Insert time

Indexes: event_at_utc, event_type, severity

Entity relationships

erDiagram
    devices ||--o{ observations : has
    devices ||--o{ events : generates

    devices {
        integer id PK
        text mac_normalized UK
        text classification
        integer active
    }

    observations {
        integer id PK
        integer device_id FK
        text uniqueness_key UK
        text observed_at_utc
    }

    events {
        integer id PK
        integer device_id FK
        text event_type
        text severity
        text idempotency_key UK
    }
Loading

Active device logic

A device is active when its last_seen_utc falls within inventory.active_window_hours of the current time. Inactive devices remain in the database but can be excluded from reports with --no-include-inactive.

The internal refresh_active_flags() function emits device_became_active / device_became_inactive events when status changes. This is not exposed as a standalone CLI command in v1.0.0.

Default paths

Config Default path
Production data/inventory.db
Demo temp/demo-inventory.db

Backup and portability

The database is a single SQLite file. To backup:

cp data/inventory.db data/inventory.db.backup

Or use SQLite's backup API / sqlite3 .backup for online copies.

Security note

Because the database contains full MAC, IP, and hostname values:

  • Restrict filesystem permissions (chmod 600 on Linux; ACLs on Windows)
  • Exclude from public repositories and unencrypted cloud sync
  • Apply privacy modes when exporting via reports, not when assuming the DB is safe to share

See security-and-privacy.md.

Querying directly

For ad-hoc inspection:

sqlite3 data/inventory.db "SELECT mac_normalized, classification, current_ip, active FROM devices LIMIT 10;"

Prefer list-devices and list-events CLI commands for privacy-aware output.