Database Schema - 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:

awaiting_quote - Created, needs scoping
in_progress - Being scoped by team
quoted - Ready for internal review
awaiting_client_approval - Sent to client
approved - Client approved (converts to task)
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

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

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

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:

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

// 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

Edit Schema File

Modify lib/db/schema.ts to add/change tables or columns

Test Locally

npm run db:push

Applies changes to local dev database instantly

Verify in Studio

npm run db:studio

Browse updated schema visually

Generate Migration

npm run db:generate

Creates SQL migration file for production

Commit & Deploy

Commit schema + migration files. Migrations run automatically on deployment.

Database Overview

Database architecture and tools

Drizzle ORM

Query syntax and patterns

Migrations

Managing schema changes

​Schema Overview

​Entity Relationships

​Core Tables

​Authentication & Users

​user - Team Members

​session & account

​user_preference

​Client Management

​client - Client Companies

​client_period - Billing Periods

​client_deliverability_score - Scoring

​client_score_history - Score Audit

​client_user - Client Portal Users

​plan - Pricing Tiers

​Quote System

​quote - Work Requests

​quote_requirement - Requirement Blocks

​Task System

​task - Approved Work

​subtask - Task Phases

​Time Tracking

​time_entry - Logged Time

​Projects

​project - Multi-Phase Projects

​project_phase - Budgeted Phases

​Scheduling

​client_block - Scheduled Work

​Metrics & Reporting

​metric_snapshot - Daily Metrics

​Help Desk

​help_desk_ticket - Support Tickets

​help_desk_deliverability_score - Help Desk Scoring

​help_desk_score_history - Score Audit

​Annual Leave

​annual_leave - Leave Requests

​RAG Reports

​rag_report - Client Status Reports

​Enums Reference

​User & Access

​Task & Subtask

​Quote

​Type Inference

​Relationships

​Foreign Keys

​Common Joins

​Modifying the Schema

​Related Documentation