Data Models & Schema Design

Flour n Sugar uses PostgreSQL, Prisma, and Better Auth. The model separates platform authority from a person's role within a particular store, so one person can be a customer, own a store, and work for another.

Entity Relationship Overview

User ── 1:1 ── CustomerProfile
 │
 ├── 1:N ── StoreMember ── N:1 ── Store ── 1:N ── Product ── 1:N ── ProductVariant
 │                    │
 │                    └── StoreRole (OWNER, MANAGER, BAKER, STAFF, DELIVERY, ACCOUNTANT)
 │
 └── PlatformRole (USER, MODERATOR, SUPPORT, ADMIN)

Subcategory ── 1:N ── CatalogProduct
       │
       └── referenced by store Products

Core Prisma Schema Specification

This is the intended MVP shape. It keeps authorization boundaries explicit while allowing product, image, and catalogue fields to evolve independently.

enum PlatformRole {
  USER
  MODERATOR
  SUPPORT
  ADMIN
}

enum StoreRole {
  OWNER
  MANAGER
  BAKER
  STAFF
  DELIVERY
  ACCOUNTANT
}

enum StoreStatus {
  DRAFT
  PUBLISHED
  SUSPENDED
}

enum ProductUnit {
  KG
  GRAM
  POUND
  PIECE
  DOZEN
  BOX
  PACK
  SLICE
  SERVING
  INCH
  ML
  LITER
}

model User {
  id               String       @id @default(uuid())
  name             String
  email            String       @unique
  emailVerified    Boolean      @default(false)
  image            String?
  platformRole     PlatformRole @default(USER)
  storeMemberships StoreMember[]
  customerProfile  CustomerProfile?
  sessions         Session[]
  accounts         Account[]
}

model Store {
  id        String      @id @default(uuid())
  slug      String      @unique
  name      String
  status    StoreStatus @default(DRAFT)
  members   StoreMember[]
  products  Product[]
  createdAt DateTime    @default(now())
  updatedAt DateTime    @updatedAt
}

model StoreMember {
  userId    String
  storeId   String
  role      StoreRole
  user      User  @relation(fields: [userId], references: [id], onDelete: Cascade)
  store     Store @relation(fields: [storeId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@id([userId, storeId])
  @@index([storeId])
}

model Product {
  id            String           @id @default(uuid())
  storeId       String
  store         Store            @relation(fields: [storeId], references: [id], onDelete: Cascade)
  name          String
  description   String?
  images        String[]         @default([])
  isEggless     Boolean          @default(false)
  isVegan       Boolean          @default(false)
  isGlutenFree  Boolean          @default(false)
  isAvailable   Boolean          @default(true)
  variants      ProductVariant[]
  createdAt     DateTime         @default(now())
  updatedAt     DateTime         @updatedAt

  @@index([storeId])
}

model ProductVariant {
  id          String      @id @default(uuid())
  productId   String
  product     Product     @relation(fields: [productId], references: [id], onDelete: Cascade)
  name        String?     // Optional display name, e.g. "Half Kg"
  quantity    Decimal?    @db.Decimal(10, 3)
  unit        ProductUnit?
  price       Decimal     @db.Decimal(10, 2)
  isAvailable Boolean     @default(true)
  sortOrder   Int         @default(0)

  @@index([productId, sortOrder])
}

Authorization and Data-Isolation Rules

• PlatformRole controls platform-wide operations such as moderation and support; it is not a substitute for store permissions.
• StoreMember controls access to one store. A role is a default permission bundle, allowing future refinement without changing the tenant relationship.
• Store-facing queries and mutations must always scope records through the active store membership.
• Public discovery and storefront reads must return only PUBLISHED stores. Owners and authorized staff can access their own drafts.