Couche données de Synedre OS

La couche données

Cette page décrit comment le harness agentique de Synedre OS lit et écrit ses données : une base PostgreSQL centrale, un adaptateur qui traduit le SQL legacy MySQL→PG côté interface web, des classes d'entité Python côté outillage agentique, un système de schéma-as-code pour le DDL et les migrations, et les conventions de nommage qui font tenir l'ensemble. Elle s'adresse aux ingénieurs qui reprennent le code.

1. La base de données centrale

Toute la donnée du vaisseau-mère réside dans un seul schéma PostgreSQL, hébergé dans une base dédiée, dans un container Docker isolé.

Piège récurrent : le container Docker et la base de données portent des noms distincts. Les tables du harness vivent dans le schéma privé du vaisseau-mère, pas dans le schéma public.

La connexion nécessite plusieurs paramètres d'environnement : l'hôte, le nom de la base, le schéma cible, l'utilisateur et le mot de passe de base de données. Le mot de passe n'est jamais stocké en clair — il est porté par les fichiers d'environnement du dépôt et une exception est levée s'il est absent.

Frontière harness / multi-tenant

Ce chapitre documente exclusivement le schéma du harness Synedre OS (mono-base, cockpit agentique privé). Les bases de données des tenants CodeMyShop utilisent le même adaptateur (voir §4–5) mais leurs données ne croisent jamais le schéma central : chaque tenant possède sa propre base, son propre schéma, ses propres tables. Toute référence aux tenants dans la suite est contextuelle, non constitutive du harness.

Trois chemins d'accès

Deux chemins coexistent à l'exécution (DML : lire/écrire des lignes) et un au design (DDL : faire évoluer la structure) :

                   ┌─────────────────────────────────────────┐
                   │  PostgreSQL — base centrale              │
                   │  schéma privé du vaisseau-mère           │
                   └───────────────┬─────────────────────────┘
                                   │
   ┌──────────────────┬────────────┴────────────┬────────────────────────┐
   │  DML runtime      │  DML outillage          │  DDL / structure        │
   │ (A) Interface web │ (B) Python agentique    │ (C) Schéma-as-code      │
   │  adaptateur PG    │  classes <entité>.py    │  <outil-migrations>     │
   │  pool TCP         │  exécution SQL isolée   │  generate + migrations  │
   └───────────────────┴─────────────────────────┴────────────────────────┘

• Chemin A (interface web) : un adaptateur PostgreSQL utilise un pool de connexions TCP pour toutes les lectures/écritures de l'interface.
• Chemin B (outillage Python) : les classes d'entité Python accèdent à la base via un mécanisme d'exécution SQL isolé dans le container. Les écritures passent par un fichier SQL temporaire ; les lectures utilisent des requêtes inline. Aucun chemin n'emploie de pipe stdin.
• Chemin C (schéma-as-code) : le DDL est défini en code versionné et fait autorité pour l'évolution structurelle des tables. Il ne manipule pas de lignes de données — uniquement la structure. Détaillé en §6.

Familles de tables — instantané

L'état de la base au moment de la rédaction de ce chapitre se répartit en quatre familles :

Famille	Nombre de tables (approx.)	Périmètre
Tables legacy privées CodeMyShop / mothership	~159	Dette historique à migrer
Tables cockpit Synedre OS	~69	Agents, chantiers, runs, négociation…
Tables PrestaShop natif	~18	Dette historique : produits, catégories, traductions, langues…
Tables PaaS public OSS	~1 (vestige)	La table de file d'attente SEO est vide dans la base centrale ; ses écrivains réels vivent dans les bases tenants. Une ancienne table d'erreurs a été reclassée dans le périmètre cockpit.

S'ajoutent à ces tables 16 vues dans le schéma central (voir §3.4). Point critique : la vue listant les agents est construite sur la table d'agents du cockpit Synedre OS — ce n'est pas une table indépendante.

Note : ces décomptes évoluent à chaque chantier. La requête de vérification à jour est fournie en §7.

2. Les familles de tables (préfixes)

Le préfixe d'une table encode son périmètre et son régime de propriété. Quatre familles coexistent :

Famille	Périmètre	Statut	Exemples de rôles
Tables PaaS public OSS	Core produit + communauté	Cible	FAQ, blocs page d'accueil (dans les bases tenants)
Tables cockpit Synedre OS	Cockpit interne agentique	Cible	Tâches de chantier, agents, runs, négociation
Tables legacy privées CodeMyShop / mothership	Legacy privé	Dette à migrer	Chantiers, cicatrices, inventaire VPS clients
Tables PrestaShop natif	Héritage PrestaShop	Dette historique	Produits, catégories, traductions, langues

Conventions transverses

• Nom singulier pour toutes les tables — aligné sur la convention PrestaShop natif.
• Une table = une entité parente ; pas de table fourre-tout.
• Fichiers source en kebab-case ; composants Vue en PascalCase.

Historique de migration des préfixes

Le préfixe PaaS public OSS résulte d'un rename opéré lors du passage à la version 0.2.0, depuis le préfixe legacy privé (à l'époque où CodeMyShop était une extension PrestaShop). Dans le schéma du vaisseau-mère, la plupart des tables sont restées sous le préfixe legacy — la dette n'a pas encore été soldée — tandis que le préfixe public OSS est appliqué dans les bases tenants.

2.1 Cohabitation des familles au sein d'une même entité logique

Point qui surprend à la reprise : une seule entité logique chantier est répartie sur deux familles de tables, legacy et cockpit.

Rôle de la table	Famille	Description
Table chantier principale	Legacy privé	1 ligne par chantier
Table des travaux granulaires	Legacy privé	N lignes par chantier
Table des tâches atomiques	Cockpit Synedre OS	N lignes par travail
Tables satellites (équipe, verrou multi-session, QA…)	Cockpit Synedre OS	Objets annexes du cockpit

Détail révélateur de l'historique de rename : la table des tâches atomiques a été renommée lors de la migration de préfixe, mais sa séquence de clé primaire et le nom de sa contrainte PRIMARY KEY conservent l'ancien préfixe. Le rename a touché le nom de la table, pas tous les objets dépendants — point d'attention lors de toute introspection ou génération DDL.

