Transaction Processing

Overview

The transaction system handles all financial aspects of business deals, including commission calculation, payment processing, escrow management, and payout distribution. It integrates with Stripe for secure payment handling and provides comprehensive audit trails.

Transaction Lifecycle

graph LR
    A[Deal Initiated] --> B[Payment Required]
    B --> C[Buyer Funds Escrow]
    C --> D[Escrow Funded]
    D --> E[Due Diligence]
    E --> F[Asset Transfer]
    F --> G[Buyer Confirmation]
    G --> H[Admin Release Payout]
    H --> I[Payout Released]
    I --> J[Auto Finalization]
    J --> K[Transaction Complete]
    
    style H fill:#e1f5fe
    style I fill:#e8f5e8
    style J fill:#fff3e0
    style K fill:#f3e5f5

Data Model

Core Transaction Table

CREATE TABLE transactions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  listing_id uuid REFERENCES listings(id),
  buyer_id uuid REFERENCES profiles(id),
  seller_id uuid REFERENCES profiles(id),
  
  -- Financial Details
  amount numeric NOT NULL,              -- Base transaction amount
  amount_gross numeric,                 -- Total including fees
  commission_amount numeric,            -- Platform commission
  commission_rate numeric,              -- Applied commission rate
  
  -- Payment Processing
  stripe_payment_intent_id text,        -- Stripe payment reference
  stripe_transfer_id text,              -- Seller payout reference
  escrow_account_id text,               -- Escrow holding account
  
  -- Status and Timestamps
  status transaction_status DEFAULT 'initiated',
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  funded_at timestamptz,
  completed_at timestamptz
);

Transaction Status Types

CREATE TYPE transaction_status AS ENUM (
  'initiated',           -- Deal created, payment pending
  'payment_required',    -- Awaiting buyer payment
  'funded',             -- Escrow funded successfully
  'dd_in_progress',     -- Due diligence period
  'handover_in_progress', -- Asset transfer phase
  'buyer_accepts',      -- Buyer confirmed receipt
  'payout_released',    -- Seller payment processed
  'completed',          -- Transaction finalized
  'cancelled',          -- Deal cancelled
  'disputed',           -- Under dispute resolution
  'refunded'            -- Refunded to buyer
);

Commission System

Tiered Commission Structure

Based on seller subscription level:

Subscription	Commission Rate	Minimum Transaction
Free	10%	$1,000
Starter	7.5%	$1,000
Pro	5%	$1,000

Commission Calculation

Function: calculate_commission(amount, seller_id)

CREATE OR REPLACE FUNCTION calculate_commission(
  p_amount numeric, 
  p_seller_id uuid DEFAULT NULL
)
RETURNS numeric AS $$
DECLARE
  v_rate numeric := 0.10; -- Default 10%
  v_subscription_level text;
BEGIN
  IF p_seller_id IS NOT NULL THEN
    v_subscription_level := get_user_subscription_level(p_seller_id);
    
    CASE v_subscription_level
      WHEN 'pro' THEN v_rate := 0.05;     -- 5%
      WHEN 'starter' THEN v_rate := 0.075; -- 7.5%
      ELSE v_rate := 0.10;                 -- 10% for free users
    END CASE;
  END IF;
  
  RETURN p_amount * v_rate;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

Volume-Based Discounts (Future Enhancement)

-- Potential tiered structure based on transaction volume
CASE 
  WHEN p_amount >= 100000 THEN v_base_rate * 0.6  -- 40% discount for $100K+
  WHEN p_amount >= 50000 THEN v_base_rate * 0.8   -- 20% discount for $50K+
  WHEN p_amount >= 10000 THEN v_base_rate * 0.9   -- 10% discount for $10K+
  ELSE v_base_rate                                 -- Standard rate
END

Payment Processing

Stripe Integration Architecture

// Payment flow implementation
const processTransaction = async (transactionId: string, paymentMethodId: string) => {
  // 1. Calculate total amount including fees
  const transaction = await getTransaction(transactionId)
  const commission = await calculateCommission(transaction.amount, transaction.seller_id)
  const totalAmount = transaction.amount + commission
  
  // 2. Create Stripe Payment Intent
  const paymentIntent = await stripe.paymentIntents.create({
    amount: Math.round(totalAmount * 100), // Convert to cents
    currency: 'usd',
    payment_method: paymentMethodId,
    confirmation_method: 'manual',
    confirm: true,
    metadata: {
      transaction_id: transactionId,
      buyer_id: transaction.buyer_id,
      seller_id: transaction.seller_id
    }
  })
  
  // 3. Update transaction with payment details
  await supabase
    .from('transactions')
    .update({
      stripe_payment_intent_id: paymentIntent.id,
      amount_gross: totalAmount,
      commission_amount: commission,
      status: 'funded',
      funded_at: new Date()
    })
    .eq('id', transactionId)
}

Escrow Management

Purpose: Hold buyer funds securely until deal completion Implementation: Stripe Connect with delayed payouts

// Escrow funding
const fundEscrow = async (transactionId: string, paymentIntentId: string) => {
  // Funds are held in Stripe until manual release
  // No immediate transfer to seller
  
  const { data, error } = await supabase
    .from('transactions')
    .update({
      status: 'funded',
      funded_at: new Date(),
      stripe_payment_intent_id: paymentIntentId
    })
    .eq('id', transactionId)
  
  // Log escrow funding event
  await logTransactionEvent(transactionId, 'escrow_funded', {
    payment_intent_id: paymentIntentId,
    amount: transaction.amount_gross
  })
}

Payout System

Seller Payout Process

Edge Function: release-deal-payout

const releasePayout = async (transactionId: string) => {
  const transaction = await getTransaction(transactionId)
  
  // Calculate net payout (amount - commission)
  const netPayout = transaction.amount - transaction.commission_amount
  const commission = transaction.commission_amount
  
  // Create Stripe Transfer to seller
  const transfer = await stripe.transfers.create({
    amount: Math.round(netPayout * 100), // Convert to cents
    currency: 'usd',
    destination: transaction.seller_stripe_account_id,
    metadata: {
      transaction_id: transactionId,
      commission_amount: commission
    }
  })
  
  // Update transaction status to payout_released
  await supabase
    .from('transactions')
    .update({
      status: 'payout_released',
      stripe_transfer_id: transfer.id
    })
    .eq('id', transactionId)
  
  // Log payout event
  await supabase
    .from('deal_events_log')
    .insert({
      deal_id: transactionId,
      actor_id: userId,
      event_type: 'payout_released',
      details: {
        message: 'Payout released to seller',
        transfer_id: transfer.id,
        amount: netPayout,
        commission: commission,
        net_amount: netPayout
      }
    })
  
  // Automatically finalize the deal
  await finalizeDeal(transactionId)
}

Deal Finalization Process

Edge Function: finalize-deal

const finalizeDeal = async (transactionId: string) => {
  // Validate deal status (must be payout_released)
  const transaction = await getTransaction(transactionId)
  if (transaction.status !== 'payout_released') {
    throw new Error('Deal must be in payout_released status to finalize')
  }
  
  // Update status to completed
  await supabase
    .from('transactions')
    .update({ 
      status: 'completed',
      completed_at: new Date().toISOString()
    })
    .eq('id', transactionId)
  
  // Log the completion event
  await supabase
    .from('deal_events_log')
    .insert({
      deal_id: transactionId,
      actor_id: userId,
      event_type: 'deal_completed',
      details: {
        message: 'Deal finalized successfully',
        amount: transaction.amount,
        commission: transaction.commission_amount,
        net_amount: transaction.net_amount
      }
    })
  
  // Send notifications to both parties
  await supabase
    .from('notifications')
    .insert([
      {
        user_id: transaction.buyer_id,
        type: 'deal_completed',
        title: 'Deal Completed',
        message: 'Your deal has been completed successfully!',
        link: `/deal/${transactionId}`
      },
      {
        user_id: transaction.seller_id,
        type: 'deal_completed', 
        title: 'Deal Completed',
        message: 'Your deal has been completed successfully!',
        link: `/deal/${transactionId}`
      }
    ])
}

Payout Schedule

• Immediate: Upon buyer confirmation (standard)
• Delayed: 7-day hold for high-risk transactions
• Manual: Admin review required for disputed transactions

Fee Structure

Platform Fees

const calculateFees = (amount: number, sellerSubscription: string) => {
  const commissionRate = getCommissionRate(sellerSubscription)
  const commission = amount * commissionRate
  
  // Additional fees (if applicable)
  const paymentProcessingFee = amount * 0.029 + 0.30 // Stripe standard
  const crossBorderFee = isInternational ? amount * 0.01 : 0
  
  return {
    commission,
    paymentProcessing: paymentProcessingFee,
    crossBorder: crossBorderFee,
    total: commission + paymentProcessingFee + crossBorderFee
  }
}

Fee Transparency

Display to users:

• Base transaction amount
• Platform commission (tiered)
• Payment processing fees
• Total amount due
• Net amount to seller

Refund and Dispute Management

Refund Process

const processRefund = async (transactionId: string, reason: string, amount?: number) => {
  const transaction = await getTransaction(transactionId)
  const refundAmount = amount || transaction.amount_gross
  
  // Process Stripe refund
  const refund = await stripe.refunds.create({
    payment_intent: transaction.stripe_payment_intent_id,
    amount: Math.round(refundAmount * 100),
    reason: 'requested_by_customer',
    metadata: {
      transaction_id: transactionId,
      refund_reason: reason
    }
  })
  
  // Update transaction status
  await supabase
    .from('transactions')
    .update({
      status: 'refunded',
      refund_amount: refundAmount,
      refund_reason: reason
    })
    .eq('id', transactionId)
}

Dispute Resolution

