Supabase + Next.js 全端實戰:從零打造協作看板 | Supabase 完整教學

-- 看板:owner_id 記錄建立者
create table public.boards (
  id uuid primary key default gen_random_uuid(),
  owner_id uuid not null references auth.users on delete cascade,
  title text not null check (char_length(title) between 1 and 80),
  created_at timestamptz not null default now()
);

-- 看板成員(多對多中介表):RLS 判斷「誰能存取哪個看板」的核心
create table public.board_members (
  board_id uuid not null references public.boards on delete cascade,
  user_id uuid not null references auth.users on delete cascade,
  role text not null default 'member' check (role in ('owner', 'member')),
  created_at timestamptz not null default now(),
  primary key (board_id, user_id)
);

-- 清單(待辦 / 進行中 / 完成):position 用來排序
create table public.lists (
  id uuid primary key default gen_random_uuid(),
  board_id uuid not null references public.boards on delete cascade,
  title text not null,
  position int not null default 0,
  created_at timestamptz not null default now()
);

-- 卡片:list_id 決定它在哪個清單(拖動卡片 = 改 list_id + position)
create table public.cards (
  id uuid primary key default gen_random_uuid(),
  board_id uuid not null references public.boards on delete cascade,
  list_id uuid not null references public.lists on delete cascade,
  title text not null,
  description text,
  attachment_path text,               -- 對應 Storage 裡的檔案路徑
  position int not null default 0,
  created_at timestamptz not null default now()
);

-- 效能:RLS 與查詢會頻繁用到的欄位一定要建索引
create index idx_board_members_user on public.board_members (user_id);
create index idx_lists_board on public.lists (board_id);
create index idx_cards_board on public.cards (board_id);
create index idx_cards_list on public.cards (list_id, position);

-- 四張表一律開 RLS,啟用後預設全拒絕
alter table public.boards        enable row level security;
alter table public.board_members enable row level security;
alter table public.lists         enable row level security;
alter table public.cards         enable row level security;

-- helper:判斷呼叫者是否為某看板成員(SECURITY DEFINER 繞過 board_members 自身的 RLS,避免遞迴)
create or replace function public.is_board_member(_board_id uuid)
returns boolean
language sql
security definer
set search_path = ''
stable
as $$
  select exists (
    select 1 from public.board_members
    where board_id = _board_id
      and user_id = (select auth.uid())
  );
$$;

-- boards:成員可讀;只有本人能建立自己是 owner 的看板
create policy "members can read boards" on public.boards
  for select to authenticated
  using (public.is_board_member(id));

create policy "users can create own board" on public.boards
  for insert to authenticated
  with check ((select auth.uid()) = owner_id);

create policy "owner can update board" on public.boards
  for update to authenticated
  using (public.is_board_member(id) and (select auth.uid()) = owner_id);

-- board_members:成員可看到同看板的其他成員;owner 可新增成員(邀請)
create policy "members can read membership" on public.board_members
  for select to authenticated
  using (public.is_board_member(board_id));

create policy "owner can add members" on public.board_members
  for insert to authenticated
  with check (
    exists (
      select 1 from public.boards b
      where b.id = board_id and b.owner_id = (select auth.uid())
    )
  );

-- lists 與 cards:成員可對「自己是成員的看板」做全部 CRUD
create policy "members manage lists" on public.lists
  for all to authenticated
  using (public.is_board_member(board_id))
  with check (public.is_board_member(board_id));

create policy "members manage cards" on public.cards
  for all to authenticated
  using (public.is_board_member(board_id))
  with check (public.is_board_member(board_id));

create or replace function public.add_owner_as_member()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.board_members (board_id, user_id, role)
  values (new.id, new.owner_id, 'owner');
  return new;
end;
$$;

create trigger trg_add_owner_as_member
  after insert on public.boards
  for each row execute function public.add_owner_as_member();

npm install @supabase/supabase-js @supabase/ssr

