What it does
The numbers founders trust most live in their own tables: accounts created, orders placed, teams that did the key action. Analytics tools approximate them; the database has them exactly. What is missing is someone who runs the query every morning and writes the result down.
This skill runs one saved SQL file with a read-only connection and posts the result as a tile with its history. You write the query once, review it once, and the agent never writes SQL of its own against production. Add a second query for a second tile.
How it works
- 1
1Save the query
Write one file per metric, for example
metrics/new_accounts.sql, returning one row per day:select created_at::date as day, count(*) as value from accounts where created_at >= current_date - 14 and created_at < current_date group by 1 order by 1. Review it once; the skill runs it as written. 2Run it read-only
PGOPTIONS="-c default_transaction_read_only=on -c statement_timeout=30000" psql "$DATABASE_URL" -X -A -t -F , -f metrics/new_accounts.sql, or the same file through a Postgres MCP server connected with a read-only role.- 3
3Refuse anything that writes
Before running, check that the file contains a single
select(orwith ... select) and noinsert,update,delete,alter,drop,truncate,grantorcopy. If it does, stop and report instead of running. - 4
4Compare
Yesterday against the day before, the same weekday last week and the 7-day average. Note days with no row as zero, not missing.
- 5
5Post the tile
The metric is the headline with the last 10 days as history, and each saved query gets its own card. The glance holds the three comparisons; the query text opens with the card.
Post it to the board
With
BRIEFKIN_TOKENset, the agent sends the result as one card that updates in place every run. Without it, you get the report in the chat.
What you get
- Yesterday's value from the saved query, with 10 days of history.
- Day before, same weekday last week and 7-day average.
- The query file it ran, so anyone can check what the number means.
Install and run it
Pick your agent, where the data comes from and how to schedule it; the commands below follow.
Install the skill
One command. It puts the SKILL.md in ~/.claude/skills and in ~/.agents/skills, which Codex, Cursor, Gemini CLI, Copilot CLI and OpenCode read too.
shell
curl -fsSL https://briefkin.dev/skills/postgresql-daily-metric-from-sql/install | shRun it
Ask Claude Code:
prompt
Run the postgresql-daily-metric-from-sql skillOr paste the prompt instead of installing:
prompt
Run the SQL in metrics/new_accounts.sql against my Postgres database with psql, using DATABASE_URL and PGOPTIONS set to default_transaction_read_only=on and a 30-second statement_timeout. First check the file is a single SELECT with no INSERT, UPDATE, DELETE, ALTER, DROP, TRUNCATE, GRANT or COPY; if not, stop and tell me. The query returns one row per day. Give me yesterday's value, the change on the day before, the same weekday last week and the 7-day average, treating missing days as zero. Do not write or run any other SQL.Put it on a schedule
Every day at 8:00, from cron on any machine where Claude Code is signed in:
crontab
0 8 * * * claude -p "Run the postgresql-daily-metric-from-sql skill"Add it with
crontab -e. A laptop asleep at that time runs it when it wakes if you use launchd or Task Scheduler instead; GitHub Actions works for the API route.Send it to your board
Connect Briefkin once with
curl -fsSL https://briefkin.dev/install | shand every run lands on your board as a stat tile. How setup works
View the SKILL.mdRaw file
SKILL.md---
name: postgresql-daily-metric-from-sql
description: "Every morning: one number from a saved SQL query against your Postgres database, such as signups or orders, with the change since the day before. Use when the user asks for this, or when it runs on a schedule."
---
# Postgres daily metrics from your own tables
Every morning: one number from a saved SQL query against your Postgres database, such as signups or orders, with the change since the day before.
The numbers founders trust most live in their own tables: accounts created, orders placed, teams that did the key action. Analytics tools approximate them; the database has them exactly. What is missing is someone who runs the query every morning and writes the result down.
This skill runs one saved SQL file with a read-only connection and posts the result as a tile with its history. You write the query once, review it once, and the agent never writes SQL of its own against production. Add a second query for a second tile.
## Two ways to get the data
Use the API or MCP route when you have an MCP server, a CLI or an API token for the app. Otherwise, if you can drive a browser the user is signed in to (Claude in Chrome, a Playwright or Chrome DevTools MCP server), use the browser route. Say which one you used.
## Route A: API or MCP server
### What you need
- A Postgres role with `SELECT` on the tables the query reads and nothing more, ideally on a read replica, and its connection string in `DATABASE_URL`.
- `psql` on the machine running the agent, or a Postgres MCP server configured with that read-only role.
If something is missing, say what and stop; do not invent numbers.
### Steps
1. **Save the query**: Write one file per metric, for example `metrics/new_accounts.sql`, returning one row per day: `select created_at::date as day, count(*) as value from accounts where created_at >= current_date - 14 and created_at < current_date group by 1 order by 1`. Review it once; the skill runs it as written.
2. **Run it read-only** (PostgreSQL): `PGOPTIONS="-c default_transaction_read_only=on -c statement_timeout=30000" psql "$DATABASE_URL" -X -A -t -F , -f metrics/new_accounts.sql`, or the same file through a Postgres MCP server connected with a read-only role.
3. **Refuse anything that writes**: Before running, check that the file contains a single `select` (or `with ... select`) and no `insert`, `update`, `delete`, `alter`, `drop`, `truncate`, `grant` or `copy`. If it does, stop and report instead of running.
4. **Compare**: Yesterday against the day before, the same weekday last week and the 7-day average. Note days with no row as zero, not missing.
5. **Post the tile**: The metric is the headline with the last 10 days as history, and each saved query gets its own card. The glance holds the three comparisons; the query text opens with the card.
## Route B: the browser
### What you need
- Signed in to the hosting console as a member of the project; on Neon, any role that can open the SQL Editor on the branch, ideally a read replica or a non-production branch.
If something is missing, say what and stop; do not invent numbers.
### Steps
Start at https://console.neon.tech/app/projects. The user is signed in already; never type passwords or codes, and ask if a sign-in page appears.
1. **Open your host's SQL page**: Postgres has no web UI of its own, so this route uses the hosting console. The steps follow Neon's SQL Editor: pick the project, then **Postgres database > SQL Editor**, and choose the branch and database. Supabase (**SQL Editor** in the project sidebar), Railway (the **Data** tab) and the AWS RDS query editor work the same way.
2. **Paste the saved query**: Open `metrics/new_accounts.sql` from your repository and paste it unchanged: `select created_at::date as day, count(*) as value from accounts where created_at >= current_date - 14 and created_at < current_date group by 1 order by 1`. Read it first: it must be a single `select` with no `insert`, `update`, `delete`, `alter`, `drop`, `truncate`, `grant` or `copy`.
3. **Run it and read the rows**: Click **Run**. The results show one row per day; a missing day means zero. If you need the numbers elsewhere, the download buttons under the results export CSV or JSON.
4. **Compare and report**: Yesterday against the day before, the same weekday last week and the 7-day average, and name the query file the number came from.
Note: A console SQL editor runs as your console role, which can usually write, so the protection is the query text and your reading of it. The Neon editor keeps a history of what ran, which helps when checking later. If your database is self-hosted with no console, there is no browser route; use psql.
## The report
- **Yesterday's value** from the saved query, with 10 days of history.
- **Day before**, **same weekday last week** and **7-day average**.
- The query file it ran, so anyone can check what the number means.
## Post it to Briefkin
When `BRIEFKIN_TOKEN` is set, or `~/.config/briefkin/env` exists (the installer from https://briefkin.dev/docs writes it), also send the result to the user's Briefkin board as one card. Keep the `item_key` the same on every run so the card updates in place, use a new `event_id` per run, and put today's real numbers in place of the example ones. If the request fails, say so once and carry on.
```bash
[ -n "${BRIEFKIN_TOKEN:-}" ] || . ~/.config/briefkin/env
curl -sS -X POST "${BRIEFKIN_URL:-https://briefkin.dev}/v1/events" \
-H "authorization: Bearer $BRIEFKIN_TOKEN" \
-H "content-type: application/json" \
-d @- <<'JSON'
{
"v": 1,
"event_id": "postgresql-daily-metric-from-sql/<date of this run, e.g. 2026-10-16>",
"item_key": "postgresql-daily-metric-from-sql",
"project": "acme-web",
"source": "Postgres daily metrics from your own tables skill",
"kind": "metric",
"title": "86 new accounts yesterday, up 12% on the day before",
"metric": {
"label": "New accounts",
"value": 86,
"better": "higher",
"period_start": "<start of the period, ISO 8601>",
"period_end": "<end of the period, ISO 8601>"
},
"glance": {
"type": "Grid",
"props": {
"columns": 3
},
"children": [
{
"type": "Stat",
"props": {
"label": "Day before",
"value": 77
}
},
{
"type": "Stat",
"props": {
"label": "Same day last week",
"value": 75
}
},
{
"type": "Stat",
"props": {
"label": "7-day average",
"value": 72
}
}
]
},
"widget": [
{
"type": "KeyValue",
"props": {
"items": [
{
"key": "Query",
"value": "metrics/new_accounts.sql"
},
{
"key": "Ran as",
"value": "metrics_reader (read-only, replica)"
},
{
"key": "Run time",
"value": "0.4 s"
}
]
}
},
{
"type": "Text",
"props": {
"text": "select created_at::date as day, count(*) as value from accounts where created_at >= current_date - 14 and created_at < current_date group by 1 order by 1",
"variant": "mono"
}
}
],
"size": "md"
}
JSON
```
Widgets come from the Briefkin catalog (https://briefkin.dev/v1/catalog). Without a token, just give the user the report.
Set `period_start` and `period_end` to the window the number covers: the tile names it from them ("Last 7 days", "Today", "This month"). For a count right now (open items, a balance), send `"period_label": ""` so no period shows; for another name, send it as `period_label` (at most 24 characters).
## Run it on a schedule
Suggested: Every day at 8:00 (`0 8 * * *`). From cron, with the agent signed in on that machine:
```bash
0 8 * * * claude -p "Run the postgresql-daily-metric-from-sql skill"
# Codex: codex exec, Gemini CLI: gemini -p, Copilot CLI: copilot -p, OpenCode: opencode run
```
Or use the agent's own scheduler (Claude Code routines, Codex automations, Claude or ChatGPT scheduled tasks): https://briefkin.dev/skills/postgresql-daily-metric-from-sql#schedule
Source: https://briefkin.dev/skills/postgresql-daily-metric-from-sql
What your agent needs
A Postgres role with
SELECTon the tables the query reads and nothing more, ideally on a read replica, and its connection string inDATABASE_URL.psqlon the machine running the agent, or a Postgres MCP server configured with that read-only role.
Connecting PostgreSQL
- MCP server. The PostgreSQL project doesn't publish an MCP server, and the reference Postgres server from the MCP project has been archived. A common community choice is Postgres MCP Pro (
postgres-mcp), whose--access-mode=restrictedruns every statement in a read-only transaction with a time limit:claude mcp add --env DATABASE_URI=postgresql://report_reader:<password>@db.example.com:5432/app --transport stdio postgres -- uvx postgres-mcp --access-mode=restricted. In Codex and Cursor, put the same command, arguments andDATABASE_URIin the server config. Managed hosts such as Supabase and Neon run their own MCP servers. psqlwith a connection string. Claude Code and Codex can runpsql "$DATABASE_URL" -X -A -F ',' -c "SELECT ..."directly; it needs only the client and the URL.- A read-only role, used by both:
CREATE ROLE report_reader LOGIN PASSWORD '...',GRANT CONNECT ON DATABASE app TO report_reader, thenGRANT pg_read_all_data, pg_monitor TO report_readeron Postgres 14 and later (on older versions, grantUSAGEon the schema andSELECTon its tables). AddALTER ROLE report_reader SET default_transaction_read_only = onandALTER ROLE report_reader SET statement_timeout = '30s'.
The role is the safeguard that holds whichever tool connects: with only read privileges it can't write, alter or drop anything, pg_monitor lets it see the statistics views, and the timeout stops a runaway query. Point it at a read replica if you have one.
Questions
How do I make the role read-only for real?
Create a role that can only read: create role metrics_reader login password '...', then grant connect on the database, grant usage on the schema and grant select on the tables it needs, and alter role metrics_reader set default_transaction_read_only = on. A read replica, where you have one, keeps the query off the primary.
Can the agent write the query for me?
It can draft one in a normal session, where you read and approve it before saving the file. The scheduled run deliberately executes only the saved file, so the number means the same thing every day and no new SQL ever runs unattended against production.
Do I need Briefkin to use the Postgres daily metrics from your own tables skill?
No. The skill runs with your own agent and gives you the report in the chat. With a Briefkin token set, each run also posts it to your board as a stat tile, so you can glance at it instead of asking again.
Which agents can run it?
Claude Code, Codex, Cursor, Gemini CLI, GitHub Copilot CLI, OpenCode, Windsurf and Amp all read SKILL.md files. The install command puts it in ~/.claude/skills and ~/.agents/skills, where they look. ChatGPT, the Claude app and Grok take the prompt instead. Pick your agent under "Install and run it" and the commands change to match.
Can it work without an API or MCP server, from my browser?
Yes. Switch the data source to "Your browser" and the steps change to the PostgreSQL pages themselves: the agent opens console.neon.tech/app/projects in a browser you are signed in to and reads the numbers off the page. Claude Code, Codex, Cursor, Claude app and ChatGPT can drive a browser out of the box; Gemini CLI, Copilot CLI and OpenCode need a Playwright or Chrome DevTools MCP server first. It is slower than the API and needs you signed in, so it suits a report you run by hand or from a scheduler on your own computer.