Reporting database: view reference

This document describes the vw_* views of the InnoMeer reporting database. It is written for people and AI assistants that query the database directly (SQL client, Power BI, Tableau, Metabase, an LLM with a read-only connection) instead of using the dashboards in this application.

The vw_* views are the intended query interface. The underlying dim_*, fact_*, source_* and *_queue tables are implementation details of the sync pipeline and may change; do not build reports on them. Everything you need is exposed through the views below.

• The database is PostgreSQL. All identifiers are lower-case snake_case.
• The views are read-only.
• View definitions live in db/occupancy_views.sql and db/revenue_views.sql and are applied by the database migrations. For the modelling background see occupancy-model.md and revenue-model.md.

Contents

1. Which view answers which question
2. Rules that apply to every view
3. Occupancy views
4. Revenue views
5. Example queries
6. Checklist for AI assistants

1. Which view answers which question

Rule of thumb: use the most aggregated view that still contains the columns you need. The aggregated views are smaller and faster; vw_revenue_base and vw_occupancy_hourly_base are the escape hatch.

2. Rules that apply to every view

Measures are additive, ratios are not

Every count, minute, amount and *_time_units column is additive: you may SUM it across any dimension, date range or hour. Every ratio and average (*_ratio, average_*) is not: it is pre-computed for the row's own grain and cannot be averaged again.

To get a ratio for a larger group (a week, a month, several locations), sum the additive parts and divide:

-- Correct: ratio of sums
SUM(participants_time_units)::numeric / NULLIF(SUM(capacity_time_units), 0)

-- Wrong: an unweighted average of ratios
AVG(net_occupancy_ratio)

Missing rows are not zeros

Views only contain rows where something happened. An hour in which a location had no time slots has no row in the occupancy views (it is not a row with zero capacity). A day without sales has no row in the revenue views. When you need a complete calendar, generate the dates yourself (generate_series) and LEFT JOIN.

Dates and times are venue wall-clock time

Dates and times are stored exactly as the InnoMeer API returned them, as wall-clock values without a time zone and without any conversion. Treat them as the venue's local time. There are no UTC columns in the views.

*_id, *_code and *_name columns

• *_id (uuid): surrogate key that is only meaningful inside this database. Use it for joins between views; never share it with other systems.
• *_code: the identifier from InnoMeer (the "external id"). Stable, suitable for matching against other InnoMeer data.
• *_name: display name. Names can be renamed in InnoMeer, and two different items can share a name.

Freshness

Data is loaded from the InnoMeer API by scheduled jobs and rebuilt through queues, so it lags the source system, typically by minutes but occasionally longer while a backlog is being worked off. The most recent hours or days can therefore be incomplete. Rows that fail to sync are retried automatically; the application's Queue Status page shows the backlog.

Data is synced from a configured start date (currently 2025-01-01); nothing earlier is available.

Nullable dimensions

In the revenue views a line can legitimately lack a ledger, package, sales representative, floor or till. These show up as NULL values and, in the aggregated views, as their own group (a row where the name is NULL). Filter them out with IS NOT NULL if you do not want them, and be aware that doing so changes totals.

3. Occupancy views

3.1 Concept

Occupancy is measured for time slots: a bookable window on a location with a maximum number of places, for example a bowling lane from 19:00 to 19:30 with 6 places. Slots are split onto a fixed time grid (currently 15-minute buckets) and then rolled up to hourly rows for these views.

Instead of storing "number of people", the model stores time units, which are person-minutes:

A time unit is not "people per minute". It is "capacity (or usage) over time", which is why it can be added across slots, locations and hours without distortion. Convert to people by dividing by minutes: SUM(participants_time_units) / 60 is the average number of participants present during one hour.

Two definitions of "occupied":

• Gross occupancy: capacity that is no longer available, capacity − available. Uses the source system's own availability figure, so it includes places that are blocked or reserved but not (yet) filled with named participants.
• Net occupancy: participants compared to capacity. Can exceed 100% when a slot is over-booked.

