feat(insights): permanent archive of PostHog (events+recordings+identity) + Sentry (Phase A+B+C) #2
Reference in New Issue
Block a user
Delete Branch "feat/observability-archive"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Context
PostHog Cloud free tier deletes data on a rolling window (events ~1yr, session recordings ~30d). This mirrors both into our own infra permanently — hot Postgres (queryable) + cold MinIO (gzip JSONL). Phase A of a broader observability archive; Sentry & Grafana are deferred (see plan).
Verified live during planning: PostHog Cloud EU project 127747, ~22.7K events all-time, low volume (~500–1300/day). Grafana Cloud is dormant (OTEL disabled, no agent) → deferred. Sentry not yet configured → deferred.
What this adds
PostHog raw event archive
PosthogEventmodel —uuidPK (natural dedup), fullpropertiesJSONB (lossless), hot columns (event/distinctId/sessionId/timestamp) promoted + indexed.posthog-event-archivejob (*/15min): watermark-paged HogQL pull. Cursor stored in the existingIngestionWatermarkunder a"sase:events"stream key (no collision with recording ingest).createMany({ skipDuplicates })→ idempotent. 365-day backfill on first run.posthog-archive/{project}/YYYY/MM/DD.jsonl.gz;dumpedAtflag makes it idempotent and deterministic.hogqlQueryhelper. Note: cursor usesparseDateTimeBestEffort()— ClickHouse 500s on a raw ISO8601 string literal (confirmed live).Raw rrweb recording preservation
compress(which only covers scored sessions and caps at 12 blobs). This covers every recording at full fidelity before the 30-day deletion.SessionMeta.rrwebArchivedAt/rrwebArchiveKey.archive-recordingsjob (*/6h): recordings within 25d (margin), full blob range, gzip → MinIOrrweb-archive/{project}/YYYY/MM/DD/{sessionId}.jsonl.gz.Wiring & safety
putBuffergzip helper inminio.ts; both jobs registered inpipeline.ts.retention.tsuntouched → the new table + both buckets are never pruned (the explicit goal).prisma db pushon web start (purely additive: new table + nullable columns).Test plan
pnpm --filter worker typecheckpropertiesJSON parsing +parseDateTimeBestEffortfix)prisma db push(or web start) createsposthog_events+ newSessionMetacolumnsrunArchivePosthogEvents():inserted > 0; immediate re-run → all duplicates (idempotency).SELECT count(*) FROM posthog_events≈ 22.7K after backfillposthog-archive/sase/YYYY/MM/DD.jsonl.gzobject exists; rows havedumpedAtrunArchiveRecordings()writes arrweb-archive/.../{sessionId}.jsonl.gz; gunzip first line is valid rrweb JSONDeferred (spec only)
🤖 Generated with Claude Code
Phase A of the external observability archive. PostHog Cloud free tier deletes data on a rolling window (events ~1yr, session recordings ~30d); this mirrors both into our own infra permanently (hot Postgres + cold MinIO gzip JSONL). PostHog raw event archive: - New `PosthogEvent` model (uuid PK → dedup, full properties JSONB, hot columns promoted + indexed). - `posthog-event-archive` job: watermark-paged HogQL pull (cursor in the existing IngestionWatermark via a "sase:events" stream key), createMany + skipDuplicates for idempotency, 365d backfill. Daily closed-day cold dump to MinIO `posthog-archive/{project}/YYYY/MM/DD.jsonl.gz`. - `hogqlQuery` helper added to posthog.ts. Timestamp cursor uses parseDateTimeBestEffort() — ClickHouse 500s on a raw ISO8601 literal (verified live against the project). Raw rrweb recording preservation (separate from compress, which only covers scored sessions and caps blobs): - `SessionMeta.rrwebArchivedAt` / `rrwebArchiveKey`. - `archive-recordings` job: every recording within 25d (margin before 30d deletion), full blob range (no 12-cap), gzip → MinIO `rrweb-archive/{project}/YYYY/MM/DD/{sessionId}.jsonl.gz`. - `putBuffer` gzip helper in minio.ts. - Both jobs wired into pipeline.ts (event-archive@*/15min, recordings@*/6h). - retention.ts untouched → new table + buckets persist forever (the goal). Schema applies via the existing `prisma db push` on web start (additive: new table + nullable columns). Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>feat(insights): permanent archive of PostHog events + raw recordings (Phase A)to feat(insights): permanent archive of PostHog events + recordings + person/cohort snapshots (Phase A+B)Phase B added — person + cohort snapshots
Append-only history of PostHog persons & cohorts (free tier overwrites the live person / prunes data). Change-detected: a new row is written only when the payload hash changes, so the tables are a compact timeline, not a daily full copy.
PosthogPersonSnapshot—personId(joinsPosthogEvent.personId), alldistinctIds,propertiesJSONB,propertiesHash.PosthogCohortSnapshot—cohortId,name,count,filters,stateHash.listPersons/listCohortsREST helpers (next-pagination);stableHash(sorted-key JSON) for change detection.posthog-identity-archivejob @03:00 daily. Latest-hash lookup via oneDISTINCT ONquery; only changed rows inserted.Verified against prod: 242 persons → 242 snapshots on first run, 0 on immediate re-run (change detection works). 0 cohorts currently → graceful no-op (future-proofed for when cohorts are created).
Note: web vitals & exceptions need no separate work — they already land as event rows via Phase A. Remaining deferred: Phase C (Sentry), Phase D (Grafana, blocked on telemetry).
Mirrors Sentry into our DB before the free tier prunes events (~30d). - `SentryIssue` (aggregate state, upserted to latest) + `SentryEvent` (raw occurrences, lossless full payload JSONB, dedup by eventId, cold dump to MinIO `sentry-archive/{project}/YYYY/MM/DD.jsonl.gz`). - `lib/sentry.ts`: read-only client, Link-header cursor pagination, listIssuesPage / listEventsPage. EU-region aware (SENTRY_API_BASE). - `sentry-archive` job @hourly: issues upsert + events newest-first with skipDuplicates, stops once a page is all-duplicates (caught up). - Config via env: SENTRY_AUTH_TOKEN / SENTRY_ORG / SENTRY_PROJECT / SENTRY_API_BASE. Verified live against otolog/python (EU): a test event archived on run 1, 0 inserts / 1 duplicate on run 2 (dedup), issue upserted idempotently. Note: Sase API currently sends to an inaccessible org's DSN (hardcoded fallback); repointing SENTRY_DSN to otolog/python is a separate deploy step. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>feat(insights): permanent archive of PostHog events + recordings + person/cohort snapshots (Phase A+B)to feat(insights): permanent archive of PostHog (events+recordings+identity) + Sentry (Phase A+B+C)Phase C added — Sentry issues + events archive
Mirrors Sentry into our DB before the free tier prunes events (~30d).
SentryIssue(aggregate state, upserted to latest) +SentryEvent(raw occurrences, lossless full payload JSONB, dedup by eventId, cold-dumped to MinIOsentry-archive/{project}/YYYY/MM/DD.jsonl.gz).lib/sentry.tsread-only client with Link-header cursor pagination; EU-region aware (SENTRY_API_BASE).sentry-archivejob @hourly: issues upsert + events newest-first withskipDuplicates, stops once a page is all-duplicates.SENTRY_AUTH_TOKEN/SENTRY_ORG/SENTRY_PROJECT/SENTRY_API_BASE.Verified live against
otolog/python(EU): test event archived on run 1, 0 inserts / 1 duplicate on run 2 (dedup), issue upserted idempotently.Discovery + repoint (operational)
The Sase API's hardcoded fallback DSN pointed to an inaccessible org (
4511360959250432), while the account's token only reaches orgotolog(which was empty). Per decision, repointed Sase tootolog/python:SENTRY_DSN(otolog/python) on the Sase Coolify app + restarted it → real errors now flow to an org we can read.SENTRY_AUTH_TOKEN/ORG/PROJECT/API_BASEon panel-worker so the hourly job archives once this PR deploys.Grafana (Phase D) still deferred — telemetry not flowing (OTEL disabled, no agent).