Supabase Integration

AdRecon uses Supabase as its complete backend infrastructure, providing authentication, PostgreSQL database, Row Level Security (RLS), and real-time queries. All ad feeds, user data, and projects are stored and secured through Supabase.

Overview

Supabase powers:

Authentication: Magic Link email + Google OAuth with automatic session management
Database: PostgreSQL with optimized views for ad feeds, saved ads, and projects
Row Level Security: User-scoped data access enforced at the database level
Real-time: Auto-refreshing queries for saved ads and project changes
Admin API: Server-side user management via Supabase Auth Admin API

Setup

1. Environment Configuration

Set these environment variables in .env:

VITE_SUPABASE_URL=https://your-project.supabase.co
VITE_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

2. Client Initialization

The Supabase client is created with resilient fetch and auto-refresh:

src/lib/supabase.ts:87

export const supabase: SupabaseClient | null = isSupabaseConfigured
  ? createClient(supabaseUrl!, supabaseAnonKey!, {
      auth: {
        autoRefreshToken: true,
        persistSession: true,
        detectSessionInUrl: true,
      },
      global: {
        fetch: resilientSupabaseFetch, // Custom fetch with timeout + retry
      },
    })
  : null;

export function getSupabaseClient(): SupabaseClient {
  if (!supabase) {
    throw new Error('Supabase is not configured. Set VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY.');
  }
  return supabase;
}

The custom resilientSupabaseFetch adds 12-second timeout and automatic retry with backoff (400ms, 1200ms) for 5xx errors and timeouts.

3. Resilient Fetch Implementation

src/lib/supabase.ts:37

const REQUEST_TIMEOUT_MS = 12_000;
const RETRY_BACKOFF_MS = [400, 1200];

function isRetryableStatus(status: number): boolean {
  return status === 408 || status === 429 || status >= 500 || status === 522 || status === 524;
}

async function resilientSupabaseFetch(input: RequestInfo | URL, init?: RequestInit): Promise<Response> {
  const sourceRequest = new Request(input, init);
  const maxAttempts = RETRY_BACKOFF_MS.length + 1;

  for (let attempt = 0; attempt < maxAttempts; attempt += 1) {
    const timeoutController = new AbortController();
    const timeoutId = window.setTimeout(() => timeoutController.abort(), REQUEST_TIMEOUT_MS);
    const signal = mergeSignals(sourceRequest.signal, timeoutController.signal);
    const request = new Request(sourceRequest.clone(), { signal });

    try {
      const response = await fetch(request);
      const contentType = (response.headers.get('content-type') || '').toLowerCase();
      const isHtmlError = contentType.includes('text/html') && response.status >= 500;

      if (!isRetryableStatus(response.status) && !isHtmlError) {
        return response;
      }

      if (attempt >= maxAttempts - 1) {
        throw new Error(`Supabase request failed (HTTP ${response.status}).`);
      }
    } catch (error) {
      if (attempt >= maxAttempts - 1) {
        if (error instanceof Error && /aborted|timeout/i.test(error.message)) {
          throw new Error('Supabase request timed out.');
        }
        throw error;
      }
    } finally {
      window.clearTimeout(timeoutId);
    }

    await delay(RETRY_BACKOFF_MS[attempt]);
  }
}

Authentication

AdRecon supports two authentication methods:

Magic Link (Email)

src/components/Auth.tsx

const handleMagicLink = async (email: string) => {
  const { error } = await supabase.auth.signInWithOtp({
    email,
    options: {
      emailRedirectTo: `${window.location.origin}/app`,
      shouldCreateUser: false // Users must be created by admin or webhook
    }
  });
  
  if (error) {
    if (error.message?.toLowerCase().includes('email not confirmed')) {
      setError('Account not found. Please contact support.');
    } else {
      setError('Failed to send magic link. Please try again.');
    }
    return;
  }
  
  setSuccess('Check your email for a magic link to sign in.');
};

shouldCreateUser: false prevents arbitrary email signups. Users are created via:

Fanbasis webhook on purchase
Admin manual provisioning
Server-side user creation API

Google OAuth

src/components/Auth.tsx

const handleGoogleSignIn = async () => {
  const { error } = await supabase.auth.signInWithOAuth({
    provider: 'google',
    options: {
      redirectTo: `${window.location.origin}/app`
    }
  });
  
  if (error) {
    setError('Google sign-in failed. Please try again.');
  }
};

Session Management

src/App.tsx

useEffect(() => {
  let active = true;

  const initAuth = async () => {
    const { data } = await supabase.auth.getSession();
    if (!active) return;
    setSession(data.session);
    setAuthLoading(false);
  };

  const { data: authListener } = supabase.auth.onAuthStateChange(
    async (event, newSession) => {
      if (!active) return;
      setSession(newSession);
      
      // Refresh on token update
      if (event === 'TOKEN_REFRESHED') {
        window.location.reload();
      }
    }
  );

  void initAuth();

  return () => {
    active = false;
    authListener.subscription.unsubscribe();
  };
}, []);

