sw

Tables (21)

ad_targets

  • id — BIGINT
  • advertiser_id — BIGINT — Foreign key → advertisers.id — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • branch_name — VARCHAR(200)
  • address — TEXT
  • dianping_registered — BOOLEAN — NOT NULL
  • gaode_registered — BOOLEAN — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

admin_roles

  • id — BIGINT
  • code — VARCHAR(50) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • sort_order — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

admins

  • id — BIGINT
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • role_id — BIGINT — Foreign key → admin_roles.id
  • status — user_status — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

advertisers

  • id — BIGINT
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • status — user_status — NOT NULL
  • business_name — VARCHAR(200)
  • business_number — VARCHAR(20)
  • grade_code_id — BIGINT
  • sales_route_code_id — BIGINT
  • customer_type_code_id — BIGINT
  • channel_type_code_id — BIGINT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

attach_file

  • id — BIGINT
  • target_type — VARCHAR(30) — NOT NULL
  • target_id — BIGINT — NOT NULL
  • file_url — VARCHAR(500) — NOT NULL
  • file_type — VARCHAR(30)
  • category — VARCHAR(30)
  • sort_order — INT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

campaign_applications

  • id — BIGINT
  • campaign_id — BIGINT — Foreign key → campaigns.id — NOT NULL
  • influencer_id — BIGINT — Foreign key → influencers.id — NOT NULL
  • ad_target_id — BIGINT
  • unique_code — VARCHAR(50)
  • status — application_status — NOT NULL
  • video_required — BOOLEAN — NOT NULL
  • guideline_confirmed — BOOLEAN — NOT NULL
  • staff_wechat_added — BOOLEAN — NOT NULL
  • notes — TEXT
  • applied_at — TIMESTAMPTZ — NOT NULL
  • admin_selected_at — TIMESTAMPTZ
  • proposed_at — TIMESTAMPTZ
  • approved_at — TIMESTAMPTZ
  • rejected_at — TIMESTAMPTZ
  • rejection_reason — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

campaign_briefs

  • id — BIGINT
  • campaign_id — BIGINT — Foreign key → campaigns.id — NOT NULL
  • title — VARCHAR(300)
  • status — brief_status — NOT NULL
  • version — INT — NOT NULL
  • generated_at — TIMESTAMPTZ
  • requested_by — BIGINT — Foreign key → admins.id
  • notes — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

campaign_expenditures

  • id — BIGINT
  • campaign_id — BIGINT — Foreign key → campaigns.id — NOT NULL
  • manager_id — BIGINT — Foreign key → admins.id
  • item_type_code_id — BIGINT — NOT NULL
  • item_detail — TEXT
  • amount — DECIMAL(15,2) — NOT NULL
  • expense_date — DATE
  • expense_month — VARCHAR(7)
  • notes — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL

campaign_products

  • id — BIGINT
  • campaign_id — BIGINT — Foreign key → campaigns.id — NOT NULL
  • product_name — VARCHAR(200)
  • product_url — VARCHAR(500)
  • provision_detail — TEXT
  • description — TEXT
  • service — TEXT
  • features — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL

campaign_revenues

  • id — BIGINT
  • campaign_id — BIGINT — Foreign key → campaigns.id — NOT NULL
  • expected_payment_date — DATE
  • expected_payment_month — VARCHAR(7)
  • expected_amount — DECIMAL(15,2)
  • confirmed_payment_date — DATE
  • confirmed_payment_month — VARCHAR(7)
  • confirmed_amount — DECIMAL(15,2)
  • quantity — INT
  • unit_price — DECIMAL(15,2)
  • duration_days — INT
  • expected_duration_days — INT
  • avg_duration_per_case — INT
  • refund_status — BOOLEAN — NOT NULL
  • refund_amount — DECIMAL(15,2)
  • refund_date — DATE
  • renewal_status — renewal_status — NOT NULL
  • renewal_date — DATE
  • renewal_amount — DECIMAL(15,2)
  • renewal_reason — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

