# Unified Locator - Entity Relationship Diagrams

Mermaid sources; render natively on GitHub/GitLab, Confluence (Mermaid macro), Notion, Obsidian, or https://mermaid.live. Companion to the Google Sheet tab `locator-data-spec` and `Unified-Locator-data-spec-v2.xlsx`.

Notation: `||` exactly one, `|o` zero or one, `}o` zero or more, `}|` one or more. `PK`/`FK`/`UK` = primary / foreign / unique key; the quoted comment says what the locator uses the column for. Tier 1 = November 2026; Tier 2 (section 7) = free-text search phase.

## The model in one picture

Twelve plain-named concepts and how they connect. A shop teaches courses (the shop x course pair is the course card), sells products with priced bookable days and extras, carries tags, sits in the place hierarchy, may be linked to a Google rating.

```mermaid
erDiagram
  DIVE_SHOP ||--o{ SHOP_COURSE_PAIR : "teaches (offered or bookable)"
  COURSE ||--o{ SHOP_COURSE_PAIR : "catalogue entry of"
  DIVE_SHOP }o--o{ TAG : "carries tags (tier, awards, facilities, services, languages)"
  DIVE_SHOP }o--o| GOOGLE_RATING : "linked Google Business Profile"
  DIVE_SHOP }o--o| LOCATION : "reference location"
  LOCATION }|--|| AREA : "in"
  AREA }|--|| COUNTRY : "in"
  LOCATION }o--o{ REGION : "belongs to (also via area, country)"
  DIVE_SHOP ||--o{ PRODUCT : "sells"
  PRODUCT }o--o| COURSE : "is the shop version of"
  PRODUCT ||--o{ BOOKABLE_DAY : "priced dates"
  PRODUCT ||--o{ EXTRA : "inclusions and mandatory extras"
  DIVE_SHOP {
    int id PK
    bool is_active "eligibility"
    geo lat_lon "map pin"
    text title "card"
  }
  TAG {
    int id PK
    text group "which filter"
    text title "label in 10 languages"
  }
  GOOGLE_RATING {
    text place_id PK
    float rating
    int review_count
  }
  LOCATION {
    int id PK
    text title "10 languages"
  }
  AREA {
    int id PK
  }
  COUNTRY {
    int id PK
    text code "ISO, display rules"
  }
  REGION {
    int id PK
  }
  COURSE {
    int id PK
    text standard_name "card"
    int type "activity"
    int category "experience level"
    int popularity_order "business order"
  }
  SHOP_COURSE_PAIR {
    int id PK "course document key"
    bool is_offered
    bool is_elearning
  }
  PRODUCT {
    int id PK
    int status "approved"
    arr shown_on "Adventures"
    int duration_hours
    int ratio "group size"
    int cancellation_policy
  }
  BOOKABLE_DAY {
    date date
    num price
    num price_usd
    ts available_before
  }
  EXTRA {
    int price_policy "included etc"
    bool payable_on_site "mandatory"
    num price
  }
```

## Shop core, contact and hours

`shop_shop` and its 1:1 tables; opening hours are a parent/child pair; closures optional.

