Database Schema

Classification: Internal / God Mode Only Last Updated: January 2026 Database: PostgreSQL (Neon) ORM: Prisma

---

Overview

Girard AI uses a multi-tenant PostgreSQL database with organization-scoped data. The schema supports:

• Multi-tenancy with Organizations
• Agency sub-accounts hierarchy
• Credit-based billing system
• Multiple AI agent types
• Workflow automation
• Marketplace for agent templates

---

Entity Relationship Diagram

┌─────────────┐       ┌──────────────────┐       ┌─────────────┐
│    User     │──────<│   Membership     │>──────│Organization │
└─────────────┘       └──────────────────┘       └─────────────┘
      │                                                 │
      │                                    ┌────────────┼────────────┐
      │                                    │            │            │
      ▼                                    ▼            ▼            ▼
┌─────────────┐                   ┌─────────────┐ ┌──────────┐ ┌──────────┐
│   ApiKey    │                   │  Workflow   │ │  Agent   │ │  Credit  │
└─────────────┘                   └─────────────┘ │  Types   │ │  System  │
                                        │         └──────────┘ └──────────┘
                                        ▼
                                  ┌─────────────┐
                                  │WorkflowRun  │
                                  └─────────────┘

---

Core Models

User

Synced from Clerk. Contains user profile and admin status.

Field	Type	Description
id	String	Clerk user ID (primary key)
email	String	Unique email address
firstName	String?	First name
lastName	String?	Last name
imageUrl	String?	Profile image URL
isSuperAdmin	Boolean	Platform admin access
isGodMode	Boolean	Full platform access
impersonatingUserId	String?	Currently impersonating

Indexes: isSuperAdmin, isGodMode

Organization

Multi-tenant container for all resources.

Field	Type	Description
id	String	CUID primary key
name	String	Organization name
slug	String	URL-safe unique identifier
stripeCustomerId	String?	Stripe customer reference
parentId	String?	Parent org for sub-accounts
creditAllocation	Int?	Credits from parent
isActive	Boolean	Account status
parentAccessLevel	Enum	NONE, READ_ONLY, FULL_ACCESS

Indexes: parentId, isActive

Membership

Links users to organizations with roles.

Field	Type	Description
id	String	CUID primary key
role	Enum	OWNER, ADMIN, MEMBER
userId	String	Reference to User
organizationId	String	Reference to Organization

Unique: [userId, organizationId]

---

Billing Models

Plan

Subscription plan definitions.

Field	Type	Description
id	String	CUID primary key
name	String	Plan display name
slug	String	Unique identifier (starter, professional, agency)
priceMonthly	Int	Monthly price in cents
priceYearly	Int	Yearly price in cents
monthlyCredits	Int	Credits per month
overageEnabled	Boolean	Allow overage billing
overageRate	Float	Cost per overage credit
subAccountLimit	Int	Max sub-accounts (Agency)
planType	Enum	STANDARD, AGENCY, ENTERPRISE

Subscription

Organization subscription state.

Field	Type	Description
id	String	CUID primary key
status	Enum	TRIALING, ACTIVE, CANCELED, etc.
stripeSubscriptionId	String?	Stripe reference
trialEndsAt	DateTime?	Trial expiration
billingCycle	Enum	MONTHLY, YEARLY
organizationId	String	One-to-one with Organization
planId	String	Reference to Plan

---

Credit System Models

CreditBalance

Current credit balance per organization.

Field	Type	Description
id	String	CUID primary key
organizationId	String	Unique organization reference
currentBalance	Int	Available credits
rolloverBalance	Int	Rolled over from previous period
monthlyAllocation	Int	Credits allocated this period
lastAllocationAt	DateTime?	When credits were last allocated

CreditTransaction

Audit trail of all credit movements.

Field	Type	Description
id	String	CUID primary key
organizationId	String	Organization reference
amount	Int	Credits (positive = credit, negative = debit)
type	Enum	ALLOCATION, USAGE, PURCHASE, REFUND, TRANSFER
description	String	Human-readable description
featureType	String?	AI_CHAT, VOICE_AGENT, etc.
referenceId	String?	Related entity ID

ApiCostLog

Tracks actual API costs for profitability analysis.

