Deriving SQL Conventions with Claude Code — the Generate-Refine-Derive Loop

An AI writes you a stored procedure in seconds. It compiles, it runs – and it looks like a stranger wrote it. The next artifact looks different again. Generated SQL is technically correct but stylistically arbitrary – and without an enforced convention, a generated collection drifts apart just like one five developers write by hand. The obvious fix – reformatting every artifact by hand – doesn’t scale.

This article shows the way out as a repeatable workflow: the Generate→Refine→Derive loop. Instead of correcting the AI on every artifact, you correct it once by hand, let Claude Code analyze the difference between its output and your hand-edited version, and derive an explicit rule from it. That rule then lives in a .claude/rules/ file and kicks in automatically on the next generation. This is how deriving SQL conventions beats enforcing them file by file – one manual correction becomes a permanent convention.

What you’ll take away:

• the loop in six steps – Generate, Refine, Derive, Codify, Enforce, Repeat – and where the human works versus the AI;
• how to have Claude Code analyze the difference between generated and hand-corrected code and turn it into reusable rules;
• when you have to spell something out for the AI that it can’t reliably infer from any single diff – using parameter order in a procedure signature as the example;
• why the manual refine step doesn’t just produce the rule but forces the close reading that surfaces substantive bugs.

Prerequisite: Claude Code (or a comparable AI coding agent with project rule files), a working knowledge of PL/pgSQL. For the overview of the three levers – rules, skills, agents – see the hub article AI-Assisted SQL Development with Claude Code; this piece drills into the workflow behind the rules.

Contents

• The problem: generated SQL without a convention
• The loop in six steps
• The loop on a single example
• When you have to give the AI a rule
• Why the manual step pays off
• Summary
• FAQ
• Related articles

The problem: generated SQL without a convention

An AI agent produces working code – but not your code. It names a procedure update_project where your house style calls for sp_upd_project; it indents a JOIN differently from the last one; it builds the error message right into the RAISE where your convention puts it in a variable. Each artifact is readable on its own – at scale the stylistic variety becomes a search-and-review problem: 50 generated procedures in five styles are as hard to search as 50 hand-written ones in five styles.

There are three possible reactions, and only one takes the repeat work off your plate for good:

1. Live with it. The code turns inconsistent, review and grep suffer. Exactly what conventions are meant to prevent.
2. Reformat every artifact by hand. It works, but it costs the same handwork on every artifact. The AI saved you nothing – it just moved the work around.
3. Derive the convention once and hand it to the AI as a rule. You correct one artifact by hand, derive the rule from it, and drop it into a .claude/rules/ file. From then on the agent generates convention-compliant code – the handwork happens exactly once.

The third path is the Generate→Refine→Derive loop. It shifts the convention from “human discipline on every artifact” to “the default when generating” – and at the point where the code is born it’s cheapest to enforce. The loop isn’t the only source of conventions, though: coding standards, architectural constraints, and review comments are others. It covers specifically the implicit rules that only surface when you bring a generated artifact into shape by hand.

The loop in six steps

The sequence is always the same, whether it’s a table, a function, or a procedure:

1. Generate – the AI produces the SQL artifact from your request.
2. Refine – you correct it by hand into the state you actually want (naming, alignment, ordering, error handling).
3. Derive – you have Claude Code compare its original output with your hand-edited version and derive the rule(s) from it – each rule with a rationale.
4. Codify – the derived rule moves into the matching .claude/rules/<type>.md file (e.g. procedures.md for procedures).
5. Enforce – on the next artifact the agent loads the rule automatically as a project instruction and generates compliant code.
6. Repeat – every new edge case you correct by hand sharpens the rule set by one more rule.

The decisive step is Derive: instead of phrasing the rule yourself, you let the AI extract it from your own edit. That’s faster, and it forces precision – a rule that can be derived from a concrete before/after is automatically backed by an example.

The loop on a single example

Take a simple procedure: it should update a project’s name. The request to the AI is just “write me a procedure that changes the project name”.

1. Generate — the AI’s output

  1: create or replace procedure update_project(p_name varchar, p_id bigint)
  2: language plpgsql as $$
  3: begin
  4:   update project set name = p_name, modified_on = now() where id = p_id;
  5: end; $$;

  1: create or replace procedure update_project(p_name varchar, p_id bigint)
  2: language plpgsql as $$
  3: begin
  4:   update project set name = p_name, modified_on = now() where id = p_id;
  5: end; $$;

