Database Schema & Data Model

This document provides a comprehensive overview of DeelRxCRM’s database schema, including entity relationships, constraints, and data flow patterns.

Architecture Overview

Database Technology

ORM: Prisma 5.0 with PostgreSQL 16
Provider: Neon serverless PostgreSQL (production) / Local PostgreSQL (development)
Connection: Pooled connections via PgBouncer for optimal performance
Schema: Multi-tenant design with team-based data isolation

Design Principles

Multi-tenancy: All data scoped to teams/organizations
Audit trails: Comprehensive logging of changes and access
Referential integrity: Foreign key constraints and cascade rules
Performance optimization: Strategic indexing and query optimization
Data validation: Server-side validation with Zod schemas

Core Entities

Authentication & Multi-tenancy

User

Primary entity for authentication and user management

model User {
  id          String   @id @default(cuid())
  clerkId     String   @unique // Clerk authentication integration
  email       String   @unique
  firstName   String?
  lastName    String?
  phone       String?
  imageUrl    String?
  isActive    Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // Relations
  teamMemberships  TeamMembership[]
  createdCustomers Customer[]       @relation("CustomerCreatedBy")
  createdOrders    Order[]          @relation("OrderCreatedBy")
  aliases          UserAlias[]

  @@map("users")
}

Key Relationships:

One-to-many with TeamMembership (user can be in multiple teams)
One-to-many with Customer (users create customers)
One-to-many with Order (users create orders)
One-to-many with UserAlias (email aliases for privacy)

Indexes:

clerkId (unique) - Fast lookup from Clerk authentication
email (unique) - Email-based user lookup
Composite index on (firstName, lastName) - Name-based searches

Team

Organization/tenant entity for multi-tenancy

model Team {
  id          String   @id @default(cuid())
  name        String
  slug        String   @unique
  description String?
  isActive    Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // Relations (all team-scoped data)
  memberships          TeamMembership[]
  customers            Customer[]
  products             Product[]
  orders               Order[]
  payments             Payment[]
  paymentIdempotencyKeys PaymentIdempotency[]
  deliveries           Delivery[]
  inventoryAdjustments InventoryAdjustment[]
  loyaltyPrograms      LoyaltyProgram[]
  loyaltyAccounts      LoyaltyAccount[]
  loyaltyEvents        LoyaltyEvent[]
  loyaltyTransactions  LoyaltyTransaction[]
  referrals            CustomerReferral[]

  @@map("teams")
}

Multi-tenancy Pattern:

All business data (customers, products, orders) scoped to teams
Team isolation enforced at database level with foreign keys
Row-level security via application-level filtering
Team ownership tracked for all entities

TeamMembership

User-to-team relationship with role-based access

model TeamMembership {
  id       String           @id @default(cuid())
  teamId   String
  userId   String
  role     TeamMemberRole   @default(MEMBER)
  isActive Boolean          @default(true)
  joinedAt DateTime         @default(now())

  // Relations - CORRECTED
  team Team @relation(fields: [teamId], references: [id], onDelete: Cascade)
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([teamId, userId])
  @@index([userId]) // Performance index for user lookups
  @@map("team_memberships")
}

enum TeamMemberRole {
  OWNER    // Full access, team management
  ADMIN    // Full access, no team management
  MANAGER  // Customer/operations access, read-only settings
  MEMBER   // Read-only access
}

Role-Based Access Control (RBAC):

OWNER: Full system access including team management
ADMIN: Full operational access, read-only settings
MANAGER: Customer/order/payment access, read-only reports
MEMBER: Read-only access to assigned data

Core CRM Entities

Customer

Primary customer relationship management entity

model Customer {
  id          String    @id @default(cuid())
  teamId      String
  firstName   String?
  lastName    String?
  email       String?
  phone       String?
  dateOfBirth DateTime?
  address     Json? // Validated by Zod schema before persistence
  isActive    Boolean   @default(true)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt
  createdById String?

  // Relations
  team                Team                 @relation(fields: [teamId], references: [id], onDelete: Cascade)
  createdBy           User?                @relation("CustomerCreatedBy", fields: [createdById], references: [id])
  orders              Order[]
  loyaltyAccounts     LoyaltyAccount[]
  referralsAsReferrer CustomerReferral[]   @relation("ReferrerCustomer")
  referralsAsReferred CustomerReferral[]   @relation("ReferredCustomer")

  @@index([teamId])
  @@index([email])
  @@index([createdById])
  @@map("customers")
}

Customer Data Structure:

Basic Info: Name, contact details, date of birth
Address: JSON field with structured address data
Lifecycle: Active/inactive status with audit trail
Relationships: Orders, loyalty programs, referrals

