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:

1// app/api/webhooks/stripe/route.ts - EXTERNAL webhook (assumed already implemented)
2export async function POST(request: NextRequest) {
3  const sig = headers().get('stripe-signature');
4  const body = await request.text();
5  
6  // Verify Stripe webhook signature
7  const event = stripe.webhooks.constructEvent(body, sig!, process.env.STRIPE_WEBHOOK_SECRET!);
8  
9  // Update your database based on the Stripe event
10  if (event.type === 'payment_intent.succeeded') {
11    await supabase
12      .from('payments')
13      .update({ status: 'succeeded' })
14      .eq('stripe_payment_id', event.data.object.id);
15  }
16  
17  return NextResponse.json({ received: true });
18}

 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

1graph TD
2    A[Database Operation] --> B{Operation Type}
3    B -->|INSERT| C[Insert Trigger]
4    B -->|UPDATE| D[Update Trigger] 
5    B -->|DELETE| E[Delete Trigger]
6    B -->|Custom Event| F[Custom Trigger]
7    
8    C --> G[Trigger Fires]
9    D --> G
10    E --> G
11    F --> G
12    
13    style A fill:#333333
14    style G fill:#333000

Step 2

1graph TD
2    A[Trigger Fired] --> B[Webhook System Activated]
3    B --> C[Data Collection]
4    
5    C --> D[Payload Assembly]
6    D --> E[Changed Data]
7    D --> F[User Information]
8    D --> G[Timestamps]
9    D --> H[Operation Type]
10    D --> I[Metadata]
11    
12    E --> J[Complete Payload]
13    F --> J
14    G --> J
15    H --> J
16    I --> J
17    
18    style A fill:#333333
19    style B fill:#333333
20    style J fill:#333000

Step 3

1graph TD
2    A[Complete Payload] --> B[Payload Validation]
3    B --> C{Valid?}
4    C -->|No| D[Log Error]
5    C -->|Yes| E[HTTP POST Request]
6    
7    E --> F[Send to Endpoint]
8    F --> G{Response Status}
9    
10    G -->|Success 2xx| H[Log Success]
11    G -->|Failure 4xx/5xx| I[Retry Mechanism]
12    
13    I --> J{Retry Count < Max?}
14    J -->|Yes| K[Wait & Retry]
15    J -->|No| L[Log Final Failure]
16    
17    K --> F
18    
19    style A fill:#333333
20    style E fill:#333333
21    style H fill:#333333
22    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.

1// types/webhook.ts
2export interface SupabaseWebhookPayload<T = any> {
3  type: 'INSERT' | 'UPDATE' | 'DELETE';
4  table: string;
5  schema: string;
6  record: T | null;
7  old_record: T | null;
8  created_at: string;
9  user_id?: string;
10}
11
12export interface WebhookAuthPayload {
13  type: 'user.created' | 'user.updated' | 'user.deleted' | 'user.signed_in';
14  user: {
15    id: string;
16    email: string;
17    created_at: string;
18    updated_at: string;
19    user_metadata: Record<string, any>;
20    app_metadata: Record<string, any>;
21  };
22  created_at: string;
23}

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.

1// app/api/webhooks/supabase/route.ts
2import { NextRequest, NextResponse } from 'next/server';
3import { headers } from 'next/headers';
4import { createServerClient } from '@supabase/ssr';
5import { SupabaseWebhookPayload } from '@/types/webhook';
6
7export async function POST(request: NextRequest) {
8  try {
9    // Verify the webhook signature
10    const headersList = headers();
11    const signature = headersList.get('x-supabase-signature');
12    const timestamp = headersList.get('x-supabase-timestamp');
13    
14    if (!signature || !timestamp) {
15      return NextResponse.json(
16        { error: 'Missing webhook signature or timestamp' },
17        { status: 401 }
18      );
19    }
20
21    // Parse the webhook payload
22    const payload: SupabaseWebhookPayload = await request.json();
23    
24    // Create Supabase client for server-side operations
25    const supabase = createServerClient(
26      process.env.NEXT_PUBLIC_SUPABASE_URL!,
27      process.env.SUPABASE_SERVICE_KEY!,
28      {
29        cookies: {
30          get: () => '',
31          set: () => {},
32          remove: () => {},
33        },
34      }
35    );
36
37    // Route to appropriate handler based on table and type
38    await handleWebhookEvent(payload, supabase);
39
40    return NextResponse.json({ success: true });
41  } catch (error) {
42    console.error('Webhook processing error:', error);
43    return NextResponse.json(
44      { error: 'Webhook processing failed' },
45      { status: 500 }
46    );
47  }
48}

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.

