--- 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/", "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": "", "period_end": "" }, "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