DEV Community: Dexterlung

spec-sonar: The Complete Design Record (Part 1) — From One Idea to an Open-Source Toolchain

Dexterlung — Wed, 05 Aug 2026 13:05:13 +0000

This is one long conversation — about 40+ exchanges — in which I went from a very vague idea to the initial design of an open-source toolchain called spec-sonar. Part 1 ends where I hand 13 review questions to Fable 5.

Part 2 covers the next 24 hours: the review results, 25 files shipped, and a plot twist I never saw coming — the tool catching conflicts between my own two open-source repos.

Read on: Part 2 — Fable's review and a real-world conflict · 繁體中文版 · this tool is listed in my open-source toolkit index.

1. The starting point: an intuition

The first thing I typed into Claude was this:

"I'm wondering whether I can use the standard body of software-engineering knowledge, Claude Code's round-based questioning, and a well-designed skill to help people new to AI-assisted development turn ideas into reality — confirm the safety boundaries, set design limits to prevent scope creep, settle the architecture, and finally produce a series of execution playbooks or goals…"

Several key intuitions were already in that sentence:

The concept of dark zones and bright zones — use set difference to find the questions worth asking.
Convergence, not generation — the goal is fewer rework cycles, not faster output.
Complexity loaded on demand — I decide how deep to go, instead of getting everything at full depth.

2. First turning point: interrogate before designing

I forced myself to answer one question first: is this reinventing the wheel?

What the search turned up:

Requirements-management tools for PMs/BAs exist, but they assume you already understand User Stories.
Spec-driven tools for developers exist, but they assume a spec draft already exists.
Claude Code's CLAUDE.md ecosystem exists, but nobody generates specs for non-technical users.

Conclusion: not a reinvented wheel — but the differentiation has to be explicit. The real difference:

Existing tools' route: make the AI smarter so it tolerates vague input
spec-sonar's route:    make the user's input itself precise

The latter is sturdier, because it doesn't depend on a model's tolerance for ambiguity — and that tolerance is exactly where hallucination comes from.

3. Second turning point: reframing the value proposition

I first wrote the value proposition as "reduce design rework." That was wrong.

The problem: a non-technical user can't perceive that value — they don't know how many detours they would otherwise have taken, so they can't feel the detours they avoided.

The corrected value proposition: "reduce the anxiety of landing something vague."

That's trust, not efficiency. What the user actually feels is: "I told it what I wanted, it told me the problems I hadn't thought of, and what got built matches what I had in mind."

This reframe shaped every later decision:

The tone of the Q&A — assistance, not interrogation.
How infeasibility is handled — not rejection, but shrinking to a feasible version.
Showing bright/dark-zone progress every round — the core trust-building mechanism.

4. Core system-design decisions

4.1 A behavioral contract, not roleplay

My first idea was to inject a "you are a senior architect" persona prompt.

The problem: an identity is not a set of behavior rules. Claude plays the architect but doesn't know what this architect must do, must not do, or when to stop.

So I switched to injecting a behavioral contract:

Inject a behavioral contract, not an identity:
"Your job is to converge a software product idea into an executable spec.
 You are not a roleplaying architect.
 You are a system with fixed rules of behavior."

This made the system's behavior predictable, testable, and iterable.

4.2 STATE: externalizing conversational memory

The problem: there is no persistent memory between rounds; by round 4 the model has forgotten what round 1 established.

My fix was to externalize the conversation memory into a portable JSON block:

<STATE>
{
  "round": 2,
  "bright": ["established items"],
  "dark": ["unconfirmed items"],
  "next_focus": "next round's topic"
}
</STATE>

Key principles:

Wrap it in an XML tag — more stable than plain text; Claude parses its own XML output more reliably.
The next_focus field forces each round onto a single topic, preventing scattershot questioning.
STATE_FINAL.json is portable — you can leave mid-process and resume later with full state.

4.3 The baseline problem of the set-difference method

Doing gap analysis, I found a hole:

Set difference only works if I already hold a standard template of "what a complete system looks like" to diff against. No baseline, no difference.

The solution is a dark-zone-baseline.md: 10 standard dimensions that seed every new session's dark list — target users, core feature boundary, data model, auth & permissions, third-party integrations, deployment platform, performance & scale, maintenance & updates, budget & timeline, success criteria.

The most important detail: the Session-vs-Persistent fork.

I only discovered this after running the real test case (a math tower-defense game). The "do we need an account system?" decision had been back-derived from "does the data persist?", rather than asked proactively. The fork has enormous architectural impact, so I promoted it to a mandatory proactive question.

5. The real test: a math tower-defense game

Why this case: a non-technical user (with an engineer partner) wanted to land "Plants-vs-Zombies tower defense × middle-school math × in-class team battle." I used it to stress-test my tool.

What ran: the full 5-round convergence, from "I don't know what to build" all the way to a complete README.md + CLAUDE.md + STATE_FINAL.json.

Key observations:

The resource mechanic was the highest-value discussion. That user raised the real classroom pain point — "a team-captain system creates conflict between teammates." The final design: personal earn, personal spend, plus per-unit cooldowns — individual effort converts directly into individual agency, with no middleman to skim.
Wheel-reinvention detection confirmed the differentiation. Single-player math tower-defense games exist; "whole class online + team battle + teacher console + realtime multiplayer" does not.
The dark zones cleared after 6 rounds — but one was never proactively probed. The Session-vs-Persistent fork emerged by inference from the "no accounts" answer, not from the baseline. That bug directly drove the baseline fix.

The test case's role: the math game is not spec-sonar's target scenario. It's a deliberately hard stress test — WebSocket realtime sync, a game engine, multiple dependency types, ephemeral-session design. Only that level of complexity genuinely probes the system.

6. Third turning point: from a tool to a toolchain

The original design was a single requirements-convergence skill (idea-to-spec). It kept growing:

idea-to-spec (requirements convergence)
    ↓ problem found: CLAUDE.md outputs modules, but no dependencies
goal-decomposer (goal decomposition)
    ↓ problem found: only works from zero; can't diagnose existing projects
Audit Mode (existing-project diagnosis)
    ↓ problem found: large complex systems (ERP) have different needs
Complex System Mode
    ↓ problem found: Claude-Code-only is too narrow
Adapters layer (platform-agnostic universal format)

The most consequential evolution: from Claude-specific to platform-agnostic.

