supabase-auth-memory

Supabase Auth + Memory System

Purpose

Implement user authentication and persistent AI conversation memory using Supabase as unified backend.

When to Use

Need user authentication in SaaS app
Want conversation history across devices
Building multi-tenant AI applications
Need SQL-based memory for agents
Syncing state between localStorage and cloud

Quick Start

Installation

npm install @supabase/supabase-js npm install zustand zustand/middleware

Environment Variables

NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key SUPABASE_SERVICE_KEY=your-service-key # Backend only

Database Schema

Core Tables

-- Conversations table CREATE TABLE conversations ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE, title TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), metadata JSONB DEFAULT '{}'::jsonb,

-- Indexes CONSTRAINT conversations_user_id_fkey FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE );

CREATE INDEX idx_conversations_user ON conversations(user_id); CREATE INDEX idx_conversations_updated ON conversations(updated_at DESC);

-- Messages table CREATE TABLE messages ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), conversation_id UUID REFERENCES conversations(id) ON DELETE CASCADE, role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')), content TEXT NOT NULL, tool_used TEXT, tool_result JSONB, reasoning_details JSONB, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

-- Indexes CONSTRAINT messages_conversation_id_fkey FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE );

CREATE INDEX idx_messages_conversation ON messages(conversation_id); CREATE INDEX idx_messages_created ON messages(created_at DESC);

-- User preferences (config storage) CREATE TABLE user_preferences ( user_id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE, preferences JSONB NOT NULL DEFAULT '{}'::jsonb, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() );

-- Auto-update timestamp trigger CREATE OR REPLACE FUNCTION update_updated_at() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

CREATE TRIGGER conversations_updated_at BEFORE UPDATE ON conversations FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Row Level Security (RLS) Policies

-- Enable RLS ALTER TABLE conversations ENABLE ROW LEVEL SECURITY; ALTER TABLE messages ENABLE ROW LEVEL SECURITY; ALTER TABLE user_preferences ENABLE ROW LEVEL SECURITY;

-- Conversations policies CREATE POLICY "Users can view own conversations" ON conversations FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can create own conversations" ON conversations FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own conversations" ON conversations FOR UPDATE USING (auth.uid() = user_id);

CREATE POLICY "Users can delete own conversations" ON conversations FOR DELETE USING (auth.uid() = user_id);

-- Messages policies CREATE POLICY "Users can view own messages" ON messages FOR SELECT USING ( conversation_id IN ( SELECT id FROM conversations WHERE user_id = auth.uid() ) );

CREATE POLICY "Users can create messages" ON messages FOR INSERT WITH CHECK ( conversation_id IN ( SELECT id FROM conversations WHERE user_id = auth.uid() ) );

-- User preferences policies CREATE POLICY "Users can view own preferences" ON user_preferences FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can upsert own preferences" ON user_preferences FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own preferences" ON user_preferences FOR UPDATE USING (auth.uid() = user_id);

Frontend Integration

Supabase Client Setup

// lib/supabase.ts import { createClient } from '@supabase/supabase-js'

export const supabase = createClient( process.env.NEXT_PUBLIC_SUPABASE_URL!, process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY! )

// Get current user export async function getCurrentUser() { const { data: { user } } = await supabase.auth.getUser() return user }

Zustand Store with Supabase Sync

// stores/conversationStore.ts import { create } from 'zustand' import { persist, createJSONStorage } from 'zustand/middleware' import { supabase } from '@/lib/supabase'

interface Message { id: string role: 'user' | 'assistant' | 'system' content: string tool_used?: string created_at: string }

