Refund and Revenue Reporting

Shop Rentals uses ledger-style tables for refunds and gift card transactions. This article covers the queries you'll run for accounting, tax, and partner-payout reconciliation.

Two ledgers

Both follow the insert-first discipline: a row is written before any wallet/card credit happens. This means you can audit them independently and the totals always match.

Revenue reporting

Gross revenue (per month)

SELECT
  date_trunc('month', completed_at) AS month,
  count(*) AS rentals,
  sum(total_cents) / 100.0 AS gross_revenue,
  sum(tax_cents) / 100.0 AS tax_collected,
  sum(amount_paid_cents) / 100.0 AS amount_paid
FROM reservations
WHERE subaccount_id = '<your-subaccount-id>'
  AND status NOT IN ('cancelled', 'no_show', 'expired')
  AND completed_at &gt;= '2026-01-01'
GROUP BY 1
ORDER BY 1;

Net revenue (after refunds)

WITH r AS (
  SELECT
    date_trunc('month', completed_at) AS month,
    sum(amount_paid_cents) AS paid
  FROM reservations
  WHERE subaccount_id = '<your-subaccount-id>'
    AND status NOT IN ('cancelled', 'no_show', 'expired')
    AND completed_at &gt;= '2026-01-01'
  GROUP BY 1
),
rf AS (
  SELECT
    date_trunc('month', processed_at) AS month,
    sum(amount * 100) AS refunded
  FROM reservation_refunds rr
  JOIN reservations re ON re.id = rr.reservation_uuid
  WHERE re.subaccount_id = '<your-subaccount-id>'
    AND rr.status = 'succeeded'
    AND processed_at &gt;= '2026-01-01'
  GROUP BY 1
)
SELECT
  r.month,
  r.paid / 100.0 AS paid,
  COALESCE(rf.refunded, 0) / 100.0 AS refunded,
  (r.paid - COALESCE(rf.refunded, 0)) / 100.0 AS net
FROM r
LEFT JOIN rf USING (month)
ORDER BY r.month;

Refund analysis

Refunds by destination

SELECT
  refund_type,
  refund_mode,
  count(*) AS refunds,
  sum(amount) AS total_refunded
FROM reservation_refunds rr
JOIN reservations r ON r.id = rr.reservation_uuid
WHERE r.subaccount_id = '<your-subaccount-id>'
  AND rr.status = 'succeeded'
  AND processed_at &gt;= '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;

Tells you the wallet vs card split, full vs partial split. High card-refund ratios suggest customer dissatisfaction (they want the cash back instead of a wallet credit).

Refunds by reason

SELECT
  COALESCE(reason, '<no reason>') AS reason,
  count(*) AS refunds,
  sum(amount) AS total
FROM reservation_refunds
WHERE subaccount_id = '<your-subaccount-id>'  -- via reservations join
  AND processed_at &gt;= '2026-01-01'
GROUP BY 1
ORDER BY count DESC
LIMIT 20;

Patterns surface common dissatisfaction sources.

Gift card liability

SELECT
  count(*) AS active_cards,
  sum(initial_balance_cents) / 100.0 AS total_loaded,
  sum(initial_balance_cents - current_balance_cents) / 100.0 AS total_redeemed,
  sum(current_balance_cents) / 100.0 AS outstanding_balance
FROM gift_cards
WHERE subaccount_id = '<your-subaccount-id>'
  AND is_active = true;

outstanding_balance is your gift card liability — money collected, service not yet delivered. Should match your books' deferred revenue account.

Partner payouts

If your shop is part of a partner-revenue-share program:

partner_payout = (net_deposited - customer_sales_tax) × partner_share%

where net_deposited = card_funded - stripe_fees - refunds.

This is the formula that makes the refund discipline so important — refunds NEED to flow through reservation_refunds so net_deposited is correct. If you bypass and credit a wallet manually, partner gets overpaid.

Tax remittance

Sales tax flows from reservations.tax_cents:

SELECT
  date_trunc('month', completed_at) AS month,
  sum(tax_cents) / 100.0 AS tax_collected,
  sum(tax_cents) FILTER (WHERE rr.refund_mode = 'full') AS tax_refunded_via_full_refunds
FROM reservations r
LEFT JOIN reservation_refunds rr ON rr.reservation_uuid = r.id
WHERE r.subaccount_id = '<your-subaccount-id>'
  AND r.status NOT IN ('cancelled', 'no_show', 'expired')
  AND r.completed_at &gt;= '2026-01-01'
GROUP BY 1
ORDER BY 1;

For tax filings, the metric you typically need is collected minus refunded (you don't owe tax on what you refunded). This is also where the card_source_amount on wallet transactions matters — it tells you how much of a wallet refund originally came from a card charge.

Damage / late fee revenue

SELECT
  date_trunc('month', updated_at) AS month,
  sum(adjustment_cents) FILTER (WHERE adjustment_reason LIKE 'Late return fee%') / 100.0 AS late_fees,
  sum(adjustment_cents) FILTER (WHERE adjustment_reason LIKE 'Damage at return%') / 100.0 AS damage_charges,
  sum(adjustment_cents) / 100.0 AS total_adjustments
FROM reservations
WHERE subaccount_id = '<your-subaccount-id>'
  AND adjustment_cents > 0
  AND updated_at &gt;= '2026-01-01'
GROUP BY 1
ORDER BY 1;

These are typically your highest-margin revenue lines — virtually pure profit beyond the labor of capturing the events.

Reconciliation tips

When totals don't match between Stripe and your reports

Likely culprits:

• Stripe fees aren't captured (you're seeing gross, not net)
• Refunds went to wallet (no Stripe charge to reconcile against)
• Gift card redemptions (no Stripe involvement)

Reconcile by ledger type, not against Stripe directly. The reservation_refunds.stripe_refund_ids array IS your Stripe-paired audit trail for card refunds; wallet refunds and gift card redemptions are independent.

When refunded_cents on a reservation looks wrong

It's denormalized — recomputed via syncReservationRefundedTotal after each refund. If it looks stale, run the helper manually for that reservation. The source of truth is always the sum of reservation_refunds.amount rows.

When current_balance_cents on a gift card looks wrong

Run:

SELECT
  gc.code,
  gc.initial_balance_cents,
  gc.current_balance_cents,
  gc.initial_balance_cents + COALESCE(SUM(gct.amount_cents), 0) AS computed_balance
FROM gift_cards gc
LEFT JOIN gift_card_transactions gct ON gct.gift_card_id = gc.id
WHERE gc.id = '<gift-card-id>'
GROUP BY gc.id;

If computed_balance != current_balance_cents, the denormalized field drifted. Update it from the computed value.

Auditing the discipline

Quick check that no wallet credit happened without a refund row:

SELECT count(*)
FROM wallet_transactions wt
WHERE wt.reference_type = 'reservation_refund'
  AND wt.reference_id NOT IN (
    SELECT reservation_uuid FROM reservation_refunds
  );

Should return 0. If non-zero, someone bypassed the refund discipline — investigate immediately.