0031. Property performance, pickup and financial reports

• Status: Accepted
• Date: 2026-10-27

Observable acceptance criteria

An admin, revenue manager or accountant opens Report for the selected property, chooses an exclusive date range of 1–366 nights and day/week/month grouping, and sees KPIs, occupancy/ADR chart, equivalent accessible table and source breakdown. Filters survive reload in the URL. JSON and downloaded CSV agree. Invalid ranges explain how to recover; empty inventory and bookings are valid results. Other roles have no navigation entry and receive 403 from the API; another workspace’s property is 404. No guest data is exported.

Definitions and decisions

All ranges are half-open [from,to) civil dates in the property’s explicit IANA zone. Weeks start Monday; weeks/months at range edges are clipped. Summary ratios are calculated from total numerators/denominators, never by averaging bucket ratios. Undefined ratios are null (shown as a dash), not zero. Money is the property’s currency, frozen at booking; ADR and RevPAR round half to even to its ISO minor unit. No exchange conversion occurs.

• Available room-nights: active units with active room types on each stay date, less out-of-order units for that date, reconstructed from captured inventory history. End-of-day observed state applies to past dates; future dates use the latest known state. Dirty and out-of-service units remain saleable. See the history boundary below.
• Sold room-nights: non-cancelled frozen nights before released_on. Tentative bookings count, unassigned rooms count, historical departed nights count. Released nights do not.
• Room revenue: sum of frozen prices of sold nights; excludes released/cancelled nights. This is booked room revenue, not cash, folio postings, tax reporting or recognized revenue. From migration 0054 on, a night’s price counts less the package services it includes (ADR 0029), so ADR and RevPAR measure the room alone; folio revenue shows those services as the package category. Later rate changes and payment/folio events do not change it.
• Occupancy: sold / available × 100. Not capped: accepted OTA bookings or OOO blocks can leave sales above saleable capacity.
• ADR: room revenue / sold nights. RevPAR: room revenue / available room-nights.
• Arrivals: non-cancelled reservation rooms with scheduled arrival in the interval.
• Departures: non-cancelled rooms with checked_out_on, otherwise effective booked departure (earlier released_on when present), in the interval. Actual late checkout is respected; it does not create additional sold/priced nights.
• Average length of stay: effective sold stay length of arriving rooms / arriving rooms; early releases shorten the stay; future arrivals use booked length.
• Booking lead time: average max(0, scheduled arrival minus creation’s property-local date) for those same arriving rooms. Multi-room bookings weight each arriving room equally.
• Cancellations: distinct cancelled reservation headers with frozen nights in the interval; cancelled nights: count of those frozen nights. This is cancellation impact by stay date, not cancellations made during the interval; no cancellation timestamp exists yet. Counts can repeat across buckets, but summary counts each booking once.
• Sources: same facts grouped by canonical reservation source, with the full property’s capacity denominator (source occupancy is its share of capacity). No source whitelist in the reporting adapter: future OTA canonical identifiers appear without special cases.

One SQL aggregation (GROUPING SETS) produces buckets, totals and sources in a repeatable-read read-only unit of work after the property read. Current indexes cover workspace/property parents and nightly primary-key joins. Reports do not write audit/events. Core pure metric functions apply definitions and exact money rounding to database sufficient statistics. Permission is enforced in core.

GET performance returns versioned, documented JSON; a separate performance.csv operation exports summary, chronological period rows and source rows in one RFC 4180 CSV, with ISO dates, decimal-point numbers, empty undefined ratios, CRLF and no localized headers. CSV textual cells are quoted and spreadsheet formula prefixes neutralized.

Recharts 3 is MIT and installed through the catalog. The chart uses semantic tokens, separate labeled percentage/currency axes and the same server rows as the table. An adapted chart composition uses the existing UI kit rather than introducing a second component CLI. Country reports remain planned; cancellation trends and multi-property consolidation are decided below.

Original verification environment (27 October 2026)

The local gate uses isolated PostgreSQL 17 on port 4011 and transaction-mode PgBouncer on 4012, with SCRAM authentication for runtime roles. The ordinary pool has one server connection; the job roles have two, matching their test harness requirement. Browser verification uses the production build on 4013 and a disposable database.

VITEST_MAX_WORKERS=1 is used for the full gate on this shared external database. With two workers, existing server integration fixtures concurrently bootstrapped the same instance role and PostgreSQL refused an ALTER ROLE with tuple concurrently updated, before the HTTP suite ran. That fixture concurrency limitation requires serial execution on the shared external database. No retry, timeout or coverage threshold was changed. The optional Channex upstream drift suite retains its existing cached-spec condition; reporting contract generation and validation run offline independently.