interface ConversationStore { // State messages: Message[] conversationId: string | null isSyncing: boolean

// Local actions (optimistic UI) addMessage: (message: Omit<Message, 'id' | 'created_at'>) => void clearMessages: () => void

// Supabase sync actions createConversation: (title: string) => Promise<string> loadConversation: (conversationId: string) => Promise<void> saveToSupabase: () => Promise<void> syncWithSupabase: () => Promise<void> }

export const useConversationStore = create<ConversationStore>()( persist( (set, get) => ({ // Initial state messages: [], conversationId: null, isSyncing: false,

  // Add message optimistically
  addMessage: (message) => {
    const newMessage: Message = {
      ...message,
      id: crypto.randomUUID(),
      created_at: new Date().toISOString()
    }

    set(state => ({
      messages: [...state.messages, newMessage]
    }))

    // Auto-save to Supabase (non-blocking)
    get().saveToSupabase()
  },

  // Clear messages
  clearMessages: () => set({ messages: [], conversationId: null }),

  // Create new conversation in Supabase
  createConversation: async (title) => {
    const user = await getCurrentUser()
    if (!user) throw new Error('Not authenticated')

    const { data, error } = await supabase
      .from('conversations')
      .insert({ user_id: user.id, title })
      .select()
      .single()

    if (error) throw error

    set({ conversationId: data.id })
    return data.id
  },

  // Load conversation from Supabase
  loadConversation: async (conversationId) => {
    set({ isSyncing: true })

    const { data, error } = await supabase
      .from('messages')
      .select('*')
      .eq('conversation_id', conversationId)
      .order('created_at', { ascending: true })

    if (error) throw error

    set({
      messages: data || [],
      conversationId,
      isSyncing: false
    })
  },

  // Save messages to Supabase
  saveToSupabase: async () => {
    const { messages, conversationId } = get()
    if (!conversationId) return

    set({ isSyncing: true })

    // Find new messages (not yet in Supabase)
    const newMessages = messages.filter(m => !m.id.startsWith('uuid'))

    if (newMessages.length > 0) {
      const { error } = await supabase
        .from('messages')
        .insert(
          newMessages.map(m => ({
            conversation_id: conversationId,
            role: m.role,
            content: m.content,
            tool_used: m.tool_used
          }))
        )

      if (error) console.error('Failed to save to Supabase:', error)
    }

    set({ isSyncing: false })
  },

  // Sync with Supabase (merge local + remote)
  syncWithSupabase: async () => {
    const { conversationId } = get()
    if (!conversationId) return

    await get().loadConversation(conversationId)
  }
}),
{
  name: 'conversation-storage',
  storage: createJSONStorage(() => localStorage),

  // Only persist specific fields
  partialize: (state) => ({
    messages: state.messages,
    conversationId: state.conversationId
  })
}

) )

User Preferences Store

// stores/preferencesStore.ts import { create } from 'zustand' import { persist } from 'zustand/middleware' import { supabase, getCurrentUser } from '@/lib/supabase'

interface Preferences { theme: 'light' | 'dark' temperature: number model: string [key: string]: any }

interface PreferencesStore { preferences: Preferences updatePreferences: (updates: Partial<Preferences>) => void syncWithSupabase: () => Promise<void> }

export const usePreferencesStore = create<PreferencesStore>()( persist( (set, get) => ({ preferences: { theme: 'dark', temperature: 0.7, model: 'openai/gpt-4o' },

  updatePreferences: (updates) => {
    set(state => ({
      preferences: { ...state.preferences, ...updates }
    }))

    // Auto-sync to Supabase
    get().syncWithSupabase()
  },

  syncWithSupabase: async () => {
    const user = await getCurrentUser()
    if (!user) return

    const { preferences } = get()

    await supabase
      .from('user_preferences')
      .upsert({
        user_id: user.id,
        preferences
      })
  }
}),
{
  name: 'user-preferences',
  onRehydrateStorage: () => (state) => {
    // Load from Supabase after localStorage rehydration
    state?.syncWithSupabase()
  }
}

) )

Real-time Subscriptions

// hooks/useRealtimeMessages.ts import { useEffect } from 'react' import { supabase } from '@/lib/supabase' import { useConversationStore } from '@/stores/conversationStore'

export function useRealtimeMessages(conversationId: string) { const addMessage = useConversationStore(state => state.addMessage)

useEffect(() => { const channel = supabase .channel(conversation:${conversationId}) .on( 'postgres_changes', { event: 'INSERT', schema: 'public', table: 'messages', filter: conversation_id=eq.${conversationId} }, (payload) => { addMessage(payload.new as any) } ) .subscribe()

return () => {
  supabase.removeChannel(channel)
}

}, [conversationId, addMessage]) }

Authentication Patterns

async function signUp(email: string, password: string) { const { data, error } = await supabase.auth.signUp({ email, password, options: { emailRedirectTo: ${window.location.origin}/auth/callback } })

if (error) throw error return data }

async function signIn(email: string, password: string) { const { data, error } = await supabase.auth.signInWithPassword({ email, password })

if (error) throw error return data }

OAuth (Google, GitHub, etc.)

async function signInWithOAuth(provider: 'google' | 'github') { const { data, error } = await supabase.auth.signInWithOAuth({ provider, options: { redirectTo: ${window.location.origin}/auth/callback } })

if (error) throw error return data }

Supabase MCP Integration

Setup (.mcp.json)

{ "mcpServers": { "supabase": { "url": "https://mcp.supabase.com/mcp?project_ref=YOUR_PROJECT_REF", "transport": "streamable-http", "auth": { "type": "oauth" } } } }

Available MCP Tools

Create/manage projects
Design tables & migrations
Query data with SQL
Generate TypeScript types
Manage configurations

Best Practices

Security First

Always enable RLS on all tables
Use service key only on server
Validate user ownership in policies

Hybrid Storage

localStorage for offline/instant UX
Supabase for cross-device sync
Background sync with debouncing

Optimistic UI

Update UI immediately
Sync to Supabase in background
Handle conflicts gracefully

Real-time Updates

Use subscriptions for collaboration
Clean up subscriptions on unmount
Debounce rapid updates

Error Handling

Retry failed syncs
Show sync status to user
Fallback to localStorage

Performance

Index frequently queried columns
Use JSONB for flexible metadata
Implement pagination for large datasets

Common Patterns

Multi-tenant SaaS

-- Add tenant isolation CREATE TABLE organizations ( id UUID PRIMARY KEY DEFAULT uuid_generate_v4(), name TEXT NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() );

CREATE TABLE organization_members ( organization_id UUID REFERENCES organizations(id), user_id UUID REFERENCES auth.users(id), role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')), PRIMARY KEY (organization_id, user_id) );

-- Update RLS policies for organization isolation CREATE POLICY "Organization members can view conversations" ON conversations FOR SELECT USING ( user_id IN ( SELECT user_id FROM organization_members WHERE organization_id = ( SELECT organization_id FROM organization_members WHERE user_id = auth.uid() ) ) );

Resources

Supabase Docs
Supabase MCP Server
RLS Policies Guide
Real-time Guide

supabase-auth-memory

Safety Notice

Copy this and send it to your AI assistant to learn

Source Transparency

Related Skills

nextjs-16-complete-guide

replicate-integration

nano-banana-image-combine

skill-creator