# Design This file fixes the parts that are easy to get wrong. Choose the language and framework that fit the person's project; nothing here depends on them. ## Data model Use the database in the `database` input. | Table | Columns | | --- | --- | | `customers` | `id`, `name`, `email`, `provider_customer_ref`, `created_at` | | `meters` | `id`, `name` (unique) | | `plans` | `id`, `name`, `base_fee_minor`, `created_at`, `archived_at` | | `plan_prices` | `plan_id`, `meter_id`, `unit_price_scaled`, `included_units`; primary key on both ids | | `subscriptions` | `id`, `customer_id`, `plan_id`, `starts_on`, `ends_on`, `next_plan_id` | | `usage_events` | `id`, `customer_id`, `meter_id`, `quantity`, `occurred_at`, `idempotency_key` (unique), `received_at` | | `invoices` | `id`, `number` (unique), `customer_id`, `subscription_id`, `period_start`, `status`, `total_minor`, `issued_at`, `paid_at`, `payment_ref`; unique on `subscription_id` and `period_start` | | `invoice_lines` | `id`, `invoice_id`, `kind`, `meter_id`, `units`, `amount_minor`, `description` | | `charge_attempts` | `id`, `invoice_id`, `attempted_at`, `outcome`, `provider_ref` (only for `payment_provider`) | ## Money rules - Never use floating point for money. - `base_fee_minor` and every amount on an invoice are whole minor units. - `unit_price_scaled` is the unit price in minor units times 10,000, as an integer. A price of 0.25 cents per unit is stored as 2,500. - Line amount = round(billable units × `unit_price_scaled` ÷ 10,000), halves away from zero. Do the multiplication in the application, not the database, with 128-bit or arbitrary-precision integers: at high volumes the product overflows 64 bits. ### Worked examples Use these as tests. The plan has a base fee of 2,900 minor units, a unit price of 0.25 minor units (2,500 scaled) and 10,000 included units. | Usage | Included usage | Billable units | Usage line | Invoice total | | --- | --- | --- | --- | --- | | 0 | on | 0 | no line | 2,900 | | 10,000 | on | 0 | no line | 2,900 | | 10,001 | on | 1 | 0 | 2,900 | | 10,002 | on | 2 | 1 | 2,901 | | 12,345 | on | 2,345 | 586 | 3,486 | | 12,345 | off | 12,345 | 3,086 | 5,986 | Check: 2 × 0.25 = 0.5, which rounds away from zero to 1. 2,345 × 0.25 = 586.25, which rounds to 586. 12,345 × 0.25 = 3,086.25, which rounds to 3,086. A usage line with zero billable units is left off the invoice. A line whose units are above zero but whose amount rounds to zero is kept, as in the third row. ## Invoice run Run once a day. For each subscription active at any time in the previous month, in one transaction: 1. Stop if an invoice exists for that subscription and period. The unique index is the real guard; the check only avoids noise. 2. Sum usage events for the period per meter. 3. Build the lines, compute the total, take the next invoice number and insert. 4. Apply `next_plan_id` to the subscription if one is set. Take the invoice number from a single-row counter locked inside the same transaction, so numbers have no gaps. Reject events for a period once any invoice for that customer and period exists. ## Collection Keep the provider behind one small interface with two operations: charge an invoice, and verify and parse a webhook. Tests use a fake. - Send the invoice id as the provider's idempotency key so a retried request cannot charge twice. - Verify the webhook signature with `PAYMENT_WEBHOOK_SECRET` before reading the body. - Handle each webhook event id once; store the ids you have handled. - Move an invoice from `open` to `paid` only inside the webhook handler. Statuses: `open`, `paid`, `uncollectible`. ## Configuration | Secret | Purpose | | --- | --- | | `DATABASE_URL` | Connection string for the chosen database. | | `INGEST_API_KEY` | Authenticates usage events from the product. | | `PAYMENT_PROVIDER_KEY` | Charges invoices. Only for `payment_provider`. | | `PAYMENT_WEBHOOK_SECRET` | Verifies provider webhooks. Only for `payment_provider`. | ## Named here - The keys of an ingest body are `customer_id`, `meter`, `quantity`, `occurred_at` and `idempotency_key`. - The retries are 3, 5 and 7 days after the invoice was issued. A fourth failure marks it uncollectible. - A plan is in use when any subscription has it as its plan or its next plan. ## What this spec leaves to you Where this spec names something, use that name, so that two builds of it agree. Where it does not, the choice is yours. List what you chose in the README.