Skip to content

database.md

Latest commit

 

History

History
165 lines (130 loc) · 10.6 KB

database.md

File metadata and controls

165 lines (130 loc) · 10.6 KB
Raw

Database

PostgreSQL on Neon, accessed through Prisma 7 (prisma / @prisma/client 7.10) with the @prisma/adapter-pg driver adapter. Everything lives in packages/database; Prisma types do not leave that package (Database.client is for repository implementations only).

Prisma 7 setup

  • packages/database/prisma.config.ts holds the datasource URL, schema path, migrations path and seed command. schema.prisma has no url. Prisma 7 does not read .env, so the config loads the repo-root .env itself.
  • Generator is prisma-client (not the legacy prisma-client-js), emitting ESM TypeScript to src/generated/prisma (gitignored). It is compiled by tsc with the rest of the package. The generate Turbo task runs before build, typecheck, lint, dev and test.
  • Runtime uses the driver adapter: new PrismaClient({ adapter: new PrismaPg(...) }).
  • prisma validate, prisma generate and migrate diff work without a real database. Only migrate deploy/dev/status and db seed need credentials.

Connections (Neon)

Variable Used by Value
DATABASE_URL API, seed Pooled connection (host contains -pooler)
DATABASE_DIRECT_URL Prisma CLI only Direct connection; falls back to DATABASE_URL

The pool size (10), connect timeout (10 s) and transaction timeout (5 s) are fixed in packages/config/src/settings.ts.

Put them in the repo-root .env (copy .env.example). Use sslmode=verify-full in the URLs.

DATABASE_URL is required: the API fails at startup with a validation error if it is missing. Tooling (generate, validate, build, lint, typecheck, unit tests) does not need it.

Client lifecycle: createDatabase(config) returns an independent { client, ping, close }. getSharedDatabase(config) memoises one instance on globalThis for dev servers and workers that reload modules. The API owns its instance through DatabaseModule and closes it on shutdown.

Commands

pnpm db:generate         # generate the client
pnpm db:validate         # validate schema.prisma
pnpm db:migrate          # prisma migrate dev (create/apply migrations locally; needs a shadow DB)
pnpm db:migrate:deploy   # apply committed migrations (CI / production / Neon)
pnpm db:migrate:status
pnpm db:seed             # idempotent foundation seed
pnpm db:studio

Create new migrations with pnpm db:migrate against a development database. Never use db push for shared environments. The initial migration was generated offline with prisma migrate diff --from-empty --to-schema prisma/schema.prisma --script (it needs a syntactically valid but unused DATABASE_URL, and never connects).

Money

Every canonical amount is a String holding a non-negative integer in the asset's smallest unit ("150025" = ₦1,500.25; "20000000" = 20 USDC at 6 decimals). Asset.decimals gives the interpretation. There is no Float/Decimal anywhere. The format (^(0|[1-9][0-9]*)$) is enforced by CHECK constraints, and src/schema.test.ts fails if a money column changes type or loses its CHECK.

Fields covered: Intent.amount, Quote.{inputAmount,outputAmount,feeAmount}, Route.{estimatedInput,estimatedOutput,totalFeeAmount}, RouteStep.{inputAmount,outputAmount}, Transaction.{amount,gasAmount,nonce}, RampSession.amount.

IDs

All primary keys are String @db.Uuid with no database default. Application code generates UUIDv4 with randomUUID() from node:crypto. Inserting without an id fails at the database.

Migrations

  1. 20261009000000_init is generated by Prisma from schema.prisma.
  2. 20261009000001_money_and_asset_constraints is hand-written: CHECK constraints and partial unique indexes that Prisma 7.10 cannot express. Prisma does not manage or drop these. Keep manual SQL in its own migration; never edit the generated one.
  3. 20261010000000_intent_revision_and_clarification_options (Build 6): Intent.revision (with a >= 1 CHECK), Intent.preferredSourceAssetId, and the ClarificationOption table (server-side meaning of selectable answers; cascades with its intent and conversation).
  4. 20261011000000_provider_capability_uniqueness (Build 7, hand-written): a NULL-safe unique expression index over a capability's provider, type, chain, input, output and country.
  5. 20261012000000_quote_route_intent_revision (Build 8): Quote.intentRevision and Route.intentRevision (>= 1, no default) plus an index, binding every price and route to the intent revision it was built for.
  6. 20261014000000_wallets_passkeys_permissions (Build 10, generated) and 20261014000001_wallet_constraints (hand-written): Wallet gains type/status/deployment/provider fields (address nullable while PROVISIONING); new PasskeyCredential (public key only), PasskeyChallenge (single-use) and DelegatedPermission (bounded, with a per-constraint enforcement record). Constraints: one non-revoked EMBEDDED wallet per user and chain (partial unique index), ACTIVE requires an address, passkey coordinates are 32-byte lower-case hex, and a permission must name operations, contracts and assets, have a positive window and canonical amounts, with lower-case contract addresses.
  7. 20261015000000_wallet_setup_sessions (Build 10.1, hand-written): WalletSetupSession (only a SHA-256 token hash is stored; CHECKs enforce the digest shape, usedAt set iff COMPLETED, and expiry after creation).
  8. 20261016000000_pin_and_payment_authorization (Build 11, Prisma-generated) and 20261016000001_authorization_constraints (hand-written): TransactionPinSecurity (Argon2id hash only, attempt counters, lock), AuthorizationSession (token stored as SHA-256 only, one PENDING per payment), PaymentAuthorization (canonical positive amounts, one ACTIVE per intent, terminal fields consistent).
  9. 20261017000000_firm_quotes_and_execution_plan (Build 12, Prisma-generated) and 20261017000001_firm_quote_constraints (hand-written): ExecutionSecret (AES-GCM envelope only; a bare token cannot be stored), FirmQuoteAttempt (one live attempt per authorization and provider, a complete quote when QUOTED), and the Execution table gains paymentAuthorizationId (unique), walletId, firmQuoteAttemptId, plan and the PREPARING/READY/BLOCKED statuses. A payment execution row can never be EXECUTING or COMPLETED yet.

