# Data Quality

> Data contracts with scheduled, rule-based checks that produce a weighted quality score, track trends over time, and alert on threshold breaches.

Source: https://moberghr.github.io/beacon/features/data-quality/

Beacon monitors the health of your tables through **data contracts**: scheduled, rule-based checks that produce a
weighted quality score, track trends over time, and alert your team when a table drops below its agreed threshold.

## Overview

A **data contract** is an agreement about the expected state of one table: how fresh its data should be, how many rows
it should hold, which columns must be unique or non-null, and so on. Each contract:

- targets **one table** (schema and table) on one database [data source](https://moberghr.github.io/beacon/features/data-sources/);
- contains one or more **rules**, each with its own severity and weight;
- runs on a **cron schedule** and on demand;
- produces an **overall score (0–100%)** per evaluation, a severity- and weight-adjusted average of the rule scores;
- optionally **alerts recipients** when the score falls below a failure threshold.

Every evaluation is stored, so each contract has a full history, and each table has a latest score with a trend
(improving, stable, degrading).

:::note
Data contracts evaluate on **PostgreSQL, SQL Server and MySQL** data sources. On other database engines their rules
fail with "Rule type … is not supported on …". A contract on a non-database data source (a REST API, for example)
fails with "Data contract's data source must be a database type".
:::

## Ownership and access

A contract's **owner** is the user who created it (an API key's owner, for a key), or the user it was handed to. Only
the owner or an Admin can change, disable, retarget, evaluate on demand (**Evaluate now**) or delete it. To anyone but an
Admin, a contract that does not exist is refused (`403`) like one that is not theirs.

An Admin or a UserAdmin gives a contract a new owner with **Change owner** on its page
(`PUT /beacon/api/data-quality/contracts/{id}/owner` with `{ "userId": <user id> }`). The new owner must be an existing,
enabled user who may run saved SQL in a project that links the contract's data source; otherwise the request is refused
(`409`) with a message saying so, because every scheduled evaluation would be skipped. If someone else changed the owner,
or archived the contract, since it was read, nothing is changed and the call answers `409`; choosing the owner the
contract already has changes nothing. Contract lists and details name the owner by display name, or as "User {id}"
(`hasOwner`, `ownerDisplayName`), never by user name or external id.

