Docs
Database

Database

Instructions for setting up and using the database.

Prisma

This project uses Prisma as the database client. Prisma is an agnostic object-relational mapper (ORM) that can be used with various database engines, including Postgres, MySQL, and even NoSQL engines like MongoDB.

While Postgres is recommended, the choice of database is ultimately up to you.

Postgres

To get started, you need to install Postgres (PostgreSQL). If you already have it installed or have chosen another database connector, you can skip this section.

Windows

  1. Visit the PostgreSQL Download page.
  2. Download and install Postgres.

Alternatively, use Chocolatey which is like Homebrew for Windows.

MacOS

If you are using a Mac, the recommended installation method is Homebrew.

brew install postgresql

Initial User

On Windows, an initial user is usually created automatically. However, on MacOS and Linux, you need to create it manually. First, log into Postgres as a superuser:

sudo -u postgres psql

Then, create the initial user:

CREATE USER postgres WITH PASSWORD 'password';
ALTER USER postgres WITH SUPERUSER;

Exit the Postgres prompt with:

\q

Connection String

Replace the connection string in the .env file with your Postgres credentials. Default credentials (username/password) are postgres and password.

DATABASE_URL=postgresql://postgres:password@localhost:5432/achromatic_pro?schema=public

Migrations

First make sure that the dependencies are installed:

npm install

To create the database and apply the migration files, run the following command:

npx prisma migrate dev

Prisma Studio

Prisma Studio is a useful tool for viewing and managing data in your local database. It serves as a lightweight alternative to tools like pgAdmin or DBeaver.

Visit Prisma Studio for more information.

Core Models

The most important models are:

  • Account
  • Contact
  • Invitation
  • Organization
  • Session
  • User

Model Relationships

  • Organization -> Users
  • Organization -> Contacts
  • Organization -> Invitations
  • User -> Sessions
  • User -> Accounts

The Contact model is an example of an aggregate with many dependent models, illustrating a real-world scenario. In an e-commerce context, this could be renamed to Customer or something similar.

Multitenancy

Multitenancy is implemented by separating data in the same database using an organizationId, also commonly known as the tenantId.

For example the API keys of an organization

model ApiKey {
  id             String       @id(map: "PK_ApiKey") @default(uuid()) @db.Uuid
  organizationId String       @db.Uuid
  description    String       @db.VarChar(70)
  hashedKey      String       @unique()
  expiresAt      DateTime?
  lastUsedAt     DateTime?
  createdAt      DateTime     @default(now())
  updatedAt      DateTime     @updatedAt
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade, onUpdate: Cascade)
}

The organizationId is on the session object, i.e.

const session = await auth();
session.user.organizationId;

Prisma Schema

Below is the full Prisma schema used in the solution with all enumerations and models.

generator client {
  provider        = "prisma-client-js"
  previewFeatures = []
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
// ---- Enumerations ---- //
 
enum ActionType {
  CREATE @map("create")
  UPDATE @map("update")
  DELETE @map("delete")
}
 
enum ActorType {
  SYSTEM @map("system")
  MEMBER @map("member")
  API    @map("api")
}
 
enum ContactRecord {
  PERSON  @map("person")
  COMPANY @map("company")
}
 
enum ContactStage {
  LEAD           @map("lead")
  QUALIFIED      @map("qualified")
  OPPORTUNITY    @map("opportunity")
  PROPOSAL       @map("proposal")
  IN_NEGOTIATION @map("inNegotiation")
  LOST           @map("lost")
  WON            @map("won")
}
 
enum ContactTaskStatus {
  OPEN      @map("open")
  COMPLETED @map("completed")
}
 
enum DayOfWeek {
  SUNDAY    @map("sunday")
  MONDAY    @map("monday")
  TUESDAY   @map("tuesday")
  WEDNESDAY @map("wednesday")
  THURSDAY  @map("thursday")
  FRIDAY    @map("friday")
  SATURDAY  @map("saturday")
}
 
enum FeedbackCategory {
  SUGGESTION @map("suggestion")
  PROBLEM    @map("problem")
  QUESTION   @map("question")
}
 
enum InvitationStatus {
  PENDING  @map("pending")
  ACCEPTED @map("accepted")
  REVOKED  @map("revoked")
}
 
enum Role {
  MEMBER @map("member")
  ADMIN  @map("admin")
}
 
enum WebhookTrigger {
  CONTACT_CREATED @map("contactCreated")
  CONTACT_UPDATED @map("contactUpdated")
  CONTACT_DELETED @map("contactDeleted")
}
 
// ---- Models ---- //
 
model Account {
  id                String   @id(map: "PK_Account") @default(uuid()) @db.Uuid
  userId            String   @db.Uuid
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?  @db.Text
  access_token      String?  @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?  @db.Text
  session_state     String?
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt
  user              User     @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@unique([provider, providerAccountId])
  @@index([userId], map: "IX_Account_userId")
}
 
model ApiKey {
  id             String       @id(map: "PK_ApiKey") @default(uuid()) @db.Uuid
  organizationId String       @db.Uuid
  description    String       @db.VarChar(70)
  hashedKey      String       @unique()
  expiresAt      DateTime?
  lastUsedAt     DateTime?
  createdAt      DateTime     @default(now())
  updatedAt      DateTime     @updatedAt
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([organizationId], map: "IX_ApiKey_organizationId")
}
 
model AuthenticatorApp {
  id            String   @id(map: "PK_AuthenticatorApp") @default(uuid()) @db.Uuid
  userId        String   @unique @db.Uuid
  accountName   String   @db.VarChar(255)
  issuer        String   @db.VarChar(255)
  secret        String   @db.VarChar(255)
  recoveryCodes String   @db.VarChar(1024)
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt
  user          User     @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([userId], map: "IX_AuthenticatorApp_userId")
}
 
model ChangeEmailRequest {
  id        String   @id(map: "PK_ChangeEmailRequest") @default(uuid()) @db.Uuid
  userId    String   @db.Uuid
  email     String
  expires   DateTime
  valid     Boolean  @default(false)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([userId], map: "IX_ChangeEmailRequest_userId")
}
 
model Contact {
  id             String             @id(map: "PK_Contact") @default(uuid()) @db.Uuid
  organizationId String             @db.Uuid
  record         ContactRecord      @default(PERSON)
  image          String?            @db.VarChar(2048)
  name           String             @db.VarChar(255)
  email          String?            @db.VarChar(255)
  address        String?            @db.VarChar(255)
  phone          String?            @db.VarChar(32)
  stage          ContactStage       @default(LEAD)
  createdAt      DateTime           @default(now())
  updatedAt      DateTime           @updatedAt
  organization   Organization       @relation(fields: [organizationId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  activities     ContactActivity[]
  comments       ContactComment[]
  notes          ContactNote[]
  pageVisits     ContactPageVisit[]
  tags           ContactTag[]
  favorites      Favorite[]
  tasks          ContactTask[]
 
  @@index([organizationId], map: "IX_Contact_organizationId")
}
 
model ContactActivity {
  id         String     @id(map: "PK_ContactActivity") @default(uuid()) @db.Uuid
  contactId  String     @db.Uuid
  actionType ActionType
  actorId    String     @db.VarChar(255)
  actorType  ActorType
  metadata   Json?
  occurredAt DateTime   @default(now())
  contact    Contact    @relation(fields: [contactId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([contactId], map: "IX_ContactActivity_contactId")
  @@index([occurredAt], map: "IX_ContactActivity_occurredAt")
}
 
model ContactComment {
  id        String   @id(map: "PK_ContactComment") @default(uuid()) @db.Uuid
  contactId String   @db.Uuid
  userId    String   @db.Uuid
  text      String   @db.VarChar(2000)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  contact   Contact  @relation(fields: [contactId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([contactId], map: "IX_ContactComment_contactId")
  @@index([userId], map: "IX_ContactComment_userId")
}
 
model ContactImage {
  id          String  @id(map: "PK_ContactImage") @default(uuid()) @db.Uuid
  contactId   String  @db.Uuid
  data        Bytes?
  contentType String? @db.VarChar(255)
  hash        String? @db.VarChar(64)
 
  @@index([contactId], map: "IX_ContactImage_contactId")
}
 
model ContactNote {
  id        String   @id(map: "PK_ContactNote") @default(uuid()) @db.Uuid
  contactId String   @db.Uuid
  userId    String   @db.Uuid
  text      String?  @db.VarChar(8000)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  contact   Contact  @relation(fields: [contactId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([contactId], map: "IX_ContactNote_contactId")
  @@index([userId], map: "IX_ContactNote_userId")
}
 
model ContactPageVisit {
  id        String   @id(map: "PK_ContactPageVisit") @default(uuid()) @db.Uuid
  contactId String   @db.Uuid
  userId    String?  @db.Uuid
  timestamp DateTime @default(now())
  contact   Contact  @relation(fields: [contactId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  user      User?    @relation(fields: [userId], references: [id], onDelete: SetNull, onUpdate: Cascade)
 
  @@index([contactId], map: "IX_ContactPageVisit_contactId")
  @@index([userId], map: "IX_ContactPageVisit_userId")
}
 
model ContactTag {
  id       String    @id(map: "PK_ContactTag") @default(uuid()) @db.Uuid
  text     String    @unique @db.VarChar(128)
  contacts Contact[]
}
 
model ContactTask {
  id          String            @id(map: "PK_ContactTask") @default(uuid()) @db.Uuid
  contactId   String            @db.Uuid
  title       String            @db.VarChar(255)
  description String?           @db.VarChar(8000)
  status      ContactTaskStatus @default(OPEN)
  dueDate     DateTime?
  createdAt   DateTime          @default(now())
  updatedAt   DateTime          @updatedAt
  contact     Contact           @relation(fields: [contactId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([contactId], map: "IX_ContactTask_contactId")
}
 
model Favorite {
  id        String  @id(map: "PK_Favorite") @default(uuid()) @db.Uuid
  userId    String  @db.Uuid
  contactId String  @db.Uuid
  order     Int     @default(0)
  user      User    @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  contact   Contact @relation(fields: [contactId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([userId], map: "IX_Favorite_userId")
  @@index([contactId], map: "IX_Favorite_contactId")
}
 
model Feedback {
  id             String           @id(map: "PK_Feedback") @default(uuid()) @db.Uuid
  organizationId String           @db.Uuid
  userId         String?          @db.Uuid
  category       FeedbackCategory @default(SUGGESTION)
  message        String           @db.VarChar(4000)
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @updatedAt
  organization   Organization     @relation(fields: [organizationId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  user           User?            @relation(fields: [userId], references: [id], onDelete: SetNull, onUpdate: Cascade)
 
  @@index([organizationId], map: "IX_Feedback_organizationId")
  @@index([userId], map: "IX_Feedback_userId")
}
 
model Invitation {
  id             String           @id(map: "PK_Invitation") @default(uuid()) @db.Uuid
  organizationId String           @db.Uuid
  token          String           @default(uuid()) @db.Uuid
  email          String           @db.VarChar(255)
  role           Role             @default(MEMBER)
  status         InvitationStatus @default(PENDING)
  lastSentAt     DateTime?
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @updatedAt
  organization   Organization     @relation(fields: [organizationId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([organizationId], map: "IX_Invitation_organizationId")
  @@index([token], map: "IX_Invitation_token")
}
 
model Notification {
  id        String    @id(map: "PK_Notification") @default(uuid()) @db.Uuid
  userId    String    @db.Uuid
  subject   String?   @db.VarChar(128)
  content   String    @db.VarChar(8000)
  link      String?   @db.VarChar(2000)
  seenAt    DateTime?
  dismissed Boolean   @default(false)
  createdAt DateTime  @default(now())
  updatedAt DateTime  @updatedAt
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([userId], map: "IX_Notification_userId")
}
 
model Organization {
  id                  String       @id(map: "PK_Organization") @default(uuid()) @db.Uuid
  stripeCustomerId    String
  name                String       @db.VarChar(255)
  address             String?      @db.VarChar(255)
  phone               String?      @db.VarChar(32)
  email               String?      @db.VarChar(255)
  website             String?      @db.VarChar(2000)
  linkedInProfile     String?      @db.VarChar(2000)
  instagramProfile    String?      @db.VarChar(2000)
  youTubeChannel      String?      @db.VarChar(2000)
  xProfile            String?      @db.VarChar(2000)
  tikTokProfile       String?      @db.VarChar(2000)
  facebookPage        String?      @db.VarChar(2000)
  completedOnboarding Boolean      @default(false)
  tier                String       @default("free") @db.VarChar(255)
  apiKeys             ApiKey[]
  businessHours       WorkHours[]
  contacts            Contact[]
  feedback            Feedback[]
  invitations         Invitation[]
  users               User[]
  webhooks            Webhook[]
 
  @@index([stripeCustomerId], map: "IX_Organization_stripeCustomerId")
}
 
model ResetPasswordRequest {
  id        String   @id(map: "PK_ResetPasswordRequest") @default(uuid()) @db.Uuid
  email     String
  expires   DateTime
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
 
  @@index([email], map: "IX_ResetPasswordRequest_email")
}
 
model Session {
  id           String   @id(map: "PK_Session") @default(uuid()) @db.Uuid
  sessionToken String   @unique
  userId       String   @db.Uuid
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
 
  @@index([userId], map: "IX_Session_userId")
}
 
model User {
  id                           String               @id(map: "PK_User") @default(uuid()) @db.Uuid
  organizationId               String?              @db.Uuid
  image                        String?              @db.VarChar(2048)
  name                         String               @db.VarChar(64)
  email                        String?              @unique
  emailVerified                DateTime?
  password                     String?              @db.VarChar(60)
  lastLogin                    DateTime?
  role                         Role                 @default(MEMBER)
  phone                        String?              @db.VarChar(32)
  locale                       String               @default("en-US") @db.VarChar(8)
  completedOnboarding          Boolean              @default(false)
  enabledContactsNotifications Boolean              @default(false)
  enabledInboxNotifications    Boolean              @default(false)
  enabledWeeklySummary         Boolean              @default(false)
  enabledNewsletter            Boolean              @default(false)
  enabledProductUpdates        Boolean              @default(false)
  createdAt                    DateTime             @default(now())
  updatedAt                    DateTime             @updatedAt
  accounts                     Account[]
  authenticatorApp             AuthenticatorApp?
  changeEmailRequests          ChangeEmailRequest[]
  comments                     ContactComment[]
  favorites                    Favorite[]
  feedback                     Feedback[]
  notes                        ContactNote[]
  notifications                Notification[]
  organization                 Organization?        @relation(fields: [organizationId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  pageVisits                   ContactPageVisit[]
  sessions                     Session[]
 
  @@index([organizationId], map: "IX_User_organizationId")
}
 
model UserImage {
  id          String  @id(map: "PK_UserImage") @default(uuid()) @db.Uuid
  userId      String  @db.Uuid
  data        Bytes?
  contentType String? @db.VarChar(255)
  hash        String? @db.VarChar(64)
 
  @@index([userId], map: "IX_UserImage_userId")
}
 
model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime
 
  @@unique([identifier, token])
}
 
model Webhook {
  id             String           @id(map: "PK_Webhook") @default(uuid()) @db.Uuid
  organizationId String           @db.Uuid
  url            String           @db.VarChar(2000)
  triggers       WebhookTrigger[]
  secret         String?          @db.VarChar(1024)
  createdAt      DateTime         @default(now())
  updatedAt      DateTime         @updatedAt
  organization   Organization     @relation(fields: [organizationId], references: [id], onDelete: Cascade)
 
  @@index([organizationId], map: "IX_Webhook_organizationId")
}
 
model WorkHours {
  id             String         @id(map: "PK_WorkHours") @default(uuid()) @db.Uuid
  organizationId String         @db.Uuid
  dayOfWeek      DayOfWeek      @default(SUNDAY)
  organization   Organization   @relation(fields: [organizationId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  timeSlots      WorkTimeSlot[]
 
  @@index([organizationId], map: "IX_WorkHours_organizationId")
}
 
model WorkTimeSlot {
  id          String    @id(map: "PK_WorkTimeSlot") @default(uuid()) @db.Uuid
  workHoursId String    @db.Uuid
  start       DateTime  @db.Time(0)
  end         DateTime  @db.Time(0)
  workHours   WorkHours @relation(fields: [workHoursId], references: [id], onDelete: Cascade, onUpdate: Cascade)
 
  @@index([workHoursId], map: "IX_WorkTimeSlot_workHoursId")
}

How to use Prisma

This guide covers basic database client operations, including querying, creating, updating, and deleting records. For more details about the Prisma client, refer to the official Prisma documentation.

Querying Records

To retrieve records from the database, use the findMany method on the Prisma client. This method accepts an object with optional where, orderBy, skip, and take fields, enabling you to filter, order, and paginate results.

Here's an example of querying all contacts of the current organization from the database:

import { prisma } from '@/lib/db/prisma';
 
const contacts = await prisma.contact.findMany({
  where: { organizationId: session.user.organizationId },
  orderBy: { createdAt: 'desc' }
});

Creating Records

To create a new record in the database, use the create method on the Prisma client.

import { prisma } from '@/lib/db/prisma';
 
const contact = await prisma.contact.create({
  data: {
    organizationId: session.user.organizationId,
    name: 'John Doe',
    email: 'john.doe@gmail.com'
  }
});

Updating Records

To update an existing record in the database, use the update method on the Prisma client.

import { prisma } from '@/lib/db/prisma';
 
const updatedContact = await prisma.contact.update({
  where: {
    organizationId: session.user.organizationId,
    id: parsedInput.contactId
  },
  data: {
    name: 'John Travolta'
  }
});

Deleting Records

To delete a record from the database, use the delete method on the Prisma client.

import { prisma } from '@/lib/db/prisma';
 
const deletedContact = await prisma.contact.delete({
  where: {
    organizationId: session.user.organizationId,
    id: parsedInput.contactId
  }
});