Schema

The schema is plain Prisma in a single file:

prisma/schema.prisma

70 models and 19 enums, with no second place where the truth lives.

That is deliberate. A schema split across generated fragments, a migration tool's own model file, and a set of application-side type definitions has three descriptions of the same tables, and they drift. Here there is one file to read and one file to change.

This page introduces the models everything else builds on, and the conventions every model follows.

The core models

Catalog

Product holds title, description, SEO, status and publish state.

ProductVariant holds SKU, label and option values. The variant is the sellable thing — a product is the thing customers browse, a variant is the thing that has stock and a price.

VariantPrice holds list, sale and cost price per channel and currency, with startsAt and endsAt. A price is therefore scoped in three dimensions at once: which channel, which currency, and when.

Collection, Category and Tag carry merchandising and structure.

Stock

StockLocation, Inventory and StockMovement record where stock is and how it got there.

Inventory is the current quantity at a location. StockMovement is the history that produced it.

Checkout and orders

The path from browsing to a paid order runs through four model pairs, in order:

1. Cart / CartItem — before checkout
2. OrderIntent / OrderIntentItem — the checkout session, before payment confirms
3. Order / OrderItem — after
4. PaymentTransaction — the money ledger, append-only

The split between OrderIntent and Order is the point where payment confirms. An intent is a checkout in progress; an order is a checkout that succeeded.

Money and goods coming back

RefundRequest, Return and PaymentDispute cover the reverse direction.

Fulfilment

Shipment and ShipmentItem record what left, and the shipment's event history.

People and storefront

Customer and CustomerAddress are who.

Page and Block are the storefront.

The generic legs

MediaAssociation, Translation and Activity are polymorphic over owner type.

One MediaAssociation table serves every entity with images, one Translation table everything localisable, and one Activity ledger all history — each keyed by ownerType/ownerId rather than by a foreign key to one specific table.

A new entity adds an owner type, never a new table.

Conventions

Money is a Postgres Decimal stored next to its currency

Prices use:

Decimal(10, 2)

Order and campaign totals use:

Decimal(12, 2)

Arithmetic goes through utils/money.ts, which wraps decimal.js-light and requires a currency on every Money value. An amount without a currency is a compile error.

Totals are then guarded by database CHECK constraints:

• order_amounts_refunded_not_over_total stops cumulative refunds exceeding an order
• order_breakdown_matches_total_when_present keeps the breakdown reconciled to the charged total
• order_amount_base_present requires every order to carry its base-currency amount

Ledgers are append-only

Payment transactions and stock movements are never edited. Corrections are new rows.

The stock_movement_quantity_math constraint enforces:

previousQuantity + delta = newQuantity AND delta <> 0

So a movement that does not add up is refused by Postgres, not merely discouraged by the application.

Status is derived, not stored

Lifecycle state derives from decision timestamps through one resolver per entity:

• lib/orders/order-lifecycle.ts
• lib/orders/payment-state.ts
• lib/orders/refundable.ts

The 19 enums that remain describe stable categories rather than lifecycle position — FinancialStatus, FulfillmentStatus, UserRole, DiscountKind, InventoryPolicy and the like.

Soft delete where an order could reference it

Those models carry a deletedAt column.

lib/soft-delete-extension.ts injects deletedAt: null into top-level reads, and the client in services/db.ts is extended with it at construction.

It covers:

• findMany
• findFirst
• findUnique
• count
• aggregate
• groupBy

Writing deletedAt: undefined in a where is the documented opt-out for reading deleted rows.

IDs are cuid

Every model declares @default(cuid()). The result is an id that is not guessable and is safe to use in a URL.

Making a change

Edit prisma/schema.prisma, then run:

bun run db:migrate

db:migrate runs prisma migrate dev, which writes the migration into prisma/migrations/ and regenerates the client in the same step.

After that, register the entity the whole way:

1. Admin table
2. Activity logging
3. Cache tags
4. Permissions

.claude/guides/new-entity.md in the repository is the checklist that keeps a new model from ending up half-wired.

Reading the database directly

It is your database, so you can point any reader at it.

bun run db:studio

Prisma Studio, on port 5556.

psql works, and so does a BI tool against a read replica. Nothing about the app assumes it is the only reader.

Where a new model fits

Before adding a table, check whether the thing is really an owner type. Images, translations and history already have their generic leg, and an entity that needs any of the three registers an ownerType instead of getting its own table.

If the model holds money, it needs a Decimal column and a currency beside it, and its totals belong under a CHECK constraint rather than an application-side assertion.

If the model records events rather than current state, make it append-only and let corrections be new rows.

If an order could reference it, give it deletedAt rather than allowing a hard delete to orphan an order line.

And if the model has a lifecycle, ask whether the state is already implied by the timestamps you are storing:

Is this a stable category, or a position in a lifecycle that decision timestamps already record?

The first is an enum. The second is a resolver.

Next Steps