데이터베이스
CSO 정산 포털의 데이터베이스 구조, 데이터 관리 방법, 무결성 검사에 대해 설명합니다.
Supabase PostgreSQL 구조
포털은 Supabase가 제공하는 PostgreSQL 데이터베이스를 사용합니다. Supabase Auth는 사용하지 않고 자체 JWT 인증을 구현하며, DB 접근은 Supabase 클라이언트(@supabase/supabase-js)를 통해 수행합니다.
접근 방식
| 용도 | 키 | 환경변수 |
|---|---|---|
| 클라이언트 (읽기) | Anon Key | NEXT_PUBLIC_SUPABASE_ANON_KEY |
| 서버 (읽기/쓰기) | Service Role Key | SUPABASE_SERVICE_ROLE_KEY |
서버 측 API 라우트에서는 Service Role Key를 사용하여 RLS를 우회합니다. 클라이언트에서 직접 DB에 접근하는 경우는 없으며, 모든 데이터 조작은 API 라우트를 경유합니다.
주요 테이블
members (회원)
CSO 업체 회원 정보를 저장합니다.
| 컬럼 | 타입 | 설명 |
|---|---|---|
id | UUID | 기본키 |
business_number | VARCHAR(10) | 사업자번호 (Unique, 로그인 ID) |
company_name | TEXT | 업체명 |
ceo_name | TEXT | 대표자명 |
email | TEXT | 대표 이메일 |
password_hash | TEXT | bcrypt 해시 |
is_admin | BOOLEAN | 관리자 여부 |
is_approved | BOOLEAN | 가입 승인 여부 |
is_test | BOOLEAN | 테스트 계정 여부 |
must_change_password | BOOLEAN | 비밀번호 변경 강제 |
profile_complete | BOOLEAN | 회원정보 완성 여부 |
failed_login_attempts | INTEGER | 로그인 실패 횟수 (15회 시 잠금) |
locked_at | TIMESTAMPTZ | 계정 잠금 일시 |
last_login_at | TIMESTAMPTZ | 최근 로그인 일시 |
settlements (정산)
SIT 솔루션에서 내보낸 정산 데이터를 저장합니다. 엑셀 1행이 1건의 처방/거래에 해당합니다.
| 컬럼 | 타입 | 설명 |
|---|---|---|
id | SERIAL | 기본키 |
business_number | VARCHAR(10) | 거래처(약국) 사업자번호 |
정산월 | TEXT | 정산 기준월 (예: 2026-01) |
처방월 | TEXT | 처방 기준월 |
CSO관리업체 | TEXT | CSO 업체명 (매칭 키) |
거래처명 | TEXT | 약국/거래처 이름 |
영업사원 | TEXT | 담당 영업사원 |
제품명 | TEXT | 의약품 제품명 |
수량 | NUMERIC | 처방 수량 |
금액 | NUMERIC | 거래 금액 |
제약수수료_합계 | NUMERIC | 제약사 수수료 합계 |
담당수수료_합계 | NUMERIC | 담당자 수수료 합계 |
upload_year_month | TEXT | 업로드 시점 연월 |
upload_date | TEXT | 업로드 일시 |
총 47개의 한글 컬럼이 존재합니다. 수수료율, 인센티브율 등 상세 항목은 제약사 정산 구조에 따릅니다.
주의:
settlements.business_number는 거래처(약국) 사업자번호입니다. CSO 업체의 사업자번호가 아닙니다. CSO 업체 사업자번호를 찾으려면CSO관리업체→cso_matching테이블을 JOIN해야 합니다.
cso_matching (CSO 매칭)
정산서의 CSO관리업체 이름과 회원의 사업자번호를 매핑합니다. 이 테이블이 없으면 회원은 자기 정산을 조회할 수 없습니다.
| 컬럼 | 타입 | 설명 |
|---|---|---|
cso_company_name | TEXT | 정산서 CSO관리업체명 (PK) |
business_number | VARCHAR(10) | 해당 업체의 사업자번호 |
created_at | TIMESTAMPTZ | 생성일시 |
updated_at | TIMESTAMPTZ | 수정일시 |
매칭 관계: settlements.CSO관리업체 = cso_matching.cso_company_name → cso_matching.business_number = members.business_number
email_logs (이메일 로그)
발송된 모든 이메일의 이력을 저장합니다.
| 컬럼 | 타입 | 설명 |
|---|---|---|
id | UUID | 기본키 |
recipient_email | TEXT | 수신자 이메일 |
subject | TEXT | 이메일 제목 |
template_type | TEXT | 유형 (아래 참조) |
status | TEXT | pending / sent / failed |
error_message | TEXT | 실패 시 에러 메시지 |
sent_at | TIMESTAMPTZ | 발송 완료 일시 |
created_at | TIMESTAMPTZ | 생성 일시 |
template_type 목록:
| 값 | 설명 | 수신자 |
|---|---|---|
registration_request | 가입 신청 알림 | 관리자 |
approval_complete | 가입 승인 알림 | 회원 |
approval_rejected | 가입 거부 알림 | 회원 |
settlement_uploaded | 정산서 업로드 알림 | 회원 |
password_reset | 비밀번호 재설정 | 회원 |
mail_merge | 메일머지 발송 | 회원 |
company_settings (회사 설정)
사이트 전체 설정을 저장합니다. 단일 행 테이블입니다.
| 컬럼 그룹 | 주요 항목 |
|---|---|
| 회사 정보 | 회사명, 대표자명, 사업자번호, 주소, 연락처 |
| 이메일 설정 | 프로바이더(Resend/SMTP), SMTP 호스트/포트/인증, 발송 간격 |
| 알림 설정 | 이메일 유형별 ON/OFF (5종) |
| 공지사항 | 대시보드 공지 내용 (변수 치환 지원) |
password_reset_tokens (비밀번호 재설정 토큰)
비밀번호 찾기 요청 시 생성되는 일회용 토큰을 저장합니다. 유효기간은 30분입니다.
settlement_uploads (업로드 이력)
정산서 업로드 이력과 접속 업체 스냅샷을 저장합니다.
RLS (Row Level Security) 정책
포털은 서버 측에서 권한을 검증하고 Service Role Key로 DB에 접근하므로, Supabase RLS는 보조적인 안전장치 역할입니다.
정산 데이터 접근 제어
일반회원의 정산 데이터 접근은 API 라우트에서 다음과 같이 필터링됩니다.
- JWT에서
business_number추출 cso_matching테이블에서 해당 사업자번호에 매핑된cso_company_name목록 조회settlements테이블에서CSO관리업체 IN (매칭된 업체명)조건으로 필터링
관리자(is_admin: true)는 필터 없이 전체 데이터에 접근합니다.
데이터 업로드 절차
SIT 솔루션에서 내보낸 엑셀 파일을 포털에 업로드하는 절차입니다.
업로드 과정 (/admin/upload)
-
파일 선택:
.xlsx또는.xls파일을 드래그 앤 드롭하거나 선택합니다.- 최대 파일 크기: 4MB
- 첫 번째 시트만 처리됩니다.
-
컬럼 매핑 확인 (선택): “컬럼 매핑 확인” 버튼을 클릭하면 엑셀 컬럼과 DB 컬럼의 매핑을 미리 볼 수 있습니다.
- 컬럼명이 정확히 일치하면 자동 매핑
- 유사한 이름은 유사도 점수(0.6 이상)로 자동 매핑
- 자동 매핑 실패 시 수동으로 지정 가능
- 필수 컬럼:
사업자번호,정산월
-
업로드 실행: “바로 업로드” 또는 “매핑 확인 후 업로드” 클릭
- 같은 정산월 데이터가 있으면 기존 데이터를 삭제한 후 새 데이터를 삽입합니다.
- 배치 크기: 500건 단위로 DB에 삽입
-
업로드 결과 확인: 성공 건수, 실패 건수, 업로드된 정산월 목록이 표시됩니다.
-
이메일 발송 (선택): 업로드 완료 후 “정산서 업로드 알림” 이메일을 발송할 수 있습니다.
- 매칭된 CSO 업체 회원에게만 발송됩니다.
데이터 관리 (/admin/data)
업로드된 정산 데이터를 정산월 기준으로 관리합니다.
| 기능 | 설명 |
|---|---|
| 통계 카드 | 전체 건수, 정산월 수, CSO 업체 수 |
| 정산월별 테이블 | 각 월의 건수, CSO 업체 수, 업로드 일시 |
| 월별 삭제 | 특정 정산월 데이터 전체 삭제 (확인 다이얼로그) |
| 새로고침 | 최신 통계 재조회 |
CSO 매칭 무결성 검사
무결성 검사란
정산서의 CSO관리업체와 회원 사업자번호 간 매핑이 올바른지 검사합니다. 매칭이 누락되면 해당 업체의 정산 데이터가 어떤 회원에게도 보이지 않습니다.
매칭 상태 (/admin/integrity)
| 상태 | 의미 | 조치 |
|---|---|---|
normal | 정상 매칭 완료 | 조치 불필요 |
unregistered | 매칭은 있으나 해당 사업자번호로 가입한 회원이 없음 | 회원 가입 안내 또는 사업자번호 확인 |
pending_join | 회원이 가입 신청했으나 아직 승인되지 않음 | /admin/members에서 승인 처리 |
missing_match | 정산서에 업체명이 있으나 cso_matching에 매핑이 없음 | 매칭 데이터 추가 |
무결성 검사 결과 항목
| 항목 | 설명 |
|---|---|
| CSO 관리업체명 | 정산서에 기재된 업체명 |
| 사업자번호 | 매칭된 사업자번호 (없으면 비어 있음) |
| 상태 | 위 4가지 중 하나 |
| 정산 건수 | 해당 업체의 정산 데이터 건수 |
| 최근 정산월 | 가장 최근 정산월 |
| 승인 여부 | 회원 가입 승인 상태 |
매칭 데이터 수정
무결성 검사 페이지에서 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;
정산 데이터 복원
정산 데이터는 원본 엑셀 파일을 다시 업로드하면 복원됩니다. 같은 정산월 데이터를 업로드하면 기존 데이터가 교체되므로, 원본 엑셀 파일을 별도로 보관하는 것이 중요합니다.