본문 바로가기
개인 프로젝트/Danmuji04

Supabase SQL 보관

by 학습하는 청년 2025. 2. 10.

 

최종 수정 : 25.02.10

 

클로디에 의존하여 개발을 진행했는데, 테이블이 많이 엉켰었다. 섹션마다 서로 데이터가 호환되지 않아 생긴 이유였고, 무엇보다 내가 고민하지 않고 문제를 해결하고 넘어가기만 해서 발생했던 참사였다.

 

그래서 프로젝트에 사용된 테이블과 함수, 정책들을 다 삭제하고 처음부터 다시 만들고 있다.

 

# 회원가입을 위한 profiles 테이블

1. profiles 테이블 생성

create table public.profiles (
  id uuid references auth.users on delete cascade primary key,
  name text,
  nickname text unique,
  email text,
  avatar_url text,
  interests text[],
  role user_role default 'normal',
  marketing_agree boolean default false,
  created_at timestamp with time zone default timezone('utc'::text, now()),
  updated_at timestamp with time zone default timezone('utc'::text, now())
);

Q. created_at 칼럼과 updated_at 칼럼의 차이는 무엇인가?

created_at

 

  • 레코드가 처음 생성된 시간을 저장
  • 한 번 설정되면 변경되지 않음
  • 사용자가 언제 가입했는지를 추적할 때 사용

 

updated_at

 

  • 레코드가 마지막으로 수정된 시간을 저장
  • 데이터가 수정될 때마다 현재 시간으로 자동 업데이트
  • 프로필이 마지막으로 언제 수정되었는지 추적할 때 사용

 

updated_at 칼럼을 저장하는 주요 이유는 다음과 같다.

  1. 데이터 무결성/감사 추적
  • 언제 어떤 데이터가 변경되었는지 기록
  • 문제 발생시 디버깅에 활용
  • 비정상적인 수정 패턴 감지
  1. 캐싱 및 동기화
  • 클라이언트와 서버 간 데이터 동기화
  • 데이터가 언제 업데이트되었는지 확인
  • 캐시 무효화 결정에 사용
  1. 마지막 활동 시간 추적
  • 사용자의 활동성 분석
  • 휴면 계정 식별

 

2. 회원가입할 때 자동으로 profiles 테이블에 기본 정보를 생성하는 함수와 트리거

-- 2. 새 사용자 생성 시 프로필 자동 생성을 위한 트리거 함수
create or replace function public.handle_new_user()
returns trigger as $$
begin
  insert into public.profiles (
    id, 
    name,
    email,
    nickname,
    interests,
    marketing_agree
  )
  values (
    new.id,
    new.raw_user_meta_data->>'name',
    new.email,
    new.raw_user_meta_data->>'nickname',
    string_to_array(new.raw_user_meta_data->>'interests', ','),
    (new.raw_user_meta_data->>'marketing_agree')::boolean
  );
  return new;
end;
$$ language plpgsql security definer;

 

3. RLS 정책 설정

alter table public.profiles enable row level security;

create policy "Users can view their own profile"
  on public.profiles for select
  using (auth.uid() = id);

create policy "Users can update their own profile"
  on public.profiles for update
  using (auth.uid() = id);

 

  • 새 사용자가 가입하면 자동으로 profiles 테이블에 기본 정보가 생성됨
  • 사용자는 자신의 프로필만 조회/수정 가능
  • 다른 사용자의 프로필은 접근 불가

 

-- 회원가입할 때 자동으로 profiles 테이블에 데이터를 생성하는 함수
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = public
as $$
declare
  interests_array text[];
begin
  interests_array := array(select jsonb_array_elements_text(case 
    when new.raw_user_meta_data->>'interests' is null then '[]'::jsonb
    else (new.raw_user_meta_data->>'interests')::jsonb
  end));

  insert into public.profiles (
    id,
    name,
    email,
    nickname,
    interests,
    avatar_url,
    role,
    marketing_agree
  )
 values (
    new.id,
    new.raw_user_meta_data->>'name',
    new.email,
    new.raw_user_meta_data->>'nickname',
    new.raw_user_meta_data->>'avatar_url',
    coalesce((new.raw_user_meta_data->>'interests')::jsonb, '[]'::jsonb),
    'normal',
    (new.raw_user_meta_data->>'marketing_agree')::boolean
  );
  return new;
end;
$$;

 

-- 트리거 생성
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

-- RLS(Row Level Security) 정책 설정
alter table public.profiles enable row level security;

-- RLS 정책들
create policy "Users can view their own profile"
on public.profiles for select
using (auth.uid() = id);

create policy "Users can update their own profile"
on public.profiles for update
using (auth.uid() = id);

create policy "Users can insert their own profile"
on public.profiles for insert
with check (auth.uid() = id);

 

프로필 이미지 업로드를 위한 storage bucket 생성

insert into storage.buckets (id, name, public) values ('avatars', 'avatars', true);

 

Storage 정책 설정

