08-rules-engine.sql

-- 08-rules-engine.sql
--
-- A data-driven rules engine: PostgreSQL stores the rules, Ruby evaluates them.

CREATE EXTENSION IF NOT EXISTS postr;

DROP FUNCTION IF EXISTS public.ruby_match_checkout_rules(text, jsonb);
DROP FUNCTION IF EXISTS public.ruby_evaluate_checkout_rules(text, jsonb);
DROP TABLE IF EXISTS public.postr_rules;
DROP TABLE IF EXISTS public.postr_rule_sets;

CREATE TABLE public.postr_rule_sets(
  id bigserial PRIMARY KEY,
  name text NOT NULL UNIQUE
);

CREATE TABLE public.postr_rules(
  id bigserial PRIMARY KEY,
  rule_set_id bigint NOT NULL REFERENCES public.postr_rule_sets(id),
  name text NOT NULL,
  priority integer NOT NULL,
  stop_on_match boolean NOT NULL DEFAULT false,
  predicate jsonb NOT NULL,
  effect jsonb NOT NULL
);

INSERT INTO public.postr_rule_sets(name)
VALUES ('checkout');

INSERT INTO public.postr_rules(rule_set_id, name, priority, stop_on_match, predicate, effect)
SELECT
  id,
  'manual_review_large_order',
  10,
  false,
  '{"field":"order_total","op":"gte","value":1000}'::jsonb,
  '{"kind":"review","queue":"manual"}'::jsonb
FROM public.postr_rule_sets
WHERE name = 'checkout';

INSERT INTO public.postr_rules(rule_set_id, name, priority, stop_on_match, predicate, effect)
SELECT
  id,
  'deny_blocked_email_domain',
  20,
  true,
  '{"field":"email_domain","op":"in","value":["mailinator.com","blocked.test"]}'::jsonb,
  '{"kind":"deny","reason":"blocked email domain"}'::jsonb
FROM public.postr_rule_sets
WHERE name = 'checkout';

INSERT INTO public.postr_rules(rule_set_id, name, priority, stop_on_match, predicate, effect)
SELECT
  id,
  'priority_shipping_for_us_orders',
  30,
  false,
  '{"field":"country","op":"eq","value":"US"}'::jsonb,
  '{"kind":"allow","shipping":"priority"}'::jsonb
FROM public.postr_rule_sets
WHERE name = 'checkout';

CREATE FUNCTION public.ruby_evaluate_checkout_rules(rule_set_name text, facts jsonb)
RETURNS jsonb
LANGUAGE ruby
AS $ruby$
  class RulesEngine
    def initialize(rules)
      @rules = rules.sort_by { |rule| [rule["priority"], rule["id"]] }
    end

    def evaluate(facts)
      matches = []

      @rules.each do |rule|
        next unless matches_rule?(facts, rule["predicate"])

        matches << {
          "rule_id" => rule["id"],
          "name" => rule["name"],
          "priority" => rule["priority"],
          "effect" => rule["effect"]
        }

        break if rule["stop_on_match"]
      end

      {
        "decision" => decide(matches),
        "matched" => matches
      }
    end

    private

    def matches_rule?(facts, predicate)
      actual = facts[predicate["field"]]
      expected = predicate["value"]

      case predicate["op"]
      when "eq" then actual == expected
      when "gte" then !actual.nil? && actual >= expected
      when "lte" then !actual.nil? && actual <= expected
      when "in" then Array(expected).include?(actual)
      else
        raise ArgumentError, "unknown predicate op: #{predicate["op"]}"
      end
    end

    def decide(matches)
      deny = matches.find { |match| match.dig("effect", "kind") == "deny" }
      return deny["effect"] if deny

      first_match = matches.first
      return first_match["effect"] if first_match

      { "kind" => "allow" }
    end
  end

  def load_rules(rule_set_name)
    Postr.select(
      <<~SQL,
        SELECT
          r.id,
          r.name,
          r.priority,
          r.stop_on_match,
          r.predicate,
          r.effect
        FROM public.postr_rules r
        JOIN public.postr_rule_sets rs ON rs.id = r.rule_set_id
        WHERE rs.name = $1
        ORDER BY r.priority, r.id
      SQL
      rule_set_name
    )
  end

  def call(rule_set_name:, facts:)
    RulesEngine.new(load_rules(rule_set_name)).evaluate(facts)
  end
$ruby$;

CREATE FUNCTION public.ruby_match_checkout_rules(rule_set_name text, facts jsonb)
RETURNS TABLE(name text, priority integer, effect jsonb)
LANGUAGE ruby
AS $ruby$
  def call(rule_set_name:, facts:)
    result = Postr.value(
      "SELECT public.ruby_evaluate_checkout_rules($1, $2)",
      rule_set_name,
      facts
    )

    result["matched"].each do |match|
      yield({
        "name" => match["name"],
        "priority" => match["priority"],
        "effect" => match["effect"]
      })
    end

    nil
  end
$ruby$;

SELECT public.ruby_evaluate_checkout_rules(
  'checkout',
  '{"country":"US","order_total":1200,"email_domain":"ada.dev"}'::jsonb
);

SELECT * FROM public.ruby_match_checkout_rules(
  'checkout',
  '{"country":"US","order_total":1200,"email_domain":"ada.dev"}'::jsonb
);