Examples: Block LEFT JOIN "Nullability Bug" Risk

Goal: prevent correctness regressions where an INNER JOIN becomes a LEFT JOIN (or a join is introduced as LEFT) and downstream logic accidentally treats nullable rows as non-null.

End-to-end approach:

1. Use a custom diff rule to detect JoinTypeChanged → LEFT
2. Use a policy to block that custom rule in prod
3. Allow deliberate changes via exception grants

Files to commit

.lexega/
  custom_rules.yaml
  policy.yaml
  exceptions.yaml        # optional

1) Custom rules: detect join type changed to LEFT

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

rules:
  - id: JOIN-LEFT-NULLABILITY-001
    risk_level: critical
    message: "Join type changed from {witness.baseline_kind} to {witness.head_kind} on {witness.left.canonical} ↔ {witness.right.canonical}. Audit null-handling and downstream filters."
    triggers:
      diff.events:
        each:
          all_of:
            - kind: join_type_changed
            - head_kind: { in: [left, natural_left] }

2) Policy: block this in prod (requires exception)

# 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-LEFT-NULLABILITY-001
    description: Block join type changes to LEFT in prod unless exception
    envs: [prod]
    action: block
    requires_exception: true

3) Run review + gate CI

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

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

4) Exception (optional)

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

exceptions:
  - exception_id: EX-2025-LEFTJOIN-001
    policy_id: team-policy
    rule_id: JOIN-LEFT-NULLABILITY-001
    approved_by: [email protected]
    approved_at: "2025-12-31T00:00:00Z"
    reason: "Reviewed LEFT join change; downstream null handling updated"
    ticket: DATA-7777
    scope:
      global:
        expires_at: "2027-03-01T00:00:00Z"

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.