Protection Hub Documentation
System Reference for AI Analysis
The platform uses MySQL/TiDB with Drizzle ORM. Below is every table and its fields.
Admin users who can access the adviser hub. Only one user (Rob) in production.
| Field | Type | Description |
|---|---|---|
| id | int (PK) | Auto-increment primary key |
| openId | varchar(64) | Manus OAuth identifier |
| name | text | Display name |
| varchar(320) | Email address | |
| role | enum(user, admin) | Role — defaults to admin |
| createdAt, updatedAt, lastSignedIn | timestamp | Metadata timestamps |
The core table — one row per client. Contains all journey state, adviser pre-fill data, and application tracking.
| Field | Type | Description |
|---|---|---|
| id | int (PK) | Auto-increment primary key |
| clientRef | varchar(20) | Unique reference (LM-YYYY-###) |
| secureToken | varchar(64) | Unique token for client access URL |
| clientName | varchar(255) | Client's full name |
| clientEmail | varchar(320) | Client's email (optional) |
| clientPhone | varchar(50) | Client's phone (optional) |
| journeyType | enum | new_mortgage, standalone, review, campaign |
| mortgagePaymentMonthly | decimal | Adviser pre-fill for Risk Snapshot |
| householdOutgoingsMonthly | decimal | Adviser pre-fill for Risk Snapshot |
| clientEmploymentStatus | varchar | Adviser pre-fill for conditional questions |
| lvReportUrl | text | URL to LV Risk Reality report PDF |
| status | enum | pending, in_progress, recommendations_sent, proceeded, parked, declined, follow_up |
| currentStep | enum | risk_reality, risk_review, risk_snapshot, education, holding, recommendations, compliance, decision, application, completed |
| loomVideoUrl | text | Personalised recommendation video URL |
| loomDocsVideoUrl | text | Documentation process video URL |
| recommendationsSummary | text | Written summary of recommendations |
| applicationStatus | enum | not_started, submitted, under_review, gp_requested, awaiting_gp, decision_received, on_risk, declined_by_provider, non_standard_terms |
| applicationProvider | varchar | Insurance provider name |
| applicationReference | varchar | Provider's application reference |
| notes | text | Adviser-only notes |
| createdById | int | Admin user who created the journey |
| createdAt, updatedAt | timestamp | Metadata timestamps |
Stores all questionnaire responses for a client journey.
| Field | Type | Description |
|---|---|---|
| id | int (PK) | Auto-increment |
| journeyId | int | FK to client_journeys |
| clientName | text | Name as entered by client |
| worriedMost | text | Q3: Biggest worry |
| seriousIllnessImpact | text | Q4: Financial impact of serious illness |
| deathImpact | text | Q5: Impact of death on family |
| incomeStopDuration | text | Q6: How long could they pay mortgage |
| employerSickPay | text | Q7: Employer sick pay details |
| statutorySickPayConfirm | varchar | Q8: SSP reality check confirmation |
| otherFinancialSupport | text | Q9: Savings and other support |
| hasExistingPolicies | varchar | Q10: Whether they have existing policies |
| existingPoliciesStructured | json | Q11: Structured policy details |
| priorityMatters | text | Q12: What matters most |
| protectionAttitude | text | Q13: Attitude to protection |
| budgetRange | varchar | Q14: Monthly budget range |
| smokerStatus | varchar | Q15: Smoking/nicotine status |
| healthConditions | text | Q16: Existing health conditions |
| additionalThoughts | text | Q17: Additional information |
| confirmAccurate | varchar | Q18: Confirmation |
| partialResponses | json | Auto-saved progress (all answers + current step) |
| completedAt | timestamp | When the questionnaire was submitted |
Digital signatures for proceed/park/decline decisions.
| Field | Type | Description |
|---|---|---|
| type | enum | proceed, parked, decline |
| signatureName | varchar | Client's typed name |
| signatureTimestamp | timestamp | When they signed |
| ipAddress | varchar | Client's IP |
| acknowledgedRisks | json | Array of acknowledged risk statements (decline only) |
Automated check-in reminders for parked/declined clients.
| Field | Type | Description |
|---|---|---|
| reminderType | enum | 3_month, 6_month, 12_month |
| scheduledDate | timestamp | When the reminder is due |
| status | enum | pending, sent, completed, cancelled |
Every client interaction is logged for analytics and audit trail.
Editable email templates with variable interpolation support.
Complete log of all emails sent or attempted, with delivery status tracking.
Pre-recorded video slots that Rob can configure for different points in the client journey (e.g., before "What Worries You" section, before "Your Safety Net" section).
White-label branding settings (company name, tagline, logo, colours, font).
Bulk re-engagement campaign system for reaching out to declined/incomplete clients.
Audit trail of application status changes with timestamps and notes.