Data Model Decision

Data Model - Decision Schema

Part of the Data Model reference. Schema: decision. Companion to ADR-0008, Domain-Model.

---

Tables in this schema

Table	Role
decision.decision_rules	Rule master record (mutable)
decision.rule_versions	Immutable versioned rule definitions
decision.decision_logs	Append-only evaluation log (range-partitioned by month)
decision.replay_runs	Batch replay job tracking

---

Table: decision_rules

CREATE TABLE IF NOT EXISTS decision.decision_rules (
    id              UUID          PRIMARY KEY,
    rule_set_id     VARCHAR(64)   NOT NULL,
    name            VARCHAR(255)  NOT NULL,
    status          VARCHAR(16)   NOT NULL DEFAULT 'DRAFT',
    current_version INT           NOT NULL DEFAULT 1,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    created_by      VARCHAR(64)   NOT NULL,
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),

    CONSTRAINT ck_decision_rules_status CHECK (status IN ('DRAFT','ACTIVE','DEPRECATED'))
);

CREATE INDEX IF NOT EXISTS idx_decision_rules_rule_set_status
    ON decision.decision_rules(rule_set_id, status);

CREATE OR REPLACE TRIGGER trg_decision_rules_updated_at
    BEFORE UPDATE ON decision.decision_rules
    FOR EACH ROW EXECUTE FUNCTION shared.set_updated_at();

Query patterns:

• List active rules for a rule set: idx_decision_rules_rule_set_status filtered by status = 'ACTIVE'

---

Table: rule_versions

CREATE TABLE IF NOT EXISTS decision.rule_versions (
    id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_id         UUID        NOT NULL,
    version         INT         NOT NULL,
    priority        INT         NOT NULL DEFAULT 100,
    terminate       BOOLEAN     NOT NULL DEFAULT false,
    definition      JSONB       NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by      VARCHAR(64) NOT NULL,
    active_from     TIMESTAMPTZ,
    active_until    TIMESTAMPTZ,

    CONSTRAINT fk_rule_versions_rule FOREIGN KEY (rule_id)
        REFERENCES decision.decision_rules(id) ON DELETE RESTRICT,
    CONSTRAINT uq_rule_versions UNIQUE (rule_id, version)
);

CREATE INDEX IF NOT EXISTS idx_rule_versions_rule_active
    ON decision.rule_versions(rule_id, active_from DESC, active_until DESC);

Query patterns:

• Load current version of a rule: uq_rule_versions lookup by (rule_id, version) or latest via idx_rule_versions_rule_active
• Point-in-time rule lookup: filter active_from <= :t AND (active_until IS NULL OR active_until > :t) on idx_rule_versions_rule_active

Notes:

• definition stores the JSON DSL representation of the rule. See ADR-0008 for the schema.
• terminate = true means the engine stops evaluating further rules once this one matches.
• Append-only. Once a version is created it is never modified; corrections create a new version.

---

Table: decision_logs (append-only, range-partitioned by month)

CREATE TABLE IF NOT EXISTS decision.decision_logs (
    id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    rule_set_id     VARCHAR(64) NOT NULL,
    input_payload   JSONB       NOT NULL,
    matched_rules   JSONB       NOT NULL,   -- snapshot including rule_version_id refs
    decision        VARCHAR(16) NOT NULL,
    explanation     TEXT        NOT NULL,
    latency_ms      INT         NOT NULL,
    invoked_by      VARCHAR(64) NOT NULL,
    correlation_id  UUID,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT ck_decision_logs_decision CHECK (decision IN ('APPROVE','REJECT','REVIEW'))
) PARTITION BY RANGE (created_at);

-- Initial monthly partitions; subsequent partitions via pg_partman
CREATE TABLE IF NOT EXISTS decision.decision_logs_2026_04 PARTITION OF decision.decision_logs
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');

CREATE TABLE IF NOT EXISTS decision.decision_logs_2026_05 PARTITION OF decision.decision_logs
    FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');

CREATE TABLE IF NOT EXISTS decision.decision_logs_2026_06 PARTITION OF decision.decision_logs
    FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');

CREATE INDEX IF NOT EXISTS idx_decision_logs_rule_set
    ON decision.decision_logs(rule_set_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_decision_logs_correlation
    ON decision.decision_logs(correlation_id) WHERE correlation_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_decision_logs_decision
    ON decision.decision_logs(decision, created_at DESC);

Query patterns:

• Audit log for a rule set: idx_decision_logs_rule_set, filtered by time range
• Correlation trace: idx_decision_logs_correlation (partial)
• Decision breakdown by outcome: idx_decision_logs_decision

Notes:

• Regulatory retention requirement: 7 years. Partition rotation handled by pg_partman with monthly granularity.
• matched_rules stores a snapshot of the matched rule versions (including rule_version_id) at evaluation time, so the log is self-contained for replay and audit even after rule changes.
• input_payload stores the full evaluation input. Ensure no PII is included in payloads logged here; sanitize at the application layer.

pg_partman configuration:

SELECT partman.create_parent(
    p_parent_table  => 'decision.decision_logs',
    p_control       => 'created_at',
    p_type          => 'range',
    p_interval      => '1 month',
    p_premake       => 3
);

---

Table: replay_runs

CREATE TABLE IF NOT EXISTS decision.replay_runs (
    id                  UUID          PRIMARY KEY,
    initiated_by        VARCHAR(64)   NOT NULL,
    range_start         TIMESTAMPTZ   NOT NULL,
    range_end           TIMESTAMPTZ   NOT NULL,
    rule_set_id         VARCHAR(64)   NOT NULL,
    new_rules_snapshot  JSONB         NOT NULL,
    total_evaluated     BIGINT,
    total_diverged      BIGINT,
    status              VARCHAR(32)   NOT NULL DEFAULT 'RUNNING',
    started_at          TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    completed_at        TIMESTAMPTZ,
    error               TEXT,

    CONSTRAINT ck_replay_status CHECK (status IN ('RUNNING','COMPLETED','FAILED','CANCELLED'))
);

Query patterns:

• Load replay run by ID: PK lookup
• List recent runs: no specific index needed; table is small (one row per batch job run)

Notes:

• new_rules_snapshot stores the proposed rule set used for the replay, enabling comparison of historical decisions against the new rules without modifying the live rule set.

---

Liquibase changelogs for this schema

v0.1/
  040-decision-rules.xml
  041-decision-rule-versions.xml
  042-decision-logs.xml        -- creates partitioned table + initial partitions
  043-decision-replay-runs.xml

---

Related

• Data-Model - conventions, ERD, cross-cutting sections
• ADR-0008 - decision engine DSL design
• Domain-Model - Decision aggregate definitions