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

> Source: <https://dev.to/dexterlung/sql-only-trace-engineering-edition-testing-db-side-pure-logic-f9j>
> Published: 2026-07-30 13:05:14+00:00

**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*