Modelo de base de datos - AYUNTAMIENTOSMART

Capítulo 1: Visión general del modelo de datos

El modelo de datos de ayuntamientosmart.com sigue un diseño relacional normalizado (hasta 3NF) adaptado al almacenamiento JSON. Las principales características son:

Entidades principales

Denuncias: Denuncias municipales de cualquier tipo
Sanciones: Multas de tráfico y sanciones administrativas
Atestados: Expedientes judiciales e investigaciones
Personas: Denunciantes, denunciados, detenidos, testigos
Vehículos: Vehículos implicados en expedientes
Usuarios: Usuarios del sistema (agentes, administrativos)
Servicios: Servicios municipales realizados
Objetos: Objetos perdidos y encontrados
Turnos: Turnos de trabajo y cuadrantes
Auditoría: Log de todas las operaciones

Metadatos automáticos

Todos los registros incluyen automáticamente estos campos de metadatos:

{
    "_id": "DEN-2026-00001",              // ID único del registro
    "_created": "2026-01-10T10:30:00Z",  // Fecha de creación
    "_modified": "2026-01-10T11:45:00Z", // Última modificación
    "_created_by": "usr-123",            // Usuario que creó
    "_modified_by": "usr-456",           // Usuario que modificó
    "_version": 3,                        // Número de versión (para control de concurrencia)
    "_hash": "sha256:abc123...",         // Hash del contenido
    "_deleted": null                      // Timestamp de eliminación (soft delete)
}

Capítulo 2: Entidad Denuncias

La entidad Denuncias almacena todas las denuncias municipales registradas en el sistema.

Estructura JSON

{
    "_id": "DEN-2026-00001",
    "tipo": "hechos",
    "estado": "registrada",
    "numero": "DEN-2026-00001",
    "fecha_registro": "2026-01-10T10:30:00Z",
    "agente_tramitador": "usr-123",
    
    "denunciante": {
        "persona_id": "per-456",
        "dni": "ENCRYPTED:abc123...",
        "nombre": "María García López",
        "fecha_nacimiento": "1985-05-15",
        "nacionalidad": "ESP",
        "direccion": "ENCRYPTED:def456...",
        "telefono": "ENCRYPTED:ghi789...",
        "email": "maria@example.com"
    },
    
    "hechos": {
        "fecha": "2026-01-09",
        "hora": "18:30",
        "lugar": "Calle Mayor 15, Madrid",
        "coordenadas": {
            "lat": 40.4168,
            "lon": -3.7038
        },
        "descripcion": "Me sustrajeron el teléfono móvil mientras caminaba..."
    },
    
    "denunciados": [
        {
            "persona_id": "per-789",
            "dni": "ENCRYPTED:jkl012...",
            "nombre": "Juan Pérez",
            "relacion_hechos": "Presunto autor de la sustracción"
        }
    ],
    
    "testigos": [
        {
            "persona_id": "per-012",
            "nombre": "Pedro Sánchez",
            "telefono": "ENCRYPTED:mno345..."
        }
    ],
    
    "objetos": [
        {
            "tipo": "telefono",
            "marca": "Apple",
            "modelo": "iPhone 14 Pro",
            "color": "Azul",
            "imei": "123456789012345",
            "valor_estimado": 1000.00
        }
    ],
    
    "adjuntos": [
        {
            "id": "att-001",
            "nombre": "foto_danos.jpg",
            "tipo": "image/jpeg",
            "tamano": 2548672,
            "ruta": "/data/{tenant}/adjuntos/2026/01/att-001.jpg",
            "hash_sha256": "abc123..."
        }
    ],
    
    "tramitacion": {
        "estado_actual": "registrada",
        "fecha_notificacion": "2026-01-10T12:00:00Z",
        "metodo_notificacion": "email",
        "remitida_a": null,
        "fecha_archivo": null,
        "motivo_archivo": null
    },
    
    "firma": {
        "firmada": true,
        "fecha_firma": "2026-01-10T10:35:00Z",
        "usuario_firma": "usr-123",
        "hash_documento": "sha256:def456...",
        "certificado_digital": false
    },
    
    "_created": "2026-01-10T10:30:00Z",
    "_modified": "2026-01-10T10:35:00Z",
    "_created_by": "usr-123",
    "_modified_by": "usr-123",
    "_version": 1,
    "_hash": "sha256:xyz789...",
    "_deleted": null
}

