database-schema-guide

name	database-schema-guide
description	VaultCPA database schema reference with 50+ Prisma models, relationships, and query patterns. Use when working with database models, designing features, or understanding data structure. Use when this capability is needed.
metadata	{"author":"galactivale"}

VaultCPA Database Schema Guide

Version: 2.0 Last Updated: January 2026 Schema Location: server/prisma/schema.prisma

This Skill provides a comprehensive guide to VaultCPA's PostgreSQL database schema, including model relationships, common patterns, and query examples.

Quick Navigation

• Schema Overview
• Core Models
• Common Query Patterns
• Relationship Diagrams
• Migration Patterns
• Data Model Decisions

For detailed model references, see:

• Core Models Reference - Organization, User, Client
• Compliance Models - Alerts, Nexus, Risk
• Workflow Models - Tasks, Decisions, Documents

---

Schema Overview

Model Categories

Tenant & Identity (4 models)

• Organization - Root tenant entity
• User - Team members with CPA credentials
• Permission - Role-based access control
• ApiKey - API authentication

Core Business (12 models)

• Client - Primary data subject
• ClientState - Per-state tracking
• BusinessProfile - Business details
• BusinessLocation - Physical locations
• Contact - Client contacts
• GeographicDistribution - Revenue by region
• RevenueBreakdown - Categorized revenue
• CustomerDemographics - Customer analytics
• ClientRevenueHistory - Historical revenue
• StateTaxInfo - State tax thresholds
• OrganizationMetadata - Custom org data
• PerformanceMetric - Business metrics

Compliance & Risk (10 models)

• Alert - Multi-purpose alerts
• NexusAlert - State tax nexus specific
• NexusActivity - Activity tracking
• RiskFactor - Risk assessments
• ComplianceStandard - Compliance frameworks
• RegulatoryChange - Law changes
• DataProcessing - Processing records
• AuditLog - System audit trail
• AuditTrail - Business audit trail
• Notification - In-app notifications

Workflow & Decisions (7 models)

• Task - Workflow tasks
• TaskStep - Task breakdown
• ProfessionalDecision - High-stakes decisions
• DecisionTable - Decision audit
• Document - File management
• AdvisoryDocument - Client advice
• Comment - Collaborative notes

Communication (3 models)

• Consultation - Client meetings
• Communication - Contact log
• ClientCommunication - Interaction tracking

Tax & Doctrine (4 models)

• DoctrineRule - Tax rules with versioning
• DoctrineApproval - Approval workflow
• DoctrineVersionEvent - Change history
• DoctrineImpactMetrics - Rule impact

System & Integration (9 models)

• Integration - Third-party connections
• Webhook - Webhook configs
• WebhookDelivery - Delivery tracking
• GeneratedDashboard - Custom dashboards
• Template - Reusable content
• Report - Scheduled reports
• ActivityFeed - Team activity
• DataProcessing - Processing jobs

Total: 50+ models

---

Core Models

Organization (Tenant Root)

model Organization {
  id                   String   @id @default(uuid())
  slug                 String   @unique
  name                 String
  subscriptionTier     String   @default("trial")
  subscriptionStatus   String   @default("active")

  // Relationships - ALL data scoped to organization
  users                User[]
  clients              Client[]
  alerts               Alert[]
  tasks                Task[]
  // ... 30+ more relationships
}

Key Points:

• Root of multi-tenant hierarchy
• Every other model references organizationId
• Subscription and billing tracked here
• Custom settings stored in JSON fields

Common Queries:

// Get organization with user count
const org = await prisma.organization.findUnique({
  where: { id: orgId },
  include: {
    _count: {
      select: { users: true, clients: true }
    }
  }
});

// Get all orgs expiring soon
const expiring = await prisma.organization.findMany({
  where: {
    subscriptionExpiresAt: {
      lte: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000) // 30 days
    }
  }
});

---

User (Team Members)

model User {
  id              String   @id @default(uuid())
  organizationId  String   @map("organization_id")
  email           String
  passwordHash    String   @map("password_hash")
  role            String   // MANAGING_PARTNER, TAX_MANAGER, STAFF_ACCOUNTANT, SYSTEM_ADMIN

  // CPA Credentials
  cpaLicense      String?  @map("cpa_license")
  cpaState        String?  @map("cpa_state")
  cpaExpiration   DateTime? @map("cpa_expiration")

  // Relationships
  organization    Organization @relation(fields: [organizationId], references: [id])
  assignedTasks   Task[]       @relation("AssignedUser")
  createdTasks    Task[]       @relation("CreatedByUser")
}

Key Points:

• Scoped to organization
• Role determines dashboard access
• CPA credentials for compliance tracking
• Audit trail through created/assigned relationships

Common Queries:

// Get user with permissions
const user = await prisma.user.findFirst({
  where: {
    email,
    organizationId
  },
  include: {
    organization: true,
    permissions: true
  }
});

// Get all CPAs in org
const cpas = await prisma.user.findMany({
  where: {
    organizationId,
    cpaLicense: { not: null }
  }
});

---

Client (Primary Business Entity)

model Client {
  id                  String   @id @default(uuid())
  organizationId      String   @map("organization_id")
  name                String
  status              String   @default("prospect")
  riskLevel           String   @default("low")

  // Relationships
  organization        Organization @relation(fields: [organizationId], references: [id])
  alerts              Alert[]
  nexusAlerts         NexusAlert[]
  tasks               Task[]
  decisions           ProfessionalDecision[]
  clientStates        ClientState[]
  revenueHistory      ClientRevenueHistory[]
}

Key Points:

• Central entity for all client data
• Risk level drives compliance workflows
• State-specific data in related tables
• Extensive relationships (20+ related models)

Common Queries:

// Get client with all nexus alerts
const client = await prisma.client.findFirst({
  where: {
    id: clientId,
    organizationId
  },
  include: {
    nexusAlerts: {
      where: { status: 'ACTIVE' },
      orderBy: { createdAt: 'desc' }
    },
    clientStates: true,
    revenueHistory: {
      orderBy: { year: 'desc' },
      take: 3 // Last 3 years
    }
  }
});

// Get high-risk clients
const highRisk = await prisma.client.findMany({
  where: {
    organizationId,
    riskLevel: { in: ['HIGH', 'CRITICAL'] }
  },
  include: {
    _count: {
      select: { alerts: true }
    }
  }
});

---

Alert (Multi-Purpose Alerts)

model Alert {
  id             String   @id @default(uuid())
  organizationId String   @map("organization_id")
  clientId       String?  @map("client_id")
  type           String   // NEXUS, COMPLIANCE, RISK, DOCUMENT, DEADLINE
  severity       String   // CRITICAL, HIGH, MEDIUM, LOW
  status         String   @default("pending")
  message        String

  // Polymorphic relationships
  client         Client?  @relation(fields: [clientId], references: [id])
  consultation   Consultation? @relation(fields: [consultationId], references: [id])
}

Key Points:

• Generic alert system for all alert types
• Polymorphic - can relate to different entities
• Status workflow: pending → acknowledged → in_progress → resolved
• Severity determines urgency

---

NexusAlert (State Tax Nexus Specific)

model NexusAlert {
  id              String   @id @default(uuid())
  organizationId  String   @map("organization_id")
  clientId        String   @map("client_id")
  state           String
  type            String   // SALES_TAX, INCOME_TAX, FRANCHISE_TAX, PAYROLL
  severity        String   // RED, ORANGE, YELLOW
  threshold       Decimal?
  currentAmount   Decimal?

  // Doctrine integration
  appliedDoctrineRuleId String? @map("applied_doctrine_rule_id")
  doctrineRule          DoctrineRule? @relation(fields: [appliedDoctrineRuleId], references: [id])
}

Key Points:

• Specialized for tax nexus alerts
• Links to doctrine rules for professional judgment
• Tracks threshold vs actual amounts
• Color-coded severity (RED/ORANGE/YELLOW)

---

ProfessionalDecision (High-Stakes Decisions)

model ProfessionalDecision {
  id                String   @id @default(uuid())
  organizationId    String   @map("organization_id")
  clientId          String   @map("client_id")
  decisionType      String
  riskLevel         String
  financialExposure Decimal?

  // Decision content
  question          String
  analysis          String
  conclusion        String
  supportingEvidence Json    @default("{}")

  // Peer review
  reviewStatus      String   @default("pending")
  reviewedBy        String?
  reviewedAt        DateTime?

  // Audit trail
  createdById       String   @map("created_by_id")
  createdBy         User     @relation("DecisionCreator", fields: [createdById], references: [id])
}

Key Points:

• Documents high-stakes professional judgments
• Peer review workflow built-in
• Financial exposure tracking
• Complete audit trail for liability protection

---

DoctrineRule (Tax Doctrine with Versioning)

model DoctrineRule {
  id            String   @id @default(uuid())
  organizationId String? @map("organization_id")
  clientId      String?  @map("client_id")
  scope         String   // FIRM, OFFICE, CLIENT

  version       Int      @default(1)
  status        String   // DRAFT, PENDING_APPROVAL, ACTIVE, DISABLED

  // Rule content
  title         String
  description   String
  taxType       String
  states        String[] // Array of state codes

  // Versioning
  previousVersionId String?
  versionEvents     DoctrineVersionEvent[]
  approvals         DoctrineApproval[]
  impactMetrics     DoctrineImpactMetrics[]
}

