TL;DR

  • You buy a lot. You sell units. That asymmetry is the whole data-modelling problem, and spreadsheets lose to it immediately.
  • The spine is four tables: purchase_lot → inventory_item → listing → sale, with join tables where the relationships are genuinely many-to-many.
  • Cost gets allocated from the lot down to units, and the allocation locks once the first unit sells. Retroactively re-allocating cost after a sale is how you accidentally rewrite last quarter’s profit.
  • A unit walks a status machine: sourced → processing → drafted → listed → sold → shipped → reconciled, with dead and personal as the two honest escape hatches.
  • Selling quantity-N on one listing is N sales, not one sale with a quantity column. I modelled it the wrong way first and spent August unpicking it.
  • Packing several units into one sellable bundle, and splitting a bundle back into units, both need to conserve cost exactly. Getting that wrong is silent and it shows up as fake margin.

The mismatch

Here’s the transaction that starts everything: I pay $340 for a pallet described as Assorted Networking and Computer Equipment. One line item, one payment, one date.

Here’s what leaves: eleven desktops sold individually over four months, a bundle of six identical power supplies sold as one listing, three units cannibalised for parts, two things I kept, and a monitor that turned out to be broken and went to recycling.

Every one of those exits needs to know what it cost, because profit per unit is the only number that tells you whether the pallet was a good buy. And the pallet didn’t come with a per-unit price. Nothing does.

That’s it. That’s the problem. Everything below is consequences.

The spine

purchase_lot ──┬── inventory_item ──┬── listing_item ── listing
               │        │           │
               │        │           └── sale_item ── sale ── sale_fee
               │        └── (parent_item_id → itself)
               └── expense

purchase_lot is the transaction: source, the source’s own lot reference, gross cost, and the buyer’s premium for that lot — because premiums vary per auction, per source, sometimes per lot within a source, and hard-coding one rate quietly poisons every downstream number.

inventory_item is a physical thing I can sell. It carries a SKU, a title, category and condition, a location_bin so I can find it on a shelf, an allocated_cost, an estimated_fmv, and a parent_item_id pointing at itself — which is how bundles and parts-outs work without a second table.

listing is a marketplace entry, and listing_item is a join table, because one listing can cover several units (a bundle) and one unit can appear across several listings over its life (relisted, moved to a different channel, rolled into a different bundle).

sale is one buyer’s order, and sale_item links the exact units that went out in it. sale_fee is a separate table, because a single sale attracts several fees with different rates — final value, insertion, promoted-ad, store subscription — and lumping them into one fees column means you can never answer “what are promoted ads actually costing me.”

Thirty-odd tables in total once you add tags, photos, repair tickets, parts, labour, mileage, and accounting periods. But those four are the spine, and if you get them wrong nothing above them can be right.

Allocation, and why it locks

The lot cost $340. Distributing that across the units is allocation, and it’s a judgement call, not a formula. Equal split is wrong when the pallet is one server and nine keyboards. Splitting by estimated resale value is defensible and it’s what I mostly do. Splitting by weight is occasionally right for genuinely fungible goods.

Whatever you choose, one rule matters more than the choice:

Once the first unit from a lot sells, the allocation locks.

There’s an allocation_locked flag on the item for exactly this. Before it flips, re-allocating is free and I do it constantly as I find out what things really are. After it flips, re-allocation would change the cost basis of a sale that has already happened — which means it changes reported profit for a period that’s already closed, and possibly already used to file something.

I learned this the way everyone does. I refined an allocation in month three, and a sale from month one silently changed its margin. Nothing errored. The number was just different than it had been, and I had no idea which of the two was right.

The status machine

Every unit has exactly one status:

sourced → processing → drafted → listed → sold → shipped → reconciled
                                                   ↘
                                            dead │ personal
  • sourced — I own it, it’s on a shelf, nothing has happened.
  • processing — being repaired, tested, wiped, reimaged. The bench stage.
  • drafted — a marketplace draft exists but isn’t public. This state exists purely because publishing is a manual gate and drafts pile up.
  • listed — publicly for sale.
  • sold → shipped → reconciled. The last one means the marketplace’s own payout record has been matched against my sale. Until then the money is theoretical.

Then the two that keep the model honest:

dead — gone, with cost zeroed and a parent pointer. Parted out, recycled, absorbed into a bundle. Without an explicit dead state you get zombie units: rows that will never sell, sitting in your on-hand inventory forever, inflating what you think you’re holding.

personal — I kept it. Distinct from dead because the cost is real but it’s not a business loss, and pretending otherwise is either bad bookkeeping or slightly worse than that.

