hamster-posthog-user-audit
When the question is "what did this one user do, and were the events populated correctly?", use this skill. For dashboard-level analysis (multiple users, aggregates), use hamster-posthog-dashboards instead.
When to invoke
- "Review LLM events for
<email>in<env>" - "Tell me the story of what I did" / "what events did I fire?"
- "Audit
<user>'s events" - "Verify
$ai_generationattribution for<user>over<window>" - "I'm about to do X — watch my events and tell me if they're right"
- "Did
<EventName>fire when I clicked<Y>?"
When NOT to invoke
- Question is about a dashboard or aggregate metric →
hamster-posthog-dashboards. - User wants to add event tracking →
hamster-analytics. - User wants raw event data dumped to a file with no analysis → call
posthog_client.fetch_events()directly.
Two modes
The skill operates in one of two modes. Confirm the mode with the user if it's ambiguous.
Guided mode
Use when the user wants a controlled experiment — they will perform a specific sequence of actions and want you to verify that the right events fire with the right properties.
- Define the action plan. Either accept the user's plan or propose one (e.g. "1. sign in, 2. create a brief, 3. click Generate Plan, 4. send a chat message in that brief, 5. wait 2 minutes for background jobs, 6. tell me when done").
- Note the start timestamp (UTC, second precision) before the user begins.
- Tell the user to proceed and to confirm when complete. Don't try to drive the browser; this is a manual flow.
- Wait for confirmation. Don't poll or pre-fetch — there's a 5–30s ingestion lag plus background-job lag (BullMQ jobs can run 1–10+ minutes after the user action).
- Run the audit against
[start_time, now]. Use Q1–Q8 below. - Compare what fired against what should have fired given the action plan. Flag every gap.
- Verdict: pass / fail / partial. List the specific events that fired correctly and the ones that didn't, with property-level details.
Free mode
Use when the user already did stuff and wants a retrospective.
- Default window: last 7 days. Adjust to match the user's stated activity if they say "last hour" / "yesterday" / etc.
- Run Q1–Q8.
- Reconstruct the story chronologically, broken into sessions.
- Verify properties for each event class.
- Flag findings as F1, F2, … with action items.
Mode-decision shortcuts
| User says | Mode |
|---|---|
| "review my events", "what did I do", "tell me the story" | free |
| "I just did X", "I'm about to test", "watch what fires" | guided |
"audit <user>" with no other context |
free |
| "verify event X fires correctly when I do Y" | guided |
Inputs the user MUST provide
| Input | Required | Default |
|---|---|---|
| Email (or distinct_id) | yes | — |
Env (prod, tux, dev, local) |
yes | — |
| Window | no (free) / no (guided — defined by start_time→now) | 7d |
Focus (LLM only, product only, billing, all) |
no | all |
If any required input is missing, ask via AskUserQuestion.
Credentials & environment
PostHog query API hits POST /api/projects/<id>/query with {"query": {"kind": "HogQLQuery", "query": "..."}}.
| Var | Value | Where |
|---|---|---|
POSTHOG_API_KEY |
phx_… personal API key |
packages/glass-box/.env.<env> |
POSTHOG_PROJECT_ID |
env-specific | see below |
| Host | https://us.posthog.com |
hard-coded |
Project IDs (named POSTHOG_PROD_PROJECT_ID in the env files but means "the prod-side project for that env"):
| Env | Project ID |
|---|---|
| prod | 371628 |
| tux | 371881 |
Use the POSTHOG_API_KEY, not POSTHOG_DASHBOARD_API_KEY — the dashboard key lacks events:read.
How to run queries
The simplest path — raw requests, no glass-box init (which pulls Supabase and slows things down):
uv run --no-project --with requests python /tmp/posthog_query.py
Inside the script:
import json
from urllib import request
HOST = 'https://us.posthog.com'
PROJ = '<env-specific>' # 371628 prod, 371881 tux
KEY = '<phx_…>'
def hq(q):
body = json.dumps({'query': {'kind': 'HogQLQuery', 'query': q}}).encode()
req = request.Request(f'{HOST}/api/projects/{PROJ}/query', data=body,
headers={'Authorization': f'Bearer {KEY}', 'Content-Type': 'application/json'},
method='POST')
return json.loads(request.urlopen(req, timeout=60).read())
For a one-off use of PostHogClient.query(hogql) from packages/glass-box/replay/utils/posthog_client.py, the import side-effects pull in Supabase. If that's already configured locally, fine; otherwise prefer the raw approach.
HogQL pitfalls (memorise these)
$-prefixed keys need bracket syntax.properties.$ai_trace_idreturns NULL silently. Useproperties['$ai_trace_id'].OPERATION_PROPERTY_NONEis the literal string'none'. Filter as missing in property-presence audits —prop NOT IN ('', 'none').- Shell escaping breaks complex HogQL. Write the query to a temp
.pyfile and run that — don't try to inline throughpython -c "…"with nested quotes. - A person can have multiple distinct_ids (anonymous browser ID + Supabase user.id). Use
person.properties.email = '<email>'instead ofdistinct_id = '<id>'so you catch both.
The query playbook (Q1–Q8)
Q1 — Person + distinct_ids
SELECT person.id, person.properties.email, person.properties.name,
max(timestamp) as last_seen, count() as evt_count
FROM events
WHERE person.properties.email = '<email>'
AND timestamp > <start>
GROUP BY person.id, person.properties.email, person.properties.name
LIMIT 5
SELECT distinct_id, count() as n,
min(timestamp) as first_seen, max(timestamp) as last_seen
FROM events
WHERE person.properties.email = '<email>'
AND timestamp > <start>
GROUP BY distinct_id ORDER BY n DESC
Expect 1–2: the Supabase user.id (highest count) and a UUIDv7 anonymous browser ID from before identify.
Q2 — Event-name overview
SELECT event, count() as n, min(timestamp) as first, max(timestamp) as last
FROM events
WHERE person.properties.email = '<email>'
AND timestamp > <start>
GROUP BY event ORDER BY n DESC
Skim for:
- Typed product events (
Brief Created,Plan Generation Started, etc.) - LLM events (
$ai_generation,$ai_trace,$ai_span) - Drift events (
User Signed InvsUser Logged In,checkout.started,user.signedUp) - Infra events (
$identify,$pageview,$autocapture— usually skip from the story but useful for session boundaries)
Q3 — $ai_generation attribution audit
SELECT
count() as total,
countIf(notEmpty(toString(properties['$ai_span_name']))) as has_span_name,
countIf(notEmpty(toString(properties['$ai_trace_id']))) as has_trace,
countIf(properties['service.name'] IS NOT NULL) as has_service_name,
countIf(properties.account_id IS NOT NULL AND properties.account_id NOT IN ('', 'none')) as has_account,
countIf(properties.prompt_key IS NOT NULL AND properties.prompt_key NOT IN ('', 'none')) as has_prompt_key,
countIf(properties.step_id IS NOT NULL AND properties.step_id NOT IN ('', 'none')) as has_step_id,
countIf(properties.agent_name IS NOT NULL AND properties.agent_name NOT IN ('', 'none')) as has_agent_name,
countIf(distinct_id = toString(properties['$ai_trace_id'])) as anonymous_n
FROM events
WHERE person.properties.email = '<email>'
AND event = '$ai_generation'
AND timestamp > <start>
Acceptance bar (post-PR-#2186):
| Metric | Target |
|---|---|
anonymous_n / total |
≤ 5% (was 96% pre-fix prod-wide) |
has_account / total |
≥ 95% |
has_trace / total |
100% |
has_span_name, has_service_name |
open instrumentation gaps — report whatever you see |
has_prompt_key |
gradually improving; B2 brief in flight |
has_step_id, has_agent_name |
gradually improving; B3 brief in flight |
Q4 — workflow_type roots + cost
SELECT
toString(properties.workflow_type) as wt,
count() as n,
round(sum(toFloat(coalesce(toString(properties['$ai_total_cost_usd']), '0'))), 4) as cost
FROM events
WHERE person.properties.email = '<email>'
AND event = '$ai_generation'
AND timestamp > <start>
GROUP BY wt ORDER BY n DESC
Map roots through the glossary at packages/glass-box/data/posthog-reports/llm-cost/003-workflow-type-glossary.md:
| Tier | Examples | User-driven? |
|---|---|---|
| A — HTTP-rooted | chat-*, plan_generation, subtask_generation |
yes (request) |
| B — consumer-rooted | chat-processing-*, flow:*, uploaded-document-processing |
yes (job) |
| C — indirectly user-driven | *_generation (title, version_metadata), meeting-summary |
yes (deferred) |
| D — system | context-graph-inference, routine-execution-* |
no |
Q5 — sessions
SELECT
toString(properties['$session_id']) as sid,
count() as n,
countIf(event = '$ai_generation') as ai_gen,
countIf(event = 'Chat Message Sent') as chat_msgs,
countIf(event = 'Brief Created') as briefs,
min(timestamp) as start_ts, max(timestamp) as end_ts,
round(sum(toFloat(coalesce(toString(properties['$ai_total_cost_usd']), '0'))), 4) as total_cost
FROM events
WHERE person.properties.email = '<email>'
AND timestamp > <start>
AND properties['$session_id'] IS NOT NULL
GROUP BY sid ORDER BY start_ts ASC
BullMQ-fired LLM events have NULL $session_id until _sessionId is propagated end-to-end. Sum-of-sessions ai_gen count will be smaller than the global total — that's expected.
Q6 — chronological story
SELECT formatDateTime(timestamp, '%Y-%m-%d %H:%i:%S') as ts,
event,
toString(properties.workflow_type) as wt,
toString(properties['$ai_total_cost_usd']) as cost,
toString(properties['$ai_model']) as model
FROM events
WHERE person.properties.email = '<email>'
AND timestamp > <start>
AND event NOT IN ('$autocapture', '$pageview', '$pageleave', '$set', '$identify',
'$groupidentify', '$web_vitals', '$conversations_loaded')
ORDER BY timestamp ASC
LIMIT 200
Use %i for minute (ClickHouse) — %M renders as month name in some PostHog versions.
Q7 — Trace continuity for one chat thread
This is the cheapest test for F7-class regressions. Pick a thread_id from Q6's output:
SELECT toString(properties['$ai_trace_id']) as tid, event,
toString(properties.workflow_type) as wt, timestamp
FROM events
WHERE person.properties.email = '<email>'
AND timestamp > <start>
AND properties.thread_id = '<thread_uuid>'
AND event IN ('Chat Message Sent', '$ai_generation', 'AI Response Received',
'AI Tool Called', 'Context Retrieved')
ORDER BY timestamp ASC
Pass = Chat Message Sent, all chat-processing > core_chat > … events, AND chat-* > title_generation share one $ai_trace_id.
Fail = ≥2 distinct trace_ids for one thread → F7 (BullMQ trace-context propagation broken; PR #2186 territory). Pre-fix expectation in prod was 3 disjoint trace_ids per chat send.
Q8 — Property dump per event class
For each typed product event the user fired (from Q2), pull one example:
SELECT properties FROM events
WHERE person.properties.email = '<email>'
AND event = '<EventName>'
AND timestamp > <start>
ORDER BY timestamp DESC LIMIT 1
Decode the JSON, filter to non-$-prefixed keys, and check the row matches the property-quality matrix below.
Property-quality matrix
For every typed product event, these MUST be present (non-empty, non-'none'):
| Event class | Required app keys | Optional |
|---|---|---|
*Created/*Updated/*Deleted |
account_id, user_id, <entity>_id |
feature-specific |
Chat Message Sent |
account_id, user_id, thread_id, message_length |
has_document_context |
AI Tool Called |
account_id, user_id, tool_name, workflow_type, success |
thread_id, model fields |
AI Response Received |
account_id, user_id, thread_id, response_time_ms, workflow_type |
brief_id, tool_call_count |
Context Retrieved |
account_id, user_id, query_length, results_count, retrieval_time_ms |
source_types |
Plan Generation Started |
account_id, user_id, brief_id, generation_type |
— |
User Signed In |
user_id, email |
(no account_id — pre-team-selection) |
Account Switched |
account_id, account_slug, account_type, new_account_id, team_role |
from_account_id |
Team Created |
account_id, account_name, account_type, created_via, user_id |
— |
Feedback Submitted |
account_id, user_id, source_url |
— |
Integration Connected |
account_id, user_id, connection_id, integration_type |
workspace_id, workspace_name |
Checkout Started (typed) |
account_id, user_id, plan_id, product_id |
— |
Brief Created |
account_id, user_id, brief_id, has_initial_content |
content_length |
Blueprint Created |
account_id, user_id, blueprint_id, creation_method |
blueprint_type |
Source of truth for the property shapes: packages/analytics/src/event-types.ts (find the *Properties interface for each event name).
For every $ai_generation:
$ai_trace_idpresent (always — set by exporter)distinct_idresolves to the Supabase user.id (NOT$ai_trace_id) for user-driven flowsaccount_idpopulated for B-tier and most C-tier flowsprompt_keypopulated for primary agent calls (not Input Validator, not system jobs)workflow_typepopulated and non-'none'$ai_model,$ai_total_cost_usd,$ai_input_tokens,$ai_output_tokens,$ai_latencypopulated
Output: the report
Write to packages/glass-box/data/posthog-reports/tracking-quality/<NNN>-<slug>.md (next free number — 001 is the per-user audit baseline; subsequent reports are follow-ups or guided-mode runs).
Frontmatter:
---
dashboard_id: n/a (single-user audit) | <id> if a dashboard backs the report
env: prod | tux | dev | local
spec_section: posthog-insights-scope.md §2 + posthog-events-inventory.md
parent_report: <prev report path or 'none'>
supersedes: none
created: YYYY-MM-DD
status: action-items-issued
mode: guided | free
subject: <email or user_id>
window: <ISO start> → <ISO end>
---
Sections in order:
- TL;DR — 2–3 sentences. The one conclusion.
- Headline numbers — table from Q3 + cost from Q4 + session count from Q5.
- The story — chronological narrative from Q6, broken into sessions. For guided mode: the action plan + observed events side-by-side.
- Findings — numbered F1, F2, … each with: what the data shows / why it matters / confidence / source query.
- Hypotheses — for medium/low-confidence findings.
- Actions — prioritised P0/P1/P2: code changes, inventory/doc updates, follow-up investigations.
- Decisions taken — what this audit settled.
- Open questions — what needs more data.
- Children — list of reports spawned from this one.
After writing the report:
- Add a row to
packages/glass-box/data/posthog-reports/tracking-quality/README.md"Reports" table. - Update P0/P1 counts in
docs/architecture/posthog-insights-progress.md§1 (tracking-quality row).
Common findings (memorise)
| Pattern | What it means | Action |
|---|---|---|
≥30% anonymous_n on $ai_generation for user-driven flows |
F7 — BullMQ producers don't stampJobObservability; consumer opens fresh trace + has no userId |
Wait for / verify PR #2186 deploy |
account_id missing only on *-title_generation rows |
F9 — thread-title-generation.consumer.ts partial setRequestCorrelation |
PR #2186 |
$ai_span_name 0% present |
OTel exporter not propagating the metadata key | Investigate @posthog/ai/otel version; possibly rename key in buildExperimentalTelemetry |
service.name / $service 0% present |
OTel resource attribute set, event property not | Add 'service.name' to metadata in buildExperimentalTelemetry |
prompt_key missing on Title Generator / Version Metadata Generator |
F12 — promptKey not plumbed at those call sites |
B2 brief |
Chat Message Sent and chat-processing > core_chat have different $ai_trace_id |
F7 (browser→server boundary, not just BullMQ→BullMQ) | PR #2186 |
Event name typed in event-types.ts but listed TYPED-NOT-FIRED in inventory |
Inventory drift | Flip to TYPED-&-FIRED |
<event> fires in prod but isn't in the inventory at all |
Catalog drift | Add to appropriate §3.X subsection |
checkout.started (untyped) double-fires alongside typed Checkout Started |
Bus-forwarder drift | Retire apps/web/components/analytics-provider.tsx:138 |
Pitfalls
properties.$fooreturns NULL silently. Use bracket syntax for$-prefixed keys.'none'is the missing-value sentinel. Treat as absent in coverage calculations.- PR-deploy lag matters. When comparing pre/post-fix, verify the deploy time, not the merge time. Cloud Run rollouts can be ≥30 minutes after merge.
$session_idis NULL on background BullMQ events. Don't expect every$ai_generationto be in a session.- A person can have multiple distinct_ids (anonymous → identified). Use
person.properties.email = …. - A person can have multiple accounts (personal + team(s)). Watch for
Account Switched. - Don't run
inspect_posthog_dashboard.pyfor this — it's dashboard-scoped. Go direct via Q1–Q8. - In guided mode, give background jobs time to fire — title-generation, version-metadata, plan-generation chains can run 30s–10min after the user's last click. Tell the user "wait 2 minutes after your last action before saying you're done."
Anti-patterns
- Writing the report before running Q1. You'll guess wrong about distinct_ids.
- Reporting "100% have property X" without checking the
'none'sentinel. You'll over-report coverage. - Forgetting the pre-identify anonymous distinct_id. You'll miss the first events of a session.
- Conflating typed-catalog status with live-fire status. Both can be wrong (catalog drift OR fire drift).
- Skipping Q7 (trace continuity) for chat flows. It's the cheapest F7 regression test.
- Acting on guided-mode findings before background jobs fire. Re-run the audit after a 2-minute wait if any C-tier event might still be in flight.
Cross-references
docs/architecture/observability.md—$ai_*taxonomy + correlation envelopedocs/architecture/posthog-events-inventory.md— typed catalog + drift tablepackages/glass-box/data/posthog-reports/llm-cost/003-workflow-type-glossary.md—workflow_typeroot taxonomypackages/glass-box/data/posthog-reports/tracking-quality/001-nicolas-prod-event-audit.md— worked example, free modepackages/analytics/src/event-types.ts— typed catalog source of truthapps/ai/src/common/aisdk/sdk.ts—buildExperimentalTelemetry()packages/api/core/src/context/bullmq.ts—stampJobObservability/withBullMQContext- Sister skill:
hamster-posthog-dashboards(dashboard-level analysis) - Sister skill:
hamster-analytics(event instrumentation patterns when the audit surfaces a code fix)