Rules Engines & DAG-Shaped Schemas
You'll learn to
- -Store detection rules as rows so a rule can be added or switched off with no redeploy
- -Model prerequisites as a directed acyclic graph and check whether a skill can be unlocked
- -Explain why a CHECK cannot stop an indirect cycle, and detect one with a graph walk
This chapter applies the Open/Closed instinct from Phase 1 to schema design. Rules that change often should live as data, not as code that needs a deployment for every change. And structures of prerequisites, such as a skill tree or a dependency list, need a careful shape to stay correct as they grow.
An Analogy: A Thermostat and a Recipe Card
A thermostat has a dial you can change without rewiring the house. The rule "turn on heat below 18 degrees" is a setting, not part of the wiring. Rules as data work the same way. A recipe card works for the second half: you cannot ice the cake before baking it, and baking needs the batter first. Each step lists what must come before it.
Rules as Data: An Anomaly-Detection Ruleset
Hardcoding "if cpu usage is above 90 for 5 minutes, flag an anomaly" means every new detection rule needs a code change and a deployment. Storing rules as rows means a rule can be added, switched off or tuned with an INSERT or UPDATE. An anomaly is a reading that departs from what is normal.
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 redeployThe demo runs a small version. Two rules are rows in a list, and one check function evaluates all of them. Watch what happens when a rule is added and when another is switched off, with no change to check.
There are limits. Simple threshold comparisons fit cleanly as data, but a genuinely complex rule, such as correlating several metrics or a statistical model, usually needs real code. Data-driven rules suit the common case. They do not replace all business logic.
DAG-Shaped Schemas: A Skill Tree
A skill tree, where unlocking "Advanced Combat" requires both "Basic Combat" and "Tactical Awareness", is a DAG, a directed acyclic graph. Directed means each link has a direction (needs, not just relates to). Acyclic means no loops. A node can depend on several others, but a skill that indirectly needs itself is 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 a CHECK Cannot Catch
The CHECK skill_id != prerequisite_skill_id only stops a skill from requiring itself directly. It cannot catch an indirect cycle: A needs B, B needs C, C needs A. That pattern spans several rows, and a single-row CHECK sees only the row being written. Preventing it needs application-level validation, a graph walk before a new edge is allowed, or a database trigger that does the same check. Say so plainly, instead of assuming the CHECK covers everything.
The second half of the demo does exactly that check. With B needing C and A needing B, it tries to make C need A. Starting from A, the walk reaches C, so the new link would close a loop, and it is refused. An unrelated new link is accepted.
Connect back to the recursive query from the previous module. "Does adding this edge create a cycle?" is a reachability question, answerable with the same recursive CTE pattern used for the wormhole network.
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.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!