Database Schema

Complete database schema documentation for the Juadah API

Overview

The Juadah API uses PostgreSQL as its database with Prisma ORM for type-safe database access. The schema defines the core entities for the e-commerce platform including users, products, orders, addresses, and ratings.

Database Configuration

The database connection is configured in prisma/schema.prisma:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

generator client {
  provider      = "prisma-client-js"
  binaryTargets = ["native", "rhel-openssl-3.0.x", "debian-openssl-1.1.x"]
}

The schema uses both DATABASE_URL for connection pooling and DIRECT_URL for direct database connections (useful for migrations).

Models

Users

Stores user account information including authentication details and email verification.

model users {
  id                 BigInt     @id @default(autoincrement())
  fullname           String
  email              String     @unique
  password           String
  refresh_token      String?
  email_verified     Boolean?   @default(false)
  verification_token String?    @db.Char(6)
  role               Role       @default(USER)
  ratings            ratings[]
  orders             orders[]
  adresses           adresses[]
}

id · BigInt · required

Primary key, auto-incremented user identifier

fullname · String · required

User’s full name

email · String · required

Unique email address for authentication

password · String · required

Hashed password (using bcrypt)

refresh_token · String

JWT refresh token for session management

email_verified · Boolean · default: "false"

Email verification status

verification_token · String

6-character verification code sent to user’s email

role · Role · default: "USER"

User role (ADMIN or USER)

Relationships:

• One-to-many with ratings - users can rate multiple products
• One-to-many with orders - users can place multiple orders
• One-to-many with adresses - users can have multiple delivery addresses

---

Products

Stores product catalog information.

model products {
  id          BigInt    @id @default(autoincrement())
  name        String
  description String
  price       Float
  images      Json?
  ratings     ratings[]
  orders      orders[]
}

id · BigInt · required

Primary key, auto-incremented product identifier

name · String · required

Product name

description · String · required

Product description

price · Float · required

Product price

images · Json

JSON array of product image URLs (stored in Cloudinary)

Relationships:

• One-to-many with ratings - products can have multiple ratings
• One-to-many with orders - products can be in multiple orders

---

Ratings

Stores product ratings and reviews from users.

model ratings {
  id_user    BigInt
  id_product BigInt
  star       Int
  message    String
  products   products @relation(fields: [id_product], references: [id], onDelete: NoAction, onUpdate: NoAction)
  users      users    @relation(fields: [id_user], references: [id], onDelete: NoAction, onUpdate: NoAction)

  @@id([id_user, id_product])
}

id_user · BigInt · required

Foreign key to users table

id_product · BigInt · required

Foreign key to products table

star · Int · required

Star rating (typically 1-5)

message · String · required

Review message from the user

This model uses a composite primary key of [id_user, id_product], meaning a user can only rate a specific product once.

Relationships:

• Many-to-one with products - each rating belongs to one product
• Many-to-one with users - each rating belongs to one user

---

Addresses

Stores delivery addresses for users.

model adresses {
  id           Int    @id @default(autoincrement())
  province     String
  city         String
  subdistrict  String
  village      String
  postcal_code String @db.Char(6)
  full_address String
  user_id      BigInt

  user   users    @relation(fields: [user_id], references: [id])
  orders orders[]
}

id · Int · required

Primary key, auto-incremented address identifier

province · String · required

Province name

city · String · required

City name

subdistrict · String · required

Subdistrict name

village · String · required

Village name

postcal_code · String · required

6-character postal code

full_address · String · required

Complete address details

user_id · BigInt · required

Foreign key to users table

Relationships:

• Many-to-one with users - each address belongs to one user
• One-to-many with orders - address can be used for multiple orders

---

Orders

Stores order transactions and payment information.

model orders {
  id                 String             @id
  transaction_id     String?
  user_id            BigInt
  product_id         BigInt
  amount             Int // how many product user bought
  total_price        String // amount x product price
  transaction_status Transaction_Status?
  created_at         BigInt
  completed_at       BigInt?
  payment_type       String?
  address_id         Int

  user    users    @relation(fields: [user_id], references: [id])
  product products @relation(fields: [product_id], references: [id])
  address adresses @relation(fields: [address_id], references: [id])
}

id · String · required

Primary key, unique order identifier

transaction_id · String

Payment gateway transaction ID (from Midtrans)

user_id · BigInt · required

Foreign key to users table

product_id · BigInt · required

Foreign key to products table

amount · Int · required

Quantity of products purchased

total_price · String · required

Total price (amount × product price)

transaction_status · Transaction_Status

Current status of the payment transaction

created_at · BigInt · required

Order creation timestamp (Unix timestamp)

completed_at · BigInt

Order completion timestamp (Unix timestamp)

payment_type · String

Payment method used

address_id · Int · required

Foreign key to adresses table for delivery location

Relationships:

• Many-to-one with users - each order belongs to one user
• Many-to-one with products - each order is for one product
• Many-to-one with adresses - each order has one delivery address

---

Enums

Role

Defines user permission levels.

enum Role {
  ADMIN
  USER
}

ADMIN · enum

Administrator with full access

USER · enum

Regular user with standard permissions

---

Transaction_Status

Defines payment transaction states (from Midtrans payment gateway).

enum Transaction_Status {
  capture
  settlement
  pending
  deny
  cancel
  expire
  failure
  refund
  partial_refund
  authorize
}

Status	Description
capture	Payment captured but not yet settled
settlement	Payment successfully completed
pending	Payment awaiting completion
deny	Payment denied by payment gateway
cancel	Payment cancelled by user or system
expire	Payment expired before completion
failure	Payment failed
refund	Full refund issued
partial_refund	Partial refund issued
authorize	Payment authorized but not captured

These statuses correspond to Midtrans payment gateway transaction states. See the Midtrans documentation for more details.

---

Entity Relationship Diagram

The schema follows this relationship structure:

users (1) ──────< (N) ratings (N) >────── (1) products
  │                                            │
  │                                            │
  └────< (N) orders (N) >─────────────────────┘
  │                │
  │                │
  └────< (N) adresses (1) >───────────────────┘

Key Relationships

1. Users can have multiple:

   • Ratings
   • Orders
   • Addresses

2. Products can have multiple:

   • Ratings
   • Orders

3. Orders reference:

   • One user
   • One product
   • One delivery address

4. Ratings have a composite key:

   • One user can rate one product only once
   • Combination of id_user and id_product is unique

---

Database Provider

The schema uses PostgreSQL with specific configuration:

enum crdb_internal_region {
  gcp_asia_southeast1 @map("gcp-asia-southeast1")
}

This enum suggests the database may be deployed on CockroachDB (PostgreSQL-compatible) in the GCP Asia Southeast 1 region.

---

Next Steps

Migrations

Learn how to run and manage database migrations

API Endpoints

Explore the API endpoints that interact with these models