Why sqlfluff Can’t Lint Our SQL Conventions — and a 240-Line Script Can

Published Aug 23, 2026
by

The conventions were settled: procedure skeletons, DECLARE banners, file naming, forbidden constructs — all versioned in rule files, every rule with its rationale. The only open question was which tool should check the SQL conventions. And the first reflex was the same one that probably surfaces in every Postgres team with convention ambitions: “Just use sqlfluff, that’s exactly what it’s for.”

Three real test runs later, the answer was clear: sqlfluff is not the right tool for this checking task. Four of the eight rules are structurally out of its reach, and the remaining four would come at the price of a foreign toolchain. That is not a quality problem of the tool. It is a property of the task. A generic SQL linter checks standard SQL style in parseable SQL. The project conventions in this case, however, live in psql deploy scripts with PL/pgSQL bodies, which is exactly where a SQL parser structurally never looks.

This article documents the tool decision on the real case: the project’s eight check rules matched against sqlfluff 4.3.0, the three structural blind spots behind the result, and the 241-line Node script that checks the SQL conventions instead — running as a blocking CI step since July 2026.

The essentials up front:

  • A formatter is not a convention guard: sqlfluff checks and produces standard SQL layout — as an auto-formatter, this blog recommends it too. Enforcing project-specific conventions in psql scripts is a different task.
  • Three blind spots: project conventions have no built-in rules, psql meta syntax is not SQL, and PL/pgSQL bodies in dollar quoting are a single string literal to the parser.
  • The rule-by-rule result: of 8 check rules, not one gets a ✅ — 4 get a clear ❌️, 4 a ⚠️ that would require a self-written Python plugin.
  • The counter-design: 241 lines of Node without dependencies, 8 rule classes, exactly 1 coded exception, well under 10 seconds across 152 files.
  • The author of the guard: all 241 lines come from Claude Code sessions, and the maintainer is not a Node developer. His input was the rule files, hand-corrected SQL reference files and an acceptance criterion. Three working prompts show the path.
  • The division of labour: the standard layout is checked by a linter (optionally), the project conventions are checked by a small custom script, and correctness is checked by a real apply against a throwaway database.

Prerequisite: The case is PostgreSQL with psql deploy scripts. No sqlfluff experience is required, the tested version is 4.3.0. To rebuild the custom check, Node 20 or later is enough, with no npm install.

Contents

The Reflex: “Just Use sqlfluff”

The setting is DI², an ETL generator built on Next.js and PostgreSQL whose database layer consists of more than 150 DDL files under db/schemas/. Deployment happens without a migration tool, through numbered psql scripts with \ir includes and schema variables. The directory convention behind this is described in Deploying a SQL Schema Without a Migration Tool. These files are governed by a convention rulebook that was derived from the codebase with Claude Code and is versioned in rule files for tables, procedures and functions.

Why compliance has to be checked by machine is something the sibling article 799 hardcoded font sizes measured on the frontend case: a documented convention improves the hit rate of a code-generating agent, but it does not guarantee it. At high generation volume, every residual miss rate turns into measurable drift. That story ended with a custom ESLint rule at error level. For the SQL side, the same enforcement question came up, only with the tool choice still open.

The rules to check look like this:

  • File names follow the pattern NNN.sp_<verb>_<entity>.sql.
  • Forbidden constructs stay out (serial instead of IDENTITY, _at suffixes on timestamp columns, CURRENT_USER in app logic).
  • DECLARE blocks carry banners in the sequence Common → Error Handling → Workload.
  • A -- Parameter documentation block sits between DROP and CREATE.
  • Banner separator lines are exactly -- plus 80 dashes.
  • Every \echo header has its matching - DONE closer.
  • Every CREATE is followed by an ALTER … OWNER TO.
  • Line endings are LF.

