PostgreSQLBubble query guard: naive quote/paren counting rejects valid SQL (comments, string literals, reused $N)

[zhubzy]

Summary

PostgreSQLBubble's query validation (validateParenthesesBalance + the placeholder count in packages/bubble-core/src/bubbles/service-bubble/postgresql.ts) is a character-level count with no lexical awareness. It rejects perfectly valid SQL, and because it throws at run time (not at flow-edit validation), the failures land in production webhook runs. This keeps biting in real flows — three distinct field incidents so far.

Field incidents

1. SQL comments with apostrophes → Unbalanced single quotes in query (2026-07-06, broke the OGC dev Member Directory webhook, failure-notification email). A -- ... every community's roster sync ... comment contains an odd number of apostrophes; the guard counts them as quote delimiters. Any prose comment with a possessive breaks the flow — silently at edit time, loudly at run time.
2. Reused $N placeholder → false Parameter count mismatch. (query.match(/\$\d+/g) || []).length counts occurrences, not distinct parameters — a query using $5 twice with 5 params reads as "6 placeholders, 5 parameters". Forces authors to compute values in JS and pass duplicate params.
3. Parens/$ inside string literals miscounted, e.g. WHERE x = 'a :) smiley' trips paren balance, and regexp/text containing $ trips the string-literal warning. Also blocks legitimate regexp_replace/|| usage per the dangerous-pattern list.

Root cause

for (const char of query) {
  switch (char) {
    case "'": singleQuoteCount++; break;   // counts comment/apostrophe chars as delimiters
    case '(': parenCount++; break;         // counts parens inside string literals
    ...

No tracking of whether the scanner is inside a '...' literal, a -- ... line comment, or a /* ... */ block comment; no handling of '' escapes.

Proposed fix

One small single-pass tokenizer shared by all the checks:

• Track state: inSingleQuote (with '' escape), inLineComment (until \n), inBlockComment (until */), and only count parens/placeholders/dangerous patterns outside those states.
• Quote balance = "did we end the scan still inside a string", not char parity.
• Placeholder check = compare parameters.length against max distinct $N index found outside strings/comments (and allow reuse).
• Alternatively: strip comments + string bodies first, then run the existing checks over the residue.

This keeps the injection-guard intent while eliminating the false positives. Happy to provide the failing queries from the incidents above as test cases — postgresql.test.ts already exists next to the implementation.