1// lib/webhook-security.ts
2import crypto from 'crypto';
3
4export function verifyWebhookSignature(
5  payload: string,
6  signature: string,
7  timestamp: string,
8  secret: string
9): boolean {
10  try {
11    // Create the expected signature
12    const expectedSignature = crypto
13      .createHmac('sha256', secret)
14      .update(timestamp + '.' + payload)
15      .digest('hex');
16
17    // Compare signatures using a constant-time comparison
18    return crypto.timingSafeEqual(
19      Buffer.from(signature, 'hex'),
20      Buffer.from(expectedSignature, 'hex')
21    );
22  } catch (error) {
23    console.error('Signature verification error:', error);
24    return false;
25  }
26}
27
28export function validateTimestamp(timestamp: string, toleranceSeconds = 300): boolean {
29  const webhookTime = parseInt(timestamp, 10) * 1000; // Convert to milliseconds
30  const currentTime = Date.now();
31  const timeDifference = Math.abs(currentTime - webhookTime);
32  
33  return timeDifference <= toleranceSeconds * 1000;
34}
35
36// Enhanced webhook route with security
37export async function POST(request: NextRequest) {
38  try {
39    const body = await request.text();
40    const headersList = headers();
41    const signature = headersList.get('x-supabase-signature');
42    const timestamp = headersList.get('x-supabase-timestamp');
43
44    // Validate required headers
45    if (!signature || !timestamp) {
46      return NextResponse.json(
47        { error: 'Missing required webhook headers' },
48        { status: 401 }
49      );
50    }
51
52    // Verify timestamp to prevent replay attacks
53    if (!validateTimestamp(timestamp)) {
54      return NextResponse.json(
55        { error: 'Webhook timestamp is too old' },
56        { status: 401 }
57      );
58    }
59
60    // Verify webhook signature
61    if (!verifyWebhookSignature(body, signature, timestamp, process.env.SUPABASE_WEBHOOK_SECRET!)) {
62      return NextResponse.json(
63        { error: 'Invalid webhook signature' },
64        { status: 401 }
65      );
66    }
67
68    const payload: SupabaseWebhookPayload = JSON.parse(body);
69    await handleWebhookEvent(payload);
70
71    return NextResponse.json({ success: true });
72  } catch (error) {
73    console.error('Webhook error:', error);
74    return NextResponse.json(
75      { error: 'Internal server error' },
76      { status: 500 }
77    );
78  }
79}

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.

