newERD

Tables (21)

ai_analyses

  • id — BIGINT — Primary key — NOT NULL
  • conversation_id — BIGINT — Foreign key → ai_conversations.id — NOT NULL
  • idempotency_key — VARCHAR(64) — NOT NULL
  • based_through_sequence — BIGINT — NOT NULL
  • analysis_status — ENUM('QUEUED', 'RUNNING', 'DELAYED', 'AWAITING_REVIEW', 'APPROVED', 'REJECTED', 'FAILED') — NOT NULL
  • progress_percent — TINYINT — NOT NULL
  • result_json — JSON — recent needs, interests, hints and long-term traits
  • summary_text — VARCHAR(1000)
  • model_version — VARCHAR(100) — NOT NULL
  • prompt_version — VARCHAR(100) — NOT NULL
  • started_at — DATETIME(6)
  • delayed_at — DATETIME(6)
  • finished_at — DATETIME(6)
  • decided_at — DATETIME(6)
  • failure_code — VARCHAR(100)
  • failure_message — VARCHAR(1000)
  • active_conversation_id — BIGINT
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

ai_conversations

  • id — BIGINT — Primary key — NOT NULL
  • public_id — BINARY(16) — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • conversation_status — ENUM('READY', 'ACTIVE', 'RESPONDING', 'ANALYZING', 'ANALYSIS_DELAYED', 'REVIEW_REQUIRED', 'COMPLETED', 'FAILED') — NOT NULL
  • analysis_progress — TINYINT — NOT NULL
  • next_sequence_no — BIGINT — NOT NULL
  • started_at — DATETIME(6) — Set by the first valid user message
  • completed_at — DATETIME(6)
  • row_version — BIGINT — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

ai_messages

  • id — BIGINT — Primary key — NOT NULL
  • conversation_id — BIGINT — Foreign key → ai_conversations.id — NOT NULL
  • sequence_no — BIGINT — NOT NULL
  • sender_type — ENUM('USER', 'AI', 'SYSTEM') — NOT NULL
  • message_text — TEXT — NOT NULL
  • message_status — ENUM('PENDING', 'SENT', 'FAILED') — NOT NULL
  • client_request_id — VARCHAR(64) — NOT NULL
  • failure_code — VARCHAR(100)
  • sent_at — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

anonymous_messages

  • id — BIGINT — Primary key — NOT NULL
  • anonymous_room_id — BIGINT — Foreign key → anonymous_rooms.id — NOT NULL
  • sender_user_id — BIGINT — Foreign key → users.id — NOT NULL — Internal only
  • sequence_no — BIGINT — NOT NULL
  • message_text — VARCHAR(500) — NOT NULL
  • client_request_id — VARCHAR(64) — NOT NULL
  • message_status — ENUM('SENT', 'FAILED', 'REDACTED') — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • redacted_at — DATETIME(6)

anonymous_reports

  • id — BIGINT — Primary key — NOT NULL
  • anonymous_room_id — BIGINT — Foreign key → anonymous_rooms.id — NOT NULL
  • reporter_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • reported_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • evidence_message_id — BIGINT — Foreign key → anonymous_messages.id
  • reason_code — ENUM('ABUSE_HATE_SEXUAL', 'REPEATED_SPAM', 'THREAT_PRIVACY_REQUEST', 'OTHER') — NOT NULL
  • idempotency_key — VARCHAR(64) — NOT NULL
  • report_status — ENUM('RECEIVED', 'REVIEWING', 'ACTIONED', 'DISMISSED') — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • resolved_at — DATETIME(6)

anonymous_rooms

  • id — BIGINT — Primary key — NOT NULL
  • public_id — BINARY(16) — NOT NULL
  • requester_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • recipient_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • recommendation_item_id — BIGINT — Foreign key → recommendation_items.id
  • idempotency_key — VARCHAR(64) — NOT NULL
  • room_status — ENUM('PENDING_UNREAD', 'ACTIVE', 'EXPIRED', 'CANCELLED', 'BLOCKED') — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • first_opened_at — DATETIME(6) — Recipient first-open time
  • expires_at — DATETIME(6)
  • next_sequence_no — BIGINT — NOT NULL
  • evidence_retain_until — DATETIME(6)
  • purge_after — DATETIME(6)
  • updated_at — DATETIME(6) — NOT NULL