Address JSON Schema:

{
  "street": "123 Main St",
  "city": "San Francisco",
  "state": "CA",
  "zipCode": "94105",
  "country": "USA"
}

Product

Product catalog with inventory management

model Product {
  id                   String   @id @default(cuid())
  teamId               String
  name                 String
  description          String?
  sku                  String?
  price                Decimal  @default(0) @db.Decimal(12,2)
  cost                 Decimal? @db.Decimal(12,2)
  stockQuantity        Int      @default(0)
  lowStockThreshold    Int      @default(10)
  category             String?
  isActive             Boolean  @default(true)
  createdAt            DateTime @default(now())
  updatedAt            DateTime @updatedAt

  // Relations
  team                 Team                  @relation(fields: [teamId], references: [id], onDelete: Cascade)
  orderItems           OrderItem[]
  inventoryAdjustments InventoryAdjustment[]

  @@unique([teamId, sku]) // Composite tenant uniqueness
  @@index([teamId])
  @@map("products")
}

Inventory Management:

Stock Tracking: Real-time quantity management
Low Stock Alerts: Configurable thresholds
Cost Management: Purchase cost for profit calculations
Categorization: Product organization and reporting

Order

Order management with lifecycle tracking

model Order {
  id             String      @id @default(cuid())
  teamId         String
  customerId     String?
  orderNumber    String      // Generated per-team sequence
  status         OrderStatus @default(PENDING)
  totalAmount    Decimal     @default(0) @db.Decimal(12,2)
  taxAmount      Decimal?    @db.Decimal(12,2)
  discountAmount Decimal?    @db.Decimal(12,2)
  notes          String?
  createdAt      DateTime    @default(now())
  updatedAt      DateTime    @updatedAt
  createdById    String?

  // Relations
  team                Team                 @relation(fields: [teamId], references: [id], onDelete: Cascade)
  customer            Customer?            @relation(fields: [customerId], references: [id])
  createdBy           User?                @relation("OrderCreatedBy", fields: [createdById], references: [id])
  items               OrderItem[]
  payments            Payment[]
  paymentIdempotencyKeys PaymentIdempotency[]
  delivery            Delivery?
  loyaltyTransactions LoyaltyTransaction[]

  @@unique([teamId, orderNumber])
  @@index([teamId])
  @@index([customerId])
  @@index([createdById])
  @@map("orders")
}

enum OrderStatus {
  PENDING     // Initial state
  CONFIRMED   // Customer confirmed
  PROCESSING  // Being prepared
  SHIPPED     // Sent to customer
  DELIVERED   // Successfully delivered
  CANCELLED   // Order cancelled
}

Order Lifecycle:

PENDING: Order created, awaiting confirmation
CONFIRMED: Customer confirmed order details
PROCESSING: Order being prepared for shipment
SHIPPED: Order sent to customer
DELIVERED: Successful delivery confirmation
CANCELLED: Order cancelled (refund if applicable)

Payment

Payment processing and reconciliation

model Payment {
  id                String        @id @default(cuid())
  teamId            String
  orderId           String
  amount            Decimal       @db.Decimal(12,2)
  paymentMethod     PaymentMethod
  status            PaymentStatus @default(PENDING)
  transactionId     String?
  processorResponse Json?
  createdAt         DateTime      @default(now())

  // Relations
  team  Team  @relation(fields: [teamId], references: [id], onDelete: Cascade)
  order Order @relation(fields: [orderId], references: [id], onDelete: Cascade)
  idempotencyKeys PaymentIdempotency[]

  @@index([teamId])
  @@index([orderId])
  @@map("payments")
}

enum PaymentMethod {
  CASH              // Physical cash payment
  CARD              // Credit/debit card (future)
  CHECK             // Paper check
  BANK_TRANSFER     // Wire transfer, ACH
  DIGITAL_WALLET    // Apple Pay, Google Pay, etc.
}

enum PaymentStatus {
  PENDING    // Payment initiated
  COMPLETED  // Payment successful
  FAILED     // Payment failed
  REFUNDED   // Payment refunded
  CANCELLED  // Payment cancelled
}

Payment Features:

Manual Reconciliation: Zero-fee processing for cash, checks, digital wallets
Future Card Processing: Stripe integration planned
Idempotency: Prevents duplicate payments
Audit Trail: Complete transaction history

Extended Operations (Phase 2 Features)

Inventory Management

InventoryAdjustment

Stock level changes with audit trail

