haacked/dotfiles/metabase-prod-query

Metabase Prod Query

Run SQL against PostHog production Metabase via hogli metabase:*. Production Metabase sits behind ALB Cognito OAuth, so this is the only path that works (API keys won't pass the ALB).

The four underlying commands:

Non-negotiables

• This hits prod. Run a bounded read-only query without asking, and show its SQL with the results. Ask first when a query matches a trigger in step 5.
• Never run `metabase:cookie`. That command prints credentials to the terminal/logs. The query command reads the cookie internally.
• Use `--save PATH` for `query`. Results can be large; dumping rows into the conversation transcript bloats context and risks leaking customer data. Save to a file, then read just what you need.
• Refresh DB IDs every session. They change when Metabase metadata is rebuilt. Run metabase:databases once at the start.
• Never use this for local dev. Local dev uses the posthog-db MCP. This skill is prod-only.

Steps

1. Confirm scope

Parse args. Determine:

• Region(s): us, eu, or both. If ambiguous, ask. (both means run sequentially per region.)
• Question: what is the user trying to learn? If they passed raw SQL, use it. If they passed a question, draft SQL in step 3.

If region or intent is unclear, ask the user before doing anything.

2. Verify session

For each region in scope, check the cookie cache:

test -f ~/.config/posthog/metabase/cookie-<region> && echo cached || echo missing

If missing, OR if a later command returns 301/302/401, run:

hogli metabase:login --region <region>

This opens the user's default browser. Tell the user this will happen before running it.

3. Refresh database IDs

Once per session per region:

hogli metabase:databases --region <region> --format json

Save the output. Note the relevant database IDs (typically posthog Postgres and replica ClickHouse, but verify by name — IDs are not stable across rebuilds). Pick the ID that matches the data the user is asking about (Postgres for app/control-plane data; ClickHouse for events/analytics).

4. Compose SQL

Write the query to a temp file under /tmp/metabase-<short-slug>-<region>.sql. Keep queries:

• Bounded: include LIMIT (e.g., LIMIT 1000) unless the user explicitly wants a full export.
• Time-bounded: include WHERE timestamp >= now() - interval ... (or equivalent) on event tables.
• Read-only: this is SELECT-only. Never write DDL/DML.

For ClickHouse-flavored questions, use ClickHouse SQL (no interval '1 day' Postgres syntax — use INTERVAL 1 DAY). For Postgres, use Postgres syntax.

5. Decide whether to ask first

Ask for approval before running when any of these holds:

• The query has no LIMIT because the user wants a full export.
• A ClickHouse query reads events, flag_evaluations, session_replay_events, or another table of similar size, and has no time filter or a window longer than 30 days.
• The query needs a --timeout above the 120-second default.
• The user asked to review SQL before it runs.

If none holds, go to step 6.

To ask, display to the user, exactly:

Region:      <us|eu|both>
Database:    <name> (id <N>)
Save to:     /tmp/metabase-<slug>-<region>.<tsv|json>
 
SQL:
  <the query, indented 2 spaces>

Then ask: "Run this query against prod? Reply yes to confirm, or describe changes."

Do not proceed until the user replies yes (or equivalent confirmation). If the user requests changes, update and re-show.

6. Run the query

hogli metabase:query \
  --region <region> \
  --database-id <id> \
  --file /tmp/metabase-<slug>-<region>.sql \
  --save /tmp/metabase-<slug>-<region>.tsv

For --region both, run once per region with separate --save paths.

Default format is TSV. Pass --format json only when the user needs structured output.

7. Read and summarize results

First, check the file size and shape:

wc -l <file> && head -5 <file>

If the file has 50 rows or fewer, read it in full with Read. Otherwise, work from targeted extracts (head/awk/cut) to get counts, top-N rows, or specific columns rather than loading the whole file into context.

Report to the user:

• The region, database, and SQL that ran, in the step 5 format
• Row count
• Top N rows (or summary stats — sums, distributions — depending on the question)
• Anomalies worth flagging

8. Clean up (optional)

If the saved files contain potentially sensitive data and the user is done, offer to delete them:

rm /tmp/metabase-<slug>-*.{sql,tsv,json}

Error handling

Notes

• The cookie cache file is mode 0600 and lives at ~/.config/posthog/metabase/cookie-{region}. Do not cat it, copy it elsewhere, or print its contents.
• The two production hosts are metabase.prod-us.posthog.dev and metabase.prod-eu.posthog.dev. You don't need to use them directly — hogli handles routing.
• For long-running queries, pass --timeout <seconds> (default 120s).