Implementation Reference

File Structure

Database Schema

Location: packages/db/src/schema/

• paymentEnums.ts - All payment-related pgEnums
• stripePayments.ts - Master transaction record table
• payOut.ts - Completed payouts table
• paymentShareTracking.ts - Revenue distribution tracking table
• paymentSystemLogs.ts - Audit trail table
• accountPayoutRoutes.ts - Payout method configuration table
• accountPaymentSettings.ts - Payment settings (one-to-one with account)
• ppu.ts - Proof of purchase codes table
• accountAgents.ts - Agent relationships table

Access Layer

Location: packages/db/src/access/

• stripePayments.ts - Payment CRUD operations

  • createStripePayment() - Create payment record
  • getStripePaymentById() - Get payment by ID
  • getStripePaymentByStripeId() - Get by Stripe PaymentIntent ID
  • markStripePaymentCompleted() - Atomic completion with FOR UPDATE lock
  • updateStripePaymentStatus() - Update status
  • updateStripePaymentStripeId() - Set PaymentIntent ID
  • listStripePaymentsByAccount() - List payments for account

• payOut.ts - Payout CRUD operations

  • createPayOut() - Create payout record
  • getPayOutById() - Get payout by ID
  • listPayoutsByAccount() - List payouts for account
  • getPayoutsWithAdvanceBalance() - Get payouts with advance > 0
  • reduceAdvanceAmount() - Atomic decrement advance
  • incrementAdvanceAmount() - Atomic increment advance
  • updatePayOutStatus() - Update status and Stripe transfer ID
  • cancelPayoutAndReopenTracking() - Compensation action for failed transfers

• paymentShareTracking.ts - Share tracking operations

  • createShareTracking() - Create single tracking record
  • createShareTrackingBatch() - Batch create tracking records
  • getOpenTrackingSumByAccount() - Sum OPEN tracking for account
  • getOpenTrackingByAccount() - Get all OPEN tracking for account
  • closeTrackingRecords() - Batch close tracking records
  • cancelTrackingRecord() - Cancel tracking (for refunds)
  • listTrackingByStripePayment() - Get tracking for payment
  • listTrackingByPayOut() - Get tracking for payout

• accountPayoutRoutes.ts - Payout route operations

  • getPayoutRouteByAccountId() - Get payout route
  • upsertPayoutRoute() - Create or update payout route
  • updateKycStatus() - Update KYC verification status

• accountPaymentSettings.ts - Payment settings operations

  • getAccountPaymentSettings() - Get settings for account
  • upsertAccountPaymentSettings() - Create or update settings
  • updateLastPayoutInspection() - Update inspection timestamp
  • updateEmailOutstandingPayout() - Update email flag

• paymentSystemLogs.ts - Logging operations

  • logPaymentEvent() - Create log entry
  • getRecentLogs() - Query logs with filters

• ppuCodes.ts - PPU code operations

  • createPpuCode() - Generate and create code
  • getPpuCodeByStripePayment() - Get code by payment
  • getPpuCodeByCode() - Get code by code string

• accountAgents.ts - Agent relationship operations

  • getAgentsByTalentAccountId() - Get agents for talent
  • getAgentByIds() - Get specific agent-talent relationship
  • createAccountAgent() - Create agent relationship
  • updateAccountAgent() - Update agent fields
  • deleteAccountAgent() - Soft delete agent
  • getShareEligibleAgents() - Get agents eligible for share

• account.ts - Account operations (extended)

  • getAccountsNeedingPayoutInspection() - Find accounts for daemon
  • updateLastPayoutInspection() - Update inspection timestamp

Service Layer

Location: packages/srv-stripe-payment/src/

• stripe.ts - Stripe client and operations

  • getStripe() - Lazy-initialized Stripe client singleton
  • createStripePaymentIntent() - Create PaymentIntent
  • retrieveStripePaymentIntent() - Retrieve PaymentIntent with expanded charge (used by client completion)
  • createOrGetStripeCustomer() - Get or create Stripe customer
  • createStripeTransfer() - Create Stripe Connect transfer
  • createStripeRefund() - Create Stripe refund
  • verifyWebhookSignature() - Verify webhook signature

• getProductByPayForId.ts - Product resolution and pricing

  • getProductByPayForId() - Resolve product by payFor/payForId, returns PaymentProduct with full pricing breakdown (includes OFFER from offers row; status guards for checkout vs settlement)
  • computePriceData() - Calculate 3-way split (Stripe fee, platform fee, talent gross)

• insertShareTrackingForProduct.ts - Share tracking + advance offset (used by default completion and by releaseOfferEscrowForOffer)

• createPaymentIntent.ts - Payment intent creation

  • createPaymentIntent() - Orchestrates payment intent creation (fetches product, creates DB record, creates Stripe PaymentIntent)

• completePayment.ts - Payment completion engine

  • completePayment() - Atomic payment completion: default path uses insertShareTrackingForProduct; OFFER uses applyOfferPaymentEscrow (no tracking until release)
  • completePaymentFromClient() - Client-side wrapper that verifies with Stripe API before calling completePayment()

• releaseOfferEscrowForOffer.ts - After buyer accepts delivery: insertShareTrackingForProduct + mark offer completed (idempotent)

• refund.ts - Refund processing

  • processRefund() - Full refund with reversal of all tracking

• advancePayout.ts - Advance payout mechanism

  • processAdvancePayout() - Create advance payout

