: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 { DateTime } from 'luxon'
import { QueryResult } from 'pg'
import { PostgresRouter, PostgresUse, TransactionClient } from '~/common/utils/db/postgres'
import { sanitizeJsonbValue } from '~/common/utils/db/utils'
import { RaceConditionError } from '~/common/utils/utils'
import { Properties } from '~/plugin-scaffold'
import {
Group,
GroupTypeIndex,
ProjectId,
PropertiesLastOperation,
PropertiesLastUpdatedAt,
RawGroup,
TeamId,
} from '~/types'
import { GroupRepositoryTransaction } from './group-repository-transaction.interface'
import {
GroupCreate,
GroupCreateResult,
GroupKey,
GroupPropertiesToSetUpdate,
GroupRepository,
} from './group-repository.interface'
import { PostgresGroupRepositoryTransaction } from './postgres-group-repository-transaction'
import { RawPostgresGroupRepository } from './raw-postgres-group-repository.interface'
const MAX_GROUP_TYPES_PER_TEAM = 5
function queryTag(base: string, callerTag?: string): string {
return callerTag ? `${base}:${callerTag}` : base
}
export class PostgresGroupRepository
implements GroupRepository, RawPostgresGroupRepository, GroupRepositoryTransaction
{
constructor(public postgres: PostgresRouter) {}
async fetchGroup(
teamId: TeamId,
groupTypeIndex: GroupTypeIndex,
groupKey: string,
options: { forUpdate?: boolean; useReadReplica?: boolean; callerTag?: string } = {},
tx?: TransactionClient
): Promise<Group | undefined> {
if (options.forUpdate && options.useReadReplica) {
throw new Error("can't enable both forUpdate and useReadReplica in fetchGroup")
}
let queryString = `SELECT * FROM posthog_group WHERE team_id = $1 AND group_type_index = $2 AND group_key = $3`
if (options.forUpdate) {
queryString = queryString.concat(` FOR UPDATE`)
}
const selectResult: QueryResult = await this.postgres.query(
tx ?? (options.useReadReplica ? PostgresUse.PERSONS_READ : PostgresUse.PERSONS_WRITE),
queryString,
[teamId, groupTypeIndex, groupKey],
queryTag('fetchGroup', options.callerTag)
)
if (selectResult.rows.length > 0) {
const rawGroup: RawGroup = selectResult.rows[0]
return this.toGroup(rawGroup)
}
}
async fetchGroupsByKeys(
keys: GroupKey[],
callerTag?: string,
tx?: TransactionClient
): Promise<
{
team_id: TeamId
group_type_index: GroupTypeIndex
group_key: string
group_properties: Record<string, any>
created_at: DateTime
version: number
}[]
> {
if (keys.length === 0) {
return []
}
// Use unnest to match specific (team_id, group_type_index, group_key) tuples
const { rows } = await this.postgres.query(
tx ?? PostgresUse.PERSONS_READ,
`SELECT g.team_id, g.group_type_index, g.group_key, g.group_properties, g.created_at, g.version
FROM posthog_group g
JOIN unnest($1::int[], $2::int[], $3::text[]) AS t(team_id, group_type_index, group_key)
ON g.team_id = t.team_id
AND g.group_type_index = t.group_type_index
AND g.group_key = t.group_key`,
[
keys.map(({ teamId }) => teamId),
keys.map(({ groupTypeIndex }) => groupTypeIndex),
keys.map(({ groupKey }) => groupKey),
],
queryTag('fetchGroupsByKeys', callerTag)
)
return rows.map((row) => {
if (row.group_type_index < 0 || row.group_type_index > 4) {
throw new Error(
`Invalid group_type_index ${row.group_type_index} for team ${row.team_id}. Must be between 0 and 4.`
)
}
return {
team_id: row.team_id as TeamId,
group_type_index: row.group_type_index as GroupTypeIndex,
group_key: row.group_key,
group_properties: row.group_properties,
created_at: DateTime.fromISO(row.created_at).toUTC(),
version: Number(row.version || 0),
}
})
}
async updateGroupsBatch(updates: GroupPropertiesToSetUpdate[]): Promise<Group[]> {
if (updates.length === 0) {
return []
}
// Sort by row identity so concurrent flushes lock overlapping rows in
// the same order. Postgres doesn't guarantee lock order for
// UPDATE ... FROM, but with small batches it nested-loops over the
// UNNEST in input order, so this removes the practical deadlock risk
// between pods; the residue degrades to the individual-write fallback.
const sortedUpdates = [...updates].sort(
(a, b) => a.teamId - b.teamId || a.groupTypeIndex - b.groupTypeIndex || a.groupKey.localeCompare(b.groupKey)
)
const teamIds: number[] = []
const groupTypeIndexes: number[] = []
const groupKeys: string[] = []
const propertiesToSet: string[] = []
const createdAts: string[] = []
for (const update of sortedUpdates) {
teamIds.push(update.teamId)
groupTypeIndexes.push(update.groupTypeIndex)
groupKeys.push(update.groupKey)
propertiesToSet.push(sanitizeJsonbValue(update.propertiesToSet))
createdAts.push(update.createdAt.toISO()!)
}
// Merge semantics (jsonb ||) applied server-side: concurrent writers
// can only race on the same key, never clobber each other's other
// keys, so no version assertion (and no conflict retry) is needed.
// UNNEST keeps the statement shape constant for prepared-statement reuse.
const { rows } = await this.postgres.query<RawGroup>(
PostgresUse.PERSONS_WRITE,
`
UPDATE posthog_group AS g SET
group_properties = COALESCE(g.group_properties, '{}'::jsonb) || batch.new_properties::jsonb,
created_at = LEAST(g.created_at, batch.new_created_at::timestamp with time zone),
version = COALESCE(g.version, 0)::numeric + 1
FROM UNNEST(
$1::int[],
$2::int[],
$3::text[],
$4::text[],
$5::text[]
) AS batch(batch_team_id, batch_group_type_index, batch_group_key, new_properties, new_created_at)
WHERE g.team_id = batch.batch_team_id
AND g.group_type_index = batch.batch_group_type_index
AND g.group_key = batch.batch_group_key
RETURNING g.*
`,
[teamIds, groupTypeIndexes, groupKeys, propertiesToSet, createdAts],
'updateGroupsBatch'
)
return rows.map((row) => this.toGroup(row))
}
async insertGroupsBatch(creates: GroupCreate[]): Promise<GroupCreateResult[]> {
if (creates.length === 0) {
return []
}
// Sort by row identity for the same cross-pod lock-ordering hygiene as
// updateGroupsBatch. UNNEST keeps the statement shape constant for
// prepared-statement reuse.
const sortedCreates = [...creates].sort(
(a, b) => a.teamId - b.teamId || a.groupTypeIndex - b.groupTypeIndex || a.groupKey.localeCompare(b.groupKey)
)
const teamIds: number[] = []
const groupTypeIndexes: number[] = []
const groupKeys: string[] = []
const groupProperties: string[] = []
const createdAts: string[] = []
for (const create of sortedCreates) {
teamIds.push(create.teamId)
groupTypeIndexes.push(create.groupTypeIndex)
groupKeys.push(create.groupKey)
groupProperties.push(sanitizeJsonbValue(create.groupProperties))
createdAts.push(create.createdAt.toISO()!)
}
// Rows that already exist (a lost cross-pod race) are merged in the
// same statement with updateGroupsBatch's jsonb semantics, so callers
// never need a second round trip to converge. `xmax = 0` is the
// standard idiom for telling fresh inserts from conflict-updates:
// an updated row carries the locking transaction's xid there.
const { rows } = await this.postgres.query<RawGroup & { inserted: boolean }>(
PostgresUse.PERSONS_WRITE,
`
INSERT INTO posthog_group AS g (team_id, group_key, group_type_index, group_properties, created_at, properties_last_updated_at, properties_last_operation, version)
SELECT batch_team_id, batch_group_key, batch_group_type_index, new_properties::jsonb, new_created_at::timestamp with time zone, '{}'::jsonb, '{}'::jsonb, 1
FROM UNNEST(
$1::int[],
$2::int[],
$3::text[],
$4::text[],
$5::text[]
) AS batch(batch_team_id, batch_group_type_index, batch_group_key, new_properties, new_created_at)
ON CONFLICT (team_id, group_key, group_type_index) DO UPDATE SET
group_properties = COALESCE(g.group_properties, '{}'::jsonb) || EXCLUDED.group_properties,
created_at = LEAST(g.created_at, EXCLUDED.created_at),
version = COALESCE(g.version, 0)::numeric + 1
RETURNING g.*, (g.xmax = 0) AS inserted
`,
[teamIds, groupTypeIndexes, groupKeys, groupProperties, createdAts],
'insertGroupsBatch'
)
return rows.map(({ inserted, ...row }) => ({ ...this.toGroup(row), inserted }))
}
async insertGroup(
teamId: TeamId,
groupTypeIndex: GroupTypeIndex,
groupKey: string,
groupProperties: Properties,
createdAt: DateTime,
propertiesLastUpdatedAt: PropertiesLastUpdatedAt,
propertiesLastOperation: PropertiesLastOperation,
tx?: TransactionClient
): Promise<number> {
const result = await this.postgres.query<{ version: string }>(
tx ?? PostgresUse.PERSONS_WRITE,
`
INSERT INTO posthog_group (team_id, group_key, group_type_index, group_properties, created_at, properties_last_updated_at, properties_last_operation, version)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
ON CONFLICT (team_id, group_key, group_type_index) DO NOTHING
RETURNING version
`,
[
teamId,
groupKey,
groupTypeIndex,
sanitizeJsonbValue(groupProperties),
createdAt.toISO(),
JSON.stringify(propertiesLastUpdatedAt),
JSON.stringify(propertiesLastOperation),
1,
],
'insertGroup'
)
if (result.rows.length === 0) {
throw new RaceConditionError('Parallel posthog_group inserts, retry')
}
return Number(result.rows[0].version || 0)
}
async updateGroup(
teamId: TeamId,
groupTypeIndex: GroupTypeIndex,
groupKey: string,
groupProperties: Properties,
createdAt: DateTime,
propertiesLastUpdatedAt: PropertiesLastUpdatedAt,
propertiesLastOperation: PropertiesLastOperation,
tag: string,
tx?: TransactionClient
): Promise<number | undefined> {
const result = await this.postgres.query<{ version: string }>(
tx ?? PostgresUse.PERSONS_WRITE,
`
UPDATE posthog_group SET
created_at = $4,
group_properties = $5,
properties_last_updated_at = $6,
properties_last_operation = $7,
version = COALESCE(version, 0)::numeric + 1
WHERE team_id = $1 AND group_key = $2 AND group_type_index = $3
RETURNING version
`,
[
teamId,
groupKey,
groupTypeIndex,
createdAt.toISO(),
sanitizeJsonbValue(groupProperties),
JSON.stringify(propertiesLastUpdatedAt),
JSON.stringify(propertiesLastOperation),
],
tag
)
if (result.rows.length === 0) {
return undefined
}
return Number(result.rows[0].version || 0)
}
async updateGroupOptimistically(
teamId: TeamId,
groupTypeIndex: GroupTypeIndex,
groupKey: string,
expectedVersion: number,
groupProperties: Properties,
createdAt: DateTime,
propertiesLastUpdatedAt: PropertiesLastUpdatedAt,
propertiesLastOperation: PropertiesLastOperation
): Promise<number | undefined> {
const result = await this.postgres.query<{ version: string }>(
PostgresUse.PERSONS_WRITE,
`
UPDATE posthog_group SET
created_at = $5,
group_properties = $6,
properties_last_updated_at = $7,
properties_last_operation = $8,
version = COALESCE(version, 0)::numeric + 1
WHERE team_id = $1 AND group_key = $2 AND group_type_index = $3 AND version = $4
RETURNING version
`,
[
teamId,
groupKey,
groupTypeIndex,
expectedVersion,
createdAt.toISO(),
sanitizeJsonbValue(groupProperties),
JSON.stringify(propertiesLastUpdatedAt),
JSON.stringify(propertiesLastOperation),
],
'updateGroupOptimistically'
)
if (result.rows.length === 0) {
return undefined
}
return Number(result.rows[0].version || 0)
}
async fetchGroupTypesByProjectIds(
projectIds: ProjectId[],
callerTag?: string,
tx?: TransactionClient
): Promise<Record<string, { group_type: string; group_type_index: GroupTypeIndex }[]>> {
if (projectIds.length === 0) {
return {}
}
const { rows } = await this.postgres.query(
tx ?? PostgresUse.PERSONS_READ,
`SELECT project_id, group_type, group_type_index FROM posthog_grouptypemapping WHERE project_id = ANY($1)`,
[projectIds],
queryTag('fetchGroupTypesByProjectIds', callerTag)
)
const response: Record<string, { group_type: string; group_type_index: GroupTypeIndex }[]> = {}
// Initialize empty arrays for all requested project IDs
for (const projectId of projectIds) {
response[projectId.toString()] = []
}
// Group the results by project_id
for (const row of rows) {
const projectIdStr = row.project_id.toString()
if (!response[projectIdStr]) {
response[projectIdStr] = []
}
if (row.group_type_index < 0 || row.group_type_index > 4) {
throw new Error(
`Invalid group_type_index ${row.group_type_index} for team ${row.team_id}, project ${row.project_id}. Must be between 0 and 4.`
)
}
response[projectIdStr].push({
group_type: row.group_type,
group_type_index: row.group_type_index as GroupTypeIndex,
})
}
return response
}
async fetchGroupTypesByTeamIds(
teamIds: TeamId[],
callerTag?: string,
tx?: TransactionClient
): Promise<Record<string, { group_type: string; group_type_index: GroupTypeIndex }[]>> {
if (teamIds.length === 0) {
return {}
}
const { rows } = await this.postgres.query(
tx ?? PostgresUse.PERSONS_READ,
`SELECT team_id, group_type, group_type_index FROM posthog_grouptypemapping WHERE team_id = ANY($1)`,
[teamIds],
queryTag('fetchGroupTypesByTeamIds', callerTag)
)
const response: Record<string, { group_type: string; group_type_index: GroupTypeIndex }[]> = {}
for (const teamId of teamIds) {
response[teamId.toString()] = []
}
for (const row of rows) {
const teamIdStr = row.team_id.toString()
if (!response[teamIdStr]) {
response[teamIdStr] = []
}
if (row.group_type_index < 0 || row.group_type_index > 4) {
throw new Error(
`Invalid group_type_index ${row.group_type_index} for team ${row.team_id}. Must be between 0 and 4.`
)
}
response[teamIdStr].push({
group_type: row.group_type,
group_type_index: row.group_type_index as GroupTypeIndex,
})
}
return response
}
async insertGroupType(
teamId: TeamId,
projectId: ProjectId,
groupType: string,
index: number,
createdAt: DateTime,
tx?: TransactionClient
): Promise<[GroupTypeIndex | null, boolean]> {
if (index < 0 || index >= MAX_GROUP_TYPES_PER_TEAM) {
return [null, false]
}
// created_at is stamped from the triggering event's timestamp so historical imports don't get a
// wall-clock date that postdates their events — HogQL masks $group_N for events older than this.
const insertGroupTypeResult = await this.postgres.query(
tx ?? PostgresUse.PERSONS_WRITE,
`
WITH insert_result AS (
INSERT INTO posthog_grouptypemapping (team_id, project_id, group_type, group_type_index, created_at)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT DO NOTHING
RETURNING group_type_index
)
SELECT group_type_index, 1 AS is_insert FROM insert_result
UNION
SELECT group_type_index, 0 AS is_insert FROM posthog_grouptypemapping WHERE project_id = $2 AND group_type = $3;
`,
[teamId, projectId, groupType, index, createdAt.toISO()],
'insertGroupType'
)
if (insertGroupTypeResult.rows.length == 0) {
return await this.insertGroupType(teamId, projectId, groupType, index + 1, createdAt, tx)
}
const { group_type_index, is_insert } = insertGroupTypeResult.rows[0]
return [group_type_index, is_insert === 1]
}
async inTransaction<T>(
description: string,
transaction: (tx: GroupRepositoryTransaction) => Promise<T>
): Promise<T> {
return await this.inRawTransaction(description, async (tx: TransactionClient) => {
const transactionClient = new PostgresGroupRepositoryTransaction(tx, this)
return await transaction(transactionClient)
})
}
async inRawTransaction<T>(description: string, transaction: (tx: TransactionClient) => Promise<T>): Promise<T> {
return await this.postgres.transaction(PostgresUse.PERSONS_WRITE, description, transaction)
}
private toGroup(row: RawGroup): Group {
return {
...row,
created_at: DateTime.fromISO(row.created_at).toUTC(),
version: Number(row.version || 0),
}
}
}