And this is what a typical DI² DDL file looks like — shortened, but with everything that matters below: the \echo header, the :schema_variables, the -- Parameter block and the $procedure$ body with DECLARE banners. It is the exhibit for everything that follows:

  1: \echo "## CREATE PROCEDURE :schema_name.sp_check_customer"
  2: 
  3: DROP PROCEDURE IF EXISTS :schema_name.sp_check_customer(text, int);
  4: 
  5: -- --------------------------------------------------------------------------------
  6: -- Parameter
  7: -- --------------------------------------------------------------------------------
  8: --    p_table_name         text
  9: --       Name der zu prüfenden Tabelle
 10: --    p_min_rows           int
 11: --       Mindest-Zeilenzahl, unter der die Prüfung einen Fehler meldet
 12: -- --------------------------------------------------------------------------------
 13: CREATE OR REPLACE PROCEDURE :schema_name.sp_check_customer
 14: (
 15:     IN    p_table_name         text
 16:    ,IN    p_min_rows           int
 17: )
 18: LANGUAGE plpgsql
 19: AS $procedure$
 20: DECLARE
 21:    -- --------------------------------------------------------------------------------
 22:    -- Common
 23:    -- --------------------------------------------------------------------------------
 24:    l_component               text;
 25: 
 26:    -- --------------------------------------------------------------------------------
 27:    -- Error Handling
 28:    -- --------------------------------------------------------------------------------
 29:    l_error_message           text;
 30: 
 31:    -- --------------------------------------------------------------------------------
 32:    -- Workload
 33:    -- --------------------------------------------------------------------------------
 34:    l_row_count               bigint;
 35:    l_sql                     text;
 36: BEGIN
 37: 
 38:    l_component := 'sp_check_customer';
 39: 
 40:    l_sql := format($sql$SELECT
 41:                             count(*)
 42:                         FROM
 43:                             %1$I.%2$I
 44:                    $sql$
 45:       ,'staging'
 46:       ,p_table_name
 47:    );
 48: 
 49:    EXECUTE l_sql INTO l_row_count;
 50: 
 51:    IF l_row_count < p_min_rows THEN
 52:       l_error_message := format($$%1$s: table %2$s has %3$s rows (expected >= %4$s)$$
 53:          ,l_component, p_table_name, l_row_count, p_min_rows);
 54:       RAISE EXCEPTION USING MESSAGE = l_error_message;
 55:    END IF;
 56: 
 57: END;
 58: $procedure$;
 59: 
 60: ALTER PROCEDURE :schema_name.sp_check_customer(text, int) OWNER TO :schema_owner;
 61: 
 62: \echo "## CREATE PROCEDURE :schema_name.sp_check_customer - DONE"

What sqlfluff Does Well

The fair acknowledgement comes first, because this article is not a takedown. sqlfluff is the established SQL linter: it handles many dialects, ships an extensive catalogue of layout and style rules (the rules reference lists them in full) and can be configured in detail. Leading commas, indentation depth, keyword capitalisation — for standard layout questions it is linter and auto-fixer in one tool.

In exactly that role, sqlfluff also appears on this blog. The articles on the functional design aesthetics of SQL, on the editor options in SSMS and on formatting SQL statements recommend it as an auto-formatter, and this article changes nothing about that. A formatter produces and checks standard layout in parseable SQL. A convention guard is a checking script that enforces project-specific conventions: rules that exist in the project’s rulebook and nowhere else. Those are two different tasks, and the tool question of this article concerns only the second one.

Blind Spot 1: Project Conventions Have No Built-in Rules

The catalogue of built-in rules covers what applies across projects: layout, capitalisation, aliasing, structural patterns like SELECT *. A rule “DECLARE blocks carry banners in the sequence Common → Error Handling → Workload” naturally is not in there. Neither is “file names follow NNN.sp_<verb>_<entity>.sql” or “every CREATE is followed by an ALTER … OWNER TO“. Project conventions are, by definition, not part of a generic tool’s delivery.

sqlfluff does anticipate this case. There is a documented plugin API for custom rules, present and working in version 4.3.0. The way there, however, is a Python package with entry-point registration, a custom rule class and access to the syntax tree (the sqlfluff docs call it the parse tree). For a project whose toolchain consists of Node and psql, that means a second language world in the build, complete with a Python version and package management in every CI run and on every developer machine.

That would be acceptable if the syntax tree contributed something for the project conventions. The tree is what separates a linter rule from a regex tool: sqlfluff decomposes every statement into such a tree, and a custom rule can navigate it deliberately. It can, for instance, ask for every column reference or every SELECT without an alias, and it always knows the syntactic context of a match. But that strength only pays off when the checking question is a tree question.

