Penta 데이터베이스 스키마 (개발자 참조)
개발자용 간결한 스키마 참조 문서이다. 상세 설명은 docs/정책문서/09_데이터베이스/01_DB_스키마.md를 참고한다.
- ORM: Django ORM + django-parler (다국어) + django-safedelete (소프트 삭제)
- 운영 DB: PostgreSQL
- 다국어: 모든
_translation테이블은(master_id, language_code)UNIQUE
사용자 도메인
users_user
| 컬럼 | 타입 | 제약조건 | 설명 |
|---|---|---|---|
| id | BIGINT | PK, AUTO | |
| VARCHAR(255) | NULL | 이메일 (소셜은 placeholder) | |
| username | VARCHAR(150) | NULL | |
| full_name | VARCHAR(255) | ||
| nickname | VARCHAR(100) | UNIQUE, INDEX | |
| social_provider | VARCHAR(50) | NULL | google/apple/kakao/line/facebook |
| social_id | VARCHAR(255) | NULL | |
| language | VARCHAR(10) | DEFAULT 'en' | |
| country | VARCHAR(2) | NULL | ISO 국가코드 |
| birth_year | INTEGER | NULL | |
| has_subscribed_before | BOOLEAN | DEFAULT FALSE | |
| is_active | BOOLEAN | DEFAULT TRUE | |
| is_staff | BOOLEAN | DEFAULT FALSE | |
| date_joined | DATETIME | ||
| last_login | DATETIME | NULL | |
| app_language | VARCHAR(10) | DEFAULT 'en' | 앱 표시 언어 |
| viewer_languages | JSON | DEFAULT [] | 콘텐츠 언어 |
| push_notification_enabled | BOOLEAN | DEFAULT TRUE | |
| notification_marketing | BOOLEAN | DEFAULT FALSE | |
| notification_bookmark | BOOLEAN | DEFAULT TRUE | |
| marketing_consent | BOOLEAN | DEFAULT FALSE | |
| deletion_reason | VARCHAR(50) | NULL | |
| deleted | DATETIME | NULL | SafeDelete |
INDEX: email / (social_provider, social_id)
UNIQUE: (social_provider, social_id) WHERE deleted IS NULL
users_device
| 컬럼 | 타입 | 제약조건 | 설명 |
|---|---|---|---|
| id | BIGINT | PK | |
| user_id | BIGINT | FK(users_user) | CASCADE |
| device_id | VARCHAR(255) | UNIQUE | |
| device_type | VARCHAR(50) | ios/android | |
| device_model | VARCHAR(100) | ||
| app_version | VARCHAR(20) | ||
| timezone | VARCHAR(50) | DEFAULT '' | |
| fcm_token | VARCHAR(500) | NULL, INDEX | |
| is_active | BOOLEAN | DEFAULT TRUE | |
| push_enabled | BOOLEAN | DEFAULT TRUE | |
| last_active | DATETIME | auto_now | |
| created_at | DATETIME | auto_now_add |
users_loginhistory
| 컬럼 | 타입 | 제약조건 | 설명 |
|---|---|---|---|
| id | BIGINT | PK | |
| user_id | BIGINT | FK(users_user) | CASCADE |
| login_type | VARCHAR(50) | ||
| ip_address | INET | NULL | |
| user_agent | TEXT | ||
| device_id | BIGINT | FK(users_device) NULL | SET_NULL |
| created_at | DATETIME | auto_now_add |
users_reviewrequestlog
| 컬럼 | 타입 | 제약조건 | 설명 |
|---|---|---|---|
| id | BIGINT | PK | |
| user_id | BIGINT | FK(users_user) | CASCADE |
| device_id | VARCHAR(255) | ||
| created_at | DATETIME | auto_now_add |
콘텐츠 도메인
books_book
| 컬럼 | 타입 | 제약조건 | 설명 |
|---|---|---|---|
| id | BIGINT | PK | |
| book_code | VARCHAR(10) | UNIQUE, NULL | 예: A001 |
| lexile_label | VARCHAR(50) | NULL | |
| age_label | VARCHAR(50) | NULL | |
| brand | VARCHAR(20) | DEFAULT 'disney' | disney/pixar |
| status | VARCHAR(20) | DEFAULT 'ongoing' | ongoing/completed/hiatus |
| views | INTEGER | DEFAULT 0 | |
| is_active | BOOLEAN | DEFAULT TRUE | |
| average_rating | DECIMAL(3,2) | DEFAULT 0.00 | |
| pv_score | INTEGER | DEFAULT 0 | |
| series_id | BIGINT | FK(books_series) NULL | SET_NULL |
| lexile_filter_id | BIGINT | FK(home_homefilter) NULL | SET_NULL |
| created_at | DATETIME |
INDEX: status / -views
M2M: characters -> books_character / illustrators -> books_illustrator / age_filters -> home_homefilter
books_book_translation
| 컬럼 | 타입 | 설명 |
|---|---|---|
| master_id | BIGINT | FK(books_book) |
| language_code | VARCHAR(15) | ko/en/ja/es |
| title | VARCHAR(500) | |
| author | VARCHAR(200) | |
| illustrator | VARCHAR(200) | |
| publisher | VARCHAR(100) | |
| synopsis | TEXT | |
| cover_url | VARCHAR(500) | |
| content_url | VARCHAR(500) | |
| published_date | DATE | |
| is_new | BOOLEAN |
books_episode
| 컬럼 | 타입 | 제약조건 | 설명 |
|---|---|---|---|
| id | BIGINT | PK | |
| book_id | BIGINT | FK(books_book) | CASCADE |
| episode_number | INTEGER | ||
| published_date | DATE | NULL | |
| views | INTEGER | DEFAULT 0 |
UNIQUE: (book_id, episode_number)
books_episode_translation
| 컬럼 | 타입 | 설명 |
|---|---|---|
| master_id | BIGINT | FK(books_episode) |
| language_code | VARCHAR(15) | |
| title | VARCHAR(500) | |
| pages | JSON | 페이지 URL/메타데이터 리스트 |
books_series / books_series_translation
비번역: display_order (INT), created_at
번역: name (VARCHAR(200))
books_character / books_character_translation
비번역: character_key (VARCHAR(100) UNIQUE), brand, image_url, display_order, created_at
번역: character_name (VARCHAR(200))
books_illustrator / books_illustrator_translation
비번역: profile_image_url, display_order, created_at, updated_at
번역: name (VARCHAR(200)), bio (TEXT)
books_readinghistory
| 컬럼 | 타입 | 제약조건 | 설명 |
|---|---|---|---|
| id | BIGINT | PK | |
| user_id | BIGINT | FK(users_user) | CASCADE |
| book_id | BIGINT | FK(books_book) | CASCADE |
| episode_id | BIGINT | FK(books_episode) | CASCADE |
| last_page | INTEGER | DEFAULT 0 | |
| total_pages | INTEGER | DEFAULT 0 | |
| progress_percentage | FLOAT | DEFAULT 0.0 | 0~100 |
| reading_time | INTEGER | DEFAULT 0 | 초 단위 |
| is_completed | BOOLEAN | DEFAULT FALSE | |
| started_at | DATETIME | ||
| last_read_at | DATETIME | ||
| completed_at | DATETIME | NULL |
UNIQUE: (user_id, book_id, episode_id)
books_bookmark
UNIQUE: (user_id, book_id)
books_recording
UNIQUE: (user_id, book_id, episode_id, language_code)
books_dailystat
UNIQUE: (stat_date, book_id)
컬럼: views, completions, bookmarks, stickers_earned
홈/배너/큐레이션
home_homefilter / home_homefilter_translation
비번역: filter_type (tab/series/reading_level), value, code (UNIQUE), display_order, is_active
번역: name
home_realtimeranking
UNIQUE: (book_id, country, ranking_date, ranking_hour)
컬럼: rank (1-10), previous_rank, score, ranking_date, ranking_hour (0-23)
books_banner / books_banner_translation
비번역: link_type, content_type, sequence, is_active, start_date, end_date
번역: title, image_url, link_url, target_type, content_id, curation_id
home_curation / home_curation_translation
비번역: curation_type (theme/collection), display_order, is_active
번역: title, description
M2M: books through home_curationitem
home_curationitem
UNIQUE: (curation_id, book_id)
컬럼: display_order (DEFAULT 999)
스티커
stickers_sticker / stickers_sticker_translation
비번역: episode_id (OneToOne, UNIQUE), image_url, base_score (FLOAT, INDEX), score_updated_at
번역: name
stickers_usersticker
UNIQUE: (user_id, sticker_id) / 컬럼: earned_at
stickers_stat
sticker_id (OneToOne) / total_collectors, daily_collectors, weekly_collectors, monthly_collectors, last_updated
stickers_popular
UNIQUE: (sticker_id, period, country) / position, popularity_score, period (daily/weekly/monthly/all-time), country (NULL=global)
stickers_missing
UNIQUE: (user_id, sticker_id) / position
stickers_upcoming
sticker_id (FK), position, release_date, is_featured
stickers_wishlist
UNIQUE: (user_id, sticker_id)
stickers_exposure
UNIQUE: (user_id, sticker_id, exposure_type) / exposure_type (popular/missing), exposed_at
stickers_campaign
sticker_id (FK), event_id (FK NULL), campaign_name, is_active, priority_boost (DEFAULT 2), start_date, end_date
결제
payments_subscription (OneToOne user)
| 컬럼 | 타입 | 설명 |
|---|---|---|
| user_id | BIGINT | OneToOne(users_user) |
| type | VARCHAR(20) | 1_month/6_month/12_month |
| start_date | DATETIME | |
| end_date | DATETIME | 유효성 기준 |
| is_cancelled | BOOLEAN | 자동갱신 중지 (만료까지 유효) |
| cancelled_at | DATETIME | NULL |
| pause_until | DATETIME | NULL |
| grace_until | DATETIME | NULL |
| auto_renew | BOOLEAN | DEFAULT FALSE |
| next_billing_date | DATETIME | NULL |
| promo_code | VARCHAR(50) | NULL |
| cancellation_reason | VARCHAR(50) | NULL |
| has_referral_bonus | BOOLEAN | NULL |
| referral_bonus_weeks | INTEGER | DEFAULT 0 |
| referral_minimum_period_days | INTEGER | DEFAULT 14 |
| referral_benefits_revoked | BOOLEAN | DEFAULT FALSE |
payments_transaction
id (VARCHAR(32) PK), user_id FK, type (subscription/renewal/refund/cancellation), status (pending/completed/failed/refunded), amount DECIMAL(10,2), currency (DEFAULT 'USD'), payment_method, gateway_transaction_id, purchase_token, metadata JSON
payments_app_store_transaction
transaction_id (UNIQUE), original_transaction_id, user_id FK, product_id, subscription_type, environment, purchase_date, expires_date, price_amount, price_currency, auto_renew_status, is_trial_period, ownership_type (PURCHASED/FAMILY_SHARED), raw_transaction JSON, raw_renewal JSON
payments_google_play_receipt
latest_order_id (UNIQUE), purchase_token, linked_purchase_token, subscription_state, acknowledgement_state, start_time, expiry_time, price_amount_micros BIGINT, currency_code, region_code, product_id, base_plan_id, offer_id, offer_tags JSON, auto_renew_enabled, canceled_state_context JSON, paused_state_context JSON, raw_json JSON, user_id FK NULL, transaction_id OneToOne NULL
payments_refundrequest
transaction_id FK, user_id FK, status, refund_type (full/partial/subscription_cancel), reason, reason_description, google_refund_id (UNIQUE NULL), google_order_id, purchase_token, revoke_entitlement (DEFAULT TRUE), initiated_by_id FK NULL
프로모코드/레퍼럴
promocodes_promocode / promocodes_promocode_translation
비번역: code (UNIQUE), type (corporate/influencer/referral), name, description, bonus_weeks, max_uses NULL, current_uses, one_time_only, new_users_only, valid_from, valid_until NULL, is_active, subscription_types JSON, is_referral_stackable, referrer_bonus_weeks NULL, offer_mapping JSON, discount_mapping JSON, ios_offer_code, qr_code_url, partner_name
번역: banner_text
promocodes_usage
UNIQUE: (user_id, promo_code_id) / subscription_id FK NULL, applied_bonus_weeks, ip_address, user_agent
promocodes_referral
referrer_id FK, promo_code_id OneToOne, total_referrals, successful_referrals
promocodes_applied
user_id OneToOne, promo_code_id FK, expires_at
promocodes_referral_reward
referral_code_id FK, user_id FK, reward_type (referrer/referee), bonus_weeks, subscription_id FK, status (pending/applied/revoked), awarded_at, applied_at, revoked_at, revoke_reason
promocodes_partner_promotion
code (UNIQUE), partner_name, promo_code_id FK NULL, landing_page, custom_message, custom_image_url, is_active, total_clicks, total_conversions, total_views
promocodes_click_tracking
promo_code_id FK NULL, partner_promotion_id FK NULL, ip_address, user_agent, referer, utm_source/medium/campaign/term/content, device_type, os, browser, clicked_at, converted, converted_at
이벤트
events_news / events_news_translation
비번역: type (event/news), image_url, start_date, end_date NULL, button_action, button_action_value, button_color, target_audience, sequence, is_active
번역: title, content, subtitle, body, notes JSON, hero_image_url, button_text
events_news_participations
UNIQUE: (user_id, event_id) / participated_at, completed, completed_at
알림
notifications_notification / notifications_notification_translation
비번역: type (user_content/penta_news), subtype, action_type, target_id
번역: title, message
notifications_usernotification
UNIQUE: (user_id, notification_id) / is_read DEFAULT FALSE, received_at
notifications_push_message_template / translation
비번역: push_type, is_active
번역: title, body
notifications_push_log
user_id FK, push_type, template_id FK NULL, sent_at, status (success/failed)
지원
support_faq / support_faq_translation
비번역: category (account/payment/content/technical/other), order, is_active
번역: question, answer
support_inquiry
user_id FK, type, subject, message, status (pending/in_progress/resolved/closed), admin_response, responded_at
support_announcement / support_announcement_translation
비번역: is_active, published_at
번역: title, content
블로그
blog_post / blog_post_translation
비번역: slug (UNIQUE), author_id FK NULL, category_id FK NULL, status (draft/published/archived), is_featured, thumbnail, views, published_at
번역: title, excerpt, content, meta_title, meta_description
M2M: tags -> blog_tag
blog_category / blog_category_translation
비번역: slug (UNIQUE), display_order, is_active
번역: name, description
blog_tag / blog_tag_translation
비번역: slug (UNIQUE), post_count
번역: name
blog_image
post_id FK NULL, image ImageField, original_filename, file_size
blog_post_view
post_id FK, ip_address, session_key, user_id FK NULL, user_agent, viewed_at
INDEX: (post, ip_address, viewed_at) / (post, session_key, viewed_at)
기타
app_config
| 컬럼 | 타입 | 설명 |
|---|---|---|
| key | VARCHAR(50) | UNIQUE, INDEX |
| value | VARCHAR(100) | NULL이면 비활성 |
| description | TEXT |
관계 다이어그램
users_user
|-- users_device (1:N)
|-- users_loginhistory (1:N)
|-- users_reviewrequestlog (1:N)
|-- payments_subscription (1:1)
|-- books_readinghistory (1:N)
|-- books_bookmark (1:N)
|-- books_recording (1:N)
|-- stickers_usersticker (1:N)
|-- notifications_usernotification (1:N)
|-- notifications_push_log (1:N)
|-- promocodes_usage (1:N)
|-- promocodes_applied (1:1)
|-- promocodes_referral (1:N)
|-- support_inquiry (1:N)
books_book
|-- books_book_translation (1:N)
|-- books_episode (1:N)
| |-- books_episode_translation (1:N)
| |-- stickers_sticker (1:1)
| | |-- stickers_sticker_translation (1:N)
| | |-- stickers_usersticker (1:N)
| | |-- stickers_stat (1:1)
| | |-- stickers_popular (1:N)
| | |-- stickers_missing (1:N)
| | |-- stickers_upcoming (1:N)
| | |-- stickers_wishlist (1:N)
| | |-- stickers_exposure (1:N)
| | |-- stickers_campaign (1:N)
| |-- books_recording (1:N)
|-- books_series (N:1)
|-- books_character (M2M)
|-- books_illustrator (M2M)
|-- home_homefilter (lexile: N:1, age: M2M)
|-- home_curationitem (M2M through)
|-- books_bookmark (1:N)
|-- books_readinghistory (1:N)
|-- books_dailystat (1:N)
|-- home_realtimeranking (1:N)