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

# Database Schema

> Complete reference for all tables and relationships in CharleOS

The CharleOS database schema is defined in `lib/db/schema.ts` with **47 tables** organized into functional groups. This page provides a comprehensive reference.

## Schema Overview

### Entity Relationships

```
User (team)
├── assigns → Tasks
├── schedules → Subtasks
└── logs → Time Entries

Client
├── subscribes to → Plan
├── has → ClientUsers (portal access)
├── requests → Quotes
│   └── convert to → Tasks
├── owns → Projects
├── receives → Blocks (scheduled work)
└── tracked in → ClientPeriods (billing)

Task
├── contains → Subtasks (phases)
├── tracks → TimeEntries
├── part of → Project (optional)
└── converts from → Quote

Quote
├── contains → QuoteRequirements
└── approved by → Client
```

## Core Tables

### Authentication & Users

#### `user` - Team Members

Internal team members who use CharleOS.

| Column                | Type      | Description                                       |
| --------------------- | --------- | ------------------------------------------------- |
| `id`                  | text      | Primary key (CUID)                                |
| `name`                | text      | Full name                                         |
| `email`               | text      | Email (unique, @charle.co.uk)                     |
| `status`              | enum      | `pending`, `sent`, `active`, `deactivated`        |
| `workType`            | enum      | `development`, `design`, `pm`, `csm`, `qa`, `sdr` |
| `accessLevel`         | enum      | `admin`, `manager`, `staff`                       |
| `hasBillableCapacity` | boolean   | Track for utilization metrics?                    |
| `hasExecutiveAccess`  | boolean   | Access to commercials dashboard?                  |
| `engagementType`      | enum      | `retainer`, `project` (for ICs)                   |
| `departmentId`        | text      | Department assignment                             |
| `reportsToId`         | text      | Manager (references `user.id`)                    |
| `slackUserId`         | text      | Cached Slack user ID                              |
| `birthday`            | text      | Format: MM-DD                                     |
| `joinDate`            | timestamp | Employment start date                             |

**Access Levels:**

* **Admin**: Full system access (Luke)
* **Manager**: Management access (Simon, Andre, Ben, Nic)
* **Staff**: Standard access (developers, designers, PMs, CSMs)

**Work Types:**

* **development**, **design**, **qa**: Individual Contributors (ICs)
* **pm**: Project Managers
* **csm**: Client Success Managers
* **sdr**: Sales Development Representatives

#### `session` & `account`

Better Auth tables for session management and OAuth accounts.

* `session`: Active login sessions (expires after 7 days)
* `account`: OAuth provider connections (Google)

#### `user_preference`

User UI preferences (key-value store).

| Column   | Type | Description                                |
| -------- | ---- | ------------------------------------------ |
| `userId` | text | References `user.id`                       |
| `key`    | text | Preference name (e.g., "sidebarCollapsed") |
| `value`  | text | JSON string value                          |

### Client Management

#### `client` - Client Companies

Companies that hire Charle for work.

| Column                 | Type      | Description                         |
| ---------------------- | --------- | ----------------------------------- |
| `id`                   | text      | Primary key                         |
| `name`                 | text      | Company name                        |
| `slug`                 | text      | URL-friendly identifier (unique)    |
| `csmId`                | text      | Assigned CSM (references `user.id`) |
| `planId`               | text      | Pricing plan (references `plan.id`) |
| `monthlyHoursOverride` | integer   | Override plan hours                 |
| `monthlyCostOverride`  | integer   | Override plan cost (pence)          |
| `startDate`            | timestamp | Client start date                   |
| `status`               | enum      | `active`, `archived`                |
| `currentRagStatus`     | enum      | `green`, `amber`, `red`             |
| `slackChannelId`       | text      | Slack integration channel           |

#### `client_period` - Billing Periods

Tracks budget usage per billing period (monthly).

| Column             | Type      | Description                                                 |
| ------------------ | --------- | ----------------------------------------------------------- |
| `clientId`         | text      | References `client.id`                                      |
| `periodStart`      | timestamp | Start of period                                             |
| `periodEnd`        | timestamp | End of period                                               |
| `allocatedMinutes` | integer   | Budget for period                                           |
| `usedMinutes`      | integer   | Actual time logged                                          |
| `soldDayRate`      | integer   | Calculated sold rate (pence)                                |
| `actualDayRate`    | integer   | Calculated actual rate (pence)                              |
| `rolloverMinutes`  | integer   | Hour rollover from previous period (positive = client owes) |