The ESLint case from the font-size article shows when that is true. There, the same enforcement question was decided in favour of a custom rule inside the linter, for three reasons. First, the ESLint rule lives in the same ecosystem as the code it checks: it is written in JavaScript, checks TypeScript and runs in the toolchain the project already has. Second, the ESLint infrastructure with configuration, plugins and CI step existed before the new rule. The rule was one more entry in an established system, and the marginal cost was close to zero. Third, the syntax tree there answers the actual checking question: a hardcoded font size can sit in a string literal, in a template literal or in an object property. A line regex would find either too much or too little, while the syntax tree asks specifically for literal nodes and template parts and knows the context of every match.

For the SQL conventions above, none of the three points holds. The plugin would be Python code in a Node project, so the second language world from the previous paragraph would arrive without any payoff. Above all, the rules are almost all anchored to lines: a banner is a comment line, a parameter block is a comment sequence, a file name is not SQL at all. On patterns like these, a syntax tree gives no better answer than a regex. A sqlfluff plugin for these rules would internally contain the same regexes as a custom script, only wrapped in a foreign toolchain. And for the two blind spots that follow, even the plugin API does not help, because the lines to check never reach the rule layer in the first place.

Blind Spot 2: psql Scripts Are Not SQL

The deploy files are not pure SQL files but psql scripts. \echo writes a progress line into the deploy log, \ir includes files relative to the current script, and :schema_name is a variable that only the psql invocation fills with the real schema name via -v. For the deploy workflow, this is the heart of the matter. For a SQL parser, it is foreign syntax.

The real run against the sample file aborts accordingly:

$ sqlfluff --version
sqlfluff, version 4.3.0

$ sqlfluff lint --dialect postgres 117001.sql

== [117001.sql] FAIL
L:   3 | P:   1 |  PRS | Line 3, Position 1: Found unparsable section: 'DROP
                       | PROCEDURE IF EXISTS :schema_name.sp...'
WARNING: Parsing errors found and dialect is set to 'postgres'. Have you
configured your dialect correctly?
All Finished!

Two details here are more precise than the usual error description suggests. First: the \echo and \ir lines themselves are no longer a parse error in version 4.3.0, the parser tolerates them as an opaque meta-command token. Anyone who finds older reports on the net with an unparsable section right at the \echo will get a milder picture from a current sqlfluff. The lines still do not become checkable, though, because a rule has no access to the content of this token. The echo-pair check rule therefore remains out of reach even for a custom rule. Second: the actual parse breaker is the psql variables. A :schema_name in the middle of a statement cannot be resolved by the Postgres grammar, and the parser bails out at exactly the first such line.

For the variables there is a documented workaround: the placeholder templater with param_style = colon substitutes :schema_name before parsing. The file does indeed become parseable that way. The second run shows what happens then (output shortened around repeated LT01 hits):

$ sqlfluff lint 117001.sql   # mit .sqlfluff: templater = placeholder, param_style = colon

== [117001.sql] FAIL
L:   5 | P:   1 | LT05 | Line is too long (83 > 80). [layout.long_lines]
L:   7 | P:   1 | LT05 | Line is too long (83 > 80). [layout.long_lines]
L:  12 | P:   1 | LT05 | Line is too long (83 > 80). [layout.long_lines]
L:  13 | P:  59 | LT01 | Unexpected line break. [layout.spacing]
L:  15 | P:   7 | LT01 | Expected only single space before parameter. Found '
                       | '. [layout.spacing]
L:  16 | P:   1 | LT02 | Expected indent of 4 spaces. [layout.indent]
L:  16 | P:   4 | LT04 | Found leading comma ','. Expected only trailing near
                       | line breaks. [layout.commas]
L:  60 | P:   1 | LT05 | Line is too long (81 > 80). [layout.long_lines]
All Finished!

Now the default rules flag precisely the project conventions as violations: LT04 reports the leading commas, LT02 the 3-space indentation, LT01 the tabular alignment of the parameters, and LT05 stumbles over the 83-character banner separator lines. None of this is a fault of the tool, and all four points can be configured or disabled. But the direction is remarkable. After the workaround, one would first have to adapt part of the rule catalogue to the project conventions so that the linter stops linting against the project’s own rulebook. And the meta lines that the check rules are actually about remain invisible.

