PostgreSQL is the authoritative store. Prisma supplies generated types, parameterized queries, migrations, and transaction support. Production uses checked-in migrations; schema synchronization commands are not part of deployment.
erDiagram
ZITADEL_USER ||--|| USER : linked_by_subject
ZITADEL_ORG ||--|| VENDOR : scopes_access
VENDOR ||--o{ IAM_PROVISIONING_OPERATION : provisions
VENDOR ||--o{ PRODUCT : owns
PRODUCT ||--o{ PRODUCT_VARIANT : defines
PRODUCT_VARIANT ||--|| INVENTORY : stocked_by
USER ||--o| CART : owns
CART ||--o{ CART_ITEM : contains
PRODUCT_VARIANT ||--o{ CART_ITEM : selected
USER ||--o{ MARKETPLACE_ORDER : places
MARKETPLACE_ORDER ||--o{ VENDOR_ORDER : splits_into
VENDOR ||--o{ VENDOR_ORDER : fulfills
VENDOR_ORDER ||--o{ ORDER_ITEM : contains
PRODUCT_VARIANT ||--o{ ORDER_ITEM : snapshots
MARKETPLACE_ORDER ||--o{ PAYMENT : paid_by
VENDOR_ORDER ||--o{ SHIPMENT : ships_as
SHIPMENT ||--o{ TRACKING_EVENT : records
ORDER_ITEM ||--o{ COMMISSION_SNAPSHOT : priced_by
VENDOR ||--o{ VENDOR_LEDGER_ENTRY : posts
VENDOR ||--o{ SETTLEMENT : receives
The exact generated model names and every secondary relationship are in prisma/schema.prisma.
The initial migration creates the following aggregate groups:
| Group | Models |
|---|---|
| Identity | minimal User (zitadelUserId, preferences, lifecycle timestamps) and Address; credentials, sessions, tokens, roles, and assignments live in ZITADEL |
| Vendors | Vendor with zitadelOrganizationId, IamProvisioningOperation, verification/document references, and vendor status/verification history |
| Catalog | Category, Attribute, AttributeOption, CategoryAttribute, Product, ProductVariant, product/variant attribute values, MediaAsset, ProductMedia, product status history |
| Stock and shopping | Inventory, InventoryReservation, InventoryMovement, Cart, CartItem, Promotion, Coupon, PromotionRedemption, CheckoutSession |
| Orders and payment | MarketplaceOrder, VendorOrder, OrderItem, both status histories, Payment, PaymentAttempt, WebhookEvent, IdempotencyKey |
| Finance | CommissionRule, OrderCommissionSnapshot, VendorLedgerEntry, Settlement, SettlementItem, Refund, RefundTransaction, reconciliation run/items |
| Fulfillment | Fulfillment, Shipment, TrackingEvent, ReturnRequest, ReturnItem |
| Trust/operations | Dispute, messages/evidence, Review, Notification, NotificationDelivery, SupportCase, AuditLog |
The migration supplements Prisma-generated foreign keys, unique constraints and indexes with PostgreSQL checks for positive quantities, non-negative money, internally consistent totals, commission/rating ranges, return shipment references, valid scope targets, and lifecycle timestamp consistency.
API and application values use integer minor units plus an ISO currency code. For INR, 199900 means ₹1,999.00. Never use JavaScript floating point for financial arithmetic. Prisma stores financial values as PostgreSQL BIGINT; the response interceptor serializes them as base-10 strings so JSON clients do not lose precision. Percentage rules are stored/calculated as basis points where 1,000 bps means 10%. A cart accepts only variants in its currency, checkout verifies every line again, and payment/refund provider results must match the authoritative amount and currency. Order items and commission rows snapshot the values effective at purchase time.
Inventory maintains immediately available, reserved, and committed quantities, with every adjustment represented by a movement. Reservation processes variants in deterministic order inside a serializable PostgreSQL transaction and uses a conditional available-quantity update so concurrent checkouts cannot both claim the final unit. Prisma P2034 serialization conflicts receive a bounded retry, after which the availability guard returns the appropriate stock/concurrency domain error. The safe transition rules are:
available >= 0
reserved >= 0
committed >= 0
reserve q: available -= q; reserved += q
commit q: reserved -= q; committed += q
release q: reserved -= q; available += q
Authoritative payment confirmation rechecks that the checkout and every exact held reservation are unexpired before creating an order. Successful payment then commits the reservation and records a sale movement. Checkout/payment-initiation failure or expiry releases it. Reservation processing is idempotent so a late/duplicate webhook or expiry job cannot change stock twice.
The database-backed e2e suite runs two checkout requests simultaneously against stock 1 and asserts that exactly one reserves successfully while the other receives PRODUCT_OUT_OF_STOCK.
Audit logs, webhook events, payment attempts, order snapshots, inventory movements, commission snapshots, ledger entries, and settlement items are historical records. Normal APIs do not physically delete or rewrite them. Corrective financial activity is posted as a new adjustment or reversal.
Critical mutations persist a key scoped to the actor/operation. Reusing a key with the same request returns the recorded outcome; reusing it for a different payload is rejected. Provider webhook event IDs have a provider-scoped unique constraint. Financial and inventory side effects additionally use stable reference keys/unique constraints as a second defense.
For local schema development:
pnpm migrate:dev -- --name describe_changeFor CI and deployed environments:
pnpm migrate:deployUseful checks:
pnpm prisma:generate
pnpm prisma:validateThe repository uses Prisma 7.9.1. The root prisma.config.ts loads .env, identifies the schema and migrations directory, supplies the CLI datasource URL, and defines the prisma db seed command. The deprecated package.json#prisma property is not used, and the Prisma 7 schema datasource contains only provider = "postgresql".
The prisma-client generator writes an explicit, ignored client to src/generated/prisma. Application code imports this generated client rather than the legacy @prisma/client generated entry point. Generation targets CommonJS to match the established NestJS/Jest runtime. Both the API and seed instantiate Prisma Client with @prisma/adapter-pg; PostgreSQL connection pooling therefore comes from the pg driver. Connection and idle timeouts are set to 5 and 300 seconds respectively to preserve the previous timeout behavior during the upgrade. pg is pinned to the adapter's 8.16.3 compatibility baseline; update it together with Prisma adapter verification because newer pg releases surface an upstream concurrent-query deprecation from Prisma's nested-query interpreter.
The ZITADEL cutover migration is additive and rollback-aware. It backfills User.zitadelUserId from the existing UUID, makes legacy identity columns nullable for JIT identities, adds vendor organization/provisioning fields, and creates durable IAM operation records. The following idempotent reconciliation migration repairs databases that recorded the original migration before its finalized SQL was available. Prisma no longer exposes legacy credential/token/session/RBAC/vendor-membership tables, but the physical tables and columns are retained until a separately approved destructive post-soak migration.
Do not use prisma db push for production, and do not reset a database that may contain valuable data. Review generated SQL for locks, backfills, defaults, and index creation before release. Back up PostgreSQL before material production migrations.
Three forward-only migrations are checked in, with migration_lock.toml pinning PostgreSQL:
20260809000000_initcreates the complete relational model, access-pattern indexes, and the additional checks described above.20260809010000_active_forward_shipment_guardadds a partial unique index allowing only one non-terminalFORWARDshipment per vendor order. This closes the race where concurrent requests use different idempotency keys for the same order.20260817000000_zitadel_authperforms the additive identity crosswalk and provisioning-schema cutover without dropping rollback data.
Neither migration resets or truncates data.
Before applying the shipment guard to an already populated environment, audit and remediate any duplicate active forward shipments. PostgreSQL will intentionally reject the index creation rather than choose a shipment or rewrite history silently.
pnpm seed first applies pending checked-in migrations with prisma migrate deploy, then idempotently inserts obviously fake commerce identities linked to deterministic synthetic ZITADEL subjects, two provisioned vendor fixtures, catalog/inventory, a customer cart/address, media references, a default commission rule, and an audit record. It does not reset the database or create passwords, sessions, roles, or role assignments. Synthetic subjects are for deterministic tests and are not interactive ZITADEL accounts. It is safe to rerun without resetting historical orders or changed inventory state and is never a production identity bootstrap mechanism.
Existing identity migration uses pnpm zitadel:migration:export; see ZITADEL Authentication and Self-Hosting. The dry run reads retained legacy tables and reports only counts. Explicit output writes ignored, sensitive bulk-import and organization-scoped authorization-plan files without overwriting an existing export.