Pickup, cash, folio revenue and historical capacity (31 October 2026)

An operator with reports.view selects Performance, Pickup, Cash or Folio revenue on the same property Reports screen. Each report has a chart, equivalent accessible table and CSV. Filters, including pickup’s two as-of dates, persist in the URL. The existing grant and built-in administrator, revenue-manager and accounting access apply to all reports; no new grant or financial write permission is required. Read-only exports contain no guest identifiers, names, contact data, posting descriptions or notes.

History boundary and capture

The pre-upgrade schema retained only current reservation and inventory state. It did not retain historical frozen nights after an edit, nor room/type activation/archive or deleted block history. Audit/event payloads do not contain enough information to reconstruct those facts. Migration 0030 therefore establishes an explicit observation baseline for existing properties. Newly created properties start their coverage at creation. Pickup refuses pre-baseline observations with request.invalid and a query field error on /baseline or /comparison. Performance remains available for all previously supported ranges. Before history_from, its capacity uses only inventory states recorded in the baseline: the first unit/type/block state applies backwards if observed before the end of the history_from day in the property’s time zone. This includes rooms added during a new property’s setup day. Entities first observed on later local dates retain their creation cutoff; subsequent state changes retain their observation times. This is a labelled baseline estimate, not reconstructed pre-upgrade history. Each period, source and total exposes pre_history_nights, counting stay dates before coverage rather than room-nights. The screen explains “Before {date}, capacity uses the rooms recorded on {date}” and marks affected periods; the default remains the start of this month.

Migration 0030 remains contract. No release exists, so no released deployment upgrades across this migration. It requires roll-forward: a previous runtime cannot cold-start after it. The migration takes SHARE ROW EXCLUSIVE locks on properties, reservations, reservation_rooms, reservation_nights, units, room_types and unit_blocks. All INSERT, UPDATE and DELETE operations on these tables wait, including bookings, Channex imports, housekeeping, blocks and property settings. Plain reads and row locks remain possible. The five-second lock_timeout bounds each table’s acquisition wait separately, not the total wait or hold time; while waiting for a busy table, earlier locks are held. The locks remain held through the complete baseline scan and trigger DDL. A set-based night aggregation joined to reservations avoids one correlated scan per reservation. The same per-property observation instant identifies the baseline and its snapshots. Deploy large instances in a quiet window: blocked writers can exceed runtime statement timeouts and pin pooler server connections, making reads queue too. The migration runner requires its existing direct/session connection; the lock and SET LOCAL are transactional.

Measured on PostgreSQL 17 with the repository’s local test settings (fsync and synchronous commit off), one property and three synthetic frozen nights per reservation:

Reservations	Nights	Writer wait, correlated baseline	Writer wait, set-based baseline
20,000	60,000	526.94 ms	525.71 ms
400,000	1,200,000	14,961.82 ms	13,856.27 ms

The revised baseline took about 26.29–34.64 microseconds per reservation in these samples; its aggregate executes once and joins by hash, with no correlated SubPlan. The 400k sample is about 7.4% shorter but still exceeds the default ten-second runtime statement timeout. These shared-host samples are guidance, not a deployment time bound. In the busy-table probe an already-fenced property writer waited 4,994.65 ms while the migration failed on lock acquisition with 55P03 after 5,012.65 ms. Deploy large instances in a quiet window.

Cash and folio revenue range reads use the (workspace_id, service_date) btree index added by expand migration 0031. It is a separate transaction after 0030 commits, so its index build does not extend the seven-table baseline fence measured above. The runner sets a five-second lock-acquisition timeout for expand migrations. Ordinary index creation blocks ledger writes while building; reads continue. The timeout does not bound build duration, so large ledgers also need a quiet deployment window. It adds no grants and keeps the existing folio-order index.

Database triggers capture PII-free reservation nightly facts and unit, room-type and block state in the same transaction as the existing audited operation. Reservation snapshots are captured after the transaction’s header/detail writes, so a snapshot contains the final frozen nights, cancellation and release state. Rollback also rolls back history. Capture uses the database observation timestamp after writer admission; an injected business clock cannot backdate an observation. Runtime roles can read history through workspace/property RLS and cannot edit or delete recorded history. History is a report projection; it adds no duplicate operator audit record or domain event to a write already audited by its use case. All three reporting history tables follow their property through ON DELETE CASCADE; deleting a property removes its derived history without affecting another property.

