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.
3. A date_field to window on, and start_date/end_date to pick the window.

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
  }'

[
  {
    "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:

Window by date (single metric)

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.

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)

Uncapped (get-multiple-metrics)

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.

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:

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 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.