Database Schema

Database Schema Documentation

ORM: Drizzle ORM Databases: PostgreSQL (primary), MongoDB (whatsapp-web-server sessions) Generated: 2026-01-04

Overview

NexisChat uses a multi-database architecture:

• PostgreSQL (via Drizzle): User accounts, subscriptions, folders, templates
• MongoDB (via Mongoose): WhatsApp session data (managed by whatsapp-web-js)

PostgreSQL Schema

Tables

user

Core user identity (Better Auth managed).

Column	Type	Constraints	Description
id	varchar(36)	PRIMARY KEY	User UUID
name	text	NOT NULL	Display name
email	text	UNIQUE, NOT NULL	Email address
emailVerified	boolean	NOT NULL	Email verification status
image	text		Profile image URL
createdAt	timestamp	NOT NULL	Creation timestamp
updatedAt	timestamp	NOT NULL	Last update timestamp

---

session

User sessions (Better Auth managed).

Column	Type	Constraints	Description
id	varchar(36)	PRIMARY KEY	Session UUID
expiresAt	timestamp	NOT NULL	Session expiry
token	text	UNIQUE, NOT NULL	Session token
createdAt	timestamp	NOT NULL	Creation timestamp
updatedAt	timestamp	NOT NULL	Last update
ipAddress	text		Client IP
userAgent	text		Browser user agent
userId	varchar(36)	FK → user.id	Owner user

---

account

OAuth provider connections (Better Auth managed).

Column	Type	Constraints	Description
id	varchar(36)	PRIMARY KEY	Account UUID
accountId	text	NOT NULL	Provider account ID
providerId	text	NOT NULL	OAuth provider name
userId	varchar(36)	FK → user.id	Owner user
accessToken	text		OAuth access token
refreshToken	text		OAuth refresh token
idToken	text		OIDC ID token
accessTokenExpiresAt	timestamp		Token expiry
refreshTokenExpiresAt	timestamp		Refresh expiry
scope	text		OAuth scopes
password	text		Hashed password (credentials)
createdAt	timestamp	NOT NULL	Creation timestamp
updatedAt	timestamp	NOT NULL	Last update

---

verification

Email/phone verification tokens (Better Auth managed).

Column	Type	Constraints	Description
id	varchar(36)	PRIMARY KEY	Verification UUID
identifier	text	NOT NULL	Email/phone to verify
value	text	NOT NULL	Verification token
expiresAt	timestamp	NOT NULL	Token expiry
createdAt	timestamp		Creation timestamp
updatedAt	timestamp		Last update

---

accounts

WhatsApp account connections (app-managed).

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Account UUID
userId	varchar(36)	FK → user.id, NOT NULL	Owner user
name	text	NOT NULL	Account display name
phoneNumber	text	NOT NULL	WhatsApp phone number
avatar	text		Profile avatar URL
color	text	NOT NULL	UI color identifier
status	text	DEFAULT ‘active’	Account status
isBusinessAccount	boolean	NOT NULL	WhatsApp Business flag
isVerified	boolean	NOT NULL	Verified badge status
accountNumber	integer	NOT NULL	Sequential number per user
createdAt	timestamp	NOT NULL	Creation timestamp

Indexes:

• idx_accounts_userId on userId
• Composite index on (userId, accountNumber)

---

subscriptions

User subscription records.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Subscription UUID
userId	varchar(36)	FK → user.id, UNIQUE, NOT NULL	Owner user
planType	text	NOT NULL	Plan: ‘free’, ‘pro_monthly’, ‘pro_yearly’
status	text	NOT NULL	Status: ‘active’, ‘canceled’, ‘expired’
creemCustomerId	text	UNIQUE	Creem customer ID
creemSubscriptionId	text	UNIQUE	Creem subscription ID
creemCheckoutId	text	UNIQUE	Checkout ID (idempotency)
currentPeriodStart	timestamp		Billing period start
currentPeriodEnd	timestamp		Billing period end
canceledAt	timestamp		Cancellation timestamp
createdAt	timestamp	NOT NULL	Creation timestamp
updatedAt	timestamp	NOT NULL	Last update

Constraint: One subscription per user (UNIQUE on userId)

---

priorities

Chat priority configuration per account.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Priority UUID
name	text	NOT NULL	Priority name (e.g., “High”)
color	text	NOT NULL	UI color
accountId	uuid	FK → accounts.id, NOT NULL	Parent account
createdAt	timestamp	NOT NULL	Creation timestamp

---

folders

Chat organization folders per account.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Folder UUID
name	text	NOT NULL	Folder name
color	text	NOT NULL	UI color
accountId	uuid	FK → accounts.id, NOT NULL	Parent account
createdAt	timestamp	NOT NULL	Creation timestamp

---

templates

Message templates per account.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Template UUID
name	text	NOT NULL	Template name
content	text	NOT NULL	Template message content
accountId	uuid	FK → accounts.id, NOT NULL	Parent account
createdAt	timestamp	NOT NULL	Creation timestamp

---

chat_tags

Tags assigned to chats.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Tag UUID
chatId	text	NOT NULL	WhatsApp chat ID
tag	text	NOT NULL	Tag name
accountId	uuid	FK → accounts.id, NOT NULL	Parent account
createdAt	timestamp	NOT NULL	Creation timestamp

Unique: (chatId, accountId) - one tag per chat per account

---

chat_priorities

Priority assignments to chats.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Assignment UUID
chatId	text	NOT NULL	WhatsApp chat ID
priorityId	uuid	FK → priorities.id, NOT NULL	Priority reference
accountId	uuid	FK → accounts.id, NOT NULL	Parent account
createdAt	timestamp	NOT NULL	Creation timestamp

Unique: (chatId, accountId) - one priority per chat per account

---

chat_folders

Folder assignments to chats (many-to-many).

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Assignment UUID
chatId	text	NOT NULL	WhatsApp chat ID
folderId	uuid	FK → folders.id, NOT NULL	Folder reference
accountId	uuid	FK → accounts.id, NOT NULL	Parent account
createdAt	timestamp	NOT NULL	Creation timestamp

---

whatsapp_sessions

WhatsApp Web.js session metadata.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Session UUID
accountId	uuid	FK → accounts.id, UNIQUE, NOT NULL	Parent account
sessionData	jsonb		Encrypted session data
status	text	DEFAULT ‘disconnected’	Connection status
lastConnected	timestamp		Last successful connection
createdAt	timestamp	NOT NULL	Creation timestamp
updatedAt	timestamp	NOT NULL	Last update

---

newsletters

Subscribed newsletters/channels.

Column	Type	Constraints	Description
id	uuid	PRIMARY KEY, DEFAULT gen_random_uuid()	Newsletter UUID
accountId	uuid	FK → accounts.id, NOT NULL	Parent account
channelId	text	NOT NULL	WhatsApp channel ID
name	text		Channel name
subscribedAt	timestamp	NOT NULL	Subscription date
createdAt	timestamp	NOT NULL	Creation timestamp

Entity Relationship Diagram

┌────────────┐
│    user    │
├────────────┤
│ id (PK)    │◄─────────────────┬─────────────────┬─────────────────┐
│ name       │                  │                 │                 │
│ email      │                  │                 │                 │
│ ...        │                  │                 │                 │
└────────────┘                  │                 │                 │
      │                         │                 │                 │
      │ 1:N                     │ 1:N             │ 1:1             │ 1:N
      ▼                         │                 │                 │
┌────────────┐            ┌─────┴──────┐   ┌─────┴──────┐   ┌─────┴──────┐
│  session   │            │  account   │   │subscription│   │  accounts  │
├────────────┤            │(OAuth/Auth)│   ├────────────┤   │ (WhatsApp) │
│ id (PK)    │            └────────────┘   │ id (PK)    │   ├────────────┤
│ userId(FK) │                             │ userId(FK) │   │ id (PK)    │
│ token      │                             │ planType   │   │ userId(FK) │
│ ...        │                             │ status     │   │ phoneNumber│
└────────────┘                             │ ...        │   │ ...        │
                                           └────────────┘   └────────────┘
                                                                   │
                           ┌───────────────────┬───────────────────┼───────────────────┐
                           │                   │                   │                   │
                           ▼                   ▼                   ▼                   ▼
                    ┌────────────┐      ┌────────────┐      ┌────────────┐      ┌────────────┐
                    │ priorities │      │  folders   │      │ templates  │      │ chat_tags  │
                    ├────────────┤      ├────────────┤      ├────────────┤      ├────────────┤
                    │ id (PK)    │      │ id (PK)    │      │ id (PK)    │      │ id (PK)    │
                    │ accountId  │      │ accountId  │      │ accountId  │      │ accountId  │
                    │ name       │      │ name       │      │ content    │      │ chatId     │
                    │ color      │      │ color      │      │ ...        │      │ tag        │
                    └────────────┘      └────────────┘      └────────────┘      └────────────┘
                           │                   │
                           ▼                   ▼
                    ┌────────────┐      ┌────────────┐
                    │chat_priorit│      │chat_folders│
                    ├────────────┤      ├────────────┤
                    │ id (PK)    │      │ id (PK)    │
                    │ priorityId │      │ folderId   │
                    │ chatId     │      │ chatId     │
                    │ accountId  │      │ accountId  │
                    └────────────┘      └────────────┘

Migration History

Migration	Description
0000	Initial schema (user, session, account, verification)
0001	Add WhatsApp accounts table
0002	Add subscriptions table
0003	Add priorities table
0004	Add folders table
0005	Add templates table
0006	Add chat_tags table
0007	Add chat_priorities table
0008	Add chat_folders table
0009	Add whatsapp_sessions table
0010	Add newsletters table
0011	Add indexes for performance
0012	Add Creem payment fields to subscriptions
0013	Add checkout_id for idempotency
0014	Add cascade delete rules

Drizzle Configuration

drizzle.config.ts

export default defineConfig({
  schema: './src/db/schema.ts',
  out: './drizzle',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!
  }
})

Query Patterns

Get User with Subscription

const userWithSub = await db
  .select()
  .from(users)
  .leftJoin(subscriptions, eq(users.id, subscriptions.userId))
  .where(eq(users.id, userId))

Get Folders with Chats

const folders = await db
  .select()
  .from(foldersTable)
  .leftJoin(chatFolders, eq(foldersTable.id, chatFolders.folderId))
  .where(inArray(foldersTable.accountId, accountIds))

Count Templates per Account

const counts = await db
  .select({
    accountId: templates.accountId,
    count: count()
  })
  .from(templates)
  .groupBy(templates.accountId)
  .where(inArray(templates.accountId, accountIds))