Unified Locator — Entity Relationship Diagram

v3 · 2026-09-06 · 42 tables · 52 relationships verified against Postgres FK constraints · 3 OpenSearch documents · companion to the “locator-data-spec” sheet

How PADI Travel’s Postgres tables fit together for the locator and how they collapse into three search documents. Tier 1 (blue) is the November release; Tier 2 (brass) the free-text phase after it. Diagrams fit the page; use + / − / fit, Cmd/Ctrl-scroll, or drag to pan. Every figure has SVG, PNG and source downloads.

↓

Downloads for other tools

ERD.md — Mermaid, renders in GitHub · Confluence · Notionlocator.dbml — import at dbdiagram.io (all 49 tables, full columns, real FKs)locator-schema.sql — DDL for pgAdmin ERD / DBeaver / ChartDBerd-full.png — the poster
01

The model in one picture

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

Shop side

What a shop card needs

  • identity, pin, photo, contact, hours → shop_shop + its 1:1 tables
  • tier, awards, filter chips → tags
  • review score → Google rating
  • “Location, Country” → places
Course side

What a course card needs

  • the shop × course pair is the document
  • mode = bookable / eLearning / contact, from products and flags
  • duration, group size, cancellation, dates, price → product + pricing + extras
Places

What the search box becomes

  • a lookup over continent → region → country → area → location
  • returns an id the locator filters on; titles in 10 languages
Conceptual modelSVG ↗PNGDOTMermaid
ERD Dive shop Dive shop id eligibility, pin, card Tag Tag tier , award , facility , service , language labels in 10 languages Dive shop:h->Tag:h carries Google rating Google rating rating x review count trust, ordering Dive shop:h->Google rating:h linked profile Shop × course pair Shop x course pair is_offered , is_elearning mode Dive shop:h->Shop × course pair:h teaches Product Product approved , on Adventures bookable duration , ratio , cancellation filters Dive shop:h->Product:h sells Location Location title 10 languages Location:h->Dive shop:h reference location Region Region Location:h->Region:h belongs to (also via area, country) Area Area Area:h->Location:h in Country Country ISO code display rules Country:h->Area:h in Course Course standard name card type , category activity, level popularity order business order Course:h->Shop × course pair:h catalogue entry Course:h->Product:h shop version of Bookable day Bookable day date , price , USD price dates, price filter Product:h->Bookable day:h priced dates Extra Extra price policy , payable on site inclusions, all-in price Product:h->Extra:h inclusions
02

Shop core, contact and hours

One row in shop_shop is one PADI shop; three 1:1 tables hang off it and two parent/child pairs hold opening hours and closures. Only the columns the locator reads are shown; grey boxes are tables from other sections.

Eligibility

Four gates decide whether a shop document exists

  • is_active, is_on_dsl, kind (7 kinds or resort with dive centre), shop_shopinfo.padi_last_renew_year ≥ current year (grace to 1 Feb)
  • is_draft is not a gate — production ignores it
Contact

Read contact from shop_bookinginfo

  • the Salesforce sync writes shop_bookinginfo.phone/email; shop_shop.phone/email have no active writer
  • bank and legal columns in the same table are never extracted
Hours

Opening / contactable times

  • shop_openinghour = one row per weekday; shop_openinghourtime = one row per range
  • “open now” is computed at read time with shop_shop.timezone_id
Shop coreSVG ↗PNGDOTMermaid
ERD shop_shop shop_shop id int PK document key is_active bool   gate 1 is_on_dsl bool   gate 2 kind smallint   gate 3, stored title text   card self_slug text   profile URL latitude, longitude float8   geo_point reference_location_id int FK scope root country_id int FK fallback country city, state text   card, province picture_background text   card photo web_site, socials text   card currency varchar(3)   FX base timezone_id text   open now google_place_id text FK rating dsl_points, membership_level int   audit only shop_shopinfo shop_shopinfo shop_id int FK 1:1 affiliate_id varchar(10) UK store number padi_last_renew_year smallint   eligibility no_index bool   SEO flag shop_shop:h->shop_shopinfo:c0 shop_id shop_bookinginfo shop_bookinginfo shop_id int FK 1:1 phone text   card email text   card booking_contact_phone text   fallback booking_contact_email text[]   fallback shop_shop:h->shop_bookinginfo:c0 shop_id shop_resortinformation shop_resortinformation shop_id int FK 1:1 resort_kind smallint   eligibility if 7 kinds linked_dive_center_id int FK the dive centre shop shop_shop:h->shop_resortinformation:c0 shop_id (resorts) shop_shop:h->shop_resortinformation:c2 linked_dive_center_id shop_openinghour shop_openinghour id int PK   shop_id int FK   weekday smallint   1 Mon ... 7 Sun is_closed bool     is_contactable bool     shop_shop:h->shop_openinghour:c1 one per weekday shop_closedperiod shop_closedperiod id int PK   shop_id int FK   title text   optional shop_shop:h->shop_closedperiod:c1 closures (optional) shop_shop_adventure_dive_sites shop_shop_adventure_dive_sites -> Tier 2 - dive sites shop_shop:h->shop_shop_adventure_dive_sites:c0 shop_id diving_activityonedaypricing diving_activityonedaypricing -> Products, pricing, inclusions shop_shop:h->diving_activityonedaypricing:c7 shop_id (denormalised) shop_shop_parameters shop_shop_parameters -> Tag vocabulary and Google rating shop_shop:h->shop_shop_parameters:c0 shop_id booking_shopdivecourse booking_shopdivecourse -> Course catalogue and shop x course pairs shop_shop:h->booking_shopdivecourse:c1 shop_id diving_activitypricing diving_activitypricing -> Products, pricing, inclusions shop_shop:h->diving_activitypricing:c3 shop_id (denormalised) shop_shop_marine_life shop_shop_marine_life -> Tier 2 - marine life shop_shop:h->shop_shop_marine_life:c0 shop_id booking_diveproduct booking_diveproduct -> Products, pricing, inclusions shop_shop:h->booking_diveproduct:c1 shop_id shop_openinghourtime shop_openinghourtime opening_hour_id int FK   time_start time     time_end time     shop_openinghour:h->shop_openinghourtime:c0 opening_hour_id shop_closeddate shop_closeddate period_id int FK   date_from, date_to date   optional shop_closedperiod:h->shop_closeddate:c0 period_id reviews_googleplace reviews_googleplace -> Tag vocabulary and Google rating reviews_googleplace:h->shop_shop:c14 google_place_id places_country places_country -> Places (geography spine) places_country:h->shop_shop:c8 country_id (fallback) places_location places_location -> Places (geography spine) places_location:h->shop_shop:c7 reference_location_id
03

Tags, membership tiers, awards and the Google rating

Almost every shop attribute is a tag: shop_parametergroup is the category, shop_parameter the vocabulary (10 languages, optional logo), shop_shop_parameters the link that says which tags a shop has. Filters are arrays of tag ids per group; the membership tier is the highest tier tag a shop carries.

Routing

Groups are addressed by code_name, never id

  • dsl_activity_type · dive_center_services · dive_center_facilities · languages_spoken · membership_level
  • ids differ per environment; code_name is not unique-constrained — guard it
Constants

Tier and award tag ids are code constants

  • tiers 240 CDC, 243 IDC, 244 IDC Resort, 241 5★ DC, 242 5★ Resort, 245 Dive Center …
  • awards 249, 250, 513, 528, 530 → move to an awards group (D9)
  • is_public = false marks SSI/SDI/TDI credentials: exclude
Trust

reviews_googleplace

  • 1:1 through shop_shop.google_place_id; written by an external Lambda
  • rating × user_rating_count orders within a tier; store updated for freshness
Tag vocabulary and Google ratingSVG ↗PNGDOTMermaid
ERD shop_parametergroup shop_parametergroup id int PK differs per env code_name text   routing key is_active bool     shop_parameter shop_parameter id int PK tier/award ids = constants title text   10 languages group_id int FK   image_id int FK logo is_public bool   false = competitor is_active bool     order smallint   display shop_parametergroup:h->shop_parameter:c2 group_id shop_shop_parameters shop_shop_parameters shop_id int FK   parameter_id int FK   link , REPLICA IDENTITY FULL shop_parameter:h->shop_shop_parameters:c1 parameter_id diving_activityproduct_staff_languages diving_activityproduct_staff_languages -> Products, pricing, inclusions shop_parameter:h->diving_activityproduct_staff_languages:c1 languages_spoken rows common_image common_image id int PK   image text   path -> CDN width, height int     common_image:h->shop_parameter:c3 image_id reviews_googleplace reviews_googleplace place_id text PK Google id rating float8   card, ordering user_rating_count int   ordering updated ts   freshness external Lambda writer places_adventuredivesite places_adventuredivesite -> Tier 2 - dive sites reviews_googleplace:h->places_adventuredivesite:c6 google_place_id shop_shop shop_shop -> Shop core, contact, hours reviews_googleplace:h->shop_shop:c14 google_place_id shop_shop:h->shop_shop_parameters:c0 shop_id
04

Places — the geography spine

A shop points at one places_location; the location belongs to an area and a country; regions and continents attach at any level through six link tables (drawn as one line each, named on the line). The projection resolves the chain once and writes flat scope ids onto every document; the same five tables feed the Places lookup index.