```mermaid
erDiagram
  shop_shop ||--|| shop_shopinfo : "shop_id"
  shop_shop ||--|| shop_bookinginfo : "shop_id"
  shop_shop ||--o| shop_resortinformation : "shop_id (resorts only)"
  shop_shop ||--o{ shop_openinghour : "one row per weekday"
  shop_openinghour ||--o{ shop_openinghourtime : "opening_hour_id"
  shop_shop ||--o{ shop_closedperiod : "holiday closures (optional)"
  shop_closedperiod ||--o{ shop_closeddate : "period_id"
  shop_shop {
    int id PK
    bool is_active "gate 1"
    bool is_on_dsl "gate 2"
    smallint kind "gate 3, stored"
    text title "card"
    text self_slug "profile URL"
    float8 latitude "geo_point"
    float8 longitude "geo_point"
    int reference_location_id FK "places_location"
    int country_id FK "fallback country"
    text city "card"
    text state "province"
    text picture_background "card photo"
    text web_site "card"
    text socials "9 columns"
    varchar3 currency "FX base"
    text timezone_id "open now"
    text google_place_id FK "reviews_googleplace"
    int dsl_points "profile_complete"
    smallint membership_level "audit only"
  }
  shop_shopinfo {
    int shop_id FK
    varchar10 affiliate_id UK "store number"
    smallint padi_last_renew_year "eligibility"
    bool no_index "SEO flag"
  }
  shop_bookinginfo {
    int shop_id FK
    text phone "card"
    text email "card"
    text booking_contact_phone "fallback"
    arr booking_contact_email "fallback"
  }
  shop_resortinformation {
    int shop_id FK
    smallint resort_kind "eligibility if 7 kinds"
  }
  shop_openinghour {
    int id PK
    int shop_id FK
    smallint weekday "1 Mon to 7 Sun"
    bool is_closed
    bool is_contactable
  }
  shop_openinghourtime {
    int opening_hour_id FK
    time time_start
    time time_end
  }
  shop_closedperiod {
    int id PK
    int shop_id FK
    text title
  }
  shop_closeddate {
    int period_id FK
    date date_from
    date date_to
  }
```

## Tags, membership tiers, awards, Google rating

The three-table tag vocabulary (`shop_parametergroup` -> `shop_parameter` <- `shop_shop_parameters`), logos, and `reviews_googleplace`.

```mermaid
erDiagram
  shop_shop ||--o{ shop_shop_parameters : "shop_id"
  shop_parameter ||--o{ shop_shop_parameters : "parameter_id"
  shop_parametergroup ||--o{ shop_parameter : "group_id"
  common_image |o--o{ shop_parameter : "image_id (tier and award logos)"
  reviews_googleplace |o--o{ shop_shop : "google_place_id"
  shop_shop {
    int id PK
    text google_place_id FK
  }
  shop_shop_parameters {
    int shop_id FK
    int parameter_id FK
  }
  shop_parameter {
    int id PK "constants for tiers and awards"
    text title "10 languages, labels"
    int group_id FK
    int image_id FK
    bool is_public "false = competitor tags"
    bool is_active
    smallint order "display order"
  }
  shop_parametergroup {
    int id PK "differs per environment"
    text code_name "routing key"
    bool is_active
  }
  common_image {
    int id PK
    text image "path to CDN URL"
    int width
    int height
  }
  reviews_googleplace {
    text place_id PK
    float8 rating "card, ordering"
    int user_rating_count "ordering"
    ts updated "freshness"
  }
```

## Places - the geography spine

Location -> area -> country, regions and continents attached through six link tables (named on the lines); country -> default currency.

```mermaid
erDiagram
  shop_shop }o--o| places_location : "reference_location_id"
  places_location }|--|| places_area : "area_id"
  places_location }|--|| places_country : "_country_id"
  places_area }|--|| places_country : "country_id"
  places_location }o--o{ places_region : "places_location__regions"
  places_area }o--o{ places_region : "places_area__regions"
  places_country }o--o{ places_region : "places_country__regions"
  places_location }o--o{ places_continent : "places_location__continents"
  places_area }o--o{ places_continent : "places_area__continents"
  places_country }o--o{ places_continent : "places_country__continents"
  places_country ||--o| places_countrycurrency : "default currency (API)"
  shop_shop {
    int id PK
    int reference_location_id FK
  }
  places_location {
    int id PK "scope key"
    text title "10 languages, card"
    text slug "10 languages, URL"
    int area_id FK
    int country_id FK "column is _country_id"
    float8 latitude "centroid"
    float8 longitude "centroid"
    bool is_autosuggest "lookup"
    bool is_active
    bool is_on_map
  }
  places_area {
    int id PK "scope key"
    text title "10 languages"
    text slug "Adventures URLs"
    int country_id FK
    float8 latitude
    float8 longitude
    bool is_autosuggest
  }
  places_country {
    int id PK "scope key"
    text title "10 languages, card"
    text title_lc "native name"
    varchar2 code "ISO, display rules"
    text slug
    bool is_autosuggest
  }
  places_region {
    int id PK "region_ids"
    text title "10 languages"
    text slug
    bool is_autosuggest
  }
  places_continent {
    int id PK "continent_ids"
    text title "10 languages"
    bool is_autosuggest
  }
  places_countrycurrency {
    int country_id FK
    varchar3 currency
  }
```

