# Milestone 2: Database Foundation

## Objective

Define the database foundation for the Project Management System. This milestone establishes schema conventions, common columns, ID strategy, audit fields, RBAC tables, user tables, developer profile tables, client records, developer rate history, indexes, constraints, and initial Prisma modeling rules.

This milestone is intentionally limited to the foundation layer. Project, milestone, sprint, task, reporting, notification, costing detail, and calendar tables will be added in later milestones.

## Database Standards

### Database Engine

- MySQL
- Prisma ORM
- `provider = "mysql"`
- UTF-8 compatible character set
- InnoDB storage engine

Recommended database defaults:

```sql
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci
ENGINE = InnoDB
```

### Naming Rules

| Item | Rule | Example |
| --- | --- | --- |
| Tables | plural, camelCase | `developerProfiles` |
| Columns | camelCase | `createdAt` |
| Primary keys | `id` | `id` |
| Foreign keys | referenced entity + `Id` | `userId` |
| Booleans | `is` or `has` prefix | `isActive` |
| Timestamps | `At` suffix | `createdAt` |
| Dates | `Date` suffix | `joiningDate` |
| Amounts | explicit purpose | `costPerHour` |

### ID Strategy

Use Prisma-compatible string IDs.

Recommended:

```text
VARCHAR(191)
```

The application should generate IDs using CUID or UUID. CUID is preferred for Prisma ergonomics and distributed generation.

Prisma pattern:

```prisma
id String @id @default(cuid()) @db.VarChar(191)
```

### Common Columns

Most business tables should include these columns where applicable:

| ColumnName | DataType | Nullable | Default | Description |
| --- | --- | --- | --- | --- |
| id | VARCHAR(191) | No | generated by app/Prisma | Primary key |
| createdBy | VARCHAR(191) | Yes | NULL | User who created the record |
| updatedBy | VARCHAR(191) | Yes | NULL | User who last updated the record |
| createdAt | DATETIME | No | CURRENT_TIMESTAMP | Creation timestamp |
| updatedAt | DATETIME | No | CURRENT_TIMESTAMP | Last update timestamp |
| deletedAt | DATETIME | Yes | NULL | Soft delete timestamp |
| isActive | TINYINT(1) | No | 1 | Active/inactive flag |

Authentication and join tables may omit some audit fields when they add noise without value.

### Timestamp Rules

- Use `DATETIME`, not `TIMESTAMP`.
- Store timestamps in UTC.
- Convert to user timezone in the frontend.
- Use `createdAt` and `updatedAt` consistently.
- Use `deletedAt` for soft deletes instead of hard deleting important records.

### Money and Rate Rules

| Use Case | Type |
| --- | --- |
| Rates | DECIMAL(10,2) |
| Budgets and totals | DECIMAL(15,2) |
| Percentages | DECIMAL(5,2) |
| Hours | DECIMAL(8,2) |

### JSON Usage

Use JSON only where structure is flexible and not frequently filtered.

Good JSON candidates:
- `skills`
- `technologyStack`
- `metadata`
- `preferences`

Avoid JSON for core relational data such as users, roles, project members, task assignment, or permissions.

## Foundation Tables

Milestone 2 defines these tables:

| Table | Purpose |
| --- | --- |
| `users` | System user accounts |
| `refreshTokens` | Refresh token rotation and session tracking |
| `roles` | Named system roles |
| `permissions` | Atomic access permissions |
| `rolePermissions` | Role to permission mapping |
| `userRoles` | User to role mapping |
| `developerProfiles` | Developer-specific capacity and skill data |
| `developerRates` | Historical developer cost and billing rates |
| `clients` | Client companies or individuals |

## users

Stores login identity and general user profile data.