Core spec layer (universal):  goal-graph.json + goals/*.md
Adapter layer (per platform):
  adapters/CLAUDE.md               ← Claude Code
  adapters/.cursor/rules           ← Cursor
  adapters/copilot-instructions.md ← Copilot
  adapters/system-prompt.md        ← any AI agent

That decision turned spec-sonar from "a Claude Code tool" into "a general requirements-engineering tool for the whole AI-coding ecosystem."

7. The philosophy: one expensive deep design → cheap structured execution

The core philosophy of the whole toolchain, surfaced while I was working out how goal-decomposer integrates with goal-workflow-designer.

The background problem is concrete: not everyone can afford Fable-tier model costs.

My insight:

Fable (expensive) thinks deeply once
    → the thinking is frozen into a goal graph and individual goal files
    → any model (Haiku, Sonnet) executes by following the graph
    → Fable-tier reasoning is no longer required

The analogy is compilation:

Fable = the compiler that does the complex reasoning
goal-graph.json = the compiled executable
any model = the CPU that runs it

The smartest (and most expensive) model appears only once, at design time, to settle all the hard decisions; everything after that runs literally on cheap models.

8. Naming: spec-sonar

I considered SpecForge, DarkMap, ConvergeKit, and ReqSonar. I picked spec-sonar because it passed three tests:

Searchability: no same-name repo on GitHub.
Explanation cost: "like sonar, it finds what's submerged in your spec" — one sentence.
Works in both languages: "I sonar-scanned the requirements" reads naturally.

The logic behind the name: sonar's core mechanism is using waves you can't see to find things you can't see. spec-sonar's core mechanism is using questions you didn't ask to find requirements you didn't think of. Form matches content.

9. Compatibility principles (the last major decisions)

Compatibility with existing CLAUDE.md files. spec-sonar is a design-time tool; CLAUDE.md is a build-time artifact — naturally on different timelines. So spec-sonar installs non-destructively: it never overwrites your existing CLAUDE.md, it only appends one reference line.
Conflict detection across existing skills. I upgraded the goal from "avoid conflicts" to "conflict as a feature": spec-sonar's Conflict Analysis Mode reads all your installed skills and outputs a conflict map. That turns it from "yet another skill" into "a governance tool for your skill ecosystem."
Load on demand: three install tiers — Lite (1 skill) for small projects/MVPs/anything finishable in a week; Standard (2 skills) for most projects under 3 months; Pro (full suite) for enterprise/ERP/long-term maintenance.

10. The role of Fable 5

On 2026-06-09 Anthropic released Claude Fable 5 — the first public model of the Mythos family. Its edge: "the longer and more complex the task, the wider the lead," with a particular strength in one-shotting a complete design.

My prompt strategy wasn't "what do you think of this design?" — it was "under what conditions does this design break, and fix it."

I asked Fable to do five things:

Review the CLAUDE.md format from an executor's perspective ("could I start work from this spec?").
Find the dark-zone baseline's systematic omissions per product type.
Design STATE's conflict handling (retracted bright items, contradictions, late additions).
Design the complete goal-decomposer SKILL.md (dependency inference, model-tier assignment).
Design the adapter format for every platform.

11. This design was designed with spec-sonar

The most meta observation worth recording: this very design started from "I have a vague idea" and went through scope judgment, bright/dark separation, set-difference questioning, infeasibility detection (none found), complexity calibration, and wheel-reinvention detection — and that whole flow is spec-sonar's own flow.

The tool's first user was the person designing the tool.

12. Final output list

By the end of the conversation:

idea-to-spec-v1.1.zip
├── SKILL.md                      the convergence engine
├── references/
│   ├── dark-zone-baseline.md     10 dimensions (with the Session-vs-Persistent fix)
│   └── output-templates.md       three output format templates
├── examples/
│   ├── README.md                 math tower-defense case (human-readable)
│   ├── CLAUDE.md                 math tower-defense case (executable spec)
│   └── STATE_FINAL.json          final convergence state
└── docs/
    ├── fable-review-prompt.md    the full Fable 5 review prompt (13 questions, 5 jobs)
    └── spec-sonar-README.md      open-source README draft

Pending Fable 5's reply: goal-decomposer/SKILL.md, project-scanner.py, the adapters/ directory (4 platforms), audit-mode, Conflict Analysis Mode, the tiered INSTALL.md.

(Every item on that list ships in Part 2 — plus three spec holes, one schema self-audit, and a real-world conflict analysis spanning two open-source repos.)

13. Reusable thinking patterns (Part 1)

Interrogate before designing. Every new idea first answers "is this a reinvented wheel?" — with search results, not gut feeling.
Dark zones matter more than bright zones. Stated requirements are rarely the problem; unstated assumptions are the root of failure.
Behavioral contracts beat roleplay. Tell the AI its rules of behavior for this session, not who it is.
Externalized state solves context loss. Multi-round tools must carry state explicitly every round.
Find design gaps with real cases. After the theory, run one real case and let the gaps surface (that's how the Session-vs-Persistent fork was found).
Expensive thinking once → cheap structured execution. The cost-optimal AI strategy.
Universal core + per-platform adapters. The more universal the core format, the longer the tool lives.
Load on demand. Tiered installation lets users choose their complexity instead of paying full cost by default.

Coming in Part 2

Part 1 ends with 13 questions packed into a review prompt and handed to Fable 5. Part 2 records what happened next:

Fable reviews the test case as an executor and finds three day-one landmines in a seemingly complete spec.
The tool audits its own output and catches its own schema drift.
The full goal-decomposer design: dependency inference, contract freezing, pre-adjudication, the cold-start test.
25 files shipped as a complete open-source package.
Then the real plot twist: installed into a production workspace, the Conflict Analysis Mode finds 5 conflicts between my own two open-source repos — and one structural problem nobody had seen.

→ Continue to Part 2

本文原載於我的部落格：spec-sonar: The Complete Design Record (Part 1) — From One Idea to an Open-Source Toolchain

Audio Plays on Desktop but Not on iPhone / iPad — The Culprit Is the MP4 moov Atom

Dexterlung — Sun, 02 Aug 2026 13:05:25 +0000

Series: Engineering gotchas

I put a batch of podcast audio files (.m4a) on Cloudflare R2 and embedded them with a plain <audio> tag. Desktop Chrome and Firefox played them perfectly. But on iPhone and iPad, they wouldn't play at all — tap the button, nothing happens, no error message, console completely clean.

The most maddening part: it throws no error. You have no idea where to even start looking.

This is how I tracked it down to the root cause, and the one-line fix at the end. If you googled "audio plays on desktop but not iphone" or "m4a not playing ios safari" and landed here, skip to section three.

First, rule out all the usual suspects

iOS Safari is picky about audio, and the three most common answers online are: wrong MIME type, no HTTP Range support, or bad codec. I checked them one by one — and they were all correct.

# 1) Is the Content-Type right?
curl -sI "https://your-domain/xxx.m4a"
# → Content-Type: audio/mp4   ✅ correct (m4a is audio/mp4)
# → Accept-Ranges: bytes      ✅ Range supported

# 2) Does a Range request actually return 206? (iOS strictly requires this)
curl -s -o /dev/null -D - -H "Range: bytes=0-99" "https://your-domain/xxx.m4a" | grep HTTP
# → HTTP/1.1 206 Partial Content   ✅ correct

The server side is entirely correct. Content-Type is audio/mp4, Range is supported, it returns 206 Partial Content. R2 is fine. CORS isn't the issue either (an <audio> without the crossorigin attribute plays cross-origin without needing CORS).

And the codec is .m4a (AAC) — the format iOS supports most natively. All three common culprits ruled out. So what is it?

The real culprit: the moov atom is at the end of the file (no faststart)

An MP4 / m4a file is made of boxes (also called atoms). Two matter most here:

mdat: the actual audio / video data (large — 11 MB in my file)
moov: the playback index / metadata (timeline, sample tables — the player must read this first to know how to decode)

Here's the problem: ffmpeg's default output puts mdat first and moov at the very end of the file.

Desktop browsers (Chrome / Firefox) are smarter / more aggressive: they fire a Range request for the end of the file to grab moov, find the index, and play.
iOS Safari / WebKit, with <audio preload="metadata">, only fetches the beginning of the file. No moov at the start → it can't find the index → it silently gives up and won't play.

That's why it plays on desktop but not on iPhone / iPad. Both iPhone and iPad use WebKit, so they fail together.

Moving moov to the front of the file is called faststart — the standard trick that lets video / audio "play while downloading."

How to confirm it (30 seconds)

Grab the first 48 bytes and check whether ftyp is followed by moov or mdat:

curl -s -H "Range: bytes=0-47" "https://your-domain/xxx.m4a" | xxd | head -3

Broken (no faststart) — mdat right after ftyp:

00000000: 0000 001c 6674 7970 4d34 4120 ...  ....ftypM4A
00000020: 6672 6565 00a8 3b91 6d64 6174 ...  free..;.mdat   ← mdat first, moov at the end

Good (faststart) — moov right after ftyp:

00000000: 0000 001c 6674 7970 4d34 4120 ...  ....ftypM4A
00000020: 6d6f 6f76 0000 006c 6d76 6864 ...  moov...lmvhd   ← moov right after ftyp ✅

If mdat comes before moov, that's your bug.

The fix: one line of ffmpeg, lossless, no re-encode

ffmpeg -i input.m4a -c copy -movflags +faststart output.m4a

The key is -c copy — it does not re-encode; it just copies the existing stream as-is and relocates moov to the front. So:

Lossless: audio quality is untouched
Same size (mine was 11,258,702 bytes before and after — not a single byte different)
Instant: 11 MB in under a second

Then re-upload to overwrite the original. If your files are on R2 / S3, keep Content-Type: audio/mp4 on the upload.

(If a CDN like Cloudflare sits in front, check whether the edge cached the old file. Mine was cf-cache-status: DYNAMIC — not cached — so the overwrite took effect immediately. The only remaining cache is the phone browser's local cache — test in a private tab for a clean result.)

How to stop it from happening again

If you have a "generate audio → upload" pipeline, always run it through faststart before uploading — never push the raw ffmpeg / converter output straight up. I baked it into the upload script as a mandatory step: remux faststart → upload → auto-verify with curl that moov really is at the front, all three together. New audio never hits this bug again.

This bug is a good reminder: "works on my machine" and "works on the user's device" are two different things. The server config was all correct, the file was there, desktop played fine — every "obvious" place was clean, and the defect hid in the order of the atoms inside the MP4. Problems that only surface on real devices (especially iOS) slip right past automated tests and desktop development. Next time you hit "fine on desktop, broken on mobile," don't just stare at your code — think file format / platform differences.

本文原載於我的部落格：Audio Plays on Desktop but Not on iPhone / iPad — The Culprit Is the MP4 moov Atom

Series reading guide — How to pick which of these 9 Trace Lock posts to read

Dexterlung — Fri, 31 Jul 2026 14:34:55 +0000

May 2026 · Series "Trace Lock — Governance notes from pairing with AI to write code" · Post 5 of 9

This series has 9 posts total. They're an organized record of conversations I had with Claude between late April and end of May 2026.

After writing the first 4, I re-read them and noticed a problem: readers won't know where to start. The difficulty range goes from beginner to advanced. The content jumps from personal narrative to engineering implementation details. Reading from post 1 to post 9 in order will probably make you give up by post 3.

So this post is a map, not content. If you only read this one and leave, that's fine with me.

What this series is about

In one sentence: when pairing with AI to write code, cross-layer bugs kept happening, so Claude and I worked out two governance patterns that pair together.

Both patterns are working names I gave them myself (not industry-standard terminology):

Defensive Trace Lock: after tripping on a cross-layer bug once, lock down that relationship so it doesn't rot later
Offensive audit: proactively audit a business flow (e.g. "order to shipment"), find all unprotected chain nodes, fix them in one pass

Combined, they form what I call the "dual-blade" approach. The 9 posts spread these 2 patterns across 3 layers: plain-language version, engineering version, and meta reflection.

Map of the 9 posts

Post	Topic	Series category	Difficulty	Word count	Who it's for
E	Meta: how the methodology emerged from conversations with AI	Cross-field diary	beginner	EN 1300 / ZH 1700	Wants to know how the whole thing came together
This post	Series reading guide	Cross-field diary	beginner	EN 700 / ZH 900	Wants to decide which posts to read
A1	Defensive Trace Lock plain-language intro	Indie dev notes	intermediate	EN 2000 / ZH 2500	Already hit cross-layer bugs, wants to see how to lock them down
B1	Offensive audit plain-language intro	Indie dev notes	intermediate	EN 1600 / ZH 2100	Wants to know how to audit a whole business flow in one pass
C1	Offense + Defense combined	Indie dev notes	intermediate	EN 2000 / ZH 2500	Wants to see how the two pair up + where this doesn't apply
A2	Defensive engineering version	Engineer diary	advanced	EN 2000 / ZH 2500	Wants the 5-artifact implementation details
B2	Offensive engineering version	Engineer diary	advanced	EN 2000 / ZH 2500	Wants the 6-piece fix pattern template
C2	Cross-project reuse matrix	Engineer diary	advanced	EN 2000 / ZH 2500	Wants to port this to their own project, needs to know what transfers
D	sql-only-trace	Engineer diary	advanced	EN 2000 / ZH 2500	How to test pure-DB logic

Three reading paths

"I only have 30 minutes"

Read E meta (how the whole thing came together) plus this guide. That's enough.

You don't need to look at the specific patterns. E meta compresses the whole journey into one post. After reading it you'll know "this kind of thing exists, here's the context it fits." That's fine.

"I'm not a software engineer but I'm curious about how AI pair programming gets governed"

Order: E meta → A1 → B1 → C1.

Skip the 4 engineering posts. The 3 plain-language posts cover the full thinking, real time costs, and where the approach doesn't apply. After reading you'll have a sense of "is there an analogue to this in my own work." If you're a designer, product manager, or like me a self-taught coder, this path fits best.

"I'm an engineer, I want implementation details"

Order: A1 (shared baseline) → A2 → B2 → C2 → D.

A1 is required reading because the engineering posts assume you know what the 5 artifacts are. A2/B2/C2/D are where the code examples, governance rule templates, and pure-SQL test gotchas live.

If you only want one technical angle:

"How is the protection rule written" → A2
"How does the cross-layer audit actually run" → B2
"Can I port this to my project" → C2
"How do I test pure-DB logic" → D

Who this series is not for

In C1 I listed 5 scenarios where trace lock doesn't apply. Extending that to "who can skip this whole series":

Your project is pure read-only reporting / pure frontend without DB. The whole approach doesn't fit
You don't pair with AI to write code. The series premise doesn't hold
You're on a 5+ person team with strong code review culture. Defensive may overlap with human review, value drops
You're looking for "best practices" or "industry standards." This series doesn't offer those. I name things myself, estimate time myself, list where it doesn't apply myself. No authority claims
You want to read just one post and leave. Apart from this guide, the others assume context, so cherry-picking only works in the order I suggested above

Shared vocabulary: the working names

Names that recur across the series (none of them are industry-standard terminology):

Name	Meaning	First appears
Trace Lock	Locking a cross-layer relationship with 5 artifacts	A1
Offensive / Defensive	The two audit modes	A1 / B1
Dual-blade	The closed loop where both modes pair up	C1
Decision Pinning (business contract freeze)	Writing the rule down explicitly before fixing a BLOCKER	B1
6-piece fix pattern	The 6 steps for fixing one BLOCKER	B1 / B2
sql-only-trace	The trace category for pure-DB logic	D
5 artifacts	The 5 things every defensive trace must contain	A1 / A2

If you hit an unfamiliar term in any post, come back here.

Why I wrote these 9 posts

Not because I think the patterns "need to be known by the industry." Because I tripped on the same shape of bug for half a year, the conversations with Claude when sorting it out got long, and I was afraid of forgetting. The blog version is partly for my own future re-reading, partly so that if a professional engineer sees me misusing something they can correct me.

There's no "teaching" stance when I was writing. I noticed I'd written 9 posts only after they were done, so I laid them out and put them on the blog.

E · Meta: how the methodology emerged from conversations with AI (series starting point)
C1 · Offense + Defense combined (dual-blade overview, read this before deciding whether to go into A2/B2/C2/D)
中文版

About this post

This post is an organized record of conversations I had with Claude (an AI pair-programming tool)
during May 2026. I noticed some patterns worth keeping for my own future reference,
so I asked Claude to help structure them into writing.

A few things I'm not claiming:

Terms used in this post (Trace Lock / Offensive / Defensive / Dual-blade / Decision Pinning / 6-piece fix pattern / sql-only-trace / 5 artifacts) are working names I gave them myself, not industry-standard terminology
My system has a specific shape (solo-maintained, many cross-layer dependencies, ambiguous business contracts). These patterns may not apply to your context
I'm not a software engineer, just a barista who pairs with AI to write code

If a professional engineer spots misuse, or there's already a more standard name for any of these concepts, I genuinely welcome corrections.

本文原載於我的部落格：Series reading guide — How to pick which of these 9 Trace Lock posts to read

I built a vertically-integrated commerce system with AI — and accidentally discovered 4 governance patterns

Dexterlung — Thu, 30 Jul 2026 14:05:11 +0000

May 2026 · Series "Trace Lock — Governance notes from pairing with AI to write code" · Post 1 of 9 (meta)

In May 2026, Claude (my AI pair-programming tool) and I did something I genuinely didn't expect:

We slowly extracted 4 methodology patterns (working names — I'll explain why in a moment) from a 6-month loop of "fix a bug → another one appears → fix that one → another one appears."

I'm a barista. I maintain a self-built, vertically-integrated commerce system by pair-programming with AI. I'm writing this 9-post series for 3 reasons:

I'll forget why I designed things this way in 3 months
Every new AI session starts from zero — writing patterns as markdown that lives in the project means the next conversation auto-onboards (the AI reads them before we start)
There should be more and more of us — non-CS-background, pair-with-AI, solo-maintaining a medium-complexity system. If this series saves one person a year of wall-banging, it was worth writing

This post is the series' meta entry — context, why these patterns surfaced, what the 4 patterns are. The "how to actually do it" goes into the next 8 posts.

What my system is

For the past 7 years I cobbled together a bunch of off-the-shelf SaaS to run my business (a POS, a member system, a pile of spreadsheets)
In 2024 I decided to build a vertically-integrated system myself — products / orders / inventory / members / roasting station / packaging station / shipping all wired together
Stack: Vue 3 frontend, Supabase backend, Claude Code for pair-programming
Scale: 14,000+ files, 40,000+ code relationships (numbers from the indexing tool)

The pattern I kept noticing

For 6 months I kept hitting the same class of bug:

"Data flows from a write-side to a render-side, and somewhere in the middle a layer forgot to sync. Then something breaks."

Concrete examples:

Order payment status didn't sync with order status. Customer thinks they paid, backend shows unpaid.
Stored-value top-up plans got accidentally pushed into the packaging worksheet. The roaster was confused: "How do I package store credit?"
13 coffee products showed "out of stock" to consumers on the storefront, but the admin backend showed them fine (admin / anon view discrepancy).
A VVIP customer topped up $5,000 and got the wrong bonus amount.
After adding a "medium-dark" roast tier, the UI showed the English enum code (no translation).
A customer ordered a "drip-bag variant of a half-pound product" and could only order 1 pack (the system calculated it as half-pound grams).

Each fix took 3-5 hours. Over 6 months that's 5-6 fixes for the same class.

The painful part isn't the fix itself. It's that a month after fixing it, a sibling bug appears, because the root cause wasn't addressed. A team has QA / SRE / PM / code review to catch these. I don't.

The conversation that changed things

On May 25th I hit another one of these. Customer screenshot: "The 'complete roasting' button is frozen, showing 'roast level mismatch'."

I was about to fix it the usual way: comment out one RAISE statement and call it done. But I asked Claude one question:

"I've fixed 5 of these in 6 months. Should I stop and look for all leaks of this same shape, fix them all at once?"

Claude proposed: Spend 1 hour auditing the whole business flow first. List which chain nodes are unprotected. Then decide what to fix first.

I spent 1 hour with Claude mapping it out. We found:

11 chain nodes in the business flow
5 already well-protected
2 partially protected
4 completely unprotected BLOCKERs

Those 4 BLOCKERs included: "order cancel / refund flow," "roasting completion stage," "FIFO ingredient consumption," "packaging station task state." Each one was a "next customer to trip on this costs me 3-5 hours" landmine.

Then Claude and I spent 7 hours fixing all 4 BLOCKERs at once. Plus building a mechanism that auto-reminds me when I edit related code in the future.

The 4 patterns that surfaced during the process

These 4 patterns came to me gradually during those 7 hours. They weren't known in advance. They crystallized in retrospect:

Pattern 1: "Fix a bug" vs "audit a chain" are fundamentally different mental modes

Fix a bug = start from the specific symptom the customer reported, trace outward, fix the root cause.
Audit a chain = walk from the business flow's entry to its exit, listing whether each node is protected.

For 6 months I'd only been doing the first kind. The problem with the first kind: it only fixes reported bugs. The unreported ones keep lurking.

The second kind looks like more work (1 hour of audit), but the ROI is much higher, because it surfaces problems that haven't shown up yet.

I gave this pair of mental modes a working name: "outside-in (looking at the whole chain from outside) vs inside-out (tracing from a specific bug outward) debugging." But this is just my own naming. Formal software engineering probably has more precise terms already, I'm not sure.

Pattern 2: Decisions made with a customer present are forgotten 3 months later

These 4 BLOCKER fixes all involved a business decision:

Should a refund go back to cash first or bonus first? (I chose cash-first. The original logic deducted bonus first, so a cash-first reversal keeps things balanced.)
Should "roast level mismatch" block the operator or write a warning and let them continue? (I chose the latter, to avoid the operator station freezing.)
Should packaging tasks support a "skipped" state? (I chose no, to avoid state-machine explosion.)

These decisions feel obvious to me in the moment, but in 3 months me or another engineer (or AI) reading the code might think "this logic is weird, let me clean it up."

Claude's suggestion: write those decisions as test cases with incident-pinning comments (tests that include a "why" comment). Six months later, when anyone edits the code, the test fails red, forcing them to see the original decision context.

I gave this practice a working name: "Decision Pinning." Same thing, just my working name. I don't know if there's a more standard industry term.

Pattern 3: Cross-layer dependencies should be managed as assets

Software systems are full of "if I change A, I must also change B" cross-layer dependencies. For example:

Change an RPC return shape → must sync the normalize function's whitelist → must sync the UI render side
Change an SSOT field → must sync every caller

Historically these relationships only existed in my head. Three months later, forgetting means "I changed A but forgot B."

Claude and I designed a thing we call "Trace Lock" (working name again):

Register each cross-layer relationship in a markdown table
Pair it with a "fuse test" that pins down the currently-correct behavior
Pair it with two "auto-inspectors" (governance rules) that block regression
Pair it with an "AI reminder" (Claude Code auto-reminds me when I'm about to edit an affected file)

This setup is working well so far. Claude and I extended it during this audit with a new sub-category called sql-only-trace (a testing approach for pure DB logic).

Pattern 4: Offense + Defense as a dual mode

Combining Pattern 1 and Pattern 3 surfaces a larger pattern:

Offense mode (audit + fix N items at once) = proactively attacking, building new protection
Defense mode (trace lock + auto trigger) = passively defending, blocking regression

Neither alone is enough:

No offense → protection coverage doesn't expand, bugs keep accumulating
No defense → existing protection rots
Both together → the system's "entropy" moves toward decreasing

In physics, entropy (you can think of it as "disorder") increasing monotonically is "natural". To decrease entropy you need to continuously inject energy. Software bug count follows the same logic, I think (this is just how I personally make sense of it. I'm not claiming it's a rigorous analogy).

What this series will cover

I plan to write:

A1 Defensive Trace Lock: How to lock down a single cross-layer relationship (for future me; readable without a CS degree)
B1 Offensive audit: How to periodically audit a whole business flow (for future me; readable without a CS degree)
C1 Offense + Defense combined: How the two pair up (for future me)
D sql-only-trace: How to test pure DB logic (engineer-facing technical detail)
A2 / B2 / C2 Engineer versions: Engineering-grade versions of the 3 above (for engineers who might be curious, with code samples + cross-project reuse matrix)
Index post: A "which post should you read first" map for readers

If you only read one, I'd recommend A1 (defensive). That's where this whole thing started.

A1 · Defensive Trace Lock (next in series)
Series index (reading map)
中文版

About this post

A few things I'm not claiming:

Terms used in this post (Decision Pinning / Trace Lock / outside-in vs inside-out / offense vs defense / sql-only-trace) are working names I gave them myself, not industry-standard terminology
My system has a specific shape (solo-maintained, many cross-layer dependencies, ambiguous business contracts). These patterns may not apply to your context
I'm not a software engineer, just a barista who pairs with AI to write code

If a professional engineer spots misuse, or there's already a more standard name for any
of these concepts, I genuinely welcome corrections.

本文原載於我的部落格：I built a vertically-integrated commerce system with AI — and accidentally discovered 4 governance patterns

sql-only-trace Engineering Edition — Testing DB-Side Pure Logic

Dexterlung — Thu, 30 Jul 2026 13:05:14 +0000

May 2026 · Series "Trace Lock — Governance Notes from AI Pair-Programming" · Post 9 of 9 (series finale)

This is the final post in the series. It expands the "sql-only-trace variant" briefly mentioned in B2 Offense Engineering Edition at line 485, and fills in the engineering details that C2 Combined Engineering Edition's cross-database mapping section left out.

Written for engineers who already know the 11-piece matrix from C2 and the sql-only-trace variant concept from B2 line 485. If you are a non-technical reader, C1 Combined Offense + Defense (plain version) is the post you want.

My environment: Vue 3 + Vite + Vitest + Supabase (PostgreSQL) + Node.js scripts. The DO block, set_config, and RAISE EXCEPTION mechanics discussed below are PostgreSQL-specific. Equivalents for MySQL / SQLite / MongoDB are covered in the "when not to use" section at the end.

Why Unit Test Frameworks Are Not Enough

In the 11-piece matrix, piece 2 ("Trace test 5-section structure") assumes Vitest / Jest / Pytest or similar application-layer unit test frameworks. But some business logic lives purely inside the database, and application-layer tests cannot reach the core.

Four scenarios where unit tests fall short:

Scenario	Why frontend / app-layer tests cannot reach
Complex PL/pgSQL stored procedures	Logic lives inside `DO $$ ... $$` or `CREATE FUNCTION`. App layer only sees "input → output" as a black box
FIFO / inventory invariants	Multi-table interactions (`green_beans` + `opened_beans` + `roasting_input_items`) plus trigger cascades. App layer mocks cannot reproduce them
RLS policy boundaries	RLS conditions use `current_setting('request.jwt.claims')`. You need to simulate JWT to test them
Function overload behavior	`CREATE OR REPLACE` vs `DROP FUNCTION` behave differently. When multiple overloads exist, the app layer cannot predict which one gets called

My environment hits all four. fn_roaster_consume_beans_fifo is a FIFO consumption RPC (scenarios 1, 2, 3). fn_issue_order_action_token had an overload incident (scenario 4; see CLAUDE.md Rule 27 for the incident pinning case).

Testing these RPCs from the Vue component layer only verifies "call succeeded vs failed". It cannot verify "is FIFO ordering correct", "is the inventory invariant maintained", "does a bad JWT return permission denied instead of silent return".

sql-only-trace replaces piece 2's trace test from .trace.test.js with .trace.test.sql, letting the trace test run inside the database and use the database's own assertion mechanism.

SQL DO Block Structure Anatomy

Expand the skeleton from B2 line 485-549 into 6 sections plus an EXCEPTION fallback:

-- scripts/integration-test/T021-fifo-consume-test.sql

-- ════════════════════════════════════════════════════════════
-- 0. JWT config (let RLS / has_operator_capability pass)
-- ════════════════════════════════════════════════════════════
SELECT set_config(
  'request.jwt.claims',
  '{"sub":"t021-test-user","system_role":"super_admin","tenant_id":"coffeeshooters"}',
  false
) AS jwt_set;

DO $T021$
DECLARE
  -- ════════════════════════════════════════════════════════════
  -- 1. Variable declarations (seed IDs, result holders, errors array)
  -- ════════════════════════════════════════════════════════════
  v_kind_id UUID := gen_random_uuid();
  v_gb1_id TEXT := 'T021-GB1-' || substring(gen_random_uuid()::TEXT, 1, 8);
  v_ro_id  TEXT := 'T021-RO-'  || substring(gen_random_uuid()::TEXT, 1, 8);
  v_consume_result JSONB;
  v_gb1_stock_after INTEGER;
  v_errors TEXT[] := ARRAY[]::TEXT[];
BEGIN
  -- ════════════════════════════════════════════════════════════
  -- 2. SEED (insert fake data with unique prefix to avoid pollution)
  -- ════════════════════════════════════════════════════════════
  RAISE NOTICE '=== T021 SEED ===';
  INSERT INTO public.coffee_bean_kinds (id, tenant_id, name, ...) VALUES (v_kind_id, ...);
  INSERT INTO public.green_beans (id, kind_id, ...) VALUES (v_gb1_id, v_kind_id, ...);
  INSERT INTO public.roasting_orders (id, kind_id, ...) VALUES (v_ro_id, v_kind_id, ...);

  -- ════════════════════════════════════════════════════════════
  -- 3. RUN (call the RPC under test)
  -- ════════════════════════════════════════════════════════════
  RAISE NOTICE '=== T021 RUN ===';
  v_consume_result := public.fn_roaster_consume_beans_fifo(v_ro_id, v_kind_id, 1500);
  RAISE NOTICE 'consume_result = %', v_consume_result;

  -- ════════════════════════════════════════════════════════════
  -- 4. ASSERTIONS (append failures to v_errors, do not abort)
  -- ════════════════════════════════════════════════════════════
  SELECT stock_grams INTO v_gb1_stock_after FROM public.green_beans WHERE id = v_gb1_id;

  IF COALESCE(v_gb1_stock_after, 0) <> 0 THEN
    v_errors := array_append(v_errors,
      format('A1 GB1.stock_grams expected 0, got %s', v_gb1_stock_after));
  END IF;
  -- ... other assertions

  -- ════════════════════════════════════════════════════════════
  -- 5. CLEANUP (explicit DELETE, no transaction rollback available)
  -- ════════════════════════════════════════════════════════════
  DELETE FROM public.roasting_input_items WHERE roasting_order_id = v_ro_id;
  DELETE FROM public.roasting_orders WHERE id = v_ro_id;
  DELETE FROM public.green_beans WHERE id = v_gb1_id;
  DELETE FROM public.coffee_bean_kinds WHERE id = v_kind_id;

  -- ════════════════════════════════════════════════════════════
  -- 6. Conclusion (green or red)
  -- ════════════════════════════════════════════════════════════
  IF array_length(v_errors, 1) IS NULL THEN
    RAISE NOTICE '✅ T-021 ALL ASSERTIONS PASSED';
  ELSE
    RAISE EXCEPTION E'❌ T-021 FAILED — %s errors:\n%',
      array_length(v_errors, 1),
      array_to_string(v_errors, E'\n  - ');
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    -- 7. EXCEPTION fallback (best-effort cleanup + re-raise)
    BEGIN
      DELETE FROM public.roasting_input_items WHERE roasting_order_id = v_ro_id;
      DELETE FROM public.roasting_orders WHERE id = v_ro_id;
      DELETE FROM public.green_beans WHERE id = v_gb1_id;
      DELETE FROM public.coffee_bean_kinds WHERE id = v_kind_id;
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    RAISE;
END
$T021$;

-- Final SELECT lets the PASS message show in CLI output
SELECT 'T-021 PASSED' AS status, 16 AS assertions, NOW() AS executed_at;

The seven sections map to a typical unit test as follows:

SQL section	Vitest equivalent	Notes
0 JWT config	`beforeEach(setupAuth)`	RLS simulation
1 Variable declarations	`let v = ...`	seed IDs + result holders
2 SEED	`beforeAll(seed)`	No transaction; need prefix to avoid pollution
3 RUN	`result = await fn()`	Call the RPC
4 ASSERTIONS	`expect().toBe()`	Accumulating, non-aborting
5 CLEANUP	`afterAll(cleanup)`	Must use explicit DELETE
6 Conclusion	test runner decides	RAISE EXCEPTION = red
7 EXCEPTION fallback	no equivalent	Unit tests get auto-rollback from transactions; SQL does not

The two most critical sections are 0 JWT config and 7 EXCEPTION fallback. Without them sql-only-trace cannot run (blocked by RLS) or leaves seed pollution when it crashes (no cleanup).

Supabase CLI Gotchas When Running SQL Tests

npx supabase db query --linked -f behaves differently from a normal unit test runner in four ways. Each one bit me.

Gotcha 1 (no transaction)

Supabase CLI treats each statement as an auto-committing independent transaction. You cannot wrap the entire DO block in BEGIN; ... ROLLBACK;.

Implication: All SEED data stays in the database. CLEANUP must use explicit DELETE; you cannot rely on rollback.

Mitigation: Section 5 and section 7 both need DELETE. Best-effort cleanup inside EXCEPTION needs its own copy. The original error is already in flight, so even if cleanup itself fails, swallow that failure.

Gotcha 2 (only the last SELECT is returned)

By default, the CLI only prints the rows from the last SELECT. RAISE NOTICE is hidden. SELECT statements in the middle of the DO block are hidden.

Implication: To show PASS / FAIL, put the conclusion in the last SELECT. On failure, RAISE EXCEPTION carries the error string out (the CLI shows the EXCEPTION message).

Mitigation:

-- Always end with this
SELECT 'T-021 PASSED' AS status, 16 AS assertions, NOW() AS executed_at;

On failure, RAISE EXCEPTION aborts before this SELECT runs, so it only shows on PASS. On FAIL, the CLI prints the EXCEPTION message (including the v_errors array string).

Gotcha 3 (RAISE NOTICE is hidden)

The CLI defaults client_min_messages to WARNING, so NOTICE-level RAISE NOTICE gets swallowed.

When debugging, add SET client_min_messages = NOTICE; at the top of the file to expose RAISE NOTICE for inspecting SEED IDs or RUN intermediate state. Do not leave it on for daily test runs.

Gotcha 4 (no retry, no parallel)

The CLI runs a file sequentially top to bottom with no retry. A network blip or DB connection drop is an immediate fail.

Mitigation: Write sql-only-trace tests as idempotent (use SEED prefix to ensure multiple runs do not conflict). For CI, run them in both the pre-push hook and the main branch CI (two layers of defense).

Full Run Command

npx supabase db query --linked -f scripts/integration-test/T021-fifo-consume-test.sql

On PASS the CLI prints:

 status        | assertions | executed_at
---------------+------------+------------------------------
 T-021 PASSED  |         16 | 2026-05-26 14:30:00+00

On FAIL the CLI prints (RAISE EXCEPTION message):

ERROR:  ❌ T-021 FAILED — 2 errors:
  - A1 GB1.stock_grams expected 0, got 600
  - A7 inventory invariant violated: deducted 1800 but RPC asked for 1500

Exit code is non-zero, so the pre-push hook blocks the push.

Extending the Registry for sql-only-trace

Piece 1 (Registry markdown) and pieces 3-4 (Governance rules) originally assume trace tests are .trace.test.js running on Vitest. Adding sql-only-trace requires the registry parser to recognize the new category.

Registry Entry Format (with sql-only-trace type)

### T-021: FIFO inventory invariant for roasting consumption

- **Type**: sql-only-trace
- **Anchor SSOT**: `supabase/migrations/N_fn_roaster_consume_beans_fifo.sql`
- **Trace test (SQL)**: `scripts/integration-test/T021-fifo-consume-test.sql`
- **Business contract**: FIFO consumption ordered by created_at ASC within the same kind. opened_beans remainder takes priority. weighted_avg_cost = SUM(grams × cost) / total_grams
- **Last edited**: 2026-05-25

Compared to a frontend trace entry, the differences are Type: sql-only-trace and Trace test (SQL) instead of Trace test: src/__tests__/traces/T-NN.trace.test.js.

Parser isSqlOnly Detection

scripts/parse-trace-registry.mjs adds ~10 lines:

function parseTraceEntry(block) {
  const type = block.match(/\*\*Type\*\*:\s*(\S+)/)?.[1];
  const isSqlOnly = type === 'sql-only-trace';

  const testPath = isSqlOnly
    ? block.match(/\*\*Trace test \(SQL\)\*\*:\s*`([^`]+)`/)?.[1]
    : block.match(/\*\*Trace test\*\*:\s*`([^`]+)`/)?.[1];

  return {
    id: block.match(/### (T-\d+)/)?.[1],
    type,
    isSqlOnly,
    anchorPath: block.match(/\*\*Anchor SSOT\*\*:\s*`([^`]+)`/)?.[1],
    testPath,
    lastEdited: block.match(/\*\*Last edited\*\*:\s*(\S+)/)?.[1],
  };
}

Governance Rule B Skips Import Check

checkTraceTestImportsAnchor() originally enforces that "trace test must import the anchor SSOT it declares" (catches the rot where a trace test was written but no longer actually references the anchor). SQL has no import concept, so it needs a carveout:

function checkTraceTestImportsAnchor(traces) {
  const violations = [];
  for (const trace of traces) {
    // sql-only-trace skips import check (SQL has no import concept)
    if (trace.isSqlOnly) continue;

    const testContent = fs.readFileSync(trace.testPath, 'utf8');
    const anchorBasename = path.basename(trace.anchorPath, path.extname(trace.anchorPath));

    if (!testContent.includes(anchorBasename)) {
      violations.push({
        traceId: trace.id,
        message: `trace test ${trace.testPath} does not import anchor ${trace.anchorPath}`,
      });
    }
  }
  return violations;
}

Governance Rule A Still Applies (trace-registry-test-coverage)

The "every trace in the registry must have a corresponding test file that exists" rule applies regardless of frontend or SQL:

function checkTraceRegistryTestCoverage(traces) {
  const violations = [];
  for (const trace of traces) {
    if (!fs.existsSync(trace.testPath)) {
      violations.push({
        traceId: trace.id,
        message: `${trace.testPath} does not exist (${trace.isSqlOnly ? 'SQL' : 'JS'} test)`,
      });
    }
  }
  return violations;
}

The total parser plus governance rule extension is about 15-20 lines, done in the same sprint.

T-021 Full Case Study

T-021 is the trace test for Gap-3 ("FIFO inventory invariant for roasting consumption") in the sprint's 4 BLOCKERs. The actual file lives at scripts/integration-test/T021-fifo-consume-test.sql, 282 lines, 16 assertions.

Business Contract

Frozen 2026-05-25 (the day the sprint ended):

Within the same kind, deduct in green_beans.created_at ASC order (FIFO)
For each bean batch, deduct from opened_beans remainder first, then break a fresh pack from stock_grams into opened_beans
After breaking a pack, leftover opened remainder stays in opened_beans for the next FIFO round
weighted_avg_cost_per_g = SUM(grams × cost_per_g) / total_grams
roasting_orders.is_fifo_resolved = true with green_bean_id/name carrying the primary source
Inventory invariant: (pre-deduction stock+opened total) - (post-deduction stock+opened total) = consumed total_grams

Six business contract clauses, each backed by 2-3 assertions.

Test Scenario Design

GB1 (earliest, spec=600g): stock=1200g, cost=400/kg, created_at=-3 days
GB2 (next,     spec=500g): stock=500g,  cost=500/kg, created_at=-2 days
GB3 (latest,   spec=600g): stock=600g,  cost=600/kg, created_at=-1 day

Roasting order consumes 1500g

Expected FIFO behavior:

GB1 breaks 2 fresh packs and consumes 1200g (stock=0, opened=0)
GB2 breaks 1 pack and consumes 300g (stock=0, opened=200 remainder)
GB3 untouched (v_remaining=0 triggers EXIT WHEN)
200g remainder stays in opened_beans for the next FIFO round

The 16 Assertions

#	Check	Expected
A1	GB1.stock_grams	0
A2	GB1.opened.remaining_grams	0
A3	GB2.stock_grams	0
A4	GB2.opened.remaining_grams	200
A5	GB3.stock_grams (untouched)	600
A6	GB3.opened.remaining_grams (untouched)	0
A7	Inventory invariant	1500g consumed
A8	roasting_input_items row count	2
A9	roasting_input_items total grams	1500
A10	seq=1 is GB1	GB1 id
A11	seq=1 grams	1200
A12	seq=2 is GB2	GB2 id
A13	seq=2 grams	300
A14	roasting_orders.is_fifo_resolved	true
A15	roasting_orders.input_grams	1500
A16	weighted_avg_cost_per_g	0.42

weighted_avg_cost_per_g formula: (1200×0.4 + 300×0.5) / 1500 = 0.42. This one breaks most easily if someone changes the algorithm (for example, switching to simple average).

SEED Prefix Anti-Pollution

v_gb1_id TEXT := 'T021-GB1-' || substring(gen_random_uuid()::TEXT, 1, 8);

Each run has a different prefix, so multiple runs do not collide. Also useful when cleanup fails: you can manually grep WHERE id LIKE 'T021-%' to wipe leftovers.

EXCEPTION Cleanup, Real Value

The first version of T-021 had no EXCEPTION block. When the RPC threw, seed data stayed in the database and polluted subsequent test runs. With EXCEPTION, even if the RPC throws, best-effort cleanup still runs:

EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      DELETE FROM public.roasting_input_items WHERE roasting_order_id = v_ro_id;
      DELETE FROM public.roasting_orders WHERE id = v_ro_id;
      DELETE FROM public.opened_beans WHERE green_bean_id IN (v_gb1_id, v_gb2_id, v_gb3_id);
      DELETE FROM public.green_beans WHERE id IN (v_gb1_id, v_gb2_id, v_gb3_id);
      DELETE FROM public.coffee_bean_kinds WHERE id = v_kind_id;
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    RAISE;

The inner EXCEPTION WHEN OTHERS THEN NULL swallows cleanup failures (such as FK conflicts). This ensures the outer RAISE propagates the original error. Without this layer, cleanup failure would mask the original error and make debugging painful.

Reverse Verification Inside SQL

Piece 9 of the defense matrix ("reverse verification anchor-break flow") also applies to sql-only-trace, but the break point switches from helper logic to RPC logic.

5-Step Reverse Verification (sql-only-trace variant)

Write trace test, run green: T-021 all 16 assertions PASS
Deliberately break the RPC: change fn_roaster_consume_beans_fifo's ORDER BY from created_at ASC to created_at DESC
Run trace test, confirm red: A10 (seq=1 should be GB1) will fail because under DESC, seq=1 becomes GB3
Revert to ORDER BY ASC
Run again, confirm green

Going through all 5 steps is what makes "lock has actual catching power" true. If step 3 does not turn red, your trace test missed a core contract clause. Add the missing assertion.

Choosing the Break Point

T-021's break point is ORDER BY ASC vs DESC because that is the core contract of FIFO. Changing something else (such as the gen_random_uuid() prefix) would not break the trace (that is not part of FIFO behavior).

Principle for choosing break points: choose a "core business contract logic" point, not an "implementation detail" point. FIFO's core is ORDER BY ordering + opened remainder priority + pack-breaking rule + weighted average formula. Any one of those four broken should turn the trace test red.

Anti-Rot Value

Three months from now, an AI pairing on fn_roaster_consume_beans_fifo accidentally drops the ORDER BY (perhaps trying to optimize the query). The trace test goes red in CI and blocks the push.

Without sql-only-trace, this bug only surfaces when a customer reports "why did you use the new batch first and leave the old batch to expire". By then, weeks of bad data may already exist.

When Not to Use

Five situations where porting sql-only-trace is not necessarily worth it.

1. Pure Frontend / App-Layer Business Logic

If your business logic lives entirely in Vue components / Pinia stores / composables / pure helpers, and the database is just a CRUD store, sql-only-trace has no anchor to attach to.

My environment has both, so I use both: frontend traces for frontend logic (T-001 / T-002 / T-005), sql-only-trace for DB-pure logic (T-021 / T-022).

2. Multi-DB / Heterogeneous Data Stores

When business spans PostgreSQL + Redis + Elasticsearch, sql-only-trace only covers the PostgreSQL portion. Redis has no PL/pgSQL, so you would write redis-cli scripts + Lua + redis-py yourself. Elasticsearch uses _search API + curl + jq.

This is doable, but cross-store consistency tests belong in the application layer (write integration tests from Node / Python that hit all three). sql-only-trace is not enough on its own.

3. No SQL Test Runner / No CI Hook

npx supabase db query --linked -f is the standard runner for sql-only-trace. Other stacks have equivalents:

DB	CLI
PostgreSQL (native)	`psql -f file.sql`
Supabase	`npx supabase db query --linked -f file.sql`
MySQL	`mysql -u user -p db < file.sql`
MongoDB	`mongosh < file.js`

If your stack lacks such a CLI, or your CI cannot run it, sql-only-trace has nowhere to attach. The pre-push hook also cannot wire it up.

4. Cross-Postgres-Version Compatibility

set_config('request.jwt.claims', ...) requires PostgreSQL 9.5+ for the set_config function syntax. Older versions need SET LOCAL "request.jwt.claims" = '...'. gen_random_uuid() is built-in only in PostgreSQL 13+ (older versions need the pgcrypto extension enabled).

Deploying across multiple Postgres versions means maintaining sql-only-trace per version, which is more work than maintaining frontend traces.

5. RLS Policies Themselves Are the Target

sql-only-trace uses set_config to inject a super_admin JWT and pass RLS. If the goal is to test the RLS policies themselves (such as "anon should not see X" / "customer should only see their own orders"), you need multiple JWT scenarios, each in its own run, and sql-only-trace becomes 2-3x the work.

Mitigation: Split RLS tests into separate files (T-023-rls-customer-only.sql / T-024-rls-anon-readonly.sql), one JWT scenario per file. The engineering effort is 2-3x larger than for a typical sql-only-trace.

Cross-Database Downgrade Table

If you are not on PostgreSQL, sql-only-trace downgrades to "application-layer integration test":

Source	Replacement
PG `DO $$ ... $$` block	MySQL `CREATE PROCEDURE ... CALL`, two steps
PG `set_config('request.jwt.claims', ...)`	MySQL has no equivalent. Skip RLS; test at app layer
PG `RAISE EXCEPTION`	MySQL `SIGNAL SQLSTATE '45000'`
PG `RAISE NOTICE`	MySQL `SELECT 'msg' AS log`
PG `gen_random_uuid()`	MongoDB `ObjectId()` or app-layer `uuid()`
PG `npx supabase db query --linked -f`	Node + `pg-promise` integration test

The skeleton (SEED / RUN / ASSERTIONS / CLEANUP / Conclusion) applies across all DBs. Only the location moves from SQL to the application layer.

Series Wrap-Up

This is post 9 of 9 in "Trace Lock — Governance Notes from AI Pair-Programming" (series finale). The 9 posts span 3 series:

Cross-Field Diary (cross-field-diary) — E meta + index
Indie Dev Notes (indie-dev-notes) — A1 / B1 / C1
Engineer Diary (engineer-diary) — A2 / B2 / C2 / D (this post)

Defense (A1 / A2 / parts of D), offense (B1 / B2), and combined (C1 / C2) form three interwoven axes. D fills in the DB-pure logic testing gap, completing the engineering details of the whole dual-blade approach.

From the opening E meta post on "how I discovered methodology through conversations with AI" to this closing post on sql-only-trace details, the series records:

The 11-piece matrix (C2)
The 5-artifact defense framework (A2)
The 6-piece fix pattern (B2)
Cross-stack and cross-database mappings (C2 + D)
8 not-applicable scenarios (C2) plus 5 sql-only-trace not-applicable scenarios (this post)

I gave these working names ("dual blade" / "Trace Lock" / "sql-only-trace") myself. None are industry-standard terminology. If your context happens to match the conditions (solo-maintained / many cross-layer dependencies / relatively stable business contracts / 6+ months maintenance period / AI pair-programming / pure PostgreSQL), parts of this may be useful.

D · sql-only-trace · 中文版
C2 · Combined Engineering Edition (Cross-Project Reuse Matrix + When Not to Use) (previous post in series)
B2 · Offense Engineering Edition (6-Piece Fix Pattern Implementation Details) (source of the sql-only-trace variant reference)
A2 · Defense Engineering Edition (5-Artifact Implementation Details) (starting point of the defense axis)
C1 · Combined Offense + Defense (plain version) (plain-language counterpart)

About this post

A few things I'm not claiming:

Terms used in this post (such as sql-only-trace / Trace Lock / dual blade / 11-piece matrix / 5 artifacts / 6-piece fix pattern / Anchor SSOT / fuse-style test / Decision Pinning / reverse verification anchor break / framework layer vs content layer / objective vs subjective projects / AI reminder skill) are working names I gave them myself, not industry-standard terminology
My system has a specific shape (solo-maintained, many cross-layer dependencies, ambiguous business contracts). These patterns may not apply to your context
I'm not a software engineer — just a barista who pairs with AI to write code

If a professional engineer spots misuse, or there's already a more standard name for any
of these concepts, I genuinely welcome corrections.

本文原載於我的部落格：sql-only-trace Engineering Edition — Testing DB-Side Pure Logic

Combined Offense + Defense (Engineering Edition) — Cross-Project Reuse Matrix and When Not to Use

Dexterlung — Thu, 30 Jul 2026 01:08:56 +0000

May 2026 · Series "Trace Lock — Governance Notes from AI Pair-Programming" · Post 8 of 9

This is the engineering version of C1 Combined Offense + Defense, and also the convergence point of A2 Defense Engineering Edition and B2 Offense Engineering Edition.

A2 explains how to lock a single known trace using 5 artifacts. B2 explains how to fix N gaps surfaced by an audit using a 6-piece fix pattern. Each of those posts came with its own "cross-project reuse" table, but only covered its own side. This post merges the two tables into an 11-piece matrix, then adds cross-stack mapping, objective-vs-subjective project differences, AI pair-programming vs traditional development differences, plus an expanded "when not to use" list.

Written for engineers who already know A2's 5 artifacts and B2's 6-piece pattern. If you are a non-technical reader, C1 Combined Offense + Defense (plain version) is the post you want.

My environment: Vue 3 + Vite + Vitest + Supabase (PostgreSQL) + Node.js scripts. The "framework layer / content layer" split and porting cost estimates below are based on this stack. Moving to a different stack (Next.js + Prisma + Jest + tRPC, or Django + pytest + Celery) keeps the skeleton applicable, but code skeletons need to be replaced.

The 11-Piece Matrix

Merge A2's 5 artifacts with B2's 6-piece pattern. Deduplicate the overlap (Registry / Trace test / Governance rule are shared). Add each side's unique pieces. The result is an 11-piece total list:

#	Piece	A2 uses?	B2 uses?	Main location	Reusability	Framework dependency
1	Registry markdown entry	✅	✅	`文檔/data-source-registry.md` Critical Traces section	★★★★★	Pure markdown
2	Trace test 5-section structure	✅	✅	`frontend-app/src/__tests__/traces/T{NN}-*.trace.test.js`	★★★★☆	Any unit test runner
3	Governance rule A (trace-registry-test-coverage)	✅	✅	`scripts/governance-guard.mjs`	★★★★☆	Node.js script
4	Governance rule B (trace-test-must-import-anchor)	✅	✅	`scripts/governance-guard.mjs`	★★★★☆	Node.js script
5	AI reminder skill	✅	⚠️ partial	`.claude/skills/trace-lock-modify/SKILL.md` + `.claude/skills/business-flow-audit-fix/SKILL.md`	★★★☆☆	Claude Code / Cursor / other skill-enabled AI tools
6	Pure-function helper convention	❌	✅	`frontend-app/src/lib/business-rules/<feature>Logic.js`	★★★★★	Pure JS module convention, framework-agnostic
7	Caller exemption comment convention	⚠️ occasional	✅	source code inline `@xxx-ok: <reason>`	★★★★★	Pure text regex convention
8	Iteration log 5-section structure	❌	✅	`文檔/iteration-logs/<sprint>/NN_Gap-N_<topic>.md`	★★★★★	Pure markdown record
9	Reverse verification anchor-break flow	⚠️ occasional	✅	5-step flow (write test → green → break anchor → confirm red → revert)	★★★★☆	Pure procedure, needs team / AI cooperation
10	sql-only-trace variant	⚠️ occasional	✅	`scripts/integration-test/T{NN}-*.sql` + DO block	★★★☆☆	PostgreSQL-specific
11	Decision Pinning (business contract freezing)	❌	✅	trace test's `pin current behavior` section + registry's backlog finding	★★★★★	Pure discipline, tool-agnostic

A few observations.

Framework layer vs content layer split

Pull out pieces rated ≥ ★★★★☆:

Framework layer (high reusability):
   1 Registry markdown entry             ★★★★★
   2 Trace test 5-section structure       ★★★★☆
   3 Governance rule A                   ★★★★☆
   4 Governance rule B                   ★★★★☆
   6 Pure-function helper convention      ★★★★★
   7 Caller exemption comment             ★★★★★
   8 Iteration log 5-section              ★★★★★
   9 Reverse verification anchor-break    ★★★★☆
  11 Decision Pinning discipline          ★★★★★

Medium reusability (★★★☆☆):
   5 AI reminder skill         (tool-specific)
  10 sql-only-trace variant    (DB-specific)

Content layer (not reusable ★☆☆☆☆, reinvent per project):
  - Specific traces (business chain shape)
  - Specific helpers (business algorithm)
  - Specific business contracts (pinning content)
  - Specific incident pinning cases

A2 line 321 and B2 line 644 each observed "framework layer 80-90% reusable, business contract 0% reusable". The 11-piece matrix matches: 9 framework-layer pieces, 2 medium-tier pieces, business contract 0% reusable.

Why piece 5 (AI reminder skill) and piece 10 (sql-only-trace) drop to ★★★☆☆

Both have strong framework dependencies.

Piece 5 AI reminder skill depends on Claude Code's skill auto-trigger mechanism (description-based fuzzy matching + frontmatter). Cursor has .cursorrules with similar intent but different syntax. Other AI tools (GitHub Copilot / Continue.dev / Cody) currently lack this mechanism. Reusability depends on whether the target environment has the "skill auto-trigger" primitive:

Claude Code / Cursor: portable, syntax conversion needed
VSCode + GitHub Copilot: not portable (no description-based trigger)
Others: requires evaluation

Without this primitive, fall back to "PR template + checklist". But losing the "auto-trigger" property means relying on humans to remember the checklist, which becomes another rot source.

Piece 10 sql-only-trace variant depends on PostgreSQL's DO $$ ... $$ LANGUAGE plpgsql block + JWT config + SET LOCAL. MySQL / SQLite / MongoDB each have their own stored-procedure / migration-test concepts, but with different syntax and portability (see cross-database mapping below).

Applicability Dimensions

"My environment hits all the conditions" decomposes into 5 dimensions. Each dimension has its own marginal-value curve for the combined offense + defense pattern.

Dimension 1 (team size)

Size	Offense applicable?	Defense applicable?	Why
Solo developer	✅ Strongly recommended	✅ Strongly recommended	Zero external correction force; engineering means is the only remedy
2-3 small team	✅ Recommended	✅ Recommended	Before code review culture forms, similar to solo
4-10 team	⚠️ Evaluate	✅ Recommended	Code review partly plays the attack role, but reviews do not actively audit
10+ organization	⚠️ Evaluate	⚠️ Evaluate	Multi-tier review + QA + senior dev pings, governance rule marginal returns diminish

10+ organizations are not "cannot use", but "ROI becomes hard to calculate". Yet piece 1 (Registry markdown) and piece 8 (Iteration log) remain applicable. These two are for "organizational memory", independent of team size.

Dimension 2 (project maintenance horizon)

Horizon	Offense applicable?	Defense applicable?	Why
< 1 month (hackathon / spike)	❌	❌	Forgetting "3 months later" does not exist; pinning via commit message is sufficient
1-3 months (early MVP)	❌	⚠️ Evaluate	Business contract not stable; frequent trace breaks drag iteration
3-12 months (growth phase)	⚠️ Evaluate	✅ Recommended	Cross-layer bug experience accumulating; pick 2-3 painful traces to lock first
12+ months (long-term maintenance)	✅ Strongly recommended	✅ Strongly recommended	"Forgetting 3 months later" cost fully visible

My environment (24+ months maintenance) hits the strongly recommended region.

Dimension 3 (cross-layer complexity)

"Cross-layer dependency" definition: a piece of data flowing from DB to UI through N transformation points (trigger / RPC / store / composable / component / helper).

Depth	Offense applicable?	Defense applicable?
≤ 2 layers (DB → UI direct)	❌	❌
3-5 layers (with store + composable)	⚠️ Evaluate	✅ Recommended
6+ layers (with trigger + RPC + helper + ACL + multiple components)	✅ Strongly recommended	✅ Strongly recommended

My environment (e-commerce + inventory + roasting + POS + multiple RLS policies) sits at roughly 6-9 layers per business chain.

Dimension 4 (business contract stability)

Change frequency	Offense applicable?	Defense applicable?
Weekly algorithm change	❌	❌
Monthly 1-2 changes	⚠️ Evaluate	⚠️ Evaluate
Quarterly 0-1 change	✅	✅
Yearly stable	✅ Strongly recommended	✅ Strongly recommended

When business contracts churn often, pinning becomes a burden (every change requires updating trace test + registry + iteration log).

Dimension 5 (AI pair-programming intensity)

Intensity	Offense applicable?	Defense applicable?
Never use AI	✅ but piece 5 unused	✅ but piece 5 unused
Occasional (few times a week)	✅ Recommended	✅ Strongly recommended
Heavy (daily main driver)	✅ Strongly recommended	✅ Strongly recommended

When AI pair-programming is heavy, piece 5 (AI reminder skill) provides the highest value because AI lacks the "I edited X last week" muscle memory; every new conversation starts from zero.

Objective vs Subjective Project Differences

"Objective" vs "subjective" is my own coarse project classification (working name, my own placeholder term).

Objective: Has clear "right / wrong" business logic. E-commerce (inventory / pricing / checkout), accounting, finance, ERP, CRM derived fields, ticketing, check-in
Subjective: Output is "taste / style / subjective experience". Design mockups, copywriting, video editing, UX A/B test outcomes, recommendation systems

The two project classes have very different applicability profiles.

Objective projects

Piece	Applicability for objective projects	Why
1 Registry / 2 Trace test / 3-4 Governance rule	✅ High	"Right / wrong" has explicit oracle (queryable from DB, expressible as spec)
6 Pure-function helper	✅ High	Business logic expressible as pure function (e.g. "annual threshold → tier")
7 Caller exemption	✅ High	Thin wrapper scenarios common
8 Iteration log	✅ High	Decisions like "why cash-first not bonus-first" need recording
9 Reverse verification	✅ High	Breaking the anchor produces a clear expected red
10 sql-only-trace	✅ High	Useful when DB logic is dense
11 Business contract freezing	✅ High	"Current behavior" is definable

The combined pattern fits as a whole set. My environment is objective.

Subjective projects

Piece	Applicability for subjective projects	Why
1 Registry / 2 Trace test	⚠️ Partial	No clear "right / wrong" oracle; pinning does not land
3-4 Governance rule	⚠️ Partial	Can pin "structure" (helper must exist) but not "content"
6 Pure-function helper	✅	But helper itself is a suggested value, not a SSOT
7 Caller exemption	✅	Same applicability as objective
8 Iteration log	✅	"Why this copy revision" decisions still valuable
9 Reverse verification	❌	Breaking the anchor does not necessarily produce red (no clear oracle)
10 sql-only-trace	❌	Subjective projects usually do not use DB logic
11 Business contract freezing	⚠️ Partial	"Contract" concept on subjective side is closer to "style guide"

Subjective projects typically use 5-6 of the 11 pieces. A rubric-driven workflow + iteration log fits better than pinning test + governance rule.

Hybrid projects

Many projects are hybrid. Examples:

My CS-SAAS: objective business logic (inventory / pricing / orders) + subjective blog copy
Design tool: objective UI behavior + subjective aesthetics / typography
Recommendation system: objective infra (instrumentation / statistics) + subjective recommendation result

Hybrid projects use "zoned governance": objective zones get the full combined set, subjective zones get rubric + iteration log.

AI Pair-Programming vs Traditional Development Differences

Why does AI pair-programming need trace lock more? Four structural differences.

Difference 1 (no muscle memory)

A traditional developer who has spent 6 months in a codebase has "if I change X, also touch Y" baked into muscle memory. Grep is no longer needed every time; past pitfalls are avoided instinctively.

An AI agent enters every new conversation with empty context. A bug fixed last week is rediscovered from symptom-grep this week. Institutional memory does not exist at all.

Trace lock externalizes muscle memory into AI-readable artifacts (registry markdown + skill auto-trigger), so each new conversation inherits the previous one's "muscle memory" directly.

Difference 2 (no colleague pings)

Traditional teams have Slack threads, code review comments, standups. A senior dev seeing a PR will proactively ping "remember to also update Y after editing X".

AI has no senior dev on Slack. Governance rule + skill auto-trigger is the engineering means of supplying the "senior dev on Slack" role.

Difference 3 (context window has a ceiling)

A traditional developer does not "forget" after reading the codebase. An AI agent's context window has a hard cap (~200k tokens); long conversations drop early decisions.

Iteration log + registry externalize decisions into markdown, so the next conversation reloads from markdown.

Difference 4 (AI is more prone to "change without understanding why")

When AI sees a red test, the instinct is to edit the test (turn it green) rather than ask "why was this test written this way".

Business contract freezing (piece 11) + a // Why: contract pinned after last week's bug; read incident pinning case before editing comment inside the trace test is what blocks the instinct. Without that comment, AI two turns later will flip expect(x).toBe(true) to expect(x).toBe(false) because "the test was red".

Concrete value-add to AI pair-programming

AI pair-programming pain        →  Combined-pattern piece
Unaware of N cross-layer rels   →  Offensive audit + piece 1 Registry
Unaware editing X breaks Y      →  Piece 2 Trace test + piece 5 skill auto-trigger
Internal helper edit unblocked  →  Piece 3-4 Governance rule
Unsure why this was written     →  Piece 8 Iteration log + piece 11 Decision Pinning
Red test → edit the test        →  Piece 11 + trace test's incident pinning comment

Every pain point has a corresponding piece.

Cross-Stack Mapping

When porting pieces to a different stack, the skeleton stays but the implementation gets swapped.

Trace test framework mapping (piece 2)

Source (Vitest)	Target framework	Main replacement
`describe / it / expect`	Jest	Same, near-zero edits
`describe / it / expect`	Mocha + Chai	`expect(x).toBe(y)` → `expect(x).to.equal(y)`
`describe / it / expect`	Pytest	`def test_xxx():` + `assert x == y`
`describe / it / expect`	RSpec	`describe "x" do; it "..." do; expect(x).to eq(y); end; end`
`describe / it / expect`	Go testing	`func TestXxx(t *testing.T)` + `if x != y { t.Errorf(...) }`

The 5-section structure (imports anchor / setup current state / assert behavior / future regression catch / why-comment) is framework-independent.

Governance rule script mapping (pieces 3-4)

Source (Node.js)	Target	Main replacement
`fs.readFileSync` + `/regex/`	Python	`open().read()` + `re.compile(...)`
Node script	Ruby rake task	`Rake::Task` + `File.read` + `Regexp`
Node script	bash + grep / awk	Feasible but regex syntax inconsistent; recommend ripgrep + logic layer in bash
Node script	Go binary	`os.ReadFile` + `regexp.MustCompile`

CI integration: every stack supports pre-push hook (Husky / pre-commit / lefthook) + GitHub Actions / GitLab CI.

Caller exemption comment convention (piece 7)

Source	Target	Replacement
JS `// @xxx-ok: reason`	Python	`# noqa: xxx-ok reason` (or custom)
JS	Go	`//go:build xxx-ok` or custom build tag
JS	Ruby	`# rubocop:disable Style/Xxx` style

The key: the rule is "a comment containing the xxx-ok token is recognized by the governance rule as a valid bypass", language-independent.

Cross-database mapping (piece 10 sql-only-trace)

This is the most stack-specific piece.

Database	Corresponding mechanism	Main difference
PostgreSQL	`DO $$ ... $$ LANGUAGE plpgsql` block	My environment, full support
MySQL	`DELIMITER // PROCEDURE ... //` + manual CALL	No DO block; needs CREATE PROCEDURE + CALL two-step
SQLite	No stored procedure; use BEGIN TRANSACTION + application-layer driver	Most restricted
MongoDB	No SQL; use aggregation pipeline or mongosh script	Different paradigm entirely
Snowflake / BigQuery	Stored procedures exist, syntax differs	Cloud DW; CI integration harder

The SET LOCAL mechanism (RLS JWT simulation) is PostgreSQL-specific. MySQL / SQLite have no equivalent; you must test through the application layer or bypass RLS.

If your stack is not PostgreSQL, downgrade piece 10 to "application-layer integration test" (run tests via Node / Python / Ruby code connecting to the DB). The skeleton stays "SEED / RUN / ASSERTIONS / CLEANUP" but sits in the application layer instead of SQL.

When Not to Use (A2 + B2 + new additions = 8 cases)

A2 line 327-337 lists 5 cases (against pieces 1-5); B2 line 650-660 lists 5 cases (against pieces 6-11). Merging, deduplicating, and adding fresh cases gives 8.

1. Hackathon / spike / project less than 1 month old

Forgetting "3 months later" does not exist; pinning via commit message + PR description is sufficient. The setup cost of the combined pattern (A2 takes about 3-4h, B2's first gap takes 6-9h) exceeds the entire short-project development time.

2. Only 1-2 cross-layer relationships

Writing to commit message or PR description is enough; building a registry is over-engineering. The offensive audit also will not reach N BLOCKER (no N traces to find), so ROI does not justify the work.

3. No markdown-friendly editing pipeline

If the team lives in Notion / Confluence, syncing git and wiki in parallel becomes a drag. Piece 1 (Registry) and piece 8 (Iteration log) both assume markdown is first-class; moving them to Notion / Confluence is infeasible (no git diff, no CI integration).

4. CI pipeline cannot accept custom scripts

Pieces 3-4 (Governance rule) depend on pre-push hook or CI enforcement. If the CI pipeline cannot accept custom Node / Python scripts, governance rules have nowhere to attach and slowly drift to "ignored".

5. No AI pair-programming

Piece 5 (AI reminder skill) targets the integration points of Claude Code / Cursor and similar tools. Without such a tool the skill has nowhere to attach. The pattern drops to "9 applicable pieces" and still works, but loses the "auto-trigger" key property.

6. Business contract churns extremely fast

In scenarios where helper algorithms change weekly, trace test pinning becomes a drag. Stabilize business first, then lock. This is dimension 4's "weekly change" case.

7. Multi-person maintenance with strong code review culture

Traditional code review already plays part of the governance role. The combined pattern still works but with diminishing marginal returns, so ROI needs evaluation. This is dimension 1's 10+ organization case.

That said, piece 1 Registry + piece 8 Iteration log remain applicable (for organizational memory), and can be adopted standalone.

8. Pure read-only reporting systems / pure ML data pipelines

Two C1 cases:

Pure read-only reporting systems have no write path, so there is no "data flow" and no trace to build
Pure ML / data pipelines benefit more from data-contract tests (Great Expectations / Pandera). The "business contract" concept of the combined pattern does not fit ML pipelines well

Real Porting Cost Estimate (from my environment to a new project)

Suppose I take on a contract and want to apply the combined pattern to a new Vue + Supabase project (same stack).

Phase	Estimated time	Content
1. Copy 9 framework-layer pieces	1h	Copy governance-guard.mjs / .claude/skills/ / registry markdown template / iteration log template
2. Update paths and env vars	0.5h	repoRoot logic / registry path / trace test directory
3. Write first trace (client-specific)	1-1.5h	Run 5-step audit + write registry block + trace test
4. Write first helper (business-specific)	1-2h	Extract pure-function logic from scattered callers
5. CI / pre-push hook integration	0.5h	Add governance-guard.mjs to hook
6. Write the matching CLAUDE.md rule	0.5h	Document "this project's trace nodes are X; AI must run 5-step audit before editing"
Total	4.5-6h	Includes first trace + first helper

Each subsequent trace is roughly 30-45 min (A2 estimate); each gap fix using the 6-piece pattern is roughly 1-1.5h (B2 estimate).

Switching to a different stack (e.g. Next.js + Prisma + Jest) adds 2-3h of replacement work (trace test syntax / governance rule regex / sql-only-trace variant).

ROI break-even: in my environment it is 2-3 months (past 6 months: 5 cross-layer bugs × 3-5h = 15-25h of fixing). The break-even on a new project depends on cross-layer bug frequency: six months without a cross-layer bug is unprofitable; six months with 3+ such bugs is clearly profitable.

If You Want to Try This

The order I would suggest (extending C1's suggestion with engineering details):

Phase 1 (Defense, 3-4 hours)

Copy the 9 framework-layer templates (about 1h)
Write the first trace (about 1.5h), choosing the one closest to "the bug I hit in the past 6 months"
Integrate pre-push hook + CI (about 0.5h)
Validate: intentionally break the trace test's expected value → governance should turn red → revert → should turn green

Phase 2 (Offense, 5-10 hours)

Wait until business produces 2-3 cross-layer bugs, then run the first offensive audit
Use the business-flow-audit-fix skill to run 11 chain-nodes × 3 questions audit
Fix each BLOCKER using the 6-piece pattern
Write sprint summary

Phase 3 (Steady state, one offensive sprint per quarter)

Run one offensive audit per quarter to keep system entropy from rising
Routine code change is auto-protected by the defensive side

Not sure whether it is worth doing?

C1's heuristic: did the past 6 months produce ≥ 3 "cross-layer contract drift" bugs? Yes → worth it. No → maybe not yet.

The engineering version adds a quantified indicator: the cumulative hours spent fixing cross-layer bugs in the past 6 months.

Cumulative < 5h: skip this pattern
Cumulative 5-15h: consider Phase 1 (defense only)
Cumulative 15-30h: consider Phase 1 + Phase 2
Cumulative 30h+: strongly recommend the full set

My own past 6 months totals about 25-30h.

I am also unsure whether this threshold transfers to your situation. But "cumulative bug-fix hours" is a more honest signal than "count" (5 small bugs differ from 1 big bug).

C1 · Combined Offense + Defense (plain version) (series same-topic plain version)
A2 · Defense Engineering Edition: 5 artifacts implementation details (series same-branch, two posts earlier)
B2 · Offense Engineering Edition: 6-piece fix pattern (series same-branch, previous post)
D · sql-only-trace Engineering Edition: testing DB-side pure logic (series same-branch, next post)
Chinese version

About this post

This post is an organized record of conversations I had with Claude (an AI pair-programming tool) during May 2026. I noticed some patterns worth keeping for my own future reference, so I asked Claude to help structure them into writing.

A few things I'm not claiming:

Terms used in this post (11-piece matrix / framework layer vs content layer / objective vs subjective projects / combined pattern / Trace Lock / business contract freezing / Decision Pinning / 6-piece fix pattern / 5 artifacts / sql-only-trace / AI reminder skill / reverse verification anchor break) are working names I gave them myself, not industry-standard terminology
My system has a specific shape (solo-maintained, many cross-layer dependencies, ambiguous business contracts). These patterns may not apply to your context
I'm not a software engineer — just a barista who pairs with AI to write code

If a professional engineer spots misuse, or there's already a more standard name for any of these concepts, I genuinely welcome corrections.

本文原載於我的部落格：Combined Offense + Defense (Engineering Edition) — Cross-Project Reuse Matrix and When Not to Use

Offense + Defense combined — How the two pair up (for self-taught developers)

Dexterlung — Wed, 29 Jul 2026 14:05:15 +0000

May 2026 · Series "Trace Lock — Governance notes from pairing with AI to write code" · Post 4 of 9

The previous two posts covered the two trace-lock modes separately:

A1 Defensive: after tripping on a cross-layer bug once, lock that relationship down so it doesn't rot
B1 Offensive: proactively audit a business flow, find unprotected chain nodes, fix them all at once

This post covers "how the two pair up." It's also what I saw when I looked back after the offensive sprint Claude and I finished over the past 2 weeks.

This is still the plain-language version for self-taught developers. Engineering details (cross-project reuse matrix, what counts as transferable helper patterns, governance rule templates) go into C2 (engineer-facing).

Why covering them separately isn't enough

After writing the A1 post and re-reading it, I noticed a gap:

The defensive mode sounds complete on its own. Five artifacts, registry, fuse test, two governance rules, AI reminder skill. Locking one cross-layer relationship takes 30-45 minutes. By the 7th lock, the total time invested breaks even.

But there's an unanswered question: How do I know which relationships still need locking but haven't been locked yet?

The defensive mode answers "how to lock a known relationship." It assumes you already know which one will break. But most of the time I have no idea how many cross-layer relationships my codebase actually contains, let alone which ones are most dangerous.

It took the 5th cross-layer bug on May 25th for me to stop and ask Claude "should I go looking for other leaks of the same shape." That question opened the offensive sprint, expanding from 13 known traces to 17 plus fixing 4 BLOCKERs.

In other words: without offensive, defensive only reacts to bugs already hit. Without defensive, the BLOCKERs offensive finds get re-fixed next month. The two are one set; neither is complete alone.

Division of labor between the two modes

Claude and I summarized the two roles in this table:

Dimension	Offensive (B1)	Defensive (A1)
Trigger	I say "audit the whole X chain"	I (or AI) touch any node listed in trace nodes
Frequency	Once a month or quarter (planned, low frequency)	Every code change auto-triggers (high frequency)
Output	N new traces locked (range expansion)	Existing traces don't rot (range maintenance)
Target	A whole business flow (containing N chain nodes)	A single trace (one node in one chain)
Cost per use	7-15 hours (audit + fix + summary)	30-45 minutes (5-step checklist)
Cost of not having it	Trace count stays flat, always chasing known bugs	Existing traces rot, fixing one breaks another

The division is clean. Offensive handles "advancing into new territory." Defensive handles "holding territory already taken."

Four states of system entropy

When Claude and I discussed ROI, I sketched another mental model. System entropy (total bugs + hidden debt) over time:

No offense, no defense: entropy increases monotonically. Bugs accumulate faster than I fix them
Offense only: entropy oscillates. Each sprint reduces it temporarily, climbs back up months later
Defense only: entropy stays flat at current level. Existing traces held, but no expansion
Offense + defense: entropy decreases monotonically. Quarterly offensive expands lock coverage, daily defensive maintains, overall trend down

I only saw this picture after the sprint was done. While writing the code I was just thinking "fix these 4 BLOCKERs," not realizing system entropy had actually started trending down for the first time.

The real sprint's numbers

Pulling the past 2 weeks onto a timeline:

Day 1 (defensive emerges):

Customer screenshot: "half-pound drip bag only orderable as 1 pack"
30-minute fix
But realized this was the 6th similar bug in 6 months
Discussed with Claude "why does this keep happening" → arrived at the 5-artifact Trace Lock pattern
4 hours to build registry + first trace (T-018) + two governance checkers + skill

Day 2-3 (defensive expands to 13 traces):

Backfilled past cross-layer bugs into traces one by one (T-001 to T-017, partially historical events)
30-45 minutes each
13 locked. Self-estimated chain protection: 30% → 45%

Day 4 (offensive emerges):

5th cross-layer contract bug hit (roasting station "complete" button frozen)
Asked Claude "can we find all same-shape leaks at once"
1-hour audit → 11 chain nodes → 4 BLOCKERs

Day 4-5 (offensive sprint):

7 hours to fix all 4 BLOCKERs + write sprint summary
Each BLOCKER did Decision Pinning + 6-piece fix
Also added these 4 to the defensive registry (T-019/020/021/022) → 13 expanded to 17
Self-estimated chain protection: 45% → 81%

Day 6 (the dual-blade insight):

While writing the E meta post, looking back I saw "offensive finds + defensive locks" as a closed loop
Offensive finds N at a time, defensive maintains daily, next offensive round finds new candidates
That's when I started writing this post

Total 17 days, including 4 blog posts of writing, around 50-60 hours of pure governance investment.

Why neither alone works (two counter-examples)

Counter-example 1: the defense-only developer

After locking my first 13 traces, I had a brief "OK that's probably enough" illusion.

The reality: those 13 were all "known bugs hit in the past 6 months." A bug I lock is one a customer already tripped on. Bugs not yet hit are completely outside the defensive perimeter.

Stopping there, 6 months later I'd hit bug #14, #15, each taking 3-5 hours to fix, then backfilled into the registry. Defense scope always trails the bugs. At best, you break even.

Counter-example 2: the offense-only developer

Suppose I run an offensive audit every quarter, find 5 BLOCKERs, fix them all. But I have no defensive registry, no tests, no governance rules.

3 months later, I (or an AI) change some internal logic of a helper. No fuse test fails. No governance rule warns. The change ships to prod, no one notices. The next audit re-discovers this as a new BLOCKER.

The offensive sprint becomes "perpetually re-fixing the same set of bugs." Marginal cost never goes down.

The closed loop when used together

Offensive audit
    ↓
Find N BLOCKERs
    ↓
Decision Pinning
    ↓
6-piece fix + add to defensive registry
    ↓
Defensive maintains daily (fuse test + governance + skill)
    ↓
Next quarter's offensive doesn't re-find these N, only finds new candidates
    ↓
Coverage expands continuously, marginal cost drops

Run this loop for 6-12 months, codebase shifts from "decays as I change it" to "stabilizes as I change it."

Cross-project application: what transfers, what doesn't

The next context I'd try this in is a different project (if I take on consulting work). Predicting in advance:

Directly copyable (framework layer):

Registry markdown format
The two governance checker script skeletons
AI reminder skill's frontmatter structure
6-piece fix pattern step order
Iteration log's 5-section structure

Not copyable (content layer):

Specific traces (every project's business flows differ)
Specific helpers (depends on domain logic)
Specific business contracts (every company / product makes different decisions)
Migrations / RPC details

In short: 80% framework transfers, 20% content must be re-invented.

Time estimate for moving to a new project: ~4-6 hours to set up framework + first trace. Each subsequent trace takes about the same as in the original project (30-45 minutes).

Where this doesn't apply (don't take the wrong lesson)

There's a risk in writing this far. Readers might think "every project should use trace lock." That's not what I'm claiming.

Scenarios I can think of where it doesn't fit:

Pure read-only reporting systems: no write path means no "data flow," nothing to trace
Early-stage MVP under 2 months old: business contracts aren't stable yet, traces break frequently and slow iteration
5+ person team with strong code review culture: governance overlaps with human review, value drops (unless you use governance as a "save reviewer attention" tool)
Pure frontend SaaS with no DB: few cross-layer dependencies, trace nodes mostly in frontend, regular hooks/composables extracted as helpers is enough
Pure ML / data pipeline: the "business contract" concept doesn't quite apply, data contract tests (Great Expectations / Pandera) fit better

If your project matches any of those, you don't need this. Trace lock works best for "many cross-layer dependencies + ambiguous business contracts + AI pair programming." It's not a universal solution.

A transferable suggestion

If you decide to try the dual-blade approach, the order I'd recommend:

Defensive first (3-4 hours to build framework + first trace). Simpler, immediate protection.
Lock 1-2 traces to learn the pattern, don't start with 10
After your business surfaces 2-3 cross-layer bugs, run the first offensive audit (5-10 hours)
Quarterly offensive to keep system entropy from rising

Not sure if it's worth the investment? My own heuristic: have you had ≥ 3 "cross-layer contract drift" bugs in the past 6 months? Yes → worth it. No → maybe not yet.

I'm not sure if this threshold generalizes. But under 3, fixing one at a time might be cheaper than building governance.

Why this works well for AI pair programming

Both ends of the dual-blade are AI-friendly:

Offensive: I ask Claude "audit the whole X chain," it has the business-flow-audit-fix skill that auto-runs the 11-node × 3-question audit + 6-piece fix
Defensive: AI touches any node in trace nodes, trace-lock-modify skill auto-triggers, reminds it to run the fuse test first

I don't have to re-explain governance rules each time. Skill written once, AI inherits it next session.

Compared to "please AI be more careful" (which is not executable), "write the audit flow into a skill so AI auto-runs it" is an executable engineering intervention.

What this series will expand on next

The next few posts rewrite the plain-language content from an engineering angle (for engineers who've written governance rules / pinning tests):

A2 Defensive engineering version: 5-artifact implementation details (how the registry parser script is written, fuse test's 5-section structure, the two governance rules' code)
B2 Offensive engineering version: 6-piece fix pattern templates (pure-function helper / trace test / governance rule / caller exemption list / iteration log)
C2 Cross-project reuse matrix (each artifact's transferability rated 0-5 stars)
D sql-only-trace: how to test pure-DB logic (why unit test frameworks aren't enough, gotchas of running SQL tests via Supabase CLI)

If you're an engineer who wants implementation details after reading this, those 4 posts are for you.

A1 · Defensive Trace Lock — How to lock down a cross-layer relationship (one of the previous two in series)
B1 · Offensive audit — How to periodically audit a whole business flow (previous in series)
中文版

About this post

A few things I'm not claiming:

Terms used in this post (dual-blade / offensive / defensive / Trace Lock / Decision Pinning / 6-piece fix pattern / sql-only-trace) are working names I gave them myself, not industry-standard terminology
My system has a specific shape (solo-maintained, many cross-layer dependencies, ambiguous business contracts). These patterns may not apply to your context
I'm not a software engineer, just a barista who pairs with AI to write code

If a professional engineer spots misuse, or there's already a more standard name for any of these concepts, I genuinely welcome corrections.

本文原載於我的部落格：Offense + Defense combined — How the two pair up (for self-taught developers)

Offensive audit, engineering edition — the 6-piece fix pattern in detail

Dexterlung — Wed, 29 Jul 2026 13:05:17 +0000

May 2026 · Series "Trace Lock — Governance notes from pairing with AI to write code" · Post 7 of 9

This post is the engineering version of B1 Offensive audit.

B1 covered "why audit the whole chain" and "what the overall flow looks like." This post unpacks the 6-piece fix pattern (working name, my own tentative term) used to actually fix each gap that the audit surfaced: the code shape of each piece, why it is designed that way, what you can tune.

Written for engineers already familiar with governance rules, pinning tests, or incident-driven tests. If you have a non-engineering background, B1 is more suitable.

My stack: Vue 3 + Vite + Vitest + Supabase (PostgreSQL) + Node.js scripts. But the 6-piece pattern below is mostly independent of the frontend framework. It depends on filesystem conventions + regex parsers + CI-enforced execution (pre-push hook + GitHub Actions).

The 6 pieces at a glance

#	Piece	Role	File type
1	Pure-function helper	Extracts business logic into a side-effect-free function	`frontend-app/src/lib/business-rules/<feature>Logic.js`
2	Trace test (fuse test)	Pins the business contract + incident pinning cases	`frontend-app/src/__tests__/traces/T{NN}-*.trace.test.js`
3	Governance rule	Pre-push enforces callers route through the helper (or carry an explicit exemption)	`checkXxxCoverage()` in `scripts/governance-guard.mjs`
4	Caller exemption comment	Legitimate carve-out for thin wrapper / docstring scenarios	`@xxx-ok: <reason>` comment in source
5	Registry entry	The directory entry for this trace	`## Critical Traces` block in `文檔/data-source-registry.md`
6	Iteration log	The record of how this gap was fixed (for the future you)	`文檔/iteration-logs/<sprint>/NN_Gap-N_<topic>.md`

The set corresponds to the pipeline: a. offense surfaces gap → b. freeze business contract → c. extract pure function → d. pin behavior → e. force callers → f. record decision.

Mapping to A2's 5 artifacts

A2's 5 artifacts (registry + trace test + 2 governance rules + AI reminder skill) solve "rot protection for a single, known trace."

B2's 6 pieces solve "starting from zero, discover and fix N gaps in one sprint." Differences:

A2 does not need a helper (the trace usually already has an SSOT)
B2 must add a helper (gaps are gaps precisely because no SSOT exists yet; logic is scattered)
A2 does not need an iteration log (single trace, one-time setup)
B2 must add an iteration log (multi-gap repair process needs to record escalations, reverse verification, self-checks)

The two sets compose. B2 surfaces and locks new gaps; A2 prevents subsequent rot.

What breaks if you skip any one piece

Missing	Consequence
Pure-function helper	Business logic stays scattered; the next caller re-implements it
Trace test	The frozen contract lives only in your head; in 3 months you forget why
Governance rule	New callers bypass review and the helper is decorative
Caller exemption	Some thin wrappers / cross-domain files get false-positive governance violations
Registry entry	No central directory; AI does not know which files belong to which trace
Iteration log	The context for "why cash-first instead of bonus-first" evaporates

No single piece is sufficient on its own. The closed loop forms only when all 6 are present.

Piece 1 (Pure-function helper): extraction logic

Once business logic is embedded in a Vue component, composable, or RPC handler, it carries side effects (DB reads/writes, store mutations, emits). The trace test cannot get a clean test target.

The design conventions for pure-function helpers:

// frontend-app/src/lib/business-rules/cancelOrderLogic.js

/**
 * Calculate refund allocation (cash first, bonus second)
 *
 * @param {object} params
 * @param {number} params.refundAmount - Total to refund
 * @param {number} params.originalDeductFromCash - Original cash deduction
 * @param {number} params.originalDeductFromBonus - Original bonus deduction
 * @returns {{ refundToCash: number, refundToBonus: number }}
 */
