Data Model

This page documents the dedicated mvs-pay extensions database — the PostgreSQL schema owned by the @mvs/mvs-pay package (packages/mvs-pay/prisma/schema.prisma) and reached through the MVS_PAY_DATABASE_URL connection string. It is the only relational store MVS Pay owns directly; everything payment-canonical lives in Hyperswitch.

The sidecar extension pattern

MVS Pay runs two separate databases for the payment domain:

1. Hyperswitch’s own Postgres — canonical payment objects. We do not write to it directly; we go through the Hyperswitch API (surfaced via apps/mvs-pay-extensions and @mvs/mvs-pay-sdk).
2. The mvs-pay extensions DB (this page) — its own Neon project, separate from the auth and control-plane databases (see ADR-004: Neon, not RDS).

Because they are physically separate databases, no foreign key can cross from an extension row into a Hyperswitch row. Instead, extension tables store the relevant Hyperswitch ID as an opaque String column (for example hyperswitchPaymentIntentId, hyperswitchMerchantConnectorId, hyperswitchPayoutId) and the application layer joins the two worlds at read time by calling the SDK. This is the “sidecar” pattern: each MVS row rides alongside a Hyperswitch entity, attached by ID only.

ADR-008: MerchantConnectorExtension sidecar table records the canonical decision for this pattern at the per-MCA level: rather than a JSON blob hanging off MerchantExtension, per-merchant-connector metadata gets its own table keyed by hyperswitch_merchant_connector_id so the fields are indexable and delete cleanly. The same reasoning generalises to every *Extension model.

Multi-tenancy and the client

Every table carries an organizationId (the column is even present, but nullable, on WebhookDeliveryAttempt, where the org may be unknown until the payload is parsed). Tenant isolation is row-level, enforced in application code — there is no connection-level routing. The client factory in packages/mvs-pay/src/client.ts returns a single shared PrismaClient for all tenants:

1
// packages/mvs-pay/src/client.ts
2
const db = getMvsPayDb(organizationId);
3
// The argument is a typed reminder, NOT a connection selector.
4
// You MUST still pass `where: { organizationId }` on every query.
5
const devices = await db.device.findMany({ where: { organizationId } });

The client uses @prisma/adapter-pg (PrismaPg) over MVS_PAY_DATABASE_URL and throws a structured MissingMvsPayDsnError (code: "MVS_PAY_DSN_MISSING") if the DSN is unset. The generated client is emitted to node_modules/@prisma/mvs-pay-client, separate from any other Prisma client in the monorepo.

Model inventory

The schema currently defines 31 models and 14 enums. The root container is PaymentOrganization, which mirrors the control-plane PlatformOrganization by ID and holds the org’s hyperswitchMerchantId / hyperswitchProfileId linkage; every other table cascades from it.

The models group into the following domains.

Merchant & connector extensions

Per-merchant and per-connector MVS configuration, keyed by Hyperswitch merchant_id / merchant_connector_id.

Transfer, settlement & dispute extensions

Per-payment, per-payout, and per-dispute MVS metadata and workflow.

IIAS substantiation

IiasSubstantiation is one of the few models with a true in-DB foreign key — it has a unique 1:1 relation to TransferExtension (both live in this database). See the IIAS substantiation runbook.

Payouts & Plaid

Instant-payout and bank-linking subsystems. Hyperswitch’s payouts module is the settlement primitive; these tables hold the MVS-side request workflow and bank/card metadata.

Devices

Billing, subscriptions, products & invoices

A custom, Stripe-style billing platform. Hyperswitch’s mandate model is recurring-only; MVS needs full products/prices/invoices for healthcare-platform billing, so this is built MVS-side and linked to Hyperswitch mandates/payment-intents by ID where relevant.

The Revenue Recovery webhook fields on BillingSubscription are updated by routes/webhooks-revenue-recovery.ts; see ADR-011: Revenue Recovery.

Webhooks, idempotency & audit (platform plumbing)

Other MVS-native subsystems

Conventions you will see everywhere

• hyperswitch*Id columns — opaque foreign references into Hyperswitch, never enforced by Postgres. Usually @unique on the owning sidecar.
• tenant*Id columns (tenantPatientId, tenantLocationId, tenantUserId, tenantInvoiceId, tenantPurchaseId) — opaque links back to the consuming app (HealthOS, etc.). Also no FK; they are indexed where queried.
• Money is integer cents. Int columns hold cents (e.g. surchargeAmount, total). The one exception is MerchantExtension.defaultSurchargePercent, a Decimal(5,4) (e.g. 0.0350 = 3.5%).
• Soft deletes via isDeleted / deletedAt on TransferExtension, DisputeExtension, BillableItem, and PlaidItem. Filter on isDeleted in reads; these are indexed.
• Enums are prefixed Payment* or Billing* (e.g. PaymentRemediationStatus, BillingSubscriptionStatus). Note several state/status fields are plain String, not enums — SettlementExtension.approvalStatus, InstantPayoutRequest.status, PaymentLinkExtension.state, Device.status, WebhookDeliveryAttempt.status, and ManualPayment.status use string literals with the allowed values documented only in schema comments. Treat those as application-validated, not DB-validated.

Migrations

Migrations live in packages/mvs-pay/prisma/migrations/ and follow a forward-only, squashed-baseline policy.

1
# packages/mvs-pay/prisma/migrations/migration_lock.toml
2
provider = "postgresql"

Honest gaps & caveats

• Stale README count. packages/mvs-pay/README.md says “30 models”; the schema has 31 (PaymentLinkExtension was added later). Several other guides have quoted “~44 models” — that figure does not match the current schema and should be read as an over-estimate; the authoritative count is 31 models / 14 enums in schema.prisma.
• No cross-DB integrity. As above, orphaned sidecars are possible and are not prevented at the database layer.
• String state fields. Workflow states stored as String (settlement approval, payout request, payment-link state, device status, DLQ status) rely entirely on application validation; a bad write will not be rejected by Postgres.
• PlaidItem.accessTokenSecretName is legacy. It coexists with the newer encrypted-column path (accessTokenEnc); a row may resolve through either, which is a migration in progress, not a finished state.
• AdminAuditLog is not tenant-scoped. It has no organizationId column, so tenant filtering of the audit log must be done by entityId / userId, not by org.

Related reading