데이터베이스

CSO 정산 포털의 데이터베이스 구조, 데이터 관리 방법, 무결성 검사에 대해 설명합니다.

Supabase PostgreSQL 구조

포털은 Supabase가 제공하는 PostgreSQL 데이터베이스를 사용합니다. Supabase Auth는 사용하지 않고 자체 JWT 인증을 구현하며, DB 접근은 Supabase 클라이언트(@supabase/supabase-js)를 통해 수행합니다.

접근 방식

서버 측 API 라우트에서는 Service Role Key를 사용하여 RLS를 우회합니다. 클라이언트에서 직접 DB에 접근하는 경우는 없으며, 모든 데이터 조작은 API 라우트를 경유합니다.

주요 테이블

members (회원)

CSO 업체 회원 정보를 저장합니다.

settlements (정산)

SIT 솔루션에서 내보낸 정산 데이터를 저장합니다. 엑셀 1행이 1건의 처방/거래에 해당합니다.

총 47개의 한글 컬럼이 존재합니다. 수수료율, 인센티브율 등 상세 항목은 제약사 정산 구조에 따릅니다.

주의: settlements.business_number는 거래처(약국) 사업자번호입니다. CSO 업체의 사업자번호가 아닙니다. CSO 업체 사업자번호를 찾으려면 CSO관리업체 → cso_matching 테이블을 JOIN해야 합니다.

cso_matching (CSO 매칭)

정산서의 CSO관리업체 이름과 회원의 사업자번호를 매핑합니다. 이 테이블이 없으면 회원은 자기 정산을 조회할 수 없습니다.

매칭 관계: settlements.CSO관리업체 = cso_matching.cso_company_name → cso_matching.business_number = members.business_number

email_logs (이메일 로그)

발송된 모든 이메일의 이력을 저장합니다.

template_type 목록:

company_settings (회사 설정)

사이트 전체 설정을 저장합니다. 단일 행 테이블입니다.

password_reset_tokens (비밀번호 재설정 토큰)

비밀번호 찾기 요청 시 생성되는 일회용 토큰을 저장합니다. 유효기간은 30분입니다.

settlement_uploads (업로드 이력)

정산서 업로드 이력과 접속 업체 스냅샷을 저장합니다.

RLS (Row Level Security) 정책

포털은 서버 측에서 권한을 검증하고 Service Role Key로 DB에 접근하므로, Supabase RLS는 보조적인 안전장치 역할입니다.

정산 데이터 접근 제어

일반회원의 정산 데이터 접근은 API 라우트에서 다음과 같이 필터링됩니다.

1. JWT에서 business_number 추출
2. cso_matching 테이블에서 해당 사업자번호에 매핑된 cso_company_name 목록 조회
3. settlements 테이블에서 CSO관리업체 IN (매칭된 업체명) 조건으로 필터링

관리자(is_admin: true)는 필터 없이 전체 데이터에 접근합니다.

데이터 업로드 절차

SIT 솔루션에서 내보낸 엑셀 파일을 포털에 업로드하는 절차입니다.

업로드 과정 (/admin/upload)

1. 파일 선택: .xlsx 또는 .xls 파일을 드래그 앤 드롭하거나 선택합니다.

   • 최대 파일 크기: 4MB
   • 첫 번째 시트만 처리됩니다.

2. 컬럼 매핑 확인 (선택): “컬럼 매핑 확인” 버튼을 클릭하면 엑셀 컬럼과 DB 컬럼의 매핑을 미리 볼 수 있습니다.

   • 컬럼명이 정확히 일치하면 자동 매핑
   • 유사한 이름은 유사도 점수(0.6 이상)로 자동 매핑
   • 자동 매핑 실패 시 수동으로 지정 가능
   • 필수 컬럼: 사업자번호, 정산월

3. 업로드 실행: “바로 업로드” 또는 “매핑 확인 후 업로드” 클릭

   • 같은 정산월 데이터가 있으면 기존 데이터를 삭제한 후 새 데이터를 삽입합니다.
   • 배치 크기: 500건 단위로 DB에 삽입

4. 업로드 결과 확인: 성공 건수, 실패 건수, 업로드된 정산월 목록이 표시됩니다.

5. 이메일 발송 (선택): 업로드 완료 후 “정산서 업로드 알림” 이메일을 발송할 수 있습니다.

   • 매칭된 CSO 업체 회원에게만 발송됩니다.

데이터 관리 (/admin/data)

업로드된 정산 데이터를 정산월 기준으로 관리합니다.

CSO 매칭 무결성 검사

무결성 검사란

정산서의 CSO관리업체와 회원 사업자번호 간 매핑이 올바른지 검사합니다. 매칭이 누락되면 해당 업체의 정산 데이터가 어떤 회원에게도 보이지 않습니다.

매칭 상태 (/admin/integrity)

무결성 검사 결과 항목

매칭 데이터 수정

무결성 검사 페이지에서 missing_match 상태의 업체에 대해 사업자번호를 직접 입력하여 매칭을 추가할 수 있습니다. 매칭 추가 시 cso_matching 테이블에 UPSERT됩니다.

데이터 백업/복원

Supabase 자동 백업

Supabase Pro 플랜에서 제공하는 자동 백업을 사용합니다.

• 일일 백업: 매일 자동 수행
• 보관 기간: 7일 (Pro 플랜 기본)
• 복원: Supabase 대시보드 → Database → Backups에서 특정 시점으로 복원

수동 백업

Supabase 대시보드에서 SQL Editor를 통해 특정 테이블을 CSV로 내보낼 수 있습니다.

-- 정산 데이터 확인 (특정 월)
SELECT COUNT(*) FROM settlements WHERE "정산월" = '2026-01';
 
-- 매칭 데이터 전체 조회
SELECT * FROM cso_matching ORDER BY cso_company_name;
 
-- 회원 목록 (비밀번호 해시 제외)
SELECT id, business_number, company_name, email, is_admin, is_approved, created_at
FROM members ORDER BY created_at DESC;

정산 데이터 복원

정산 데이터는 원본 엑셀 파일을 다시 업로드하면 복원됩니다. 같은 정산월 데이터를 업로드하면 기존 데이터가 교체되므로, 원본 엑셀 파일을 별도로 보관하는 것이 중요합니다.