Examples: Block JOIN Condition Changes for Specific Table Pairs

Goal: block JOIN condition changes, but only for a specific table pair that you consider high-risk.

This uses a custom rule to detect the change, and a policy to block it in specific environments.

Files to commit

.lexega/
  custom_rules.yaml      # defines detection logic
  policy.yaml            # references rule IDs for enforcement
  exceptions.yaml        # optional

1) Custom Rule: detect JOIN changes on specific tables

First, define a custom rule that fires when JOIN conditions change for your critical tables:

# .lexega/custom_rules.yaml
# yaml-language-server: $schema=https://lexega.com/schemas/v1/custom_rules.schema.json

rules:
  - id: JOIN-PAIR-ORDERS-CUSTOMERS
    risk_level: high
    message: "JOIN condition changed for critical table pair: {witness.left.canonical} ↔ {witness.right.canonical}."
    triggers:
      diff.events:
        each:
          all_of:
            - kind: join_condition_changed
            - any_of:
                - left.canonical:  { in: ["ANALYTICS.ORDERS", "ANALYTICS.CUSTOMERS"] }
                - right.canonical: { in: ["ANALYTICS.ORDERS", "ANALYTICS.CUSTOMERS"] }

How matching works:

• kind: join_condition_changed — only fires on JOIN predicate changes
• The any_of block fires if ANY of the listed tables appears on either side of the join. To require BOTH tables to be from the critical list, replace any_of with all_of.
• each: makes this a per-witness rule: one signal per matching event. {witness.left.canonical} / {witness.right.canonical} interpolate the matched event's table refs.

2) Policy: block that rule in prod

The policy references the rule ID — it doesn't define detection logic:

# .lexega/policy.yaml
# yaml-language-server: $schema=/schemas/v1/policy.schema.json
schema_version: 1
policy_id: team-policy
policy_version: 1.0.0

default_action: allow

policies:
  - rule_id: JOIN-PAIR-ORDERS-CUSTOMERS
    description: Block JOIN condition changes for high-risk table pair
    envs: [prod]
    action: block
    requires_exception: true

3) Run review with policy (CI gate)

lexega-sql review main..HEAD models/ -r \
  --custom-rules .lexega/custom_rules.yaml \
  --policy .lexega/policy.yaml \
  --env prod \
  --decision-out decision.json \
  --format markdown > review.md

Gate on the decision artifact:

ALLOWED=$(jq -r '.outcome.allowed' decision.json)
test "$ALLOWED" = "true"

4) Exception (optional)

If you need to permit a one-off change:

# .lexega/exceptions.yaml
# yaml-language-server: $schema=/schemas/v1/exceptions.schema.json
schema_version: 1

exceptions:
  - exception_id: EX-2025-001
    policy_id: team-policy
    rule_id: JOIN-PAIR-ORDERS-CUSTOMERS
    approved_by: [email protected]
    approved_at: "2025-12-31T00:00:00Z"
    reason: "Reviewed JOIN predicate update, tested downstream impact"
    ticket: DATA-1234
    scope:
      scoped:
        path_prefixes:
          - "models/"
        expires_at: "2026-03-01T00:00:00Z"

Then run review with exceptions:

lexega-sql review main..HEAD models/ -r \
  --custom-rules .lexega/custom_rules.yaml \
  --policy .lexega/policy.yaml \
  --exceptions .lexega/exceptions.yaml \
  --env prod \
  --decision-out decision.json

Key Concepts

Separation of concerns:

• Custom rules define WHAT to detect (detection logic)
• Policies define WHAT TO DO when a rule fires (enforcement)
• Exceptions grant one-off permissions for specific rules

This separation keeps policies simple and detection testable.