3.2 The five views

The four by_* views share exactly the same metric columns (section 3.3) and differ only in their grain, that is, in which dimension columns they carry. They are all aggregated from vw_occupancy_hourly_base, which carries the base measures only (section 3.4).

Terminology: a location is an individual resource (lane 4, kart track, escape room). A location type groups locations of the same kind (all bowling lanes). The dashboards in this application show the location type under the label "Location". An activity is what is played or booked on the location (karting, kids karting, disco bowling); one location can host several activities.

3.3 Common columns

Time

Dimensions: see the table in 3.2. location_code, location_type_code and activity_code are the InnoMeer identifiers; *_name are display names.

Base measures (additive). These hold whole numbers; the SQL type is bigint in the base view and numeric in the grouped views, so cast to integer if a tool insists on it.

Derived measures (additive)

Averages (people, not additive)

In the grouped views (for example by location type) these averages are totals over the group, not per location: 12 lanes with an average of 4 participants each give average_participants_per_hour = 48.

Ratios (fractions, not percentages: 0.35 means 35%; NULL when capacity_time_units = 0)

3.4 Extra columns in vw_occupancy_hourly_base

The base view has none of the derived measures, averages or ratios of 3.3. It only carries the base measures; calculate the rest from them.

3.5 Which occupancy measure should I use?

• "How full were we?" → net_occupancy_ratio, or bounded_net_occupancy_ratio if over-booking should not inflate the number. The dashboards show net occupancy.
• "How much capacity was reserved or blocked?" → gross_occupancy_ratio.
• "How many people were there on average?" → average_participants_per_hour (all clock time) or average_participants_during_planned_minutes (only while something was scheduled).
• "Is the location being oversold?" → over_capacity_ratio.

4. Revenue views

4.1 Concept

Revenue is stored per reservation line item: one sold product on one reservation, with its quantity and amount. All revenue views are built on vw_revenue_base (one row per line item) and simply GROUP BY it, so all revenue views contain the same underlying lines and reconcile with each other: summing total_revenue over the whole date range of any aggregated view gives the same total as summing line_amount in vw_revenue_base. (This holds when you sum over every date; each view filters on its own date axis.)

Amounts

• line_amount / total_revenue are excluding VAT and are the revenue to report. The database has no currency column; amounts are in the venue's currency.
• line_amount is the total of the line, not a per-unit price. Do not multiply it by quantity.
• unit_price (base view only) is derived as line_amount / quantity (0 when the quantity is 0). It is for display; never SUM it.
• quantity and line_amount can be negative: a cancellation or reversal of an earlier line is a separate line with negative values. Sums therefore give net revenue and net quantity.
• A line where the source has neither an excl.-VAT nor an incl.-VAT price is not loaded at all. If only an incl.-VAT price exists, the line is loaded with amount 0.
• Reservations with the is_optional flag (not yet confirmed) are included in every view. Exclude them via vw_revenue_base if you only want confirmed business (see the examples).

Three date axes

Pick one axis per query; do not mix them in a single time series. sold_date is only available in vw_revenue_base and vw_revenue_by_product_time_of_day. The other aggregated views expose reservation_date and (except the ledger/representative view) booking_date.

Ledger

The GL ledger (accounting category, for example 4100 Food & Beverage) is captured on the line at the moment of sale, so reassigning a product to another ledger later does not rewrite history. Lines without a price (for instance a free welcome item) never get a ledger; priced lines can have their ledger filled in later when billing data arrives. A NULL ledger is therefore normal. The dashboards' ledger pages only show lines that have a ledger; totals over everything (vw_revenue_base without a ledger filter) include lines without one.

4.2 The seven views

All aggregated views expose total_revenue (SUM(line_amount)) and total_quantity (SUM(quantity)) as well; line_item_count (COUNT(*), the number of line rows, which includes reversal lines) only where listed. Because of GROUP BY on names, aggregated views identify products, ledgers and representatives by name/number, not by id. Two different products with the same name land in the same row; use vw_revenue_base (which has product_id) when that matters.

