Database Configuration

Noteverse uses PostgreSQL as the primary database and Prisma as the ORM for type-safe database access.

Prerequisites

PostgreSQL 12 or higher installed
Node.js 18.x or higher
Database connection string (see Environment Variables)

Installation

Prisma is already included in the project dependencies. After cloning the repository:

npm install

The postinstall script automatically runs prisma generate after installation to generate the Prisma Client.

Database Setup

1. Create Database

Create a PostgreSQL database for Noteverse:

# Connect to PostgreSQL
psql -U postgres

# Create database
CREATE DATABASE noteverse;

# Create user (optional)
CREATE USER noteverse_user WITH PASSWORD 'your_password';

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE noteverse TO noteverse_user;

2. Configure Connection

Set the DATABASE_URL in your .env.local file:

DATABASE_URL="postgresql://noteverse_user:your_password@localhost:5432/noteverse?schema=public"

3. Run Migrations

Apply the database schema using Prisma migrations:

# Generate Prisma Client
npx prisma generate

# Create and apply migrations
npx prisma migrate dev --name init

Always back up your database before running migrations in production. Use prisma migrate deploy for production environments.

Database Schema

The Prisma schema is located at prisma/schema.prisma.

Data Models

Noteverse uses the following core models:

User Model

model User {
  id                Int            @id @default(autoincrement())
  email             String         @unique
  username          String
  password          String
  authToken         String?
  emailVerified     Boolean        @default(false)
  verificationToken String?

  // Relations
  sharedNotes       SharedStatus[] @relation("SharedBy")
  receivedNotes     SharedStatus[] @relation("SharedWith")
  notes             Note[]         @relation("Owner")
  comments          Comment[]
  likedNotes        Note[]         @relation("LikesOnNotes")
  favoriteNotes     Note[]         @relation("FavoritesOnNotes")
}

Fields:

Int

required

Auto-incrementing primary key

String

required

Unique email address for authentication

username

String

required

Display name for the user

password

String

required

Hashed password using bcrypt

authToken

String

Custom authentication token for API access

emailVerified

Boolean

default:"false"

Email verification status

verificationToken

String

Token used for email verification

Note Model

enum Visibility {
  Public
  Private
  Shared
}

model Note {
  id            Int        @id @default(autoincrement())
  title         String
  description   String?
  data          String?     // Rich text content
  thumbnail     String?
  views         Int        @default(0)
  visibility    Visibility @default(Private)
  createdAt     DateTime   @default(now())
  updatedAt     DateTime   @updatedAt
  ownerId       Int
  categoryId    Int?
  subcategoryId Int?

  // Relations
  owner          User           @relation("Owner", fields: [ownerId], references: [id])
  category       Category?      @relation(fields: [categoryId], references: [id])
  subcategory    SubCategory?   @relation(fields: [subcategoryId], references: [id])
  tags           Tag[]          @relation("TagsOnNotes")
  comments       Comment[]      @relation("CommentsOnNote")
  sharedStatuses SharedStatus[]
  likes          User[]         @relation("LikesOnNotes")
  favorites      User[]         @relation("FavoritesOnNotes")
}

Key Features:

Visibility Control: Public, Private, or Shared access levels
Rich Content: Stores TipTap editor JSON in the data field
Social Features: Likes, favorites, and view tracking
Categorization: Optional category and subcategory classification
Tagging: Many-to-many relationship with tags

Category & SubCategory

model Category {
  id            Int           @id @default(autoincrement())
  title         String
  description   String
  thumbnail     String?
  subcategories SubCategory[]
  notes         Note[]
}

model SubCategory {
  id          Int      @id @default(autoincrement())
  title       String
  description String
  thumbnail   String?
  categoryId  Int
  category    Category @relation(fields: [categoryId], references: [id])
  notes       Note[]

  @@unique([title, categoryId])
}

Categories organize notes hierarchically with optional thumbnails for visual navigation.

Tag Model

model Tag {
  id        Int      @id @default(autoincrement())
  name      String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  notes     Note[]   @relation("TagsOnNotes")
}

Tags enable flexible content organization with many-to-many relationships to notes.

Comment Model

model Comment {
  id              Int      @id @default(autoincrement())
  text            String
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
  noteId          Int
  userId          Int
  parentCommentId Int?

  note          Note     @relation("CommentsOnNote", fields: [noteId], references: [id])
  user          User     @relation(fields: [userId], references: [id])
  parentComment Comment? @relation("ParentComment", fields: [parentCommentId], references: [id])
  replies       Comment[] @relation("ParentComment")
}

Supports nested comments with parent-child relationships for threaded discussions.

SharedStatus Model

enum Permission {
  View
  Edit
}

model SharedStatus {
  id           Int        @id @default(autoincrement())
  sharedById   Int
  sharedWithId Int
  permissions  Permission
  noteId       Int
  sharedAt     DateTime   @default(now())

  sharedBy   User @relation("SharedBy", fields: [sharedById], references: [id])
  sharedWith User @relation("SharedWith", fields: [sharedWithId], references: [id])
  note       Note @relation(fields: [noteId], references: [id])

  @@map("shared_status")
}

Manages note sharing with granular permissions (View or Edit) between users.

Prisma Client Usage

The Prisma Client is initialized in src/lib/prisma.ts:

import { PrismaClient } from '@prisma/client'

const globalForPrisma = global as unknown as { prisma: PrismaClient }

export const prisma =
  globalForPrisma.prisma ||
  new PrismaClient({
    log: ['query'],
  })

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

export default prisma

Querying Data

import prisma from '@/lib/prisma'

// Find user by email
const user = await prisma.user.findUnique({
  where: { email: '[email protected]' },
  include: {
    notes: true,
    likedNotes: true
  }
})

// Create a new note
const note = await prisma.note.create({
  data: {
    title: 'My First Note',
    description: 'A collaborative note',
    data: JSON.stringify(editorContent),
    visibility: 'Public',
    ownerId: userId,
    categoryId: 1
  }
})

// Find notes with relations
const notes = await prisma.note.findMany({
  where: { visibility: 'Public' },
  include: {
    owner: true,
    category: true,
    tags: true,
    comments: {
      include: { user: true }
    }
  },
  orderBy: { createdAt: 'desc' }
})

// Share a note with another user
const sharedStatus = await prisma.sharedStatus.create({
  data: {
    noteId: noteId,
    sharedById: currentUserId,
    sharedWithId: recipientUserId,
    permissions: 'Edit'
  }
})

Database Seeding

Seed your database with initial data:

npm run seed

Create a seed script at prisma/seed.ts:

import { PrismaClient } from '@prisma/client'
import bcrypt from 'bcrypt'

const prisma = new PrismaClient()

async function main() {
  // Create categories
  const techCategory = await prisma.category.create({
    data: {
      title: 'Technology',
      description: 'Tech-related notes and articles',
      subcategories: {
        create: [
          {
            title: 'Web Development',
            description: 'Frontend and backend development'
          },
          {
            title: 'DevOps',
            description: 'Infrastructure and deployment'
          }
        ]
      }
    }
  })

  // Create demo user
  const hashedPassword = await bcrypt.hash('demo123', 10)
  const demoUser = await prisma.user.create({
    data: {
      email: '[email protected]',
      username: 'Demo User',
      password: hashedPassword,
      emailVerified: true
    }
  })

  // Create sample note
  await prisma.note.create({
    data: {
      title: 'Welcome to Noteverse',
      description: 'Getting started with collaborative notes',
      visibility: 'Public',
      ownerId: demoUser.id,
      categoryId: techCategory.id
    }
  })

  console.log('Database seeded successfully!')
}

main()
  .catch((e) => {
    console.error(e)
    process.exit(1)
  })
  .finally(async () => {
    await prisma.$disconnect()
  })

Migrations

Create Migration

npx prisma migrate dev --name add_user_bio

Apply Migrations

npx prisma migrate deploy

Reset Database

This will delete all data in your database!

npx prisma migrate reset

Prisma Studio

Launch a visual database browser:

npx prisma studio

Access at http://localhost:5555 to view and edit data visually.

Performance Optimization

Connection Pooling

Prisma handles connection pooling automatically. Configure pool size:

DATABASE_URL="postgresql://user:password@localhost:5432/noteverse?schema=public&connection_limit=10"

Indexes

Add indexes for frequently queried fields:

model Note {
  // ...
  ownerId Int
  
  @@index([ownerId])
  @@index([visibility])
  @@index([createdAt])
}

Query Optimization

// Use select to fetch only needed fields
const notes = await prisma.note.findMany({
  select: {
    id: true,
    title: true,
    description: true,
    owner: {
      select: { username: true }
    }
  }
})

// Use pagination for large datasets
const paginatedNotes = await prisma.note.findMany({
  take: 20,
  skip: (page - 1) * 20,
  orderBy: { createdAt: 'desc' }
})

Troubleshooting

Common Issues

Migration fails:

Check database connection string
Ensure PostgreSQL is running
Verify user has sufficient privileges

Prisma Client not found:

Run npx prisma generate
Check that @prisma/client is installed

Slow queries:

Add database indexes
Use select instead of full models
Enable query logging to identify bottlenecks

Next Steps

Authentication

Configure user authentication with NextAuth.js

Socket Server

Set up real-time collaboration with Socket.IO

Get Started

Core Features

User Guide

Configuration

Deployment

Database Configuration

Prerequisites

Installation

Database Setup

1. Create Database

2. Configure Connection

3. Run Migrations

Database Schema

Data Models

User Model

Note Model

Category & SubCategory

Tag Model

Comment Model

SharedStatus Model

Prisma Client Usage

Querying Data

Database Seeding

Migrations

Create Migration

Apply Migrations

Reset Database

Prisma Studio

Performance Optimization

Connection Pooling

Indexes

Query Optimization

Troubleshooting

Common Issues

Next Steps

Authentication

Socket Server

Build docs developers (and LLMs) love

Get Started

Core Features

User Guide

Configuration

Deployment

​Prerequisites

​Installation

​Database Setup

​1. Create Database

​2. Configure Connection

​3. Run Migrations

​Database Schema

​Data Models

​User Model

​Note Model

​Category & SubCategory

​Tag Model

​Comment Model

​SharedStatus Model

​Prisma Client Usage

​Querying Data

​Database Seeding

​Migrations

​Create Migration

​Apply Migrations

​Reset Database

​Prisma Studio

​Performance Optimization

​Connection Pooling

​Indexes

​Query Optimization

​Troubleshooting

​Common Issues

​Next Steps

Authentication

Socket Server

Build docs developers (and LLMs) love

Prerequisites

Installation

Database Setup

1. Create Database

2. Configure Connection

3. Run Migrations

Database Schema

Data Models

User Model

Note Model

Category & SubCategory

Tag Model

Comment Model

SharedStatus Model

Prisma Client Usage

Querying Data

Database Seeding

Migrations

Create Migration

Apply Migrations

Reset Database

Prisma Studio

Performance Optimization

Connection Pooling

Indexes

Query Optimization

Troubleshooting

Common Issues

Next Steps