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