PgTidy/docs/plan.md

PgTidy — PostgreSQL Linter & Formatter

Context

We are starting a greenfield project, PgTidy: a PostgreSQL-focused linter and formatter that enforces consistent SQL style, detects PostgreSQL-specific issues, and formats migrations, schemas, functions, procedures, triggers, and related DB code. The primary day-one use case is formatting PL/pgSQL stored procedures to match an existing house style (sample corpus at testdata/corpus).

The core is written in Go. We ship a CLI, then an LSP server that powers a VSCode extension (priority) and later a DataGrip/JetBrains integration (lower priority).

Decisions locked in (from planning Q&A)

• Parsing = hybrid. Real PostgreSQL grammar via go-pgquery (libpg_query compiled to WASM with wazero, no cgo) powers deep semantic lint rules. A custom lossless lexer + CST powers the comment-preserving formatter. (libpg_query drops comments & whitespace, so it cannot be the sole basis for a formatter — confirmed.)
• V1 = Formatter + CLI first. Lint → LSP/VSCode → DataGrip follow.
• Lint scope (later milestones): style/consistency, migration safety (Squawk-style), naming conventions, correctness/anti-patterns — all four.
• Editors: one Go LSP core → VSCode now (bundled per-platform binary), DataGrip via the free LSP4IJ plugin later.

Why this architecture

• No cgo (WASM-embedded libpg_query) keeps cross-compilation trivial and lets us bundle a single static binary per platform inside the VSCode extension. sqlc migrated to this exact approach to escape cgo pain.
• Formatting needs a lossless CST (round-trippable, comments + whitespace preserved). This is the dominant pattern in mature tools (Roslyn, rust-analyzer/rowan, Biome, SQLFluff). We build our own lexer/CST so the formatter never loses a comment.
• Deep lint needs an accurate AST — go-pgquery gives the real PostgreSQL parse tree.
• One core, many frontends — CLI, LSP, VSCode and DataGrip all reuse the same engine and the same diagnostics type.

Proposed layout

pgtidy/
  cmd/pgtidy/            — CLI entry; subcommands: fmt, lint (v2), lsp (v3), version
  pkg/lexer/            — lossless lexer: tokens + trivia (comments/whitespace) attached
  pkg/cst/              — concrete syntax tree (round-trippable node model)
  pkg/parser/           — recursive-descent parser → CST (DML + DDL + PL/pgSQL)
  pkg/format/           — Doc-IR printer (Wadler/Prettier-style) + style application
  pkg/pgast/            — go-pgquery wrapper: SQL → real PG AST (for lint, v2)
  pkg/lint/             — rule engine + rule packs (v2)
  pkg/config/           — .pgtidy.yaml discovery/merge: style + rule config
  pkg/diagnostics/      — shared diagnostic type (CLI + LSP)
  pkg/lsp/              — LSP server (v3)
  editors/vscode/       — VSCode extension (TS), bundles pgtidy binary (v3)
  editors/datagrip/     — LSP4IJ integration (v4)
  testdata/             — golden formatter fixtures + lint fixtures +  corpus

House style (formatter defaults — reverse-engineered from the corpus)

These become the default style config; all are configurable. The corpus had human inconsistencies (e.g. a DECLARE var at column 0, mixed =/:=); the formatter normalizes to the intended style below.

• Keywords UPPERCASE; data types lowercase; identifiers lowercase snake_case.
• Indent: 2 spaces per level.
• Leading-comma style, one item per line, for SELECT column lists and function params.
• Function headers: params one-per-line in parens; LANGUAGE / SECURITY / volatility each on own line; AS then $$ on its own line; body; closing $$; on its own line.
• PL/pgSQL: DECLARE alone, vars 2-space indented; --Block-- comment markers preserved; BEGIN/END at body level; IF/THEN/ELSIF/ELSE/END IF, loops, CASE indent their bodies.
• Spacing: spaces around binary operators (=,<>,||,…) and :=; no space around ::, ->, ->>, array [...], or before a call's (.
• Dollar-quote tags preserved verbatim ($$, $S$, $Z$, …).

DataGrip settings mapping