| ColumnName | DataType | Nullable | PK | FK | Default | Description |
| --- | --- | --- | --- | --- | --- | --- |
| id | VARCHAR(191) | No | Yes | No | cuid | Primary key |
| firstName | VARCHAR(100) | No | No | No |  | User first name |
| lastName | VARCHAR(100) | Yes | No | No | NULL | User last name |
| email | VARCHAR(191) | No | No | No |  | Login email |
| passwordHash | VARCHAR(255) | No | No | No |  | Hashed password |
| phone | VARCHAR(30) | Yes | No | No | NULL | Contact number |
| avatarUrl | VARCHAR(500) | Yes | No | No | NULL | Profile image URL |
| status | ENUM('ACTIVE','INVITED','SUSPENDED','DEACTIVATED') | No | No | No | 'ACTIVE' | User account status |
| emailVerifiedAt | DATETIME | Yes | No | No | NULL | Email verification timestamp |
| lastLoginAt | DATETIME | Yes | No | No | NULL | Last successful login |
| createdBy | VARCHAR(191) | Yes | No | users.id | NULL | Creator user |
| updatedBy | VARCHAR(191) | Yes | No | users.id | NULL | Last updater user |
| createdAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Creation timestamp |
| updatedAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Last update timestamp |
| deletedAt | DATETIME | Yes | No | No | NULL | Soft delete timestamp |
| isActive | TINYINT(1) | No | No | No | 1 | Active flag |

### Relationships

- A user can have many roles through `userRoles`.
- A user can have one `developerProfile`.
- A user can have many `developerRates` if the user is a developer.
- A user can create and update records across the system.

### Indexes

- `idxUsersStatus` on `status`
- `idxUsersIsActive` on `isActive`
- `idxUsersDeletedAt` on `deletedAt`

### Unique Constraints

- `uqUsersEmail` on `email`

## refreshTokens

Stores refresh token sessions for rotation, revocation, and auditability. Store only a hash of the refresh token, never the raw token.

| ColumnName | DataType | Nullable | PK | FK | Default | Description |
| --- | --- | --- | --- | --- | --- | --- |
| id | VARCHAR(191) | No | Yes | No | cuid | Primary key |
| userId | VARCHAR(191) | No | No | users.id |  | Owner user |
| tokenHash | VARCHAR(255) | No | No | No |  | Hashed refresh token |
| familyId | VARCHAR(191) | No | No | No |  | Token family for rotation detection |
| expiresAt | DATETIME | No | No | No |  | Token expiry |
| revokedAt | DATETIME | Yes | No | No | NULL | Revocation timestamp |
| replacedByTokenId | VARCHAR(191) | Yes | No | refreshTokens.id | NULL | Next rotated token |
| ipAddress | VARCHAR(45) | Yes | No | No | NULL | IPv4 or IPv6 address |
| userAgent | VARCHAR(500) | Yes | No | No | NULL | Login device user agent |
| createdAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Creation timestamp |

### Relationships

- Each refresh token belongs to one user.
- A token may reference the token that replaced it.

### Indexes

- `idxRefreshTokensUserId` on `userId`
- `idxRefreshTokensFamilyId` on `familyId`
- `idxRefreshTokensExpiresAt` on `expiresAt`
- `idxRefreshTokensRevokedAt` on `revokedAt`

### Unique Constraints

- `uqRefreshTokensTokenHash` on `tokenHash`

## roles

Stores assignable system roles.

| ColumnName | DataType | Nullable | PK | FK | Default | Description |
| --- | --- | --- | --- | --- | --- | --- |
| id | VARCHAR(191) | No | Yes | No | cuid | Primary key |
| name | VARCHAR(100) | No | No | No |  | Display name |
| slug | VARCHAR(100) | No | No | No |  | Stable role key |
| description | TEXT | Yes | No | No | NULL | Role description |
| isSystem | TINYINT(1) | No | No | No | 0 | Prevent accidental deletion |
| createdBy | VARCHAR(191) | Yes | No | users.id | NULL | Creator user |
| updatedBy | VARCHAR(191) | Yes | No | users.id | NULL | Last updater user |
| createdAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Creation timestamp |
| updatedAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Last update timestamp |
| deletedAt | DATETIME | Yes | No | No | NULL | Soft delete timestamp |
| isActive | TINYINT(1) | No | No | No | 1 | Active flag |

### Default Roles

- `admin`
- `projectManager`
- `teamLeader`
- `teamMember`

### Indexes

- `idxRolesIsSystem` on `isSystem`
- `idxRolesIsActive` on `isActive`

### Unique Constraints

- `uqRolesSlug` on `slug`

## permissions

Stores atomic permissions used by RBAC middleware.

| ColumnName | DataType | Nullable | PK | FK | Default | Description |
| --- | --- | --- | --- | --- | --- | --- |
| id | VARCHAR(191) | No | Yes | No | cuid | Primary key |
| module | VARCHAR(100) | No | No | No |  | Module name |
| action | VARCHAR(100) | No | No | No |  | Action name |
| key | VARCHAR(150) | No | No | No |  | Permission key, for example `project.create` |
| description | TEXT | Yes | No | No | NULL | Permission description |
| isSystem | TINYINT(1) | No | No | No | 1 | System-defined permission |
| createdAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Creation timestamp |
| updatedAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Last update timestamp |
| deletedAt | DATETIME | Yes | No | No | NULL | Soft delete timestamp |
| isActive | TINYINT(1) | No | No | No | 1 | Active flag |

### Indexes

- `idxPermissionsModule` on `module`
- `idxPermissionsAction` on `action`
- `idxPermissionsIsActive` on `isActive`

### Unique Constraints

- `uqPermissionsKey` on `key`
- `uqPermissionsModuleAction` on `module`, `action`

## rolePermissions

Maps roles to permissions.

| ColumnName | DataType | Nullable | PK | FK | Default | Description |
| --- | --- | --- | --- | --- | --- | --- |
| id | VARCHAR(191) | No | Yes | No | cuid | Primary key |
| roleId | VARCHAR(191) | No | No | roles.id |  | Role |
| permissionId | VARCHAR(191) | No | No | permissions.id |  | Permission |
| createdBy | VARCHAR(191) | Yes | No | users.id | NULL | User who granted permission |
| createdAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Creation timestamp |

### Relationships

- Many roles can have many permissions.

### Indexes

- `idxRolePermissionsRoleId` on `roleId`
- `idxRolePermissionsPermissionId` on `permissionId`

### Unique Constraints

- `uqRolePermissionsRolePermission` on `roleId`, `permissionId`

## userRoles

Maps users to roles.

| ColumnName | DataType | Nullable | PK | FK | Default | Description |
| --- | --- | --- | --- | --- | --- | --- |
| id | VARCHAR(191) | No | Yes | No | cuid | Primary key |
| userId | VARCHAR(191) | No | No | users.id |  | User |
| roleId | VARCHAR(191) | No | No | roles.id |  | Role |
| assignedBy | VARCHAR(191) | Yes | No | users.id | NULL | User who assigned role |
| assignedAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Assignment timestamp |
| revokedAt | DATETIME | Yes | No | No | NULL | Role revocation timestamp |
| isActive | TINYINT(1) | No | No | No | 1 | Active role assignment |

### Relationships

- A user can have multiple roles.
- A role can belong to many users.

### Indexes

- `idxUserRolesUserId` on `userId`
- `idxUserRolesRoleId` on `roleId`
- `idxUserRolesIsActive` on `isActive`

### Unique Constraints

- `uqUserRolesUserRole` on `userId`, `roleId`

## developerProfiles

Stores developer-specific profile, skill, capacity, and workload planning data. Cost and billing rates are not stored here.