A contract belongs to the projects that link its data source. It is listed, evaluated and changed only through such a
project the caller may use (see [Security model](https://moberghr.github.io/beacon/features/security-model/)); in a restricted project an Editor deletes
only contracts they own. While Beacon user management is on, a caller without a Beacon user (a host principal, or a bearer
token not bound to a user) cannot create a contract (`403`).

### Scheduled evaluations run as the owner

Right before any rule runs, Beacon checks that the owner, as they are now, may still run saved SQL in a project that
links the contract's data source. When the owner may not (disabled or archived, no longer a member of a restricted
project, or without the role), the evaluation is skipped:

- no rule runs, and no score, history or alert is written;
- the job does not fail, and a Warning is logged (event 7111 `ScheduledWorkSkipped`, ids only);
- the contract records the skip until an evaluation runs again (`ownerNotAuthorized`): its row on the Data Quality page
  shows **Owner not authorized**, its page shows a warning with **Change owner**, the overview counts such contracts
  (**Skipped (owner)**, `ownerNotAuthorizedContracts`), and the [Control Tower](https://moberghr.github.io/beacon/features/control-tower/) says how many
  contracts are not evaluated.

A contract without an owner is evaluated only while its data source is openly reachable, and never with
`Beacon:Authorization:RequireProjectMembership: true`. **Evaluate now** runs as you, the caller.

## Rule types

Each rule has a **type**, an optional **column** and a **configuration** (JSON).

| Rule type | What it checks | Example configuration |
|-----------|----------------|-----------------------|
| **Freshness** | The newest value in a timestamp column is no older than a maximum age | `{"column": "updated_at", "maxAgeMinutes": 60}` |
| **Volume** | Row count is within an expected range (`minRows`, `maxRows`, or both) | `{"minRows": 100}` |
| **Null rate** | Percentage of NULLs in a column stays at or below a threshold | `{"column": "email", "maxNullPercent": 5}` |
| **Uniqueness** | A column contains no duplicate values | `{"column": "id"}` |
| **Referential** | Every non-null value in a column exists in a reference table (no orphans) | `{"column": "user_id", "referenceTable": "users", "referenceColumn": "id"}` |
| **Range** | Numeric values stay between `min` and `max` (either bound optional) | `{"column": "age", "min": "0", "max": "150"}` |
| **Pattern** | All values match a pattern | `{"column": "email", "pattern": "^[^@]+@[^@]+$"}` |
| **Custom SQL** | Any check you can express as SQL | `{"sql": "SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS passed FROM ..."}` |

You do not need `schema` or `table` in the configuration: Beacon takes them from the contract's target table.
**Referential** rules default the reference table to the contract's schema; add `"referenceSchema": "..."` for another
schema.

**Rules stay on the contract's data source.** The contract's table, a rule's column, and a Referential rule's reference
table and column must be objects of the contract's data source as its stored metadata lists them.

- A name that differs from the metadata only in case is resolved to the metadata's spelling, both when the contract is
  saved and when a rule stored earlier is evaluated. A name that matches several names differing only in case (different
  objects on a case-sensitive server) has to be written exactly.
- Saving a contract that names anything else answers `400` ("Table … is not in the data source's metadata …",
  "Column … is not a column of …", "… matches several …"). Columns are checked where the metadata lists the table's
  columns.
- The generated SQL quotes these names as identifiers of the data source, so a rule cannot reach another schema, database
  or catalog the data source's login can see.
- The same check runs on every evaluation: a rule that names an object outside the metadata is recorded as failed and not
  run. Refresh the data source's metadata first when the table is new.

:::note[Pattern matching depends on the engine]
On PostgreSQL the pattern is a regular expression (`!~`), on MySQL it uses `NOT REGEXP`, and on SQL Server it is
evaluated with `NOT LIKE`. Use `LIKE` wildcards (`%`, `_`) for SQL Server sources, not regex.
:::

### Custom SQL rules

A Custom SQL rule runs your query and reads the first row of the result.

- The query must be a single read-only `SELECT` in the data source's SQL dialect. It is checked when the contract is saved
  and again on every evaluation, and runs through the same read-only path as MCP queries (a `READ ONLY` transaction on
  PostgreSQL and MySQL).
- Beacon caps the result at one row itself, so the query must not set its own outer `LIMIT`, `TOP` or `FETCH`.
- It may name only the data source's own database: a name qualified with another database, catalog or project is refused
  on save, and the rule is recorded as failed on evaluation.
- Only an **Admin** can create, change or delete a contract that contains a Custom SQL rule.
- Custom SQL rules are not available on host-managed data sources.

Return these columns:

| Column | Required | Meaning |
|--------|----------|---------|
| `passed` | Yes | `1` = pass, `0` = fail |
| `score` | No | Score 0–100; defaults to 100 on pass, 0 on fail |
| `actual_value` | No | Shown as the observed value in results |
| `message` | No | Shown as the result message |

`actual_value` and `message` reach the evaluation history and the contract's alerts. A PII column read into them (by a PII
name such as `email`, an alias over one such as `MAX(email) AS actual_value`, or a project's custom PII pattern) is masked
before the result is stored, following the PII detection setting of the projects that link the data source (the
strictest applies). `passed` and `score` are read as numbers.

If you keep reusable checks as saved [queries](https://moberghr.github.io/beacon/features/queries/), a Custom SQL rule folds the same logic into a quality
score.

### Approval of Custom SQL

The [approval workflow](https://moberghr.github.io/beacon/features/approvals/) is off by default; a host turns it on in code with
`options.EnableApprovalWorkflow(...)`. While it is on, its `CoversCustomSqlRules` switch (on by default) makes the SQL a
Custom SQL rule runs a reviewed change:

- **A Custom SQL rule that is new, or whose SQL or data source changes, waits for a second person's approval** and is not
  evaluated meanwhile. It is left out like a disabled rule (each evaluation logs a Warning with the contract and rule
  ids), and the contract's other rules keep running. The contract page marks a waiting rule **Awaiting approval**.
- **An evaluation that leaves rules out is partial.** It records how many enabled rules it left out
  (`awaitingApprovalRules`, `isPartial`), and the contract page marks it **Partial** with that count. A partial score
  **does not replace the table's score**: the contract's latest score and the table's trend stay as they were, so a held
  rule never improves them silently. For a contract that alerts, a scheduled partial evaluation at or above the failure
  threshold logs a Warning; one below the threshold alerts as usual, and the alert's payload carries the number of rules
  left out.
- **A contract whose every enabled rule waits is not evaluated.** **Evaluate now** answers `400` with *"Every enabled rule
  of this data contract is a Custom SQL rule waiting for approval, so it was not evaluated. Approve the change on the
  Approvals page, or enable a rule that does not wait."*; the scheduled run is skipped with a Warning. No history, score
  or alert is written.
- **The waiting rules form one change request per contract**, submitted with the save (the Admin who saved it is the
  requester). Creating a contract with Custom SQL rules answers with `awaitingApproval: true` and the message
  *"Submitted for approval: the contract's Custom SQL rules are not evaluated until a second person approves them."*
- **An Approver (or an Admin) other than the requester reviews it** on the **Approvals** page under **Custom SQL rules**,
  which shows the contract's Custom SQL rules before and after the change, and approves exactly that SQL on that data
  source; a rule changed again since is not approved (`409`). Someone without the Approver permission who may read the
  contract's data source can open the request, but cannot decide it.
- **An unchanged rule keeps its approval.** A save rewrites the rules, but a rule whose SQL and data source stay the same
  keeps its state (approved, waiting or ungoverned), so changing a schedule or a threshold submits nothing. A rule whose
  SQL changes keeps the SQL approved for it (by its name), so putting that SQL back needs no approval. A rule renamed and
  changed in the same save is a new rule. Moving the contract to another data source makes every Custom SQL rule wait
  again. Removing the waiting rules, or putting their approved SQL back, withdraws the requester's own pending request; a
  change of the waiting rules while another person's request waits is refused (`409`).
- **Concurrent saves.** A save that finds the contract gained a Custom SQL rule while it was being saved is refused
  (`409`, *"The contract's rules changed while it was being saved, and a change of its Custom SQL rules waits for
  approval: reload it and save again."*); nothing is written.
- **A rejected change** leaves its rules out of evaluations until they are changed back to approved SQL, removed, or saved
  again (which submits them again). The contract page shows the rejection with the reviewer's comment
  (`lastApprovalDecision` while no request is pending).
- **Alerts skip recipients that wait.** A recipient whose destination waits for approval (see
  [Notifications](https://moberghr.github.io/beacon/features/notifications/)) receives no alert. The evaluation records how many were skipped
  (`heldAlertRecipients`), and an alert that every recipient waits for is logged as a Warning instead of being sent.

:::caution[Upgrading]
Custom SQL rules that exist when the workflow is turned on are not reviewed: they run as before, as long as their SQL and
data source stay the same. A rule saved while the workflow is off is not reviewed later. Turning `CoversCustomSqlRules`
(or the workflow) off again lets waiting and rejected rules be evaluated as they are; startup logs their count.
:::

### Severity and weight

| Setting | Values | Effect |
|---------|--------|--------|
| **Severity** | Low, Medium, High, Critical | Multiplies the rule's influence: Low ×1, Medium ×2, High ×3, Critical ×4 |
| **Weight** | The dialog accepts 0.1–10 (default 1.0) | Fine-grained influence within the same severity |
| **Enabled** | on or off per rule | Disabled rules are skipped and do not affect the score |

## How evaluations run

1. **Scheduled.** Every enabled contract gets a recurring job on the host's scheduler, driven by its cron expression. The
   job is created when you save an enabled contract, updated when the schedule changes, and removed when you disable or
   delete the contract.
2. **On demand.** Click **Evaluate now** on the contract page (or
   `POST /beacon/api/data-quality/contracts/{id}/evaluate`).

During an evaluation, Beacon:

1. loads the contract and its **enabled** rules;
2. generates engine-specific SQL for each rule and runs it against the data source (60-second timeout per rule);
3. turns each result into pass or fail, a rule score, an actual and expected value, and a message;
4. computes the overall score, stores the evaluation with all rule results, and updates the table's latest score and
   trend;
5. sends alerts if the score is below the failure threshold (scheduled runs only; see [Alerting](#alerting)).

If one rule fails (bad configuration, missing column, timeout), it is recorded as **failed with score 0** and the other
rules still run.

A contract with no enabled rules is not evaluated: it keeps its last score, records no history and sends no alert.
**Evaluate now** reports that it has no enabled rules, and the scheduled run is skipped.

## Scoring

### Per-rule score

Most rule types score **100 on pass and 0 on fail**. Three score differently:

| Rule type | Score on failure |
|-----------|------------------|
| **Null rate** | `max(0, 100 − (actualNullPercent − maxNullPercent))`: small overruns cost little |
| **Range** | `(1 − outOfRangeRows / totalRows) × 100`: proportional to the rows that violate the range |
| **Custom SQL** | What your query returns in `score` (default 100 or 0) |

### Overall score

The overall score is a weighted average of rule scores. Each rule's effective weight is:

```text
effectiveWeight = ruleWeight × severityMultiplier
```

with Critical = 4, High = 3, Medium = 2, Low = 1. Then:

```text
overallScore = Σ(ruleScore × effectiveWeight) / Σ(effectiveWeight)
```

rounded to two decimals. Custom SQL rules waiting for approval are not part of the score.

**Example:** a passing Critical freshness rule (weight 1, score 100) and a failing Low volume rule (weight 1, score 0)
score `(100×4 + 0×1) / (4+1) = 80%`.

### Table scores and trends

After every evaluation that is not partial, the target table's **latest score** is updated (one score per data source,
schema and table). Beacon keeps the previous score and derives a trend:

| Trend | Condition |
|-------|-----------|
| **Improving** | New score more than 1 point above the previous |
| **Stable** | Within ±1 point |
| **Degrading** | New score more than 1 point below the previous |

Table scores also appear in the MCP `get_documentation` output for the table (see [MCP server](https://moberghr.github.io/beacon/features/mcp-server/)).

## Alerting

| Setting | Default | What it does |
|---------|---------|--------------|
| **Alert on failure** | on | Turns alerts for the contract on or off |
| **Failure threshold (%)** | 80 | Alerts fire when the overall score drops **below** this value |
| **Notification recipients** | none | Who is alerted (the same recipients [subscriptions](https://moberghr.github.io/beacon/features/subscriptions/) use) |

When a **scheduled** evaluation scores below the threshold, Beacon sends a notification titled
`[Data Quality] {contract name}` to every recipient. The payload has the overall score, the threshold, the target table,
pass and fail counts, and every failed rule (name, score, expected and actual value, message). For channels, see
[Notifications](https://moberghr.github.io/beacon/features/notifications/).

:::note
**Evaluate now** records the evaluation and updates the score but sends no alert. Only scheduled runs alert.
:::

## Using the UI

### Data Quality page

Open **Data Quality** in the navigation (`/beacon/data-quality`). The page shows:

- **overview cards per data source**: average score, table count, **Healthy** tables (score of at least 80),
  **Degrading** tables, **Contracts**, **Skipped (owner)** when any, and per-table scores;
- **the contracts list**, paged on the server, newest first: each contract with its target table, latest score and state,
  and **View**, **Edit** and **Delete**.

### Create a contract

1. Click **New contract**.
2. Fill in the details:

| Field | Required | Example |
|-------|----------|---------|
| **Contract name** | Yes | `Orders freshness & volume` |
| **Description** | No | |
| **Data source** | Yes | A PostgreSQL, SQL Server or MySQL data source |
| **Schema name** / **Table name** | Yes | `public` / `orders` |
| **Cron expression** | Yes | `0 */6 * * *` (every 6 hours) |
| **Failure threshold (%)** | No | `80` |
| **Enabled** | | on: the schedule is active |
| **Alert on failure** | | on: alert below the threshold |
| **Notification recipients** | | Shown while **Alert on failure** is on |

3. Add one or more rules: **Name**, **Type**, **Column (optional)**, **Severity**, **Weight** and
   **Configuration (JSON)** (the dialog shows an example for the type).
4. Click **Create**. An enabled contract is scheduled at once.

### Contract page

The contract page (`/beacon/data-quality/{id}`) shows the KPIs **Latest score**, **Rules**, **Schedule**,
**Failure threshold** and **Owner**, and three tabs:

- **Rules**: the rule set;
- **Evaluations**: history, newest first, with overall score, pass and fail counts and execution time, a **Partial**
  marker with the number of Custom SQL rules left out, and the number of alert recipients skipped;
- **Latest results**: per-rule results of the most recent evaluation (pass or fail, score, expected and actual value,
  message).

**Evaluate now**, **Edit**, **Change owner** (Admins and UserAdmins) and **Delete** sit in the header. Deleting a contract
archives it and removes its scheduled job.

:::caution
**Editing a contract replaces its whole rule set.** Stored per-rule results of the old rules are removed with the update;
evaluation summaries and their overall scores remain. Export rule-level history first if you need it.
:::

## REST API

Routes under `/beacon/api/data-quality`. A scoped API key needs the Execute scope for `POST`, `PUT` and `DELETE`.

| Method | Route | What it does |
|--------|-------|--------------|
| `GET` | `/overview` | Quality overview per data source (optional `dataSourceId`) |
| `GET` | `/contracts` | List contracts, paged: `page` (zero-based), `pageSize` (default 20, at most 200), `sort` (default `-createdTime`), optional `dataSourceId` |
| `GET` | `/contracts/{id}` | Contract detail with rules and latest score |
| `POST` | `/contracts` | Create a contract |
| `PUT` | `/contracts/{id}` | Update a contract (replaces rules and recipients) |
| `PUT` | `/contracts/{id}/owner` | Give the contract a new owner (UserAdmin or Admin), body `{ "userId": … }` |
| `DELETE` | `/contracts/{id}` | Archive a contract and remove its schedule |
| `GET` | `/contracts/{id}/evaluations` | Evaluation history (optional `take`, default 20) |
| `POST` | `/contracts/{id}/evaluate` | Run an evaluation now |

Lists and the overview cover only contracts in projects you can read.

## Troubleshooting

**A rule always fails with "Execution failed on the data source."**

The rule's SQL could not run. The server's error text is not shown or stored, because it can quote row values. Check
that the column exists and the data source connection works. Other failures name their cause:

- "Invalid rule configuration: …": the configuration JSON is incomplete for the rule type.
- "Rule type … is not supported on …": the rule type has no SQL for the data source's engine.
- "Rule SQL was rejected: …": the SQL is not a single read-only `SELECT`, or reads something a host-managed source does
  not expose.
- "Table … is not in the data source's metadata …" / "Column … is not a column of …": the contract or rule names an object
  the metadata does not list. Refresh the metadata or fix the name. "… matches several tables (columns) … that differ
  only in case": write the name exactly as the metadata spells it.
- "Custom SQL must not set its own LIMIT, TOP or FETCH; Beacon reads one row.": remove the outer row limit.
- "Rule timed out.": the query ran longer than 60 seconds.
- "Rule result did not have the expected columns or types.": a Custom SQL rule did not return `passed` (and the optional
  columns) as described above.
- "Query failed on the host database.": any failure on a host-managed data source.

**"Data contract's data source must be a database type"**

The contract targets a non-database data source (a REST API source, for example).

**"This data contract has no enabled rules, so it was not evaluated."**

Turn at least one rule's **Enabled** toggle on and save the contract.

**No alerts despite a failing score**

Alerts need **Alert on failure** on, at least one recipient, and an overall score strictly **below** the threshold. Only
scheduled runs alert; **Evaluate now** does not. A recipient whose destination waits for approval receives nothing.

**Pattern rule behaves differently across engines**

PostgreSQL and MySQL treat the pattern as a regular expression; SQL Server uses `LIKE` syntax.

**History shows fewer entries than expected**

The history route returns the 20 most recent evaluations by default; pass a larger `take`. Rule-level details of old
rules are removed when a contract is edited.