Dates are property-local civil dates. A snapshot as of a date includes observations before the next local midnight, including the whole selected day. Inventory for each historical stay date uses that day’s last known unit/type/block state; dates in the future use the latest known state and dated OOO intervals. A later archive or block edit does not rewrite an earlier day’s capacity. OOS blocks do not reduce capacity. Outside the labelled baseline estimate, a room created after a stay date does not contribute to that date. The available-room-night denominator is summed independently of booking/source joins so multiple bookings cannot multiply capacity. Active unit/type validity intervals are intersected before joining stay dates, avoiding a materialized day-by-entity JSON history expansion for the capacity calculation. Day-end instants are computed once per stay date, and active room-type states are filtered once before the join. On the same 2,100-room/365-day fixture, three alternating app-role aggregate transactions at host load 40–45 took 4,063–4,552 ms before and 1,057–1,175 ms after this change (medians 4,198 and 1,092 ms). Every one of the 367 output rows matched. These local samples include transaction/context setup, not the property/history reads or core metric mapping; the full-use-case regression retains its original five-second budget and six SQL calls.

Performance definition 2 replaces the current-inventory denominator. The changed closed definition_version and capacity_basis values are breaking, so API revision 5.0.0 records an explicit dated exception in ADR 0016. No release exists and the SDK is unpublished. Upgrade client SDKs and checks with the server. Existing CSV column positions are preserved; definition/capacity/history metadata and pre_history_nights are appended.

Pickup

Pickup compares booked rooms and frozen booked room revenue at two property-local end-of-day snapshots for each requested stay date. History metadata names history_from and available_through; future as-of dates are refused. The current as-of day includes only changes visible in this report snapshot. The earlier snapshot precedes or equals the later one; signed pickup is later minus earlier, so cancellations and price reductions can produce negative values. Tentative, unassigned and departed sold nights follow the performance definition. Cancelled and released nights do not remain on the books. A reservation that did not yet exist contributes zero; a subsequent edit, cancellation or re-price never rewrites its earlier observed snapshot. This is observation history after the coverage baseline, including imported changes when GuestCraft received them, not a claim about when an OTA first knew a reservation.

Cash and folio revenue

Cash reports all manually recorded payments and refunds, including non-cash methods, by each movement’s own date (service_date) and original payment method. This matches the folio and statement, including a clerk recording a payment after midnight on the still-open business date. Payments and refunds are separate, with signed net receipts; reversals correct their relevant subtotal on their own service date. This is a ledger report, not provider reconciliation or cash-shift closing.

Folio revenue is by service date: signed charges and reversals of charges group by the movement’s service_date, source category and canonical tax category. Check-in may record a whole stay at once, but room charges land on each night they are for. Corrections stay on their own service date. The explicit revenue-kind allow-list is charge and reversal whose original kind is charge; payments, refunds, folio transfers and every unlisted future kind are excluded. Every movement is attributed through its own workspace/folio join, so several folios for one reservation cannot multiply or lose revenue. A regression pins both guarantees. Sources include room, extra, manual, policy (cancellation/no-show fees) and adapter (including module levy lines such as tourist taxes collected for authorities). Gross levy amounts are included in these ledger totals; this does not classify them as earned revenue. Frozen gross amounts are reported without exchange conversion, and this is not a tax return, fiscal document or recognized-revenue statement. A tax category does not claim a country tax adapter is installed. Neither financial report requires reservation-history coverage: the ledger is append-only. The service date determines the period, not the recording instant.

Financial charts have a per-day x-axis and separate method/category series, using the same values as their equivalent table. Numeric tooltips retain original decimal strings rather than rounding through binary floating point.

The implementation uses existing Recharts and accessible UI components; no paid charting, external service or new dependency is introduced. Country compliance, online payment providers, owner statements, groups/allotments, booking engine and online check-in remain planned outside this track.

Cancellation trends (1 November 2026)

An operator with reports.view chooses Cancellation trends on the property Reports screen, a 1–366 day range and day/week/month grouping. The report shows when rooms were cancelled, through which channel and why, how far ahead of arrival, and what booked value left the books. It has a chart, equivalent accessible tables by period, by channel and cause, and by lead time, and a CSV with the same rows. Filters persist in the URL. No guest data is shown or exported. Performance’s cancellation columns keep their meaning: impact by stay date, not this report.

