# Blueprints

A blueprint is the instruction that tells Plock how to pull consumption data for a product from your connected data source. Every usage-based product has one blueprint — it defines the query to run, how to filter it to the right customer and date range, and how to aggregate the result into a billable number.

## Blueprint types

Three types are supported, matching your data source:

* **SQL**: A `SELECT` query against your database (MySQL, PostgreSQL, SQL Server, Snowflake, etc.)
* **JSON**: Extracts a value from a JSON response, used with REST API connectors
* **MongoDB**: A MongoDB aggregation pipeline command

## Required output format

Your query must return a single aggregate value aliased as `value`. Plock reads this number and applies the blueprint's aggregation type to it.

```sql
SELECT COUNT(*) AS value
FROM api_requests
WHERE
  account_id = {customer_id}
  AND created_at BETWEEN {start} AND {end}
```

## Aggregation type

Plock aggregates the value returned by the blueprint for each billing period. Four options are available:

| Type        | When to use                                                                    |
| ----------- | ------------------------------------------------------------------------------ |
| **Sum**     | Total of all events in the period — most common for API calls, storage, events |
| **Max**     | Highest value seen — useful for peak seat counts                               |
| **Min**     | Lowest value seen                                                              |
| **Average** | Mean across the period                                                         |

## Macros

Macros are placeholders in your query that Plock replaces with the correct values before running it. This is how the query is scoped to the right customer and billing period.

| Macro           | Replaced with                                  |
| --------------- | ---------------------------------------------- |
| `{start}`       | Start of the billing period                    |
| `{end}`         | End of the billing period                      |
| `{customer_id}` | The account's primary key value in your system |

The date format for `{start}` and `{end}` is configured in **Settings → Date macros**. Supported formats include `Y-m-d`, `Y-m-d H:i:s`, ISO 8601, Unix timestamp (seconds), and Unix timestamp (milliseconds).

{% hint style="info" %}
**Important:** If a macro is referenced in the query but its value is empty for a given account, Plock skips that account's billing run rather than execute the query with a blank filter — preventing accidental full-table queries.
{% endhint %}

You can also define custom account-level macros in Settings to reference per-customer metadata fields in your queries.

### Advanced macro functions

For cases where you need to filter against all values used in a subscription — for example, to exclude certain product SKUs — you can use the `{macro(unique)}` syntax. This collects all distinct values for a given plan-level macro across a subscription's current items and inserts them as a list.

Example — exclude specific product IDs from a usage query:

```sql
SELECT SUM(qty) AS value
FROM events
WHERE customer_id = {customer_id}
  AND product_id NOT IN {product_id(unique)}
  AND created_at BETWEEN {start} AND {end}
```

## Testing a blueprint

Plock can run a test of your blueprint before it is used in a live billing run. The test executes the query against a real account using yesterday as the date range and checks that the result contains a valid `value` field.

Test results are shown immediately in the UI. If the test fails, the error message from the connector is displayed so you can adjust the query. No usage data is stored from a test run.

## Blueprint logs

Every time a blueprint runs — during a billing cycle, a snapshot, or a test — Plock records the outcome. You can view the log history for a blueprint to see past runs, whether they succeeded, and any error messages. This is useful for diagnosing why usage data is missing or showing unexpected values.