Examples: Block Missing "Critical Filter" (Table/Column Heuristic)

Goal: block PRs that may have removed or weakened a "critical filter" like row_status = 'A'.

Important limitation: Lexega's diff matching currently operates on structured signals (tables, columns, change types). It does not match the exact predicate text row_status = 'A' inside WHERE.

So the end-to-end workflow is a heuristic:

1. Detect WhereRemoved / WhereConditionChanged
2. Scope to a specific table
3. Scope to a specific column name (e.g., row_status)
4. Emit a custom policy signal
5. Block that custom rule in prod (with exceptions)

Files to commit

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

1) Custom rules: emit a signal on dangerous WHERE changes

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

rules:
  - id: CRITICAL-FILTER-001
    risk_level: high
    message: "Potential critical-filter change on {witness.table.canonical}. Verify row_status filtering remains correct."
    triggers:
      diff.events:
        each:
          all_of:
            - kind: { in: [where_removed, where_condition_changed] }
            - table.canonical: ANALYTICS.ORDERS

Two notes on the predicate shape:

• diff.events is a list of Change items. The each: quantifier fires one signal per matching event, so authors get one finding per affected WHERE site instead of one per file.
• This example gates on table identity (table.canonical: ANALYTICS.ORDERS) — every WHERE change on that table fires. Column-level discrimination (matching on removed_columns or column.name) is on the roadmap; today the extractor leaves those slots unpopulated for unqualified column references, so a stricter rule would silently never fire. Qualify the table in your call sites and gate on table.canonical until column-level diff facts land.

2) Policy: block that custom rule in prod

# 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: CRITICAL-FILTER-001
    description: Block critical-filter changes in prod unless exception
    envs: [prod]
    action: block
    requires_exception: true

3) Run review (PR range) and 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)

If the filter change is intentional and reviewed, grant an exception:

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

exceptions:
  - exception_id: EX-2025-CRITFILTER-001
    policy_id: team-policy
    rule_id: CRITICAL-FILTER-001
    approved_by: [email protected]
    approved_at: "2025-12-31T00:00:00Z"
    reason: "Reviewed critical-filter change; safe"
    ticket: DATA-5678
    scope:
      scoped:
        path_patterns:
          - "**/models/**"
        expires_at: "2027-03-01T00:00:00Z"

Use a path_pattern (or a prefix that matches the absolute file path the engine reports, e.g. "/path/to/repo/models/"). A bare relative prefix like "models/" will not match the absolute file paths findings carry.

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