The Complete Guide to Supabase Webhooks: Building a reliable Payment & Auth Context with RBAC PART-1

~What This Guide Covers (Important Context)

Before we get deep into Supabase webhooks, it's mandatory to understand the scope of what we're building together. This guide focuses on the internal webhook processing system that handles events after they've already been received and validated by your server. We're not covering the initial external webhook setup from payment providers like Stripe, PayPal, or authentication services - that's assumed to be already configured.

Think of it this way: when Stripe sends a webhook to your /api/webhooks/stripe endpoint about a successful payment, your external webhook handler might look something like this:

// app/api/webhooks/stripe/route.ts - EXTERNAL webhook (assumed already implemented)
export async function POST(request: NextRequest) {
  const sig = headers().get('stripe-signature');
  const body = await request.text();
  
  // Verify Stripe webhook signature
  const event = stripe.webhooks.constructEvent(body, sig!, process.env.STRIPE_WEBHOOK_SECRET!);
  
  // Update your database based on the Stripe event
  if (event.type === 'payment_intent.succeeded') {
    await supabase
      .from('payments')
      .update({ status: 'succeeded' })
      .eq('stripe_payment_id', event.data.object.id);
  }
  
  return NextResponse.json({ received: true });
}

What happens next is where our guide begins. When that Supabase database update occurs (changing the payment status), it triggers a Supabase webhook - an internal system that responds to your own database changes. The entire system we'll build together handles these internal Supabase-generated webhooks, creating a reliable event-driven architecture that automatically manages user permissions, subscription tiers, and business logic based on your database state changes.

This approach gives you the best of both worlds: external webhooks handle third-party integrations, while internal Supabase webhooks manage your application's business logic and state synchronization.

The Business Problem We're Solving

Supabase webhooks are one of the most powerful features for building real-time, event-driven applications. They allow your application to react instantly to database changes, user authentication events, and custom triggers without constantly polling your database. In this comprehensive guide, we'll build a complete payment and authentication system with Role-Based Access Control (RBAC) using Next.js 15+ App Router, TypeScript, and Supabase webhooks.

Our specific challenges:

• Users have different subscription tiers (Free, Pro, Enterprise)
• Each tier has different permissions (create projects, invite team members etc.)
• When someone upgrades/downgrades, their access should change immediately
• When payment fails, they should be downgraded automatically

Webhooks work by sending HTTP POST requests to your specified endpoint whenever certain events occur in your Supabase project. Think of them as automated messengers that notify your application about important changes. Whether a user signs up, a payment is processed, or data is modified, webhooks ensure your application stays in perfect sync with your database state. This real-time synchronization is mandatory for modern applications where users expect immediate feedback and smooth experiences.

Understanding Supabase Webhook Architecture

Before getting into code, let's understand how Supabase webhooks operate under the hood. When you create a webhook in Supabase, you're essentially creating a database trigger that fires whenever specific conditions are met. These triggers can be configured to activate on INSERT, UPDATE, DELETE, or even custom events. The webhook system then takes the triggered data, formats it into a structured payload, and sends it to your designated endpoint via HTTP POST request.

Step 1

graph TD
    A[Database Operation] --> B{Operation Type}
    B -->|INSERT| C[Insert Trigger]
    B -->|UPDATE| D[Update Trigger] 
    B -->|DELETE| E[Delete Trigger]
    B -->|Custom Event| F[Custom Trigger]
    
    C --> G[Trigger Fires]
    D --> G
    E --> G
    F --> G
    
    style A fill:#333333
    style G fill:#333000

Step 2

graph TD
    A[Trigger Fired] --> B[Webhook System Activated]
    B --> C[Data Collection]
    
    C --> D[Payload Assembly]
    D --> E[Changed Data]
    D --> F[User Information]
    D --> G[Timestamps]
    D --> H[Operation Type]
    D --> I[Metadata]
    
    E --> J[Complete Payload]
    F --> J
    G --> J
    H --> J
    I --> J
    
    style A fill:#333333
    style B fill:#333333
    style J fill:#333000

