🗄️ Supabase 설정 가이드

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

좌측 메뉴에서 SQL Editor 클릭

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

New Query 버튼 클릭

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

아래 버튼으로 SQL 전체 복사

SQL 내용 (참고용)

-- ================================================================
-- 인 다이렉트 카보험  Supabase 완전 설치 SQL  v3.0
-- ================================================================
-- ✅ 이미 실행한 적 있어도 안전하게 재실행 가능 (멱등성 보장)
-- ✅ 기존 데이터는 절대 삭제되지 않습니다
-- ⚠️ 직원 비밀번호 평문 저장 — 내부 전산 전용
-- ================================================================

-- [STEP 0] 확장 모듈
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "unaccent";

-- [STEP 1] 테이블 생성 ──────────────────────────────────────────

-- ① 직원
CREATE TABLE IF NOT EXISTS staff (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  staff_id    TEXT        NOT NULL UNIQUE,
  password    TEXT        NOT NULL,   -- ⚠️ 평문 저장 — 내부 전산 전용
  name        TEXT        NOT NULL,
  role        TEXT        NOT NULL DEFAULT 'staff'
                          CHECK (role IN ('admin','secretary','staff')),
  dept        TEXT, title TEXT, phone TEXT, email TEXT,
  permissions JSONB       NOT NULL DEFAULT '[]'::JSONB,
  active      BOOLEAN     NOT NULL DEFAULT true,
  last_login  TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted     BOOLEAN     NOT NULL DEFAULT false
);

-- ② 고객
CREATE TABLE IF NOT EXISTS customers (
  id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  name             TEXT        NOT NULL,
  phone            TEXT, email TEXT,
  gender           TEXT        CHECK (gender IN ('male','female') OR gender IS NULL OR gender = ''),
  birth_year       TEXT, resident_num TEXT,
  address          TEXT, bank_account TEXT, card_number TEXT,
  car_number       TEXT, car_model TEXT,
  insurer          TEXT, insurance_company TEXT,
  start_date       DATE, expiry_date DATE,
  premium          NUMERIC(15,0),
  contract_type    TEXT, referrer TEXT, assigned_staff TEXT,
  status           TEXT        NOT NULL DEFAULT 'active'
                               CHECK (status IN ('active','vip','warning','inactive','new_joining','new_done')),
  notice           TEXT,
  photos           JSONB       NOT NULL DEFAULT '[]'::JSONB,
  profile_photo    TEXT,
  join_date        DATE        DEFAULT CURRENT_DATE,
  source           TEXT        NOT NULL DEFAULT 'staff'
                               CHECK (source IN ('staff','web','ad_kakao','ad_naver','ad_insta','ad_google','dm','partner','referral')),
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted          BOOLEAN     NOT NULL DEFAULT false
);

-- ③ 계약
CREATE TABLE IF NOT EXISTS contracts (
  id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id      UUID        REFERENCES customers(id) ON DELETE SET NULL ON UPDATE CASCADE,
  car_number       TEXT, car_model TEXT, insurer TEXT,
  insured          TEXT, insured_phone TEXT, insured_rrn TEXT,
  contractor       TEXT, contractor_phone TEXT,
  contractor_rrn   TEXT, contractor_addr TEXT,
  start_date       DATE, expiry_date DATE,
  premium          NUMERIC(15,0),
  renewal_status   TEXT        NOT NULL DEFAULT 'pending'
                               CHECK (renewal_status IN ('pending','contacted','quoted','confirmed','completed','canceled')),
  renewal_note     TEXT, contract_type TEXT,
  referrer         TEXT, assigned_to TEXT, staff_name TEXT, memo TEXT,
  policy_number    TEXT, imported_at TIMESTAMPTZ,
  email            TEXT, address TEXT, bank_info TEXT,
  card_number      TEXT, payment_info TEXT,
  status           TEXT        NOT NULL DEFAULT 'active'
                               CHECK (status IN ('active','inactive')),
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted          BOOLEAN     NOT NULL DEFAULT false
);

