Introducing the Calmony Pay Database Schema

With v1.0.0 we've laid the foundation for everything Calmony Pay will do: a complete, production-ready database schema built with Drizzle ORM. This post walks through the key design decisions and what each table is responsible for.

The tables

Nine tables cover the full surface area of a Stripe-compatible payment API.

customers

The top-level entity for anyone being billed. Every customer belongs to a project and can have multiple payment methods, subscriptions, and invoices attached.

payment_methods

Stores tokenised card and payment method data associated with a customer. These are referenced by payment intents and subscriptions to execute charges without re-collecting card details.

payment_intents

Represents a single attempt to collect a payment. A payment intent moves through a defined lifecycle (e.g. requires_payment_method → processing → succeeded or failed), giving a clear audit trail for every transaction.

subscriptions

Manages recurring billing — linking a customer to a plan with a defined billing interval. Subscriptions drive the automatic creation of invoices and payment intents on each renewal.

invoices

Invoices are generated for both one-off charges and subscription renewals. Each invoice references the customer and, where applicable, the subscription that triggered it.

api_keys

API keys are the entry point for all API access. Each key is bound to a projectId, which is used to scope every subsequent database query — ensuring complete data isolation between projects.

webhook_endpoints

Projects register URLs here to receive real-time event notifications. When something noteworthy happens (a payment succeeds, a subscription is cancelled), Calmony Pay delivers a signed payload to the registered endpoint.

events

An immutable append-only log of everything that happens within a project. Events are the source of truth for webhook delivery and can be replayed or inspected at any time.

idempotency_keys

Prevents duplicate operations caused by retried requests. When a client submits a request with an Idempotency-Key header, the key and response are stored here so subsequent retries return the original result without re-executing the operation.

---

Key design principles

Stripe-compatible ID prefixes

Every resource ID carries a type prefix — cus_, pm_, pi_, sub_, inv_, cs_, evt_ — mirroring the Stripe convention. This makes IDs self-describing: if you see pi_ in a log, you know immediately you're looking at a payment intent.

Integer pence for money

All monetary amounts are stored as integers representing pence. £9.99 is stored as 999. This avoids the well-known pitfalls of floating-point arithmetic and makes arithmetic in application code predictable.

Multi-tenancy via projectId

Calmony Pay is built to serve multiple SaaS products from a single deployment. Every table includes a projectId column, and the value is resolved automatically from the authenticating API key on every request. Application code never needs to manually thread a tenant identifier — it's enforced at the schema level.

Composite indexes for performance

All main tables carry a composite index on (projectId, status). The most common query pattern — "show me all pending payment intents for this project" — is covered without a full table scan.

---

What's next

With the schema in place, the next milestone is the API layer: authentication middleware, REST endpoints for each resource, and Cardstream integration for payment processing.