1// types/payment.ts
2export interface PaymentRecord {
3  id: string;
4  user_id: string;
5  amount: number;
6  currency: string;
7  status: 'pending' | 'succeeded' | 'failed' | 'canceled' | 'refunded';
8  payment_method: string;
9  subscription_id?: string;
10  created_at: string;
11  updated_at: string;
12  metadata: Record<string, any>;
13}
14
15export interface SubscriptionRecord {
16  id: string;
17  user_id: string;
18  plan_id: string;
19  status: 'active' | 'inactive' | 'canceled' | 'past_due';
20  current_period_start: string;
21  current_period_end: string;
22  cancel_at_period_end: boolean;
23  created_at: string;
24  updated_at: string;
25}
26
27// lib/payment-handler.ts
28import { createServerClient } from '@supabase/ssr';
29import { PaymentRecord, SubscriptionRecord } from '@/types/payment';
30
31export class PaymentWebhookHandler {
32  private supabase;
33
34  constructor(supabaseUrl: string, serviceKey: string) {
35    this.supabase = createServerClient(supabaseUrl, serviceKey, {
36      cookies: { get: () => '', set: () => {}, remove: () => {} },
37    });
38  }
39
40  async handlePaymentEvent(payload: SupabaseWebhookPayload<PaymentRecord>) {
41    const { type, record, old_record } = payload;
42
43    switch (type) {
44      case 'INSERT':
45        await this.handleNewPayment(record!);
46        break;
47      case 'UPDATE':
48        await this.handlePaymentUpdate(record!, old_record!);
49        break;
50      case 'DELETE':
51        await this.handlePaymentDeletion(old_record!);
52        break;
53    }
54  }
55
56  private async handleNewPayment(payment: PaymentRecord) {
57    console.log(`New payment created: ${payment.id} for user ${payment.user_id}`);
58    
59    // Update user's payment history
60    await this.updateUserPaymentStats(payment.user_id, payment);
61    
62    // Send confirmation email if payment succeeded
63    if (payment.status === 'succeeded') {
64      await this.sendPaymentConfirmation(payment);
65    }
66    
67    // Handle subscription-related payments
68    if (payment.subscription_id) {
69      await this.updateSubscriptionStatus(payment);
70    }
71  }
72
73  private async handlePaymentUpdate(payment: PaymentRecord, oldPayment: PaymentRecord) {
74    // Check if payment status changed
75    if (payment.status !== oldPayment.status) {
76      console.log(`Payment ${payment.id} status changed from ${oldPayment.status} to ${payment.status}`);
77      
78      switch (payment.status) {
79        case 'succeeded':
80          await this.activateUserServices(payment.user_id);
81          await this.sendPaymentConfirmation(payment);
82          break;
83        case 'failed':
84          await this.handlePaymentFailure(payment);
85          break;
86        case 'refunded':
87          await this.handleRefund(payment);
88          break;
89      }
90    }
91  }
92
93  private async updateUserPaymentStats(userId: string, payment: PaymentRecord) {
94    const { data: user } = await this.supabase
95      .from('user_profiles')
96      .select('payment_stats')
97      .eq('id', userId)
98      .single();
99
100    const currentStats = user?.payment_stats || {
101      total_payments: 0,
102      total_amount: 0,
103      last_payment_at: null,
104    };
105
106    const updatedStats = {
107      total_payments: currentStats.total_payments + 1,
108      total_amount: currentStats.total_amount + payment.amount,
109      last_payment_at: payment.created_at,
110    };
111
112    await this.supabase
113      .from('user_profiles')
114      .update({ payment_stats: updatedStats })
115      .eq('id', userId);
116  }
117}

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.

