Entity Relationship Diagram
Database Schema Overview
This document describes the entity relationships in the YTP database schema.
Entity Groups
Core User Management
- users - Central user table for all roles (Admin, Lawyer, Client, Lead, Prospect)
- clientProfiles - Extended profile information for client users
Appointments & Scheduling
- appointments - Scheduled meetings between lawyers and clients
Document Management
- templateFolders - Hierarchical folder structure for organizing templates
- documentTemplates - Reusable document templates with variable placeholders
- documents - Generated documents from templates, linked to clients and services
- uploadedDocuments - DOCX files uploaded for processing
Service Catalog & Matters
- serviceCatalog - Product/service definitions (Trust Formation, LLC, etc.)
- matters - Client cases that group related services
- services - Specific engaged services linked to a matter
Questionnaires
- questionnaires - Pre-consultation questionnaire definitions
- questionnaireResponses - Client responses to questionnaires
Journey System
- journeys - Workflow/journey definitions (templates or active)
- journeySteps - Individual steps in a journey (Milestones or Bridges)
- clientJourneys - Tracks client progress through journeys
- journeyStepProgress - Detailed progress tracking per step
- actionItems - Tasks to complete within journey steps
- bridgeConversations - Chat messages within bridge steps
- faqLibrary - Knowledge base for help content
- documentUploads - Client-uploaded documents for review
- snapshotVersions - Version tracking for snapshot documents
- automations - Automation rules for journeys
Marketing
- marketingSources - Marketing campaign/source definitions
- clientMarketingAttribution - UTM tracking and source attribution per client
Other
- notes - Internal notes about clients
- activities - Activity/audit log
- settings - Application configuration key-value store
- lawpayConnections - LawPay OAuth2 connection metadata
Key Relationships
- Users are the central entity - they can be lawyers, clients, admins, leads, or prospects
- Matters group multiple Services for a client (e.g., "Smith Family Trust 2024" matter contains Trust Formation and Annual Maintenance services)
- Services are instances of ServiceCatalog items purchased by clients
- Journeys define workflows with JourneySteps that clients progress through via ClientJourneys
- Documents can be generated from DocumentTemplates and linked to both Services and Matters
- ActionItems define tasks that need completion, either at the template level (linked to steps) or instance level (linked to client journeys)