PlacesSVG ↗PNGDOTMermaid
ERD places_location places_location id int PK scope key title text   10 languages, card slug text   10 languages area_id int FK   _country_id int FK   latitude, longitude float8   centroid is_autosuggest, is_active, is_on_map bool   lookup places_region places_region id int PK region_ids title text   10 languages slug text     is_autosuggest bool     places_location:h->places_region:h places_location__regions places_continent places_continent id int PK continent_ids title text   10 languages slug text     is_autosuggest bool     places_location:h->places_continent:h places_location__continents places_adventuredivesite places_adventuredivesite -> Tier 2 - dive sites places_location:h->places_adventuredivesite:c5 reference_location_id shop_shop shop_shop -> Shop core, contact, hours places_location:h->shop_shop:c7 reference_location_id diving_activityproduct diving_activityproduct -> Products, pricing, inclusions places_location:h->diving_activityproduct:c10 _base_location_id places_area places_area id int PK scope key title text   10 languages slug text   Adventures URL country_id int FK   latitude, longitude float8   centroid is_autosuggest bool   lookup places_area:h->places_location:c3 area_id places_area:h->places_region:h places_area__regions places_area:h->places_continent:h places_area__continents places_country places_country id int PK scope key title text   10 languages, card title_lc text   native name code varchar(2)   ISO, display rules slug text     is_autosuggest bool   lookup places_country:h->places_location:c4 _country_id places_country:h->places_area:c3 country_id places_country:h->places_region:h places_country__regions places_country:h->places_continent:h places_country__continents places_country:h->shop_shop:c8 country_id (fallback) booking_divecourseprice booking_divecourseprice -> Course catalogue and shop x course pairs places_country:h->booking_divecourseprice:c2 country_id places_countrycurrency places_countrycurrency -> FX and currency (read by the API) places_country:h->places_countrycurrency:c0 country_id
05

Course catalogue and the shop × course pair

booking_divecourseinfo is PADI’s catalogue (~106 courses) with type, lifecycle category and the business-editable order; booking_engine_productstandardname is the shared standard name; booking_shopdivecourse is the pair row that becomes a course document. booking_divecourseprice carries the eLearning B2C cost.

Course catalogue and pairsSVG ↗PNGDOTMermaid
ERD booking_shopdivecourse booking_shopdivecourse id int PK course document key shop_id int FK   dive_course_info_id int FK   is_offered bool   contact mode is_elearning, on_padi_com bool   eLearning mode booking_divecourseinfo booking_divecourseinfo id int PK course_id title text   10 languages slug text   URL type smallint   activity category smallint   level is_active bool     is_elearning, on_padi_com bool   mode cap popularity_order, order smallint   business order standard_name_id int FK   padi_url text   eLearning CTA booking_divecourseinfo:h->booking_shopdivecourse:c2 dive_course_info_id booking_divecourseprice booking_divecourseprice dive_course_info_id int FK   currency, price num   eLearning cost country_id int FK optional booking_divecourseinfo:h->booking_divecourseprice:c0 eLearning prices booking_divecourseimage booking_divecourseimage dive_course_info_id int FK   image text   fallback photo booking_divecourseinfo:h->booking_divecourseimage:c0 photos booking_divecoursetag booking_divecoursetag id int PK   title, slug text   7 tags today is_on_search bool     booking_divecourseinfo:h->booking_divecoursetag:h booking_divecourseinfo_tags booking_diveproduct booking_diveproduct -> Products, pricing, inclusions booking_divecourseinfo:h->booking_diveproduct:c2 course_info_id booking_engine_productstandardname booking_engine_productstandardname id int PK standard_name_id title text   10 languages, card order smallint   top-4 pick kind smallint   course kind booking_engine_productstandardname:h->booking_divecourseinfo:c8 standard_name_id booking_engine_productstandardname:h->booking_diveproduct:c3 standard_name_id places_country places_country -> Places (geography spine) places_country:h->booking_divecourseprice:c2 country_id shop_shop shop_shop -> Shop core, contact, hours shop_shop:h->booking_shopdivecourse:c1 shop_id
06

Products, pricing and inclusions

A shop’s sellable version of a course is booking_diveproduct; Adventures details live 1:1 in diving_activityproduct; bookable dates and prices are rows in diving_activityonedaypricing; inclusions and mandatory extras are diving_activityextraoption rows whose generic definition says eLearning / classroom / equipment. Pricing rows also carry shop_id directly (dashed) — useful for CDC routing. based_on_id marks distributor copies of a product: the locator should read only rows where it is null.

