Querying your data (SQL)

Once data is in an artifact source, queries turn it into the numbers behind your dashboards and role metrics. Each source behaves like a table you can select from — named by the source, with the columns you defined.

A safe subset of SQL

Queries are read-only SQL — a deliberately limited subset of PostgreSQL:

• SELECT only — no inserting, updating, or deleting data.
• A whitelist of functions and casts — the common ones for reporting (aggregates, dates, string and math functions) are available.
• Org-scoped automatically — a query only ever sees your own organization’s data, and runs under a short time limit so a heavy query can’t slow things down.

In short: it’s safe to let anyone explore data with a query.

Variables

A query can take variables — :name placeholders you fill in at run time — so one query answers many questions:

SELECT created_at::date AS date, count(*) AS prs
FROM github_prs
WHERE created_at > :start_date::timestamptz
GROUP BY 1
ORDER BY 1

• Dashboards supply variables through interactive controls (a date picker, a team selector, and so on) — see Dashboards.
• Role metrics must include a :staff_id variable; Admire runs the query per person, filling in each staff member’s ID — see Role metrics.

Examples

A grouped count for a dashboard chart:

SELECT state, count(*) AS total
FROM github_prs
GROUP BY state

A per-person time series for a role metric (date + value, scoped by :staff_id):

SELECT closed_at::date AS date, count(*) AS tickets_closed
FROM support_tickets
WHERE assignee_id = :staff_id
GROUP BY 1
ORDER BY 1

Plain-English vs. SQL

You don’t need to know SQL. In English mode, describe what you want and Admire drafts the query for you — it knows your sources’ tables and columns, so the result usually just works, and you can refine it in plain English. Prefer to write it yourself? Switch to SQL mode and author the query directly. You can move between the two at any time.