> ## Documentation Index
> Fetch the complete documentation index at: https://bilanc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# Exporting Row-Level Data
> Pull individual pull requests, reviews, issues, CI runs, AI sessions and prompts out of Bilanc using the metrics endpoint
## The pattern
The metrics endpoint aggregates a table. If you group by the table's **primary key**, every group contains exactly one record, so the "aggregate" is the record itself. Add the other columns you want to `group_by` and you have a row-level export with the same row-level security the dashboard applies.
This is exactly how the Bilanc dashboard renders its own tables, PR drill-downs and the posthook session viewer, so anything you can see in the app you can pull this way.
Three ingredients:
1. **A count metric for the table** (see the table below). Counts don't need an `aggregation`.
2. **The primary key plus the columns you want** in `group_by`. Any column of the source table is allowed; see [Tables and their columns](/api-reference/endpoint/metrics-overview#tables-and-their-columns).
3. **A `date_field`** to window on, and `start_date`/`end_date` to pick the window.
```bash theme={null}
curl -X POST 'https://api.bilanc.co/metrics/pull-requests-count' \
-H 'Authorization: YOUR_API_KEY' \
-H 'Content-Type: application/json' \
-d '{
"filters": {
"start_date": "2026-09-01",
"end_date": "2026-09-07",
"pr_states": ["merged"]
},
"group_by": ["pr_id", "pr_number", "pr_title", "pr_url", "repository", "name", "pr_created_at", "pr_merged_at", "additions", "deletions", "output_estimate", "ai_pct_of_code"],
"date_field": "pr_merged_at",
"order_by": "pr_merged_at",
"order_direction": "DESC",
"limit": 200
}'
```
```json theme={null}
[
{
"date_level_not_set": true,
"pr_id": "github:acme/api:4812",
"pr_number": "4812",
"pr_title": "Add retry to webhook dispatcher",
"pr_url": "https://github.com/acme/api/pull/4812",
"repository": "acme/api",
"name": "Jordan Lee",
"pr_created_at": "2026-09-05T09:14:22",
"pr_merged_at": "2026-09-06T15:02:10",
"additions": 184,
"deletions": 37,
"output_estimate": 61,
"ai_pct_of_code": 0.42,
"pr_count": 1
}
]
```
Every row carries the count column (`pr_count: 1` here) and, because no `date_level` was given, `date_level_not_set: true`. Both can be dropped by your pipeline.
## Which metric and key to use per table
| Table | Count metric | Primary key for `group_by` | Recommended `date_field` |
| --------------------------- | --------------------------------- | ---------------------------------------------- | --------------------------------- |
| pull\_requests | `pull-requests-count` | `pr_id` | `pr_merged_at` or `pr_created_at` |
| pull\_request\_reviews | `reviews-count` | `pr_review_id` | `reviewed_at` |
| pull\_request\_comments | `comments-count` | `comment_id` | `commented_at` |
| pull\_request\_commits | `commits-count` | `commit_id` | `committed_at` |
| pull\_request\_events | `pull-request-events-count` | `pr_id`, `event_type`, `event_at`, `event_url` | `event_at` |
| issues | `issues-count` | `issue_id` | `created_at`, `completed_at` |
| issue\_comments | `issue-comments-count` | `issue_comment_id` | `created_at` |
| release\_details | `releases-count` | `release_id` | `release_date` |
| workflow\_runs | `workflow-runs-count` | `workflow_run_id` | `run_created_at` |
| ai\_copilot | `adopted-users` | `merged_user_id`, `source`, `date` | `date` |
| user\_metrics | `users-count` | `merged_user_id` | none (no date window) |
| posthook\_sessions | `posthook-sessions-count` | `session_id` | `date` |
| posthook\_session\_messages | `posthook-session-message-counts` | `message_id` | `date` |
| posthook\_session\_files | `posthook-session-file-counts` | `session_id`, `file_path` | `date` |
| posthook\_session\_commits | `posthook-session-commit-counts` | `session_id`, `commit_id` | `date` |
| survey\_responses | `survey-responses-count` | `survey_response_id` | `final_response_submitted_at` |
| survey\_recipients | `survey-recipients-count` | `survey_recipient_id` | `created_at` |
Some count metrics carry a definition. `posthook-sessions-count` only counts sessions that generated or committed AI code, so sessions with neither show `posthook_sessions_count: 0` but are still returned. `reviews-count` counts distinct PRs, which is 1 per review row anyway. `adopted-users` counts active users, so inactive seat-days show `0`. Set `metric_min_value: 1` to keep only rows the dashboard would count, or leave it at `0` to export everything.
## Paging through large tables
The single-metric endpoint caps `limit` at **200 rows** and has no offset parameter. Two ways to get everything:
Shrink the window until each one returns fewer than 200 rows. Daily windows are usually enough; for very busy days add a `member` or `repositories` filter to split further.
```python theme={null}
import requests
from datetime import date, timedelta
API = "https://api.bilanc.co/metrics"
HEADERS = {"Authorization": "YOUR_API_KEY", "Content-Type": "application/json"}
def export_day(day: date) -> list[dict]:
body = {
"filters": {"start_date": day.isoformat(), "end_date": day.isoformat()},
"group_by": ["session_id", "engineer_email", "agent_slug", "model_slug",
"repo_name", "branch", "started_at", "ended_at",
"active_minutes", "total_tokens", "generated_lines",
"committed_lines", "files_edited", "commits_contributed"],
"date_field": "date",
"order_by": "started_at",
"order_direction": "ASC",
"limit": 200,
}
r = requests.post(f"{API}/posthook-sessions-count", json=body, headers=HEADERS)
r.raise_for_status()
rows = r.json()
if len(rows) == 200:
raise RuntimeError(f"{day}: hit the 200-row cap, split by member")
return rows
start, end = date(2026, 9, 1), date(2026, 9, 7)
sessions = []
d = start
while d <= end:
sessions.extend(export_day(d))
d += timedelta(days=1)
```
`get-multiple-metrics` has no row limit. Request a single metric through it and you get the whole window in one call. Keep windows to a month or so; the query runs synchronously.
```bash theme={null}
curl -X POST 'https://api.bilanc.co/metrics/get-multiple-metrics' \
-H 'Authorization: YOUR_API_KEY' \
-H 'Content-Type: application/json' \
-d '{
"filters": {"start_date": "2026-08-01", "end_date": "2026-08-31"},
"metrics": ["posthook-sessions-count"],
"date_fields": {"posthook-sessions-count": "date"},
"group_by": ["session_id", "engineer_email", "agent_slug", "model_slug", "repo_name", "started_at", "ended_at", "total_tokens", "generated_lines", "committed_lines"]
}'
```
Note the per-metric `date_fields` map instead of `date_field`, and that `order_by`/`limit` are not honoured here. Rows come back sorted by date bucket.
## Child records: fetch by parent id
Detail tables (posthook prompts, files and commits) are usually pulled per parent. Filter on the parent key and drop the date window down to the parent's date range:
```bash theme={null}
curl -X POST 'https://api.bilanc.co/metrics/posthook-session-message-counts' \
-H 'Authorization: YOUR_API_KEY' \
-H 'Content-Type: application/json' \
-d '{
"filters": {
"start_date": "2026-09-03",
"session_id": "7f0c3b8e-2d1a-4b6f-9e21-0a5c6d4e8b90"
},
"group_by": ["message_seq", "message_ts", "role", "source", "message_text", "tool_calls", "turn_minutes"],
"date_field": "date",
"order_by": "message_seq",
"order_direction": "ASC"
}'
```
For PR-scoped tables use `pr_ids`; for issues use `issue_ids`; for CI use `workflow_run_ids`. Full worked examples for the posthook tables are on the [Posthook Metrics](/api-reference/endpoint/posthook-metrics) page.
## Incremental sync
The tables have no `updated_at` column exposed for change tracking, so the reliable approach is to **re-pull a trailing window** on each run (for example the last 3 days) and upsert on the primary key. Late-arriving data is common: PRs get merged days after creation, posthook commits are attributed after the session ends, and CI runs complete after they start. Choose the `date_field` that matches when your downstream cares about the record (e.g. `pr_merged_at` for merged PRs, `date` for sessions) and re-pull generously.
Results are cached for five minutes per unique request body, and the underlying tables refresh hourly, so polling more often than hourly returns the same data.
## Things to know
* **Row-level security applies.** A key created by an Engineer exports only that engineer's rows. Use an Owner's key for an org-wide export.
* **`team_id` / `team_name` / `department` fan out.** A person in two teams produces two rows for the same record. Group by `team_names` (the array) instead if you need one row per record with team context.
* **Durations are seconds and rates are 0–1.** Interval columns such as `coding_time` come back as ISO-style strings when placed in `group_by`; the metric versions (`cycle-time` etc.) return seconds.
* **Text columns are safe to request.** `pr_body`, `comment`, `message_text` and `commit_message` are returned in full.
* **Invalid `group_by` fields return a 400 that lists every valid column** for the metric's table. Use it as a live schema lookup.