Skip to content

Parallax Release 2 — Planning Analysis

Context

Parallax Release 2 targets UI/UX polish, performance, and functional gaps in the Oracle APEX hotel revenue-management application. Inputs come from two sources: (1) the client-provided functional brief covering General, Hotel Management, Events, Hotel Data, Strategies, Reports, and User Management, and (2) the internal augmentation list adding multiple report improvements, data-load resilience, strategy quality-of-life features, Teams integration v1, housekeeping, expanded user management (SaaS isolation, granular access, password resets), and template lifecycle/global-attribute UI.

The goal of this document is to convert that backlog into an executable plan: per-item effort, complexity, impacted modules, dependency graph, and a parallelisation strategy for a team of three engineers.

Table of Contents

Architecture Reference (Current State)

This section is for client orientation. It captures Parallax as it is today, prior to Release 2. Diagrams use Mermaid and render natively in MkDocs (Material) with the pymdownx.superfences mermaid custom-fence configured.

MkDocs setup note: ensure mkdocs.yml includes: 

markdown_extensions:
  - pymdownx.superfences:
      custom_fences:
        - name: mermaid
          class: mermaid
          format: !!python/name:pymdownx.superfences.fence_code_format

System Context

Parallax is a single-tenant Oracle APEX application that ingests external hotel data (Lighthouse, Duetto, PMS), evaluates revenue-management strategies against that data, and produces interactive reports for revenue managers.

flowchart LR
    subgraph External
        LH[Lighthouse exports]
        DU[Duetto exports]
        PMS[PMS exports]
        CLI[Client users<br/>Revenue managers]
    end

    subgraph Parallax [Parallax APEX Application]
        UI[APEX Pages<br/>UI Layer]
        JS[Custom JS Widgets<br/>DynamicReport / ReportDashboard / gridReportSummary]
        PL[PL/SQL Core<br/>UR_UTILS / UR_ALGO_EVALUATOR_PKG]
        DB[(Oracle DB<br/>UR_* tables)]
        CACHE[(UR_ALGO_EVAL_CACHE<br/>+ Queue)]
        SCHED[DBMS_SCHEDULER<br/>jobs]
    end

    subgraph Ops [Ops / Integration]
        N8N[n8n workflows<br/>bug-reporter]
        MAIL[APEX_MAIL]
    end

    LH --> UI
    DU --> UI
    PMS --> UI
    CLI --> UI
    UI --> JS
    UI --> PL
    JS --> PL
    PL --> DB
    PL --> CACHE
    SCHED --> CACHE
    PL --> MAIL
    UI -- error reports --> N8N

Module Map

flowchart TB
    subgraph HM [Hotel Management]
        H1[Hotels]
        H2[Hotel Address]
    end

    subgraph EV [Events]
        E1[Hotel Events]
        E2[Add Event]
        E3[Events Data Load]
    end

    subgraph HD [Hotel Data]
        T1[Templates]
        T2[Page Template]
        T3[Load Data]
        T4[Price Override]
        T5[Reservations]
    end

    subgraph ST [Strategies]
        S1[Algorithms / Base Strategy]
        S2[Strategy Data]
    end

    subgraph RP [Reports]
        R1[Report Dashboard]
        R2[Report Summary]
        R3[Report Template]
        R4[Run Reports]
    end

    subgraph UM [User Management]
        U1[User List]
        U2[Page Access]
        U3[Hotel Access]
    end

    HM --> HD
    HD --> ST
    ST --> RP
    HM --> EV
    UM -.authorises.-> HM
    UM -.authorises.-> HD
    UM -.authorises.-> ST
    UM -.authorises.-> RP

Strategy Evaluation & Cache Flow

The pricing-strategy engine pre-computes results into UR_ALGO_EVAL_CACHE to give sub-second report rendering. Changes to templates, hotels, or price overrides enqueue refresh jobs processed asynchronously.

sequenceDiagram
    participant U as User
    participant UI as APEX Page
    participant PK as UR_ALGO_EVALUATOR_PKG
    participant Q as UR_ALGO_CACHE_QUEUE
    participant SCH as DBMS_SCHEDULER
    participant C as UR_ALGO_EVAL_CACHE
    participant DB as UR_* tables

    U->>UI: Edit template / load data
    UI->>PK: QUEUE_TEMPLATE_REFRESH
    PK->>Q: Insert refresh row
    SCH->>Q: PROCESS_CACHE_QUEUE tick
    Q->>PK: REFRESH_STRATEGIES_ORDERED
    PK->>DB: Read inputs
    PK->>PK: GENERIC_MATH_EVAL<br/>topological eval
    PK->>C: Upsert results
    U->>UI: Open report
    UI->>C: Read precomputed rows
    UI-->>U: Render in <1s

Data Load Pipeline

flowchart LR
    F[File upload<br/>CSV / XLSX] --> P[Parse + sanitize<br/>UR_UTILS.sanitize_template_definition]
    P --> M[Column mapping<br/>LOAD_DATA_MAPPING_COLLECTION]
    M --> V[Validation<br/>validate_calculated_formula]
    V --> S[(Staging)]
    S --> L[Load_Data_v2]
    L --> T[(UR_* target tables)]
    L --> LOG[(UR_INTERFACE_LOGS)]
    L --> QC[Queue cache refresh]
    QC --> CACHE[(UR_ALGO_EVAL_CACHE)]

Authorisation Stack

Authorisation is layered: APEX native auth handles identity, then a custom layer enforces page-level and hotel-level access. Workspace developers and the APEX ADMINISTRATOR ACL role bypass the custom layer.

flowchart TB
    L[Login] --> A[APEX Native Auth<br/>NATIVE_APEX_ACCOUNTS]
    A --> W{Workspace dev<br/>or ACL admin?}
    W -- yes --> ALLOW[Allow]
    W -- no --> CA[UR_UTILS.CHECK_AUTHORIZATION]
    CA --> PA[UR_USER_PAGE_ACCESS<br/>bitmask R/C/U/D]
    CA --> HA[UR_USER_HOTELS_ACCESS<br/>hotel scope]
    PA --> D{Mask satisfies<br/>requested action?}
    HA --> D
    D -- yes --> ALLOW
    D -- no --> DENY[Deny / redirect]

Critical Components Summary

The following components are load-bearing for Release 2 — most R2 changes touch at least one. They are also the highest-risk surfaces for regressions.

Component Location Why critical for R2
UR_ALGO_EVALUATOR_PKG scripts/UR_ALGO_EVALUATOR_PKG_BODY.sql All strategy changes (S1–S14), ISR live recalc (R2), perf targets
UR_UTILS (load + template) scripts/UR_UTILS_Body.sql Template inheritance (T2), source-based templates (T1), Duetto fix (T6), local-store (T5)
UR_ALGO_EVAL_CACHE + queue scripts/UR_ALGO_EVAL_CACHE_DDL.sql ISR live recalc SLA, strategy perf ≤2s
ajx_get_report_data application/shared_components/logic/application_processes/ajx_get_report_data.sql Report column handling (R5/T4), UR-223 fix lineage
Report JS bundle ReportDashboardJS.js, DynamicReportJS.js, gridReportSummary.js All report items (R1, R6, R7, R10, R11), ISR (R2)
Authorisation layer ur_custom_authorization_rights.sql + UR_USER_PAGE_ACCESS All User Management items (U1–U5)
Bug reporter pipeline bug-reporter.js + n8n workflows/bug-reporter.js H4 v2 rebuild
Install scripts application/deployment/install/ H3 scheduled deployments

Maturity assessment (going into R2):

Area Maturity Notes
Strategy engine Production-grade (recent UR-219/UR-227 fixes) 50K+ calcs reduced from 4–5 min → 10–15s; cache-queue pattern in place
Template / Load Data Mature core, gaps in inheritance and local-store Greenfield: T2 inheritance, T5 local pickup/STLY
Reports Mature; ISR is the only greenfield page R2 ISR is the headline R2 deliverable
User Management Functional; needs UI + granular hotel access + multi-tenancy U3/U5 are significant additions
Integrations Webhook plumbing exists (bug-reporter only) TI1 generalises it
Test infrastructure Manual only H5 introduces automated pre-merge CI

1. Team & Method Assumptions

  • Capacity: 3 engineers — R1 = lead architect, R2 = backend / PL/SQL, R3 = APEX + JS/UI.
  • Sprint length: 2 weeks. Effort is expressed in working days per person. One sprint delivers roughly 9 productive working days per person after meetings, reviews, and unplanned interruptions.
  • Release shape: Single release branch with feature flags for risky items (ISR rollout, SaaS isolation). Backwards-compatible DB changes deployed first.
  • Out of scope for R2: Net-new analytics, mobile, multi-currency rework beyond display formatting.

2. Effort & Complexity Legend

Symbol Meaning
Effort Working days for one person — covers build, testing, and review
Complexity L (low — isolated change), M (medium — touches 2–3 modules), H (high — cross-cutting, data migration, or perf-sensitive), XH (multi-week, design-heavy)
Risk L / M / H — likelihood of slipping or causing regressions
Parallel? Y if it can start in sprint 1 with no upstream blocker; N if blocked

3. Module Map (Where Code Lives)

Reference for the breakdown tables — paths are repo-relative.