model InventoryAdjustment {
  id                 String           @id @default(cuid())
  teamId             String
  productId          String
  reason             AdjustmentReason
  previousQuantity   Int
  adjustmentQuantity Int              // Signed delta
  newQuantity        Int              // Computed
  notes              String?
  createdAt          DateTime         @default(now())
  createdById        String?

  // Relations
  team    Team    @relation(fields: [teamId], references: [id], onDelete: Cascade)
  product Product @relation(fields: [productId], references: [id], onDelete: Cascade)

  @@index([teamId])
  @@index([productId])
  @@map("inventory_adjustments")
}

enum AdjustmentReason {
  WASTE      // Product damaged/spoiled
  DAMAGE     // Product damage
  THEFT      // Inventory theft/loss
  EXPIRY     // Expired product
  SAMPLE     // Sample given to customer
  PERSONAL   // Personal use
  RECOUNT    // Inventory count correction
  OTHER      // Other reason
}

Inventory Tracking:

Automatic Calculations: New quantity computed from adjustments
Reason Codes: Standardized reasons for adjustments
Audit Trail: Complete history of stock changes
Low Stock Alerts: Triggered when quantity falls below threshold

Customer Referrals

CustomerReferral

Referral program management

model CustomerReferral {
  id                   String         @id @default(cuid())
  teamId               String
  referrerCustomerId   String
  referredEmail        String?
  referredPhone        String?
  referredCustomerId   String?
  status               ReferralStatus @default(PENDING)
  rewardAmount         Int            @default(0)
  rewardPaid           Boolean        @default(false)
  notes                String?
  expiresAt            DateTime?
  convertedAt          DateTime?
  createdAt            DateTime       @default(now())

  // Relations
  team             Team      @relation(fields: [teamId], references: [id], onDelete: Cascade)
  referrerCustomer Customer  @relation("ReferrerCustomer", fields: [referrerCustomerId], references: [id], onDelete: Cascade)
  referredCustomer Customer? @relation("ReferredCustomer", fields: [referredCustomerId], references: [id])

  @@index([teamId])
  @@index([referrerCustomerId])
  @@index([referredCustomerId])
  @@map("customer_referrals")
}

enum ReferralStatus {
  PENDING    // Referral sent, awaiting response
  CONVERTED  // Referred customer made purchase
  EXPIRED    // Referral expired without conversion
}

Referral System:

Reward Tracking: Points/rewards for successful referrals
Conversion Tracking: Links referrals to actual customers
Expiration: Time-limited referral validity
Analytics: Referral performance metrics

Delivery Management

Delivery

Order delivery tracking

model Delivery {
  id                String         @id @default(cuid())
  teamId            String
  orderId           String         @unique
  address           Json           // Delivery address
  deliveryMethod    DeliveryMethod
  status            DeliveryStatus @default(PENDING)
  trackingNumber    String?
  estimatedDelivery DateTime?
  actualDelivery    DateTime?
  notes             String?
  createdAt         DateTime       @default(now())

  // Relations
  team  Team  @relation(fields: [teamId], references: [id], onDelete: Cascade)
  order Order @relation(fields: [orderId], references: [id], onDelete: Cascade)

  @@index([teamId])
  @@map("deliveries")
}

enum DeliveryMethod {
  PICKUP      // Customer pickup
  DELIVERY    // Local delivery
  SHIPPING    // Postal/shipping service
}

enum DeliveryStatus {
  PENDING           // Delivery not yet scheduled
  CONFIRMED         // Delivery scheduled
  PREPARING         // Order being prepared for delivery
  OUT_FOR_DELIVERY  // In transit to customer
  DELIVERED         // Successfully delivered
  CANCELLED         // Delivery cancelled
}

Delivery Features:

Multiple Methods: Pickup, local delivery, shipping
Status Tracking: Complete delivery lifecycle
Address Management: Structured address data
Integration Ready: Prepared for shipping provider APIs

Loyalty Program System

LoyaltyProgram

Points-based customer retention

model LoyaltyProgram {
  id              String   @id @default(cuid())
  teamId          String
  name            String
  description     String?
  pointsPerDollar Int      @default(1)
  isActive        Boolean  @default(true)
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  // Relations
  team     Team             @relation(fields: [teamId], references: [id], onDelete: Cascade)
  accounts LoyaltyAccount[]

  @@index([teamId])
  @@map("loyalty_programs")
}

LoyaltyAccount

Customer loyalty program participation

