skills.rest
netalertx
netalertxOFFICIAL
Explore all skills by this author →
𝕏@NetAlertXGitHub

netalertx-database-patterns

Guides SQLite trigger design and audit logging patterns for the NetAlertX Devices table.

7.0k|426|Updated Dec 23, 2021
Search Tags:#sqlite#change tracking#audit logging#netalertx#Event Sourcing#database triggers#Schema design
One-click install
npx skills add https://github.com/netalertx/NetAlertX --skill netalertx-database-patterns
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: netalertx-database-patterns
Source: https://github.com/netalertx/NetAlertX/tree/main/.github/skills/database-patterns
Command: npx skills add https://github.com/netalertx/NetAlertX --skill netalertx-database-patterns

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Features that write to the NetAlertX Devices table can silently miss one of 14+ write paths, causing correctness bugs and incomplete audit history. This Skill documents the full write-path inventory, the *Source attribution system, and proven patterns for choosing SQLite triggers over Python hooks.

Core Features & Use Cases

  • Write-Path Inventory: Lists every known function that modifies the Devices table so no update path is overlooked when designing new features.
  • Attribution System Guidance: Explains the FIELD_SOURCE_MAP and *Source columns so features can derive correct changedBy values from NEW.*Source fields inside triggers.
  • Trigger vs Python Hook Decision Rules: Provides criteria and a performance-safe trigger template with WHEN guards and per-field conditional inserts.
  • Use Case: When implementing a DevicesHistory audit log, use this Skill to build an event-sourced AFTER UPDATE trigger that captures per-field changes with attribution instead of costly full-row snapshots.

Quick Start

Ask the AI to design an audit logging trigger for the Devices table using the NetAlertX database patterns.

Frequently Asked Questions about netalertx-database-patterns

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I audit all writes to the NetAlertX Devices table?▼

Use a SQLite AFTER UPDATE trigger on the Devices table rather than Python hooks. Triggers automatically catch all 14+ write paths, including executemany bulk updates, and can read attribution from NEW.*Source columns in the same transaction.

SQLite trigger vs Python hook for change logging, which should I use?▼

Prefer SQLite triggers when the feature must intercept every write path, since they are self-healing and require no changes to existing functions. Use Python hooks only when the logic needs application-layer objects, settings, or services unavailable in SQL.

How does NetAlertX track which source changed a device field?▼

The FIELD_SOURCE_MAP in authoritative_handler.py pairs 10 fields with *Source columns holding values like USER, LOCKED, NEWDEV, or plugin prefixes. Triggers derive changedBy with COALESCE(NULLIF(NEW.fieldSource, ''), 'system').

Should audit history use snapshots or event-sourced rows?▼

Use event-sourced per-field rows, not full-row snapshots. Snapshots store all 40+ columns per mutation and can reach roughly 280 MB per day at 1000 devices, while event-sourced storage typically stays under 1 MB per day.

Do per-row Settings reads inside SQLite triggers hurt performance?▼

No. The Settings table is about 100 rows and stays in SQLite's page cache, so per-row reads are effectively in-memory lookups. A WHEN guard on the trigger makes the disabled state zero-cost.

Related Skills

Explore More Software Engineering Skills→
D26FORWARDD26FORWARD

schema-exploration

List database tables, inspect schemas, and map foreign key join paths.

Community
Intermediate
HainrixzHainrixz
18

db-migration-safety

Analyze SQL migration scripts for reversibility, lock levels, and destructive operations.

Community
Intermediate
UnsupervisedcomUnsupervisedcom
49

manual_tests

Automates DeepWork hook and rule tests across isolated sub-agents with enforced constraints.

Official
Basic
gwpjpgwpjp
3

web3-implementer

Coordinate Ponder schema queries and wagmi reads/writes with two-layer hooks.

Community
Advanced