Build a Real-Time SaaS Application with Supabase

I'm building a project management SaaS for freelancers. I need a multi-tenant schema where each user can create workspaces, invite members, and manage projects with tasks. The schema should support: workspaces (owned by a user, members with roles), projects (belong to a workspace), tasks (belong to a project, assigned to a member, have status and priority), and comments (on tasks, real-time). Design the Supabase schema with proper Row-Level Security so users can only access their own workspaces and data. Use Postgres best practices.

-- supabase/migrations/20260218_create_schema.sql

CREATE TABLE workspaces (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  owner_id UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE workspace_members (
  workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id),
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  PRIMARY KEY (workspace_id, user_id)
);

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID REFERENCES workspaces(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'active'
);

CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  description TEXT,
  status TEXT DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'review', 'done')),
  priority TEXT DEFAULT 'medium',
  assignee_id UUID REFERENCES auth.users(id),
  due_date DATE,
  position INTEGER,          -- for drag-and-drop ordering
  updated_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE comments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
  author_id UUID REFERENCES auth.users(id),
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Workspaces: only members can see them
CREATE POLICY "workspace_member_select" ON workspaces FOR SELECT USING (
  id IN (SELECT workspace_id FROM workspace_members WHERE user_id = auth.uid())
);

-- Tasks: only workspace members can view, assignee or admin can update
CREATE POLICY "task_workspace_select" ON tasks FOR SELECT USING (
  project_id IN (
    SELECT p.id FROM projects p
    JOIN workspace_members wm ON wm.workspace_id = p.workspace_id
    WHERE wm.user_id = auth.uid()
  )
);

-- Comments: workspace members can read and write, but only authors can edit/delete
CREATE POLICY "comment_author_update" ON comments FOR UPDATE USING (
  author_id = auth.uid()
);

Configure Supabase auth for my app. I want two sign-in methods: magic link (passwordless email) as the primary method, and Google OAuth as an alternative. After sign-up, auto-create a user profile with their name and avatar from the auth metadata. Set up the auth callback handler for my Next.js app. Show me the complete login page component with both methods.

-- Auto-create profile on signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO profiles (id, email, full_name, avatar_url)
  VALUES (
    NEW.id,
    NEW.email,
    COALESCE(NEW.raw_user_meta_data->>'full_name', split_part(NEW.email, '@', 1)),
    NEW.raw_user_meta_data->>'avatar_url'
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

Create a real-time Kanban task board component. It should: load tasks for a project grouped by status (todo, in_progress, review, done), support drag-and-drop between columns (update status and position), show real-time updates when another team member moves a task or adds a comment, and display an avatar of who's currently viewing the board using Supabase Presence. When a task moves, all connected users should see it move instantly.

// components/task-board.tsx — Real-time subscriptions

const channel = supabase.channel(`project-${projectId}`);

// Subscribe to task changes (INSERT, UPDATE, DELETE)
channel.on('postgres_changes',
  { event: '*', schema: 'public', table: 'tasks', filter: `project_id=eq.${projectId}` },
  (payload) => {
    if (payload.eventType === 'INSERT') addTaskToColumn(payload.new);
    if (payload.eventType === 'UPDATE') moveTaskBetweenColumns(payload.old, payload.new);
    if (payload.eventType === 'DELETE') removeTaskFromColumn(payload.old);
  }
);

// Track who's viewing the board via Presence
channel.on('presence', { event: 'sync' }, () => {
  setOnlineUsers(channel.presenceState());
});

channel.subscribe(async (status) => {
  if (status === 'SUBSCRIBED') {
    await channel.track({ user_id: user.id, name: user.name, avatar: user.avatar });
  }
});

Add file attachment support to tasks. Users should be able to upload files (images, PDFs, documents) up to 10MB per file. Store files in a Supabase storage bucket organized by workspace/project/task. Show image previews inline in the task detail view. Only workspace members should be able to view and upload files. Add drag-and-drop upload to the task detail panel.

-- Storage policies for task-attachments bucket

-- Only workspace members can view files
CREATE POLICY "attachment_read" ON storage.objects FOR SELECT USING (
  bucket_id = 'task-attachments' AND
  (storage.foldername(name))[1]::uuid IN (
    SELECT workspace_id FROM workspace_members WHERE user_id = auth.uid()
  )
);

-- Only workspace members can upload, with size and type restrictions
CREATE POLICY "attachment_upload" ON storage.objects FOR INSERT WITH CHECK (
  bucket_id = 'task-attachments' AND
  (storage.foldername(name))[1]::uuid IN (
    SELECT workspace_id FROM workspace_members WHERE user_id = auth.uid()
  )
);

Prepare this Supabase project for production. Set up: environment-specific configs (dev/staging/prod), database migrations workflow using Supabase CLI, proper CORS and redirect URLs, email templates for magic links, and rate limiting on edge functions. Also create a seed script that populates a demo workspace with sample data for new users.