Design a daily-essentials subscription service

I would ask: how far ahead is the order cut-off (say, 10 pm for next morning)? Is payment prepaid from a wallet, or charged per delivery or per week? Can a customer change or skip after the cut-off? Is delivery per neighbourhood hub with its own stock? How many different schedule patterns exist? Which time zone rules apply? What happens when delivery fails?

I would assume one country and one time zone, a prepaid wallet topped up through an external payment provider, a 10 pm cut-off, hub-based fulfilment, and 2 million active subscriptions. In scope: subscription rules, daily order generation, wallet charging, pause, skip, notifications. Out of scope: catalogue search, route planning for riders and supplier procurement.

Planning figures for the exercise, not facts about any company.

• Daily orders: not every subscription is due every day. Assume 1.5 million deliveries on a typical day.
• Generation window: if the nightly job runs for two hours after the cut-off, 1,500,000 ÷ 7,200 = about 208 orders a second. Each order needs about 3 writes (order, wallet debit, outbox), about 625 writes a second. That is far under 5,000 committed writes a second.
• Storage: 1.5 million orders × 200 B = 300 MB a day; 90 days hot is 27 GB. Subscriptions: 2 million × 300 B = 600 MB. All of it fits on one primary.
• Charging cadence: a card charge per delivery would be 1.5 million provider calls a day. Weekly top-ups cut that to 2,000,000 ÷ 7 = about 286,000 a day, roughly 3 a second, and fees and failures drop with it.
• App reads: if 500,000 customers open the app in the morning window, even at 2,000 reads a second a cache is optional.

The numbers say a single relational primary plus a partitioned batch job is enough. The hard parts are the schedule semantics, the cut-off, and not double-charging.

Components. A subscription service (rules and overrides), an order generator (a batch job per partition), a wallet and ledger service, an order and delivery service for hub fulfilment, and a notification service fed through a queue.

Data model.

• subscriptions(sub_id, customer_id, sku, qty, schedule, start_date, end_date, status, paused_until, hub_id, version). The schedule is a rule (a weekday bitmask, or "every N days from start"), not a list of future dates. A calendar of every future delivery per subscription would be endless.
• overrides(sub_id, date, action, qty) with primary key (sub_id, date): a skip, an extra, or a one-day quantity change. Pause is simply paused_until.
• orders(order_id, sub_id, delivery_date, status, amount) with a unique index on (sub_id, delivery_date).
• Wallet entries in a ledger, with a unique key of (order_id, kind) so a debit or refund happens once.

Order generation (the core loop). At the cut-off, a scheduler splits subscriptions into, say, 64 partitions by hash of sub_id and enqueues one job per partition. A job reads active subscriptions due tomorrow, applies overrides, and for each inserts the order with an insert-if-absent on the unique index. A crashed or duplicated job is therefore harmless: re-running it creates nothing new. Delivery dates are local calendar dates, not instants, so time zones and daylight saving cannot move a delivery.

Charging. When an order is generated, debit the wallet in the same transaction (a reservation; at this size the wallet tables share the primary). If the balance is short, do not create the order; mark the subscription "payment needed" and notify. Money added through the payment provider uses idempotency keys and webhook de-duplication as in the payments walkthrough. If a delivery fails, post a reversing entry and refund to the wallet; do not edit the original debit.

Pause, skip, change. These write to subscriptions or overrides with a version check, so two taps cannot interleave. Before the cut-off they simply change what the generator sees. Between the cut-off and the hub's pick-list lock, a skip cancels the generated order and refunds it. After the lock, changes apply from the next day, and the app says so.

Fulfilment. At the pick-list lock, group tomorrow's orders by hub and SKU and send totals to the hub and supplier. Because orders are materialised, the totals are exact.

Notifications. The order service writes an outbox row on every state change. Workers consume it for reminders (low balance the evening before), confirmations of skips, and delivery updates, with a de-duplication key per notification as in the notification walkthrough.

Failure handling. Monitor generated count against expected count per partition before the cut-off plus one hour; page if any partition is missing. Re-running a missing partition is safe by construction.

• Nightly materialisation vs per-subscription timers. A nightly batch is easy to reason about, easy to re-run and gives exact hub totals. Timers per subscription spread load evenly but multiply moving parts. Pick the batch; flip to a delay queue if schedules need minute-level times or rolling cut-offs per customer.
• Rules plus overrides vs pre-expanded calendar rows. Rules stay small and editing is one row. Pre-expansion makes queries trivial but explodes storage and edits. Pick rules; flip to expanding 14 days ahead if holiday calendars and schedule exceptions get complicated.
• Debit at generation vs at delivery. Debiting early prevents delivering unpaid goods but needs refunds for failures. Debiting at delivery is gentler on customers but exposes you to unpaid orders. Pick early; flip to delivery-time charging for trusted customers with a card on file.
• Prepaid wallet vs charge per cycle. A wallet turns thousands of tiny charges into occasional top-ups. Flip to card-on-file billing where customers dislike prepaying and rules for recurring card charges in your market allow it; check the current regulations.

• Creating a cron timer or a row per subscription per future day. It is slow to generate and miserable to edit.
• No unique key on (subscription, date), so a rerun double-orders.
• Storing delivery times as UTC instants, which drift across a date boundary.
• Charging a card for every delivery and ignoring fees and failures.
• Ignoring the cut-off: letting edits race with generation and the pick list.
• Deleting the wallet debit on a failed delivery instead of posting a reversal.
• Designing for 2 million concurrent users instead of a predictable nightly batch.
• Having no answer for what the customer sees when the wallet is short the evening before a delivery.