T-SQL readers know the same hurdle from SQLCMD scripts, by the way: :setvar and :r are the same class of meta syntax that a SQL parser does not understand.

Blind Spot 3: Dollar Quoting Is a String to the Parser

The most important check rule of the guard checks the DECLARE banners in the procedure bodies. And exactly these bodies sit behind a closed door for sqlfluff. sqlfluff parse shows the complete $procedure$ … $procedure$ block of the sample file as one single token of type quoted_literal. That is grammatically entirely correct: dollar quoting is a string-literal mechanism in Postgres. From the SQL statement’s point of view, the body of a procedure is a text argument that only the PL/pgSQL interpreter reads at CREATE time.

The consequence can be tested drastically. A deliberately mangled body, say with SELCT instead of SELECT, produces zero findings in the lint run as long as the dollar quotes are closed. Everything between the two $procedure$ markers is, to the linter, the content of a string. That puts the DECLARE banner sequence, the format() block and the entire body structure outside every possible sqlfluff rule — custom plugin or not.

Rule by Rule: Can sqlfluff Check These SQL Conventions?

That completes the three structural findings. Applied to the guard’s eight rule classes, the balance looks like this. ❌️ means “structurally impossible”, ⚠️ means “only as a self-written Python plugin”:

Check rulecheckssqlfluff?Rationale
namingfile name follows NNN.sp_<verb>_<entity>.sql❌️File names are not a subject of linting, what gets checked is file content.
forbiddenforbidden constructs (serial, _at suffix, CURRENT_USER …)⚠️Feasible as a custom rule, the only point with a real syntax-tree advantage — and even that only outside the dollar bodies.
declareDECLARE banner sequence Common → Error Handling → Workload❌️Sits in the $procedure$ body, which the parser sees as a string literal (blind spot 3).
param-block-- Parameter doc block between DROP and CREATE⚠️Comment content remains a regex match even in a custom rule.
banner-formatseparator lines exactly -- + 80 dashes⚠️Likewise a regex in a custom rule, with no syntax-tree gain.
echo-pair\echo header with matching - DONE closer❌️psql meta is not SQL, rules have no access to the token content (blind spot 2).
ownerone ALTER … OWNER TO per CREATE⚠️File-level counting across statements, untypical for linter rules, but buildable.
crlfLF line endings❌️Verified for real: a CRLF file passes with --rules all without a single finding.

The bottom line: not a single ✅. Four rules fail structurally, and the remaining four would be custom-plugin work in a foreign toolchain whose core, except for the forbidden check, would still consist of regexes. At this point, the tool decision was made.

The Custom Guard: 241 Lines, 0 Dependencies

The guard is a single Node script, 241 lines long, with imports exclusively from Node builtins (node:fs, node:path, node:url). It collects all .sql files under db/schemas/, applies the eight rule classes line by line and reports findings in the format file:line [rule] message (see rule doc). Exit code 1 on findings makes it a blocking CI step.