#### `client_deliverability_score` - Scoring

Tracks how actual task times compare to quoted estimates per client.

| Column             | Type      | Description                                    |
| ------------------ | --------- | ---------------------------------------------- |
| `clientId`         | text      | References `client.id`                         |
| `requirementType`  | text      | `development` or `design`                      |
| `score`            | real      | Ratio (1.15 = 15% slower than quoted)          |
| `sampleSize`       | integer   | Number of tasks used to calculate              |
| `confidence`       | enum      | `low`, `medium`, `high` (based on sample size) |
| `lastCalculatedAt` | timestamp | When score was last updated                    |

**Score interpretation:**

* `1.00` = Tasks match estimates exactly
* `1.15` = Tasks take 15% longer than quoted
* `0.90` = Tasks complete 10% faster than quoted

**Confidence levels:**

* `low` (\< 5 tasks): Don't use for scheduling
* `medium` (5-15 tasks): Use with caution
* `high` (15+ tasks): Reliable for scheduling

#### `client_score_history` - Score Audit

Audit trail for score changes.

| Column            | Type | Description                                            |
| ----------------- | ---- | ------------------------------------------------------ |
| `clientId`        | text | References `client.id`                                 |
| `requirementType` | text | `development` or `design`                              |
| `previousScore`   | real | Score before change (null for first entry)             |
| `newScore`        | real | Score after change                                     |
| `reason`          | enum | `auto_calculation`, `manual_reset`, `outlier_excluded` |
| `changedById`     | text | User who made change (null for auto)                   |

#### `client_user` - Client Portal Users

Client employees who access the client portal.

| Column        | Type | Description                             |
| ------------- | ---- | --------------------------------------- |
| `clientId`    | text | Parent client company                   |
| `email`       | text | Email (unique)                          |
| `name`        | text | Full name                               |
| `role`        | enum | `admin`, `member`                       |
| `status`      | enum | `pending`, `sent`, `active`, `inactive` |
| `inviteToken` | text | Invitation token                        |

**Separate Auth System:**

* Client portal uses Better Auth with separate tables
* `client_session` for sessions
* `client_account` for credentials
* Completely isolated from team auth

#### `plan` - Pricing Tiers

Retainer plan configurations.

| Column         | Type    | Description                      |
| -------------- | ------- | -------------------------------- |
| `name`         | text    | Plan name (e.g., "Retainer 100") |
| `monthlyHours` | integer | Included hours per month         |
| `monthlyCost`  | integer | Cost in pence                    |

### Quote System

#### `quote` - Work Requests

Requests for work that go through approval workflow.

| Column          | Type    | Description                                                                                    |
| --------------- | ------- | ---------------------------------------------------------------------------------------------- |
| `displayId`     | integer | Sequential ID (QUOTE-123)                                                                      |
| `clientId`      | text    | Client requesting work                                                                         |
| `title`         | text    | Quote title                                                                                    |
| `description`   | text    | Brief description (rich text)                                                                  |
| `status`        | enum    | `awaiting_quote`, `in_progress`, `quoted`, `awaiting_client_approval`, `approved`, `cancelled` |
| `origin`        | enum    | `internal`, `client_request`                                                                   |
| `estimatedTime` | integer | Total estimated minutes                                                                        |
| `createdById`   | text    | Who created it                                                                                 |

**Quote Workflow:**

1. `awaiting_quote` - Created, needs scoping
2. `in_progress` - Being scoped by team
3. `quoted` - Ready for internal review
4. `awaiting_client_approval` - Sent to client
5. `approved` - Client approved (converts to task)
6. `cancelled` - Client cancelled

#### `quote_requirement` - Requirement Blocks

Individual work items within a quote.