Tradeoffs and decisions

Asset references are all real foreign keys. Sixteen columns reference Asset (intent source/destination, recipient preferred, capability input/output, quote input/output/fee, route input/output/fee, route-step input/output, transaction asset/gas, ramp asset), each onDelete: Restrict. There are no application-only asset references. The cost is 16 named back-relation fields on Asset, grouped in one commented block. Assets are deactivated (isActive), never deleted. FKs guarantee an asset exists; they do not check that it is active or the right kind for the use. That validation belongs to the AssetRegistry in Build 3+.

Asset uniqueness. The draft @@unique([chainId, contractAddress]) is kept for tokens, but Postgres treats NULLs as distinct, so on its own it does nothing for fiat or native assets. The manual migration adds partial unique indexes (one FIAT row per fiatCode, one NATIVE_ASSET per chainId), an Asset_shape CHECK tying kind to which of fiatCode/chainId/contractAddress are set, and a lowercase-contract CHECK. Fiat-pegged stablecoins may carry a fiatCode without colliding, because the index only covers kind = FIAT. Prisma cannot see these indexes, so upsert by fiatCode is unavailable; the seed uses find-then-create/update.

Transaction hashes. @@unique([chainId, hash]): NULL (before broadcast) is allowed any number of times; a present hash is unique per chain. No partial index needed.

Messages / conversations. unique(conversationId, externalMessageId) de-duplicates webhooks; messages without an external id are never constrained. unique(channel, externalConversationId) maps one external chat to one conversation; WEB conversations (NULL) are unaffected.

Idempotency. Execution.idempotencyKey is globally unique.

ProviderCapability has no composite unique key (nullable columns make one impossible); the seed is responsible for not inserting duplicates. Nothing is assumed supported without a row.

Immutability. Quote and AuditEvent have no updatedAt. Route and RouteStep are snapshots; only Route.status is expected to change. This is convention, not enforced by triggers.

Wallets. Addresses are lowercase (CHECK) and at most one primary wallet per user per chain (partial unique index). A Wallet row does not imply custody. The lowercase rule assumes EVM chains and should be relaxed if a non-EVM chain is added.

Delete behaviour. Financial and audit history is Restrict everywhere: Intent, Quote, Route, RouteStep, Execution, Transaction, RampSession, AuditEvent and all Asset references. A user with any intent, execution, ramp session or audit event therefore cannot be deleted; anonymise instead. Cascade is used only for personal/transient data (Identity, Session, Wallet, Conversation to Message, Recipient owner, ProviderCapability to Provider). Recipient.linkedUser is SetNull.

Drift. migrate dev needs a shadow database; Neon allows creating one with an owner role. Prisma ignores the manual CHECKs and partial indexes when diffing.

Seed

pnpm db:seed upserts providers (textile FX, ripio RAMP, celo RPC) and the fiat assets USD, NGN, ARS, BRL, IDR (ISO 4217, 2 decimals). It never changes the decimals of an existing asset.

It also seeds the verified Celo tokens (USDT, USDC, cNGN, wARS, wBRL, IDRX) and the 40 verified Textile RFQ capabilities (both directions of each USDT corridor). wMXN, wCOP, wPEN, wCLP, USA₮ and every Ripio and Textile ramp capability stay unseeded; see settlement.md for provenance.

Execution lifecycle (Build 13)

Migrations 20261018000000_execution_lifecycle and 20261018000001_execution_lifecycle_constraints: payment states on Execution (SIGNING, SUBMITTING, SUBMITTED, REQUIRES_USER_ACTION), authorizationConsumedAt, settled amounts, claim tombstone; Transaction (userOpHash, idempotencyKey unique, block, timestamps, UNKNOWN); RootActionSession (hashed token, server-fixed challenge, one pending per execution); DelegatedPermission session key and approval references and installedAt; ExecutionSecret tombstones. Constraints keep a payment status from existing without a consumed authorization and keep hashes well formed.