Step 3

graph TD
    A[Complete Payload] --> B[Payload Validation]
    B --> C{Valid?}
    C -->|No| D[Log Error]
    C -->|Yes| E[HTTP POST Request]
    
    E --> F[Send to Endpoint]
    F --> G{Response Status}
    
    G -->|Success 2xx| H[Log Success]
    G -->|Failure 4xx/5xx| I[Retry Mechanism]
    
    I --> J{Retry Count < Max?}
    J -->|Yes| K[Wait & Retry]
    J -->|No| L[Log Final Failure]
    
    K --> F
    
    style A fill:#333333
    style E fill:#333333
    style H fill:#333333
    style L fill:#333000

The beauty of Supabase webhooks lies in their reliability and flexibility. They include built-in retry mechanisms, payload validation, and detailed logging. Each webhook payload contains not only the changed data but also metadata about the operation, including the user who made the change, timestamps, and the type of operation performed. This rich context allows you to build sophisticated business logic that responds appropriately to different scenarios.

// types/webhook.ts
export interface SupabaseWebhookPayload<T = any> {
  type: 'INSERT' | 'UPDATE' | 'DELETE';
  table: string;
  schema: string;
  record: T | null;
  old_record: T | null;
  created_at: string;
  user_id?: string;
}

export interface WebhookAuthPayload {
  type: 'user.created' | 'user.updated' | 'user.deleted' | 'user.signed_in';
  user: {
    id: string;
    email: string;
    created_at: string;
    updated_at: string;
    user_metadata: Record<string, any>;
    app_metadata: Record<string, any>;
  };
  created_at: string;
}

Setting Up Your Next.js 15+ Environment

The foundation of our webhook system starts with a properly configured Next.js application. Next.js 15+ with the App Router provides excellent TypeScript support and makes it easy to create API routes that can handle webhook requests. We'll structure our application to separate concerns clearly, with dedicated handlers for different types of webhook events.

Our project structure will include dedicated API routes for handling webhooks, TypeScript types for strong typing, utility functions for common operations, and React components that respond to real-time changes. The App Router's file-based routing system makes it intuitive to organize webhook endpoints, and the built-in middleware support allows us to add authentication and validation layers easily.

// app/api/webhooks/supabase/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { headers } from 'next/headers';
import { createServerClient } from '@supabase/ssr';
import { SupabaseWebhookPayload } from '@/types/webhook';

export async function POST(request: NextRequest) {
  try {
    // Verify the webhook signature
    const headersList = headers();
    const signature = headersList.get('x-supabase-signature');
    const timestamp = headersList.get('x-supabase-timestamp');
    
    if (!signature || !timestamp) {
      return NextResponse.json(
        { error: 'Missing webhook signature or timestamp' },
        { status: 401 }
      );
    }

    // Parse the webhook payload
    const payload: SupabaseWebhookPayload = await request.json();
    
    // Create Supabase client for server-side operations
    const supabase = createServerClient(
      process.env.NEXT_PUBLIC_SUPABASE_URL!,
      process.env.SUPABASE_SERVICE_KEY!,
      {
        cookies: {
          get: () => '',
          set: () => {},
          remove: () => {},
        },
      }
    );

    // Route to appropriate handler based on table and type
    await handleWebhookEvent(payload, supabase);

    return NextResponse.json({ success: true });
  } catch (error) {
    console.error('Webhook processing error:', error);
    return NextResponse.json(
      { error: 'Webhook processing failed' },
      { status: 500 }
    );
  }
}

Implementing Webhook Security and Validation

Security is paramount when handling webhooks, as they create public endpoints that external services can access. Supabase provides webhook signatures that allow you to verify that requests are genuinely coming from your Supabase instance and haven't been tampered with during transmission. This verification process involves comparing a hash of the payload with the signature provided in the request headers.

