semillero-special-hotel/docs/GAP_ANALYSIS.md

Enterprise Gap Analysis & Architectural Foundations

Variable Remuneration, Compensation, and Commissions System - Hoteles Estelar

---

This document performs a gap analysis comparing the client's functional user stories (what was asked for in the PDF) against the security, compliance, data integrity, and architectural foundations required for an enterprise-grade financial settlement application (what is needed).

It details the mitigation strategies we are building into the project's foundation.

---

1. Functional Ask vs. Enterprise Need Matrix

Client Ask (From PDF User Stories)	Enterprise Operational Need (The Gap)	Architectural Foundation Mitigation
US-COM-001/002: Create and edit compensation plans and calculation rules.	Historical Versioning & Audit Integrity: Editing a plan mid-month must not corrupt calculations of previous closed months or skew historical audits.	Temporal Plan Versioning: Plans are version-controlled. Modifying an active plan creates a new version. Closed settlements remain permanently linked to the specific plan version ID used for calculation.
US-COM-004/005: Import Excel sheets and automatically integrate with CRM/ERP/PMS.	Integrency, Reconciliation & Idempotency: Duplicate uploads or double-triggered calculation jobs must not result in double payouts.	Reconciliation & Idempotency Keys: Batch sales imports require unique transaction IDs. Calculations enforce unique constraints on (user_id, period, plan_id, status) to guarantee execution idempotency.
US-COM-005: Handle external API integration failures.	Resilience & Rate Limiting: Remote ERPs/PMSs frequently experience downtime or rate limit requests.	n8n Async Queueing & Dead Letter Queues (DLQ): Integrations are decoupled via n8n queues. Failed API pushes retry exponentially and fall back to a DLQ for administrator manual review.
US-COM-006/007: Auto-calculate settlements and simulate before closing.	Retroactive Adjustments & Clawbacks: Sales statuses in PMS systems are dynamic (e.g., booking modifications or cancellations occur after a commission has been paid).	Retroactive Adjustment Engine: Calculations perform delta checks on past closed periods. Any change in historical sales automatically generates a "Clawback" or "Adjustment Credit" applied to the next period.
US-COM-011: Log audit trails (user, action, previous/new value).	Immutability & Data Privacy: Audit logs must be tamper-proof and must not expose sensitive personal compensation data in plain text.	Immutable Audit Log Table: Database enforces INSERT-ONLY permissions on AuditLog table. Personal details and password hashes are redacted/masked before serializing JSON snapshots.
US-COM-014: Roles and permissions restricted by hotel, region, and area.	Strict Row-Level Security (RLS) & Multi-tenant Isolation: Under no circumstances should a manager of Hotel A view sales or commission totals of Hotel B.	PostgreSQL Row-Level Security: DB queries dynamically apply filters on hotel_id and region_id based on the authenticated session's RBAC tokens.

---

2. Deep Dive: Architectural Foundations

2.1. Plan Versioning & Temporal Database Design

To maintain historical reproducibility:

• When a plan is created, its version is initialized to 1 and its status is DRAFT.
• When activated, status becomes ACTIVE with a defined validity start date.
• If a change is made to an ACTIVE plan:
  1. The active plan is marked as INACTIVE (with its validity end date set to the change timestamp).
  2. A new record is created with version incremented (e.g., version = 2), status set to ACTIVE, and validity start set to the current timestamp.
• The SETTLEMENTS table references plan_id which points to the specific version record, ensuring future recalculations or audits fetch the exact rate parameters that were active when the settlement occurred.

2.2. Retroactive Adjustments (PMS Clawbacks)

Hotels deal with dynamic reservations (no-shows, cancellations, late refunds). The settlement engine handles adjustments as follows:

• When calculating commissions for period = YYYY-MM, the engine executes a lookup on sales of period = (YYYY-MM) - 1 and (YYYY-MM) - 2.
• It compares the calculated sales amounts in the database against current live PMS records.
• If a discrepancy is found (e.g., a booking worth $1,000 in (YYYY-MM) - 1 was refunded in the PMS):
  1. A RECONCILIATION_ENTRY is generated with type CLAWBACK for the discrepancy amount.
  2. The delta calculation is stored in the database.
  3. The current month's commission payout is modified: \text{Total Payout} = \text{Current Commission} - \text{Clawback Amount}.

2.3. Idempotency & Reconciliation Control

To prevent duplicate execution risks:

• The Next.js API requires an idempotency-key header for any write request originating from n8n or bulk Excel imports.
• If a request with the same key is received within a 24-hour window, the API returns the cached response instead of executing the database transaction again.
• ERP Reconciliation: The system runs a daily reconciliation check:

  \Delta \text{Revenue} = \text{Total PMS Confirmed Sales} - \text{Total ERP Settled Revenue}

  Any delta exceeding a configurable threshold (e.g., 1%) generates a high-priority warning in the Analyst dashboard.

2.4. Dual-Environment Log Redaction & Compliance

To meet financial audit requirements (e.g., SOC 2 and GDPR compliance):

• Logs Redaction: Node.js logs and n8n execution canvases must never output personal identifiers, base salaries, or raw password hashes. All logs go through a redaction stream.
• Separation of Environments: The dual-container architecture ensures that development testing (using fake collaborator data and test APIs) runs strictly on app-dev connected to TEST_DATABASE_URL, preventing real customer metrics from leaking into staging/dev outputs.