export function calculateRefundAllocation({
  refundAmount,
  originalDeductFromCash = 0,
  originalDeductFromBonus = 0,
}) {
  const refundToCash = Math.min(refundAmount, originalDeductFromCash)
  const refundToBonus = Math.min(refundAmount - refundToCash, originalDeductFromBonus)
  return { refundToCash, refundToBonus }
}

A few engineering details.

Zero side effects + dependency injection

The helper does not call useUserStore(), supabase.from(...), or window.localStorage directly. All dependencies are passed via parameters:

// Counter-example (coupled to store)
export function calculateRefundAllocation(refundAmount) {
  const userStore = useUserStore()
  const cash = userStore.user.deductFromCash  // side effect
  ...
}

// Correct (pure function)
export function calculateRefundAllocation({ refundAmount, originalDeductFromCash }) { ... }

Reason: trace tests can call the helper with fixture data without mocking the store or Supabase. Test cost drops by an order of magnitude.

Place under `lib/business-rules/`, not `composables/`

composables/ is a Vue convention; functions there return refs / reactives to components. lib/business-rules/ is plain JS, completely decoupled from Vue.

If in the future you want to:

Reuse the helper in an Edge Function (Deno runtime, cannot import Vue)
Reuse in SSR (no window)
Write unit tests (no need to spin up Vue test utils)