The signature verification uses HMAC-SHA256 encryption with your webhook secret key. This cryptographic approach ensures that only requests with the correct signature (which requires knowledge of your secret key) can be processed. Additionally, implementing timestamp validation prevents replay attacks where malicious actors might attempt to resend old webhook requests.

// lib/webhook-security.ts
import crypto from 'crypto';

export function verifyWebhookSignature(
  payload: string,
  signature: string,
  timestamp: string,
  secret: string
): boolean {
  try {
    // Create the expected signature
    const expectedSignature = crypto
      .createHmac('sha256', secret)
      .update(timestamp + '.' + payload)
      .digest('hex');

    // Compare signatures using a constant-time comparison
    return crypto.timingSafeEqual(
      Buffer.from(signature, 'hex'),
      Buffer.from(expectedSignature, 'hex')
    );
  } catch (error) {
    console.error('Signature verification error:', error);
    return false;
  }
}

export function validateTimestamp(timestamp: string, toleranceSeconds = 300): boolean {
  const webhookTime = parseInt(timestamp, 10) * 1000; // Convert to milliseconds
  const currentTime = Date.now();
  const timeDifference = Math.abs(currentTime - webhookTime);
  
  return timeDifference <= toleranceSeconds * 1000;
}

// Enhanced webhook route with security
export async function POST(request: NextRequest) {
  try {
    const body = await request.text();
    const headersList = headers();
    const signature = headersList.get('x-supabase-signature');
    const timestamp = headersList.get('x-supabase-timestamp');

    // Validate required headers
    if (!signature || !timestamp) {
      return NextResponse.json(
        { error: 'Missing required webhook headers' },
        { status: 401 }
      );
    }

    // Verify timestamp to prevent replay attacks
    if (!validateTimestamp(timestamp)) {
      return NextResponse.json(
        { error: 'Webhook timestamp is too old' },
        { status: 401 }
      );
    }

    // Verify webhook signature
    if (!verifyWebhookSignature(body, signature, timestamp, process.env.SUPABASE_WEBHOOK_SECRET!)) {
      return NextResponse.json(
        { error: 'Invalid webhook signature' },
        { status: 401 }
      );
    }

    const payload: SupabaseWebhookPayload = JSON.parse(body);
    await handleWebhookEvent(payload);

    return NextResponse.json({ success: true });
  } catch (error) {
    console.error('Webhook error:', error);
    return NextResponse.json(
      { error: 'Internal server error' },
      { status: 500 }
    );
  }
}

Building the Payment Context System

Payment processing requires careful orchestration of multiple systems - your payment provider, your database, and your user interface. Webhooks play a mandatory role in this ecosystem by ensuring that payment status changes are immediately reflected throughout your application. When a payment succeeds, fails, or requires additional verification, your webhook system can update user records, send notifications, and trigger business logic automatically.

Our payment context system will handle subscription management, one-time payments, refunds, and payment method updates. Each payment event triggers specific actions that maintain data consistency and provide users with immediate feedback. The system also implements idempotency to handle duplicate webhook calls gracefully, which is essential for financial transactions where accuracy is critical.

// types/payment.ts
export interface PaymentRecord {
  id: string;
  user_id: string;
  amount: number;
  currency: string;
  status: 'pending' | 'succeeded' | 'failed' | 'canceled' | 'refunded';
  payment_method: string;
  subscription_id?: string;
  created_at: string;
  updated_at: string;
  metadata: Record<string, any>;
}

export interface SubscriptionRecord {
  id: string;
  user_id: string;
  plan_id: string;
  status: 'active' | 'inactive' | 'canceled' | 'past_due';
  current_period_start: string;
  current_period_end: string;
  cancel_at_period_end: boolean;
  created_at: string;
  updated_at: string;
}

// lib/payment-handler.ts
import { createServerClient } from '@supabase/ssr';
import { PaymentRecord, SubscriptionRecord } from '@/types/payment';

export class PaymentWebhookHandler {
  private supabase;

  constructor(supabaseUrl: string, serviceKey: string) {
    this.supabase = createServerClient(supabaseUrl, serviceKey, {
      cookies: { get: () => '', set: () => {}, remove: () => {} },
    });
  }