// lib/supabase/client.ts —— 瀏覽器端,給 Client Component
import { createBrowserClient } from '@supabase/ssr'
import type { Database } from '@/types/database.types'

export function createClient() {
  return createBrowserClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!  // 前端只用 anon key,受 RLS 保護
  )
}

// lib/supabase/server.ts —— 伺服器端,透過 cookie 讀寫 session
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'
import type { Database } from '@/types/database.types'

export async function createClient() {
  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) {
          try {
            cookiesToSet.forEach(({ name, value, options }) =>
              cookieStore.set(name, value, options)
            )
          } catch {
            // 在 Server Component 中 set 會拋錯,交給 middleware 處理即可,可忽略
          }
        },
      },
    }
  )
}

// middleware.ts —— 每次請求刷新 token(全端 SSR 的關鍵)
import { createServerClient } from '@supabase/ssr'
import { NextResponse, type NextRequest } from 'next/server'

export async function middleware(request: NextRequest) {
  let response = NextResponse.next({ request })

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() {
          return request.cookies.getAll()
        },
        setAll(cookiesToSet) {
          cookiesToSet.forEach(({ name, value }) => request.cookies.set(name, value))
          response = NextResponse.next({ request })
          cookiesToSet.forEach(({ name, value, options }) =>
            response.cookies.set(name, value, options)
          )
        },
      },
    }
  )

  // 一定要用 getUser()(向伺服器驗 JWT),它會順帶觸發 token 刷新
  const { data: { user } } = await supabase.auth.getUser()

  // 未登入且要進看板頁 → 導去登入頁
  if (!user && request.nextUrl.pathname.startsWith('/board')) {
    return NextResponse.redirect(new URL('/login', request.url))
  }

  return response
}

export const config = {
  matcher: ['/((?!_next/static|_next/image|favicon.ico).*)'],
}

// app/login/actions.ts (Client Component 中呼叫)
const supabase = createClient()  // 瀏覽器端

async function signIn(email: string, password: string) {
  const { error } = await supabase.auth.signInWithPassword({ email, password })
  if (error) throw error
  window.location.href = '/board'  // 登入後導向看板列表
}

// app/board/[id]/page.tsx —— Server Component,首屏 SSR 撈看板資料
import { createClient } from '@/lib/supabase/server'

export default async function BoardPage({ params }: { params: Promise<{ id: string }> }) {
  const { id } = await params
  const supabase = await createClient()

  // RLS 會自動把「非成員看板」擋掉,查不到就回空
  const { data: lists } = await supabase
    .from('lists')
    .select('id, title, position, cards ( id, title, list_id, position, attachment_path )')
    .eq('board_id', id)
    .order('position')

  return <BoardView boardId={id} initialLists={lists ?? []} />
}

// 建看板:只要傳 title 與 owner_id,RLS 的 with check 會擋掉冒名建立
async function createBoard(title: string) {
  const supabase = createClient()
  const { data: { user } } = await supabase.auth.getUser()
  const { data, error } = await supabase
    .from('boards')
    .insert({ title, owner_id: user!.id })
    .select()
    .single()
  if (error) throw error
  return data  // 觸發器已自動把 owner 加進 board_members
}

// 加卡片
async function addCard(boardId: string, listId: string, title: string) {
  const supabase = createClient()
  const { data, error } = await supabase
    .from('cards')
    .insert({ board_id: boardId, list_id: listId, title })
    .select()
    .single()
  if (error) throw error
  return data
}

// 拖動卡片 = 更新 list_id 與 position(RLS 確保只能改自己有權限的看板)
async function moveCard(cardId: string, toListId: string, position: number) {
  const supabase = createClient()
  const { error } = await supabase
    .from('cards')
    .update({ list_id: toListId, position })
    .eq('id', cardId)
  if (error) throw error
}

