Subscription Billing Data Layer

Problem/Feature Description

A SaaS company needs the data access layer for their subscription billing system. The backend is TypeScript with Drizzle ORM and SQLite. The system manages customers, subscription plans, and invoices.

Customers subscribe to plans (basic, pro, enterprise -- each with a monthly price). When a billing cycle runs, the system needs to:

1. Create an invoice for each active subscription -- this creates an invoice record linked to the customer and plan, with the amount matching the plan price, and marks it as "pending". All invoices for a billing cycle must be created together.
2. Process a payment -- marks an invoice as "paid" and records the payment timestamp.
3. Cancel a subscription -- changes the subscription status to "cancelled" and generates a final prorated invoice in a single operation.
4. Customer billing summary -- retrieves a customer with their active subscription (including plan details) and all their invoices sorted by date.

The engineering team wants properly typed function signatures that match the schema types without manual duplication.

Output Specification

Produce:

• src/db/schema.ts -- Full Drizzle schema for plans, customers, subscriptions, and invoices
• src/db/billing.ts -- The four operations as exported functions
• src/db/db.ts -- Database connection setup

The following starter file is provided:

=============== FILE: package.json =============== { "name": "billing-service", "version": "1.0.0", "dependencies": { "drizzle-orm": "^0.30.0", "better-sqlite3": "^9.0.0" }, "devDependencies": { "drizzle-kit": "^0.21.0", "typescript": "^5.0.0" } }