Modèle de données — tables principales

Table des chantiers

La table centrale des chantiers porte un identifiant serial en clé primaire. Ses colonnes notables sont les suivantes :

• codename — identifiant kebab-case unique du chantier (varchar 64, obligatoire).
• title — intitulé lisible (varchar 255, obligatoire).
• client_id — référence optionnelle vers un tenant ; NULL indique un chantier interne.
• status — cycle de vie du chantier, valeur par défaut 'planning'.
• priority — niveau de priorité, valeur par défaut 'P2'.
• current_focus, deadline, notes, mission_letter, preprod_test_plan — champs texte libres de pilotage.
• external_contacts — contacts externes liés au chantier.
• ship_command — commande de livraison associée.
• scope — périmètre du chantier ; la contrainte de base de données accepte les valeurs : synedre, codemyshop-oss, codemyshop-enterprise, tenant, business, juridique, negociation, conseil (ou NULL). Cet ensemble est plus large que celui décrit dans certains documents de référence internes, qui ne listent pas les valeurs juridiques et commerciales.
• auto_explode — booléen, active la décomposition automatique en tâches (défaut true).
• mode_auto — booléen, active le pilotage autonome (défaut false).
• max_cost_eur — plafond budgétaire en euros.
• archived_at / archived_by — horodatage et auteur de l'archivage.
• date_add / date_upd — horodatages de création et mise à jour (défaut now()).

Un déclencheur (trigger) s'active après chaque mise à jour du statut : lorsqu'un chantier passe à l'état résolu, il propage automatiquement ce changement vers les éléments de la file de traitement qui lui sont rattachés.

Table des tâches

Chaque tâche est rattachée à un travail via une clé étrangère logique. Les colonnes principales sont :

• title, status (défaut 'todo'), priority (défaut 'P2').
• assignee_codename — codename de l'agent ou de l'automate assigné (varchar 64).
• estimated_tokens / actual_tokens / actual_cost_usd — suivi de la consommation IA.
• recommended_model — modèle IA conseillé pour exécuter la tâche.
• position — ordre dans la liste.
• scope — périmètre de la tâche ; valeurs possibles : synedre-internal, codemyshop-oss, codemyshop-enterprise, tenant-single, tenant-multi, infra, doctrine.
• visual_intent — description de ce qui doit être visible à l'écran après le changement ; NULL indique une tâche non-visuelle. Ce champ alimente le moteur de vérification visuelle automatisée.
• visual_url — URL de vérification du rendu ; NULL renvoie au staging du chantier.

Ces deux colonnes visuelles ont été ajoutées de façon idempotente (ADD COLUMN IF NOT EXISTS). Elles sont reconnues par la couche de persistance côté Python, mais pas encore déclarées dans le schéma TypeScript correspondant — divergence intentionnelle, la migration étant gérée manuellement hors du périmètre de l'outil de détection de dérive schéma/ORM.

Un graphe de dépendances inter-tâches est maintenu dans une table dédiée, permettant d'exprimer qu'une tâche B ne peut démarrer qu'après la complétion de la tâche A.

Journal des cicatrices

Le journal des erreurs et leçons apprises (les cicatrices) enregistre chaque incident ou victoire rencontrés par les agents. Ses colonnes principales sont :

• agent_codename — agent concerné (obligatoire).
• error_type, description, root_cause, corrected_by — anatomie de l'incident.
• severity — niveau de gravité : low, medium, high, critical.
• kind — nature de l'entrée : 'failure' par défaut, ou 'victory' enregistré via la compétence dédiée.
• resolved — état de résolution (0, 1 ou 2).
• tags — tableau de tags libres.
• importance — score de 1 à 10.
• recall_count — nombre de fois où la cicatrice a été rappelée en contexte.
• learnable — booléen indiquant si la leçon peut être extraite pour entraîner de futurs comportements.

Registre des agents : table physique et vues de compatibilité

Point d'attention pour toute reprise. La table physique des agents est distincte de la vue du même nom exposée aux couches applicatives historiques. Cette vue est un shim de rétrocompatibilité : elle redirige en lecture vers la table physique sous-jacente, de façon totalement transparente pour le code qui l'interroge.

La table physique contient les colonnes codename, nickname, role, group_name, active, des champs de configuration de tâche (job_*), un cadre cognitif (cognitive_frame) et un champ d'héritage (heritage).

Ce mécanisme de vue-shim est généralisé à une douzaine de paires : chaque ancienne référence applicative pointe vers sa table physique réelle sans modification du code appelant. Au total, la base compte 16 vues, dont :

• 12 vues-shim de rétrocompatibilité couvrant les agents, automates, activité, heartbeat, relations, expérience, conduites, logs et smart-automation.
• Une vue de suivi des événements de spawn d'Atlas.
• Trois vues analytiques opérationnelles :
  • Vue d'agrégation des cicatrices — agrège les 14 derniers jours par tenant, jour, sévérité et nature (échec/victoire), avec un comptage par groupe.
  • Vue de suivi des rollbacks — sur les 14 derniers jours, par tenant : nombre total de runs, nombre de rollbacks, taux de rollback en pourcentage, nombre de verdicts bloquants, date du dernier run.
  • Vue de fréquence des avertissements — dénormalise sur 30 jours le tableau des codes d'avertissement issus des runs de revue, avec leur fréquence, le nombre de verdicts bloquants associés et la date de dernière occurrence.

Vue de consolidation des erreurs. Une vue unifiée d'erreurs (union des erreurs front-end et des erreurs serveur) est déclarée dans le manifeste du module de tracking d'erreurs enterprise et interrogée par l'API. Elle n'existe cependant pas dans l'environnement du vaisseau-mère faute que la table d'erreurs front-end y soit instanciée — seule la table d'erreurs serveur y est présente. La migration correspondante n'a donc pas pu la recréer dans ce contexte. Le décompte réel de vues dans cet environnement est bien de 16, et non 17. Cette vue existe en revanche dans les environnements produits dédiés où les deux sources de données sont disponibles. Une vue PostgreSQL référençant une table par OID (et non par nom), elle a automatiquement suivi les renommages sans intervention manuelle.

