CC-3 report — Audit pack D: tenant-safe, replay-safe net-yield economics (WO-08, WO-09)

Date: 2026-09-15 · Branch: audit/cc-3-net-yield-economics · Base: main @ 1f601d171 (== origin/main, 2026-09-15) Lane: ENG (+ MEAS sign-off on invariants — still owed, see §5) · Source: docs/audits/AUDIT_EXECUTION_PROMPTS_2026-09-08.md §CC-3, docs/audits/wo/WO-08.md, WO-09.md, docs/audits/AUDIT_2026-09-06_RECONCILIATION.md rows R8/R9 (#976, #977) Ownership honoured: services/net-yield/** only. Nothing else in the tree was edited (the repo-root bigquery/schemas/*.sql, docs/net-yield/RUNBOOK.md and the parity-pinned onboarding vocabulary were read, not written — §5 names the follow-ups that land elsewhere). Claim: not recorded by this session (execution context: the coordinator records claims). PR: to be opened by the coordinator — title Audit pack D — tenant-safe, replay-safe net-yield economics (WO-08, 09). Not pushed.

Both findings were treated as hypotheses and re-run on 1f601d171 before anything changed (Step 0). Both reproduced, and — as the prompt asked — this is the first time either was executed rather than read: the SQL that the audit inspected was run, against an in-process BigQuery-semantics emulator, because the sandbox has no GCP credentials and no network to Google. The live scratch-dataset run (audit_wo08_<initials>_<date>) is therefore deferred, and §5 says exactly what it must measure first. Nothing widens access; every refusal path (pending, fail-closed version error, refused fee-key collision) is explicit and tested.

Non-independence disclosure (rule 01): the same session wrote every fix and every test. Step-0 reproductions were run before the fixes and their first failing outputs are quoted verbatim below; the acceptance runs are this session's measurements, not an independent verifier's.

Summary table

Commits, one per WO, on 1f601d171:

ea0b56456 WO-08: tenant-key every net-yield aggregation
d51d0ee48 WO-09: replay-safe refunds, fees and changed orders

Deploy fan-out of the branch, measured with the router itself: python3 .github/scripts/deploy_router.py --base origin/main --head HEAD → Changed files: 21 -> no deploy workflows matched []. 0 workflows. (net-yield is dispatch-only — deploy-net-yield.yml has no on.push; merging deploys nothing. A human dispatch of a WO-09 build must be preceded by the operator DDL in §WO-09.)

The emulator, because both WOs rest on it

services/net-yield/tests/bq_emulator.py (test fixture; never imported by the service, and test_flags.py's HTTP-client scan covers it) executes the service's own generated SQL — compute.py's three MERGE stages and every ledger.py statement — in DuckDB 1.5 after a mechanical, per-regex dialect translation (backticked three-part names → table; @p → $p; MERGE → MERGE INTO; COUNTIF/LOGICAL_AND/ARRAY_LENGTH/ DATE()/DATE_DIFF(...,DAY)/TIMESTAMP()/CURRENT_TIMESTAMP(); UNNEST(x) AS li → LATERAL (SELECT UNNEST(x) AS li); SAFE_DIVIDE as a macro). Tables are created with the DDL's column types. It reports num_dml_affected_rows like a BigQuery job, executes each statement atomically, and can inject a crash between statements (fail_after). It is not a Python re-implementation of the semantics: a pooled GROUP BY or an additive UPDATE in the SQL text is the same bug here.

What it does not model, and why the live run still has to happen: BigQuery's streaming-buffer DML restriction (rows landed by insert_rows_json cannot be UPDATEd for up to ~90 min — a hazard that already existed on main for _update_order and apply_refund), typed ARRAY query parameters on the SDK path, and slot-level concurrency errors.

duckdb is a test-only dependency (tests/requirements-test.txt); without it the emulator-backed cases skip with a visible reason rather than pass. No CI workflow runs services/net-yield today (measured: grep -rl net-yield .github/workflows/ finds only the dispatch-only deploy file), so this changes nothing in CI; a future PR that wires the suite must install it.

To make the emulator possible at all, bq.BigQueryStore._run had to change: on main the no-SDK branch silently dropped every query parameter ("test fakes never execute SQL"), so no offline test could ever have executed the generated SQL. Parameters are now always bound (typed SDK parameters when google-cloud-bigquery is importable, a dependency-free .query_parameters shape otherwise).

WO-08 — Tenant-key every aggregation

Status: fixed. Live two-tenant run on a scratch dataset: deferred (no GCP).

Step 0 — reproduced on 1f601d171. services/net-yield/test_wo08_tenant_keyed_sql.py was written first and run against the unfixed compute.py:

• test_wo08_every_group_by_and_join_on_carries_tenant_id scanned every GROUP BY, JOIN … ON and JOIN … USING of the three stages (a guard-the-guard test proves the scanner sees the audit's exact shapes, including a multi-line ON). First run: E tenant pooling: these clauses aggregate or join across tenants: E sku_return_rates: GROUP BY <sku> E nightly_order_merge: GROUP BY <sku> E nightly_order_merge: GROUP BY <oe.order_id> E nightly_order_merge: JOIN ON <r.sku = li.sku> E nightly_order_merge: JOIN USING <order_id> — the finding verbatim. The cohort stage was already keyed (GROUP BY tenant_id, cohort_id); the final MERGE's ON … AND nc.tenant_id = src.tenant_id was there and, as the audit said, could not undo the pooled inputs.
• test_wo08_tenant_a_output_is_invariant_to_tenant_b_rows executed the unfixed SQL: tenants A and B share SKU-1; A has 1 refunded of 4 orders over 100 days (cycle 60), B has 3 of 3. First run: E AssertionError: 57.14285714285714 != 25.0 (A's expected return_cost: pooled 4/7, not A's 1/4) E ... : tenant B's rows changed tenant A's net contribution — an aggregation is pooled across tenants (57.14 with B variant 1 → 16.67 with B variant 2; A's rows moved.) The parameter binder in that test binds only the names a statement references, so this failure is on semantics, not on a stray @tenant_id.
• test_wo08_nightly_tenant_parameter_scopes_every_stage: the tenant-scoped nightly wrote tenant B's rows (Items in the first set but not the second: 'B') — on main /v1/compute/nightly?tenant_id= only chose the cycle length and applied that tenant's cycle to every tenant's rows; nothing was scoped.

Fix (compute.py, bq.py, main.py; COMPUTE_VERSION 1.0.0 → 1.1.0): - sku_return_rates_sql: tenant_id in the CTE projection and GROUP BY tenant_id, sku; the maturity window (HAVING observed_days >= @cycle_days) is therefore per tenant. - nightly_order_merge_sql: order_rate CTE joins rates on r.tenant_id = oe.tenant_id AND r.sku = li.sku and groups by (oe.tenant_id, oe.order_id); the final join is ON orr.tenant_id = oe.tenant_id AND orr.order_id = oe.order_id (no USING); CROSS JOIN UNNEST replaces the comma join so the ON may reference oe in both dialects. - @tenant_id (NULL = fleet run, every tenant still keyed) filters every stage — rates CTE, order_rate CTE, MERGE source, cohort source — not only the final ON. - BigQueryStore.run_nightly(cycle_days, tenant_id=None) binds it on both MERGEs and reports tenant_scope; /v1/compute/nightly?tenant_id=acme now scopes the recompute to acme with acme's cycle, and a fleet run keeps the conservative 365-day window.

Acceptance evidence (python3 -m pytest services/net-yield -q -k wo08, 9 passed): (a) test_wo08_every_group_by_and_join_on_carries_tenant_id, test_wo08_generated_sql_matches_committed_snapshot (byte-for-byte against tests/snapshots/{sku_return_rates,nightly_order_merge,cohort_merge}.sql), test_wo08_every_stage_scopes_on_the_tenant_parameter; (b) test_wo08_tenant_a_output_is_invariant_to_tenant_b_rows — A's net_contribution and net_contribution_cohort rows identical after replacing all of B's rows, with computed_at excluded (the only column that legitimately differs between two runs); test_wo08_tenant_a_expected_rate_is_its_own_not_pooled (25.0); (c) the pre-existing suite: 173 → 200 tests, all green (the WO said "existing 9"; the file on main had 173). (c') test_wo08_nightly_tenant_parameter_scopes_every_stage, test_wo08_store_run_nightly_passes_the_tenant_scope, and in test_api.py test_nightly_uses_tenant_cycle + test_wo08_fleet_nightly_has_no_tenant_scope.

Observed but out of scope, not silently fixed: the order-rate CTE picks a rate with ANY_VALUE while its docstring promised the modal (highest-quantity) SKU. Deterministic for single-SKU orders (the fixture), nondeterministic for multi-SKU orders. Docstring now says what the code does; the modal choice is a follow-up (BigQuery-only ARRAY_AGG(... ORDER BY li.quantity DESC LIMIT 1), which the emulator would need a rule for).

WO-09 — Replay-safe refunds, fees and changed orders

Status: fixed. Live run against a real test DB: deferred (no GCP).

Step 0 — did each of the four counterexamples reproduce on current main? Yes, all four. services/net-yield/test_wo09_replay_safe_economics.py was written first and run on 1f601d171's bq.py/cost_config.py/order_economics.py (plus only the WO-08 parameter plumbing, without which no offline test could execute the store's SQL — the refund/order statements themselves were main's). First run, 15 failed:

Also reproduced in the same run: a second distinct refund on the same order overwrote return_cost instead of summing it (4.0 != 8.0), and no order version table existed (0 != 1).

Fix (ledger.py new; bq.py, cost_config.py, order_economics.py; four DDL files): - Durable unique event ledger net_yield_event_ledger keyed (tenant_id, provider, provider_event_id) (provider='shopify', provider_event_id = Shopify's refund id). The claim is ONE statement — MERGE … WHEN NOT MATCHED THEN INSERT — and num_dml_affected_rows (1/0) tells the delivery whether it owns the event. BigQuery executes DML atomically and serializes DML per table, so concurrent deliveries cannot both insert (the emulator test test_wo09_ledger_claim_is_atomic_insert_if_absent pins the 1-then-0 property; the live concurrency measurement is deferred). - Deterministic materialization, never an increment: refunded_amount = SUM(...), return_cost = SUM(...), refund_flag = TRUE, and economics_complete is derived (no missing_costs AND LOGICAL_AND(processing_known)) — not ratcheted — from the order's ledger rows in one UPDATE … FROM (…) agg. Order of operations in apply_refund: claim → materialize (whether or not this delivery claimed; a retry after a crash between the two must still apply, and applying twice is a no-op) → if the order is absent, hold and return pending. applied is only ever answered when the affected-row count proves the order row changed. - Unmatched refunds retained in net_yield_pending_refunds (insert-if-absent, same key), tenant-bound; upsert_order materializes from the ledger when the order lands and clears that order's pending rows. A same-numbered order under another tenant never absorbs it (test_wo09_pending_refund_never_lands_on_another_tenants_order). - Order changes: upsert_order's updated path rewrites every priced column (ledger.ORDER_UPDATE_COLUMNS, arrays included), records a version in net_yield_order_versions — insert-if-absent keyed by source_payload_hash with order_version = MAX+1 computed in the same statement, so a retried unchanged update mints no second version — and re-materializes refunds against the new version. Both versions are retained; a replayed unchanged payload is still duplicate and adds no version. - Fees keyed by (tenant_id, fee_type) and summed: cost_config.FeeRule, TenantCosts.fee_rules()/fee_lines(gross)/payment_fees(gross)/with_fee(...). The two parity-pinned YAML rates are two named types (payment.percent_of_gross, payment.fixed_per_order); further types register under their own key. The vocabulary the onboarding surface mirrors (_PAYMENT_KEYS etc.) is unchanged — the service-marketing-connectors parity suite passes (217). A duplicate key is refused (CostConfigError), never overwritten and never silently double-counted; with_fee returns a copy because CostConfig.for_tenant hands out one shared object per tenant (a mutating API would leak one request's registration into every later request — found by the test run, fixed before commit). Per-order fee lines are materialized to net_yield_order_fees per (tenant_id, order_id, order_version, fee_type); the order row's payment_fees is their sum; fee_lines is not an order_economics column and as_row() leaves it out (the streamed row keeps the DDL's shape). tenant_gaps now reports payment_fees missing iff no fee type at all is known. - Fail closed on a silent client: a client that swallows DML makes upsert_order raise (version not recorded — never assumed v1) and apply_refund answer pending (test_bq.py::TestFailClosedOnSilentClient). - bq.PARAM_TYPES types NULL-able SDK parameters (a STRING-typed NULL in a FLOAT64 slot is a BigQuery error); timestamps are bound as ISO strings and parsed in SQL (TIMESTAMP(@p)); returns_adjustment.py needed no change — the multiplier is applied at map time and is now recorded on every ledger row (return_cost_multiplier_applied), so each row states its own basis. - net_yield_refund_ledger (W1) is superseded: no longer written, never dropped, header says so.

Acceptance evidence (python3 -m pytest services/net-yield -q -k wo09, 17 passed; whole suite 200): duplicate refund → exactly one ledger row, $40 once (test_wo09_duplicate_refund_delivery_applies_40_once, …_ledger_claim_is_atomic_insert_if_absent); refund-before-order → held, pending, matched on arrival, pending row cleared (test_wo09_refund_before_order_is_held_then_matched_on_arrival, …_redelivered_stays_one_row, …_never_lands_on_another_tenants_order); crash between ledger insert and apply, then retry → idempotent (test_wo09_crash_between_ledger_claim_and_apply_then_retry_is_idempotent, and the main-shaped crash …_between_apply_and_ledger_…); two fee types → both present in net_yield_order_fees, sum on the row, YAML-only output byte-identical to pre-fix (test_wo09_two_flat_fee_types_are_both_kept_and_summed, …_registering_the_same_fee_type_twice_is_refused, …_yaml_only_fees_are_byte_identical_to_pre_fix); order change → COGS 24 → 36, fees recomputed, versions 1 and 2 retained, applied refund survives the recompute, a change that breaks completeness is named not numbered (test_wo09_changed_order_recomputes_cogs_and_keeps_both_versions, …_order_change_keeps_already_applied_refund, …_replayed_unchanged_order_adds_no_version, …_order_change_that_breaks_completeness_is_named).

Operator DDL (before any dispatch of a WO-09 build): services/net-yield/bigquery/schemas/net_yield_event_ledger.sql, net_yield_pending_refunds.sql, net_yield_order_versions.sql, net_yield_order_fees.sql — bq query --use_legacy_sql=false --project_id=<PROJECT> < <file> after substituting PROJECT_ID.DATASET_ID. Without them the refund path fails loudly (MERGE on a missing table → 5xx), which is the fail-closed direction, not a silent one — but it is an outage for refunds, so the DDL goes first. docs/net-yield/RUNBOOK.md is outside this prompt's ownership and still lists only the W1 ledger: coordinator follow-up to add the four files to its DDL step.

Files changed (21; git diff --stat origin/main HEAD: +1962 / −205)

services/net-yield/: compute.py, bq.py, main.py, ledger.py (new), cost_config.py, order_economics.py, README.md, test_bq.py (ported onto the emulator, original intents kept), test_api.py, test_wo08_tenant_keyed_sql.py (new), test_wo09_replay_safe_economics.py (new), tests/bq_emulator.py (new), tests/requirements-test.txt (new), tests/snapshots/{sku_return_rates,nightly_order_merge,cohort_merge}.sql (new), bigquery/schemas/{net_yield_event_ledger,net_yield_pending_refunds,net_yield_order_versions,net_yield_order_fees}.sql (new), bigquery/schemas/net_yield_refund_ledger.sql (superseded header).

5. Deferred, and why

1. Live BigQuery runs (both WOs) — no GCP credentials or Google network in the sandbox; no scratch dataset was created or dropped. When a session with access runs them, in this order: (a) apply the four DDL files to audit_wo08_<initials>_<date> plus the repo-root order_economics.sql/net_contribution.sql; (b) streaming-buffer DML — insert an order via insert_rows_json, apply a refund within a minute, and expect BigQuery to refuse the UPDATE; that hazard pre-dates this pack (main's _update_order/apply_refund had it) and the fix is to land order rows by DML INSERT/load job rather than streaming, which needs ARRAY parameters or a JSON-parsing insert — not done here; (c) the SDK ARRAY parameter typing in bq._sdk_param (line_items incl. the empty-array StructQueryParameterType case) is unexecuted; (d) the version MERGE's USING subquery references its own target table — legal in DuckDB, believed legal in BigQuery, unmeasured; (e) the two-tenant invariance and the four WO-09 cases as written, then drop the dataset.
2. MEAS sign-off on the invariants (lane requirement) — owed; the invariance test is the artefact to sign.
3. docs/net-yield/RUNBOOK.md DDL step (outside ownership) — coordinator.
4. Modal-SKU rate (ANY_VALUE today) — follow-up, above.
5. Wiring services/net-yield into a CI workflow with duckdb installed — .github/** is protected/CX-4; today the suite runs offline only, as it did before this pack.

Session note: one git stash -q was typed by mistake mid-lane in the worktree; git stash list was empty afterwards and the tree had no tracked modifications at that moment (verified before continuing), so nothing was stashed or lost.

Gates run

python3 -m pytest services/net-yield -q                       # 200 passed (173 on main + 27)
python3 -m pytest services/net-yield -q -k "wo08 or wo09"      # 26 passed
python3 -m pytest services/service-marketing-connectors -q     # 217 passed (vocabulary parity with cost_config)
python3 .github/scripts/deploy_router.py --base origin/main --head HEAD   # 0 workflows matched
bash .github/scripts/content_gates.sh                          # canon audit 0 findings; governance gate suites 138 passed
python3 scripts/gate_leak_scan.py --check                      # 0 new / 0 grown / 0 stale
python3 -m pytest tests/reports/test_evidence_manifest.py -q   # 12 passed
python3 scripts/claude_memory.py check --strict                # structurally valid
rule-03 V1/V2/V3 greps over the diff                            # empty

Skipped for lack of credentials: bq mk/bq rm of the scratch dataset and every live query (§5.1).

Base SHA: 1f601d171. Branch head: d51d0ee48. PR: to be opened by the coordinator with the title above. Not pushed; no claim recorded; docs/OPEN_ITEMS.md, CLAUDE.md and .claude/memory/** untouched.