A refund is not a status

The old refund button worked. An admin clicked it, the customer’s subscriptions were cancelled, and the payment status changed from confirmed to refunded. Two database calls. One toast. Done.

It was also wrong in almost every way that mattered.

A partial refund did not fit. A replacement did not fit. A failed transfer did not fit. There was nowhere to record why the decision was made, which product line it affected, where the money went, or whether the supplier returned any of the cost. Changing the payment status also destroyed the original fact that the customer had paid.

I had not built a refund system. I had built a flag.

Replacing it became one of those projects where the visible interface was the smallest part of the work. The real job was deciding what a refund actually means inside Royal Subz, then making sure the database, reporting, permissions, customer flow, and published policy all agreed.

Start with the constraint, not the interface

My first assumption was that the payment gateway should do the refund. That would have been convenient. It also was not possible.

I went through all 125 endpoints in NOWPayments’ official collection. The word “refund” appeared six times, always inside descriptions of payment statuses. refunded is a status the gateway may report after its support team manually rescues a customer-error payment. It is not a status merchants can set.

The payout API was not a practical substitute. It requires short-lived authentication, a whitelisted source IP, pre-approved destination addresses, 2FA approval, and a custody balance. Royal Subz uses auto-forwarding, and Supabase Edge Functions do not have a stable egress IP anyway.

The more important problem was simpler: the payment payload does not contain the customer’s sending address. It contains our deposit address and transaction details, but not a reliable destination for returning funds. Binance was no better because our integration verifies payments; it is not a refund-capable gateway.

That changed the system boundary.

Money movement would stay manual. The product’s job was to become the authoritative record of what was requested, decided, sent, and completed.

The refund destination had to come from the customer at request time because it could not be recovered later from either gateway.

The policy became the specification

Before designing tables, I read the published refund policy as if it were an API contract.

It contained rules the old button knew nothing about:

• The 48-hour activation window is a minimum age for a customer request, not an expiry date and not a restriction on an admin fixing an unavailable product early.
• Replacement is the preferred resolution where a product has warranty coverage.
• If replacement is impossible, the customer may receive a partial refund for unused days.
• Defect claims can require an investigation, and a rejected decision is final.
• Requests should be reviewed within 24 hours, while approved refunds may take 5–7 business days to process.

It also exposed a content problem. Three places on the site promised a seven-day money-back guarantee. The actual policy contains no such guarantee.

I tested the design against three real situations: an unavailable product refunded before 48 hours, an account that stopped working halfway through its term with no replacement stock, and a discretionary full refund. If the model could not represent all three without special-case edits, it was not the right model.

A case and a ledger are different facts

The central decision was to separate the customer problem from the money movement.

refund_requests is the case: who raised it, what they claim, the investigation, the decision, the destination they supplied, and whether the outcome was final.

refunds is the money ledger: amount, method, status, outgoing reference, who approved it, when it was sent, and when it completed. refund_items records which subscriptions or invoice lines the amount belongs to and how the figure was calculated.

A replacement closes a case but creates no refund row. That is correct because no money moved. A single payment can have several partial refunds without losing its original payment record.

The payment itself remains confirmed. Refund state is derived from completed ledger entries:

completed refunds = 0                 → no refund
0 < completed refunds < amount paid   → partially refunded
completed refunds = amount paid       → fully refunded

This sounds like a database detail. It changes the meaning of the whole system. Gross revenue remains the historical fact of money received. Refunds are recorded when money leaves. Net revenue is the difference. Refunding something today no longer reaches backwards and makes an old sale disappear.

Partial refunds need a clock you can defend

The policy covers the remaining subscription period, so the calculation needed a real starting point. Payment time was wrong: activation can happen hours later, and charging those hours against the customer would quietly reduce their refund.

I added delivered_at, stamped when access is first created. Coverage falls back through delivery time, the access record’s creation time, and finally the subscription start date for older data.

Warranty became structured data too. A product can define warranty_days, a variant can override it, and the resolved value is snapshotted onto the subscription at purchase. Changing the catalog later cannot rewrite the cover somebody already bought.

The pro-rata calculation is deliberately boring:

unused days ÷ total days × amount actually paid

“Amount actually paid” matters when a coupon was used. List price can produce a refund larger than the revenue received.

Even the rounding needed work. Dividing five cents across seven lines and rounding each independently produces seven cents. The final implementation uses largest-remainder apportionment: floor every share, then distribute the leftover cents to the largest discarded fractions. The line items always reconcile exactly to the refund total.

The important button is a transaction

The old flow made separate client-side updates. If the first succeeded and the second failed, the system was left halfway through a refund.

The replacement uses SECURITY DEFINER database functions with internal permission checks. create_refund locks the payment row, checks the remaining refundable balance, creates the ledger and line items, applies the chosen access revocations, optionally restores coupon usage, and closes the originating case in one transaction.

The over-refund guard includes refunds in requested, approved, sent, and completed states. A requested refund is already a claim on the balance. Leaving it out would allow two admins in separate tabs to each create a full refund before either one was approved.

The row lock is the least visible part of the feature and the most important. Without it, two individually valid actions can still send the same money twice.

I planned the rollout around what could go wrong

I split the work into eight phases: schema and atomic functions, the admin refund dialog, the operations queue, reporting, warranty plumbing, customer visibility and the status cutover, customer requests, then emails and policy corrections.

The order changed during implementation. Reporting moved ahead of the queue because the new refund dialog stopped overwriting payment status. Shipping that dialog alone would have recorded refunds correctly while temporarily overstating revenue. The accounting fix mattered more than the next screen.

The legacy cutover was even more sensitive. Flipping old refunded payments back to confirmed before every reporting and customer-facing reader moved to the ledger would have put five historical refunds back into revenue. I delayed the flip until the last status-based consumer was replaced, then migrated the old rows and added a constraint preventing refunded from returning as a payment status.

This was less elegant than a single large launch. It was also much easier to reason about and reverse.

The self-review found the expensive bugs

The first implementation was not the final one. A deliberate completeness and breakage review after every phase caught problems that ordinary happy-path testing would not.

The pro-rata calculation initially depended on the admin’s local timezone, so Dhaka and New York could calculate different refunds from the same timestamps. A realtime listener was present but its table had never been added to Supabase’s realtime publication. One delete button still cascaded through the new tables and could erase the audit trail. A service-role permission check asked auth.uid() who the caller was and received null, rejecting everyone.

The end-to-end audit found the most embarrassing one: delivered_at had been backfilled for historical subscriptions but was never stamped for new ones. The field existed, the old data looked correct, and every future order would silently fall back to the wrong clock. I replaced three easy-to-forget call-site writes with one database trigger on the first access record.

That is why the implementation plan grew to include manual deployment steps, expected SQL results, rollback instructions, permission tests, and failure cases. A plan is not only a list of things to build. It is a record of what must remain true while the system changes.

Where it stands

The core schema, reporting migrations, warranty snapshotting, and payment-status cutover are applied. All eight phases are code-complete. The remaining rollout is operational: the customer-request and email migrations, the frontend deployment, the refund-email function, and the policy text update.

The system still does not move money automatically. That is not unfinished automation; it is the boundary the gateways impose. What it does now is preserve the truth around that manual action: the request, the decision, the affected access, the amount, the destination, the proof of transfer, and the effect on revenue.

The original refund button took an afternoon. The replacement took research, policy work, data modeling, staged migrations, and several rounds of finding out where my own assumptions were wrong.

That was the lesson: a refund is not the opposite of a payment. It is an operational workflow and an accounting event with a customer waiting at the other end. The model has to carry all three.