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