## Course catalogue and shop x course pairs

Catalogue, standard names, eLearning price list, course tags, and the pair row that becomes a course document.

```mermaid
erDiagram
  shop_shop ||--o{ booking_shopdivecourse : "shop_id"
  booking_divecourseinfo ||--o{ booking_shopdivecourse : "dive_course_info_id"
  booking_engine_productstandardname |o--o{ booking_divecourseinfo : "standard_name_id"
  booking_divecourseinfo ||--o{ booking_divecourseprice : "eLearning B2C prices"
  booking_divecourseinfo ||--o{ booking_divecourseimage : "catalogue photos"
  booking_divecourseinfo }o--o{ booking_divecoursetag : "booking_divecourseinfo_tags"
  places_country |o--o{ booking_divecourseprice : "country_id (optional)"
  shop_shop {
    int id PK
  }
  booking_shopdivecourse {
    int id PK "course document key"
    int shop_id FK
    int dive_course_info_id FK
    bool is_offered "contact shop mode"
    bool is_elearning "elearning mode"
    bool on_padi_com "elearning mode"
  }
  booking_divecourseinfo {
    int id PK "course_id"
    text title "10 languages, label"
    text slug "URL"
    smallint type "activity type"
    smallint category "experience level"
    bool is_active "eligibility"
    bool is_elearning "mode cap"
    bool on_padi_com "mode cap"
    smallint popularity_order "business order"
    smallint order "fallback order"
    int standard_name_id FK
    text padi_url "Buy eLearning CTA"
  }
  booking_engine_productstandardname {
    int id PK "standard_name_id"
    text title "10 languages, card"
    smallint order "top-4 pick"
    smallint kind "course kind"
  }
  booking_divecourseprice {
    int dive_course_info_id FK
    varchar3 currency
    num price "eLearning cost"
    int country_id FK
  }
  booking_divecourseimage {
    int dive_course_info_id FK
    text image "fallback photo"
  }
  booking_divecoursetag {
    int id PK
    text title "7 tags today"
    text slug
    bool is_on_search
  }
  places_country {
    int id PK
  }
```

## Products, pricing, inclusions

Product -> Adventures details (1:1) -> bookable days, extras -> generic inclusion codes, teaching languages.

