How to design a PostgreSQL schema for a UK e-commerce returns database

A customer clicks "return this item", a label appears, and everyone assumes the hard part is over. Months later someone in finance asks why a refund went out twice, or the warehouse is holding a parcel that arrived with no paperwork. Returns databases fail quietly like that. The schema decides how much of it you can answer with a query, and how much you have to reconstruct from old emails.

Start with a header and lines, not one flat table

Returns arrive in two shapes: whole-order and partial. Most retailers start with whole-order only, because that is what the first version of the returns form offers. Then a customer keeps one item out of three, and the schema has nowhere to put that fact. Build for partial returns from the beginning, even if the interface does not expose it yet.

A returns header holds the request itself: the RMA reference, the order it belongs to, the customer, the current status, the requested and received dates, the carrier, the tracking number and the warehouse handling it. Return lines hold each item: a reference to the original order line, the SKU, the quantity coming back, the condition, the reason and the disposition. One header, many lines. That single decision keeps stock, refunds and reporting consistent later.

Choose keys that survive the real world

Use a bigint identity column as the primary key and keep a separate human-facing reference for the RMA. Support staff read those references out loud, customers transpose digits, and warehouse labels get smudged. Keeping the internal key clean, and the public reference unique but not load-bearing, avoids a whole class of support tickets.

If returns can arrive from a website, a marketplace and a phone line, add source and external_reference columns with a unique constraint across the pair. Webhooks retry, and idempotency enforced by the database is cheaper than de-duplicating later.

Money belongs in integers. Store amounts in pence as bigint, or numeric(12,2) if you prefer decimals — never floating point. Keep a currency column even if every order is GBP today; one EU order will arrive eventually and you will be glad the field already exists. Capture the VAT rate and the gross and net split on the refund, because rates change and old refunds must still reconcile.

Treat status as data, not a text field

A varchar status column starts tidy and ends up containing "Refunded", "refunded", "REFUNDED " and "customer refunded" within a year. Constrain it. A lookup table with a foreign key is the most flexible option; a check constraint or a PostgreSQL enum is fine if the list rarely changes and you accept that adding values needs a migration.

A workable set for most UK retailers:

• requested — the customer has asked; nothing is approved.
• authorised — a label or instruction exists.
• in_transit — the parcel is on its way back.
• received — booked in at the warehouse.
• inspected — condition and contents checked.
• approved or rejected — the decision after inspection.
• refunded — money has gone out.
• closed — nothing further will happen.

Two rules matter more than the exact wording. First, store status explicitly rather than deriving it from timestamps; a generated status looks clever until a parcel arrives before its label was scanned. Second, keep a separate status_history table recording the previous status, the new status, the timestamp, the user or system that made the change, and an optional note. That table answers "how long do refunds take?" without anyone reading application logs.

Separate lookups for reason, condition and disposition

Reason codes should be their own table, with a code, a label for the interface, a category and a couple of flags — whether the item is normally refundable, whether a photo is required. The category is the useful part. Grouping reasons into change of mind, fit or sizing, damaged in transit, faulty and wrong item sent lets you report on root causes without parsing free text.

Condition and disposition deserve the same treatment. Condition describes what came back: unopened, opened and unused, used, damaged, missing parts. Disposition describes what happens next: restock, refurbish, return to supplier, scrap. Conflating the two is a common mistake and makes stock adjustments impossible to audit.

Give refunds their own table

One return does not always mean one refund. A retailer may refund the item value now and the original delivery charge later, split a refund between a gift card and a card, or deduct a restocking fee where the terms allow it. A refunds table holding the return, amount, method, gateway reference, timestamp and its own status keeps finance happy and turns partial refunds into a normal case rather than an exception.

A few UK-specific fields are worth adding now: the delivery date on the order, so you can calculate cancellation windows under the Consumer Contracts Regulations 2013, and the VAT rate captured at refund time. Keep those rules in application code rather than baking them into generated columns, since they change. If your returns policy touches consumer rights or tax, take professional advice rather than inferring obligations from a schema.

Plan for data protection too. Returns records hold names, addresses and order histories. Decide a retention period, add a flag such as anonymised_at, and write the routine that strips personal details while leaving anonymised analytics rows behind.

Index the queries you actually run

Every foreign key used in joins needs an index; PostgreSQL does not create them for you. Beyond that, index for the two workloads a returns system really has: the open work queue and month-end reporting.

1. A partial index on the open statuses — for example on created_at where status is requested, authorised or in transit. It stays small and fast because closed returns drop out of it.
2. A composite index on status and created_at for dashboards counting returns by state over a period.
3. An index on the line table covering SKU and disposition, for "how much of this product came back and what did we do with it".
4. A unique index on the RMA reference, and on the source plus external reference pair.

Then stop. Indexing every column slows writes and hides the real problem. Run EXPLAIN ANALYZE on your slowest query before adding the next one.

A practical order of work

Build in this sequence and the awkward questions tend to arrive while you can still answer them cheaply: headers and lines first, then the lookup tables for reason, condition and disposition, then status history, then refunds, then constraints. Add constraints as guardrails — quantity returned greater than zero, refund amount not negative, and the sum of returned quantities never exceeding the order line, enforced by a trigger or in application logic where a check constraint cannot see another table.

Only add indexes once you have real queries and real data volumes. If you are starting from a single flat returns table, migrate in stages: create the new tables, backfill from the old one, run both in parallel for a release, then cut over. And write the anonymisation job early. It is far easier to build from the start than to retrofit once the table is a gigabyte deep.

Photo: heladodementa / Pixabay