Ugrás a fő tartalomhoz

Database Schema

CakeMarket uses SQLite via Drizzle ORM with the better-sqlite3 driver.

Configuration​

  • ORM: Drizzle ORM
  • Driver: better-sqlite3
  • Config file: drizzle.config.ts
  • Schema location: src/lib/db/schema.ts
  • IDs: UUID v4 for all primary keys
  • Prices: Stored as integers in grosze (1/100 PLN)

Key Tables​

Users​

  • id (UUID, PK)
  • name, email, phone, imageUrl
  • role (user, admin)
  • Authentication-related fields

Organisations​

  • id (UUID, PK)
  • name, address, locationLat, locationLng
  • contactEmail, contactPhone, profileImageUrl
  • isActive (automatic after required seller details and Stripe onboarding)
  • isDisabled (platform enforcement; independent of seller setup)
  • Capabilities: acceptedCakeTypes, acceptedTastes, specialRequirements
  • Operations: leadTimeDays, deliveryEnabled, deliveryRangeKm, pickupEnabled
  • minDepositPercent (20-100)
  • Stripe: stripeAccountId, stripeOnboardingComplete
  • Optional badge review: public certificationLevel; private sanepidCertDocUrl and sanepidCertExpiry (Organisation admins and platform admins only)

Organisation Members​

  • organisationId, userId (composite PK)
  • role (admin, baker)
  • status (pending, active, rejected)
  • joinedAt

Requests (Cake Requests)​

  • id (UUID, PK)
  • buyerId or guestToken
  • eventType, servings, desiredDate
  • deliveryMethod, deliveryAddress
  • fillings, dietaryRequirements
  • themeDescription, cakeImageUrl
  • isReorder, originalOrderId, targetOrganisationId
  • status (active, expired)
  • expiresAt

Offers​

  • id (UUID, PK)
  • requestId, organisationId
  • price (grosze), depositPercent
  • message, proposedImageUrl, proposedImageSource
  • status (pending, accepted, withdrawn)
  • holdExpiresAt

Orders​

  • id (UUID, PK)
  • orderNumber
  • requestId, offerId, organisationId, buyerId
  • totalPrice (grosze), depositAmount, balanceAmount
  • status (deposit_paid, in_progress, ready, delivered, completed, cancelled; disputed is retained only for legacy database compatibility and is not an active workflow)
  • balancePaidAt, balancePaymentMethod
  • cancelledBy, cancelReason
  • Timeline timestamps: depositPaidAt, startedAt, readyAt, deliveredAt, completedAt

Payments​

  • id (UUID, PK)
  • orderId, organisationId
  • type (deposit, balance, refund)
  • amount (grosze), netAmount
  • status (pending, succeeded, failed, refunded)
  • stripePaymentIntentId
  • method (stripe, cash, card_at_pickup)

Messages​

  • id (UUID, PK)
  • orderId or offerId
  • senderId, senderType (buyer, seller)
  • content
  • readAt

Reviews​

  • id (UUID, PK)
  • orderId, buyerId, organisationId
  • rating (1-5), comment

Portfolio Items​

  • id (UUID, PK)
  • organisationId
  • imageUrl, caption
  • sortOrder

Notifications​

  • id (UUID, PK)
  • userId
  • title, body
  • type
  • readAt

Migrations​

Drizzle handles migrations. Run:

npx drizzle-kit generate
npx drizzle-kit migrate