Écriture via les vues-shim. Ces vues sont vraisemblablement en lecture seule. Toute opération d'écriture (INSERT, UPDATE) doit cibler la table physique sous-jacente, et non la vue. À vérifier systématiquement avant toute modification du registre des agents.

Accès base de données en runtime — architecture multi-tenant

Résolution du tenant et sélection de l'adaptateur

Le système de routage de base de données résout dynamiquement le tenant à chaque requête entrante. Le mécanisme fonctionne en deux étapes :

1. Résolution du tenant — trois stratégies sont tentées dans l'ordre :
   1. La configuration d'exécution du processus Nuxt (runtimeConfig.clientId) — chaque VPS tenant définit cette valeur statiquement dans son environnement.
   2. La correspondance du hostname de la requête contre une table de configuration interne — ce chemin permet de servir plusieurs tenants depuis un seul déploiement Nuxt.
   3. Valeur de repli : l'identifiant du vaisseau-mère lui-même.
2. Sélection de l'adaptateur — une fois le tenant identifié, le système vérifie s'il est autorisé à accéder à PostgreSQL (voir la porte globale ci-dessous). Si oui, un adaptateur PostgreSQL est instancié pour ce tenant. Sinon, une exception est levée : le chemin d'accès MySQL/MariaDB a été définitivement supprimé lors d'une phase de migration antérieure.

Porte d'activation PostgreSQL globale

Point critique pour tout opérateur. Un drapeau d'environnement global commande l'accès PostgreSQL pour l'ensemble des tenants, y compris les tenants internes du vaisseau-mère. Si la variable d'environnement correspondante ne contient pas le caractère générique *, aucun tenant — même un tenant interne — ne peut accéder à PostgreSQL. Le système lève une exception pour toutes les requêtes DB.

Ce mécanisme est un héritage de la phase d'activation progressive (opt-in domaine par domaine), où la variable listait explicitement les modules activés. Le basculement global a été effectué en production (valeur *). Un opérateur qui vide cette variable déclenche une coupure totale de l'accès PostgreSQL sans autre avertissement.

Configuration des connexions par tenant

La table de configuration des connexions est construite dynamiquement au démarrage à partir de variables d'environnement suivant la convention NUXT_TENANT_DB_<IDENTIFIANT>, où l'identifiant du tenant est transcrit en majuscules avec les tirets remplacés par des underscores. La valeur de chaque variable suit le format :

