certimine/sql/schema.sql
remaster ff0fd6a89d API Peñasquito: ticket persistente, company y mejor flujo de búsqueda
- Renombrado «DC3» a «API Peñasquito» (URL base del documento) en UI/docs;
  rutas y claves de config conservadas por compatibilidad.
- Persistencia de ticket_id en nueva tabla `penasquito_tickets`:
  POST /search/ guarda el ticket y los siguientes requests retoman
  GET /result/<ticket_id>/ sin reencolar.
- Polling acotado por request HTTP (~6 s) con `PenasquitoPendingException`
  y caja «reintentar» en lookup; ticket queda válido 1 h.
- `users_cache.company` nuevo (con migración idempotente); persistResult
  guarda empresa + competencias en transacción y aplica source distinto.
- Mensajes HTTP específicos (400/401/403/404/408/429/5xx) y `/` final
  forzado en rutas (DRF), FOLLOWLOCATION habilitado.
- Vista de perfil muestra «Empresa» y aviso cuando no hay competencias.
- Auth::login evita doble fetch cuando ya tenemos el `result` del API.
- i18n ES/EN actualizados; lookup_pending_*; limpieza de archivos lang.

Co-authored-by: Cursor <cursoragent@cursor.com>
2026-05-13 19:43:37 -06:00

145 lines
5.3 KiB
SQL

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
CREATE DATABASE IF NOT EXISTS certimine CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE certimine;
DROP TABLE IF EXISTS audit_log;
DROP TABLE IF EXISTS sync_event;
DROP TABLE IF EXISTS course_requirement;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS certificates;
DROP TABLE IF EXISTS contractor_authorizations;
DROP TABLE IF EXISTS frm_nom_map;
DROP TABLE IF EXISTS public_documents;
DROP TABLE IF EXISTS penasquito_tickets;
DROP TABLE IF EXISTS users_cache;
DROP TABLE IF EXISTS nom_competency;
DROP TABLE IF EXISTS frm_standard;
CREATE TABLE frm_standard (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(64) NOT NULL UNIQUE,
name_en VARCHAR(255) NOT NULL,
name_es VARCHAR(255) NOT NULL,
sort_order INT NOT NULL DEFAULT 0,
doc_url VARCHAR(512) NULL,
description_en TEXT NULL,
description_es TEXT NULL
) ENGINE=InnoDB;
CREATE TABLE nom_competency (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(32) NOT NULL UNIQUE,
name_en VARCHAR(255) NOT NULL,
name_es VARCHAR(255) NOT NULL,
sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
CREATE TABLE frm_nom_map (
frm_id INT UNSIGNED NOT NULL,
nom_id INT UNSIGNED NOT NULL,
PRIMARY KEY (frm_id, nom_id),
CONSTRAINT fk_frm_nom_frm FOREIGN KEY (frm_id) REFERENCES frm_standard(id) ON DELETE CASCADE,
CONSTRAINT fk_frm_nom_nom FOREIGN KEY (nom_id) REFERENCES nom_competency(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE public_documents (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title_en VARCHAR(255) NOT NULL,
title_es VARCHAR(255) NOT NULL,
url VARCHAR(512) NOT NULL,
frm_id INT UNSIGNED NULL,
sort_order INT NOT NULL DEFAULT 0,
CONSTRAINT fk_pubdoc_frm FOREIGN KEY (frm_id) REFERENCES frm_standard(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE users_cache (
mobiliseme_id VARCHAR(64) NOT NULL PRIMARY KEY,
employee_number VARCHAR(64) NULL,
display_name VARCHAR(255) NOT NULL,
company VARCHAR(255) NULL,
person_type ENUM('employee','contractor') NOT NULL DEFAULT 'employee',
green_license_flag TINYINT(1) NOT NULL DEFAULT 0,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY idx_employee_number (employee_number)
) ENGINE=InnoDB;
-- Tickets pendientes del API Peñasquito (POST /search/ → GET /result/<ticket_id>/).
-- Permite reanudar el polling entre requests HTTP sin tener que reencolar.
CREATE TABLE penasquito_tickets (
passport VARCHAR(64) NOT NULL PRIMARY KEY,
ticket_id VARCHAR(128) NOT NULL,
status VARCHAR(32) NOT NULL DEFAULT 'pending',
posicion INT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_polled_at DATETIME NULL,
last_error VARCHAR(255) NULL,
KEY idx_pt_status (status),
KEY idx_pt_ticket (ticket_id)
) ENGINE=InnoDB;
CREATE TABLE certificates (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
mobiliseme_id VARCHAR(64) NOT NULL,
nom_id INT UNSIGNED NULL,
frm_id INT UNSIGNED NULL,
title VARCHAR(255) NOT NULL,
issued_at DATE NULL,
expires_at DATE NULL,
status ENUM('valid','expiring','expired','pending') NOT NULL DEFAULT 'pending',
external_ref VARCHAR(128) NULL,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
KEY idx_person (mobiliseme_id),
UNIQUE KEY uq_cert_ext (mobiliseme_id, external_ref),
CONSTRAINT fk_cert_nom FOREIGN KEY (nom_id) REFERENCES nom_competency(id) ON DELETE SET NULL,
CONSTRAINT fk_cert_frm FOREIGN KEY (frm_id) REFERENCES frm_standard(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE contractor_authorizations (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
mobiliseme_id VARCHAR(64) NOT NULL,
frm_id INT UNSIGNED NOT NULL,
valid_until DATE NULL,
source VARCHAR(32) NOT NULL DEFAULT 'sync',
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uq_contractor_frm (mobiliseme_id, frm_id),
CONSTRAINT fk_auth_frm FOREIGN KEY (frm_id) REFERENCES frm_standard(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE courses (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title_en VARCHAR(255) NOT NULL,
title_es VARCHAR(255) NOT NULL,
url VARCHAR(512) NOT NULL,
sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB;
CREATE TABLE course_requirement (
course_id INT UNSIGNED NOT NULL,
nom_id INT UNSIGNED NOT NULL,
PRIMARY KEY (course_id, nom_id),
CONSTRAINT fk_cr_course FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
CONSTRAINT fk_cr_nom FOREIGN KEY (nom_id) REFERENCES nom_competency(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE sync_event (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
external_id VARCHAR(128) NOT NULL,
event_type VARCHAR(64) NOT NULL,
payload_hash CHAR(64) NOT NULL,
received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
processed_at DATETIME NULL,
UNIQUE KEY uq_ext (external_id, event_type)
) ENGINE=InnoDB;
CREATE TABLE audit_log (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
action VARCHAR(64) NOT NULL,
subject_type VARCHAR(32) NOT NULL,
subject_id VARCHAR(128) NOT NULL,
ip VARCHAR(45) NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
SET FOREIGN_KEY_CHECKS = 1;