konnosDashboard
UnifiedLogs.queries.test.ts192 lines · main
1import { describe, expect, it } from 'vitest'
2
3import {
4 getFacetCountQuery,
5 getLogsChartQuery,
6 getLogsCountQuery,
7 getUnifiedLogsQuery,
8} from './UnifiedLogs.queries'
9import { getUnifiedLogsQuery as getUnifiedLogsQueryBQ } from './UnifiedLogs.queries.bq'
10
11const baseSearch = {
12 date: [new Date('2026-05-08T09:00:00Z'), new Date('2026-05-08T10:00:00Z')],
13} as any
14
15describe('UnifiedLogs.queries (OTEL flat)', () => {
16 describe('getUnifiedLogsQuery', () => {
17 it('defaults to postgres + postgrest log types when none specified', () => {
18 const sql = getUnifiedLogsQuery(baseSearch)
19 expect(sql).toContain(`source = 'postgres_logs'`)
20 // postgrest = edge_logs filtered by /rest/ path
21 expect(sql).toContain(
22 `source = 'edge_logs' AND log_attributes['request.path'] LIKE '%/rest/%'`
23 )
24 })
25
26 it('routes the `edge` log type to edge_logs without /rest/ or /storage/ paths', () => {
27 const sql = getUnifiedLogsQuery({ ...baseSearch, log_type: ['edge'] } as any)
28 expect(sql).toContain(`NOT LIKE '%/rest/%'`)
29 expect(sql).toContain(`NOT LIKE '%/storage/%'`)
30 const where = sql.split(/\bWHERE\b/)[1] ?? ''
31 expect(where).not.toContain(`source = 'postgres_logs'`)
32 })
33
34 it('routes the `storage` log type to edge_logs filtered by /storage/', () => {
35 const sql = getUnifiedLogsQuery({ ...baseSearch, log_type: ['storage'] } as any)
36 expect(sql).toContain(
37 `source = 'edge_logs' AND log_attributes['request.path'] LIKE '%/storage/%'`
38 )
39 })
40
41 it('escapes single quotes in filter values to prevent SQL injection', () => {
42 const sql = getUnifiedLogsQuery({
43 ...baseSearch,
44 method: [`G'ET`],
45 pathname: `/customers'; DROP TABLE logs --`,
46 } as any)
47 // Single quotes are doubled (SQL-standard escaping) by pg-meta's
48 // literal(); a raw single quote from user input never closes its
49 // string literal early.
50 expect(sql).toContain(`'G''ET'`)
51 expect(sql).toContain(`%/customers''; DROP TABLE logs --%`)
52 })
53
54 it('translates method/status/pathname filters to log_attributes predicates', () => {
55 const sql = getUnifiedLogsQuery({
56 ...baseSearch,
57 method: ['GET'],
58 status: ['401'],
59 pathname: '/customers',
60 } as any)
61 expect(sql).toContain(`log_attributes['request.method'] IN ('GET')`)
62 // Status filter wraps the CASE that picks HTTP code or Postgres SQLSTATE
63 // so e.g. '00000' matches postgres success rows.
64 expect(sql).toContain(`log_attributes['parsed.sql_state_code']`)
65 expect(sql).toMatch(/END\) IN \('401'\)/)
66 expect(sql).toContain(`log_attributes['request.path'] LIKE '%/customers%'`)
67 })
68
69 it('does not emit subqueries or CTEs (rejected by the OTEL endpoint)', () => {
70 const sql = getUnifiedLogsQuery(baseSearch)
71 expect(sql).not.toMatch(/WITH\s+\w+\s+AS\s*\(/i)
72 expect(sql).not.toMatch(/FROM\s*\(\s*SELECT/i)
73 // Single SELECT * FROM logs (not "SELECT *" wildcard usage either).
74 expect(sql).not.toMatch(/SELECT\s+\*/)
75 })
76 })
77
78 describe('getLogsCountQuery', () => {
79 it('emits one UNION ALL branch per log_type bucket and per level', () => {
80 const sql = getLogsCountQuery(baseSearch)
81 // Per-log-type counts
82 for (const lt of ['edge', 'postgrest', 'storage', 'postgres', 'edge function', 'auth']) {
83 expect(sql).toContain(`'${lt}'`)
84 }
85 // Per-level counts
86 for (const lvl of ['success', 'warning', 'error']) {
87 expect(sql).toContain(`'${lvl}'`)
88 }
89 // Bundled via UNION ALL — multiple occurrences expected
90 expect(sql.match(/UNION ALL/g)?.length ?? 0).toBeGreaterThan(5)
91 })
92
93 it('honours an active log_type filter in the total count branch', () => {
94 const sql = getLogsCountQuery({ ...baseSearch, log_type: ['edge'] } as any)
95 // The first branch is the total — its WHERE must include the edge
96 // log_type predicate, otherwise the total badge would over-count
97 // when a log_type filter is active.
98 const totalBranch = sql.split(/\bUNION ALL\b/)[0]
99 expect(totalBranch).toContain(`'total'`)
100 expect(totalBranch).toContain(`source = 'edge_logs'`)
101 expect(totalBranch).not.toContain(`source = 'postgres_logs'`)
102 })
103 })
104
105 describe('getLogsChartQuery', () => {
106 it('uses minute bucketing for short ranges', () => {
107 const sql = getLogsChartQuery(baseSearch)
108 expect(sql).toContain('toStartOfMinute(timestamp)')
109 })
110
111 it('uses hour bucketing for ranges spanning more than 12 hours', () => {
112 const sql = getLogsChartQuery({
113 ...baseSearch,
114 date: [new Date('2026-05-08T00:00:00Z'), new Date('2026-05-08T18:00:00Z')],
115 } as any)
116 expect(sql).toContain('toStartOfHour(timestamp)')
117 })
118
119 it('uses day bucketing for ranges spanning more than 2 days', () => {
120 const sql = getLogsChartQuery({
121 ...baseSearch,
122 date: [new Date('2026-05-01T00:00:00Z'), new Date('2026-05-08T00:00:00Z')],
123 } as any)
124 expect(sql).toContain('toStartOfDay(timestamp)')
125 })
126 })
127
128 describe('getFacetCountQuery', () => {
129 it('groups by the requested facet and excludes that facet from the WHERE filters', () => {
130 const sql = getFacetCountQuery({
131 search: { ...baseSearch, method: ['GET'], status: ['200'] } as any,
132 facet: 'method',
133 })
134 // Filtered facet is excluded from WHERE; other filters still applied.
135 // For facet='method' the SELECT projection doesn't include STATUS_EXPR,
136 // so SQLSTATE/IN ('200') must come from the WHERE clause.
137 expect(sql).not.toContain(`log_attributes['request.method'] IN ('GET')`)
138 expect(sql).toContain(`log_attributes['parsed.sql_state_code']`)
139 expect(sql).toMatch(/END\) IN \('200'\)/)
140 expect(sql).toContain('GROUP BY value')
141 expect(sql).toContain('LIMIT 20')
142 })
143 })
144
145 describe('analyticsLiteral escaping', () => {
146 it('emits ClickHouse / BigQuery escape syntax (doubled `\\\\`, no `E` prefix)', () => {
147 // pg-meta's literal() would emit `E'a\\b'` for `a\b` — the `E` prefix is
148 // Postgres-only and rejected by both analytics engines. analyticsLiteral
149 // doubles the backslash inside plain `'…'` delimiters instead.
150 const sql = getUnifiedLogsQuery({ ...baseSearch, method: 'a\\b' } as any)
151 expect(sql).toContain(`log_attributes['request.method'] = 'a\\\\b'`)
152 expect(sql).not.toContain(`E'a`)
153 })
154
155 it("escapes single quotes by doubling them ('' rather than \\')", () => {
156 const sql = getUnifiedLogsQuery({ ...baseSearch, method: "GET' OR '1'='1" } as any)
157 expect(sql).toContain(`log_attributes['request.method'] = 'GET'' OR ''1''=''1'`)
158 })
159 })
160})
161
162describe('UnifiedLogs.queries.bq', () => {
163 it('backtick-quotes column identifiers (BigQuery syntax)', () => {
164 const sql = getUnifiedLogsQueryBQ({ ...baseSearch, method: 'GET' } as any)
165 expect(sql).toContain("`method` = 'GET'")
166 })
167
168 it('rejects keys with non-identifier characters', () => {
169 // A key like "foo; DROP TABLE" fails the bqIdent regex (the `;` is not
170 // in `[A-Za-z_][A-Za-z0-9_]*`), so the predicate is dropped entirely.
171 const sql = getUnifiedLogsQueryBQ({ ...baseSearch, 'foo; DROP TABLE x': 'y' } as any)
172 expect(sql).not.toContain('DROP TABLE')
173 expect(sql).not.toContain('foo;')
174 })
175
176 it('rejects keys containing spaces', () => {
177 const sql = getUnifiedLogsQueryBQ({
178 ...baseSearch,
179 'level OR id IS NOT NULL': 'anything',
180 } as any)
181 expect(sql).not.toContain('IS NOT NULL')
182 expect(sql).not.toContain('level OR')
183 })
184
185 it('escapes injection attempts in filter values via analyticsLiteral', () => {
186 const sql = getUnifiedLogsQueryBQ({ ...baseSearch, method: "GET' OR '1'='1" } as any)
187 // The value is single-quote-escaped, so the synthetic OR can't break out
188 // of the string literal.
189 expect(sql).toContain("`method` = 'GET'' OR ''1''=''1'")
190 expect(sql).not.toMatch(/`method` = 'GET' OR '1'='1'/)
191 })
192})