2026-04-28 — Exhaustive brand_id INSERT Audit (T9-A baseline)

Sibling audit: this document covers the INSERT half of the brand-leak surface. The symmetric SELECT/UPDATE/DELETE half lives at 2026-04-29-brand-id-rw-audit.md (T14-A baseline). Both halves run on every PR via .github/workflows/brand-id-audit.yml and both must report FIX=0 for Phase 16 hard-flip to proceed (tools/multi_brand_backfill/phase16_dryrun.py).

Round-9 deliverable. The previous five cross-review rounds (CR1–CR5) each trusted a narrow grep, shipped a partial fix, and let the next CR surface 5–30 sibling sites that had been missed. This document is the exhaustive baseline that closes the cycle: every brand-scoped table in migration 0024a_add_brand_id_nullable.py is grepped against both the raw INSERT INTO … form and the SQLAlchemy insert(<Model>).values(…) form, with every match classified.

Future regressions can be detected by re-running the greps in § Reproducing the audit and diffing against the tables below.

Scope

• Tree audited: servers_v2/ (active services). The legacy servers/ monolith is included in the grep but every match is classified OOS-LEGACY.
• Tables audited: 122 brand-scoped tables — 111 in the rgb schema plus 11 in the wallet schema, sourced verbatim from RGB_SCHEMA_TABLES and WALLET_SCHEMA_TABLES in servers_v2/shared/rgb_db/migrations/versions/0024a_add_brand_id_nullable.py.
• Patterns audited:
  • Raw text("INSERT INTO <table> …") and f"INSERT INTO …" strings.
  • SQLAlchemy insert(<ModelClass>) calls, with the model class name cross-referenced to its __tablename__ via the ORM models in servers_v2/shared/rgb_db/src/rgb_db/models/.

Headline numbers

Bucket	Raw INSERT INTO …	SA insert(<Model>)
OK (brand_id bound)	42	28
FIX (production)	55	3
FIX-TEST (test fixture missing brand_id)	6	0
OOS-LEGACY (servers/ tree, intentionally out of scope)	5	252
OOS-NON-SQL (regex literal / docstring / assertion)	53	7
UNKNOWN (parser could not classify)	0	0
TOTAL matches	161	290

Total production sites that need a code change: 58 (55 raw + 3 SA). Total test-fixture sites that need a code change: 6.

The 55 raw FIX sites + 1 SA FIX site (= 56 total production fixes) span 13 files across 5 services. The 6 test-fixture FIX-TEST sites span 3 files. The fixes are partitioned across batches B–F below; the SA entries originally surfaced as OK by a less strict classifier (because brand_id appeared in a sibling INSERT statement nearby) are explicitly verified as missing in this baseline.

Why 55 raw FIX, not 53: tightening the parameter-dict scan to stop at the next await session.execute( or sibling INSERT INTO surfaced two additional sites in agent_service/app/api/routes/legacy_routes.py (coupon at :2166 and coupon_recycle_log at :2313) that an earlier, looser scan was incorrectly marking OK because the immediately adjacent agent_balance_log INSERT did bind brand_id. Those two sibling-INSERT-leakage cases are exactly the failure mode that has caused every prior round to ship a partial fix; the audit doc is the place where that gap is permanently closed.

The 3 SA FIX entries (wallet.py:283, player.py:368, expiry_scheduler.py:115) are listed in Batch B/E sweep below. They cross service boundaries (wallet, player, rolling) so they are folded into the per-service test runs at the end of each fix batch rather than into a single dedicated batch.

Per-batch fix manifest

The remainder of T9 closes every FIX entry below. Batches B–F are the fix batches; this audit (Batch A) is the deliverable that proves the universe of FIX sites is bounded.

Batch B — admin_service back-office CRUD (35 sites)

Source brand_id from request.state.brand_id (populated by the admin BrandResolutionMiddleware post-T8-D). Fail-loud envelope when absent — never default to 0/1.

#	Table	Site
1	agent_announcement	servers_v2/admin_service/app/api/routes/agent.py:1002
2	agent_notice	servers_v2/admin_service/app/api/routes/agent.py:1109
3	bank	servers_v2/admin_service/app/api/routes/banking.py:75
4	bank_card	servers_v2/admin_service/app/api/routes/banking.py:138
5	bank_card_group	servers_v2/admin_service/app/api/routes/banking.py:230
6	bank_owner_color	servers_v2/admin_service/app/api/routes/banking.py:326
7	rebate_config	servers_v2/admin_service/app/api/routes/finance_config.py:91
8	lossback_config	servers_v2/admin_service/app/api/routes/finance_config.py:154
9	player_level	servers_v2/admin_service/app/api/routes/finance_config.py:217
10	player	servers_v2/admin_service/app/api/routes/player.py:710
11	player_note	servers_v2/admin_service/app/api/routes/player.py:746
12	player_group	servers_v2/admin_service/app/api/routes/player.py:843
13	player_tag	servers_v2/admin_service/app/api/routes/player.py:893
14	player_level_domain	servers_v2/admin_service/app/api/routes/player.py:1071
15	coupon_log	servers_v2/admin_service/app/api/routes/player.py:1213
16	player_message	servers_v2/admin_service/app/api/routes/player.py:1233
17	player_message	servers_v2/admin_service/app/api/routes/player_finance.py:312
18	player_message	servers_v2/admin_service/app/api/routes/player_finance.py:1203
19	player_message	servers_v2/admin_service/app/api/routes/player_finance.py:1235
20	coupon	servers_v2/admin_service/app/api/routes/promotion.py:188
21	coupon_log	servers_v2/admin_service/app/api/routes/promotion.py:285
22	coupon_recycle_log	servers_v2/admin_service/app/api/routes/promotion.py:394
23	coupon	servers_v2/admin_service/app/api/routes/promotion.py:838
24	coupon_log	servers_v2/admin_service/app/api/routes/promotion.py:870
25	promotion	servers_v2/admin_service/app/api/routes/promotion.py:930
26	register_event	servers_v2/admin_service/app/api/routes/promotion.py:1039
27	attendance_event	servers_v2/admin_service/app/api/routes/promotion.py:1123
28	attendance_event_details	servers_v2/admin_service/app/api/routes/promotion.py:1148
29	attendance_event_details	servers_v2/admin_service/app/api/routes/promotion.py:1201
30	message_template	servers_v2/admin_service/app/api/routes/promotion.py:1245
31	ban	servers_v2/admin_service/app/api/routes/system.py:148
32	ip_blacklist	servers_v2/admin_service/app/api/routes/system.py:222
33	banner	servers_v2/admin_service/app/api/routes/system.py:401
34	player_notice	servers_v2/admin_service/app/api/routes/system.py:473
35	excel_download	servers_v2/admin_service/app/api/routes/system.py:567

Plus two promotion_service sites that share the back-office shape:

#	Table	Site
36	lossback_config	servers_v2/promotion_service/app/api/routes/lossback.py:140
37	rebate_config	servers_v2/promotion_service/app/api/routes/rebate.py:130

Batch C — admin scheduled tasks (8 sites)

Aggregate writes need per-brand iteration: for brand in brands: wraps each task body so every brand gets one row per period.

#	Table	Site
1	player_message	servers_v2/admin_service/app/tasks/check_expired.py:50
2	player_message	servers_v2/admin_service/app/tasks/check_expired.py:98
3	player_message	servers_v2/admin_service/app/tasks/expire.py:51
4	player_stat_acc	servers_v2/admin_service/app/tasks/stat.py:34
5	agent_stat_day	servers_v2/admin_service/app/tasks/stat.py:95
6	player_tag	servers_v2/admin_service/app/tasks/tag.py:105
7	player_tag	servers_v2/admin_service/app/tasks/tag.py:272
8	player_tag	servers_v2/admin_service/app/tasks/tag.py:346

Batch D — agent legacy_routes coupon siblings + bootstrap (7 sites)

Source brand_id from agent_brand_id already resolved earlier in legacy_coupon_grant. Bootstrap rows hard-code 1 (default brand) with a # dev-only bootstrap comment.

#	Table	Site
1	coupon	servers_v2/agent_service/app/api/routes/legacy_routes.py:2166
2	coupon_log	servers_v2/agent_service/app/api/routes/legacy_routes.py:2218
3	coupon_recycle_log	servers_v2/agent_service/app/api/routes/legacy_routes.py:2313
4	bank	servers_v2/agent_service/bootstrap_local_data.py:148
5	bank_card_group	servers_v2/agent_service/bootstrap_local_data.py:158
6	bank_card	servers_v2/agent_service/bootstrap_local_data.py:169
7	agent_setting	servers_v2/agent_service/bootstrap_local_data.py:391

Batch E — recon shooter_sms doc/migration conflict (3 sites + migration + docs)

shooter_sms rows are inserted before brand context exists (SMS arrives from Pushbullet/Telegram with no player context). New migration 0037_relax_shooter_sms_brand_id.py flips the column back to NULL-able; ORM model loses nullable=False; recon_store.py INSERTs stay as-is; the match-time UPDATE adds a fail-loud guard if brand_id wasn't populated.

#	Table	Site
1	shooter_sms	servers_v2/recon_service/app/services/recon_store.py:509
2	shooter_sms	servers_v2/recon_service/app/services/recon_store.py:569
3	shooter_sms	servers_v2/recon_service/app/services/recon_store.py:884

Batch F — agent coupon cross-brand attribution (mode change, no new sites)

agent_service/app/api/routes/coupon.py grant_coupon / recycle_coupon bind the signed token's brand_id for the coupon_log + player_message writes. If player.brand_id != token.brand_id, the route refuses the request before any balance, coupon, log, or message mutation lands. The agent aggregate is brand-global, so cross-brand-agent-grant is a bug, not a feature.

Batch B/E sweep — SQLAlchemy insert(<Model>) FIX sites (3 sites)

The strict classifier confirms 3 SA-form production FIX sites:

#	Table	Site	Source for brand_id
1	player_balance_log	servers_v2/wallet_service/app/api/routes/wallet.py:283 (_log() helper)	thread the request brand_id through _log() — both call sites (:502 and :570) already have it from _request_brand_id(request)
2	player_login	servers_v2/player_service/app/api/routes/player.py:368	the player row's brand_id is already loaded earlier in the login route — pass it as a kwarg to the insert
3	player_rolling_log	servers_v2/rolling_service/app/tasks/expiry_scheduler.py:115	the cycle already SELECTs rolling.get("brand_id") for the wallet cancel call (line 75); reuse the same value as a kwarg into .values(...)

Verified-OK SA sites worth listing (initially flagged by a looser heuristic, now confirmed safe by the strict classifier):

• servers_v2/rolling_service/app/api/routes/rolling.py:224 — binds brand_id via log_values["brand_id"] = ... before splatting into .values(**log_values) (lines 221–224). The classifier's dict-key-write heuristic confirms this.
• servers_v2/rolling_service/app/api/routes/rolling.py:421 — binds brand_id via insert_values["brand_id"] = ... before splatting into .values(**insert_values) (lines 414–422). Same pattern.

FIX-TEST entries (6 sites)

CR9-A known false positives (T16-D callout): 2 of the historical FIX-TEST entries flagged in servers_v2/agent_service/tests/test_agent_integration.py:1063,1068 are NOT actual INSERTs. They are assertion strings that match the production SQL shape (the test asserts that the production route's INSERT statement contains a brand_id column, by substring match). CR9-A confirmed they are test code that asserts ON production SQL — the brand_id is on the asserted-against side, not the executed side. They are safe to ignore. The classifier's _is_non_sql_match heuristic should ideally pick these up; if it ever surfaces them again, do not treat as a regression — verify by reading the test, then either (a) tighten the assertion to make it classifier-friendly, or (b) add a # brand-global: assertion-only marker on the line above.

These are test fixtures that INSERT directly into brand-scoped tables without binding brand_id. They will fail at INSERT time once 0024c_set_brand_id_not_null is applied to a fresh test schema. They are FIX-TEST (not FIX) because they are not production code paths but they MUST be updated to keep the test suite green.

#	Table	Site
1	coupon_log	servers_v2/tests/e2e/test_coupon_saga_flow.py:165
2	promotion_coupon_saga	servers_v2/tests/e2e/test_coupon_saga_flow.py:182
3	player_stat_day	servers_v2/promotion_service/tests/test_event_consumer_brand_conflict_target.py:36
4–6	player_stat_day	servers_v2/wallet_service/tests/test_stat_day_brand_conflict_target.py:4,20,62 (mostly regex literals, but lines 62+ have an actual fixture-shape SQL template that needs verification)

OOS classifications (rationale)

OOS-LEGACY (5 raw + 252 SA matches)

Every match under /Users/saber/Dev/RGB/servers/ (the legacy monolith tree) is OOS. The legacy services are still deployed in production but do not write to the brand-scoped tables in a way that servers_v2/ cares about: the multi-brand isolation initiative targets the v2 service split, and the legacy services are scheduled for sunset once the v2 cutover completes (see docs/reference/architecture/2026-04-rebuild-decision.md).

OOS-NON-SQL (53 raw + 2 SA matches)

These are matches inside test assertions, regex literals, comments, or docstrings that mention INSERT INTO … for diagnostic/validation purposes but do not execute SQL. Examples:

# tests/test_money_admin_audit.py:216
if "INSERT INTO player_message" in sql:
    captured.append(...)

# tests/test_event_consumer_brand_conflict_target.py:44
r"INSERT INTO player_stat_day.*?ON CONFLICT\s*\(([^)]+)\)"

The classifier flags any match where the SAME LINE contains one of: assert, in sql, in str(, re.compile|search|match, legacy =, pattern =, needle, .count(, .split(, r"INSERT, leading #, or a numbered docstring bullet (^\s*\d+\.).

Reproducing the audit

mkdir -p /tmp/brand_audit

# Raw INSERT INTO sites (any brand-scoped table)
grep -rniE --include='*.py' \
  'INSERT[[:space:]]+INTO[[:space:]]+(rgb\.|wallet\.)?[a-z_]+' \
  servers_v2 servers \
  > /tmp/brand_audit/all_inserts.txt

# SQLAlchemy insert(<Model>) sites
grep -rniE --include='*.py' '\binsert\(' \
  servers_v2 servers \
  > /tmp/brand_audit/sa_inserts.txt

Then run the classifier at tools/audit/brand_id_insert_classifier.py (T11-B3 hoisted runtime tooling; the doc-tree copy was deleted in the same change) to re-partition the matches. The Make target wraps both the greps and the classifier:

make audit-brand-id-inserts

CI runs the same Make target on every PR (see .github/workflows/brand-id-audit.yml); the classifier exits non-zero when RAW FIX > 0 or SA FIX > 0 so a regression fails the build. The classifier is intentionally conservative: it walks the AST-adjacent column list (...) and the parameter dict {...} of each text(...) call and checks for brand_id in either; failures are surfaced as FIX/FIX-TEST/UNKNOWN so a human can break ties.

After every fix batch, re-run the greps and confirm the new bucket counts:

RAW: FIX=0  FIX-TEST=0  OK ≥ 103   (was FIX=55, FIX-TEST=6, OK=42)
SA:  FIX=0  FIX-TEST=0  OK ≥ 31    (was FIX=3,  FIX-TEST=0, OK=28)

If a new FIX entry appears that is not in the manifest above, treat it as a regression — a brand-scoped INSERT was added without brand_id between this baseline and the present commit.

Why the previous rounds missed sites

Each prior round (T6 / T7 / T8) used a hand-curated grep against a handful of "obvious" tables (bet, player_deposit, player_withdraw, wallet_*). The 122-table inventory in 0024a_add_brand_id_nullable.py was never the source of truth for these greps. CR5 finally enumerated that table list against the active codebase and found the long tail of admin/agent-service back-office CRUD that no prior grep had ever covered.

This audit makes the migration table list the source of truth: the classifier reads RGB_SCHEMA_TABLES + WALLET_SCHEMA_TABLES directly from 0024a_add_brand_id_nullable.py so adding a new brand-scoped table to the migration automatically extends the audit's coverage.