🗄️ Supabase 설정 가이드

아래 버튼을 클릭해 대시보드를 여세요

좌측 메뉴에서 SQL Editor 클릭

대시보드 왼쪽 사이드바에서 ⌨️ SQL Editor 를 찾아 클릭하세요.

New Query 버튼 클릭

SQL Editor가 열리면 우측 상단 또는 좌측 패널에서 + New query 버튼을 클릭하세요.

아래 버튼으로 SQL 전체 복사

SQL 내용 (참고용)

-- ================================================================
-- 인 다이렉트 카보험 DB 초기화 SQL (한번만 실행하면 됩니다)
-- ================================================================

-- ① 직원 테이블
CREATE TABLE IF NOT EXISTS staff (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  staff_id TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL,
  name TEXT NOT NULL,
  role TEXT DEFAULT 'staff',
  dept TEXT, title TEXT, phone TEXT, email TEXT,
  permissions JSONB DEFAULT '[]',
  active BOOLEAN DEFAULT true,
  last_login TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  deleted BOOLEAN DEFAULT false
);

-- ② 고객 테이블
CREATE TABLE IF NOT EXISTS customers (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL, phone TEXT, email TEXT,
  gender TEXT, birth_year TEXT, resident_num TEXT,
  address TEXT, bank_account TEXT, card_number TEXT,
  car_number TEXT, car_model TEXT, insurer TEXT,
  start_date DATE, expiry_date DATE, premium BIGINT,
  contract_type TEXT, referrer TEXT, assigned_staff TEXT,
  status TEXT DEFAULT 'active', notice TEXT,
  photos JSONB DEFAULT '[]', profile_photo TEXT,
  join_date DATE DEFAULT CURRENT_DATE,
  source TEXT DEFAULT 'staff',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  deleted BOOLEAN DEFAULT false
);

-- ③ 계약 테이블
CREATE TABLE IF NOT EXISTS contracts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  customer_id UUID REFERENCES customers(id),
  car_number TEXT, car_model TEXT, insurer TEXT,
  insured TEXT, insured_phone TEXT,
  contractor TEXT, contractor_phone TEXT,
  start_date DATE, expiry_date DATE, premium BIGINT,
  renewal_status TEXT DEFAULT 'pending',
  contract_type TEXT, referrer TEXT,
  assigned_to TEXT, memo TEXT,
  status TEXT DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  deleted BOOLEAN DEFAULT false
);

-- ④ 파트너 테이블
CREATE TABLE IF NOT EXISTS partners (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  partner_id TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL, name TEXT NOT NULL,
  type TEXT DEFAULT 'dealer', phone TEXT, email TEXT,
  biz_number TEXT, address TEXT,
  status TEXT DEFAULT 'pending',
  assigned_staff TEXT, notice TEXT, memo TEXT,
  reject_reason TEXT,
  photos JSONB DEFAULT '[]', profile_photo TEXT,
  join_date DATE DEFAULT CURRENT_DATE,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  deleted BOOLEAN DEFAULT false
);

-- ⑤ 유입 고객 테이블 (웹/광고 DM)
CREATE TABLE IF NOT EXISTS leads (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL, phone TEXT NOT NULL,
  car_number TEXT, car_model TEXT,
  insurer TEXT, expiry_date DATE, message TEXT,
  source TEXT DEFAULT 'web',
  ad_code TEXT, utm_source TEXT, utm_campaign TEXT,
  status TEXT DEFAULT 'new',
  assigned_to TEXT, memo TEXT,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  deleted BOOLEAN DEFAULT false
);

-- ⑥ 공지사항
CREATE TABLE IF NOT EXISTS notices (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  title TEXT NOT NULL, content TEXT,
  priority TEXT DEFAULT 'normal',
  author TEXT, target TEXT DEFAULT 'all',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  deleted BOOLEAN DEFAULT false
);

-- ⑦ 이력 로그
CREATE TABLE IF NOT EXISTS history_logs (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  target_id TEXT, target_type TEXT,
  staff_id TEXT, staff_name TEXT, action TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- ⑧ 샘플 직원 데이터 삽입
INSERT INTO staff (staff_id, password, name, role, dept, title, active) VALUES
  ('admin',      'admin123',  '대표자',   'admin',     '경영',   '대표',    true),
  ('secretary1', 'sec123',    '이비서',   'secretary', '관리',   '비서',    true),
  ('staff1',     'staff123',  '김직원',   'staff',     '영업1팀','팀장',    true),
  ('staff2',     'staff123',  '박직원',   'staff',     '영업2팀','사원',    true)
ON CONFLICT (staff_id) DO NOTHING;

-- ⑨ RLS 활성화 (보안)
ALTER TABLE staff        ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers    ENABLE ROW LEVEL SECURITY;
ALTER TABLE contracts    ENABLE ROW LEVEL SECURITY;
ALTER TABLE partners     ENABLE ROW LEVEL SECURITY;
ALTER TABLE leads        ENABLE ROW LEVEL SECURITY;
ALTER TABLE notices      ENABLE ROW LEVEL SECURITY;
ALTER TABLE history_logs ENABLE ROW LEVEL SECURITY;

-- ⑩ 개발용 전체 허용 정책 (중복 생성 방지)
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='staff'        AND policyname='allow_all') THEN
    CREATE POLICY "allow_all" ON staff        FOR ALL USING (true) WITH CHECK (true); END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='customers'    AND policyname='allow_all') THEN
    CREATE POLICY "allow_all" ON customers    FOR ALL USING (true) WITH CHECK (true); END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='contracts'    AND policyname='allow_all') THEN
    CREATE POLICY "allow_all" ON contracts    FOR ALL USING (true) WITH CHECK (true); END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='partners'     AND policyname='allow_all') THEN
    CREATE POLICY "allow_all" ON partners     FOR ALL USING (true) WITH CHECK (true); END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='leads'        AND policyname='allow_all') THEN
    CREATE POLICY "allow_all" ON leads        FOR ALL USING (true) WITH CHECK (true); END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='notices'      AND policyname='allow_all') THEN
    CREATE POLICY "allow_all" ON notices      FOR ALL USING (true) WITH CHECK (true); END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE tablename='history_logs' AND policyname='allow_all') THEN
    CREATE POLICY "allow_all" ON history_logs FOR ALL USING (true) WITH CHECK (true); END IF;
END $$;

-- ⑪ Realtime 활성화 (실시간 알람)
ALTER PUBLICATION supabase_realtime ADD TABLE leads;
ALTER PUBLICATION supabase_realtime ADD TABLE customers;
ALTER PUBLICATION supabase_realtime ADD TABLE contracts;

SQL Editor 우측 상단 ▶ Run 버튼 클릭

또는 단축키 Ctrl + Enter (Mac: Cmd+Enter)

Table Editor에서 테이블 생성 확인

왼쪽 메뉴 📋 Table Editor 클릭 → 아래 테이블들이 보이면 성공입니다:

아래 버튼을 클릭해 연결을 확인하세요

SQL 실행 후 직원 4명이 나오면 완전히 성공입니다.