The Postgres development platform. Supabase gives you a dedicated Postgres database to build your web, mobile, and AI applications.
TypeScript aialternativeauthdatabasedenoembeddingsexamplefirebasenextjsoauth2pgvectorpostgispostgrespostgresqlpostgrestrealtimesupabasevectorswebsockets
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/)
})
})