A complete subscription box service database schema for businesses like Birchbox, HelloFresh, or Loot Crate. Includes subscriber management with flexible recurring plans, curated box assembly with individual item tracking, shipment lifecycle management, and subscription lifecycle handling with pause/cancel support. Designed for subscription commerce platforms that ship physical goods on a recurring schedule.
boxes is what makes this different from an ordinary subscription. Each billing period produces a box row with its own period and contents, so the subscription is the agreement and the box is the thing that was actually curated, packed and shipped — and a subscriber's history is a list of boxes rather than a list of charges.
subscription_pauses is a table rather than a status, because a pause has a shape: a reason, when it started, when it is due to resume and when it actually did. Recording it as a status value would answer whether someone is paused and nothing about how often or for how long.
box_items copies retail_value from the product. The appeal of a box is that its contents are worth more than the price paid, and that claim has to stay true for a box shipped last March even after the products have been repriced.
A subscription with a payment and a shipment per period would work and would lose the curation. The box has a name, a period, a total value and contents chosen for that month, and the subscriber's relationship is with those boxes. Creating it explicitly is also what lets a box be assembled and reviewed before it ships, which a payment-driven model cannot express.
A paused status on the subscription is one column and answers one question. A pause table records why, how long and whether the resume actually happened, which is what turns churn analysis into something answerable — a pause that never resumes is a cancellation nobody labelled. The cost is that the current state has to consider both the subscription status and any open pause.
Reading retail_price from the product would restate the value of every past box when a product's price changes, and would break entirely when a product is retired. Freezing it per item keeps the box's stated value permanently true, which matters because that number is the marketing claim the subscriber bought on.
They are separate events that fail separately. A card can decline while the box has already been packed, and a box can ship while a payment is still settling. Attaching both to the subscription would conflate them; attaching both to the box would leave a failed payment with nowhere to live when no box was created.