Database Schema

AdRecon uses three primary views for ad data:

ads_feed_v2 (All Ads)

Public view with all indexed ads:

SELECT
  ad_archive_id,
  offer_name,
  advertiser,
  ad_copy,
  media_url,
  media_type,
  niche,
  network,
  status,
  start_date,
  days_running,
  updated_at
FROM public.ads_feed_v2
WHERE niche = 'Health'
  AND network = 'ClickBank'
  AND status = 'Active'
ORDER BY days_running DESC
LIMIT 50;

user_saved_ads_feed_v1 (Saved Ads)

User-scoped view joining saved ads with full ad metadata:

CREATE VIEW public.user_saved_ads_feed_v1 AS
SELECT
  s.user_id,
  s.ad_archive_id,
  s.saved_at,
  a.offer_name,
  a.advertiser,
  a.ad_copy,
  a.media_url,
  a.media_type,
  a.niche,
  a.network,
  a.status,
  a.start_date,
  a.days_running,
  a.updated_at
FROM public.user_saved_ads s
JOIN public.ads_feed_v2 a ON s.ad_archive_id = a.ad_archive_id
WHERE s.user_id = auth.uid();

RLS policies enforce user_id = auth.uid() so users only see their own saved ads.

user_project_ads_feed_v1 (Project Ads)

User-scoped view for ads within a specific project:

CREATE VIEW public.user_project_ads_feed_v1 AS
SELECT
  p.user_id,
  p.project_id,
  p.project_name,
  sap.ad_archive_id,
  a.offer_name,
  a.advertiser,
  a.ad_copy,
  a.media_url,
  a.media_type,
  a.niche,
  a.network,
  a.status,
  a.start_date,
  a.days_running,
  a.updated_at
FROM public.user_projects p
JOIN public.user_saved_ad_projects sap ON p.id = sap.project_id
JOIN public.ads_feed_v2 a ON sap.ad_archive_id = a.ad_archive_id
WHERE p.user_id = auth.uid();

Row Level Security (RLS)

All user tables enforce RLS:

user_saved_ads

-- Users can only read/write their own saved ads
CREATE POLICY "Users manage own saved ads"
  ON public.user_saved_ads
  FOR ALL
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

user_projects

-- Users can only read/write their own projects
CREATE POLICY "Users manage own projects"
  ON public.user_projects
  FOR ALL
  TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

user_saved_ad_projects

-- Users can only link ads to their own projects
CREATE POLICY "Users manage own project-ad links"
  ON public.user_saved_ad_projects
  FOR ALL
  TO authenticated
  USING (
    project_id IN (
      SELECT id FROM public.user_projects WHERE user_id = auth.uid()
    )
  )
  WITH CHECK (
    project_id IN (
      SELECT id FROM public.user_projects WHERE user_id = auth.uid()
    )
  );

fanbasis_enabled_offers

-- All authenticated users can read offers
CREATE POLICY "Authenticated can read fanbasis_enabled_offers"
  ON public.fanbasis_enabled_offers
  FOR SELECT
  TO authenticated
  USING (true);

-- Only admins can insert/update/delete
CREATE POLICY "Admins can modify fanbasis_enabled_offers"
  ON public.fanbasis_enabled_offers
  FOR ALL
  TO authenticated
  USING ((auth.jwt() -> 'app_metadata' ->> 'user_type') = 'admin')
  WITH CHECK ((auth.jwt() -> 'app_metadata' ->> 'user_type') = 'admin');

fanbasis_webhook_log

-- Only admins can read webhook logs
CREATE POLICY "Admins can read fanbasis_webhook_log"
  ON public.fanbasis_webhook_log
  FOR SELECT
  TO authenticated
  USING ((auth.jwt() -> 'app_metadata' ->> 'user_type') = 'admin');

Admin status is checked via auth.jwt() -> 'app_metadata' ->> 'user_type'. This is set during user creation and can only be modified via the Auth Admin API.

Real-time Queries

Supabase automatically refreshes data when underlying tables change:

src/lib/savedAds.ts

export async function listSavedAdIds(): Promise<string[]> {
  const client = getSupabaseClient();
  const { data, error } = await client
    .from('user_saved_ads')
    .select('ad_archive_id')
    .order('saved_at', { ascending: false });

  if (error) {
    throw new Error(error.message || 'Failed to load saved ad IDs.');
  }

  return data ? data.map((row) => row.ad_archive_id) : [];
}

When a user saves an ad, the user_saved_ads table is updated and the UI automatically reflects the change through reactive queries.

Admin API Integration

Server-side endpoints use Supabase service role for admin operations:

api/_admin.js

import { createClient } from '@supabase/supabase-js';

function getServerClient() {
  const url = process.env.VITE_SUPABASE_URL || process.env.SUPABASE_URL;
  const serviceKey = process.env.SUPABASE_SERVICE_ROLE_KEY;
  
  if (!url || !serviceKey) {
    throw new Error('Supabase server credentials not configured.');
  }
  
  return createClient(url, serviceKey, {
    auth: {
      autoRefreshToken: false,
      persistSession: false
    }
  });
}

export async function requireAdminRequest(req, res) {
  const authHeader = req.headers.authorization || '';
  const token = authHeader.replace(/^Bearer\s+/i, '');
  
  if (!token) {
    json(res, 401, { error: 'Missing authorization header.' });
    return null;
  }
  
  const client = getServerClient();
  const { data: { user }, error } = await client.auth.getUser(token);
  
  if (error || !user) {
    json(res, 401, { error: 'Invalid or expired token.' });
    return null;
  }
  
  const userType = user.app_metadata?.user_type || 'member';
  if (userType !== 'admin') {
    json(res, 403, { error: 'Admin access required.' });
    return null;
  }
  
  return { client, user };
}

Admin Operations

const { data, error } = await client.auth.admin.listUsers({
  page,
  perPage
});

Error Handling

All Supabase operations include error handling:

src/lib/ads.ts

export async function fetchAdsPage(params: AdQueryParams): Promise<AdsPageResult> {
  const client = getSupabaseClient();
  
  try {
    const { data, error, count } = await client
      .from('ads_feed_v2')
      .select('*', { count: 'exact' })
      .eq('niche', params.niche)
      .eq('network', params.network)
      .order(params.sortBy, { ascending: params.sortDir === 'asc' })
      .range(offset, offset + params.pageSize - 1);

    if (error) {
      // Check for timeout or statement timeout
      if (error.code === 'PGRST116' || error.message?.includes('timeout')) {
        // Fallback: fetch by IDs only, apply filters client-side
        return fetchAdsFallback(params);
      }
      throw new Error(error.message || 'Failed to fetch ads.');
    }

    return {
      items: data || [],
      total: count ?? 0,
      page: params.page,
      pageSize: params.pageSize
    };
  } catch (err) {
    throw new Error(err instanceof Error ? err.message : 'Unknown database error.');
  }
}

Timeout errors trigger client-side fallback: fetch all IDs first, then apply filters in-memory to avoid complex predicates.

Performance Optimization

AdRecon uses several strategies to optimize Supabase queries:

1. Indexed Columns

CREATE INDEX idx_ads_niche ON public.ads_feed_v2(niche);
CREATE INDEX idx_ads_network ON public.ads_feed_v2(network);
CREATE INDEX idx_ads_status ON public.ads_feed_v2(status);
CREATE INDEX idx_ads_days_running ON public.ads_feed_v2(days_running DESC);
CREATE INDEX idx_ads_updated_at ON public.ads_feed_v2(updated_at DESC);

2. View Materialization

For large datasets, consider materializing views:

CREATE MATERIALIZED VIEW public.ads_feed_v2_mat AS
SELECT * FROM public.ads_feed_v2;

CREATE UNIQUE INDEX idx_ads_feed_mat_id ON public.ads_feed_v2_mat(ad_archive_id);

REFRESH MATERIALIZED VIEW CONCURRENTLY public.ads_feed_v2_mat;

3. Query Fallback

When complex queries timeout, fall back to simpler patterns:

src/lib/ads.ts

async function fetchAdsFallback(params: AdQueryParams): Promise<AdsPageResult> {
  // 1. Fetch IDs only (fast)
  const { data: idRows } = await client
    .from('ads_feed_v2')
    .select('ad_archive_id')
    .order('updated_at', { ascending: false })
    .limit(10000);

  // 2. Apply filters client-side
  const filtered = idRows.filter(row => {
    if (params.niche !== 'All Niches' && row.niche !== params.niche) return false;
    if (params.network !== 'All Networks' && row.network !== params.network) return false;
    return true;
  });

  // 3. Fetch full records for page
  const pageIds = filtered.slice(offset, offset + params.pageSize).map(r => r.ad_archive_id);
  const { data } = await client
    .from('ads_feed_v2')
    .select('*')
    .in('ad_archive_id', pageIds);

  return {
    items: data || [],
    total: filtered.length,
    page: params.page,
    pageSize: params.pageSize,
    appliedSortBy: 'updatedAt',
    appliedSortDir: 'desc'
  };
}

Migration Management

Supabase migrations are versioned in supabase/migrations/:

supabase/migrations/
├── 20260101000000_initial_schema.sql
├── 20260225020000_add_fanbasis_tables.sql
└── 20260301000000_add_project_views.sql