-- ④ 파트너
CREATE TABLE IF NOT EXISTS partners (
  id             UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  partner_id     TEXT        NOT NULL UNIQUE,
  password       TEXT        NOT NULL, name TEXT NOT NULL,
  type           TEXT        NOT NULL DEFAULT 'dealer'
                             CHECK (type IN ('dealer','agent')),
  phone          TEXT, contact TEXT, dealer_name TEXT,
  email          TEXT, biz_number TEXT, address TEXT,
  status         TEXT        NOT NULL DEFAULT 'pending'
                             CHECK (status IN ('pending','approved','rejected','suspended')),
  assigned_staff TEXT, notice TEXT, memo TEXT, reject_reason TEXT,
  contract_count INTEGER     NOT NULL DEFAULT 0,
  photos         JSONB       NOT NULL DEFAULT '[]'::JSONB,
  profile_photo  TEXT,
  join_date      DATE        DEFAULT CURRENT_DATE,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted        BOOLEAN     NOT NULL DEFAULT false
);

-- ⑤ 신규 유입 (leads)
CREATE TABLE IF NOT EXISTS leads (
  id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  name          TEXT        NOT NULL, phone TEXT NOT NULL,
  car_number    TEXT, car_model TEXT,
  insurer       TEXT, expiry_date DATE, message TEXT,
  source        TEXT        NOT NULL DEFAULT 'web'
                            CHECK (source IN ('web','ad_kakao','ad_naver','ad_insta','ad_google','dm','partner','referral')),
  ad_code       TEXT, utm_source TEXT, utm_medium TEXT, utm_campaign TEXT,
  type          TEXT,
  status        TEXT        NOT NULL DEFAULT 'new'
                            CHECK (status IN ('new','contacted','consulting','converted','cancel','lost')),
  internal_memo TEXT, done_at TIMESTAMPTZ,
  assigned_to   TEXT, memo TEXT,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted       BOOLEAN     NOT NULL DEFAULT false
);

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

-- ⑦ 이력 로그 (불변 append-only)
CREATE TABLE IF NOT EXISTS history_logs (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  target_id   TEXT        NOT NULL,
  target_type TEXT        NOT NULL
              CHECK (target_type IN ('customer','contract','partner','lead','staff','notice')),
  staff_id    TEXT, staff_name TEXT, action TEXT NOT NULL,
  detail      JSONB       NOT NULL DEFAULT '{}'::JSONB,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- [STEP 2] 누락 컬럼 보완 (구 DB 호환) ─────────────────────────
ALTER TABLE customers ADD COLUMN IF NOT EXISTS insurance_company TEXT;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS card_number       TEXT;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS bank_account      TEXT;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS address           TEXT;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS email             TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS insured_rrn       TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS contractor_rrn    TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS contractor_addr   TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS renewal_note      TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS policy_number     TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS imported_at       TIMESTAMPTZ;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS email             TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS address           TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS bank_info         TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS card_number       TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS payment_info      TEXT;
ALTER TABLE contracts ADD COLUMN IF NOT EXISTS staff_name        TEXT;
ALTER TABLE partners  ADD COLUMN IF NOT EXISTS contact           TEXT;
ALTER TABLE partners  ADD COLUMN IF NOT EXISTS dealer_name       TEXT;
ALTER TABLE partners  ADD COLUMN IF NOT EXISTS contract_count    INTEGER NOT NULL DEFAULT 0;
ALTER TABLE partners  ADD COLUMN IF NOT EXISTS join_date         DATE DEFAULT CURRENT_DATE;
ALTER TABLE leads     ADD COLUMN IF NOT EXISTS type              TEXT;
ALTER TABLE leads     ADD COLUMN IF NOT EXISTS utm_medium        TEXT;
ALTER TABLE leads     ADD COLUMN IF NOT EXISTS internal_memo     TEXT;
ALTER TABLE leads     ADD COLUMN IF NOT EXISTS done_at           TIMESTAMPTZ;
ALTER TABLE notices   ADD COLUMN IF NOT EXISTS popup             BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE notices   ADD COLUMN IF NOT EXISTS author_id         TEXT;
ALTER TABLE history_logs ADD COLUMN IF NOT EXISTS detail         JSONB NOT NULL DEFAULT '{}'::JSONB;

-- premium 타입 보정 (BIGINT → NUMERIC, 이미 NUMERIC이면 무시)
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.columns
    WHERE table_name='customers' AND column_name='premium' AND data_type='bigint')
  THEN ALTER TABLE customers ALTER COLUMN premium TYPE NUMERIC(15,0) USING premium::NUMERIC; END IF;
  IF EXISTS (SELECT 1 FROM information_schema.columns
    WHERE table_name='contracts' AND column_name='premium' AND data_type='bigint')
  THEN ALTER TABLE contracts ALTER COLUMN premium TYPE NUMERIC(15,0) USING premium::NUMERIC; END IF;
