RBD Data Dictionary & Exceptions Ledger

Purpose: The durable home for Cole's tacit understanding of rb_warehouse (NetSuite mirror) data. Every transaction type, item type, filter, and hard-won exception — captured ONCE, never re-derived. This is the backbone of every sales-intel tool wrapper in the Company Brain plan.

How this gets populated (3 channels):

1. Harvest — mine Cole's existing Tableau calcs/filters, SQL, reconciliation scripts (knowledge already externalized).
2. Data-triggered — run anomaly queries, Cole reacts "oh that's because…", log the rule. Triggered > recalled.
3. Usage-triggered — live agent wrong-answers surface gaps → brain-steward approves each as a new rule.

Status legend: ✅ confirmed by Cole · 🔷 inferred (needs confirm) · ❓ unknown/TODO

⭐ CANONICAL SOURCE RULE (governs all drift — Cole's biggest worry, 2026-08-05)

Cole: "some formulas MIGHT be outdated… we have iterated on older workbooks." Drift IS real but bounded and self-resolving:

• Only 11 of 235 calc fields disagree across workbooks — a short, reviewable list, not a fog of unknown staleness.
• RBD Datasources.twb (modified 2026-05, ~3mo old) is the SINGLE SOURCE OF TRUTH. It is the NEWEST version of every divergent field. When any older workbook disagrees, the Datasources version wins.
• Drift = refinement, not rot. Newer versions are more correct (they fixed bugs the old ones had): e.g. Tran or Created Date newer adds the 1970-01-01 epoch check the 2025-02 version lacked; Fulfilled Sales newer handles Quantity Billed=0; old Cancelled Total referenced a since-renamed [is_closed 1] field. Defaulting to newest = inheriting Cole's latest thinking + fixes.
• Final proof = live reconciliation. A formula is only "verified current" when its output ties to a trusted figure (NetSuite/P&L/a number Cole knows) against live rb_warehouse. Harvested = candidate; reconciled = canonical.
• Long-term fix: the brain's tool wrappers become the ONE version-controlled home for this logic → ends the 30-workbook duplication that caused drift in the first place. Building the brain solves the drift problem.

The 11 divergent (drift) fields — resolve to newest, then verify: Cancelled Total, Combined Sales, Fulfilled Sales, Order Type, Sales Channel (4 versions), Sales Channels (4), Sales Timing (3), Tran or Created Date, Year(created_date), + 2 non-sales. Full detail in rbd-tableau-harvest.md / re-run detector.

Canonical RBD sales query recipe (base every wrapper inherits)

1. Sign fix — IF type IN ('CashRfnd','CustCred','RtnAuth','CustRfnd','CardRfnd') THEN -1*net_amount ELSE net_amount ✅
2. Real product lines only — item_type IN ('InvtPart','NonInvtPart') ✅ (also cleans tariffs, which sit outside these types)

3. Revenue-account filter — expense_account_name LIKE '4%' AND expense_account_name NOT LIKE '40600%' → keeps 4xxxx revenue, drops the 12000 AR double-count AND account 40600 = tariffs ✅ LIVE-VERIFIED 3b. Exclude cancellations — is_closed <> 1 (is_closed=1 = CANCELLED, counterintuitive) ✅

4. Booked vs invoiced — SalesOrd (booked/ordered) vs CustInvc (shipped/billed); ALWAYS report both, led by booked ✅

5. As-of — nightly mirror; caveat every time-bounded answer "as of last night's sync" ✅

Transaction types (type field)

Group A — Sales, counted positive (+net_amount)

Group B — Returns/credits, counted negative (−net_amount)

Group C — NOT revenue, exclude from sales

(Full distinct list TODO — enumerate via read-only query: SELECT type, COUNT(*), SUM(net_amount) FROM <txn> GROUP BY type ORDER BY 2 DESC)

⚠️ Harvested gotchas (from Tableau calc fields — would silently corrupt naive queries)

Item types (item_type field)

Include (real product)

Exclude (charges & noise — this is how tariffs get cleaned)

(Full distinct list TODO — enumerate: SELECT item_type, COUNT(*) FROM <line> GROUP BY item_type ORDER BY 2 DESC)

Key harvested business logic (from RBD Datasources.twb, 235 fields total → rbd-tableau-harvest.md)

• Booked vs Invoiced — Booked = SalesOrd; Invoiced = CustInvc + CashSale. ✅
• Pre-sale definition (At Once SO Based): ISNULL(anticipated_release_date)→'At Once'; created_date < anticipated_release_date →'Presale'; created_date >= release →'At Once'. This is THE pre-sale test.
• Unfulfilled ("hole") $: IF Quantity Billed > Quantity THEN 0; elseif Status='Closed' THEN 0; elseif Reorderable='Discontinued' THEN 0; ELSE (Quantity − Quantity Billed) * Item Rate.
• Fulfilled $: IF Quantity Billed = 0 THEN Quantity Fulfilled/Received * Item Rate ELSE Quantity Billed * Item Rate.
• Discount vs full price: foreign_amount = list/gross, net_amount = actual; discount % = -((net_amount/foreign_amount)-1).
• SKU parsing: Case Pack = LEFT(rbd_item_id,2)='CP'; Color Scheme parsed from rbd_item_id via - delimiters (4-char colorway).
• Sales Channel = entity/category/employee_name → channel map; includes the DIRECT sales-rep roster by name (feeds permission table): Firth, Gray, Porter, Taylor, Goldenberg, Ross, Winninger, Goeckeritz, Cloward, Mathis, Lindner, Miller, Rurup, Zimmerman(Cole), Milligan, Andersen, Herndon; Deneault=Independent; 'In-House,'=In House. (Verify current — workbook dated 2024–25.)
• Key fields: net_amount (actual $), foreign_amount (gross/list $), quantity, Quantity Billed, Quantity Fulfilled/Received, Item Rate, rbd_item_id (SKU), rbd_collection, company_name (customer), employee_name/nt_employee_name (rep), class (product category, e.g. 'Batik - *', 'Projects - ME'), entity_name, category, anticipated_release_date, re_release_date, created_date, tran_date.

✅ LIVE VERIFICATION (rb_warehouse, read-only, 2026-08-05) — enumerated against real data

Connection: ssh ubuntu@rb.alpineanalytica.com → pipe SQL via stdin to mysql -ularavel rb_warehouse --batch (creds in [[rb_warehouse Tableau connection]]). Tables: netsuite_transactions (header: type, tran_id, tran_date, ns_transaction_id), netsuite_transaction_items (lines: item_type, is_closed, item_sku, quantity, quantity_backordered, quantity_ship_recv, quantity_committed, expense_account_name, foreign_amount, net_amount).

Transaction type — actual distribution (row counts): | type | rows | note | |---|---|---| | SalesOrd | 318,624 | booked | | CustInvc | 258,617 | invoiced | | CashSale | 248,931 | invoiced — HUGE, ~= CustInvc; missing it halves invoiced revenue | | ItemRcpt | 22,059 | receipts (not sales) | | PurchOrd | 17,681 | POs (not sales) | | CustCred | 3,415 | credit memo (−) | | RtnAuth | 1,932 | return auth (−) — see double-count Q | | CashRfnd | 1,144 | (−) | | CustRfnd | 603 | (−) | | CardRfnd | 8 | (−) |

• NOT present: CustPymt, CustDep, Estimate, Opprtnty — so those aren't in this table. Open confirmation #5 dissolved: the AR double-count is NOT a CustPymt transaction type — it's the 12000 AR line inside NULL-item_type rows (see below).

item_type — actual distribution (line counts / net): | item_type | lines | net | |---|---|---| | InvtPart | 12.69M | $1.37B | | NULL | 1.89M | $764M — GL lines, NOT sales (see below) | | ShipItem | 1.63M | $25.7M (freight) | | Discount | 493K | $0 | | NonInvtPart | 17K | $10.5M (small) | | OthCharge | 1,668 | $2.6M (tariffs/misc — small) | | TaxGroup/TaxItem/Group/Payment/GiftCert | tiny | ~$0 |

⭐ The NULL item_type $764M = GL/accounting double-entry lines, NOT missing sales (resolved by drilling into expense_account): (null acct) $0 headers · 12000 Accounts Receivable $63.7M (THE AR double-count) · 10150 Undeposited Funds · 20000 AP · 10100 Bank · 90100/90200 PO/SO statistical · etc. → Cole's two filters are BOTH correct & redundant: item_type IN ('InvtPart','NonInvtPart') drops them AND expense_account_name LIKE '4%' drops them (they're 1xxxx/2xxxx/9xxxx, not 4xxxx). Live-validated: the scary $764M is exactly what should be excluded. ✅

• Tariffs = account 40600 (from reconcile_rep.py: LIKE '4%' AND NOT LIKE '40600%'). Confirmed the real tariff filter is account-based, not item_type (OthCharge is only $2.6M). Updated canonical recipe.

Return/refund net_amount (stored positive, sign-flipped by formula): CustCred $2.56M · RtnAuth $1.65M · CashRfnd $360K · CustRfnd $340K · CardRfnd $2.9K.

#1 RtnAuth double-count — RESOLVED to a decision for Cole (2026-08-05, live data)

• RtnAuth posts $1.16M to REVENUE accounts (4% excl 40600) — NOT neutralized by the account filter. CustCred posts $1.79M revenue.
• RtnAuth status: 73% "Refunded" (1402/1932), rest Closed/Pending Receipt/Partially Received/Pending Refund.
• Both RtnAuth AND CustCred are in Cole's sign-flip formula → double-subtract risk when a return flows RtnAuth→credit/refund and both are counted.
• No linkage column found (transactions has: source, order_source, but no created_from/applied_to) → can't auto-trace pairs.
• DECISION (Cole 2026-08-05): INCLUDE BOTH. RtnAuth and CustCred are two DIFFERENT transaction types representing different events — NOT the same return double-posted. Keep both in the sign-flip (as canonical net_amount_all_types already does); do NOT net them against each other (that would under-count). ✅ resolved.
• 📌 NOTE TO CONFIRM (not blocking): verify empirically that a single return event does not produce BOTH an RtnAuth AND a linked CustCred with the same net (which WOULD be a true double-count). Cole "pretty sure" but flagged for confirmation. Check: sample RtnAuth 'Refunded'-status txns for a matching same-amount CustCred/refund on the same customer+date.

#2 Cancellation Report harvest + roster (2026-08-05)

• Cancelled = is_closed = 1 (confirmed again). Field named [is_closed 1] in some calcs (naming inconsistency).
• Case Pack detection (2 methods): SKU LEFT(rbd_item_id,2)='CP' OR purchase_description CONTAINS 'casepack'/'case pack' (all case variants) → quantity.
• At Once (Cancellation ver, older/drift): IF created_date > anticipated_release_date THEN 'At Once' ELSE 'Presales' — simpler than canonical (no NULL handling). Use canonical RBD Datasources version.
• Roster drift proof: Cancellation Report lists MORE reps ("RBD Direct Rep" label) than RBD Datasources ("Direct Sales Reps"). Neither is current.

⭐ AUTHORITATIVE IDENTITY & ROSTER SOURCE — netsuite_employees (2026-08-05, live)

Columns: email · first_name · last_name · is_sales_rep · is_inactive · ns_employee_id · commission_rate · hire_date.

• email = the Google SSO join key → solves the email→rep_id mapping blocker. Login: Google email → netsuite_employees.email → ns_employee_id + is_sales_rep.
• employee_name on netsuite_transactions = "Last, First" → links to first/last_name.
• Live active roster = ~40 reps wrote SalesOrders in last 12mo (Firth 3741, Taylor 3369, Andersen 2883, Porter, Mathis, Rushing, Freese, Painter, Wise, Herndon, … Zimmerman/Cole 1470, Cloward Bret 200). 'In-House,' and 'House Account' are non-rep buckets.
• Permission-table seed rule: seed from netsuite_employees WHERE is_sales_rep=1 AND is_inactive=0; enrich by hand for non-rep roles (design/finance/warehouse/exec/customer-care). NEVER seed rep list from a workbook.

⭐ REP ATTRIBUTION — two conflicting rep fields (Cole 2026-08-05, live-verified)

Permission-critical. TWO sales-rep attributions that disagree ~5.4% of the time:

• Customer-record rep: netsuite_customers.sales_rep_id / sales_rep_name (the account's assigned rep).
• Transaction rep: netsuite_transactions.employee_id / employee_name (who wrote the order).
• Join: netsuite_transactions.entity_id = netsuite_customers.ns_customer_id.
• Conflict rate (SalesOrd, last 12mo): match 62,688 (94.6%) · CONFLICT (diff rep) 3,582 (5.4%) · no-rep 1.
• Cole's steer: "most of the time a rep wants to know WHICH transactions are theirs" → transaction-level (employee_id) is the default for 'my sales'.
• Design → two filters, two questions:
• "my customers/accounts" = customer-level (c.sales_rep_id = me)
• "my orders / my sales $" = transaction-level (t.employee_id = me)
• "what did customer X buy" = X's orders with attribution flag (mine vs other rep), since conflict orders exist.
• OPEN — permission default (Cole to pick): on a rep's OWN assigned account, do they see orders written by a DIFFERENT rep? (a) strict transaction-only, (b) UNION [wrote-it OR my-account, labeled] ← recommended, (c) full account-level. Governs the row-scope filter in get_customer_orders.
• Like booked/invoiced: agent must be EXPLICIT about which rep-lens it's using; never silently blend.

Open confirmations (remaining — mostly resolved by live data)

1. ✅ Tariffs = account 40600 (not OthCharge; OthCharge only $2.6M). Filter: LIKE '4%' AND NOT LIKE '40600%'. LIVE-VERIFIED.
2. ❓ NonInvtPart — what is it at RBD? (small, $10.5M/17K lines — still worth a one-line answer from Cole; drop-ship? services?)
3. ❓❓ RtnAuth double-count — THE ONE OPEN QUESTION FOR COLE. RtnAuth ($1.65M) AND CustCred ($2.56M) both carry net_amount and both are in the sign-flip list. In NetSuite a return often flows RtnAuth→CustCred; if both counted, the return is subtracted twice. Hypothesis: credited RtnAuths get is_closed=1 and are excluded, leaving only open ones — but UNVERIFIED. Need Cole's rule: is RtnAuth double-counting with CustCred, or does is_closed/status separate them?
4. ✅ CashSale — YES; 249K txns; counts as invoiced. LIVE-VERIFIED.
5. ✅ CustPymt — dissolved: not a transaction type here; AR double-count is the 12000 line in NULL-item_type rows, killed by both filters. LIVE-VERIFIED.

Exceptions ledger (append-only, dated — the tacit-knowledge capture log)

Every hard-won exception goes here the moment it surfaces. Format: date · trigger · rule · why.

• 2026-08-05 · Cole's Tableau sign formula · refund/credit types negate net_amount · returns must reduce sales.
• 2026-08-05 · Cole's Tableau item_type filter · restrict to InvtPart/NonInvtPart · drops freight/discount/tariff/noise lines.
• 2026-08-05 · Cole · reps don't distinguish booked vs invoiced · agent must always show both, led by booked · chronic human ambiguity.
• 2026-08-05 · Tableau harvest (RBD Datasources.twb) · Invoiced Revenue = CustInvc + CashSale · CashSale is a real RBD sale, always pair with CustInvc.
• 2026-08-05 · Tableau harvest · is_closed=1 = CANCELLED (not completed) · EXCLUDE from sales totals · counterintuitive field name.
• 2026-08-05 · Tableau harvest · tran_date sometimes NULL or 1970-01-01 epoch · coalesce to created_date · else date filters silently break.
• 2026-08-05 · Tableau harvest · Pre-sale = created_date < anticipated_release_date · null release = At Once · the canonical pre-sale test.
• 2026-08-05 · Tableau harvest · foreign_amount=gross/list, net_amount=actual · discount % derived from the two · never confuse the two amount fields.
• 2026-08-05 · LIVE query · NULL item_type ($764M) = GL double-entry lines (AR/AP/bank/undeposited/PO-SO stat), NOT sales · both canonical filters correctly exclude them · the scary number is supposed to be dropped.
• 2026-08-05 · LIVE query · tariffs = GL account 40600 · exclude via NOT LIKE '40600%' · OthCharge item_type is NOT the tariff carrier (only $2.6M).
• 2026-08-05 · LIVE query · CashSale = 249K txns (~=CustInvc volume) · omitting it would halve invoiced revenue · always include with CustInvc.
• 2026-08-05 · Cole (domain call) · RtnAuth + CustCred = DIFFERENT transaction types / different events · INCLUDE BOTH as negatives, do NOT net them · 📌 confirm-later: check no single return posts both same-amount RtnAuth+CustCred (true double-count).

Harvest queue (artifacts to mine — knowledge already externalized)

• ☑ Cole's Tableau workbooks — 235 calc fields extracted 2026-08-05 → rbd-tableau-harvest.md (key logic curated above). REMAINING: mine Cancellation Report (105 fields) + Customer Universe (17) for cancel/customer-segmentation logic; parse worksheet item_type/type/status filter member-values (stored as sqlproxy refs, need deeper parse or live query).
• ☐ Existing rb_warehouse SQL / scripts
• ☐ Rep-sales reconciliation playbook (already in memory: reference_rep_sales_reconciliation_playbook)
• ☐ Schema cheat-sheet (already in memory: reference_rb_warehouse_netsuite_schema)
• ☐ Read-only enumeration of distinct type + item_type values with volumes