청년똑똑
테이블 (22)
admin_review_items
- id — CHAR(36) — 기본 키 — NOT NULL
- item_type — VARCHAR(50) — NOT NULL
- target_id — CHAR(36) — NOT NULL
- priority — VARCHAR(30) — NOT NULL
- status — VARCHAR(30) — NOT NULL
- reason — TEXT
- created_at — DATETIME — NOT NULL
- resolved_at — DATETIME
announcement_sources
- id — CHAR(36) — 기본 키 — NOT NULL
- announcement_id — CHAR(36) — 외래 키 → policy_announcements.id — NOT NULL
- source_record_id — CHAR(36) — 외래 키 → source_records.id — NOT NULL
- source_id — CHAR(36) — 외래 키 → source_registry.id — NOT NULL
- source_url — TEXT — NOT NULL
- evidence_type — VARCHAR(50) — NOT NULL
- confidence — DECIMAL(5, 4) — NOT NULL
- created_at — DATETIME — NOT NULL
bookmarks
- id — CHAR(36) — 기본 키 — NOT NULL
- user_id — CHAR(36) — 외래 키 → users.id — NOT NULL
- program_id — CHAR(36) — 외래 키 → policy_programs.id — NOT NULL
- announcement_id — CHAR(36) — 외래 키 → policy_announcements.id
- memo — TEXT
- notify_enabled — BOOLEAN — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME
category_subscriptions
- id — CHAR(36) — 기본 키 — NOT NULL
- user_id — CHAR(36) — 외래 키 → users.id
- anonymous_id — VARCHAR(100)
- category_code — VARCHAR(50) — NOT NULL
- region_code — VARCHAR(50)
- is_active — BOOLEAN — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
match_candidates
- id — CHAR(36) — 기본 키 — NOT NULL
- source_record_id — CHAR(36) — 외래 키 → source_records.id — NOT NULL
- candidate_program_id — CHAR(36) — 외래 키 → policy_programs.id
- candidate_announcement_id — CHAR(36) — 외래 키 → policy_announcements.id
- match_type — VARCHAR(30) — NOT NULL
- score — DECIMAL(5, 4) — NOT NULL
- reasons — TEXT
- status — VARCHAR(30) — NOT NULL
- reviewed_by — CHAR(36) — 외래 키 → users.id
- reviewed_at — DATETIME
- created_at — DATETIME — NOT NULL
notification_deliveries
- id — CHAR(36) — 기본 키 — NOT NULL
- notification_event_id — CHAR(36) — 외래 키 → notification_events.id — NOT NULL
- user_id — CHAR(36) — 외래 키 → users.id
- anonymous_id — VARCHAR(100)
- device_id — CHAR(36) — 외래 키 → user_devices.id
- channel — VARCHAR(30) — NOT NULL
- dedupe_key — VARCHAR(500) — NOT NULL
- status — VARCHAR(30) — NOT NULL
- provider_message_id — VARCHAR(255)
- sent_at — DATETIME
- failed_reason — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
notification_events
- id — CHAR(36) — 기본 키 — NOT NULL
- event_type — VARCHAR(50) — NOT NULL
- program_id — CHAR(36) — 외래 키 → policy_programs.id
- announcement_id — CHAR(36) — 외래 키 → policy_announcements.id
- scheduled_at — DATETIME — NOT NULL
- event_key — VARCHAR(500) — NOT NULL
- payload — TEXT
- status — VARCHAR(30) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
notification_interactions
- id — CHAR(36) — 기본 키 — NOT NULL
- delivery_id — CHAR(36) — 외래 키 → notification_deliveries.id — NOT NULL
- action — VARCHAR(30) — NOT NULL
- occurred_at — DATETIME — NOT NULL
- metadata — TEXT
notification_preferences
- id — CHAR(36) — 기본 키 — NOT NULL
- user_id — CHAR(36) — 외래 키 → users.id
- anonymous_id — VARCHAR(100)
- channel — VARCHAR(30) — NOT NULL
- enabled — BOOLEAN — NOT NULL
- new_policy_enabled — BOOLEAN — NOT NULL
- new_announcement_enabled — BOOLEAN — NOT NULL
- start_reminder_enabled — BOOLEAN — NOT NULL
- deadline_reminder_enabled — BOOLEAN — NOT NULL
- reopened_enabled — BOOLEAN — NOT NULL
- offsets — TEXT
- quiet_hours — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
policy_announcements
- id — CHAR(36) — 기본 키 — NOT NULL
- program_id — CHAR(36) — 외래 키 → policy_programs.id — NOT NULL
- title — VARCHAR(500) — NOT NULL
- normalized_title — VARCHAR(500)
- round_label — VARCHAR(100)
- business_year — INT
- apply_start_at — DATETIME
- apply_end_at — DATETIME
- status — VARCHAR(30) — NOT NULL
- capacity — INT
- source_confidence — VARCHAR(30) — NOT NULL
- published_at — DATETIME
- first_seen_at — DATETIME — NOT NULL
- last_seen_at — DATETIME — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
policy_programs
- id — CHAR(36) — 기본 키 — NOT NULL
- canonical_name — VARCHAR(300) — NOT NULL
- normalized_key — VARCHAR(300) — NOT NULL
- category_code — VARCHAR(50)
- subcategory_code — VARCHAR(50)
- main_org_name — VARCHAR(200)
- main_org_code — VARCHAR(100)
- region_scope — TEXT
- target_age_min — INT
- target_age_max — INT
- support_summary — TEXT
- status — VARCHAR(30) — NOT NULL
- priority — VARCHAR(30) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
program_aliases
- id — CHAR(36) — 기본 키 — NOT NULL
- program_id — CHAR(36) — 외래 키 → policy_programs.id — NOT NULL
- alias — VARCHAR(300) — NOT NULL
- normalized_alias — VARCHAR(300) — NOT NULL
- alias_type — VARCHAR(30) — NOT NULL
- created_at — DATETIME — NOT NULL
program_sources
- id — CHAR(36) — 기본 키 — NOT NULL
- program_id — CHAR(36) — 외래 키 → policy_programs.id — NOT NULL
- source_id — CHAR(36) — 외래 키 → source_registry.id — NOT NULL
- source_url — TEXT — NOT NULL
- external_id_hint — VARCHAR(255)
- source_role — VARCHAR(50) — NOT NULL
- is_primary — BOOLEAN — NOT NULL
- monitor_enabled — BOOLEAN — NOT NULL
- check_interval_minutes — INT
- last_seen_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
program_subscriptions
- id — CHAR(36) — 기본 키 — NOT NULL
- user_id — CHAR(36) — 외래 키 → users.id
- anonymous_id — VARCHAR(100)
- program_id — CHAR(36) — 외래 키 → policy_programs.id — NOT NULL
- subscription_type — VARCHAR(30) — NOT NULL
- is_active — BOOLEAN — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
source_extractions
- id — CHAR(36) — 기본 키 — NOT NULL
- snapshot_id — CHAR(36) — 외래 키 → source_snapshots.id — NOT NULL
- field_name — VARCHAR(100) — NOT NULL
- field_value — TEXT
- normalized_value — TEXT
- confidence — DECIMAL(5, 4) — NOT NULL
- extracted_by — VARCHAR(30) — NOT NULL
- evidence_text — TEXT
- created_at — DATETIME — NOT NULL
source_records
- id — CHAR(36) — 기본 키 — NOT NULL
- source_id — CHAR(36) — 외래 키 → source_registry.id — NOT NULL
- external_record_id — VARCHAR(255)
- canonical_url — TEXT — NOT NULL
- title — TEXT
- published_at — DATETIME
- first_seen_at — DATETIME — NOT NULL
- last_seen_at — DATETIME — NOT NULL
- latest_snapshot_id — CHAR(36)
- record_status — VARCHAR(30) — NOT NULL
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
source_registry
- id — CHAR(36) — 기본 키 — NOT NULL
- name — VARCHAR(200) — NOT NULL
- source_type — VARCHAR(50) — NOT NULL
- role — VARCHAR(50) — NOT NULL
- base_url — TEXT — NOT NULL
- list_url — TEXT
- detail_url_template — TEXT
- identity_param — VARCHAR(100)
- parser_type — VARCHAR(100)
- parser_config — TEXT
- reliability_rank — INT — NOT NULL
- crawl_interval_minutes — INT — NOT NULL
- is_active — BOOLEAN — NOT NULL
- last_checked_at — DATETIME
- last_success_at — DATETIME
- last_error — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
source_snapshots
- id — CHAR(36) — 기본 키 — NOT NULL
- source_record_id — CHAR(36) — 외래 키 → source_records.id — NOT NULL
- fetched_at — DATETIME — NOT NULL
- http_status — INT
- content_type — VARCHAR(100) — NOT NULL
- raw_storage_path — TEXT
- raw_text — TEXT
- raw_json — TEXT
- raw_hash — VARCHAR(128) — NOT NULL
- text_hash — VARCHAR(128)
- parser_version — VARCHAR(100)
- created_at — DATETIME — NOT NULL
user_devices
- id — CHAR(36) — 기본 키 — NOT NULL
- user_id — CHAR(36) — 외래 키 → users.id
- anonymous_id — VARCHAR(100)
- platform — VARCHAR(30) — NOT NULL
- push_token — TEXT — NOT NULL
- provider — VARCHAR(30) — NOT NULL
- app_version — VARCHAR(50)
- is_active — BOOLEAN — NOT NULL
- last_seen_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
user_events
- id — CHAR(36) — 기본 키 — NOT NULL
- user_id — CHAR(36) — 외래 키 → users.id
- anonymous_id — VARCHAR(100)
- event_name — VARCHAR(100) — NOT NULL
- program_id — CHAR(36) — 외래 키 → policy_programs.id
- announcement_id — CHAR(36) — 외래 키 → policy_announcements.id
- source_record_id — CHAR(36) — 외래 키 → source_records.id
- metadata — TEXT
- occurred_at — DATETIME — NOT NULL
user_profiles
- user_id — CHAR(36) — 외래 키 → users.id — NOT NULL
- birth_year — INT
- age_range_code — VARCHAR(50)
- region_code — VARCHAR(50)
- district_codes — TEXT
- employment_status — VARCHAR(50)
- education_status — VARCHAR(50)
- income_range_code — VARCHAR(50)
- interests — TEXT
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
users
- id — CHAR(36) — 기본 키 — NOT NULL
- auth_provider — VARCHAR(50)
- auth_subject — VARCHAR(255)
- email — VARCHAR(255)
- nickname — VARCHAR(100)
- status — VARCHAR(30) — NOT NULL
- last_login_at — DATETIME
- created_at — DATETIME — NOT NULL
- updated_at — DATETIME — NOT NULL
- deleted_at — DATETIME