All Docs
FeaturesCalmony PayUpdated March 14, 2026

Database Schema

Database Schema

Calmony Pay uses Drizzle ORM with a PostgreSQL backend. All tables are prefixed with pay_ and scoped to a projectId resolved from the authenticating API key.

Tables

pay_projects

One record per organisation. Stores Cardstream and Griffin credentials. Credential columns (cardstreamSignatureKey, secretEncrypted) are encrypted at rest.

  • ID type: UUID

pay_api_keys

Holds publishable and secret API keys in both live and test environments.

  • ID type: UUID
  • Key formats: sk_live_, sk_test_, pk_live_, pk_test_
  • Storage: Only the SHA-256 hash (keyHash) is stored — the raw key is never persisted after generation.
  • Enum: pay_api_key_type (secret | publishable), pay_api_key_env (live | test)

pay_customers

Customer records including name, email, and billing address.

  • ID prefix: cus_
  • Soft-delete: deletedAt timestamp for GDPR right-to-erasure compliance.
  • Enum: pay_customer_status
  • Indexes: (projectId, status), (projectId, email)

pay_payment_methods

Stores non-sensitive card metadata and a Cardstream cross-reference token. Raw card numbers are never stored.

  • ID prefix: pm_
  • Stored fields: last4, expiry month/year, card brand, Cardstream token
  • Enum: pay_payment_method_type (currently card)
  • Indexes: (projectId, customerId)

pay_payment_intents

Represents a single payment attempt. Amounts are stored as integer pence (GBP minor units). The full Cardstream response object is stored as JSONB.

  • ID prefix: pi_
  • Amounts: Integer pence (e.g. 1050 = £10.50)
  • Enum: pay_payment_intent_status
  • Indexes: (projectId, status), (projectId, customerId)

pay_subscriptions

Recurring billing records. Supports trial periods, billing intervals, and cancel-at-period-end behaviour.

  • ID prefix: sub_
  • Enums: pay_subscription_status, pay_subscription_interval
  • Indexes: (projectId, status), (projectId, customerId)

pay_invoices

Invoices follow a draft → open → paid lifecycle. Line items are stored as a JSONB array. A PDF URL is stored once the invoice is finalised.

  • ID prefix: inv_
  • Invoice numbers: Sequential INV-YYYY-NNNN format, generated from pay_invoice_sequences
  • Enum: pay_invoice_status
  • Indexes: (projectId, status), (projectId, customerId)

pay_checkout_sessions

Hosted checkout sessions. Each session expires 24 hours after creation.

  • ID prefix: cs_
  • Modes: payment, subscription, setup
  • Enums: pay_checkout_session_status, pay_checkout_session_mode
  • Indexes: (projectId, status), expiry timestamp

pay_webhook_endpoints

Customer-registered URLs to receive event notifications. The HMAC signing secret is both hashed and encrypted.

  • ID prefix: we_
  • Security: Secret is hashed + encrypted; raw secret is not queryable after registration.
  • Enum: pay_webhook_endpoint_status
  • Indexes: (projectId, status)

pay_webhook_deliveries

Records every delivery attempt for a webhook event, including HTTP response status and retry count.

  • ID type: UUID
  • Indexes: (webhookEndpointId), (eventId)

pay_events

Immutable log of all platform events (e.g. payment_intent.succeeded, invoice.paid). Events are written once and never mutated.

  • ID prefix: evt_
  • Enum: pay_event_status
  • Indexes: (projectId, status)

pay_idempotency_keys

Caches API responses for 24 hours keyed on (projectId, Idempotency-Key) to allow safe request retries.

  • ID type: UUID
  • TTL: 24 hours (expiresAt)
  • Indexes: (projectId, key), expiry timestamp

pay_invoice_sequences

Per-project monotonic counter used to generate sequential INV-YYYY-NNNN invoice numbers. Primary key is projectId.

Enums Reference

EnumValues
pay_api_key_typesecret, publishable
pay_api_key_envlive, test
pay_customer_statusSee schema
pay_payment_method_typecard
pay_payment_intent_statusSee schema
pay_subscription_statusSee schema
pay_subscription_intervalSee schema
pay_invoice_statusdraft, open, paid
pay_checkout_session_statusSee schema
pay_checkout_session_modepayment, subscription, setup
pay_webhook_endpoint_statusSee schema
pay_event_statusSee schema

Database Commands

# Push schema changes to the database
npm run db:push

# Generate migration files
npm run db:generate

# Open Drizzle Studio (visual DB browser)
npm run db:studio

Security & Compliance Notes

  • No raw card numbers are stored at any point. Only last4, expiry month/year, and card brand are persisted.
  • API key hashing — raw secret keys are shown once at creation and then discarded. Only the SHA-256 keyHash is stored.
  • Credential encryption — Cardstream and webhook secret columns are marked for encryption at rest.
  • GDPR soft-delete — pay_customers.deletedAt supports right-to-erasure without immediate hard deletion of related records.