DATABASE.md

Database Schema & Management

Overview

PostgreSQL 16 database with Prisma ORM for type-safe database operations.

Data Models

User

Stores admin users for authentication.

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  password  String   (hashed with bcryptjs)
  role      Role     @default(ADMIN)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

enum Role {
  ADMIN        # Standard admin
  SUPER_ADMIN  # Full system access
}

Floor

Represents building floors.

model Floor {
  id          Int            @id @default(autoincrement())
  number      Int            @unique
  name        String
  description String?
  rooms       Room[]         # Related rooms
  announcements Announcement[]
  nodes       Node[]         # Display nodes on this floor
  createdAt   DateTime       @default(now())
  updatedAt   DateTime       @updatedAt
}

Room

Individual rooms/classrooms.

model Room {
  id        Int        @id @default(autoincrement())
  name      String
  code      String     @unique     # Room identifier
  type      RoomType               # Lecture hall, Lab, etc.
  capacity  Int?
  floor     Floor      @relation(fields: [floorId], references: [id], onDelete: Cascade)
  floorId   Int
  schedules Schedule[]             # Room schedules
  createdAt DateTime   @default(now())
  updatedAt DateTime   @updatedAt
}

enum RoomType {
  LECTURE_HALL
  LAB
  SEMINAR_ROOM
  OFFICE
  COMMON_AREA
  OTHER
}

Schedule

Room availability and bookings.

model Schedule {
  id              Int        @id @default(autoincrement())
  room            Room       @relation(fields: [roomId], references: [id], onDelete: Cascade)
  roomId          Int
  title           String
  description     String?
  startTime       DateTime
  endTime         DateTime
  recurring       Boolean    @default(false)
  recurrenceRule  String?    # RRULE format
  instructor      String?
  courseCode      String?
  createdAt       DateTime   @default(now())
  updatedAt       DateTime   @updatedAt
}

Announcement

System-wide or floor-specific announcements.

model Announcement {
  id          Int        @id @default(autoincrement())
  title       String
  content     String
  floor       Floor?     @relation(fields: [floorId], references: [id], onDelete: SetNull)
  floorId     Int?       # Null = global announcement
  priority    Priority   @default(NORMAL)
  startDate   DateTime
  endDate     DateTime?
  active      Boolean    @default(true)
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt
}

enum Priority {
  LOW
  NORMAL
  HIGH
  URGENT
}

Node

Display kiosk devices (Raspberry Pi nodes).

model Node {
  id              Int        @id @default(autoincrement())
  name            String
  identifier      String     @unique  # Device ID
  floor           Floor      @relation(fields: [floorId], references: [id], onDelete: Cascade)
  floorId         Int
  location        String?               # Physical location
  isActive        Boolean    @default(true)
  lastHeartbeat   DateTime?
  createdAt       DateTime   @default(now())
  updatedAt       DateTime   @updatedAt
}

Database Setup

Initialize Database

cd server

# Generate Prisma Client
npm run db:generate

# Push schema to database
npm run db:push

Seed Initial Data

npm run db:seed

Default seed creates:

• Super admin user (email: admin@example.com)
• Sample floors
• Sample rooms
• Test schedules

Create Migrations

# Create a new migration after schema changes
npm run db:migrate:dev --name "description_of_changes"

# Deploy existing migrations
npm run db:migrate

# Reset database (⚠️ deletes all data)
npx prisma migrate reset

Database Tools

Prisma Studio

GUI for database management:

npm run db:studio

# Opens at http://localhost:5555

View and edit data directly without SQL.

Direct Database Access

# Connect to PostgreSQL via psql
docker exec -it displaydb psql -U postgres -d display_db

# Useful commands:
\dt              # List tables
\d <table>       # Describe table
SELECT * FROM "User";

Common Operations

Add New User

// Via API or admin panel
const user = await prisma.user.create({
  data: {
    email: "user@example.com",
    name: "John Doe",
    password: await bcrypt.hash("password", 10),
    role: "ADMIN"
  }
});

Add Floor

const floor = await prisma.floor.create({
  data: {
    number: 1,
    name: "First Floor",
    description: "Main entrance floor"
  }
});

Create Room

const room = await prisma.room.create({
  data: {
    name: "Lecture Hall A",
    code: "A101",
    type: "LECTURE_HALL",
    capacity: 100,
    floorId: 1
  }
});

Add Schedule

const schedule = await prisma.schedule.create({
  data: {
    title: "Advanced Mathematics",
    roomId: 1,
    startTime: new Date("2024-03-15T09:00:00"),
    endTime: new Date("2024-03-15T11:00:00"),
    instructor: "Dr. Smith",
    courseCode: "MATH301"
  }
});

Database URL Format

postgresql://[user]:[password]@[host]:[port]/[database]

Example:
postgresql://postgres:postgres@localhost:5432/display_db

Backup & Restore

Backup

docker exec displaydb pg_dump -U postgres display_db > backup.sql

Restore

docker exec -i displaydb psql -U postgres display_db < backup.sql

Performance Tips

1. Add indexes for frequently queried fields
2. Use Prisma's select to limit returned fields
3. Implement pagination for large result sets
4. Cache frequently accessed data client-side