Liaison Management Portal
Database active
Reference

Schema overview

Live relationship map and FK inventory generated directly from Postgres metadata.

Relationship map

erDiagram
  alembic_version {
    varchar version_num PK
  }
  allowance_city_rate {
    uuid id PK
    uuid city_id
    numeric rate_hkd
    date effective_from
    date effective_to
    text remarks
  }
  allowance_country_rate {
    uuid id PK
    uuid country_id
    numeric rate_hkd
    date effective_from
    date effective_to
    text remarks
  }
  annual_vote_budget {
    uuid id PK
    numeric approved_amount
    varchar approval_ref
    text remarks
    varchar fiscal_year_code
    varchar budget_vote_code
  }
  association {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
    text remarks
    varchar abbr_en
    varchar abbr_zh
  }
  budget_item_type {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
  }
  budget_vote {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
    text remarks
    varchar main_category_code
  }
  city {
    uuid id PK
    uuid country_id
    uuid province_id
    varchar name_en
    varchar name_zh
    bool is_active
    text remarks
  }
  command {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
  }
  continent {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
  }
  country {
    uuid id PK
    varchar iso_code
    varchar name_en
    varchar name_zh
    uuid continent_id
    bool is_active
    text remarks
  }
  country_association {
    uuid id PK
    uuid country_id
    uuid association_id
    text remarks
  }
  duty_visit {
    uuid id PK
    varchar visit_code
    varchar title_en
    varchar title_zh
    bool report_required
    date report_due_date
    date report_submitted_date
    bool report_completed
    bool presentation_required
    date presentation_due_date
    date presentation_completed_date
    bool presentation_completed
    text remarks
    timestamptz created_at
    timestamptz updated_at
    varchar visit_category_code
    varchar main_category_code
    varchar visit_direction_code
    varchar status_code
    varchar fiscal_year_code
    varchar event_series_code
    duty_visit_stream_enum stream
    numeric budget_annual_amount
    numeric estimation_before_departure_amount
    numeric actual_completion_amount
    jsonb budget_annual_calc
    jsonb estimation_before_departure_calc
    jsonb actual_completion_calc
    jsonb actual_expenditure_items
    varchar budget_vote_code
  }
  duty_visit_attachment {
    uuid id PK
    uuid duty_visit_id
    visit_attachment_type_enum attachment_type
    varchar file_name
    text file_path
    timestamptz uploaded_at
    varchar uploaded_by
    text remarks
  }
  duty_visit_budget_allocation {
    uuid id PK
    uuid duty_visit_id
    uuid annual_vote_budget_id
    numeric allocated_amount
    text remarks
  }
  duty_visit_expenditure_item {
    uuid id PK
    uuid duty_visit_plan_version_id
    uuid duty_visit_budget_allocation_id
    expenditure_stage_enum expenditure_stage
    numeric amount
    varchar currency_code
    text remarks
    varchar budget_item_type_code
  }
  duty_visit_external_contact {
    uuid id PK
    uuid duty_visit_id
    uuid external_contact_id
    contact_role_enum contact_role
    uuid met_via_org_id
    text remarks
  }
  duty_visit_itinerary_item {
    uuid id PK
    uuid duty_visit_plan_version_id
    int4 sequence_no
    timestamptz start_datetime
    timestamptz end_datetime
    uuid country_id
    uuid province_id
    uuid city_id
    uuid organization_id
    varchar title
    text remarks
  }
  duty_visit_plan_version {
    uuid id PK
    uuid duty_visit_id
    plan_version_type_enum version_type
    timestamptz start_datetime
    timestamptz end_datetime
    uuid host_country_id
    uuid host_province_id
    uuid host_city_id
    int4 planned_headcount
    text purpose
    text notes
    timestamptz created_at
    timestamptz updated_at
    text visit_location
    varchar planned_date_mode
    int4 planned_year
    int4 planned_month
    int4 planned_quarter
    bool location_to_be_confirmed
    uuid host_continent_id
  }
  duty_visit_unit {
    uuid id PK
    uuid duty_visit_id
    uuid fsd_unit_id
    text remarks
  }
  duty_visit_version_location {
    uuid id PK
    uuid duty_visit_plan_version_id
    int4 sequence_no
    uuid country_id
    uuid province_id
    uuid city_id
    bool is_primary
    text remarks
  }
  duty_visit_version_organization {
    uuid id PK
    uuid duty_visit_plan_version_id
    uuid organization_id
    bool is_primary
    varchar role_note
    text remarks
  }
  duty_visit_version_participant {
    uuid id PK
    uuid duty_visit_plan_version_id
    participant_certainty_enum certainty
    varchar rank_text_snapshot
    participant_role_enum participant_role
    int4 headcount
    text remarks
    varchar person_staff_identifier
    varchar rank_code
    uuid post_id
  }
  duty_visit_visit_category {
    uuid duty_visit_id PK
    varchar visit_category_code PK
    int4 sort_order
    timestamptz created_at
  }
  event_series {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    bool is_active
    text remarks
  }
  external_contact {
    uuid id PK
    varchar name_en
    varchar name_zh
    varchar rank_title_en
    varchar rank_title_zh
    uuid country_id
    uuid province_id
    uuid city_id
    uuid organization_id
    varchar office_phone
    varchar mobile_phone
    varchar email
    varchar whatsapp_number
    varchar wechat_id
    text notes
    bool is_active
    timestamptz created_at
    timestamptz updated_at
    varchar fsd_rank_code
  }
  external_contact_attachment {
    uuid id PK
    uuid external_contact_id
    external_contact_attachment_type_enum attachment_type
    varchar file_name
    text file_path
    timestamptz uploaded_at
    varchar uploaded_by
    text remarks
  }
  fiscal_year {
    uuid id PK
    varchar code
    date start_date
    date end_date
    bool is_active
  }
  fsd_unit {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    fsd_unit_type_enum unit_type
    int4 sort_order
    bool is_active
    text remarks
  }
  main_category {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
  }
  organization {
    uuid id PK
    varchar name_en
    varchar name_zh
    uuid country_id
    uuid province_id
    uuid city_id
    varchar general_phone
    varchar general_email
    varchar website
    bool is_active
    text remarks
    timestamptz created_at
    timestamptz updated_at
    varchar organization_type_code
    varchar abbr_en
    varchar abbr_zh
  }
  organization_contact {
    uuid id PK
    uuid organization_id
    varchar name_en
    varchar name_zh
    varchar rank_title_en
    varchar rank_title_zh
    varchar email
    varchar phone
    varchar whatsapp_number
    varchar wechat_id
    text contact_notes
    text how_we_contacted
    bool is_primary
    bool is_active
    timestamptz created_at
    timestamptz updated_at
  }
  organization_type {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
  }
  person {
    uuid id PK
    varchar staff_identifier
    varchar name_en
    varchar name_zh
    bool is_active
    text remarks
    timestamptz created_at
    timestamptz updated_at
    uuid post_id
  }
  person_rank_history {
    uuid id PK
    date effective_from
    date effective_to
    varchar post_title
    text remarks
    varchar person_staff_identifier
    varchar rank_code
  }
  post {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    varchar command_code
  }
  province {
    uuid id PK
    uuid country_id
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
    text remarks
  }
  rank {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
    varchar rank_group_code
  }
  rank_group {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
  }
  seed_city {
    varchar seed_id PK
    varchar source
    varchar country_iso_code2
    varchar province_seed_id
    varchar city_code
    int8 geoname_id
    varchar feature_code
    int8 population
    varchar name_en
    varchar name_zh_hant
    varchar zh_variant
    int4 sort_order
    bool is_active
    text remarks
  }
  seed_continent {
    varchar code PK
    varchar name_en
    varchar name_zh_hant
    int8 geoname_id
    int4 sort_order
    varchar source
    bool is_active
    text remarks
  }
  seed_country {
    varchar iso_code2 PK
    varchar iso_code3
    varchar iso_numeric
    varchar fips_code
    int8 geoname_id
    varchar continent_code
    varchar name_en
    varchar name_zh_hant
    varchar capital_name
    varchar zh_variant
    varchar source
    bool is_active
    text remarks
  }
  seed_dataset_sync {
    varchar dataset_code PK
    text source_url
    text source_last_modified
    timestamptz loaded_at
    int4 row_count
    text notes
  }
  seed_province {
    varchar seed_id PK
    varchar source
    varchar country_iso_code2
    varchar province_code
    int8 geoname_id
    varchar name_en
    varchar name_zh_hant
    varchar zh_variant
    int4 sort_order
    bool is_active
    text remarks
  }
  tag {
    uuid id PK
    uuid tag_group_id
    varchar name
    varchar color_hex
    int4 sort_order
    bool is_active
    timestamptz deleted_at
    timestamptz created_at
    timestamptz updated_at
  }
  tag_group {
    uuid id PK
    varchar name
    bool is_exclusive
    varchar color_hex
    int4 sort_order
    bool is_active
    timestamptz created_at
    timestamptz updated_at
  }
  visit_category {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
  }
  visit_direction {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
  }
  visit_list {
    uuid id PK
    varchar name
    int4 sort_order
    timestamptz created_at
    timestamptz updated_at
  }
  visit_list_item {
    uuid visit_list_id PK
    uuid duty_visit_id PK
    int4 sort_order
    timestamptz created_at
  }
  visit_status {
    uuid id PK
    varchar code
    varchar name_en
    varchar name_zh
    int4 sort_order
    bool is_active
  }
  visit_tag {
    uuid duty_visit_id PK
    uuid tag_id PK
    timestamptz created_at
  }
  allowance_city_rate }o--|| city : "city_id -> id"
  allowance_country_rate }o--|| country : "country_id -> id"
  annual_vote_budget }o--|| budget_vote : "budget_vote_code -> code"
  annual_vote_budget }o--|| fiscal_year : "fiscal_year_code -> code"
  budget_vote }o--|| main_category : "main_category_code -> code"
  city }o--|| country : "country_id -> id"
  city }o--|| province : "province_id -> id"
  country }o--|| continent : "continent_id -> id"
  country_association }o--|| association : "association_id -> id"
  country_association }o--|| country : "country_id -> id"
  duty_visit }o--|| event_series : "event_series_code -> code"
  duty_visit }o--|| fiscal_year : "fiscal_year_code -> code"
  duty_visit }o--|| main_category : "main_category_code -> code"
  duty_visit }o--|| visit_status : "status_code -> code"
  duty_visit }o--|| visit_category : "visit_category_code -> code"
  duty_visit }o--|| visit_direction : "visit_direction_code -> code"
  duty_visit_attachment }o--|| duty_visit : "duty_visit_id -> id"
  duty_visit_budget_allocation }o--|| annual_vote_budget : "annual_vote_budget_id -> id"
  duty_visit_budget_allocation }o--|| duty_visit : "duty_visit_id -> id"
  duty_visit_expenditure_item }o--|| budget_item_type : "budget_item_type_code -> code"
  duty_visit_expenditure_item }o--|| duty_visit_budget_allocation : "duty_visit_budget_allocation_id -> id"
  duty_visit_expenditure_item }o--|| duty_visit_plan_version : "duty_visit_plan_version_id -> id"
  duty_visit_external_contact }o--|| duty_visit : "duty_visit_id -> id"
  duty_visit_external_contact }o--|| external_contact : "external_contact_id -> id"
  duty_visit_external_contact }o--|| organization : "met_via_org_id -> id"
  duty_visit_itinerary_item }o--|| city : "city_id -> id"
  duty_visit_itinerary_item }o--|| country : "country_id -> id"
  duty_visit_itinerary_item }o--|| duty_visit_plan_version : "duty_visit_plan_version_id -> id"
  duty_visit_itinerary_item }o--|| organization : "organization_id -> id"
  duty_visit_itinerary_item }o--|| province : "province_id -> id"
  duty_visit_plan_version }o--|| duty_visit : "duty_visit_id -> id"
  duty_visit_plan_version }o--|| city : "host_city_id -> id"
  duty_visit_plan_version }o--|| continent : "host_continent_id -> id"
  duty_visit_plan_version }o--|| country : "host_country_id -> id"
  duty_visit_plan_version }o--|| province : "host_province_id -> id"
  duty_visit_unit }o--|| duty_visit : "duty_visit_id -> id"
  duty_visit_unit }o--|| fsd_unit : "fsd_unit_id -> id"
  duty_visit_version_location }o--|| city : "city_id -> id"
  duty_visit_version_location }o--|| country : "country_id -> id"
  duty_visit_version_location }o--|| duty_visit_plan_version : "duty_visit_plan_version_id -> id"
  duty_visit_version_location }o--|| province : "province_id -> id"
  duty_visit_version_organization }o--|| duty_visit_plan_version : "duty_visit_plan_version_id -> id"
  duty_visit_version_organization }o--|| organization : "organization_id -> id"
  duty_visit_version_participant }o--|| duty_visit_plan_version : "duty_visit_plan_version_id -> id"
  duty_visit_version_participant }o--|| person : "person_staff_identifier -> staff_identifier"
  duty_visit_version_participant }o--|| post : "post_id -> id"
  duty_visit_version_participant }o--|| rank : "rank_code -> code"
  duty_visit_visit_category }o--|| duty_visit : "duty_visit_id -> id"
  duty_visit_visit_category }o--|| visit_category : "visit_category_code -> code"
  external_contact }o--|| city : "city_id -> id"
  external_contact }o--|| country : "country_id -> id"
  external_contact }o--|| rank : "fsd_rank_code -> code"
  external_contact }o--|| organization : "organization_id -> id"
  external_contact }o--|| province : "province_id -> id"
  external_contact_attachment }o--|| external_contact : "external_contact_id -> id"
  organization }o--|| city : "city_id -> id"
  organization }o--|| country : "country_id -> id"
  organization }o--|| organization_type : "organization_type_code -> code"
  organization }o--|| province : "province_id -> id"
  organization_contact }o--|| organization : "organization_id -> id"
  person }o--|| post : "post_id -> id"
  person_rank_history }o--|| person : "person_staff_identifier -> staff_identifier"
  person_rank_history }o--|| rank : "rank_code -> code"
  post }o--|| command : "command_code -> code"
  province }o--|| country : "country_id -> id"
  rank }o--|| rank_group : "rank_group_code -> code"
  seed_city }o--|| seed_country : "country_iso_code2 -> iso_code2"
  seed_city }o--|| seed_province : "province_seed_id -> seed_id"
  seed_country }o--|| seed_continent : "continent_code -> code"
  seed_province }o--|| seed_country : "country_iso_code2 -> iso_code2"
  tag }o--|| tag_group : "tag_group_id -> id"
  visit_list_item }o--|| duty_visit : "duty_visit_id -> id"
  visit_list_item }o--|| visit_list : "visit_list_id -> id"
  visit_tag }o--|| duty_visit : "duty_visit_id -> id"
  visit_tag }o--|| tag : "tag_id -> id"

