Skip to content
LLD Learn/Planet-Scale Schema Design
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Rules Engines & DAG-Shaped Schemas

4 min read

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.

Detection rules stored as structured, queryable data
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 redeploy

The 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.

Press Run: rules come and go as data, and the evaluator never changes

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.

Skills and their prerequisite edges
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.

Press Run: unlock rules from prerequisites, and a cycle refused before it is saved

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.

Ready to Build This?

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.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!