Data Model

Overview

Twenty-four PostgreSQL tables via Prisma 7 (@prisma/adapter-pg), modeling one idea carefully: what the customer bought must never change when the catalog does. Variants, snapshots, soft deletes, Decimal money, and an append-only audit trail do the heavy lifting.

The Variant Model

Apparel needs combinations, not just products:

• products own identity: name, slug, brand, description, images.
• product_variants own everything purchasable: SKU (unique), barcode, price, color, size, dimensions, status.
• inventory is 1:1 per variant with quantity_on_hand, quantity_reserved, and reorder_level.

A black medium shirt and a blue large shirt are two variants of one product — each with its own price, stock, and photos.

Orders Are Immutable Snapshots

order_items copies at purchase time: unit price, discount percentage, product name/slug/SKU, and full variant attributes including dimensions. shipments copies the shipping address. Renaming a product or changing a price never rewrites history.

Identity: Two IDs Per Entity

Every major entity carries both:

Internal IDs never appear in the API. Enumeration by guessing sequential IDs is structurally prevented.

Conventions That Hold Everywhere

• Money: Decimal(10,2) (12,2 for operating_expenses) computed with decimal math, serialized as fixed strings. No floating-point anywhere.
• Soft deletes: deleted_at on ten tables (users, user_addresses, products, product_variants, categories, coupons, reviews, order_items, payments, shipments); repositories always filter them out — nothing commerce-relevant is ever hard-deleted. audit_logs and coupon_usages are never deleted.
• Audit trail: audit_logs is append-only by contract ("rows are never updated or deleted") — actor, action, entity type/id, method/path/status, request body (sensitive keys redacted), previous values/diffs, IP, user agent.
• P&L: operating_expenses (rent/salaries/marketing/… with spent_at date) alongside COGS to compute true net profit.
• Indexes with intent: composite indexes for real query patterns ([users_id, placed_at] on orders), dedicated index migrations added after query analysis; every FK column carries an index.

Auth & Security Tables