1// types/rbac.ts
2export interface Role {
3  id: string;
4  name: string;
5  description: string;
6  level: number; // Higher numbers indicate more permissions
7  permissions: Permission[];
8  created_at: string;
9  updated_at: string;
10}
11
12export interface Permission {
13  id: string;
14  name: string;
15  resource: string;
16  action: string; // 'create', 'read', 'update', 'delete'
17  conditions?: Record<string, any>;
18}
19
20export interface UserRole {
21  id: string;
22  user_id: string;
23  role_id: string;
24  assigned_at: string;
25  assigned_by: string;
26  expires_at?: string;
27  is_active: boolean;
28}
29
30// lib/rbac-handler.ts
31export class RBACWebhookHandler {
32  private supabase;
33
34  constructor(supabaseUrl: string, serviceKey: string) {
35    this.supabase = createServerClient(supabaseUrl, serviceKey, {
36      cookies: { get: () => '', set: () => {}, remove: () => {} },
37    });
38  }
39
40  async handleUserRoleChange(payload: SupabaseWebhookPayload<UserRole>) {
41    const { type, record, old_record } = payload;
42
43    switch (type) {
44      case 'INSERT':
45        await this.handleRoleAssignment(record!);
46        break;
47      case 'UPDATE':
48        await this.handleRoleUpdate(record!, old_record!);
49        break;
50      case 'DELETE':
51        await this.handleRoleRevocation(old_record!);
52        break;
53    }
54  }
55
56  private async handleRoleAssignment(userRole: UserRole) {
57    // Get the role details
58    const { data: role } = await this.supabase
59      .from('roles')
60      .select('*, permissions(*)')
61      .eq('id', userRole.role_id)
62      .single();
63
64    if (!role) {
65      console.error(`Role ${userRole.role_id} not found`);
66      return;
67    }
68
69    // Update user's permission cache
70    await this.updateUserPermissions(userRole.user_id);
71    
72    // Log the role assignment
73    await this.logRoleChange({
74      user_id: userRole.user_id,
75      action: 'role_assigned',
76      role_name: role.name,
77      assigned_by: userRole.assigned_by,
78      timestamp: userRole.assigned_at,
79    });
80
81    // Send notification to user
82    await this.notifyRoleChange(userRole.user_id, 'assigned', role.name);
83  }
84
85  private async updateUserPermissions(userId: string) {
86    // Get all active roles for the user
87    const { data: userRoles } = await this.supabase
88      .from('user_roles')
89      .select(`
90        role_id,
91        roles (
92          name,
93          level,
94          permissions (*)
95        )
96      `)
97      .eq('user_id', userId)
98      .eq('is_active', true)
99      .or(`expires_at.is.null,expires_at.gt.${new Date().toISOString()}`);
100
101    if (!userRoles) return;
102
103    // Aggregate all permissions from active roles
104    const allPermissions = new Set<string>();
105    let highestRoleLevel = 0;
106
107    userRoles.forEach(userRole => {
108      const role = userRole.roles as any;
109      if (role.level > highestRoleLevel) {
110        highestRoleLevel = role.level;
111      }
112      
113      role.permissions.forEach((permission: Permission) => {
114        allPermissions.add(`${permission.resource}:${permission.action}`);
115      });
116    });
117
118    // Update user's permission cache in the database
119    await this.supabase
120      .from('user_profiles')
121      .update({
122        permissions: Array.from(allPermissions),
123        role_level: highestRoleLevel,
124        permissions_updated_at: new Date().toISOString(),
125      })
126      .eq('id', userId);
127  }
128
129  async checkPermission(userId: string, resource: string, action: string): Promise<boolean> {
130    const { data: user } = await this.supabase
131      .from('user_profiles')
132      .select('permissions, role_level')
133      .eq('id', userId)
134      .single();
135
136    if (!user) return false;
137
138    const requiredPermission = `${resource}:${action}`;
139    return user.permissions?.includes(requiredPermission) || false;
140  }
141}

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.

