-- ## iitp DB Schemas - Initial setup - Creation and Delete if tables exists -- ## ver 0.0.5 last updated data : 2025.08.18 -- ## Only for PostgreSQL -- ## Open API Admin 관리용 Admin 서비스 데이터용 테이블 생성 스크립트 -- ## -- ################################################ -- ## ADMIN 시스템 테이블 생성 -- ################################################ -- #### IITP Admin Account(user) 정보 테이블 #### -- # public.sys_adm_account definition DROP TABLE IF EXISTS public.sys_adm_account; CREATE TABLE public.sys_adm_account ( adm_id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL, -- system id, 고유 식별자 (자동 증가) login_id VARCHAR(128) NOT NULL, -- admin login id password char(60) NOT NULL, -- admin login password (bcrypt Hassing) name VARCHAR(90) NOT NULL, -- admin name roles varchar(8) NOT NULL, -- admin role, "sys_admin_roles" comm code 참조 status char(1) DEFAULT 'A' NOT NULL, -- 데이터 상태, "data_status" comm code 참조 del_yn char(1) DEFAULT 'N'::bpchar NOT NULL, -- 삭제여부 (Y: 삭제) latest_login_at timestamptz, -- latest login time affiliation VARCHAR(60), -- admin 소속 description VARCHAR(600), -- admin 설명 note VARCHAR(600), -- 비고 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 레코드 수정 시각 deleted_at timestamptz NULL, -- 삭제 일시 (논리 삭제 시 기록) created_by varchar(40) NOT NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 updated_by varchar(40) NULL, -- 데이터 수정자 deleted_by varchar(40) NULL, -- 데이터 삭제자 CONSTRAINT pkey_sys_adm_account PRIMARY KEY (adm_id) ); CREATE UNIQUE INDEX uidx_sys_adm_account_login ON public.sys_adm_account USING btree (login_id); CREATE INDEX idx_sys_adm_account_name ON public.sys_adm_account USING btree (name); CREATE INDEX idx_sys_adm_account_role_status ON public.sys_adm_account USING btree (roles, status); COMMENT ON TABLE public.sys_adm_account IS 'IITP Admin Account(user) 정보 테이블'; COMMENT ON COLUMN public.sys_adm_account.adm_id IS ' system id, 고유 식별자 (자동 증가)'; COMMENT ON COLUMN public.sys_adm_account.login_id IS 'admin login id'; COMMENT ON COLUMN public.sys_adm_account.password IS 'admin login password (bcrypt Hassing), 초기 비번(12345!!!)'; COMMENT ON COLUMN public.sys_adm_account.name IS 'admin name'; COMMENT ON COLUMN public.sys_adm_account.roles IS 'admin role, "sys_admin_roles" comm code 참조'; COMMENT ON COLUMN public.sys_adm_account.status IS '"data_status" comm code 참조'; COMMENT ON COLUMN public.sys_adm_account.del_yn IS '삭제여부 (Y: 삭제)'; COMMENT ON COLUMN public.sys_adm_account.affiliation IS 'admin 소속'; COMMENT ON COLUMN public.sys_adm_account.description IS 'admin 설명'; COMMENT ON COLUMN public.sys_adm_account.note IS '비고'; COMMENT ON COLUMN public.sys_adm_account.latest_login_at IS '마지막 로그인 시각'; COMMENT ON COLUMN public.sys_adm_account.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN public.sys_adm_account.updated_at IS '레코드 수정 시각'; COMMENT ON COLUMN public.sys_adm_account.deleted_at IS '삭제 일시 (논리 삭제 시 기록)'; COMMENT ON COLUMN public.sys_adm_account.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조'; COMMENT ON COLUMN public.sys_adm_account.updated_by IS '데이터 수정자'; COMMENT ON COLUMN public.sys_adm_account.deleted_by IS '데이터 삭제자'; -- #### IITP 공지사항 게시 정보 테이블 #### -- # public.sys_notice definition DROP TABLE IF EXISTS public.sys_notice; CREATE TABLE sys_notice ( notice_id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL, -- 공지사항 고유 ID title VARCHAR(300) NOT NULL, -- 공지 제목 content VARCHAR(6000) NOT NULL, -- 공지 본문 내용 notice_type CHAR(1) NOT null DEFAULT 'G' CHECK (notice_type IN ('G', 'S', 'E')), -- 공지 유형 (예: G(general), S(system), E(event)) pinned_yn CHAR(1) DEFAULT 'N', -- 상단 고정 여부 (Y 시 우선 노출) public_yn CHAR(1) DEFAULT 'Y', -- 공개 여부 (N이면 비공개 또는 관리자 전용) posted_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 게시 일시 start_dt DATE, -- 공지 시작일 (노출 시작일) end_dt DATE, -- 공지 종료일 (노출 종료일) created_by VARCHAR(100), -- 작성자 updated_by VARCHAR(100), -- 수정자 created_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 생성 일시 updated_at timestamptz, -- 수정 일시 CONSTRAINT pkey_sys_notice PRIMARY KEY (notice_id) ); CREATE INDEX idx_sys_notice_type_dt ON public.sys_notice USING btree (notice_type, start_dt,end_dt); COMMENT ON TABLE sys_notice IS '공지사항 게시 정보 테이블'; COMMENT ON COLUMN sys_notice.notice_id IS '공지사항 고유 식별자'; COMMENT ON COLUMN sys_notice.title IS '공지 제목'; COMMENT ON COLUMN sys_notice.content IS '공지 본문 텍스트'; COMMENT ON COLUMN sys_notice.notice_type IS '공지 유형 (예: G(general), S(system), E(event))'; COMMENT ON COLUMN sys_notice.pinned_yn IS '공지 상단 고정 여부 (Y 시 우선 노출)'; COMMENT ON COLUMN sys_notice.public_yn IS '공개 여부 (N이면 비공개 또는 관리자 전용)'; COMMENT ON COLUMN sys_notice.posted_at IS '공지 게시 일시'; COMMENT ON COLUMN sys_notice.start_dt IS '공지 노출 시작일'; COMMENT ON COLUMN sys_notice.end_dt IS '공지 노출 종료일'; COMMENT ON COLUMN sys_notice.created_by IS '공지 작성자 ID 또는 이름'; COMMENT ON COLUMN sys_notice.updated_by IS '공지 수정자 ID 또는 이름'; COMMENT ON COLUMN sys_notice.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN sys_notice.updated_at IS '레코드 수정 시각'; -- #### IITP 자주 묻는 질문(FAQ) 정보 테이블 #### -- # public.sys_faq definition DROP TABLE IF EXISTS public.sys_faq; CREATE TABLE public.sys_faq ( faq_id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL, -- system id, 고유 식별자 (자동 증가) faq_type VARCHAR(12) NOT NULL, -- FAQ 유형, "faq_type" comm code 참조 question VARCHAR(600) NOT NULL, -- 질문 answer VARCHAR(3000) NOT NULL, -- 답변 sort_order INT DEFAULT 0, -- 정렬 우선순위 hit_cnt INT DEFAULT 0, -- 조회수 use_yn CHAR(1) DEFAULT 'Y', -- 사용 여부 (Y/N) created_at timestamptz DEFAULT CURRENT_TIMESTAMP, updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, created_by varchar(40), updated_by varchar(40), CONSTRAINT pkey_sys_faq PRIMARY KEY (faq_id) ); CREATE INDEX idx_sys_faq_type_sort ON public.sys_faq USING btree (faq_type, sort_order,use_yn); COMMENT ON TABLE public.sys_faq IS '자주 묻는 질문(FAQ) 정보 테이블'; COMMENT ON COLUMN public.sys_faq.faq_id IS 'FAQ 고유 ID'; COMMENT ON COLUMN public.sys_faq.faq_type IS 'FAQ 유형, "faq_type" comm code 참조'; COMMENT ON COLUMN public.sys_faq.question IS 'FAQ 질문'; COMMENT ON COLUMN public.sys_faq.answer IS 'FAQ 답변'; COMMENT ON COLUMN public.sys_faq.sort_order IS '정렬 순서'; COMMENT ON COLUMN public.sys_faq.hit_cnt IS 'FAQ 조회수'; COMMENT ON COLUMN public.sys_faq.use_yn IS '사용 여부 (Y/N))'; COMMENT ON COLUMN public.sys_faq.created_by IS '등록자 ID 또는 이름'; COMMENT ON COLUMN public.sys_faq.updated_by IS '수정자 ID 또는 이름'; COMMENT ON COLUMN public.sys_faq.created_at IS '등록 일시'; COMMENT ON COLUMN public.sys_faq.updated_at IS '수정 일시'; -- #### IITP 일반 사용자 QnA 테이블 (질문과 답변 포함) #### -- # public.sys_qna definition DROP TABLE IF EXISTS public.sys_qna; CREATE TABLE public.sys_qna ( qna_id BIGSERIAL NOT NULL, -- 질문 고유 식별자 user_id int4 NOT NULL, -- 작성자 system ID qna_type VARCHAR(32) NOT NULL, -- 질문 유형, "qna_type" comm code 참조 title VARCHAR(600) NOT NULL, -- 질문 제목 content VARCHAR(6000) NOT NULL, -- 질문 내용 secret_yn CHAR(1) DEFAULT 'N', -- 비공개 여부 (Y: 비공개, N: 공개) writer_name varchar(90), -- 작성자 이름 (선택 입력) answer_content VARCHAR(6000), -- 답변 내용 answered_by varchar(40), -- 답변자 ID 또는 이름 answered_yn CHAR(1) DEFAULT 'N', -- 답변 공개(완료) 여부 (Y: 공개(완료), N: 비공개(미답변)) hit_cnt INT DEFAULT 0, -- 조회수 del_yn char(1) DEFAULT 'N'::bpchar NOT NULL, -- 삭제여부 (Y: 삭제) answered_at timestamptz, -- 답변 일시 created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 질문 등록일시 updated_at timestamptz DEFAULT CURRENT_TIMESTAMP, -- 마지막 수정일시 deleted_at timestamptz, -- 삭제 일시 created_by varchar(40), -- 생성자 updated_by varchar(40), -- 수정자 deleted_by varchar(40), -- 삭제자 CONSTRAINT pkey_sys_qna PRIMARY KEY (qna_id) ); CREATE INDEX idx_sys_qna_type_screct ON public.sys_qna USING btree (qna_type, secret_yn); CREATE INDEX idx_sys_qna_type_sort ON public.sys_qna USING btree (hit_cnt, created_at); COMMENT ON TABLE public.sys_qna IS '일반 사용자 QnA 테이블 (질문과 답변 포함)'; COMMENT ON COLUMN public.sys_qna.qna_id IS 'QnA 고유 식별자 (Primary Key)'; COMMENT ON COLUMN public.sys_qna.user_id IS '작성자 system ID '; COMMENT ON COLUMN public.sys_qna.qna_type IS 'QnA 질문 유형, "qna_type" comm code 참조'; COMMENT ON COLUMN public.sys_qna.title IS '질문 제목'; COMMENT ON COLUMN public.sys_qna.content IS '질문 본문 내용'; COMMENT ON COLUMN public.sys_qna.secret_yn IS '비공개 여부 (Y: 비공개, N: 공개)'; COMMENT ON COLUMN public.sys_qna.writer_name IS '작성자 이름 (옵션)'; COMMENT ON COLUMN public.sys_qna.answer_content IS '답변 내용'; COMMENT ON COLUMN public.sys_qna.answered_by IS '답변 작성자 ID 또는 이름'; COMMENT ON COLUMN public.sys_qna.answered_yn IS '답변 공개(완료) 여부 (Y: 공개(완료), N: 비공개(미답변))'; COMMENT ON COLUMN public.sys_qna.hit_cnt IS 'QnA 조회수'; COMMENT ON COLUMN public.sys_qna.del_yn IS '삭제여부 (Y: 삭제)'; COMMENT ON COLUMN public.sys_qna.answered_at IS '답변 등록 일시'; COMMENT ON COLUMN public.sys_qna.created_at IS '질문 등록 일시'; COMMENT ON COLUMN public.sys_qna.updated_at IS '질문 수정 일시'; COMMENT ON COLUMN public.sys_qna.deleted_at IS '삭제 일시 (soft delete)'; COMMENT ON COLUMN public.sys_qna.created_by IS '생성자 ID'; COMMENT ON COLUMN public.sys_qna.updated_by IS '수정자 ID'; COMMENT ON COLUMN public.sys_qna.deleted_by IS '삭제자 ID'; -- ################################################ -- ## 시스템 로그 기록 -- ################################################ -- #### IITP 사용자 access(login/logout) 이력 테이블 #### -- # public.sys_log_user_access definition DROP TABLE IF EXISTS public.sys_log_user_access; CREATE TABLE public.sys_log_user_access ( log_id BIGSERIAL NOT NULL, -- 로그 고유 ID user_id int4 NOT NULL, -- 사용자 ID user_type CHAR(1) NOT NULL CHECK (user_type IN ('U', 'A')), -- 사용자 유형 log_type VARCHAR(16) NOT NULL CHECK (log_type IN ('LOGIN', 'LOGOUT', 'LOGOUT-T-EXP')), -- 수행한 액션 종류 (예: LOGIN, LOGOUT, LOGOUT-T-EXP) act_result CHAR(1) NOT NULL CHECK (act_result IN ('S', 'F')), -- 액션 결과 ( S, F) err_code VARCHAR(10), -- 실패 시 에러 코드 err_msg VARCHAR(200), -- 실패 시 에러 메시지 ip_addr VARCHAR(50), -- 요청 IP 주소 user_agent VARCHAR(512), -- 사용자 디바이스 정보 (브라우저 등) access_tm timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, -- access 시각 created_at timestamptz DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pkey_sys_log_user_access PRIMARY KEY (log_id) ); CREATE INDEX idx_sys_log_user_access_user_type ON public.sys_log_user_access USING btree (user_id, user_type,log_type); COMMENT ON TABLE public.sys_log_user_access IS '사용자 access(login/logout) 이력 테이블'; COMMENT ON COLUMN public.sys_log_user_access.log_id IS '로그 고유 ID'; COMMENT ON COLUMN public.sys_log_user_access.user_id IS '사용자 ID'; COMMENT ON COLUMN public.sys_log_user_access.user_type IS '사용자 유형(U:User, A:Admin)'; COMMENT ON COLUMN public.sys_log_user_access.log_type IS '수행한 액션 (예: LOGIN, LOGOUT,LOGOUT-T-EXP)'; COMMENT ON COLUMN public.sys_log_user_access.act_result IS '성공 여부( S:성공, F:실패)'; COMMENT ON COLUMN public.sys_log_user_access.err_code IS '에러 발생 시 코드'; COMMENT ON COLUMN public.sys_log_user_access.err_msg IS '에러 발생 시 상세 메시지'; COMMENT ON COLUMN public.sys_log_user_access.ip_addr IS '요청을 보낸 클라이언트 IP'; COMMENT ON COLUMN public.sys_log_user_access.user_agent IS '사용자 디바이스 정보 (브라우저 등)'; COMMENT ON COLUMN public.sys_log_user_access.access_tm IS 'access 시각'; COMMENT ON COLUMN public.sys_log_user_access.created_at IS '등록 일시'; -- #### IITP 어드민/사용자 데이터 변경 이력 테이블 #### -- # public.sys_log_change_his definition DROP TABLE IF EXISTS public.sys_log_change_his; CREATE TABLE sys_log_change_his ( log_id BIGSERIAL PRIMARY KEY, actor_type CHAR(1) NOT NULL CHECK (actor_type IN ('U', 'A')), -- 사용자 유형 actor_id BIGINT NOT NULL, -- user_id or admin_id depending on actor_type action_type VARCHAR(36) NOT NULL, -- 변경 타입 (예: 'DEL_ACCOUNT', 'EDIT_ACCOUNT') target_type VARCHAR(64), -- 변경 대상 타입(예: 'USER_PROFILE', 'ADMIN_MENU', 'ROLE', etc.) target_id BIGINT, -- 대상 ID act_result CHAR(1) NOT NULL CHECK (act_result IN ('S', 'F')), -- 액션 결과 ( S, F) chg_summary JSONB, -- 주요 변경 내용 요약, 예: {"bf": {...}, "af: {...}} err_code VARCHAR(10), -- 실패 시 에러 코드 err_msg VARCHAR(200), -- 실패 시 에러 메시지 ip_addr VARCHAR(50), -- 요청 IP 주소 user_agent VARCHAR(512), -- 사용자 디바이스 정보 (브라우저 등) act_tm timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, -- action 시각 created_at timestamptz DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX idx_sys_log_change_his_act_type_id ON public.sys_log_change_his USING btree (actor_type, actor_id, action_type); CREATE INDEX idx_sys_log_change_his_tgt_type_id ON public.sys_log_change_his USING btree (target_type, target_id); COMMENT ON TABLE public.sys_log_change_his IS '어드민/사용자 데이터 변경 이력 테이블'; COMMENT ON COLUMN public.sys_log_change_his.log_id IS '로그 고유 ID'; COMMENT ON COLUMN public.sys_log_change_his.actor_type IS '사사용자 유형(U:User, A:Admin)'; COMMENT ON COLUMN public.sys_log_change_his.actor_id IS 'user_id or admin_id depending on actor_type'; COMMENT ON COLUMN public.sys_log_change_his.action_type IS '변경 타입 (예: DEL_ACCOUNT, EDIT_ACCOUNT)'; COMMENT ON COLUMN public.sys_log_change_his.target_type IS '변경 대상 타입 (예: USR_PROFILE, ADM_MENU, ROLE, etc.)'; COMMENT ON COLUMN public.sys_log_change_his.target_id IS '대상 ID'; COMMENT ON COLUMN public.sys_log_change_his.act_result IS '성공 여부( S:성공, F:실패)'; COMMENT ON COLUMN public.sys_log_change_his.chg_summary IS '주요 변경 내용 요약, 예: {"bf": {...}, "af: {...}}'; COMMENT ON COLUMN public.sys_log_change_his.err_code IS '에러 발생 시 코드'; COMMENT ON COLUMN public.sys_log_change_his.err_msg IS '에러 발생 시 상세 메시지'; COMMENT ON COLUMN public.sys_log_change_his.ip_addr IS '요청을 보낸 클라이언트 IP'; COMMENT ON COLUMN public.sys_log_change_his.user_agent IS '사용자 디바이스 정보 (브라우저 등)'; COMMENT ON COLUMN public.sys_log_change_his.act_tm IS 'action 시각'; COMMENT ON COLUMN public.sys_log_change_his.created_at IS '등록 일시'; -- ################################################ -- ## 통계 데이터 수집 기록 -- ################################################ -- #### KOSIS 통계 데이터 수집 기록 #### -- # public.sys_ext_api_info definition -- Drop table if table exists CREATE TABLE public.sys_log_stats_intg_his ( log_id int8 GENERATED BY DEFAULT AS identity intg_tbl_id VARCHAR(50) NOT NULL, -- 데이터 동합할 내부 db table id stat_tbl_id varchar(40) NOT NULL, -- 원데이터 통계 id action_type varchar(24) NOT NULL, -- action type "log_his_stat_act_type" comm code 참조 ret_yn char(1) NOT NULL, -- action 실패 여부 err_code char(6), -- 실패시 에러 코드 err_msg varchar(300), -- 실패시 에러 메시지 created_at timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL, -- 레코드 생성 시각 created_by varchar(40) NULL, -- 데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조 CONSTRAINT pkey_log_his_stats_intg PRIMARY KEY (log_id) }; COMMENT ON COLUMN public.log_his_stats_intg.created_at IS '레코드 생성 시각'; COMMENT ON COLUMN public.log_his_stats_intg.created_by IS '데이터 생성자 (SYS-BACH, SYS-MANUAL, BY-USER, admin name), "sys_work_type" comm code 참조';