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. allowed-tools: Read, Grep, Glob

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.

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

WHERE clauses - Fields frequently used in filters
ORDER BY - Fields used for sorting
JOIN operations - Foreign key fields
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

❌ Missing organizationId filter → Data leakage
❌ N+1 queries → Use include/select
❌ No pagination → Memory issues with large datasets
❌ Not using transactions → Data inconsistency
❌ Forgetting indexes → Slow queries as data grows
❌ Using findUnique without unique constraint → Runtime errors
❌ 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:

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

ナビゲーション

Skillsとは？

リンク

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. allowed-tools: Read, Grep, Glob

VaultCPA Database Schema Guide

Quick Navigation

Schema Overview

Model Categories

Core Models

Organization (Tenant Root)

User (Team Members)

Client (Primary Business Entity)

Alert (Multi-Purpose Alerts)

NexusAlert (State Tax Nexus Specific)

ProfessionalDecision (High-Stakes Decisions)

DoctrineRule (Tax Doctrine with Versioning)

Common Query Patterns

Pattern 1: Multi-Tenant Filtering

Pattern 2: Pagination

Pattern 3: Efficient Relationships (Avoid N+1)

Pattern 4: Selective Field Loading

Pattern 5: Transactions

Pattern 6: Soft Deletes

Relationship Diagrams

Core Entity Relationships

Alert Workflow

Decision Workflow

Migration Patterns

Safe Migration Strategy

Migration Commands

Data Model Decisions

Why Separate NexusAlert from Alert?

Why ClientState Table?

Why JSON Fields?

Audit Trail Strategy

Index Strategy

Critical Indexes

When to Add Indexes

Index Performance Check

Common Mistakes to Avoid

Quick Reference

Get Organization

Get User with Org

Get Client with Alerts

Create with Audit Trail

関連スキル(🌐 Web開発)