The states carry timestamps (sourced_date, processed_date, listed_date, sold_date, shipped_date), which is where the genuinely useful metric lives: days from sourced to sold, per category. That’s the number that tells you which categories are quietly eating your working capital. Not margin — velocity. A 60% margin that takes nine months to realise is worse than 30% in three weeks, and until I had those timestamps I couldn’t see it.

The quantity-N mistake

Here’s the one that cost me a fortnight.

I have six identical bench instruments. I list them as one listing with quantity six. Four different buyers buy them: one takes two, one takes one, one takes two, one takes one. Four orders.

I originally modelled that as a sale with a quantity column. Two sales, actually, because that’s how I’d stitched it together — and it was wrong in a way that isn’t obvious until you try to answer a simple question: which specific units left, and what did each one cost me?

With a quantity column, you can’t answer it. The cost is an average, the units aren’t identified, and if the six had different allocated costs (they did — two came from a different lot) your margin is fiction.

The fix is that sale_item is one row per unit per order, and each row freezes cost_at_sale at the moment of sale. Six units across four orders is four sale rows and six sale_item rows. Cost is per-unit and immutable once written.

Migrating to it took three passes: identify the affected listings, work out which physical units actually went in which order (a delightful evening with four months of shipping records), then backfill with an assertion that total cost was conserved before and after. That assertion caught two mistakes I’d have otherwise shipped.

The general lesson, which applies well beyond resale: a quantity column on a transaction table is a smell whenever the things being counted are individually identifiable. If they have serial numbers, they need rows.

Packing and splitting

Two operations that look symmetric and aren’t.

Packing takes N units and makes one sellable bundle: six power supplies, one listing, one shipment. The bundle becomes a new item whose allocated_cost is the sum of its contents’ costs, and the contents go dead with their cost zeroed and parent_item_id set to the bundle.

The rule that keeps this honest: no multiplication anywhere. The bundle’s cost is the true landed cost of what’s inside it, not a unit cost times a count. Every time I’ve seen this modelled with a multiplier, rounding drift shows up within a month and the books stop tying out.

Splitting goes the other way: a bundle that didn’t sell becomes N loose units. Crucially, the split creates new child rows — it does not revive the originals. Reviving them sounds tidier and it isn’t: those rows are referenced by old listings and old events, and resurrecting a dead row silently changes the meaning of that history.

One genuinely nasty edge: SKUs. If a bundle’s SKU is derived from its contents with a numeric suffix, and you kill the parent while its children come back as new rows, you can end up with a dead parent whose SKU could be claimed by a future unit — a phantom. Renaming the dead parent to a non-numeric suffix on split (-SPLIT) is an ugly little fix for a bug that took hours to understand.

Guards that earn their place

Three assertions live in the write path, and each of them exists because something went wrong once.

Quantity shape. When recording a listing, the number of linked unit rows must match the listing’s quantity. Refuses the write otherwise. This is the guard that would have prevented the quantity-N mess in the first place.

Orphan detection. A scheduled check looks for dead units still attached to open listings. That combination means I’m advertising something that no longer exists, which ends in a cancelled order and a defect on the account.

Allocation lock. Already covered, but it belongs on this list: reject any allocation change to a lot that has a completed sale.

None of these are clever. They’re all one-line invariants that a spreadsheet cannot express, and that’s really the argument for having a database here at all.

Gotchas

Freeze cost at sale, always. Not “look up the item’s cost when you need it.” The item’s cost can change; the sale’s cost cannot. One column, cost_at_sale, written once.

Fees are per-fee rows, not a column. Rates differ by category and by promotion, they change when the marketplace feels like it, and a single blended number destroys your ability to notice.

Buyer’s premium belongs on the lot, not in config. It varies. A global constant is wrong for most lots and you won’t feel it, because it’s wrong in the same direction every time.

Model personal explicitly. The stuff you keep is real cost. Hiding it in dead understates what the hobby costs you; leaving it in sourced means your on-hand inventory report is permanently wrong.

Backfills need a conservation assertion. Total cost before must equal total cost after. Write that check first, then write the migration. Mine caught two errors, either of which would have looked like profit.

Where this sits

Four spine tables, one status machine, three write-path guards, and a migration I’d rather not do twice. The system now answers the questions I actually care about — margin per unit, days-to-sale per category, which lots were mistakes — and it answers them the same way twice, which the spreadsheet never did.

Earlier in this series: the sourcing funnel that decides what to buy in the first place. Next: getting the units back out the door, and the automation around listings, labels, and paperwork.