campaign_reward_plans

  • id — BIGINT
  • campaign_id — BIGINT — Foreign key → campaigns.id — NOT NULL
  • item_name — VARCHAR(200) — NOT NULL
  • reward_type_code_id — BIGINT
  • description — TEXT
  • payout_method_code_id — BIGINT
  • payout_amount — DECIMAL(15,2)
  • payout_currency — VARCHAR(3)
  • settlement_amount — DECIMAL(15,2)
  • settlement_currency — VARCHAR(3)
  • exchange_rate — DECIMAL(15,6)
  • effective_start_date — DATE
  • effective_end_date — DATE
  • status — reward_plan_status — NOT NULL
  • is_additional — BOOLEAN — NOT NULL
  • added_reason — TEXT
  • sort_order — INT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

campaign_sns_platforms

  • sns_platform_code_id — BIGINT — Primary key — NOT NULL
  • campaign_id — BIGINT — Foreign key → campaigns.id — NOT NULL

campaigns

  • id — BIGINT
  • group_id — UUID — NOT NULL
  • lead_id — VARCHAR(50)
  • advertiser_id — BIGINT — Foreign key → advertisers.id — NOT NULL
  • country_code_id — BIGINT — NOT NULL
  • ad_target_id — BIGINT
  • ad_target_name — VARCHAR(200)
  • manager_id — BIGINT — Foreign key → admins.id
  • sales_manager_id — BIGINT — Foreign key → admins.id
  • title — VARCHAR(300) — NOT NULL
  • type — campaign_type — NOT NULL
  • service_type_code_id — BIGINT
  • progress_type_code_id — BIGINT
  • progress_method_code_id — BIGINT
  • status — campaign_status — NOT NULL
  • kpi_count — INT — NOT NULL
  • follower_min — INT
  • follower_max — INT
  • content_review_required — BOOLEAN — NOT NULL
  • content_type — content_type
  • brief_content — TEXT
  • shoot_notes — TEXT
  • required_missions — TEXT
  • optional_missions — TEXT
  • hashtags_required — JSONB
  • hashtags_optional — JSONB
  • reference_links — TEXT
  • timeline — TEXT
  • expected_upload_month — VARCHAR(7)
  • launch_date — DATE
  • completion_expected_date — DATE
  • target_end_date — DATE
  • actual_end_date — DATE
  • campaign_end_date — DATE
  • summary_expected_date — DATE
  • expected_duration_days — INT
  • is_summarized — BOOLEAN — NOT NULL
  • notes — TEXT
  • name_en — VARCHAR(200)
  • name_cn — VARCHAR(200)
  • region — VARCHAR(100)
  • address_en — TEXT
  • phone — VARCHAR(30)
  • latitude — DECIMAL(10,7)
  • longitude — DECIMAL(10,7)
  • industry_code_id — BIGINT
  • business_hours — TEXT
  • max_visitors_per_session — INT
  • max_visits_per_day — INT
  • visit_start_date — DATE
  • visit_available_times — TEXT
  • waiting_required — BOOLEAN — NOT NULL
  • has_cn_menu — BOOLEAN — NOT NULL
  • celebrity_visits — TEXT
  • sponsorship_scope — TEXT
  • appeal_points — TEXT
  • dianping_store_url — VARCHAR(500)
  • naver_place_url — VARCHAR(500)
  • other_requests — TEXT
  • created_at — TIMESTAMPTZ — NOT NULL
  • … +1

common_codes

  • id — BIGINT
  • group_code — VARCHAR(50) — NOT NULL
  • code — VARCHAR(50) — NOT NULL
  • label_ko — VARCHAR(100) — NOT NULL
  • label_en — VARCHAR(100)
  • sort_order — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • extra — JSONB
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