  async handlePaymentEvent(payload: SupabaseWebhookPayload<PaymentRecord>) {
    const { type, record, old_record } = payload;

    switch (type) {
      case 'INSERT':
        await this.handleNewPayment(record!);
        break;
      case 'UPDATE':
        await this.handlePaymentUpdate(record!, old_record!);
        break;
      case 'DELETE':
        await this.handlePaymentDeletion(old_record!);
        break;
    }
  }

  private async handleNewPayment(payment: PaymentRecord) {
    console.log(`New payment created: ${payment.id} for user ${payment.user_id}`);
    
    // Update user's payment history
    await this.updateUserPaymentStats(payment.user_id, payment);
    
    // Send confirmation email if payment succeeded
    if (payment.status === 'succeeded') {
      await this.sendPaymentConfirmation(payment);
    }
    
    // Handle subscription-related payments
    if (payment.subscription_id) {
      await this.updateSubscriptionStatus(payment);
    }
  }

  private async handlePaymentUpdate(payment: PaymentRecord, oldPayment: PaymentRecord) {
    // Check if payment status changed
    if (payment.status !== oldPayment.status) {
      console.log(`Payment ${payment.id} status changed from ${oldPayment.status} to ${payment.status}`);
      
      switch (payment.status) {
        case 'succeeded':
          await this.activateUserServices(payment.user_id);
          await this.sendPaymentConfirmation(payment);
          break;
        case 'failed':
          await this.handlePaymentFailure(payment);
          break;
        case 'refunded':
          await this.handleRefund(payment);
          break;
      }
    }
  }

  private async updateUserPaymentStats(userId: string, payment: PaymentRecord) {
    const { data: user } = await this.supabase
      .from('user_profiles')
      .select('payment_stats')
      .eq('id', userId)
      .single();

    const currentStats = user?.payment_stats || {
      total_payments: 0,
      total_amount: 0,
      last_payment_at: null,
    };

    const updatedStats = {
      total_payments: currentStats.total_payments + 1,
      total_amount: currentStats.total_amount + payment.amount,
      last_payment_at: payment.created_at,
    };

    await this.supabase
      .from('user_profiles')
      .update({ payment_stats: updatedStats })
      .eq('id', userId);
  }
}

Implementing Role-Based Access Control (RBAC)

RBAC systems become incredibly powerful when combined with webhooks because they can automatically adjust user permissions based on real-time events. When a user's subscription expires, their role can be automatically downgraded. When they upgrade their plan, new permissions can be instantly granted. This automation ensures that your application's security model stays perfectly synchronized with your business logic.

Our RBAC implementation will use hierarchical roles where higher-level roles inherit permissions from lower levels. This approach simplifies permission management while providing fine-grained control over user capabilities. The webhook system will monitor role changes, permission updates, and subscription status changes to maintain accurate access control throughout your application.

// types/rbac.ts
export interface Role {
  id: string;
  name: string;
  description: string;
  level: number; // Higher numbers indicate more permissions
  permissions: Permission[];
  created_at: string;
  updated_at: string;
}

export interface Permission {
  id: string;
  name: string;
  resource: string;
  action: string; // 'create', 'read', 'update', 'delete'
  conditions?: Record<string, any>;
}

export interface UserRole {
  id: string;
  user_id: string;
  role_id: string;
  assigned_at: string;
  assigned_by: string;
  expires_at?: string;
  is_active: boolean;
}

// lib/rbac-handler.ts
export class RBACWebhookHandler {
  private supabase;

  constructor(supabaseUrl: string, serviceKey: string) {
    this.supabase = createServerClient(supabaseUrl, serviceKey, {
      cookies: { get: () => '', set: () => {}, remove: () => {} },
    });
  }

