Redshift Clients Datalake — Entity Relationship Diagram

This documents the entity-relationship structure of the tables the Clients Datalake exports to each client’s Redshift database. The export pipeline serves 50 views, including the deprecated property_calendars view. This diagram excludes property_calendars (see Excluded: deprecated views) and therefore covers the remaining 49. The Client Data Dictionary is the column-level reference for every table listed here; this document is relationship-focused and only lists primary-key, foreign-key, and discriminator columns on each entity.

Table and column names below are the literal names in your Redshift database — they follow Lavanda’s internal data model, which differs from some product terminology. Notably: properties = Units, multi_unit_groups = Unit Types, and inquiries = Leads.

How to read this diagram

Every view is filtered to the client’s own workspace IDs. Most views reach that scope by joining through a parent table (e.g. bookings → properties → workspaces) rather than owning a workspace_id column themselves — those hops are recorded as scope-hop rows in the evidence table below but are not drawn as diagram edges, to avoid a WORKSPACES fan-out to nearly every entity. An edge to WORKSPACES is drawn only for a table that filters or joins on its own workspace_id (or workspace_uuid) column.

An edge between two other entities is drawn on one of two evidence tiers, both recorded in the evidence table below: hard-fk, where a JOIN predicate in the view SQL itself touches the served table’s own alias column (t.*) and that column is part of the view’s projection; or documented-fk, where the view SQL doesn’t join the target table, but the column’s own sources.yml description explicitly names a served table as the target (e.g. “ID of the associated booking”) — never inferred from a column name alone, always from the documented description. A *_id/*_uuid column whose description points at a table this export does not serve (e.g. an internal CRM company or contact-entity record) is listed as an external reference instead of drawn, since there is no entity in this diagram to draw it to. Dashed relationships mark polymorphic/soft references (a *_type discriminator paired with a *_id/array column that can resolve to more than one table, or that isn’t declared as a real foreign key in Lavanda’s internal schema).

Diagram

%%{init: {"er": {"useMaxWidth": false}, "themeVariables": {"fontSize": "16px"}} }%%
erDiagram
    direction TB

    WORKSPACES {
        bigint id PK
    }

    PROPERTIES {
        integer id PK
        bigint workspace_id FK
        bigint multi_unit_group_id FK "-> MULTI_UNIT_GROUPS, optional"
        integer parent_unit_id FK "-> PROPERTIES (self), optional"
        bigint onboarding_ticket_id FK "-> TICKETS, optional"
    }

    BUILDINGS {
        bigint id PK
        bigint workspace_id FK
    }

    MULTI_UNIT_GROUPS {
        bigint id PK
        bigint workspace_id FK
    }

    MULTI_UNIT_GROUP_CALENDARS {
        bigint id PK
        bigint multi_unit_group_id FK
    }

    ADDRESSES {
        bigint id PK
        varchar addressable_type "soft ref discriminator"
        bigint addressable_id "soft ref: PROPERTIES or BUILDINGS"
    }

    AVAILABILITY_UNIT_AVAILABILITY_POOL_ALLOCATIONS {
        bigint id PK
        bigint unit_id FK "-> PROPERTIES"
    }

    UNIT_CALENDARS {
        bigint id PK
        bigint unit_id FK "-> PROPERTIES"
        bigint booking_id FK "-> BOOKINGS, optional"
    }

    TARIFFS {
        varchar id PK "Mongo _id"
    }

    INTERVAL_PRICING_CALENDARS {
        bigint id PK
        varchar bookable_type "soft ref discriminator"
        bigint bookable_id "soft ref: PROPERTIES or MULTI_UNIT_GROUPS"
        varchar tariff_id FK "-> TARIFFS"
    }

    BOOKINGS {
        integer id PK
        integer property_id FK
        bigint multi_unit_group_id FK "-> MULTI_UNIT_GROUPS, optional"
        varchar product_id FK "-> CATALOG_PRODUCTS, optional"
    }

    REPORT_AR_DOCUMENTS {
        varchar document_id PK
        varchar booking_id FK "-> BOOKINGS"
        varchar workspace_id FK "-> WORKSPACES"
    }

    REPORT_AR_BALANCES {
        varchar id PK
        varchar workspace_id FK "-> WORKSPACES"
    }

    BILLING_PERIODS {
        bigint id PK
        bigint booking_id FK
    }

    BOOKING_GUARANTOR_INFORMATIONS {
        bigint id PK
        bigint booking_id FK
        varchar external_contact_id FK "-> CRM_CONTACT_INFORMATIONS (unique_reference)"
    }

    BOOKING_CONTEXT_PARTICIPANTS {
        bigint id PK
        bigint booking_context_id "-> BOOKINGS.booking_context_id (context, not a booking row)"
        varchar external_contact_id FK "-> CRM_CONTACT_INFORMATIONS (unique_reference)"
    }

    BOOKING_GUESTS {
        bigint id PK
        bigint booking_id FK
        varchar external_contact_id FK "-> CRM_CONTACT_INFORMATIONS (unique_reference)"
        varchar role "GUEST, LEAD_GUEST, GUARANTOR, BOOKING_AGENT"
    }

    BOOKINGS_INSTALMENT_PLAN_TEMPLATES {
        varchar id PK
        varchar workspace_id FK "-> WORKSPACES (stored as text)"
        varchar product_group_ids "soft ref: CATALOG_PRODUCT_GROUPS (array)"
    }

    BOOKING_CONTRACT_VALUES {
        bigint id PK
        bigint booking_id FK
    }

    DAILY_REVENUES {
        integer id PK
        integer property_id FK
        integer booking_id FK "-> BOOKINGS, optional"
    }

    BOOKING_DAILY_RATES {
        bigint id PK
        bigint booking_id FK
        bigint billing_period_id FK "-> BILLING_PERIODS"
        varchar issued_invoice_uuid FK "-> INVOICING_INVOICES, optional"
    }

    BOOKING_RATES {
        bigint id PK
        bigint booking_id FK
    }

    FINANCE_BILLING_CYCLES {
        bigint id PK
        bigint booking_id FK
    }

    GUEST_PAYMENTS {
        bigint id PK
        bigint booking_id FK
        varchar invoice_uuid FK "-> INVOICING_INVOICES"
    }

    INVOICING_INVOICES {
        bigint id PK
        json payload "embeds booking/guest-payment IDs, see note below"
    }

    INVOICE_LIST_ITEMS {
        bigint id PK
        varchar workspace_uuid FK "-> WORKSPACES (uuid, not id)"
        varchar invoice_uuid FK "-> INVOICING_INVOICES (uuid)"
        bigint booking_id FK "-> BOOKINGS"
        bigint billing_period_id FK "-> BILLING_PERIODS"
    }

    CATALOG_PRODUCT_GROUPS {
        varchar id PK "NanoID"
        bigint workspace_id FK
    }

    CATALOG_PRODUCT_RATES {
        varchar id PK "NanoID"
    }

    CATALOG_PRODUCTS {
        varchar id PK "NanoID"
        varchar product_group_id FK
        varchar product_rate_id FK
    }

    CATALOG_RATE_CALENDARS {
        varchar id PK "NanoID"
        varchar product_rate_id FK
    }

    AGREEMENTS_AGREEMENTS {
        varchar id PK "NanoID"
        bigint workspace_id FK
        bigint company_id "external ref, see note below"
    }

    AGREEMENTS_AGREEMENT_UNIT_TYPE_ALLOCATIONS {
        varchar id PK "NanoID"
        varchar agreement_id FK
        bigint unit_type_id FK "-> MULTI_UNIT_GROUPS"
    }

    AGREEMENTS_AGREEMENT_VERSIONS {
        bigint id PK
        varchar item_type "soft ref discriminator (PaperTrail)"
        varchar item_id "soft ref: AGREEMENTS_AGREEMENTS"
    }

    AGREEMENTS_AGREEMENT_UNIT_TYPE_ALLOCATION_VERSIONS {
        bigint id PK
        varchar item_type "soft ref discriminator (PaperTrail)"
        varchar item_id "soft ref: AGREEMENTS_AGREEMENT_UNIT_TYPE_ALLOCATIONS"
    }

    CRM_NAMESPACES {
        uuid uuid PK
    }

    CRM_CONTACT_INFORMATIONS {
        bigint id PK
        bigint crm_contact_entity_id "external ref, see note below"
    }

    CRM_COMPANIES {
        bigint id PK
        bigint crm_namespace_id "internal namespace id, see note below"
        uuid crm_namespace_uuid FK "-> CRM_NAMESPACES"
    }

    CRM_COMPANIES_CONTACT_INFORMATIONS {
        bigint id PK
        bigint crm_company_id FK "-> CRM_COMPANIES"
        bigint crm_contact_information_id FK "-> CRM_CONTACT_INFORMATIONS"
    }

    CRM_CONTACT_RELATIONSHIPS {
        bigint id PK
        bigint source_contact_information_id FK "-> CRM_CONTACT_INFORMATIONS (guest)"
        bigint target_contact_information_id FK "-> CRM_CONTACT_INFORMATIONS (related contact)"
        varchar relationship_type "discriminator"
        varchar sub_type
    }

    LEAD_STATUSES {
        bigint id PK
        uuid crm_namespace_uuid FK "-> CRM_NAMESPACES"
    }

    INQUIRIES {
        bigint id PK
        bigint lead_status_id FK "-> LEAD_STATUSES, optional"
        uuid crm_namespace_uuid FK "-> CRM_NAMESPACES"
    }

    INQUIRY_PARTICIPANTS {
        bigint id PK
        bigint inquiry_id FK
    }

    WORKSPACES_INQUIRIES {
        bigint id PK
        bigint workspace_id FK
        bigint inquiry_id FK "-> INQUIRIES"
    }

    OFFER_MANAGEMENT_OFFERS {
        bigint id PK
        varchar resource_owner_type "soft ref discriminator"
        varchar resource_owner_id "soft ref: INQUIRIES (currently always 'inquiry')"
        bigint building_id FK "-> BUILDINGS"
    }

    OFFER_MANAGEMENT_OFFER_ACCOMMODATIONS {
        bigint id PK
        bigint offer_id FK
    }

    TAGS_TAGS {
        bigint id PK
        bigint container_id "external ref, see note below"
        varchar scope "discriminator"
        varchar entity "discriminator"
    }

    TAGS_ENTITY_TAGS {
        bigint id PK
        bigint tag_id FK "-> TAGS_TAGS"
        varchar ref "soft ref, see Soft and polymorphic references"
    }

    TASK_CATEGORIES {
        bigint id PK
    }

    TASKS {
        integer id PK
        bigint workspace_id FK
        integer property_id FK "-> PROPERTIES, optional"
        bigint parent_ticket_id FK "-> TICKETS, optional"
        bigint booking_id FK "-> BOOKINGS, optional"
        bigint task_category_id FK "-> TASK_CATEGORIES"
    }

    TICKETS {
        integer id PK
        bigint workspace_id FK
        integer property_id FK "-> PROPERTIES"
    }

    WORKSPACES ||--o{ PROPERTIES : "scopes"
    WORKSPACES ||--o{ BUILDINGS : "scopes"
    WORKSPACES ||--o{ MULTI_UNIT_GROUPS : "scopes"
    WORKSPACES ||--o{ AGREEMENTS_AGREEMENTS : "scopes"
    WORKSPACES ||--o{ CATALOG_PRODUCT_GROUPS : "scopes"
    WORKSPACES ||--o{ TASKS : "scopes"
    WORKSPACES ||--o{ TICKETS : "scopes"
    WORKSPACES ||--o{ WORKSPACES_INQUIRIES : "scopes"
    WORKSPACES ||--o{ INVOICE_LIST_ITEMS : "scopes (via workspace_uuid)"
    WORKSPACES ||--o{ REPORT_AR_DOCUMENTS : "scopes"
    WORKSPACES ||--o{ REPORT_AR_BALANCES : "scopes"
    WORKSPACES ||--o{ BOOKINGS_INSTALMENT_PLAN_TEMPLATES : "scopes"

    PROPERTIES ||--o{ AVAILABILITY_UNIT_AVAILABILITY_POOL_ALLOCATIONS : "has many"
    PROPERTIES ||--o{ UNIT_CALENDARS : "has many"
    PROPERTIES ||--o{ BOOKINGS : "has many"
    PROPERTIES ||--o{ DAILY_REVENUES : "has many"
    PROPERTIES }o..o{ TARIFFS : "soft ref (tariff_ids array)"

    MULTI_UNIT_GROUPS ||--o{ MULTI_UNIT_GROUP_CALENDARS : "has many"

    ADDRESSES }o..o{ PROPERTIES : "soft ref"
    ADDRESSES }o..o{ BUILDINGS : "soft ref"

    INTERVAL_PRICING_CALENDARS }o..o{ PROPERTIES : "soft ref"
    INTERVAL_PRICING_CALENDARS }o..o{ MULTI_UNIT_GROUPS : "soft ref"

    BOOKINGS ||--o{ BILLING_PERIODS : "has many"
    BOOKINGS ||--o{ BOOKING_CONTRACT_VALUES : "has many (versioned)"
    BOOKINGS ||--o{ BOOKING_DAILY_RATES : "has many"
    BOOKINGS |o..o{ DAILY_REVENUES : "has many (soft ref)"
    BOOKINGS ||--o| BOOKING_RATES : "has one"
    BOOKINGS ||--o{ FINANCE_BILLING_CYCLES : "has many"
    BOOKINGS ||--o{ GUEST_PAYMENTS : "has many"
    BOOKINGS ||--o{ REPORT_AR_DOCUMENTS : "issued documents"
    BOOKINGS ||--o{ BOOKING_GUESTS : "has many"
    BOOKINGS ||--o{ BOOKING_GUARANTOR_INFORMATIONS : "has many"
    BOOKINGS }o..o{ BOOKING_CONTEXT_PARTICIPANTS : "soft ref (shared booking_context_id)"

    INVOICING_INVOICES ||--o{ INVOICE_LIST_ITEMS : "has many"

    CATALOG_PRODUCT_GROUPS ||--o{ CATALOG_PRODUCTS : "has many"
    CATALOG_PRODUCT_RATES ||--o{ CATALOG_PRODUCTS : "prices"
    CATALOG_PRODUCT_RATES ||--o{ CATALOG_RATE_CALENDARS : "has many"

    AGREEMENTS_AGREEMENTS ||--o{ AGREEMENTS_AGREEMENT_UNIT_TYPE_ALLOCATIONS : "has many"
    AGREEMENTS_AGREEMENTS }o..o{ AGREEMENTS_AGREEMENT_VERSIONS : "soft ref (PaperTrail)"
    AGREEMENTS_AGREEMENT_UNIT_TYPE_ALLOCATIONS }o..o{ AGREEMENTS_AGREEMENT_UNIT_TYPE_ALLOCATION_VERSIONS : "soft ref"

    INQUIRIES ||--o{ INQUIRY_PARTICIPANTS : "has many"
    OFFER_MANAGEMENT_OFFERS }o..o{ INQUIRIES : "soft ref"
    OFFER_MANAGEMENT_OFFERS ||--o{ OFFER_MANAGEMENT_OFFER_ACCOMMODATIONS : "has many"

    MULTI_UNIT_GROUPS ||--o{ PROPERTIES : "unit type (optional)"
    PROPERTIES ||--o{ PROPERTIES : "parent unit (optional)"
    TICKETS ||--o{ PROPERTIES : "onboarding ticket (optional)"
    MULTI_UNIT_GROUPS ||--o{ AGREEMENTS_AGREEMENT_UNIT_TYPE_ALLOCATIONS : "allocated to"
    MULTI_UNIT_GROUPS ||--o{ BOOKINGS : "unit type (optional)"
    CATALOG_PRODUCTS ||--o{ BOOKINGS : "product (optional)"
    BILLING_PERIODS ||--o{ BOOKING_DAILY_RATES : "has many"
    INVOICING_INVOICES ||--o{ BOOKING_DAILY_RATES : "issued invoice (optional)"
    INVOICING_INVOICES ||--o{ GUEST_PAYMENTS : "has many"
    LEAD_STATUSES ||--o{ INQUIRIES : "has many (optional)"
    CRM_NAMESPACES ||--o{ INQUIRIES : "namespace scope"
    CRM_NAMESPACES ||--o{ LEAD_STATUSES : "namespace scope"
    CRM_NAMESPACES ||--o{ CRM_COMPANIES : "namespace scope"
    CRM_COMPANIES ||--o{ CRM_COMPANIES_CONTACT_INFORMATIONS : "has many"
    CRM_CONTACT_INFORMATIONS ||--o{ CRM_COMPANIES_CONTACT_INFORMATIONS : "linked to companies"
    CRM_CONTACT_INFORMATIONS ||--o{ BOOKING_GUESTS : "contact (via unique_reference)"
    CRM_CONTACT_INFORMATIONS ||--o{ BOOKING_GUARANTOR_INFORMATIONS : "contact (via unique_reference)"
    CRM_CONTACT_INFORMATIONS ||--o{ BOOKING_CONTEXT_PARTICIPANTS : "contact (via unique_reference)"
    CRM_CONTACT_INFORMATIONS ||--o{ CRM_CONTACT_RELATIONSHIPS : "has relationships (source)"
    CRM_CONTACT_INFORMATIONS ||--o{ CRM_CONTACT_RELATIONSHIPS : "is related contact (target)"
    TAGS_TAGS ||--o{ TAGS_ENTITY_TAGS : "attached as"
    TAGS_ENTITY_TAGS }o..o| CATALOG_PRODUCT_GROUPS : "soft ref (ref, entity = product_group)"
    TAGS_ENTITY_TAGS }o..o| BOOKINGS_INSTALMENT_PLAN_TEMPLATES : "soft ref (ref, entity = instalment_plan_template)"
    BOOKINGS_INSTALMENT_PLAN_TEMPLATES }o..o{ CATALOG_PRODUCT_GROUPS : "soft ref (product_group_ids array)"
    INQUIRIES ||--o{ WORKSPACES_INQUIRIES : "has many"
    BUILDINGS ||--o{ OFFER_MANAGEMENT_OFFERS : "has many"
    TARIFFS ||--o{ INTERVAL_PRICING_CALENDARS : "priced by"
    BOOKINGS ||--o{ INVOICE_LIST_ITEMS : "has many"
    BILLING_PERIODS ||--o{ INVOICE_LIST_ITEMS : "has many"
    PROPERTIES ||--o{ TASKS : "has many (optional)"
    TICKETS ||--o{ TASKS : "parent ticket (optional)"
    BOOKINGS ||--o{ TASKS : "has many (optional)"
    TASK_CATEGORIES ||--o{ TASKS : "has many"
    PROPERTIES ||--o{ TICKETS : "has many"
    BOOKINGS ||--o{ UNIT_CALENDARS : "has many (optional)"

CRM_CONTACT_INFORMATIONS’s own relational column, crm_contact_entity_id, points at crm_contact_entities, which this export does not serve (see External references below); its drawn edges are the reverse ones from the external_contact_id of BOOKING_GUESTS, BOOKING_GUARANTOR_INFORMATIONS and BOOKING_CONTEXT_PARTICIPANTS, which match its unique_reference. CRM_CONTACT_RELATIONSHIPS is scoped through its source contact’s crm_contact_entities row and that row’s CRM namespace; TAGS_TAGS and TAGS_ENTITY_TAGS are scoped through the tag’s tags_containers row, which carries a crm_namespace_uuid. Neither crm_contact_entities nor tags_containers is served. CRM_NAMESPACES, LEAD_STATUSES and TASK_CATEGORIES are all tenant-scoped through a crm_namespace_uuid/workspace_id/task_category_id chain that never touches a column the WORKSPACES view itself serves (see the evidence table) — they connect to other served tables via documented-fk edges instead, not through WORKSPACES directly.

Soft and polymorphic references

Source table	Column(s)	Discriminator	Possible targets	Evidence view
`ADDRESSES`	`addressable_id`	`addressable_type`	`PROPERTIES`, `BUILDINGS`	`addresses.sql` (two `UNION` branches)
`AGREEMENTS_AGREEMENT_VERSIONS`	`item_id`	`item_type`	`AGREEMENTS_AGREEMENTS`	`agreements_agreement_versions.sql` (PaperTrail)
`AGREEMENTS_AGREEMENT_UNIT_TYPE_ALLOCATION_VERSIONS`	`item_id`	`item_type`	`AGREEMENTS_AGREEMENT_UNIT_TYPE_ALLOCATIONS`	`agreements_agreement_unit_type_allocation_versions.sql` (PaperTrail)
`INTERVAL_PRICING_CALENDARS`	`bookable_id`	`bookable_type`	`PROPERTIES`, `MULTI_UNIT_GROUPS`	`interval_pricing_calendars.sql` (two `LEFT JOIN` branches)
`OFFER_MANAGEMENT_OFFERS`	`resource_owner_id`	`resource_owner_type`	`INQUIRIES` (documented as always `'inquiry'`)	`offer_management_offers.sql`
`PROPERTIES`	`tariff_ids` (array)	— (array of IDs, no discriminator)	`TARIFFS`	`tariffs.sql` (many-to-many via `UNNEST`)
`BOOKING_CONTEXT_PARTICIPANTS`	`booking_context_id`	— (shared key, no discriminator)	`BOOKINGS` (every booking in the context)	`booking_context_participants.sql` (CTE over `bookings`)
`BOOKINGS_INSTALMENT_PLAN_TEMPLATES`	`product_group_ids` (array)	— (array of IDs, no discriminator)	`CATALOG_PRODUCT_GROUPS`	`bookings_instalment_plan_templates.sql` (projected, not joined)
`TAGS_ENTITY_TAGS`	`ref` (text)	`TAGS_TAGS.scope` + `TAGS_TAGS.entity` (via `tag_id`)	`CATALOG_PRODUCT_GROUPS` (`catalog` / `product_group`), `BOOKINGS_INSTALMENT_PLAN_TEMPLATES` (`accounting` / `instalment_plan_template`)	`tags_entity_tags.sql` (projected, not joined)

External references (not served by this export)

Some *_id/*_uuid columns are real foreign keys, but their target table is not exported, so there is no entity in this diagram to draw an edge to. Documented here instead so they aren’t mistaken for gaps in the audit:

Source table	Column	Target (not served)
`CRM_CONTACT_INFORMATIONS`	`crm_contact_entity_id`	`crm_contact_entities` — the real join key between CRM namespaces and contact records; not exported.
`AGREEMENTS_AGREEMENTS`	`company_id`	An internal CRM company record; not exported.
`CRM_COMPANIES`	`crm_namespace_id`	The internal `crm_namespaces.id`, which the `crm_namespaces` view does not serve; join on `crm_namespace_uuid` instead.
`TAGS_TAGS`	`container_id`	`tags_containers` — one tag container per CRM namespace; synced for tenant scoping but not exported.

INVOICING_INVOICES.payload is a JSON blob, not a column-level FK, so it can’t appear in the evidence table below — but it embeds identifiers for other served (and unserved) entities, commonly including a booking ID, a booking-context ID, and guest-payment IDs. These are not queryable as columns and are not drawn as edges; if you need them, they must be extracted from the JSON payload itself.

Evidence — view to confirmed parents

One row per JOIN predicate found in each view’s SQL (scope-hop rows are tenant-filtering hops that do not touch the served table’s own alias and are not drawn as edges), plus one row per documented-fk column — a real foreign key confirmed by its sources.yml description rather than by a JOIN in this particular view’s SQL.

View	Joined table	Join predicate	Class	Note
`addresses.sql`	`properties`	`t.addressable_id = p.id`	soft-ref	`UNION` branch 1; `addressable_type` confirms target
`addresses.sql`	`workspaces`	`p.workspace_id = ws.id`	scope-hop	via `properties`, not `addresses`’ own column
`addresses.sql`	`buildings`	`t.addressable_id = b.id`	soft-ref	`UNION` branch 2; `addressable_type` confirms target
`addresses.sql`	`workspaces`	`b.workspace_id = ws.id`	scope-hop	via `buildings`
`agreements_agreement_unit_type_allocation_versions.sql`	`agreements_agreement_unit_type_allocations`	`t.item_id = aaut.id`	soft-ref	PaperTrail `item_type`/`item_id`, single confirmed target
`agreements_agreement_unit_type_allocation_versions.sql`	`agreements_agreements`	`aaut.agreement_id = aa.id`	scope-hop	via allocations, not own alias
`agreements_agreement_unit_type_allocation_versions.sql`	`workspaces`	`aa.workspace_id = ws.id`	scope-hop	via `agreements_agreements`
`agreements_agreement_unit_type_allocations.sql`	`agreements_agreements`	`t.agreement_id = aa.id`	hard-fk	own column, projected
`agreements_agreement_unit_type_allocations.sql`	`workspaces`	`aa.workspace_id = ws.id`	scope-hop	via `agreements_agreements`
`agreements_agreement_versions.sql`	`agreements_agreements`	`t.item_id = aa.id`	soft-ref	PaperTrail `item_type`/`item_id`, single confirmed target
`agreements_agreement_versions.sql`	`workspaces`	`aa.workspace_id = ws.id`	scope-hop	via `agreements_agreements`
`agreements_agreements.sql`	`workspaces`	`t.workspace_id = ws.id`	tenant-direct	own `workspace_id` column
`availability_unit_availability_pool_allocations.sql`	`properties`	`p.id = t.unit_id`	hard-fk	own column `t.unit_id`, projected
`availability_unit_availability_pool_allocations.sql`	`workspaces`	`ws.id = p.workspace_id`	scope-hop	via `properties`
`billing_periods.sql`	`bookings`	`b.id = t.booking_id`	hard-fk	own column, projected
`billing_periods.sql`	`properties`	`p.id = b.property_id`	scope-hop	via `bookings`; tenant filter is `WHERE p.workspace_id IN (...)`, no `workspaces` join
`booking_context_participants.sql`	`bookings` (via CTE)	`e.booking_context_id = t.booking_context_id`	soft-ref	own column, projected; a context spans several bookings, so `DISTINCT` CTE
`booking_context_participants.sql`	`properties` (nested in CTE)	`p.id = b.property_id`	scope-hop	via `bookings`
`booking_context_participants.sql`	`workspaces` (nested in CTE)	`ws.id = p.workspace_id`	scope-hop	via `properties`
`booking_contract_values.sql`	`bookings`	`b.id = t.booking_id`	hard-fk	own column, projected
`booking_contract_values.sql`	`properties`	`p.id = b.property_id`	scope-hop	via `bookings`
`booking_contract_values.sql`	`workspaces`	`ws.id = p.workspace_id`	scope-hop	via `properties`
`booking_daily_rates.sql`	`bookings`	`b.id = t.booking_id`	hard-fk	own column, projected
`booking_daily_rates.sql`	`properties`	`p.id = b.property_id`	scope-hop	via `bookings`
`booking_daily_rates.sql`	`workspaces`	`ws.id = p.workspace_id`	scope-hop	via `properties`
`booking_guarantor_informations.sql`	`bookings`	`b.id = t.booking_id`	hard-fk	own column, projected
`booking_guarantor_informations.sql`	`properties`	`p.id = b.property_id`	scope-hop	via `bookings`
`booking_guarantor_informations.sql`	`workspaces`	`ws.id = p.workspace_id`	scope-hop	via `properties`
`booking_guests.sql`	`bookings`	`b.id = t.booking_id`	hard-fk	own column, projected
`booking_guests.sql`	`properties`	`p.id = b.property_id`	scope-hop	via `bookings`
`booking_guests.sql`	`workspaces`	`ws.id = p.workspace_id`	scope-hop	via `properties`
`booking_rates.sql`	`bookings`	`b.id = t.booking_id`	hard-fk	own column, projected
`booking_rates.sql`	`properties`	`p.id = b.property_id`	scope-hop	via `bookings`; `WHERE p.workspace_id IN (...)`, no `workspaces` join
`bookings.sql`	`properties`	`p.id = t.property_id`	hard-fk	own column, projected
`bookings.sql`	`workspaces`	`p.workspace_id = ws.id`	scope-hop	via `properties`
`bookings_instalment_plan_templates.sql`	`workspaces`	`CAST(ws.id AS VARCHAR) = t.workspace_id`	tenant-direct	own `workspace_id` column (text in Abacus)
`buildings.sql`	`workspaces`	`t.workspace_id = ws.id`	tenant-direct	own `workspace_id` column
`catalog_product_groups.sql`	`workspaces`	`t.workspace_id IN ({{ client_workspace_ids }})`	tenant-direct	`WHERE` filter on own column, no `JOIN` keyword
`catalog_product_rates.sql`	`catalog_products`	`t.id IN (SELECT cp.product_rate_id …)`	hard-fk (reverse)	confirms `CATALOG_PRODUCTS.product_rate_id` → this view
`catalog_product_rates.sql`	`catalog_product_groups`	`pg.id = cp.product_group_id`	scope-hop	inside the `IN` subquery tenant-scope chain
`catalog_product_rates.sql`	`workspaces`	`ws.id = pg.workspace_id`	scope-hop	inside the `IN` subquery tenant-scope chain
`catalog_products.sql`	`catalog_product_groups`	`pg.id = t.product_group_id`	hard-fk	own column, projected
`catalog_products.sql`	`workspaces`	`ws.id = pg.workspace_id`	scope-hop	via `catalog_product_groups`
`catalog_rate_calendars.sql`	`catalog_products`	`t.product_rate_id IN (SELECT cp.product_rate_id …)`	hard-fk (transitive)	own column `t.product_rate_id`; combined with `catalog_product_rates.sql`’s row confirms → `CATALOG_PRODUCT_RATES`
`catalog_rate_calendars.sql`	`catalog_product_groups`	`pg.id = cp.product_group_id`	scope-hop	inside the `IN` subquery tenant-scope chain
`catalog_rate_calendars.sql`	`workspaces`	`ws.id = pg.workspace_id`	scope-hop	inside the `IN` subquery tenant-scope chain
`crm_companies.sql`	`crm_namespaces` (via CTE)	`e.id = t.crm_namespace_id`	hard-fk	own column, projected; the CTE also projects `cn.uuid AS crm_namespace_uuid`
`crm_companies.sql`	`workspaces` (nested in CTE)	`ws.crm_namespace_uuid = cn.uuid`	scope-hop	tenant eligibility chain
`crm_companies_contact_informations.sql`	`crm_companies` (via CTE)	`e.id = t.crm_company_id`	hard-fk	own column, projected
`crm_companies_contact_informations.sql`	`crm_namespaces`, `workspaces` (nested in CTE)	`cn.id = c.crm_namespace_id`, `ws.crm_namespace_uuid = cn.uuid`	scope-hop	tenant eligibility chain
`crm_companies_contact_informations.sql`	`crm_contact_informations`	(documented-fk) `sources.yml` `crm_contact_information_id`: “Identifier of the contact; matches crm_contact_informations.id”	documented-fk	no JOIN in view SQL
`crm_contact_informations.sql`	`inquiries` (via CTE)	`e.external_contact_id = t.unique_reference`	tenant-scope	filters to contacts referenced by client leads; not a modeled parent edge
`crm_contact_informations.sql`	`workspaces` (nested in CTE)	`ws.crm_namespace_uuid = i.crm_namespace_uuid`	scope-hop	via `inquiries` CTE
`crm_contact_informations.sql`	`crm_contact_relationships` (via CTE)	`pe.external_contact_id = sci.unique_reference`, `tci.id = r.target_contact_information_id`	tenant-scope	also serves the related contacts of contacts eligible through the participant paths
`crm_contact_relationships.sql`	`crm_contact_informations`	`sci.id = t.source_contact_information_id`	hard-fk	own column, projected
`crm_contact_relationships.sql`	`crm_contact_entities`, `crm_namespaces`, `workspaces` (nested in CTE)	`sce.id = sci.crm_contact_entity_id`, `e.id = sce.crm_namespace_id`, `ws.crm_namespace_uuid = cn.uuid`	scope-hop	tenant eligibility chain; `crm_contact_entities` is not served
`crm_contact_relationships.sql`	`crm_contact_informations`	(documented-fk) `sources.yml` `target_contact_information_id`: “Identifier of the related contact (e.g. the emergency contact); matches crm_contact_informations.id”	documented-fk	no JOIN on the target in view SQL
`crm_namespaces.sql`	`workspaces` (via CTE)	`e.crm_namespace_uuid = cn.uuid`	tenant-scope	`workspaces` view doesn’t serve `crm_namespace_uuid`; no edge drawn
`daily_revenues.sql`	`properties`	`p.id = t.property_id`	hard-fk	own column, projected
`daily_revenues.sql`	`workspaces`	`ws.id = p.workspace_id`	scope-hop	via `properties`
`finance_billing_cycles.sql`	`bookings`	`b.id = t.booking_id`	hard-fk	own column, projected
`finance_billing_cycles.sql`	`properties`	`p.id = b.property_id`	scope-hop	via `bookings`
`finance_billing_cycles.sql`	`workspaces`	`ws.id = p.workspace_id`	scope-hop	via `properties`
`guest_payments.sql`	`bookings`	`b.id = t.booking_id`	hard-fk	own column, projected
`guest_payments.sql`	`properties`	`p.id = b.property_id`	scope-hop	via `bookings`
`guest_payments.sql`	`workspaces`	`ws.id = p.workspace_id`	scope-hop	via `properties`
`inquiries.sql`	`workspaces` (via CTE)	`e.crm_namespace_uuid = t.crm_namespace_uuid`	tenant-scope	own column, but namespace-based, not `workspace_id`; `workspaces` view doesn’t serve `crm_namespace_uuid`
`inquiry_participants.sql`	`inquiries` (via CTE)	`e.inquiry_id = t.inquiry_id`	hard-fk	own column, projected; CTE resolves to `inquiries.id`
`inquiry_participants.sql`	`workspaces` (nested in CTE)	`ws.crm_namespace_uuid = i.crm_namespace_uuid`	scope-hop	via `inquiries`
`interval_pricing_calendars.sql`	`properties`	`t.bookable_type = 'Spaces::Property' AND t.bookable_id = p.id`	soft-ref	`LEFT JOIN` branch 1
`interval_pricing_calendars.sql`	`multi_unit_groups`	`t.bookable_type = 'Spaces::MultiUnitGroup' AND t.bookable_id = mug.id`	soft-ref	`LEFT JOIN` branch 2
`interval_pricing_calendars.sql`	`workspaces`	`ws.id = COALESCE(p.workspace_id, mug.workspace_id)`	scope-hop	via whichever branch matched
`invoice_list_items.sql`	`workspaces`	`ws.uuid = t.workspace_uuid`	tenant-direct	own column, UUID variant of `workspace_id`
`invoicing_invoices.sql`	`invoice_list_items`	`il.invoice_uuid = t.uuid`	hard-fk (reverse)	confirms `INVOICE_LIST_ITEMS.invoice_uuid` → this view
`invoicing_invoices.sql`	`workspaces`	`ws.uuid = il.workspace_uuid`	scope-hop	via `invoice_list_items`
`lead_statuses.sql`	`workspaces` (via CTE)	`e.crm_namespace_uuid = t.crm_namespace_uuid`	tenant-scope	not the `workspace_id` pattern; no edge drawn
`multi_unit_group_calendars.sql`	`multi_unit_groups`	`mug.id = t.multi_unit_group_id`	hard-fk	own column, projected
`multi_unit_group_calendars.sql`	`workspaces`	`mug.workspace_id = ws.id`	scope-hop	via `multi_unit_groups`
`multi_unit_groups.sql`	`workspaces`	`t.workspace_id IN ({{ client_workspace_ids }})`	tenant-direct	`WHERE` filter on own column, no `JOIN` keyword
`offer_management_offer_accommodations.sql`	`offer_management_offers` (via CTE)	`e.offer_id = t.offer_id`	hard-fk	own column, projected
`offer_management_offer_accommodations.sql`	`inquiries` (nested in CTE)	`i.unique_reference = omo.resource_owner_id`	scope-hop	tenant eligibility chain, not this view’s own alias
`offer_management_offer_accommodations.sql`	`workspaces` (nested in CTE)	`ws.crm_namespace_uuid = i.crm_namespace_uuid`	scope-hop	tenant eligibility chain
`offer_management_offers.sql`	`inquiries` (via CTE)	`e.unique_reference = t.resource_owner_id`	soft-ref	`resource_owner_type`/`resource_owner_id` pair; documented as always `'inquiry'`
`offer_management_offers.sql`	`workspaces` (nested in CTE)	`ws.crm_namespace_uuid = i.crm_namespace_uuid`	scope-hop	tenant eligibility chain
`properties.sql`	`workspaces`	`t.workspace_id = ws.id`	tenant-direct	own `workspace_id` column
`property_calendars.sql`	`properties`	`p.id = t.property_id`	hard-fk (excluded)	table is `DEPRECATED`; not drawn
`property_calendars.sql`	`workspaces`	`p.workspace_id = ws.id`	scope-hop (excluded)	table is `DEPRECATED`; not drawn
`report_ar_balances.sql`	`workspaces`	`t.workspace_id IN ({{ client_workspace_ids }})`	tenant-direct	own `workspace_id` column; no issued filter
`report_ar_documents.sql`	`workspaces`	`t.workspace_id IN ({{ client_workspace_ids }})`	tenant-direct	own `workspace_id` column; issued documents only
`tariffs.sql`	`properties` (via CTE, `UNNEST(tariff_ids)`)	`t._id = wti.tariff_id`	soft-ref	many-to-many; `PROPERTIES.tariff_ids` → `TARIFFS.id`
`tariffs.sql`	`workspaces`	`wti.workspace_id = ws.id`	scope-hop	via `properties` CTE
`tags_entity_tags.sql`	`tags_tags`	`tt.id = t.tag_id`	hard-fk	own column, projected
`tags_entity_tags.sql`	`tags_containers`, `workspaces` (nested in CTE)	`e.id = tt.container_id`, `ws.crm_namespace_uuid = tc.crm_namespace_uuid`	scope-hop	tenant eligibility chain; `tags_containers` is not served
`tags_tags.sql`	`tags_containers`, `workspaces` (nested in CTE)	`e.id = t.container_id`, `ws.crm_namespace_uuid = tc.crm_namespace_uuid`	scope-hop	tenant eligibility chain; `tags_containers` is not served
`task_categories.sql`	—	—	none	no `JOIN` and no tenant filter — global lookup table
`tasks.sql`	`workspaces`	`t.workspace_id = ws.id`	tenant-direct	own `workspace_id` column
`tickets.sql`	`workspaces`	`t.workspace_id = ws.id`	tenant-direct	own `workspace_id` column
`unit_calendars.sql`	`properties`	`p.id = t.unit_id`	hard-fk	own column, projected
`unit_calendars.sql`	`workspaces`	`p.workspace_id = ws.id`	scope-hop	via `properties`
`workspaces.sql`	`workspaces` (self)	`ws.id IN ({{ client_workspace_ids }})`	tenant-direct	root of the scoping chain
`workspaces_inquiries.sql`	`workspaces`	`wi.workspace_id = ws.id`	tenant-direct	own `workspace_id` column; `inquiry_id` has no confirming `JOIN` in any of the 50 views
`properties.sql`	`multi_unit_groups`	(documented-fk) `sources.yml`: “The ID of the unit type; each unit can either have an ID or not”	documented-fk	no JOIN in view SQL; optional (nullable)
`properties.sql`	`properties` (self)	(documented-fk) `sources.yml` `parent_unit_id`: “The parent unit ID for this unit”	documented-fk	self-referential; optional
`properties.sql`	`tickets`	(documented-fk) `sources.yml` `onboarding_ticket_id`: “The ID of the onboarding ticket for this unit”	documented-fk	no JOIN in view SQL; optional
`report_ar_documents.sql`	`bookings`	(documented-fk) `sources.yml` `booking_id`: “Identifier of the booking the document was issued against”	documented-fk	no JOIN in view SQL
`agreements_agreement_unit_type_allocations.sql`	`multi_unit_groups`	(documented-fk) `sources.yml` `unit_type_id`: “Unit type identifier”	documented-fk	no JOIN in view SQL
`bookings.sql`	`multi_unit_groups`	(documented-fk) `sources.yml` `multi_unit_group_id`: “The unit type ID for this booking”	documented-fk	no JOIN in view SQL; optional
`bookings.sql`	`catalog_products`	(documented-fk) `sources.yml` `product_id`: “The ID of the Sales Proposition product linked to this booking”	documented-fk	no JOIN in view SQL; optional
`daily_revenues.sql`	`bookings`	(documented-fk) `sources.yml` `booking_id`: “Identifier for the booking the revenue belongs to”	documented-fk	no JOIN in view SQL
`booking_daily_rates.sql`	`billing_periods`	(documented-fk) `sources.yml` `billing_period_id`: “Identifier for the billing period associated with the rate”	documented-fk	no JOIN in view SQL
`booking_daily_rates.sql`	`invoicing_invoices`	(documented-fk) `sources.yml` `issued_invoice_uuid`: “UUID of the issued invoice (if applicable)”	documented-fk	no JOIN in view SQL; optional
`guest_payments.sql`	`invoicing_invoices`	(documented-fk) `sources.yml` `invoice_uuid`: “A unique UUID for the invoice related to this payment”	documented-fk	no JOIN in view SQL
`inquiries.sql`	`lead_statuses`	(documented-fk) `sources.yml` `lead_status_id`: “This is the ID of the lead status, not the lead”	documented-fk	no JOIN in view SQL; optional
`inquiries.sql`	`crm_namespaces`	(documented-fk) `sources.yml` `crm_namespace_uuid`: “The UUID for the CRM namespace”	documented-fk	no JOIN in view SQL
`interval_pricing_calendars.sql`	`tariffs`	(documented-fk) `sources.yml` `tariff_id`: “Identifier of the associated tariff”	documented-fk	no JOIN in view SQL; distinct from the `properties.tariff_ids` many-to-many soft ref
`invoice_list_items.sql`	`bookings`	(documented-fk) `sources.yml` `booking_id`: “The ID of the booking”	documented-fk	no JOIN in view SQL
`invoice_list_items.sql`	`billing_periods`	(documented-fk) `sources.yml` `billing_period_id`: “The ID of the billing period”	documented-fk	no JOIN in view SQL
`offer_management_offers.sql`	`buildings`	(documented-fk) `sources.yml` `building_id`: “The ID of the building”	documented-fk	no JOIN in view SQL
`lead_statuses.sql`	`crm_namespaces`	(documented-fk) `sources.yml` `crm_namespace_uuid`: “UUID of the CRM namespace associated with the lead status”	documented-fk	no JOIN in view SQL; view SQL only joins `workspaces` for tenant scope
`tasks.sql`	`properties`	(documented-fk) `sources.yml` `property_id`: “The identifier of the associated unit”	documented-fk	no JOIN in view SQL; optional
`tasks.sql`	`tickets`	(documented-fk) `sources.yml` `parent_ticket_id`: “The identifier of the parent ticket, if applicable”	documented-fk	no JOIN in view SQL; optional
`tasks.sql`	`bookings`	(documented-fk) `sources.yml` `booking_id`: “The ID of the booking”	documented-fk	no JOIN in view SQL; optional
`tasks.sql`	`task_categories`	(documented-fk) `sources.yml` `task_category_id`: “The identifier of the task category to which the task belongs”	documented-fk	no JOIN in view SQL
`tickets.sql`	`properties`	(documented-fk) `sources.yml` `property_id`: “The ID of the unit”	documented-fk	no JOIN in view SQL
`booking_guests.sql`	`crm_contact_informations`	(documented-fk) `sources.yml` `external_contact_id`: “External contact reference; matches crm_contact_informations.unique_reference”	documented-fk	no JOIN in view SQL; `crm_contact_informations.sql` joins the reverse way
`booking_guarantor_informations.sql`	`crm_contact_informations`	(documented-fk) `sources.yml` `external_contact_id`: “External contact reference; matches crm_contact_informations.unique_reference”	documented-fk	no JOIN in view SQL
`booking_context_participants.sql`	`crm_contact_informations`	(documented-fk) `sources.yml` `external_contact_id`: “External contact reference; matches crm_contact_informations.unique_reference”	documented-fk	no JOIN in view SQL
`unit_calendars.sql`	`bookings`	(documented-fk) `sources.yml` `booking_id`: “Identifier of the associated booking, if applicable”	documented-fk	no JOIN in view SQL; optional
`workspaces_inquiries.sql`	`inquiries`	(documented-fk) `sources.yml` `inquiry_id`: “Identifier for the associated lead”	documented-fk	no JOIN in view SQL; supersedes the earlier “no confirming JOIN” note

Excluded: deprecated views

property_calendars is already marked DEPRECATED in sources.yml — superseded by unit_calendars (availability/booking spans) and interval_pricing_calendars (pricing). It is omitted from the diagram entirely, not greyed out.

This change also ran the mechanical deprecation rules (a source with no matching view file; a projected column that is a SQL literal/NULL constant; a table whose full grain is superseded by two or more other served views) against all 39 views. None of the three rules fired on any table or column beyond the pre-existing property_calendars flag, so no further sources.yml edits were made in this change.

Keeping this in sync

This diagram is hand-maintained — there is no CI drift guard for it. make check-client-views-sources (scripts/client_data/validate_views_sources.py) checks that every view file has a matching sources.yml entry and vice versa, but it does not validate this document’s diagram content. When a view’s SQL or sources.yml changes in a way that adds, removes, or re-parents a relationship, update this document in the same PR.

Client Data Dictionary — column-level reference for every table listed above.