:hedgehog: PostHog is the leading platform for building self-driving products. Our developer tools – AI observability, analytics, session replay, flags, experiments, error tracking, logs, and more – capture all the context agents need to diagnose problems, uncover opportunities, and ship fixes. Steer it all from Slack, web, desktop, or the MCP.
Python ab-testingai-analyticsanalyticscdpdata-warehouseexperimentsfeature-flagsjavascriptproduct-analyticspythonreactsession-replaysurveystypescriptweb-analytics
import { randomUUID } from 'crypto'
import { defaultConfig } from '~/common/config/config'
import { PostgresRouter, PostgresUse } from '~/common/utils/db/postgres'
import { createTestTeamFixture } from '~/tests/helpers/sql'
import { RecordingService } from './recording-service'
import { KeyStore, RecordingDecryptor } from './types'
// The unit tests assert the statement text against a mock, so a bad column, cast or CTE would pass
// them. These run the real SQL.
describe('recording cleanup (integration)', () => {
let postgres: PostgresRouter
let service: RecordingService
let teamId: number
let userId: number
let scannerId: string
let targetObservationId: string
let otherObservationId: string
let alertId: string
beforeEach(async () => {
scannerId = randomUUID()
targetObservationId = randomUUID()
otherObservationId = randomUUID()
alertId = randomUUID()
postgres = new PostgresRouter(defaultConfig)
const fixture = await createTestTeamFixture(postgres)
teamId = fixture.team.id
userId = fixture.userId
const keyStore = {
deleteKey: jest
.fn()
.mockResolvedValue({ status: 'deleted', deletedAt: 1700000000, deletedBy: '[email protected]' }),
} as unknown as KeyStore
service = new RecordingService(
{} as never,
'test-bucket',
'session_recordings',
keyStore,
{} as unknown as RecordingDecryptor,
undefined,
undefined,
postgres,
undefined
)
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_replayscanner
(id, team_id, name, description, scanner_type, scanner_config, query, sampling_rate,
sampling_mode, provider, model, enabled, emits_signals, scanner_version, last_swept_at,
admission_credits_since_refresh, created_at, updated_at)
VALUES ($1, $2, 'cleanup-scanner', '', 'monitor', '{}'::jsonb, '{}'::jsonb, 1.0,
'comprehensive', 'google', 'gemini-3.8-flash', true, false, 1, now(),
0, now(), now())`,
[scannerId, teamId],
'fixtureScanner'
)
for (const [observationId, sessionId] of [
[targetObservationId, 'session-to-delete'],
[otherObservationId, 'session-to-keep'],
]) {
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_replayobservation
(id, scanner_id, team_id, session_id, status, error_reason, workflow_id,
scanner_snapshot, scanner_result, triggered_by, created_at, completed_at)
VALUES ($1, $2, $3, $4, 'succeeded', '', '', '{}'::jsonb, '{}'::jsonb, 'schedule', now(), now())`,
[observationId, scannerId, teamId, sessionId],
'fixtureObservation'
)
}
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_replayobservationlabel
(id, observation_id, team_id, is_correct, feedback, created_at, updated_at)
VALUES ($1, $2, $3, true, '', now(), now())`,
[randomUUID(), targetObservationId, teamId],
'fixtureLabel'
)
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_replayobservationview
(id, observation_id, team_id, user_id, created_at)
VALUES ($1, $2, $3, $4, now())`,
[randomUUID(), targetObservationId, teamId, userId],
'fixtureView'
)
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_visionalertconfiguration
(id, scanner_id, team_id, name, enabled, kind, selection, metric, direction,
window_days, check_interval_minutes, state, consecutive_failures,
evaluation_periods, datapoints_to_alarm, cooldown_minutes, created_at, updated_at)
VALUES ($1, $2, $3, 'cleanup-alert', true, 'match', '{}'::jsonb, 'count', 'above',
1, 60, 'not_firing', 0, 1, 1, 0, now(), now())`,
[alertId, scannerId, teamId],
'fixtureAlert'
)
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_visionalertmatch
(id, alert_id, observation_id, team_id, created_at)
VALUES ($1, $2, $3, $4, now())`,
[randomUUID(), alertId, targetObservationId, teamId],
'fixtureMatch'
)
const thumbnail = await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO posthog_exportedasset
(team_id, export_format, created_at, export_context, is_system, expires_after)
VALUES ($1, 'image/png', now(), $2::jsonb, true, now() + interval '90 days')
RETURNING id`,
[
teamId,
JSON.stringify({ session_recording_id: 'session-to-delete', observation_id: targetObservationId }),
],
'fixtureThumbnailAsset'
)
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_replayobservationmedia
(id, observation_id, asset_id, team_id, kind, position, video_start_ms, created_at)
VALUES ($1, $2, $3, $4, 'thumbnail', 0, 0, now())`,
[randomUUID(), targetObservationId, thumbnail.rows[0].id, teamId],
'fixtureMedia'
)
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_replayobservationanchorsimilarity
(observation_id, team_id, anchor_version, similarities)
VALUES ($1, $2, 1, '{}'::jsonb)`,
[targetObservationId, teamId],
'fixtureAnchorSimilarity'
)
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_replayobservationrank
(observation_id, team_id, scanner_id, created_at, anchor_version, contrast, category, person_key,
experiment_id)
VALUES ($1, $2, $3, now(), 1, 0.5, 1, 7, 0)`,
[targetObservationId, teamId, scannerId],
'fixtureRank'
)
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_replayhighlightfeedentry
(team_id, window_days, generation, place, observation_id, scanner_id, created_at, category,
experiment_id, percentile, group_root)
VALUES ($1, 1, 1, 1, $2, $3, now(), 1, 0, 0.9, $2)`,
[teamId, targetObservationId, scannerId],
'fixtureHighlightEntry'
)
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_replayobservationfeeddismissal
(id, observation_id, team_id, user_id, created_at)
VALUES ($1, $2, $3, $4, now())`,
[randomUUID(), targetObservationId, teamId, userId],
'fixtureDismissal'
)
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO replay_vision_replayobservationfeedimpression
(id, observation_id, team_id, user_id, shown_count, last_shown_at)
VALUES ($1, $2, $3, $4, 1, now())`,
[randomUUID(), targetObservationId, teamId, userId],
'fixtureImpression'
)
// Of the deleted recording: a system render, a person's own gif export, and an event screenshot frame.
// Of a kept recording: one export, which must stay.
for (const [format, isSystem, sessionId] of [
['video/mp4', true, 'session-to-delete'],
['image/gif', false, 'session-to-delete'],
['image/png', true, 'session-to-delete'],
['video/mp4', false, 'session-to-keep'],
] as const) {
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO posthog_exportedasset
(team_id, export_format, created_at, export_context, is_system, expires_after)
VALUES ($1, $2, now(), $3::jsonb, $4, now() + interval '30 days')`,
[teamId, format, JSON.stringify({ session_recording_id: sessionId }), isSystem],
'fixtureAsset'
)
}
})
afterEach(async () => {
await postgres.end()
})
const count = async (sql: string, params: unknown[]): Promise<number> => {
const result = await postgres.query(PostgresUse.COMMON_READ, sql, params, 'assertion')
return Number(result.rows[0].count)
}
it('removes the observation graph for the deleted recording and leaves the rest', async () => {
await service.deleteRecordings(['session-to-delete'], teamId, '[email protected]')
expect(
await count(`SELECT count(*) FROM replay_vision_replayobservation WHERE id = $1`, [targetObservationId])
).toBe(0)
for (const table of [
'replay_vision_replayobservationlabel',
'replay_vision_replayobservationview',
'replay_vision_visionalertmatch',
'replay_vision_replayobservationmedia',
'replay_vision_replayobservationfeeddismissal',
'replay_vision_replayobservationfeedimpression',
'replay_vision_replayobservationanchorsimilarity',
'replay_vision_replayobservationrank',
'replay_vision_replayhighlightfeedentry',
]) {
expect(await count(`SELECT count(*) FROM ${table} WHERE observation_id = $1`, [targetObservationId])).toBe(
0
)
}
// An observation of a different recording is untouched.
expect(
await count(`SELECT count(*) FROM replay_vision_replayobservation WHERE id = $1`, [otherObservationId])
).toBe(1)
})
it('queues the deleted observations for the ClickHouse event sweep, once', async () => {
const queued = (observationId: string): Promise<number> =>
count(
`SELECT count(*) FROM posthog_asyncdeletion
WHERE deletion_type = 5 AND team_id = $1 AND key = $2 AND delete_verified_at IS NULL`,
[teamId, observationId]
)
// Already queued, as after a cleanup that failed past this insert: the unique (deletion_type, key)
// constraint must not abort the whole statement on the retry.
await postgres.query(
PostgresUse.COMMON_WRITE,
`INSERT INTO posthog_asyncdeletion (deletion_type, team_id, key, created_at) VALUES (5, $1, $2, now())`,
[teamId, targetObservationId],
'fixtureQueuedEvent'
)
await service.deleteRecordings(['session-to-delete'], teamId, '[email protected]')
expect(
await count(`SELECT count(*) FROM replay_vision_replayobservation WHERE id = $1`, [targetObservationId])
).toBe(0)
expect(await queued(targetObservationId)).toBe(1)
expect(await queued(otherObservationId)).toBe(0)
})
it('expires every export of the deleted recording and none of a kept one', async () => {
await service.deleteRecordings(['session-to-delete'], teamId, '[email protected]')
const live = async (sessionId: string): Promise<number> =>
count(
`SELECT count(*) FROM posthog_exportedasset
WHERE team_id = $1 AND expires_after > now() AND export_context ->> 'session_recording_id' = $2`,
[teamId, sessionId]
)
expect(await live('session-to-delete')).toBe(0)
expect(await live('session-to-keep')).toBe(1)
})
})