Field	Type	Description
id	String	CUID primary key
organizationId	String	Organization reference
provider	String	anthropic, openai, twilio, etc.
model	String	Specific model used
operation	String	chat, image_generation, voice, etc.
inputUnits	Int	Input tokens/units
outputUnits	Int	Output tokens/units
totalCost	Float	Cost in cents
creditsCharged	Int	Credits charged to user

---

Workflow Models

Workflow

Workflow definition.

Field	Type	Description
id	String	CUID primary key
name	String	Workflow name
description	String?	Optional description
config	Json	Configuration options
steps	Json	Array of workflow steps
aiProvider	Enum	ANTHROPIC, OPENAI, GOOGLE
aiModel	String	Model identifier
isActive	Boolean	Enabled status
currentVersion	Int	Active version number

WorkflowRun

Execution instance of a workflow.

Field	Type	Description
id	String	CUID primary key
status	Enum	PENDING, RUNNING, COMPLETED, FAILED, etc.
input	Json?	Execution input
output	Json?	Execution result
error	String?	Error message if failed
tokensUsed	Int	Total tokens consumed
cost	Float	Cost in cents
durationMs	Int?	Execution duration

Indexes: [workflowId, status], [organizationId, status, createdAt]

---

Agent Models

ChatAgent

Embeddable AI chat agents.

Field	Type	Description
id	String	CUID primary key
name	String	Agent name
systemPrompt	String	Base instructions
welcomeMessage	String?	Initial greeting
model	String	AI model to use
temperature	Float	Response randomness (0-2)
maxTokens	Int	Max response length
widgetToken	String	Embed authentication token
isActive	Boolean	Enabled status

VoiceAgent

Twilio-powered voice agents.

Field	Type	Description
id	String	CUID primary key
name	String	Agent name
systemPrompt	String	Base instructions
voiceId	String	Text-to-speech voice
language	String	Primary language
interruptible	Boolean	Can user interrupt
silenceTimeout	Int	Seconds before timeout
maxDuration	Int	Max call duration

SMSAgent

SMS-based conversational agents.

Field	Type	Description
id	String	CUID primary key
name	String	Agent name
systemPrompt	String	Base instructions
model	String	AI model to use
autoReply	Boolean	Automatic responses

---

Admin Models

ImpersonationLog

Audit trail for God Mode impersonation.

Field	Type	Description
id	String	CUID primary key
adminUserId	String	Admin performing impersonation
targetUserId	String	User being impersonated
reason	String	Justification
ipAddress	String?	Request IP
userAgent	String?	Browser info
startedAt	DateTime	When impersonation started
endedAt	DateTime?	When impersonation ended

EnterpriseLead

Enterprise sales pipeline.

Field	Type	Description
id	String	CUID primary key
firstName	String	Contact first name
lastName	String	Contact last name
email	String	Contact email
company	String	Company name
companySize	String?	Employee count range
status	Enum	NEW, CONTACTED, QUALIFIED, DEMO, etc.
source	String	Lead source

---

Indexes & Performance

Critical Indexes

-- Workflow runs by org and status (dashboard queries)
CREATE INDEX idx_workflow_runs_org_status ON workflow_runs(organization_id, status, created_at DESC);

-- Credit transactions by org (balance calculations)
CREATE INDEX idx_credit_transactions_org ON credit_transactions(organization_id, created_at DESC);

-- API cost logs by date (reporting)
CREATE INDEX idx_api_cost_logs_date ON api_cost_logs(created_at DESC, organization_id);

Query Patterns

Dashboard Stats:

// Get workflow run counts by status
await db.workflowRun.groupBy({
  by: ['status'],
  where: { organizationId },
  _count: true,
})

Credit Balance:

// Check credits with plan info
await db.creditBalance.findUnique({
  where: { organizationId },
  include: {
    organization: {
      include: { subscription: { include: { plan: true } } }
    }
  }
})

---

Data Retention

Data Type	Retention	Notes
User data	Indefinite	Until account deletion
Workflow runs	90 days	Configurable per plan
API logs	30 days	For billing reconciliation
Audit logs	1 year	Compliance requirement
Chat history	30 days	Configurable per agent

---

Migrations

Creating Migrations

# Development: sync schema changes
npx prisma db push

# Production: create migration file
npx prisma migrate dev --name descriptive_name

# Deploy to production
npx prisma migrate deploy

Migration Best Practices

1. Never drop columns in production without migration plan
2. Always add new columns as nullable or with defaults
3. Test migrations on a branch database first
4. Document breaking changes in migration files