227 lines
9.5 KiB
PL/PgSQL
227 lines
9.5 KiB
PL/PgSQL
-- =====================================================
|
|
-- 부산교통공사 1호선 고장코드 시스템 - 데이터베이스 스키마
|
|
-- =====================================================
|
|
|
|
-- 1. 부서 테이블 (departments)
|
|
-- 소속 부서 관리 및 권한 제어를 위한 테이블
|
|
CREATE TABLE IF NOT EXISTS public.departments (
|
|
id SERIAL PRIMARY KEY,
|
|
code VARCHAR(20) UNIQUE NOT NULL, -- 부서 코드 (예: SPC001, NPC001)
|
|
name VARCHAR(100) NOT NULL, -- 부서명 (예: 신평차량, 노포차량, 차량처)
|
|
description TEXT, -- 부서 설명
|
|
is_active BOOLEAN DEFAULT true, -- 활성화 여부
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 부서 코드에 인덱스 생성 (검색 성능 향상)
|
|
CREATE INDEX IF NOT EXISTS idx_departments_code ON public.departments(code);
|
|
CREATE INDEX IF NOT EXISTS idx_departments_is_active ON public.departments(is_active);
|
|
|
|
-- 부서 테이블에 초기 데이터 삽입
|
|
INSERT INTO public.departments (code, name, description) VALUES
|
|
('SPC', '신평차량', '신평차량사업소'),
|
|
('NPC', '노포차량', '노포차량사업소'),
|
|
('VHD', '차량처', '차량처')
|
|
ON CONFLICT (code) DO NOTHING;
|
|
|
|
-- 2. 사용자 테이블 (users)
|
|
-- 회원 정보 및 인증 정보를 저장하는 테이블
|
|
CREATE TABLE IF NOT EXISTS public.users (
|
|
id SERIAL PRIMARY KEY,
|
|
auth_id UUID UNIQUE, -- Supabase Auth의 사용자 ID (외래키 개념)
|
|
email VARCHAR(255) UNIQUE NOT NULL, -- 이메일 (humetro.busan.kr 도메인만)
|
|
employee_id VARCHAR(50) UNIQUE NOT NULL, -- 사번
|
|
name VARCHAR(100) NOT NULL, -- 이름
|
|
department_id INTEGER NOT NULL REFERENCES public.departments(id) ON DELETE RESTRICT, -- 소속 부서
|
|
is_active BOOLEAN DEFAULT true, -- 계정 활성화 여부
|
|
last_login_at TIMESTAMPTZ, -- 마지막 로그인 시각
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 사용자 테이블 인덱스
|
|
CREATE INDEX IF NOT EXISTS idx_users_auth_id ON public.users(auth_id);
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON public.users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_employee_id ON public.users(employee_id);
|
|
CREATE INDEX IF NOT EXISTS idx_users_department_id ON public.users(department_id);
|
|
CREATE INDEX IF NOT EXISTS idx_users_is_active ON public.users(is_active);
|
|
|
|
-- 3. 부서별 데이터 접근 권한 테이블 (department_permissions)
|
|
-- 향후 부서별 고장코드 조회/수정 권한 관리를 위한 테이블
|
|
CREATE TABLE IF NOT EXISTS public.department_permissions (
|
|
id SERIAL PRIMARY KEY,
|
|
department_id INTEGER NOT NULL REFERENCES public.departments(id) ON DELETE CASCADE,
|
|
resource_type VARCHAR(50) NOT NULL, -- 'fault_code', 'signal', 'mmi_code' 등
|
|
can_read BOOLEAN DEFAULT true, -- 조회 권한
|
|
can_write BOOLEAN DEFAULT false, -- 수정 권한
|
|
can_delete BOOLEAN DEFAULT false, -- 삭제 권한
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(department_id, resource_type)
|
|
);
|
|
|
|
-- 권한 테이블 인덱스
|
|
CREATE INDEX IF NOT EXISTS idx_dept_permissions_dept_id ON public.department_permissions(department_id);
|
|
|
|
-- 부서별 기본 권한 설정 (모든 부서에 조회 권한 부여)
|
|
INSERT INTO public.department_permissions (department_id, resource_type, can_read, can_write, can_delete)
|
|
SELECT d.id, 'fault_code', true, false, false
|
|
FROM public.departments d
|
|
ON CONFLICT (department_id, resource_type) DO NOTHING;
|
|
|
|
INSERT INTO public.department_permissions (department_id, resource_type, can_read, can_write, can_delete)
|
|
SELECT d.id, 'signal', true, false, false
|
|
FROM public.departments d
|
|
ON CONFLICT (department_id, resource_type) DO NOTHING;
|
|
|
|
INSERT INTO public.department_permissions (department_id, resource_type, can_read, can_write, can_delete)
|
|
SELECT d.id, 'mmi_code', true, false, false
|
|
FROM public.departments d
|
|
ON CONFLICT (department_id, resource_type) DO NOTHING;
|
|
|
|
-- 4. 생체인증 credential 테이블 (biometric_credentials)
|
|
-- WebAuthn/생체인증을 위한 credential 저장
|
|
CREATE TABLE IF NOT EXISTS public.biometric_credentials (
|
|
id SERIAL PRIMARY KEY,
|
|
employee_id VARCHAR(50) NOT NULL REFERENCES public.users(employee_id) ON DELETE CASCADE,
|
|
credential_id VARCHAR(255) UNIQUE NOT NULL, -- WebAuthn credential ID
|
|
credential_data JSONB NOT NULL, -- Credential 전체 데이터
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
last_used_at TIMESTAMPTZ,
|
|
UNIQUE(employee_id, credential_id)
|
|
);
|
|
|
|
-- 생체인증 인덱스
|
|
CREATE INDEX IF NOT EXISTS idx_biometric_employee_id ON public.biometric_credentials(employee_id);
|
|
CREATE INDEX IF NOT EXISTS idx_biometric_credential_id ON public.biometric_credentials(credential_id);
|
|
|
|
-- 5. 감사 로그 테이블 (audit_logs) - 선택사항
|
|
-- 사용자의 중요한 작업을 기록하기 위한 테이블
|
|
CREATE TABLE IF NOT EXISTS public.audit_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES public.users(id) ON DELETE SET NULL,
|
|
action VARCHAR(50) NOT NULL, -- 'login', 'logout', 'create', 'update', 'delete'
|
|
resource_type VARCHAR(50), -- 'fault_code', 'user', 'department' 등
|
|
resource_id VARCHAR(100), -- 대상 리소스의 ID
|
|
details JSONB, -- 추가 상세 정보
|
|
ip_address VARCHAR(45), -- 사용자 IP 주소
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- 감사 로그 인덱스
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_id ON public.audit_logs(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_action ON public.audit_logs(action);
|
|
CREATE INDEX IF NOT EXISTS idx_audit_logs_created_at ON public.audit_logs(created_at);
|
|
|
|
-- 5. 트리거: updated_at 자동 업데이트
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- departments 테이블에 트리거 적용
|
|
DROP TRIGGER IF EXISTS update_departments_updated_at ON public.departments;
|
|
CREATE TRIGGER update_departments_updated_at
|
|
BEFORE UPDATE ON public.departments
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- users 테이블에 트리거 적용
|
|
DROP TRIGGER IF EXISTS update_users_updated_at ON public.users;
|
|
CREATE TRIGGER update_users_updated_at
|
|
BEFORE UPDATE ON public.users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- department_permissions 테이블에 트리거 적용
|
|
DROP TRIGGER IF EXISTS update_dept_permissions_updated_at ON public.department_permissions;
|
|
CREATE TRIGGER update_dept_permissions_updated_at
|
|
BEFORE UPDATE ON public.department_permissions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- 6. Row Level Security (RLS) 정책 - 선택사항
|
|
-- Supabase에서 RLS를 활성화하면 부서별 데이터 접근을 자동으로 제어할 수 있습니다
|
|
|
|
-- RLS 활성화
|
|
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.departments ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.department_permissions ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- 부서 테이블: 모든 인증된 사용자가 조회 가능
|
|
CREATE POLICY "부서 목록은 모든 사용자가 조회 가능" ON public.departments
|
|
FOR SELECT
|
|
TO authenticated
|
|
USING (is_active = true);
|
|
|
|
-- 사용자 테이블: 자기 자신의 정보만 조회 가능
|
|
CREATE POLICY "사용자는 본인 정보만 조회 가능" ON public.users
|
|
FOR SELECT
|
|
TO authenticated
|
|
USING (auth.uid() = auth_id);
|
|
|
|
-- 사용자 테이블: 자기 자신의 정보만 업데이트 가능 (특정 필드만)
|
|
CREATE POLICY "사용자는 본인 정보만 수정 가능" ON public.users
|
|
FOR UPDATE
|
|
TO authenticated
|
|
USING (auth.uid() = auth_id)
|
|
WITH CHECK (auth.uid() = auth_id);
|
|
|
|
-- 7. 뷰 생성: 사용자 정보와 부서 정보를 조인한 뷰
|
|
CREATE OR REPLACE VIEW public.users_with_department AS
|
|
SELECT
|
|
u.id,
|
|
u.auth_id,
|
|
u.email,
|
|
u.employee_id,
|
|
u.name,
|
|
u.department_id,
|
|
d.code as department_code,
|
|
d.name as department_name,
|
|
u.is_active,
|
|
u.last_login_at,
|
|
u.created_at,
|
|
u.updated_at
|
|
FROM public.users u
|
|
LEFT JOIN public.departments d ON u.department_id = d.id;
|
|
|
|
-- =====================================================
|
|
-- 사용 예시 및 참고사항
|
|
-- =====================================================
|
|
|
|
-- 1. 새로운 부서 추가
|
|
-- INSERT INTO public.departments (code, name, description)
|
|
-- VALUES ('NEW001', '새로운부서', '부서 설명');
|
|
|
|
-- 2. 사용자 조회 (부서 정보 포함)
|
|
-- SELECT * FROM public.users_with_department WHERE email = 'user@humetro.busan.kr';
|
|
|
|
-- 3. 부서별 사용자 수 조회
|
|
-- SELECT d.name, COUNT(u.id) as user_count
|
|
-- FROM public.departments d
|
|
-- LEFT JOIN public.users u ON d.id = u.department_id
|
|
-- GROUP BY d.id, d.name
|
|
-- ORDER BY user_count DESC;
|
|
|
|
-- 4. 특정 부서의 권한 조회
|
|
-- SELECT d.name, dp.resource_type, dp.can_read, dp.can_write, dp.can_delete
|
|
-- FROM public.department_permissions dp
|
|
-- JOIN public.departments d ON dp.department_id = d.id
|
|
-- WHERE d.code = 'SPC';
|
|
|
|
-- =====================================================
|
|
-- 마이그레이션 롤백 (필요시)
|
|
-- =====================================================
|
|
|
|
-- DROP VIEW IF EXISTS public.users_with_department;
|
|
-- DROP TABLE IF EXISTS public.audit_logs CASCADE;
|
|
-- DROP TABLE IF EXISTS public.department_permissions CASCADE;
|
|
-- DROP TABLE IF EXISTS public.users CASCADE;
|
|
-- DROP TABLE IF EXISTS public.departments CASCADE;
|
|
-- DROP FUNCTION IF EXISTS update_updated_at_column() CASCADE;
|
|
|