> ## Documentation Index
> Fetch the complete documentation index at: https://docs.nullpass.xyz/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Schema

> Prisma schema and database structure

## Overview

Null Pass uses PostgreSQL with Prisma ORM for database management. This document describes the database schema and relationships.

## Prisma Schema

The complete schema is defined in `prisma/schema.prisma`:

```prisma theme={null}
generator client {
  provider = "prisma-client-js"
}

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

enum ServiceIdentifier {
  DROP
  MAILS
  VAULT
  DB
}

enum AuditAction {
  USER_LOGIN
  USER_LOGOUT
  USER_REGISTER
  PASSWORD_CHANGE
  TWO_FACTOR_ENABLE
  TWO_FACTOR_DISABLE
  USER_UPDATE
  USER_DELETE
  SESSION_CREATE
  SESSION_DELETE
  SERVICE_ACCESS_GRANT
  SERVICE_ACCESS_REVOKE
  SERVICE_TIER_CHANGE
  SUBSCRIPTION_CREATE
  SUBSCRIPTION_UPDATE
  SUBSCRIPTION_CANCEL
  SUBSCRIPTION_REVOKE
  USER_BAN
  USER_DISABLE
  SERVICE_ENTITLEMENT_DISCONNECT
  SERVICE_ENTITLEMENT_CONNECT
  UNKNOWN
}

model User {
  id                String                     @id @default(cuid())
  email             String                     @unique
  passwordHash      String?
  avatar            String?                    @db.Text
  displayName       String?
  twoFactorEnabled  Boolean                    @default(false)
  banned            Boolean                    @default(false)
  disabled          Boolean                    @default(false)
  twoFactorSecret   String?
  createdAt         DateTime                   @default(now())
  updatedAt         DateTime                   @updatedAt
  migraited         Boolean                    @default(false)
  sessions          Session[]
  serviceAccess     UserServiceEntitlement[]
  auditLogs         AuditLog[]
  
  @@map("users")
}

model Session {
  id        String   @id @default(cuid())
  userId    String
  ip        String
  token     String   @unique
  expiresAt DateTime
  createdAt DateTime @default(now())
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  @@map("sessions")
  @@index([userId])
  @@index([expiresAt])
}

model AuditLog {
  id        String      @id @default(cuid())
  action    AuditAction @default(UNKNOWN)
  data      Json        @default("{}")
  userId    String
  createdAt DateTime    @default(now())
  user      User        @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  @@map("audit_logs")
  @@index([userId])
  @@index([action])
  @@index([createdAt])
}

model UserServiceEntitlement {
  id                      String            @id @default(cuid())
  userId                  String
  service                 ServiceIdentifier
  tier                    String            @default("free")
  isPremium               Boolean           @default(false)
  accessFlags             Json?
  metadata                Json?
  connected               Boolean           @default(true)
  customStorageLimit      Int?
  customApiKeyLimit       Int?
  polarCustomerId         String?
  polarSubscriptionId     String?
  polarSubscriptionStatus String?
  createdAt               DateTime          @default(now())
  updatedAt               DateTime          @updatedAt
  user                    User              @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  @@map("user_service_entitlements")
  @@unique([userId, service])
  @@index([service])
}
```

## Models

### User

Core user account model.

**Fields:**

* `id`: CUID primary key
* `email`: Unique email address
* `passwordHash`: bcrypt hash (nullable for OAuth users)
* `avatar`: Avatar URL (text field)
* `displayName`: User display name
* `twoFactorEnabled`: 2FA status
* `twoFactorSecret`: TOTP secret (base32)
* `banned`: Account ban flag
* `disabled`: Account disable flag
* `migraited`: Migration flag (LEGACY)
* `createdAt`: Creation timestamp
* `updatedAt`: Last update timestamp

**Relations:**

* `sessions`: One-to-many with Session
* `serviceAccess`: One-to-many with UserServiceEntitlement
* `auditLogs`: One-to-many with AuditLog

### Session

User session management.

**Fields:**

* `id`: CUID primary key
* `userId`: Foreign key to User
* `ip`: Encrypted IP address
* `token`: JWT token (unique)
* `expiresAt`: Expiration timestamp
* `createdAt`: Creation timestamp

**Indexes:**

* `userId`: For user session queries
* `expiresAt`: For expiration cleanup

**Cascade:** Deleted when user is deleted

### AuditLog

Audit trail for all actions.

**Fields:**

* `id`: CUID primary key
* `action`: AuditAction enum
* `data`: JSON metadata
* `userId`: Foreign key to User
* `createdAt`: Timestamp

**Indexes:**

* `userId`: For user audit queries
* `action`: For action type queries
* `createdAt`: For time-based queries

**Cascade:** Deleted when user is deleted

### UserServiceEntitlement

Service access entitlements.

**Fields:**

* `id`: CUID primary key
* `userId`: Foreign key to User
* `service`: ServiceIdentifier enum
* `tier`: Access tier string
* `isPremium`: Premium flag
* `accessFlags`: JSON object
* `metadata`: JSON object
* `connected`: Connection status
* `customStorageLimit`: Custom storage limit (bytes)
* `customApiKeyLimit`: Custom API key limit
* `polarCustomerId`: Polar customer ID
* `polarSubscriptionId`: Polar subscription ID
* `polarSubscriptionStatus`: Polar subscription status
* `createdAt`: Creation timestamp
* `updatedAt`: Last update timestamp

**Unique Constraint:** `userId` + `service`

**Indexes:**

* `service`: For service-based queries

**Cascade:** Deleted when user is deleted

## Migrations

Migrations are managed with Prisma:

```bash theme={null}
# Create migration
npm run prisma:migrate

# Generate Prisma Client
npm run prisma:generate

# Open Prisma Studio
npm run prisma:studio
```

## Database Operations

### Prisma Client Usage

```typescript theme={null}
import { prisma } from '@/lib/prisma'

// Find user
const user = await prisma.user.findUnique({
  where: { email: 'user@example.com' }
})

// Create session
const session = await prisma.session.create({
  data: {
    userId: user.id,
    token: jwtToken,
    expiresAt: expiresAt,
    ip: encryptedIp
  }
})

// Create audit log
await prisma.auditLog.create({
  data: {
    userId: user.id,
    action: 'USER_LOGIN',
    data: { ip: encryptedIp }
  }
})
```

## Indexes

Indexes are defined for performance:

* `sessions.userId`: Fast user session lookup
* `sessions.expiresAt`: Expiration cleanup queries
* `audit_logs.userId`: User audit queries
* `audit_logs.action`: Action type filtering
* `audit_logs.createdAt`: Time-based queries
* `user_service_entitlements.service`: Service filtering

## Constraints

* `users.email`: Unique constraint
* `sessions.token`: Unique constraint
* `user_service_entitlements.userId_service`: Unique constraint

## Cascade Deletes

When a user is deleted:

* All sessions are deleted
* All audit logs are deleted
* All service entitlements are deleted

This ensures data consistency and prevents orphaned records.