PostgresCodeStyleSettings (DataGrip / JetBrains) is the reference for all configurable style options. The table below maps every relevant DataGrip key to its .pgtidy.yaml counterpart so a user can reproduce their DataGrip style exactly in PgTidy.

DataGrip enum conventions used below:

• Case: 0=preserve, 1=upper, 2=lower
• Comma: 1=leading (,col), 2=trailing (col,)
• Placement: 1=same_line, 2=new_line
• Wrap: 0=never, 1=when_long, 2=always

Casing

DataGrip key	PgTidy key	Default	Notes
KEYWORD_CASE	keyword_case	upper	SELECT, FROM, WHERE, …
IDENTIFIER_CASE	ident_case	lower	unquoted column/variable names
TYPE_CASE	type_case	lower	built-in type names (text, integer, …)
CUSTOM_TYPE_CASE	custom_type_case	lower	user-defined / domain types
ALIAS_CASE	alias_case	lower	column and table aliases
BUILT_IN_CASE	builtin_case	lower	built-in functions (COALESCE, MAX, …)

Query layout

DataGrip key	PgTidy key	Default	Notes
QUERY_EL_COMMA	commas	leading	applies to all clause element lists
QUERY_ALIGN_ELEMENTS	align_columns	false	align SELECT list items to same column
QUERY_ALIGN_LINE_COMMENTS	align_line_comments	false	align -- inline comments in a block
SELECT_ALIGN_AS	select_align_as	false	align AS keyword across SELECT list
FROM_INDENT_JOIN	indent_join	false	indent JOIN relative to FROM
FROM_ONLY_JOIN_INDENT	join_indent_size	1	extra indent levels for JOINs
SET_ALIGN_EQUAL_SIGN	set_align_equal	false	align = in UPDATE SET list
WHERE_EL_WRAP + WHERE_EL_LINE	where_wrap	always	always | when_long | never — each AND/OR condition on its own line
(no DataGrip equivalent)	where_and_or_indent	true	when true, AND/OR are indented one level under WHERE, not at WHERE's column

Subqueries

