Tables

Defines the discount amounts and effective periods for discount campaigns. Covers all three campaign scope types — main contract rate bundle (RATE_BUNDLE), flat-fee bundle (FLAT_FEE), and optional add-on module (MODULE) — in a single table.

Each row represents one discount tier (position) for one campaign scope entry. Multiple tiers per scope entry are possible (e.g. position 0: 100% off for 3 months, position 1: 50% off for the following 3 months).

Join to dim_discount_campaign_scope on discount_campaign_scope_id to determine which rate bundles or terms the discount applies to.

Table contains all employees and their relevant information

A definition of a voucher that can be generated into voucher codes and applied to a contract to get a discount or credit. Vouchers can be used to grant discounts on contracts, waive fees, or provide credit.

Dimension table exposing the dunning and billing stop configuration per customer. Each customer has at most one row. All four stop types are sourced from a single record configured manually by studio operators.

Stop types use a three-value LimitType pattern: - DISABLED — the stop is not active - UNLIMITED — the stop is permanently active (until_date is NULL) - LIMITED — the stop is active until until_date (inclusive)

To test whether a stop is currently active as of a reference date, apply: _limit_type = ‘UNLIMITED’ OR (_limit_type = ‘LIMITED’ AND _until_date >= <reference_date>)

Important: This table covers only manually configured stops set by studio operators. Dunning-engine-triggered collection stops (automatically set when a dunning level is reached) are stored in fct_dunning_step (collection_stop column). Combine both sources for a complete collection stop picture:

-- All customers with an active collection stop as of a reference date:
SELECT customer_id FROM dim_customer_dunning_configuration
WHERE collection_stop_limit_type = 'UNLIMITED'
   OR (collection_stop_limit_type = 'LIMITED' AND collection_stop_until_date >= '<ref_date>')
UNION
SELECT customer_id FROM fct_dunning_step
WHERE collection_stop IS TRUE
  AND dunning_date <= '<ref_date>'
  AND (withdrawn_date IS NULL OR withdrawn_date::date > '<ref_date>')

Use cases: - Active billable member counts: exclude customers where collection_stop_limit_type is UNLIMITED or (LIMITED and collection_stop_until_date >= reference_date) - Dunning portfolio analysis: identify customers where dunning is paused - Debt collection oversight: track customers blocked from external collection handover - Invoice audit: identify customers where invoice generation is suppressed

Defines the group discount schedule for a customer group. Each row is one step in the schedule: when the number of eligible group members reaches or exceeds min_member_count, that step’s discount_value becomes the group discount applied to all eligible members’ contracts. The highest matching step is always used — for example, with thresholds at 2 members (5%) and 4 members (10%), a group of 4 eligible members receives a 10% group discount. dim_customer_group.discount_value is then added on top of the matched step’s value. Join to dim_customer_group on customer_group_id to get the group type and discount_type (percentage or absolute) that applies to all steps.

Date dimension covering years 1900-2099

The term of the contract.

Link organization unit (gym) to all its organization unit codes (tags).

Bridge table linking rate bundles to partner companies for corporate deals, including any cooperation identifier used for that partnership.

A device connected to the system, which enables registering customers using certain areas (the whole gym, the gym sauna) or machines (eg a massage bed) of a gym.

Campaign dimension

Bridge table listing which payment choices are allowed when selling a rate bundle.

Bridge table linking rates to their assigned rate codes (tags). A rate can have multiple codes and a code can be assigned to multiple rates.

DEPRECATED. Use dim_contract_cancellation_reason instead.

A reason for contract cancellation

Bridge table linking revenue transactions to purchased contingents (day passes, class packs, etc.). Handles many-to-many relationships where one sale position may create multiple contingents (e.g., bundle products, family packs). Use this table to analyze revenue by specific purchased contingent while avoiding grain issues and revenue duplication.

Example use cases: - “Show revenue generated from day pass sales with their utilization rates” - “Analyze which purchased contingents contribute most to revenue” - “Track revenue for bundle products that create multiple contingents”

A period during which a discount is applied in the context of a contract voucher. Discounts are defined at a hierarchical granularity: rate → term configuration → payment frequency. A null at any level means the discount applies to all values at that level and below. For example, a null rate_id means the discount covers all rates; a set rate_id with a null rate_term_configuration_id means all term configurations of that rate.

Bridge table linking each POS sale position to every payment actively settling it. A single sale position can be split across multiple payments (e.g. partial settlements or split payment methods), so this table has a many-to-many grain: one row per active sale-position-to-payment link. Use this table instead of the deprecated fct_pos_saleposition.internal_payment_id, which collapses splits down to a single link and loses this granularity.

A class that a customer can participate in, e.g. yoga, dance class, etc

Dimension table for dunning levels configured in the Magicline dunning engine. Each row represents one level in a dunning escalation ladder, as defined by a studio’s dunning configuration template.

Two level types exist (dunning_level_type): - DUNNING — a standard escalation step with configurable rules and actions. dunning_levels_order = 0 is the “No Dunning Level” baseline. - DEBT_COLLECTION — the terminal level representing handoff to an external debt collection agency. Has no configurable dunning rules.

Join from fct_dunning_step.dunning_level_id to obtain the level’s position in the escalation sequence and its type. Archived levels may appear on historical fct_dunning_step rows but are no longer assigned to new dunning steps.

Access control mapping that defines which services are available at which gym locations. Services must be explicitly whitelisted for a gym before they can be booked or used there. This enables multi-location businesses to offer different service menus at different facilities (e.g., specialized classes only at flagship locations, or region-specific offerings).