  async handleUserRoleChange(payload: SupabaseWebhookPayload<UserRole>) {
    const { type, record, old_record } = payload;

    switch (type) {
      case 'INSERT':
        await this.handleRoleAssignment(record!);
        break;
      case 'UPDATE':
        await this.handleRoleUpdate(record!, old_record!);
        break;
      case 'DELETE':
        await this.handleRoleRevocation(old_record!);
        break;
    }
  }

  private async handleRoleAssignment(userRole: UserRole) {
    // Get the role details
    const { data: role } = await this.supabase
      .from('roles')
      .select('*, permissions(*)')
      .eq('id', userRole.role_id)
      .single();

    if (!role) {
      console.error(`Role ${userRole.role_id} not found`);
      return;
    }

    // Update user's permission cache
    await this.updateUserPermissions(userRole.user_id);
    
    // Log the role assignment
    await this.logRoleChange({
      user_id: userRole.user_id,
      action: 'role_assigned',
      role_name: role.name,
      assigned_by: userRole.assigned_by,
      timestamp: userRole.assigned_at,
    });

    // Send notification to user
    await this.notifyRoleChange(userRole.user_id, 'assigned', role.name);
  }

  private async updateUserPermissions(userId: string) {
    // Get all active roles for the user
    const { data: userRoles } = await this.supabase
      .from('user_roles')
      .select(`
        role_id,
        roles (
          name,
          level,
          permissions (*)
        )
      `)
      .eq('user_id', userId)
      .eq('is_active', true)
      .or(`expires_at.is.null,expires_at.gt.${new Date().toISOString()}`);

    if (!userRoles) return;

    // Aggregate all permissions from active roles
    const allPermissions = new Set<string>();
    let highestRoleLevel = 0;

    userRoles.forEach(userRole => {
      const role = userRole.roles as any;
      if (role.level > highestRoleLevel) {
        highestRoleLevel = role.level;
      }
      
      role.permissions.forEach((permission: Permission) => {
        allPermissions.add(`${permission.resource}:${permission.action}`);
      });
    });

    // Update user's permission cache in the database
    await this.supabase
      .from('user_profiles')
      .update({
        permissions: Array.from(allPermissions),
        role_level: highestRoleLevel,
        permissions_updated_at: new Date().toISOString(),
      })
      .eq('id', userId);
  }

  async checkPermission(userId: string, resource: string, action: string): Promise<boolean> {
    const { data: user } = await this.supabase
      .from('user_profiles')
      .select('permissions, role_level')
      .eq('id', userId)
      .single();

    if (!user) return false;

    const requiredPermission = `${resource}:${action}`;
    return user.permissions?.includes(requiredPermission) || false;
  }
}

Creating Comprehensive Webhook Handlers

A reliable webhook system requires handlers that can process various types of events efficiently and reliably. Our comprehensive handler system will route different webhook types to appropriate processors, implement retry logic for failed operations, and maintain detailed logs for debugging and monitoring. This modular approach makes it easy to add new webhook types and modify existing behavior without affecting other parts of the system.

The handler system implements the Command pattern, where each webhook type has a dedicated handler class that knows how to process that specific type of event. This separation of concerns makes the code more maintainable and testable. Additionally, we'll implement a queue system for processing webhooks asynchronously, which is mandatory for handling high-volume webhook traffic without blocking your application.

// lib/webhook-dispatcher.ts
import { PaymentWebhookHandler } from './payment-handler';
import { RBACWebhookHandler } from './rbac-handler';
import { AuthWebhookHandler } from './auth-handler';
import { SupabaseWebhookPayload, WebhookAuthPayload } from '@/types/webhook';

export class WebhookDispatcher {
  private paymentHandler: PaymentWebhookHandler;
  private rbacHandler: RBACWebhookHandler;
  private authHandler: AuthWebhookHandler;

  constructor() {
    const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
    const serviceKey = process.env.SUPABASE_SERVICE_KEY!;

    this.paymentHandler = new PaymentWebhookHandler(supabaseUrl, serviceKey);
    this.rbacHandler = new RBACWebhookHandler(supabaseUrl, serviceKey);
    this.authHandler = new AuthWebhookHandler(supabaseUrl, serviceKey);
  }

  async dispatch(payload: SupabaseWebhookPayload | WebhookAuthPayload) {
    try {
      // Determine the webhook type and route accordingly
      if ('table' in payload) {
        await this.handleDatabaseWebhook(payload);
      } else {
        await this.handleAuthWebhook(payload);
      }
    } catch (error) {
      console.error('Webhook dispatch error:', error);
      await this.handleWebhookError(payload, error);
      throw error; // Re-throw to ensure proper HTTP status code
    }
  }

  private async handleDatabaseWebhook(payload: SupabaseWebhookPayload) {
    const { table } = payload;

    switch (table) {
      case 'payments':
        await this.paymentHandler.handlePaymentEvent(payload);
        break;
      case 'subscriptions':
        await this.paymentHandler.handleSubscriptionEvent(payload);
        break;
      case 'user_roles':
        await this.rbacHandler.handleUserRoleChange(payload);
        break;
      case 'roles':
        await this.rbacHandler.handleRoleDefinitionChange(payload);
        break;
      case 'user_profiles':
        await this.handleUserProfileChange(payload);
        break;
      default:
        console.warn(`Unhandled table webhook: ${table}`);
    }
  }

  private async handleAuthWebhook(payload: WebhookAuthPayload) {
    const { type } = payload;

    switch (type) {
      case 'user.created':
        await this.authHandler.handleUserCreated(payload);
        break;
      case 'user.updated':
        await this.authHandler.handleUserUpdated(payload);
        break;
      case 'user.signed_in':
        await this.authHandler.handleUserSignedIn(payload);
        break;
      case 'user.deleted':
        await this.authHandler.handleUserDeleted(payload);
        break;
      default:
        console.warn(`Unhandled auth webhook: ${type}`);
    }
  }

  private async handleWebhookError(payload: any, error: any) {
    // Log the error for debugging
    console.error('Webhook processing failed:', {
      payload,
      error: error.message,
      stack: error.stack,
      timestamp: new Date().toISOString(),
    });

    // Store failed webhook for retry processing
    await this.storeFailedWebhook(payload, error);
  }

  private async storeFailedWebhook(payload: any, error: any) {
    // Implementation would store failed webhooks in a queue for retry
    // This could use a database table, Redis queue, or message queue service
  }
}

// Main webhook handler function
export async function handleWebhookEvent(payload: SupabaseWebhookPayload | WebhookAuthPayload) {
  const dispatcher = new WebhookDispatcher();
  await dispatcher.dispatch(payload);
}

Authentication Event Handling

Authentication events are among the most critical webhooks to handle correctly because they directly impact user experience and security. When users sign up, sign in, update their profiles, or delete their accounts, your application needs to respond immediately with appropriate actions. These might include creating user profiles, sending welcome emails, updating activity logs, or cleaning up associated data.

Our authentication webhook handler will create comprehensive user profiles when new users register, track login patterns for security monitoring, synchronize user data across different systems, and ensure proper cleanup when users delete their accounts. The system also implements security features like detecting suspicious login patterns and automatically locking accounts when necessary.

// lib/auth-handler.ts
export class AuthWebhookHandler {
  private supabase;

  constructor(supabaseUrl: string, serviceKey: string) {
    this.supabase = createServerClient(supabaseUrl, serviceKey, {
      cookies: { get: () => '', set: () => {}, remove: () => {} },
    });
  }

  async handleUserCreated(payload: WebhookAuthPayload) {
    const { user } = payload;
    
    try {
      // Create comprehensive user profile
      await this.createUserProfile(user);
      
      // Assign default role
      await this.assignDefaultRole(user.id);
      
      // Send welcome email
      await this.sendWelcomeEmail(user);
      
      // Initialize user preferences
      await this.initializeUserPreferences(user.id);
      
      console.log(`User profile created for ${user.email}`);
    } catch (error) {
      console.error('Error handling user creation:', error);
      throw error;
    }
  }

  private async createUserProfile(user: any) {
    const profileData = {
      id: user.id,
      email: user.email,
      full_name: user.user_metadata?.full_name || '',
      avatar_url: user.user_metadata?.avatar_url || '',
      created_at: user.created_at,
      updated_at: user.updated_at,
      email_verified: user.email_confirmed_at ? true : false,
      phone_verified: user.phone_confirmed_at ? true : false,
      last_sign_in_at: null,
      sign_in_count: 0,
      preferences: {
        theme: 'light',
        notifications: {
          email: true,
          push: false,
          marketing: false,
        },
        privacy: {
          profile_visible: false,
          show_email: false,
        },
      },
      metadata: user.user_metadata || {},
    };

    const { error } = await this.supabase
      .from('user_profiles')
      .insert(profileData);

    if (error) {
      console.error('Error creating user profile:', error);
      throw error;
    }
  }

  private async assignDefaultRole(userId: string) {
    // Get the default role (e.g., 'user' role)
    const { data: defaultRole } = await this.supabase
      .from('roles')
      .select('id')
      .eq('name', 'user')
      .single();

    if (!defaultRole) {
      console.error('Default role not found');
      return;
    }

    // Assign the default role to the user
    const { error } = await this.supabase
      .from('user_roles')
      .insert({
        user_id: userId,
        role_id: defaultRole.id,
        assigned_by: 'system',
        assigned_at: new Date().toISOString(),
        is_active: true,
      });

    if (error) {
      console.error('Error assigning default role:', error);
      throw error;
    }
  }

  async handleUserSignedIn(payload: WebhookAuthPayload) {
    const { user } = payload;
    
    try {
      // Update last sign-in timestamp and increment counter
      await this.updateSignInStats(user.id);
      
      // Check for suspicious activity
      await this.checkSuspiciousActivity(user.id);
      
      // Update user's online status
      await this.updateUserStatus(user.id, 'online');
      
      console.log(`User ${user.email} signed in`);
    } catch (error) {
      console.error('Error handling user sign-in:', error);
    }
  }

  private async updateSignInStats(userId: string) {
    const { data: profile } = await this.supabase
      .from('user_profiles')
      .select('sign_in_count')
      .eq('id', userId)
      .single();

    const currentCount = profile?.sign_in_count || 0;

    await this.supabase
      .from('user_profiles')
      .update({
        last_sign_in_at: new Date().toISOString(),
        sign_in_count: currentCount + 1,
      })
      .eq('id', userId);
  }

  private async checkSuspiciousActivity(userId: string) {
    // Get recent sign-in attempts
    const { data: recentSignIns } = await this.supabase
      .from('auth_logs')
      .select('*')
      .eq('user_id', userId)
      .eq('action', 'sign_in')
      .gte('created_at', new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString())
      .order('created_at', { ascending: false })
      .limit(10);

    if (recentSignIns && recentSignIns.length > 5) {
      // More than 5 sign-ins in 24 hours - potentially suspicious
      await this.flagSuspiciousActivity(userId, 'frequent_logins');
    }
  }

  async handleUserDeleted(payload: WebhookAuthPayload) {
    const { user } = payload;
    
    try {
      // Clean up user data while preserving audit trails
      await this.cleanupUserData(user.id);
      
      // Anonymize rather than delete for compliance
      await this.anonymizeUserData(user.id);
      
      console.log(`User ${user.id} data cleaned up`);
    } catch (error) {
      console.error('Error handling user deletion:', error);
    }
  }

  private async cleanupUserData(userId: string) {
    // Remove personal data but keep audit trails
    await this.supabase
      .from('user_profiles')
      .update({
        email: `deleted_user_${userId}@deleted.local`,
        full_name: 'Deleted User',
        avatar_url: null,
        deleted_at: new Date().toISOString(),
      })
      .eq('id', userId);

    // Deactivate all roles
    await this.supabase
      .from('user_roles')
      .update({ is_active: false })
      .eq('user_id', userId);
  }
}

Part 2 is UP!