Database Schema

Last Updated: January 2026 Status Legend: ✅ IMPLEMENTED | 🚧 PARTIAL | 📋 PLANNED

Overview

The CareHub database manages:

• User accounts and roles ✅
• Member intake information ✅
• Cases and treatment tracking ✅
• Sessions and video appointments ✅
• Calendar and scheduling ✅
• Documents and compliance data ✅
• Billing and claims ✅
• Workflows and automation ✅
• Member engagement (activities, check-ins, feed) ✅

ORM: Prisma with PostgreSQL

---

Entity Relationship Diagram

┌──────────────┐       ┌──────────────┐       ┌──────────────┐
│    Users     │       │    Cases     │       │   Sessions   │
├──────────────┤       ├──────────────┤       ├──────────────┤
│ id           │───┐   │ id           │───┐   │ id           │
│ email        │   │   │ case_number  │   │   │ case_id      │──┐
│ firstName    │   ├──▶│ member_id    │   ├──▶│ provider_id  │  │
│ lastName     │   │   │ psych_id     │◀──┘   │ scheduledDate│  │
│ role         │   │   │ lpcc_id      │       │ twilioRoom*  │  │  NEW: Video
│ entraId      │   │   │ coordinator_id│      │ status       │  │
│ clerkId      │   │   │ intake_form_id│      │ transcriptUrl│  │  NEW: Transcription
└──────────────┘   │   │ status       │       └──────────────┘  │
        │          │   │ currentPhase │                         │
        ▼          │   └──────────────┘                         │
┌──────────────┐   │           │                                │
│ProviderSettings│  │           │                                │
│ Availability │   │           ▼                                │
│ TimeBlock    │   │   ┌──────────────┐       ┌──────────────┐  │
└──────────────┘   │   │  CarePlans   │       │  Documents   │  │
                   │   └──────────────┘       └──────────────┘  │
NEW: Calendar      │                                            │
                   │                                            │
┌──────────────┐   │   ┌──────────────┐       ┌──────────────┐  │
│  IntakeForm  │◀──┘   │  CheckIns    │       │   Activity   │  │
├──────────────┤       ├──────────────┤       ├──────────────┤  │
│ 80+ fields   │       │ anxietyLevel │ NEW   │ ipAddress    │  │
│ phq9/gad7    │       │ moodLevel    │       │ userAgent    │  │
└──────────────┘       └──────────────┘       └──────────────┘  │
                                                                │
┌──────────────┐       ┌──────────────┐       ┌──────────────┐  │
│ RFARequest   │ NEW   │ Discharge    │ NEW   │MemberActivity│ NEW
├──────────────┤       │ Summary      │       ├──────────────┤  │
│ status       │       ├──────────────┤       │ activityType │  │
│ psychReview  │       │ goalAchieve  │       │ memberResponse│ │
└──────────────┘       └──────────────┘       └──────────────┘  │
                                                                │
┌──────────────┐       ┌──────────────┐       ┌──────────────┐  │
│ BillingEntry │ NEW   │ClaimSubmission│ NEW  │ CaseFeedItem │ NEW
├──────────────┤       ├──────────────┤       ├──────────────┤  │
│ serviceCode  │       │ formData     │       │ type         │  │
│ chargeAmount │       │ totalCharges │       │ visibility   │  │
└──────────────┘       └──────────────┘       └──────────────┘  │
                                                                │
┌──────────────────────────────────────────────────────────────┐
│                    Workflow Engine (Inngest)                  │ NEW
├──────────────────────────────────────────────────────────────┤
│ Workflow → WorkflowStep → WorkflowConfig → WorkflowRun       │
└──────────────────────────────────────────────────────────────┘

---

Enums ✅

Core Enums

Enum	Values	Description
Role	ADMIN, INTAKE_COORDINATOR, PSYCHOLOGIST, LPCC, MEMBER	User roles
CaseStatus	PENDING_ASSIGNMENT, INTAKE, EVALUATION, ACTIVE_TREATMENT, DISCHARGED	Case lifecycle
CasePhase	PRE_INTAKE, INTAKE, EVALUATION, TREATMENT, DISCHARGE	Workflow phases
CaseType	PHYSICAL_MENTAL, MENTAL_MENTAL, MENTAL_PHYSICAL	CA WC claim types
RiskLevel	NOT_ASSESSED, LOW, MODERATE, HIGH	Clinical risk