communication_memos

  • id — BIGINT
  • author_id — BIGINT — Foreign key → admins.id — NOT NULL
  • target_type — VARCHAR(30) — NOT NULL
  • target_id — BIGINT — NOT NULL
  • channel_code_id — BIGINT
  • content — TEXT — NOT NULL
  • is_pinned — BOOLEAN — NOT NULL
  • contacted_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

deliveries

  • id — BIGINT
  • application_id — BIGINT — Foreign key → campaign_applications.id — NOT NULL
  • campaign_product_id — BIGINT — Foreign key → campaign_products.id
  • recipient_name — VARCHAR(100)
  • recipient_phone — VARCHAR(30)
  • address — TEXT
  • postal_code — VARCHAR(10)
  • product_option — VARCHAR(200)
  • courier — VARCHAR(100)
  • tracking_number — VARCHAR(100)
  • status — delivery_status — NOT NULL
  • shipped_at — DATE
  • delivered_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

influencer_sns_accounts

  • id — BIGINT
  • influencer_id — BIGINT — Foreign key → influencers.id — NOT NULL
  • sns_platform_code_id — BIGINT — NOT NULL
  • account_id — VARCHAR(200)
  • account_name — VARCHAR(200)
  • account_url — VARCHAR(500)
  • follower_count — INT
  • total_likes_saves — INT
  • price_reported_image — DECIMAL(15,2)
  • price_reported_video — DECIMAL(15,2)
  • price_reported_complete_fee — DECIMAL(15,2)
  • price_unreported_image — DECIMAL(15,2)
  • price_unreported_video — DECIMAL(15,2)
  • last_synced_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

influencers

  • id — BIGINT
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • status — user_status — NOT NULL
  • nickname — VARCHAR(100)
  • gender — gender_type
  • wechat_id — VARCHAR(100)
  • wechat_added — BOOLEAN — NOT NULL
  • community_joined — BOOLEAN — NOT NULL
  • is_quality_verified — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

reward_payments

  • id — BIGINT
  • application_id — BIGINT — Foreign key → campaign_applications.id — NOT NULL
  • campaign_id — BIGINT — Foreign key → campaigns.id — NOT NULL
  • influencer_id — BIGINT — Foreign key → influencers.id — NOT NULL
  • reviewer_id — BIGINT — Foreign key → admins.id
  • reward_item — VARCHAR(200)
  • reward_type_code_id — BIGINT
  • achievement_metric — TEXT
  • payout_method_code_id — BIGINT
  • payout_amount — DECIMAL(15,2)
  • payout_currency — VARCHAR(3)
  • settlement_amount — DECIMAL(15,2)
  • settlement_currency — VARCHAR(3)
  • exchange_rate — DECIMAL(15,6)
  • bank_info_encrypted — TEXT
  • post_url — VARCHAR(500)
  • is_paid — BOOLEAN — NOT NULL
  • paid_at — DATE
  • created_at — TIMESTAMPTZ — NOT NULL

sns_posts

  • id — BIGINT
  • application_id — BIGINT — Foreign key → campaign_applications.id — NOT NULL
  • sns_platform_code_id — BIGINT — NOT NULL
  • post_url — VARCHAR(500)
  • expected_post_date — DATE
  • published_at — DATE
  • review_status — post_review_status — NOT NULL
  • reviewed_by — BIGINT — Foreign key → admins.id
  • reviewed_at — DATE
  • review_notes — TEXT
  • likes_count — INT — NOT NULL
  • saves_count — INT — NOT NULL
  • comments_count — INT — NOT NULL
  • metrics_elapsed_days — INT
  • metrics_recorded_at — DATE
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL

visit_schedules

  • id — BIGINT
  • application_id — BIGINT — Foreign key → campaign_applications.id — NOT NULL
  • visitor_count — INT — NOT NULL
  • preferred_dates — JSONB
  • preferred_time — VARCHAR(100)
  • confirmed_date — DATE
  • confirmed_time — VARCHAR(100)
  • confirmed_at — TIMESTAMPTZ
  • visit_completed_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ — NOT NULL