> ## Documentation Index
> Fetch the complete documentation index at: https://docs.coval.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Custom SQL Metrics

> Query structured conversation data, return timestamped numeric values, and aggregate them into a metric.

**Custom SQL** computes a numeric metric from the structured data for one conversation at a time. Use it when you can express the check precisely: count tool calls, measure message durations, or calculate values from event sequences. The query produces timestamped values, which Coval combines into one score and displays on the timeline.

<Note>
  Use the SQL editor's current schema when authoring a query. The examples below assume the tables and columns are present in your workspace's schema.
</Note>

## Create and inspect the schema

1. Open **Metrics > New Metric** and choose **Custom SQL** from the available types.
2. Give the metric a descriptive name.
3. Click **View schema** in the SQL editor. Inspect the current tables, column types, enum values, and sample data before writing the query.
4. Choose the aggregation and output unit, test the draft, and save the metric.

The schema can include transcript messages, tool calls, events, audio segments, and voice activity. Data availability depends on what was captured for the conversation. A table appearing in the schema does not guarantee every conversation has rows in it. Use the current schema panel as the reference for table and column names.

SQL runs against the selected conversation's data. You do not need to add organization or conversation identifiers to the query, and this workflow does not provide a cross-conversation reporting query.

## Return timestamped values

Return only these columns, using exact aliases:

| Column | Required | Meaning |
| - | - | - |
| `value` | Yes | Numeric measurement for this row. |
| `start_offset_milliseconds` | Yes | Start relative to the beginning of the conversation, in milliseconds. |
| `end_offset_milliseconds` | No | End relative to the beginning of the conversation, in milliseconds; defaults to the start when omitted. |

Extra columns, duplicate column names, missing required columns, and nonnumeric values or offsets are rejected. Use aliases for computed expressions and avoid `SELECT *`. Offsets determine where subvalues appear on the timeline; they do not set the unit of `value`.

For example, measure each transcript message's duration in seconds:

```sql theme={null}
SELECT
  start_offset_milliseconds,
  end_offset_milliseconds,
  (end_offset_milliseconds - start_offset_milliseconds) / 1000.0 AS value
FROM transcript_messages
WHERE end_offset_milliseconds >= start_offset_milliseconds
ORDER BY start_offset_milliseconds
```

Choose **Average** and the **seconds** output unit to report mean message duration. Use **Maximum** for the longest message. The query performs the milliseconds-to-seconds conversion; selecting a unit labels the output and does not rescale it.

To count a specific tool's invocations, return one non-null value per invocation and choose **Count** with a **count** unit:

```sql theme={null}
SELECT
  start_offset_milliseconds,
  end_offset_milliseconds,
  1 AS value
FROM tool_calls
WHERE tool_name = 'lookup_policy'
ORDER BY start_offset_milliseconds
```

Check the tool name and frame coverage against a known conversation before interpreting a zero as “the agent did not call the tool.” Missing captured evidence can also yield no rows.

## Choose an aggregation

| Aggregation | Result |
| - | - |
| **Average** | Mean of non-null values; default when no method is specified. |
| **Sum** | Total of non-null values. |
| **Minimum** | Smallest non-null value. |
| **Maximum** | Largest non-null value. |
| **Count** | Number of rows with a non-null `value`. |

Rows with `value = NULL` contribute neither a timeline subvalue nor an aggregate value. If the query returns no rows, **Count** reports 0; the other methods are skipped as not applicable. If it returns rows but every value is null, the other methods report a query error. Use `COALESCE` only when substituting a number reflects the intended measurement.

Choose a supported output unit such as seconds, milliseconds, count, ratio, percent, tokens, or unitless. Keep the query's arithmetic consistent with that unit. For example, a percentage query should calculate a percentage, rather than return a 0–1 ratio labeled as percent.

## Test before saving

Click **Test Metric** in the SQL editor to evaluate the draft query, aggregation, and unit on up to 10 recent eligible simulations in the current workspace. Inspect each result's row count, aggregate value, timeline subvalues, and errors. This draft test does not require saving the metric first.

Each tested conversation can return at most 1,000 rows. A larger result is rejected rather than silently aggregated as a partial sample. Filter, aggregate, or carefully limit the query, and check that any limit still answers your measurement question. A bounded interactive request can time out; simplify the query or retry when the data is ready.

Test a conversation with matching data, one without matches, and one with a known expected value. Where available, **Ask Sofia** can help draft a query from the schema and your measurement goal. Review the generated SQL and its test results before saving it.

After saving, select the metric for an evaluation with the necessary structured data. **Test Metric** on a saved metric is separate from the SQL editor's bounded draft test.

## Query restrictions and missing data

* Use read-only SQL beginning with `SELECT` or `WITH`; data-changing statements are unsupported.
* Query the tables and columns shown in the schema panel. This is a conversation-data measurement surface, not general database access.
* Keep the result to the timestamp/value contract above. Compute supporting columns inside a subquery when needed, then return only the supported aliases.
* A query error, unavailable data, and a valid empty result have different meanings. Inspect the test message before changing the query or treating the score as zero.

For measurements already available in OTel spans, [Custom Trace](/concepts/metrics/types/trace#custom-trace) can extract numeric attributes or span durations without SQL. Use an [LLM Judge](/concepts/metrics/types/llm-judge) when the criterion requires interpretation rather than an exact calculation.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.