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/:

File	Models	Domain
_config.prisma	0	Generator & datasource config
enums.prisma	0	All 87 enum definitions
analytics.prisma	12	Metrics, rollups, competitor analysis
auth.prisma	7	Account, Session, Passkey, verification
autoresponder.prisma	9	Auto-response rules and scheduling
billing.prisma	13	Invoices, subscriptions, payments
business.prisma	7	Business, Portal, Client, Company
calendar.prisma	7	Appointments, availability, booking
commissions.prisma	6	Commission rules and transactions
connections.prisma	5	Integrations, API settings
contacts.prisma	15	Contact, Lead, relationship profiles
messaging.prisma	7	Messages, conversations, templates
platform.prisma	16	Feature flags, webhooks, wallets
quality.prisma	4	Quality monitoring and AI review
reviews.prisma	33	All review platforms (Yelp, Google, etc.)
sales.prisma	5	Sales config, objections
social.prisma	7	Twitter, LinkedIn, social posts
system.prisma	9	Errors, deferred tasks, audit logs
user.prisma	12	User, Admin, preferences, behavior
voice.prisma	6	Calls, phone numbers, touchpoints
workflows.prisma	12	Workflows, 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

---

Related Documentation

• Database Migrations - Migration notes and history
• Documentation Index - Complete documentation

---

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.