In detail
Project snapshot
Client: North Coast Medical / RX Cart commission operations
Role: Full-stack design engineer
Stack: Retool, MySQL/MariaDB, SQL transactions, PHP ecommerce data, Holistics, Redshift, dbt models.
Problem
A Retool payment-completion error exposed partial updates. Reconciliation found payout history totaling $1,522.68 against $1,338.57 in paid item commissions: a $184.11 discrepancy.
Shared customer numbers, incomplete user attribution, affiliate IDs, item-level commissions, and comma-separated legacy history made eligibility, mutation scope, audit, and reversal difficult to establish.
Approach
I worked across admin UX, SQL, schema design, reconciliation, and reporting. Read-only previews exposed recipient eligibility, item evidence, totals, counts, attribution, dates, status, and linked history. SELECT equivalents allow operators to inspect UPDATE scope first.
Implementation
- Moved readiness aggregation from user ID to customer number, with an affiliate-ID mode independent of user joins.
- Eligibility requires submitted orders, pending non-null commissions, orders older than 30 days, an explicit program date floor, business exclusions, and a minimum payable threshold.
- Handled Retool’s column-oriented arrays deliberately. Prepared-statement interpolation can bind a comma-separated list as one string; numeric IDs are validated and relational links preferred.
- Replaced reliance on pay_to_order_item_lines and FIND_IN_SET with affiliate_commission_payout_items linking order items to affiliate_commision_payouts headers.
- Payment uses one transaction: insert header, capture ID, insert per-item links, update only pending items, then commit; rollback remains available on failure.
- Transactional undo identifies linked items, returns only currently paid items to pending, deletes child links and header, and commits. Detail previews provide confirmation.
- Reporting prefers normalized links, falls back to legacy strings, and backfills only sufficiently complete records.
- Added reporting around affiliate attribution, bundle ownership, share tokens, view sources, and submitted orders. Holistics relationships use the correct many-to-one grain and reusable order, quantity, price, and commission measures.
Audit findings and outcomes
Audits identified 28 pending items totaling $56.72 already represented in payment history, enabling review without duplicate payment. Another header recorded $698.19, while its truncated legacy string resolved to only $378.54.
Delivered item-traceable totals, scoped undo, and atomic history/status updates. Truncated legacy history cannot be reconstructed with certainty. These figures describe reconciliation findings, not claimed revenue or savings.
Adapted from portfolio text; images and navigation omitted.