Osool USALI Hotel Financial & Operational Planning System

Hotel financial planning and operational management are governed by the strict Uniform System of Accounts for the Lodging Industry (USALI) standard. Hospitality operators must reconcile monthly financial P&L transactions (actuals, budgets, forecasts) with granular daily and weekly operational metrics, including room nights, Average Daily Rate (ADR), Revenue Per Available Room (RevPAR), and Food & Beverage (F&B) covers across meal periods and beverage categories.

Historically, hotel finance teams relied on fragile, disconnected spreadsheets for operational planning and monthly ledger reconciliation. This fragmented workflow created severe data retrieval bottlenecks, high risk of calculation error, and days of manual consolidation to produce standard USALI variance and P&L reports.

As lead software architect and full-stack engineer, I designed and implemented the end-to-end hotel financial and operational planning platform:

• Architected a database-centric PostgreSQL schema organized into three distinct namespaces: reporting for hierarchical master data, transactions for monthly financial facts, and statistics for granular daily operational allocations.
• Modeled multi-tier recursive trees using materialized_path indexing across accounts, departments, and operational details, allowing instant hierarchical rollups and sub-tree aggregations.
• Engineered automated PostgreSQL triggers that compute incremental deltas on daily room and cover entries and push operational revenue directly into the monthly financial fact table.
• Built the dynamic F&B beverage revenue engine supporting percentage-of-food rules, dollar overrides, and category breakdowns across liquor, wine, beer, and other beverages.
• Designed Retool operational planning interfaces utilizing a hybrid client-server model: SQL queries return flat, normalized datasets, while custom client-side JavaScript utilities transform them into interactive two-dimensional matrix grids with dynamic row totals and level-based styling.
• Implemented the unified financial reporting layer, variance query engines, and monthly P&L statements with dynamic scenario comparisons.

• Database-centric business logic vs. application-layer computation: Enforced calculations (weighted ADR, delta aggregations, financial syncing) inside PostgreSQL triggers, functions, and views rather than in frontend Retool queries. This guaranteed absolute data consistency and auditability regardless of frontend changes or future API migrations.
• Client-side matrix pivoting vs. dynamic SQL crosstabs: Chose to have PostgreSQL return flat normalized records and execute matrix pivoting in Retool via custom JavaScript utilities. This eliminated database contention and temporary table locks caused by per-user dynamic SQL crosstabs on frequently updated daily planning grids.
• Decoupled beverage revenue configuration: Separated total beverage revenue derivation from category percentage splits. This allowed properties to flexibly toggle between percentage-of-food rules and fixed revenue overrides without modifying the core account ledger.

• Delivered a USALI-compliant financial planning and operational statistics suite that unifies room metrics, F&B covers, and financial ledgers into a single source of truth.
• Automated real-time operational synchronization, eliminating manual spreadsheet reconciliation by pushing daily allocation calculations directly into the monthly financial ledger.
• Engineered responsive planning grids in Retool capable of interactive multi-dimensional cell edits and client-side matrix transformations.
• Enabled instant multi-scenario financial reporting (Actual vs. Budget vs. Forecast vs. Prior Year) via unified P&L views and dynamic variance queries.