```mermaid
erDiagram
  shop_shop ||--o{ booking_diveproduct : "shop_id"
  booking_divecourseinfo |o--o{ booking_diveproduct : "course_info_id"
  booking_engine_productstandardname |o--o{ booking_diveproduct : "standard_name_id"
  booking_diveproduct ||--o{ booking_productimage : "dive_product_id"
  booking_diveproduct ||--|| diving_activityproduct : "dive_product_id (1:1)"
  diving_activityproduct }o--o{ shop_parameter : "diving_activityproduct_staff_languages"
  diving_activityproduct ||--o{ diving_activityonedaypricing : "one row per bookable day"
  diving_activityproduct ||--o{ diving_activitypricing : "date-range variant (verify)"
  diving_activityproduct ||--o{ diving_activityextraoption : "inclusions and extras"
  diving_activityextraoption }o--o| diving_standardproductactivityinclusion : "based_on_generic_id"
  diving_standardproductactivityinclusion }o--o| diving_genericactivityextraoption : "based_on_generic_id"
  diving_genericactivityextraoption |o--o{ diving_genericactivityextraoption : "included_in_id (nesting)"
  booking_diveproduct {
    int id PK "product_id"
    int shop_id FK
    int course_info_id FK
    int standard_name_id FK
    smallint status "30 = approved"
    arr shown_on "must contain 20 Adventures"
    text title "10 languages, card"
    bool is_deleted "column is _is_deleted"
  }
  booking_productimage {
    int dive_product_id FK
    text picture "card photo"
    bool is_cover
    int position
  }
  diving_activityproduct {
    int id PK "activity_id"
    int dive_product_id FK
    smallint duration "hours, 7 bands"
    smallint duration_presentation_type
    smallint number_of_dives "card"
    smallint number_of_confined_dives
    smallint ratio "group size bands"
    smallint cancellation_policy "filter, card"
    bool is_elearning "inclusions input"
    text external_url "booking link"
  }
  diving_activityonedaypricing {
    int activity_id FK
    date date "Dates filter"
    num price "card"
    num converted_price "USD, price filter"
    varchar3 currency
    ts available_before "eligibility"
    smallint status
  }
  diving_activitypricing {
    int activity_id FK
    daterange date "verify usage"
    num price
    num converted_price
  }
  diving_activityextraoption {
    int activity_id FK
    int based_on_generic_id FK
    smallint price_policy "8 phrases"
    num price "mandatory extras"
    num price_converted "USD"
    bool payable_on_site "mandatory"
  }
  diving_standardproductactivityinclusion {
    int id PK
    int based_on_generic_id FK
  }
  diving_genericactivityextraoption {
    int id PK
    text code "eLearning, classroom, equipment"
    text title "10 languages"
    int included_in_id FK
  }
  shop_parameter {
    int id PK "languages_spoken rows"
  }
```

## Tier 2 - marine life and dive sites

Captured by CDC from day one, indexed in the free-text phase.

```mermaid
erDiagram
  shop_shop ||--o{ shop_shop_marine_life : "shop_id"
  marine_life2_marinelifecritter ||--o{ shop_shop_marine_life : "marinelifecritter_id"
  marine_life2_marinelifegroup ||--o{ marine_life2_marinelifecritter : "group_id"
  marine_life2_marinelifekind ||--o{ marine_life2_marinelifegroup : "kind_id"
  shop_shop ||--o{ shop_shop_adventure_dive_sites : "shop_id"
  places_adventuredivesite ||--o{ shop_shop_adventure_dive_sites : "adventuredivesite_id"
  diving_activityproduct ||--o{ diving_activityproduct_sites : "activityproduct_id"
  places_adventuredivesite ||--o{ diving_activityproduct_sites : "adventuredivesite_id"
  places_location |o--o{ places_adventuredivesite : "reference_location_id"
  shop_shop {
    int id PK
  }
  shop_shop_marine_life {
    int shop_id FK
    int marinelifecritter_id FK
  }
  marine_life2_marinelifecritter {
    int id PK "critter_ids"
    text title "10 languages, search"
    int group_id FK
    bool is_active
  }
  marine_life2_marinelifegroup {
    int id PK "marine_group_ids"
    text title "Sharks and Rays"
    int kind_id FK
  }
  marine_life2_marinelifekind {
    int id PK
    text title "Fish, Mammals"
  }
  shop_shop_adventure_dive_sites {
    int shop_id FK
    int adventuredivesite_id FK
  }
  places_adventuredivesite {
    int id PK "dive_site_ids"
    text title "10 languages, search"
    smallint status "approved only"
    bool is_private "exclude"
    float8 latitude
    float8 longitude
    int reference_location_id FK
  }
  diving_activityproduct {
    int id PK
  }
  diving_activityproduct_sites {
    int activityproduct_id FK
    int adventuredivesite_id FK
  }
  places_location {
    int id PK
  }
```

## The three OpenSearch documents

Shop document (with nested bookable experiences), course document (copies shop fields), places document.

```mermaid
erDiagram
  SHOP_DOCUMENT ||--o{ BOOKABLE_EXPERIENCE : "nested: every bookable product, card shows 4"
  COURSE_DOCUMENT }o--|| SHOP_DOCUMENT : "shop_id plus copied shop fields"
  SHOP_DOCUMENT }o--o| PLACES_DOCUMENT : "location_id, area_id, country_id, region_ids, continent_ids"
  COURSE_DOCUMENT }o--o| PLACES_DOCUMENT : "same scope ids"
  SHOP_DOCUMENT {
    int shop_id PK
    geo location "viewport, radius, clusters"
    int location_id "scope"
    arr region_ids "scope"
    int membership_tier "1 to 5, from tags"
    float review_score "rating x count"
    bool promoted "reserved slot"
    bool profile_complete "optional"
    arr activity_type_ids "filter"
    arr service_ids "filter"
    arr facility_ids "filter"
    arr language_ids "filter"
    arr award_ids "filter"
    obj card "title, photo, contact, hours, tier label, socials"
    ts deleted_at "tombstone"
  }
  COURSE_DOCUMENT {
    int pair_id PK
    int shop_id FK
    int course_id FK
    int mode "1 bookable, 2 elearning, 3 contact"
    int course_type "filter"
    int lifecycle_category "filter"
    text duration_bucket "filter"
    arr available_dates "filter"
    num price_from_usd "filter, histogram"
    arr teaching_language_ids "filter"
    int group_size_band "filter"
    int cancellation_policy "filter"
    int course_order "ordering"
    obj card "standard name, title, dives, inclusions, all-in price"
  }
  PLACES_DOCUMENT {
    text type PK "continent, region, country, area, location"
    int id PK
    obj title_by_lang "10 languages, analyzed"
    geo centroid
    obj parent_chain
    int shop_count
    int course_count
  }
  BOOKABLE_EXPERIENCE {
    int product_id PK
    int standard_name_id "filter, inner_hits"
    num price_from_usd "filter"
    text standard_name "card"
    text url
  }
```

## Lineage: source -> mirror -> projection -> indices -> API

Every source table lands in a canonical mirror through CDC; projection re-reads entities and rebuilds documents; FX is read by the API at request time.

```mermaid
flowchart TD
  subgraph SRC["PADI Travel Postgres — source of truth"]
    S1["Shop core<br/><small>shop_shop · shopinfo · bookinginfo · hours</small>"]
    S2["Tag vocabulary<br/><small>parametergroup · parameter · shop_parameters · image</small>"]
    S3["Google reviews<br/><small>reviews_googleplace (external Lambda)</small>"]
    S4["Places<br/><small>continent · region · country · area · location + links</small>"]
    S5["Course catalogue<br/><small>divecourseinfo · standard name · prices · tags · pairs</small>"]
    S6["Products, pricing, inclusions<br/><small>diveproduct · activityproduct · onedaypricing · extraoption</small>"]
    S7["Tier 2 sources<br/><small>marine life · dive sites (in CDC from day one)</small>"]
    S8["FX and country currency<br/><small>currencystorage · countrycurrency</small>"]
  end
  subgraph PIPE["Change data capture and projection"]
    M[("Canonical mirror<br/><small>row-for-row copy, DMS from WAL</small>")]
    P["Projection<br/><small>event → entity ids → re-read → build document</small>"]
  end
  subgraph OS["OpenSearch — one index per entity, aliases"]
    D1["Shop document"]
    D2["Course document"]
    D3["Places document"]
  end
  API["Locator API<br/><small>read-time FX · open now · display rules · seeded random</small>"]
  S1 & S2 & S3 & S4 & S5 & S6 & S7 --> M --> P
  P --> D1 & D2 & D3
  D1 & D2 & D3 --> API
  S8 -.-> API
  classDef src fill:#1f4e7922,stroke:#1f4e79,stroke-width:1.5px
  classDef t2 fill:#a86d1622,stroke:#a86d16,stroke-width:1.5px
  classDef doc fill:#0f766e22,stroke:#0f766e,stroke-width:1.5px
  classDef pipe fill:#5a6b7d22,stroke:#5a6b7d,stroke-width:1.5px
  class S1,S2,S3,S4,S5,S6,S8 src
  class S7 t2
  class D1,D2,D3,API doc
  class M,P pipe
```