Foreign key list

Source Target Constraint
allowance_city_rate.city_id city.id allowance_city_rate_city_id_fkey
allowance_country_rate.country_id country.id allowance_country_rate_country_id_fkey
annual_vote_budget.budget_vote_code budget_vote.code annual_vote_budget_budget_vote_code_fkey
annual_vote_budget.fiscal_year_code fiscal_year.code annual_vote_budget_fiscal_year_code_fkey
budget_vote.main_category_code main_category.code budget_vote_main_category_code_fkey
city.country_id country.id city_country_id_fkey
city.province_id province.id city_province_id_fkey
country.continent_id continent.id country_continent_id_fkey
country_association.association_id association.id country_association_association_id_fkey
country_association.country_id country.id country_association_country_id_fkey
duty_visit.event_series_code event_series.code duty_visit_event_series_code_fkey
duty_visit.fiscal_year_code fiscal_year.code duty_visit_fiscal_year_code_fkey
duty_visit.main_category_code main_category.code duty_visit_main_category_code_fkey
duty_visit.status_code visit_status.code duty_visit_status_code_fkey
duty_visit.visit_category_code visit_category.code duty_visit_visit_category_code_fkey
duty_visit.visit_direction_code visit_direction.code duty_visit_visit_direction_code_fkey
duty_visit_attachment.duty_visit_id duty_visit.id duty_visit_attachment_duty_visit_id_fkey
duty_visit_budget_allocation.annual_vote_budget_id annual_vote_budget.id duty_visit_budget_allocation_annual_vote_budget_id_fkey
duty_visit_budget_allocation.duty_visit_id duty_visit.id duty_visit_budget_allocation_duty_visit_id_fkey
duty_visit_expenditure_item.budget_item_type_code budget_item_type.code duty_visit_expenditure_item_budget_item_type_code_fkey
duty_visit_expenditure_item.duty_visit_budget_allocation_id duty_visit_budget_allocation.id duty_visit_expenditure_item_duty_visit_budget_allocation_i_fkey
duty_visit_expenditure_item.duty_visit_plan_version_id duty_visit_plan_version.id duty_visit_expenditure_item_duty_visit_plan_version_id_fkey
duty_visit_external_contact.duty_visit_id duty_visit.id duty_visit_external_contact_duty_visit_id_fkey
duty_visit_external_contact.external_contact_id external_contact.id duty_visit_external_contact_external_contact_id_fkey
duty_visit_external_contact.met_via_org_id organization.id duty_visit_external_contact_met_via_org_id_fkey
duty_visit_itinerary_item.city_id city.id duty_visit_itinerary_item_city_id_fkey
duty_visit_itinerary_item.country_id country.id duty_visit_itinerary_item_country_id_fkey
duty_visit_itinerary_item.duty_visit_plan_version_id duty_visit_plan_version.id duty_visit_itinerary_item_duty_visit_plan_version_id_fkey
duty_visit_itinerary_item.organization_id organization.id duty_visit_itinerary_item_organization_id_fkey
duty_visit_itinerary_item.province_id province.id duty_visit_itinerary_item_province_id_fkey
duty_visit_plan_version.duty_visit_id duty_visit.id duty_visit_plan_version_duty_visit_id_fkey
duty_visit_plan_version.host_city_id city.id duty_visit_plan_version_host_city_id_fkey
duty_visit_plan_version.host_continent_id continent.id fk_dvpv_host_continent
duty_visit_plan_version.host_country_id country.id duty_visit_plan_version_host_country_id_fkey
duty_visit_plan_version.host_province_id province.id duty_visit_plan_version_host_province_id_fkey
duty_visit_unit.duty_visit_id duty_visit.id duty_visit_unit_duty_visit_id_fkey
duty_visit_unit.fsd_unit_id fsd_unit.id duty_visit_unit_fsd_unit_id_fkey
duty_visit_version_location.city_id city.id duty_visit_version_location_city_id_fkey
duty_visit_version_location.country_id country.id duty_visit_version_location_country_id_fkey
duty_visit_version_location.duty_visit_plan_version_id duty_visit_plan_version.id duty_visit_version_location_duty_visit_plan_version_id_fkey
duty_visit_version_location.province_id province.id duty_visit_version_location_province_id_fkey
duty_visit_version_organization.duty_visit_plan_version_id duty_visit_plan_version.id duty_visit_version_organization_duty_visit_plan_version_id_fkey
duty_visit_version_organization.organization_id organization.id duty_visit_version_organization_organization_id_fkey
duty_visit_version_participant.duty_visit_plan_version_id duty_visit_plan_version.id duty_visit_version_participant_duty_visit_plan_version_id_fkey
duty_visit_version_participant.person_staff_identifier person.staff_identifier duty_visit_version_participant_person_staff_identifier_fkey
duty_visit_version_participant.post_id post.id duty_visit_version_participant_post_id_fkey
duty_visit_version_participant.rank_code rank.code duty_visit_version_participant_rank_code_fkey
duty_visit_visit_category.duty_visit_id duty_visit.id duty_visit_visit_category_duty_visit_id_fkey
duty_visit_visit_category.visit_category_code visit_category.code duty_visit_visit_category_visit_category_code_fkey
external_contact.city_id city.id external_contact_city_id_fkey
external_contact.country_id country.id external_contact_country_id_fkey
external_contact.fsd_rank_code rank.code external_contact_fsd_rank_code_fkey
external_contact.organization_id organization.id external_contact_organization_id_fkey
external_contact.province_id province.id external_contact_province_id_fkey
external_contact_attachment.external_contact_id external_contact.id external_contact_attachment_external_contact_id_fkey
organization.city_id city.id organization_city_id_fkey
organization.country_id country.id organization_country_id_fkey
organization.organization_type_code organization_type.code organization_organization_type_code_fkey
organization.province_id province.id organization_province_id_fkey
organization_contact.organization_id organization.id organization_contact_organization_id_fkey
person.post_id post.id person_post_id_fkey
person_rank_history.person_staff_identifier person.staff_identifier person_rank_history_person_staff_identifier_fkey
person_rank_history.rank_code rank.code person_rank_history_rank_code_fkey
post.command_code command.code post_command_code_fkey
province.country_id country.id province_country_id_fkey
rank.rank_group_code rank_group.code rank_rank_group_code_fkey
seed_city.country_iso_code2 seed_country.iso_code2 seed_city_country_iso_code2_fkey
seed_city.province_seed_id seed_province.seed_id seed_city_province_seed_id_fkey
seed_country.continent_code seed_continent.code seed_country_continent_code_fkey
seed_province.country_iso_code2 seed_country.iso_code2 seed_province_country_iso_code2_fkey
tag.tag_group_id tag_group.id tag_tag_group_id_fkey
visit_list_item.duty_visit_id duty_visit.id visit_list_item_duty_visit_id_fkey
visit_list_item.visit_list_id visit_list.id visit_list_item_visit_list_id_fkey
visit_tag.duty_visit_id duty_visit.id visit_tag_duty_visit_id_fkey
visit_tag.tag_id tag.id visit_tag_tag_id_fkey
DBML-like text 
Table alembic_version {
  version_num varchar [pk]
}

Table allowance_city_rate {
  id uuid [pk]
  city_id uuid
  rate_hkd numeric
  effective_from date
  effective_to date
  remarks text
}

Table allowance_country_rate {
  id uuid [pk]
  country_id uuid
  rate_hkd numeric
  effective_from date
  effective_to date
  remarks text
}

Table annual_vote_budget {
  id uuid [pk]
  approved_amount numeric
  approval_ref varchar
  remarks text
  fiscal_year_code varchar
  budget_vote_code varchar
}

Table association {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
  remarks text
  abbr_en varchar
  abbr_zh varchar
}

Table budget_item_type {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
}

Table budget_vote {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
  remarks text
  main_category_code varchar
}

Table city {
  id uuid [pk]
  country_id uuid
  province_id uuid
  name_en varchar
  name_zh varchar
  is_active bool
  remarks text
}

Table command {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
}

Table continent {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
}

Table country {
  id uuid [pk]
  iso_code varchar
  name_en varchar
  name_zh varchar
  continent_id uuid
  is_active bool
  remarks text
}

Table country_association {
  id uuid [pk]
  country_id uuid
  association_id uuid
  remarks text
}

Table duty_visit {
  id uuid [pk]
  visit_code varchar
  title_en varchar
  title_zh varchar
  report_required bool
  report_due_date date
  report_submitted_date date
  report_completed bool
  presentation_required bool
  presentation_due_date date
  presentation_completed_date date
  presentation_completed bool
  remarks text
  created_at timestamptz
  updated_at timestamptz
  visit_category_code varchar
  main_category_code varchar
  visit_direction_code varchar
  status_code varchar
  fiscal_year_code varchar
  event_series_code varchar
  stream duty_visit_stream_enum
  budget_annual_amount numeric
  estimation_before_departure_amount numeric
  actual_completion_amount numeric
  budget_annual_calc jsonb
  estimation_before_departure_calc jsonb
  actual_completion_calc jsonb
  actual_expenditure_items jsonb
  budget_vote_code varchar
}