Products, pricing, inclusionsSVG ↗PNGDOTMermaid
ERD booking_diveproduct booking_diveproduct id int PK product_id shop_id int FK   course_info_id int FK   standard_name_id int FK   status smallint   30 = approved shown_on smallint[]   contains 20 Adventures title text   10 languages, card _is_deleted bool   eligibility based_on_id int FK null = shop own product booking_diveproduct:h->booking_diveproduct:c8 based_on_id (distributor copy) booking_productimage booking_productimage dive_product_id int FK   picture text   card photo is_cover, position bool, int   pick booking_diveproduct:h->booking_productimage:c0 dive_product_id diving_activityproduct diving_activityproduct id int PK activity_id dive_product_id int FK 1:1 duration smallint   hours, 7 bands duration_presentation_type smallint     number_of_dives smallint   card number_of_confined_dives smallint     ratio smallint   group size cancellation_policy smallint   filter, card is_elearning bool   inclusions external_url text   booking link _base_location_id int FK activity place booking_diveproduct:h->diving_activityproduct:c1 dive_product_id diving_activityproduct_staff_languages diving_activityproduct_staff_languages activityproduct_id int FK   parameter_id int FK teaching language link , REPLICA IDENTITY FULL diving_activityproduct:h->diving_activityproduct_staff_languages:c0 diving_activityonedaypricing diving_activityonedaypricing activity_id int FK   date date   Dates filter price num   card converted_price num   USD, price filter currency varchar(3)     available_before ts   eligibility status smallint     shop_id int FK denormalised diving_activityproduct:h->diving_activityonedaypricing:c0 per bookable day diving_activitypricing diving_activitypricing activity_id int FK   date daterange   verify usage price, converted_price num     shop_id int FK denormalised diving_activityproduct:h->diving_activitypricing:c0 date ranges (verify) diving_activityextraoption diving_activityextraoption activity_id int FK   based_on_generic_id int FK   price_policy smallint   8 phrases price, price_converted num   mandatory extras payable_on_site bool   mandatory diving_activityproduct:h->diving_activityextraoption:c0 inclusions, extras diving_activityproduct_sites diving_activityproduct_sites -> Tier 2 - dive sites diving_activityproduct:h->diving_activityproduct_sites:c0 activityproduct_id diving_standardproductactivityinclusion diving_standardproductactivityinclusion id int PK   based_on_generic_id int FK   diving_standardproductactivityinclusion:h->diving_activityextraoption:c1 based_on_generic_id diving_genericactivityextraoption diving_genericactivityextraoption id int PK   code text   eLearning / classroom / equipment title text   10 languages included_in_id int FK nesting diving_genericactivityextraoption:h->diving_standardproductactivityinclusion:c1 based_on_generic_id diving_genericactivityextraoption:h->diving_genericactivityextraoption:c3 included_in_id booking_engine_productstandardname booking_engine_productstandardname -> Course catalogue and shop x course pairs booking_engine_productstandardname:h->booking_diveproduct:c3 standard_name_id shop_shop shop_shop -> Shop core, contact, hours shop_shop:h->booking_diveproduct:c1 shop_id shop_shop:h->diving_activityonedaypricing:c7 shop_id (denormalised) shop_shop:h->diving_activitypricing:c3 shop_id (denormalised) shop_parameter shop_parameter -> Tag vocabulary and Google rating shop_parameter:h->diving_activityproduct_staff_languages:c1 languages_spoken rows places_location places_location -> Places (geography spine) places_location:h->diving_activityproduct:c10 _base_location_id booking_divecourseinfo booking_divecourseinfo -> Course catalogue and shop x course pairs booking_divecourseinfo:h->booking_diveproduct:c2 course_info_id
07

Tier 2 sources — marine life and dive sites

Captured by CDC from day one, added to the documents in the free-text phase.

Marine lifeSVG ↗PNGDOTMermaid
ERD shop_shop_marine_life shop_shop_marine_life shop_id int FK   marinelifecritter_id int FK   link , REPLICA IDENTITY FULL marine_life2_marinelifecritter marine_life2_marinelifecritter id int PK critter_ids title text   10 languages, search group_id int FK   is_active bool     marine_life2_marinelifecritter:h->shop_shop_marine_life:c1 marinelifecritter_id marine_life2_marinelifegroup marine_life2_marinelifegroup id int PK marine_group_ids title text   e.g. Sharks and Rays kind_id int FK   marine_life2_marinelifegroup:h->marine_life2_marinelifecritter:c2 group_id marine_life2_marinelifekind marine_life2_marinelifekind id int PK   title text   Fish, Mammals ... marine_life2_marinelifekind:h->marine_life2_marinelifegroup:c2 kind_id shop_shop shop_shop -> Shop core, contact, hours shop_shop:h->shop_shop_marine_life:c0 shop_id
Dive sitesSVG ↗PNGDOTMermaid
ERD shop_shop_adventure_dive_sites shop_shop_adventure_dive_sites shop_id int FK   adventuredivesite_id int FK   link , REPLICA IDENTITY FULL places_adventuredivesite places_adventuredivesite id int PK dive_site_ids title text   10 languages, search status smallint   approved only is_private bool   exclude latitude, longitude float8     reference_location_id int FK   google_place_id text FK site rating places_adventuredivesite:h->shop_shop_adventure_dive_sites:c1 adventuredivesite_id diving_activityproduct_sites diving_activityproduct_sites activityproduct_id int FK   adventuredivesite_id int FK   link , REPLICA IDENTITY FULL places_adventuredivesite:h->diving_activityproduct_sites:c1 adventuredivesite_id reviews_googleplace reviews_googleplace -> Tag vocabulary and Google rating reviews_googleplace:h->places_adventuredivesite:c6 google_place_id shop_shop shop_shop -> Shop core, contact, hours shop_shop:h->shop_shop_adventure_dive_sites:c0 shop_id places_location places_location -> Places (geography spine) places_location:h->places_adventuredivesite:c5 reference_location_id diving_activityproduct diving_activityproduct -> Products, pricing, inclusions diving_activityproduct:h->diving_activityproduct_sites:c0 activityproduct_id
08

What the source becomes — three OpenSearch documents

One document per eligible shop, one per eligible shop × course pair, one per place. Course documents copy their shop’s card fields; the shop document nests every bookable product so a course filter can surface the matching one with inner_hits. FX and country-currency tables are read by the API at request time.