Session Enums

Enum	Values	Description
SessionType	EVALUATION, INDIVIDUAL, GROUP, PHONE, TELEHEALTH	Session modality
SessionStatus	SCHEDULED, COMPLETED, NO_SHOW, CANCELLED	Session state

Clinical Enums

Enum	Values	Description
MMIStatus	NOT_YET, APPROACHING, AT_MMI	Maximum Medical Improvement
RTWStatus	RETURNED_FULL, RETURNED_MODIFIED, UNABLE, PENDING	Return to Work
WorkRestrictions	NONE, TEMPORARY, PERMANENT, UNABLE	Work limitations
IndustrialDetermination	INDUSTRIAL, NON_INDUSTRIAL, PENDING	Causation result
DischargeReason	COMPLETED, NON_COMPLIANT, TRANSFERRED, MEMBER_REQUEST	Exit reason

Calendar Enums ✅ NEW

Enum	Values	Description
DayOfWeek	SUNDAY-SATURDAY	Weekly scheduling
TimeBlockType	LUNCH, ADMIN, BREAK, PERSONAL, VACATION, SICK, MEETING, OTHER	Non-session blocks

Workflow Enums ✅ NEW

Enum	Values	Description
WorkflowStatus	ACTIVE, PAUSED, DISABLED	Workflow state
WorkflowRunStatus	PENDING, RUNNING, COMPLETED, FAILED, CANCELLED	Execution state
WorkflowConfigType	STRING, NUMBER, BOOLEAN, JSON, PROMPT	Config value types

Billing Enums ✅ NEW

Enum	Values	Description
BillingStatus	PENDING, READY_TO_BILL, SUBMITTED, ACCEPTED, REJECTED, PAID, etc.	Claim lifecycle
ServiceCode	CPT codes (90791, 90834, 90837, etc.)	Billable services
PlaceOfService	OFFICE, TELEHEALTH_HOME, TELEHEALTH_OTHER	CMS 1500 codes

RFA Enums ✅ NEW

Enum	Values	Description
RFAStatus	DRAFT, PENDING_PSYCH_REVIEW, PSYCH_APPROVED, PSYCH_DENIED, SUBMITTED, UR_APPROVED, UR_DENIED, UR_MODIFIED	Authorization workflow

Member Activity Enums ✅ NEW

Enum	Values	Description
MemberActivityStatus	ASSIGNED, IN_PROGRESS, COMPLETED, SKIPPED, EXPIRED	Activity state
MemberActivityType	THOUGHT_JOURNAL, BEHAVIORAL_ACTIVATION, RELAXATION, EXPOSURE_TASK, PSYCHOEDUCATION, WORKSHEET, SELF_ASSESSMENT, CUSTOM	Therapeutic activities

Feed Enums ✅ NEW

Enum	Values	Description
FeedItemType	MESSAGE_*, EVENT_*, AGENT_* (25+ types)	Activity feed categories
FeedAuthorType	HUMAN, AGENT, SYSTEM	Who created the item
FeedVisibility	INTERNAL, MEMBER_VISIBLE	Access control

---

Tables

Users ✅

Stores all system users (staff and members).

Field	Type	Required	Description	Status
id	UUID	Yes	Primary key	✅
email	String	Yes	Unique email	✅
firstName	String	Yes		✅
lastName	String	Yes		✅
role	Enum	Yes	Admin, IntakeCoordinator, Psychologist, LPCC, Member	✅
phone	String	No	E.164 format	✅
isActive	Boolean	Yes	Default true	✅
portalAccess	Boolean	Yes	For members, controls login	✅
accessToken	String	No	For member portal auth	✅
entraId	String	No	Microsoft Entra ID (staff SSO)	✅ NEW
clerkId	String	No	Clerk ID (member auth)	✅ NEW
intakeToken	String	No	Magic link token for intake	✅ NEW
intakeTokenExpiresAt	DateTime	No	Token expiration	✅ NEW
createdAt	DateTime	Yes	Auto	✅
updatedAt	DateTime	Yes	Auto	✅

Relations:

• casesAsMember, casesAsCoordinator, casesAsPsychologist, casesAsLpcc
• providerSettings, providerAvailability, timeBlocks (NEW - Calendar)
• rfaRequestsSubmitted, rfaRequestsReviewed (NEW - RFA)
• dischargesPrepared, dischargesAttested (NEW - Discharge)
• memberActivitiesAssigned, memberActivitiesCreated (NEW - Activities)
• feedItemsAuthored (NEW - Feed)

