Schema

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

Schema file

prisma/schema.prisma

The file defines 62 models and 17 enums, and it's the only place the tables are described. When a schema is split across generated fragments, a migration tool's own model file and application-side type definitions, those descriptions tend to drift apart over time, so we keep everything in this one file.

The sections below introduce the models the rest of the app builds on and then explain the conventions every model follows.

Core models

Catalog

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

ProductVariant stores the SKU, the label and the option values. Variants are what customers actually buy. Customers browse products, but stock and prices are tracked per variant.

VariantPrice stores the list price, sale price and cost price for one channel and one currency, along with startsAt and endsAt. Because of this, the price that applies depends on the channel, the currency and the current time.

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

Stock

StockLocation, Inventory and StockMovement track where stock is held and how the quantities changed. Inventory holds the current quantity at one location, and StockMovement holds the history of changes that led to that quantity.

Checkout and orders

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

An OrderIntent represents a checkout that's still in progress. Once the payment is confirmed, Litestore creates the Order.

Refunds, returns and disputes

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

Fulfilment

Shipment and ShipmentItem record the goods that left the warehouse, along with each shipment's event history.

Customers and storefront

Customer and CustomerAddress describe the store's customers. Page holds each storefront page's HTML content and the Feed it shows, and Feed describes a product stream and how it's ordered.

Shared tables

MediaAssociation and Translation can belong to any kind of record, and Activity holds the history of the whole store.

Every entity with images uses the same MediaAssociation table, and every translatable record uses the same Translation table. Both point at their owner with ownerType and ownerId instead of a foreign key to one specific table. All history goes into the same Activity ledger, which links to the user, customer, product, order or collab it concerns and keeps any other IDs in its metadata.

When a new entity needs images or translations, you register a new owner type instead of adding a table. When it needs history, you write Activity rows.

Conventions

Money columns

Prices use Decimal(10, 2). Order and campaign totals use Decimal(12, 2).

All arithmetic goes through platform/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.

Ledgers

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 rejects the insert, so the rule holds even if application code gets it wrong.

Derived status

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.

The 17 enums that remain describe stable categories rather than positions in a lifecycle. Examples are FinancialStatus, FulfillmentStatus, UserRole, DiscountKind and InventoryPolicy.

Soft delete

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

platform/db/soft-delete-extension.ts adds deletedAt: null to top-level reads. The client in platform/db/client.ts is built with this extension from the start. The extension applies to findMany, findFirst, findFirstOrThrow, findUnique, findUniqueOrThrow, count, aggregate and groupBy.

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

IDs

Almost every model declares @default(cuid()). Nobody can guess the resulting IDs, and they're safe to put in a URL. The few exceptions are key-value and join tables, such as settings keyed by name and Inventory keyed by location and variant.

Schema changes

bun run db:migrate

Direct database access

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

bun run db:studio

This command opens Prisma Studio on port 5556.

psql and BI tools connected to a read replica work too. The app is fine with other tools reading the database.

Adding models

Next steps