Skip to content

Business Rules

Expecto validates data at two levels, and it helps to keep them distinct.

Entity validation rules

An Entity carries row-level validation rules. Each rule is an expression that every row must satisfy; rows that fail are reported as violations. Rules compile to a DuckDB violation-finding query of the shape SELECT ... WHERE NOT (expression).

Rules support:

  • Operators: ==, !=, >, <, and, or, not.
  • Field references: d.field_name for the row under validation, c.context_field for session context.
  • Catalog lookups: the session's Catalogs are available as cat_<name> tables (cat_<name>__org, cat_<name>__project, cat_<name>__provider by scope), the same names transformers and business rules use — e.g. flag a tax code missing from the provider's cat_steuerkennzeichen__provider.
  • Context table lookups: tables in the session context (_tables, e.g. fetched by a context source) are available as ctx_<name> — again the same names transformers and business rules use.
  • Functions: len(), matches(), in(), is_empty(), abs(), nz(), round(), and more.
  • Severity: ERROR, WARNING, or INFO.
  • Preconditions and localized messages (per-locale text).

Editing in the UI. The entity-rule editor is the same SQL workbench the project business rules use: a schema explorer on the left lists data with the entity's columns, plus the catalogs and context tables the entity's projects see (cat_*, ctx, ctx_<name>), and the editor autocompletes their names. Click a table or column to insert it at the cursor.

Project business rules

A Project carries cross-cutting business rules in a dedicated table (not JSONB). Each is either an expression or a SQL rule, is ordered, and can be toggled active. These cover checks that span the delivery rather than a single row — including cross-entity reconciliation:

  • Keyed reconciliation — a FULL OUTER JOIN on a grouping key (e.g. a bookings file must balance against a totals file per account).
  • Global reconciliation — a CROSS JOIN of scalar aggregates.

The not_applicable outcome

A rule can resolve to not applicable rather than pass/fail when its preconditions do not hold for a given delivery — for example a rule about a VAT source when that optional source was not delivered. Prefer this semantic outcome over surfacing a technical error (like "unknown table"): it tells the reviewer the rule simply did not apply, instead of looking like a failure.

Severity and blocking

A project's blocking severity decides which violations stop a session from being approved. WARNING and INFO violations are surfaced for review without necessarily blocking; ERROR violations typically block until resolved.