Customer spend tracker
Per-customer spend for the general manager — favorite wines, wine vs. food spend, comped tastings — built on a nightly sync of Clover POS orders that now holds 50,000+ orders.
https://sammishin.dev/work/spend-tracker
- Role
- Lead designer and developer
- Status
- Live · syncing nightly
- Stack
- Supabase functions, Vercel Cron, Postgres, Clover API
- How it’s built
- AI-assisted; I review and test every change
Customer and context
The owner and general manager of 14 Acres Vineyard & Winery, a working winery and tasting room in Ridgefield, Washington. Every sale goes through the Clover POS, but there was no single view of revenue and the top-selling food and wine.
The GM uses the dashboard once or twice a week to see a breakdown of each member: their favorite wines, how much they’ve spent on wine vs. food, their favorite day, how many comped tastings they’ve been given, and more.
Discovery
The real problem: the GM needed accurate data on which wine club members take advantage of their comped tastings, and which don’t.
What it grew into: each member’s favorite wines and food, their lifetime spend and average order size, and all customers, not just members.
Constraints
- Existing systems: the data lives in Clover, so the tracker reads orders from the Clover API rather than asking staff to enter anything.
- Volume: the sync now holds 50,000+ orders, so it has to keep up night after night.
- Duplicate customers: Clover has lots of duplicate customer records, so I had to design a way for several Clover customers to link to the same profile.
- Orders with no customer: plenty of orders have no customer attached, and they can’t be thrown out.
What I shipped
A nightly sync copies Clover POS orders into Postgres (a Supabase function on a Vercel Cron), and on top of it sit a filter-and-search page and dashboards showing different totals and trends.
Three decisions shaped it:
- A nightly sync, not live data. The GM doesn’t need live numbers, and a nightly sync is more efficient.
- Several Clover customers, one profile. Duplicate customer records link to a single profile, so one person’s spend isn’t split across records.
- Every order counts. Orders with no customer attached are kept, so revenue totals stay complete.
Rollout and training
Training was simple: the whole site is one filter-and-search page plus dashboards showing different totals and trends.
Results
-
50,000+ orders synced from Clover into Postgres so far.
-
The GM checks it once or twice a week for accurate data on which members use their comped tastings.
What broke and what I changed
Duplicate customers. Duplicate Clover customer records — the same person saved more than once — split one customer’s spend across several records. The fix was letting several Clover customers link to the same account. I’m also building a deduper package: a hand-written name-similarity matcher, tested with Vitest.
Orders with no customer. I built the tracker assuming every order had a customer attached, so at first the code ignored orders without one. That made total revenue for different food items wrong, so I changed it to keep every order.
What’s next
- Measure the identity matcher on a hand-labeled sample of real duplicates: how many it catches versus how many it wrongly merges
- Package the matcher as a small open-source library with its Vitest tests
- Once the deduper package is finished, extend it to a webhook-based sync