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

A developer detailed a technique called 'sql-only-trace' for testing database-side pure logic in PostgreSQL, addressing scenarios where application-layer unit tests cannot verify complex stored procedures, FIFO inventory invariants, RLS policy boundaries, and function overload behavior. The approach replaces traditional trace tests with SQL-based tests that run inside the database, using PostgreSQL-specific features like DO blocks, set_config, and RAISE EXCEPTION.

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 https://./trace-lock-b2-offense-engineering-en.md at line 485, and fills in the engineering details that C2 Combined Engineering Edition https://./trace-lock-c2-combo-engineering-en.md '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 https://./trace-lock-c1-combo-en.md 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. 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 https://../../CLAUDE.md 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. 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 . npx supabase db query --linked -f behaves differently from a normal unit test runner in four ways. Each one bit me. 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. 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 . 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. 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 . 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. 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. 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 . scripts/parse-trace-registry.mjs adds ~10 lines: js 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 , }; } 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: js 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; } The "every trace in the registry must have a corresponding test file that exists" rule applies regardless of frontend or SQL: js 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 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. Frozen 2026-05-25 the day the sprint ended : green beans.created at ASC order FIFO opened beans remainder first, then break a fresh pack from stock grams into opened beans 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 sourceSix business contract clauses, each backed by 2-3 assertions. 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: opened beans for the next FIFO round| | 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 . 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. 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. 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. fn roaster consume beans fifo 's ORDER BY from created at ASC to created at DESC 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. 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. 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. Five situations where porting sql-only-trace is not necessarily worth it . 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 . 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. 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. 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. 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. 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. This is post 9 of 9 in "Trace Lock — Governance Notes from AI Pair-Programming" series finale . The 9 posts span 3 series: 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: 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. 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: 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