Skip to content
Flows
Explore Library Paths Vault Generate Pricing Progress Run Demo Flow Oort — prompt library
App Builder Generated Intermediate · 2-3 hours

Add Usage‑Based Metered Billing

Integrate real usage tracking, billing calculations, and Stripe metered billing into the existing app.

Start Route · 6 steps

The route

6 steps to Done

  1. 01

    Define billing and usage data model

    Create persistent tables to store usage events, rates, and invoices.

    Preview prompt + verify gate ▾

    Write a database migration that adds three new tables: 1. `usage_events` with columns: `id PK`, `user_id FK -> users.id`, `event_type VARCHAR`, `quantity INT`, `recorded_at TIMESTAMP`. 2. `rate_plans` with columns: `id PK`, `name VARCHAR`, `unit_price_cents INT`, `tier_start INT`, `tier_end INT` (allow null for unlimited). 3. `invoices` with columns: `id PK`, `user_id FK`, `period_start DATE`, `period_end DATE`, `total_cents INT`, `status VARCHAR` (pending/paid), `created_at TIMESTAMP`. Make the migration idempotent, add foreign key constraints, and generate the corresponding ORM models. Finally, run the migration against a local dev DB and verify tables exist.

    • ✓ Can you query `SELECT column_name FROM information_schema.columns WHERE table_name='usage_events'` and see all columns?
    • ✓ Do ORM model files contain definitions for UsageEvent, RatePlan, and Invoice?
    • ✓ Was the migration applied without errors?
  2. 02

    Implement usage logging middleware

    Automatically record each billable action performed by a user.

    Preview prompt + verify gate ▾

    Create a middleware function `logUsage(eventType, quantity)` that: - Takes `eventType` (string) and optional `quantity` (default 1). - Reads `req.user.id` for the current user. - Inserts a row into `usage_events` with the user ID, event type, quantity, and current timestamp. - Calls `next()` after insertion. Export the middleware and demonstrate usage by attaching it to two existing routes (e.g., `/api/upload` and `/api/export`). Ensure errors are caught and do not break the request flow. After adding the middleware, run the server and perform a request to each route; then query the DB to confirm rows were inserted. Provide the full middleware file and updated route definitions.

    • ✓ After hitting `/api/upload`, does a `usage_events` row with `event_type='upload'` exist?
    • ✓ After hitting `/api/export`, does a row with `event_type='export'` exist?
    • ✓ Does the middleware file contain real DB insertion code (no stubs)?
  3. 03

    Build metered billing calculation service

    Aggregate usage per billing period and generate invoice records.

    Preview prompt + verify gate ▾

    Create a Node.js service module `billingService.js` exposing a function `generateMonthlyInvoices(date)` that: 1. Determines the period start and end for the month containing `date`. 2. For each user, sums `quantity` of `usage_events` grouped by `event_type` within the period. 3. Retrieves applicable rate(s) from `rate_plans` (support tiered pricing: if `tier_start`/`tier_end` defined, apply respective `unit_price_cents`). 4. Calculates `total_cents` = sum of (usage quantity * unit price) across all events. 5. Inserts a row into `invoices` with `user_id`, `period_start`, `period_end`, `total_cents`, `status='pending'`. 6. Returns an array of generated invoice IDs. Include proper error handling, transactions to ensure atomicity, and unit tests that insert sample usage and rate data, then verify the invoice total matches expected calculation. Do not use mock DB; run against the real dev DB.

    • ✓ After inserting test usage and rates, does `generateMonthlyInvoices` create an invoice with the expected `total_cents`?
    • ✓ Are invoice rows persisted in the real `invoices` table?
    • ✓ Did the service run inside a DB transaction without errors?
  4. 04

    Integrate Stripe metered usage and webhook handling

    Connect invoices to Stripe, submit usage records, and capture payments.

    Preview prompt + verify gate ▾

    Using the Stripe Node SDK: 1. In a setup script, create a Stripe Product named "App Usage" and a Price with `unit_amount` = 0, `currency` = 'usd', `recurring` = `{interval: 'month', usage_type: 'metered'}`. Store the `price.id` in a config file. 2. Extend the billing service so that after inserting an invoice with status 'pending', it calls `stripe.usageRecords.create` for the subscription associated with the user, passing `quantity` = total usage units for the period and `timestamp` = period end. 3. Set up an Express webhook endpoint `/webhooks/stripe` that verifies the signature, listens for `invoice.payment_succeeded` and `invoice.payment_failed` events, and updates the corresponding `invoices.status` in the DB to 'paid' or 'failed'. 4. Ensure idempotency: webhook handling must be safe to retry. Run the server, trigger a test usage period, and confirm via the Stripe dashboard that a usage record appears and an invoice is generated and marked paid when a test payment method is attached. Provide the full setup script, updated billing service code, and webhook handler. All code must execute real Stripe API calls (use Stripe test mode).

    • ✓ Does the Stripe dashboard (test mode) show the "App Usage" product and a metered price?
    • ✓ After a billing run, is there a usage record for the user in Stripe?
    • ✓ Did the webhook change the corresponding invoice row to `status='paid'`?
  5. 05

    Expose admin API endpoints for usage & invoices

    Allow authorized staff to view per‑user usage summaries and invoice histories.

    Preview prompt + verify gate ▾

    Create two new Express routes protected by admin middleware: 1. `GET /admin/usage/:userId` – queries `usage_events` for the given user, groups by `event_type`, sums `quantity`, and returns JSON `{event_type, total_quantity}`. 2. `GET /admin/invoices/:userId` – fetches all rows from `invoices` for the user, returning fields `period_start`, `period_end`, `total_cents`, `status`. Both routes must verify `req.user.role === 'admin'` and return 403 otherwise. Use the real ORM/DB client, no mock data. Test by issuing requests with an admin JWT and confirming the JSON reflects actual DB contents.

    • ✓ Does `/admin/usage/:userId` return aggregated usage matching the DB rows?
    • ✓ Does `/admin/invoices/:userId` list all invoices with correct totals and statuses?
    • ✓ Is a non‑admin request rejected with 403?
  6. 06

    Write end‑to‑end test and verify full billing flow

    Automate validation that usage, invoice creation, Stripe submission, and payment all work together.

    Preview prompt + verify gate ▾

    Add a test file `billing.e2e.test.js` that: 1. Registers a new test user and logs in to obtain a JWT and a Stripe test customer/subscription linked to the metered price. 2. Performs three billable API calls (`/api/upload`, `/api/export`, `/api/upload`). 3. Calls the billing service’s `generateMonthlyInvoices` for the current month. 4. Waits for Stripe webhook processing (poll the DB for invoice status `paid`). 5. Asserts that: - `usage_events` contains three rows with correct `event_type`. - An `invoices` row exists with `total_cents` equal to `quantity * unit_price_cents`. - The Stripe dashboard (via Stripe SDK) shows a paid invoice for the test customer. Run the test against the local dev server and ensure it passes without any mocked data. Include any necessary setup/teardown to clean the DB after the test.

    • ✓ Did the test create real usage rows in the DB?
    • ✓ Did the test generate an invoice with the correct total?
    • ✓ Did the Stripe test dashboard show a paid invoice for the test customer?