| ColumnName | DataType | Nullable | PK | FK | Default | Description |
| --- | --- | --- | --- | --- | --- | --- |
| id | VARCHAR(191) | No | Yes | No | cuid | Primary key |
| userId | VARCHAR(191) | No | No | users.id |  | Linked user |
| designation | VARCHAR(150) | Yes | No | No | NULL | Developer designation |
| experienceYears | DECIMAL(4,1) | Yes | No | No | NULL | Total experience |
| workingHoursPerDay | DECIMAL(4,2) | No | No | No | 8.00 | Standard working hours |
| availableHoursPerDay | DECIMAL(4,2) | No | No | No | 8.00 | Available capacity |
| skills | JSON | Yes | No | No | NULL | Skill list |
| joiningDate | DATE | Yes | No | No | NULL | Joining date |
| notes | TEXT | Yes | No | No | NULL | Internal notes |
| createdBy | VARCHAR(191) | Yes | No | users.id | NULL | Creator user |
| updatedBy | VARCHAR(191) | Yes | No | users.id | NULL | Last updater user |
| createdAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Creation timestamp |
| updatedAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Last update timestamp |
| deletedAt | DATETIME | Yes | No | No | NULL | Soft delete timestamp |
| isActive | TINYINT(1) | No | No | No | 1 | Active flag |

### Relationships

- Each developer profile belongs to one user.
- A developer profile does not store cost rates.

### Indexes

- `idxDeveloperProfilesUserId` on `userId`
- `idxDeveloperProfilesIsActive` on `isActive`

### Unique Constraints

- `uqDeveloperProfilesUserId` on `userId`

## developerRates

Stores historical cost and billing rates for developers. This table allows old project costs to remain accurate after future rate changes.

| ColumnName | DataType | Nullable | PK | FK | Default | Description |
| --- | --- | --- | --- | --- | --- | --- |
| id | VARCHAR(191) | No | Yes | No | cuid | Primary key |
| developerId | VARCHAR(191) | No | No | users.id |  | Developer user |
| costPerHour | DECIMAL(10,2) | No | No | No | 0.00 | Internal cost per hour |
| billingRatePerHour | DECIMAL(10,2) | No | No | No | 0.00 | Client billing rate per hour |
| currency | VARCHAR(10) | No | No | No | 'USD' | Rate currency |
| effectiveFrom | DATE | No | No | No |  | Start date for this rate |
| effectiveTo | DATE | Yes | No | No | NULL | End date for this rate |
| isCurrent | TINYINT(1) | No | No | No | 1 | Current active rate |
| createdBy | VARCHAR(191) | Yes | No | users.id | NULL | Creator user |
| updatedBy | VARCHAR(191) | Yes | No | users.id | NULL | Last updater user |
| createdAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Creation timestamp |
| updatedAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Last update timestamp |
| deletedAt | DATETIME | Yes | No | No | NULL | Soft delete timestamp |
| isActive | TINYINT(1) | No | No | No | 1 | Active flag |

### Relationships

- A developer can have many rates over time.
- Costing services select the rate where `workDate` is between `effectiveFrom` and `effectiveTo`, or where `effectiveTo` is NULL.

### Indexes

- `idxDeveloperRatesDeveloperId` on `developerId`
- `idxDeveloperRatesEffectiveRange` on `developerId`, `effectiveFrom`, `effectiveTo`
- `idxDeveloperRatesIsCurrent` on `developerId`, `isCurrent`

### Constraints

- Only one current rate should exist per developer.
- `effectiveTo` must be greater than or equal to `effectiveFrom` when present.
- New rate creation must close the previous current rate in the same transaction.

MySQL cannot reliably enforce a partial unique index for `isCurrent = 1` in every version. Enforce this in the service layer and optionally with a generated column in production if needed.

## clients

Stores client companies or individuals for project ownership and reporting.

| ColumnName | DataType | Nullable | PK | FK | Default | Description |
| --- | --- | --- | --- | --- | --- | --- |
| id | VARCHAR(191) | No | Yes | No | cuid | Primary key |
| name | VARCHAR(191) | No | No | No |  | Client name |
| code | VARCHAR(50) | Yes | No | No | NULL | Optional client code |
| contactName | VARCHAR(150) | Yes | No | No | NULL | Primary contact person |
| contactEmail | VARCHAR(191) | Yes | No | No | NULL | Contact email |
| contactPhone | VARCHAR(30) | Yes | No | No | NULL | Contact phone |
| companyWebsite | VARCHAR(500) | Yes | No | No | NULL | Client website |
| billingAddress | TEXT | Yes | No | No | NULL | Billing address |
| notes | TEXT | Yes | No | No | NULL | Internal notes |
| createdBy | VARCHAR(191) | Yes | No | users.id | NULL | Creator user |
| updatedBy | VARCHAR(191) | Yes | No | users.id | NULL | Last updater user |
| createdAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Creation timestamp |
| updatedAt | DATETIME | No | No | No | CURRENT_TIMESTAMP | Last update timestamp |
| deletedAt | DATETIME | Yes | No | No | NULL | Soft delete timestamp |
| isActive | TINYINT(1) | No | No | No | 1 | Active flag |

