Track yesterday's Supabase signups from auth.users without opening the dashboard

SKILL.md---
name: supabase-daily-signups
description: "Every morning: how many people signed up yesterday in your Supabase project, confirmed and by provider, against the 7-day average. Use when the user asks for this, or when it runs on a schedule."
---

# Supabase daily signups

Every morning: how many people signed up yesterday in your Supabase project, confirmed and by provider, against the 7-day average.

If your app runs on Supabase Auth, every signup is a row in `auth.users`, and the most honest growth number you have is a count of yesterday's rows. Getting it usually means opening the SQL editor, finding last week's query and remembering to exclude anonymous users.

The agent runs that query each morning through the Supabase MCP server in read-only mode, so it cannot change a thing, and posts yesterday's signups with the 7-day average and the same day last week. Founders get a number that does not depend on analytics scripts or ad blockers.

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

- The Supabase MCP server scoped to one project in read-only mode: `https://mcp.supabase.com/mcp?project_ref=<ref>&read_only=true&features=database`. Read-only mode runs queries as a read-only Postgres user.
- Or a database connection (`DATABASE_URL`) for a role that can read `auth.users`, used through `psql` with `default_transaction_read_only` on.

If something is missing, say what and stop; do not invent numbers.

### Steps

1. **Count signups per day** (Supabase): Through the MCP server's `execute_sql`: `select (created_at at time zone 'UTC')::date as day, count(*) as signups, count(*) filter (where email_confirmed_at is not null) as confirmed from auth.users where created_at >= now() - interval '15 days' and coalesce(is_anonymous, false) = false group by 1 order by 1`. Use your own time zone in place of `UTC`, and drop today's partial day.
2. **Split yesterday by provider** (Supabase): For yesterday only, group by `raw_app_meta_data->>'provider'` (email, google, github and so on) to see which sign-in method people chose.
3. **Compare**: Yesterday against the same weekday last week and against the average of the last 7 days. Flag a day 40% or more below the weekday's usual, which more often means a broken sign-up form than a quiet day.
4. **Post the tile**: New signups is the headline with 14 days of history. Confirmed signups, the 7-day average and the same day last week go in the glance; the provider split opens with the card.

## Route B: the browser

### What you need

- Signed in to the Supabase dashboard as an organization member with at least Read-only access to the project; the SQL Editor needs Developer or above to run queries.

If something is missing, say what and stop; do not invent numbers.

### Steps

Start at https://supabase.com/dashboard/projects. The user is signed in already; never type passwords or codes, and ask if a sign-in page appears.

1. **Open the project's SQL Editor**: Pick the project, then click **SQL Editor** in the left sidebar and open a new query. The dashboard runs as your account, so use the browser route only with a project where you accept that; the query below only reads.
2. **Run the daily count**: Paste `select (created_at at time zone 'UTC')::date as day, count(*) as signups, count(*) filter (where email_confirmed_at is not null) as confirmed from auth.users where created_at >= now() - interval '15 days' and coalesce(is_anonymous, false) = false group by 1 order by 1` with your time zone in place of `UTC`, and click **Run**. Ignore today's partial row.
3. **Split yesterday by provider**: Run a second query over the same table for yesterday only, grouped by `raw_app_meta_data->>'provider'`. As a cross-check without SQL, **Authentication > Users** lists users newest first with a **Provider** column, but it does not total by day.
4. **Compare and report**: Yesterday against the same weekday last week and the average of the last 7 days. Flag a day 40% or more below that weekday's usual as a possible broken sign-up flow.

Note: The SQL Editor runs with your dashboard role, not a read-only database user, so the safety comes from the query text alone. Read the two statements before clicking Run. The Users page in Authentication paginates and cannot group by day, so it is only a spot check.

## The report

- **New signups** yesterday, with 14 days of history.
- **Confirmed** signups (email confirmed) out of those.
- **Same day last week** and the **7-day average**.
- **By provider**: email, Google, GitHub and others.
- A warning when the drop looks like a broken sign-up flow.

## 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": "supabase-daily-signups/<date of this run, e.g. 2026-10-16>",
  "item_key": "supabase-daily-signups",
  "project": "acme-web",
  "source": "Supabase daily signups skill",
  "kind": "metric",
  "title": "142 signups yesterday, 17% above last Thursday",
  "metric": {
    "label": "New signups",
    "value": 142,
    "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": "Confirmed email",
          "value": 118
        }
      },
      {
        "type": "Stat",
        "props": {
          "label": "7-day average",
          "value": 113
        }
      },
      {
        "type": "Stat",
        "props": {
          "label": "Same day last week",
          "value": 121
        }
      }
    ]
  },
  "widget": {
    "type": "Bars",
    "props": {
      "items": [
        {
          "label": "Email",
          "value": 81
        },
        {
          "label": "Google",
          "value": 52
        },
        {
          "label": "GitHub",
          "value": 9
        }
      ],
      "unit": "signups"
    }
  },
  "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 supabase-daily-signups 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/supabase-daily-signups#schedule

Source: https://briefkin.dev/skills/supabase-daily-signups