# 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](#excluded-deprecated-views)) and therefore covers the remaining **49**.
The [Client Data Dictionary](/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](#evidence--view-to-confirmed-parents) 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](#evidence--view-to-confirmed-parents) 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

```mermaid
%%{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](#external-references-not-served-by-this-export) 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.

## Related

- [Client Data Dictionary](/data-dictionary/) — column-level reference for every table listed
  above.