A set of basic contract properties, e.g whether or not contract is signed, via which service it was created, is contract disabled, which payment type was used for the contract, etc.

The unique event of an appointment on the calendar with its properties.

Maps studios (organization units) to their currently active discount campaign. One row per studio — at most one active discount campaign per studio is represented here. This is a point-in-time snapshot sourced from active_organization_unit_configuration, which holds at most one active discount campaign configuration per studio at any given time.

Important limitation: When a studio deactivates a discount campaign, the corresponding row is hard-deleted — no historical record is preserved.

A customer is a person who signed up to a studio owned by you or your franchise.

Localized public names and descriptions for discount campaigns, one row per campaign per locale. Used to display campaign information in the member’s preferred language (e.g. in the MySports app or online checkout). Follows the same pattern as dim_rate_localized and dim_cancellation_reason_localized.

A physical or virtual point-of-sale register, used to process sales and issue receipts.

This table contains all times of a day from 00:00:00 to 23:59:59 with the granularity of one second.

Irregular or recurring closure times for an organization unit (gym). Defines exceptions to the regular opening hours, including one-time closures and yearly recurring closures (holidays, maintenance, etc.). YEARLY entries are expanded into specific datetime ranges based on creation/archival dates.

Defines how often and how much is paid for an individual contract.

Localized text for contract voucher definitions, one row per definition per locale. Includes the public-facing name and description shown to members, plus the staff-only internal description used to categorise a promotion or voucher (not visible to members).

Defines which rate bundles (and optionally which specific terms) are in scope for each discount campaign, across all three price component types: RATE_BUNDLE (main contract), FLAT_FEE (flat-fee bundle), and MODULE (optional add-on). One row per scope entry.

A NULL rate_bundle_id means the campaign applies to all rate bundles of that type. A NULL rate_bundle_term_id means the campaign applies to all terms of the rate bundle (only meaningful when rate_bundle_id is also set).

Use this table to answer: “Which rate bundles have a discount campaign set on them?” and “Does this campaign apply to all bundles or only specific ones?”

Join with dim_discount_campaign_discount_period on discount_campaign_scope_id to get the actual discount amounts for each scoped entry.

The table contains simple properties of class events. This is a junk dimension that stores distinct combinations of event status, source, and booked_for_free flag.

Dimension table containing customer codes (tags) used to categorize customers. Customer codes help organize and filter customers based on business-defined categories. Each customer code has a unique identifier and can be associated with multiple customers.

Records each customer’s membership in a customer group, one row per membership event. Captures the validity period, the member’s individual discount eligibility, and — for Shared Contract type groups — the relationship role within the group. A customer may appear multiple times for the same group across non-overlapping periods. Join to dim_customer_group on customer_group_id to determine the group type, discount configuration, and member limits. Join to dim_customer on customer_id for customer attributes.

The localized version of a cancellation reason, defining translated information of a cancellation reason per locale. One cancellation reason can have multiple localized versions.

Contract cancellations with their reasons. Built from actual contract cancellations that occurred in the system. Includes information about how the cancellation was initiated, the type of cancellation, and the reason.

Company codes (tags) that can be assigned to companies for categorization and filtering purposes. Similar to customer codes but for partner companies.

DEPRECATED. Use dim_contract_cancellation_reason instead.

A set of basic properties of cancellation such as origin and if the cancellation was extraordinary. This is a junk dimension containing all distinct combinations of cancellation properties.

A discount campaign is a time-bound promotional discount configuration that studio administrators define and activate per studio. When active, it automatically applies a discount to contracts signed via the configured allowed sales channels (origin types). A campaign defines its validity window (active_from / active_to), how it interacts with discount vouchers (voucher_interoperability_mode), and which rate bundles / flat fees / modules it applies to.

Bridge table linking companies to their assigned company codes (tags). A company can have multiple codes and a code can be assigned to multiple companies.

Link customer to all his customer codes.

Maps discount campaigns to their allowed sales channel origin types. One row per (campaign, origin_type) pair. Only origin types listed here are permitted to trigger the campaign discount when a contract is signed.

If a contract is created via a channel not present in this table for the active campaign, the campaign discount is not applied.

A ‘partner company’ that contracts can be related to for the Corporate Fitness program. Only includes companies that are currently whitelisted for the studios related to this DWH instance.

Access control mapping that defines which products are available at which gym locations. Products can be made available at gyms through whitelisting (for MATERIAL, VOUCHER, and VIRTUAL types) or by being sold there (for dynamic ad-hoc products). This bridge table replaces the single organization_unit_id foreign key in dim_product to support the many-to-many relationship where products can be available at multiple locations.

All available currencies

Custom field values assigned to customers on lead or member level.

Dimension table that tracks customer communication consent preferences across different message categories. This table combines customer-level and organization-level communication configurations, with customer-level settings taking priority over organization-level defaults.

Bridge table linking companies to organization units (gyms) they are whitelisted for. Represents access permissions for gyms on companies.

One row per customer group configured in the system. Customer groups classify customers by membership arrangement and determine whether group discounts apply to their contracts. Groups belong to an organization unit and come in three types: Customer (a generic grouping for shared discounts), Premium (an elevated-tier group linking a referred customer to a referrer), and Shared Contract (a family plan in which all members share a single contract). The group type governs discount behaviour, member capacity limits, and contract linking throughout the model.