1// lib/webhook-dispatcher.ts
2import { PaymentWebhookHandler } from './payment-handler';
3import { RBACWebhookHandler } from './rbac-handler';
4import { AuthWebhookHandler } from './auth-handler';
5import { SupabaseWebhookPayload, WebhookAuthPayload } from '@/types/webhook';
6
7export class WebhookDispatcher {
8  private paymentHandler: PaymentWebhookHandler;
9  private rbacHandler: RBACWebhookHandler;
10  private authHandler: AuthWebhookHandler;
11
12  constructor() {
13    const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
14    const serviceKey = process.env.SUPABASE_SERVICE_KEY!;
15
16    this.paymentHandler = new PaymentWebhookHandler(supabaseUrl, serviceKey);
17    this.rbacHandler = new RBACWebhookHandler(supabaseUrl, serviceKey);
18    this.authHandler = new AuthWebhookHandler(supabaseUrl, serviceKey);
19  }
20
21  async dispatch(payload: SupabaseWebhookPayload | WebhookAuthPayload) {
22    try {
23      // Determine the webhook type and route accordingly
24      if ('table' in payload) {
25        await this.handleDatabaseWebhook(payload);
26      } else {
27        await this.handleAuthWebhook(payload);
28      }
29    } catch (error) {
30      console.error('Webhook dispatch error:', error);
31      await this.handleWebhookError(payload, error);
32      throw error; // Re-throw to ensure proper HTTP status code
33    }
34  }
35
36  private async handleDatabaseWebhook(payload: SupabaseWebhookPayload) {
37    const { table } = payload;
38
39    switch (table) {
40      case 'payments':
41        await this.paymentHandler.handlePaymentEvent(payload);
42        break;
43      case 'subscriptions':
44        await this.paymentHandler.handleSubscriptionEvent(payload);
45        break;
46      case 'user_roles':
47        await this.rbacHandler.handleUserRoleChange(payload);
48        break;
49      case 'roles':
50        await this.rbacHandler.handleRoleDefinitionChange(payload);
51        break;
52      case 'user_profiles':
53        await this.handleUserProfileChange(payload);
54        break;
55      default:
56        console.warn(`Unhandled table webhook: ${table}`);
57    }
58  }
59
60  private async handleAuthWebhook(payload: WebhookAuthPayload) {
61    const { type } = payload;
62
63    switch (type) {
64      case 'user.created':
65        await this.authHandler.handleUserCreated(payload);
66        break;
67      case 'user.updated':
68        await this.authHandler.handleUserUpdated(payload);
69        break;
70      case 'user.signed_in':
71        await this.authHandler.handleUserSignedIn(payload);
72        break;
73      case 'user.deleted':
74        await this.authHandler.handleUserDeleted(payload);
75        break;
76      default:
77        console.warn(`Unhandled auth webhook: ${type}`);
78    }
79  }
80
81  private async handleWebhookError(payload: any, error: any) {
82    // Log the error for debugging
83    console.error('Webhook processing failed:', {
84      payload,
85      error: error.message,
86      stack: error.stack,
87      timestamp: new Date().toISOString(),
88    });
89
90    // Store failed webhook for retry processing
91    await this.storeFailedWebhook(payload, error);
92  }
93
94  private async storeFailedWebhook(payload: any, error: any) {
95    // Implementation would store failed webhooks in a queue for retry
96    // This could use a database table, Redis queue, or message queue service
97  }
98}
99
100// Main webhook handler function
101export async function handleWebhookEvent(payload: SupabaseWebhookPayload | WebhookAuthPayload) {
102  const dispatcher = new WebhookDispatcher();
103  await dispatcher.dispatch(payload);
104}

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.