---

IntakeForm ✅

Stores member intake responses. 80+ fields organized by section.

Prefix	Section	Example Fields	Status
PI_	Personal Information	PI_FirstName, PI_LastName, PI_DOB	✅
CI_	Contact Information	CI_Phone, CI_Email, CI_Address	✅
EC_	Emergency Contact	EC_Name, EC_Phone, EC_Relationship	✅
EMP_	Employment History	EMP_Employer, EMP_StartDate	✅
CC_	Current Concerns	CC_PrimaryConcern, CC_OnsetDate	✅
MH_	Medical History	MH_Conditions, MH_Surgeries	✅
MHH_	Mental Health History	MHH_PreviousTreatment	✅
FH_	Family History	FH_FamilyConditions	✅
SU_	Substance Use	SU_AlcoholUse, SU_DrugUse	✅
SA_	Safety Assessment	SA_SuicidalIdeation	✅
TG_	Treatment Goals	TG_PrimaryGoal, TG_RTWGoal	✅
NIS_	Non-Industrial Stressors	NIS_FinancialStress	✅
PA_	Personnel Actions	PA_Discipline, PA_Termination	✅
CON_	Consent	CON_TreatmentConsent	✅
FA_	Functional Assessment	FA_Mobility, FA_WorkAbility	📋
MED_	Medications	MED_CurrentMeds (repeater)	📋
RH_	Relationship History	RH_CurrentStatus	📋
TH_	Trauma History	TH_TraumaEvents	📋
DU_	Document Upload	DU_MedicalRecords	📋

Screening Scores:

Field	Type	Description	Status
phq9_q1-q9	Number (0-3)	PHQ-9 individual questions	✅
phq9_score	Computed	Sum of PHQ-9 (0-27)	✅
gad7_q1-q7	Number (0-3)	GAD-7 individual questions	✅
gad7_score	Computed	Sum of GAD-7 (0-21)	✅

---

Cases ✅

Core case management table.

Field	Type	Required	Description	Status
id	UUID	Yes	Primary key	✅
caseNumber	String	Yes	Auto-generated (CH-2025-00001)	✅
memberId	FK→Users	Yes	The injured worker	✅
intakeFormId	FK→IntakeForm	No	Linked intake	✅
coordinatorId	FK→Users	No	Assigned coordinator	✅
psychologistId	FK→Users	No	Assigned psychologist	✅
lpccId	FK→Users	No	Assigned LPCC	✅
caseType	Enum	No	physical-mental, mental-mental, mental-physical	✅
status	Enum	Yes	Case lifecycle status	✅
currentPhase	Enum	Yes	Workflow phase	✅
riskLevel	Enum	No	Clinical risk level	✅
riskIndicators	Array	No	List of risk factors	✅
sessionsAuthorized	Number	Yes	Default 7	✅
sessionsUsed	Number	Yes	Default 0	✅

Causation Fields: ✅

Field	Type	Description
causePreExistingPct	Number	0-100
causePreExistingNarrative	Text
causeNonIndustrialPct	Number	0-100
causeNonIndustrialNarrative	Text
causePersonnelActionPct	Number	0-100
causePersonnelActionNarrative	Text
causeWorkExposurePct	Number	0-100
causeWorkExposureNarrative	Text
causeIndustrialDetermination	Enum	Industrial, Non-Industrial, Pending
causeClinicalRationale	Text
causeCompletedById	FK→Users
causeCompletedAt	DateTime

GAF Fields: ✅

Field	Type	Description
gafPreInjury	Number	1-100
gafInitial	Number	1-100
gafCurrent	Number	1-100
gafFinal	Number	1-100
gafRationale	Text

MMI Fields: ✅

Field	Type	Description
mmiStatus	Enum	Not Yet, Approaching, At MMI
mmiEstimatedDate	Date
mmiDate	Date
mmiPermanentImpairment	Boolean
mmiWpi	Number	0-100
mmiWorkRestrictions	Enum	None, Temporary, Permanent, Unable
mmiRestrictionDetails	Text
mmiRtwStatus	Enum	Return to work status
mmiFutureMedicalNeeded	Boolean

Relations:

• sessions, carePlans, checkIns, documents, authorizations, activities
• billingEntries (NEW)
• rfaRequests (NEW)
• dischargeSummary (NEW)
• memberActivities (NEW)
• feedItems (NEW)