END; $$;

-- [STEP 3] 검색 인덱스 ──────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_cust_car_number   ON customers (car_number)     WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_cust_phone        ON customers (phone)           WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_cust_expiry       ON customers (expiry_date)     WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_cust_assigned     ON customers (assigned_staff)  WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_cust_status       ON customers (status)          WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_cust_name         ON customers (name)            WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_cust_created      ON customers (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_ct_car_number     ON contracts (car_number)      WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_ct_expiry         ON contracts (expiry_date)     WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_ct_customer_id    ON contracts (customer_id)     WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_ct_assigned_to    ON contracts (assigned_to)     WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_ct_renewal_status ON contracts (renewal_status)  WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_ct_policy_number  ON contracts (policy_number)   WHERE deleted=false AND policy_number IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_ct_insurer        ON contracts (insurer)         WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_ct_created        ON contracts (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_pt_status         ON partners (status)           WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_pt_type           ON partners (type)             WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_leads_status      ON leads (status)              WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_leads_phone       ON leads (phone)               WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_leads_created     ON leads (created_at DESC);
CREATE INDEX IF NOT EXISTS idx_leads_assigned    ON leads (assigned_to)         WHERE deleted=false;
CREATE INDEX IF NOT EXISTS idx_hl_target         ON history_logs (target_id, target_type);
CREATE INDEX IF NOT EXISTS idx_hl_created        ON history_logs (created_at DESC);

-- [STEP 4] updated_at 자동 갱신 트리거 ─────────────────────────
CREATE OR REPLACE FUNCTION fn_set_updated_at()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END; $$;

DO $$
DECLARE tbl TEXT;
BEGIN
  FOREACH tbl IN ARRAY ARRAY['staff','customers','contracts','partners','leads','notices']
  LOOP
    IF NOT EXISTS (
      SELECT 1 FROM pg_trigger
      WHERE tgname = 'trg_'||tbl||'_updated_at' AND tgrelid = tbl::regclass
    ) THEN
      EXECUTE format(
        'CREATE TRIGGER trg_%I_updated_at BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION fn_set_updated_at()',
        tbl, tbl
      );
    END IF;
  END LOOP;
END; $$;

-- [STEP 5] settings 테이블 (IP허용목록·전사설정 공유) ─────────────
CREATE TABLE IF NOT EXISTS settings (
  id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  key         TEXT        NOT NULL UNIQUE,
  value       JSONB       NOT NULL DEFAULT 'null'::JSONB,
  updated_by  TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
ALTER TABLE settings ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "settings_anon_all" ON settings;
CREATE POLICY "settings_anon_all" ON settings FOR ALL TO anon USING (true) WITH CHECK (true);
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname='trg_settings_updated_at' AND tgrelid='settings'::regclass) THEN
    CREATE TRIGGER trg_settings_updated_at BEFORE UPDATE ON settings FOR EACH ROW EXECUTE FUNCTION fn_set_updated_at();
  END IF;
END; $$;
INSERT INTO settings (key, value) VALUES
  ('ip_restrict_enabled', 'false'::JSONB),
  ('ip_allow_list',       '[]'::JSONB),
  ('sheets_url',          'null'::JSONB)
ON CONFLICT (key) DO NOTHING;

-- [STEP 6] 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 $$
DECLARE r RECORD;
BEGIN
  FOR r IN SELECT policyname, tablename FROM pg_policies
    WHERE schemaname='public'
      AND tablename IN ('staff','customers','contracts','partners','leads','notices','history_logs')
  LOOP
    EXECUTE format('DROP POLICY IF EXISTS %I ON %I', r.policyname, r.tablename);
  END LOOP;
END; $$;

-- staff: SELECT·INSERT·UPDATE 허용 (로그인 검증 + 직원 관리)
CREATE POLICY "staff_anon_select" ON staff FOR SELECT TO anon USING (true);
CREATE POLICY "staff_anon_insert" ON staff FOR INSERT TO anon WITH CHECK (true);
CREATE POLICY "staff_anon_update" ON staff FOR UPDATE TO anon USING (true) WITH CHECK (true);
-- customers · contracts · partners · notices: 전체 허용
CREATE POLICY "customers_anon_all"  ON customers    FOR ALL TO anon USING (true) WITH CHECK (true);
CREATE POLICY "contracts_anon_all"  ON contracts    FOR ALL TO anon USING (true) WITH CHECK (true);
CREATE POLICY "partners_anon_all"   ON partners     FOR ALL TO anon USING (true) WITH CHECK (true);
CREATE POLICY "notices_anon_all"    ON notices      FOR ALL TO anon USING (true) WITH CHECK (true);
-- leads: 웹(anon) INSERT + 직원 SELECT·UPDATE·DELETE
CREATE POLICY "leads_anon_insert"   ON leads FOR INSERT TO anon WITH CHECK (true);
CREATE POLICY "leads_anon_select"   ON leads FOR SELECT TO anon USING (true);
CREATE POLICY "leads_anon_update"   ON leads FOR UPDATE TO anon USING (true) WITH CHECK (true);
CREATE POLICY "leads_anon_delete"   ON leads FOR DELETE TO anon USING (true);
CREATE POLICY "leads_auth_all"      ON leads FOR ALL  TO authenticated USING (true) WITH CHECK (true);
-- history_logs: INSERT·SELECT만 허용 (불변 로그)
CREATE POLICY "history_logs_anon_insert" ON history_logs FOR INSERT TO anon WITH CHECK (true);
CREATE POLICY "history_logs_anon_select" ON history_logs FOR SELECT TO anon USING (true);

-- [STEP 7] Realtime 구독 ─────────────────────────────────────────
DO $$
DECLARE tbl TEXT;
BEGIN
  FOREACH tbl IN ARRAY ARRAY['leads','customers','contracts']
  LOOP
    BEGIN
      EXECUTE format('ALTER PUBLICATION supabase_realtime ADD TABLE %I', tbl);
    EXCEPTION WHEN duplicate_object THEN NULL;
    END;
  END LOOP;
END; $$;

-- [STEP 8] 직원 계정 초기화 ──────────────────────────────────────
INSERT INTO staff (staff_id, password, name, role, dept, title, active, permissions) VALUES
  ('speed',     'dlscfj22',     '신인철', 'admin',     '관리', '대표',  true, '["notice","user_manage","partner_approve"]'::JSONB),
  ('secretary', 'secretary123', '이윤미', 'secretary', '관리', '실장',  true, '["notice","user_manage"]'::JSONB),
  ('staff1',    'staff123',     '박승홍', 'staff',     '영업', '부장',  true, '[]'::JSONB),
  ('staff2',    'staff1234',    '박미래', 'staff',     '영업', '과장',  true, '[]'::JSONB)
ON CONFLICT (staff_id) DO UPDATE SET
  password=EXCLUDED.password, name=EXCLUDED.name,
  role=EXCLUDED.role, dept=EXCLUDED.dept, title=EXCLUDED.title,
  active=EXCLUDED.active, updated_at=now();

-- [STEP 8] 완료 확인 ─────────────────────────────────────────────
SELECT
  t.table_name AS "테이블",
  pg_size_pretty(pg_total_relation_size(t.table_name::regclass)) AS "크기",
  (XPATH('/row/c/text()',
    query_to_xml(format('SELECT count(*) AS c FROM %I', t.table_name), false, true, '')
  ))[1]::TEXT::BIGINT AS "전체 행 수"
FROM (
  VALUES ('staff'),('customers'),('contracts'),
         ('partners'),('leads'),('notices'),('history_logs')
) AS t(table_name)
ORDER BY t.table_name;

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

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

Table Editor에서 테이블 생성 확인

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

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

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