1// lib/auth-handler.ts
2export class AuthWebhookHandler {
3  private supabase;
4
5  constructor(supabaseUrl: string, serviceKey: string) {
6    this.supabase = createServerClient(supabaseUrl, serviceKey, {
7      cookies: { get: () => '', set: () => {}, remove: () => {} },
8    });
9  }
10
11  async handleUserCreated(payload: WebhookAuthPayload) {
12    const { user } = payload;
13    
14    try {
15      // Create comprehensive user profile
16      await this.createUserProfile(user);
17      
18      // Assign default role
19      await this.assignDefaultRole(user.id);
20      
21      // Send welcome email
22      await this.sendWelcomeEmail(user);
23      
24      // Initialize user preferences
25      await this.initializeUserPreferences(user.id);
26      
27      console.log(`User profile created for ${user.email}`);
28    } catch (error) {
29      console.error('Error handling user creation:', error);
30      throw error;
31    }
32  }
33
34  private async createUserProfile(user: any) {
35    const profileData = {
36      id: user.id,
37      email: user.email,
38      full_name: user.user_metadata?.full_name || '',
39      avatar_url: user.user_metadata?.avatar_url || '',
40      created_at: user.created_at,
41      updated_at: user.updated_at,
42      email_verified: user.email_confirmed_at ? true : false,
43      phone_verified: user.phone_confirmed_at ? true : false,
44      last_sign_in_at: null,
45      sign_in_count: 0,
46      preferences: {
47        theme: 'light',
48        notifications: {
49          email: true,
50          push: false,
51          marketing: false,
52        },
53        privacy: {
54          profile_visible: false,
55          show_email: false,
56        },
57      },
58      metadata: user.user_metadata || {},
59    };
60
61    const { error } = await this.supabase
62      .from('user_profiles')
63      .insert(profileData);
64
65    if (error) {
66      console.error('Error creating user profile:', error);
67      throw error;
68    }
69  }
70
71  private async assignDefaultRole(userId: string) {
72    // Get the default role (e.g., 'user' role)
73    const { data: defaultRole } = await this.supabase
74      .from('roles')
75      .select('id')
76      .eq('name', 'user')
77      .single();
78
79    if (!defaultRole) {
80      console.error('Default role not found');
81      return;
82    }
83
84    // Assign the default role to the user
85    const { error } = await this.supabase
86      .from('user_roles')
87      .insert({
88        user_id: userId,
89        role_id: defaultRole.id,
90        assigned_by: 'system',
91        assigned_at: new Date().toISOString(),
92        is_active: true,
93      });
94
95    if (error) {
96      console.error('Error assigning default role:', error);
97      throw error;
98    }
99  }
100
101  async handleUserSignedIn(payload: WebhookAuthPayload) {
102    const { user } = payload;
103    
104    try {
105      // Update last sign-in timestamp and increment counter
106      await this.updateSignInStats(user.id);
107      
108      // Check for suspicious activity
109      await this.checkSuspiciousActivity(user.id);
110      
111      // Update user's online status
112      await this.updateUserStatus(user.id, 'online');
113      
114      console.log(`User ${user.email} signed in`);
115    } catch (error) {
116      console.error('Error handling user sign-in:', error);
117    }
118  }
119
120  private async updateSignInStats(userId: string) {
121    const { data: profile } = await this.supabase
122      .from('user_profiles')
123      .select('sign_in_count')
124      .eq('id', userId)
125      .single();
126
127    const currentCount = profile?.sign_in_count || 0;
128
129    await this.supabase
130      .from('user_profiles')
131      .update({
132        last_sign_in_at: new Date().toISOString(),
133        sign_in_count: currentCount + 1,
134      })
135      .eq('id', userId);
136  }
137
138  private async checkSuspiciousActivity(userId: string) {
139    // Get recent sign-in attempts
140    const { data: recentSignIns } = await this.supabase
141      .from('auth_logs')
142      .select('*')
143      .eq('user_id', userId)
144      .eq('action', 'sign_in')
145      .gte('created_at', new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString())
146      .order('created_at', { ascending: false })
147      .limit(10);
148
149    if (recentSignIns && recentSignIns.length > 5) {
150      // More than 5 sign-ins in 24 hours - potentially suspicious
151      await this.flagSuspiciousActivity(userId, 'frequent_logins');
152    }
153  }
154
155  async handleUserDeleted(payload: WebhookAuthPayload) {
156    const { user } = payload;
157    
158    try {
159      // Clean up user data while preserving audit trails
160      await this.cleanupUserData(user.id);
161      
162      // Anonymize rather than delete for compliance
163      await this.anonymizeUserData(user.id);
164      
165      console.log(`User ${user.id} data cleaned up`);
166    } catch (error) {
167      console.error('Error handling user deletion:', error);
168    }
169  }
170
171  private async cleanupUserData(userId: string) {
172    // Remove personal data but keep audit trails
173    await this.supabase
174      .from('user_profiles')
175      .update({
176        email: `deleted_user_${userId}@deleted.local`,
177        full_name: 'Deleted User',
178        avatar_url: null,
179        deleted_at: new Date().toISOString(),
180      })
181      .eq('id', userId);
182
183    // Deactivate all roles
184    await this.supabase
185      .from('user_roles')
186      .update({ is_active: false })
187      .eq('user_id', userId);
188  }
189}

 Part 2 is UP!