What is captured

From migration 0058 on, every saved change that flips a room between cancelled and booked appends one immutable cancellation_events row per room. A room is cancelled when its reservation is cancelled, its own status is cancelled, or its channel cancelled it. Three causes exist:

• member: a member or integration cancelled the reservation or some of its rooms;
• option_expired: night audit released an unconfirmed option;
• channel: a Channex revision cancelled or reinstated rooms.

No-shows are not cancellations. The reservation saver maps its saved action to a cause; an action outside that map that flips a room fails as a bug instead of losing the event. The Channex import calls the same pure function after saving its projection.

Each event freezes, as they were before the change, the room’s arrival and departure and its cancelled unserved nights: the frozen nights performance would count as sold, before any release. Their count and value are in the reservation’s currency, which is the property’s. The retained fee is the room’s cancellation decision amount (zero for option expiry and for channels) and is kept separately. A channel cancellation of a stay that happened keeps its stayed nights, so it records a cancelled room with no unserved nights. A reinstatement is its own event with the nights it books again; it never deletes the cancellation.

The instant is the injected clock when the change is saved. For a channel cause it is when GuestCraft applied the revision. The revision’s inserted_at, Channex’s receive time, is kept separately with the revision id. Neither is the guest’s action time. The key is (workspace, room, reservation version): a retried transaction or replayed revision saves the same transition again and adds nothing. A booking first received already cancelled, or a room first seen cancelled, is not a new cancellation.

Coverage and what is unknown

report_history_start.cancellations_from is the migration instant for existing properties and the creation instant for new ones. Nothing is backfilled from updated_at, revisions, arrival dates or audit records, because none of them is a cancellation instant. Rooms cancelled now without a captured cancellation count in unknown_date_cancelled_rooms. These are rooms cancelled before capture, by a runtime older than this migration, or first received from a channel already cancelled (GuestCraft never saw them booked). They are never placed in a period. Each period reports pre_capture_days, and the screen says from which date cancellations are recorded.

Definitions

Periods group events by the property-local date of their instant, with the same half-open range, Monday weeks and clipping as the other reports. Rows are kept per period, per source, OTA channel name and cause, plus period totals, range totals per channel and cause, and a total.

• Cancelled rooms and nights: events of kind cancelled, and their unserved nights.
• Booked value cancelled: the frozen value of those nights. It is booked value taken off the books, not recognized revenue, and is never recomputed from a later amendment.
• Fees retained: the sum of the events’ fees.
• Reinstated rooms and value: events of kind reinstated.
• Net value lost: booked value cancelled − fees retained − value reinstated, in the same rows. It can be negative in a period with more reinstatements than cancellations.
• Lead time: the room’s arrival minus the cancellation’s property-local date, in days. The buckets are: arrival day or later (0 or fewer), 1–7, 8–30, 31–90 and more than 90. The average is total lead days over cancelled rooms, with the arrival day or later counted as 0. It is never an average of averages, and it is null without cancellations.

The table is append-only for the runtime roles (select and insert). It carries tenant RLS, the property restriction, the restrictive workspace_active policy and workspace-first foreign keys that cascade with the property, reservation and room. Migration 0058 is contract: it adds a column with a volatile default to the one-row-per-property report_history_start table, which rewrites that small table. It adds no trigger and no function.

Consolidation across properties and stored exchange rates (1 November 2026)

Acceptance criteria

A member with reports.view at one property at least opens Reports → Consolidated report for the workspace, chooses an exclusive range of 1–366 nights and, optionally, a reporting currency, and sees totals by currency, an all-property line, each property in its own currency and time zone, and a CSV with the same values. Filters survive reload in the URL. An owner or administrator of the organization opens the Organization report: the same report across the organization’s active workspaces, grouped by workspace then property, with each workspace’s environment labelled and sandboxes left out unless included. A missing or stale rate names the currencies that lack one and leads to Exchange rates, where members with exchange_rates.manage add a rate exactly as published or delete a mistyped one, and members with reports read them. Other members get no navigation entry and 403.

Scope and authorization

• Workspace. Only properties where the caller holds reports.view, decided server-side at each property; a member limited to some properties gets those. Integrations’ credentials with the grant may read it, as they read property reports.
• Organization. Owners and administrators of the organization only, with a browser session: credentials are refused by the route and by the use case (an organization token is a narrowed delegation, never organization administration, ADR 0047). The use case lists the organization’s workspaces under the principal’s organization scope, keeps the active ones (and only the production ones unless include_sandbox), and reads every figure in one read-only repeatable-read unit of work whose scope is the organization with exactly those workspaces. That snapshot reads the workspaces again, so one archived in between is left out; archived workspaces never contribute. Reports audit nothing.
• The per-property figures come from the unchanged performance aggregation (its total row), one query per property on the same snapshot: the cost grows with the number of properties.