Diccionario de campos

Campo	Tipo	Descripción	Valores
tipo	string	Tipo de denuncia	hechos, perdida, trafico, ruidos, urbanistica
estado	string	Estado de tramitación	borrador, registrada, en_investigacion, remitida, archivada, anulada
denunciante.dni	string	DNI/NIE cifrado	Cifrado AES-256-GCM
hechos.coordenadas	object	Geolocalización del lugar	{lat: float, lon: float}
objetos	array	Objetos sustraídos/perdidos	Array de objetos

Capítulo 3: Entidad Sanciones

Almacena sanciones de tráfico y multas administrativas.

Estructura JSON

{
    "_id": "SAN-2026-00001",
    "tipo": "in_situ",
    "estado": "notificada",
    "numero_boletin": "SAN-2026-00001",
    "fecha_imposicion": "2026-01-10T15:30:00Z",
    "agente_denunciante": "usr-123",
    
    "vehiculo": {
        "matricula": "1234ABC",
        "marca": "SEAT",
        "modelo": "IBIZA",
        "color": "Blanco",
        "bastidor": "VF1XXXXXXXXXXXX",
        "titular": {
            "dni": "ENCRYPTED:abc123...",
            "nombre": "José Martínez García",
            "direccion": "ENCRYPTED:def456..."
        }
    },
    
    "conductor": {
        "es_titular": true,
        "dni": "ENCRYPTED:abc123...",
        "nombre": "José Martínez García",
        "permiso": "ES987654321",
        "clases_permiso": ["B"],
        "puntos_disponibles": 12
    },
    
    "infraccion": {
        "codigo": "3.1",
        "descripcion": "Circular en sentido contrario al establecido",
        "articulo_ley": "Artículo 29 RGC",
        "calificacion": "grave",
        "fecha": "2026-01-10",
        "hora": "15:15",
        "lugar": "Calle Alcalá 100, Madrid",
        "coordenadas": {"lat": 40.4200, "lon": -3.6900},
        "circunstancias": "Vehículo circulando por carril contrario en calle de sentido único"
    },
    
    "sancion": {
        "importe": 200.00,
        "importe_reducido": 100.00,
        "puntos": 4,
        "fecha_limite_descuento": "2026-01-30",
        "pagada": false,
        "fecha_pago": null,
        "importe_pagado": null,
        "forma_pago": null
    },
    
    "notificacion": {
        "metodo": "in_situ",
        "fecha": "2026-01-10T15:30:00Z",
        "firmada_conductor": true,
        "negativa_firma": false,
        "acuse_recibo": true
    },
    
    "alegaciones": {
        "presentadas": false,
        "fecha_presentacion": null,
        "texto": null,
        "adjuntos": [],
        "resolucion": null,
        "fecha_resolucion": null
    },
    
    "dgt": {
        "enviado": true,
        "fecha_envio": "2026-01-10T16:00:00Z",
        "numero_boletin_dgt": "DGT123456789",
        "acuse_recibo": true
    },
    
    "_created": "2026-01-10T15:30:00Z",
    "_modified": "2026-01-10T16:00:00Z",
    "_created_by": "usr-123",
    "_modified_by": "usr-123",
    "_version": 2,
    "_hash": "sha256:abc123...",
    "_deleted": null
}

Campos específicos de sanciones

Campo	Tipo	Descripción	Valores
tipo	string	Tipo de sanción	in_situ, formulada
infraccion.calificacion	string	Gravedad	leve, grave, muy_grave
sancion.puntos	integer	Puntos a detraer	0-6
conductor.es_titular	boolean	¿El conductor es el titular?	true/false

