Skip to content
Skills Library
Programming · Building & coding

database-patterns

/database-patterns

NetAlertX database architecture patterns. Use this when designing features that write to the Devices table, implementing audit/history logging, or choosing between trigger-based vs Python-hook approaches.

Repository
NetAlertX ↗
Licence
GPL-3.0
Last repository change
27.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 27.10.2026, without references to retired models or APIs.

Licence

GPL-3.0, otwarta.

What it does and when to use it

NetAlertX database architecture patterns. Use this when designing features that write to the Devices table, implementing audit/history logging, or choosing between trigger-based vs Python-hook approaches.

Original author description

NetAlertX database architecture patterns. Use this when designing features that write to the Devices table, implementing audit/history logging, or choosing between trigger-based vs Python-hook approaches.

How it works

  1. Devices Table — Write-Path Inventory
  2. Device Identity: devMac (today's PK) vs devGUID (the intended durable identity)
  3. Source Fields — Attribution System
  4. Cross-Cutting Concerns — Prefer SQLite Triggers Over Python Hooks
  5. Snapshot vs Event-Sourced Audit Logging
  6. DevicesHistory Table — Reference Schema
Original SKILL.md headings · 149 lines, 1,016 words
SKILL.md preview
---
name: database-patterns
description: NetAlertX database architecture patterns. Use this when designing features that write to the Devices table, implementing audit/history logging, or choosing between trigger-based vs Python-hook approaches.
---

# Database Patterns

## Devices Table — Write-Path Inventory

Before implementing any feature that reads or writes the `Devices` table, audit ALL write paths. The table is modified from many locations — missing one path is a correctness bug.

**Known production write paths:**

| File | Function | Fields written |
|---|---|---|
| `server/models/device_instance.py` | `setDeviceData()` | All user-editable fields |
| `server/models/device_instance.py` | `updateField()` | Any single field (workflows) |
| `server/models/device_instance.py` | `updateDeviceColumn()` | Any single column |
| `server/models/device_instance.py` | `deleteDevices()` etc. | DELETE operations |
| `server/scan/device_handling.py` | `update_devices_data_from_scan()` | Scan-derived fields |
| `server/scan/device_handling.py` | `update_vendors_from_mac()` | `devVendor`, `devVendorSource` |
| `server/scan/device_handling.py` | Name resolution block | `devName`, `devFQDN`, `*Source` |
| `server/scan/device_handling.py` | `update_ipv4_ipv6()` | `devPrimaryIPv4`, `devPrimaryIPv6` |
| `server/scan/device_handling.py` | `update_icons_and_types()` | `devIcon`, `devType` |
| `server/scan/device_handling.py` | `update_presence_from_CurrentScan()` | `devPresentLastScan` |
| `server/scan/device_handling.py` | `update_devLastConnection_from_CurrentScan()` | `devLastConnection` |
| `server/scan/device_handling.py` | `update_devPresentLastScan_based_on_*()` | `devPresentLastScan` |
| `server/db/authoritative_handler.py` | `enforce_source_on_user_update()` | `*Source` columns |
| `server/db/authoritative_handler.py` | `lock_field()` / `unlock_field()` | `*Source` columns |
| `server/models/notification_instance.py` | `clearPendingEmailFlag()` | `devLastNotification` |
| `server/plugins/db_cleanup/script.py` | `cleanup_database()` | DELETE operations |

**Key insight:** Most scan functions use `sql.executemany()` — there is no per-row Python state available. Python hooks before/after executemany require a pre-fetch+diff pattern that is expensive and error-prone.

---

## Device Identity: `devMac` (today's PK) vs `devGUID` (the intended durable identity)

`devMac STRING(50) PRIMARY KEY NOT NULL COLLATE NOCASE` (`server/db/schema/app.sql`) is still the literal SQL primary key. `devGUID TEXT` (indexed via `idx_dev_guid`) is a plain column today, but per the maintainer it's the intended long-term durable identity, since MAC has known limits as an identifier that `devGUID` doesn't share (privacy MAC randomization on iOS/Android/Windows, virtualized/containerized interfaces sharing one physical MAC, multi-homed devices presenting several). `devGUID` already backs device-history grouping (`server/models/device_history_instance.py`) and workflow trigger lookups (`server/workflows/triggers.py`).

This is a gradual, in-progress migration, not a flag day. New code should resolve device identity from an already-fetched device row (which carries both `devMac` and `devGUID`) rather than assuming either field is *the* identifier, so it doesn't need rework as the migration progresses.

**One thing that will never migrate, regardless of how far the PK change goes:** `Plugins_Objects.objectPrimaryId`, `CurrentScan.scanMac`, and `Events.eveMac` are permanently MAC-keyed. A plugin discovers a device by scanning the network, so it can only ever report a MAC address, never an app-internal `devGUID` NetAlertX hasn't assigned yet at scan time. This isn't a migration gap to eventually close; it's a structural ceiling on what network-originated data can ever identify a device by.

---

## `*Source` Fields — Attribution System

The `FIELD_SOURCE_MAP` in `server/db/authoritative_handler.py` defines 10 fields that carry write attribution via paired `*Source` columns:

```python
FIELD_SOURCE_MAP = {
    "devMac":           "devMacSource",
    "devName":          "devNameSource",
    "devFQDN":          "devFQDNSource",
    "devLastIP":        "devLastIPSource",
    "devVendor":        "devVendorSource",
    "devSSID":          "devSSIDSource",
    "devParentMAC":     "devParentMACSource",
    "devParentPort":    "devParentPortSource",
    "devParentRelType": "devParentRelTypeSource",
    "devVlan":          "devVlanSource",
}
```

`*Source` values: `'USER'`, `'LOCKED'`, `'NEWDEV'`, or a plugin prefix (e.g., `'ARPSCAN'`, `'NSLOOKUP'`).

These fields are updated **in the same transaction** as the primary field. A SQLite `AFTER UPDATE` trigger can read `NEW.devNameSource` to obtain correct attribution without any extra context-passing.

**Attribution rules for features that need `changedBy`:**

| Field category | Attribution |
|---|---|
| In `FIELD_SOURCE_MAP` | `COALESCE(NULLIF(NEW.<field>Source, ''), 'system')` |
| User-only fields (`devGroup`, `devComments`, `devFavorite`, `devOwner`, `devLocation`, etc.) | `'user:api'` — only `setDeviceData()` writes these |
| Auto-computed fields (`devIcon`, `devType`, `devPrimaryIPv4`, `devPrimaryIPv6`) | `'system'` |
| `*Source` fields themselves | `'system'` |

---

## Cross-Cutting Concerns — Prefer SQLite Triggers Over Python Hooks

When a feature needs to intercept **every write** to the `Devices` table (audit logging, computed columns, cascading logic), prefer a **SQLite `AFTER UPDATE` / `AFTER INSERT` trigger** over Python-layer hooks.

**Why:**
- Triggers catch all 14+ write paths automatically, including `executemany()` bulk updates
- Zero modifications to existing write-path functions (DRY)
- Self-healing: future write paths are automatically covered
- Attribution is available via `NEW.*Source` fields (see above)

**When Python hooks are still appropriate:**
- The logic needs access to Python objects, settings, or services not available in SQL
- The feature only fires from one or two known write paths
- The logic is too complex to express in SQL (multi-table joins with app-layer business logic)

### Trigger Performance Pattern

```sql
CREATE TRIGGER trg_example
AFTER UPDATE ON Devices
FOR EACH ROW
-- Guard: short-circuit entire body when feature is disabled (zero cost)
WHEN (SELECT CAST(setValue AS INTEGER) FROM Settings WHERE setKey = 'FEATURE_ENABLED') > 0
BEGIN
  -- Per-field conditional insert
  INSERT INTO SomeTable (devGUID, column, oldVal, newVal, changedBy, ts)
  SELECT NEW.devGUID, 'devName', OLD.devName, NEW.devName,
         COALESCE(NULLIF(NEW.devNameSource, ''), 'system'),
         datetime('now', 'utc')
  WHERE OLD.devName IS NOT NEW.devName
    AND instr(',' || (SELECT setValue FROM Settings WHERE setKey = 'TRACKED_FIELDS') || ',', ',devName,') > 0;
  -- Repeat for each tracked field...
END;
```

**Performance:** The Settings table is tiny (~100 rows) and stays in SQLite's page cache. Per-row Settings reads inside triggers are effectively in-memory lookups. The `WHEN` guard makes the disabled state zero-cost.

---

## Snapshot vs Event-Sourced Audit Logging

When implementing change history, always use **event-sourced (per-field rows)** not **snapshots (full row copies)**.

| | Event-sourced | Snapshot |
|---|---|---|
| Storage | Small — only changed fields | Large — all 40+ columns every mutation |
| Filter by field | O(log n) via index | O(n) — must diff every adjacent pair |
| Filter by source | O(log n) via index | Not possible without diffing |
| `changedBy` attribution | Embedded at write time | Not available without extra context |
| Retention calculation | Simple timestamp DELETE | Same, but much higher storage |

At 1000 devices, 5-min scan interval, 14-day retention: snapshot storage ≈ 280 MB/day. Event-sourced storage for the same workload is typically <1 MB/day (most scans produce no tracked field changes).

---

## `DevicesHistory` Table — Reference Schema

```sql
CREATE TABLE IF NOT EXISTS DevicesHistory (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    devGUID      TEXT NOT NULL,
    timestamp    DATETIME DEFAULT CURRENT_TIMESTAMP,
    changedBy    TEXT NOT NULL,
    changedColumn TEXT NOT NULL,
    oldValue     TEXT,
    newValue     TEXT,
    FOREIGN KEY (devGUID) REFERENCES Devices(devGUID) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_devhist_guid_column ON DevicesHistory(devGUID, changedColumn);
CREATE INDEX IF NOT EXISTS idx_devhist_timestamp   ON DevicesHistory(timestamp);
```

Versions

RepositoryScoreLicence
This page · netalertx/NetAlertX100GPL-3.0
majiayu000/spellbook100MIT

Identical content appears in 2 other GitHub folders. We show one copy with the highest score.

Database snapshot: 13.11.2026 · package version 03c76eb9678e (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 95netalertx-code-standards
    NetAlertX coding standards and conventions. Use this when writing code, reviewing code, or implementing features.
  • 95netalertx-plugin-run-development
    Create and run NetAlertX plugins. Use this when asked to create plugin, run plugin, test plugin, plugin development, or execute plugin script.
  • 100node-patterns
    Backend architecture patterns, API design, database optimization, and server-side best practices for Node.js, Express, and Next.js API routes. Use when building or reviewing Node.js, Express, or Next.js API routes and their data access.
  • 95backend-patterns
    Backend architecture patterns, API design, database optimization, and server-side best practices for Node.js, Express, and Next.js API routes. Use when building or reviewing Node.js, Express, or Next.js API routes and their data access.
  • 100bulletproof-react
    Bulletproof React architecture patterns for scalable, maintainable applications. Covers feature-based project structure, component patterns, state management boundaries, API layer design, error handling, security, and testing strategies. Use when structuring a React project,…
  • 95database-expert
    Advanced database design and administration for PostgreSQL, MongoDB, and Redis. Use when designing schemas, optimizing queries, managing database performance, or implementing data patterns.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.