Database Schema

Schema Overview

The payment system uses PostgreSQL with Drizzle ORM. All schemas follow project conventions:

• nanoid() for primary keys (not auto-increment)
• snake_case column names in SQL, camelCase in TypeScript
• timestamp with timezone for all date fields
• Soft delete with deletedAt (except financial tables)
• Foreign keys to account table (account-centric architecture)

Invariant

Core Tables

stripe_payments

Master transaction record tracking the full lifecycle from checkout initiation through charge completion and refunds. This table is Stripe-focused and does not store product-specific pricing breakdowns (platform fee, talent share) - those live in the product table. The payment system fetches pricing via getProductByPayForId().

File: packages/db/src/schema/stripePayments.ts

Key Fields:

• id: nanoid primary key
• buyerUserId: Buyer's user ID (required)
• buyerAccountId: Buyer's account ID (optional, only if logged in)
• sellerAccountId: Seller's account ID (required)
• payFor: Enum (MERCH, VOICE_OVER, LIKENESS, IMAGE, OFFER)
• payForId: Foreign key to product-specific table (for OFFER, the offers row id)
• amountCent: Payment amount in integer cents (negative for refunds) - fetched from product table
• stripeFeeCent: Stripe transaction fees (set when charge succeeds, initially null)
• status: Lifecycle status enum (CREATED, PROCESSING, SUCCEEDED, FAILED, CANCELED, REFUNDED, PARTIALLY_REFUNDED)
• stripePaymentIntentId: Stripe PaymentIntent ID (pi_xxx)
• stripeChargeId: Stripe charge/refund ID (ch_xxx or re_xxx)
• stripeCustomerId: Stripe customer ID (cus_xxx)
• canceledByStripePaymentId: Self-referential FK to refund record

Status Lifecycle:

CREATED → PROCESSING → SUCCEEDED
                ↓
            FAILED / CANCELED
                ↓
            REFUNDED

Refund Pattern:

• Refunds create a NEW stripe_payments record with negative amountCent
• Original record gets status = REFUNDED and canceledByStripePaymentId points to refund record

pay_out

Completed payouts to talent accounts via Stripe Connect.

File: packages/db/src/schema/payOut.ts

Key Fields:

• id: nanoid primary key
• payeeAccountId: Account receiving the payout
• amountCent: Total payout amount in cents
• advanceAmountCent: Amount that was an advance (pre-earning payment)
• status: Payout status (PENDING, CHARGE, CANCELED, REFUND, EXTERNAL)
• stripeChargeId: Stripe Connect transfer ID (tr_xxx)
• isLiveMode: Live vs test mode flag

Status Lifecycle:

PENDING → CHARGE (on Stripe success)
      → CANCELED (on Stripe failure)

Note: Financial tables (pay_out, stripe_payments, payment_share_tracking) intentionally omit deletedAt - financial records must never be soft-deleted. Status transitions mark records as inactive while preserving audit trail.

payment_share_tracking

The heart of the revenue distribution system. Tracks who gets paid what from each incoming payment.

File: packages/db/src/schema/paymentShareTracking.ts

Key Fields:

• id: nanoid primary key
• payeeAccountId: Account receiving this share (always set, including system accounts)
• stripePaymentId: Foreign key to stripe_payments
• payOutId: Foreign key to pay_out (null until paid out)
• amountCent: Share amount in cents
• type: Share type enum (TALENT, AGENT, AMBASSADOR, PLATFORM, STRIPE_FEE, HOST_PARTNER, PRODUCER, LAWYER)
• status: Tracking status (OPEN, CLOSED, CANCELED, REFUNDED)
• canceledByTrackingId: Self-referential FK to refund tracking record

Status Lifecycle:

OPEN → CLOSED (on payout or advance offset)
    → CANCELED (on refund)
    → REFUNDED (on refund)

Auto-Closed Types:

• STRIPE_FEE: Auto-closed (Stripe deducts directly)
• PLATFORM: Auto-closed (stays in Stripe account)

Key Invariants:

• SUM(amountCent) WHERE stripePaymentId = X = stripePayment.amountCent
• SUM(amountCent) WHERE payOutId = X = payOut.amountCent

account_payout_routes

One record per account storing payout method configuration.

File: packages/db/src/schema/accountPayoutRoutes.ts

Key Fields:

• id: nanoid primary key
• accountId: Foreign key to account (unique, one-to-one)
• payoutMethod: Payout method enum (STRIPE_CONNECT, BANK_TRANSFER)
• stripeConnectAccountId: Stripe Connect account ID (acct_xxx)
• kycVerified: KYC verification status (must be true for payouts)

account_payment_settings

One-to-one relationship with account table for payment-related settings.

File: packages/db/src/schema/accountPaymentSettings.ts

Key Fields:

• id: nanoid primary key
• accountId: Foreign key to account (unique, one-to-one)
• minimumPayoutAmountCent: Payout threshold (default $100)
• stripeCustomerId: Buyer-side Stripe customer ID (cus_xxx)
• lastPayoutInspectionAt: Timestamp of last payout daemon check
• emailOutstandingPayout: Flag to prevent duplicate "outstanding payout" emails

payment_system_logs

Structured audit trail for all payment operations.

File: packages/db/src/schema/paymentSystemLogs.ts

Key Fields:

• id: nanoid primary key
• component: Component enum (SHARE_CALCULATION, PAYMENT_COMPLETION, PAYOUT_PROCESSING, INTEGRITY_CHECK, PAYOUT_DAEMON, REFUND, WEBHOOK)
• message: Human-readable log message
• data: JSONB structured log data
• stripePaymentId: Optional FK to payment
• payOutId: Optional FK to payout
• accountId: Optional FK to account
• isError: Error flag

ppu_codes

Unique PPU (Purchase Proof URL) codes generated per completed payment (1:1 with stripe_payments).

File: packages/db/src/schema/ppuCodes.ts

Key Fields:

• id: nanoid primary key
• code: Unique PPU code
• stripePaymentId: Foreign key to stripe_payments (unique)
• licenseType: License type (optional)
• licenseOwner: License owner (optional)

account_agents

Tracks which accounts are agents for which talent accounts, and their revenue share configuration.

File: packages/db/src/schema/accountAgents.ts

Key Fields:

• id: nanoid primary key
• agentAccountId: Agent's account ID
• talentAccountId: Talent's account ID
• canEdit: Can edit permission flag
• getsShare: Gets revenue share flag
• shareAmountCent: Share amount (basis points if PERCENTAGE, cents if FIXED_AMOUNT)
• shareUnit: Share unit enum (PERCENTAGE, FIXED_AMOUNT)
• deletedAt: Soft delete timestamp

Enums

All enums defined in packages/db/src/schema/paymentEnums.ts:

stripePaymentPayForEnum

MERCH, VOICE_OVER, LIKENESS, IMAGE, OFFER

stripePaymentStatusEnum

CREATED, PROCESSING, SUCCEEDED, FAILED, CANCELED, REFUNDED, PARTIALLY_REFUNDED

payOutStatusEnum

PENDING, CHARGE, CANCELED, REFUND, EXTERNAL

shareTrackingTypeEnum

TALENT, AGENT, AMBASSADOR, PLATFORM, STRIPE_FEE, HOST_PARTNER, PRODUCER, LAWYER

shareTrackingStatusEnum

OPEN, CLOSED, CANCELED, REFUNDED

payoutMethodEnum

STRIPE_CONNECT, BANK_TRANSFER

paymentLogComponentEnum

SHARE_CALCULATION, PAYMENT_COMPLETION, PAYOUT_PROCESSING, INTEGRITY_CHECK, PAYOUT_DAEMON, REFUND, WEBHOOK

agentShareUnitEnum

PERCENTAGE, FIXED_AMOUNT

System Accounts

Two system accounts with fixed IDs are required:

• zooly_acc: Platform account (receives PLATFORM share tracking records)
• stripe_acc: Stripe fee account (receives STRIPE_FEE share tracking records)

These are real account records with ownerUserId = null (no associated user). They must be seeded in the DB before any payment is processed.

Indexes

All tables have appropriate indexes for common query patterns:

• stripe_payments: buyerAccountId, sellerAccountId, status, stripePaymentIntentId, stripeChargeId
• pay_out: payeeAccountId, status
• payment_share_tracking: (payeeAccountId, status), stripePaymentId, payOutId, (type, status)
• payment_system_logs: component, isError, createdAt
• account_payment_settings: accountId, lastPayoutInspectionAt

Relationships

Next Steps

• Payment Flows - How payments flow through the system
• Implementation Reference - Code structure and key functions