Kakao Gift Sending Program

Tables (8)

delivery_history (배송 이력)

  • id — BIGSERIAL — Primary key — NOT NULL
  • reservation_id — BITINT — Foreign key → reservations.id — NOT NULL
  • gift_code_id — BIGINT — Foreign key → gift_code_master.id — NOT NULL
  • attempt_number — INT — NOT NULL
  • delivery_channel — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • error_message — TEXT
  • external_id — VARCHAR(100)
  • delivered_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL

gift (선물)

  • id — BIGSERIAL — Primary key — NOT NULL
  • reservation_id — BIGINT — Foreign key → reservations.id — NOT NULL
  • gift_code_id — BIGINT — Foreign key → gift_code_master.id — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • used_at — TIMESTAMP
  • expires_at — TIMESTAMP — NOT NULL
  • is_transferred — BOOLEAN — NOT NULL
  • transferred_to — BIGINT
  • created_at — TIMESTAMP — NOT NULL

gift_code_master (기프티콘 번호 마스터)

  • id — BIGSERIAL — Primary key — NOT NULL
  • gift_item_master — BIGINT — Foreign key → gift_item_master.id — NOT NULL
  • code — VARCHAR(100) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • expires_at — TIMESTAMP — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • used_at — TIMESTAMP

gift_item_master (선물 상품 마스터)

  • id — BIGSERIAL — Primary key — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • category — VARCHAR(50)
  • price — DECIMAL(10, 2) — NOT NULL
  • expiry_days — INT — NOT NULL
  • description — TEXT
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL

notifications (알림)

  • id — BIGSERIAL — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • reservation_id — BIGINT — Foreign key → reservations.id — NOT NULL
  • delivery_history_id — BIGINT — Foreign key → delivery_history.id — NOT NULL
  • notification_type — VARCHAR(50) — NOT NULL
  • TRANSFER_COMPLETED — ) 'RESERVATION_CONFIRMED', 'DELIVERY_COMPLETE', 'EXPIRY_WARNING', 'CANCELLED', 'TRANSFER_REQUEST', 'TRANSFER_COMPLETED'
  • channel — VARCHAR(20) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • content — TEXT
  • scheduled_at — TIMESTAMP
  • sent_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL

payments (결제)

  • id — BIGSERIAL — Primary key — NOT NULL
  • reservation_id — BIGINT — NOT NULL
  • amount — DECIMAL(10, 2) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • payment_method — VARCHAR(50)
  • transaction_id — VARCHAR(100)
  • paid_at — TIMESTAMP
  • refunded_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL

reservations (발송 예약)

  • id — BIGSERIAL — Primary key — NOT NULL
  • sender_id — BIGINT — Foreign key → users.id — NOT NULL
  • gift_item_id — BIGINT — Foreign key → gift_item_master.id — NOT NULL
  • payment_id — BIGINT — Foreign key → payments.id — NOT NULL
  • recipient_phone — VARCHAR(20)
  • recipient_kakao_id — VARCHAR(100)
  • scheduled_time_utc — TIMESTAMP — NOT NULL
  • sender_timezone — VARCHAR(50) — NOT NULL
  • recipient_timezone — VARCHAR(50) — NOT NULL
  • message — TEXT
  • status — VARCHAR(20) — NOT NULL
  • idempotency_key — VARCHAR(100)
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

users (사용자)

  • id — BIGSERIAL — Primary key — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • kakao_id — VARCHAR(100)
  • phone — VARCHAR(20)
  • name — VARCHAR(100)
  • timezone — VARCHAR(50) — NOT NULL
  • is_deleted — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL