Documentation

DATABASE_SCHEMA

docs/features/DATABASE_SCHEMA.md

Database Schema

Status: Active Last Updated: 2025-01-11


Overview

This document describes the Petunia database schema. The database uses PostgreSQL with Prisma as the ORM.

Technology Stack

  • Database: PostgreSQL (Supabase)
  • ORM: Prisma 7.0.1
  • Schema Location: prisma/schema/ (multi-file)
  • Migrations: prisma/migrations/

Schema Statistics

  • Models: 192
  • Enums: 87
  • Domain Files: 21 (including _config.prisma and enums.prisma)

Schema Organization

The schema is organized into domain-specific files in prisma/schema/:

FileModelsDomain
_config.prisma0Generator & datasource config
enums.prisma0All 87 enum definitions
analytics.prisma12Metrics, rollups, competitor analysis
auth.prisma7Account, Session, Passkey, verification
autoresponder.prisma9Auto-response rules and scheduling
billing.prisma13Invoices, subscriptions, payments
business.prisma7Business, Portal, Client, Company
calendar.prisma7Appointments, availability, booking
commissions.prisma6Commission rules and transactions
connections.prisma5Integrations, API settings
contacts.prisma15Contact, Lead, relationship profiles
messaging.prisma7Messages, conversations, templates
platform.prisma16Feature flags, webhooks, wallets
quality.prisma4Quality monitoring and AI review
reviews.prisma33All review platforms (Yelp, Google, etc.)
sales.prisma5Sales config, objections
social.prisma7Twitter, LinkedIn, social posts
system.prisma9Errors, deferred tasks, audit logs
user.prisma12User, Admin, preferences, behavior
voice.prisma6Calls, phone numbers, touchpoints
workflows.prisma12Workflows, campaigns, followups

Schema Source

  • Prisma Schema: prisma/schema/ (folder)
  • Migrations: prisma/migrations/
  • Seed Data: prisma/seed.ts
  • Configuration: prisma.config.ts

Key Model Categories

User Management

  • User - User accounts and profiles
  • Account - OAuth accounts (NextAuth.js)
  • Session - User sessions
  • VerificationToken - Email verification tokens

Business Entities

  • Company - Business/company records
  • Client - Client records
  • Location - Business locations
  • Portal - Multi-tenant portals

Integrations

  • Connection - Third-party service connections
  • YelpReview - Yelp reviews
  • GoogleReview - Google Business reviews
  • FacebookPage - Facebook page integrations
  • InstagramAccount - Instagram account integrations

Communication

  • Message - Messages across all channels
  • Conversation - Message threads
  • Review - Customer reviews
  • CallRecord - Phone call logs

AI & Automation

  • AIResponse - AI-generated responses
  • Autoresponder - Automated response rules
  • KnowledgeBase - AI context and knowledge

Analytics & Tracking

  • Event - System events
  • Metric - Analytics metrics
  • AuditLog - Audit trail

Model Relationships

[To be documented with detailed ER diagrams]

Core Relationships

  • User → Portal (one-to-many)
  • Portal → Location (one-to-many)
  • Location → Connection (one-to-many)
  • Connection → Message (one-to-many)

Integration Relationships

[To be documented]


Database Indexes

[To be documented with performance considerations]


Data Types

[To be documented with custom types and enums]


Migrations

See prisma/migrations/ for complete migration history.

How to Run Migrations

# Generate Prisma client
npx prisma generate

# Apply migrations
npx prisma migrate deploy

# Create new migration
npx prisma migrate dev --name <migration-name>

Seed Data

See prisma/seed.ts for seed data configuration.

How to Seed Database

npx prisma db seed

Database Commands

Common Operations

# Open Prisma Studio (database GUI)
npx prisma studio

# View database schema
npx prisma db pull

# Reset database (CAUTION: destroys data)
npx prisma migrate reset

# Generate client after schema changes
npx prisma generate


TODO

This is a stub document. The following sections need to be filled:

  • Complete model list with descriptions
  • Entity-relationship diagrams
  • Detailed field descriptions for each model
  • Index strategy and performance considerations
  • Custom types and enum definitions
  • Data retention and archival policies
  • Security considerations (RLS, permissions)
  • Backup and restore procedures
  • Scaling considerations

This document will be expanded with comprehensive database documentation. For now, refer directly to prisma/schema.prisma for the source of truth.