Capítulo 4: Entidad Personas

Registro unificado de todas las personas que interactúan con el sistema.

Estructura JSON

{
    "_id": "per-123",
    "dni": "ENCRYPTED:abc123...",
    "nombre": "María",
    "apellido1": "García",
    "apellido2": "López",
    "nombre_completo": "María García López",
    "fecha_nacimiento": "1985-05-15",
    "nacionalidad": "ESP",
    "sexo": "F",
    
    "contacto": {
        "direccion": "ENCRYPTED:def456...",
        "municipio": "Madrid",
        "provincia": "Madrid",
        "cp": "28001",
        "telefono_fijo": "ENCRYPTED:ghi789...",
        "telefono_movil": "ENCRYPTED:jkl012...",
        "email": "maria@example.com"
    },
    
    "documentacion": {
        "tipo_documento": "DNI",
        "numero_documento": "ENCRYPTED:abc123...",
        "fecha_expedicion": "2015-06-01",
        "fecha_caducidad": "2025-06-01"
    },
    
    "antecedentes": {
        "tiene_antecedentes": false,
        "denuncias_como_denunciante": 2,
        "denuncias_como_denunciado": 0,
        "sanciones": 1,
        "detenciones": 0
    },
    
    "notas": "Persona colaboradora, víctima habitual de hurtos",
    
    "_created": "2025-03-15T09:00:00Z",
    "_modified": "2026-01-10T10:30:00Z",
    "_created_by": "usr-100",
    "_modified_by": "usr-123",
    "_version": 5,
    "_hash": "sha256:xyz789...",
    "_deleted": null
}

Capítulo 5: Entidad Usuarios

Usuarios del sistema (agentes, administrativos, mandos).

Estructura JSON

{
    "_id": "usr-123",
    "usuario": "agente.garcia",
    "password_hash": "$argon2id$v=19$m=65536,t=4,p=3$...",
    "email": "garcia@policia.madrid.es",
    
    "perfil": {
        "tip": "12345",
        "nombre": "Juan",
        "apellidos": "García Pérez",
        "rango": "Técnico",
        "unidad": "AyuntamientoSmart Madrid",
        "telefono": "666123456",
        "foto": "/uploads/users/usr-123.jpg"
    },
    
    "rol": "agente",
    "permisos": [
        "denuncias.crear",
        "denuncias.ver",
        "sanciones.crear",
        "sanciones.ver",
        "vehiculos.consultar"
    ],
    
    "seguridad": {
        "2fa_habilitado": true,
        "2fa_secret": "ENCRYPTED:abc123...",
        "ultimo_acceso": "2026-01-10T09:00:00Z",
        "ip_ultimo_acceso": "192.168.1.50",
        "intentos_fallidos": 0,
        "bloqueado_hasta": null,
        "cambiar_password": false
    },
    
    "preferencias": {
        "idioma": "es",
        "zona_horaria": "Europe/Madrid",
        "notificaciones_email": true,
        "items_por_pagina": 20
    },
    
    "firma_digital": {
        "tiene_certificado": true,
        "certificado_cn": "Juan García Pérez",
        "certificado_issuer": "FNMT-RCM",
        "certificado_caducidad": "2027-06-01"
    },
    
    "activo": true,
    "fecha_alta": "2020-01-15",
    "fecha_baja": null,
    
    "_created": "2020-01-15T08:00:00Z",
    "_modified": "2026-01-10T09:00:00Z",
    "_created_by": "usr-001",
    "_modified_by": "usr-123",
    "_version": 45,
    "_hash": "sha256:def456...",
    "_deleted": null
}

Capítulo 6: Entidad Auditoría

Log inmutable de todas las operaciones del sistema.

Estructura JSON