<base-de-données>[,<hôte>[,<port>[,<utilisateur>[,<mot-de-passe>

Une fonction utilitaire expose la liste des identifiants de tenant connus, utilisée notamment pour propager des secrets globaux vers chaque base de données tenant lors des opérations de maintenance.

La convention « un tenant = un codename canonique » est déclinée de façon cohérente sur l'ensemble des surfaces du système (configuration d'environnement, résolution de hostname, routage applicatif, etc.). La fonction de résolution du tenant décrite ci-dessus est l'une de ces surfaces et doit toujours retourner ce codename canonique.

L'adaptateur base de données : couche de compatibilité SQL

Ce composant expose une interface unifiée query / get / run — identique à l'ancienne couche MySQL — mais dispatch en interne vers le driver PostgreSQL, en convertissant à la volée le SQL MySQL hérité. Il constitue la pièce centrale de la migration de moteur de persistance sans réécriture des callers.

Pool de connexions

Au premier appel, un singleton de pool est instancié avec les paramètres suivants :

• Hôte, port et base cibles lus depuis des variables d'environnement dédiées ; le mot de passe de base de données est obligatoire — le processus lève une exception immédiate s'il est absent.
• Taille maximale du pool : 20 connexions simultanées.
• Timeout d'inactivité : 60 s ; durée de vie maximale d'une connexion : 1 800 s ; timeout de connexion : 15 s.
• Toutes les tables sont automatiquement qualifiées par le schéma PostgreSQL cible (voir ci-dessous).

Pipeline de traduction MySQL → PostgreSQL

Chaque requête SQL passe par un pipeline de réécriture appliqué dans l'ordre suivant :

#	Transformation	Détail
1	Backticks → guillemets doubles	`col` devient "col" (syntaxe identifiants PG).
2	Qualification de schéma automatique	Après FROM, JOIN, INTO, UPDATE, TABLE : les préfixes de tables du catalogue e-commerce et du hub sont préfixés par le nom de schéma PostgreSQL cible.
2b/2c	DATE_SUB / DATE_ADD + INTERVAL N UNIT	Réécrits en arithmétique de date PostgreSQL ; supporte DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, littéraux et placeholders.
2d	TIMESTAMPDIFF(unit, a, b)	Converti en FLOOR(EXTRACT(EPOCH FROM (b-a)) / diviseur) pour SECOND, MINUTE, HOUR, DAY.
—	IFNULL(a, b) → COALESCE(a, b)	PostgreSQL ne connaît pas IFNULL.
—	INSERT IGNORE INTO …	Réécrit en INSERT … ON CONFLICT DO NOTHING (sauf si la clause ON CONFLICT est déjà présente).
—	Auto-quotation des alias AS fooBar	Devient AS "fooBar" pour préserver la casse (PostgreSQL met en minuscules les identifiants non quotés). Exception : les types natifs PostgreSQL (TEXT, INTEGER, etc.) sont laissés tels quels pour ne pas casser les expressions CAST(x AS TEXT).
3	Placeholders ? → $1, $2, …	Conversion positionnelle par un parseur caractère-par-caractère qui ignore les ? à l'intérieur de chaînes quotées.

Important — séparation réécriture / binding : la transformation des placeholders se limite à renommer les marqueurs dans le texte SQL (? → $N). Le binding réel des valeurs est délégué au driver PostgreSQL, qui reçoit le tableau de paramètres inchangé via son API paramétrée. Aucune valeur n'est interpolée dans la chaîne SQL : il n'y a donc pas de risque d'injection SQL lié à cette couche de traduction.

Fonctions SQL non prises en charge automatiquement

Les constructions suivantes ne sont pas réécrites automatiquement et doivent être portées manuellement côté appelant, via une branche spécifique PostgreSQL dans l'endpoint concerné :

• ON DUPLICATE KEY UPDATE
• LAST_INSERT_ID()
• GROUP_CONCAT
• FIND_IN_SET
• DATE_FORMAT
• CURDATE()

Interface unifiée et émulation de l'identifiant d'insertion

L'adaptateur expose trois méthodes :

• query<T>(sql, params?) — retourne un tableau de résultats typé.
• get<T>(sql, params?) — retourne le premier résultat ou null.
• run(sql, params?) — retourne { affectedRows, insertId }, émulant le comportement MySQL.

La méthode run() émule l'insertId MySQL : pour un INSERT simple sans clause RETURNING ni ON CONFLICT, elle injecte automatiquement RETURNING id_<entité> (convention de nommage des clés primaires du catalogue : la colonne suit le nom de la table sans son préfixe).

Ce mécanisme est désactivé pour les tables à clé primaire composite, qui n'ont pas de colonne id_<table> unique. Sont exclues : plusieurs tables de relation du catalogue (groupes clients, catégories-produits, zones transporteurs, accessoires, croisements de catégories), ainsi que toute table dont le suffixe indique une déclinaison par langue ou par boutique.

Database Adapter: SQL Compatibility Layer

This component exposes a unified query / get / run interface — identical to the former MySQL layer — while dispatching internally to the PostgreSQL driver, converting legacy MySQL SQL on the fly. It is the central piece enabling engine migration without rewriting callers.

Connection Pool

On first call, a singleton pool is instantiated with the following parameters:

• Host, port and target database read from dedicated environment variables; the database password is mandatory — the process throws immediately if it is absent.
• Maximum pool size: 20 simultaneous connections.
• Idle timeout: 60 s; maximum connection lifetime: 1 800 s; connection timeout: 15 s.
• All tables are automatically schema-qualified for the target PostgreSQL schema (see below).

MySQL → PostgreSQL Translation Pipeline

Every SQL query passes through a rewrite pipeline applied in the following order:

#	Transformation	Detail
1	Backticks → double quotes	`col` becomes "col" (PG identifier syntax).
2	Automatic schema qualification	After FROM, JOIN, INTO, UPDATE, TABLE: table prefixes from the e-commerce catalogue and hub are prefixed with the target PostgreSQL schema name.
2b/2c	DATE_SUB / DATE_ADD + INTERVAL N UNIT	Rewritten as PostgreSQL date arithmetic; supports DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, both literals and placeholders.
2d	TIMESTAMPDIFF(unit, a, b)	Converted to FLOOR(EXTRACT(EPOCH FROM (b-a)) / divisor) for SECOND, MINUTE, HOUR, DAY.
—	IFNULL(a, b) → COALESCE(a, b)	PostgreSQL does not support IFNULL.
—	INSERT IGNORE INTO …	Rewritten as INSERT … ON CONFLICT DO NOTHING (unless an ON CONFLICT clause is already present).
—	Auto-quoting of aliases AS fooBar	Becomes AS "fooBar" to preserve case (PostgreSQL lowercases unquoted identifiers). Exception: native PostgreSQL types (TEXT, INTEGER, etc.) are left as-is to avoid breaking CAST(x AS TEXT) expressions.
3	Placeholders ? → $1, $2, …	Positional conversion via a character-by-character parser that ignores ? inside quoted strings.

Important — rewrite vs. binding separation: placeholder transformation only renames markers in the SQL text (? → $N). Actual value binding is delegated to the PostgreSQL driver, which receives the parameter array unchanged through its parameterised API. No value is interpolated into the SQL string: this translation layer therefore introduces no SQL injection risk.

SQL Functions Not Handled Automatically

The following constructs are not automatically rewritten and must be ported manually on the caller side, via a dedicated PostgreSQL branch in the relevant endpoint:

• ON DUPLICATE KEY UPDATE
• LAST_INSERT_ID()
• GROUP_CONCAT
• FIND_IN_SET
• DATE_FORMAT
• CURDATE()

Unified Interface and Insert ID Emulation

The adapter exposes three methods:

• query<T>(sql, params?) — returns a typed result array.
• get<T>(sql, params?) — returns the first result or null.
• run(sql, params?) — returns { affectedRows, insertId }, emulating MySQL behaviour.

The run() method emulates MySQL's insertId: for a simple INSERT without a RETURNING or ON CONFLICT clause, it automatically appends RETURNING id_<entity> (catalogue primary key naming convention: the column follows the table name stripped of its prefix).

This mechanism is disabled for tables with a composite primary key, which do not have a unique id_<table> column. Excluded tables include several catalogue relation tables (customer groups, category–product links, carrier zones, accessories, category cross-references), as well as any table whose suffix indicates a per-language or per-shop variant.

Drizzle ORM — le schéma comme source de vérité DDL

Les deux chemins d'accès aux données décrits précédemment (côté interface Nuxt et côté outillage Python) opèrent exclusivement en DML : ils lisent et écrivent des lignes dans une structure de table supposée déjà en place. Aucun des deux ne crée ni n'altère de table. La structure — création de tables, colonnes, types, index, contraintes — est régie par un troisième chemin : Drizzle ORM, utilisé en mode schema-as-code.

Les schémas TypeScript sont la source de vérité déclarative de la structure des tables ; le SQL CREATE/ALTER est généré ou écrit à la main, puis appliqué à la base de données, jamais l'inverse.

Le dialecte était MariaDB avant une migration structurelle réalisée en avril 2026 ; il est désormais postgresql. Les dépendances sont drizzle-orm et drizzle-kit dans leurs versions mineures courantes.

Configuration centrale

Le fichier de configuration à la racine du dépôt définit les paramètres suivants :

Clé	Valeur	Note
dialect	'postgresql'	Remplace l'ancien dialecte MariaDB
schema	3 globs couvrant le cœur OSS, les modules du cockpit et les packs enterprise	Emplacements des déclarations TypeScript
out	Dossier des migrations SQL générées (cœur OSS)	Fichiers écrits ou générés par drizzle-kit
schemaFilter	Restreint l'introspection au schéma applicatif propre	Ignore les schémas natifs PostgreSQL
Credentials DB	Hôte, port, utilisateur, mot de passe, nom de base — portés par variables d'environnement	Jamais écrits en clair dans le dépôt
strict / verbose	true / true	Confirmation avant push ; sortie détaillée

Note de port : la configuration Drizzle pointe par défaut un port d'exposition TCP différent du port interne utilisé par les adaptateurs applicatifs. Drizzle-kit est conçu pour cibler une base exposée en TCP depuis la machine hôte, pas pour passer par l'exécution directe dans un conteneur.

Commandes disponibles

Aucun script npm dédié n'est exposé dans le manifeste du projet ; les commandes se lancent directement via npx :

Commande	Effet
npx drizzle-kit generate	Compare les schémas TypeScript à l'état connu et écrit une nouvelle migration SQL
npx drizzle-kit migrate	Applique les migrations en attente à la base configurée
npx drizzle-kit introspect	Effectue l'ingénierie inverse d'une base existante pour en produire les fichiers TypeScript

Organisation des schémas TypeScript

Chaque fichier de schéma déclare une ou plusieurs tables via les primitives Drizzle (pgSchema(...).table(...)). Les déclarations sont réparties en trois périmètres correspondant aux trois globs de configuration :

Périmètre	Contenu	Couvert par l'audit de dérive
Cœur OSS	Tables du socle applicatif (environ 85 fichiers, dont des re-exports vers la base runtime)	✅ Oui
Modules du cockpit	Tables du cockpit : agents, chantiers, cicatrices, facturation, exercices… (environ 47 fichiers)	❌ Non
Packs enterprise	Extensions métier (environ 14 fichiers)	❌ Non

Point d'attention : un fichier de schéma décrivant le registre de modules existe dans le dépôt mais se trouve hors des trois globs actifs. Il sert de référence de typage et d'illustration, pas d'entrée DDL effective. De même, les tables du cockpit déclarées dans les modules (périmètre 2) suivent une voie de migration séparée (voir ci-dessous) et ne déclenchent pas d'alerte de dérive bloquante — ce qui explique que des colonnes ajoutées récemment (par exemple pour stocker l'intention visuelle d'une tâche) puissent coexister dans la base et dans les entités métier sans figurer dans l'audit cœur OSS.

Ce que les schémas TypeScript expriment : nom physique de colonne, type PostgreSQL, contrainte NOT NULL, valeur par défaut, clé primaire simple ou composite, unicité, index. Les types métier sont affinés via $type<...>() pour le typage TypeScript sans contrainte côté base. Exemple illustratif d'une table de registre de modules :

export const appSchema = pgSchema('<nom-schema>')
export type Runtime      = 'ps' | 'nuxt'
export type ModuleStatus = 'active' | 'disabled' | 'deprecated'

export const moduleRegistry = appSchema.table('<table-registre-modules>', {
  id:       serial('id').primaryKey(),
  codename: varchar('codename', { length: 128 }).notNull().unique(),
  version:  varchar('version',  { length: 32 }).notNull(),
  runtime:  varchar('runtime',  { length: 4 }).$type<Runtime>().notNull().default('ps'),
  status:   varchar('status',   { length: 10 }).$type<ModuleStatus>().notNull().default('active'),
  manifest: text('manifest').$type<ModuleManifest | null>(),
  // … hash de schéma, date de dernière migration, timestamps
}, (t) => ({
  idxRuntimeStatus: index('idx_runtime_status').on(t.runtime, t.status)
}))

export type Row    = typeof moduleRegistry.$inferSelect
export type Insert = typeof moduleRegistry.$inferInsert

Points clés pour la maintenance :

• Les types ENUM MariaDB ont été portés en varchar(N) + $type<Union>() : la contrainte est appliquée au niveau TypeScript, sans type ENUM PostgreSQL natif.
• Les tables à clé primaire composite (par exemple les tables de traduction) utilisent primaryKey({ columns: [t.idEntite, t.idLang] }).
• Les types $inferSelect/$inferInsert exportés sont consommables côté code applicatif ; le runtime Nuxt passe néanmoins par l'adaptateur bas niveau (§ précédent), pas par le query-builder Drizzle. Drizzle ne sert ici qu'au DDL et au typage statique.

Les migrations SQL et leur application

Migrations cœur OSS

Le dossier de migrations du cœur OSS contient à ce jour neuf migrations numérotées séquentiellement, couvrant des évolutions telles qu'un mapping d'import, l'ajout d'un champ téléphone pour l'inscription B2B/C, des alertes de stock, la gestion des contextes de méga-menu, des liens d'en-tête, et un fichier daté pour le contexte d'en-tête de méga-menu. Un fichier de journal (meta/_journal.json) trace les migrations connues.

Fait structurant : le journal de drizzle-kit contient une liste d'entrées vide. Le tracking automatique de drizzle-kit migrate n'est donc pas la voie opérante. Les fichiers SQL sont en réalité écrits à la main de façon idempotente (CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS) et appliqués manuellement par client via une connexion directe à chaque base. En pratique : les schémas TypeScript constituent la source de vérité déclarative ; l'application réelle reste un psql par client, jamais propagée automatiquement seule.

Migrations du cockpit (voie séparée)

Les tables du cockpit — celles déclarées dans les modules (périmètre 2) et non auditées par le détecteur de dérive — utilisent un système de migrations SQL manuelles séparé, réparti sur deux emplacements dans le dépôt :

• Migrations exclusives au cockpit (synedre.com, Odyssée, documentation, routage IA…) : nommées par date et sujet, toutes idempotentes. Exemples récents : ajout des colonnes d'intention visuelle sur les tâches, création de la table de routage IA avec son seed initial, table de revue externe de documentation, miroir public de documentation.
• Migrations appliquées directement à la base locale : tables du cockpit hors périmètre précédent (réflexes, paliers de réflexes, portée des automates). Ce dossier accueille également des sous-dossiers d'archivage (applied/ et _applied/) des migrations déjà exécutées sur la cible principale.

L'application est dans les deux cas manuelle via une connexion directe à la base concernée. Ces migrations ne transitent pas par la fonction d'application automatique de dérive ni par drizzle-kit migrate.

Le détecteur de dérive DDL

Comme l'application des migrations est manuelle et multi-client, un ALTER appliqué sur la base principale mais oublié sur la base d'un client partirait en production silencieusement. Un outil d'audit dédié ferme ce trou.

Fonctionnement

L'outil d'audit opère en trois étapes :

1. Parsing des schémas TypeScript — lit les fichiers du périmètre cœur OSS et extrait, par expression régulière, la liste des tables et colonnes déclarées (après suppression des commentaires bloc).
2. Lecture de la structure live — interroge information_schema.columns de chaque client ciblé, filtré sur le schéma applicatif et les familles de tables concernées, via une connexion directe à chaque base.
3. Comparaison et rapport — identifie les écarts : bloquant = table ou colonne déclarée en TypeScript mais absente de PostgreSQL (migration manquante) ; informatif = présent en base mais absent du schéma TypeScript.

Les faux positifs sont filtrés par un fichier d'ignorés versionné qui distingue deux catégories : les tables présentes uniquement sur le cockpit central (à ignorer chez les clients) et les tables du socle boutique OSS retirées de la base cockpit lors d'un chantier de découplage (à ignorer uniquement sur cette cible).

Modes d'exécution :

# Audit du client courant (rétro-compat)
python3 <outil-audit-derive>

# Audit d'un client spécifique
python3 <outil-audit-derive> --tenant <ID>

# Audit de tous les clients
python3 <outil-audit-derive> --all --verbose

Codes de sortie : 0 aucune dérive bloquante, 1 dérive bloquante détectée, 2 erreur d'exécution ou de connexion.

Intégration dans le pipeline de déploiement

L'audit est câblé de façon bloquante dans le pipeline de déploiement : avant de pousser un artefact, le pipeline vérifie que la structure de la base du client cible correspond aux schémas TypeScript. Si une dérive est détectée, le déploiement s'arrête immédiatement. Il n'existe pas de cron périodique dédié — l'audit est un contrôle pré-déploiement.

Application automatique de la dérive DDL

Un outil complémentaire corrige la dérive au lieu de seulement la signaler. Il partage avec l'outil d'audit la définition des clients cibles et la logique de parsing des schémas TypeScript, afin de maintenir une source de vérité unique.

Principes fondamentaux

• Jamais de DROP — ni de table, ni de colonne. On n'ajoute que ce qui manque.
• Idempotent — rejouable sans effet si la base est déjà à jour.
• Transaction unique — BEGIN … COMMIT avec arrêt immédiat en cas d'erreur : rollback total si une instruction échoue.
• Génère uniquement ADD COLUMN IF NOT EXISTS et CREATE TABLE IF NOT EXISTS, avec types, contraintes NOT NULL/DEFAULT et vérifications dérivés des schémas TypeScript.

Pipeline interne

1. Parsing approfondi des schémas TypeScript — produit des structures typées décrivant chaque table et colonne (type PostgreSQL, nullable, valeur par défaut, index). Un correctif important a été apporté suite à un incident de production : un index Drizzle multi-colonnes avec virgule finale produisait un jeton vide après découpage, générant un identifiant de longueur nulle rejeté par PostgreSQL, ce qui provoquait un rollback automatique et annulait le déploiement. Le correctif filtre les jetons vides et écarte tout index dont la liste de colonnes est vide après nettoyage.
2. Calcul de la dérive — différence entre l'état TypeScript et l'état live : tables absentes → CREATE TABLE ; colonnes absentes → ADD COLUMN.
3. Génération du SQL idempotent — bloc SQL prêt à être appliqué.
4. Application en transaction — exécutée sur le client cible uniquement en mode --apply.

Modes d'usage

Commande	Effet
python3 <outil-application-derive> --tenant <ID> --dry-run	Affiche le SQL sans l'exécuter (mode par défaut)
python3 <outil-application-derive> --tenant <ID> --apply	Applique en transaction
python3 <outil-application-derive> --all --dry-run	Inspecte tous les clients

Codes de sortie : 0 aucune dérive ou application réussie ; 1 dérive détectée en dry-run ou erreur d'application ; 2 erreur de parsing ou de connexion.

Câblage dans le déploiement unifié

L'application automatique est activée par défaut (DRIFT_AUTO_APPLY=1) depuis un correctif de juin 2026 qui a inversé la valeur par défaut précédente. L'opt-in effectif reste la présence d'une clé drift: dans le fichier de configuration du client (deploy.yaml) : si elle est absente, la fonction d'application n'est jamais appelée.

Les anciens scripts de déploiement par client ont été supprimés lors d'un chantier de consolidation (suite à un incident où une dérive silencieuse avait provoqué des erreurs 500 sur l'ensemble d'une vitrine) : tous les clients empruntent désormais le même chemin de déploiement unifié, qui porte les deux fonctions — audit bloquant puis application automatique.

Séquence complète du pipeline de déploiement (ordre d'exécution) :

1. Vérification de dérive DDL (bloquante)
2. Application automatique de la dérive DDL
3. Hooks d'arrière-plan
4. Génération des routes i18n depuis la base
5. Build Nuxt
6. Seed i18n
7. Source maps
8. Packaging de l'artefact
9. Push git en arrière-plan
10. Upload de l'artefact
11. Rechargement distant (PM2 graceful ou Docker)
12. Installation du cron client sur le VPS (optionnel, si déclaré dans la configuration)
13. Attente des hooks d'arrière-plan
14. Vérification de santé
15. Préchauffage du cache SSR en arrière-plan (non bloquant, après la vérification de santé ; désactivable via variable d'environnement)
16. Bannière de fin

Qui fait foi pour l'évolution structurelle

schema TypeScript (cœur OSS)   ← SOURCE DE VÉRITÉ déclarative
   │  (1) on édite le TypeScript
   ▼
Migration SQL idempotente
   │  (2a) application manuelle sur chaque client impacté
   │  (2b) application automatique via le pipeline de déploiement
   ▼
PostgreSQL live (base principale + bases clients)
   ▲
   └─ (3) audit de dérive vérifie TypeScript == live,
          bloque le déploiement si écart détecté

Règle de maintenance : pour faire évoluer la structure d'une table du socle, on édite d'abord le schéma TypeScript, on génère ou écrit la migration idempotente, on l'applique sur chaque base concernée (voie manuelle ou automatique), puis on relance l'audit — le pipeline le rejoue de toute façon au prochain déploiement. Ne jamais modifier la base live sans répercuter le TypeScript : l'audit le signalerait et bloquerait le prochain déploiement.

Inversement, les entités métier côté outillage Python ne créent jamais de colonne : leur liste de champs est une liste blanche de colonnes supposées déjà présentes en base, pas une déclaration de structure.

Accès agentique depuis Python — le patron Entity

La couche Python du cockpit s'appuie sur une hiérarchie d'une quarantaine de classes héritant toutes d'une classe de base commune. Chaque sous-classe représente un concept métier — chantier, travail, tâche, agent, doctrine, facturation — et expose un CRUD générique que les règles métier de la sous-classe viennent affiner.

Transport vers la base de données

Contrairement à la façade Nuxt qui ouvre un pool TCP vers la base, la couche Python dialogue avec la base via des appels système à l'interface en ligne de commande de PostgreSQL, exécutés dans le container de base de données. Trois helpers assurent ce transport :

Helper	Usage	Note
Écriture par fichier temporaire	Toutes les écritures SQL	Le SQL est écrit dans un fichier temporaire à nom unique (PID + UUID) pour éviter les collisions entre processus concurrents, copié dans le container, puis exécuté avec arrêt immédiat sur erreur.
Lecture tabulaire	Requêtes SELECT dont les colonnes ne contiennent pas de sauts de ligne	SQL transmis directement en ligne de commande ; la sortie est séparée par des tabulations.
Lecture CSV	Requêtes SELECT sur des colonnes de type texte long	Même mécanisme, mode CSV (RFC 4180) ; les valeurs NULL sont normalisées en chaîne vide.

Tous les helpers préfixent le SQL d'une directive de chemin de recherche qui pointe vers le schéma principal de la base. Les paramètres de connexion (container cible, nom de base, utilisateur, mot de passe, schéma) sont lus depuis des variables d'environnement au démarrage de la couche Python.

La classe de base Entity

Le CRUD générique est paramétré par trois attributs de classe :

• La table cible : nom de la table PostgreSQL dans le schéma principal.
• La clé primaire : nom de la colonne PK de cette table.
• La liste blanche de colonnes : seules ces colonnes sont acceptées en INSERT et UPDATE, ce qui prévient toute injection de colonne non prévue.

Le comportement des méthodes principales :

• create(data) : valide la donnée, force la valeur du champ client_id si la colonne est dans la liste blanche, filtre sur la liste blanche, puis exécute un INSERT … RETURNING <pk> avec horodatage automatique des colonnes date_add et date_upd.
• update(pk, data) : filtre sur la liste blanche, met à jour date_upd.
• find / find_one / exists / delete : helpers de lecture et de suppression.

L'échappement SQL interne gère les booléens Python en TRUE/FALSE SQL, et sérialise les dictionnaires et listes en JSON avant insertion. Les sous-classes surchargent la méthode validate(data, mode) pour ajouter leurs règles métier : les violations bloquantes lèvent une ValidationError ; les avertissements non bloquants sont collectés et remontés à l'appelant.

Création atomique d'un chantier avec squelette

La méthode de création atomique du gestionnaire de chantiers garantit qu'aucun chantier orphelin ne peut exister en base : chantier, premier travail et au moins une tâche sont insérés en une seule transaction.

ChantierEntity().create_with_skeleton(
    codename, title,
    first_travail={
        "codename": "<codename-travail>",
        "title": "<titre-travail>",
        # priority, current_phase, ...
    },
    first_taches=[                        # doctrine v3 — recommandé
        {
            "title": "<titre-tâche>",
            "assignee_codename": "<codename-agent>",
            # priority, estimated_tokens, scope, ...
        }
    ],
    first_tache=None,                     # singulier — rétrocompatibilité
    client_id=None,
    priority="P2",
    scope=None,
    current_focus=None,
    notes=None,
)
# Retourne : {id_chantier, id_travail, id_tache, id_taches, codenames, warnings}

Les champs suivants sont propagés en transparence à chaque tâche du squelette : priority, description, estimated_tokens, estimated_h, position, recommended_model, scope, visual_intent, visual_url.

Validations bloquantes

• Le codename doit respecter la forme kebab-case (4 à 64 caractères) et être unique en base.
• La priorité doit appartenir à {P0, P1, P2, P3}.
• Chaque tâche doit avoir un titre et un codename d'assignee non vides ; l'agent désigné doit exister dans le registre des agents actifs.
• Recrutement multi-agents : lorsque le champ scope indique un périmètre de type « tenant », la méthode exige au minimum deux agents distincts assignés. Cette règle prévient le travail en silo sur les chantiers multi-domaines.

Avertissements non bloquants

• L'absence de champ estimated_tokens sur une tâche génère un avertissement mais ne bloque pas la création.

Déroulement transactionnel

La méthode ouvre une transaction unique (BEGIN … COMMIT). Elle insère d'abord le chantier, puis le travail (en retrouvant l'identifiant du chantier par codename), puis chaque tâche (en retrouvant l'identifiant du travail). L'option d'arrêt immédiat sur erreur garantit le rollback complet si l'une des insertions échoue. Lorsque le champ recommended_model est absent d'une tâche, il est calculé automatiquement par l'heuristique de recommandation de modèle.

Gestion des tâches — estimation, modèle, compétences et outils

Création et estimation automatique

La surcharge de create() dans le gestionnaire de tâches complète automatiquement l'estimation de tokens si elle est absente, en appelant un estimateur dédié. Elle valide également que la valeur du champ scope appartient à l'ensemble des valeurs autorisées, et émet un avertissement non bloquant si l'agent assigné ne fait pas partie de l'équipe de production du chantier.

Heuristique de recommandation de modèle

La méthode recommend_model_for(tokens, priority, fail_recurrent) sélectionne automatiquement le modèle IA le plus adapté à une tâche :

• Modèle le plus puissant si la priorité est P0, si la tâche a accumulé au moins deux itérations en échec, ou si le volume estimé dépasse 8 000 tokens.
• Modèle intermédiaire si le volume dépasse 1 500 tokens.
• Modèle léger dans tous les autres cas.

Rattachement de compétences et d'outils

Le rattachement d'une compétence à une tâche effectue une recherche dans le référentiel de compétences par nom (clé naturelle), puis insère le lien en ignorant silencieusement les doublons. Si la compétence est inconnue du référentiel, elle est enregistrée dans une table de propositions en attente de validation, et la méthode retourne False pour signaler l'échec partiel.

Le rattachement d'un outil suit le même pattern : recherche par slug dans le référentiel d'outils, insertion du lien, et en cas d'outil inconnu, création d'une proposition en attente.

Cascades automatiques de statut

Les mises à jour de statut des tâches et des travaux déclenchent des cascades automatiques qui propagent l'avancement vers les niveaux supérieurs de la hiérarchie :

Tâche → done / cancelled
  (toutes les tâches du travail sont dans un état terminal, dont ≥1 done)
   └─ Le moteur de cascade vérifie si une équipe QA est recrutée sur le chantier
        ├─ OUI : le verdict QA est appliqué avant de clore le travail
        └─ NON : le travail passe directement à l'état 'done'

Travail → done / cancelled
  (tous les travaux du chantier sont dans un état terminal, dont ≥1 done)
   └─ Le moteur de cascade évalue le contexte
        ├─ Tous les travaux terminés sont en phase 'discovery'
        │     → auto-éclatement : un LLM génère les travaux d'implémentation
        │       au lieu de promouvoir le chantier
        ├─ Sinon → chantier passe à l'état 'test' (preprod, en attente de revue)
        │           la cascade s'arrête à ce cran
        └─ Avertissement non bloquant si le plan de test preprod
              ou la commande de mise en production sont absents

Un mécanisme complémentaire gère les travaux de résolution : lorsqu'un travail subsidiaire marqué comme résolvant un travail parent passe à l'état done, le travail parent en attente est automatiquement clos, ses tâches non démarrées sont annulées, et la décision est consignée dans le journal d'audit du travail.

Conventions d'internationalisation et polymorphisme

Tables de traduction (_lang)

Tout texte visible par un visiteur final est stocké dans une table sœur suffixée _lang, jamais dans la table parente. Les règles de nommage sont strictes :

• Le suffixe est exactement _lang — les variantes _translation, _i18n ou _locale sont interdites.
• La clé primaire est composite : (id_<entité>, id_lang), sans auto-incrément. Pour les contextes multi-boutique, elle s'étend à (id_<entité>, id_lang, id_shop) — aucune table _shop_lang séparée n'est créée.
• La séparation des responsabilités est stricte : la table parente porte les clés étrangères, les flags, les dates et les énumérations ; la table _lang porte les champs texte (title, description, meta_*) et tout contenu visible.

Cette convention a un impact direct sur la couche d'adaptation base de données : les tables _lang sont exclues de l'heuristique d'identification de clé primaire retournée, car elles ne possèdent pas de PK simple. Côté interface, les chaînes de caractères transitent par la fonction de traduction t('domaine.clé', 'fallback') qui lit la table de traductions centrale ; toute chaîne codée en dur dans le code source est une violation P0.

Polymorphisme par type parent

Lorsqu'une fonctionnalité s'applique à plusieurs types d'entités parentes — par exemple une FAQ attachable à une page CMS, à une catégorie ou à un produit — une seule table polymorphique est créée, jamais une table par type de parent :

-- Exemple : table de FAQ polymorphique
id_faq       integer  -- clé primaire
parent_type  varchar  -- 'cms' | 'category' | 'product'
parent_id    integer  -- identifiant de l'entité parente
position     integer
active       boolean
date_add     datetime
date_upd     datetime
-- + table _lang associée : (id_faq, id_lang, question, answer)

Exception documentée : l'extension 1:1 d'une entité native du framework suit le patron <préfixe>_<entité>_extra, où la clé primaire est aussi la clé étrangère vers l'entité d'origine. Ce patron évite le polymorphisme pour les relations strictement bijectives. Les tables de liaison N-N pures sont nommées en combinant les deux entités dans l'ordre alphabétique, sans suffixe _asso ou _link, et sans table _lang associée.

Interdiction de JSON métier en colonne

Les colonnes dont le nom se termine par _json et qui stockeraient du contenu métier (payload_json, content_i18n, labels_json…) sont interdites par les conventions de nommage. La seule tolérance concerne les payloads techniques éphémères — webhooks, logs, état de session — documentés explicitement par un commentaire de colonne en base.

Dans le cockpit, les colonnes JSON existantes sur les tables de travaux et de tâches servent exclusivement à de l'audit technique en mode append-only (contexte, décisions, découvertes) : c'est la tolérance autorisée, non la règle générale. Un audit automatique planifié chaque nuit détecte et signale les violations de cette convention en niveau P0.

Pièges courants lors de la reprise du code

1. Les vues-shim : une douzaine de tables apparentes dans l'API publique du cockpit sont en réalité des vues pointant vers des tables physiques dans un schéma différent. Lire depuis la vue est correct ; écrire doit se faire directement sur la table physique sous-jacente. Les paires concernées incluent les agents, l'activité des agents, les heartbeats, les relations inter-agents, les points d'expérience, les automates et leurs journaux.
2. Trois niveaux distincts : le container de base de données, la base de données elle-même et le schéma principal sont trois objets indépendants avec des noms différents. Une confusion entre ces niveaux est une source d'erreur fréquente.
3. Migration PG obligatoire : la façade Nuxt lève une exception pour tout client dont la base n'a pas été migrée vers PostgreSQL. Le chemin de compatibilité MySQL a été supprimé. Un client doit figurer dans la liste des clients PostgreSQL activés pour que l'adaptateur fonctionne.
4. La contrainte de scope fait foi : l'énumération des valeurs autorisées pour le champ scope est définie par une contrainte CHECK en base, qui peut être plus large que la doctrine écrite dans la documentation. En cas de divergence, c'est la contrainte en base qui s'applique.
5. Résidus de renommage : la table de tâches a conservé des noms de séquence et de contrainte issus d'un renommage antérieur — ces identifiants ne reflètent pas le nom actuel de la table.
6. Conversion SQL MySQL→PG : le SQL legacy écrit en dialecte MySQL transite par un convertisseur automatique. Les fonctions non couvertes par ce convertisseur (GROUP_CONCAT, DATE_FORMAT, ON DUPLICATE KEY UPDATE…) échouent silencieusement en production et nécessitent une réécriture manuelle dans une branche PostgreSQL explicite.