Table duty_visit_attachment {
  id uuid [pk]
  duty_visit_id uuid
  attachment_type visit_attachment_type_enum
  file_name varchar
  file_path text
  uploaded_at timestamptz
  uploaded_by varchar
  remarks text
}

Table duty_visit_budget_allocation {
  id uuid [pk]
  duty_visit_id uuid
  annual_vote_budget_id uuid
  allocated_amount numeric
  remarks text
}

Table duty_visit_expenditure_item {
  id uuid [pk]
  duty_visit_plan_version_id uuid
  duty_visit_budget_allocation_id uuid
  expenditure_stage expenditure_stage_enum
  amount numeric
  currency_code varchar
  remarks text
  budget_item_type_code varchar
}

Table duty_visit_external_contact {
  id uuid [pk]
  duty_visit_id uuid
  external_contact_id uuid
  contact_role contact_role_enum
  met_via_org_id uuid
  remarks text
}

Table duty_visit_itinerary_item {
  id uuid [pk]
  duty_visit_plan_version_id uuid
  sequence_no int4
  start_datetime timestamptz
  end_datetime timestamptz
  country_id uuid
  province_id uuid
  city_id uuid
  organization_id uuid
  title varchar
  remarks text
}

Table duty_visit_plan_version {
  id uuid [pk]
  duty_visit_id uuid
  version_type plan_version_type_enum
  start_datetime timestamptz
  end_datetime timestamptz
  host_country_id uuid
  host_province_id uuid
  host_city_id uuid
  planned_headcount int4
  purpose text
  notes text
  created_at timestamptz
  updated_at timestamptz
  visit_location text
  planned_date_mode varchar
  planned_year int4
  planned_month int4
  planned_quarter int4
  location_to_be_confirmed bool
  host_continent_id uuid
}