OpenSearch documentsSVG ↗PNGDOTMermaid
ERD Shop document Shop document shop_id int PK   location geo_point   viewport, radius, clusters location_id, area_id, country_id int   scope region_ids, continent_ids int[]   scope membership_tier int   1-5 from tags, ordering review_score float   rating x count promoted, profile_complete bool   ordering activity_type_ids, service_ids, facility_ids int[]   filters language_ids, award_ids, lifecycle_category_ids int[]   filters card object   title, photo, contact, hours, tier label deleted_at ts   tombstone Bookable experience (nested) Bookable experience (nested) product_id int PK   standard_name_id int   filter via inner_hits price_from_usd num   filter standard_name, title, url text   card Shop document:h->Bookable experience (nested):h nested, all products Course document Course document pair_id int PK   shop_id int FK copied shop fields course_id int FK   mode int   1 bookable , 2 eLearning , 3 contact course_type, lifecycle_category int   filters duration_bucket, group_size_band, cancellation_policy int   filters available_dates date[]   filter price_from_usd num   filter, histogram teaching_language_ids int[]   filter course_order int   ordering card object   standard name, title, dives, inclusions, all-in price Shop document:h->Course document:h shop_id + copied fields Places document Places document type, id text, int PK continent ... location title_by_lang object   10 languages, analyzed centroid geo_point     parent_chain object   breadcrumb shop_count, course_count int   never an empty map Places document:h->Shop document:h scope ids Places document:h->Course document:h scope ids
Lineage: source → mirror → projection → indices → APISVG ↗PNGDOTMermaid
ERD cluster_src PADI Travel Postgres — source of truth cluster_pipe Change data capture and projection cluster_os OpenSearch — one index per entity, aliases S1 Shop core shop_shop , shopinfo , bookinginfo , hours M Canonical mirror row-for-row copy , DMS from the WAL S1->M S2 Tag vocabulary parametergroup , parameter , shop_parameters , image S2->M S3 Google reviews reviews_googleplace (external Lambda) S3->M S4 Places continent , region , country , area , location + links S4->M S5 Course catalogue divecourseinfo , standard name , prices , tags , pairs S5->M S6 Products, pricing, inclusions diveproduct , activityproduct , onedaypricing , extraoption S6->M S7 Tier 2 sources marine life , dive sites - in CDC from day one S7->M S8 FX and country currency currencystorage , countrycurrency API Locator API read-time FX , open now , display rules , seeded random S8->API P Projection event -> entity ids -> re-read -> build document M->P D1 Shop document one per eligible shop P->D1 D2 Course document one per eligible shop x course pair P->D2 D3 Places document one per place, the lookup index P->D3 D1->API D2->API D3->API
FX and currency tables (API layer)SVG ↗PNGDOT
ERD booking_engine_currencystorage booking_engine_currencystorage id int PK   base_currency varchar(6)     json_storage jsonb   rates created ts   one row per day places_countrycurrency places_countrycurrency country_id int FK   currency varchar(3)   display currency places_country places_country -> Places (geography spine) places_country:h->places_countrycurrency:c0 country_id
09

Full schema poster

All 42 tables grouped by domain, every relationship, on one sheet. Zoom in or open the PNG (4091 × 4638 px).

Full schema, clustered by domainSVG ↗PNGDOT
ERD cluster_shop Shop core, contact, hours cluster_tags Tag vocabulary and Google rating cluster_places Places (geography spine) cluster_catalogue Course catalogue and shop x course pairs cluster_products Products, pricing, inclusions cluster_fx FX and currency (read by the API) cluster_marine Tier 2 - marine life cluster_sites Tier 2 - dive sites shop_shop shop_shop id int PK document key is_active bool   gate 1 is_on_dsl bool   gate 2 kind smallint   gate 3, stored title text   card self_slug text   profile URL latitude, longitude float8   geo_point reference_location_id int FK scope root country_id int FK fallback country city, state text   card, province picture_background text   card photo web_site, socials text   card currency varchar(3)   FX base timezone_id text   open now google_place_id text FK rating dsl_points, membership_level int   audit only shop_shopinfo shop_shopinfo shop_id int FK 1:1 affiliate_id varchar(10) UK store number padi_last_renew_year smallint   eligibility no_index bool   SEO flag shop_shop:h->shop_shopinfo:c0 shop_id shop_bookinginfo shop_bookinginfo shop_id int FK 1:1 phone text   card email text   card booking_contact_phone text   fallback booking_contact_email text[]   fallback shop_shop:h->shop_bookinginfo:c0 shop_id shop_resortinformation shop_resortinformation shop_id int FK 1:1 resort_kind smallint   eligibility if 7 kinds linked_dive_center_id int FK the dive centre shop shop_shop:h->shop_resortinformation:c0 shop_id (resorts) shop_shop:h->shop_resortinformation:c2 linked_dive_center_id shop_openinghour shop_openinghour id int PK   shop_id int FK   weekday smallint   1 Mon ... 7 Sun is_closed bool     is_contactable bool     shop_shop:h->shop_openinghour:c1 one per weekday shop_closedperiod shop_closedperiod id int PK   shop_id int FK   title text   optional shop_shop:h->shop_closedperiod:c1 closures (optional) shop_shop_parameters shop_shop_parameters shop_id int FK   parameter_id int FK   link , REPLICA IDENTITY FULL shop_shop:h->shop_shop_parameters:c0 shop_id booking_shopdivecourse booking_shopdivecourse id int PK course document key shop_id int FK   dive_course_info_id int FK   is_offered bool   contact mode is_elearning, on_padi_com bool   eLearning mode shop_shop:h->booking_shopdivecourse:c1 shop_id booking_diveproduct booking_diveproduct id int PK product_id shop_id int FK   course_info_id int FK   standard_name_id int FK   status smallint   30 = approved shown_on smallint[]   contains 20 Adventures title text   10 languages, card _is_deleted bool   eligibility based_on_id int FK null = shop own product shop_shop:h->booking_diveproduct:c1 shop_id diving_activityonedaypricing diving_activityonedaypricing activity_id int FK   date date   Dates filter price num   card converted_price num   USD, price filter currency varchar(3)     available_before ts   eligibility status smallint     shop_id int FK denormalised shop_shop:h->diving_activityonedaypricing:c7 shop_id (denormalised) diving_activitypricing diving_activitypricing activity_id int FK   date daterange   verify usage price, converted_price num     shop_id int FK denormalised shop_shop:h->diving_activitypricing:c3 shop_id (denormalised) shop_shop_marine_life shop_shop_marine_life shop_id int FK   marinelifecritter_id int FK   link , REPLICA IDENTITY FULL shop_shop:h->shop_shop_marine_life:c0 shop_id shop_shop_adventure_dive_sites shop_shop_adventure_dive_sites shop_id int FK   adventuredivesite_id int FK   link , REPLICA IDENTITY FULL shop_shop:h->shop_shop_adventure_dive_sites:c0 shop_id shop_openinghourtime shop_openinghourtime opening_hour_id int FK   time_start time     time_end time     shop_openinghour:h->shop_openinghourtime:c0 opening_hour_id shop_closeddate shop_closeddate period_id int FK   date_from, date_to date   optional shop_closedperiod:h->shop_closeddate:c0 period_id shop_parametergroup shop_parametergroup id int PK differs per env code_name text   routing key is_active bool     shop_parameter shop_parameter id int PK tier/award ids = constants title text   10 languages group_id int FK   image_id int FK logo is_public bool   false = competitor is_active bool     order smallint   display shop_parametergroup:h->shop_parameter:c2 group_id shop_parameter:h->shop_shop_parameters:c1 parameter_id diving_activityproduct_staff_languages diving_activityproduct_staff_languages activityproduct_id int FK   parameter_id int FK teaching language link , REPLICA IDENTITY FULL shop_parameter:h->diving_activityproduct_staff_languages:c1 languages_spoken rows common_image common_image id int PK   image text   path -> CDN width, height int     common_image:h->shop_parameter:c3 image_id reviews_googleplace reviews_googleplace place_id text PK Google id rating float8   card, ordering user_rating_count int   ordering updated ts   freshness external Lambda writer reviews_googleplace:h->shop_shop:c14 google_place_id places_adventuredivesite places_adventuredivesite id int PK dive_site_ids title text   10 languages, search status smallint   approved only is_private bool   exclude latitude, longitude float8     reference_location_id int FK   google_place_id text FK site rating reviews_googleplace:h->places_adventuredivesite:c6 google_place_id places_location places_location id int PK scope key title text   10 languages, card slug text   10 languages area_id int FK   _country_id int FK   latitude, longitude float8   centroid is_autosuggest, is_active, is_on_map bool   lookup places_location:h->shop_shop:c7 reference_location_id places_region places_region id int PK region_ids title text   10 languages slug text     is_autosuggest bool     places_location:h->places_region:h places_location__regions places_continent places_continent id int PK continent_ids title text   10 languages slug text     is_autosuggest bool     places_location:h->places_continent:h places_location__continents diving_activityproduct diving_activityproduct id int PK activity_id dive_product_id int FK 1:1 duration smallint   hours, 7 bands duration_presentation_type smallint     number_of_dives smallint   card number_of_confined_dives smallint     ratio smallint   group size cancellation_policy smallint   filter, card is_elearning bool   inclusions external_url text   booking link _base_location_id int FK activity place places_location:h->diving_activityproduct:c10 _base_location_id places_location:h->places_adventuredivesite:c5 reference_location_id places_area places_area id int PK scope key title text   10 languages slug text   Adventures URL country_id int FK   latitude, longitude float8   centroid is_autosuggest bool   lookup places_area:h->places_location:c3 area_id places_area:h->places_region:h places_area__regions places_area:h->places_continent:h places_area__continents places_country places_country id int PK scope key title text   10 languages, card title_lc text   native name code varchar(2)   ISO, display rules slug text     is_autosuggest bool   lookup places_country:h->shop_shop:c8 country_id (fallback) places_country:h->places_location:c4 _country_id places_country:h->places_area:c3 country_id places_country:h->places_region:h places_country__regions places_country:h->places_continent:h places_country__continents booking_divecourseprice booking_divecourseprice dive_course_info_id int FK   currency, price num   eLearning cost country_id int FK optional places_country:h->booking_divecourseprice:c2 country_id places_countrycurrency places_countrycurrency country_id int FK   currency varchar(3)   display currency places_country:h->places_countrycurrency:c0 country_id booking_divecourseinfo booking_divecourseinfo id int PK course_id title text   10 languages slug text   URL type smallint   activity category smallint   level is_active bool     is_elearning, on_padi_com bool   mode cap popularity_order, order smallint   business order standard_name_id int FK   padi_url text   eLearning CTA booking_divecourseinfo:h->booking_shopdivecourse:c2 dive_course_info_id booking_divecourseinfo:h->booking_divecourseprice:c0 eLearning prices booking_divecourseimage booking_divecourseimage dive_course_info_id int FK   image text   fallback photo booking_divecourseinfo:h->booking_divecourseimage:c0 photos booking_divecoursetag booking_divecoursetag id int PK   title, slug text   7 tags today is_on_search bool     booking_divecourseinfo:h->booking_divecoursetag:h booking_divecourseinfo_tags booking_divecourseinfo:h->booking_diveproduct:c2 course_info_id booking_engine_productstandardname booking_engine_productstandardname id int PK standard_name_id title text   10 languages, card order smallint   top-4 pick kind smallint   course kind booking_engine_productstandardname:h->booking_divecourseinfo:c8 standard_name_id booking_engine_productstandardname:h->booking_diveproduct:c3 standard_name_id booking_diveproduct:h->booking_diveproduct:c8 based_on_id (distributor copy) booking_productimage booking_productimage dive_product_id int FK   picture text   card photo is_cover, position bool, int   pick booking_diveproduct:h->booking_productimage:c0 dive_product_id booking_diveproduct:h->diving_activityproduct:c1 dive_product_id diving_activityproduct:h->diving_activityproduct_staff_languages:c0 diving_activityproduct:h->diving_activityonedaypricing:c0 per bookable day diving_activityproduct:h->diving_activitypricing:c0 date ranges (verify) diving_activityextraoption diving_activityextraoption activity_id int FK   based_on_generic_id int FK   price_policy smallint   8 phrases price, price_converted num   mandatory extras payable_on_site bool   mandatory diving_activityproduct:h->diving_activityextraoption:c0 inclusions, extras diving_activityproduct_sites diving_activityproduct_sites activityproduct_id int FK   adventuredivesite_id int FK   link , REPLICA IDENTITY FULL diving_activityproduct:h->diving_activityproduct_sites:c0 activityproduct_id diving_standardproductactivityinclusion diving_standardproductactivityinclusion id int PK   based_on_generic_id int FK   diving_standardproductactivityinclusion:h->diving_activityextraoption:c1 based_on_generic_id diving_genericactivityextraoption diving_genericactivityextraoption id int PK   code text   eLearning / classroom / equipment title text   10 languages included_in_id int FK nesting diving_genericactivityextraoption:h->diving_standardproductactivityinclusion:c1 based_on_generic_id diving_genericactivityextraoption:h->diving_genericactivityextraoption:c3 included_in_id booking_engine_currencystorage booking_engine_currencystorage id int PK   base_currency varchar(6)     json_storage jsonb   rates created ts   one row per day marine_life2_marinelifecritter marine_life2_marinelifecritter id int PK critter_ids title text   10 languages, search group_id int FK   is_active bool     marine_life2_marinelifecritter:h->shop_shop_marine_life:c1 marinelifecritter_id marine_life2_marinelifegroup marine_life2_marinelifegroup id int PK marine_group_ids title text   e.g. Sharks and Rays kind_id int FK   marine_life2_marinelifegroup:h->marine_life2_marinelifecritter:c2 group_id marine_life2_marinelifekind marine_life2_marinelifekind id int PK   title text   Fish, Mammals ... marine_life2_marinelifekind:h->marine_life2_marinelifegroup:c2 kind_id places_adventuredivesite:h->shop_shop_adventure_dive_sites:c1 adventuredivesite_id places_adventuredivesite:h->diving_activityproduct_sites:c1 adventuredivesite_id
10