4.3 vw_revenue_base: columns

Identity and traceability

Reservation date (the day of the visit)

Booking date (the day the booking was made): booking_date, booking_year, booking_month, booking_day_of_week_iso, booking_is_weekend (same meaning as above; no week column).

Sold date (the day the line was rung up): sold_date, sold_year, sold_month, sold_week, sold_day_of_week_iso, sold_is_weekend.

Time of sale

Dimensions

Measures

Reservation flags (copied from the reservation to each of its lines)

4.4 Aggregated views: columns

Common to all aggregated views: total_revenue, total_quantity (see 4.2).

In vw_revenue_by_product_time_of_day, hour_of_day (0–23) and bucket_start_time (start of the time bucket, currently 15 minutes) describe when the line was sold, and are independent of reservation_date/booking_date.

4.5 Which revenue view should I use?

• Monthly revenue per accounting category → vw_revenue_by_ledger (group by reservation_year, reservation_month).
• Best-selling products → vw_revenue_by_product; packages → vw_revenue_by_package.
• How far ahead do people book → reservation_date minus booking_date, from vw_revenue_by_ledger or vw_revenue_by_product.
• Peak selling hours → vw_revenue_by_product_time_of_day.
• Anything involving floor, till, line item type, weekday of the sale, or the incentive/optional/exclusive flags → vw_revenue_base.

5. Example queries

The names used in filters ('Bowling', '4100') are placeholders. Look up real values with SELECT DISTINCT.

Net occupancy per month per location type

SELECT date_trunc('month', date_value)::date AS month,
       location_type_name,
       SUM(participants_time_units)::numeric / NULLIF(SUM(capacity_time_units), 0) AS net_occupancy_ratio
FROM vw_occupancy_hourly_by_location_type
WHERE date_value >= DATE '2026-01-01' AND date_value < DATE '2026-07-01'
GROUP BY 1, 2
ORDER BY 1, 2;

Busiest hours of the day for one location type (average over a period)

SELECT hour_of_day,
       SUM(participants_time_units)::numeric / NULLIF(SUM(capacity_time_units), 0) AS net_occupancy_ratio
FROM vw_occupancy_hourly_by_location_type
WHERE location_type_name = 'Bowling'
  AND date_value >= DATE '2026-06-01' AND date_value < DATE '2026-07-01'
GROUP BY hour_of_day
ORDER BY hour_of_day;

Which individual locations are over-booked

SELECT location_name,
       SUM(over_capacity_time_units) AS over_capacity_person_minutes,
       SUM(over_capacity_time_units)::numeric / NULLIF(SUM(capacity_time_units), 0) AS over_capacity_ratio
FROM vw_occupancy_hourly_by_location
WHERE date_value >= DATE '2026-06-01' AND date_value < DATE '2026-07-01'
GROUP BY location_name
HAVING SUM(over_capacity_time_units) > 0
ORDER BY over_capacity_person_minutes DESC;

Revenue per ledger per month (by day of the visit)

SELECT reservation_year, reservation_month, ledger_number, ledger_name,
       SUM(total_revenue) AS revenue,
       SUM(total_quantity) AS quantity
FROM vw_revenue_by_ledger
WHERE reservation_date >= DATE '2026-01-01' AND reservation_date < DATE '2027-01-01'
GROUP BY reservation_year, reservation_month, ledger_number, ledger_name
ORDER BY reservation_year, reservation_month, ledger_number;

Top 10 products in a ledger

SELECT product_name, SUM(total_revenue) AS revenue, SUM(total_quantity) AS quantity
FROM vw_revenue_by_product
WHERE ledger_number = '4100'
  AND reservation_date >= DATE '2026-06-01' AND reservation_date < DATE '2026-07-01'
GROUP BY product_name
ORDER BY revenue DESC
LIMIT 10;

Revenue-weighted average booking lead time in days