create policy "anyone can upload an avatar"
on storage.objects for insert
to public
with check (bucket_id = 'avatars');

create policy "authenticated users can update own avatar"
on storage.objects for update
to authenticated
using (bucket_id = 'avatars' AND auth.uid() = owner);

create policy "avatar images are publicly accessible"
on storage.objects for select
to public
using (bucket_id = 'avatars');

2.강의 데이터

lectures 테이블 생성

-- lectures 테이블 생성
CREATE TABLE lectures (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    title VARCHAR(255) NOT NULL,
    thumbnail_url VARCHAR(255),
    category VARCHAR(50) NOT NULL,
    instructor VARCHAR(100) NOT NULL,
    depth VARCHAR(20) NOT NULL,
    keyword TEXT NOT NULL,
    group_type VARCHAR(20) NOT NULL,
    likes INTEGER DEFAULT 0,
    students INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

 

업데이트 트리거 추가

-- 업데이트 트리거 추가
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_lectures_updated_at
    BEFORE UPDATE ON lectures
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
  • 테이블이 update될 때 트리거가 실행된다.
  • updated_at 칼럼을 현재 시간으로 자동 설정
  • 개발자가 직접 updated_at을 관리할 필요가 없음

 

데이터의 마지막 수정 시간을 자동으로 추적해준다.

 

수강신청을 위한 테이블 생성

CREATE TABLE enrollments (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID REFERENCES auth.users NOT NULL,
    lecture_id BIGINT REFERENCES lectures NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    payment_status VARCHAR(20) NOT NULL DEFAULT 'free',
    enrolled_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, lecture_id)
);

 

수강평을 위한 테이블 생성

CREATE TABLE reviews (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES auth.users,
    lecture_id BIGINT NOT NULL REFERENCES lectures,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, lecture_id)
);

 

수강평 좋아요 테이블 생성

CREATE TABLE review_likes (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES auth.users,
    review_id BIGINT NOT NULL REFERENCES reviews,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, review_id)
);

 

답글 테이블 생성

CREATE TABLE review_replies (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    review_id BIGINT NOT NULL REFERENCES reviews ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES auth.users,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

 

답글 좋아요 테이블 생성

-- 답글 좋아요 테이블
CREATE TABLE review_reply_likes (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id UUID NOT NULL REFERENCES auth.users,
    reply_id BIGINT NOT NULL REFERENCES review_replies ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 한 사용자가 같은 답글에 중복으로 좋아요를 누를 수 없도록
    UNIQUE(user_id, reply_id)
);

찜하기

-- 찜하기 테이블 생성
CREATE TABLE public.bookmarks (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    lecture_id BIGINT NOT NULL REFERENCES public.lectures(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    UNIQUE(user_id, lecture_id)
);

-- RLS (Row Level Security) 정책 설정
ALTER TABLE public.bookmarks ENABLE ROW LEVEL SECURITY;

-- 인증된 사용자만 자신의 bookmarks를 읽을 수 있음
CREATE POLICY "Users can view own bookmarks" 
ON public.bookmarks 
FOR SELECT 
USING (auth.uid() = user_id);

-- 인증된 사용자만 bookmarks를 생성할 수 있음
CREATE POLICY "Users can create bookmarks" 
ON public.bookmarks 
FOR INSERT 
WITH CHECK (auth.uid() = user_id);

-- 인증된 사용자만 자신의 bookmarks를 삭제할 수 있음
CREATE POLICY "Users can delete own bookmarks" 
ON public.bookmarks 
FOR DELETE 
USING (auth.uid() = user_id);

강의 섹션

-- 1. is_public 컬럼 추가
ALTER TABLE lectures 
ADD COLUMN is_public BOOLEAN DEFAULT false;

-- 2. author_id 컬럼을 추가 (아직 NOT NULL 아님)
ALTER TABLE lectures 
ADD COLUMN author_id UUID REFERENCES auth.users(id);

-- 3. 현재 admin 사용자를 찾아서 임시로 모든 lectures의 author_id를 설정
UPDATE lectures
SET author_id = (
  SELECT id 
  FROM auth.users 
  WHERE id IN (
    SELECT id FROM profiles WHERE role = 'admin'
  )
  LIMIT 1
)
WHERE author_id IS NULL;

-- 4. author_id에 NOT NULL 제약조건 추가
ALTER TABLE lectures
ALTER COLUMN author_id SET NOT NULL;

 

동영상 업로드 Storage bucket 생성

-- Supabase SQL 에디터에서 실행
insert into storage.buckets (id, name, public) 
values ('videos', 'videos', true);

-- Storage 정책 설정
create policy "Anyone can upload a video"
on storage.objects for insert
to public
with check (bucket_id = 'videos');

create policy "Anyone can view videos"
on storage.objects for select
to public
using (bucket_id = 'videos');

create policy "Authenticated users can update own videos"
on storage.objects for update
to authenticated
using (bucket_id = 'videos' AND auth.uid() = owner);
저작자표시 비영리 변경금지

댓글

티스토리툴바