Table d’audit séparée ou simple colonne sur la ligne existante : ce choix d’architecture s’est posé concrètement sur une extension de suivi de dossiers pour une structure d’accompagnement associatif. La solution retenue a été la seconde option, avec une colonne de type JSON qui accumule un tableau d’événements horodatés directement sur la ligne du dossier concerné.
Ce choix d’architecture n’est pas universel : il convient à un historique consulté rarement, en lecture ponctuelle depuis l’écran de détail d’un dossier, jamais agrégé sur l’ensemble de la table pour des statistiques globales. Comprendre cette limite avant de choisir cette structure évite une migration douloureuse plus tard.
Arborescence de la solution
wp_dossiers
├── id (BIGINT UNSIGNED, clé primaire)
├── titre (VARCHAR)
├── statut_courant (VARCHAR)
└── historique (JSON)
└── [
{ "date": "2023-09-20T10:15:00Z", "statut": "ouvert", "auteur_id": 12 },
{ "date": "2023-09-25T14:30:00Z", "statut": "en_cours", "auteur_id": 7 }
]Chaque changement de statut ajoute un élément au tableau JSON stocké dans la colonne historique, plutôt que d’insérer une ligne dans une table séparée dédiée à l’audit. La lecture de l’historique complet d’un dossier ne nécessite alors aucune jointure : une seule ligne contient déjà tout ce qu’il faut afficher.
La structure de la table
La création de la table passe, comme toute structure personnalisée sous WordPress, par dbDelta() :
function dossiers_creer_table() {
global $wpdb;
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
$table = $wpdb->prefix . 'dossiers';
$charset_collate = $wpdb->get_charset_collate();
$sql = "CREATE TABLE $table (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
titre VARCHAR(255) NOT NULL,
statut_courant VARCHAR(30) NOT NULL DEFAULT 'ouvert',
historique JSON NOT NULL,
PRIMARY KEY (id)
) $charset_collate;";
dbDelta( $sql );
}L’ajout d’un événement se fait via la fonction MySQL JSON_ARRAY_APPEND(), qui ajoute un élément à un tableau JSON existant sans avoir à relire puis réécrire l’intégralité de la colonne côté PHP :
$wpdb->query( $wpdb->prepare(
"UPDATE {$wpdb->prefix}dossiers
SET historique = JSON_ARRAY_APPEND(historique, '$', JSON_OBJECT(
'date', %s, 'statut', %s, 'auteur_id', %d
)),
statut_courant = %s
WHERE id = %d",
gmdate( 'c' ), 'en_cours', get_current_user_id(), 'en_cours', $dossier_id
) );Rendre un champ JSON interrogeable
Interroger directement l’intérieur d’une colonne JSON avec WHERE reste possible via JSON_EXTRACT(), mais sans index dédié, MySQL doit analyser chaque ligne pour en extraire la valeur, ce qui redevient un balayage complet dès que la table grossit. Pour un besoin de recherche fréquent, un index généré (generated column) extrait une valeur précise de la structure JSON dans une colonne virtuelle indexable, disponible depuis MySQL 5.7 :
ALTER TABLE wp_dossiers
ADD COLUMN dernier_auteur_id BIGINT UNSIGNED
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(historique, '$[last].auteur_id'))) VIRTUAL,
ADD INDEX idx_dernier_auteur (dernier_auteur_id);Cette colonne virtuelle se recalcule automatiquement à chaque modification de la colonne JSON source, sans espace de stockage supplémentaire en mode VIRTUAL, et devient interrogeable comme n’importe quelle colonne indexée classique.
Quand cette approche devient un problème
| Usage | Colonne JSON adaptée ? |
|---|---|
| Afficher l’historique complet d’un seul dossier | Oui, une lecture suffit |
| Compter le nombre total de changements de statut sur tous les dossiers | Non, nécessite de parcourir chaque JSON |
| Filtrer les dossiers modifiés par un auteur précis, à fort volume | Limité, même avec index généré |
| Générer un tableau de bord statistique sur l’historique | Non, une table d’audit relationnelle devient préférable |
Le repère que je donne pour ce choix d’architecture : une colonne JSON convient tant que l’historique se lit ligne par ligne, jamais quand il doit s’agréger en masse. Le jour où l’agrégation devient un besoin réel, la migration vers une table dédiée redevient nécessaire.
En résumé
Une colonne JSON avec index généré offre une solution légère pour un historique d’événements consulté rarement, en évitant la complexité d’une table d’audit séparée pour un besoin modeste. Cette architecture ne convient pas aux tables à fort volume de lecture agrégée, qui restent hors du périmètre de cette solution.