| Column                  | Type      | Description                                      |
| ----------------------- | --------- | ------------------------------------------------ |
| `quoteId`               | text      | Parent quote                                     |
| `type`                  | enum      | `development`, `design`, `seo`, `retain`         |
| `title`                 | text      | Requirement title                                |
| `briefContent`          | text      | Brief description (rich text JSON)               |
| `scopeContent`          | text      | Detailed scope (rich text JSON)                  |
| `tshirtSize`            | enum      | `xs`, `s`, `m`, `l`, `xl`, `xxl`, `not_required` |
| `estimatedMinutes`      | integer   | Total time (from t-shirt size)                   |
| `workSplitCore`         | integer   | Core work minutes (80%)                          |
| `workSplitFeedback`     | integer   | QA/feedback minutes (20%)                        |
| `isComplete`            | boolean   | Scoping complete?                                |
| `scopedById`            | text      | Who scoped it                                    |
| `aiSuggestedTshirtSize` | enum      | AI's suggested t-shirt size                      |
| `aiSuggestedConfidence` | enum      | `low`, `medium`, `high`                          |
| `aiSuggestedReasoning`  | text      | AI's explanation                                 |
| `aiSuggestedAt`         | timestamp | When suggestion was generated                    |

**AI Suggestions:** When a quote requirement is created, Alan automatically generates a t-shirt size suggestion based on the brief and client history. The suggestion is stored against the requirement for team review.

**T-shirt Sizing:**

* XS: 15-30 min
* S: 30-60 min
* M: 1-4 hours
* L: 4-8 hours
* XL: 8-16 hours
* XXL: 16-32 hours

**80/20 Split:**

* Core work (80%) rounded down
* QA/feedback budget (20%) rounded up to nearest 15 min

### Task System

#### `task` - Approved Work

Work items created from approved quotes or directly.

| Column             | Type    | Description                          |
| ------------------ | ------- | ------------------------------------ |
| `displayId`        | integer | Sequential ID (TASK-123)             |
| `quoteId`          | text    | Source quote (if converted)          |
| `clientId`         | text    | Client (optional for internal)       |
| `projectId`        | text    | Parent project (optional)            |
| `title`            | text    | Task title                           |
| `description`      | text    | Task description (rich text)         |
| `pmId`             | text    | Assigned project manager             |
| `priority`         | integer | Priority (1-5)                       |
| `status`           | enum    | Task status                          |
| `estimatedMinutes` | integer | Total estimate                       |
| `isScoreOutlier`   | boolean | Exclude from deliverability scoring? |
| `outlierReason`    | text    | Why task is flagged as outlier       |

**Outlier Tasks:**
Tasks that deviate significantly (>130%) from quoted can be flagged as outliers. Outliers are excluded from client deliverability score calculations to prevent anomalous tasks from skewing the data.

#### `subtask` - Task Phases

Individual work phases within a task.

| Column             | Type    | Description                                                                      |
| ------------------ | ------- | -------------------------------------------------------------------------------- |
| `taskId`           | text    | Parent task                                                                      |
| `type`             | enum    | Subtask type (see below)                                                         |
| `phase`            | enum    | `design`, `development`, `qa`, `deployment`                                      |
| `status`           | enum    | `awaiting_scheduling`, `scheduled`, `in_progress`, `awaiting_review`, `complete` |
| `assigneeId`       | text    | Who's doing the work                                                             |
| `estimatedMinutes` | integer | Time estimate                                                                    |
| `actualMinutes`    | integer | Actual time logged                                                               |
| `iteration`        | integer | Feedback cycle number                                                            |
| `reviewOutcome`    | enum    | `pending`, `approved`, `rejected`                                                |

**Subtask Types:**

Design Phase:

* `design` - Initial design work
* `client_design_feedback` - Feedback round

Development Phase:

* `development` - Build work
* `internal_qa_fixes` - Internal QA fixes
* `external_qa_fixes` - Client QA fixes

QA Phase:

* `internal_qa_review` - Internal review
* `external_qa_review` - Client review

Deployment:

* `deployment` - Deploy to production

### Time Tracking

#### `time_entry` - Logged Time

Time logged against clients, tasks, and subtasks.

| Column        | Type    | Description                |
| ------------- | ------- | -------------------------- |
| `userId`      | text    | Who logged the time        |
| `clientId`    | text    | Client being billed        |
| `taskId`      | text    | Related task (optional)    |
| `subtaskId`   | text    | Related subtask (optional) |
| `duration`    | integer | Minutes worked             |
| `date`        | date    | Date of work               |
| `description` | text    | Work description           |
| `category`    | text    | Type of work               |

### Projects

#### `project` - Multi-Phase Projects

Long-running projects with budgeted phases.

| Column               | Type    | Description                                       |
| -------------------- | ------- | ------------------------------------------------- |
| `clientId`           | text    | Client                                            |
| `name`               | text    | Project name                                      |
| `description`        | text    | Description                                       |
| `status`             | enum    | `planning`, `in_progress`, `on_hold`, `completed` |
| `totalBudgetMinutes` | integer | Total allocated time                              |
| `usedMinutes`        | integer | Time used across tasks                            |

