데이터베이스 마이그래이션

1. 개요

1. 데이터 마이그레이션 역할

• 로컬, 개발서버, 운영서버 등에서 데이터베이스의 정합성을 지킴
• 데이터베이스의 변경 이력을 기록 (보안 감사 대응)
• CI/CD 파이프라인 배포

2. V1 ~ V10 설명

• 사용자, 사용자 로그인, 로그인, 로그 관련 테이블 정리
• 파일 위치 : Spring Boot -> resource -> db -> migration

2. 목록

• V1__create_version_table
• V2__make_init_enums
• V3__create_user_table
• V4__add_unique_constraints_to_users
• V5__create_user_connect_sns
• V6__user_refresh_token
• V7__user_refresh_token_status_comment
• V8__user_access_token
• V9__create_user_login_history
• V10__create_user_api_access_log

3. 버전 내용

1. V1__create_version_table

• 버전 관리 테이블
• 최초에 실행되는 마이그레이션. 버전 관리 테이블을 만들고 향수 하용

CREATE TABLE IF NOT EXISTS app_version
(
    id SERIAL PRIMARY KEY,
    version_name VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. V2__make_init_enums

• 사용자 유형, 로그인 유형등의 열거형(ENUM) Type 을 정의

DO $$
BEGIN
  -- 사용자 유형
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_type_enum') THEN
CREATE TYPE user_type_enum AS ENUM ('User', 'Admin', 'SuperAdmin');
END IF;

  -- 로그인 유형
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'login_type_enum') THEN
CREATE TYPE login_type_enum AS ENUM ('Email', 'Kakao', 'Google', 'Apple', 'Naver');
END IF;

  -- 사용자 상태
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'user_state_enum') THEN
CREATE TYPE user_state_enum AS ENUM ('Active', 'Inactive', 'Suspended', 'Deleted');
END IF;

  -- 변경 주체
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'actor_enum') THEN
CREATE TYPE actor_enum AS ENUM ('SYSTEM', 'ADMIN', 'USER');
END IF;

  -- 성별
  IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'sex_enum') THEN
CREATE TYPE sex_enum AS ENUM ('Male', 'Female');
END IF;
END$$;

3. V3__create_user_table

• 사용자 테이블

CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,                            -- 자동 증가 PK
    user_uuid UUID NOT NULL UNIQUE,                      -- 사용자 고유 UUID
    user_type user_type_enum NOT NULL,                        -- User, Admin, SuperAdmin
    login_type login_type_enum NOT NULL,                 -- Email, Kakao, Google, Apple, Naver
    user_state user_state_enum NOT NULL DEFAULT 'Active',     -- Active, Inactive, Suspended, Deleted

    di_cipher BYTEA,                                     -- di 암호화 값
    di_nonce BYTEA,
    di_tag BYTEA,
    di_hash BYTEA,

    profile_image_url TEXT,
    name_cipher BYTEA,                                   -- 이름 암호화 값
    name_nonce BYTEA,
    name_tag BYTEA,
    name_chosung VARCHAR(20) NULL,

    nick_name VARCHAR(50) UNIQUE,
    sex sex_enum,                                        -- Male, Female
    birthday_cipher BYTEA,                               -- 생년월일 암호화 값
    birthday_nonce BYTEA,
    birthday_tag BYTEA,

    email_cipher BYTEA,
    email_nonce BYTEA,
    email_tag BYTEA,
    email_hash BYTEA,

    phone_cipher BYTEA,
    phone_nonce BYTEA,
    phone_tag BYTEA,
    phone_hash BYTEA,
    phone_last4_hash BYTEA,

    password_hash BYTEA,                                 -- 비밀번호 해시

    about_me TEXT,

    address_gu VARCHAR(200),
    address_dong_code VARCHAR(100),
    address_dong_name VARCHAR(200),
    address_zip VARCHAR(20),
    address_detail_cipher BYTEA,
    address_detail_nonce BYTEA,
    address_detail_tag BYTEA,
    address_longitude_cipher BYTEA,
    address_longitude_nonce BYTEA,
    address_longitude_tag BYTEA,
    address_latitude_cipher BYTEA,
    address_latitude_nonce BYTEA,
    address_latitude_tag BYTEA,

    timezone VARCHAR(50) DEFAULT 'Asia/Seoul',

    last_login_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP,
    last_password_changed_at TIMESTAMP,

    delete_reason TEXT,
    suspend_reason TEXT,
    data_retention_until TIMESTAMP
    );

4. V4__add_unique_constraints_to_users

• 사용자 테이블에 고유 키를 넣음 (중복 금지)
• 탈퇴 시 24시간 내에 재가입 방지함으로써 중복 입력을 금지 시킴
• 다른 이메일인데 본인인증을 다른 사람이 하면 본인인증을 막음.
• 1시간마다 스케쥴링을 돌려서 24시간이 지나면 delete_reason 를 옮기고 사용자를 물리적으로 삭제

-- 이메일 해시에 UNIQUE 제약 추가
ALTER TABLE users
    ADD CONSTRAINT uq_users_email_hash UNIQUE (email_hash);

-- 본인인증 DI 해시에 UNIQUE 제약 추가
ALTER TABLE users
    ADD CONSTRAINT uq_users_di_hash UNIQUE (di_hash);

5. V5__create_user_connect_sns

• User 와 SNS 로그인을 연결하는 테이블
• 각 SNS 고유 아이디 sub_id 는 암호화/해시 처리

CREATE TABLE IF NOT EXISTS user_connect_sns (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    sub_id_cipher BYTEA NOT NULL,
    sub_id_nonce  BYTEA NOT NULL,
    sub_id_tag    BYTEA NOT NULL,
    sub_id_hash   BYTEA UNIQUE NOT NULL,
    login_type login_type_enum NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);