---

Sessions ✅

Tracks all therapy sessions with video and transcription support.

Field	Type	Required	Description	Status
id	UUID	Yes	Primary key	✅
caseId	FK→Cases	Yes		✅
providerId	FK→Users	Yes	Psychologist or LPCC	✅
sessionNumber	Number	Yes	Sequential per case	✅
sessionType	Enum	Yes	Evaluation, Individual, etc.	✅
scheduledDate	DateTime	Yes		✅
scheduledTime	String	Yes	HH:MM format	✅
timezone	String	Yes	IANA timezone (default LA)	✅ NEW
duration	Number	Yes	Minutes (default 50)	✅
status	Enum	Yes	Scheduled, Completed, etc.	✅

Session Notes: ✅

Field	Type	Description
memberPresent	Boolean
startTime	Time	Actual start
endTime	Time	Actual end
painRating	Number	1-10
moodRating	Number	1-10
sessionNotes	Text
interventionsUsed	Array
homeworkAssigned	Text
riskLevel	Enum
nextSessionPlan	Text

Twilio Video Fields: ✅ NEW

Field	Type	Description
twilioRoomSid	String	Twilio room ID
twilioRoomName	String	Unique room name
twilioRoomStatus	String	scheduled, in-progress, completed
videoStartedAt	DateTime
videoEndedAt	DateTime
videoActualDuration	Int	Actual minutes
memberJoinToken	String	Secure member join link
providerJoinToken	String	Secure provider join link

Transcription Fields: ✅ NEW

Field	Type	Description
transcript	Text	Full transcript
transcriptUrl	String	Blob storage path
transcriptStatus	String	pending, processing, completed, failed

Reminder Tracking: ✅ NEW

Field	Type	Description
reminder24hSent	Boolean
reminderSessionSent	Boolean

Recording Consent (HIPAA): ✅ NEW

Field	Type	Description
recordingConsentAt	DateTime
recordingConsentIp	String
recordingConsentUserAgent	String

AI Fields: ✅

Field	Type	Description
aiSummary	Text
aiThemes	Array
aiGoalProgress	JSON
aiRecommendations	Text

---

CarePlans ✅

Treatment plans for each case.

Field	Type	Required	Description	Status
id	UUID	Yes	Primary key	✅
caseId	FK→Cases	Yes		✅
createdById	FK→Users	Yes		✅
version	Number	Yes	Start at 1	✅
status	Enum	Yes	Draft, Active, Completed, Superseded	✅
diagnosisCodes	Array	No	ICD-10 codes	✅
presentingIssues	Text	Yes		✅
treatmentGoals	JSON	Yes	Array of goal objects	✅
interventions	Text	Yes		✅
frequency	Enum	Yes	Weekly, Bi-weekly, Monthly, As Needed	✅
estimatedSessions	Number	Yes		✅
expectedOutcomes	Text	Yes		✅
rtwGoal	String	Yes		✅
rtwTimeline	String	No		✅
dischargeCriteria	Text	No		✅
barriersIdentified	Text	No		✅
aiGenerated	Boolean	Yes	Default false	✅
aiRecommendations	Text	No		✅

---

CheckIns ✅

Member self-reported wellness data.

Field	Type	Required	Description	Status
id	UUID	Yes	Primary key	✅
memberId	FK→Users	Yes		✅
caseId	FK→Cases	Yes		✅
checkinDate	Date	Yes		✅
painLevel	Number	Yes	1-10	✅
moodLevel	Number	Yes	1-10	✅
sleepQuality	Number	Yes	1-10	✅
anxietyLevel	Number	Yes	1-10 (North Star V2)	✅ NEW
activityLevel	Enum	Yes	Very Low - Very High	✅
notes	Text	No		✅

---

Documents ✅

Generated and uploaded documents.

Field	Type	Required	Description	Status
id	UUID	Yes	Primary key	✅
caseId	FK→Cases	No		✅
sessionId	FK→Sessions	No		✅
documentType	Enum	No	PR-2, DFR, RFA, etc.	✅
documentTitle	String	No		✅
generatedDate	DateTime	No		✅
contentJson	JSON	No	Structured content	✅
status	Enum	Yes	Draft, Final, Sent, Acknowledged	✅
sentTo	String	No		✅
sentDate	DateTime	No		✅
fileName	String	No	Upload filename	✅ NEW
fileType	String	No	MIME type	✅ NEW
blobPath	String	No	Azure Blob path	✅ NEW
containerName	String	No	Blob container	✅ NEW
size	Int	No	File size in bytes	✅ NEW
description	String	No	User description	✅ NEW
uploadedAt	DateTime	No		✅ NEW

---

Authorizations ✅

Session authorization tracking.

Field	Type	Required	Description	Status
id	UUID	Yes	Primary key	✅
caseId	FK→Cases	Yes		✅
authorizationType	Enum	Yes	Initial, Extension	✅
sessionsRequested	Number	Yes		✅
sessionsApproved	Number	No		✅
requestDate	DateTime	Yes		✅
responseDate	DateTime	No		✅
status	Enum	Yes	Pending, Approved, Denied, Partially Approved	✅
rfaDocumentId	FK→Documents	No		✅
decisionNotes	Text	No		✅
requestedById	FK→Users	Yes		✅

---

Activity (Audit Log) ✅

Audit log for case activities (HIPAA compliance).

Field	Type	Required	Description	Status
id	UUID	Yes	Primary key	✅
caseId	FK→Cases	No		✅
userId	FK→Users	Yes		✅
action	String	Yes	e.g., "case.created"	✅
details	Text	No		✅
metadata	JSON	No		✅
ipAddress	String	No	Client IP	✅ NEW
userAgent	String	No	Browser info	✅ NEW
createdAt	DateTime	Yes		✅

---

NEW Models ✅

ProviderSettings ✅ NEW

Provider-specific calendar configuration.

Field	Type	Required	Description
id	UUID	Yes	Primary key
userId	FK→Users	Yes	One-to-one with User
timezone	String	Yes	IANA timezone (default America/Los_Angeles)
defaultBufferMinutes	Int	Yes	Buffer between sessions (default 10)
defaultSessionDuration	Int	Yes	Default session length (default 50)
minimumBookingNotice	Int	Yes	Hours advance notice required (default 24)

---

ProviderAvailability ✅ NEW

Weekly availability schedule for providers.

Field	Type	Required	Description
id	UUID	Yes	Primary key
userId	FK→Users	Yes	Provider
dayOfWeek	Enum	Yes	SUNDAY-SATURDAY
startTime	String	Yes	"09:00" (24hr format)
endTime	String	Yes	"17:00"
isEnabled	Boolean	Yes	Whether day is available

Constraints: Unique(userId, dayOfWeek)

---

TimeBlock ✅ NEW

Non-session time blocks (lunch, vacation, etc.).

Field	Type	Required	Description
id	UUID	Yes	Primary key
userId	FK→Users	Yes	Provider
title	String	Yes	Display name
blockType	Enum	Yes	LUNCH, ADMIN, VACATION, etc.
dayOfWeek	Enum	No	For recurring blocks
startTime	String	No	"12:00" for recurring
endTime	String	No	"13:00" for recurring
startDate	DateTime	No	For one-time blocks
endDate	DateTime	No	For one-time blocks
isAllDay	Boolean	Yes	Default false
isRecurring	Boolean	Yes	Default false
notes	String	No

---

Workflow ✅ NEW

Workflow definition (Inngest-based).

Field	Type	Required	Description
id	UUID	Yes	Primary key
slug	String	Yes	Unique identifier (e.g., "intake-complete")
name	String	Yes	Human-readable name
description	String	No
triggerEvent	String	Yes	Inngest event (e.g., "carehub/intake.completed")
status	Enum	Yes	ACTIVE, PAUSED, DISABLED
category	String	No	e.g., "intake", "session"
version	Int	Yes	Default 1

Relations: steps, configs, runs

---

WorkflowStep ✅ NEW

Steps within a workflow.

Field	Type	Required	Description
id	UUID	Yes	Primary key
workflowId	FK→Workflow	Yes	Parent workflow
stepOrder	Int	Yes	Execution order
stepId	String	Yes	Code identifier (e.g., "analyze-intake")
name	String	Yes	Human-readable name
isEnabled	Boolean	Yes	Can disable individual steps
retryCount	Int	Yes	Default 3
timeoutMs	Int	Yes	Default 30000

---

WorkflowConfig ✅ NEW

Configurable workflow parameters including LLM prompts.

Field	Type	Required	Description
id	UUID	Yes	Primary key
workflowId	FK→Workflow	Yes	Parent workflow
key	String	Yes	Config key (e.g., "intake_analysis_prompt")
name	String	Yes	Human-readable name
configType	Enum	Yes	STRING, NUMBER, BOOLEAN, JSON, PROMPT
value	String	Yes	Current value
defaultValue	String	Yes	Default value
isPrompt	Boolean	Yes	Whether this is an LLM prompt
promptVariables	Array	No	Template variables
version	Int	Yes	Prompt versioning

---

WorkflowRun ✅ NEW

Workflow execution history.

Field	Type	Required	Description
id	UUID	Yes	Primary key
workflowId	FK→Workflow	Yes	Which workflow
inngestRunId	String	No	Inngest correlation ID
inngestEventId	String	No	Triggering event
caseId	String	No	Related case
memberId	String	No	Related member
status	Enum	Yes	PENDING, RUNNING, COMPLETED, FAILED, CANCELLED
startedAt	DateTime	No
completedAt	DateTime	No
durationMs	Int	No
currentStep	String	No
stepsCompleted	Array	No	Step IDs completed
stepResults	JSON	No	Results per step
errorMessage	String	No
errorStep	String	No

---

PayerConfig ✅ NEW

Workers' Compensation payer configuration.

Field	Type	Required	Description
id	UUID	Yes	Primary key
name	String	Yes	e.g., "State Compensation Insurance Fund"
payerId	String	Yes	Unique payer ID
address, city, state, zip	String	No	Contact info
submissionMethod	String	Yes	electronic, paper, portal
portalUrl	String	No
defaultRates	JSON	No	CPT code rates
requiresAuth	Boolean	Yes	Default true
isActive	Boolean	Yes

---

BillingEntry ✅ NEW

Individual billable service entries.

Field	Type	Required	Description
id	UUID	Yes	Primary key
caseId	FK→Cases	Yes
sessionId	FK→Sessions	No
payerId	FK→PayerConfig	Yes
serviceDate	DateTime	Yes
serviceCode	Enum	Yes	CPT code
placeOfService	Enum	Yes	CMS 1500 code
diagnosisCodes	Array	No	ICD-10 codes
providerId	String	Yes
providerNpi	String	No
chargeAmount	Decimal	Yes
allowedAmount	Decimal	No
paidAmount	Decimal	No
status	Enum	Yes	PENDING through WRITTEN_OFF
denialReason	String	No

---

ClaimSubmission ✅ NEW

CMS 1500 claim submissions.

Field	Type	Required	Description
id	UUID	Yes	Primary key
payerId	FK→PayerConfig	Yes
claimNumber	String	Yes	Internal claim #
externalClaimNumber	String	No	Payer's claim #
submissionDate	DateTime	Yes
formData	JSON	Yes	CMS 1500 form data
status	Enum	Yes
totalCharges	Decimal	Yes
totalPaid	Decimal	No
documentPath	String	No	Generated PDF path

---

RFARequest ✅ NEW

Request for Authorization workflow.

Field	Type	Required	Description
id	UUID	Yes	Primary key
caseId	FK→Cases	Yes
requestNumber	String	Yes	RFA-YYYY-XXXX
requestType	Enum	Yes	INITIAL, EXTENSION
currentSessionsUsed	Int	Yes
currentSessionsAuthorized	Int	Yes
additionalSessionsRequested	Int	Yes
clinicalRationale	String	Yes
treatmentGoals	String	Yes
progressToDate	String	Yes
phq9Current	Int	No
gad7Current	Int	No
barriersToClosure	String	No
LPCC Fields
requestedById	FK→Users	Yes
requestedAt	DateTime	Yes
Psychologist Review
reviewedById	FK→Users	No
reviewedAt	DateTime	No
psychReviewNotes	String	No
sessionsApprovedByPsych	Int	No
UR Response
urSubmittedAt	DateTime	No
urResponseAt	DateTime	No
urApprovedSessions	Int	No
urDenialReason	String	No
status	Enum	Yes	DRAFT through UR_MODIFIED

---

DischargeSummary ✅ NEW

Case discharge documentation.

