erd_3

Tables (11)

coupon

  • coupon_id — bigint — Primary key — NOT NULL
  • name — varchar(255) — NOT NULL
  • open_at — datetime — NOT NULL — 쿠폰 발급 시작 일시
  • close_at — datetime — NOT NULL — 쿠폰 발급 종료 일시
  • status — varchar(20) — NOT NULL — SCHEDULED / OPEN / CLOSED
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

coupon_issue

  • coupon_issue_id — bigint — Primary key — NOT NULL
  • coupon_id — bigint — Foreign key → coupon.coupon_id — NOT NULL
  • member_id — bigint — Foreign key → member.member_id — NOT NULL
  • status — varchar(20) — NOT NULL — ISSUED, USED, EXPIRED
  • issued_at — datetime — NOT NULL
  • used_at — datetime
  • expires_at — datetime — NOT NULL — 발급 시점에 계산해 저장 (issued_at + 유효기간)
  • created_at — datetime — NOT NULL
  • updated_at — datetime — NOT NULL

coupon_issue_history

  • history_id — bigint — Primary key — NOT NULL
  • coupon_issue_id — bigint — Foreign key → coupon_issue.coupon_issue_id — NOT NULL
  • from_status — varchar(20) — NOT NULL — 전이 전 상태. 최초 발급 이력은 UNISSUED
  • to_status — varchar(20) — NOT NULL
  • changed_at — datetime — NOT NULL
  • idempotency_key — varchar(64) — NOT NULL — 클라이언트가 생성하는 상태변경 요청 멱등성 키

coupon_issue_run (쿠폰 발급 실행)

  • run_id — bigint — Primary key — NOT NULL
  • coupon_id — bigint — Foreign key → coupon.coupon_id — NOT NULL
  • requested_count — int — NOT NULL — 실행 시점에 입력한 동시 요청 수 (이벤트에는 저장되지 않는 파라미터)
  • issued_count — int — NOT NULL
  • failed_count — int — NOT NULL
  • status — varchar(20) — NOT NULL — PENDING, RUNNING, SUCCESS, FAILED
  • started_at — datetime — NOT NULL
  • finished_at — datetime
  • created_at — datetime — NOT NULL

coupon_stock

  • coupon_stock_id — bigint — Primary key — NOT NULL
  • coupon_id — bigint — Foreign key → coupon.coupon_id — NOT NULL
  • total_quantity — int — NOT NULL — 최초 발급 가능 수량
  • remaining_quantity — int — NOT NULL — coupon_issue INSERT와 같은 트랜잭션으로 갱신
  • updated_at — datetime — NOT NULL

issue_failure_log

  • failure_log_id — bigint — Primary key — NOT NULL
  • coupon_id — bigint — FK 제약 없음 - COUPON_NOT_FOUND 등 존재하지 않는 값도 기록 가능해야 함
  • member_id — bigint — FK 제약 없음 - 위와 동일한 이유
  • failure_reason — varchar(30) — NOT NULL — SOLD_OUT, DUPLICATE_ISSUE, NOT_OPEN_YET, COUPON_NOT_FOUND, INTERNAL_ERROR, SERVICE_UNAVAILABLE
  • occurred_at — datetime — NOT NULL

member

  • member_id — bigint — Primary key — NOT NULL
  • email — varchar(255) — NOT NULL
  • name — varchar(100) — NOT NULL
  • phone — varchar(20) — NOT NULL
  • created_at — datetime — NOT NULL

notification

  • notification_id — bigint — Primary key — NOT NULL
  • coupon_id — bigint — Foreign key → coupon.coupon_id
  • member_id — bigint — Foreign key → member.member_id
  • type — varchar(30) — NOT NULL — 예: OPEN_SOON
  • status — varchar(20) — NOT NULL — PENDING, SENT, FAILED
  • sent_at — datetime
  • created_at — datetime — NOT NULL

verification_rule_result

  • rule_result_id — bigint — Primary key — NOT NULL
  • run_id — bigint — Foreign key → verification_run.run_id — NOT NULL
  • rule_name — varchar(50) — NOT NULL — DUPLICATE_ISSUE, OVER_ISSUE, STOCK_MISMATCH, STATE_TIMESTAMP_MISMATCH, REDIS_DB_MISMATCH, TOOL_RELIABILITY
  • checked_count — bigint — NOT NULL — 해당 규칙으로 검사한 총 건수
  • violation_count — bigint — NOT NULL — 위반 검출 건수 (정상 케이스는 0)

verification_run

  • run_id — bigint — Primary key — NOT NULL
  • issue_run_id — bigint — Foreign key → coupon_issue_run.run_id — NOT NULL — 검증 대상 발급 실행. coupon_issue_run.run_id 참조
  • snapshot_at — datetime — NOT NULL — 검증 기준 시점(스냅샷 시각)
  • verdict — varchar(20) — NOT NULL — 전체 판정: PASS / FAIL
  • started_at — datetime — NOT NULL
  • finished_at — datetime
  • created_at — datetime — NOT NULL

verification_violation

  • violation_id — bigint — Primary key — NOT NULL
  • rule_result_id — bigint — Foreign key → verification_rule_result.rule_result_id — NOT NULL
  • target_type — varchar(30) — NOT NULL — COUPON, COUPON_ISSUE, COUPON_MEMBER_PAIR 등 위반 대상 종류
  • target_id — bigint — 위반 대상의 1차 식별자 (예: coupon_id, coupon_issue_id)
  • target_id2 — bigint — 쌍(pair) 위반의 2차 식별자 (예: member_id) - 단일 대상 위반이면 NULL
  • detail — varchar(500) — NOT NULL — 위반 상세 설명 (예: 재고 불일치 수치, 중복 발급 건수 등)
  • detected_at — datetime — NOT NULL