Database Schema

Last Updated: 2025-11-22

Overview

The AccessALI Customer Portal uses PostgreSQL 16 as its primary database, accessed through Prisma ORM 6.2.1 for type-safe database operations. The schema is designed to support the complete property purchase lifecycle from reservation to turnover.

Schema Management

All schema changes are managed through Prisma migrations located in src/prisma/migrations/. Never modify the database directly - always use Prisma migrations.

Entity Relationship Diagram

erDiagram
    User ||--o{ Property : owns
    User ||--o{ Payment : makes
    User ||--o{ Document : uploads
    User ||--o{ Message : sends
    User ||--o{ Appointment : schedules
    User ||--o{ Notification : receives
    User ||--o{ Account : "has OAuth"
    User ||--o{ Session : "has active"

    Property ||--o{ Payment : "has payments"
    Property ||--o{ Document : "has documents"
    Property ||--o{ Milestone : "has milestones"
    Property ||--o{ ConstructionUpdate : "has updates"

    User {
        string id PK
        string email UK
        string firstName
        string lastName
        enum role
        enum status
    }

    Property {
        string id PK
        string userId FK
        string sapPropertyId UK
        string projectName
        string unitNumber
        decimal totalContractPrice
        int milestoneProgress
    }

    Milestone {
        string id PK
        string propertyId FK
        enum type
        enum status
        int progress
        datetime completedAt
    }

    Payment {
        string id PK
        string userId FK
        string propertyId FK
        decimal amount
        enum status
        datetime dueDate
    }

    Document {
        string id PK
        string userId FK
        string propertyId FK
        enum type
        string fileUrl
        enum status
    }

    Message {
        string id PK
        string userId FK
        string subject
        enum status
    }

    Appointment {
        string id PK
        string userId FK
        enum type
        datetime scheduledAt
        enum status
    }

    Notification {
        string id PK
        string userId FK
        enum type
        string title
        boolean isRead
    }

    Account {
        string id PK
        string userId FK
        string provider
        string providerAccountId
    }

    Session {
        string id PK
        string userId FK
        string sessionToken UK
        datetime expires
    }

    ConstructionUpdate {
        string id PK
        string propertyId FK
        enum phase
        enum status
        int progress
    }

Core Entities

User & Authentication

User

The central entity representing authenticated users in the system.

model User {
  id              String     @id @default(cuid())
  email           String     @unique
  emailVerified   DateTime?
  password        String?    // Nullable for OAuth users
  firstName       String?
  lastName        String?
  profilePhoto    String?
  role            UserRole   @default(BUYER)
  status          UserStatus @default(PENDING)
  termsAcceptedAt DateTime?
  lastLogin       DateTime?
  createdAt       DateTime   @default(now())
  updatedAt       DateTime   @updatedAt
}

Enums:

enum UserRole {
  BUYER   // Property buyer (default)
  ADMIN   // System administrator
  SUPPORT // Customer support agent
}

enum UserStatus {
  PENDING   // Invited but not activated
  ACTIVE    // Account activated
  INACTIVE  // Account deactivated
  SUSPENDED // Account suspended
}

Indexes: - email - Fast email lookups - role - Filter by user role

Account (NextAuth.js)

Stores OAuth provider credentials for Google and Facebook authentication.

model Account {
  id                String @id @default(cuid())
  userId            String
  type              String // "oauth" | "oidc" | "email"
  provider          String // "google" | "facebook"
  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?

  @@unique([provider, providerAccountId])
  @@index([userId])
}

OAuth Tokens

OAuth tokens are stored securely and automatically refreshed by NextAuth.js when expired.

Session (NextAuth.js)

Manages user sessions for authentication.

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime

  @@index([userId])
  @@index([sessionToken])
}

VerificationToken (NextAuth.js)

Used for magic link email authentication (passwordless login).

model VerificationToken {
  identifier String   // User's email
  token      String   @unique
  expires    DateTime // Expires after 15 minutes

  @@unique([identifier, token])
  @@index([identifier])
  @@index([token])
}

ActivationToken

Custom token for new buyer account activation flow.

model ActivationToken {
  id        String   @id @default(cuid())
  email     String
  token     String   @unique
  expires   DateTime // Expires after 24 hours
  used      Boolean  @default(false)
  createdAt DateTime @default(now())

  @@index([email])
  @@index([token])
  @@index([expires])
}

Token Cleanup

Expired activation tokens should be cleaned up periodically using a cron job or scheduled task.

Property Management

Property

Represents a real estate property purchased by a buyer.

model Property {
  id                String   @id @default(cuid())
  userId            String
  sapPropertyId     String   @unique // External SAP ID
  contractNumber    String?
  companyCode       String?
  projectName       String
  unitNumber        String
  address           String
  city              String?
  zipCode           String?
  brand             String?  // ALVEO, AVIDA, AMAIA

  // Specifications
  floorArea         Decimal? @db.Decimal(10, 2)
  balconyArea       Decimal? @db.Decimal(10, 2)
  lotArea           Decimal? @db.Decimal(10, 2)
  bedrooms          Int?
  bathrooms         Int?
  parkingSlots      Int?     @default(0)
  floorLevel        Int?

  // Pricing
  totalContractPrice Decimal? @db.Decimal(15, 2)
  pricePerSqm        Decimal? @db.Decimal(10, 2)

  // Milestone tracking
  milestoneStatus    String         @default("PENDING")
  milestoneProgress  Int            @default(0) // 0-100%
  currentMilestone   MilestoneType?

  // Dates
  expectedTurnover   DateTime?
  actualTurnover     DateTime?
  createdAt          DateTime @default(now())
  updatedAt          DateTime @updatedAt
}

Performance Indexes:

@@index([userId, milestoneStatus])
@@index([userId, createdAt(sort: Desc)])
@@index([userId, milestoneProgress])
@@index([brand])
@@index([milestoneStatus, updatedAt(sort: Desc)])

Milestone

Tracks the 8 major milestones in the property purchase journey.

model Milestone {
  id               String          @id @default(cuid())
  propertyId       String
  type             MilestoneType
  name             String
  description      String?
  status           MilestoneStatus @default(PENDING)
  progress         Int             @default(0) // 0-100%

  startedAt        DateTime?
  completedAt      DateTime?
  expectedDate     DateTime?

  notes            String?
  assignedTo       String?

  // Dependency tracking
  dependsOn        String?         // Comma-separated milestone IDs
  dependencyMessage String?

  @@unique([propertyId, type]) // One of each milestone per property
  @@index([propertyId])
  @@index([status])
  @@index([type])
}

Milestone Types (8 Major Milestones):

enum MilestoneType {
  RESERVATION        // Milestone 1: Reservation
  REQUIRED_DOCS      // Milestone 2: Required Documents
  PAYMENTS           // Milestone 3: Payments
  CTS                // Milestone 4: Contract to Sell
  DOAS               // Milestone 5: Deed of Absolute Sale
  TITLE              // Milestone 6: Title (TCT/CCT)
  TAX_DECLARATION    // Milestone 7: Tax Declaration
  TURNOVER           // Milestone 8: Property Turnover
}

enum MilestoneStatus {
  COMPLETED       // Green - Fully completed
  IN_PROGRESS     // Yellow - Currently working on
  PENDING         // Gray - Not started
  NOT_APPLICABLE  // Gray - Doesn't apply
}

ConstructionUpdate

Tracks construction progress through 7 phases.

model ConstructionUpdate {
  id          String             @id @default(cuid())
  propertyId  String

  phase       ConstructionPhase
  title       String
  description String
  status      ConstructionStatus @default(UPCOMING)
  progress    Int                @default(0) // 0-100%

  photoUrl    String?
  photoAlt    String?

  startDate   DateTime?
  completionDate DateTime?
  expectedDate   DateTime?
  updateDate     DateTime @default(now())

  @@index([propertyId])
  @@index([status])
  @@index([updateDate])
}

Construction Phases:

enum ConstructionPhase {
  SITE_CLEARING      // Site clearing, layout, excavation
  FOUNDATION         // Ground floor structural works
  STRUCTURE          // Structural works completion
  ROOFING            // Roof installation
  FINISHING          // Interior/exterior finishing
  UNIT_COMPLETED     // Housing unit completed
  TURNOVER_PREP      // Turnover preparations
  READY_FOR_TURNOVER // Ready for turnover
}

enum ConstructionStatus {
  COMPLETED      // Green - Phase completed
  ONGOING        // Yellow - In progress
  UPCOMING       // Gray - Not started
  NOT_APPLICABLE // N/A
}

Payments

Payment

Tracks all payment transactions.

model Payment {
  id            String        @id @default(cuid())
  userId        String
  propertyId    String
  amount        Decimal       @db.Decimal(10, 2)
  status        PaymentStatus @default(PENDING)
  paymentMethod String?
  transactionId String?       @unique
  dueDate       DateTime?
  paidAt        DateTime?
  createdAt     DateTime      @default(now())
  updatedAt     DateTime      @updatedAt

  @@index([userId, status])
  @@index([userId, dueDate])
  @@index([propertyId, status, dueDate])
  @@index([status, dueDate])
  @@index([createdAt(sort: Desc)])
}

Payment Statuses:

enum PaymentStatus {
  PENDING
  PROCESSING
  COMPLETED
  FAILED
  REFUNDED
}

Document Management

Document

Manages all property-related documents.

model Document {
  id               String         @id @default(cuid())
  userId           String
  propertyId       String
  type             DocumentType
  nomenclature     String?        // SpringCM document name
  fileName         String
  fileUrl          String
  fileSize         Int
  mimeType         String
  status           DocumentStatus @default(PENDING)

  // Quick link metadata
  isQuickLink      Boolean @default(false)
  displayOrder     Int?

  // External IDs
  springCmId       String? @unique
  salesforceId     String?

  uploadedAt       DateTime @default(now())
  verifiedAt       DateTime?
  lastAccessedAt   DateTime?

  @@index([propertyId, uploadedAt(sort: Desc)])
  @@index([userId, propertyId, type])
  @@index([userId, type, uploadedAt(sort: Desc)])
  @@index([isQuickLink, uploadedAt(sort: Desc)])
}

Document Types:

enum DocumentType {
  RESERVATION_AGREEMENT  // FRA
  CONTRACT_TO_SELL      // CTS
  DEED_OF_SALE          // DOAS
  OFFICIAL_RECEIPT      // OR
  STATEMENT_OF_ACCOUNT  // SOA
  VALID_ID              // ID
  PROOF_OF_INCOME       // Income docs
  TAX_DECLARATION       // Tax Certificate
  TITLE                 // TCT/CCT
  OTHER                 // Other documents
}

enum DocumentStatus {
  PENDING              // Waiting for upload
  SUBMITTED            // Submitted to Amicassa
  VERIFIED             // Verified and approved
  REJECTED             // Needs revision
  NOT_YET_AVAILABLE    // Not generated yet
}

Communication

Message

Customer support messaging system.

model Message {
  id         String        @id @default(cuid())
  userId     String
  subject    String
  body       String
  status     MessageStatus @default(NEW)
  isUnread   Boolean       @default(true)
  sentAt     DateTime      @default(now())
  resolvedAt DateTime?

  @@index([userId, status])
  @@index([userId, sentAt(sort: Desc)])
  @@index([userId, status, sentAt(sort: Desc)])
}

enum MessageStatus {
  NEW
  IN_PROGRESS
  RESOLVED
  CLOSED
}

Appointment

Appointment scheduling system.

model Appointment {
  id          String            @id @default(cuid())
  userId      String
  type        AppointmentType
  scheduledAt DateTime
  status      AppointmentStatus @default(SCHEDULED)
  notes       String?
  createdAt   DateTime          @default(now())
  cancelledAt DateTime?

  @@index([userId, scheduledAt])
  @@index([userId, status, scheduledAt])
  @@index([scheduledAt])
  @@index([status, scheduledAt])
}

enum AppointmentType {
  DOCUMENT_PICKUP
  PAYMENT
  PROPERTY_VIEWING
  CONSULTATION
}

enum AppointmentStatus {
  SCHEDULED
  CONFIRMED
  COMPLETED
  CANCELLED
}

Notification

In-app notification system.

model Notification {
  id          String           @id @default(cuid())
  userId      String
  type        NotificationType
  title       String           @db.VarChar(50)
  description String           @db.VarChar(150)
  actionUrl   String?
  actionLabel String?
  priority    Priority         @default(MEDIUM)
  isRead      Boolean          @default(false)
  createdAt   DateTime         @default(now())
  readAt      DateTime?

  @@index([userId, isRead])
  @@index([userId, priority, createdAt(sort: Desc)])
  @@index([userId, createdAt(sort: Desc)])
  @@index([userId, type, isRead])
}

enum NotificationType {
  PAYMENT
  DOCUMENT
  APPOINTMENT
  CONSTRUCTION
}

enum Priority {
  HIGH
  MEDIUM
  LOW
}

Database Migrations

Running Migrations

# Development (Docker)
cd src
pnpm docker:db:migrate

# Local development
cd src
pnpm db:migrate

# Production
npx prisma migrate deploy

Creating New Migrations

# 1. Edit prisma/schema.prisma
# 2. Generate migration
npx prisma migrate dev --name add_new_field

# 3. Review generated SQL in prisma/migrations/
# 4. Test migration
pnpm db:migrate

# 5. Commit migration files
git add prisma/migrations/
git commit -m "feat: add new field to User model"

Production Migrations

Always test migrations in development before deploying to production. Use prisma migrate deploy in production, never prisma migrate dev.

Database Indexes

Index Strategy

The schema uses strategic indexes to optimize common query patterns:

User Queries: - Email lookups (unique index) - Role-based filtering

Property Queries: - User's properties (userId index) - Milestone status filtering - Property search by SAP ID

Payment Queries: - User's payments with status filter - Upcoming payments by due date - Property payment history

Document Queries: - User's documents by property - Quick link documents - Document type filtering

Index Naming Convention

@@index([field1, field2], name: "idx_table_field1_field2")

Example:

@@index([userId, status], name: "idx_payment_user_status")

Data Seeding

Seed Data

Development seed data is located in prisma/seed.ts:

# Seed database
cd src
pnpm db:seed

Seed Data Includes: - Test users (buyers, admin, support) - Sample properties - Milestone data - Payment history - Sample documents - Notifications

Development Data

Seed data is useful for local development and testing. Never seed production databases.

Database Tools

Prisma Studio

Visual database browser for development:

# Start Prisma Studio
cd src
pnpm docker:db:studio

# Opens http://localhost:5555

Database Backup

# Backup database
pg_dump $DATABASE_URL > backup.sql

# Restore database
psql $DATABASE_URL < backup.sql

Performance Considerations

Connection Pooling

Prisma uses connection pooling by default. Configure in schema.prisma:

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")      // Pooled connection
  directUrl = env("DIRECT_URL")        // Direct connection for migrations
}

Query Optimization

1. Use Proper Includes - Only include relations you need
2. Leverage Indexes - Query using indexed fields
3. Paginate Results - Use take and skip for large datasets
4. Avoid N+1 Queries - Use Prisma's include and select

// ❌ Bad: N+1 query
const users = await prisma.user.findMany()
for (const user of users) {
  const properties = await prisma.property.findMany({ where: { userId: user.id } })
}

// ✅ Good: Single query with include
const users = await prisma.user.findMany({
  include: { properties: true }
})

Related Documentation

• System Architecture - Overall system design
• Layered Architecture - Code organization
• API: Repositories - Data access patterns

Next Steps

• Review Repositories for database access patterns
• Explore System Architecture for context
• Check Security for data protection guidelines