Skip to content

Database Guide

Overview

CaseSolo uses PostgreSQL with Prisma 7 ORM and pgvector for AI embeddings. Production is configured through DATABASE_URL/DIRECT_URL in the self-hosted hostssh deployment; Neon or another managed Postgres provider can still be used for branch-style migration rehearsal when desired.

Connection

DATABASE_URL="postgresql://user:pass@host/casesolo?sslmode=require"

The Prisma client uses @prisma/adapter-pg for PostgreSQL connections.

Schema Location

prisma/schema.prisma     # Model definitions
prisma/migrations/       # Migration history
prisma.config.ts         # Prisma configuration

Models (51 total)

Core (6)

| Model | Purpose | Firm-scoped | |-------|---------|:-----------:| | Firm | Tenant organization | N/A (is the tenant) | | User | Team members | Yes | | Case | Legal cases | Yes | | Contact | People & organizations | Yes | | Task | Action items | Yes | | Document | Uploaded files | Yes |

Case Relations (4)

| Model | Purpose | |-------|---------| | CaseStatusHistory | Status change audit trail | | CaseParty | Contacts linked to cases with roles | | CaseAssignment | Staff assigned to cases | | Note | Case notes with rich text |

Medical (4)

| Model | Purpose | |-------|---------| | MedicalRecord | Medical history records | | MedicalProvider | Provider directory (firm-scoped) | | Treatment | Appointment/treatment tracking | | MedicalBill | Bill tracking with liens |

Legal (4)

| Model | Purpose | |-------|---------| | InsurancePolicy | Policy coverage per case | | NegotiationEntry | Offer/counter-offer log | | Deposition | Deposition scheduling & transcripts | | JurisdictionStatute | State-specific legal rules |

Financial (6)

| Model | Purpose | |-------|---------| | Invoice | Client invoices with line items | | Payment | Payment records | | TimeEntry | Billable time tracking | | TrustTransaction | Trust account ledger; nullable caseId is preserved with ON DELETE SET NULL so financial history survives case deletion | | Expense | Case-related expenses | | MileageEntry | Mileage tracking |

Communication (4)

| Model | Purpose | |-------|---------| | CommunicationRecord | Unified comm log (email, phone, SMS) | | SmsConsent | TCPA opt-in/opt-out | | SmsMessage | SMS history | | EmailLog | Email delivery tracking |

AI & Automation (6)

| Model | Purpose | |-------|---------| | AgentConfig | AI agent definitions | | AgentRun | Agent execution logs | | DocumentChunk | RAG embeddings (pgvector) | | Workflow | Visual workflow definitions | | WorkflowRun | Workflow execution logs | | Template | Document templates |

Configuration (6)

| Model | Purpose | |-------|---------| | CustomFieldDefinition | Firm-defined custom fields | | CustomFieldValue | Custom field data | | AlertRule | Automated alert triggers | | WebhookEndpoint | Outbound webhook config | | ApiKey | Developer API keys | | SavedReport | Saved report configurations |

Signatures (2)

| Model | Purpose | |-------|---------| | SignatureRequest | E-signature requests | | SignatureSigner | Individual signer tracking |

Intake (3)

| Model | Purpose | |-------|---------| | Lead | Pre-case lead tracking | | IntakeSubmission | Form submission data | | ReferralSource | Referral partner tracking |

Other (4)

| Model | Purpose | |-------|---------| | Recording | Video/audio recordings | | Activity | Audit trail | | Notification | User notifications | | CalendarEvent | Calendar events & deadlines | | ChecklistTemplate | Reusable checklists | | ChecklistItem | Checklist line items |

Multi-Tenant Queries

Using tenant-scoped client (recommended)

import { getTenantPrisma } from '@/lib/db'

const db = getTenantPrisma(user.firmId)
const cases = await db.case.findMany() // firmId auto-injected

Using raw client (when needed)

import { prisma } from '@/lib/db'

const cases = await prisma.case.findMany({
  where: { firmId: user.firmId } // must manually scope
})

Migrations

# Create a migration after schema changes
npx prisma migrate dev --name description_of_change

# Apply migrations in production
npx prisma migrate deploy

# Generate client after schema changes
npx prisma generate

# View current schema status
npx prisma migrate status

Enums

Key enums used throughout:

Source: docs/dev/database.md