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.tsmerges 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.