-- 建 bucket
insert into storage.buckets (id, name, public) values
  ('avatars', 'avatars', true),
  ('card-attachments', 'card-attachments', false)
on conflict (id) do nothing;

-- 頭像:使用者只能上傳到「以自己 uid 命名的資料夾」(avatars/<uid>/xxx.png)
create policy "users upload own avatar" on storage.objects
  for insert to authenticated
  with check (
    bucket_id = 'avatars'
    and (storage.foldername(name))[1] = (select auth.uid())::text
  );

-- 卡片附件:路徑第一段是 board_id,只有該看板成員能上傳/讀取
create policy "members read attachments" on storage.objects
  for select to authenticated
  using (
    bucket_id = 'card-attachments'
    and public.is_board_member(((storage.foldername(name))[1])::uuid)
  );

create policy "members upload attachments" on storage.objects
  for insert to authenticated
  with check (
    bucket_id = 'card-attachments'
    and public.is_board_member(((storage.foldername(name))[1])::uuid)
  );

// 上傳頭像:路徑用 <uid>/avatar.png,對應 Storage RLS
async function uploadAvatar(file: File) {
  const supabase = createClient()
  const { data: { user } } = await supabase.auth.getUser()
  const path = `${user!.id}/avatar.png`

  const { error } = await supabase.storage
    .from('avatars')
    .upload(path, file, { upsert: true })
  if (error) throw error

  // 公開 bucket 直接取 public URL 存進 profile
  const { data } = supabase.storage.from('avatars').getPublicUrl(path)
  return data.publicUrl
}

// 上傳卡片附件:路徑用 <board_id>/<card_id>/<檔名>,對應 Storage RLS
async function uploadAttachment(boardId: string, cardId: string, file: File) {
  const supabase = createClient()
  const path = `${boardId}/${cardId}/${file.name}`

  const { error } = await supabase.storage
    .from('card-attachments')
    .upload(path, file, { upsert: true })
  if (error) throw error

  // 私有 bucket:把路徑存進 card,顯示時再用簽署 URL(有效 1 小時)
  await supabase.from('cards').update({ attachment_path: path }).eq('id', cardId)
  const { data } = await supabase.storage
    .from('card-attachments')
    .createSignedUrl(path, 3600)
  return data?.signedUrl
}

-- 讓 Realtime 監聽這兩張表(漏了這步就永遠收不到事件)
alter publication supabase_realtime add table public.cards;
alter publication supabase_realtime add table public.lists;

// components/BoardView.tsx —— 訂閱 cards 變更,即時更新畫面
'use client'
import { useEffect, useState } from 'react'
import { createClient } from '@/lib/supabase/client'

export function BoardView({ boardId, initialLists }: BoardViewProps) {
  const [lists, setLists] = useState(initialLists)

  useEffect(() => {
    const supabase = createClient()

    const channel = supabase
      .channel(`board:${boardId}`)
      .on(
        'postgres_changes',
        // 只訂閱「這個看板」的 cards 變更(server 端 filter,省流量)
        { event: '*', schema: 'public', table: 'cards', filter: `board_id=eq.${boardId}` },
        (payload) => {
          setLists((prev) => applyCardChange(prev, payload))  // 依 eventType 更新本地 state
        }
      )
      .subscribe()

    // 元件卸載一定要退訂,否則重複訂閱 + 記憶體洩漏
    return () => {
      supabase.removeChannel(channel)
    }
  }, [boardId])

  return <Board lists={lists} />
}

// 依 INSERT / UPDATE / DELETE 更新本地卡片狀態
function applyCardChange(lists: ListWithCards[], payload: CardChangePayload) {
  const { eventType, new: newCard, old: oldCard } = payload
  // INSERT → 加進對應 list;UPDATE → 移動/更新;DELETE → 移除
  // ...(依 newCard.list_id / oldCard.id 重組 lists,略)
  return lists
}

npx supabase gen types typescript --project-id "$PROJECT_REF" > types/database.types.ts