Skip to Content
ConsoleCore FeaturesSupabase Backend

Backend Infrastructure

Earna AI Console uses Supabase as the backend for authentication, PostgreSQL database, real-time subscriptions, and file storage for the GPT-4o powered chat platform. This guide covers complete setup, schema design, RLS policies, and best practices.

Overview

Supabase provides:

  • Authentication: Email/password, OAuth, magic links
  • Database: PostgreSQL with Row Level Security (RLS)
  • Storage: S3-compatible file storage
  • Real-time: WebSocket subscriptions for live updates
  • Edge Functions: Serverless functions (optional)
  • Vector Database: pgvector for embeddings (optional)

Architecture

Setup

Create Supabase Project

  1. Go to supabase.com  and create an account
  2. Create a new project
  3. Choose a region close to your users
  4. Save your database password securely

Get API Keys

Navigate to Settings → API in your Supabase dashboard:

# Add to .env.local
NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key

Install Client Libraries

pnpm add @supabase/supabase-js @supabase/ssr

Configure Client

Create the Supabase client configuration:

// lib/supabase/client.ts
import { createBrowserClient } from '@supabase/ssr'
import type { Database } from '@/types/database'
 
export function createClient() {
  return createBrowserClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  )
}
// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'
import type { Database } from '@/types/database'
 
export async function createServerSupabaseClient() {
  const cookieStore = await cookies()
 
  return createServerClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() {
          return cookieStore.getAll()
        },
        setAll(cookiesToSet) {
          cookiesToSet.forEach(({ name, value, options }) =>
            cookieStore.set(name, value, options)
          )
        },
      },
    }
  )
}

Database Schema

Core Database Tables

-- Users table (extends Supabase auth.users)
CREATE TABLE public.users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email TEXT UNIQUE,
  username TEXT UNIQUE,
  full_name TEXT,
  avatar_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  is_anonymous BOOLEAN DEFAULT false,
  subscription_tier TEXT DEFAULT 'free',
  daily_message_count INTEGER DEFAULT 0,
  daily_pro_message_count INTEGER DEFAULT 0,
  last_message_reset TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  settings JSONB DEFAULT '{}',
  metadata JSONB DEFAULT '{}'
);
 
-- Chats table
CREATE TABLE public.chats (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE,
  title TEXT,
  model TEXT DEFAULT 'gpt-4o',
  system_prompt TEXT,
  temperature FLOAT DEFAULT 0.7,
  max_tokens INTEGER DEFAULT 4096,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  is_archived BOOLEAN DEFAULT false,
  metadata JSONB DEFAULT '{}'
);
 
-- Messages table
CREATE TABLE public.messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  chat_id UUID REFERENCES public.chats(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system', 'tool')),
  content TEXT NOT NULL,
  model TEXT,
  tokens_used INTEGER,
  cost DECIMAL(10,6),
  attachments JSONB DEFAULT '[]',
  tool_calls JSONB DEFAULT '[]',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  metadata JSONB DEFAULT '{}'
);
 
-- Subscriptions table
CREATE TABLE public.subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE,
  stripe_customer_id TEXT UNIQUE,
  stripe_subscription_id TEXT UNIQUE,
  plan TEXT NOT NULL,
  status TEXT NOT NULL,
  current_period_start TIMESTAMP WITH TIME ZONE,
  current_period_end TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  metadata JSONB DEFAULT '{}'
);
 
-- Settings table
CREATE TABLE public.settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE,
  key TEXT NOT NULL,
  value JSONB NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(user_id, key)
);
 
-- Create indexes for performance
CREATE INDEX idx_chats_user_id ON public.chats(user_id);
CREATE INDEX idx_messages_chat_id ON public.messages(chat_id);
CREATE INDEX idx_messages_created_at ON public.messages(created_at DESC);
CREATE INDEX idx_subscriptions_user_id ON public.subscriptions(user_id);
CREATE INDEX idx_settings_user_id ON public.settings(user_id);

Row Level Security (RLS)

Always enable RLS on all tables to ensure data security. Never expose the service role key to the client.

-- Enable RLS on all tables
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.chats ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.settings ENABLE ROW LEVEL SECURITY;
 
-- Users policies
CREATE POLICY "Users can view own profile"
  ON public.users FOR SELECT
  USING (auth.uid() = id);
 
