청년똑똑

테이블 (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