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.

11.7 KB
Raw Blame
import { BIDataSource, BIField } from '~/queries/schema/schema-business-intelligence'
import { DatabaseSchemaField, DatabaseSchemaTable } from '~/queries/schema/schema-general'

import {
    buildBIConnections,
    filterBIConnections,
    getPendingBIConnectionTables,
} from 'products/business_intelligence/frontend/biConnectionTree'
import { buildBIQuery, DEFAULT_BI_CONFIG, getBIDropTarget } from 'products/business_intelligence/frontend/biEditorTypes'

const field = (
    name: string,
    type: DatabaseSchemaField['type'],
    rest: Partial<DatabaseSchemaField> = {}
): DatabaseSchemaField => ({ name, type, hogql_value: name, schema_valid: true, ...rest })
const table = (name: string, fields: DatabaseSchemaField[]): DatabaseSchemaTable => ({
    id: name,
    name,
    type: 'posthog',
    fields: Object.fromEntries(fields.map((field) => [field.name, field])),
})
const source: BIDataSource = { table: 'events', connectionId: 'example-connection' }
const tables = {
    events: table('events', [field('person', 'lazy_table', { table: 'persons' })]),
    persons: table('persons', [
        field('email', 'string'),
        field('lifetime_value', 'float'),
        field('company', 'lazy_table', { table: 'companies' }),
    ]),
    companies: table('companies', [field('name', 'string'), field('annual_revenue', 'decimal')]),
}

describe('BI connections', () => {
    it('resolves aliases from a table named __proto__', () => {
        const protoTable = table('__proto__', [
            field('person', 'lazy_table', { table: 'persons' }),
            field('alias', 'field_traverser', { chain: ['person'] }),
        ])
        const catalog = new Proxy(tables, {
            get: (target, name, receiver) => (name === '__proto__' ? protoTable : Reflect.get(target, name, receiver)),
        })
        const connections = buildBIConnections({ ...source, table: '__proto__' }, catalog, [], {}, true)
        expect(connections.map((connection) => connection.name)).toEqual(['alias', 'person'])
    })

    it('drags nested dimensions and measures using paths from the original source', () => {
        const catalog = new Proxy(tables, {
            ownKeys: () => {
                throw new Error('Connection traversal must not enumerate the full table catalog')
            },
        })
        const [person] = buildBIConnections(source, catalog, ['["person"]', '["person","company"]'], {}, true)
        const [company] = person.connections

        expect(company.connections).toEqual([])
        const dimension = company.fields.dimensions[0]
        const measure = company.fields.measures[0]
        expect(dimension).toMatchObject({ name: 'person.company.name', expression: 'person.company.name', source })
        expect(measure).toMatchObject({ name: 'person.company.annual_revenue', type: 'decimal', source })
        const value = getBIDropTarget(measure, 'rows').field as BIField
        const query = buildBIQuery({
            ...DEFAULT_BI_CONFIG,
            source,
            rows: [dimension],
            values: [{ field: value, aggregation: 'sum' }],
        })
        expect(query?.query).toContain('person.company.name')
        expect(query?.query).toContain('sum(person.company.annual_revenue)')
        expect(query?.query).toContain('FROM events')
    })

    it.each([
        ['events', 'person'],
        ['stripe_charges', 'customer'],
    ])('drags related dimensions and measures from %s through %s', (rootTable, relation) => {
        const rootSource = { ...source, table: rootTable }
        const warehouse = rootTable === 'stripe_charges'
        const relatedTable = warehouse ? 'stripe_customers' : 'persons'
        const catalog: Record<string, DatabaseSchemaTable> = {
            ...tables,
            [rootTable]: {
                ...table(rootTable, [
                    field(relation, 'lazy_table', {
                        table: `\`${relatedTable}\``,
                        fields: ['email', 'lifetime_value', 'company'],
                    }),
                ]),
                type: warehouse ? 'data_warehouse' : 'posthog',
            },
            [relatedTable]: {
                ...tables.persons,
                name: relatedTable,
                id: warehouse ? 'warehouse-customer-id' : 'persons',
                type: warehouse ? 'data_warehouse' : 'posthog',
            },
        }
        const [related] = buildBIConnections(
            rootSource,
            catalog,
            [JSON.stringify([relation]), JSON.stringify([relation, 'company'])],
            {},
            true
        )
        const [company] = related.connections
        expect(related.tableName).toBe(relatedTable)
        expect(company.connections).toEqual([])
        const dimension = company.fields.dimensions[0]
        const measure = company.fields.measures[0]
        expect(dimension).toMatchObject({
            name: `${relation}.company.name`,
            expression: `${relation}.company.name`,
            source: rootSource,
        })
        expect(measure).toMatchObject({
            name: `${relation}.company.annual_revenue`,
            type: 'decimal',
            source: rootSource,
        })
        const value = getBIDropTarget(measure, 'rows').field as BIField
        const query = buildBIQuery({
            ...DEFAULT_BI_CONFIG,
            source: rootSource,
            rows: [dimension],
            values: [{ field: value, aggregation: 'sum' }],
        })
        expect(query?.query).toContain(`${relation}.company.name`)
        expect(query?.query).toContain(`sum(${relation}.company.annual_revenue)`)
        expect(query?.query).toContain(`FROM ${rootTable}`)
    })

    it.each(['lazy_table', 'view', 'materialized_view', 'virtual_table'] as const)(
        'uses declared fields for %s connections',
        (type) => {
            const catalog = {
                ...tables,
                events: table('events', [field('person', type, { table: 'persons', fields: ['email'] })]),
            }
            const [person] = buildBIConnections(source, catalog, ['["person"]'], {}, true)
            expect(person.fields.dimensions.map((field) => field.name)).toEqual(['person.email'])
            expect(person.fields.measures).toEqual([])
            expect(person.connections).toEqual([])
        }
    )

    it('keeps native property groups searchable without treating warehouse JSON as PostHog properties', () => {
        const catalog = {
            ...tables,
            persons: table('persons', [field('properties', 'json'), field('email', 'string')]),
        }
        for (const rootSource of [{ table: 'events' }, source]) {
            const connections = buildBIConnections(rootSource, catalog, ['["person"]'], {}, true)
            const filtered = filterBIConnections(connections, '$browser')
            expect(filtered.flatMap((connection) => connection.fields.dimensions.map((field) => field.name))).toEqual(
                rootSource.connectionId ? [] : ['person.properties']
            )
        }
    })

    it('uses the PostHog table ID for hydration and status', () => {
        const catalog = { ...tables, persons: { ...table('persons', []), id: 'person-table-id' } }
        const expanded = ['["person"]']
        expect(getPendingBIConnectionTables(buildBIConnections(source, catalog, expanded, {}, false))).toEqual([
            'person-table-id',
        ])
        expect(buildBIConnections(source, catalog, expanded, { 'person-table-id': 'error' }, false)[0].state).toBe(
            'error'
        )
    })

    it('resolves linked aliases and only expands requested paths through cyclic tables', () => {
        const catalog = {
            ...tables,
            events: table('events', [
                field('person', 'lazy_table', { table: 'persons' }),
                field('customer', 'field_traverser', { chain: ['person'] }),
            ]),
            persons: table('persons', [field('email', 'string'), field('manager', 'lazy_table', { table: 'persons' })]),
        }
        const [customer] = buildBIConnections(source, catalog, ['["customer"]', '["customer","manager"]'], {}, true)
        expect(customer.fields.dimensions[0].expression).toBe('customer.email')
        const [manager] = customer.connections
        expect(manager.fields.dimensions[0].expression).toBe('customer.manager.email')
        expect(manager.connections[0]).toMatchObject({ expanded: false, connections: [] })
    })

    it.each([
        [undefined, false, 'loading'],
        ['error', false, 'error'],
        ['missing', false, 'missing'],
        ['loaded', false, 'ready'],
        [undefined, true, 'ready'],
    ] as const)(
        'distinguishes unresolved, failed, and empty connections (%s, complete=%s)',
        (status, complete, state) => {
            const catalog = { ...tables, persons: table('persons', []) }
            const [person] = buildBIConnections(
                source,
                catalog,
                ['["person"]'],
                status ? { persons: status } : {},
                complete
            )
            expect(person.state).toBe(state)
            expect(person.fields).toEqual({ dimensions: [], measures: [] })
        }
    )

    it('does not recurse forever when aliases refer to each other', () => {
        const catalog = {
            events: table('events', [
                field('first', 'field_traverser', { chain: ['second'] }),
                field('second', 'field_traverser', { chain: ['first'] }),
            ]),
        }
        expect(buildBIConnections(source, catalog, [], {}, true)).toEqual([])
    })

    it('lists virtual fields without borrowing types or connections from the printed parent table', () => {
        const catalog = {
            events: table('events', [
                field('id', 'integer'),
                field('created_at', 'lazy_table', { table: 'companies' }),
                field('person', 'field_traverser', { chain: ['poe'] }),
                field('poe', 'virtual_table', {
                    table: 'events',
                    fields: ['id', 'created_at', 'properties', 'team_id'],
                }),
            ]),
        }
        const person = buildBIConnections(source, catalog, ['["person"]'], { events: 'error' }, false).find(
            (c) => c.name === 'person'
        )!
        expect(person).toMatchObject({ state: 'ready', tableName: undefined, connections: [] })
        expect(person.fields.dimensions).toEqual([
            expect.objectContaining({ name: 'person.created_at', type: 'unknown', source }),
            expect.objectContaining({ name: 'person.properties', type: 'unknown', source }),
        ])
        expect(person.fields.measures).toEqual([
            expect.objectContaining({ name: 'person.id', type: 'unknown', source }),
        ])
        expect(getPendingBIConnectionTables([person])).toEqual([])
    })

    it.each([undefined, 'error'] as const)(
        'keeps unresolved aliases visible and searchable while intermediate fields are %s',
        (status) => {
            const catalog = {
                events: table('events', [
                    field('person', 'field_traverser', { chain: ['pdi', 'person'] }),
                    field('pdi', 'lazy_table', { table: 'person_distinct_ids' }),
                ]),
                person_distinct_ids: table('person_distinct_ids', []),
            }
            const connections = buildBIConnections(
                source,
                catalog,
                ['["person"]'],
                status ? { person_distinct_ids: status } : {},
                false
            )
            const person = filterBIConnections(connections, 'email').find((c) => c.name === 'person')!
            expect(person).toMatchObject({ state: status ?? 'loading', tableName: 'person_distinct_ids' })
            expect(getPendingBIConnectionTables(connections)).toEqual(status ? [] : ['person_distinct_ids'])
        }
    )
})