CREATE POLICY "Users can update own profile"
  ON public.users FOR UPDATE
  USING (auth.uid() = id);
 
-- Chats policies
CREATE POLICY "Users can view own chats"
  ON public.chats FOR SELECT
  USING (auth.uid() = user_id);
 
CREATE POLICY "Users can create own chats"
  ON public.chats FOR INSERT
  WITH CHECK (auth.uid() = user_id);
 
CREATE POLICY "Users can update own chats"
  ON public.chats FOR UPDATE
  USING (auth.uid() = user_id);
 
CREATE POLICY "Users can delete own chats"
  ON public.chats FOR DELETE
  USING (auth.uid() = user_id);
 
-- Messages policies
CREATE POLICY "Users can view messages in own chats"
  ON public.messages FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM public.chats
      WHERE chats.id = messages.chat_id
      AND chats.user_id = auth.uid()
    )
  );
 
CREATE POLICY "Users can create messages in own chats"
  ON public.messages FOR INSERT
  WITH CHECK (
    EXISTS (
      SELECT 1 FROM public.chats
      WHERE chats.id = messages.chat_id
      AND chats.user_id = auth.uid()
    )
  );
 
-- Subscriptions policies
CREATE POLICY "Users can view own subscription"
  ON public.subscriptions FOR SELECT
  USING (auth.uid() = user_id);
 
-- Settings policies
CREATE POLICY "Users can manage own settings"
  ON public.settings FOR ALL
  USING (auth.uid() = user_id);

Authentication

Authentication Setup

// lib/auth/config.ts
import { createClient } from '@/lib/supabase/client'
 
export const authConfig = {
  providers: ['email', 'google', 'github'],
  redirectTo: process.env.NEXT_PUBLIC_APP_URL || 'http://localhost:3000',
  emailRedirectTo: '/auth/callback',
 
  // Session configuration
  session: {
    expiresIn: 60 * 60 * 24 * 7, // 7 days
    refreshThreshold: 60 * 60, // Refresh if less than 1 hour left
  },
 
  // Rate limiting
  rateLimits: {
    signUp: { points: 5, duration: 60 * 60 }, // 5 signups per hour
    signIn: { points: 10, duration: 60 * 60 }, // 10 attempts per hour
  }
}

Storage

Configure and use Supabase Storage for file uploads:

// lib/storage/config.ts
export const storageConfig = {
  buckets: {
    chatFiles: 'chat-files',
    avatars: 'avatars',
    documents: 'documents'
  },
 
  limits: {
    maxFileSize: 10 * 1024 * 1024, // 10MB
    allowedMimeTypes: [
      'image/jpeg',
      'image/png',
      'image/gif',
      'application/pdf',
      'text/plain',
      'application/json'
    ]
  }
}
// lib/storage/upload.ts
import { createClient } from '@/lib/supabase/client'
 
export async function uploadFile(
  file: File,
  bucket: string,
  path?: string
) {
  const supabase = createClient()
  const fileName = path || `${Date.now()}-${file.name}`
 
  const { data, error } = await supabase.storage
    .from(bucket)
    .upload(fileName, file, {
      cacheControl: '3600',
      upsert: false
    })
 
  if (error) throw error
 
  // Get public URL
  const { data: { publicUrl } } = supabase.storage
    .from(bucket)
    .getPublicUrl(data.path)
 
  return { path: data.path, publicUrl }
}
 
export async function deleteFile(bucket: string, path: string) {
  const supabase = createClient()
 
  const { error } = await supabase.storage
    .from(bucket)
    .remove([path])
 
  if (error) throw error
}
 
export async function getSignedUrl(
  bucket: string,
  path: string,
  expiresIn = 3600
) {
  const supabase = createClient()
 
  const { data, error } = await supabase.storage
    .from(bucket)
    .createSignedUrl(path, expiresIn)
 
  if (error) throw error
  return data.signedUrl
}

Real-time Subscriptions

// hooks/use-realtime.ts
import { useEffect } from 'react'
import { createClient } from '@/lib/supabase/client'
 
export function useRealtimeMessages(chatId: string, onNewMessage: (message: any) => void) {
  const supabase = createClient()
 
  useEffect(() => {
    const channel = supabase
      .channel(`messages:${chatId}`)
      .on(
        'postgres_changes',
        {
          event: 'INSERT',
          schema: 'public',
          table: 'messages',
          filter: `chat_id=eq.${chatId}`
        },
        (payload) => {
          onNewMessage(payload.new)
        }
      )
      .subscribe()
 
    return () => {
      supabase.removeChannel(channel)
    }
  }, [chatId])
}
 