Here is the core in a shortened, meant-to-run form. Shown are the exception pattern and the declare rule — that is, of all rules the one that would be invisible to a SQL parser:

  1: // Gekürzter Auszug aus scripts/check-sql-conventions.mjs (241 Zeilen, nur
  2: // Node-Builtins). Gezeigt: das Ausnahmen-Muster und die zustandsbehaftete
  3: // DECLARE-Banner-Regel — die Regel, die tief im $procedure$-Body liegt und
  4: // für einen SQL-Parser unsichtbar ist.
  5: 
  6: import { readdirSync, readFileSync, statSync } from "node:fs"
  7: import { join, relative, sep } from "node:path"
  8: 
  9: // -----------------------------------------------------------------------------
 10: // Ausnahmen: Set aus "<repo-relativer-pfad>|<regel>" — jede Ausnahme ist eine
 11: // kommentierte Zeile mit Grund. Keine generelle Regel-Abschaltung.
 12: // -----------------------------------------------------------------------------
 13: const EXCEPTIONS = new Set([
 14:   // applied_by im Deploy-Audit-Log ist bewusst CURRENT_USER: es protokolliert
 15:   // den DB-Deploy-Runner (psql-Rolle), nicht den App-User.
 16:   "db/schemas/app/db/data/999.schema_apply_log.sql|forbidden",
 17: ])
 18: 
 19: const findings = []
 20: 
 21: function finding(file, line, rule, message, ruleDoc) {
 22:   const rel = relative(REPO_ROOT, file).split(sep).join("/")
 23:   if (EXCEPTIONS.has(`${rel}|${rule}`)) return
 24:   findings.push({ file: rel, line, rule, message, ruleDoc })
 25: }
 26: 
 27: // -----------------------------------------------------------------------------
 28: // [declare] — DECLARE-Banner-Sequenz Common -> Error Handling -> Workload.
 29: // Zustandsbehaftet: Block eingrenzen, Deklarationen zählen, Schwelle prüfen,
 30: // Banner-Reihenfolge gegen eine dynamische Soll-Sequenz vergleichen.
 31: // -----------------------------------------------------------------------------
 32: const DECL_LINE = /^\s{3}(l_[a-z0-9_]+)\s/
 33: 
 34: function checkDeclareBanners(file, lines) {
 35:   const declStart = lines.indexOf("DECLARE")
 36:   if (declStart < 0) return
 37:   const beginIdx = lines.findIndex((l, i) => i > declStart && l === "BEGIN")
 38:   if (beginIdx < 0) return
 39: 
 40:   const block = lines.slice(declStart + 1, beginIdx)
 41:   const names = block.map((l) => DECL_LINE.exec(l)?.[1]).filter(Boolean)
 42:   const hasCommon = names.includes("l_context") || names.includes("l_component")
 43:   const hasError = names.includes("l_error_message")
 44:   // Scope-Regel: Banner erst ab 6 Deklarationen mit Common- UND
 45:   // Error-Variablen; kleinere Blöcke bleiben bewusst flach.
 46:   if (names.length < 6 || !hasCommon || !hasError) return
 47: 
 48:   const seq = []
 49:   for (const l of block) {
 50:     const banner = /^\s*-- (Common|Error Handling|Workload)$/.exec(l)
 51:     if (banner) seq.push(banner[1])
 52:     else if (DECL_LINE.test(l)) {
 53:       const name = DECL_LINE.exec(l)[1]
 54:       if (name === "l_error_message" || name === "l_error_code") seq.push(name)
 55:     }
 56:   }
 57:   // Soll-Sequenz dynamisch: l_error_code ist optional — eine starre Erwartung
 58:   // würde konforme Objekte ohne ERRCODE false-positiv flaggen.
 59:   const expected = ["Common", "Error Handling", "l_error_message"]
 60:   if (names.includes("l_error_code")) expected.push("l_error_code")
 61:   expected.push("Workload")
 62: 
 63:   if (JSON.stringify(seq) !== JSON.stringify(expected)) {
 64:     finding(
 65:       file,
 66:       declStart + 1,
 67:       "declare",
 68:       `DECLARE-Banner-Sequenz erwartet \`${expected.join(" | ")}\`, gefunden: \`${seq.join(" | ") || "(keine Banner)"}\``,
 69:       "sql.md Gruppierung im DECLARE-Block",
 70:     )
 71:   }
 72: }
 73: 
 74: // Lauf (gekürzt): alle *.sql unter db/schemas/ einsammeln, Regeln je Datei
 75: // anwenden, Findings als `datei:zeile  [regel]  message  (siehe regel-doku)`
 76: // ausgeben. Exit 0 = 0 Findings, Exit 1 = Findings — blockierender CI-Step.

Three decisions carry the architecture:

  • Exceptions are entries, not switch-offs. The EXCEPTIONS set contains pairs of file path and rule, each entry with a comment and a reason. Currently there is exactly one: the audit table of the deploy log may use CURRENT_USER, because there it deliberately records the DB deploy runner. For every other file, the rule stays sharp. The path normalisation in line 22 also makes sure that the same exception key matches on the Windows developer machine and in the Ubuntu CI: Windows delivers backslash paths, and the comparison always uses the / form.
  • The declare rule is stateful. It delimits the DECLARE block, counts the declarations, checks a scope threshold and compares the banner order against a dynamically built expected sequence in which l_error_code is optional. Exactly this state logic is the reason the check is a script and not a grep one-liner (more on that in the FAQ).
  • Findings name the rule doc. Every message ends with a reference to the rule file in which the convention is justified. Whoever sees the error also sees where the rule lives. That holds for human readers just as much as for the agent reacting to the CI failure.

