Skip to content

The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.

20.1 KB
Raw Blame
import { describe, expect, it } from 'vitest'

import {
  getFacetCountQuery,
  getLogsChartQuery,
  getLogsCountQuery,
  getUnifiedLogsQuery,
  isUserFilterUnreachable,
} from './UnifiedLogs.queries'
import { getUnifiedLogsQuery as getUnifiedLogsQueryBQ } from './UnifiedLogs.queries.bq'

const baseSearch = {
  date: [new Date('2026-05-08T09:00:00Z'), new Date('2026-05-08T10:00:00Z')],
} as any

// Helper: build a search with extra `filter` URL entries on top of the base.
const withFilters = (...entries: string[]) => ({ ...baseSearch, filter: entries }) as any

// Helper: build a search with the cross-cutting `user` filter set, plus optional `filter` entries.
const withUser = (user: string, ...entries: string[]) =>
  ({ ...baseSearch, user, filter: entries }) as any

describe('UnifiedLogs.queries (OTEL flat)', () => {
  describe('getUnifiedLogsQuery', () => {
    it('defaults to postgres + edge log types when none specified', () => {
      const sql = getUnifiedLogsQuery(baseSearch)
      const where = sql.split(/\bWHERE\b/)[1] ?? ''
      expect(where).toContain(`source = 'postgres_logs'`)
      expect(where).toContain(`source = 'edge_logs'`)
      expect(where).not.toContain(`source = 'postgrest_logs'`)
    })

    it.each([
      ['postgrest', 'postgrest_logs', '%/rest/%'],
      ['storage', 'storage_logs', '%/storage/%'],
    ])(
      'routes the `%s` log type solely to %s (mutually exclusive from edge_logs)',
      (logType, source, edgePath) => {
        const sql = getUnifiedLogsQuery(withFilters(`log_type:eq:${logType}`))
        const where = sql.split(/\bWHERE\b/)[1] ?? ''
        expect(where).toContain(`source = '${source}'`)
        expect(where).not.toContain(`source = 'edge_logs'`)
        expect(where).not.toContain(`log_attributes['request.path'] LIKE '${edgePath}'`)
      }
    )

    it('routes the `compute` log type to every worker OTEL stream', () => {
      const sql = getUnifiedLogsQuery(withFilters('log_type:eq:compute'))
      const where = sql.split(/\bWHERE\b/)[1] ?? ''
      expect(where).toContain(
        `log_attributes['subservice'] IN ('worker_ingress_logs','worker_guest_logs','worker_api_logs')`
      )
      expect(where).not.toContain(`source = 'compute'`)
    })

    it('classifies every worker OTEL stream as compute in the projected log type', () => {
      const sql = getUnifiedLogsQuery(baseSearch)
      expect(sql).toContain(
        `WHEN log_attributes['subservice'] IN ('worker_ingress_logs','worker_guest_logs','worker_api_logs') THEN 'compute'`
      )
    })

    it('excludes every worker OTEL stream when the compute log type is negated', () => {
      const sql = getUnifiedLogsQuery(withFilters('log_type:neq:compute'))
      const where = sql.split(/\bWHERE\b/)[1] ?? ''
      expect(where).toContain(
        `NOT (log_attributes['subservice'] IN ('worker_ingress_logs','worker_guest_logs','worker_api_logs'))`
      )
    })

    it('projects only Compute fields that exist on worker logs', () => {
      const sql = getUnifiedLogsQuery(withFilters('log_type:eq:compute'))
      const workerCondition =
        "log_attributes['subservice'] IN ('worker_ingress_logs','worker_guest_logs','worker_api_logs')"

      expect(sql).toContain(`WHEN ${workerCondition} THEN null`)
      expect(sql).toContain(
        `if(${workerCondition}, null, if(source = 'storage_logs', log_attributes['req.method'], log_attributes['request.method']))`
      )
      expect(sql).toContain(
        `if(${workerCondition}, null, if(source = 'storage_logs', log_attributes['req.url'], log_attributes['request.path']))`
      )
      expect(sql).toContain(`if(${workerCondition}, log_attributes, map()) AS metadata`)
    })

    it('escapes single quotes in filter values to prevent SQL injection', () => {
      const sql = getUnifiedLogsQuery(
        withFilters(`method:eq:G'ET`, `pathname:eq:/customers'; DROP TABLE logs --`)
      )
      // Single quotes are doubled (SQL-standard escaping) by pg-meta's
      // literal(); a raw single quote from user input never closes its
      // string literal early.
      expect(sql).toContain(`'G''ET'`)
      expect(sql).toContain(`%/customers''; DROP TABLE logs --%`)
    })

    it('translates method/status/pathname filters to log_attributes predicates', () => {
      const sql = getUnifiedLogsQuery(
        withFilters('method:eq:GET', 'status:eq:401', 'pathname:eq:/customers')
      )
      // Method/pathname filters wrap the if() that picks the gateway or
      // storage-service attribute key so storage rows match too.
      expect(sql).toContain(
        `(if(source = 'storage_logs', log_attributes['req.method'], log_attributes['request.method'])) IN ('GET')`
      )
      // Status filter wraps the CASE that picks HTTP code or Postgres SQLSTATE
      // so e.g. '00000' matches postgres success rows.
      expect(sql).toContain(`log_attributes['parsed.sql_state_code']`)
      expect(sql).toMatch(/END\) IN \('401'\)/)
      expect(sql).toContain(
        `(if(source = 'storage_logs', log_attributes['req.url'], log_attributes['request.path'])) LIKE '%/customers%'`
      )
    })

    it('flips IN to NOT IN when the operator is `<>`', () => {
      const sql = getUnifiedLogsQuery(withFilters('method:neq:GET', 'status:neq:401'))
      expect(sql).toContain(
        `(if(source = 'storage_logs', log_attributes['req.method'], log_attributes['request.method'])) NOT IN ('GET')`
      )
      expect(sql).toMatch(/END\) NOT IN \('401'\)/)
    })

    // Auth- and storage-service logs expose their status under their own keys,
    // not the gateway's `response.status_code`; without this their 4xx/5xx
    // classify as success and the severity filter returns nothing.
    it.each([
      ['auth', `WHEN source = 'auth_logs' THEN log_attributes['status']`],
      ['storage', `WHEN source = 'storage_logs' THEN log_attributes['res.statusCode']`],
    ])('reads the HTTP status from the service-specific key for %s rows', (logType, expected) => {
      const sql = getUnifiedLogsQuery(withFilters(`log_type:eq:${logType}`))
      expect(sql).toContain(expected)
    })

    it('flips LIKE to NOT LIKE when the pathname/host operator is `<>`', () => {
      const sql = getUnifiedLogsQuery(withFilters('pathname:neq:/health', 'host:neq:cdn.foo'))
      expect(sql).toContain(
        `(if(source = 'storage_logs', log_attributes['req.url'], log_attributes['request.path'])) NOT LIKE '%/health%'`
      )
      expect(sql).toContain(`log_attributes['request.url'] NOT LIKE '%cdn.foo%'`)
    })

    const pathExpr = `(if(source = 'storage_logs', log_attributes['req.url'], log_attributes['request.path']))`

    it.each([
      ['pathname:ilike:Customers', `${pathExpr} ILIKE '%Customers%'`, null],
      ['pathname:notilike:health', `${pathExpr} NOT ILIKE '%health%'`, null],
      ['pathname:ilike:foo%', `${pathExpr} ILIKE 'foo%'`, `'%foo%%'`],
      ['pathname:ilike:fo_bar', `${pathExpr} ILIKE 'fo_bar'`, `'%fo_bar%'`],
      ['event_message:ilike:Permission Denied', `event_message ILIKE '%Permission Denied%'`, null],
      ['event_message:notilike:cron', `event_message NOT ILIKE '%cron%'`, null],
      ['event_message:ilike:error%', `event_message ILIKE 'error%'`, `'%error%%'`],
      ['event_message:ilike:foo_bar', `event_message ILIKE 'foo_bar'`, `'%foo_bar%'`],
    ])(
      'emits the pattern predicate for %s, auto-wrapping only values without wildcards',
      (filter, expected, notExpected) => {
        const sql = getUnifiedLogsQuery(withFilters(filter))
        expect(sql).toContain(expected)
        if (notExpected) expect(sql).not.toContain(notExpected)
      }
    )

    it.each([
      ['pathname', pathExpr],
      ['event_message', 'event_message'],
    ])('joins multiple %s NOT ILIKE values with AND (row must match none)', (column, expr) => {
      const sql = getUnifiedLogsQuery(
        withFilters(`${column}:notilike:health`, `${column}:notilike:metrics`)
      )
      expect(sql).toContain(`${expr} NOT ILIKE '%health%' AND ${expr} NOT ILIKE '%metrics%'`)
    })

    it('excludes connection log messages when show_connection_logs=false', () => {
      const sql = getUnifiedLogsQuery({ ...baseSearch, show_connection_logs: false } as any)
      expect(sql).toContain("source != 'postgres_logs'")
      expect(sql).toContain("event_message NOT LIKE 'connection received%'")
      expect(sql).toContain("event_message NOT LIKE 'connection authenticated%'")
      expect(sql).toContain("event_message NOT LIKE 'connection authorized%'")
    })

    it('includes connection log messages by default (show_connection_logs=true)', () => {
      const sql = getUnifiedLogsQuery({ ...baseSearch, show_connection_logs: true } as any)
      expect(sql).not.toContain("event_message NOT LIKE 'connection received%'")
    })

    it.each([
      ['edge_auth', '%/auth/%'],
      ['edge_storage', '%/storage/%'],
      ['edge_postgrest', '%/rest/%'],
    ] as const)('excludes %s-pathed requests from edge_logs when %s=false', (key, pathFilter) => {
      const sql = getUnifiedLogsQuery({ ...baseSearch, [key]: false } as any)
      expect(sql).toContain("source != 'edge_logs'")
      expect(sql).toContain(`log_attributes['request.path'] NOT LIKE '${pathFilter}'`)
    })

    it('does not filter edge_logs by service path by default (all edge_* toggles true)', () => {
      const sql = getUnifiedLogsQuery(baseSearch)
      expect(sql).not.toContain("log_attributes['request.path'] NOT LIKE '%/auth/%'")
      expect(sql).not.toContain("log_attributes['request.path'] NOT LIKE '%/storage/%'")
      expect(sql).not.toContain("log_attributes['request.path'] NOT LIKE '%/rest/%'")
    })

    it('leaves dedicated auth_logs/storage_logs/postgrest_logs rows untouched by the edge_* toggles', () => {
      // These toggles only hide traffic nested inside the `edge_logs` (API
      // Gateway) source — the dedicated sources are separate log types now
      // that log types are mutually exclusive, so they shouldn't be scoped by
      // a `source != 'edge_logs' OR ...` guard meant for gateway rows.
      const sql = getUnifiedLogsQuery({
        ...baseSearch,
        edge_auth: false,
        edge_storage: false,
        edge_postgrest: false,
      } as any)
      expect(sql).toContain(
        "(source != 'edge_logs' OR log_attributes['request.path'] NOT LIKE '%/auth/%')"
      )
      expect(sql).toContain(
        "(source != 'edge_logs' OR log_attributes['request.path'] NOT LIKE '%/storage/%')"
      )
      expect(sql).toContain(
        "(source != 'edge_logs' OR log_attributes['request.path'] NOT LIKE '%/rest/%')"
      )
    })

    it('does not emit subqueries or CTEs (rejected by the OTEL endpoint)', () => {
      const sql = getUnifiedLogsQuery(baseSearch)
      expect(sql).not.toMatch(/WITH\s+\w+\s+AS\s*\(/i)
      expect(sql).not.toMatch(/FROM\s*\(\s*SELECT/i)
      // Single SELECT * FROM logs (not "SELECT *" wildcard usage either).
      expect(sql).not.toMatch(/SELECT\s+\*/)
    })
  })

  describe('getLogsCountQuery', () => {
    const whereOfBranchContaining = (sql: string, needle: string) => {
      const branch = sql.split(/\bUNION ALL\b/).find((b) => b.includes(needle)) ?? ''
      return branch.split(/\bWHERE\b/)[1]?.split(/\bGROUP BY\b/)[0] ?? ''
    }

    it('folds facets into single-pass arrayJoin scans with a total row', () => {
      const sql = getLogsCountQuery(baseSearch)
      expect(sql).toContain('arrayJoin([')
      expect(sql).toContain('multiIf(')
      expect(sql).toContain(`facet = 'total', 'all'`)
      for (const lt of ['postgrest', 'storage', 'postgres', 'edge function', 'auth']) {
        expect(sql).toContain(`'${lt}'`)
      }
      for (const lvl of ['success', 'warning', 'error']) {
        expect(sql).toContain(`'${lvl}'`)
      }
    })

    it('honours an active log_type filter in the total count scan', () => {
      const sql = getLogsCountQuery(withFilters('log_type:eq:storage'))
      // Assert on the WHERE only: value expressions mention other sources inline.
      const totalWhere = whereOfBranchContaining(sql, `'all'`)
      expect(totalWhere).toContain(`source = 'storage_logs'`)
      expect(totalWhere).not.toContain(`source = 'edge_logs'`)
      expect(totalWhere).not.toContain(`source = 'postgres_logs'`)
    })

    it('gives the log_type facet its own scan that excludes the log_type filter', () => {
      const sql = getLogsCountQuery(withFilters('log_type:eq:postgrest'))
      const logTypeWhere = whereOfBranchContaining(sql, `'log_type'`)
      expect(logTypeWhere).not.toContain(`LIKE '%/rest/%'`)
    })

    it('applies the connection-logs filter to every count scan so badges match the list', () => {
      const sql = getLogsCountQuery({ ...baseSearch, show_connection_logs: false } as any)
      const scans = sql.split(/\bUNION ALL\b/)
      expect(scans.length).toBeGreaterThan(1)
      for (const scan of scans) {
        expect(scan).toContain("event_message NOT LIKE 'connection received%'")
      }
    })

    it('applies the edge_* service filters to every count scan so badges match the list', () => {
      const sql = getLogsCountQuery({ ...baseSearch, edge_postgrest: false } as any)
      const scans = sql.split(/\bUNION ALL\b/)
      expect(scans.length).toBeGreaterThan(1)
      for (const scan of scans) {
        expect(scan).toContain("log_attributes['request.path'] NOT LIKE '%/rest/%'")
      }
    })
  })

  describe('getLogsChartQuery', () => {
    it.each([
      ['toStartOfMinute', '2026-05-08T09:00:00Z', '2026-05-08T10:00:00Z'],
      ['toStartOfHour', '2026-05-08T00:00:00Z', '2026-05-08T18:00:00Z'],
      ['toStartOfDay', '2026-05-01T00:00:00Z', '2026-05-08T00:00:00Z'],
    ])('buckets with %s for a %s - %s range', (bucketFn, start, end) => {
      const sql = getLogsChartQuery({
        ...baseSearch,
        date: [new Date(start), new Date(end)],
      } as any)
      expect(sql).toContain(`${bucketFn}(timestamp)`)
    })

    it('buckets auth rows by their log_attributes[status] so 4xx/5xx are not counted as success', () => {
      const sql = getLogsChartQuery(baseSearch)
      expect(sql).toContain(`WHEN source = 'auth_logs' THEN log_attributes['status']`)
    })

    it('does not classify Compute rows into a severity bucket', () => {
      const sql = getLogsChartQuery(withFilters('log_type:eq:compute'))
      const workerCondition =
        "log_attributes['subservice'] IN ('worker_ingress_logs','worker_guest_logs','worker_api_logs')"

      expect(sql).toContain(`WHEN ${workerCondition} THEN null`)
      expect(sql).not.toContain(`WHEN ${workerCondition} THEN 'success'`)
    })
  })

  describe('getFacetCountQuery', () => {
    it('groups by the requested facet and excludes that facet from the WHERE filters', () => {
      const sql = getFacetCountQuery({
        search: withFilters('method:eq:GET', 'status:eq:200'),
        facet: 'method',
      })
      // Filtered facet is excluded from WHERE; other filters still applied.
      // For facet='method' the SELECT projection doesn't include STATUS_EXPR,
      // so SQLSTATE/IN ('200') must come from the WHERE clause.
      expect(sql).not.toContain(`log_attributes['request.method'] IN ('GET')`)
      expect(sql).toContain(`log_attributes['parsed.sql_state_code']`)
      expect(sql).toMatch(/END\) IN \('200'\)/)
      expect(sql).toContain('GROUP BY value')
      expect(sql).toContain('LIMIT 20')
    })
  })

  describe('user filter', () => {
    it('restricts to auth_logs/edge_logs and skips the default postgres+edge restriction', () => {
      const sql = getUnifiedLogsQuery(withUser('user-123'))
      const where = sql.split(/\bWHERE\b/)[1] ?? ''
      expect(where).toContain(
        `(source = 'auth_logs' AND log_attributes['auth_event.actor_id'] = 'user-123')`
      )
      expect(where).toContain(
        `(source = 'edge_logs' AND log_attributes['request.sb.jwt.authorization.payload.subject'] = 'user-123')`
      )
      // The unfiltered default (edge_logs OR postgres_logs) would incorrectly exclude
      // auth_logs, the primary attributable source, so it must not appear here.
      expect(where).not.toContain(`(source = 'edge_logs') OR (source = 'postgres_logs')`)
    })

    it('does not restrict sources when the user filter is inactive', () => {
      const sql = getUnifiedLogsQuery(baseSearch)
      const where = sql.split(/\bWHERE\b/)[1] ?? ''
      expect(where).not.toContain(`auth_event.actor_id`)
    })

    it('ANDs an explicit non-attributable log_type filter with the user condition (edge case: always zero rows)', () => {
      // Locks in the exact shape `isUserFilterUnreachable` detects — `source = 'edge_logs'`
      // AND `(source = 'auth_logs' ...) OR (source = 'postgres_logs' ...)` can never both hold.
      const sql = getUnifiedLogsQuery(withUser('user-123', 'log_type:eq:edge'))
      const where = sql.split(/\bWHERE\b/)[1] ?? ''
      expect(where).toContain(`source = 'edge_logs'`)
      expect(where).toContain(`log_attributes['auth_event.actor_id'] = 'user-123'`)
    })
  })

  describe('isUserFilterUnreachable', () => {
    it('is false when no user filter is active', () => {
      expect(isUserFilterUnreachable(withFilters('log_type:eq:edge'))).toBe(false)
    })

    it.each([
      [[], false],
      [['log_type:eq:auth'], false],
      [['log_type:eq:edge'], false],
      [['log_type:eq:edge', 'log_type:eq:auth'], false],
      [['log_type:eq:storage'], true],
      [['log_type:eq:realtime', 'log_type:eq:storage'], true],
      // Excluding one attributable source leaves the other eligible.
      [['log_type:neq:edge'], false],
      [['log_type:neq:auth', 'log_type:neq:edge'], true],
    ])('with a user filter and log_type filters %j returns %s', (filters, expected) => {
      expect(isUserFilterUnreachable(withUser('user-123', ...filters))).toBe(expected)
    })
  })

  describe('analyticsLiteral escaping', () => {
    it('emits ClickHouse / BigQuery escape syntax (doubled `\\\\`, no `E` prefix)', () => {
      // pg-meta's literal() would emit `E'a\\b'` for `a\b` — the `E` prefix is
      // Postgres-only and rejected by both analytics engines. analyticsLiteral
      // doubles the backslash inside plain `'…'` delimiters instead.
      const sql = getUnifiedLogsQuery(withFilters('method:eq:a\\b'))
      expect(sql).toContain(
        `(if(source = 'storage_logs', log_attributes['req.method'], log_attributes['request.method'])) IN ('a\\\\b')`
      )
      expect(sql).not.toContain(`E'a`)
    })
  })
})

describe('UnifiedLogs.queries.bq', () => {
  it('backtick-quotes column identifiers (BigQuery syntax)', () => {
    const sql = getUnifiedLogsQueryBQ(withFilters('method:eq:GET'))
    expect(sql).toContain("`method` IN ('GET')")
  })

  // Keys failing the quotedIdent regex (`[A-Za-z_][A-Za-z0-9_]*`) drop the predicate entirely.
  it.each([
    ['foo; DROP TABLE x:eq:y', ['DROP TABLE', 'foo;']],
    ['level OR id IS NOT NULL:eq:anything', ['IS NOT NULL', 'level OR']],
  ])('rejects the non-identifier key in %s', (filter, forbidden) => {
    const sql = getUnifiedLogsQueryBQ(withFilters(filter))
    for (const fragment of forbidden) expect(sql).not.toContain(fragment)
  })

  it('escapes injection attempts in filter values via analyticsLiteral', () => {
    const sql = getUnifiedLogsQueryBQ(withFilters("method:eq:GET' OR '1'='1"))
    // The value is single-quote-escaped, so the synthetic OR can't break out
    // of the string literal.
    expect(sql).toContain("`method` IN ('GET'' OR ''1''=''1')")
    expect(sql).not.toMatch(/`method` IN \('GET' OR '1'='1'\)/)
  })

  // BigQuery has no ILIKE keyword. Wildcard handling must match the ClickHouse
  // builder, otherwise switching the `otelUnifiedLogs` flag would silently
  // change what a saved/shared filter matches.
  it.each([
    [
      'event_message:ilike:Permission Denied',
      "LOWER(`event_message`) LIKE LOWER('%Permission Denied%')",
    ],
    ['event_message:notilike:cron', "LOWER(`event_message`) NOT LIKE LOWER('%cron%')"],
    ['pathname:ilike:Customers', "LOWER(`pathname`) LIKE LOWER('%Customers%')"],
    ['pathname:notilike:health', "LOWER(`pathname`) NOT LIKE LOWER('%health%')"],
    ['pathname:ilike:foo%', "LOWER(`pathname`) LIKE LOWER('foo%')"],
    ['pathname:ilike:fo_bar', "LOWER(`pathname`) LIKE LOWER('fo_bar')"],
  ])('emulates ILIKE with LOWER() for %s', (filter, expected) => {
    const sql = getUnifiedLogsQueryBQ(withFilters(filter))
    expect(sql).toContain(expected)
    expect(sql).not.toMatch(/\bILIKE\b/)
  })
})