Schema

Litestore describes its whole database in one plain Prisma file. You read this file to understand the data, and you edit it when you add or change a model.

prisma/schema.prisma

The file defines 70 models and 19 enums. No other file describes the same tables.

We chose this on purpose. When a schema is split across generated fragments, a migration tool's own model file and application-side type definitions, you end up with three descriptions of the same tables, and they drift apart. With one file, you have one place to read and one place to change.

This page introduces the models the rest of the app builds on. It then explains the conventions every model follows.

The core models

Catalog

Product stores the title, description, SEO fields, status and publish state.

ProductVariant stores the SKU, the label and the option values. The variant is the thing you sell. Customers browse products, but each variant has its own stock and price.

VariantPrice stores the list price, sale price and cost price for one channel and one currency. It also has startsAt and endsAt. This means every price depends on three things at once: the channel, the currency and the time.

Collection, Category and Tag organise the catalog and drive merchandising.

Stock

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

Inventory is the current quantity at one location. StockMovement is the history of changes that produced that quantity.

Checkout and orders

A purchase moves through four groups of models, in this order:

1. Cart / CartItem are used before checkout.
2. OrderIntent / OrderIntentItem represent the checkout session before the payment confirms.
3. Order / OrderItem exist after the payment confirms.
4. PaymentTransaction is the money ledger. Rows are only ever added to it.

The payment confirmation is the line between OrderIntent and Order. An intent is a checkout that is still in progress. An order is a checkout that succeeded.

Money and goods coming back

RefundRequest, Return and PaymentDispute handle money and goods that flow back from the customer.

Fulfilment

Shipment and ShipmentItem record what left your warehouse, along with each shipment's event history.

People and storefront

Customer and CustomerAddress describe your customers.

Page and Block make up the storefront.

Shared tables for images, translations and history

MediaAssociation, Translation and Activity can belong to any kind of record.

Every entity with images uses the same MediaAssociation table. Everything you can translate uses the same Translation table, and all history goes into the same Activity ledger. Each of these tables points at its owner with ownerType and ownerId instead of a foreign key to one specific table.

When you add a new entity, you add a new owner type, never a new table.

Conventions

Money is a Postgres Decimal stored next to its currency

Prices use this type:

Decimal(10, 2)

Order and campaign totals use this type:

Decimal(12, 2)

All arithmetic goes through utils/money.ts. It wraps decimal.js-light and requires a currency on every Money value. If you write an amount without a currency, the code doesn't compile.

The database also guards totals with CHECK constraints:

• order_amounts_refunded_not_over_total stops the refunds on an order from adding up to more than the order total.
• order_breakdown_matches_total_when_present keeps the breakdown equal to the total that was charged.
• order_amount_base_present requires every order to store its amount in the base currency.

Ledgers are append-only

Litestore never edits a payment transaction or a stock movement. To correct one, it adds a new row.

The stock_movement_quantity_math constraint enforces this rule:

previousQuantity + delta = newQuantity AND delta <> 0

If a movement doesn't add up, Postgres refuses it. The application doesn't merely discourage it.

Status is derived, not stored

Litestore works out the lifecycle state of an entity from the timestamps of the decisions made about it. Each entity has one resolver that does this:

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

The 19 remaining enums describe stable categories, not a position in a lifecycle. Examples are FinancialStatus, FulfillmentStatus, UserRole, DiscountKind and InventoryPolicy.

Soft delete where an order could reference it

Any model that an order could point to has a deletedAt column.

lib/soft-delete-extension.ts adds deletedAt: null to top-level reads. The client in services/db.ts is built with this extension from the start.

The extension applies to these operations:

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

To read deleted rows, write deletedAt: undefined in the where. This is the documented way to opt out.

IDs are cuid

Every model declares @default(cuid()). Nobody can guess the resulting IDs, and you can safely put them in a URL.

Making a change

Edit prisma/schema.prisma, then run:

bun run db:migrate

db:migrate runs prisma migrate dev. In one step, it writes the migration into prisma/migrations/ and regenerates the client.

Next, connect the new entity to the rest of the app:

1. Add it to an admin table.
2. Log its activity.
3. Give it cache tags.
4. Set its permissions.

The repository includes a checklist at .claude/guides/new-entity.md. Follow it so that a new model doesn't end up half connected.

Reading the database directly

The database belongs to you, so you can connect any tool you like to it.

bun run db:studio

This command opens Prisma Studio on port 5556.

You can also use psql, or connect a BI tool to a read replica. The app doesn't assume it is the only thing reading the database.

Where a new model fits

Before you add a table, check whether the thing is really an owner type. Images, translations and history already have shared tables. If a new entity needs any of them, register an ownerType instead of creating a table.

If the model stores money, give it a Decimal column with a currency next to it. Protect its totals with a CHECK constraint rather than a check in application code.

If the model records events rather than current state, make it append-only. Corrections then become new rows.

If an order could point to the model, give it deletedAt. A hard delete would leave an order line pointing at nothing.

If the model has a lifecycle, ask whether the timestamps you already store imply its state:

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

A stable category becomes an enum. A lifecycle position becomes a resolver.

Next Steps