Subscription Plans

Overview

AcqMarketplace operates on a freemium model with tiered subscription plans that unlock features and reduce transaction fees. The system integrates with Stripe for billing and provides dynamic feature access based on subscription status.

Subscription Tiers

Free Plan ($0/month)

Target: New users, casual browsers Features:

• Browse all active listings
• Limited contact capabilities (view-only messages)
• Basic search and filtering
• Commission: 10% on successful transactions
• Limitations: Cannot send messages, blurred financial data

Starter Plan ($19/month)

Target: Individual entrepreneurs, small business owners Features:

• Full messaging capabilities (5 messages/day)
• Access to financial data on listings
• Advanced search filters
• Basic analytics dashboard
• Commission: 7.5% on successful transactions
• Message Limit: 5 per day

Pro Plan ($49/month)

Target: Professional investors, serial entrepreneurs, agencies Features:

• Unlimited messaging
• Priority customer support
• Advanced analytics and insights
• Featured listing promotions
• Commission: 5% on successful transactions
• No Limits: Full platform access

Data Model

Subscription Management

-- Core subscription table
CREATE TABLE subscriptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES profiles(id),
  subscription_level text NOT NULL,
  subscription_status text NOT NULL,
  stripe_customer_id text,
  stripe_subscription_id text,
  current_period_start timestamptz,
  current_period_end timestamptz,
  cancel_at_period_end boolean DEFAULT false,
  price_amount integer, -- in cents
  currency text DEFAULT 'usd',
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

Feature Access Control

-- Feature definitions per plan
CREATE TABLE subscription_features (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  feature_key text UNIQUE NOT NULL,
  feature_name text NOT NULL,
  free_enabled boolean DEFAULT false,
  starter_enabled boolean DEFAULT false,
  pro_enabled boolean DEFAULT false,
  free_limit integer DEFAULT 0,
  starter_limit integer DEFAULT 0,
  pro_limit integer DEFAULT 0,
  is_numeric boolean DEFAULT false -- For countable features
);

Usage Tracking

