Data model

The database tables, their important columns, and the rules that keep stock and money right.

The schema is prisma/schema.prisma. Money columns are integers in the currency's smallest unit (baisa for OMR). Texts shown to shoppers have an Arabic and an English column.

Tables

Table Holds
Setting One row (id 1): store names, taglines, announcement, contact, currency and decimals, VAT %, low-stock level, shipping methods (JSON), payment switches, taking orders, monthly goal, role permissions (JSON)
Category Departments: slug, names, descriptions, image, order, visible
Collection Curated edits: the same fields plus featured; linked to products many-to-many
Product slug, category, names, descriptions, base price, optional compareAt ("was" price), images (JSON list, first is the cover), featured, active, sort
ProductVariant One buyable combination: unique sku, size, colour names and colorHex, optional own price (null = product price), stock
Coupon code (capitals, unique), type PERCENT or FIXED, value, minOrder, expiresAt, usageLimit, usedCount, active
Order Unguessable code, status, customer name, phone, email, governorate, city, address, notes, shipping method, subtotal, discount, shippingFee, tax, total, coupon code, paymentMethod (COD, CARD), paymentStatus (UNPAID, PAID, FAILED, REFUNDED), paymentRef (Thawani session), locale, optional customerId
OrderItem A copy of what was bought: product and variant ids, SKU, names, variant label ("M / Sand"), image, unit price, quantity, line total
OrderEvent The timeline: status, note, who (customer, thawani, system or the admin's name), time
StaffUser Dashboard accounts: name, unique email, scrypt password hash, role (OWNER, ADMIN, STAFF), active
Customer Shopper accounts: name, unique email, phone, password hash
WishlistItem Customer + product, unique together
ContactMessage Messages from the contact page, with handled

Relations

Category 1─* Product *─* Collection
Product 1─* ProductVariant
Product 1─* WishlistItem *─1 Customer
Customer 1─* Order 1─* OrderItem
                Order 1─* OrderEvent

Deleting a product deletes its variants and wishlist entries; past orders keep their own copy of the names and prices (the OrderItem columns), so history never changes. A category with products cannot be deleted. Deleting a customer account keeps their orders (the link is set to empty).

Rules that keep the numbers right

  • Prices come from the database, never from the browser. src/lib/pricing.ts merges duplicate lines, checks each variant is for sale and in stock, applies the coupon (never more than the subtotal), picks the shipping fee (free over the method's threshold, measured after the discount) and adds VAT on subtotal − discount + shipping, rounded to the nearest baisa.
  • Stock and coupons are taken in the order transaction with conditional updates (stock >= quantity, usedCount < usageLimit). If either fails, nothing is saved.
  • Cancelling or refunding puts the stock back; cancelling also gives the coupon use back.
  • Order codes look like SQ-7KQ2-M9XD: random, without look-alike characters, retried on the rare collision.
  • Roles are checked against the database on every request, so a change applies at once.

These rules are pure functions in src/lib/pricing.ts, src/lib/rules.ts, src/lib/filters.ts and src/lib/permissions.ts, tested by npm test.

Changing the schema

Edit prisma/schema.prisma, then run npx prisma db push (applies the change and regenerates the client). For a team or a live store with PostgreSQL, you may prefer migrations: npx prisma migrate dev --name what-changed.