Placing it correctly makes reuse painless.

Naming convention: `<feature>Logic.js`

✅ cancelOrderLogic.js / roastingLifecycle.js / packagingTaskState.js
❌ cancelOrderUtils.js / refundHelper.js / roastingService.js

The Logic suffix lets a single grep find every SSOT helper. Utils / Helper / Service are too generic and hard to audit.

When not to extract a helper

Logic is called from only one place. Extracting scatters the reader's attention
Logic is purely a read or has no branches. Inlining is fine
Logic lives mostly in the DB (e.g., FIFO consumption). Go with the sql-only-trace variant (covered later)

Piece 2 (Trace test): the 5-section structure

A trace test is a vitest spec, but its structure is stricter than a typical unit test. Each describe block corresponds to a semantic facet. Here is the skeleton of T-019 order-cancel / refund:

// frontend-app/src/__tests__/traces/T019-cancel-refund.trace.test.js

import { describe, expect, it } from 'vitest'
import {
  calculateRefundAllocation,
  findRoastingOrdersToCancel,
} from '../../lib/business-rules/cancelOrderLogic'  // import anchor SSOT

describe('T-019 trace: refund allocation frozen contract (incident pinning)', () => {
  it('Original cash $30 + bonus $50 → refund $80 = cash 30 + bonus 50', () => {
    const result = calculateRefundAllocation({
      refundAmount: 80,
      originalDeductFromCash: 30,
      originalDeductFromBonus: 50,
    })
    expect(result).toEqual({ refundToCash: 30, refundToBonus: 50 })
  })

  it('Partial refund $40 = cash 30 + bonus 10', () => {
    expect(calculateRefundAllocation({
      refundAmount: 40,
      originalDeductFromCash: 30,
      originalDeductFromBonus: 50,
    })).toEqual({ refundToCash: 30, refundToBonus: 10 })
  })
})