Table duty_visit_unit {
  id uuid [pk]
  duty_visit_id uuid
  fsd_unit_id uuid
  remarks text
}

Table duty_visit_version_location {
  id uuid [pk]
  duty_visit_plan_version_id uuid
  sequence_no int4
  country_id uuid
  province_id uuid
  city_id uuid
  is_primary bool
  remarks text
}

Table duty_visit_version_organization {
  id uuid [pk]
  duty_visit_plan_version_id uuid
  organization_id uuid
  is_primary bool
  role_note varchar
  remarks text
}

Table duty_visit_version_participant {
  id uuid [pk]
  duty_visit_plan_version_id uuid
  certainty participant_certainty_enum
  rank_text_snapshot varchar
  participant_role participant_role_enum
  headcount int4
  remarks text
  person_staff_identifier varchar
  rank_code varchar
  post_id uuid
}

Table duty_visit_visit_category {
  duty_visit_id uuid [pk]
  visit_category_code varchar [pk]
  sort_order int4
  created_at timestamptz
}

Table event_series {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  is_active bool
  remarks text
}

Table external_contact {
  id uuid [pk]
  name_en varchar
  name_zh varchar
  rank_title_en varchar
  rank_title_zh varchar
  country_id uuid
  province_id uuid
  city_id uuid
  organization_id uuid
  office_phone varchar
  mobile_phone varchar
  email varchar
  whatsapp_number varchar
  wechat_id varchar
  notes text
  is_active bool
  created_at timestamptz
  updated_at timestamptz
  fsd_rank_code varchar
}

