SWYP Full Feature ERD

Tables (18)

badge_definitions

  • code — VARCHAR(30) — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • image_key — VARCHAR(512)
  • criterion_type — VARCHAR(30) — NOT NULL
  • threshold — INT — NOT NULL
  • display_order — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL

categories

  • id — BIGINT — Primary key — NOT NULL
  • code — VARCHAR(30) — NOT NULL
  • name — VARCHAR(30) — NOT NULL
  • display_order — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL

comment_images

  • id — BIGINT — Primary key — NOT NULL
  • comment_id — BIGINT — Foreign key → comments.id — NOT NULL
  • storage_key — VARCHAR(512) — NOT NULL
  • display_order — TINYINT — NOT NULL
  • created_at — DATETIME — NOT NULL

comment_picks

  • post_id — BIGINT — Foreign key → posts.id — NOT NULL
  • comment_id — BIGINT — Foreign key → comments.id — NOT NULL
  • picker_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • selected_at — DATETIME — NOT NULL

comments

  • id — BIGINT — Primary key — NOT NULL
  • post_id — BIGINT — Foreign key → posts.id — NOT NULL
  • author_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • content — VARCHAR(1000) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

guest_devices

  • id — BIGINT — Primary key — NOT NULL
  • device_hash — CHAR(64) — NOT NULL
  • first_seen_at — DATETIME — NOT NULL
  • last_seen_at — DATETIME — NOT NULL

level_definitions

  • level_no — TINYINT — Primary key — NOT NULL
  • name — VARCHAR(30) — NOT NULL
  • image_key — VARCHAR(512)
  • min_points — INT — NOT NULL
  • min_vote_count — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL

point_ledger

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • event_type — VARCHAR(30) — NOT NULL
  • reference_type — VARCHAR(30) — NOT NULL
  • reference_id — BIGINT — NOT NULL
  • points — INT — NOT NULL
  • created_at — DATETIME — NOT NULL

post_images

  • id — BIGINT — Primary key — NOT NULL
  • post_item_id — BIGINT — Foreign key → post_items.id — NOT NULL
  • storage_key — VARCHAR(512) — NOT NULL
  • display_order — TINYINT — NOT NULL
  • created_at — DATETIME — NOT NULL

post_items

  • id — BIGINT — Primary key — NOT NULL
  • post_id — BIGINT — Foreign key → posts.id — NOT NULL
  • item_role — VARCHAR(10) — NOT NULL
  • position — TINYINT — NOT NULL
  • product_name — VARCHAR(30) — NOT NULL
  • price_amount — DECIMAL(12,0)
  • source_url — VARCHAR(2048)

post_results

  • post_id — BIGINT — Foreign key → posts.id — NOT NULL
  • winning_option_id — BIGINT — Foreign key → post_vote_options.id — NOT NULL
  • total_vote_count — INT — NOT NULL
  • is_tie — BOOLEAN — NOT NULL
  • finalized_at — DATETIME — NOT NULL

post_vote_options

  • id — BIGINT — Primary key — NOT NULL
  • post_id — BIGINT — Foreign key → posts.id — NOT NULL
  • post_item_id — BIGINT — Foreign key → post_items.id
  • option_code — VARCHAR(10) — NOT NULL
  • option_label — VARCHAR(30) — NOT NULL
  • display_order — TINYINT — NOT NULL

post_votes

  • id — BIGINT — Primary key — NOT NULL
  • post_id — BIGINT — Foreign key → posts.id — NOT NULL
  • option_id — BIGINT — Foreign key → post_vote_options.id — NOT NULL
  • voter_user_id — BIGINT — Foreign key → users.id
  • guest_device_id — BIGINT — Foreign key → guest_devices.id
  • voted_at — DATETIME — NOT NULL

posts

  • id — BIGINT — Primary key — NOT NULL
  • author_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • category_id — BIGINT — Foreign key → categories.id — NOT NULL
  • post_type — VARCHAR(20) — NOT NULL
  • subject — VARCHAR(30)
  • description — VARCHAR(300)
  • status — VARCHAR(20) — NOT NULL
  • closes_at — DATETIME
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL
  • deleted_at — DATETIME

user_badges

  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • badge_code — VARCHAR(30) — Foreign key → badge_definitions.code — NOT NULL
  • earned_at — DATETIME — NOT NULL

user_profiles

  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • nickname — VARCHAR(5) — NOT NULL
  • profile_image_key — VARCHAR(512)
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL

user_refresh_token

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • token_hash — CHAR(64) — NOT NULL
  • expires_at — DATETIME — NOT NULL
  • created_at — DATETIME — NOT NULL

users

  • id — BIGINT — Primary key — NOT NULL
  • provider — VARCHAR(20) — NOT NULL
  • provider_id — VARCHAR(255) — NOT NULL
  • email — VARCHAR(255)
  • name — VARCHAR(100)
  • role — VARCHAR(20) — NOT NULL
  • state — VARCHAR(20) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • updated_at — DATETIME — NOT NULL