# 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 `=`, `<>`, `||`, … vertically in WHERE/expression lists; default false — must not be hardcoded |
| `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.