auth_sessions

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • session_token_hash — BINARY(32) — NOT NULL
  • refresh_token_hash — BINARY(32)
  • device_hash — BINARY(32)
  • device_name — VARCHAR(100)
  • issued_at — DATETIME(6) — NOT NULL
  • expires_at — DATETIME(6) — NOT NULL
  • last_seen_at — DATETIME(6)
  • revoked_at — DATETIME(6)
  • revoke_reason — ENUM('LOGOUT', 'WITHDRAWAL', 'SUSPENSION', 'EXPIRED', 'ROTATED', 'SECURITY')

calendar_connections

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • provider — ENUM('KAKAO') — NOT NULL
  • connection_status — ENUM('ACTIVE', 'DISCONNECTED', 'ERROR') — NOT NULL
  • access_token_ciphertext — VARBINARY(4096) — NOT NULL
  • refresh_token_ciphertext — VARBINARY(4096)
  • encryption_key_version — SMALLINT — NOT NULL
  • token_expires_at — DATETIME(6)
  • last_synced_at — DATETIME(6)
  • last_sync_status — ENUM('SUCCEEDED', 'FAILED')
  • last_sync_error — VARCHAR(1000)
  • disconnected_at — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

consent_policies

  • id — BIGINT — Primary key — NOT NULL
  • policy_code — VARCHAR(64) — NOT NULL
  • policy_version — VARCHAR(32) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • is_required — BOOLEAN — NOT NULL
  • requires_reconsent — BOOLEAN — NOT NULL
  • effective_at — DATETIME(6) — NOT NULL
  • retired_at — DATETIME(6)
  • content_hash — BINARY(32) — NOT NULL
  • created_at — DATETIME(6) — NOT NULL

events

  • id — BIGINT — Primary key — NOT NULL
  • owner_user_id — BIGINT — Foreign key → users.id — NOT NULL — Registrant/owner
  • subject_user_id — BIGINT — Foreign key → users.id — NOT NULL — User the event is about
  • calendar_connection_id — BIGINT — Foreign key → calendar_connections.id
  • event_type — ENUM('BIRTHDAY', 'ANNIVERSARY', 'CUSTOM') — NOT NULL
  • source_type — ENUM('PROFILE', 'USER', 'KAKAO_CALENDAR') — NOT NULL
  • source_key — VARCHAR(100) — Profile-field key or external source key
  • external_event_id — VARCHAR(255)
  • title — VARCHAR(100) — NOT NULL
  • event_date — DATE — NOT NULL — D-Day is calculated and never stored
  • recurrence_type — ENUM('NONE', 'YEARLY') — NOT NULL
  • visibility — ENUM('PRIVATE', 'FRIENDS') — NOT NULL
  • event_status — ENUM('ACTIVE', 'DELETED') — NOT NULL
  • deleted_at — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

friendships

  • id — BIGINT — Primary key — NOT NULL
  • requester_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • addressee_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • pair_low_user_id — BIGINT
  • pair_high_user_id — BIGINT
  • requester_favorite — BOOLEAN — NOT NULL
  • addressee_favorite — BOOLEAN — NOT NULL
  • friendship_status — ENUM('PENDING', 'ACCEPTED', 'REMOVED') — NOT NULL
  • requested_at — DATETIME(6) — NOT NULL
  • accepted_at — DATETIME(6)
  • removed_at — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

notification_settings

  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • friend_join_enabled — BOOLEAN — NOT NULL
  • friend_birthday_enabled — BOOLEAN — NOT NULL
  • event_enabled — BOOLEAN — NOT NULL
  • poke_enabled — BOOLEAN — NOT NULL
  • anonymous_question_enabled — BOOLEAN — NOT NULL
  • chat_message_enabled — BOOLEAN — NOT NULL
  • marketing_enabled — BOOLEAN — NOT NULL
  • push_enabled — BOOLEAN — NOT NULL
  • push_platform — ENUM('IOS', 'ANDROID', 'WEB')
  • push_token_hash — BINARY(32)
  • push_token_ciphertext — VARBINARY(4096)
  • push_token_key_version — SMALLINT
  • changed_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