This is what the runs look like — first against the rule-compliant codebase, then against two deliberately violated sample files:

$ npm run check:sql

SQL-Konventions-Check: 0 Findings (152 Dateien geprueft)


SQL-Konventions-Check: 8 Finding(s) in 2 Dateien

db/schemas/app/db/procedure/001.check_customer.sql:1  [naming]  Dateiname '001.check_customer.sql' passt nicht zum Muster NNN.sp_<verb>_<entity>.sql  (siehe .claude/rules/sql.md)
db/schemas/app/db/procedure/001.check_customer.sql:13  [banner-format]  Trennlinie ist nicht exakt `-- ` + 80 Bindestriche  (siehe .claude/rules/sql.md)
db/schemas/app/db/procedure/001.check_customer.sql:15  [banner-format]  Trennlinie ist nicht exakt `-- ` + 80 Bindestriche  (siehe .claude/rules/sql.md)
db/schemas/app/db/procedure/001.check_customer.sql:1  [owner]  1x CREATE, aber nur 0x `ALTER ... OWNER TO :schema_app_owner`  (siehe .claude/rules/sql.md)
db/schemas/app/db/procedure/001.check_customer.sql:5  [param-block]  `-- Parameter`-Dokublock fehlt (gehoert zwischen DROP und CREATE)  (siehe .claude/rules/procedures.md)
db/schemas/app/db/table/001.customer.sql:5  [forbidden]  serial/bigserial/smallserial - PK ist `bigint GENERATED ALWAYS AS IDENTITY`  (siehe .claude/rules/sql.md)
db/schemas/app/db/table/001.customer.sql:7  [forbidden]  Timestamp-Spalte mit `_at`-Suffix - Konvention ist `_on` (created_on, modified_on, ...)  (siehe .claude/rules/sql.md)
db/schemas/app/db/table/001.customer.sql:11  [echo-pair]  Abschluss-`\echo "## ... - DONE"` fehlt  (siehe .claude/rules/sql.md)

The check is wired up as npm run check:sql in the CI workflow, as its own step between ESLint and the unit tests. It keeps the runtime budget of under 10 seconds across the 152 files with ease, because line-based regexes over a few hundred kilobytes of SQL cost practically nothing. Since July 2026 it runs blocking: every push with a convention violation breaks the build.

The operating record since the first run is short. Before its CI debut, the guard found one real violation in the codebase, and it has so far produced a single false positive, which a QA session provoked deliberately with a constructed edge case. No more than two correction rounds on the script have been needed since. Both cases are described in the next section.

A transparency note belongs here: the DI² repository is private, so the complete script cannot be linked publicly. The rule files it checks, however, are public in generalised form: the di2-starter-kit contains the SQL rulebooks for Postgres and MSSQL (sql.md, procedures.md, tables.md and more) to read and fork. The excerpt above, the findings format and these rule files are enough as a rebuild foundation, because the remaining rule classes are built more simply than the one shown. The numbers and runs in this article, though, come from the private DI² project itself, not from the starter kit. The three sqlfluff runs can be reproduced in full regardless: the sample file is printed above in its entirety, and the tested version is 4.3.0. Only the full guard script and the 152-file codebase remain private.

How the Guard Came to Be: Reference, Prompt, Refinement

One transparency statement must not be missing in this cluster: no human typed the script, and the maintainer could not have written it either. His expertise is SQL, data models and the conventions behind them, not JavaScript. All 241 lines come from Claude Code sessions, including the two corrections after QA and review. Even the decision for Node over Bash was proposed and justified by the agent, and the maintainer merely signed it off. His role was a different one: he knew the conventions, could produce rule-compliant SQL by hand, could judge a finding as right or wrong, and made the framing decisions (no new dependencies, no sqlfluff, which files deliberately stay flat). That this division of labour works is less a matter of the tool than of the preparation. The reference existed in two forms, and only both together make a specification from which an agent can derive a check.

Form 1: rules as files. The conventions existed as versioned rule files in the repo (sql.md, procedures.md, tables.md), every rule with its rationale. How such a rulebook comes into being is described in Deriving SQL Conventions with Claude Code. For the guard, these files are the reference text: the script checks nothing that is not written there, and every finding points to the file in which the rule is justified.

Form 2: a rule-compliant codebase as template. Rules in prose leave edge cases open. From how many declarations does a banner belong in the DECLARE block? Does the sequence also apply to trigger functions? What about objects that raise without an ERRCODE? The answers were not in the rulebook but in the code. That is why the codebase was aligned with the rules before the guard: the start was made by files pulled onto the canon by hand (the project_member table with its role and history tables, plus the foundation tables), after which subagents brought the remaining files in line, with the corrected files as template. The prompt of that stage looks like this:

Die Datei db/schemas/app/db/table/004.project_member.sql habe ich von Hand
auf die Konventionen aus .claude/rules/sql.md und tables.md gezogen. Sie ist
ab jetzt die Referenz. Gleiche die übrigen Tabellen-Dateien unter
db/schemas/app/db/table/ daran an: IDENTITY statt bigserial, FK und UNIQUE
als idempotente ALTER TABLE, Trennlinien exakt "-- " plus 80 Bindestriche.
Spaltenlisten und Logik bleiben unverändert. Danach den Apply-Smoke gegen
die Wegwerf-DB fahren und das Ergebnis nennen.

That is the same loop as when the rules were derived: generate, correct by hand, make the corrected file the template. The difference to the first form is that the template is not prose but an example against which every deviation shows up in a diff.

The guard prompt. Only on this foundation was the check itself commissioned. What stands out is what the prompt does not contain: no language, no architecture, no parser question. It names the rule files as the source, the conventions to check, the constraints from the project’s point of view and the acceptance criterion. Everything technical below that was the agent’s business:

Ich möchte, dass die SQL-Konventionen aus .claude/rules/sql.md und
procedures.md automatisch geprüft werden, bei jedem Push im CI und lokal
per npm-Befehl. Prüfen sollen: Datei-Naming, die verbotenen Konstrukte
(serial, _at-Suffix, CURRENT_USER), die DECLARE-Banner-Reihenfolge, der
"-- Parameter"-Block, das Banner-Format, die \echo-Paare, OWNER TO nach
jedem CREATE und LF-Zeilenenden. Die Dateien unter db/schemas/ sind seit
der Angleichung regelkonform, nimm sie als Maßstab für Grenzfälle. Jede
Meldung soll mir Datei, Zeile und die verletzte Regel nennen, damit ich
sie beurteilen kann. Keine neuen Pakete, kein sqlfluff. Schlag mir vor,
womit du das umsetzen würdest, und begründe es. Abnahme: 0 Meldungen auf
dem aktuellen Bestand, und ein absichtlich kaputtes Beispiel wird gefunden.

Three sentences in it carry the load. “Take them as the yardstick for edge cases” moves the decision about thresholds and exceptions to where it had already been made: into the corrected files. “Propose what you would use to build this” leaves the tool choice to the agent, who then proposed Node with builtins, with the reasoning found in the FAQ below (stateful declare rule, identical behaviour on Windows and in CI). And “0 findings on the current codebase” turns the codebase into the test case for the check, without the client having to read the code. That criterion also kicked in immediately on the first run, though differently than expected: a single trigger file carried two bare 80-dash lines without the leading -- . The guard had its first catch before it ever reached CI.

Refining instead of loosening. The two corrections after the review show how such a check is steered in operation, even without understanding the code. QA (another agent session) had constructed an edge case: a rule-compliant object that only declares l_error_message because it raises without an ERRCODE. The check would have flagged it. The maintainer did not need to know where in the script the expected sequence lives, only that the rule in procedures.md permits this object:

Die QA meldet: Eine Prozedur, die ohne ERRCODE raist und deshalb kein
l_error_code deklariert, wird vom DECLARE-Check geflaggt. Laut
procedures.md ist das Objekt aber regelkonform, der Check liegt falsch.
Bitte korrigieren. Wichtig: Die Regel selbst bleibt scharf, eine Prozedur
mit vertauschten Bannern muss weiterhin gemeldet werden. Zeig mir beide
Fälle nach dem Fix im Lauf.

The sentence about the rule staying sharp is the important one. Without it, the obvious solution would have been to loosen the sequence check. With it, the rule stays, and only the expectation becomes more precise: in the code above, that is the spot where expected is built dynamically. The maintainer did not design this solution. He accepted it against the two runs he had demanded.

