Loyalty
Took loyalty away from a SaaS vendor and removed six figures of liability, without a customer losing a point.
An in-house loyalty program built to replace a SaaS vendor: real-time earning at the register, automated coupon minting, and the warehouse as the balance of record.
Context & Goal
A multi-channel retailer running loyalty on Yotpo: an annual SaaS bill, earning rules the business could not change, and point balances that lived outside the warehouse. The goal was to bring the program in-house without customers losing a point, keep earning and redemption real time at the register, and make the warehouse rather than an application the authority on what a customer is owed.
Built and operated by one engineer across the engine, the API tier, the admin console, and the models.
The loop, end to end
A customer buys something and a coupon shows up in under 30 minutes. Nobody presses a button anywhere along this path.
An order or return at the register publishes to Pub/Sub.
Cloud Run computes points per line item and appends to BigQuery. Nothing is ever updated in place.
Earnings, redemptions, adjustments, migrated balances, and expiry union into one balance.
Balances copy to Cloud SQL Postgres so the register answers in milliseconds.
A poller reads balance deltas, mints a real single-use POS code, and Klaviyo tells the customer.
Results
In production since October 2025.
- Retired the Yotpo contract. Eliminated a low 5-figure annual SaaS bill and put the earning and redemption rules under the business's own control.
- Cut six figures of points liability by dropping the vendor's carry-forward rules at cutover.
- 150k+ enrolled customers, with balances visible at the register in real time and no enrollment step to complete.
- Carried the vendor's outstanding liability across in a single cutover. Opening balances seeded from its closing report and 42k conversion coupons issued on in-house rails, zero-weighted in the ledger so migrated value could never be counted twice.
- Under 30 minutes from earning to reward, end to end. Order to warehouse in seconds, serving sync within 20 minutes, mint poller within 10. No manual issuance step anywhere in the path.
- 780k+ point events and 140k+ coupon events processed since launch, including 6k referral rewards on the same coupon rails.
What I built
Eight pieces, all of them mine: the engine, the API tier, the admin console, and the models.
Event-driven rewards engine
On Cloud Run. Consumes POS order and return events off Pub/Sub, computes points per line item, and detects redemptions from the discount codes on completed orders.
Automated coupon minting
A 10-minute poller reads balance deltas against a watermark, a worker mints a real single-use code against the POS promotion API, and the ledger deducts the points on the next model build.
The balance of record, in dbt
An append-only event log in BigQuery and a ledger that unions earnings, redemptions, manual adjustments, migrated opening balances, and inactivity expiry. A hot/cold model split keeps the current day live while history rebuilds nightly.
Serving sync to Postgres
BigQuery to Cloud SQL on a 20-minute loop, so the register gets a balance in milliseconds without ever querying the warehouse.
Admin console for support
Profile search, earning and redemption history, signed point adjustments, and profile merges. Each one lands as an append-only audit event that the models consume.
Associate-driven referrals
On the same coupon rails, with eligibility gates at capture and downstream fraud scoring for code stacking, near-free orders, self-referral, and repeat pairs.
Lifecycle messaging
Through Klaviyo. Mint notifications in real time, and expiry reminders fanned out through Cloud Tasks so a day's worth of sends never lands as a burst.
Monthly coupon accounting
For finance: issued, redeemed, and expired for the period, plus the liability still outstanding, attributed to store.
Key decisions & tradeoffs
Each of these bought something and cost something. The cost is the interesting half.
Point math lives in dbt, not in the services.
One definition of a balance, versioned in SQL and reproducible from the event log.
TradeoffA serving sync has to sit between the warehouse and the register.
Balances are copied into Postgres rather than read from BigQuery at the register.
The register needs a millisecond answer and the warehouse is a batch engine, so the sync is truncate-and-load with a hot/cold split: the small current slice moves every 20 minutes, the large history moves once a day.
TradeoffThe register is always reading a copy, never the source.
Events are append-only and never updated in place.
State like redeemed or expired is derived, which makes any balance auditable back to source.
TradeoffThe models, not the application, decide what a coupon's state is.
Coupons are real POS promotion codes, not a loyalty-side balance.
Redemption is a one-tap Apply to Cart inside the register's own flow rather than a custom checkout integration, and the platform learns about it from the resulting order.
TradeoffRedemption is observed after the fact rather than controlled.
The two services share a Postgres job queue instead of calling each other.
Simple and durable under retry.
TradeoffA coupling with no API contract, so a schema change on one side would stop work on the other quietly.
Rounding is deliberately asymmetric: gains floor, deductions ceiling.
Across millions of events, balances never drift upward through rounding alone.
Earning is restricted to the corporate POS.
Keeping one earning surface is what makes the ledger reconcilable against revenue.
TradeoffE-commerce and franchise stores could have been wired in and were not.
Architecture in detail
The defining loop runs left to right and back again: POS events become points in BigQuery, dbt turns them into balances, the sync copies those balances into Postgres, and the mint poller reads them back to decide who has earned a coupon. Hover a node to trace its connections; click a node for details. The diagram scrolls sideways on smaller screens.