Module Primary Files
Hotels application/pages/page_00014.sql, page_00011.sql, page_00023.sql
Events page_00009.sql, page_00020.sql, page_00027.sql
Templates page_01001.sql, page_01002.sql, page_01020.sql
Load Data page_01010.sql, page_01011.sql, application/shared_components/data_loads/ur_data_load.sql
Price Override page_01071.sql, page_01075.sql
Reservations page_00019.sql, page_00022.sql
Strategies page_01050.sql, page_00026.sql
Reports page_00015.sql, page_00167.sql, page_01006.sql, page_00021.sql
User Mgmt page_01611.sql, page_01612.sql, page_10001.sql–page_10004.sql
Core PL/SQL scripts/UR_UTILS_Spec.sql, scripts/UR_UTILS_Body.sql
Strategy Engine scripts/UR_ALGO_EVALUATOR_PKG_SPEC.sql, scripts/UR_ALGO_EVALUATOR_PKG_BODY.sql
Report JS readable/application/shared_components/app_static_files/ReportDashboardJS.js, DynamicReportJS.js, gridReportSummary.js
Auth application/shared_components/security/authentications/oracle_apex_accounts.sql, application/shared_components/security/authorizations/ur_custom_authorization_rights.sql

4. Requirements Breakdown

All requirements live in a single CSV at apps/data/parallax-r2-requirements.csv so they can be sorted/filtered/edited as a grid during stakeholder sessions (open with the Edit csv VSCode extension; the docs site hot-reloads on save).

Columns:

  • section — grouping (General / Hotel Management / Events / Hotel Data / Strategies / Reports / User Management / Teams Integration / Housekeeping).
  • id — reference ID (G1, T5, R2, …).
  • source — Client (in the client functional brief) or Internal (added by the engineering team).
  • status — Active (in current R2 scope) or Parked (descoped from R2; kept here as a record so the next planning round can pick them up).
  • ui_change — scope of UI work: No UI (pure backend / infra / CI), Minor (single LOV, button, validation, label, small widget, one-page touch), Medium (new admin/CRUD page, modal flow, page repurpose, ~5–15 pages touched), Large (greenfield page, major redesign, 20+ pages touched). Active-only totals: 10 No UI, 27 Minor, 20 Medium, 4 Large.
  • item — short description.
  • effort — working days for one engineer.
  • cx — complexity (L / M / H / XH).
  • risk — risk level (L / M / H).
  • par — parallel-safe? (Y, or N with dependency).
  • notes — reuse pointers and the Why line for each estimate.
  • client_feedback — refinements captured live during the stakeholder session (populated for the items the client commented on; blank otherwise).
  • internal_feedback — the development priority agreed with the client, 1 (build first) through 5 (build last). The CSV is now row-ordered by this value (all P1 first, then P2, …). Parked / Duplicate rows are left blank.
section id source status ui_change item effort cx risk par notes client_feedback internal_feedback
Hotel Data T1 Client Active Minor Source-based templates (Lighthouse/Duetto/PMS) instead of per-hotel 6 M M Y Add SOURCE column on UR_TEMPLATES; UI filter. Why: Schema change is small but backfill (assigning source to existing templates) and UI filter testing across Lighthouse/Duetto/PMS/Other adds a day over original. nan 1
Hotel Data T2 Client Active Medium Master template + per-hotel refinement (template inheritance) 12 H H N (blocks on T1) New parent_template_id; resolution at runtime; impacts attribute mgmt. Why: No inheritance pattern exists in the codebase. GET_ATTRIBUTE_VALUE (the hot path for every report cell) must walk the inheritance chain with cycle detection; manage_algo_attributes must distinguish inherited vs. overridden; UI needs override indicators. Original 8 days under-counted runtime integration cost. nan 1
Hotel Data T8 Client Active Minor Batch upload — multi-file auto-routing 5 M M N (after T1) Use template KEY matching from fetch_templates. Why: fetch_templates exists; main work is a header-signature hash, signature cache invalidation on template edit, and a fallback modal for unrouted files. nan 1
Housekeeping H3 Internal Active No UI Automated release notes + scheduled deployments 6 M M Y Git-log → markdown generator; CI pipeline trigger; APEX export/import on schedule. Reuse application/deployment/install/ scripts. Why: Seven install scripts with ordering dependencies, plus APEX export/import automation and CI wiring. Original 4 days under-counted the pipeline integration. nan 1
Housekeeping H4 Internal Active Medium Bug-reporter v2 — client status, email confirmation, RCA closure workflow 6 M M Y Extend bug-reporter.js + n8n flow. New UR_BUG_REPORTS table with status (Open/Triaged/InProgress/Closed), RCA text, assignee; apex_mail confirmation on submit and on status change; admin page to manage. Why: New status table + state-machine, two email triggers (submit, status change), admin management page, plus n8n flow extension for status updates. nan 1
Housekeeping H5 Internal Active No UI Automated test infrastructure — pre-merge CI for PL/SQL, APEX, and JS 12 H M Y Stand up utPLSQL test runner for UR_UTILS, UR_ALGO_EVALUATOR_PKG, delete_template, formula validators. Add Playwright/Cypress smoke suite for top 10 APEX flows (login, hotel select, template create, load data, strategy assign, run report, ISR render, user CRUD, bug report, export). Add Jest for shared JS (DynamicReportJS, ReportDashboardJS, gridReportSummary). Wire into GitHub Actions (or chosen CI) with required status checks before merge. Seed data fixture script + APEX export round-trip check. Why: Three test frameworks to stand up, 60 seed tests across the critical paths, CI pipeline + required status checks, plus documentation and team upskilling. Original 8 days under-counted the breadth across three layers and the documentation work. nan 1
Hotel Data T15 Client Active Medium Per-hotel shared drive (SFTP / object storage) auto-pickup → Load_Data_v2 + success/failure alerts 7 M M Y DBMS_SCHEDULER poll + route to existing Load_Data_v2 (apps/scripts/install/UR_UTILS_Spec.sql); reuse scheduler pattern from UR_ALGO_EVALUATOR_PKG_BODY.sql; reuse APEX_MAIL pattern from apps/application/shared_components/automations/weekly_debug_log_cleanup.sql. Why: Greenfield file discovery + per-hotel routing (2d), Load_Data_v2 wiring (1d), DBMS_SCHEDULER + APEX_MAIL alerts (2d), per-hotel routing edge cases + testing (2d). nan 1
Strategies S15 Client Active Minor Crash fix on duplicate strategy name after copy 2 L M Y Same shape as S8 (duplicate condition names); this is duplicate strategy names post-copy. Add unique-check in copy_strategy.sql and surface an error toast instead of a 500. Why: Backend defensive check + JS error handling on the existing copy modal. Small, but a known crash. nan 1
Hotel Data T9 Client Active No UI Load-data perf target (≤3s) 8 H H Y Profiling-led; partitioning, bulk-collect, parallel DML. Why: Performance work is not linear; profiling, identifying bottlenecks in Load_Data_v2's dynamic SQL, applying indexes / bulk-collect / parallel DML, and validating with no accuracy regression. T5 (local storage) increases the load budget, raising risk. nan 2
Hotel Data T12 Internal Active Minor Template delete / duplicate / common templates UI 2 M M Y Reuse UR_UTILS.delete_template() from UR-222; add UI on page_01001. Why: delete_template (320+ lines with preview/execute modes) is already in production from UR-222. Work is reduced to two buttons, two modals, and a "common" flag. nan 2
Strategies S1 Client Active Minor Tiered Base Strategy framework (High/Mid/Low) 3 M M Y New UR_BASE_STRATEGIES table or tier column on UR_ALGOS. Why: One additional column on a table that already has many; reuses the same tier pattern as HM1. High Tier (5), Mid (3-4), Low (1-2*) 2
Strategies S2 Client Active Medium Auto-inherit Base Strategies on hotel onboarding by tier 4 M M N (after S1, HM1) Trigger or onboarding wizard step. Why: Depends on S1 and HM1; needs collision detection (strategy already exists) and audit trail when auto-linking. nan 2
Strategies S3 Client Active Medium Remove "Stay Window" from Base; replace DOW with yielding categories 3 M M Y UI + JSON expression schema update. Why: UI-only change on the strategy builder; no migration required because existing strategies keep their schema. nan 2
Strategies S5 Client Active Minor Shortened attribute names + template-based import/export 3 M L Y Naming map; reuse load_data pipeline. Why: Naming map is a simple lookup; CSV round-trip leverages existing Load_Data_v2 pipeline. Rename attributes to small - so that its easier to work (UX), hower should show everything. Need more clarification on template based import/export 2
Reports R1 Client Active Minor Column formatting — currency / % / # symbols + per-column decimals 3 M L Y Extend report definition JSON; DynamicReportJS.js render. Why: DynamicReportJS.js already handles conditional formatting paths, so this is a render extension on top of existing infrastructure. nan 2
Reports R2 Client Active Large ISR — Interactive Strategy Report (new build) 18 XH H N (after R1, T10) Greenfield page based on Daily Pick Up layout; calendar + multi-select advanced filter; date-cell dropdowns (High/Low Season, Pos/Neg Event, Price Override); live recalc on change. Why: Headline R2 deliverable. New APEX page with no template, mandatory-column protection, calendar/table dual-sync, per-date dropdowns, and a per-date live recalc SLA below 1 second. The SLA is the hardest piece: it forces a per-date evaluation path separate from full-cache refresh. Original 15 days under-counted the calendar-grid + JS sync work. nan 2
Reports R12 Client Active Medium Round-trip Excel import for ISR — only Manual Override and Strategy-to-Publish columns writable 5 M M N (after R2) Reuse Load_Data_v2 import pipeline (same as E3); add column-level write-protection mask on import mapping. Couples to R2 (ISR is the export source). Why: Import pipeline exists; new work is the column-whitelist enforcement, per-row diff preview, and audit-table writes. nan 2
Reports R13 Client Active Medium Per-date Rate-to-Publish strategy selection with bulk save + audit 6 M M N (after R2) Builds on R2 ISR's per-date dropdown infrastructure. New UR_RATE_TO_PUBLISH_AUDIT table (date, hotel, prev_strategy, new_strategy, prev_rate, new_rate, user, ts). Why: Reuses ISR dropdown JS; main work is multi-row save with optimistic-concurrency check, audit insert, and validation that selected strategy exists for the date. nan 2
Reports R18 Client Active Minor Static summary report shipped as default alongside Daily Pickup 3 L L Y New report definition row in UR_REPORTS with IS_GLOBAL=Y; nav entry under Reports. Why: Definition-only; leverages R8 user/global view scaffolding to mark it global; no new render logic. nan 2
Hotel Data T10 Client Active Medium Price Override page → history-only; move execution to ISR 5 M M N (blocks on R2) New audit table or repurpose UR_HOTEL_PRICE_OVERRIDE w/ flag. Why: Repurposing the existing pages to read-only history + wiring ISR to create overrides into the same table is moderate work; coupling to R2 means it can only progress once ISR scaffolding lands. nan 3
Strategies S4 Client Active No UI Add Pick-up & Pace as conditional triggers 5 M M N (after T5) Requires pickup/STLY data. Why: Depends on T5 for the underlying data. The 1074-line EVALUATE function must be extended to recognise two new attributes; touching this load-bearing code carries regression risk. nan 3
Strategies S6 Client Active Medium Hotel-level refinement of inherited strategies (DOW, rankings) 4 M M N (after S2) Override layer. Why: Adds an override layer that EVALUATE must respect at eval time; UI must mark overrides clearly. nan 3
Strategies S7 Client Active Minor Bulk import/export of strategies with validation tool 5 M M Y Builds on copy_strategy.sql; dup/error detection. Why: copy_strategy.sql is fully working; the bulk wrapper, CSV parsing, and validation reporting are the new work. nan 3
Strategies S11 Client Active Large Expression Builder redesign (user-friendly) 10 H M Y AlgoPGJS.js rewrite — visual condition builder. Why: AlgoPGJS.js is ~2,700 lines; visual builder is greenfield. Must handle all 30+ attributes and all math functions, round-trip parse-to-visual without data loss, plus usability testing. The second-longest single-item pole after the ISR. nan 3
Strategies S16 Client Active Medium Strategy AI Helper (Coder / Decoder) — NL ↔ strategy expression 5 M M Y HTTP-out via apex_web_service.make_rest_request (pattern in apps/application/deployment/install/install_installation_script_251223.sql); LLM provider TBD (recommend Claude Haiku for low-latency); coder output fed through existing validate_calculated_formula; chat-panel UI integrated into Expression Builder redesign (S11). Why: REST wrapper + prompt design (1d); coder + decoder paths (1d each); UI panel + validation feedback (1d); error handling and fallback to manual entry (1d). Tightly couples to S11. nan 3
Hotel Data T4 Client Active Medium Qualifier-based display rule (qualifier name vs raw column) 4 M M N (blocks on R5) Touches ajx_get_report_data.sql and template definition JSON. Why: Logic itself is small but tightly coupled to R5 — integration testing across report flows adds a day. nan 4
Hotel Data T5 Client Active No UI Store all imported data locally; internal Pick-up & STLY calc 14 H H N (blocks on T1) Schema change; backfill; pickup/STLY views. Performance critical. Why: Greenfield raw-import table, ~400 days of historical backfill, four pickup-window aggregations (1/3/7/14/30-day), STLY join logic, and integration into the already-600+ line Load_Data_v2. Original 8 days under-counted backfill and aggregation work. data versioning, storing data for same date saperately instead of upserting. Controlled by new Enable BI flag at hotel 4
Hotel Data T7 Client Active No UI Formula-based stay dates (Stay Date + 364) 3 M L Y Extend calculated-attribute formula validator. Why: validate_calculated_formula already handles cycle detection; date arithmetic is a small, well-bounded addition. nan 4
Strategies S9 Client Active Minor Full Strategy copy 1 L L Y copy_strategy.sql exists; expose in UI. Why: Backend procedure is complete and tested; only a UI button + confirmation is needed. nan 4
Strategies S10 Client Active Minor Test tool — default Today's Date + show occupancy 2 L L Y Page_01050 enhancement. Why: APEX default-value process plus joining existing occupancy data. nan 4
Strategies S12 Client Active Minor Strategy Type toggle (Pricing vs Other) 2 L L Y Column on UR_ALGOS. Why: One column plus a LOV; no logic change. nan 4
Strategies S13 Client Active No UI Performance target ≤2s for strategy calc 7 H H Y Cache hit-rate audit; queue tuning. Why: Performance work on the 1074-line EVALUATE function and cache queue; needs profiling, targeted rewrites (bulk-collect, indexing), and a perf regression harness. ISR's per-date recalc SLA depends on this. nan 4
Reports R3 Client Active Minor Advanced filtering — dynamic dates (Today, Today±X, BOM, BOY) 3 M L Y Filter builder addition. Why: Five new date-resolution functions plus calendar edge-case tests (leap year, month-end). nan 4
Reports R4 Client Active Minor Manage Reports — allow spaces in names 1 L L Y Validation relaxation. Why: Removing one form-level validation rule. nan 4
Reports R5 Client Active Minor Available-fields qualifier-aware display 3 M M Y Couples with T4. Why: ajx_get_report_data already has alias resolution; this adds a qualifier-aware label branch and is tested jointly with T4. nan 4
Reports R6 Internal Active Minor Preview bug — Advanced Filter not reflecting on load 2 L M Y ReportDashboardJS.js state hydration. Why: A state-hydration defect — half the time is finding it in a ~3,900-line JS file, half the time is the fix and regression test. nan 4
User Management U1 Client Active Medium Admin UI for user CRUD + role assign in-app 4 M M Y Page_01611/01612 already partial; wire manage_apex_user. Why: Pages exist in partial form and manage_apex_user is a mature backend procedure; work is form completion and wiring. nan 4
User Management U2 Client Active Large Role permission sets — Admin / Untapped / ReadOnly / External / External-Limited 7 M H Y Bitmask review per page; matrix doc; many regression points. Why: ~64 pages have permission protection × 5 roles = 320 access tuples to define and audit. CHECK_AUTHORIZATION must add role-aware logic. Original 4 days under-counted the audit and regression-test surface. nan 4
User Management U3 Internal Active Medium Hotel-level granular access (R/W/D per hotel) 8 H H N (after U2) Extend UR_USER_HOTELS_ACCESS with action mask. Why: Builds on U2 but additionally requires propagating hotel-scoped access checks into ~15–20 critical data queries (templates, load data, reservations, strategies, reports), plus UI button visibility wired off the mask. nan 4
User Management U4 Internal Active Medium Custom auth platform — Email Magic Link + MFA, migrate off seeded APEX accounts, bundled with bitmask access control as reusable component 17 H H N (after U2) Replace the original self-service password-reset item with a broader auth platform: (a) Email magic-link login (token table + apex_mail send + token validation + session bridge); (b) TOTP MFA enrolment + verify; (c) migrate authentication source from APEX seeded accounts to UR_USERS (keep authorization in existing CHECK_AUTHORIZATION + bitmask masks from U2/U3); (d) package the whole thing as a reusable APEX plugin / shared schema so future Eidos APEX apps (TnE Connect, future tools) can adopt it. Why: Magic-link flow (4d), TOTP MFA (3d), APEX-account → UR_USERS migration with shim during rollout (3d), bitmask wiring + role compatibility tests (2d), extract-as-reusable-component with install scripts + docs (3d), regression across all 6 roles incl. new U6 Hotel Admin (2d). Risk: every authentication path in the app is touched; rollout needs a parallel-run window so users can fall back to APEX accounts if magic-link/MFA delivery fails. Sequenced after U2 so role logic is stable. nan 4
Reports R14 Client Active Minor Lazy tab calc — only first report tab calculates automatically 2 L L Y APEX tab on-activation trigger; convert each tab's region from page-load to dynamic-action-on-tab-show. Touches DynamicReportJS.js and the report region attribute config. Why: Pure rewiring; perceived load-time win; tested per report type to ensure no regression in tab-switching UX. nan 4
Reports R16 Client Active Medium Default Daily Pickup — Master → Hotel → User view inheritance with auto-sync and audit trail 8 H H Y Add OWNER_SCOPE (Master/Hotel/User) + PARENT_REPORT_ID on UR_REPORTS; new UR_REPORT_OVERRIDES (user_id, report_id, parent_report_id, overridden_columns CLOB); runtime resolver walks scope chain. Mirrors the T2 template-inheritance design (also H risk). Audit table + triggers. Why: Inheritance pattern doesn't exist in the codebase; runtime resolution at every report render is the load-bearing piece. Highest single-item risk in this batch. nan 4
General G1 Client Active Medium Eliminate full-page refresh on data load; add manual refresh button 5 M M Y Audit page-submit branches; convert to APEX dynamic actions / partial-page refresh; reuse existing cache-queue (PROCESS_CACHE_QUEUE) signal. Why: 2–3 page-submit branches to convert; mature PROCESS_CACHE_QUEUE plumbing already exists, so most effort is UI rewiring + testing. nan 5
General G2 Client Active Large Global hotel selection sync across tabs 6 M M Y Promote hotel-id to application item written via DA on every page; refactor pages that read page-item local copy. Why: ~32 pages currently reference a page-level hotel-ID item independently; migration to a single application item requires touching ~20 pages and regression-testing the 5 critical hotel-scoped flows. Original 4 days underestimated touch points. nan 5
Hotel Management HM1 Client Active Minor Primary Strategy tiering (High/Mid/Low) on Hotels page 2 L L Y New column on UR_HOTELS; LOV; default for new hotels. Couples to S2. Why: One schema column + one LOV; isolated change with no cross-module dependencies. nan 5
Events E1 Client Active Minor Show descriptive Impact Type / Level names (not IDs) 1 L L Y Join to lookup; pure SQL change on page_00009 IR. Why: SQL-only label change on one report region. nan 5
Events E2 Client Active Medium Custom Event Types CRUD 2 L L Y New UR_EVENT_TYPES table + admin page. Why: Small new table with a standard admin CRUD page; one referential-integrity check on delete. nan 5
Events E3 Client Active Medium Bulk import/export template for events 4 M M Y Reuse UR_UTILS.Load_Data_v2() pipeline with event-specific mapping. Why: Pipeline exists, but a new CSV spec, mapping rules, and fixture-based testing add a day over the original estimate. nan 5
Events E4 Client Active Minor Copy events between hotels 2 L L Y Modal w/ multi-select dates; PL/SQL insert. Why: Single procedure + one modal; duplicate-detection is straightforward. nan 5
Hotel Data T3 Client Active Minor Automated stay-date save (collapse 2-step number+date) 2 L L Y DA on item change → server PL/SQL. Why: Single dynamic action on one page. date format ui change - da change - while creating new templates. 5
Hotel Data T6 Client Active No UI Duetto parser fix (auto-expand date columns) 5 M M Y Isolated to Load_Data_v2. Why: Detecting and expanding collapsed date headers (one column representing one day) requires safe format detection; date parsing is consistently a higher-risk area than it looks. nan 5
Hotel Data T11 Client Active Minor Reservations Update — user-customisable date range and revenue streams 3 M L Y Per-user prefs stored in UR_USER_PREFS (new). Why: Small new prefs table + UI controls; the underlying queries already filter by date and stream. nan 5
Strategies S8 Client Active Minor Stability fix: duplicate condition names + edit without refresh 2 M M Y Defensive PL/SQL + JS state refresh. Why: Small defensive check in ajx_manage_algo plus a DOM refresh in the builder JS. nan 5
Reports R7 Internal Active Minor Divide-by-zero → return 0 not "Calculation Issue" 1 L L Y DynamicReportJS.js + safe_divide audit. Why: Change the fallback value in two places (safe_divide and the JS path) and add tests. nan 5
Reports R9 Internal Active Minor Loaded-data preview widget 2 L L Y Couples with G3. Why: Single query returning first 50 rows of the most recent load; widget reuses G3 styling. nan 5
User Management U5 Internal Active No UI SaaS isolation — provision new organisation 14 XH H Y New UR_ORGANISATIONS tenant column on most tables; RLS/VPD or scoped views; provisioning script. Major undertaking. Why: 18 core UR_* tables need tenant scoping; backfill, isolation enforcement (row-level security or scoped views), provisioning script for new orgs, APEX workspace/ORDS replication, and cross-org leak testing on every tenant table. Highest single-item risk: every missed query is a data-leak vector. Original 10 days under-counted the breadth. nan 5
Housekeeping H2 Internal Active No UI Loaded files lifecycle (retention, archival, purge) 3 M M Y Couples to UR_INTERFACE_LOGS. Why: Adds an archival/purged state column and a scheduled transition job on the existing log table. nan 5
Reports R15 Client Active Medium Report Subscriptions — per-strategy refresh, error alerts, optional attachment, per user 6 M M Y New UR_REPORT_SUBSCRIPTIONS (user_id, report_id, schedule, attach_format, alert_on_error); DBMS_SCHEDULER per subscription; APEX_MAIL send (reuse from H1 cleanup automation); export routine new (no programmatic export exists). Why: Sub table + scheduler + email reuse mature patterns; the export-as-attachment routine is new work (HTML render → PDF/XLSX) and accounts for ~2 of the 6 days. nan 5
Reports R17 Client Active Minor Persist session state on Summary report (filters, scroll, column selection) 2 L L Y APEX session-state save on filter change; restore on page load. Reuse the UR_USER_PREFS table planned in T11. Why: One small APEX process + DA; trivial once T11 prefs table lands. nan 5
User Management U6 Client Active Medium Hotel Admin role — scoped to own hotel(s), can manage internal users within scope 4 M M N (after U2) Extends the 5-role matrix from U2 to 6 roles. CHECK_AUTHORIZATION gains a hotel-scoped admin branch; user-CRUD page (U1) filters its target list by the acting user's hotel scope. Why: Incremental on U2's per-page audit (the heavy lifting); main new work is the scope-filter on the user-list query and the create-user UI's hotel auto-fill. nan 5
General G4 Client Active Minor Header status widget — refresh-in-progress pill + last-load timestamp + stale-data indicator 2 L L Y Header region combining (a) JS poller of PROCESS_CACHE_QUEUE for the refresh-in-progress pill and (b) a read-only query against UR_INTERFACE_LOGS for the last-load timestamp / stale-data indicator. Replaces and absorbs the earlier internal G3 widget. Why: One header region with two cheap data sources; reuses the cache-queue signal already used by G1 and the existing log table — no new schema. nan 5
Hotel Data T14 Internal Parked Medium Global attributes UI (currently hardcoded) 4 M M Y Admin CRUD page; migrate constants → UR_GLOBAL_ATTRIBUTES. Why: New table + admin CRUD page is light; finding and refactoring all hardcoded constants in strategy/formula code is the bulk of the work. nan nan
Strategies S14 Internal Parked Minor Automated template colour coding by attribute set 2 M L Y Hash attribute fingerprint → colour; JS. Why: Pure JS — attribute-set hash → colour map; no schema change. nan nan
Teams Integration TI1 Internal Parked No UI Outbound webhook framework (reuse bug-reporter n8n pattern) 4 M M Y Generalise existing webhook plumbing. Why: Bug-reporter has the basic webhook + credential pattern; generalising into a PUBLISH_EVENT package with topic + payload + retries + logging adds testing scope. nan nan
Teams Integration TI2 Internal Parked No UI Push user-maintenance events to Teams 2 L L N (after TI1) Why: Small trigger on UR_USERS that calls the TI1 publisher with a user payload. nan nan
Teams Integration TI3 Internal Parked Minor Push strategy output for selected dates 4 M M N (after TI1, R2) ISR row → Teams card. Why: Depends on TI1 and ISR. Adds a "Send to Teams" action with a card-payload schema for hotel/dates/strategy/price. nan nan
Teams Integration TI4 Internal Parked No UI Push hotel/events data digests 3 M L N (after TI1) Why: Scheduled job batching daily changes per opted-in user; dedup table needed. nan nan
Housekeeping H1 Internal Parked Minor Debug log management (rotation, size cap, admin viewer) 3 M L Y DBMS_SCHEDULER purge; admin page. Why: Standard retention pattern on an existing log table; viewer page is a simple report. nan nan
Hotel Data T13 Internal Duplicate No UI RST attribute rename 0 L M N (after T5) Ranking views regenerate. Why: create_ranking_view already regenerates views, but renaming must update strategy expressions and UR_ALGO_ATTRIBUTES references — that hunt-and-replace adds risk. Rename attributes to small - so that its easier to work (UX), hower should show everything 5

Active scope: ~304 working days of dev effort across 61 items. Parked: ~22 days across 7 items (kept on the page for traceability, tagged Parked in the status column). Removed / duplicate (0d, kept for traceability): H6 (already shipped and live), R10, R8, R11 (duplicates of client items), G3 (merged into G4), and T13 (now a duplicate of the T5/S5 attribute-rename work, tagged Duplicate). See "Scope Trim Note" below.

Resourcing (current). One developer (the user) with a 120 dev-day window for Release 2. Testing is handled separately and is not deducted from the dev budget. The 304 active days therefore over-runs the 120-day window by ~2.5×; the next planning step is to triage the active backlog into a Sprint-fit subset and either defer or contract the remainder. The previous three-resource parallel plan (former Section 6) is preserved below as a reference only and no longer reflects the working plan.

Scope Trim Note (this revision). - Removed (already live): H6 ADB DR — implemented on the live tenancy. - Removed (duplicates of client items): R8 (covered by client R16 Master→Hotel→User), R10 (covered by client perf items T9/S13/R14), R11 (covered by client R1 decimal precision). - Merged: G3 (stale-data widget) folded into G4 so the header carries one combined "refresh in progress + last load + stale" widget (2d). - Parked for future release: T14 (global attributes UI), S14 (template colour coding), H1 (debug log mgmt — partially implemented already), and all of TI1–TI4 (Teams integration — pick up only if Sprint slack permits). - Reclassified (duplicate): T13 (RST attribute rename) — folded into the T5 local-store / S5 shortened-names rename work; effort dropped 3d → 0d, tagged Duplicate. - Expanded scope: U4 was originally a 3d password-reset item; it is now a 17d custom auth platform (Email Magic Link + TOTP MFA, migrate off seeded APEX accounts, packaged as a reusable component for other Eidos APEX apps). H risk; depends on U2.

Round 2 Additions Note. A second client review pass added 12 requirements (IDs G4, T15, S15, S16, R12–R18, U6). Largest items now in scope: R16 Master→Hotel→User report inheritance (8d, H risk), T15 per-hotel shared-drive auto-load (7d), R13 Per-date Rate-to-Publish save with audit (6d), R15 Report Subscriptions (6d). See the cleaned client functional brief for the wording these IDs were derived from.

Estimate Validation Note. After the first draft, three specialists ran a deep review of the codebase to pressure-test each item. They revised 24 items: 22 upwards (the largest being T5 Pickup/STLY storage 8→14, U5 SaaS isolation 10→14, T2 template inheritance 8→12, H5 test infra 8→12, S11 expression builder 8→10, R2 ISR 15→18) and 4 downwards (most notably T12 template lifecycle UI 3→2 because the delete_template backend already exists from UR-222). The "Why" line in each row records the reasoning.

Client Priority Note (this revision). The stakeholder session assigned a development priority 1–5 to every Active item (internal_feedback column); the requirements CSV above is now ordered by it. The bands below summarise the load. Triage of these bands into the 120-day window is the next planning step and is not decided here.

Priority Items Dev-days Cumulative
P1 (first) 8 — T1, T2, T8, H3, H4, H5, T15, S15 56 56
P2 11 — T9, T12, S1, S2, S3, S5, R1, R2, R12, R13, R18 58 114
P3 6 — T10, S4, S6, S7, S11, S16 34 148
P4 17 — T4, T5, T7, S9, S10, S12, S13, R3, R4, R5, R6, U1, U2, U3, U4, R14, R16 88 236
P5 (last) 19 — G1, G2, HM1, E1, E2, E3, E4, T3, T6, T11, S8, R7, R9, U5, H2, R15, R17, U6, G4 68 304
Active total 61 304

Notably, P1 + P2 = 114 dev-days, landing just under the 120-day window — a natural anchor for the triage discussion.

5. Dependency Graph

Critical chains (left → right means "must finish before"):

  • T1 → T2, T5, T8 → S4 → R2 (ISR) → TI3
  • HM1 → S1 → S2 → S6
  • R1 → R2, R11
  • T4 ↔ R5 (jointly designed)
  • T10 → R2 (price override execution lives inside ISR)
  • U2 → U3, U4 (custom auth platform), U6 (Hotel Admin role) — all sequenced after the role refactor
  • R2 (ISR) → R12, R13 (Excel round-trip and per-date Rate-to-Publish both extend ISR)
  • S11 → S16 (AI Helper UI lives inside the redesigned Expression Builder)
  • T11 (UR_USER_PREFS) → R17 (Summary report session state reuses the new prefs table)

Independent islands (start day 1): G1, G2, G4, E1–E4, T3, T6, T7, T12, T15, S3, S8, S9, S10, S12, S15, R3, R4, R6, R7, R14, R15, R16, R18, U1, U5, H2.

Parked from R2 (not on the dependency graph): T14, S14, H1, TI1, TI2, TI3, TI4.

6. Parallel Workstream Plan (reference only — not the current plan)

Note. This three-resource breakdown was the original plan when the team assumed three developers. The current working assumption is one developer with a 120-day window (see Resourcing note above), so this section is retained as a reference for what a fully-staffed delivery would have looked like but is not the working plan. The sprint-by-sprint allocations below still reference items that have since been removed (H6, R8, R10, R11) or parked (T14, S14, H1, TI1–4); treat the names as illustrative only.

Each resource owns a coherent slice to minimise merge conflicts and context switching.

R1 — Lead Architect

Focus: schema-level decisions, the ISR build, SaaS isolation design, code-review gate.

  • Sprint 1: G1, G2, G3, T1, R1, schema design for T2/T5/U5.
  • Sprint 2: R2 (ISR) — kickoff + scaffold, T10.
  • Sprint 3: R2 (ISR) — complete, R3, R5/T4 (with R3).
  • Sprint 4: U5 (SaaS isolation), TI3.
  • Sprint 5: Hardening, perf, release.

Total ~75 working days (after estimate validation).

R2 — Backend / PL/SQL Engineer

Focus: data pipeline, strategy engine, performance.

  • Sprint 1: H5 (test infra — first week), T6 (Duetto fix), T7 (formula stay dates), T12 (template lifecycle UI hooks), S8, S9, T3.
  • Sprint 2: T2 (inheritance), T5 (local pickup/STLY) — large block.
  • Sprint 3: T5 cont., T8, T9 (load perf), S13 (strategy perf).
  • Sprint 4: S1, S2, S4, S6, S7.
  • Sprint 5: H1, H2, H3 (release-notes automation), H6 (ADB DR cutover, with R1), perf pass, bug bash.

Total ~95 working days (after estimate validation; T5 + T9 + S13 grew significantly).

R3 — APEX + Frontend Engineer

Focus: UI/UX, JS widgets, user management, events.

  • Sprint 1: HM1, E1, E2, E3, E4, R4, R6, R7, R8, R11, T14.
  • Sprint 2: S3, S10, S12, S14, S5, U1, U4.
  • Sprint 3: S11 (Expression Builder redesign — large), R10, T11, T13.
  • Sprint 4: U2, U3, TI1, TI2, TI4, H4 (bug-reporter v2).
  • Sprint 5: ISR UI polish (pair with R1), regression fixes.

Total ~90 working days (after estimate validation; U2/U3 + S11 + H5 grew).

Parallelisation Notes

  • Sprint 1 can start fully in parallel — no item in the sprint-1 buckets depends on another sprint-1 item across resources.
  • R2 (ISR) is the only XH item on R1's plate and sits on the critical path; protect it from scope creep.
  • U5 (SaaS isolation) is the second-longest pole. If timeline is tight, defer to R3 sprint 6 or R2.1.

7. Phased Timeline

Sprint Weeks Theme Headline Deliverables
1 1–2 Foundations & quick wins G1/G2/G3, T1, R1/R3–R11, HM1, E1–E4, T3/T6/T7/T12/T14, S8–S10/S12/S14, U1/U4
2 3–4 Inheritance & data lake T2, T5 (start), S3/S5, U2 design, ISR scaffold
3 5–6 ISR + perf R2 ISR live (flagged), T5 complete, T8/T9, S11, R10
4 7–8 Strategy tiering & access S1/S2/S4/S6/S7, S13, U2/U3, TI1/TI2/TI4, T10
5 9–10 SaaS isolation, hardening U5, TI3, H1/H2, perf bash, UAT, release cut

8. Risks & Open Questions

  1. ISR scope — "live recalculation on any strategy change" needs explicit SLA; current cache queue is ~10–15s for full hotel. Either narrow recalc scope (per-date) or invest in incremental cache.
  2. SaaS isolation (U5) — choosing the multi-tenancy approach (database-level row isolation, separate schema per organisation, or a shared tenant identifier on each record) is a multi-week decision and must be locked before Sprint 4. Consider deferring to Release 2.1 if the timeline is tight.
  3. T5 (local storage + Pick-up/STLY) — backfill volume unknown; could explode load_data perf budget (T9).
  4. U2 role refactor — high regression risk against every page; ensure automated authorisation tests before changes.
  5. Teams integration (TI1) — clarify whether webhook target is Teams direct (Incoming Webhook URL) or via n8n. Affects auth model.

  6. Expression Builder redesign (S11) — UX deliverable; needs design mock-up sign-off before R3 sprint 3.

  7. T5 data versioning (new client requirement) — the client wants imported data for the same date stored as separate versions rather than upserted, gated by a new per-hotel "Enable BI" flag. This enlarges the T5 raw-store design (history is per-load, not per-date) and pushes up the backfill/storage volume — directly affecting the T9 load-perf budget. Retention policy for old versions is undecided.

  8. S5 import/export scope (needs clarification) — shortened attribute names are confirmed (UI shows the short name, hover shows the full name), but the template-based import/export half of S5 is not yet specified. S5 cannot be fully scoped until the client clarifies the format and round-trip expectation.

Open questions for client: - Should price-override history in T10 preserve every override or just net-effective per stay date? - For SaaS isolation, is cross-org reporting ever required? (Affects design.) - Read-only role: includes export rights? - T5: what retention applies to versioned loads (keep all, or cap by age/count)? Which hotels start with the "Enable BI" flag on? - S5: what is the template-based import/export format and round-trip contract?

9. Verification & Acceptance Strategy

Every ticket must meet four gates before it is considered "done":

  1. Acceptance criteria — explicit, observable, listed per item in Section 10. PM/client sign-off uses this list.
  2. Automated tests — at least one of: utPLSQL test (PL/SQL changes), Jest test (shared JS), Playwright/Cypress flow (APEX UI). Built on H5 infrastructure.
  3. Manual smoke checklist — stored alongside each ticket; covers golden path + one negative path.
  4. Perf budgets where applicable — load_data ≤3s, strategy calc ≤2s, ISR re-render ≤1s per date change. Measured via UR_INTERFACE_LOGS + perf-probe procedure.

Additional release-level controls:

  • Feature flags — ISR, SaaS isolation, Teams integration behind app-item toggles for staged rollout.
  • UAT environment — refreshed from prod each sprint; client sign-off gate before merging to release branch.
  • Pre-merge CI — once H5 is live, all four gates must be green before merge.
  • Regression suite — scripted run-through (hotel onboarding → template → load data → strategy → ISR → report → export) executed at end of each sprint.

10. Acceptance Criteria & Test Cases (per item)

Format per item: AC = acceptance criteria (observable, testable). TC = test cases (automated where marked [auto], otherwise manual).

10.1 Cross-Cutting / General

G1 — Eliminate full-page refresh on data load - Use case: Today, after uploading data the admin has to wait for and tolerate a full page reload. After this change the affected grid updates in place; a small "Manual Refresh" button is available for rare edge cases. - AC1: After data upload, the affected region updates without a browser-level reload. - AC2: A visible "Manual Refresh" button exists on the data-load page. - AC3: No console errors; cache-queue signal is consumed. - TC1 [auto]: Playwright — upload a small CSV; assert URL unchanged and target region's DOM updates within 3s. - TC2 [auto]: Click "Manual Refresh"; assert refresh AJAX fires and grid re-binds. - TC3 [manual]: Rapid two-file upload — second upload must not lose first's results.

G2 — Global hotel selection sync - Use case: Today, switching the hotel on one tab leaves other tabs showing the old hotel until each is changed. After this change, picking a hotel anywhere updates the context everywhere; reloading the page preserves the choice. - AC1: Changing hotel on any tab updates the application item and is reflected on all other tabs without reload. - AC2: Page reload preserves selection. - TC1 [auto]: Playwright — change hotel on Tab A, switch to Tab B; assert Tab B's hotel dropdown matches. - TC2 [auto]: utPLSQL — set APP_HOTEL_ID, call any hotel-scoped query, assert correct hotel returned. - TC3 [manual]: User without access to selected hotel is gracefully blocked.

G3 — Stale-data indicator + last-X-loads widget - Use case: Today, users have no easy way to tell whether the data they are looking at is fresh. After this change, a header widget shows the most recent loads with timestamps and turns amber when data is older than a configurable threshold. - AC1: Widget shows N most recent loads with hotel, template, timestamp, status. - AC2: Stale indicator turns amber after configurable threshold (default 24h). - TC1 [auto]: utPLSQL — insert old UR_INTERFACE_LOGS row; assert widget query flags stale. - TC2 [manual]: New load updates widget within one refresh cycle.

10.2 Hotel Management

HM1 — Primary Strategy tiering - Use case: Today, every hotel uses one undifferentiated set of base strategies. After this change, each hotel is classified High / Mid / Low, which feeds the auto-inheritance step S2 so new hotels start with the right base strategies on day one. - AC1: Hotels table has High/Mid/Low tier column with LOV. - AC2: New hotels default to Mid; existing hotels migrated explicitly. - TC1 [auto]: utPLSQL — insert hotel without tier; assert default = Mid. - TC2 [auto]: Playwright — edit tier from Mid→High; reload; assert persisted.

10.3 Events

E1 — Descriptive Impact Type / Level names - Use case: Today, the events overview shows numeric IDs that staff need a key to interpret. After this change, the report shows the descriptive name directly. - AC1: Overview table shows names, not IDs. - TC1 [auto]: SQL test — query reports source contains JOIN to lookup; assert no raw IDs in output. - TC2 [manual]: Visually confirm on page_00009.

E2 — Custom Event Types CRUD - Use case: Today, the event-type list is fixed in code. After this change, an admin can add a new event type (for example a local festival) in the UI without a release. - AC1: Admin can create/edit/delete event types; deletion blocked if in use. - TC1 [auto]: utPLSQL — create type, attach to event, attempt delete, assert error. - TC2 [auto]: Playwright — full CRUD flow on admin page.

E3 — Bulk import/export events - Use case: Today, events are added one at a time via the form. After this change, an admin downloads a template, fills in many rows offline, uploads, and sees a clear report of which rows loaded and which failed. - AC1: Download template; upload populated; rows appear in UI. - AC2: Bad rows reported with line numbers, valid rows still loaded. - TC1 [auto]: Upload fixture with mixed valid/invalid; assert split. - TC2 [manual]: Round-trip — export 10 events, re-import; counts match.

E4 — Copy events between hotels - Use case: Today, the same event (e.g. a city-wide convention) must be re-entered for each property. After this change, an admin picks the source hotel, the destination hotel, a date range, previews, and confirms in one action. - AC1: Source/destination + date-range pickers; preview before confirm. - AC2: Duplicate detection prevents collisions. - TC1 [auto]: utPLSQL — copy procedure rejects duplicates. - TC2 [manual]: Visual copy of 5 events; assert in target hotel.

10.4 Hotel Data

T1 — Source-based templates - Use case: Today, templates are scoped per hotel, so the same Lighthouse layout is recreated for every property. After this change, templates are grouped by source (Lighthouse, Duetto, PMS), letting admins pick the right one without confusion. - AC1: Template has SOURCE (Lighthouse/Duetto/PMS/Other); filterable. - TC1 [auto]: utPLSQL — create per source; query by source. - TC2 [auto]: Playwright — UI filter shows correct subset.

T2 — Master template inheritance - Use case: Today, identical template logic is duplicated across every hotel; a change must be made in many places. After this change, a master template can be edited once and propagates to all hotels that have not overridden the specific attribute; per-hotel refinements are preserved. - AC1: Hotel can pick a master template; per-hotel overrides do not mutate master. - AC2: Master change propagates to hotel templates that have not overridden the affected attribute. - TC1 [auto]: utPLSQL — modify master attribute; assert hotel without override picks it up; hotel with override unchanged. - TC2 [auto]: Cycle detection — prevent A→B→A inheritance.

T3 — Automated stay-date save - Use case: Today, updating a stay date requires saving the number and then saving the date in two separate steps. After this change, a single field edit is saved automatically. - AC1: Single field change persists without secondary "save date" step. - TC1 [auto]: Playwright — change stay-date number; switch field; assert persisted.

T4 — Qualifier-based display rule - Use case: Today, a column from a third-party file shows in reports with its raw, often cryptic header. After this change, if a qualifier label is assigned the label is shown; if not, the original column name is shown — so reports stay readable and consistent. - AC1: If qualifier set → qualifier label shown; if blank → raw column name shown. - TC1 [auto]: utPLSQL on ajx_get_report_data; assert label selection logic.

T5 — Local storage + internal Pickup/STLY - Use case: Today, the system depends on external feeds to provide Pick-up and Same-Time-Last-Year figures, which limits flexibility and accuracy. After this change, Parallax retains imported history and computes both internally, enabling Pick-up and Pace to be used as strategy triggers. - AC1: Raw imports retained ≥400 days. - AC2: Pickup and STLY values match an external reference dataset within tolerance. - TC1 [auto]: utPLSQL — load fixture covering 2 years; query STLY; assert match. - TC2 [auto]: Pickup over 7-day window matches hand-computed value.

T6 — Duetto parser fix - Use case: Today, Duetto exports fail to upload until the user manually expands the date columns in Excel. After this change, raw Duetto exports load without any manual preparation. - AC1: Raw Duetto exports (collapsed date columns) load with zero manual prep. - TC1 [auto]: Upload three real-world Duetto fixtures; assert all parse successfully.

T7 — Formula-based stay dates - Use case: Today, year-over-year comparisons can fall on different days of the week, distorting the read. After this change, a formula like Stay Date + 364 keeps the same day-of-week and produces an accurate comparable. - AC1: Formula like STAY_DATE + 364 validates and resolves to same-DOW prior year. - TC1 [auto]: utPLSQL — feed formula; assert result. - TC2 [auto]: Invalid formula rejected with clear message.

T8 — Batch upload auto-routing - Use case: Today, the user selects each file and the matching template separately. After this change, multiple files can be uploaded at once and the system routes each to its template by inspecting the headers; anything ambiguous prompts a manual match. - AC1: User selects N files; system routes each to its template by header signature. - AC2: Unrouted files surface a manual-match modal. - TC1 [auto]: Upload 3 files (2 routable, 1 not); assert correct routing + modal.

T9 — Load-data perf ≤3s - Use case: Today, loading a typical export can take 10–15 seconds, slowing the morning routine. After this change, the same load completes within 3 seconds. - AC1: P95 single-file load ≤3s on reference dataset (≤50k rows). - TC1 [auto]: Perf test in CI — load fixture; assert duration; fail if >3s.

T10 — Price Override audit-only - Use case: Today, price overrides are entered on a dedicated screen separated from the strategy context. After this change, overrides are created inside the Interactive Strategy Report — the dedicated screen becomes an audit history of every change with who, when, and what. - AC1: Old Price Override page is read-only history; new entries created from ISR. - AC2: Full history preserved (no destructive migration). - TC1 [auto]: utPLSQL — assert insert from non-ISR source blocked. - TC2 [manual]: Pre-existing overrides still visible after deploy.

T11 — Reservations Update customisation - Use case: Today, the Reservations Update table is one-size-fits-all. After this change, each user can choose the date range and the revenue streams they care about and the choice is remembered next time. - AC1: User can set date range + revenue-stream columns; persists per user. - TC1 [auto]: Save preference, log out/in, assert persisted.

T12 — Template delete/duplicate/common templates UI - Use case: Today, removing a template requires backend work and duplicating one means rebuilding from scratch. After this change, an admin can preview impact, delete safely, duplicate as a starting point, and mark templates as "common" for reuse — all from the UI. - AC1: Delete shows impact report and blocks if referenced strategies are not handled. - AC2: Duplicate creates an editable clone. - TC1 [auto]: utPLSQL — delete with dependants; assert blocked. - TC2 [auto]: Duplicate; assert new ID, identical attributes, independent.

T13 — RST attribute rename - Use case: Today, renaming a ranking-template attribute is unsafe because references in strategies are easy to miss. After this change, renaming updates the underlying view and all strategy expressions automatically. - AC1: Rename updates view, all references, strategy expressions. - TC1 [auto]: utPLSQL — rename; query view; assert new name; old references resolved.

T14 — Global attributes UI - Use case: Today, system-wide thresholds and constants are hardcoded and require a release to change. After this change, an admin updates them in a dedicated page and downstream calculations pick up the new value on the next evaluation. - AC1: Admin CRUD page; values persisted in UR_GLOBAL_ATTRIBUTES; consumers read from DB not constants. - TC1 [auto]: Change a value; assert downstream computation reflects it.

10.5 Strategies

S1 — Tiered Base Strategy framework - Use case: Today, base strategies are one flat set. After this change, they are categorised as High / Mid / Low, mirroring how hotels are tiered, so the right strategies attach to the right hotels. - AC1: Base strategies categorised High/Mid/Low. - TC1 [auto]: Create base per tier; query by tier.

S2 — Auto-inherit on hotel onboarding - Use case: Today, when a new hotel is added, the revenue manager manually attaches every strategy. After this change, the moment a hotel's tier is set, all base strategies for that tier are linked automatically. - AC1: New hotel of tier T automatically receives all base strategies for tier T. - TC1 [auto]: utPLSQL — create hotel; assert strategies linked.

S3 — Remove Stay Window; replace DOW with Yielding categories - Use case: Today, base strategies must specify a Stay Window and a specific Day of Week, which is rigid. After this change, the base strategy uses High/Medium/Low yielding categories; specific day-of-week tuning moves to the hotel-level strategy. - AC1: Base Strategy UI no longer offers Stay Window; offers High/Med/Low yielding. - TC1 [auto]: Migration test — existing strategies upgraded without data loss.

S4 — Pick-up & Pace as triggers - Use case: Today, strategies cannot react directly to Pick-up or Pace because that data is not available internally. After this change (and T5), a strategy can fire when Pick-up exceeds a threshold or Pace falls below one. - AC1: Conditions can reference PICKUP and PACE; evaluation produces correct boolean. - TC1 [auto]: utPLSQL — set fixture pickup; trigger condition; assert fires.

S5 — Shortened names + import/export - Use case: Today, attribute names are long and a strategy migration between environments is manual. After this change, names are short and intuitive in the UI, and strategies can be exported to CSV and re-imported without loss. - AC1: Attribute names use short labels; CSV export+re-import is lossless. - TC1 [auto]: Round-trip a strategy; assert byte-identical re-export.

S6 — Hotel-level refinement - Use case: Today, a hotel either uses a base strategy as-is or recreates it. After this change, a hotel can override specific pieces (day-of-week, rankings, even create unique strategies) without touching the shared base. - AC1: Hotel-level override of inherited strategy does not alter Base. - TC1 [auto]: Override DOW; modify Base; assert override preserved.

S7 — Bulk import/export with validation - Use case: Today, mass strategy updates are slow and error-prone. After this change, a manager exports many strategies, edits them offline, re-imports them, and receives a validation report flagging duplicates and errors before anything is applied. - AC1: Import flags duplicates, type errors, missing attributes; valid rows still load. - TC1 [auto]: Mixed-fixture import; assert error report.

S8 — Duplicate condition stability - Use case: Today, two conditions with the same name can cause the strategy builder to crash and any rename requires a full refresh. After this change, the builder stays stable and a rename takes effect immediately. - AC1: Two conditions with same name do not crash UI; rename works without reload. - TC1 [auto]: Create dup; assert UI stable; rename; assert change reflected.

S9 — Full Strategy copy - Use case: Today, copying a strategy means manually re-creating every condition. After this change, a single "Copy" button produces a complete editable clone. - AC1: Copy reproduces all conditions, expressions, links. - TC1 [auto]: Copy; diff result vs source; assert structural equivalence.

S10 — Test tool defaults - Use case: Today, the strategy test tool starts blank and requires the user to enter the date and look up occupancy elsewhere. After this change, it opens with today's date pre-filled and the day's occupancy already on screen. - AC1: Test panel defaults to today's date; shows occupancy. - TC1 [auto]: Playwright — open test; assert today selected, occupancy non-empty.

S11 — Expression Builder redesign - Use case: Today, building a condition requires typing raw expression syntax. After this change, a manager constructs the same logic visually — selecting attributes, operators, and values from form controls — without remembering syntax. - AC1: All existing expressions render correctly in new builder (no data migration loss). - AC2: Usability benchmark: build a 5-condition strategy in ≤90s in user test. - TC1 [auto]: Round-trip — open every prod strategy; assert no parse errors. - TC2 [manual]: Usability test with 3 revenue managers.

S12 — Strategy Type toggle - Use case: Today, all strategies appear together regardless of whether they drive price. After this change, Pricing strategies appear in the ISR pricing dropdown and Other strategies appear in a static informational column, keeping the ISR focused. - AC1: Pricing strategies appear in ISR dropdown; Other appear in static ISR column. - TC1 [auto]: utPLSQL + ISR query; assert split.

S13 — Strategy calc ≤2s - Use case: Today, strategy evaluation can stall the interactive experience. After this change, a single hotel-day evaluation completes within two seconds, which is the prerequisite for ISR live recalculation. - AC1: P95 evaluation for a single hotel-day ≤2s. - TC1 [auto]: Perf test on reference dataset.

S14 — Automated template colour coding - Use case: Today, strategies look uniform and visual scanning is slow. After this change, strategies sharing the same template attributes share a colour, so groups are spottable at a glance. - AC1: Strategies whose attribute set is identical receive identical colour. - TC1 [auto]: Hash function unit test — same attr set → same hash.

10.6 Reports

R1 — Column formatting (symbols + decimals) - Use case: Today, all numbers render the same way regardless of whether they are revenue, percentage, or count. After this change, each column can be configured for currency, %, or count and the number of decimal places — and the format is honoured both on screen and on export. - AC1: Per-column currency/%/# and decimal precision configurable; persisted; rendered. - TC1 [auto]: Configure column; export; assert format applied in export and on screen.

R2 — ISR (new build) - Use case: Today, revenue managers move between separate reports to read Daily Pick-Up, choose strategies, and apply price overrides. After this change, a single Interactive Strategy Report lets them see the day, pick a strategy on a calendar or via a filter, apply seasonal/event/override tags per date, and see the resulting price recalculate within a second. - AC1: Pre-configured Daily Pick-Up layout loads with mandatory columns un-removable. - AC2: Strategy assignment via calendar AND multi-select filter both work. - AC3: Per-date dropdown offers High Season / Low Season / Positive Event / Negative Event / Price Override. - AC4: Live recalc on change — visible delta within 1s per date. - AC5: Advanced filter supports Today, Today±X, BOM, BOY, specific date. - TC1 [auto]: Playwright — assign strategy on date; assert recalc visible ≤1s. - TC2 [auto]: Try to remove mandatory column; assert blocked. - TC3 [auto]: Each advanced-date option returns correct row count for fixture. - TC4 [auto]: Price Override created in ISR appears in T10 history.

R3 — Dynamic-date filters - Use case: Today, filtering to "the next seven days" requires picking dates manually each session. After this change, options such as Today, Today±X, Beginning of Month, or Beginning of Year compute the range automatically. - AC1: Each option computes correct date relative to system date. - TC1 [auto]: utPLSQL with frozen sysdate; assert each option's resolved date.

R4 — Spaces in report names - Use case: Today, report names cannot contain spaces, which forces awkward underscores. After this change, a report can be called "Daily Revenue Tracker" naturally. - AC1: Names with spaces save and load correctly. - TC1 [auto]: Create "My Daily Report"; reload; assert intact.

R5 — Qualifier-aware available fields - Use case: Today, the field picker often shows raw column names that mean nothing to the business user. After this change, the picker shows the qualifier label when set, otherwise the original column name. - AC1: Field picker displays qualifier when set, else raw column name. - TC1 [auto]: Toggle qualifier; assert label.

R6 — Advanced Filter load bug - Use case: Today, a saved advanced filter only partially appears when the report is reopened, so users rebuild it each time. After this change, the entire saved filter renders on load and stays editable. - AC1: Saved advanced filter is fully visible/editable on reopen. - TC1 [auto]: Save filter w/ 3 clauses; reopen; assert all 3 shown.

R7 — Divide-by-zero - Use case: Today, formulas that divide by zero show a red "Calculation Issue" cell that distracts users. After this change, the cell shows 0 so the rest of the report stays readable. - AC1: Division by zero returns 0 (or configured fallback), not "Calculation Issue". - TC1 [auto]: Jest on DynamicReportJS — assert div/0 path. - TC2 [auto]: utPLSQL on safe_divide.

R8 — User vs Global report views - Use case: Today, all reports are shared and users see each other's experiments. After this change, users keep personal reports private and admins publish global, standard reports that everyone sees. - AC1: Users can mark reports as personal or global; only admins create global; users see own + global. - TC1 [auto]: utPLSQL — non-admin attempt to mark global → rejected.

R9 — Loaded-data preview widget - Use case: Today, after a load the user has to navigate to a report to see what arrived. After this change, a small preview widget shows a sample of the most recent load directly in context. - AC1: Widget shows a sample (first 50 rows) of latest load. - TC1 [auto]: Load fixture; assert widget query returns first 50 rows.

R10 — Report perf pass - Use case: Today, wide reports take notably longer than narrow ones because of per-column overhead. After this change, the column resolution is cached so wide reports render at near-flat speed. - AC1: P95 report render ≤5s for reference dataset. - TC1 [auto]: Perf test in CI.

R11 — Per-column rounding - Use case: Today, rounding rules are global. After this change, each column has its own rounding rule respected on screen and on export. - AC1: Per-column rounding rule applied to display and export. - TC1 [auto]: Configure rounding; assert export reflects.

10.7 User Management & SaaS Isolation

U1 — Admin user CRUD UI - Use case: Today, creating or modifying users requires backend work. After this change, an admin completes user provisioning, role assignment, and deactivation directly in the application. - AC1: Admin can create user, assign role, edit, deactivate — all from APEX. - TC1 [auto]: Playwright — full CRUD; assert APEX account created.

U2 — Role permission sets - Use case: Today, roles are limited and inconsistent across pages. After this change, the five defined roles — Admin, Untapped, Read Only, External, External-Limited — each have an explicit, audited permission matrix. - AC1: Each role's permission matrix matches the brief on every page. - TC1 [auto]: Generated matrix test — for each (role, page) tuple, assert observed access matches spec.

U3 — Hotel-level granular access (R/W/D) - Use case: Today, hotel access is binary: a user either sees a hotel or does not. After this change, the admin assigns read, write, or delete rights per hotel per user, and forbidden actions are hidden in the UI and blocked at the data layer. - AC1: Mask enforced at SQL level; UI hides forbidden actions. - TC1 [auto]: utPLSQL — attempt write with read-only mask; assert blocked. - TC2 [auto]: UI test — delete button hidden when no D bit.

U4 — Custom auth platform (Email Magic Link + MFA, migrate off seeded APEX accounts) - Use case: Today, Parallax uses Oracle APEX's seeded account authentication and supports only administrator-driven password resets. After this change, users authenticate via an email magic link with optional TOTP MFA, the authentication source is UR_USERS (not APEX seeded accounts), and the same auth + bitmask-authorization stack is packaged as a reusable component so future Eidos APEX apps can adopt it without re-implementing. - AC1: User requests a magic link; receives email within configured SLA; clicking the link establishes a Parallax session bound to the correct UR_USERS row; link is single-use and expires after the configured window. - AC2: User can enrol TOTP MFA; on subsequent magic-link logins the TOTP step is required and verified. - AC3: Existing users continue to log in during the migration window via a fallback path (APEX seeded account) until a per-user cut-over flag is flipped. - AC4: Component is published as a self-contained APEX plugin / shared schema with install scripts and a one-page integration guide referenced from the docs. - TC1 [auto]: utPLSQL — request magic link; assert token generated, single-use, time-limited, bound to a UR_USERS row. - TC2 [auto]: Expired or replayed magic-link token rejected. - TC3 [auto]: utPLSQL — enrol TOTP secret; verify a valid 6-digit code at known time; reject codes from outside the window. - TC4 [auto]: Authorisation regression — for each of the 6 roles (incl. new U6 Hotel Admin), the bitmask checks return identical results before and after the auth migration.

U5 — SaaS isolation (multi-tenancy) - Use case: Today, Parallax is single-tenant — provisioning a new client requires standing up a separate instance. After this change, a new organisation can be provisioned within the same application, with strict guarantees that no organisation can see or guess another organisation's data. - AC1: User in org A cannot query, list, or guess records from org B (verified by SQL probe). - AC2: Provisioning script creates a clean new org with seed data. - AC3: Cross-org leak test passes for every tenant-scoped table. - TC1 [auto]: SQL probe — for each tenant table, attempt cross-org read with org-A session; assert zero rows. - TC2 [auto]: Provision new org; run smoke flow end-to-end.

10.8 Teams Integration v1

TI1 — Outbound webhook framework - Use case: Today, the only external integration is the bug-reporter webhook. After this change, the system has a general-purpose publishing pathway that other features (Teams cards, future integrations) can plug into. - AC1: Generic publisher accepts payload + topic; retries on failure; logs to UR_INTERFACE_LOGS. - TC1 [auto]: utPLSQL — publish to mock endpoint; assert delivery + retry on 5xx.

TI2 — User-maintenance events to Teams - Use case: Today, user provisioning happens silently. After this change, key events (user created, role changed, user deactivated) post a card to a configured Teams channel so administrators have an audit trail without leaving Teams. - AC1: User create/update/deactivate triggers a Teams card. - TC1 [auto]: Trigger event; assert payload contents.

TI3 — Strategy output for selected dates - Use case: Today, sharing a price recommendation with the wider team means screenshots and copy-paste. After this change, the revenue manager selects dates in the ISR and posts the strategy and resulting price as a Teams card with one click. - AC1: User-selected date range from ISR posts to Teams as a card with hotel, dates, strategy, price. - TC1 [auto]: ISR action; assert payload schema.

TI4 — Hotel/events data digests - Use case: Today, hotel and event updates are visible only inside Parallax. After this change, opted-in users receive a daily digest in Teams summarising the day's hotel and event changes. - AC1: Scheduled digest posts daily; deduplicated; respects user opt-in. - TC1 [auto]: Run scheduler tick; assert one digest per opted-in user.

10.9 Housekeeping & Ops

H1 — Debug log management - Use case: Today, debug logs accumulate without a clear retention policy and viewing them needs database access. After this change, logs purge automatically beyond a retention window and admins can search them through an in-app viewer. - AC1: Logs older than retention window auto-purged; admin viewer with filter/search. - TC1 [auto]: Insert old row; run purge job; assert deleted.

H2 — Loaded files lifecycle - Use case: Today, uploaded files accumulate indefinitely. After this change, files older than the retention policy are archived or purged automatically, with admin visibility into the archive. - AC1: Files older than retention archived or purged per policy. - TC1 [auto]: Fixture old files; run job; assert state transition.

H3 — Automated release notes + scheduled deployments - Use case: Today, release notes are written by hand and deployments are run manually. After this change, release notes are generated automatically from commits between two release tags and deployments can be scheduled in CI with status reporting. - AC1: CI generates release notes markdown from commits between two tags. - AC2: Scheduled deployment runs APEX export/import + install scripts and reports status. - TC1 [auto]: Run generator on a fixture commit range; assert structure (Features/Fixes/Breaking). - TC2 [auto]: Dry-run deploy against staging; assert idempotent.

H4 — Bug-reporter v2 - Use case: Today, a submitted bug disappears into a webhook with no feedback to the reporter. After this change, the submitter gets an email confirmation with a ticket ID, sees status updates as the bug is triaged, and is notified with the root-cause analysis when the ticket closes. - AC1: Submitter receives confirmation email with ticket ID. - AC2: Status change (Open→Triaged→InProgress→Closed) emails submitter. - AC3: Closed tickets carry RCA text. - AC4: Admin page lists tickets with filter and bulk-status edit. - TC1 [auto]: Submit via JS widget; assert DB row + email captured by mock SMTP. - TC2 [auto]: Admin moves to Closed with RCA; assert email + status persisted. - TC3 [auto]: Permission test — non-admin cannot edit RCA.

H5 — Automated test infrastructure - Use case: Today, every change is validated manually, which slows releases and lets regressions slip through. After this change, a CI pipeline runs database, JavaScript, and end-to-end browser tests on every pull request, blocking merges that break a known-good behaviour. - AC1: CI pipeline runs utPLSQL + Jest + Playwright on every PR; results block merge on failure. - AC2: Test DB fixture (seed + APEX import) provisioned in <5 minutes. - AC3: Coverage report published for PL/SQL and JS; baseline thresholds set. - AC4: Documentation in repo explaining how to write a new test for each layer. - AC5: At least 30 PL/SQL tests, 20 Jest tests, and 10 Playwright flows seeded as the starting suite (covering critical paths: auth, template load, strategy eval, report render, ISR, user CRUD). - TC1 [auto]: Open a PR that intentionally breaks GENERIC_MATH_EVAL; assert CI fails with clear utPLSQL message. - TC2 [auto]: Open a PR that breaks ReportDashboardJS column dedup (UR-223 regression); assert Jest fails. - TC3 [auto]: Playwright flow — login → load fixture data → assert report renders; runs green in <10 min. - TC4 [manual]: New engineer follows docs and adds a test on their first day.

H6 — DR-compliant ADB with Autonomous Data Guard auto-failover - Use case: Today, an outage of the primary database region means Parallax is unavailable until the database is recovered. After this change, a standby database in a second region takes over automatically within the agreed recovery-time objective, and the application reconnects without manual reconfiguration. - AC1: A standby ADB exists in a secondary OCI region (or alternate AD) and is continuously synchronised with the primary. - AC2: Documented RTO and RPO targets agreed with client (target: RTO ≤ 5 min, RPO ≤ 1 min) and verified by test. - AC3: APEX application, workspace, schemas, wallets, and ORDS endpoints all available on the standby after failover. - AC4: A single connection endpoint (auto-failover alias / TNS) is used by the application — no app-side reconfiguration needed during failover. - AC5: Documented failover and switchover runbooks committed to the repo. - AC6: A live switchover drill is performed against staging; results captured. - TC1 [manual]: Trigger a controlled switchover; assert app remains reachable within RTO and no committed transactions lost (RPO). - TC2 [manual]: Trigger failover (standby takes over); run full smoke suite (H5 Playwright flows) against the new primary; all pass. - TC3 [auto]: Monitoring probe — assert standby lag is below RPO threshold continuously; alert if breached. - TC4 [manual]: Validate APEX workspace login, file storage, and scheduled jobs (DBMS_SCHEDULER) resume on the new primary.