What the Guard Cannot Do — and Who Does It Instead

Honesty includes the limits. The guard has two deliberate ones and one precondition.

It does not check layout in the narrower sense. Indentation depth, comma position and alignment are not controlled by the script, apart from the banner separator lines. That task stays with a formatter or linter, and for standard SQL without psql meta, sqlfluff would still be the obvious choice there (see FAQ).

And it does not check correctness. Whether the SQL runs at all, whether an \ir include is missing, or whether a procedure compiles against the real schema is nothing a line regex can answer — and, for that matter, no parser either, because blind spot 3 applies to every static check: what sits in the dollar body is judged only by the database at CREATE time. That role is taken by a separate apply smoke in the same project: a dedicated GitHub Actions workflow plays the complete schema into a postgres:17 service container, triggered by a path filter on db/**. How this throwaway-database gate is built is described in GitHub Actions for Postgres Deploys, and the bigger picture is drawn by the hub Database CI/CD with PostgreSQL.

The precondition concerns the line-based approach itself. Line-based regexes only detect convention violations reliably as long as the codebase is canonically formatted. That is exactly what the alignment before the guard was for (see the origin section above), and the guard has kept this canon locked in CI ever since. An object that deviates strongly from the skeleton in structure could still slip past a rule. Such outliers are caught by the apply smoke and by code review.

That leaves the division of labour this article offers as its take-away:

Checking taskToolWhy
Standard layout (commas, indentation, keywords)linter/formatter such as sqlfluff — optionalGeneric rules exist ready-made, parseable SQL provided.
Project conventions (banners, naming, forbidden constructs, pair rules)small custom scriptAnchored to lines, no parser needed, no foreign toolchain.
Correctness (does the schema apply?)real apply against a throwaway databaseWhether SQL is executable, only a database knows.

Whoever wants to force all three tasks into one tool ends up with custom-rule plugins that internally contain almost the same regexes as the script they were meant to avoid. And correctness still goes unchecked.

FAQ

When is sqlfluff the right choice after all?

Whenever the checking task is standard SQL style in parseable SQL: query collections, views, reporting SQL or dbt projects, for whose Jinja templating sqlfluff ships its own templater. Team-wide formatting standards are its territory too, and as an auto-formatter, the formatting articles of this blog recommend it explicitly. In the DI² project itself, sqlfluff remains noted as a documented future option for the standard-layout share.

Why Node and not Bash with grep?

Internally the effort started under the name “grep-based check”, and for most of the eight rule classes grep would indeed be enough. The declare rule is the reason for the script: delimiting the block, counting declarations, checking a threshold and comparing a dynamic expected sequence — this state logic is fragile in grep/awk pipelines and, on Windows developer machines, shell-dependent on top. The Node script runs identically on Windows and in the Ubuntu CI, and the project’s toolchain is Node anyway. Zero new dependencies was the condition, and Node builtins meet it.

Why not write sqlfluff custom rules in Python?

The plugin API exists and works, that was verified for real in version 4.3.0. But half of the rules would remain out of reach even then: file names, psql meta lines and dollar bodies never reach the rule layer, and the line-ending check practically drops out because even the standard catalogue lets CRLF files pass without a finding. The remaining rules would be regexes in a Python package with entry-point registration — more infrastructure for less coverage, in a language that appears nowhere else in the project. In a project with a Python toolchain and sqlfluff already running, the trade-off would come out differently. Then the same three criteria apply that spoke for the ESLint custom rule in the frontend case.

How do you keep the exception list from growing wild?

Through friction in the right place. An exception is an entry of file path and rule with a mandatory comment and reason, it applies to exactly one file and exactly one rule, and it goes through the same code review as any other change. Disabling a rule project-wide is not part of the pattern. After 152 checked files, the list stands at a single entry. The threshold of “deliberate, justified individual decision” is holding so far.

Does the pattern work for SQL Server too?

Yes, with the same blind spots. SQLCMD deploy scripts use the same class of meta syntax with :setvar and :r, which a SQL parser does not understand. T-SQL procedure bodies do not sit in dollar quotes, but the convention questions are the same: naming, doc blocks, pair rules. A line-based script is just as viable there, only the regexes change.

Upstream:

Convention spokes:

CI/CD bridges:

Hub: