01The pipeline
The pipeline
The point of sale has no API. It writes nightly file exports to a third party’s server, and those files are the only way in.
Products in the exports are identified by a coding scheme that had never been documented.
- Profile the raw files
- Decode the scheme by hand
- Check the decode against the business’s known totals
Every transaction maps to a product class with no codes left over. The decode is now the business’s data dictionary.
Voids and failed charges go to explicit exclusion classes instead of being dropped.
02Dashboards
The dashboards
The owners’ analytics brief became a set of Tableau dashboards, with every item in it either covered or recorded as blocked with the reason.
Membership
Lifecycle and retention, including usage in the months before a cancellation.
Services
Service adoption and cross-sell.
Locations
Location, equipment and capacity, built on occupied-minutes utilisation.
Packages
Package sales and session-pack consumption.
Staff
Employee performance and benchmarking.
Acquisition
New customers by monthly cohort, with cohorts too young to measure left blank.
Where the data to answer a question does not exist, such as hours worked or costs, the dashboard says so and names what the business would need to provide.
I have since added dashboards built to replace the legacy intranet’s reports. They are with the owners for review, and the intranet is still running.
03The API
The API and the ledger
Points writes in the mobile app. Any points write needs an admin-scoped credential, which a public app cannot hold.
Writes on a trusted server, and the balances in a Postgres ledger behind it.
- Append-only ledger. A balance is computed from its entries and never stored as an editable field, so a migration loses nothing and every change can be audited
- Interchangeable storage backends behind one interface, so the data can move between providers
- Ownership-gated endpoints. Every per-customer read and write needs a token bound to that customer’s record, and the app never holds a service secret
- Server-side pricing. Brand, reward cost and affordability are resolved on the server and never read from the request
- Atomic redemption. The balance check and the debit run together, so two simultaneous redemptions cannot overdraw. I tested it by firing them at once
- A security review before launch, with every finding fixed before customers moved onto the ledger
- Identity on Supabase with passwordless email-code sign-in. The old vendor never exported password hashes, so no customer has to reset one
- Turborepo and pnpm monorepo, with a test suite that runs locally
04Paying once
Paying out once from a shared feed
The old system stored one balance per customer and read the same nightly sales feed the new ledger reads. Importing its balances and then replaying the feed would have paid customers twice for the same visit.
Every points write goes through a single database function that does three things in one transaction.
- Lock per customer
- Check the idempotency key and the balance
- Insert
Each source of entries has its own key namespace, imports skip feed entries the carried-over balance already covers, and a dedupe pass removes anything counted twice. Imports retry failed writes and end with a verify step.
After the dedupe, the ledger matched the old system’s export exactly. Customers stay on the old app until the new one releases, so for now the ledger runs alongside it.
05Claims
Checking claims against the data
An outside partner’s proposal made a claim about the customer records. I measured it against the live database.
- ClaimedMost customer records were duplicates
- MeasuredThe real rate was a few percent
- Proposed fixMerge on email, which would have fused unrelated customers who share a placeholder address
- Also caughtConflicting consent flags, before the migration could carry them over
06Not finished
Where this is not finished
The pipeline reconciles on every run, but the check is circular: the reference it matches against comes from the same export. It has not been validated against the company’s books.
The closest independent check is the legacy intranet’s own reports.
- Session countsMatch at every store to within one
- Sales and commissionMatch at some stores and not others, and those gaps are open
- To close itOne month-end report from the point-of-sale system
Until then this page claims coverage only.