Rodax_Software/metis-desk
2
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
metis-desk/docs/diagrama-entidad-relacion.md
David ba562ad06f Refactor documentation for clarity and accuracy 
- Updated examples in "estructura-web-por-entidades-y-operaciones.md" to reflect correct entity names and directory structures.
- Clarified modal usage and component interactions in "impresoras-del-perfil.md".
- Adjusted component descriptions in "logotipos-de-companias.md" for consistency.
- Revised persistence model details in "normalizacion-de-la-persistencia.md" to remove outdated references and clarify relationships.
- Enhanced dialog patterns in "patron-de-dialogos.md" with specific guidelines for field labels and validation.
- Specified component reuse in "patron-de-listados-de-entidades.md" to include @repo/rdx-ui components.
- Updated implementation status in "personas-y-departamentos.md" to reflect current features and pending tasks.
- Clarified project scope and initial setup instructions in "plan-de-desarrollo.md" and "puesta-en-marcha.md".
- Improved descriptions of workstation management in "puestos-de-trabajo.md" for better understanding of active/inactive states.
- Enhanced resource management details in "recursos-campos-y-plantillas.md" to clarify relationships and pending features.
- Revised best practices review in "revision-de-buenas-practicas.md" to reflect current project status and components.
- Updated teleworking documentation in "teletrabajo.md" to include references to remote technical assistance.
- Clarified user account management in "usuarios-y-acceso.md" to emphasize the importance of account deactivation.
2026-10-15 17:48:46 +02:00

1296 lines
46 KiB
Markdown
Raw Permalink Blame History

