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 engagements that group related services (auto-generated matter numbers: YYYY-NNN)
• mattersToServices - Junction table linking matters to engaged services with status tracking
• payments - Payment tracking at the matter level

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

1. Users are the central entity - they can be lawyers, clients, admins, leads, or prospects
2. Matters represent client engagements with auto-generated matter numbers (YYYY-NNN format)
3. MattersToServices is a junction table creating many-to-many relationships between matters and service catalog items (e.g., "Smith Family Trust 2024" matter engages both WYDAPT and Annual Maintenance)
4. Payments are tracked at the matter level, not per-service, simplifying financial reporting
5. ClientJourneys track client progress through service workflows, referencing the engagement via composite foreign key (matterId, catalogId)
6. Journeys define workflows with JourneySteps that clients progress through
7. Documents can be generated from DocumentTemplates and linked to matters and specific service engagements
8. ActionItems define tasks that need completion, either at the template level (linked to steps) or instance level (linked to client journeys)