6. V6__user_refresh_token

• 리프래쉬 토큰 관리 테이블

CREATE TYPE refresh_token_status_enum AS ENUM (
    'ACTIVE',
    'ROTATED',
    'REVOKED',
    'EXPIRED',
    'LOGGED_OUT',
    'COMPROMISED',
    'PENDING'
);

/* Refresh Token 상태 정의
   ACTIVE      : 정상 사용
   ROTATED     : 교체됨
   REVOKED     : 관리자/시스템 강제 만료
   EXPIRED     : 자동 만료
   LOGGED_OUT  : 사용자 로그아웃
   COMPROMISED : 보안사고
   PENDING     : 최초 발급 후 미사용
*/

CREATE TABLE IF NOT EXISTS user_refresh_token (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),

    client_id VARCHAR(100) NOT NULL,
    family_id UUID NOT NULL,

    refresh_token_hash CHAR(64) UNIQUE NOT NULL,

    issued_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at  TIMESTAMP NOT NULL,
    revoked_at  TIMESTAMP NULL,
    last_used_at TIMESTAMP NULL,

    ip_address  VARCHAR(45),
    user_agent  VARCHAR(255),
    device_info VARCHAR(255),

    status refresh_token_status_enum NOT NULL DEFAULT 'PENDING',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

7. V7__user_refresh_token_status_comment

• 리프래쉬 토큰의 상태값들을 코멘트에 넣는 구문

COMMENT ON COLUMN user_refresh_token.status IS 'PENDING=issued, ACTIVE=valid, ROTATED=replaced, REVOKED=forced revoke, EXPIRED=expired, LOGGED_OUT=user logout, COMPROMISED=security incident';

8. V8__user_access_token

• 사용자 엑세스 토큰 관리 테이블

CREATE TYPE access_token_status_enum AS ENUM (
    'ACTIVE',   -- 현재 유효
    'REVOKED',  -- 강제 만료 (관리자/시스템)
    'EXPIRED'   -- 만료됨
);

CREATE TABLE IF NOT EXISTS user_access_token (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),

    client_id VARCHAR(100) NOT NULL,
    family_id UUID NOT NULL,  -- refresh_token과 동일한 세션 그룹

    jti UUID NOT NULL UNIQUE,  -- JWT 고유 식별자
    access_token_hash CHAR(64) UNIQUE NOT NULL, -- SHA-256 해시 저장

    issued_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at  TIMESTAMP NOT NULL,
    revoked_at  TIMESTAMP NULL,
    last_used_at TIMESTAMP NULL,

    ip_address  VARCHAR(45),
    user_agent  VARCHAR(255),
    device_info VARCHAR(255),

    status access_token_status_enum NOT NULL DEFAULT 'ACTIVE',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

9. V9__create_user_login_history

• 사용자 로그인 이력 관리 테이블

CREATE TABLE IF NOT EXISTS user_login_history (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),

    sub_id_cipher BYTEA,      -- 외부 로그인 공급자의 고유 ID (암호화 저장)
    sub_id_nonce  BYTEA,
    sub_id_tag    BYTEA,
    sub_id_hash   BYTEA UNIQUE, -- 중복 방지 및 조회용 해시
-- EMAIL 로그인일 경우 sub_id_* 컬럼은 NULL 가능

    login_type login_type_enum NOT NULL, -- 로그인 방식

    client_id VARCHAR(100) NOT NULL,     -- 브라우저/디바이스 구분용
    user_agent VARCHAR(255) NOT NULL,    -- 접속 환경 문자열
    ip_address VARCHAR(45) NOT NULL,     -- IPv4/IPv6 대응

    success BOOLEAN NOT NULL,            -- 로그인 성공 여부

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP
);

10. V10__create_user_api_access_log

• 사용자의 api 요청 이력 관리 테이블

CREATE TYPE http_method_enum AS ENUM (
    'GET',     -- 리소스 조회
    'POST',    -- 리소스 생성 / 동작 요청
    'PUT',     -- 리소스 전체 교체
    'DELETE',  -- 리소스 삭제
    'PATCH',   -- 리소스 부분 수정
    'OPTIONS'  -- CORS Preflight 요청 (허용 메소드 확인), 해킹 공격 대응
);

CREATE TYPE api_reason_enum AS ENUM (
    'INVALID_TOKEN',   -- 잘못된 토큰
    'EXPIRED',         -- 만료됨
    'REVOKED',         -- 강제 만료
    'UNAUTHORIZED',    -- 토큰 없음
    'DENIED',          -- 권한 없음
    'MISSING_CLIENT_ID', -- 클라이언트 식별자 누락
    'OTHER'            -- 기타
);

CREATE TABLE IF NOT EXISTS user_api_access_log (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),

    api_endpoint VARCHAR(255) NOT NULL,   -- 요청한 API
    http_method http_method_enum NOT NULL,     -- HTTP 메소드 (GET, POST...)

    device_id VARCHAR(100) NOT NULL,      -- 디바이스 아이디 (없으면 생성해서라도 넣음)
    client_id VARCHAR(100) NOT NULL,      -- 클라이언트 식별자 (X-Client-Id)
    ip_address VARCHAR(45) NOT NULL,      -- IPv4/IPv6
    user_agent VARCHAR(255) NOT NULL,     -- 클라이언트 환경 문자열

    status_code INT NOT NULL,             -- HTTP 응답 코드
    success BOOLEAN NOT NULL,             -- 성공 여부

    reason_code api_reason_enum,          -- 실패 사유
    reason_text VARCHAR(255),             -- reason_code=OTHER일 경우 설명

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);