• payoutDaemon.ts - Daily payout cron daemon

  • payoutDaemon() - Main daemon loop

• doPayout.ts - Per-account payout logic

  • doPayout() - Process payout for single account

• paymentLogger.ts - Structured logging helper

  • logPaymentEvent() - Convenience wrapper for logging

API Routes

Location: apps/zooly-app/app/api/payments/

• create-intent/route.ts - Create payment intent endpoint
• complete/route.ts - Payment completion endpoint (primary path, verifies with Stripe)
• history/route.ts - Payment history endpoint
• payout-status/route.ts - Payout status endpoint
• payout-route/route.ts - Payout route configuration endpoint
• refund/route.ts - Refund endpoint
• process-payouts/route.ts - Payout cron endpoint
• webhook/stripe/route.ts - Stripe webhook handler
• admin/do-payout/route.ts - Manual payout endpoint
• admin/advance-payout/route.ts - Advance payout endpoint

Key Implementation Patterns

Transaction Pattern

Access functions that participate in multi-step transactions accept an optional tx parameter:

type DbOrTx = typeof db | Parameters<Parameters<typeof db.transaction>[0]>[0];

export async function createStripePayment(
  data: NewStripePayment,
  tx?: DbOrTx
): Promise<StripePayment> {
  const conn = tx ?? db;
  const [result] = await conn.insert(stripePaymentsTable).values(data).returning();
  return result;
}

Service layer uses transactions:

return await db.transaction(async (tx) => {
  const payment = await createStripePayment(data, tx);
  const tracking = await createShareTrackingBatch(records, tx);
  // ... more operations
});

Atomic Operations

Critical paths use database transactions with row-level locks:

// Atomic double-spend prevention
const payment = await tx
  .select()
  .from(stripePaymentsTable)
  .where(and(
    eq(stripePaymentsTable.id, stripePaymentId),
    eq(stripePaymentsTable.status, "CREATED")
  ))
  .for("update")  // Row-level lock
  .limit(1);

Idempotency

Webhook handlers are idempotent:

const payment = await markStripePaymentCompleted(stripePaymentId);
if (!payment) {
  // Already completed - return existing result
  const existingCode = await getPpuCodeByStripePayment(stripePaymentId);
  return { success: true, alreadyCompleted: true, ppuCode: existingCode };
}

Two-Phase Commit

Payout processing uses two-phase commit:

// Phase 1: DB
const payOut = await createPayOut({ status: "PENDING", ... });
await closeTrackingRecords(trackingIds, payOut.id);

// Phase 2: Stripe
const transfer = await createStripeTransfer({ ... });

// Phase 3: Confirm
if (transfer) {
  await updatePayOutStatus(payOut.id, "CHARGE", transfer.id);
} else {
  await cancelPayoutAndReopenTracking(payOut.id, trackingIds);
}

Constants

Location: packages/srv-stripe-payment/src/getProductByPayForId.ts and packages/util/src/calculateStripeFees.ts

const PLATFORM_FEE_STANDARD_CENT = 500;  // $5.00 fixed AI creation cost

// Stripe fee constants (in @zooly/util)
const STRIPE_FEE_FIXED_CENT = 30;        // $0.30
const STRIPE_FEE_PERCENTAGE_BPS = 290;   // 2.9% (29 per 1000)

Note: These constants are currently hardcoded. TODO: Move to environment variables or configuration file.

Type Definitions

Location: packages/types/src/types/

• StripePayment.ts - Payment types and enums
• PayOut.ts - Payout types and enums
• PaymentShareTracking.ts - Share tracking types and enums
• AccountAgent.ts - Agent types and enums
• AccountPayoutRoute.ts - Payout route types and enums
• PpuCode.ts - PPU code types

Database Migrations

Location: packages/db/migrations/

Migrations are generated using Drizzle:

cd packages/db
npm run db:generate    # Generate migration files
npm run db:migrate     # Apply migrations

System Accounts Seeding

Location: packages/db/src/seedSystemAccounts.ts

System accounts must be seeded before any payment processing:

npm run db:seed:system-accounts

Creates:

• zooly_acc - Platform account
• stripe_acc - Stripe fee account

Testing Considerations

Unit Tests

• Service functions should be tested with mocked database access
• Stripe operations should use Stripe test mode
• Transaction rollback ensures test isolation

Integration Tests

• Use test database with migrations applied
• Use Stripe test mode with test API keys
• Webhook tests require signature generation

Test Data

• Create test accounts with ownerUserId set
• Create system accounts (zooly_acc, stripe_acc)
• Create test payout routes with test Stripe Connect accounts

Environment Setup

Required Environment Variables

STRIPE_SECRET_KEY=sk_test_xxx
STRIPE_PUBLISHABLE_KEY=pk_test_xxx
STRIPE_WEBHOOK_SECRET=whsec_xxx
CRON_SECRET=your-cron-secret
DATABASE_URL=postgresql://...

Stripe Webhook Testing (Local)

# Install Stripe CLI
# Forward webhooks to local dev server
stripe listen --forward-to localhost:3004/api/payments/webhook/stripe

Vercel Cron Configuration

Add to apps/zooly-app/vercel.json:

{
  "crons": [
    {
      "path": "/api/payments/process-payouts",
      "schedule": "0 14 * * *"
    }
  ]
}

Next Steps

• TODOs and Gaps - Known limitations and future work
• Database Schema - Complete database structure