{
    "_id": "log-2026-01-10-0001234",
    "timestamp": "2026-01-10T10:30:15.547Z",
    "usuario": {
        "id": "usr-123",
        "nombre": "Juan García",
        "rol": "agente"
    },
    "ip": "192.168.1.50",
    "modulo": "denuncias",
    "accion": "crear",
    "registro_id": "DEN-2026-00001",
    "datos_antes": null,
    "datos_despues": { /* Snapshot del registro creado */ },
    "cambios": [
        {"campo": "estado", "antes": "borrador", "despues": "registrada"}
    ],
    "hash_anterior": "sha256:abc123...",
    "hash_actual": "sha256:def456...",
    "metadata": {
        "user_agent": "Mozilla/5.0...",
        "duracion_ms": 145
    }
}

Relaciones entre entidades

Aunque el almacenamiento es en JSON (no relacional), el modelo lógico mantiene relaciones claras entre entidades mediante IDs de referencia.

Denuncias - Personas

Una denuncia puede tener:

1 denunciante (relación 1:1 con Personas)
0-N denunciados (relación 1:N con Personas)
0-N testigos (relación 1:N con Personas)

La relación se establece mediante el campo persona_id que referencia el _id en la entidad Personas. Al cargar una denuncia, se puede hacer join en memoria para obtener los datos completos de las personas.

Sanciones - Vehículos

Una sanción está asociada a 1 vehículo (relación 1:1). El vehículo se identifica por matrícula. Los datos del vehículo pueden estar almacenados localmente en la entidad Vehículos o consultarse en tiempo real a la DGT mediante integración TESTRA.

Atestados - Denuncias

Un atestado puede originarse de 1 denuncia (relación 1:1 opcional). El campo origen_denuncia_id en Atestados referencia el _id de la denuncia original. Esto permite trazabilidad completa desde la denuncia inicial hasta el procedimiento judicial.

Registros - Usuarios

Todos los registros tienen relación con Usuarios mediante los campos _created_by y _modified_by. Esto permite auditoría completa de quién creó/modificó cada registro.

Índices y optimización de consultas

Para acelerar las consultas más frecuentes, se mantienen índices ligeros en archivos separados.

Índice por estado

Archivo: /data/{tenant}/denuncias/_index_estado.json

{
    "borrador": ["DEN-2026-00005", "DEN-2026-00012"],
    "registrada": ["DEN-2026-00001", "DEN-2026-00002", ...],
    "archivada": ["DEN-2025-00999", ...]
}

Permite filtrar por estado sin cargar todos los buckets.

Índice por fecha

Archivo: /data/{tenant}/denuncias/_index_fecha.json

{
    "2026-01-10": ["DEN-2026-00001", "DEN-2026-00002"],
    "2026-01-09": ["DEN-2025-00999"],
    ...
}

Acelera consultas de denuncias por rango de fechas.

Índice por persona

Archivo: /data/{tenant}/personas/_index_dni.json

{
    "DNI_HASH_12345678A": "per-123",
    "DNI_HASH_87654321B": "per-456",
    ...
}

Permite búsqueda rápida de personas por DNI sin descifrar todos los registros. Se indexa el hash del DNI, no el DNI en claro.

Mantenimiento de índices

Los índices se actualizan automáticamente cada vez que se crea/modifica/elimina un registro. Si un índice se corrompe o desincroniza, puede regenerarse completamente ejecutando el script de mantenimiento: php cron.php rebuild_indexes

Versionado y control de concurrencia

El sistema implementa control de concurrencia optimista mediante versionado de registros.

Campo _version

Cada registro tiene un campo _version que se incrementa en cada modificación. Al actualizar un registro:

El cliente envía el registro con su _version actual (ej: version 5)
El servidor comprueba que la versión en base de datos coincide
Si coincide, actualiza y incrementa a versión 6
Si no coincide (otro usuario modificó mientras tanto), rechaza la actualización con error 409 Conflict

Resolución de conflictos

Cuando se detecta un conflicto de concurrencia (versiones no coinciden), el sistema:

Devuelve error 409 con los datos actuales de la base de datos
El cliente muestra al usuario los cambios en conflicto
El usuario decide: sobrescribir cambios ajenos, descartar sus cambios, o fusionar manualmente
Se reintenta la actualización con la versión correcta

Historial de versiones

Opcionalmente (según configuración), se puede activar el guardado de todas las versiones históricas de un registro en archivos separados:

/data/{tenant}/denuncias/_historial/DEN-2026-00001/
├── v1.json  # Versión 1
├── v2.json  # Versión 2
├── v3.json  # Versión 3
└── v4.json  # Versión actual

Esto permite auditoría completa y recuperación de versiones anteriores.

Soft delete y recuperación

Los registros no se eliminan físicamente de la base de datos. En su lugar, se marcan como eliminados (soft delete).

Marcado como eliminado

Al eliminar un registro, se establece el campo _deleted con el timestamp de eliminación:

{
    "_id": "DEN-2026-00001",
    /* ... resto de campos ... */
    "_deleted": "2026-01-15T16:30:00Z"
}

Las consultas normales excluyen automáticamente registros con _deleted != null. Solo usuarios con permisos especiales pueden ver registros eliminados.

Recuperación de registros eliminados

Un registro eliminado puede recuperarse estableciendo _deleted a null:

db_restore('denuncias', 'DEN-2026-00001');

El registro vuelve a aparecer en consultas normales. La recuperación queda registrada en auditoría.

Purga definitiva

Según política de retención (típicamente 3 años), los registros eliminados se purgan definitivamente mediante un proceso batch nocturno:

// Purgar registros eliminados hace más de 3 años
$fecha_limite = date('Y-m-d', strtotime('-3 years'));
db_purge('denuncias', $fecha_limite);

La purga elimina físicamente los archivos. Este proceso es irreversible.

Cifrado de campos sensibles

Ciertos campos contienen datos personales sensibles que deben cifrarse según RGPD y LO 7/2021.

Campos cifrados

Se cifran con AES-256-GCM los siguientes tipos de datos:

Identificación: DNI, NIE, pasaporte, número de seguridad social
Contacto: Dirección postal completa, teléfono, email (opcional)
Salud: Datos médicos, enfermedades, tratamientos
Antecedentes: Antecedentes penales, sanciones previas
Datos sensibles: Orientación sexual, creencias religiosas, afiliación sindical

Formato de campo cifrado

Los campos cifrados se almacenan como strings con el prefijo ENCRYPTED:

"dni": "ENCRYPTED:AeB4f7G9..."

El valor después de ENCRYPTED: es base64 de: IV (16 bytes) + ciphertext + tag (16 bytes). Al leer, el sistema detecta el prefijo ENCRYPTED: y descifra automáticamente si el usuario tiene permisos.

Búsqueda en campos cifrados

No se puede buscar directamente en campos cifrados (el cifrado impide búsqueda de texto). Para permitir búsquedas, se mantiene un índice con hash del campo:

// Índice de DNIs
{
    "sha256:abc123...": "per-123",  // Hash de DNI 12345678A
    "sha256:def456...": "per-456"
}

Al buscar por DNI, se calcula su hash y se consulta el índice.

Rotación de claves

Periódicamente (cada 2 años), se debe rotar la clave de cifrado por seguridad. El proceso es:

Generar nueva clave de cifrado
Descifrar todos los campos con clave antigua
Cifrar con clave nueva
Actualizar constante ENCRYPTION_KEY
Destruir clave antigua de forma segura

El sistema incluye script de rotación: php cron.php rotate_encryption_key

Historial de cambios

Version 1.0 - 2026-01-10

Versión inicial del modelo de base de datos
Documentación completa de entidades principales
Estructura JSON de denuncias, sanciones, personas, usuarios
Diccionario de campos con tipos y restricciones
Relaciones entre entidades
Sistema de índices para optimización
Versionado y control de concurrencia
Soft delete y recuperación
Cifrado de campos sensibles