konnosDashboard
sql-event-parser.ts148 lines · main
1/**
2 * Lightweight SQL parser for telemetry event detection.
3 *
4 * [Sean] Replace this with a proper SQL parser like `@supabase/pg-parser` once a
5 * browser-compatible version is available.
6 */
7import { TABLE_EVENT_ACTIONS, TableEventAction } from 'common/telemetry-constants'
8
9export interface TableEventDetails {
10 type: TableEventAction
11 schema?: string
12 tableName?: string
13}
14
15type Detector = {
16 type: TableEventAction
17 patterns: RegExp[]
18}
19
20export class SQLEventParser {
21 private static DETECTORS: Detector[] = [
22 {
23 type: TABLE_EVENT_ACTIONS.TableCreated,
24 patterns: [
25 /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"(?:[^"]|"")+"|`(?:[^`]|``)+`|[\w]+))/i,
26 /CREATE\s+TEMP(?:ORARY)?\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"(?:[^"]|"")+"|`(?:[^`]|``)+`|[\w]+))/i,
27 /CREATE\s+UNLOGGED\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"(?:[^"]|"")+"|`(?:[^`]|``)+`|[\w]+))/i,
28 /SELECT\s+.*?\s+INTO\s+(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"(?:[^"]|"")+"|`(?:[^`]|``)+`|[\w]+))/is,
29 /CREATE\s+TABLE\s+(?:IF\s+NOT\s+EXISTS\s+)?(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"(?:[^"]|"")+"|`(?:[^`]|``)+`|[\w]+))\s+AS\s+SELECT/i,
30 ],
31 },
32 {
33 type: TABLE_EVENT_ACTIONS.TableDataAdded,
34 patterns: [
35 /INSERT\s+INTO\s+(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"(?:[^"]|"")+"|`(?:[^`]|``)+`|[\w]+))/i,
36 /COPY\s+(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"(?:[^"]|"")+"|`(?:[^`]|``)+`|[\w]+))\s+FROM/i,
37 ],
38 },
39 {
40 type: TABLE_EVENT_ACTIONS.TableRLSEnabled,
41 patterns: [
42 /ALTER\s+TABLE\s+(?:IF\s+EXISTS\s+)?(?:ONLY\s+)?(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"(?:[^"]|"")+"|`(?:[^`]|``)+`|[\w]+)).*?ENABLE\s+ROW\s+LEVEL\s+SECURITY/i,
43 /ALTER\s+TABLE\s+(?:IF\s+EXISTS\s+)?(?:ONLY\s+)?(?<schema>(?:"[^"]+"|[\w]+)\.)?(?<table>(?:"(?:[^"]|"")+"|`(?:[^`]|``)+`|[\w]+)).*?ENABLE\s+RLS/i,
44 ],
45 },
46 ]
47
48 private cleanIdentifier(identifier?: string) {
49 return identifier?.replace(/["`']/g, '').replace(/\.$/, '')
50 }
51
52 // Blank out the body of $tag$...$tag$ blocks (PL/pgSQL function bodies, DO
53 // blocks, dollar-quoted string literals) so their contents aren't scanned for
54 // DDL. A `select ... into var` inside a function body is variable assignment,
55 // not table creation, and would otherwise trip the SELECT..INTO detector.
56 //
57 // The backreference \1 forces opening and closing tags to match, so a nested
58 // inner block with a different tag (e.g. $fn$ containing $sql$...$sql$) is
59 // consumed as part of the outer body instead of being paired as the outer.
60 //
61 // Must run before statement splitting — splitStatements' dollar-quote regex
62 // doesn't enforce matching tags, so inner semicolons would otherwise leak
63 // out and fragment the function body across statements.
64 private stripDollarQuoteBodies(sql: string): string {
65 return sql.replace(/(\$[a-zA-Z0-9_]*\$)[\s\S]*?\1/g, '$1$1')
66 }
67
68 private match(sql: string): TableEventDetails | null {
69 for (const { type, patterns } of SQLEventParser.DETECTORS) {
70 for (const pattern of patterns) {
71 const match = sql.match(pattern)
72 if (match?.groups) {
73 return {
74 type,
75 schema: this.cleanIdentifier(match.groups.schema),
76 tableName: this.cleanIdentifier(match.groups.table ?? match.groups.object),
77 }
78 }
79 }
80 }
81 return null
82 }
83
84 private splitStatements(sql: string): string[] {
85 // Regex matches:
86 // - single quotes ('...') with escapes
87 // - double quotes ("...")
88 // - dollar-quoted blocks ($$...$$ or $tag$...$tag$)
89 // - semicolons
90 // - everything else
91 const tokens =
92 sql.match(
93 /'([^']|'')*'|"([^"]|"")*"|\$[a-zA-Z0-9_]*\$[\s\S]*?\$[a-zA-Z0-9_]*\$|;|[^'"$;]+/g
94 ) || []
95
96 const statements: string[] = []
97 let current = ''
98
99 for (const token of tokens) {
100 if (token === ';') {
101 if (current.trim()) statements.push(current.trim())
102 current = ''
103 } else {
104 current += token
105 }
106 }
107
108 if (current.trim()) {
109 statements.push(current.trim())
110 }
111
112 return statements
113 }
114
115 private deduplicate(events: TableEventDetails[]): TableEventDetails[] {
116 const seen = new Set<string>()
117 return events.filter((e) => {
118 const key = `${e.type}:${e.schema || ''}:${e.tableName || ''}`
119 if (seen.has(key)) return false
120 seen.add(key)
121 return true
122 })
123 }
124
125 private removeComments(sql: string): string {
126 return sql
127 .replace(/--.*?$/gm, '') // line comments
128 .replace(/\/\*[\s\S]*?\*\//g, '') // block comments
129 }
130
131 getTableEvents(sql: string): TableEventDetails[] {
132 // Order matters: strip dollar-quote bodies first so comment syntax inside
133 // a function body (which is just literal text in Postgres) isn't treated
134 // as a comment by removeComments, and so inner semicolons inside the body
135 // can't confuse splitStatements.
136 const statements = this.splitStatements(this.removeComments(this.stripDollarQuoteBodies(sql)))
137 const results: TableEventDetails[] = []
138
139 for (const stmt of statements) {
140 const event = this.match(stmt)
141 if (event) results.push(event)
142 }
143
144 return this.deduplicate(results)
145 }
146}
147
148export const sqlEventParser = new SQLEventParser()