Skilled ERP

01 What it is

A JSON API in Flask, no HTML, serving the Skilled ERP SPA (React + Vite, hosted on Vercel). This repo exposes /api/* and the Socket.IO channel; the frontend lives in its own repo. It covers payroll, employees, projects, worked hours, loans, materials and tools inventory, with real-time notifications and audit log.

• Employees: create, edit and deactivate; employment, personal, medical and financial fields with a per-role whitelist. Internal "chatter" notes on the record.
• Payroll: weekly pre-payroll, deductions, extra deposits, loans with repayments and the per-period Inbursa adjustment.
• Hours: weekly reports, daily records, absences, vacation balance and RFID or QR clock-in.
• M:N projects: a worker can belong to several projects; record and credentials derive only from active projects.
• Inventory: products, warehouses and QR-labelled shelves; movements with an anti-concurrency lock; requests with a PENDING → APPROVED / REJECTED / DELIVERED flow and partial delivery; physical counts with automatic adjustment; Avery labels and purchase orders.
• Tools: catalogue and physical units traceable by serial or QR; assignments to workers, maintenance, incidents and authorized write-off.
• Systems panel: infrastructure status, traffic and percentiles, active sessions, account lockouts, security events and orphan files on R2.
• Reports: PDF (receipts, certificates, requests, counts, POs) and Excel (per-project totals, history), sanitized against formula injection. Bulk import of employees and products from .xlsx.

02 Architecture

Everything comes in through Cloudflare, down the tunnel, through Nginx and ends at Gunicorn. The origin server publishes no inbound ports: the tunnel is an outbound connection and the firewall drops any direct inbound attempt. Locally, the same path comes up with docker compose up.

The 4 gevent workers are separate processes, so everything that must be shared (rate limiting, lockout, 2FA anti-replay, Socket.IO events) lives in Redis. Files are not stored as they come: an external image is checked against SSRF, its magic bytes are verified, it is rewritten with Pillow to WebP and uploaded to R2 with the SHA-256 of its content as the key. In inventory, two movements on the same stock cannot overwrite each other because the row is locked with SELECT … FOR UPDATE before writing.

03 Getting started

Requirements: Python 3.12, PostgreSQL 14+, Redis and, for the recommended path, Docker with Docker Compose. With Docker the API comes up with its own PostgreSQL, Redis and ClamAV using the root .env. It is the only way on Windows to test the real WebSocket path with gevent and the antivirus, which do not start in a native venv.

> cp .env.example .env               # rellenar SECRET_KEY, DATABASE_URL, REDIS_URL…
> docker compose up --build          # api en http://localhost:5000
> docker compose logs -f api         # logs de la API
> docker compose exec api pytest     # tests dentro del contenedor

> python -m venv venv
> source venv/bin/activate           # Linux / Mac; en Windows: venv\Scripts\activate
> pip install -r requirements.txt
> flask db upgrade                   # crea la BD en Postgres antes
> python run.py                      # http://localhost:5000

04 Code layout

> run.py                  # entry point; monkey-patch de gevent si SOCKETIO_ASYNC_MODE=gevent
> Dockerfile              # imagen de la API (builder + runtime)
> docker-compose.yml      # stack de desarrollo: api, db, redis, clamav
> docker-compose.prod.yml # stack del VPS
> nginx.config            # config de Nginx
> gunicorn.serviceee      # unit de systemd; se instala como nominas.service
> app/__init__.py         # create_app(): CORS, Talisman, Limiter, blueprints, Socket.IO
> app/extensions.py       # db, limiter, mail; IP real tras Cloudflare; EncryptedString
> app/realtime.py         # Socket.IO: handshake, salas, hooks ORM, emit_to_*
> app/observabilidad.py   # after_request: contadores, histograma y detalle en Redis
> app/models/             # SQLAlchemy, 12 módulos por dominio
> app/routes/             # 18 blueprints, cada uno como sub-paquete con _core.py
> app/utils/              # seguridad, archivos, R2, antivirus, imágenes, horas, nómina
> templates/              # Jinja solo para PDFs (xhtml2pdf)
> migrations/             # Alembic
> tests/                  # 36 módulos de pytest

create_app() boots in a fixed order: config → wait for Redis → extensions → CORS on /api/* only → Talisman and CSP → register the 18 blueprints (CSRF-exempt, JWT-protected) → global error handlers → security headers and Cache-Control: no-store → auxiliary tables → ProxyFix → Socket.IO last, so it sees the corrected headers.

• Each blueprint is a sub-package: _core.py with the blueprint, decorators and serializers, plus one module per topic.
• Shared _api_helpers.py: current_user(), is_admin(), require_admin(), require_roles() and the api_transactional decorator (automatic rollback and log on exception).
• Every relevant mutation calls log_action(...): it writes to audit_log with user, IP and action, and the insert triggers the bitacora:new push.
• Standard pagination page / per_page with a {items, total, pages} response; workers, loans and projects also accept sort / dir with a column whitelist.
• Excel with _sanitize_rows (anti =formula) and styles shared across 5 packages. PDF: Jinja → xhtml2pdf.
• models/ is plain SQLAlchemy, no business logic; re-exported flat from app/models/__init__.py.

05 API reference

216 routes grouped by blueprint. All require a JWT (Authorization: Bearer …) except login, refresh and /health. Standard error responses: 401 (missing or expired JWT), 403 (role), 404, 422 (validation), 429 (rate limit) and 419 (CSRF on cookie flows).

06 Roles

Authorization goes through decorators (require_admin, require_roles, _require_inventario…) plus a per-role whitelist of editable worker fields: the coordinator cannot touch salaries or tax PII, and anything forbidden is ignored and returned in warnings. The coordinator has ownership: they only see and edit what belongs to their own projects.

07 Real time

The connect handler validates the JWT and each connection joins the user:{id} and role:{rol} rooms; join:reporte adds reporte:{id} for the capture kiosk. The worker that makes a change publishes it to Redis and the rest forward the event to their sockets. The event carries only {id, action}: the browser fetches the data again over REST, where the permission is validated again. ORM hooks emit only if the commit succeeded.

In-app notifications: REPORTE_CERRADO when an hours report closes and PRENOMINA_CERRADA when a pre-payroll closes; the push goes over notif:new and polling stays as fallback. Read ones are deleted after 30 days on each GET /resumen, no external cron. The SPA session renews itself 60 s before expiry and, if a 401 still arrives, several in-flight requests share a single refresh.

08 Security

The system went through a full offensive audit (pentest, code review and infrastructure): score 5.8 → 8.4/10, with 4 critical and 7 high findings closed in code.

09 Production

Chain: Cloudflare Tunnel → Nginx (127.0.0.1:80) → Gunicorn (127.0.0.1:8000) → Flask. The installed unit is called nominas.service; the file in the repo is gunicorn.serviceee.

> sudo cp gunicorn.serviceee /etc/systemd/system/nominas.service
> sudo systemctl daemon-reload && sudo systemctl enable --now nominas
> sudo cp nginx.config /etc/nginx/sites-available/skilled
> sudo ln -s /etc/nginx/sites-available/skilled /etc/nginx/sites-enabled/skilled
> sudo nginx -t && sudo systemctl reload nginx

• Gunicorn: 4 GeventWebSocketWorker workers × 1000 connections. gthread does not implement the WebSocket upgrade and eventlet is incompatible with psycopg3.
• --max-requests 1000 with jitter recycles workers to avoid pandas, openpyxl and xhtml2pdf leaks.
• Nginx: per-IP rate limiting as a second layer, Cloudflare real IP, anti-Slowloris and anti request smuggling, a dedicated /socket.io/ block with proxy_read_timeout 3600s, method whitelist and blocking of commonly scanned paths (.env, wp-login.php…).
• Security headers and CORS are set by Flask; they are not duplicated in Nginx.
• Frontend on Vercel with VITE_API_URL=https://api.<domain>/api; the backend carries CORS_ORIGINS with the Vercel domain, RT_COOKIE_SAMESITE=None, FLASK_ENV=production and USE_X_ACCEL_REDIRECT=true.

> curl https://api.<dominio>/health                      # {"status":"ok"}
> sudo journalctl -u nominas -n 20 --no-pager           # IPs reales, no 127.0.0.1
> sudo tail -n 20 /var/log/nginx/skilled_api.access.log  # upstream=127.0.0.1:8000 request_time=…

10 Database

PostgreSQL through SQLAlchemy and Alembic (flask db …). Models are split by domain in app/models/. Conventions: auto-increment id PK except on M:N mappings, UTC timestamps, amounts as Numeric(10,2) and states as uppercase strings.

> flask db migrate -m "descripción"    # nueva migración
> flask db upgrade                     # aplicar
> flask db current                     # revisión actual
> pytest tests/                        # correr tras cualquier cambio