Key Points:

• Reusable tax position rules
• Versioned for compliance
• Scoped to firm/office/client level
• Approval workflow integration
• Impact tracking for audit purposes

---

Common Query Patterns

Pattern 1: Multi-Tenant Filtering

// ALWAYS include organizationId
const clients = await prisma.client.findMany({
  where: {
    organizationId: req.user.organizationId  // Required!
  }
});

// With additional filters
const activeClients = await prisma.client.findMany({
  where: {
    organizationId: req.user.organizationId,
    status: 'ACTIVE',
    riskLevel: { in: ['HIGH', 'CRITICAL'] }
  }
});

Pattern 2: Pagination

const page = 1;
const limit = 20;

const clients = await prisma.client.findMany({
  where: { organizationId },
  skip: (page - 1) * limit,
  take: limit,
  orderBy: { createdAt: 'desc' }
});

const total = await prisma.client.count({
  where: { organizationId }
});

const pages = Math.ceil(total / limit);

Pattern 3: Efficient Relationships (Avoid N+1)

// ❌ BAD - N+1 query
const clients = await prisma.client.findMany({ where: { organizationId } });
for (const client of clients) {
  const alerts = await prisma.alert.findMany({
    where: { clientId: client.id }
  });
}

// ✅ GOOD - Single query with include
const clients = await prisma.client.findMany({
  where: { organizationId },
  include: {
    alerts: {
      where: { status: 'PENDING' }
    }
  }
});

Pattern 4: Selective Field Loading

// Only fetch needed fields
const clients = await prisma.client.findMany({
  where: { organizationId },
  select: {
    id: true,
    name: true,
    riskLevel: true,
    _count: {
      select: { alerts: true }
    }
  }
});

Pattern 5: Transactions

const result = await prisma.$transaction(async (tx) => {
  // Create client
  const client = await tx.client.create({
    data: { ...clientData, organizationId }
  });

  // Create onboarding alert
  const alert = await tx.alert.create({
    data: {
      type: 'ONBOARDING',
      clientId: client.id,
      organizationId
    }
  });

  // Audit log
  await tx.auditLog.create({
    data: {
      action: 'CLIENT_CREATED',
      resourceId: client.id,
      userId: req.user.id,
      organizationId
    }
  });

  return { client, alert };
});

Pattern 6: Soft Deletes

// Instead of deleting, mark as deleted
await prisma.client.update({
  where: { id: clientId },
  data: { deletedAt: new Date() }
});

// Filter out deleted records
const activeClients = await prisma.client.findMany({
  where: {
    organizationId,
    deletedAt: null
  }
});

---

Relationship Diagrams

Core Entity Relationships

Organization (Tenant Root)
│
├─► User (Team members)
│   └─► Task (Assigned work)
│
├─► Client (Business entity)
│   ├─► Alert (All alert types)
│   ├─► NexusAlert (Tax nexus specific)
│   ├─► Task (Client work)
│   ├─► ProfessionalDecision (Judgments)
│   ├─► ClientState (Per-state data)
│   ├─► RevenueHistory (Historical data)
│   ├─► Consultation (Meetings)
│   └─► Document (Files)
│
├─► DoctrineRule (Tax rules)
│   ├─► DoctrineApproval (Approval workflow)
│   ├─► DoctrineVersionEvent (Version history)
│   └─► NexusAlert (Applied to alerts)
│
└─► Integration (Third-party)
    ├─► Webhook (Event configs)
    └─► WebhookDelivery (Delivery log)

Alert Workflow

Alert Created (PENDING)
│
├─► Acknowledged (USER_ACTION)
│   └─► In Progress (WORK_STARTED)
│       ├─► Resolved (COMPLETED)
│       └─► Dismissed (NOT_APPLICABLE)
│
└─► Escalated (CRITICAL_SEVERITY)
    └─► Consultation Created

Decision Workflow

ProfessionalDecision Created (DRAFT)
│
├─► Submitted for Review (PENDING_REVIEW)
│   ├─► Approved (APPROVED)
│   │   └─► Active (Applied to clients)
│   │
│   └─► Rejected (REJECTED)
│       └─► Back to Draft (REVISIONS_NEEDED)
│
└─► Archived (ARCHIVED)

---

Migration Patterns

Safe Migration Strategy

1. Additive Changes (Safe)

// Add optional field
model Client {
  newField String?  // Optional
}

// Deploy migration
// Backfill data if needed
// Make required in next migration