It runs. But it isn’t your style: lowercase, no type prefix in the name, no alignment, the parameters in the order “attribute first, identifier second”, everything on one line.

2. Refine — your hand-edited version

You correct it into the shape you want:

  1: CREATE OR REPLACE PROCEDURE app.sp_upd_project
  2: (
  3:     IN p_id     bigint
  4:    ,IN p_name   varchar
  5: )
  6: LANGUAGE plpgsql
  7: AS $procedure$
  8: BEGIN
  9:    UPDATE app.project T01
 10:    SET
 11:        name        = p_name
 12:       ,modified_on = now()
 13:    WHERE
 14:       T01.id = p_id;
 15: END;
 16: $procedure$;

  1: CREATE OR REPLACE PROCEDURE app.sp_upd_project
  2: (
  3:     IN p_id     bigint
  4:    ,IN p_name   varchar
  5: )
  6: LANGUAGE plpgsql
  7: AS $procedure$
  8: BEGIN
  9:    UPDATE app.project T01
 10:    SET
 11:        name        = p_name
 12:       ,modified_on = now()
 13:    WHERE
 14:       T01.id = p_id;
 15: END;
 16: $procedure$;

Six things changed: uppercase keywords, a prefix + verb code in the name (sp_upd_), a schema-qualified name, a table-aligned signature with a leading comma, a positional alias T01 – and the parameter order is flipped: the identifier p_id now comes first.

3. Derive — Claude extracts the rules

Instead of phrasing those six points as rules yourself, you give Claude Code the task:

“Compare the procedure you generated with my revised version. List the differences and turn them into reusable rules for future procedures – each rule with a short rationale.”

Most of it the agent reads reliably from the diff: keywords uppercase, sp_<verb>_<entity> as the name, qualify the schema, signature table-aligned with a leading comma, positional aliases. Those are mechanical patterns visible in the before/after.

4. Codify — the rule joins the rule set

The derived rules move into .claude/rules/procedures.md. Here is what the parameter-order entry looks like there, as an actual file:

## Parameter order (identifier first)

**Rule:** The identifier parameter (p_id) always comes first in the
signature - before the attribute parameters.

**Rationale:** WHERE id = p_id identifies the row first, then its
attributes are read or set. Also applies to INSERT, where id is returned
as INOUT - "identifier first" takes precedence over "inputs before outputs".

**Do:**    sp_upd_project(IN p_id bigint, IN p_name varchar)
**Don't:** update_project(p_name, p_id)

## Parameter order (identifier first)

**Rule:** The identifier parameter (p_id) always comes first in the
signature - before the attribute parameters.

**Rationale:** WHERE id = p_id identifies the row first, then its
attributes are read or set. Also applies to INSERT, where id is returned
as INOUT - "identifier first" takes precedence over "inputs before outputs".

**Do:**    sp_upd_project(IN p_id bigint, IN p_name varchar)
**Don't:** update_project(p_name, p_id)

The triple rule → rationale → do/don’t is no accident: the rationale line tells the agent the why, so it can carry the rule over to cases the example doesn’t show literally. And here the rationale already carries the edge case (INSERT with INOUT) – exactly the part the agent will need in a moment.

5. Enforce — the next attempt lands

Ask the agent in the next session for an insert procedure for the same project, and the identifier moves to the front on its own – even though here it’s returned as INOUT:

  1: CREATE OR REPLACE PROCEDURE app.sp_ins_project
  2: (
  3:     INOUT p_id     bigint
  4:    ,IN    p_name   varchar
  5: )

  1: CREATE OR REPLACE PROCEDURE app.sp_ins_project
  2: (
  3:     INOUT p_id     bigint
  4:    ,IN    p_name   varchar
  5: )

Here, though, the diff alone no longer suffices.

When you have to give the AI a rule

In the refine step above, the agent derived “identifier first” from one example. But a single before/after doesn’t reveal why the identifier comes first – it could be coincidence, alphabetical order, or a quirk of this particular update case. The INSERT case makes it tangible: there p_id is an output parameter (INOUT, the newly assigned key is returned). The usual rule of thumb “inputs before outputs” would put it last. The house rule says the opposite: “identifier first” takes precedence over “inputs before outputs”.

