Polight_ERD

Tables (16)

analysis_results

  • id — UUID — Primary key — NOT NULL
  • document_id — UUID — Foreign key → policy_documents.id — NOT NULL
  • policy_id — UUID — Foreign key → policies.id
  • summary — TEXT
  • raw_result_json — TEXT
  • accuracy_score — REAL
  • status — VARCHAR(20) — NOT NULL
  • analysis_version — VARCHAR(50) — NOT NULL
  • chunking_version — VARCHAR(50)
  • embedding_model — VARCHAR(100)
  • embedding_dimension — INTEGER
  • started_at — TIMESTAMP — NOT NULL
  • completed_at — TIMESTAMP
  • failure_reason — TEXT
  • is_active — BOOLEAN — NOT NULL
  • analyzed_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

chat_messages

  • id — UUID — Primary key — NOT NULL
  • session_id — UUID — Foreign key → chat_sessions.id — NOT NULL
  • sender — VARCHAR(20) — NOT NULL
  • content — TEXT — NOT NULL
  • response_type — VARCHAR(30) — NOT NULL
  • metadata_json — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

chat_sessions

  • id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • trip_id — UUID — Foreign key → trips.id
  • policy_id — UUID — Foreign key → policies.id
  • title — VARCHAR(100) — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • started_at — TIMESTAMP — NOT NULL
  • last_active_at — TIMESTAMP — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

coverage_detail_items

  • id — UUID — Primary key — NOT NULL
  • coverage_item_id — UUID — Foreign key → coverage_items.id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • subtitle — VARCHAR(500)
  • is_covered — BOOLEAN — NOT NULL
  • sort_order — INTEGER — NOT NULL

coverage_item_sources

  • id — UUID — Primary key — NOT NULL
  • coverage_item_id — UUID — Foreign key → coverage_items.id — NOT NULL
  • policy_chunk_id — UUID — Foreign key → policy_chunks.id — NOT NULL
  • source_role — VARCHAR(30) — NOT NULL
  • quote_text — TEXT
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

coverage_items

  • id — UUID — Primary key — NOT NULL
  • policy_id — UUID — Foreign key → policies.id — NOT NULL
  • analysis_result_id — UUID — Foreign key → analysis_results.id — NOT NULL
  • emoji — VARCHAR(10)
  • title — VARCHAR(200) — NOT NULL
  • subtitle — VARCHAR(500)
  • category — VARCHAR(100)
  • limit_label — VARCHAR(100)
  • is_covered — BOOLEAN — NOT NULL
  • coverage_status — VARCHAR(20) — NOT NULL
  • limit_amount — BIGINT
  • limit_currency — VARCHAR(10)
  • conditions — TEXT
  • sort_order — INTEGER — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

emergency_contacts

  • id — UUID — Primary key — NOT NULL
  • country_code — VARCHAR(10) — NOT NULL
  • type — VARCHAR(30) — NOT NULL
  • name — VARCHAR(200) — NOT NULL
  • phone — VARCHAR(50)
  • description — VARCHAR(500)
  • insurer_name — VARCHAR(200)
  • policy_id — UUID — Foreign key → policies.id
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

notification_preferences

  • id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • policy_expiry_enabled — BOOLEAN — NOT NULL
  • renewal_enabled — BOOLEAN — NOT NULL
  • analysis_done_enabled — BOOLEAN — NOT NULL
  • push_enabled — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

notifications

  • id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • type — VARCHAR(30) — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • body — VARCHAR(500) — NOT NULL
  • deep_link — VARCHAR(500)
  • read_at — TIMESTAMP
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

policies

  • id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • trip_id — UUID — Foreign key → trips.id — NOT NULL
  • insurer_name — VARCHAR(200) — NOT NULL
  • product_name — VARCHAR(200) — NOT NULL
  • policy_number_encrypted — VARCHAR(500)
  • display_name — VARCHAR(200) — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • coverage_score — INTEGER
  • coverage_count — INTEGER — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

policy_chunks

  • id — UUID — Primary key — NOT NULL
  • analysis_result_id — UUID — Foreign key → analysis_results.id — NOT NULL
  • chunk_index — INTEGER — NOT NULL
  • content_type — VARCHAR(30) — NOT NULL
  • page_start — INTEGER
  • page_end — INTEGER
  • section_title — VARCHAR(500)
  • clause_path — VARCHAR(300)
  • coverage_category — VARCHAR(100)
  • coverage_type — VARCHAR(100)
  • content — TEXT — NOT NULL
  • summary — TEXT
  • embedding — vector(1536)
  • char_count — INTEGER — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

policy_documents

  • id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • trip_id — UUID — Foreign key → trips.id
  • policy_id — UUID — Foreign key → policies.id
  • original_filename — VARCHAR(255) — NOT NULL
  • stored_file_path — VARCHAR(500) — NOT NULL
  • content_type — VARCHAR(100)
  • file_size — BIGINT
  • parse_status — VARCHAR(20) — NOT NULL
  • uploaded_at — TIMESTAMP — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

required_documents

  • id — UUID — Primary key — NOT NULL
  • coverage_item_id — UUID — Foreign key → coverage_items.id — NOT NULL
  • document_name — VARCHAR(200) — NOT NULL
  • is_mandatory — BOOLEAN — NOT NULL
  • sort_order — INTEGER — NOT NULL

sub_coverage_limits

  • id — UUID — Primary key — NOT NULL
  • coverage_item_id — UUID — Foreign key → coverage_items.id — NOT NULL
  • label — VARCHAR(100) — NOT NULL
  • value — VARCHAR(200) — NOT NULL
  • limit_amount — BIGINT
  • limit_currency — VARCHAR(10)
  • description — VARCHAR(500)
  • sort_order — INTEGER — NOT NULL

trips

  • id — UUID — Primary key — NOT NULL
  • user_id — UUID — Foreign key → users.id — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • country_code — VARCHAR(10) — NOT NULL
  • country_name — VARCHAR(100) — NOT NULL
  • city_name — VARCHAR(100)
  • flag_emoji — VARCHAR(10)
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

users

  • id — UUID — Primary key — NOT NULL
  • email — VARCHAR(100)
  • password_hash — VARCHAR(255)
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(30)
  • avatar_emoji — VARCHAR(10)
  • passport_name — VARCHAR(100)
  • passport_no_encrypted — VARCHAR(500)
  • nationality_code — VARCHAR(10)
  • provider — VARCHAR(30) — NOT NULL
  • provider_id — VARCHAR(100) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL