Skip to content

: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.

19.0 KB
Raw Blame
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),
        }
    }
}