The AI can’t reliably derive this decision from the single update diff – in the artifact it simply isn’t visible. A model can certainly form a hypothesis (“maybe identifier first?”), but a guessed hypothesis is not a reliable rule. Here you need targeted manual input: you give the agent the rule together with its rationale and edge case explicitly, instead of hoping it guesses.

That’s the general point behind the concrete example: some conventions are design decisions whose rationale isn’t in the code. A diff shows the what, but not always the why – and without the why, the agent can’t generalize. Typical candidates for this kind of targeted input:

• Orderings that follow a logic (identifier before attributes, checks before mutations) – the logic has to be named, otherwise the order looks arbitrary.
• Deliberate departures from a common rule of thumb (here: “identifier first” beats “inputs before outputs”) – the agent knows the common rule and would otherwise apply it.
• Naming with a domain background (a table is called account instead of user because USER is a reserved keyword) – that can’t be inferred from the name alone.

Parameter order is, on purpose, a small example – precisely because “the why isn’t in the diff” shows up most clearly on a simple case. For weightier conventions – error handling, tenant filters, audit columns, transaction boundaries – the same pattern applies, just with higher stakes.

And that brings us to the limits of the approach: the diff loop isn’t the source of every rule. Architectural decisions, security and performance requirements, or business rules come from requirements, architecture, and review – not from a formatting diff. The loop covers the implicit style and structure conventions that surface during hand-correction. And not every correction deserves a rule: codify every single case and you get rule sprawl and contradictions – codify only what recurs.

Why the manual step pays off

You could object: if a rule file enforces the style in the end anyway – why correct by hand at all, instead of handing the AI all the rules up front and skipping the refine step?

Because the manual refine step creates an opportunity: to actually read the artifact. Note: not the reformatting itself – turning UPDATE project into UPDATE app.project T01 teaches you nothing about the domain. It’s the close reading the refine step forces on you, during which you notice that the permission check sits after the mutation, that a JOIN returns too many rows, that a parameter is missing. An agent you blindly hand a finished rule set generates pretty code – but you skipped that scrutinizing read. Generated code without understanding is the real risk: technically correct SQL that still doesn’t answer the business question.

It’s the same insight as formatting SQL by hand – formatting is understanding. The loop just makes it productive: the close reading happens during refine, and the by-product – the derived rule – means you don’t make the same correction by hand a second time. You read closely once, and the agent repeats the result very reliably – as long as the rule stays in its context and doesn’t collide with another.

Worth keeping: The Generate→Refine→Derive loop is available as a compact checklist to download – including a generalized starter excerpt for your first .claude/rules/ file. Drop it into your repo, and the next generated SQL comes out in your house style from the start.

Summary

AI agents produce working but stylistically arbitrary SQL. Reformatting it by hand on every artifact doesn’t scale; deriving the convention once and handing it to the AI as a rule does. That’s exactly what the Generate→Refine→Derive loop delivers for the implicit conventions that surface during hand-correction.

Take-away:

• The loop: Generate (AI) → Refine (human corrects) → Derive (AI extracts the rule from the diff) → Codify (rule into .claude/rules/) → Enforce (agent generates compliant code) → Repeat.
• Derive instead of phrasing it yourself: have Claude Code analyze the difference between its output and your hand-edited version – the derived rule is automatically backed by an example.
• The triple rule → rationale → do/don’t is what makes a rule usable for an agent; the rationale line is the bridge it generalizes over to new cases.
• Targeted manual input is needed wherever a rule has a why that isn’t visible in the artifact – such as the parameter order “identifier first”, which takes precedence over “inputs before outputs” even for an INSERT with an INOUT return.
• The refine step forces close reading, not just layout – exactly the scrutiny that generated code otherwise skips.
• Limits: not everything comes from diffs (architecture, security, business rules), and not every correction deserves a rule – otherwise rule sprawl.

FAQ

Related articles

AI-assisted development:

• AI-Assisted SQL Development with Claude Code — Rules, Skills and Agents That Enforce Conventions (overview/hub)
• SQL Conventions // PL/pgSQL Procedures You Can Still Read in Two Years (case study: a complete rule set)

SQL structure & formatting:

• Formatting SQL Statements (Part 1) — Identifiers, Delimiters, Commas, Aliases
• Formatting SQL Statements (Part 2) — Statement Structure: SELECT, WHERE, FROM, JOIN