# Diagrama de entidad relación de la base de datos
Esquema derivado de la captura de MariaDB del **14 de octubre de 2026** mediante
`information_schema`, depurado el **15 de octubre de 2026** según la migración activa
`removeArchivedSupportTables`. Se excluyen las 19 tablas retiradas; sus fuentes y
contratos se conservan en [el archivo](funcionalidades-archivadas.md).
No se ha realizado una nueva consulta a MariaDB ni ejecutado migraciones para esta revisión.
Los demás metadatos conservan los tipos y restricciones de la captura original.
**111 tablas y 2 vistas SQL · 860 columnas · 1008 restricciones FK · 309 relaciones distintas** (agrupando restricciones con idénticas columnas, destino y reglas).
- [Diagrama completo, con todas las columnas (Mermaid)](diagrama-entidad-relacion-completo.mmd).
- [Diccionario completo de tablas, columnas y claves foráneas](diccionario-del-esquema-actual.md).
## Cómo leer los diagramas
`||`: exactamente uno; `|o`: cero o uno; `o{`: cero o muchos; `o|`: cero o uno en el extremo hijo. La nulabilidad de la FK determina si el padre es opcional. Las restricciones PRIMARY/UNIQUE determinan si el hijo puede repetirse. Las líneas discontinuas representan asociaciones por FK; no se deduce composición de dominio a partir de ellas. La etiqueta indica las columnas del hijo.
Los diagramas por área muestran las PK/FK y los padres externos necesarios para entender sus relaciones. Cada tabla pertenece a un área; los padres pueden repetirse como referencia. El archivo completo incluye todas las columnas, también las tablas sin FK. Los nombres son los reales de MariaDB: por ejemplo, las personas se almacenan en `company_contacts`.
## Vista principal
Vista reducida: omite tablas auxiliares e históricas; todas ellas aparecen en las áreas siguientes y en el diagrama completo.
```mermaid
erDiagram
direction LR
asset_brands
asset_definitions
assets
companies
company_contacts
company_departments
company_sites
person_department_assignments
resource_definitions
resources
users
work_profiles
workstation_assets
workstation_members
workstations
companies |o..o{ asset_definitions : "companyId"
asset_brands |o..o{ assets : "brandId"
asset_definitions ||..o{ assets : "templateId"
companies ||..o{ assets : "companyId"
company_sites |o..o{ assets : "siteId"
companies ||..o{ company_contacts : "companyId"
companies ||..o{ company_departments : "companyId"
companies ||..o{ company_sites : "companyId"
companies ||..o{ person_department_assignments : "companyId"
company_contacts ||..o{ person_department_assignments : "personId, companyId"
company_departments ||..o{ person_department_assignments : "departmentId, companyId"
companies |o..o{ resource_definitions : "companyId"
companies ||..o{ resources : "companyId"
resource_definitions |o..o{ resources : "addressFieldId"
resource_definitions |o..o{ resources : "contextFieldId"
resource_definitions |o..o{ resources : "referenceFieldId"
resource_definitions ||..o{ resources : "templateId"
assets ||..o{ work_profiles : "assetId"
companies ||..o{ work_profiles : "companyId"
company_contacts ||..o{ work_profiles : "personId"
assets ||..o{ workstation_assets : "assetId"
assets ||..o{ workstation_assets : "companyId, assetId"
companies ||..o{ workstation_assets : "companyId"
workstations ||..o{ workstation_assets : "companyId, workstationId"
workstations ||..o{ workstation_assets : "workstationId"
assets ||..o{ workstation_members : "assetId"
workstations ||..o{ workstation_members : "workstationId"
assets |o..o{ workstations : "computerAssetId"
companies ||..o{ workstations : "companyId"
company_contacts |o..o{ workstations : "personId"
company_sites |o..o{ workstations : "siteId"
```
## Áreas del esquema
| Área | Tablas |
| --- | ---: |
| Cuentas y accesos: histórico | 15 |
| Cuentas y accesos: estado actual | 18 |
| Vistas SQL de cuentas y accesos | 2 |
| Correo y configuraciones | 16 |
| Activos y catálogo | 18 |
| Compañías y personas | 8 |
| Usuarios y sesión | 3 |
| Procesos de personas | 9 |
| Teletrabajo y conexiones remotas | 4 |
| Recursos y catálogo | 13 |
| Perfiles de usuario | 2 |
| Puestos de trabajo | 5 |
## Cuentas y accesos: histórico
Tablas propias: `access_account_person_revision_values`, `access_account_revision_values`, `access_coverage_revision_values`, `access_environment_revision_values`, `access_grant_revision_values`, `access_group_revision_values`, `access_membership_revision_values`, `access_need_revision_permissions`, `access_need_revision_values`, `access_permission_revision_values`, `access_resource_revision_values`, `access_resource_type_revision_values`, `access_revisions`, `access_system_revision_values`, `access_template_revision_values`.
```mermaid
erDiagram
direction LR
access_account_person_revision_values {
char ownerId PK,FK
char accountId FK
char personId FK
}
access_account_revision_values {
char ownerId PK,FK
char systemId FK
char environmentId FK "nullable"
}
access_coverage_revision_values {
char ownerId PK,FK
char needId FK
char grantId FK
char accountPersonId FK
char membershipId FK "nullable"
}
access_environment_revision_values {
char ownerId PK,FK
char systemId FK
}
access_grant_revision_values {
char ownerId PK,FK
char resourceId FK
char permissionId FK
char accountId FK "nullable"
char groupId FK "nullable"
}
access_group_revision_values {
char ownerId PK,FK
char systemId FK
char environmentId FK "nullable"
char templateId FK "nullable"
}
access_items {
char id PK
char workspaceId FK
}
access_membership_revision_values {
char ownerId PK,FK
char accountId FK
char groupId FK
}
access_need_revision_permissions {
char ownerId PK,FK
int position PK
char permissionId FK
}
access_need_revision_values {
char ownerId PK,FK
char personId FK
char resourceId FK
}
access_permission_revision_values {
char ownerId PK,FK
char resourceTypeId FK
}
access_resource_revision_values {
char ownerId PK,FK
char resourceTypeId FK
char systemId FK "nullable"
char environmentId FK "nullable"
}
access_resource_type_revision_values {
char ownerId PK,FK
}
access_revisions {
char id PK
char itemId FK
char actorId FK
}
access_system_revision_values {
char ownerId PK,FK
}
access_template_revision_values {
char ownerId PK,FK
char systemId FK "nullable"
}
company_contacts {
char id PK
char companyId FK
}
users {
char id PK
}
access_items ||..o{ access_account_person_revision_values : "accountId"
access_revisions ||..o| access_account_person_revision_values : "ownerId"
company_contacts ||..o{ access_account_person_revision_values : "personId"
access_items |o..o{ access_account_revision_values : "environmentId"
access_items ||..o{ access_account_revision_values : "systemId"
access_revisions ||..o| access_account_revision_values : "ownerId"
access_items ||..o{ access_coverage_revision_values : "accountPersonId"
access_items ||..o{ access_coverage_revision_values : "grantId"
access_items |o..o{ access_coverage_revision_values : "membershipId"
access_items ||..o{ access_coverage_revision_values : "needId"
access_revisions ||..o| access_coverage_revision_values : "ownerId"
access_items ||..o{ access_environment_revision_values : "systemId"
access_revisions ||..o| access_environment_revision_values : "ownerId"
access_items |o..o{ access_grant_revision_values : "accountId"
access_items |o..o{ access_grant_revision_values : "groupId"
access_items ||..o{ access_grant_revision_values : "permissionId"
access_items ||..o{ access_grant_revision_values : "resourceId"
access_revisions ||..o| access_grant_revision_values : "ownerId"
access_items |o..o{ access_group_revision_values : "environmentId"
access_items ||..o{ access_group_revision_values : "systemId"
access_items |o..o{ access_group_revision_values : "templateId"
access_revisions ||..o| access_group_revision_values : "ownerId"
access_items ||..o{ access_membership_revision_values : "accountId"
access_items ||..o{ access_membership_revision_values : "groupId"
access_revisions ||..o| access_membership_revision_values : "ownerId"
access_items ||..o{ access_need_revision_permissions : "permissionId"
access_need_revision_values ||..o{ access_need_revision_permissions : "ownerId"
access_items ||..o{ access_need_revision_values : "resourceId"
access_revisions ||..o| access_need_revision_values : "ownerId"
company_contacts ||..o{ access_need_revision_values : "personId"
access_items ||..o{ access_permission_revision_values : "resourceTypeId"
access_revisions ||..o| access_permission_revision_values : "ownerId"
access_items |o..o{ access_resource_revision_values : "environmentId"
access_items ||..o{ access_resource_revision_values : "resourceTypeId"
access_items |o..o{ access_resource_revision_values : "systemId"
access_revisions ||..o| access_resource_revision_values : "ownerId"
access_revisions ||..o| access_resource_type_revision_values : "ownerId"
access_items ||..o{ access_revisions : "itemId"
users ||..o{ access_revisions : "actorId"
access_revisions ||..o| access_system_revision_values : "ownerId"
access_items |o..o{ access_template_revision_values : "systemId"
access_revisions ||..o| access_template_revision_values : "ownerId"
```
## Cuentas y accesos: estado actual
Tablas propias: `access_account_person_values`, `access_account_values`, `access_coverage_values`, `access_environment_values`, `access_grant_values`, `access_group_values`, `access_items`, `access_membership_values`, `access_need_permissions`, `access_need_values`, `access_permission_values`, `access_person_references`, `access_references`, `access_resource_type_values`, `access_resource_values`, `access_system_values`, `access_template_values`, `access_workspaces`.
```mermaid
erDiagram
direction LR
access_account_person_values {
char ownerId PK,FK
char accountId FK
char personId FK
}
access_account_values {
char ownerId PK,FK
char systemId FK
char environmentId FK "nullable"
}
access_coverage_values {
char ownerId PK,FK
char needId FK
char grantId FK
char accountPersonId FK
char membershipId FK "nullable"
}
access_environment_values {
char ownerId PK,FK
char systemId FK
}
access_grant_values {
char ownerId PK,FK
char resourceId FK
char permissionId FK
char accountId FK "nullable"
char groupId FK "nullable"
}
access_group_values {
char ownerId PK,FK
char systemId FK
char environmentId FK "nullable"
char templateId FK "nullable"
}
access_items {
char id PK
char workspaceId FK
}
access_membership_values {
char ownerId PK,FK
char accountId FK
char groupId FK
}
access_need_permissions {
char ownerId PK,FK
int position PK
char permissionId FK
}
access_need_values {
char ownerId PK,FK
char personId FK
char resourceId FK
}
access_permission_values {
char ownerId PK,FK
char resourceTypeId FK
}
access_person_references {
char itemId PK,FK
char personId PK,FK
}
access_references {
int id PK
char itemId FK
char targetId FK
}
access_resource_type_values {
char ownerId PK,FK
}
access_resource_values {
char ownerId PK,FK
char resourceTypeId FK
char systemId FK "nullable"
char environmentId FK "nullable"
}
access_system_values {
char ownerId PK,FK
}
access_template_values {
char ownerId PK,FK
char systemId FK "nullable"
}
access_workspaces {
char id PK
char companyId FK "nullable"
}
companies {
char id PK
}
company_contacts {
char id PK
char companyId FK
}
access_items ||..o{ access_account_person_values : "accountId"
access_items ||..o| access_account_person_values : "ownerId"
company_contacts ||..o{ access_account_person_values : "personId"
access_items |o..o{ access_account_values : "environmentId"
access_items ||..o| access_account_values : "ownerId"
access_items ||..o{ access_account_values : "systemId"
access_items ||..o{ access_coverage_values : "accountPersonId"
access_items ||..o{ access_coverage_values : "grantId"
access_items |o..o{ access_coverage_values : "membershipId"
access_items ||..o{ access_coverage_values : "needId"
access_items ||..o| access_coverage_values : "ownerId"
access_items ||..o| access_environment_values : "ownerId"
access_items ||..o{ access_environment_values : "systemId"
access_items |o..o{ access_grant_values : "accountId"
access_items |o..o{ access_grant_values : "groupId"
access_items ||..o| access_grant_values : "ownerId"
access_items ||..o{ access_grant_values : "permissionId"
access_items ||..o{ access_grant_values : "resourceId"
access_items |o..o{ access_group_values : "environmentId"
access_items ||..o| access_group_values : "ownerId"
access_items ||..o{ access_group_values : "systemId"
access_items |o..o{ access_group_values : "templateId"
access_workspaces ||..o{ access_items : "workspaceId"
access_items ||..o{ access_membership_values : "accountId"
access_items ||..o{ access_membership_values : "groupId"
access_items ||..o| access_membership_values : "ownerId"
access_items ||..o{ access_need_permissions : "permissionId"
access_need_values ||..o{ access_need_permissions : "ownerId"
access_items ||..o| access_need_values : "ownerId"
access_items ||..o{ access_need_values : "resourceId"
company_contacts ||..o{ access_need_values : "personId"
access_items ||..o| access_permission_values : "ownerId"
access_items ||..o{ access_permission_values : "resourceTypeId"
access_items ||..o{ access_person_references : "itemId"
company_contacts ||..o{ access_person_references : "personId"
access_items ||..o{ access_references : "itemId"
access_items ||..o{ access_references : "targetId"
access_items ||..o| access_resource_type_values : "ownerId"
access_items |o..o{ access_resource_values : "environmentId"
access_items ||..o| access_resource_values : "ownerId"
access_items ||..o{ access_resource_values : "resourceTypeId"
access_items |o..o{ access_resource_values : "systemId"
access_items ||..o| access_system_values : "ownerId"
access_items ||..o| access_template_values : "ownerId"
access_items |o..o{ access_template_values : "systemId"
companies |o..o{ access_workspaces : "companyId"
```
## Vistas SQL de cuentas y accesos
Tablas propias: `access_current_values`, `access_historical_values`.
```mermaid
erDiagram
direction LR
access_current_values
access_historical_values
```
## Correo y configuraciones
Tablas propias: `application_configuration_revisions`, `application_configurations`, `certificate_configuration_revisions`, `certificate_configurations`, `folder_mapping_revisions`, `folder_mappings`, `mail_configuration_revisions`, `mail_configurations`, `mailbox_revisions`, `mailbox_use_revisions`, `mailbox_uses`, `mailboxes`, `printer_configuration_revisions`, `printer_configurations`, `software_installation_revisions`, `software_installations`.
```mermaid
erDiagram
direction LR
access_items {
char id PK
char workspaceId FK
}
application_configuration_revisions {
char id PK
char configurationId FK
char actorId FK
char profileId FK "nullable"
char resourceId FK "nullable"
}
application_configurations {
char id PK
char companyId FK
char profileId FK
char resourceId FK
}
assets {
char id PK
char companyId FK
char templateId FK
char siteId FK "nullable"
char brandId FK "nullable"
}
certificate_configuration_revisions {
char id PK
char configurationId FK
char actorId FK
char profileId FK "nullable"
char resourceId FK "nullable"
}
certificate_configurations {
char id PK
char companyId FK
char profileId FK
char resourceId FK
}
companies {
char id PK
}
company_contacts {
char id PK
char companyId FK
}
folder_mapping_revisions {
char id PK
char mappingId FK
char actorId FK
char profileId FK "nullable"
char needId FK "nullable"
}
folder_mappings {
char id PK
char companyId FK
char profileId FK
char needId FK
}
mail_configuration_revisions {
char id PK
char configurationId FK
char actorId FK
char personId FK "nullable"
char accountId FK "nullable"
char assetId FK "nullable"
char profileId FK "nullable"
char mailboxId FK "nullable"
}
mail_configurations {
char id PK
char companyId FK
char personId FK "nullable"
char accountId FK "nullable"
char assetId FK "nullable"
char profileId FK "nullable"
char mailboxId FK "nullable"
}
mailbox_revisions {
char id PK
char itemId FK
char actorId FK
char accountId FK "nullable"
}
mailbox_use_revisions {
char id PK
char itemId FK
char actorId FK
char mailboxId FK "nullable"
char personId FK "nullable"
}
mailbox_uses {
char id PK
char companyId FK
char mailboxId FK
char personId FK
}
mailboxes {
char id PK
char companyId FK
char accountId FK "nullable"
}
printer_configuration_revisions {
char id PK
char configurationId FK
char actorId FK
char profileId FK "nullable"
char printerId FK "nullable"
}
printer_configurations {
char id PK
char companyId FK
char profileId FK
char printerId FK
}
resources {
char id PK
char companyId FK
char templateId FK
char addressFieldId FK "nullable"
char contextFieldId FK "nullable"
char accountId FK "nullable"
char referenceFieldId FK "nullable"
char resourceTypeId FK "nullable"
char systemId FK "nullable"
char environmentId FK "nullable"
}
software_installation_revisions {
char id PK
char configurationId FK
char assetId FK
char resourceId FK
char actorId FK
char licenseResourceId FK "nullable"
}
software_installations {
char id PK
char companyId FK
char assetId FK
char resourceId FK
char licenseResourceId FK "nullable"
}
users {
char id PK
}
work_profiles {
char id PK
char companyId FK
char personId FK
char accountId FK "nullable"
char assetId FK
}
application_configurations ||..o{ application_configuration_revisions : "configurationId"
resources |o..o{ application_configuration_revisions : "resourceId"
users ||..o{ application_configuration_revisions : "actorId"
work_profiles |o..o{ application_configuration_revisions : "profileId"
companies ||..o{ application_configurations : "companyId"
resources ||..o{ application_configurations : "resourceId"
work_profiles ||..o{ application_configurations : "profileId"
certificate_configurations ||..o{ certificate_configuration_revisions : "configurationId"
resources |o..o{ certificate_configuration_revisions : "resourceId"
users ||..o{ certificate_configuration_revisions : "actorId"
work_profiles |o..o{ certificate_configuration_revisions : "profileId"
companies ||..o{ certificate_configurations : "companyId"
resources ||..o{ certificate_configurations : "resourceId"
work_profiles ||..o{ certificate_configurations : "profileId"
access_items |o..o{ folder_mapping_revisions : "needId"
folder_mappings ||..o{ folder_mapping_revisions : "mappingId"
users ||..o{ folder_mapping_revisions : "actorId"
work_profiles |o..o{ folder_mapping_revisions : "profileId"
access_items ||..o{ folder_mappings : "needId"
companies ||..o{ folder_mappings : "companyId"
work_profiles ||..o{ folder_mappings : "profileId"
access_items |o..o{ mail_configuration_revisions : "accountId"
assets |o..o{ mail_configuration_revisions : "assetId"
company_contacts |o..o{ mail_configuration_revisions : "personId"
mail_configurations ||..o{ mail_configuration_revisions : "configurationId"
mailboxes |o..o{ mail_configuration_revisions : "mailboxId"
users ||..o{ mail_configuration_revisions : "actorId"
work_profiles |o..o{ mail_configuration_revisions : "profileId"
access_items |o..o{ mail_configurations : "accountId"
assets |o..o{ mail_configurations : "assetId"
companies ||..o{ mail_configurations : "companyId"
company_contacts |o..o{ mail_configurations : "personId"
mailboxes |o..o{ mail_configurations : "mailboxId"
work_profiles |o..o{ mail_configurations : "profileId"
access_items |o..o{ mailbox_revisions : "accountId"
mailboxes ||..o{ mailbox_revisions : "itemId"
users ||..o{ mailbox_revisions : "actorId"
company_contacts |o..o{ mailbox_use_revisions : "personId"
mailbox_uses ||..o{ mailbox_use_revisions : "itemId"
mailboxes |o..o{ mailbox_use_revisions : "mailboxId"
users ||..o{ mailbox_use_revisions : "actorId"
companies ||..o{ mailbox_uses : "companyId"
company_contacts ||..o{ mailbox_uses : "personId"
mailboxes ||..o{ mailbox_uses : "mailboxId"
access_items |o..o{ mailboxes : "accountId"
companies ||..o{ mailboxes : "companyId"
assets |o..o{ printer_configuration_revisions : "printerId"
printer_configurations ||..o{ printer_configuration_revisions : "configurationId"
users ||..o{ printer_configuration_revisions : "actorId"
work_profiles |o..o{ printer_configuration_revisions : "profileId"
assets ||..o{ printer_configurations : "printerId"
companies ||..o{ printer_configurations : "companyId"
work_profiles ||..o{ printer_configurations : "profileId"
assets ||..o{ software_installation_revisions : "assetId"
resources |o..o{ software_installation_revisions : "licenseResourceId"
resources ||..o{ software_installation_revisions : "resourceId"
software_installations ||..o{ software_installation_revisions : "configurationId"
users ||..o{ software_installation_revisions : "actorId"
assets ||..o{ software_installations : "assetId"
companies ||..o{ software_installations : "companyId"
resources |o..o{ software_installations : "licenseResourceId"
resources ||..o{ software_installations : "resourceId"
```
## Activos y catálogo
Tablas propias: `asset_availability`, `asset_brands`, `asset_catalogs`, `asset_characteristics`, `asset_definitions`, `asset_field_choices`, `asset_relationships`, `asset_revision_characteristics`, `asset_revisions`, `asset_specification_field_choices`, `asset_specification_fields`, `asset_specification_sections`, `asset_support`, `asset_support_revisions`, `asset_template_fields`, `asset_template_sections`, `assets`, `brand_catalog_lock`.
```mermaid
erDiagram
direction LR
asset_availability {
char id PK
char catalogId FK
char companyId FK
char definitionId FK
}
asset_brands {
char id PK
}
asset_catalogs {
char id PK
}
asset_characteristics {
char assetId PK,FK
char fieldId PK,FK
}
asset_definitions {
char id PK
char catalogId FK
char companyId FK "nullable"
}
asset_field_choices {
char fieldId PK,FK
int position PK
}
asset_relationships {
char id PK
char companyId FK
char sourceId FK
char targetId FK
char typeId FK
char createdBy FK
char endedBy FK "nullable"
}
asset_revision_characteristics {
char revisionId PK,FK
char assetId FK
char fieldId PK,FK
}
asset_revisions {
char id PK
char assetId FK
char actorId FK
char siteId FK "nullable"
char brandId FK "nullable"
char templateId FK
}
asset_specification_field_choices {
char assetId PK,FK
char fieldId PK,FK
int position PK
}
asset_specification_fields {
char assetId PK,FK
char fieldId PK,FK
char sectionId FK "nullable"
}
asset_specification_sections {
char assetId PK,FK
char id PK
}
asset_support {
char id PK
char companyId FK
char assetId FK
char resourceId FK
}
asset_support_revisions {
char id PK
char connectionId FK
char actorId FK
char resourceId FK
}
asset_template_fields {
char id PK
char templateId FK
char fieldId FK
char sectionId FK "nullable"
}
asset_template_sections {
char id PK
char templateId FK
}
assets {
char id PK
char companyId FK
char templateId FK
char siteId FK "nullable"
char brandId FK "nullable"
}
brand_catalog_lock {
int id PK
}
companies {
char id PK
}
company_sites {
char id PK
char companyId FK
}
resources {
char id PK
char companyId FK
char templateId FK
char addressFieldId FK "nullable"
char contextFieldId FK "nullable"
char accountId FK "nullable"
char referenceFieldId FK "nullable"
char resourceTypeId FK "nullable"
char systemId FK "nullable"
char environmentId FK "nullable"
}
users {
char id PK
}
asset_catalogs ||..o{ asset_availability : "catalogId"
asset_definitions ||..o{ asset_availability : "definitionId"
companies ||..o{ asset_availability : "companyId"
asset_definitions ||..o{ asset_characteristics : "fieldId"
asset_specification_fields ||..o| asset_characteristics : "assetId, fieldId"
assets ||..o{ asset_characteristics : "assetId"
asset_catalogs ||..o{ asset_definitions : "catalogId"
companies |o..o{ asset_definitions : "companyId"
asset_definitions ||..o{ asset_field_choices : "fieldId"
asset_definitions ||..o{ asset_relationships : "typeId"
assets ||..o{ asset_relationships : "companyId, sourceId"
assets ||..o{ asset_relationships : "companyId, targetId"
assets ||..o{ asset_relationships : "sourceId"
assets ||..o{ asset_relationships : "targetId"
companies ||..o{ asset_relationships : "companyId"
users ||..o{ asset_relationships : "createdBy"
users |o..o{ asset_relationships : "endedBy"
asset_definitions ||..o{ asset_revision_characteristics : "fieldId"
asset_revisions ||..o{ asset_revision_characteristics : "revisionId"
asset_revisions ||..o{ asset_revision_characteristics : "revisionId, assetId"
asset_specification_fields ||..o{ asset_revision_characteristics : "assetId, fieldId"
assets ||..o{ asset_revision_characteristics : "assetId"
asset_brands |o..o{ asset_revisions : "brandId"
asset_definitions ||..o{ asset_revisions : "templateId"
assets ||..o{ asset_revisions : "assetId"
company_sites |o..o{ asset_revisions : "siteId"
users ||..o{ asset_revisions : "actorId"
asset_definitions ||..o{ asset_specification_field_choices : "fieldId"
asset_specification_fields ||..o{ asset_specification_field_choices : "assetId, fieldId"
assets ||..o{ asset_specification_field_choices : "assetId"
asset_definitions ||..o{ asset_specification_fields : "fieldId"
asset_specification_sections |o..o{ asset_specification_fields : "assetId, sectionId"
assets ||..o{ asset_specification_fields : "assetId"
assets ||..o{ asset_specification_sections : "assetId"
assets ||..o{ asset_support : "assetId"
assets ||..o{ asset_support : "companyId, assetId"
companies ||..o{ asset_support : "companyId"
resources ||..o{ asset_support : "companyId, resourceId"
resources ||..o{ asset_support : "resourceId"
asset_support ||..o{ asset_support_revisions : "connectionId"
resources ||..o{ asset_support_revisions : "resourceId"
users ||..o{ asset_support_revisions : "actorId"
asset_definitions ||..o{ asset_template_fields : "fieldId"
asset_definitions ||..o{ asset_template_fields : "templateId"
asset_template_sections |o..o{ asset_template_fields : "sectionId"
asset_template_sections |o..o{ asset_template_fields : "templateId, sectionId"
asset_definitions ||..o{ asset_template_sections : "templateId"
asset_brands |o..o{ assets : "brandId"
asset_definitions ||..o{ assets : "templateId"
companies ||..o{ assets : "companyId"
company_sites |o..o{ assets : "siteId"
```
## Compañías y personas
Tablas propias: `companies`, `company_contacts`, `company_departments`, `company_logos`, `company_sites`, `department_assignment_revisions`, `person_department_assignments`, `person_photos`.
```mermaid
erDiagram
direction LR
companies {
char id PK
}
company_contacts {
char id PK
char companyId FK
}
company_departments {
char id PK
char companyId FK
}
company_logos {
char companyId PK,FK
}
company_sites {
char id PK
char companyId FK
}
department_assignment_revisions {
char id PK
char assignmentId FK
}
person_department_assignments {
char id PK
char companyId FK
char personId FK
char departmentId FK
}
person_photos {
char personId PK,FK
char companyId FK
}
companies ||..o{ company_contacts : "companyId"
companies ||..o{ company_departments : "companyId"
companies ||..o| company_logos : "companyId"
companies ||..o{ company_sites : "companyId"
person_department_assignments ||..o{ department_assignment_revisions : "assignmentId"
companies ||..o{ person_department_assignments : "companyId"
company_contacts ||..o{ person_department_assignments : "personId, companyId"
company_departments ||..o{ person_department_assignments : "departmentId, companyId"
company_contacts ||..o| person_photos : "personId, companyId"
```
## Usuarios y sesión
Tablas propias: `http_sessions`, `identity_state`, `users`.
```mermaid
erDiagram
direction LR
http_sessions {
varchar sid PK
char userId FK "nullable"
}
identity_state {
int id PK
char initialAdminId FK "nullable"
}
users {
char id PK
}
users |o..o{ http_sessions : "userId"
users |o..o{ identity_state : "initialAdminId"
```
## Procesos de personas
Tablas propias: `person_process_revision_tasks`, `person_process_revisions`, `person_process_tasks`, `person_processes`, `process_catalog_lock`, `process_template_instructions`, `process_template_revision_instructions`, `process_template_revisions`, `process_templates`.
```mermaid
erDiagram
direction LR
companies {
char id PK
}
company_contacts {
char id PK
char companyId FK
}
person_process_revision_tasks {
char ownerId PK,FK
char taskId PK
}
person_process_revisions {
char id PK
char actorId FK "nullable"
char itemId FK
char personId FK "nullable"
char templateId FK "nullable"
}
person_process_tasks {
char ownerId PK,FK
char taskId PK
}
person_processes {
char id PK
char companyId FK
char personId FK
char templateId FK
}
process_catalog_lock {
int id PK
}
process_template_instructions {
char ownerId PK,FK
int position PK
}
process_template_revision_instructions {
char ownerId PK,FK
int position PK
}
process_template_revisions {
char id PK
char actorId FK "nullable"
char itemId FK
}
process_templates {
char id PK
}
users {
char id PK
}
person_process_revisions ||..o{ person_process_revision_tasks : "ownerId"
company_contacts |o..o{ person_process_revisions : "personId"
person_processes ||..o{ person_process_revisions : "itemId"
process_templates |o..o{ person_process_revisions : "templateId"
users |o..o{ person_process_revisions : "actorId"
person_processes ||..o{ person_process_tasks : "ownerId"
companies ||..o{ person_processes : "companyId"
company_contacts ||..o{ person_processes : "personId"
process_templates ||..o{ person_processes : "templateId"
process_templates ||..o{ process_template_instructions : "ownerId"
process_template_revisions ||..o{ process_template_revision_instructions : "ownerId"
process_templates ||..o{ process_template_revisions : "itemId"
users |o..o{ process_template_revisions : "actorId"
```
## Teletrabajo y conexiones remotas
Tablas propias: `remote_connection_revisions`, `remote_connections`, `telework_authorization_revisions`, `telework_authorizations`.
```mermaid
erDiagram
direction LR
assets {
char id PK
char companyId FK
char templateId FK
char siteId FK "nullable"
char brandId FK "nullable"
}
companies {
char id PK
}
company_contacts {
char id PK
char companyId FK
}
company_sites {
char id PK
char companyId FK
}
remote_connection_revisions {
char id PK
char connectionId FK
char actorId FK
char personId FK "nullable"
char sourceAssetId FK "nullable"
char targetAssetId FK "nullable"
char targetResourceId FK "nullable"
char siteId FK "nullable"
}
remote_connections {
char id PK
char companyId FK
char personId FK
char sourceAssetId FK "nullable"
char targetAssetId FK "nullable"
char targetResourceId FK "nullable"
char siteId FK "nullable"
}
resources {
char id PK
char companyId FK
char templateId FK
char addressFieldId FK "nullable"
char contextFieldId FK "nullable"
char accountId FK "nullable"
char referenceFieldId FK "nullable"
char resourceTypeId FK "nullable"
char systemId FK "nullable"
char environmentId FK "nullable"
}
telework_authorization_revisions {
char id PK
char authorizationId FK
char actorId FK
char personId FK "nullable"
}
telework_authorizations {
char id PK
char companyId FK
char personId FK
}
users {
char id PK
}
assets |o..o{ remote_connection_revisions : "sourceAssetId"
assets |o..o{ remote_connection_revisions : "targetAssetId"
company_contacts |o..o{ remote_connection_revisions : "personId"
company_sites |o..o{ remote_connection_revisions : "siteId"
remote_connections ||..o{ remote_connection_revisions : "connectionId"
resources |o..o{ remote_connection_revisions : "targetResourceId"
users ||..o{ remote_connection_revisions : "actorId"
assets |o..o{ remote_connections : "sourceAssetId"
assets |o..o{ remote_connections : "targetAssetId"
companies ||..o{ remote_connections : "companyId"
company_contacts ||..o{ remote_connections : "personId"
company_sites |o..o{ remote_connections : "siteId"
resources |o..o{ remote_connections : "targetResourceId"
company_contacts |o..o{ telework_authorization_revisions : "personId"
telework_authorizations ||..o{ telework_authorization_revisions : "authorizationId"
users ||..o{ telework_authorization_revisions : "actorId"
companies ||..o{ telework_authorizations : "companyId"
company_contacts ||..o| telework_authorizations : "personId"
```
## Recursos y catálogo
Tablas propias: `resource_availability`, `resource_catalogs`, `resource_characteristics`, `resource_definitions`, `resource_field_choices`, `resource_revision_characteristics`, `resource_revisions`, `resource_specification_field_choices`, `resource_specification_fields`, `resource_specification_sections`, `resource_template_fields`, `resource_template_sections`, `resources`.
```mermaid
erDiagram
direction LR
access_items {
char id PK
char workspaceId FK
}
companies {
char id PK
}
resource_availability {
char id PK
char catalogId FK
char companyId FK
char definitionId FK
}
resource_catalogs {
char id PK
}
resource_characteristics {
char resourceId PK,FK
char fieldId PK,FK
}
resource_definitions {
char id PK
char catalogId FK
char companyId FK "nullable"
}
resource_field_choices {
char fieldId PK,FK
int position PK
}
resource_revision_characteristics {
char revisionId PK,FK
char resourceId FK
char fieldId PK,FK
}
resource_revisions {
char id PK
char resourceId FK
char actorId FK
char addressFieldId FK "nullable"
char contextFieldId FK "nullable"
char accountId FK "nullable"
char referenceFieldId FK "nullable"
char resourceTypeId FK "nullable"
char systemId FK "nullable"
char environmentId FK "nullable"
char templateId FK "nullable"
}
resource_specification_field_choices {
char resourceId PK,FK
char fieldId PK,FK
int position PK
}
resource_specification_fields {
char resourceId PK,FK
char fieldId PK,FK
}
resource_specification_sections {
char resourceId PK,FK
char id PK
}
resource_template_fields {
char id PK
char templateId FK
char fieldId FK
char sectionId FK "nullable"
}
resource_template_sections {
char id PK
char templateId PK,FK
}
resources {
char id PK
char companyId FK
char templateId FK
char addressFieldId FK "nullable"
char contextFieldId FK "nullable"
char accountId FK "nullable"
char referenceFieldId FK "nullable"
char resourceTypeId FK "nullable"
char systemId FK "nullable"
char environmentId FK "nullable"
}
users {
char id PK
}
companies ||..o{ resource_availability : "companyId"
resource_catalogs ||..o{ resource_availability : "catalogId"
resource_definitions ||..o{ resource_availability : "definitionId"
resource_definitions ||..o{ resource_characteristics : "fieldId"
resources ||..o{ resource_characteristics : "resourceId"
companies |o..o{ resource_definitions : "companyId"
resource_catalogs ||..o{ resource_definitions : "catalogId"
resource_definitions ||..o{ resource_field_choices : "fieldId"
resource_definitions ||..o{ resource_revision_characteristics : "fieldId"
resource_revisions ||..o{ resource_revision_characteristics : "revisionId"
resources ||..o{ resource_revision_characteristics : "resourceId"
access_items |o..o{ resource_revisions : "accountId"
access_items |o..o{ resource_revisions : "environmentId"
access_items |o..o{ resource_revisions : "resourceTypeId"
access_items |o..o{ resource_revisions : "systemId"
resource_definitions |o..o{ resource_revisions : "addressFieldId"
resource_definitions |o..o{ resource_revisions : "contextFieldId"
resource_definitions |o..o{ resource_revisions : "referenceFieldId"
resource_definitions |o..o{ resource_revisions : "templateId"
resources ||..o{ resource_revisions : "resourceId"
users ||..o{ resource_revisions : "actorId"
resource_definitions ||..o{ resource_specification_field_choices : "fieldId"
resources ||..o{ resource_specification_field_choices : "resourceId"
resource_definitions ||..o{ resource_specification_fields : "fieldId"
resources ||..o{ resource_specification_fields : "resourceId"
resources ||..o{ resource_specification_sections : "resourceId"
resource_definitions ||..o{ resource_template_fields : "fieldId"
resource_definitions ||..o{ resource_template_fields : "templateId"
resource_template_sections |o..o{ resource_template_fields : "templateId, sectionId"
resource_definitions ||..o{ resource_template_sections : "templateId"
access_items |o..o{ resources : "accountId"
access_items |o..o{ resources : "environmentId"
access_items |o..o{ resources : "resourceTypeId"
access_items |o..o{ resources : "systemId"
companies ||..o{ resources : "companyId"
resource_definitions |o..o{ resources : "addressFieldId"
resource_definitions |o..o{ resources : "contextFieldId"
resource_definitions |o..o{ resources : "referenceFieldId"
resource_definitions ||..o{ resources : "templateId"
```
## Perfiles de usuario
Tablas propias: `work_profile_revisions`, `work_profiles`.
```mermaid
erDiagram
direction LR
access_items {
char id PK
char workspaceId FK
}
assets {
char id PK
char companyId FK
char templateId FK
char siteId FK "nullable"
char brandId FK "nullable"
}
companies {
char id PK
}
company_contacts {
char id PK
char companyId FK
}
users {
char id PK
}
work_profile_revisions {
char id PK
char profileId FK
char accountId FK "nullable"
char actorId FK
char personId FK "nullable"
char assetId FK "nullable"
}
work_profiles {
char id PK
char companyId FK
char personId FK
char accountId FK "nullable"
char assetId FK
}
access_items |o..o{ work_profile_revisions : "accountId"
assets |o..o{ work_profile_revisions : "assetId"
company_contacts |o..o{ work_profile_revisions : "personId"
users ||..o{ work_profile_revisions : "actorId"
work_profiles ||..o{ work_profile_revisions : "profileId"
access_items |o..o{ work_profiles : "accountId"
assets ||..o{ work_profiles : "assetId"
companies ||..o{ work_profiles : "companyId"
company_contacts ||..o{ work_profiles : "personId"
```
## Puestos de trabajo
Tablas propias: `workstation_assets`, `workstation_members`, `workstation_revision_members`, `workstation_revisions`, `workstations`.
```mermaid
erDiagram
direction LR
assets {
char id PK
char companyId FK
char templateId FK
char siteId FK "nullable"
char brandId FK "nullable"
}
companies {
char id PK
}
company_contacts {
char id PK
char companyId FK
}
company_sites {
char id PK
char companyId FK
}
users {
char id PK
}
workstation_assets {
char workstationId PK,FK
char companyId FK
char assetId PK,FK
}
workstation_members {
char workstationId PK,FK
char assetId PK,FK
}
workstation_revision_members {
char revisionId PK,FK
char assetId PK,FK
}
workstation_revisions {
char id PK
char workstationId FK
char actorId FK
char siteId FK "nullable"
char personId FK "nullable"
char computerAssetId FK "nullable"
}
workstations {
char id PK
char companyId FK
char siteId FK "nullable"
char personId FK "nullable"
char computerAssetId FK "nullable"
}
assets ||..o{ workstation_assets : "assetId"
assets ||..o{ workstation_assets : "companyId, assetId"
companies ||..o{ workstation_assets : "companyId"
workstations ||..o{ workstation_assets : "companyId, workstationId"
workstations ||..o{ workstation_assets : "workstationId"
assets ||..o{ workstation_members : "assetId"
workstations ||..o{ workstation_members : "workstationId"
assets ||..o{ workstation_revision_members : "assetId"
workstation_revisions ||..o{ workstation_revision_members : "revisionId"
assets |o..o{ workstation_revisions : "computerAssetId"
company_contacts |o..o{ workstation_revisions : "personId"
company_sites |o..o{ workstation_revisions : "siteId"
users ||..o{ workstation_revisions : "actorId"
workstations ||..o{ workstation_revisions : "workstationId"
assets |o..o{ workstations : "computerAssetId"
companies ||..o{ workstations : "companyId"
company_contacts |o..o{ workstations : "personId"
company_sites |o..o{ workstations : "siteId"
```
## Observaciones del esquema físico
Los 113 objetos representados son **111 tablas y 2 vistas SQL**: `access_current_values` y `access_historical_values`. Las vistas no tienen FK propias y se representan como objetos independientes; no se inventan relaciones físicas para ellas.
La captura conservada contiene **17 relaciones con varias restricciones FK equivalentes**: 699 restricciones adicionales. Se han agrupado únicamente en la representación; no se han eliminado. El diccionario conserva todos los nombres de restricciones. Conviene revisar por separado el origen de estas duplicaciones.
Solo se dibujan relaciones declaradas en MariaDB. Un identificador sin FK no implica una relación física garantizada; las referencias polimórficas y las validaciones de aplicación pueden tener relaciones adicionales. Las tablas de revisiones forman parte del esquema actual y se incluyen. Esta documentación no implica que exista ya la auditoría centralizada diseñada para una implementación futura.
## Validación
La depuración documental se ha contrastado con la lista explícita de tablas de la migración
activa. El diccionario y el diagrama completo coinciden en 111 tablas y 2 vistas SQL,
860 columnas y 309 relaciones distintas; todas las tablas se asignan a un área.
Las reglas ON DELETE/ON UPDATE se recogen en el diccionario. Esta comprobación revisa
los documentos y la migración, no certifica el estado de una base de datos en ejecución.
Reference in New Issue View Git Blame Copy Permalink