171 lines
9 KiB
Markdown
171 lines
9 KiB
Markdown
# Phase 4 Implementation & Integration Design Document
|
|
|
|
**Variable Remuneration, Compensation, and Commissions System - Hoteles Estelar**
|
|
|
|
---
|
|
|
|
This document outlines the detailed design decisions, schema mappings, API endpoints, UI layouts, and security/idempotency validations planned for **Phase 4: Data Import & Integrations**.
|
|
|
|
## 1. Architectural Strategy & Logic Flows
|
|
|
|
### 1.1. Excel Parser & Data Validation Flow
|
|
To prevent manual data-entry errors, the system allows authorized users (Administrators, Analysts, and Commercial Leaders) to upload Excel/CSV sheets with sales results.
|
|
|
|
#### Excel Spreadsheet Format Specifications
|
|
Two pre-populated spreadsheets are available under the public directory:
|
|
1. **Template spreadsheet**: [import_sales_template.xlsx](../public/templates/import_sales_template.xlsx) — Clean column layout with a single dummy row reference. **This template is made directly downloadable through the program's UI.**
|
|
2. **Test spreadsheet**: [import_sales_test.xlsx](../public/templates/import_sales_test.xlsx) — Contains mixed valid and invalid rows to verify frontend/backend validation error rendering.
|
|
|
|
The spreadsheet contains the following mandatory headers:
|
|
- `Colaborador`: String representing the unique collaborator username (e.g. `colaborador_mde`).
|
|
- `Periodo`: String matching the target period in `YYYY-MM` format (e.g. `2026-06`).
|
|
- `Hotel`: String representing the unique hotel code (e.g. `EST-MDE`).
|
|
- `Monto`: Decimal/Numeric positive value of total sales.
|
|
- `Cantidad`: Integer positive value of total sales count.
|
|
- `Id_Transaccion`: String representing the external transaction tracking ID (e.g. `TX-EST-MDE-001`).
|
|
|
|
The server-side parsing will:
|
|
1. Parse the uploaded `.xlsx` or `.csv` file buffer using the `xlsx` library.
|
|
2. Resolve row-level records and perform **Atomic Validation** on all rows before write transactions.
|
|
3. If any row contains errors, abort the entire operation and return a structured JSON report identifying the exact rows, columns, values, error codes, and translation metadata.
|
|
|
|
### 1.2. Multilingual "Code + Metadata" Response Pattern
|
|
To support dynamic localized UI translations, the API does not return pre-translated text strings. Instead, it enforces the **Code + Metadata** pattern:
|
|
- **Success Responses**: Return a strict static success code alongside numeric/string variables in a metadata payload.
|
|
- **Error Responses**: Return strict error codes (`USER_NOT_FOUND`, `INVALID_PERIOD_FORMAT`, etc.) with their respective parameters. The client-side application translates these keys locally using translation tables.
|
|
|
|
### 1.3. Two-Tier Idempotency Control (API & DB Layer)
|
|
To guarantee that duplicate uploads do not lead to duplicate commission calculations:
|
|
1. **API Header Check**: The `POST /api/sales/import` endpoint requires a client-generated `Idempotency-Key` header.
|
|
2. **Key Check**:
|
|
- The backend checks if any existing sales results contain an idempotency key matching the pattern `${idempotency_key}-*`.
|
|
- **True**: The import is recognized as a duplicate. The server returns the cached success details of the previous import, preventing re-execution.
|
|
- **False**: Processing continues.
|
|
3. **Row-level uniqueness**: Each inserted row is saved with a unique database constraint `idempotencyKey = ${idempotency_key}-${row_index}`, guaranteeing database-level data integrity under concurrent scenarios.
|
|
|
|
```mermaid
|
|
graph TD
|
|
A[Upload request with Idempotency-Key] --> B{Key already processed?}
|
|
B -->|Yes| C[Return cached/existing import summary]
|
|
B -->|No| D[Parse Excel/CSV Buffer]
|
|
D --> E{Any validation error?}
|
|
E -->|Yes| F[Abort and return detailed error list]
|
|
E -->|No| G{N8N_WEBHOOK_URL set?}
|
|
G -->|Yes| H[Forward rows to n8n Webhook & return 202 Accepted]
|
|
G -->|No / Test| I[Prisma Transaction: Save rows with idempotency key + Audit Log]
|
|
I --> J[Return 201 Created]
|
|
```
|
|
|
|
### 1.4. n8n Integration & Callback Validation
|
|
For automatic integrations (US-COM-005), the workflow follows a **Thick Client (Next.js), Thin Coordinator (n8n)** architecture:
|
|
1. **Zero DB Connections & Zero Code Blocks**: n8n does not connect directly to the database or run heavy processing code. Instead, n8n invokes Next.js validation and logic endpoints, using n8n strictly for pipeline orchestration and third-party notifications.
|
|
2. **Encapsulated LLM/AI & Model Fallback**: All LLM-based checks (semantic anomaly checks) are executed directly inside n8n using native nodes. The workflow uses a **Primary Chat Model** node (DeepSeek) and a **Fallback Chat Model** node (Google Gemini) hooked to a LangChain LLM Chain node with failover enabled.
|
|
3. **Dynamic Workspace Credential Resolution**: During the bootstrap and E2E test runs, the system queries `GET /api/v1/credentials` from n8n, searches for active credentials matching the DeepSeek and Gemini types (`deepSeekApi` and `googlePalmApi`), and dynamically injects their IDs and names into the workflow JSON before deployment.
|
|
4. **Webhook Security**: Next.js exposes a public-facing but secured callback endpoint `/api/sales/batch-save`.
|
|
5. **Signature Verification**: Webhooks from n8n must include the `x-n8n-signature` header. The server verifies this token against `N8N_WEBHOOK_SECRET` before executing database updates.
|
|
6. **Stalwart SMTP Server**: All outbound notifications (such as system error reports or settlement anomalies alerts) are sent by n8n using native SMTP/Email nodes pointing to the Stalwart mail server.
|
|
7. **Async Status Polling**: The frontend displays a progress UI. If routed through n8n, it polls `GET /api/sales/import/status/[key]` to check database status and update progress.
|
|
|
|
---
|
|
|
|
## 2. API Specifications
|
|
|
|
### 2.1. `POST /api/sales/import` (Upload Sales Sheet)
|
|
- **Role Restriction**: `admin` | `analyst` | `commercial_leader`
|
|
- **Headers**:
|
|
- `Idempotency-Key`: Required string
|
|
- **Request Body**: `multipart/form-data` with `file` field containing the spreadsheet.
|
|
- **Response**:
|
|
- `201 Created` (Direct import mode):
|
|
```json
|
|
{
|
|
"success": true,
|
|
"code": "IMPORT_SUCCESSFUL",
|
|
"metadata": {
|
|
"count": 45,
|
|
"totalAmount": 1563000.00
|
|
}
|
|
}
|
|
```
|
|
- `202 Accepted` (n8n mode):
|
|
```json
|
|
{
|
|
"success": true,
|
|
"code": "IMPORT_ACCEPTED",
|
|
"metadata": {
|
|
"idempotencyKey": "unique-client-key-123",
|
|
"status": "PROCESSING"
|
|
}
|
|
}
|
|
```
|
|
- `400 Bad Request` (Validation errors):
|
|
```json
|
|
{
|
|
"success": false,
|
|
"error": {
|
|
"code": "IMPORT_VALIDATION_FAILED",
|
|
"details": [
|
|
{
|
|
"row": 3,
|
|
"column": "Colaborador",
|
|
"value": "colaborador_inexistente",
|
|
"code": "USER_NOT_FOUND",
|
|
"metadata": { "username": "colaborador_inexistente" }
|
|
},
|
|
{
|
|
"row": 4,
|
|
"column": "Periodo",
|
|
"value": "2026/06",
|
|
"code": "INVALID_PERIOD_FORMAT",
|
|
"metadata": { "expected": "YYYY-MM" }
|
|
}
|
|
]
|
|
}
|
|
}
|
|
```
|
|
|
|
### 2.2. `POST /api/sales/batch-save` (n8n Webhook Callback)
|
|
- **Security Check**: Header `x-n8n-signature === process.env.N8N_WEBHOOK_SECRET`
|
|
- **Request Body**:
|
|
```json
|
|
{
|
|
"idempotencyKey": "unique-client-key-123",
|
|
"uploaderId": 1,
|
|
"sales": [
|
|
{ "username": "colaborador_mde", "hotelCode": "EST-MDE", "period": "2026-06", "amount": 15000.00, "salesCount": 3 }
|
|
]
|
|
}
|
|
```
|
|
- **Response**: `201 Created` or `400 Bad Request`.
|
|
|
|
### 2.3. `GET /api/sales/import/status/[key]` (Poll Status)
|
|
- **Response**:
|
|
```json
|
|
{
|
|
"idempotencyKey": "unique-client-key-123",
|
|
"status": "SUCCESS" // PROCESSING | SUCCESS | FAILED
|
|
}
|
|
```
|
|
|
|
---
|
|
|
|
## 3. UI Design Specifications
|
|
|
|
We will create a premium drag-and-drop loading interface at `/sales/import`:
|
|
1. **Interactive Drop Zone**: Elegant border animation on dragover. Shows file metadata upon dropping.
|
|
2. **Template Download Link**: Sleek, visible button to download the pre-formatted Excel template directly.
|
|
3. **Real-time Progress Indicator**: Fluid CSS progress bar tracking parse/upload state.
|
|
4. **Inconsistency Panel**: If the API returns validation errors, displays them in a sleek, scrollable log table with warning icons.
|
|
5. **Permissions Enforcement**: Renders standard unauthorized page if role is not permitted.
|
|
|
|
---
|
|
|
|
## 4. Headless Puppeteer Verification Plan
|
|
|
|
We will create a new test script `prisma/test-phase4-ui.js` verifying:
|
|
1. **Role Enforcement**: `colaborador_mde` is blocked from accessing the page and endpoint.
|
|
2. **Missing Header Validation**: Upload fails if `Idempotency-Key` is missing.
|
|
3. **File Validation**: Uploading an Excel file with negative values or fake users displays validation errors.
|
|
4. **Successful Direct Import**: Uploading a valid Excel file imports rows, saves them, and shows success.
|
|
5. **Idempotency Prevention**: Re-uploading with the same key returns the success message without modifying/inserting additional database records.
|
|
6. **n8n Webhook Callback Integrity**: POSTing to `/api/sales/batch-save` with an invalid signature is blocked (401), while a valid signature is processed.
|