// Presence for collaborative features
export function usePresence(chatId: string) {
  const supabase = createClient()
  const [presences, setPresences] = useState({})
 
  useEffect(() => {
    const channel = supabase.channel(`presence:${chatId}`)
 
    channel
      .on('presence', { event: 'sync' }, () => {
        setPresences(channel.presenceState())
      })
      .on('presence', { event: 'join' }, ({ key, newPresences }) => {
        console.log('User joined:', key, newPresences)
      })
      .on('presence', { event: 'leave' }, ({ key, leftPresences }) => {
        console.log('User left:', key, leftPresences)
      })
      .subscribe(async (status) => {
        if (status === 'SUBSCRIBED') {
          await channel.track({
            online_at: new Date().toISOString(),
            user_id: user?.id,
          })
        }
      })
 
    return () => {
      channel.untrack()
      supabase.removeChannel(channel)
    }
  }, [chatId])
 
  return presences
}

Query Patterns

// lib/queries/chat-queries.ts
import { createServerSupabaseClient } from '@/lib/supabase/server'
 
export async function getUserChats(userId: string) {
  const supabase = await createServerSupabaseClient()
 
  const { data, error } = await supabase
    .from('chats')
    .select(`
      *,
      messages (
        id,
        role,
        content,
        created_at
      )
    `)
    .eq('user_id', userId)
    .eq('is_archived', false)
    .order('updated_at', { ascending: false })
    .limit(20)
 
  return { data, error }
}
 
export async function getChatWithMessages(chatId: string) {
  const supabase = await createServerSupabaseClient()
 
  const { data, error } = await supabase
    .from('chats')
    .select(`
      *,
      messages (
        *
      )
    `)
    .eq('id', chatId)
    .single()
 
  if (data) {
    data.messages.sort((a, b) =>
      new Date(a.created_at).getTime() - new Date(b.created_at).getTime()
    )
  }
 
  return { data, error }
}
 
export async function searchMessages(query: string, userId: string) {
  const supabase = await createServerSupabaseClient()
 
  const { data, error } = await supabase
    .rpc('search_messages', {
      search_query: query,
      user_id: userId
    })
 
  return { data, error }
}

Performance Optimization

Connection Pooling

// lib/supabase/pool.ts
import { createClient } from '@supabase/supabase-js'
 
class SupabasePool {
  private pool: ReturnType<typeof createClient>[] = []
  private currentIndex = 0
  private maxConnections = 5
 
  constructor() {
    for (let i = 0; i < this.maxConnections; i++) {
      this.pool.push(
        createClient(
          process.env.NEXT_PUBLIC_SUPABASE_URL!,
          process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
        )
      )
    }
  }
 
  getClient() {
    const client = this.pool[this.currentIndex]
    this.currentIndex = (this.currentIndex + 1) % this.maxConnections
    return client
  }
}
 
export const supabasePool = new SupabasePool()

Caching Strategies

// lib/cache/supabase-cache.ts
import { unstable_cache } from 'next/cache'
 
export const getCachedUser = unstable_cache(
  async (userId: string) => {
    const supabase = await createServerSupabaseClient()
    const { data } = await supabase
      .from('users')
      .select('*')
      .eq('id', userId)
      .single()
    return data
  },
  ['user'],
  {
    revalidate: 60 * 5, // 5 minutes
    tags: ['user']
  }
)

Security Best Practices

Never expose the service role key in client-side code. Always use it only in server-side API routes.

  1. Always enable RLS on all tables
  2. Use service role key only in server-side code
  3. Validate input before database operations
  4. Implement rate limiting for sensitive operations
  5. Use prepared statements to prevent SQL injection
  6. Audit sensitive operations with audit logs
  7. Encrypt sensitive data before storing
  8. Regular backups and point-in-time recovery

Monitoring

// lib/monitoring/supabase-metrics.ts
export async function trackDatabaseOperation({
  operation,
  table,
  duration,
  success,
  error
}: DatabaseMetrics) {
  // Log to your monitoring service
  console.log({
    timestamp: new Date().toISOString(),
    operation,
    table,
    duration,
    success,
    error: error?.message
  })
}

Next Steps

Last updated on
Voice & AudioSystem Architecture