Table external_contact_attachment {
  id uuid [pk]
  external_contact_id uuid
  attachment_type external_contact_attachment_type_enum
  file_name varchar
  file_path text
  uploaded_at timestamptz
  uploaded_by varchar
  remarks text
}

Table fiscal_year {
  id uuid [pk]
  code varchar
  start_date date
  end_date date
  is_active bool
}

Table fsd_unit {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  unit_type fsd_unit_type_enum
  sort_order int4
  is_active bool
  remarks text
}

Table main_category {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
}

Table organization {
  id uuid [pk]
  name_en varchar
  name_zh varchar
  country_id uuid
  province_id uuid
  city_id uuid
  general_phone varchar
  general_email varchar
  website varchar
  is_active bool
  remarks text
  created_at timestamptz
  updated_at timestamptz
  organization_type_code varchar
  abbr_en varchar
  abbr_zh varchar
}

Table organization_contact {
  id uuid [pk]
  organization_id uuid
  name_en varchar
  name_zh varchar
  rank_title_en varchar
  rank_title_zh varchar
  email varchar
  phone varchar
  whatsapp_number varchar
  wechat_id varchar
  contact_notes text
  how_we_contacted text
  is_primary bool
  is_active bool
  created_at timestamptz
  updated_at timestamptz
}

Table organization_type {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
}

Table person {
  id uuid [pk]
  staff_identifier varchar
  name_en varchar
  name_zh varchar
  is_active bool
  remarks text
  created_at timestamptz
  updated_at timestamptz
  post_id uuid
}

Table person_rank_history {
  id uuid [pk]
  effective_from date
  effective_to date
  post_title varchar
  remarks text
  person_staff_identifier varchar
  rank_code varchar
}

Table post {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  command_code varchar
}

Table province {
  id uuid [pk]
  country_id uuid
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
  remarks text
}

Table rank {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
  rank_group_code varchar
}

Table rank_group {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
}

Table seed_city {
  seed_id varchar [pk]
  source varchar
  country_iso_code2 varchar
  province_seed_id varchar
  city_code varchar
  geoname_id int8
  feature_code varchar
  population int8
  name_en varchar
  name_zh_hant varchar
  zh_variant varchar
  sort_order int4
  is_active bool
  remarks text
}

Table seed_continent {
  code varchar [pk]
  name_en varchar
  name_zh_hant varchar
  geoname_id int8
  sort_order int4
  source varchar
  is_active bool
  remarks text
}

Table seed_country {
  iso_code2 varchar [pk]
  iso_code3 varchar
  iso_numeric varchar
  fips_code varchar
  geoname_id int8
  continent_code varchar
  name_en varchar
  name_zh_hant varchar
  capital_name varchar
  zh_variant varchar
  source varchar
  is_active bool
  remarks text
}

Table seed_dataset_sync {
  dataset_code varchar [pk]
  source_url text
  source_last_modified text
  loaded_at timestamptz
  row_count int4
  notes text
}

Table seed_province {
  seed_id varchar [pk]
  source varchar
  country_iso_code2 varchar
  province_code varchar
  geoname_id int8
  name_en varchar
  name_zh_hant varchar
  zh_variant varchar
  sort_order int4
  is_active bool
  remarks text
}

Table tag {
  id uuid [pk]
  tag_group_id uuid
  name varchar
  color_hex varchar
  sort_order int4
  is_active bool
  deleted_at timestamptz
  created_at timestamptz
  updated_at timestamptz
}

Table tag_group {
  id uuid [pk]
  name varchar
  is_exclusive bool
  color_hex varchar
  sort_order int4
  is_active bool
  created_at timestamptz
  updated_at timestamptz
}

Table visit_category {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
}

Table visit_direction {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
}

Table visit_list {
  id uuid [pk]
  name varchar
  sort_order int4
  created_at timestamptz
  updated_at timestamptz
}

Table visit_list_item {
  visit_list_id uuid [pk]
  duty_visit_id uuid [pk]
  sort_order int4
  created_at timestamptz
}

Table visit_status {
  id uuid [pk]
  code varchar
  name_en varchar
  name_zh varchar
  sort_order int4
  is_active bool
}

Table visit_tag {
  duty_visit_id uuid [pk]
  tag_id uuid [pk]
  created_at timestamptz
}

Ref: allowance_city_rate.city_id > city.id
Ref: allowance_country_rate.country_id > country.id
Ref: annual_vote_budget.budget_vote_code > budget_vote.code
Ref: annual_vote_budget.fiscal_year_code > fiscal_year.code
Ref: budget_vote.main_category_code > main_category.code
Ref: city.country_id > country.id
Ref: city.province_id > province.id
Ref: country.continent_id > continent.id
Ref: country_association.association_id > association.id
Ref: country_association.country_id > country.id
Ref: duty_visit.event_series_code > event_series.code
Ref: duty_visit.fiscal_year_code > fiscal_year.code
Ref: duty_visit.main_category_code > main_category.code
Ref: duty_visit.status_code > visit_status.code
Ref: duty_visit.visit_category_code > visit_category.code
Ref: duty_visit.visit_direction_code > visit_direction.code
Ref: duty_visit_attachment.duty_visit_id > duty_visit.id
Ref: duty_visit_budget_allocation.annual_vote_budget_id > annual_vote_budget.id
Ref: duty_visit_budget_allocation.duty_visit_id > duty_visit.id
Ref: duty_visit_expenditure_item.budget_item_type_code > budget_item_type.code
Ref: duty_visit_expenditure_item.duty_visit_budget_allocation_id > duty_visit_budget_allocation.id
Ref: duty_visit_expenditure_item.duty_visit_plan_version_id > duty_visit_plan_version.id
Ref: duty_visit_external_contact.duty_visit_id > duty_visit.id
Ref: duty_visit_external_contact.external_contact_id > external_contact.id
Ref: duty_visit_external_contact.met_via_org_id > organization.id
Ref: duty_visit_itinerary_item.city_id > city.id
Ref: duty_visit_itinerary_item.country_id > country.id
Ref: duty_visit_itinerary_item.duty_visit_plan_version_id > duty_visit_plan_version.id
Ref: duty_visit_itinerary_item.organization_id > organization.id
Ref: duty_visit_itinerary_item.province_id > province.id
Ref: duty_visit_plan_version.duty_visit_id > duty_visit.id
Ref: duty_visit_plan_version.host_city_id > city.id
Ref: duty_visit_plan_version.host_continent_id > continent.id
Ref: duty_visit_plan_version.host_country_id > country.id
Ref: duty_visit_plan_version.host_province_id > province.id
Ref: duty_visit_unit.duty_visit_id > duty_visit.id
Ref: duty_visit_unit.fsd_unit_id > fsd_unit.id
Ref: duty_visit_version_location.city_id > city.id
Ref: duty_visit_version_location.country_id > country.id
Ref: duty_visit_version_location.duty_visit_plan_version_id > duty_visit_plan_version.id
Ref: duty_visit_version_location.province_id > province.id
Ref: duty_visit_version_organization.duty_visit_plan_version_id > duty_visit_plan_version.id
Ref: duty_visit_version_organization.organization_id > organization.id
Ref: duty_visit_version_participant.duty_visit_plan_version_id > duty_visit_plan_version.id
Ref: duty_visit_version_participant.person_staff_identifier > person.staff_identifier
Ref: duty_visit_version_participant.post_id > post.id
Ref: duty_visit_version_participant.rank_code > rank.code
Ref: duty_visit_visit_category.duty_visit_id > duty_visit.id
Ref: duty_visit_visit_category.visit_category_code > visit_category.code
Ref: external_contact.city_id > city.id
Ref: external_contact.country_id > country.id
Ref: external_contact.fsd_rank_code > rank.code
Ref: external_contact.organization_id > organization.id
Ref: external_contact.province_id > province.id
Ref: external_contact_attachment.external_contact_id > external_contact.id
Ref: organization.city_id > city.id
Ref: organization.country_id > country.id
Ref: organization.organization_type_code > organization_type.code
Ref: organization.province_id > province.id
Ref: organization_contact.organization_id > organization.id
Ref: person.post_id > post.id
Ref: person_rank_history.person_staff_identifier > person.staff_identifier
Ref: person_rank_history.rank_code > rank.code
Ref: post.command_code > command.code
Ref: province.country_id > country.id
Ref: rank.rank_group_code > rank_group.code
Ref: seed_city.country_iso_code2 > seed_country.iso_code2
Ref: seed_city.province_seed_id > seed_province.seed_id
Ref: seed_country.continent_code > seed_continent.code
Ref: seed_province.country_iso_code2 > seed_country.iso_code2
Ref: tag.tag_group_id > tag_group.id
Ref: visit_list_item.duty_visit_id > duty_visit.id
Ref: visit_list_item.visit_list_id > visit_list.id
Ref: visit_tag.duty_visit_id > duty_visit.id
Ref: visit_tag.tag_id > tag.id