1. Automatic Escalation: Unresolved issues after 48 hours
2. Admin Mediation: Platform team review
3. Partial Refunds: Compromise solutions
4. Full Refunds: Complete transaction reversal

Security and Compliance

Financial Security Measures

• PCI DSS Compliance: Stripe handles all card data
• Fraud Detection: Automated transaction monitoring
• KYC Verification: Identity verification for high-value transactions
• AML Compliance: Anti-money laundering checks

Audit Trail

-- Transaction event logging
CREATE TABLE transaction_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  transaction_id uuid REFERENCES transactions(id),
  event_type text NOT NULL,
  event_data jsonb,
  created_by uuid REFERENCES profiles(id),
  created_at timestamptz DEFAULT now()
);

Data Protection

• Encryption: All financial data encrypted at rest
• Access Control: Strict RLS policies on financial tables
• Audit Logging: Complete trail of all financial operations
• Data Retention: Compliance with financial record keeping requirements

Analytics and Reporting

Transaction Metrics

const transactionAnalytics = {
  // Total platform revenue
  totalRevenue: async (period: string) => {
    const { data } = await supabase
      .from('transactions')
      .select('commission_amount')
      .eq('status', 'completed')
      .gte('completed_at', period)
    
    return data.reduce((sum, tx) => sum + tx.commission_amount, 0)
  },
  
  // Average transaction value
  averageTransactionValue: async (period: string) => {
    const { data } = await supabase
      .from('transactions')
      .select('amount')
      .eq('status', 'completed')
      .gte('completed_at', period)
    
    return data.reduce((sum, tx) => sum + tx.amount, 0) / data.length
  },
  
  // Success rate
  successRate: async (period: string) => {
    const { data: all } = await supabase
      .from('transactions')
      .select('status')
      .gte('created_at', period)
    
    const completed = all.filter(tx => tx.status === 'completed').length
    return (completed / all.length) * 100
  }
}

Financial Reporting

• Daily Transaction Summary: Volume, revenue, commission
• Monthly Revenue Reports: Platform income and growth
• Seller Payout Reports: Individual seller earnings
• Tax Reporting: 1099 generation for US sellers

Performance Optimization

Database Optimization

-- Indexes for transaction queries
CREATE INDEX idx_transactions_status ON transactions(status, created_at);
CREATE INDEX idx_transactions_buyer ON transactions(buyer_id, status);
CREATE INDEX idx_transactions_seller ON transactions(seller_id, status);
CREATE INDEX idx_transactions_dates ON transactions(created_at, completed_at);

Caching Strategy

• Transaction Status: Real-time, no caching
• Commission Rates: 1-hour cache
• Analytics Data: 15-minute cache for dashboards
• Historical Reports: Daily cache refresh

API Integration

Webhook Handling

File: supabase/functions/stripe-webhooks/index.tsEvents:

• payment_intent.succeeded: Update transaction status
• payment_intent.payment_failed: Handle payment failures
• transfer.created: Log seller payouts
• refund.created: Process refund updates

const handlePaymentIntentSucceeded = async (paymentIntent: Stripe.PaymentIntent) => {
  const transactionId = paymentIntent.metadata.transaction_id
  
  await supabase
    .from('transactions')
    .update({
      status: 'funded',
      funded_at: new Date(),
      stripe_payment_intent_id: paymentIntent.id
    })
    .eq('id', transactionId)
  
  // Trigger next steps in deal process
  await triggerDueDiligencePhase(transactionId)
}

Testing Strategy

Unit Tests

• Commission calculation accuracy
• Fee structure calculations
• Status transition validation
• Refund amount calculations

Integration Tests

• Stripe payment processing
• Webhook event handling
• Database transaction integrity
• Payout distribution accuracy

Security Testing

• Payment data encryption
• Access control verification
• Fraud detection triggers
• Compliance validation

Troubleshooting

Common Payment Issues

Payment Failed

• Check card validity and limits
• Verify Stripe account configuration
• Review fraud detection triggers
• Confirm amount and currency settings

Payout Delays

• Verify seller Stripe Connect setup
• Check payout schedule configuration
• Review transaction status progression
• Confirm bank account details

Commission Discrepancies

• Verify seller subscription level
• Check commission calculation function
• Review transaction amount accuracy
• Confirm rate tier application

Monitoring and Alerts

• Failed Payments: Immediate notification
• Stuck Transactions: Daily review
• High-Value Transactions: Manual review triggers
• Refund Requests: Admin notification

Future Enhancements

1. Multi-Currency Support: International transaction processing
2. Installment Payments: Milestone-based payment plans
3. Cryptocurrency: Bitcoin and stablecoin payment options
4. Advanced Analytics: Machine learning for fraud detection
5. Automated Underwriting: Risk-based transaction approval

---

Related Documentation:

• Deals System - Transaction context and workflow
• Subscriptions - Commission rate determination
• Security & Privacy - Financial data protection