#### `project_phase` - Budgeted Phases

Budget allocations within a project.

| Column          | Type    | Description    |
| --------------- | ------- | -------------- |
| `projectId`     | text    | Parent project |
| `name`          | text    | Phase name     |
| `budgetMinutes` | integer | Allocated time |
| `usedMinutes`   | integer | Time used      |
| `status`        | enum    | Phase status   |

### Scheduling

#### `client_block` - Scheduled Work

Blocks of scheduled time on the calendar.

| Column      | Type | Description                                         |
| ----------- | ---- | --------------------------------------------------- |
| `clientId`  | text | Client                                              |
| `userId`    | text | Assigned user                                       |
| `subtaskId` | text | Related subtask (optional)                          |
| `startDate` | date | Start date                                          |
| `endDate`   | date | End date                                            |
| `hours`     | real | Hours per day                                       |
| `type`      | enum | `subtask`, `retainer`, `placeholder`                |
| `status`    | enum | `scheduled`, `in_progress`, `complete`, `cancelled` |

### Metrics & Reporting

#### `metric_snapshot` - Daily Metrics

Daily snapshots of utilization, day rates, and efficiency.

| Column               | Type    | Description                              |
| -------------------- | ------- | ---------------------------------------- |
| `date`               | date    | Snapshot date                            |
| `entityType`         | enum    | `user`, `client`, `department`, `agency` |
| `entityId`           | text    | Entity identifier                        |
| `utilizationPercent` | integer | Utilization %                            |
| `soldDayRate`        | integer | Sold day rate (pence)                    |
| `actualDayRate`      | integer | Actual day rate (pence)                  |
| `bankedMinutes`      | integer | Efficiency gain                          |
| `overageMinutes`     | integer | Non-billable overage                     |

### Help Desk

#### `help_desk_ticket` - Support Tickets

Client support requests (converted to subtasks).

| Column                  | Type      | Description                         |
| ----------------------- | --------- | ----------------------------------- |
| `displayId`             | integer   | Ticket number                       |
| `clientId`              | text      | Client                              |
| `title`                 | text      | Ticket title                        |
| `description`           | text      | Description                         |
| `type`                  | enum      | `support`, `escalation`, `question` |
| `priority`              | enum      | `p1`, `p2`, `p3`, `p4`              |
| `status`                | enum      | Ticket status                       |
| `estimatedMinutes`      | integer   | PM's time estimate                  |
| `aiSuggestedMinutes`    | integer   | AI's suggested time                 |
| `aiSuggestedConfidence` | enum      | `low`, `medium`, `high`             |
| `aiSuggestedReasoning`  | text      | AI's explanation                    |
| `aiSuggestedAt`         | timestamp | When suggestion was generated       |

**AI Suggestions:** When a support ticket is created, Alan automatically generates a time estimate suggestion based on ticket content and client history. The suggestion is stored against the ticket for PM review.

#### `help_desk_deliverability_score` - Help Desk Scoring

Tracks how actual help desk ticket times compare to estimated times.

| Column             | Type      | Description                                |
| ------------------ | --------- | ------------------------------------------ |
| `clientId`         | text      | References `client.id` (unique per client) |
| `score`            | real      | Ratio (1.15 = 15% longer than estimated)   |
| `sampleSize`       | integer   | Number of tickets used to calculate        |
| `confidence`       | enum      | `low`, `medium`, `high`                    |
| `lastCalculatedAt` | timestamp | When score was last updated                |

Similar to `client_deliverability_score` but specifically for help desk tickets.

#### `help_desk_score_history` - Score Audit

Records changes to help desk scoring for audit purposes.

| Column          | Type | Description                        |
| --------------- | ---- | ---------------------------------- |
| `clientId`      | text | References `client.id`             |
| `previousScore` | real | Score before change                |
| `newScore`      | real | Score after change                 |
| `reason`        | enum | `auto_calculation`, `manual_reset` |
| `changedById`   | text | User who made manual change        |

### Annual Leave

#### `annual_leave` - Leave Requests

Team member leave requests and approvals.

| Column       | Type      | Description                                                      |
| ------------ | --------- | ---------------------------------------------------------------- |
| `userId`     | text      | Team member                                                      |
| `startDate`  | timestamp | Leave start                                                      |
| `endDate`    | timestamp | Leave end                                                        |
| `leaveType`  | enum      | `annual`, `sick`, `unpaid`, `compassionate`, `birthday`, `other` |
| `dayType`    | enum      | `full_day`, `half_day_am`, `half_day_pm`                         |
| `status`     | enum      | `pending`, `approved`, `rejected`, `cancelled`                   |
| `reviewerId` | text      | Who approved/rejected                                            |

### RAG Reports

#### `rag_report` - Client Status Reports

Weekly Red/Amber/Green client health reports.

| Column         | Type | Description             |
| -------------- | ---- | ----------------------- |
| `clientId`     | text | Client                  |
| `weekStarting` | date | Week start date         |
| `status`       | enum | `draft`, `completed`    |
| `ragStatus`    | enum | `green`, `amber`, `red` |
| `keyPoints`    | text | Summary points          |
| `risks`        | text | Risk assessment         |

## Enums Reference

### User & Access

```typescript theme={null}
type UserStatus = "pending" | "sent" | "active" | "deactivated";
type WorkType = "development" | "design" | "pm" | "csm" | "qa" | "sdr";
type AccessLevel = "admin" | "manager" | "staff";
type EngagementType = "retainer" | "project";
```

### Task & Subtask

```typescript theme={null}
type TaskStatus = "awaiting_scheduling" | "scheduled" | "in_progress" | 
                  "awaiting_review" | "complete" | "cancelled";
type SubtaskType = "design" | "client_design_feedback" | "development" | 
                   "internal_qa_fixes" | "external_qa_fixes" | 
                   "internal_qa_review" | "external_qa_review" | "deployment";
type TaskPhase = "design" | "development" | "qa" | "deployment";
```

### Quote

```typescript theme={null}
type QuoteStatus = "awaiting_quote" | "in_progress" | "quoted" |
                   "awaiting_client_approval" | "approved" | "cancelled";
type TshirtSize = "xs" | "s" | "m" | "l" | "xl" | "xxl" | "not_required";
```

## Type Inference

Drizzle automatically generates TypeScript types from the schema:

```typescript theme={null}
import { user, task } from "@/lib/db/schema";

// Select type (what you get from DB)
type User = typeof user.$inferSelect;

// Insert type (what you send to DB)
type NewUser = typeof user.$inferInsert;

// Usage
function processUser(user: User) {
  console.log(user.name); // ✅ TypeScript knows this exists
}
```

## Relationships

### Foreign Keys

All relationships use foreign keys with appropriate cascade rules:

* `onDelete: "cascade"` - Delete children when parent is deleted
* `onDelete: "set null"` - Set to null when parent is deleted
* Default: Restrict deletion if children exist

### Common Joins

```typescript theme={null}
// Task with client
const tasksWithClients = await db
  .select()
  .from(task)
  .leftJoin(client, eq(task.clientId, client.id));

// Subtask with task and assignee
const subtasksWithDetails = await db
  .select()
  .from(subtask)
  .leftJoin(task, eq(subtask.taskId, task.id))
  .leftJoin(user, eq(subtask.assigneeId, user.id));
```

## Modifying the Schema

<Steps>
  <Step title="Edit Schema File">
    Modify `lib/db/schema.ts` to add/change tables or columns
  </Step>

  <Step title="Test Locally">
    ```bash theme={null}
    npm run db:push
    ```

    Applies changes to local dev database instantly
  </Step>

  <Step title="Verify in Studio">
    ```bash theme={null}
    npm run db:studio
    ```

    Browse updated schema visually
  </Step>

  <Step title="Generate Migration">
    ```bash theme={null}
    npm run db:generate
    ```

    Creates SQL migration file for production
  </Step>

  <Step title="Commit & Deploy">
    Commit schema + migration files. Migrations run automatically on deployment.
  </Step>
</Steps>

## Related Documentation

<CardGroup cols={2}>
  <Card title="Database Overview" icon="database" href="/developer/database">
    Database architecture and tools
  </Card>

  <Card title="Drizzle ORM" icon="code" href="/developer/database/drizzle">
    Query syntax and patterns
  </Card>

  <Card title="Migrations" icon="arrow-right-arrow-left" href="/developer/database/migrations">
    Managing schema changes
  </Card>
</CardGroup>