Rules Engines & DAG-Shaped Schemas
You'll learn to
- -Model a pattern/anomaly-detection ruleset as data instead of hardcoded conditionals
- -Design a DAG-shaped schema (a skill tree or dependency graph) that enforces valid prerequisite ordering
This chapter applies the Open/Closed instinct from Phase 1 directly to schema design: rules that change frequently should live as data, not as code that needs a deployment every time a rule changes - and prerequisite structures (a skill tree, a dependency graph) need their own careful schema shape to stay correct as they grow.
Rules as Data: An Anomaly-Detection Ruleset
Hardcoding "if cpu_usage > 90 and duration_minutes > 5, flag as anomaly" directly in application code means every new detection rule requires a code change and a deployment. Modeling rules as rows instead lets a rule be added, disabled, or tuned by inserting or updating data - no redeploy required.
CREATE TABLE detection_rules (
id INTEGER PRIMARY KEY,
rule_name TEXT NOT NULL,
metric TEXT NOT NULL, -- 'cpu_usage', 'network_latency'
operator TEXT NOT NULL CHECK (operator IN ('>', '<', '>=', '<=', '=')),
threshold DECIMAL(10, 2) NOT NULL,
duration_minutes INTEGER NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE anomaly_flags ( -- rule matches, recorded as events
id INTEGER PRIMARY KEY,
rule_id INTEGER NOT NULL REFERENCES detection_rules(id),
entity_id INTEGER NOT NULL,
flagged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- a new detection rule is one INSERT, not a code change and redeployThis has real limits worth naming: simple threshold comparisons fit cleanly as data, but a genuinely complex rule (multi-metric correlation, statistical models) usually needs actual code - the data-driven approach is a strong fit for the common case, not a universal replacement for all business logic.
DAG-Shaped Schemas: A Skill Tree
A skill tree - where unlocking "Advanced Combat" requires already having both "Basic Combat" and "Tactical Awareness" - is a Directed Acyclic Graph: nodes with prerequisite edges, where a node can depend on multiple others, and cycles (a skill indirectly requiring itself) are invalid.
CREATE TABLE skills (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE skill_prerequisites ( -- the DAG's edges
skill_id INTEGER NOT NULL REFERENCES skills(id),
prerequisite_skill_id INTEGER NOT NULL REFERENCES skills(id),
PRIMARY KEY (skill_id, prerequisite_skill_id),
CHECK (skill_id != prerequisite_skill_id) -- a skill cannot require itself directly
);
-- "can this user unlock skill X" - every prerequisite must already be unlocked:
SELECT NOT EXISTS (
SELECT 1 FROM skill_prerequisites sp
WHERE sp.skill_id = :target_skill_id
AND sp.prerequisite_skill_id NOT IN (SELECT skill_id FROM user_unlocked_skills WHERE user_id = :user_id)
) AS can_unlock;The Cycle Problem the Direct CHECK Constraint Cannot Catch
The CHECK (skill_id != prerequisite_skill_id) constraint above only prevents a skill from directly requiring itself - it cannot catch an indirect cycle (A requires B, B requires C, C requires A), since that pattern spans multiple rows and no single-row CHECK constraint can see across rows. Preventing indirect cycles requires either application-level validation (a graph traversal checking for cycles before allowing a new prerequisite edge to be inserted) or a database trigger that performs the same check - a genuine schema-design limitation worth naming rather than assuming the CHECK constraint fully covers it.
This is a good moment to explicitly connect back to the graph-traversal recursive query technique from the Distributed Modeling module - detecting "does adding this edge create a cycle" is itself a graph reachability question, answerable with the same recursive CTE pattern used for the wormhole navigation chapter.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design Anomaly Detection and The Construct schemas in the LLD Lab's Matrix act.