Period basis

Each property’s own local dates [from, to) (period_basis: property_local_dates): the same civil range covers different instants in properties of different time zones, and the report says so rather than inventing a common instant range. Pre-history capacity follows each property’s own baseline; the pre_history_nights of a total is the sum over its properties.

Sums and ratios

The sufficient statistics of the properties are summed, then the metrics are derived from the sums: occupancy is summed sold over summed available room-nights, ADR summed revenue over summed sold, RevPAR summed revenue over summed available, and the stay and lead-time averages summed numerators over summed arrivals. Ratios are never averaged; a ratio without a denominator is null. Revenue is summed per currency only. The all-property line carries the currency-independent counts and occupancy, and revenue only once converted.

Conversion to a reporting currency (ADR 0014)

• Each currency’s revenue subtotal is converted once, never each property or night, so a currency line and its converted value reconcile and rounding cannot accumulate. Property rows stay in their own currency.
• Rounding is half to even, once, to the minor units of the reporting currency. Converted ADR and RevPAR divide the converted subtotal by the summed sold and available room-nights, again half to even. The converted all-property revenue is the sum of the converted subtotals.
• The rate is the organization’s stored rate with the latest day on or before the range’s last night and at most 7 days older (rate_max_age_days). On that day a direct or inverse rate comes first; otherwise a cross conversion through a currency both are quoted against, in either direction, of one source and one day, such as two euro rates of the ECB. Seven days covers weekends and holidays of daily publishers while keeping a report on rates of its own period; a longer gap is an error to fix by adding a rate, not a silent approximation. A future period therefore needs a rate dated within its last week.
• A currency without such a rate answers exchange_rate.missing (422) naming every such currency. A rate is never assumed to be 1 or 0.
• The response returns the rates used (pair, rate as stored, day, source) next to each converted amount and keeps the original amounts. Conversions are computed when a report is read and never booked: no conversion record is stored, and a deleted rate changes only later reports.

Stored rates

Rates are an organization table (exchange_rates): base, quote, the rate as the exact published decimal string, its day, source (manual only for now), author, reason and creation instant. They are the organization’s directory: readable and writable from any active workspace of the organization through row-level security that joins the context’s workspaces to their non-archived organization, without adding the organization to anyone’s scope, so ordinary members gain no organization administration. Reads need reports.view at a property or exchange_rates.manage; writes need exchange_rates.manage (built in for administrators and accountants, workspace scope, refused to members limited to some properties). One rate per unordered currency pair, day and source. A rate is immutable: a typo is fixed by deleting it and adding the right one. Adding and deleting are organization audit entries with the real actor and the rate’s facts (never the reason); they emit no domain event, which would belong to one workspace. The audit log’s insert check admits such organization entries from an active workspace of the organization; reading them still needs the organization.

exchange_rates.manage is browser-only: a workspace API key or an organization token cannot hold it. A manual rate is a person’s statement with a reason, and a delegated credential of one workspace should not change a fact that every workspace of the organization reads. An automatic rate source would be a server adapter behind the ExchangeRates port of ADR 0014.

Contract

GET /reports/consolidated and /reports/consolidated_csv (reports.consolidated, reports.consolidatedCsv), GET /organization_reports/consolidated and /organization_reports/consolidated_csv (reports.organizationConsolidated, reports.organizationConsolidatedCsv), and GET/POST /exchange_rates and DELETE /exchange_rates/{id} (exchangeRates.list, create, delete). The CSV has one row per property, per currency and for all properties, with the JSON’s values, RFC 4180 quoting and formula neutralization like the other report CSVs. Details are in the report contract.

Limits

The consolidated screens show tables and tiles without a chart: a Recharts chart on routes that share its modules with the property Reports screen made the bundler split that screen’s chunks and grow its initial download by about 10 KB compressed, past its budget.

Rates are entered by hand; no rate-source adapter exists yet. Consolidation covers the performance report only: pickup, cash, folio revenue and cancellation trends are not consolidated. There is no common-instant period basis and no per-workspace subtotal row in the organization report; its per-currency totals cover the whole report.