-- Track feature usage for limits
CREATE TABLE subscription_feature_usage (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id uuid REFERENCES profiles(id),
  feature_key text NOT NULL,
  usage_count integer DEFAULT 0,
  last_reset_at timestamptz DEFAULT now(),
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

Stripe Integration

Pricing Summary (Actual)

• Monthly: Starter $19, Pro $49
• Annual (effective monthly): Starter $16, Pro $41

Price resolution

• Price IDs are managed in Stripe and resolved via Edge Functions (see supabase/functions/get-price-id/index.ts).
• The frontend uses the publishable key from env; server-side secrets live in Edge Functions (see docs/setup/stripe.md).

Subscription Management

Hook: useSubscription

const useSubscription = () => {
  const { data: subscription } = useQuery({
    queryKey: ['subscription'],
    queryFn: async () => {
      const { data } = await supabase
        .from('subscriptions')
        .select('*')
        .eq('user_id', user.id)
        .eq('subscription_status', 'active')
        .single()
      
      return data
    }
  })
  
  return {
    subscription,
    isActive: subscription?.subscription_status === 'active',
    plan: subscription?.subscription_level || 'free'
  }
}

Feature Gating System

Dynamic Feature Access

Function: get_user_subscription_level(user_id)

CREATE OR REPLACE FUNCTION get_user_subscription_level(p_user_id uuid)
RETURNS text AS $$
DECLARE
  v_subscription_level text;
BEGIN
  -- Check active subscription first
  SELECT subscription_level INTO v_subscription_level
  FROM subscriptions
  WHERE user_id = p_user_id 
    AND subscription_status = 'active'
  ORDER BY updated_at DESC
  LIMIT 1;
  
  -- Fallback to profile level
  IF v_subscription_level IS NULL THEN
    SELECT COALESCE(subscription_level, 'free') INTO v_subscription_level
    FROM profiles
    WHERE id = p_user_id;
  END IF;
  
  RETURN COALESCE(v_subscription_level, 'free');
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

Messaging Limits

Function: can_send_message(user_id)

CREATE OR REPLACE FUNCTION can_send_message(p_user_id uuid)
RETURNS boolean AS $$
DECLARE
  v_subscription_level text;
  v_daily_count integer;
BEGIN
  v_subscription_level := get_user_subscription_level(p_user_id);
  
  -- Free users cannot send messages
  IF v_subscription_level = 'free' THEN
    RETURN false;
  END IF;
  
  -- Pro users have unlimited messages
  IF v_subscription_level = 'pro' THEN
    RETURN true;
  END IF;
  
  -- Starter users: check daily limit (5 messages)
  IF v_subscription_level = 'starter' THEN
    SELECT COUNT(*) INTO v_daily_count
    FROM messages
    WHERE sender_id = p_user_id 
      AND DATE(created_at) = CURRENT_DATE;
    
    RETURN v_daily_count < 5;
  END IF;
  
  RETURN false;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

Data Access Control (Blur Policy)

Function: can_view_field(listing_id, field_key, user_id); configurable via blurred_fields. See the complete field list and per-plan matrix in docs/architecture/blur-policy.md.

-- Controls access to sensitive business data
CREATE OR REPLACE FUNCTION can_view_field(
  p_listing uuid, 
  p_field_key text, 
  p_user uuid DEFAULT auth.uid()
)
RETURNS boolean AS $$
DECLARE
  bf record;
  v_owner uuid;
  v_level text;
BEGIN
  -- Owner and admin bypass all restrictions
  SELECT seller_id INTO v_owner FROM listings WHERE id = p_listing;
  
  IF p_user = v_owner OR is_admin_user() THEN
    RETURN true;
  END IF;
  
  -- Get field configuration
  SELECT * INTO bf FROM blurred_fields WHERE field_key = p_field_key;
  
  IF NOT FOUND THEN
    RETURN true; -- Field not restricted
  END IF;
  
  -- Unauthenticated users
  IF p_user IS NULL THEN
    RETURN NOT COALESCE(bf.is_blurred_for_unauthenticated, false);
  END IF;
  
  -- Check subscription level
  v_level := get_user_subscription_level(p_user);
  
  CASE v_level
    WHEN 'pro' THEN 
      RETURN NOT COALESCE(bf.is_blurred_for_pro, false);
    WHEN 'starter' THEN 
      RETURN NOT COALESCE(bf.is_blurred_for_starter, false);
    ELSE 
      RETURN NOT COALESCE(bf.is_blurred_for_free, true);
  END CASE;
END;
$$ LANGUAGE plpgsql STABLE;

Commission Calculation

Tiered Commission System

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
  -- Get seller's subscription level
  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%
    END CASE;
  END IF;
  
  RETURN p_amount * v_rate;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

Subscription Management UI

Subscription Dashboard

File: src/pages/SubscriptionSettings.tsxFeatures:

• Current plan display with feature comparison
• Upgrade/downgrade buttons
• Billing history and invoices
• Usage statistics and limits
• Cancellation management

Plan Comparison Component

File: src/components/subscription/PlanComparison.tsx

const PlanComparison = () => {
  const features = [
    {
      name: 'Monthly Messages',
      free: 'View Only',
      starter: '5 per day',
      pro: 'Unlimited'
    },
    {
      name: 'Financial Data Access',
      free: 'Blurred',
      starter: 'Full Access', 
      pro: 'Full Access'
    },
    {
      name: 'Commission Rate',
      free: '10%',
      starter: '7.5%',
      pro: '5%'
    },
    {
      name: 'Support Level',
      free: 'Community',
      starter: 'Email Support',
      pro: 'Priority Support'
    }
  ]
  
  return (
    <div className="grid grid-cols-4 gap-4">
      {/* Feature comparison table */}
    </div>
  )
}

Upgrade Flow

const upgradeToPlan = async (planId: string) => {
  try {
    // Create Stripe Checkout session
    const { data } = await supabase.functions.invoke('create-checkout-session', {
      body: { 
        priceId: planId,
        userId: user.id,
        mode: 'subscription'
      }
    })
    
    // Redirect to Stripe Checkout
    window.location.href = data.url
  } catch (error) {
    toast.error('Failed to start upgrade process')
  }
}

Webhook Integration

Stripe Webhook Handler

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

• customer.subscription.created
• customer.subscription.updated
• customer.subscription.deleted
• invoice.payment_succeeded
• invoice.payment_failed

const handleSubscriptionUpdate = async (subscription: Stripe.Subscription) => {
  const { data, error } = await supabase
    .from('subscriptions')
    .upsert({
      stripe_subscription_id: subscription.id,
      stripe_customer_id: subscription.customer,
      subscription_status: subscription.status,
      subscription_level: getPlanLevel(subscription.items.data[0].price.id),
      current_period_start: new Date(subscription.current_period_start * 1000),
      current_period_end: new Date(subscription.current_period_end * 1000),
      cancel_at_period_end: subscription.cancel_at_period_end
    })
}

Usage Analytics

Subscription Metrics

const subscriptionAnalytics = {
  // Monthly Recurring Revenue
  mrr: async () => {
    const { data } = await supabase
      .from('subscriptions')
      .select('price_amount')
      .eq('subscription_status', 'active')
    
    return data.reduce((sum, sub) => sum + (sub.price_amount / 100), 0)
  },
  
  // Churn Rate
  churnRate: async (month: string) => {
    // Calculate cancellations vs. active subscriptions
  },
  
  // Plan Distribution
  planDistribution: async () => {
    const { data } = await supabase
      .from('subscriptions')
      .select('subscription_level')
      .eq('subscription_status', 'active')
    
    return data.reduce((acc, sub) => {
      acc[sub.subscription_level] = (acc[sub.subscription_level] || 0) + 1
      return acc
    }, {})
  }
}

User Upgrade Analytics

// Track upgrade paths and conversion rates
const upgradeTracking = {
  freeToStarter: 'Free users who upgrade to Starter within 30 days',
  starterToPro: 'Starter users who upgrade to Pro within 90 days',
  conversionByFeature: 'Which features drive the most upgrades'
}

Trial Management

Free Trial Implementation

const startFreeTrial = async (userId: string, planLevel: string) => {
  const trialEndDate = new Date()
  trialEndDate.setDate(trialEndDate.getDate() + 14) // 14-day trial
  
  await supabase.from('subscriptions').insert({
    user_id: userId,
    subscription_level: planLevel,
    subscription_status: 'trialing',
    trial_end: trialEndDate,
    trial_start: new Date()
  })
}

Trial Expiration Handling

// Check trial status and handle expiration
const checkTrialStatus = async (userId: string) => {
  const { data } = await supabase
    .from('subscriptions')
    .select('*')
    .eq('user_id', userId)
    .eq('subscription_status', 'trialing')
    .single()
  
  if (data && new Date(data.trial_end) < new Date()) {
    // Trial expired - downgrade to free
    await supabase
      .from('subscriptions')
      .update({ subscription_status: 'trial_expired' })
      .eq('id', data.id)
  }
}

Performance Considerations

Caching Strategy

• Subscription Status: Cache for 1 hour with real-time invalidation
• Feature Access: Cache per request to avoid repeated DB calls
• Usage Counters: Real-time for accuracy
• Plan Comparison: Static cache (CDN) with periodic updates

Database Optimization

-- Indexes for subscription queries
CREATE INDEX idx_subscriptions_user_status ON subscriptions(user_id, subscription_status);
CREATE INDEX idx_subscriptions_stripe_customer ON subscriptions(stripe_customer_id);
CREATE INDEX idx_feature_usage_user_feature ON subscription_feature_usage(user_id, feature_key);

Security Considerations

Subscription Verification

• Server-side Validation: Always verify subscription status server-side
• Webhook Verification: Validate all Stripe webhooks with signatures
• Access Control: Use RLS policies to enforce subscription-based access
• Rate Limiting: Prevent abuse of trial periods and feature access

Data Protection

• PCI Compliance: Stripe handles all payment card data
• Encrypted Storage: Sensitive subscription data encrypted at rest
• Audit Logging: Track all subscription changes and access attempts
• GDPR Compliance: Right to deletion includes subscription data

Testing Strategy

Unit Tests

• Subscription status calculations
• Feature access validation
• Commission rate calculations
• Usage limit enforcement

Integration Tests

• Stripe webhook processing
• Subscription upgrade/downgrade flows
• Feature gating enforcement
• Trial period management

E2E Tests

• Complete subscription purchase flow
• Feature access verification per plan
• Billing cycle and renewal testing
• Cancellation and reactivation

Troubleshooting

Common Subscription Issues

Webhook Processing Failures

• Verify webhook endpoint URL configuration
• Check Stripe webhook signature validation
• Review error logs for processing failures
• Confirm database permissions for webhook handler

Feature Access Problems

• Verify subscription status in database
• Check feature configuration in subscription_features
• Review RLS policies for feature access
• Confirm user authentication status

Commission Calculation Errors

• Verify seller subscription level accuracy
• Check commission tier configuration
• Review transaction amount validation
• Confirm calculation function logic

Future Enhancements

1. Annual Billing: Discounted annual subscription options
2. Team Plans: Multi-user business accounts
3. Add-on Features: À la carte feature purchases
4. Usage-Based Billing: Pay-per-transaction options
5. Enterprise Plans: Custom pricing for high-volume users

---

Related Documentation:

• Transactions - How commissions are calculated and processed
• Messaging - Subscription-based messaging limits
• Admin Guide - Subscription management tools