king
269e779129
- vacation_db 전용 저장소(VacationDBStore), VACATION_DB_URL 미설정 시 '설정 필요' 안내 - 월간 달력: 구글식 휴가 bar(주 단위 lane 배정), 일/공휴일 빨강·토요일 파랑 - 신청 폼: 종류/기간/시작·종료 구분(종일/오전/오후)/사유 + 예상 일수 미리보기 - 일수 계산은 서버(compute_days)에서 주말+공휴일(is_red) 제외 재계산 - 워크플로: 작성중/반려→제출→승인|반려, 취소 soft delete, 본인만 수정(작성중/반려) - 권한: vacation(접근)/vacation_approver(승인), admin 통과. 설정은 admin 전용 - 공휴일(vacation_holidays) DB 기반 provider + settings CRUD, 연차잔여(vacation_balances) - 엑셀 내보내기(openpyxl), scripts/sql/vacation_db_init.sql(멱등, 2026 공휴일 seed) - main.py 등록, 메뉴 ready 전환, docs/.env.example/CLAUDE.md 갱신 Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
168 lines
8.7 KiB
PL/PgSQL
168 lines
8.7 KiB
PL/PgSQL
-- =====================================================================
|
|
-- vacation_db 초기화 스크립트 (PostgreSQL) — 휴가 관리 모듈
|
|
-- =====================================================================
|
|
-- 멱등(idempotent): 여러 번 실행해도 안전. 기존 데이터를 삭제하지 않는다.
|
|
--
|
|
-- ⚠️ 실행 전 사용자 승인 + 백업 확인 필수. DROP/TRUNCATE 없음.
|
|
--
|
|
-- 실행 방법 (운영 PostgreSQL 컨테이너명: postgres-db):
|
|
--
|
|
-- 1) DB / 역할 / 스키마 생성 (superuser 로 1회)
|
|
-- read -s -p "vacation_app password: " APP_PWD; echo
|
|
-- docker exec -i postgres-db psql -U postgres \
|
|
-- -v app_password="$APP_PWD" \
|
|
-- < scripts/sql/vacation_db_init.sql
|
|
--
|
|
-- 2) main-app .env 에 연결 정보 등록
|
|
-- VACATION_DB_URL=postgresql://vacation_app:<APP_PWD>@postgres-db:5432/vacation_db
|
|
--
|
|
-- 3) main-app 재기동
|
|
-- cd /opt/www/main && docker compose up -d --build
|
|
--
|
|
-- 주의:
|
|
-- - 기존 DB 가 있으면 DROP 하지 않는다(CREATE DATABASE 는 미존재 시에만).
|
|
-- - 비밀번호는 절대 Git 에 커밋하지 않는다. psql -v 로만 전달.
|
|
-- =====================================================================
|
|
|
|
\set ON_ERROR_STOP on
|
|
|
|
-- DB 가 없을 때만 생성
|
|
SELECT 'CREATE DATABASE vacation_db ENCODING ''UTF8'' TEMPLATE template0'
|
|
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'vacation_db')
|
|
\gexec
|
|
|
|
-- 앱 전용 로그인 역할 (expense_app / cupang_app 패턴과 동일)
|
|
SELECT 'CREATE ROLE vacation_app LOGIN PASSWORD ' || quote_literal(:'app_password')
|
|
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'vacation_app')
|
|
\gexec
|
|
|
|
-- 항상 최신 비밀번호로 동기화
|
|
SELECT 'ALTER ROLE vacation_app WITH LOGIN PASSWORD ' || quote_literal(:'app_password')
|
|
\gexec
|
|
|
|
GRANT CONNECT ON DATABASE vacation_db TO vacation_app;
|
|
|
|
-- vacation_db 컨텍스트로 전환
|
|
\connect vacation_db
|
|
|
|
-- ── updated_at 자동 갱신 트리거 함수 (멱등: CREATE OR REPLACE) ──
|
|
CREATE OR REPLACE FUNCTION vacation_set_updated_at() RETURNS trigger AS $$
|
|
BEGIN
|
|
NEW.updated_at := now();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 1) 휴가 신청 (헤더)
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS vacation_requests (
|
|
id TEXT PRIMARY KEY,
|
|
owner TEXT NOT NULL,
|
|
owner_name TEXT NOT NULL DEFAULT '',
|
|
vacation_type TEXT NOT NULL,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
start_half TEXT NOT NULL DEFAULT 'full',
|
|
end_half TEXT NOT NULL DEFAULT 'full',
|
|
days NUMERIC(5,2) NOT NULL DEFAULT 0,
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT '작성중',
|
|
approver_email TEXT,
|
|
decided_at TIMESTAMPTZ,
|
|
reject_reason TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_vacation_req_owner_start ON vacation_requests (owner, start_date DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_vacation_req_status ON vacation_requests (status);
|
|
CREATE INDEX IF NOT EXISTS idx_vacation_req_range ON vacation_requests (start_date, end_date);
|
|
|
|
DROP TRIGGER IF EXISTS trg_vacation_requests_updated ON vacation_requests;
|
|
CREATE TRIGGER trg_vacation_requests_updated
|
|
BEFORE UPDATE ON vacation_requests
|
|
FOR EACH ROW EXECUTE FUNCTION vacation_set_updated_at();
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 2) 공휴일 (달력 빨강 표시 + 휴가일수 계산 제외 기준)
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS vacation_holidays (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
holiday_date DATE NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
kind TEXT NOT NULL DEFAULT 'public',
|
|
is_red BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_vacation_holidays_date ON vacation_holidays (holiday_date);
|
|
|
|
DROP TRIGGER IF EXISTS trg_vacation_holidays_updated ON vacation_holidays;
|
|
CREATE TRIGGER trg_vacation_holidays_updated
|
|
BEFORE UPDATE ON vacation_holidays
|
|
FOR EACH ROW EXECUTE FUNCTION vacation_set_updated_at();
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 3) 사용자별 연차 잔여 (연도 단위)
|
|
-- ════════════════════════════════════════════════════════════
|
|
CREATE TABLE IF NOT EXISTS vacation_balances (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
user_email TEXT NOT NULL,
|
|
year INTEGER NOT NULL,
|
|
total_days NUMERIC(5,2) NOT NULL DEFAULT 0,
|
|
used_days NUMERIC(5,2) NOT NULL DEFAULT 0,
|
|
memo TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (user_email, year)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_vacation_balances_user_year ON vacation_balances (user_email, year);
|
|
|
|
DROP TRIGGER IF EXISTS trg_vacation_balances_updated ON vacation_balances;
|
|
CREATE TRIGGER trg_vacation_balances_updated
|
|
BEFORE UPDATE ON vacation_balances
|
|
FOR EACH ROW EXECUTE FUNCTION vacation_set_updated_at();
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 4) 공휴일 seed — 2026년 대한민국 공휴일 (멱등: ON CONFLICT DO NOTHING)
|
|
-- 연도별로 달라지므로 settings 화면에서 추가/수정/삭제 가능.
|
|
-- KASI(한국천문연구원) 발표 기준. 새 연도는 settings 또는 본 seed 추가.
|
|
-- ════════════════════════════════════════════════════════════
|
|
INSERT INTO vacation_holidays (holiday_date, name, kind) VALUES
|
|
('2026-01-01', '신정', 'public'),
|
|
('2026-02-16', '설날 연휴', 'lunar'),
|
|
('2026-02-17', '설날', 'lunar'),
|
|
('2026-02-18', '설날 연휴', 'lunar'),
|
|
('2026-03-01', '삼일절', 'public'),
|
|
('2026-03-02', '삼일절 대체', 'substitute'),
|
|
('2026-05-05', '어린이날', 'public'),
|
|
('2026-05-24', '부처님오신날', 'lunar'),
|
|
('2026-05-25', '부처님오신날 대체', 'substitute'),
|
|
('2026-06-06', '현충일', 'public'),
|
|
('2026-08-15', '광복절', 'public'),
|
|
('2026-08-17', '광복절 대체', 'substitute'),
|
|
('2026-09-24', '추석 연휴', 'lunar'),
|
|
('2026-09-25', '추석', 'lunar'),
|
|
('2026-09-26', '추석 연휴', 'lunar'),
|
|
('2026-09-28', '추석 대체', 'substitute'),
|
|
('2026-10-03', '개천절', 'public'),
|
|
('2026-10-05', '개천절 대체', 'substitute'),
|
|
('2026-10-09', '한글날', 'public'),
|
|
('2026-12-25', '성탄절', 'public')
|
|
ON CONFLICT (holiday_date) DO NOTHING;
|
|
|
|
-- ════════════════════════════════════════════════════════════
|
|
-- 5) 권한 (vacation_app: CRUD only)
|
|
-- ════════════════════════════════════════════════════════════
|
|
GRANT USAGE ON SCHEMA public TO vacation_app;
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON
|
|
vacation_requests, vacation_holidays, vacation_balances
|
|
TO vacation_app;
|
|
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO vacation_app;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO vacation_app;
|
|
ALTER DEFAULT PRIVILEGES IN SCHEMA public
|
|
GRANT USAGE, SELECT ON SEQUENCES TO vacation_app;
|
|
|
|
SELECT 'vacation_db ready' AS status;
|