Socle V004 – Standard TechDB

Écrit par

dans

28 – Standard de Creation de Table H2

Version : 4.0.1 Date : 2026-01-13 Package : eu.lmvi.socle.techdb

Introduction

Ce document definit le standard de creation des tables dans la base technique H2 du Socle V004. Ce standard garantit la coherence, la tracabilite et la compatibilite PostgreSQL/H2.

Structure de Base

Toutes les tables TechDB doivent suivre cette structure:

Champ	Type	Description
`x_id`	`BIGINT GENERATED BY DEFAULT AS IDENTITY`	Identifiant technique auto-genere
`x_dateCreated`	`TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP`	Date creation
`x_dateChanged`	`TIMESTAMP WITH TIME ZONE`	Date modification (NULL a l’insert, MAJ par appli)
`x_sub`	`VARCHAR(255)`	Sujet/categorie
`x_partition`	`VARCHAR(30)`	Partition logique
`x_comment`	`CLOB`	Commentaires/historiques JSON texte
`[champs metier]`	–	Champs specifiques a la table
`datas`	`CLOB`	Donnees metier JSON texte (toujours en fin)

Regles

Identite

Utiliser GENERATED BY DEFAULT AS IDENTITY, pas AUTO_INCREMENT (MySQL)
Pas de sequence explicite
La colonne x_id est toujours la cle primaire

-- Correct (SQL standard / H2 / PostgreSQL)
x_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

-- Incorrect (MySQL uniquement)
id BIGINT AUTO_INCREMENT PRIMARY KEY

Timestamps

x_dateCreated: Toujours NOT NULL DEFAULT CURRENT_TIMESTAMP
x_dateChanged: NULL a l’insertion, mis a jour par l’application
Utiliser TIMESTAMP WITH TIME ZONE pour la compatibilite

x_dateCreated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
x_dateChanged TIMESTAMP WITH TIME ZONE,

Triggers

Aucun trigger dans H2
L’audit, l’historisation et la mise a jour de x_dateChanged sont geres par l’application

Cle Primaire

Si une cle existante est presente (ex: worker_name), la conserver comme UNIQUE
x_id reste la PK technique

x_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
worker_name VARCHAR(255) NOT NULL UNIQUE,

Conventions de Nommage

Element	Convention	Exemple
Champs techniques	Prefixe `x_`	`x_id`, `x_dateCreated`
Cles etrangeres	`id_<table_cible>`	`id_user`, `id_order`
Champ id existant	Renommer en `x_id`
Donnees JSON	`datas` en derniere position

Permissions

Droits geres au niveau utilisateur H2
Proprietaire = utilisateur createur (socle par defaut)

Contraintes H2 vs PostgreSQL

PostgreSQL	H2
`JSONB`	`CLOB`
Triggers natifs	Logique applicative
Validation JSON DB	Validation applicative
`SERIAL`	`GENERATED BY DEFAULT AS IDENTITY`
Index GIN sur JSON	Non supporte

Exemple DDL Complet

CREATE TABLE IF NOT EXISTS techdb_example (
    -- Champs techniques (standard)
    x_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    x_dateCreated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    x_dateChanged TIMESTAMP WITH TIME ZONE,
    x_sub VARCHAR(255),
    x_partition VARCHAR(30),
    x_comment CLOB,

    -- Champs metier specifiques
    example_key VARCHAR(255) NOT NULL UNIQUE,
    status VARCHAR(50) NOT NULL,
    counter INT DEFAULT 0,
    last_activity TIMESTAMP WITH TIME ZONE,

    -- Donnees JSON (toujours en dernier)
    datas CLOB
);

-- Index recommandes
CREATE INDEX IF NOT EXISTS idx_example_key ON techdb_example(example_key);
CREATE INDEX IF NOT EXISTS idx_example_status ON techdb_example(status);
CREATE INDEX IF NOT EXISTS idx_example_created ON techdb_example(x_dateCreated);

Tables TechDB du Socle

Le Socle V004 definit 5 tables techniques:

techdb_offsets

Stockage des offsets de consommation (Kafka, NATS, etc.)

CREATE TABLE IF NOT EXISTS techdb_offsets (
    x_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    x_dateCreated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    x_dateChanged TIMESTAMP WITH TIME ZONE,
    x_sub VARCHAR(255),
    x_partition VARCHAR(30),
    x_comment CLOB,
    offset_key VARCHAR(255) NOT NULL UNIQUE,
    topic VARCHAR(255) NOT NULL,
    partition_id INT DEFAULT 0,
    offset_value BIGINT NOT NULL,
    consumer_group VARCHAR(255),
    datas CLOB
);

techdb_worker_state

Etat persistant des Workers

CREATE TABLE IF NOT EXISTS techdb_worker_state (
    x_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    x_dateCreated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    x_dateChanged TIMESTAMP WITH TIME ZONE,
    x_sub VARCHAR(255),
    x_partition VARCHAR(30),
    x_comment CLOB,
    worker_name VARCHAR(255) NOT NULL UNIQUE,
    state VARCHAR(50) NOT NULL,
    last_run_at TIMESTAMP WITH TIME ZONE,
    next_run_at TIMESTAMP WITH TIME ZONE,
    error_count INT DEFAULT 0,
    last_error CLOB,
    datas CLOB
);

techdb_events

Evenements techniques

CREATE TABLE IF NOT EXISTS techdb_events (
    x_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    x_dateCreated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    x_dateChanged TIMESTAMP WITH TIME ZONE,
    x_sub VARCHAR(255),
    x_partition VARCHAR(30),
    x_comment CLOB,
    event_type VARCHAR(100) NOT NULL,
    source VARCHAR(255) NOT NULL,
    processed BOOLEAN DEFAULT FALSE,
    processed_at TIMESTAMP WITH TIME ZONE,
    datas CLOB
);

techdb_log_buffer

Buffer de logs pour LogForwarder

CREATE TABLE IF NOT EXISTS techdb_log_buffer (
    x_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    x_dateCreated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    x_dateChanged TIMESTAMP WITH TIME ZONE,
    x_sub VARCHAR(255),
    x_partition VARCHAR(30),
    x_comment CLOB,
    log_level VARCHAR(20) NOT NULL,
    logger_name VARCHAR(255),
    message CLOB NOT NULL,
    thread_name VARCHAR(255),
    log_timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
    forwarded BOOLEAN DEFAULT FALSE,
    forwarded_at TIMESTAMP WITH TIME ZONE,
    datas CLOB
);

techdb_kv

Stockage cle-valeur generique

CREATE TABLE IF NOT EXISTS techdb_kv (
    x_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    x_dateCreated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    x_dateChanged TIMESTAMP WITH TIME ZONE,
    x_sub VARCHAR(255),
    x_partition VARCHAR(30),
    x_comment CLOB,
    kv_key VARCHAR(512) NOT NULL UNIQUE,
    value_type VARCHAR(50) DEFAULT 'string',
    expires_at TIMESTAMP WITH TIME ZONE,
    datas CLOB
);

Bonnes Pratiques

DO

Toujours utiliser le format standard x_ pour les champs techniques
Mettre datas en derniere colonne
Creer des index sur les colonnes frequemment requetees
Utiliser TIMESTAMP WITH TIME ZONE pour tous les timestamps
Documenter le contenu JSON attendu dans datas

DON’T

Ne pas utiliser AUTO_INCREMENT (syntaxe MySQL)
Ne pas creer de triggers (gestion applicative)
Ne pas stocker de BLOBs volumineux (utiliser stockage externe)
Ne pas utiliser JSONB (non supporte par H2)
Ne pas omettre les index sur les colonnes de recherche

Migration depuis l’ancien format

Si vous avez des tables existantes avec l’ancien format:

-- 1. Sauvegarder les donnees
CREATE TABLE techdb_events_backup AS SELECT * FROM techdb_events;

-- 2. Supprimer l'ancienne table
DROP TABLE techdb_events;

-- 3. Recreer avec le nouveau format
-- (voir DDL ci-dessus)

-- 4. Migrer les donnees
INSERT INTO techdb_events (event_type, source, processed, processed_at, datas)
SELECT event_type, source, processed, processed_at, payload
FROM techdb_events_backup;

-- 5. Supprimer la sauvegarde
DROP TABLE techdb_events_backup;

Voir aussi

21-H2-TECHDB.md – Documentation TechDB complete
05-WORKERS.md – Workers TechDB

H2 = dev/tests/outillage – Structure compatible PostgreSQL/H2

Socle V004 – Standard TechDB

Coding Computers