Running Migrations

# Local development
supabase db reset
supabase migration up

# Production
supabase db push

Always test migrations locally before pushing to production. Use transactions and rollback procedures for schema changes.

Environment Variables

Variable	Required	Description
`VITE_SUPABASE_URL`	Yes	Supabase project URL
`VITE_SUPABASE_ANON_KEY`	Yes	Supabase anonymous key (client-side)
`SUPABASE_SERVICE_ROLE_KEY`	Yes (server)	Service role key for admin API
`SUPABASE_URL`	No	Fallback for server-side URL

Troubleshooting

”Supabase is not configured” error

Check that both environment variables are set:

echo $VITE_SUPABASE_URL
echo $VITE_SUPABASE_ANON_KEY

In Vite, env vars must be prefixed with VITE_ to be exposed to the client.

”Invalid or expired token” error

Check that session is still valid:

const { data } = await supabase.auth.getSession();
console.log('Session:', data.session);

Force refresh:
```
await supabase.auth.refreshSession();
```
Sign out and back in:
```
await supabase.auth.signOut();
```

“Failed to fetch ads” timeout

This occurs when queries are too complex or the database is under load:

Check Supabase dashboard for slow queries
Verify indexes exist on filtered columns
Reduce pageSize or simplify filters
Use fallback query pattern (ID-first retrieval)

RLS blocking legitimate queries

Verify user is authenticated:

const { data: { user } } = await supabase.auth.getUser();
console.log('User ID:', user?.id);

Check RLS policies in Supabase dashboard

Test query with service role key (bypasses RLS):

const adminClient = createClient(url, serviceRoleKey);
const { data } = await adminClient.from('user_saved_ads').select('*');

Magic link emails not sending

Check Supabase email settings in dashboard
Verify SMTP configuration
Check spam folder
Test with shouldCreateUser: true (then delete test user)

Security Best Practices

Never expose service role key to the client. Only use it in server-side API routes.

Always use RLS: Never disable RLS on user tables
Validate input: Sanitize all user input before queries
Use parameterized queries: Supabase client handles this automatically
Rotate keys regularly: Update anon key and service role key periodically
Monitor usage: Set up alerts for unusual query patterns
Limit permissions: Grant minimal necessary permissions to each role

API Reference

Authentication

const { data, error } = await supabase.auth.signInWithOtp({
  email: '[email protected]',
  options: {
    emailRedirectTo: 'https://adrecon.app/app',
    shouldCreateUser: false
  }
});

Database Queries

const { data, error, count } = await supabase
  .from('ads_feed_v2')
  .select('*', { count: 'exact' })
  .eq('niche', 'Health')
  .eq('status', 'Active')
  .order('days_running', { ascending: false })
  .range(0, 49);

Get Started

Core Features

User Management

Integrations

​Overview

​Setup

​1. Environment Configuration

​2. Client Initialization

​3. Resilient Fetch Implementation

​Authentication

​Magic Link (Email)

​Google OAuth

​Session Management

​Database Schema

​ads_feed_v2 (All Ads)

​user_saved_ads_feed_v1 (Saved Ads)

​user_project_ads_feed_v1 (Project Ads)

​Row Level Security (RLS)

​user_saved_ads

​user_projects

​user_saved_ad_projects

​fanbasis_enabled_offers

​fanbasis_webhook_log

​Real-time Queries

​Admin API Integration

​Admin Operations

​Error Handling

​Performance Optimization

​1. Indexed Columns

​2. View Materialization

​3. Query Fallback

​Migration Management

​Running Migrations

​Environment Variables

​Troubleshooting

​”Supabase is not configured” error

​”Invalid or expired token” error

​“Failed to fetch ads” timeout

​RLS blocking legitimate queries

​Magic link emails not sending

​Security Best Practices

​API Reference

​Authentication

​Database Queries

Build docs developers (and LLMs) love

Overview

Setup

1. Environment Configuration

2. Client Initialization

3. Resilient Fetch Implementation

Authentication

Magic Link (Email)

Google OAuth

Session Management

Database Schema

ads_feed_v2 (All Ads)

user_saved_ads_feed_v1 (Saved Ads)

user_project_ads_feed_v1 (Project Ads)

Row Level Security (RLS)

user_saved_ads

user_projects

user_saved_ad_projects

fanbasis_enabled_offers

fanbasis_webhook_log

Real-time Queries

Admin API Integration

Admin Operations

Error Handling

Performance Optimization

1. Indexed Columns

2. View Materialization

3. Query Fallback

Migration Management

Running Migrations

Environment Variables

Troubleshooting

”Supabase is not configured” error

”Invalid or expired token” error

“Failed to fetch ads” timeout

RLS blocking legitimate queries

Magic link emails not sending

Security Best Practices

API Reference

Authentication

Database Queries