notifications

  • id — BIGINT — Primary key — NOT NULL
  • public_id — BINARY(16) — NOT NULL
  • recipient_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • actor_user_id — BIGINT — Foreign key → users.id — Internal actor; omit from anonymous API output
  • notification_type — ENUM('FRIEND_JOINED', 'FRIEND_BIRTHDAY', 'EVENT', 'POKE', 'ANONYMOUS_QUESTION', 'CHAT_MESSAGE', 'MARKETING') — NOT NULL
  • source_event_key — VARCHAR(160) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • body — VARCHAR(1000) — NOT NULL
  • target_type — ENUM('NONE', 'FRIEND', 'EVENT', 'AI_CONVERSATION', 'ANONYMOUS_ROOM', 'PRODUCT') — NOT NULL
  • target_id — BIGINT — Validated against target_type on navigation
  • target_status — ENUM('ACTIVE', 'UNAVAILABLE', 'EXPIRED') — NOT NULL
  • in_app_status — ENUM('VISIBLE', 'SUPPRESSED') — NOT NULL
  • push_status — ENUM('NOT_REQUESTED', 'PENDING', 'SENT', 'DELIVERED', 'FAILED', 'SUPPRESSED') — NOT NULL
  • push_failure_code — VARCHAR(100)
  • push_attempted_at — DATETIME(6)
  • push_delivered_at — DATETIME(6)
  • occurred_at — DATETIME(6) — NOT NULL
  • read_at — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL

product_feedback

  • id — BIGINT — Primary key — NOT NULL
  • respondent_user_id — BIGINT — Foreign key → users.id — NOT NULL — Never expose for anonymous feedback
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • recommendation_item_id — BIGINT — Foreign key → recommendation_items.id
  • feedback_context — ENUM('SELF_RECOMMENDATION', 'FRIEND_RECOMMENDATION', 'PROPOSED_GIFT', 'RECEIVED_GIFT') — NOT NULL
  • feedback_value — ENUM('LIKE', 'DISLIKE') — NOT NULL
  • dislike_reason_code — ENUM('DISLIKE_PRODUCT', 'ALREADY_RECEIVED', 'NOT_MY_TASTE', 'NOT_SUITABLE_CONTEXT', 'NO_EXPECTED_USE')
  • is_anonymous_response — BOOLEAN — NOT NULL
  • idempotency_key — VARCHAR(64) — NOT NULL
  • feedback_version — INT — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

products

  • id — BIGINT — Primary key — NOT NULL
  • public_id — BINARY(16) — NOT NULL
  • source_provider — VARCHAR(64) — NOT NULL
  • source_product_id — VARCHAR(255) — NOT NULL
  • product_name — VARCHAR(300)
  • description_text — TEXT
  • image_url — VARCHAR(2048)
  • merchant_name — VARCHAR(200)
  • price_amount — DECIMAL(12, 2)
  • currency_code — CHAR(3) — NOT NULL
  • category_codes — JSON — NOT NULL — Array, e.g. "LIVING"
  • attribute_codes — JSON — NOT NULL — Array used for preference matching
  • purchase_url — VARCHAR(2048)
  • detail_url — VARCHAR(2048)
  • product_status — ENUM('ACTIVE', 'UNAVAILABLE', 'DELETED') — NOT NULL
  • source_updated_at — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

recommendation_items

  • id — BIGINT — Primary key — NOT NULL
  • recommendation_request_id — BIGINT — Foreign key → recommendation_requests.id — NOT NULL
  • product_id — BIGINT — Foreign key → products.id — NOT NULL
  • generation_no — INT — NOT NULL
  • rank_order — SMALLINT — NOT NULL
  • score — DECIMAL(10, 6)
  • detail_type — ENUM('SELF_TASTE', 'FRIEND_TASTE', 'PROPOSED_GIFT') — NOT NULL
  • recommendation_reason — VARCHAR(1000) — NOT NULL
  • taste_keywords — JSON — NOT NULL — Ordered array with at most three values
  • created_at — DATETIME(6) — NOT NULL

