Base de données - Informations Officielles¶

Tables¶

informations_officielles¶

Table principale stockant les informations diffusées par les mairies.

CREATE TABLE IF NOT EXISTS informations_officielles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mairie_id INT(11) NOT NULL,

    -- Contenu
    titre VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    type_info ENUM('TRAVAUX', 'EVENEMENT', 'ALERTE', 'ANNONCE') NOT NULL,

    -- Géolocalisation
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    adresse VARCHAR(500),
    rayon_metres INT DEFAULT 1000,

    -- Cycle de vie
    statut ENUM('BROUILLON', 'PROGRAMME', 'PUBLIE', 'EXPIRE', 'DESACTIVE')
           DEFAULT 'BROUILLON',

    -- Publication différée
    date_debut_publication DATETIME NULL,
    date_publication DATETIME,
    date_expiration DATETIME,

    -- Notification push
    notification_envoyee BOOLEAN DEFAULT FALSE,
    date_notification DATETIME NULL,

    -- Audit
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by VARCHAR(100),

    -- Index
    INDEX idx_mairie (mairie_id),
    INDEX idx_statut (statut),
    INDEX idx_dates (date_debut_publication, date_publication, date_expiration),
    INDEX idx_geo (latitude, longitude),
    INDEX idx_notification (notification_envoyee, statut),

    FOREIGN KEY (mairie_id) REFERENCES mairies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Colonnes¶

Colonne	Type	Description
`id`	BIGINT	Identifiant unique auto-incrémenté
`mairie_id`	INT	Référence vers la mairie
`titre`	VARCHAR(255)	Titre de l'information
`description`	TEXT	Description complète
`type_info`	ENUM	TRAVAUX, EVENEMENT, ALERTE, ANNONCE
`latitude`	DECIMAL(10,8)	Latitude du point central
`longitude`	DECIMAL(11,8)	Longitude du point central
`adresse`	VARCHAR(500)	Adresse textuelle (optionnel)
`rayon_metres`	INT	Rayon de la zone d'impact (défaut: 1000m)
`statut`	ENUM	État du cycle de vie
`date_debut_publication`	DATETIME	Date de publication programmée
`date_publication`	DATETIME	Date effective de mise en ligne
`date_expiration`	DATETIME	Date de fin de visibilité
`notification_envoyee`	BOOLEAN	Flag notification push envoyée
`date_notification`	DATETIME	Date d'envoi de la notification
`created_at`	TIMESTAMP	Date de création
`updated_at`	TIMESTAMP	Date de dernière modification
`created_by`	VARCHAR(100)	Email de l'utilisateur créateur

Statuts¶

Statut	Description
`BROUILLON`	En cours de rédaction, non visible
`PROGRAMME`	Validé, en attente de `date_debut_publication`
`PUBLIE`	Visible par les citoyens
`EXPIRE`	`date_expiration` dépassée, archivé
`DESACTIVE`	Masqué manuellement par l'admin

info_photos¶

Photos associées aux informations officielles.

CREATE TABLE IF NOT EXISTS info_photos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    info_id BIGINT UNSIGNED NOT NULL,
    filepath VARCHAR(500) NOT NULL,
    filename VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_info (info_id),
    FOREIGN KEY (info_id) REFERENCES informations_officielles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

push_endpoints¶

Endpoints UnifiedPush pour les notifications privacy-first.

CREATE TABLE IF NOT EXISTS push_endpoints (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    endpoint VARCHAR(1000) NOT NULL UNIQUE,
    device_id VARCHAR(64) NULL COMMENT 'Fingerprint device (optionnel)',

    -- Préférences de notification
    notify_travaux BOOLEAN DEFAULT TRUE,
    notify_evenements BOOLEAN DEFAULT TRUE,
    notify_alertes BOOLEAN DEFAULT TRUE,
    notify_annonces BOOLEAN DEFAULT TRUE,

    -- Géolocalisation pour ciblage
    last_latitude DECIMAL(10, 8) NULL,
    last_longitude DECIMAL(11, 8) NULL,

    -- Audit
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_device (device_id),
    INDEX idx_geo (last_latitude, last_longitude),
    INDEX idx_notify (notify_travaux, notify_evenements, notify_alertes, notify_annonces)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Colonnes¶

Colonne	Type	Description
`endpoint`	VARCHAR(1000)	URL unique du distributeur UnifiedPush
`device_id`	VARCHAR(64)	Fingerprint device (optionnel, RGPD)
`notify_*`	BOOLEAN	Préférences par type d'info
`last_latitude`	DECIMAL	Dernière position connue
`last_longitude`	DECIMAL	Dernière position connue

RGPD

Aucun identifiant device (IMEI, Android ID) n'est stocké. Le device_id est un fingerprint anonyme optionnel.

Event Schedulers¶

Publication automatique¶

CREATE EVENT IF NOT EXISTS evt_publish_scheduled_infos
ON SCHEDULE EVERY 1 MINUTE
ENABLE
DO
    UPDATE informations_officielles
    SET statut = 'PUBLIE',
        date_publication = NOW()
    WHERE statut = 'PROGRAMME'
    AND date_debut_publication IS NOT NULL
    AND date_debut_publication <= NOW();

Fréquence : Toutes les minutes

Action : Passe les infos PROGRAMME en PUBLIE quand date_debut_publication est atteinte.

Expiration automatique¶

CREATE EVENT IF NOT EXISTS evt_expire_old_infos
ON SCHEDULE EVERY 5 MINUTE
ENABLE
DO
    UPDATE informations_officielles
    SET statut = 'EXPIRE'
    WHERE statut = 'PUBLIE'
    AND date_expiration IS NOT NULL
    AND date_expiration < NOW();

Fréquence : Toutes les 5 minutes

Action : Passe les infos PUBLIE en EXPIRE quand date_expiration est dépassée.

Activation Event Scheduler¶

-- Vérifier l'état
SHOW VARIABLES LIKE 'event_scheduler';

-- Activer (session)
SET GLOBAL event_scheduler = ON;

-- Activer (permanent via my.cnf)
-- [mysqld]
-- event_scheduler = ON

Migration¶

Fichier : database/migrations/004_create_informations_officielles.sql

# Exécuter la migration
ssh <utilisateur>@<serveur> 'cd /srv/urbafix && \
  docker-compose exec -T db mysql -uroot -p$DB_ROOT_PASSWORD urbafix < \
  /var/www/html/database/migrations/004_create_informations_officielles.sql'

# Vérifier
ssh <utilisateur>@<serveur> 'cd /srv/urbafix && \
  docker-compose exec -T db mysql -uroot -p$DB_ROOT_PASSWORD urbafix -e \
  "SHOW TABLES LIKE \"info%\"; SHOW EVENTS;"'

Requêtes utiles¶

Infos publiées dans un rayon¶

SELECT
    i.*,
    m.nom as mairie_nom,
    (
        6371000 * acos(
            LEAST(1, GREATEST(-1,
                cos(radians(:lat)) * cos(radians(i.latitude)) *
                cos(radians(i.longitude) - radians(:lng)) +
                sin(radians(:lat)) * sin(radians(i.latitude))
            ))
        )
    ) AS distance
FROM informations_officielles i
JOIN mairies m ON i.mairie_id = m.id
WHERE i.statut = 'PUBLIE'
AND (i.date_expiration IS NULL OR i.date_expiration > NOW())
HAVING distance <= :rayon
ORDER BY i.date_publication DESC
LIMIT 50;

Statistiques par mairie¶

SELECT
    statut,
    COUNT(*) as total
FROM informations_officielles
WHERE mairie_id = :mairie_id
GROUP BY statut;

Endpoints push dans une zone¶

SELECT endpoint
FROM push_endpoints
WHERE (
    6371000 * acos(
        LEAST(1, GREATEST(-1,
            cos(radians(:lat)) * cos(radians(last_latitude)) *
            cos(radians(last_longitude) - radians(:lng)) +
            sin(radians(:lat)) * sin(radians(last_latitude))
        ))
    )
) <= :rayon
AND notify_travaux = TRUE;  -- Adapter selon type_info

Diagramme relationnel¶

Modèle relationnel du module Informations Officielles