Integration tier-1 fails on a clean machine: e2e scripts assert top spender is Carol Chen, every fixture says Bob Brown

[sandeep-agami]

Summary

tests/integration/test_postgres_e2e_cli.sh asserts the top spender is Carol Chen; every fixture says Bob Brown. Step 5 of 5 fails, so tier 1 fails on a clean dev machine — and tests/integration/README.md names tier 1 as the release gate ("at least one tier passes for each DB, plus tier 1 (the documented happy path) passes on a clean dev machine").

Severity: sev-3. No product defect: the scripts shell out to psql/mysql/duckdb and invoke no agami code. The cost is that the documented release gate has been red since a37f3a6, which trains people to skip it.

Root cause

The assertion and its explanatory comment describe an order composition that is not in the fixture:

assert_eq "$top" "CarolChen" "top spender is Carol Chen (Order 4: 5x9.99 + 1x99.00 = 148.95)"

Order 4 is Carol Chen's, but it is a single line of 2 × 19.99 = 39.98. The figure 148.95 is real and correct — it belongs to Bob Brown. The fixture data appears to have been rewritten without re-running tier 1.

Evidence

Identical result from all three fixtures (Postgres and MySQL via docker compose up -d, SQLite from fixtures/sqlite-shop-init.sql):

Customer	Spend
Bob Brown	148.95
Alice Anderson	94.96
Dave Davis	67.99
Carol Chen	39.98

Order 4, in full:

 order_id |  customer  | quantity | unit_price | line
----------+------------+----------+------------+-------
        4 | Carol Chen |        2 |      19.99 | 39.98

Run output:

[1/5] Connect probe                    ✓
[2/5] List tables                      ✓
[3/5] Introspect orders columns        ✓
[4/5] Introspect foreign keys          ✓
[5/5] Sample analytical query          ✗ (got=BobBrown want=CarolChen)

Expected vs actual

• Expected: tier 1 passes on a clean machine, per the README's release gate.
• Actual: step 5 fails deterministically on Postgres. The same wrong name is baked into test_mysql_e2e_cli.sh and test_postgres_e2e_duckdb.sh, so those tiers fail the same way wherever their clients are installed.

Fix

Three text edits, no logic change:

1. test_postgres_e2e_cli.sh — CarolChen → BobBrown, and correct the comment's arithmetic to Bob Brown's actual lines.
2. The same assertion in test_mysql_e2e_cli.sh and test_postgres_e2e_duckdb.sh (verify each against the fixture rather than copying).
3. tests/integration/README.md — "Top spender across both fixtures is Carol Chen at $148.95" → Bob Brown.

Worth deciding as part of the fix: whether the assertion should hard-code a name at all, or derive the expected value from the fixture SQL so the two cannot drift again. Hard-coding is what let this land.

While in the file, two adjacent doc drifts found in the same pass:

• tests/integration/SMOKE_TEST_TRUST_LAYER.md documents the credentials key as db_type; the code reads type (plugins/agami/lib/execute_sql.py:1171). Following the doc verbatim yields Credentials profile [default] is missing the 'type' field.

• That same file is headed "Semantic-Model launch — May 18" and is a manual walkthrough. Neither it nor the .sh tiers exercise any agami code, so nothing in tests/integration/ would catch a regression in the guard, the Envelope or the executor. That gap is worth its own issue if it isn't tracked.

• Regression test / golden case added so it can't silently return