### Relationships

- A client can have many projects in later milestones.

### Indexes

- `idxClientsName` on `name`
- `idxClientsIsActive` on `isActive`
- `idxClientsDeletedAt` on `deletedAt`

### Unique Constraints

- `uqClientsCode` on `code`

## Initial Permission Catalog

Milestone 2 should seed the foundation permissions below. More permissions will be added as feature modules are delivered.

| Permission Key | Module | Action |
| --- | --- | --- |
| `user.create` | user | create |
| `user.view` | user | view |
| `user.update` | user | update |
| `user.delete` | user | delete |
| `role.create` | role | create |
| `role.view` | role | view |
| `role.update` | role | update |
| `role.delete` | role | delete |
| `permission.view` | permission | view |
| `permission.assign` | permission | assign |
| `client.create` | client | create |
| `client.view` | client | view |
| `client.update` | client | update |
| `client.delete` | client | delete |
| `developerProfile.manage` | developerProfile | manage |
| `developerRate.manage` | developerRate | manage |

## Default Role Permission Matrix

| Permission | Admin | Project Manager | Team Leader | Team Member |
| --- | --- | --- | --- | --- |
| `user.create` | Yes | No | No | No |
| `user.view` | Yes | Limited | Limited | Own |
| `user.update` | Yes | Limited | No | Own |
| `user.delete` | Yes | No | No | No |
| `role.create` | Yes | No | No | No |
| `role.view` | Yes | No | No | No |
| `role.update` | Yes | No | No | No |
| `role.delete` | Yes | No | No | No |
| `permission.view` | Yes | No | No | No |
| `permission.assign` | Yes | No | No | No |
| `client.create` | Yes | Yes | No | No |
| `client.view` | Yes | Yes | Limited | No |
| `client.update` | Yes | Yes | No | No |
| `client.delete` | Yes | No | No | No |
| `developerProfile.manage` | Yes | No | No | No |
| `developerRate.manage` | Yes | No | No | No |

`Limited` means access is constrained by assignment, team membership, or project membership once project tables exist.

## Prisma Foundation Models

```prisma
enum UserStatus {
  ACTIVE
  INVITED
  SUSPENDED
  DEACTIVATED
}

model User {
  id              String            @id @default(cuid()) @db.VarChar(191)
  firstName       String            @db.VarChar(100)
  lastName        String?           @db.VarChar(100)
  email           String            @unique @db.VarChar(191)
  passwordHash    String            @db.VarChar(255)
  phone           String?           @db.VarChar(30)
  avatarUrl       String?           @db.VarChar(500)
  status          UserStatus        @default(ACTIVE)
  emailVerifiedAt DateTime?
  lastLoginAt     DateTime?
  createdBy       String?           @db.VarChar(191)
  updatedBy       String?           @db.VarChar(191)
  createdAt       DateTime          @default(now())
  updatedAt       DateTime          @updatedAt
  deletedAt       DateTime?
  isActive        Boolean           @default(true)

  refreshTokens     RefreshToken[]
  userRoles         UserRole[]       @relation("UserRoleUser")
  assignedUserRoles UserRole[]       @relation("UserRoleAssignedBy")
  developerProfile  DeveloperProfile?
  developerRates    DeveloperRate[]  @relation("DeveloperRates")

  @@index([status])
  @@index([isActive])
  @@index([deletedAt])
  @@map("users")
}

model RefreshToken {
  id                String         @id @default(cuid()) @db.VarChar(191)
  userId            String         @db.VarChar(191)
  tokenHash         String         @unique @db.VarChar(255)
  familyId          String         @db.VarChar(191)
  expiresAt         DateTime
  revokedAt         DateTime?
  replacedByTokenId String?        @db.VarChar(191)
  ipAddress         String?        @db.VarChar(45)
  userAgent         String?        @db.VarChar(500)
  createdAt         DateTime       @default(now())

  user              User           @relation(fields: [userId], references: [id])
  replacedByToken   RefreshToken?  @relation("RefreshTokenRotation", fields: [replacedByTokenId], references: [id])
  replacedTokens    RefreshToken[] @relation("RefreshTokenRotation")

  @@index([userId])
  @@index([familyId])
  @@index([expiresAt])
  @@index([revokedAt])
  @@map("refreshTokens")
}

model Role {
  id              String           @id @default(cuid()) @db.VarChar(191)
  name            String           @db.VarChar(100)
  slug            String           @unique @db.VarChar(100)
  description     String?          @db.Text
  isSystem        Boolean          @default(false)
  createdBy       String?          @db.VarChar(191)
  updatedBy       String?          @db.VarChar(191)
  createdAt       DateTime         @default(now())
  updatedAt       DateTime         @updatedAt
  deletedAt       DateTime?
  isActive        Boolean          @default(true)

  rolePermissions RolePermission[]
  userRoles       UserRole[]

  @@index([isSystem])
  @@index([isActive])
  @@map("roles")
}

model Permission {
  id              String           @id @default(cuid()) @db.VarChar(191)
  module          String           @db.VarChar(100)
  action          String           @db.VarChar(100)
  key             String           @unique @db.VarChar(150)
  description     String?          @db.Text
  isSystem        Boolean          @default(true)
  createdAt       DateTime         @default(now())
  updatedAt       DateTime         @updatedAt
  deletedAt       DateTime?
  isActive        Boolean          @default(true)

  rolePermissions RolePermission[]

  @@unique([module, action])
  @@index([module])
  @@index([action])
  @@index([isActive])
  @@map("permissions")
}

model RolePermission {
  id           String     @id @default(cuid()) @db.VarChar(191)
  roleId       String     @db.VarChar(191)
  permissionId String     @db.VarChar(191)
  createdBy    String?    @db.VarChar(191)
  createdAt    DateTime   @default(now())

  role         Role       @relation(fields: [roleId], references: [id])
  permission   Permission @relation(fields: [permissionId], references: [id])

  @@unique([roleId, permissionId])
  @@index([roleId])
  @@index([permissionId])
  @@map("rolePermissions")
}

model UserRole {
  id         String    @id @default(cuid()) @db.VarChar(191)
  userId     String    @db.VarChar(191)
  roleId     String    @db.VarChar(191)
  assignedBy String?   @db.VarChar(191)
  assignedAt DateTime  @default(now())
  revokedAt  DateTime?
  isActive   Boolean   @default(true)

  user       User      @relation("UserRoleUser", fields: [userId], references: [id])
  role       Role      @relation(fields: [roleId], references: [id])
  assigner   User?     @relation("UserRoleAssignedBy", fields: [assignedBy], references: [id])

  @@unique([userId, roleId])
  @@index([userId])
  @@index([roleId])
  @@index([isActive])
  @@map("userRoles")
}

model DeveloperProfile {
  id                   String    @id @default(cuid()) @db.VarChar(191)
  userId               String    @unique @db.VarChar(191)
  designation          String?   @db.VarChar(150)
  experienceYears      Decimal?  @db.Decimal(4, 1)
  workingHoursPerDay   Decimal   @default(8.00) @db.Decimal(4, 2)
  availableHoursPerDay Decimal   @default(8.00) @db.Decimal(4, 2)
  skills               Json?
  joiningDate          DateTime? @db.Date
  notes                String?   @db.Text
  createdBy            String?   @db.VarChar(191)
  updatedBy            String?   @db.VarChar(191)
  createdAt            DateTime  @default(now())
  updatedAt            DateTime  @updatedAt
  deletedAt            DateTime?
  isActive             Boolean   @default(true)

  user                 User      @relation(fields: [userId], references: [id])

  @@index([userId])
  @@index([isActive])
  @@map("developerProfiles")
}

model DeveloperRate {
  id                 String    @id @default(cuid()) @db.VarChar(191)
  developerId        String    @db.VarChar(191)
  costPerHour        Decimal   @default(0.00) @db.Decimal(10, 2)
  billingRatePerHour Decimal   @default(0.00) @db.Decimal(10, 2)
  currency           String    @default("USD") @db.VarChar(10)
  effectiveFrom      DateTime  @db.Date
  effectiveTo        DateTime? @db.Date
  isCurrent          Boolean   @default(true)
  createdBy          String?   @db.VarChar(191)
  updatedBy          String?   @db.VarChar(191)
  createdAt          DateTime  @default(now())
  updatedAt          DateTime  @updatedAt
  deletedAt          DateTime?
  isActive           Boolean   @default(true)

  developer          User      @relation("DeveloperRates", fields: [developerId], references: [id])

  @@index([developerId])
  @@index([developerId, effectiveFrom, effectiveTo])
  @@index([developerId, isCurrent])
  @@map("developerRates")
}

model Client {
  id             String    @id @default(cuid()) @db.VarChar(191)
  name           String    @db.VarChar(191)
  code           String?   @unique @db.VarChar(50)
  contactName    String?   @db.VarChar(150)
  contactEmail   String?   @db.VarChar(191)
  contactPhone   String?   @db.VarChar(30)
  companyWebsite String?   @db.VarChar(500)
  billingAddress String?   @db.Text
  notes          String?   @db.Text
  createdBy      String?   @db.VarChar(191)
  updatedBy      String?   @db.VarChar(191)
  createdAt      DateTime  @default(now())
  updatedAt      DateTime  @updatedAt
  deletedAt      DateTime?
  isActive       Boolean   @default(true)

  @@index([name])
  @@index([isActive])
  @@index([deletedAt])
  @@map("clients")
}
```

## Data Integrity Rules

- Never physically delete users, roles, permissions, clients, developer profiles, or developer rates from normal application flows.
- Use `deletedAt` and `isActive` together for business-level deactivation.
- Permission keys must be stable and should not be renamed after use in route middleware.
- System roles and permissions should not be deletable from the UI.
- Refresh tokens must be revocable by user, token family, or individual token.
- Developer rate updates must be transactional.
- Rate history must not be overwritten because historical costing depends on it.

## Service-Level Rules

### Creating a User

1. Validate email uniqueness.
2. Hash password.
3. Create user.
4. Assign one or more roles.
5. Create developer profile if the selected role requires developer capacity tracking.
6. Write activity log in a later milestone.

### Updating a Developer Rate

1. Start transaction.
2. Find current developer rate.
3. Set previous rate `effectiveTo` to the day before the new `effectiveFrom`.
4. Mark previous rate `isCurrent = false`.
5. Insert new rate with `isCurrent = true`.
6. Commit transaction.

### Authenticating a User

1. Find active user by email.
2. Verify password hash.
3. Load active user roles.
4. Load permissions through role mappings.
5. Issue access token.
6. Create hashed refresh token record.

## Milestone 2 Completion Criteria

Milestone 2 is complete when the following are accepted:

- Database naming conventions are defined.
- ID and timestamp standards are defined.
- Common audit columns are defined.
- Core identity tables are defined.
- RBAC tables are defined.
- Developer profile and developer rate tables are defined.
- Client table is defined.
- Initial permission catalog is defined.
- Default role permission matrix is defined.
- Initial Prisma model patterns are defined.

## Next Milestone

Milestone 3 should deliver Authentication and RBAC implementation design:

- Auth routes
- JWT and refresh token flow
- Password hashing rules
- Login/logout/session APIs
- RBAC middleware
- Permission guard helpers
- Project-level access strategy foundation
- Seed data for roles and permissions