SELECT ledger_name,
       SUM(total_revenue * (reservation_date - booking_date)) / NULLIF(SUM(total_revenue), 0) AS avg_lead_time_days
FROM vw_revenue_by_ledger
WHERE reservation_date >= DATE '2026-01-01' AND reservation_date < DATE '2027-01-01'
GROUP BY ledger_name;

What sells when: revenue per hour of the day for the last 30 days

SELECT hour_of_day, SUM(total_revenue) AS revenue
FROM vw_revenue_by_product_time_of_day
WHERE sold_date >= CURRENT_DATE - 30
GROUP BY hour_of_day
ORDER BY hour_of_day;

Confirmed business only (exclude unconfirmed reservations; needs the base view)

SELECT ledger_name, SUM(line_amount) AS revenue
FROM vw_revenue_base
WHERE NOT is_optional
  AND reservation_date >= DATE '2026-06-01' AND reservation_date < DATE '2026-07-01'
GROUP BY ledger_name;

Revenue per floor and line item type

SELECT floor_name, line_item_type_name, SUM(line_amount) AS revenue
FROM vw_revenue_base
WHERE reservation_date >= DATE '2026-06-01' AND reservation_date < DATE '2026-07-01'
GROUP BY floor_name, line_item_type_name
ORDER BY floor_name, revenue DESC;

Revenue next to occupancy, per day. The two model families share no key other than the date, so aggregate each side to the day first, then join on the date:

WITH occupancy AS (
    SELECT date_value,
           SUM(participants_time_units)::numeric / NULLIF(SUM(capacity_time_units), 0) AS net_occupancy_ratio
    FROM vw_occupancy_hourly_by_location_type
    WHERE date_value >= DATE '2026-06-01' AND date_value < DATE '2026-07-01'
    GROUP BY date_value
),
revenue AS (
    SELECT reservation_date, SUM(total_revenue) AS revenue
    FROM vw_revenue_by_ledger
    WHERE reservation_date >= DATE '2026-06-01' AND reservation_date < DATE '2026-07-01'
    GROUP BY reservation_date
)
SELECT o.date_value, o.net_occupancy_ratio, r.revenue
FROM occupancy o
LEFT JOIN revenue r ON r.reservation_date = o.date_value
ORDER BY o.date_value;

6. Checklist for AI assistants

When writing SQL against this database:

1. Only query vw_* views. Do not read dim_, fact_, source_ or queue tables, and do not guess at columns; use the ones listed here.
2. Always filter on a date column (date_value for occupancy; reservation_date, booking_date or sold_date for revenue) and use half-open ranges (>= start AND < end).
3. State which date axis you used when reporting revenue. reservation_date is the default, and the one the dashboards use. Say so if you choose booking_date or sold_date.
4. Never average ratios. Sum the additive columns, then divide, using NULLIF(…, 0). Ratio columns (*_ratio) are fractions between 0 and 1 (net occupancy can exceed 1); multiply by 100 for a percentage.
5. Do not sum across two different views, and do not sum unit_price, any average_* column or any *_ratio column.
6. Do not multiply line_amount by quantity. It is already the line total. Both can be negative; that is correct (reversals).
7. Expect NULL dimensions in revenue (ledger, package, sales representative, floor, till). Decide explicitly whether to keep or exclude them and mention it when it changes a total.
8. Optional (unconfirmed) reservations are included in all revenue views. If the question is about confirmed revenue, use vw_revenue_base with NOT is_optional.
9. Missing rows mean "nothing scheduled/sold", not zero utilisation. For gap-free series, build the calendar with generate_series and LEFT JOIN.
10. Identify things by code when accuracy matters (location_code, activity_code, package_code, sales_representative_code); names can be duplicated or renamed. Aggregated revenue views only have names for products, so use vw_revenue_base and product_id when names may collide.
11. Watch the latest data. Today and yesterday can be incomplete because of sync delay; say so when a recent period looks low.
12. Explore with LIMIT. The base views are large (occupancy: hourly per location and activity; revenue: one row per line item).