recommendation_requests

  • id — BIGINT — Primary key — NOT NULL
  • public_id — BINARY(16) — NOT NULL
  • requester_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • target_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • target_type — ENUM('SELF', 'FRIEND') — NOT NULL
  • min_budget — DECIMAL(12, 2) — NOT NULL
  • max_budget — DECIMAL(12, 2) — NOT NULL
  • currency_code — CHAR(3) — NOT NULL
  • context_snapshot — JSON — NOT NULL — Immutable target preferences, interests and exclusions
  • context_hash — BINARY(32) — NOT NULL
  • algorithm_version — VARCHAR(100) — NOT NULL
  • idempotency_key — VARCHAR(64) — NOT NULL
  • current_generation — INT — NOT NULL — Increments for another-product requests
  • request_status — ENUM('PENDING', 'ACTIVE', 'COMPLETED', 'EMPTY', 'FAILED', 'CANCELLED') — NOT NULL
  • failure_code — VARCHAR(100)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

user_blocks

  • id — BIGINT — Primary key — NOT NULL
  • blocker_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • blocked_user_id — BIGINT — Foreign key → users.id — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • blocked_at — DATETIME(6) — NOT NULL
  • unblocked_at — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

user_consents

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • policy_id — BIGINT — Foreign key → consent_policies.id — NOT NULL
  • consent_action — ENUM('AGREED', 'WITHDRAWN') — NOT NULL
  • source_channel — ENUM('ONBOARDING', 'SETTINGS', 'RECONSENT', 'ADMIN') — NOT NULL
  • idempotency_key — VARCHAR(64) — NOT NULL
  • occurred_at — DATETIME(6) — NOT NULL

user_preferences

  • id — BIGINT — Primary key — NOT NULL
  • user_id — BIGINT — Foreign key → users.id — NOT NULL
  • source_analysis_id — BIGINT — Foreign key → ai_analyses.id
  • preference_type — ENUM('INTEREST', 'ALLERGY', 'EXCLUDED_GIFT', 'LONG_TERM_TRAIT', 'RECENT_INTEREST', 'RECOMMENDATION_HINT') — NOT NULL
  • source_type — ENUM('USER', 'AI', 'BEHAVIOR') — NOT NULL
  • normalized_code — VARCHAR(100) — NOT NULL
  • display_name — VARCHAR(200) — NOT NULL
  • confidence — DECIMAL(5,4)
  • visibility — ENUM('PRIVATE', 'FRIENDS') — NOT NULL
  • preference_status — ENUM('ACTIVE', 'EXPIRED', 'REMOVED') — NOT NULL
  • priority_order — SMALLINT — NOT NULL
  • valid_until — DATETIME(6)
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL

users

  • id — BIGINT — Primary key — NOT NULL
  • public_id — BINARY(16) — NOT NULL
  • kakao_subject_hash — BINARY(32) — NOT NULL — Deterministic HMAC/SHA-256 lookup value
  • kakao_subject_ciphertext — VARBINARY(512) — NOT NULL
  • kakao_access_token_ciphertext — VARBINARY(4096)
  • kakao_refresh_token_ciphertext — VARBINARY(4096)
  • kakao_token_expires_at — DATETIME(6)
  • encryption_key_version — SMALLINT — NOT NULL
  • nickname — VARCHAR(50) — NOT NULL
  • profile_image_url — VARCHAR(2048)
  • gender_ciphertext — VARBINARY(512)
  • birth_date_ciphertext — VARBINARY(512)
  • birthday_visibility — ENUM('PRIVATE', 'FRIENDS') — NOT NULL
  • account_status — ENUM('ONBOARDING', 'ACTIVE', 'SUSPENDED', 'WITHDRAWN') — NOT NULL
  • onboarding_step — TINYINT — NOT NULL — Last completely saved step, 0..4
  • onboarding_completed_at — DATETIME(6)
  • theme — ENUM('LIGHT', 'DARK', 'SYSTEM') — NOT NULL
  • time_zone — VARCHAR(64) — NOT NULL
  • last_login_at — DATETIME(6)
  • suspended_at — DATETIME(6)
  • withdrawn_at — DATETIME(6)
  • row_version — BIGINT — NOT NULL
  • created_at — DATETIME(6) — NOT NULL
  • updated_at — DATETIME(6) — NOT NULL