Supabase 스키마 · 권한 정책Database Schema & RLS

-- supabase/migrations/0001_init.sql
-- 1. 회원 프로필 (auth.users 확장)
create table public.profiles (
  id uuid primary key references auth.users on delete cascade,
  nickname text not null,
  phone text,
  parish text,          -- 1교구·2교구·3교구·…
  role text not null default 'member' check (role in ('member','staff','admin')),
  created_at timestamptz not null default now()
);

-- 2. 게시판 (news·bulletin·hamjul·qt 통합)
create type board_kind as enum ('news','bulletin','hamjul','qt','prayer','counsel');

create table public.posts (
  id bigint generated always as identity primary key,
  kind board_kind not null,
  slug text not null,
  title text not null,
  body text,                 -- MDX 또는 HTML
  cover_image text,
  author_id uuid references public.profiles,
  published_at timestamptz,  -- null이면 비공개 / 작성 중
  meta jsonb not null default '{}'::jsonb,   -- 설교자·성구·호별 번호 등
  created_at timestamptz not null default now(),
  unique (kind, slug)
);

create index posts_kind_published_idx
  on public.posts (kind, published_at desc nulls last);

-- 3. 신청 폼 제출물 (상담·새가족·중보기도)
create table public.applications (
  id bigint generated always as identity primary key,
  form_kind text not null,      -- 'counsel' | 'new-family' | 'prayer'
  data jsonb not null,           -- 폼 필드 그대로 저장
  status text not null default 'received'
    check (status in ('received','in-progress','done','rejected')),
  assignee_id uuid references public.profiles,
  created_at timestamptz not null default now()
);

-- 4. 일정 (예배·행사)
create table public.events (
  id bigint generated always as identity primary key,
  title text not null,
  description text,
  location text,
  starts_at timestamptz not null,
  ends_at timestamptz not null,
  rrule text,                    -- iCal RRULE (매주 일요일 등)
  category text not null,        -- '주일예배·새벽예배·구역모임·교육'
  visibility text not null default 'public',
  created_at timestamptz not null default now()
);

-- 행 단위 보안 정책 (Row Level Security)
alter table public.profiles enable row level security;
alter table public.posts enable row level security;
alter table public.applications enable row level security;
alter table public.events enable row level security;

-- profiles: 본인만 자기 행 읽기·수정, 관리자는 전체
create policy "profiles_self_read" on public.profiles
  for select using (auth.uid() = id);
create policy "profiles_admin_read" on public.profiles
  for select using (
    exists (select 1 from public.profiles p
            where p.id = auth.uid() and p.role = 'admin')
  );
create policy "profiles_self_update" on public.profiles
  for update using (auth.uid() = id);

-- posts: 게시된 글은 누구나 읽기, 작성·수정은 staff/admin만
create policy "posts_public_read" on public.posts
  for select using (published_at is not null);
create policy "posts_staff_write" on public.posts
  for all using (
    exists (select 1 from public.profiles p
            where p.id = auth.uid() and p.role in ('staff','admin'))
  );

-- applications: 본인 제출물만 읽기, 담당자·관리자는 전체
create policy "applications_self_read" on public.applications
  for select using (
    (data->>'submitter_id')::uuid = auth.uid()
    or assignee_id = auth.uid()
    or exists (select 1 from public.profiles p
               where p.id = auth.uid() and p.role = 'admin')
  );
create policy "applications_anyone_insert" on public.applications
  for insert with check (true);  -- 비로그인 사용자도 신청 가능

-- events: 공개 일정은 누구나 읽기, 작성·수정은 staff/admin
create policy "events_public_read" on public.events
  for select using (visibility = 'public');
create policy "events_staff_write" on public.events
  for all using (
    exists (select 1 from public.profiles p
            where p.id = auth.uid() and p.role in ('staff','admin'))
  );

-- 새 사용자 가입 시 profiles 자동 생성
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.profiles (id, nickname)
  values (
    new.id,
    coalesce(new.raw_user_meta_data->>'name', '성도')
  );
  return new;
end;
$$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

// 서버 컴포넌트에서 게시물 가져오기
import { createClient } from "@/lib/supabase/server";

export default async function NewsPage() {
  const supabase = await createClient();
  const { data: posts } = await supabase
    .from("posts")
    .select("id, slug, title, cover_image, published_at, meta")
    .eq("kind", "news")
    .not("published_at", "is", null)
    .order("published_at", { ascending: false })
    .limit(20);

  return <NewsList posts={posts ?? []} />;
}