model LoyaltyAccount {
  id            String   @id @default(cuid())
  teamId        String
  customerId    String
  programId     String
  pointsBalance Int      @default(0)
  totalEarned   Int      @default(0)
  totalRedeemed Int      @default(0)
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  // Relations
  team         Team                 @relation(fields: [teamId], references: [id], onDelete: Cascade)
  customer     Customer             @relation(fields: [customerId], references: [id], onDelete: Cascade)
  program      LoyaltyProgram       @relation(fields: [programId], references: [id], onDelete: Cascade)
  events       LoyaltyEvent[]
  transactions LoyaltyTransaction[]

  @@unique([customerId, programId])
  @@index([teamId])
  @@index([customerId])
  @@index([programId])
  @@map("loyalty_accounts")
}

LoyaltyEvent

Individual loyalty point transactions

model LoyaltyEvent {
  id            String           @id @default(cuid())
  teamId        String
  accountId     String
  eventType     LoyaltyEventType
  points        Int
  balanceBefore Int
  balanceAfter  Int
  description   String?
  orderId       String?
  createdAt     DateTime         @default(now())

  // Relations
  team    Team           @relation(fields: [teamId], references: [id], onDelete: Cascade)
  account LoyaltyAccount @relation(fields: [accountId], references: [id], onDelete: Cascade)

  @@index([teamId])
  @@index([accountId])
  @@map("loyalty_events")
}

enum LoyaltyEventType {
  EARNED    // Points earned from purchase
  REDEEMED  // Points used for reward
  EXPIRED   // Points expired
  ADJUSTED  // Manual point adjustment
}

Privacy & Security Systems

Email Alias Management (SimpleLogin)

UserAlias

Email alias system for privacy protection

model UserAlias {
  id           String   @id @default(cuid())
  clerkUserId  String   // Clerk user ID instead of internal user ID
  alias        String   // The actual alias email address
  aliasId      String   // SimpleLogin's internal ID for this alias
  enabled      Boolean  @default(true)
  shadow       Boolean  @default(false) // True for pre-signup aliases, false for persisted ones
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  // Relations
  user User @relation(fields: [clerkUserId], references: [clerkId], onDelete: Cascade)

  @@index([clerkUserId])
  @@index([alias])
  @@map("user_aliases")
}

Alias Features:

Privacy Protection: Hide real email addresses
Shadow Aliases: Pre-signup aliases for privacy
Alias Management: Enable/disable/delete as needed
Forwarding: All alias emails forwarded to real email

Email Event Tracking (Resend)

EmailEvent

Email delivery and engagement tracking

model EmailEvent {
  id          String   @id @default(cuid())
  eventType   String   // delivered|bounce|complaint|open|click|...
  toAddress   String   // Email address that received the event
  providerId  String   // Resend message ID or other provider identifier
  occurredAt  DateTime @default(now())
  meta        Json?    // Raw provider payload (sanitized)

  @@index([toAddress])
  @@index([eventType])
  @@index([occurredAt])
  @@map("email_events")
}

Email Tracking:

Delivery Events: Track email delivery status
Engagement: Monitor opens, clicks, bounces
Provider Integration: Works with Resend webhooks
Privacy Compliant: No PII in event tracking

Database Relationships

Entity Relationship Diagram (ERD)

┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│    User     │     │    Team     │     │  Customer   │
├─────────────┤     ├─────────────┤     ├─────────────┤
│ - id        │     │ - id        │     │ - id        │
│ - clerkId   │     │ - name      │     │ - teamId    │
│ - email     │     │ - slug      │     │ - firstName │
│ - firstName │     │ - isActive  │     │ - lastName  │
│ - lastName  │     │             │     │ - email     │
└─────────────┘     └─────────────┘     └─────────────┘
       │                   │                   │
       │                   │                   │
       ▼                   ▼                   ▼
┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│TeamMembership│     │   Product   │     │   Order     │
├─────────────┤     ├─────────────┤     ├─────────────┤
│ - userId    │     │ - id        │     │ - id        │
│ - teamId    │     │ - teamId    │     │ - teamId    │
│ - role      │     │ - name      │     │ - customerId│
│ - isActive  │     │ - sku       │     │ - orderNumber│
└─────────────┘     │ - price     │     │ - status    │
                     │ - stockQty  │     │ - totalAmt  │
                     └─────────────┘     └─────────────┘
                             │                   │
                             │                   │
                             ▼                   ▼
┌─────────────┐     ┌─────────────┐     ┌─────────────┐
│OrderItem    │     │  Payment    │     │  Delivery   │
├─────────────┤     ├─────────────┤     ├─────────────┤
│ - orderId   │     │ - id        │     │ - id        │
│ - productId │     │ - orderId   │     │ - orderId   │
│ - quantity  │     │ - amount    │     │ - address   │
│ - unitPrice │     │ - method    │     │ - method    │
└─────────────┘     │ - status    │     │ - status    │
                     └─────────────┘     └─────────────┘

Relationship Cardinality

Relationship	Type	Description
User → TeamMembership	1:many	User can be in multiple teams
Team → TeamMembership	1:many	Team has multiple members
Team → Customer	1:many	Team has many customers
Customer → Order	1:many	Customer has many orders
Order → OrderItem	1:many	Order has many items
Product → OrderItem	1:many	Product can be in many orders
Order → Payment	1:many	Order can have multiple payments
Order → Delivery	1:1	Order has one delivery
Customer → LoyaltyAccount	1:many	Customer can have multiple loyalty accounts
LoyaltyProgram → LoyaltyAccount	1:many	Program has many accounts

Performance Optimization

Indexing Strategy

Primary Indexes:

All @id fields automatically indexed
All @unique fields automatically indexed
Foreign key fields automatically indexed

Performance Indexes:

@@index([teamId]) - Team-scoped queries
@@index([createdById]) - User activity queries
@@index([email]) - Customer lookup by email
@@index([orderNumber]) - Order lookup by number
Composite indexes for common query patterns

Query Optimization

Team Scoping:

// All queries include team filtering
SELECT * FROM customers WHERE team_id = ? AND ...

// Efficient team-based pagination
SELECT * FROM orders WHERE team_id = ?
ORDER BY created_at DESC
LIMIT 50 OFFSET 0

Common Query Patterns:

Customer search by name/email/phone
Order filtering by status and date range
Product inventory level checks
Payment reconciliation queries
Loyalty program participation tracking

Data Validation & Constraints

Database-Level Constraints

Foreign Key Constraints: Prevent orphaned records
Unique Constraints: Prevent duplicate data
Check Constraints: Enforce business rules
Not Null Constraints: Required field validation

Application-Level Validation

Zod Schemas: Runtime type validation
Prisma Middleware: Cross-cutting concerns
Business Logic: Complex validation rules

Data Integrity Checks

Referential Integrity: All relationships validated
Audit Trails: All changes logged and tracked
Data Consistency: Regular integrity checks
Backup Validation: Restore testing procedures

Migration Strategy

Schema Evolution

Prisma Migrations: Version-controlled schema changes
Backward Compatibility: Support for data migration
Zero Downtime: Careful migration planning
Rollback Support: Migration reversal capabilities

Data Migration Examples

-- Add new field with default value
ALTER TABLE customers ADD COLUMN phone_verified BOOLEAN DEFAULT false;

-- Migrate existing data
UPDATE customers SET phone_verified = true WHERE phone IS NOT NULL;

-- Add index for performance
CREATE INDEX CONCURRENTLY idx_customers_phone_verified ON customers(phone_verified);

Security Considerations

Data Access Patterns

Row-Level Security: Team-based data isolation
Role-Based Access: Permission-based data access
Audit Logging: All data access tracked
Encryption: Sensitive data encrypted at rest

Privacy Protection

Email Aliases: Hide real email addresses
Data Minimization: Only collect necessary data
Consent Management: Track user consent
Right to Erasure: Support for data deletion

Monitoring & Analytics

Performance Metrics

Query execution times
Connection pool utilization
Index usage statistics
Slow query identification

Business Metrics

Customer acquisition rates
Order conversion rates
Payment reconciliation status
Inventory turnover rates

System Health

Database connection health
Query performance trends
Data consistency checks
Backup verification status

This schema documentation is automatically updated with each database migration. Last updated: September 2025

Database

​Database Schema & Data Model

​Architecture Overview

​Database Technology

​Design Principles

​Core Entities

​Authentication & Multi-tenancy

​User

​Team

​TeamMembership

​Core CRM Entities

​Customer

​Product

​Order

​Payment

​Extended Operations (Phase 2 Features)

​Inventory Management

​InventoryAdjustment

​Customer Referrals

​CustomerReferral

​Delivery Management

​Delivery

​Loyalty Program System

​LoyaltyProgram

​LoyaltyAccount

​LoyaltyEvent

​Privacy & Security Systems

​Email Alias Management (SimpleLogin)

​UserAlias

​Email Event Tracking (Resend)

​EmailEvent

​Database Relationships

​Entity Relationship Diagram (ERD)

​Relationship Cardinality

​Performance Optimization

​Indexing Strategy

​Query Optimization

​Data Validation & Constraints

​Database-Level Constraints

​Application-Level Validation

​Data Integrity Checks

​Migration Strategy

​Schema Evolution

​Data Migration Examples

​Security Considerations

​Data Access Patterns

​Privacy Protection

​Monitoring & Analytics

​Performance Metrics

​Business Metrics

​System Health