DataGrip key	PgTidy key	Default	Notes
SUBQUERY_OPENING	subquery_opening	same_line	opening ( placement
SUBQUERY_CONTENT	subquery_content	new_line	content indentation inside paren
SUBQUERY_CLOSING	subquery_closing	new_line	closing ) placement
SUBQUERY_PAR_SPACE_BEFORE	subquery_space_before_paren	false	space before (

INSERT

DataGrip key	PgTidy key	Default	Notes
INSERT_COLLAPSE_MULTI_ROW_VALUES	insert_collapse_values	true	fold VALUES rows into fewer lines

Routine (function / procedure)

DataGrip key	PgTidy key	Default	Notes
ROUTINE_ARG_COMMA	uses commas	leading	same setting as query lists
ROUTINE_ARG_ALIGN_TYPES	align_param_types	true	align type column in param list
ROUTINE_AS_WRAP	routine_as_wrap	true	newline before AS $$

PL/pgSQL body

DataGrip key	PgTidy key	Default	Notes
IMP_COMMON_KEEP_BLANK_LINES_IN_CODE	plpgsql_max_blank_lines	1	max consecutive blank lines in body
IMP_DECLARE_ALIGN_TYPE	plpgsql_declare_align_type	false	align type column in DECLARE block
IMP_DECLARE_ALIGN_EQ	plpgsql_declare_align_eq	false	align := / = in DECLARE block
IMP_IF_THEN_WRAP_THEN	plpgsql_if_then_newline	true	THEN on its own line
IMP_LOOP_COLLAPSE	plpgsql_loop_collapse	true	collapse empty loop bodies

Expressions

DataGrip key	PgTidy key	Default	Notes
EXPR_BINARY_OP_ALIGN	binary_op_align	false	align =, <>, `
EXPR_CALL_SPACE_AFTER_COMMA	space_after_comma_in_calls	false	space after , in function calls
EXPR_CASE_WHEN_WRAP	case_when_wrap	false	each WHEN on its own line
EXPR_CASE_END	case_end	new_line	same_line | new_line
EXPR_CASE_COLLAPSE	case_collapse	false	collapse short CASE to one line
CORTEGE_SPACE_BEFORE_L_PAREN	record_space_before_paren	false	space before ( in ROW/record constructors

---

Milestones

V1 — Formatter + CLI (priority)

1. Lexer (pkg/lexer): full PG token coverage incl. dollar-quoted strings, -- and /* */ comments, operators. Comments + whitespace captured as leading/trailing trivia on tokens. Acceptance: emit(lex(src)) == src byte-for-byte across the corpus.
2. CST + parser (pkg/cst, pkg/parser): recursive descent for DML (SELECT/INSERT/ UPDATE/DELETE/CTE), DDL (CREATE FUNCTION/PROCEDURE/TABLE/INDEX/TRIGGER, ALTER, DO).
3. PL/pgSQL body parser: DECLARE/BEGIN/END, IF/CASE/LOOP, assignments, nested SQL — the crux for stored-procedure formatting.
4. Printer (pkg/format): Doc-IR (group/indent/line/softline) driven by style config. Graceful degradation — any span the parser can't handle passes through verbatim rather than being corrupted.
5. CLI (cmd/pgtidy fmt): --check, --write/-w, stdin→stdout, --diff; config discovery walking up to .pgtidy.yaml; CI-friendly exit codes.
6. Config (pkg/config): load/merge style config; defaults = house style above. Full field set defined in the DataGrip settings mapping section above — covers casing (6 keys), query layout, subqueries, INSERT, routines, PL/pgSQL body, and expressions.

Safety guarantees (tested): semantic equivalence (re-lex output, compare non-trivia token stream to input), and idempotence (fmt(fmt(x)) == fmt(x)). The corpus is the primary safety/idempotence harness; golden-file tests for targeted cases.

V2 — Linter

• pkg/pgast go-pgquery (WASM) wrapper; pkg/lint rule engine (rule ID, severity, config).
• Rule packs: style/consistency, migration safety (ACCESS EXCLUSIVE locks, unsafe ALTER/ADD COLUMN, non-CONCURRENTLY index builds, blocking constraints), naming (configurable table/column/index/constraint patterns), correctness/anti-patterns (SELECT *, missing WHERE on UPDATE/DELETE, deprecated syntax). Emit diagnostics.
• pgtidy lint subcommand; --fix for autofixable rules.

V3 — LSP + VSCode

• pkg/lsp: textDocument/formatting + range formatting, publishDiagnostics, codeAction quick-fixes — all reusing the core.
• editors/vscode: TS extension using vscode-languageclient, launches bundled pgtidy lsp. Build per-platform VSIX (win32/linux/darwin × x64/arm64) in a CI matrix (rust-analyzer model), with a target-less fallback.

V4 — DataGrip

• editors/datagrip: integrate via LSP4IJ (free, works across JetBrains editions incl. DataGrip). No core changes expected.

Build / repo hygiene

• Replace boilerplate AGENTS.md/CLAUDE.md with PgTidy content; rewrite Makefile (APP := pgtidy, CMD := ./cmd/pgtidy; keep build/test/lint/release-version targets).
• Add goreleaser for the multi-platform binary matrix (clean, since no cgo).
• go.mod: deps = github.com/wasilibs/go-pgquery (v2), wazero, a YAML lib, an LSP lib (e.g. go.lsp.dev/protocol) in v3.

Verification

• Formatter: go test ./... runs golden-file tests + the corpus harness asserting (a) idempotence and (b) token-stream equality before/after (no semantic change). Manual: pgtidy fmt --diff against several procedures; confirm output matches the house style and comments/dollar-quote tags survive.
• CLI: pgtidy fmt --check returns non-zero on unformatted input, zero when clean.
• Lint (v2): fixture SQL with known violations → assert expected diagnostics; --fix round-trips.
• LSP/VSCode (v3): load a .sql file in a dev-host VSCode, confirm format-on-save and live diagnostics via the bundled binary.

Open risks

• A lossless PL/pgSQL recursive-descent parser is the largest single effort; the pass-through-on-unparsed fallback bounds the risk and lets us ship incrementally by construct.
• go-pgquery tracks PG17 (PG18 not yet) — fine for lint; irrelevant to the formatter path.
• Leading-comma + one-per-line is unusual vs. most formatters; it's a first-class style option, not an afterthought.