books/docs/architecture/database-schema.md

Database Schema

Prisma Schema Definition

// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Book {
  id           Int          @id @default(autoincrement())
  title        String       @db.VarChar(500)
  author       String?      @db.VarChar(500)
  totalPages   Int
  coverUrl     String?      @db.VarChar(1000)
  deadlineDate DateTime     @db.Date
  isPrimary    Boolean      @default(false)
  status       String       @default("reading") @db.VarChar(50)
  createdAt    DateTime     @default(now())
  updatedAt    DateTime     @updatedAt

  readingLogs  ReadingLog[]

  @@index([deadlineDate])
  @@index([status])
}

model ReadingLog {
  id          Int      @id @default(autoincrement())
  bookId      Int
  logDate     DateTime @db.Date
  currentPage Int
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  book        Book     @relation(fields: [bookId], references: [id], onDelete: Cascade)

  @@unique([bookId, logDate])
  @@index([bookId])
  @@index([logDate])
}

SQL Schema (Generated)

-- Books table
CREATE TABLE "Book" (
  "id" SERIAL PRIMARY KEY,
  "title" VARCHAR(500) NOT NULL,
  "author" VARCHAR(500),
  "totalPages" INTEGER NOT NULL,
  "coverUrl" VARCHAR(1000),
  "deadlineDate" DATE NOT NULL,
  "isPrimary" BOOLEAN NOT NULL DEFAULT false,
  "status" VARCHAR(50) NOT NULL DEFAULT 'reading',
  "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP NOT NULL
);

-- Indexes for Books
CREATE INDEX "Book_deadlineDate_idx" ON "Book"("deadlineDate");
CREATE INDEX "Book_status_idx" ON "Book"("status");

-- Reading Logs table
CREATE TABLE "ReadingLog" (
  "id" SERIAL PRIMARY KEY,
  "bookId" INTEGER NOT NULL,
  "logDate" DATE NOT NULL,
  "currentPage" INTEGER NOT NULL,
  "createdAt" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP NOT NULL,
  CONSTRAINT "ReadingLog_book_fkey" FOREIGN KEY ("bookId") REFERENCES "Book"("id") ON DELETE CASCADE
);

-- Unique constraint: one log per book per day
CREATE UNIQUE INDEX "ReadingLog_bookId_logDate_key" ON "ReadingLog"("bookId", "logDate");

-- Indexes for ReadingLog
CREATE INDEX "ReadingLog_bookId_idx" ON "ReadingLog"("bookId");
CREATE INDEX "ReadingLog_logDate_idx" ON "ReadingLog"("logDate");

Performance Considerations:

• Indexes on deadlineDate and status for filtering active books
• Composite unique index on (bookId, logDate) prevents duplicate logs and optimizes queries
• Index on logDate for date range queries (7-day rolling average)
• Cascade delete ensures orphaned logs don't persist when book is deleted

---