Fan-out, CDC notes and notation

How many documents must be rebuilt when one row changes, who writes it, and where logical replication needs REPLICA IDENTITY FULL. Generated from the same data as TABLE 1 of the sheet.

Fan-out and CDC reference — 43 table rows
TableDomainTierFeeds documentFan-outWritten byCDC note
shop_shopShop coreTier 1Shop + Course1 shop (and its course documents)PADI Salesforce sync (address, kind, website); Extranet Basics/Photos tabs (name, photo, coordinates, socials); admin; Django save() (slugs, point)
shop_shopinfoShop coreTier 1Shop + Course1PADI Salesforce sync; adminrenew-year rule flips on 1 Feb — scheduled re-evaluation
shop_bookinginfoShop contactTier 1Shop + Course1PADI Salesforce sync; Extranet booking tab
shop_resortinformationShop coreTier 1Shop1Extranet / admin
shop_openinghourShop hoursTier 1Shop1Extranet Opening Times screen
shop_openinghourtimeShop hoursTier 1Shop1 (two hops: range → day → shop)Extranet Opening Times screen
shop_closedperiod + shop_closeddateShop hoursTier 1Shop1 (two hops)Extranet
shop_parametergroupVocabulary (tags)Tier 1Shopmany (a group change touches every shop with tags in it)Admin
shop_parameterVocabulary (tags)Tier 1Shop + Course (course teaching languages reuse the same rows)manyAdmin; PADI membership feed / Salesforce sync (tier and award tags, add-only)
shop_shop_parametersVocabulary (tags)Tier 1Shop1Extranet (operator ticks attributes); PADI membership feed / Salesforce sync (tiers and awards — add-only, never removes)REPLICA IDENTITY FULL; add-only writer
common_imageVocabulary (tags)Tier 1Shop + CoursemanyAdmin upload
reviews_googleplaceReviewsTier 1Shop + Course1 (reached through shop_shop.google_place_id)travel-google-reviews ingest/processor Lambdas (outside Travel code)external Lambda writer
places_locationPlacesTier 1Shop + Course + PlacesmanyAdmin (places are editorial data)
places_areaPlacesTier 1Shop + Course + PlacesmanyAdmin
places_countryPlacesTier 1Shop + Course + PlacesmanyAdmin
places_regionPlacesTier 1Shop + Course + PlacesmanyAdmin
places_continentPlacesTier 1Shop + Course + PlacesmanyAdmin
places_location__regions, places_area__regions, places_country__regionsPlaces linksTier 1Shop + CoursemanyAdminREPLICA IDENTITY FULL
places_location__continents, places_area__continents, places_country__continentsPlaces linksTier 1Shop + CoursemanyAdminREPLICA IDENTITY FULL
booking_shopdivecourseCourse catalogueTier 1Course1Extranet "Courses offered" ticks; PADI eLearning flags
booking_divecourseinfoCourse catalogueTier 1Course (+ Shop lifecycle rollup)many (a catalogue change touches every shop offering the course)Admin
booking_engine_productstandardnameCourse catalogueTier 1Shop + CoursemanyAdmin
booking_divecoursepriceCourse catalogueTier 1CoursemanyAdmin / PADI
booking_divecourseimageCourse catalogueTier 1CoursemanyAdmin
booking_divecoursetag + booking_divecourseinfo_tagsCourse catalogueTier 1CoursemanyAdmin
booking_diveproductProductsTier 1Shop + Course1Extranet product editor; PADI approval workflow
booking_productimageProductsTier 1Shop + Course1Extranet product editor
diving_activityproductProductsTier 1Shop + Course1Extranet product editor
diving_activityproduct_staff_languagesProductsTier 1Course1Extranet product editorREPLICA IDENTITY FULL
diving_activityonedaypricingPricingTier 1Shop + Course1 (three hops: price → activity → product → shop)Extranet pricing/schedule; booking engine (status)rows expire by calendar: daily re-projection + date ≥ today at query time
diving_activitypricingPricingTier 1Course1Extranetverify usage before including
diving_activityextraoptionInclusionsTier 1Shop + Course1 (three hops)Extranet product editor
diving_standardproductactivityinclusionInclusionsTier 1CoursemanyAdmin
diving_genericactivityextraoptionInclusionsTier 1CoursemanyAdmin
booking_engine_currencystorageFXTier 1API layern/aTravel FX jobread at request time by the API
places_countrycurrencyFXTier 1API layern/aAdminread at request time by the API
shop_shop_marine_lifeMarine lifeTier 2Shop1ExtranetREPLICA IDENTITY FULL; CDC from day one
marine_life2_marinelifecritterMarine lifeTier 2ShopmanyAdmin
marine_life2_marinelifegroupMarine lifeTier 2ShopmanyAdmin
marine_life2_marinelifekindMarine lifeTier 2ShopmanyAdmin
shop_shop_adventure_dive_sitesDive sitesTier 2Shop1ExtranetREPLICA IDENTITY FULL; CDC from day one
places_adventuredivesiteDive sitesTier 2Shop + CoursemanyExtranet / admin approval
diving_activityproduct_sitesDive sitesTier 2Course1Extranet product editorREPLICA IDENTITY FULL; CDC from day one

Notation

—| one

a bar at the end of a line = exactly one

—o| zero or one

circle + bar = optional single (e.g. a nullable foreign key)

—o< zero or many

circle + crow’s foot = many optional children

—|< one or many

bar + crow’s foot = at least one

dashed line

optional or secondary relationship (fallbacks, denormalised keys, templates, tables still to verify)

PK · FK · UK

primary / foreign / unique key; the grey note says what the locator uses the column for. Grey header = table drawn in another section.

Sources: “Product Feature – Dive Shop Locator” (updated docx), Figma filter designs, the ordering-requirement doc, Travel code and Postgres schema (all 52 relationships checked against pg_constraint on the stage restore; tier/award ids are code constants). Field-level detail, categories and decisions D1–D18: Google Sheet “data requirements – locator”, tab locator-data-spec. Layout: Graphviz 16 (dot) via @viz-js/viz; sources in locator_docs/erd/.