2. Renaming Fields (Zero Downtime)

// Step 1: Add new field
model Client {
  oldName String
  newName String?
}

// Step 2: Dual-write in application
// Step 3: Backfill data
// Step 4: Switch reads to newName
// Step 5: Remove oldName

3. Breaking Changes (Requires Downtime)

// Changing field type or removing required field
// Plan maintenance window
// Run migration during low-traffic period

Migration Commands

# Development - interactive
cd server
npx prisma migrate dev --name add_client_risk_level

# Production - automated
npx prisma migrate deploy

# Check status
npx prisma migrate status

# Generate Prisma client
npx prisma generate

# View data
npx prisma studio

---

Data Model Decisions

Why Separate NexusAlert from Alert?

Reason: Specialized tax nexus tracking with doctrine rule integration

Alert - Generic (all types: compliance, risk, document)
NexusAlert - Tax nexus specific (threshold tracking, doctrine rules)

Benefits:

• Cleaner schema (nexus-specific fields don't clutter Alert)
• Better query performance (smaller Alert table)
• Doctrine integration without affecting other alerts

Why ClientState Table?

Reason: Per-state tracking for multi-state clients

Client (parent)
└─► ClientState[] (one per state where client operates)
    ├─► state: "CA"
    ├─► hasNexus: true
    ├─► registeredForSalesTax: true
    └─► lastFilingDate: 2024-01-15

Benefits:

• Scalable (clients can operate in 1-50 states)
• Clean queries (get all CA clients, get client's states)
• Historical tracking per state

Why JSON Fields?

Used for flexible, schema-less data:

model Organization {
  settings  Json  @default("{}")  // Customizable settings
  branding  Json  @default("{}")  // Logo, colors, themes
  features  Json  @default("{}")  // Feature flags
}

Use JSON when:

• Data structure varies by tenant
• Frequent schema changes needed
• Non-queryable configuration data

Use separate tables when:

• Need to query/filter on field
• Foreign key relationships needed
• Data integrity constraints required

Audit Trail Strategy

Two-Level Approach:

AuditLog - System-level (all actions, auto-generated)
├─► user_id, action, resource_type, resource_id, timestamp

AuditTrail - Business-level (important business events)
├─► decision_id, event_type, description, user_id, timestamp

Why both?

• AuditLog: Complete system history for debugging
• AuditTrail: Business events for compliance/audit

---

Index Strategy

Critical Indexes

// Multi-tenant queries
@@index([organizationId])

// Common lookups
@@index([organizationId, status])
@@index([organizationId, createdAt(sort: Desc)])

// Relationship indexes (auto-created by Prisma)
// Fields used in @relation get indexes automatically

// Composite indexes for common queries
@@index([organizationId, clientId, type])

When to Add Indexes

1. WHERE clauses - Fields frequently used in filters
2. ORDER BY - Fields used for sorting
3. JOIN operations - Foreign key fields
4. Multi-column queries - Composite indexes

Index Performance Check

-- Check query performance
EXPLAIN ANALYZE SELECT * FROM clients WHERE organization_id = '...';

-- See table indexes
SELECT * FROM pg_indexes WHERE tablename = 'clients';

-- Index usage stats
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

---

Common Mistakes to Avoid

1. ❌ Missing organizationId filter → Data leakage
2. ❌ N+1 queries → Use include/select
3. ❌ No pagination → Memory issues with large datasets
4. ❌ Not using transactions → Data inconsistency
5. ❌ Forgetting indexes → Slow queries as data grows
6. ❌ Using findUnique without unique constraint → Runtime errors
7. ❌ Not cascading deletes → Orphaned records

---

Quick Reference

Get Organization

const org = await prisma.organization.findUnique({
  where: { id: orgId }
});

Get User with Org

const user = await prisma.user.findFirst({
  where: { email, organizationId },
  include: { organization: true }
});

Get Client with Alerts

const client = await prisma.client.findFirst({
  where: { id: clientId, organizationId },
  include: {
    alerts: { where: { status: 'PENDING' } },
    nexusAlerts: true
  }
});

Create with Audit Trail

const client = await prisma.client.create({
  data: {
    ...clientData,
    organizationId,
    createdById: req.user.id,
    createdAt: new Date()
  }
});

---

For detailed model specifications, see:

• core-models.md - Complete field definitions
• compliance-models.md - Alert and risk models
• workflow-models.md - Task and decision models

When using this Skill:

1. Always verify organizationId filtering
2. Use appropriate query patterns for performance
3. Follow migration best practices
4. Refer to relationship diagrams for data modeling

---

Converted and distributed by TomeVault — claim your Tome and manage your conversions.