describe('T-019 trace: legal paths', () => { /* ... */ })
describe('T-019 trace: illegal paths / edge cases', () => { /* ... */ })
describe('T-019 trace: cascading cancel rules for roasting_orders', () => { /* ... */ })
describe('T-019 trace: cross-layer structural contract', () => { /* ... */ })

What each section is for

Section 1 (Business contract, incident pinning)

Take the conversation that froze the business contract and write it as test cases. Each BLOCKER gap's decision was made by the user on the spot (e.g., "refund fills cash first"); this section directly pins that decision. Three months later, anyone changing the algorithm flips the test red and is forced to see the decision context.

Each trace test gets at least 1 case here, often 5-10 (covering happy paths + boundaries).

Section 2 (Legal paths)

Variations of the full happy path. For example, T-022 packaging-task state machine has legal transitions pending → in_progress → completed and pending → cancelled. This section tests 4-6 legal paths.

Section 3 (Illegal paths / edge cases)

null / undefined / empty array / missing fields / negatives / very large numbers. This section guards against future AI edits regressing edge cases.

When writing this section, actively ask: "what does the helper do on dirty input? should it throw?" The answer is usually "return a safe fallback, do not throw" (because throws blow up the UI catch chain), but pin it in tests.

Section 4 (Role / permission / business rule limits)

If the gap involves role limits (e.g., T-022 specifies "rollback completed → cancelled is admin/super_admin only"), this section tests the allow/deny matrix per role.

Not every trace needs this section. Pure calculation logic can skip it.

Section 5 (Cross-layer structural contract)

The contact surface between the helper and the DB schema, other helpers, or other anchors. For example, the T-022 state string must align with valid values inside packaging_jobs.tasks JSONB on the DB. This section pins that the helper-known state set matches the DB expectation.

This section catches "helper drifted from DB schema."

Why the test must import the anchor

The first line of a trace test imports the function from lib/business-rules/<feature>Logic. Governance rule B (covered in A2) checks that every trace test really imports the anchor path declared in the registry.

A trace test that does not import the anchor is testing a copy-pasted copy of the logic. The anchor moves, the test does not see it, the trace rots while the test still goes green.

Target 15+ cases per trace

From the fulfillment-chain-fix sprint: T-019 / T-020 / T-022 averaged 30-38 cases. At least 1 pinning case in Section 1; 3-5 cases each in other sections.

Less than 15 usually means the business contract is not crisp enough. Go back to the audit map and refine.

Piece 3 (Governance rule): code template

Each gap gets a governance rule in scripts/governance-guard.mjs. The pre-push hook and GitHub Actions both run it.

A trimmed-down version of M5 (Cancel/Refund Helper Coverage):

// scripts/governance-guard.mjs
// M5 Cancel/Refund Helper Coverage (2026-05-25, T-019)
//
// Motivation: cancel-order + complete-refund both involve wallet reverse-deduction
//             and cascading cancel of roasting_orders. Callers must route through
//             cancelOrderLogic. No scattered re-implementation.
//
// Rule: any source file matching `fn_cancel_order_atomic` or `fn_admin_complete_refund_atomic`
//       must (a) import cancelOrderLogic OR
//            (b) carry `@cancel-refund-ok: <reason>` exemption

function checkCancelRefundHelperCoverage(violations) {
  const files = collectFiles('frontend-app/src', /\.(js|vue)$/)
  const RPC_PATTERN = /fn_cancel_order_atomic|fn_admin_complete_refund_atomic/

  for (const relativePath of files) {
    if (/(__tests__|\.test\.js|\.spec\.js)/.test(relativePath)) continue
    if (/(^|\/)dist\d?\//.test(relativePath)) continue

    const abs = path.join(repoRoot, relativePath)
    const src = fs.readFileSync(abs, 'utf8')
    if (!RPC_PATTERN.test(src)) continue

    const hasHelperImport = /cancelOrderLogic|calculateRefundAllocation/.test(src)
    const hasExemption = /@cancel-refund-ok\s*:\s*\S{5,}/.test(src)

    if (!hasHelperImport && !hasExemption) {
      violations.push({
        file: relativePath,
        line: 0,
        message: `M5 Cancel/Refund Coverage: this file calls cancel/refund RPC but does not import cancelOrderLogic or carry @cancel-refund-ok exemption`,
        excerpt: 'cancel/refund RPC call without helper / exemption',
      })
    }
  }
}

checkCancelRefundHelperCoverage(violations)  // call from footer

Design points

Header docstring must contain 3 things: creation date + corresponding trace ID + motivation. The motivation must be a business motivation, not "to pass CI."

Use a disjunction regex for RPC_PATTERN: combine multiple RPC names with | instead of src.includes('fn_a') || src.includes('fn_b'). Cleaner and faster.

Skip __tests__ / dist: trace tests themselves import helpers and mention RPC names; without skipping they false-positive. dist is build output, wasted scan.

Write hasHelperImport loose: callers might import the whole module (import * as logic) or destructure (import { calculateRefundAllocation }). Disjunction matches both.

@cancel-refund-ok must satisfy \S{5,}: empty reasons are forbidden (covered as a trap below).

BLOCKER vs advisory

Sprint experience: trace-lock-related rules are all BLOCKER (pre-push exits 1, blocks push). Reasons:

Traces are, by design, "supposedly stable" relationships
Advisory equals not enforced. In 3 months it will be ignored
BLOCKER false positives are handled by exemption comments (low cost)

Exception: pure-text / docs / transition-period lint rules can be advisory. Trace lock is not in that category.

Piece 4 (Caller exemption comment): two traps

The exemption comment is a legitimate escape hatch around a governance rule. The format:

// useOrders.js
// @cancel-refund-ok: thin-wrapper-only — this composable only relays RPC results to the UI
export async function cancelOrder(orderId) {
  return supabase.rpc('fn_cancel_order_atomic', { p_order_id: orderId })
}

Exemptions should be a minority. Two main categories:

Thin wrapper: caller only relays results, no business logic
Docstring mention: helper JSDoc references an RPC name but does not actually call it

Trap A: the `\S{5,}` regex and Chinese text

The exemption regex @xxx-ok\s*:\s*\S{5,} requires 5 consecutive non-whitespace characters after the colon. Chinese text like "此 helper 只透傳" has a space after the first character; regex sees one character then a break, fails to match.

Fixes:

// fails — Chinese chars + space
// @cancel-refund-ok: 此 helper 只透傳

// passes — dash-connected English
// @cancel-refund-ok: thin-wrapper-only

// passes — 5+ Chinese chars with no whitespace
// @cancel-refund-ok: 此處僅透傳上游結果不涉及計算

In practice, the sprint adopted dash-connected English: short phrases parse easily, remain readable when audit:all output is truncated, and are unambiguous in regex.

Trap B: docstring mentions of RPC names false-positive

A helper's JSDoc may mention related RPC names (to help IDE jump-to-definition) without the helper actually calling them. The governance RPC_PATTERN regex cannot tell the semantic difference and false-positives.

Example:

/**
 * cancelOrderLogic
 *
 * Atomic RPCs: fn_cancel_order_atomic (migration 238)
 * Atomic RPCs: fn_admin_complete_refund_atomic (migration 239)
 */
// @cancel-refund-ok: docstring-mention-only — this helper only mentions RPC names in JSDoc for IDE jumps, does not call them

export function calculateRefundAllocation(...) { ... }

Fix: add the exemption comment at the top of the helper file, reason docstring-mention-only. Governance sees the exemption and skips.

Sprint statistics: 4 gaps used 5 exemptions total (thin wrapper × 3 + docstring-mention × 2), under 10% of total callers. Exemptions should not become the norm; most callers should import the helper.

Piece 5 (Registry entry): align with existing format

Add a block for the new trace under ## Critical Traces in 文檔/data-source-registry.md. Format aligns with existing T-001 (covered in A2), plus a few sprint-discovered supplements:

### T-019: Order cancellation / refund SSOT

- **Type**: data-flow trace
- **Status**: locked (2026-05-25)
- **Anchor SSOT**: [`frontend-app/src/lib/business-rules/cancelOrderLogic.js`](../path)
- **Trace test**: [`frontend-app/src/__tests__/traces/T019-cancel-refund.trace.test.js`](../path)
- **Governance**: M5, Cancel/Refund Helper Coverage
- **Related migration**: 238, 239
- **Frozen business contract**:
  - Refund allocation: cash first, bonus second (frozen by user 2026-05-25)
  - Cascading cancel: source_orders contains only this order → cancel whole roasting; multiple orders share → remove share only
- **Trace nodes**:
  1. RPC `fn_cancel_order_atomic`
  2. RPC `fn_admin_complete_refund_atomic`
  3. helper `calculateRefundAllocation`
  4. helper `findRoastingOrdersToCancel`
  5. caller `useOrders.cancelOrder`
  6. caller `useRefunds.completeRefund`
- **Related incident**: half-year-old partial-refund wallet imbalance event
- **Last edited**: 2026-05-25

Two extra fields compared to A2's T-001

Gap-fix-produced traces add two fields compared to the T-001 format in A2.

Governance: the governance rule ID for this trace. IDs M5 / M6 / M7 / M8 in the registry must match governance-guard.mjs exactly. Future audits scan the registry and immediately see which rules pair with which trace.

Frozen business contract: a bullet list of the decisions the user pinned on the spot. This is the biggest value-add over A2's 5 artifacts: every frozen decision is permanently traced.

Writing `Trace nodes`

Includes RPCs (DB write side) + helpers (pure-function SSOT) + callers (frontend composable / page). Order roughly follows "DB → UI," but in practice they interleave. This field mostly tells AI which files belong to this trace; ordering strictness is looser than the T-001 in A2.

sql-only-trace variant

When a gap has no frontend helper (pure DB logic, e.g., T-021 FIFO consume):

- **Type**: sql-only-trace
- **Trace test (SQL)**: [`scripts/integration-test/T021-fifo-consume-test.sql`](../path)

Type becomes sql-only-trace. Trace test becomes Trace test (SQL). parseTraceRegistry sees both markers and skips the import check (covered later).

Piece 6 (Iteration log): 5-section structure

The iteration log records "why this gap was fixed this way." Three months later, the future you sees context here that commit messages cannot show. Filename format NN_Gap-N_<topic>.md, placed under 文檔/iteration-logs/<sprint>/:

# Iteration 1, Gap-1: Order cancel / refund (T-019)

**Completion time**: 2026-05-25 23:38

## (a) Files touched
- helper / trace test / registry / governance / caller exemption / migration with paths

## (b) Trace test result
- First run: 30/30 green
- Reverse verification A (helper anchor break): deliberately broke algorithm → N cases red → reverted → green
- Reverse verification B (governance anchor break): removed exemption → governance red → reverted → green
- Full trace suite: X files / Y cases all green
- audit:all: full PASS

## (c) Escalate log
- Traps stepped on / name alignment issues / Rule 27 signature change judgement / business contract micro-adjustment

## (d) Self-check
- Context usage estimate: ~X%
- Frozen Constraint drift check (per frozen point)
- Cross-gap consistency confirmation

## (e) Next gap
- Next gap ID + estimated time + main actions

Why none of the 5 sections can be skipped

(a) Files touched: 3 months later, commit messages do not tell you which files were part of the same fix. This section is "the artifact list for this gap."

(b) Trace test result: not just positive-green; must record the result of reverse verification anchor breaks (covered later).

(c) Escalate log: traps, name misalignments, Rule 27 judgments, Edge Function vs DB trigger trade-offs. This section is "the decision log for why A and not B."

(d) Self-check: context usage + frozen Constraints have not drifted + cross-gap consistency. If context usage exceeds 80%, time to stop & handoff.

(e) Next gap: a cue for the autonomous loop to continue.

A full iteration log per gap runs 100-200 lines, which looks like a lot but is just raw facts, 5-10 minutes to write. Three months later you thank past-you for writing it.

Reverse verification anchor break (mandatory at gap closure)

Positive verification (trace test green + governance green) only proves "current state is not broken." It does not prove the lock has catching power. Reverse verification anchor break fills that gap:

Helper anchor break: deliberately break the helper algorithm (e.g., swap cash first to bonus first) → the trace test should have at least 1 case red → revert → all green
Governance anchor break: deliberately remove the caller's exemption comment → governance should turn red → revert → all green

Both passing means "the lock really has catching power." If reverse verification does not turn red, the trace test / governance rule is too lax and needs strengthening.

Reverse verification surprises

T-022 packaging-task's first reverse verification attempt was "add skipped as a legal transition," expected to break, but PACKAGING_TASK_STATES does not include skipped → INVALID_TO_STATE still blocks → the test does not turn red.

The second attempt removed in_progress → completed legal path, which is a core transition, and the test finally broke.

Meaning: the reverse verification target must be a core contract the trace test actually protects, not an edge case. If reverse verification does not break, either pick a more central break point or backfill missing test coverage.

sql-only-trace special category (when DB logic stands alone)

When business logic lives purely inside the DB (complex triggers, FIFO RPCs, PL/pgSQL stored procedures), the 6 pieces become 5 pieces (no frontend helper) + 1 specialization (SQL test instead of vitest):

-- scripts/integration-test/T021-fifo-consume-test.sql

-- Set JWT so RPC passes is_admin / has_operator_capability
SELECT set_config(
  'request.jwt.claims',
  '{"sub":"test-user","system_role":"super_admin"}',
  false
) AS jwt_set;

DO $T021$
DECLARE
  v_errors TEXT[] := ARRAY[]::TEXT[];
  v_seed_prefix TEXT := 'T021-' || substr(md5(random()::text), 1, 8);
  -- ... seed IDs
BEGIN
  -- 1. SEED (build fixtures with unique prefix)
  -- 2. RUN (call the RPC under test, fn_consume_for_roasting)
  -- 3. ASSERTIONS (v_errors := array_append(v_errors, ...) on fail)
  -- 4. CLEANUP (explicit DELETE seed, do not rely on transaction)
  IF array_length(v_errors, 1) IS NULL THEN
    RAISE NOTICE 'T-021 ALL ASSERTIONS PASSED';
  ELSE
    RAISE EXCEPTION 'T-021 FAILED: %', array_to_string(v_errors, E'\n');
  END IF;
EXCEPTION WHEN OTHERS THEN
  -- best-effort cleanup + re-raise
  BEGIN DELETE FROM ... WHERE ... LIKE v_seed_prefix || '%'; EXCEPTION WHEN OTHERS THEN NULL; END;
  RAISE;
END
$T021$;

-- CLI db query only returns the last SELECT rows; print PASS message here so it shows
SELECT 'T-021 PASSED' AS status, 16 AS assertions, 'FIFO consume invariants' AS details;

Engineering details

JWT via set_config('request.jwt.claims', ...): before calling an RLS-gated RPC, set the JWT to simulate super_admin; otherwise permission denied.

SEED with unique prefix: 'T021-' || substr(md5(random()::text), 1, 8) ensures multiple runs do not interfere. CLI db query cannot wrap in a transaction, so cleanup must be explicit DELETE.

Best-effort cleanup: cleanup inside EXCEPTION must swallow its own errors so the original error message is not masked.

Final SELECT prints PASS: npx supabase db query --linked -f only returns rows from the last SELECT. RAISE NOTICE is not shown by default. Put the PASS string in the final SELECT to see it.

RAISE EXCEPTION for full error output: on failure, EXCEPTION carries the v_errors array string.

Registry side

parseTraceRegistry() sees **Type**: sql-only-trace and **Trace test (SQL)** and marks the trace isSqlOnly = true. Governance rule checkTraceTestImportsAnchor checks isSqlOnly and skips the import check (SQL has no import semantics).

The whole parser extension is about 10 lines of code, done as part of the sprint.

Run command:

npx supabase db query --linked -f scripts/integration-test/T021-fifo-consume-test.sql

On failure, EXCEPTION's v_errors print to CLI. On success, the final SELECT prints PASS. The pre-push hook runs this; non-zero exit blocks the push.

Engineering considerations

CLI db query limitations

Gotchas of supabase db query --linked -f:

By default does not display RAISE NOTICE, only returns the last SELECT rows
Each statement auto-commits, cannot wrap in BEGIN/ROLLBACK
Cleanup must be explicit DELETE
SEED must use a unique prefix to avoid pollution

Fix: every sql-only-trace test follows the engineering template above.

Rule 27 RPC overload risk

CREATE OR REPLACE does not remove other overloads. Changing the signature (including adding a default-NULL parameter) requires first DROP FUNCTION IF EXISTS public.foo(old signature).

Judgement:

Signature is fully equivalent (same parameter list, types, order) → no DROP needed
Any signature difference → must DROP

In the sprint, Gap-1 and Gap-2 RPC signatures stayed unchanged, no DROP. Gap-3 changed the FIFO RPC signature (added a new default-NULL parameter), so DROP before CREATE OR REPLACE.

Trigger: write warning, not RAISE

When the frozen business contract says "do not block, write a warning" (e.g., T-020 roast-level mismatch), the trigger function:

-- Old behavior (migration 177)
-- IF roast_level <> expected THEN
--   RAISE EXCEPTION '...'; -- workstation freezes
-- END IF;

-- New behavior (migration 240)
IF roast_level <> expected THEN
  UPDATE public.roasting_orders
  SET notes = CONCAT(COALESCE(notes, ''), E'\n[ROAST_LEVEL_MISMATCH] expected=', expected, ', got=', roast_level)
  WHERE id = NEW.related_id;

  INSERT INTO public.inventory_logs (action, note, ...)
  VALUES ('ROAST_LEVEL_WARNING', '...', ...);
END IF;

RETURN NEW;  -- continue write, workstation not frozen

The trace test pins the warning prefix [ROAST_LEVEL_MISMATCH]. DB and frontend share the same identifier string.

Context window management

Fixing 4 gaps used about 60-70% context (reading migrations + helpers + callers + trace tests + running reverse verification twice).

Each gap's iteration log section (d) Self-check estimates context %. At the 80% red line:

Immediately commit completed gaps
Write a handoff doc to 補充資料/CLAUDE_HANDOFF_YYYY-MM-DD_<sprint>.md
Use /clear to start a new session, run /goal --resume <sprint> to continue

Red line at 80% rather than 90% because of buffer for: writing commit messages, running full audit:all after push, finishing the handoff doc. Pushing through to 90% risks context anxiety mid-closure and partial artifacts.

Time estimate

Sprint statistics:

Audit map: 1-2h
Per gap 6-piece: 1-1.5h (including reverse verification)
Writing iteration log: 5-10 min
Sprint summary (only at ④): 1h
Skill extraction (only at ⑤): 1h

4 gaps full set: 1h audit + 4-6h fix + 1h summary = 7-9h.

Compared to the "fix one at a time" mode: 3-5h per bug × N. ROI ~5-8x (each gap blocks N future bugs).

Cross-project reusability

Piece	Reusability	Why
Pure-function helper convention (`lib/business-rules/<feature>Logic.js` + dependency injection)	★★★★★	Plain JS module convention, framework-agnostic
Trace test 5-section structure	★★★★☆	Works with Vitest / Jest / Mocha, just swap import syntax
Governance rule code	★★★★☆	Node.js script, swap RPC pattern + helper name
Caller exemption comment convention	★★★★★	Pure regex text convention, tool-agnostic
Registry entry format	★★★★★	Pure markdown convention
Iteration log 5 sections	★★★★★	Pure markdown record, tool-agnostic
Reverse verification anchor break process	★★★★☆	Pure process, but requires team / AI cooperation
sql-only-trace variant	★★★★☆	PostgreSQL-specific (DO block + JWT config), other DBs need adjustment
Concrete business contract content	★☆☆☆☆	100% business-specific, every project reinvents

Framework layer 80-90% portable, business contract 0% portable. Same ratio as A2's 5 artifacts.

For the full cross-project reusability matrix see C2 cross-project reusability (same-branch next post).

When the 6-piece set does not fit

Cases where copy-pasting the 6-piece set across is not necessarily worth it:

Single gap / single bug fix: 1h audit + 6-piece is over-engineering. Just fix and commit. The 6-piece is for sprints with 3+ gaps found in audit
No governance script infrastructure: the 6-piece set depends on pre-push hooks + CI enforcement. Without that infrastructure, build A2's 5 artifacts first
Business contracts churn very fast: when helper algorithms must change weekly, trace test pinning becomes a drag. Stabilize the business first, then lock
Solo short-term project / first month after launch: the future-self-3-months-later does not exist; commit messages + PR descriptions are enough for pinning
Multi-person maintenance + QA / code review pipeline: traditional code review already plays part of the governance role. The 6-piece set still works, but marginal value drops. Evaluate ROI

My environment (solo maintainer, many cross-layer dependencies, relatively stable business contracts, maintenance period of 6+ months, AI pair-programming) hits all the applicable conditions. Your context, you judge.

B1 · Offensive audit, how to periodically audit a whole business flow (same-topic plain version in this series)
A2 · Defensive Trace Lock engineering version, 5 artifacts in detail (same-branch previous post in this series, the companion rot-prevention mechanism)
C2 · Cross-project reusability matrix + when it does not fit (same-branch next post in this series)
中文版

About this post

A few things I'm not claiming:

Terms used in this post (6-piece fix pattern / frozen business contract / reverse verification anchor break / sql-only-trace / incident pinning case / Trace Lock) are working names I gave them myself, not industry-standard terminology
My system has a specific shape (solo-maintained, many cross-layer dependencies, ambiguous business contracts). These patterns may not apply to your context
I'm not a software engineer — just a barista who pairs with AI to write code

If a professional engineer spots misuse, or there's already a more standard name for any
of these concepts, I genuinely welcome corrections.

本文原載於我的部落格：Offensive audit, engineering edition — the 6-piece fix pattern in detail

Offensive audit — How to periodically audit a whole business flow (for self-taught developers)

Dexterlung — Tue, 28 Jul 2026 15:25:33 +0000

May 2026 · Series "Trace Lock — Governance notes from pairing with AI to write code" · Post 3 of 9

The previous post (A1 Defensive) covered "locking down a known cross-layer relationship" (defensive, reactive). This post covers the opposite direction: proactively running an audit pass to find unprotected chain nodes across a whole business flow, then fixing them all at once (offensive, proactive).

The two mental modes are complementary: A1 is "lock it down after you trip on it," this post is "patrol regularly to find unexploded bombs."

This post is still the plain-language version for self-taught developers. Engineering details (6-piece fix pattern templates, how to write a governance rule, how to run SQL tests) go into B2 (engineer-facing).

The starting point: a customer's screenshot of a frozen button

May 25th, midday. Customer screenshot: "The roasting station's 'complete' button is frozen, showing 'roast level mismatch' error."

I stared at the screen for 30 seconds. Should I fix it the usual way? One line, comment out the RAISE statement, done.

But this was the 5th "cross-layer contract drift" bug in 6 months. Each one took 3-5 hours, totaling 15-25 hours. Every fix I thought "this should be the last one," then the next month another one appeared.

I asked Claude one question:

"I've fixed 5 of these in 6 months. Should I stop and look for all leaks of the same shape, fix them all at once?"

Claude proposed: Spend 1 hour auditing the whole business flow first. List which chain nodes are unprotected. Then decide what to fix first.

What the 1-hour audit actually did

Claude and I sketched out the "order to shipment" business flow, listing 11 chain nodes (every step from customer order to shipped package that touches data).

For each node, I asked 3 questions:

Does this node have a corresponding test? (would it get caught if it broke)
Does this node have a corresponding governance rule? (would it get caught if someone edited it wrong)
Is this node's "business contract" written down? (why is this the logic and not something else)

After running through:

🟢 Fully protected: 5 nodes (test + governance + contract all present)
🟡 Partially protected: 2 nodes (missing one or two)
🔴 Completely unprotected BLOCKERs: 4 nodes

The 4 BLOCKERs were:

Order cancel / refund flow (no spec for cash-first vs bonus-first refund)
Roasting completion stage (no spec for "roast mismatch → block or warn")
FIFO ingredient consumption (no spec for which bag of the same coffee goes first)
Packaging station task state (no spec for whether "skipped" is a valid state)

Each one was a "next customer to trip on this costs me 3-5 hours" landmine.

Decision Pinning = the most critical ritual of the audit

While listing the BLOCKERs, I noticed something easy to miss: these BLOCKERs are BLOCKERs not because the code is broken, but because "how the business should work" isn't written down.

Take "should refund go back to cash first or bonus first":

I have an answer in my head (cash-first, because the original deduction prioritized bonus, so a cash-first reversal keeps things balanced)
But this answer isn't written anywhere
Three months later, I might look at the code and think "this is weird, let me clean it up"
An AI pair tool sees it and acts on its own intuition

So the first output of the audit isn't fixing code. It's formally writing down these 4 business decisions:

Refund wallet order: original deduction $50 bonus + $30 cash → refund fills cash $30 first, remainder fills bonus $50
Roasting completion roast mismatch: trigger RAISE → rewrite as warning into notes column, continue completion (avoid freezing the operator station)
FIFO ingredient consumption: same coffee consumed in created_at ASC order; inventory invariant: total before minus total after equals consumed
Packaging task state machine: purely linear pending → in_progress → completed/cancelled, no skipped / partial

Once written down, these decisions transform from "my personal preference" to "system contract." Anyone (future me, AI included) who wants to change them will see them.

I gave this step a working name: "Decision Pinning" (working name, my own).

6-piece fix pattern: every BLOCKER follows the same recipe

When fixing the 4 BLOCKERs, each one applied the same 6-step template:

Pure-function helper: extract scattered logic into one file, all callers go through it
Fuse test (trace test): pin the helper's current correct behavior (including the incident-pinning case for Decision Pinning)
Registry entry: add this trace to the registry mentioned in A1
Governance rule: auto-scan for callers not going through the helper, block commits
Caller exemption list: explicitly list files that have legitimate reasons not to use the helper (avoids permanent red)
Iteration log: record what was fixed, what the contract is, what not to miss next audit

The first BLOCKER took 2 hours to get familiar with the template. Second: 1.5 hours. Third: 1 hour. Fourth: 45 minutes (excluding one special detail for a new category). Marginal cost decreases each iteration.

The whole sprint took 7 hours for 4 BLOCKERs, including writing the sprint summary. Compared to the "fix one at a time" mode (estimated 12-20 hours), direct-time ROI is about 2-3x. But the audit path also prevents N future bugs, so equivalent ROI I estimate at 5-8x.

Reverse verification ritual: prove the lock actually catches

After fixing each BLOCKER, I did one thing: deliberately break the helper to confirm the test actually fails red, and remove the caller exemption to confirm the governance rule actually blocks the commit.

Sounds redundant? It caught me twice writing "locks that are too loose":

Once breaking the test only failed 2 cases because many tests used a single input that didn't exercise the distribution algorithm
Another time removing governance exemption didn't block, because the exemption regex was too loose and every file passed

Reverse verification is the lock for the lock. Protection without reverse verification might be "tested but doesn't actually test," worse than no test (gives false confidence).

7 things learned across BLOCKERs

The process of fixing all 4 also surfaced "things not to step on next audit":

The 6-piece pattern takes 2 hours the first time, 1.5 hours the second, 45 minutes the fourth
Pure-DB logic has its own testing method (use Supabase CLI to run SQL, not frontend unit test frameworks). I gave it a category name: sql-only-trace
Reverse verification is a mandatory ritual, not optional
Chinese regex has traps (\S{5,} doesn't necessarily match 5 consecutive Chinese characters)
Governance docstring scans easily false-positive (a helper mentioning an RPC name gets misclassified as a caller)
Context window needs active estimation (after each BLOCKER, run a self-estimate of "how much context I burned")
Postgres function signature changes need a DROP of the old version before adding a default-NULL parameter, otherwise you get an overload conflict

I wrote these 7 into the sprint summary so the next business-flow audit can reuse them directly.

A transferable suggestion

If you're a solo dev who pairs with AI a lot:

List your 5 most important business flows (checkout / order / inventory / member / payment...)
Pick one a month, spend 1 hour running an audit (11 chain nodes × 3 questions)
Find BLOCKERs → fix with the 6-piece pattern (each one 1-2 hours, gets faster)
Every BLOCKER does Decision Pinning (this is the real core of the audit, not the code fix)
Every year, do one full audit pass on your core business flows

Don't wait for the next bug to surface. By the time a bug surfaces, a customer has already tripped on it. The audit catches things before customers do.

Why A1 (Defensive) + B1 (Offensive) must be used together

With only A1 (reactive lock-down of known traces), protection always trails the bugs. At best you break even.

With only B1 (proactive audit), the BLOCKERs you find rot if not turned into trace locks. Next month they reappear.

Used together:

B1 finds unprotected chain nodes
A1 locks them down
Next round of B1 finds new candidates

That's the "dual-blade" approach covered in C1 Offense + Defense combined.

A1 · Defensive Trace Lock — How to lock down a cross-layer relationship (previous in series)
C1 · Offense + Defense combined — How the two pair up (next in series)
中文版

About this post

A few things I'm not claiming:

Terms used in this post (offensive audit / Decision Pinning / 6-piece fix pattern / sql-only-trace) are working names I gave them myself, not industry-standard terminology
My system has a specific shape (solo-maintained, many cross-layer dependencies, ambiguous business contracts). These patterns may not apply to your context
I'm not a software engineer, just a barista who pairs with AI to write code

If a professional engineer spots misuse, or there's already a more standard name for any of these concepts, I genuinely welcome corrections.

本文原載於我的部落格：Offensive audit — How to periodically audit a whole business flow (for self-taught developers)

Defensive Trace Lock, engineering edition — 5 artifacts in detail

Dexterlung — Tue, 28 Jul 2026 15:24:33 +0000

May 2026 · Series "Trace Lock — Governance notes from pairing with AI to write code" · Post 6 of 9

This post is the engineering version of A1 Defensive Trace Lock.

A1 covered "why" and "what the 5 artifacts look like at a high level." This post unpacks each artifact: code shape, why it was designed that way, what you can tune.

Written for readers who already know what governance rules, pinning tests, and config-as-code are. If you have a non-engineering background, A1 is more suitable.

My stack: Vue 3 + Vite + Vitest + Supabase (PostgreSQL) + Node.js scripts. But the pattern in the 5 artifacts below is independent of the frontend framework. It depends mainly on filesystem conventions and regex parsing.

The 5 artifacts at a glance

#	Artifact	Role	File type
1	Registry	The trace directory, hand-written markdown	`文檔/data-source-registry.md`
2	Trace test (fuse test)	A pure-function test that pins the trace's current correct behavior	`frontend-app/src/__tests__/traces/T{id}-*.trace.test.js`
3	Governance rule A	Checks every registry trace has a corresponding test file	`scripts/governance-guard.mjs`
4	Governance rule B	Checks the trace test actually imports the declared anchor	`scripts/governance-guard.mjs`
5	AI reminder skill	When AI touches trace nodes, auto-triggers the 5-step checklist	`.claude/skills/trace-lock-modify/SKILL.md`

Total ~600 lines of code (registry parser + 2 governance rules + skill markdown). First setup takes about 4 hours. Each subsequent trace costs 30-45 minutes.

Artifact 1: Registry markdown format

The registry is a markdown file. Not YAML, not JSON, not a database. The reason: it needs to be maintainable by humans AND parseable by scripts. Markdown is acceptable in both directions.

Each trace is a ### T-{id}: title block. Fields use the **Field Name**: value convention, which makes them easy to grab with regex. Here is a trimmed version of the real T-001:

### T-001: Roasted bean stock grams → POS variant orderable count

- **Type**: data-flow trace
- **Status**: locked (2026-05-25)
- **Anchor SSOT**: [`frontend-app/src/lib/business-rules/variantInventory.js`](../path)
- **Trace test**: [`frontend-app/src/__tests__/traces/T001-variant-inventory.trace.test.js`](../path)
- **Trace nodes** (write side to display side):
  1. **DB column**: `roasted_batches.remaining_weight`
  2. **DB trigger**: `trg_sync_products_stock_virtual_from_batch_iu`
  3. **DB column**: `products.stock_virtual_grams`
  4. **Frontend fetch**: useProducts.js fetchProducts
  5. **SSOT helper**: variantInventory.getVariantAvailableQuantity
  6. **Entry A (modal)**: ProductVariantModal.variantInventoryLimit
  7. **Entry B (checkout)**: AdminPOS.cartStockSafety
- **Related incident**: San Agustin drip-bag-only-1-pack issue
- **Last edited**: 2026-05-25

Why fields are sliced this way

Anchor SSOT is the one trustworthy compute entry for this trace. All Entry points must converge on the anchor. If the anchor changes, the trace contract changes. That counts as a major change.

Trace nodes lists every node from write side (DB column, trigger) to display side (UI component), in order. Each node is a candidate file where "touching this might affect trace behavior."

Entry points are the multiple entry pathways to the same anchor SSOT. For example, T-001's anchor variantInventory.js is called by both ProductVariantModal (when the user picks a variant in POS) and AdminPOS (the cart safety check before checkout). Entry points form the audit checklist for "is this SSOT actually used everywhere it should be used?"

Last edited is for AI to read. After each trace-node edit, the human bumps this field. The next time AI looks, it sees "someone touched this recently, context might have drifted."

Design tradeoff: why not YAML or JSON

I considered YAML but rejected it. Reasons:

Markdown links and code references are built-in. YAML needs an extra schema to express "this is a file path."
Human maintenance cost. YAML's strict indentation is unfriendly for small edits. Markdown can wrap and annotate freely.
GitHub and VS Code rendering. Markdown shows links inline; YAML shows raw text.

The cost is that parsing is ~30% trickier than YAML (regex is more error-prone than yaml.parse). I accept the tradeoff because the registry has a practical upper bound of ~30 traces per project, so parsing complexity is also bounded.

Artifact 2: Trace test 5-section structure

The T-001 trace test contains 5 describe blocks. Each section pins one semantic facet of the trace:

import { describe, expect, it } from 'vitest'
import {
  getVariantWeightPerPack,
  getVariantAvailableQuantity,
  resolveCartItemVariantFormat,
} from '../../lib/business-rules/variantInventory'  // ← import the anchor SSOT

describe('T-001 trace: variant weight contract', () => { /* Section 1 */ })
describe('T-001 trace: variant available quantity (incident pinning)', () => { /* Section 2 */ })
describe('T-001 trace: edge cases', () => { /* Section 3 */ })
describe('T-001 trace: cart item variant format resolution', () => { /* Section 4 */ })
describe('T-001 trace: SSOT cross-entry consistency', () => { /* Section 5 */ })

What each section pins

Section 1, Contract. Declares the "base contract" the trace covers. For T-001, the contract is "each variant format maps to a specific net weight per pack." This section tests pure spec mapping, no state.

Section 2, Incident pinning. Writes the customer-event that triggered creating this trace as a test case. For T-001, that's the San Agustin drip-bag incident (244g barrel → 1 half-pound or 2 drip-bag packs). This section is the historical memory: without it, the trace can rot back to broken.

Section 3, Edge cases. null, undefined, 0, negative numbers, missing fields. None should throw; all should fall through to a safe fallback. This section exists so future AI edits to the helper don't regress edge cases.

Section 4, Reverse resolution. If the trace requires "reversing from an entry point back to a format the anchor understands," this section tests reverse resolution. For T-001, resolveCartItemVariantFormat derives variant format from a cart item object.

Section 5, Cross-entry consistency. The same input passed to N entry points must return the same answer. This section tests that "entries actually share one path, not duplicated implementations."

Not every trace needs all 5 sections. Minimum is Section 1 + Section 2 (contract + incident). Sections 3-5 are added based on the trace's shape.

Why importing the anchor is mandatory

Artifact 4 (governance rule B) checks that the test file's first import actually points at the anchor path declared in the registry.

Reason: if the trace test doesn't import the anchor, what it's testing is "a copy-pasted logic snippet inside the test file," not the SSOT. When the anchor later changes, the test can't see it. The trace rots while the test stays green.

This check is simple but load-bearing. I had one trace (it never made it to the registry during a debugging detour) that rotted exactly this way. The test was green, but the actual logic was rewritten by a different PR.

Artifacts 3 & 4: governance rule code walkthrough

The two governance rules live in scripts/governance-guard.mjs and run on every pre-push or CI cycle. The core is parseTraceRegistry(), which extracts markdown into an object array:

function parseTraceRegistry() {
  const registryPath = path.join(repoRoot, '文檔/data-source-registry.md')
  if (!fs.existsSync(registryPath)) return []

  const content = fs.readFileSync(registryPath, 'utf8')
  // Only parse the content between ## Critical Traces and the next ## heading
  const sectionMatch = content.match(/##\s*Critical Traces[\s\S]*?(?=\n##\s|$)/)
  if (!sectionMatch) return []

  const section = sectionMatch[0]
  const traceBlocks = section.split(/\n###\s+(?=T-\d+:)/).slice(1)

  for (const block of traceBlocks) {
    const idMatch = block.match(/^T-(\d+):\s*(.+?)$/m)
    if (!idMatch) continue
    // ... extract Anchor SSOT / Trace test / Type fields
    traces.push({ id, title, anchorPath, testPath, isSqlOnly })
  }
  return traces
}

A few engineering details:

Use [\s\S]*?, not .*?. JS regex doesn't match newlines with . by default. Markdown blocks span multiple lines, so [\s\S] is required.

Use (?=\n##\s|$) as the terminator. The lookahead ensures parsing stops at the next ## heading or end-of-file. Without it, the parser would scoop up unrelated sections.

.slice(1) skips the prelude. The first chunk from split is the content between the ## Critical Traces heading and the first ###. That's the section preamble, not a trace.

isSqlOnly carveout. T-021 (FIFO consumption trace) is sql-only. Its test is a .sql file, not .js. The Type field is marked sql-only-trace and rule B skips the import check (because SQL tests have no import concept).

Rule A: every trace has a corresponding test file

function checkTraceRegistryTestCoverage(violations) {
  const traces = parseTraceRegistry()
  for (const trace of traces) {
    if (!trace.testPath) {
      violations.push({
        message: `T-${trace.id} registry has no Trace test field`,
      })
      continue
    }
    const absTestPath = path.join(repoRoot, trace.testPath)
    if (!fs.existsSync(absTestPath)) {
      violations.push({
        message: `T-${trace.id} declared Trace test file does not exist`,
      })
    }
  }
}

Blocks two failure modes: (a) a trace declared in registry without a test path field; (b) test path declared but the file doesn't exist (path typo, or not created yet).

Rule B: trace test imports its declared anchor

function checkTraceTestImportsAnchor(violations) {
  for (const trace of traces) {
    if (!trace.testPath || !trace.anchorPath) continue
    if (trace.isSqlOnly) continue

    const testSource = fs.readFileSync(absTestPath, 'utf8')
    const anchorBasename = path.basename(trace.anchorPath, path.extname(trace.anchorPath))

    const importPattern = new RegExp(
      String.raw`(?:from|require\s*\()\s*['"\`][^'"\`]*${anchorBasename}(?:\.[jt]s)?['"\`]`,
      'g',
    )
    if (!importPattern.test(testSource)) {
      violations.push({
        message: `T-${trace.id} trace test must import its declared Anchor SSOT`,
      })
    }
  }
}

Loose match on the anchor basename (path-relative-flexible) so any of import { ... } from '...variantInventory(.js)' or require(...) passes. String.raw avoids backslash escape hell.

Both rules combined: roughly 60 lines of code.

Artifact 5: the AI reminder skill

The skill is a markdown file. The frontmatter description declares the trigger condition. The body holds the 5-step audit checklist. Claude Code auto-loads it when a task description matches:

---
name: trace-lock-modify
description: "Use when modifying any file listed in 文檔/data-source-registry.md > Critical Traces 的 Anchor SSOT / Trace nodes / Entry points. Cross-layer trace guardrail. AI must list the trace's full chain + run the trace test to pin baseline + re-run after the edit + remind user to bump Last edited."
---

A few engineering details:

The description must start with "Use when ...". Claude Code uses this phrasing to judge trigger conditions. Other phrasings have measurably lower trigger rates (observed across 4 skill rewrites).

List specific file paths. The description directly lists the three categories (Anchor SSOT / Trace nodes / Entry points). When AI sees a task description that mentions these paths, the match rate is high.

The 5-step checklist lives in the body. After triggering, AI actually reads the SKILL.md body. Steps in the body need to be runnable (grep command, vitest command, "re-run after edit," etc.).

What each of the 5 steps enforces

Confirm which trace the file belongs to (grep the registry)
List the trace's full chain to the user (even if AI already knows, the user might not remember)
Run the trace test before editing (record the baseline)
Re-run the trace test after editing (confirm nothing broke)
Update the Last edited field in the registry

Step 2 matters. AI should not assume the user remembers trace details. Listing the chain every time forces user and AI to sync mental models.

Engineering considerations

Parser tolerance

Regex parsing has 3 common failure modes:

Markdown links and plain text coexist. Both **Anchor SSOT**: [\path](url) and **Anchor SSOT**: path need to be recognized. My regex uses \[? / \]? to accept either.
Annotations after field values. Field names can be followed by emoji or markers. /\*\*Type\*\*:\s* plus a ? accepts cases like **Type**: data-flow ⚠️.
Empty fields. When a trace omits a field, don't throw. Return null and let the governance rule emit "please fill this field" with a clear message for the user.

Cross-platform paths

The Node.js script runs on Windows, Mac, and Linux:

const repoRoot = path.dirname(fileURLToPath(import.meta.url)).replace(/scripts$/, '')
const registryPath = path.join(repoRoot, '文檔/data-source-registry.md')

Never hardcode '/' or '\\'. Always use path.join. CJK folder names work on Windows under UTF-8 (Node.js fs handles it automatically), but console output on Windows requires care with cp950 encoding (chcp 65001 + LC_ALL=C.UTF-8).

CI integration

scripts/governance-guard.mjs is written as a standalone entry. Pre-push hooks and GitHub Actions both run it:

# pre-push hook
node scripts/governance-guard.mjs || exit 1

A violations array collects all violation messages. At the end, it prints them and exits with code 1. Key point: all trace rules are BLOCKER tier (not advisory). They block the push. Reason: traces are by design "relationships that should already be stable." Advisory equals no enforcement.

Incremental cost

Each new trace (not the first one, but a new one added with the framework already in place):

Write the registry block: ~10 minutes
Write the trace test (5 sections): 20-30 minutes
Run the two governance rules to verify: 30 seconds
Update Last edited: 30 seconds

Total 30-45 minutes per trace. Consistent with the estimate from A1.

Cross-project portability

How portable each artifact is when copied to a different project:

Artifact	Portability	Why
Registry markdown format	★★★★★	Pure convention, project-independent
Trace test 5-section structure	★★★★☆	Vitest / Jest / Mocha all work; only the import syntax changes
Governance rule code	★★★★☆	Node.js script, replace the registry path
AI reminder skill format	★★★★☆	Claude Code skill format; other AI tools use different syntax
Specific trace contents	★☆☆☆☆	100% business-specific; reinvented per project

In other words: the framework layer is ~80% portable; the content layer is 0% portable. This ratio is consistent with the "80% framework portable, 20% content reinvented" estimate from C1 Offense + Defense combined.

Detailed cross-project reuse analysis is in C2 Cross-project reuse matrix (next post in the same series branch).

When this doesn't pay off

Lifting these 5 artifacts wholesale isn't worth it when:

Only 1-2 cross-layer relationships exist. Just write them into the commit message or PR description. Building a registry is overengineering.
No markdown-friendly editing workflow. If the team lives in Notion or Confluence, double-maintaining git and wiki is a drag.
CI can't run custom scripts. Without CI enforcement, governance rules get ignored over time.
No AI pair programming. The AI reminder skill is an integration point for Claude Code / Cursor / similar tools. Without one, the skill has nowhere to hook.
Business contracts change very fast. If traces need re-locking weekly, the registry maintenance cost exceeds the benefit.

My setup (solo maintainer, many cross-layer dependencies, relatively stable business contracts, Claude Code pairing) happens to hit all the conditions. Your situation needs its own judgment.

A1 · Defensive Trace Lock, how to lock one cross-layer relationship (same topic, plain-language version)
C2 · Cross-project reuse matrix + when not to use it (same series branch, next post)
Chinese version

About this post

A few things I'm not claiming:

Terms used in this post (5 artifacts / fuse test / AI reminder / Trace Lock / Anchor SSOT / sql-only-trace) are working names I gave them myself, not industry-standard terminology
My system has a specific shape (solo-maintained, many cross-layer dependencies, ambiguous business contracts). These patterns may not apply to your context
I'm not a software engineer — just a barista who pairs with AI to write code

If a professional engineer spots misuse, or there's already a more standard name for any of these concepts, I genuinely welcome corrections.

本文原載於我的部落格：Defensive Trace Lock, engineering edition — 5 artifacts in detail

Defensive Trace Lock — How to lock down a single cross-layer relationship (for self-taught developers)

Dexterlung — Tue, 28 Jul 2026 15:24:03 +0000

May 2026 · Series "Trace Lock — Governance notes from pairing with AI to write code" · Post 2 of 9

The previous post (E meta) mentioned that Claude and I gradually identified 4 patterns over 6 months. The third one we call "Trace Lock" (working name, my own). This post unpacks what Trace Lock actually looks like and how you lock down a single trace.

This post is the plain-language version for self-taught developers. If you're an engineer and want code-level detail (which file goes where, how the SQL looks, how the governance rule is written), see the A2 engineering post (same topic, different audience).

Goal of this post: someone who has never written a governance rule or a pinning test should finish reading and know "Trace Lock is these 5 artifacts, why each one is needed, and roughly how long locking one trace takes."

First, the bug that triggered this whole thing

May 25th, early morning. A customer messaged: "I'm trying to order 2 drip-bags of the San Agustin coffee, why does the system say max 1?"

Each drip-bag is 103 grams. The backend roasted-bean barrel had 244 grams left, so 2 bags should fit. I went to check why the system calculated 1.

Tracing through:

Backend correctly wrote 244 grams to products.stock_virtual_grams ✅
Frontend correctly read 244 grams ✅
But the frontend divided 244 by the half-pound product's gram weight (227g) = 1 bag ❌

The half-pound product had 4 variants: half-pound whole bag, quarter pound, one pound, drip-bag. The bug: the system didn't differentiate by variant. It always used "the half-pound product's gram weight" to compute orderable count. Drip-bags should use their own gram weight (103g).

Fixing this bug: 30 minutes. But it raised a deeper question:

Why have I hit 5 of these "cross-layer contract drift" bugs in 6 months?

Order payment_status didn't sync with status
Top-up plans got pushed into the packaging worksheet
13 coffee products showed "out of stock" to consumers (admin view was fine)
A VVIP customer topped up $5000 and got the wrong bonus amount
After adding a medium_dark roast tier, the UI showed the English enum code

Common factor: data flows from a write-side to a render-side, and somewhere a layer forgot to sync. Teams have code review, SRE, QA, PM to catch these. Solo devs don't.

Cross-layer relationships live in your head = forgotten in 3 months

I noticed something: every time I fix one of these bugs, I spend 30-60 minutes from scratch grepping the code to re-figure-out "which write-side, which intermediate layers, who renders."

I fixed a similar one 3 months ago, but the details are gone. Back then, the "cross-layer relationship" only lived in my head's working memory. Session ends, it's cleared.

AI pair-programming tools work the same way. Every new conversation, Claude starts from zero. It doesn't automatically know "last time you changed A and forgot B and it cost you 3 days."

So this time Claude and I thought: can we pull the cross-layer relationship out of my head and store it as a codebase-visible artifact, so 3-months-later me (and the next new-session AI) sees it the moment they open the project?

That's Trace Lock.

The 5 artifacts

Each trace (one cross-layer relationship) is composed of 5 artifacts:

Artifact 1: Registry

A markdown table. Each row is one trace, columns include:

ID (like T-001)
Anchor SSOT (which function/field is this trace's "source of truth")
Trace nodes (the 8-12 files/functions the data passes through on its way from source to render)
Why (why this trace exists, historical context)
Last edited (when was it last touched)

The registry isn't documentation. It's an index. Three months from now, when I want to find "how the half-pound drip-bag orderable count is calculated," I look in the registry and know which function is the anchor.

Artifact 2: Fuse test (pinning test)

A pure-function test that pins down the anchor's currently-correct behavior.

Note: "currently-correct" not "what it should be." This distinction matters. More on it below.

When someone changes the anchor in the future, the test fails red, giving them a chance to ask "was this change intentional?"

Artifacts 3 + 4: Two auto-inspectors (governance rules)

Inspector A: every trace in the registry must have a corresponding test file (prevents "registered but no test written")
Inspector B: every test file must actually import the anchor function it claims to test (prevents the test from rotting into "testing something else but claiming to test the anchor")

Both inspectors run automatically before commit / push. If they fail, the push doesn't go through.

Artifact 5: AI reminder (skill)

A markdown file that says: "When you (the AI) are about to edit any node listed in the trace nodes, you must first do these 5 steps: list the chain → run the current test → make the change → run the test again → update the registry's Last edited."

The AI tool I use (Claude Code) supports skill auto-triggering. It sees the file I'm about to edit is in a trace nodes list and proactively triggers this skill. If you use a different AI tool:

Cursor: write into .cursorrules
Aider: write into CONVENTIONS.md
ChatGPT: put it in system prompt or custom instructions

Same principle. Make the AI pair tool automatically inherit your governance.

Why all 5 are needed

I thought "registry + test" would be enough at first. Doing it, I realized any missing piece breaks the chain:

No registry → tests have no index, 3 months later nobody knows which traces have been locked
No test → registry is just text, nobody reads it
No Inspector A → registered without a test, nothing blocks it
No Inspector B → tests "rot" into testing something else, giving a false sense of safety
No AI reminder → new AI conversations don't trigger the governance flow automatically

How long does locking one trace take?

First-time setup: 4 hours (build the registry format, write the first trace, write two inspectors, write the skill).

Each additional trace: 30-45 minutes (add a row to the registry, write the corresponding test, do reverse verification).

Claude and I calculated the break-even point: after locking the 7th trace, total time spent is less than "grepping from scratch each time." I've now locked 13.

A common trap: fuse tests are NOT unit tests

The most common mistake writing fuse tests: treating them like unit tests.

The difference:

Unit tests pin down "what it should be": find a bug, change both the test and the code
Fuse tests pin down "what is currently correct": find anchor behavior that contradicts intuition, pin the current behavior first, don't fix it in passing

Example: while writing one trace, I found that getRoastLevelLabel(null) returned "medium roast" instead of "no roast label." Gut reaction: "this is a bug, fix it."

But the fuse test's job is not to correct "currently-designed choices." Its job is to prevent the current behavior from changing unintentionally in the future. Whether to change the null → medium roast design is a separate discussion (should the UI distinguish "unfilled" from "medium roast"), not something the AI casually decides.

So the test I wrote was: expect(getRoastLevelLabel(null)).toBe('medium'). And I added a comment in the registry: "Currently designed choice: no roast data = default to medium. To distinguish 'unfilled' vs 'medium', that's a product discussion, not an anchor change."

If anyone (or AI) wants to change the anchor in the future, the test fails red, forcing them to see the comment and realize "this isn't a bug, it's a design choice."

Always do reverse verification

After locking each trace, I deliberately break the anchor function (return wrong direction, return null) to confirm the test actually fails red. Then revert, confirm the test is green again.

Sounds redundant? It caught me twice writing "tests that are too loose":

Once only 2 cases failed because many tests used a single input that didn't exercise the distribution algorithm
Another time 5 cases failed (normal water level)

Reverse verification is the fuse for the fuse. A test without reverse verification might be "tested but doesn't actually test," worse than no test at all (gives false confidence).

Why this is especially effective for AI pair-programming

Claude and I have noticed two AI failure modes:

AI doesn't know some invariant: e.g., "VIP tier can only be customer/vip/vvip/svip"
AI knows but is misled by the user: e.g., I say "add a 'gold' tier," the AI doesn't check governance and just adds it

Trace Lock catches both:

Mode 1 → registry + skill surface the rules to the AI; the AI reads CLAUDE.md and triggers the skill at session start
Mode 2 → the two inspectors block commits even if the AI was misled

Compared to "please make the AI more careful" (unactionable), "write the rule in .mjs and run it automatically" is an actionable engineering intervention.

A transferable suggestion

If you're a solo dev who pairs with AI a lot, ask yourself 3 questions:

Which 5-10 "if I change A I must also change B" chains exist in your codebase? (those are your trace candidates)
How are these chains currently protected? (tests? docs? personal memory? None are enough)
Tomorrow when you /clear and start a new conversation, will your AI remember these? (probably not)

If no → you need Trace Lock (or something like it).

Minimum viable version:

Add a "Critical Traces" table to your docs (paper and pen works)
Write 1 fuse test pinning the first trace
Write 1 script that scans the registry to confirm the test exists
Add the script to a pre-commit hook
Write a skill (or .cursorrules / CONVENTIONS.md) telling the AI "before changing a trace, run the test first"

Step 5 matters most. Without a reminder, the AI doesn't proactively trigger the governance flow, and the first 4 steps just sit there decoratively.

E · meta — How we found the methodology through AI conversations (previous in series)
A2 · Defensive Trace Lock Engineering Edition — 5 artifacts implementation detail (same topic, engineer-facing depth)
中文版

About this post

A few things I'm not claiming:

Terms used in this post (Trace Lock / fuse test / auto-inspector / AI reminder) are working names I gave them myself, not industry-standard terminology. Pinning test is a concept Michael Feathers wrote about in "Working Effectively with Legacy Code," but my usage may not be rigorous
My system has a specific shape (solo-maintained, many cross-layer dependencies, ambiguous business contracts). These patterns may not apply to your context
I'm not a software engineer, just a barista who pairs with AI to write code

If a professional engineer spots misuse, or there's already a more standard name for any of these concepts, I genuinely welcome corrections.

本文原載於我的部落格：Defensive Trace Lock — How to lock down a single cross-layer relationship (for self-taught developers)

CREATE OR REPLACE didn't replace: one optional parameter, and my API 400'd in production

Dexterlung — Sun, 26 Jul 2026 09:33:11 +0000

A button in my admin panel stopped working. It had worked the day before. I hadn't touched it.

I run a small coffee e-commerce platform, alone. One of the things it does is issue signed links — the customer gets a link, opens it, confirms they received their order. There are three entry points: a button in the admin order list, a button that pushes the link to the customer over LINE, and a button inside the customer's own order page.

All three broke at once. All three showed the same toast: "Couldn't generate the confirmation link, please try again later."

Which told me nothing at all.

The error was there. My frontend was eating it.

The catch block behind that button looked like every catch block I've ever written:

} catch (err) {
  error.value = err.message || 'Could not create confirmation link'
  setLink('')
  return null
}

The caller only sees null, so it throws the generic toast. The actual message never reaches a human.

I found the real error the way you always find it — DevTools → Network → click the failing rpc/... request → read the response body:

{ "code": "42725", "message": "function fn_issue_order_action_token(...) is not unique" }

42725. Function is not unique. Postgres was telling me there were two functions with that name, and it refused to guess which one I meant.

I only ever wrote one.

`CREATE OR REPLACE FUNCTION` does not replace your function

Here is the thing I understood wrong for about six months.

The original function had three parameters:

CREATE OR REPLACE FUNCTION fn_issue_order_action_token(
  p_order_id       text,
  p_action         text,
  p_expires_minutes integer
) RETURNS jsonb ...

Later I added support for partial pickups, which needed to carry some extra data along with the token. So I added one parameter — optional, defaulted, entirely backward compatible:

CREATE OR REPLACE FUNCTION fn_issue_order_action_token(
  p_order_id       text,
  p_action         text,
  p_expires_minutes integer,
  p_metadata       jsonb DEFAULT NULL   -- <- the whole bug
) RETURNS jsonb ...

Every existing caller passes three arguments. The fourth defaults to NULL. Nothing breaks. That's the reasoning, and it's wrong.

CREATE OR REPLACE FUNCTION replaces a function only if the identity arguments match exactly. (text, text, integer, jsonb) is not (text, text, integer). So it didn't replace anything. It created a second function, and now the database held both:

oid 109091 | (text, text, integer)          <- from an old migration
oid 108290 | (text, text, integer, jsonb)   <- the "replacement"

Then PostgREST called it with three arguments. The 3-arg version matches. The 4-arg version also matches, because its fourth parameter has a default. Two candidates, no tiebreaker, 42725, HTTP 400.

The symptom — "worked in the last PR, started 400ing after I added a parameter" — is genuinely hard to connect to the cause, because the parameter you added is the one nobody is passing.

The fix is thirty seconds:

DROP FUNCTION IF EXISTS public.fn_issue_order_action_token(text, text, integer);
NOTIFY pgrst, 'reload schema';

Two things worth knowing here. Get the exact signature from pg_get_function_identity_arguments rather than typing it from memory — the DROP silently no-ops if you get one type wrong, and IF EXISTS means you won't even be told:

SELECT oid, pg_get_function_identity_arguments(oid)
FROM pg_proc WHERE proname = 'fn_issue_order_action_token';

And the NOTIFY pgrst matters if you're on PostgREST/Supabase — it caches the schema, so without it the API keeps serving the ambiguity you just resolved.

That's the bug. If the post ended here it would be a fine TIL. It doesn't end here, because I'd already fixed this bug. Twice.

Rule, then guard, then it came back anyway

Pulling git log afterward, this class of bug had visited me on a schedule:

When	What	What I did about it
Feb 27	Return-type conflict (`42P13`), needed a `DROP` first	Fixed it inline. Forgot about it.
May 17	Changed an `OUT` parameter on the topup RPC — same trap	Fixed it inline again, still relying on remembering
May 20	The signed-link RPC, ambiguous overload	Fixed it, and finally wrote an automated check
May 26	The signed-link RPC. Again.	—

Three months between the first and the second, so I never connected them. Then two in one week, which is what it took for me to stop patching and write a static check: scan every migration, if it changes a function signature without a preceding DROP FUNCTION, block the commit.

Six days later the same RPC broke the same way.

I want to be precise about why, because it's the part I got wrong and it's the part that generalizes.

My check asked: does this new migration introduce an overload without dropping the old one? Every migration in the repo passed. It kept passing while production was broken — because nothing new had been written. What happened was that an old migration — the one that created the 3-arg version, written months earlier and long since superseded — got applied again. Somewhere. Somehow. It re-created a function I had already dropped.

My repo said the 3-arg function was gone. My repo was right about its own contents and wrong about the world.

That's the shape of the mistake: I had written a guard against the failure I had just experienced, and assumed it was a guard against the failure mode. Introducing a bad overload and resurrecting a dead one produce the identical broken state, and my check could only see the first one.

Two things fixed it, and neither of them was a rule

The migration now checks itself. Instead of trusting the check that runs on my machine, the migration asserts against the database it's actually modifying:

BEGIN;

DROP FUNCTION IF EXISTS public.fn_issue_order_action_token(text, text, integer);

DO $$
DECLARE v_count int;
BEGIN
  SELECT COUNT(*) INTO v_count FROM pg_proc
  WHERE proname = 'fn_issue_order_action_token';

  IF v_count <> 1 THEN
    RAISE EXCEPTION 'expected 1 overload, got %', v_count;
  END IF;
END $$;

NOTIFY pgrst, 'reload schema';
COMMIT;

This doesn't care how the extra function got there. It doesn't care whether the migration was new, old, or replayed by something I don't know about. It looks at pg_proc, counts, and refuses to commit if reality disagrees. Same principle now runs as a smoke test against the live database over a whitelist of critical RPCs — the repo can't answer "how many overloads exist in production," so I stopped asking the repo.

And I removed the race that likely caused it. My migrations were numbered sequentially — 217_, 218_. Going back through the folder I found five numbers that each had two different files: 217, 218, 219, 230, 231. Sequential numbering means every branch and every parallel session guesses the same next number, and whoever writes second wins the filename. I never proved that's how the old migration got replayed, but it's an obvious way to end up applying a file you didn't mean to, so I moved everything to UTC timestamps. New filenames can't collide.

The part I gave up on

I never found out how that old migration got re-applied.

I tried. The migrations table had exactly one row for it and zero rows for everything in the 217–231 range, because these were applied by hand and hand-applied migrations don't register. Git had a single commit and nothing suspicious. The production logs from the relevant window had already rotated out.

So I wrote down that the forensics failed, and stopped. The self-asserting migration plus the live overload smoke test mean that if it happens a third time I'll know within one command instead of one customer complaint — and I decided that's the property I actually needed. Knowing the exact mechanism would have been satisfying. It wouldn't have made the next occurrence any less likely.

If there's one thing to take from this, it's not the DROP FUNCTION — you'll hit that once and remember it forever. It's the shape of the second mistake: a guard written from a single incident tends to encode that incident's route to the failure, not the failure. Mine watched the front door for six days while the same bug walked back in through a door I'd already locked and never checked again.

The cheapest way I've found to avoid that is to make the check ask the system what state it's in, rather than asking my repo what state it should be in.

DEV Community: Dexterlung

spec-sonar: The Complete Design Record (Part 1) — From One Idea to an Open-Source Toolchain

1. The starting point: an intuition

2. First turning point: interrogate before designing

3. Second turning point: reframing the value proposition

4. Core system-design decisions

4.1 A behavioral contract, not roleplay

4.2 STATE: externalizing conversational memory

4.3 The baseline problem of the set-difference method

5. The real test: a math tower-defense game

6. Third turning point: from a tool to a toolchain

7. The philosophy: one expensive deep design → cheap structured execution

8. Naming: spec-sonar

9. Compatibility principles (the last major decisions)

10. The role of Fable 5

11. This design was designed with spec-sonar

12. Final output list

13. Reusable thinking patterns (Part 1)

Coming in Part 2

Audio Plays on Desktop but Not on iPhone / iPad — The Culprit Is the MP4 moov Atom

First, rule out all the usual suspects

The real culprit: the moov atom is at the end of the file (no faststart)

How to confirm it (30 seconds)

The fix: one line of ffmpeg, lossless, no re-encode

How to stop it from happening again

Series reading guide — How to pick which of these 9 Trace Lock posts to read

What this series is about

Map of the 9 posts

Three reading paths

"I only have 30 minutes"

"I'm not a software engineer but I'm curious about how AI pair programming gets governed"

"I'm an engineer, I want implementation details"

Who this series is not for

Shared vocabulary: the working names

Why I wrote these 9 posts

Related posts

About this post

I built a vertically-integrated commerce system with AI — and accidentally discovered 4 governance patterns

What my system is

The pattern I kept noticing

The conversation that changed things

The 4 patterns that surfaced during the process

Pattern 1: "Fix a bug" vs "audit a chain" are fundamentally different mental modes

Pattern 2: Decisions made with a customer present are forgotten 3 months later

Pattern 3: Cross-layer dependencies should be managed as assets

Pattern 4: Offense + Defense as a dual mode

What this series will cover

Related posts

About this post

sql-only-trace Engineering Edition — Testing DB-Side Pure Logic

Why Unit Test Frameworks Are Not Enough

SQL DO Block Structure Anatomy

Supabase CLI Gotchas When Running SQL Tests

Gotcha 1 (no transaction)

Gotcha 2 (only the last SELECT is returned)

Gotcha 3 (RAISE NOTICE is hidden)

Gotcha 4 (no retry, no parallel)

Full Run Command

Extending the Registry for sql-only-trace

Registry Entry Format (with sql-only-trace type)

Parser isSqlOnly Detection

Governance Rule B Skips Import Check

Governance Rule A Still Applies (trace-registry-test-coverage)

T-021 Full Case Study

Business Contract

Test Scenario Design

The 16 Assertions

SEED Prefix Anti-Pollution

EXCEPTION Cleanup, Real Value

Reverse Verification Inside SQL

5-Step Reverse Verification (sql-only-trace variant)

Choosing the Break Point

Anti-Rot Value

When Not to Use

1. Pure Frontend / App-Layer Business Logic

2. Multi-DB / Heterogeneous Data Stores

3. No SQL Test Runner / No CI Hook

4. Cross-Postgres-Version Compatibility

5. RLS Policies Themselves Are the Target

Cross-Database Downgrade Table

Place under `lib/business-rules/`, not `composables/`

Naming convention: `<feature>Logic.js`