Turning a Fragile Commission Workflow into an Auditable Retool Operations System

Project snapshot

Client / context: North Coast Medical / RX Cart commission operations

Role: Full-stack design engineer

Focus: Retool admin UX, payout integrity, reconciliation, data modeling, affiliate attribution, operational reporting

Stack: Retool, MySQL/MariaDB, SQL transactions, PHP ecommerce data, Holistics, Redshift, dbt models

One-line version

I redesigned a fragile affiliate-payout workflow into an auditable Retool operations system that let admins understand who was eligible, preview exactly what would change, complete a payment atomically, and safely reverse mistakes.

The challenge

The commission workflow looked simple from the outside: find eligible RX Cart commissions, pay the recipient, and mark the related order items as paid.

Underneath, the data crossed several identities and systems:

• a customer number could be shared by multiple users
• an affiliate ID could span orders whose user attribution was incomplete
• commission amounts lived at the order-item level
• eligibility depended on order state, age, payout status, and business exclusions
• payout history stored order-item IDs as one comma-separated string
• Retool returned SQL results in column-oriented arrays rather than ordinary row objects
• older records and newer records needed to remain readable during migration

The incident that triggered the redesign was a partial-update failure. An admin attempted to mark a payment as complete in Retool, received an error, and then discovered that some rows appeared to have changed anyway.

The first reconciliation exposed a real discrepancy:

• payout-history total: $1,522.68
• paid order-item total: $1,338.57
• difference: $184.11

That was the product problem. The interface could report an error without making the resulting database state understandable. An operator could not confidently answer four basic questions:

1. What was eligible before the action?
2. Which order items were actually updated?
3. Was a matching payout-history record created?
4. How could the action be reversed without making the data less trustworthy?

My role

I worked across product design, Retool interaction design, SQL, schema design, data reconciliation, and reporting.

My contributions included:

• mapping the payout workflow from Retool controls to order-item and history tables
• writing preview queries that exposed the exact records behind every total
• reconciling payout history against paid and pending order items
• correcting Retool result-shape and SQL-interpolation failures
• redesigning payout history as a normalized parent-child relationship
• implementing transaction-safe payment and undo operations
• evolving eligibility from user-level to customer-number and affiliate-ID views
• preserving backward compatibility for historical payout records
• adding operator-friendly details such as item descriptions, order dates, ship-to numbers, counts, and last-paid dates
• extending the work into Holistics and Redshift model relationships for durable analysis

Starting with evidence

I did not begin by rewriting the update statement. I first designed an audit path that made the current state legible.

Reconcile the two sources of truth

The original workflow treated two things as if they were always synchronized:

• order_items.affiliate_commission_payout_status
• affiliate_commision_payouts

The audit compared totals from both sides and then drilled down to individual order items. This revealed several distinct failure modes:

• paid order items with no matching history entry
• history entries that referenced order items still marked pending
• payout totals that did not equal the sum of their linked item commissions
• history strings that had been truncated and silently omitted IDs
• multiple users belonging to the same customer account
• multiple affiliate IDs appearing within one customer account

Separating these cases mattered. A single “totals do not match” warning was not actionable enough for an admin.

Build preview-first queries

Before any mutation, the Retool workflow gained read-only views for:

• eligible recipients
• eligible order-item details
• amount to pay
• number of orders
• number of items
• item numbers and descriptions
• order-created and order-modified dates
• current payout status
• customer, ship-to, user, and affiliate attribution
• last paid date
• payout-history rows and their linked items

This changed the operator experience from “click and hope” to “inspect, reconcile, then act.”

Designing the admin workflow

The workflow became a sequence of explicit decisions instead of one opaque action.

1. Find who is ready for payment

The first query originally grouped by user ID. That was not always the right business grain because two users could belong to the same customer number.

I updated the readiness model to aggregate qualifying commission by customer number, while still exposing the related user IDs and contact information. For accounts that needed affiliate-level separation, I created an affiliate-ID version that did not depend on user joins to discover the commission rows.

Eligibility rules stayed visible and consistent:

• submitted orders only
• pending commission items only
• non-null commission amounts
• orders older than 30 days
• an explicit order-date floor for the current program
• business exclusions such as test accounts
• a minimum payable commission threshold

The important design decision was to preserve the actual payment grain. Customer-number and affiliate-ID views were treated as different operational modes, not forced into one ambiguous grouping.

2. Inspect the payment contents

Selecting a recipient loaded the actual order items behind the total. The table returned meaningful rows rather than only a long ID string:

• order item and order IDs
• order dates
• item number
• item description
• quantity and price
• commission amount
• payout status
• attributed user and account details
• ship-to number
• affiliate ID

A separate summary query returned the amount to pay, total orders, and total items. This gave the operator a fast overview without hiding the underlying evidence.

3. Preview the write

The write operation was represented in the UI by the same data the operator had just reviewed. I also provided SELECT equivalents for UPDATE statements so changes could be validated safely before execution.

This was especially important because Retool’s SQL interpolation created confusing failures. Query results such as readyPerson.data.order_item_id were column arrays, not arrays of row objects. Expressions using .map(row => row.order_item_id) failed because the presumed row shape did not exist.

Large comma-separated values introduced a second class of errors: depending on Retool’s prepared-statement behavior, an interpolated list could become one bound string instead of many SQL values. The query would either fail near the first ID or match only one row.

The safer pattern was to treat Retool’s returned column arrays deliberately, validate numeric IDs, and avoid asking SQL to parse application-generated lists when a relational path was available.

4. Commit atomically

The final payment operation used one database transaction:

1. insert the payout header
2. capture the new payout ID
3. insert one child row per order item
4. update only currently pending order items to paid
5. commit the transaction

If any step failed, the transaction could roll back instead of leaving a history record without statuses, or statuses without a history record.

The status guard remained part of the UPDATE:

affiliate_commission_payout_status = 'pending'

That made retries safer and prevented an old Retool selection from rewriting unrelated states.

5. Support a deliberate undo

The undo workflow also became transactional:

1. identify items linked to the selected payout
2. change only currently paid items back to pending
3. delete the payout’s child links
4. delete the payout header
5. commit

Because undo is destructive, the detail query acts as the confirmation surface. The operator can see the payout ID, recipient, amount, item count, linked total, and every affected order item before running it.

Fixing the data model

The original payout-history design stored all linked order-item IDs in one text field:

pay_to_order_item_lines = "29981480,29981479,..."

That worked only while the string remained short and every consumer parsed it correctly. In practice it created several problems:

• the field could truncate
• referential integrity was impossible
• joins required FIND_IN_SET
• indexes could not help
• totals could disagree with the IDs that survived
• one malformed value could make an audit incomplete
• Retool had to shuttle large SQL fragments between queries

I introduced a normalized junction table:

affiliate_commission_payout_items

Each row links one payout to one order item. The history model became:

• one row in affiliate_commision_payouts for the payment event
• many rows in affiliate_commission_payout_items for its contents

This made the relationship queryable, indexable, and auditable.

Backward-compatible migration

Historical records still used the old comma-separated field, so the reporting path supported both formats:

• use normalized child rows when they exist
• fall back to pay_to_order_item_lines for older payouts
• backfill old records where the stored history is complete enough
• avoid pretending truncated historical strings can be reconstructed with certainty

This let the team improve future integrity without breaking access to old payment history.

Reconciliation and recovery

The payout incident required more than a new forward path. I built focused audits to repair existing state.

Examples included:

• paid order items missing from payout history
• pending items that appeared in payout history
• payout headers whose amount differed from linked item totals
• paid and pending totals for one recipient over a known date window
• cutoff-based recovery when business records confirmed the final paid item
• preview and update queries for returning incorrectly paid items to pending

One audit found 28 pending items totaling $56.72 that were already represented in payment history. Those items could be reviewed and marked paid without creating a duplicate payment.

In another case, a payout header showed $698.19, while the legacy history string only resolved to $378.54 of order-item commission. The issue was not the SUM query; the stored ID list had been truncated. That discovery was the clearest proof that the history field itself was structurally unsafe.

Extending the system beyond payout execution

Once the operational grain was reliable, the same work improved broader RX Cart reporting.

Affiliate attribution

Order-item affiliate IDs became a first-class reporting dimension. The queries could show:

• who an affiliate ID belonged to
• the associated customer and ship-to
• all pending commission items for the affiliate
• last paid date by affiliate ID
• customer accounts containing multiple affiliate IDs

This prevented customer-level aggregation from hiding separate attribution streams.

Bundle and share reporting

The Retool work also connected bundle ownership, share tokens, view tracking, and submitted orders. Reports distinguished:

• direct bundle views
• shared views
• QR views
• link views
• bundle owner
• share-token user
• customer and ship-to location
• submitted order count
• account, starter, and regular bundles

The reporting language was intentionally role-specific. Columns such as sharer_first_name, created_by_ship_num, and attributed_user_source are more useful to an operator than generic “resolved” fields.

Warehouse readiness

I updated the Holistics modeling plan so orders, order items, RX orders, bundles, users, and share tracking could be related at the correct many-to-one grain.

I also added reusable measures for:

• order-item count
• distinct order count
• total quantity
• total order price
• total affiliate commission

This moved the work from one-off Retool debugging toward a reporting model that could support ongoing analysis.

Design engineering decisions

Treat internal tools as product surfaces

The people using Retool were making financial decisions. Their experience needed the same care as a customer-facing checkout:

• clear grouping
• meaningful labels
• visible scope
• summary plus detail
• safe defaults
• recoverable actions
• understandable errors

Put evidence beside action

The most important interaction pattern was proximity: the rows being changed, the amount being paid, and the action that commits the change should all describe the same selection.

Model reversibility

Undo was not an emergency SQL snippet added afterward. It became part of the workflow design. Financial operations are safer when reversal is explicit, scoped, and auditable.

Fix the relationship, not only the query

The comma-separated field was the root of several downstream problems. Continuing to tune FIND_IN_SET would have made individual queries faster to write while leaving the system fragile. The normalized child table removed the class of failure.

Preserve ambiguity when history is incomplete

A truncated historical record cannot be made certain by a clever query. The workflow distinguishes verified links from inferred recovery decisions and keeps previews in front of every corrective update.

Impact

The work created a much safer commission-operations surface:

• payout totals can be traced to individual order items
• the operator can preview every affected row before payment
• payout history and order-item statuses update in one transaction
• large payouts no longer depend on a text field containing every item ID
• undo operations are scoped to one payout and one verified item set
• old and new history formats can coexist during migration
• customer-level and affiliate-level payment views reflect different business grains
• recurring audits can detect drift before the next payout
• bundle, attribution, and commission data can flow into Holistics at a clearer relationship grain

The biggest outcome was not one corrected total. It was changing the operating model from manual confidence to inspectable evidence.

What made this hard

The hardest part was that each individual query could look correct while the overall workflow was still unsafe.

The system crossed:

• legacy schema conventions
• Retool’s column-oriented result shapes
• SQL prepared-statement interpolation
• customer, user, bundle-owner, share-token, and affiliate identities
• old comma-separated history and new relational history
• production data whose incomplete past could not always be reconstructed

Solving it required moving continuously between interface behavior, SQL execution, schema design, business rules, and operator communication.

What I learned

Financial admin tools need stronger interaction contracts than ordinary CRUD screens.

The system should always make five things clear:

• what is selected
• why it is eligible
• how the amount was calculated
• what will change
• how the action can be audited or reversed

I also learned that error handling is part of data design. A Retool error is not enough if a database transaction can partially succeed. The interface, transaction boundary, and history model all have to agree on what “payment completed” means.

Why it matters

This case study shows full-stack design engineering in an internal, business-critical context.

I did not only design a cleaner Retool screen or write a corrected SQL statement. I traced a financial-integrity incident through UI state, query result shapes, business rules, database relationships, and reporting. Then I redesigned the workflow so the interface and data model reinforced each other.

That is the kind of work I value most: making a complex system understandable enough to operate safely, then improving the stack so the same problem is less likely to happen again.