Field	Type	Required	Description
id	UUID	Yes	Primary key
caseId	FK→Cases	Yes	One-to-one
dischargeDate	DateTime	Yes
dischargeReason	Enum	Yes
totalSessionsCompleted	Int	Yes
treatmentDurationWeeks	Int	Yes
presentingProblems	String	Yes
treatmentProvided	String	Yes
outcomesSummary	String	Yes
goalAchievement	JSON	Yes	{ "goalId": "achieved|partial|not" }
phq9Initial/Final	Int	No	Score comparison
gad7Initial/Final	Int	No	Score comparison
gafInitial/Final	Int	No	Score comparison
rtwStatus	Enum	Yes
rtwDetails	String	No
aftercareRecommendations	String	No
referrals	String	No
Attestation
preparedById	FK→Users	Yes	LPCC who prepared
attestedById	FK→Users	No	Psychologist attestation
attestedAt	DateTime	No
attestationText	String	No

---

MemberActivity ✅ NEW

Therapeutic activities/homework for members.

Field	Type	Required	Description
id	UUID	Yes	Primary key
caseId	FK→Cases	Yes
memberId	FK→Users	Yes
assignedById	FK→Users	Yes	Provider who assigned
activityType	Enum	Yes	THOUGHT_JOURNAL, RELAXATION, etc.
title	String	Yes
description	String	Yes
instructions	String	No
resourceUrl	String	No	Link to resource
resourceType	String	No	pdf, video, link
dueDate	DateTime	No
status	Enum	Yes	ASSIGNED through EXPIRED
startedAt	DateTime	No
completedAt	DateTime	No
memberResponse	String	No	Text response/reflection
memberRating	Int	No	1-5 helpfulness
memberNotes	String	No
reviewedAt	DateTime	No
providerFeedback	String	No

---

CaseFeedItem ✅ NEW

Case activity feed (CareHub Chatter).

Field	Type	Required	Description
id	UUID	Yes	Primary key
caseId	FK→Cases	Yes
type	Enum	Yes	MESSAGE_*, EVENT_*, AGENT_*
authorType	Enum	Yes	HUMAN, AGENT, SYSTEM
authorId	FK→Users	No	Null for system events
title	String	No
content	String	Yes
metadata	JSON	No	Type-specific data
visibility	Enum	Yes	INTERNAL, MEMBER_VISIBLE
isRead	Boolean	Yes	Default false
readByIds	Array	No	User IDs who have read
isPinned	Boolean	Yes	Default false

---

Indexes

Performance Indexes

-- Users
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_is_active ON users(is_active);
CREATE UNIQUE INDEX idx_users_entra_id ON users(entra_id);
CREATE UNIQUE INDEX idx_users_clerk_id ON users(clerk_id);

-- Cases
CREATE INDEX idx_cases_member ON cases(member_id);
CREATE INDEX idx_cases_status ON cases(status);
CREATE INDEX idx_cases_phase ON cases(current_phase);
CREATE INDEX idx_cases_psych ON cases(psychologist_id);
CREATE INDEX idx_cases_lpcc ON cases(lpcc_id);

-- Sessions
CREATE INDEX idx_sessions_case ON sessions(case_id);
CREATE INDEX idx_sessions_provider ON sessions(provider_id);
CREATE INDEX idx_sessions_date ON sessions(scheduled_date);
CREATE INDEX idx_sessions_status ON sessions(status);
CREATE INDEX idx_sessions_twilio ON sessions(twilio_room_name);  -- NEW

-- Calendar (NEW)
CREATE INDEX idx_availability_user ON provider_availability(user_id);
CREATE INDEX idx_timeblocks_user ON time_blocks(user_id);
CREATE INDEX idx_timeblocks_date ON time_blocks(start_date);

-- Billing (NEW)
CREATE INDEX idx_billing_case ON billing_entries(case_id);
CREATE INDEX idx_billing_status ON billing_entries(status);
CREATE INDEX idx_billing_date ON billing_entries(service_date);

-- Feed (NEW)
CREATE INDEX idx_feed_case ON case_feed_items(case_id);
CREATE INDEX idx_feed_type ON case_feed_items(type);
CREATE INDEX idx_feed_created ON case_feed_items(created_at);

---

Migrations

Using Prisma ORM for migrations.

# Development
npx prisma migrate dev --name description

# Production
npx prisma migrate deploy

# View current schema
npx prisma studio

---

Data Retention

Data Type	Retention	Reason
Activity/Audit Logs	6 years	HIPAA requirement
Case Records	7 years after discharge	CA WC requirement
Video Recordings	90 days	Storage optimization
Transcripts	Duration of case + 1 year	Clinical reference