Schéma de la base — socle

Version 2, 29 septembre 2026 : SQL exécuté et corrigé, changements multi-clients appliqués. Livrable n° 2 des travaux de conception du Lot 0. Applique EC-022, EC-025, EC-028, EC-029, EC-030 et EC-032, et prépare les tables exigées par le protocole de synchronisation.

En bref

• Trois schémas : app (métier), sync (commandes et file de contrôle), journal (audit).
• Toutes les tables métier portent organisation_id et sont isolées par Row-Level Security : sans app.organisation_id posé dans la transaction, une requête ne voit aucune ligne.
• Les tables de preuve sont en ajout seul : un déclencheur refuse toute modification ou suppression, en plus des droits.
• La matière suit une partie double : chaque mouvement se compose de lignes signées dont la somme est nulle, vérifiée par la base à la validation de la transaction.
• Chaque écriture produit une ligne de journal, écrite par un déclencheur que l’API ne peut pas contourner.

Le Lot 0 ne crée que le socle. Les tables métier (producteur, parcelle, lot, collecte…) arrivent au Lot 1 et au Lot 2, en réutilisant les fonctions d’installation décrites ici.

1. Conventions

Sujet	Règle	Source
Identifiants	uuid v7, générable sur l’appareil. Défaut en base : app.uuid_v7() (à remplacer par uuidv7() natif sous PostgreSQL 18)	EC-022
Énumérés	text + CHECK	EC-022
Dates	timestamptz ; date pour les dates métier sans heure	EC-022
Colonnes de suivi	cree_le et modifie_le (déclencheur) sur les tables modifiables ; cree_le seulement sur les tables en ajout seul	EC-022, précisé ici
Quantités	bigint en grammes ; surfaces en m² ; rendements en g/ha ; pourcentages en centièmes de point	EC-030
Montants	bigint dans la plus petite unité de la devise, toujours avec une colonne devise (ISO 4217)	EC-028
Références polymorphes	Une clé étrangère nullable par cible + CHECK (num_nonnulls(...) = 1)	EC-025
Isolation	organisation_id NOT NULL sur chaque table métier, clés étrangères composites (organisation_id, id) entre tables métier	EC-032
Noms	Français, snake_case, singulier ; clés étrangères en {cible}_id	–

Exception à préciser dans EC-022. Les tables en ajout seul n’ont pas de modifie_le, puisqu’elles ne sont jamais modifiées.

Exception à préciser dans EC-032. Trois tables sont globales, sans organisation_id ni RLS, et en lecture seule pour l’API : pack, pack_version, taux_change. Les packs sont maintenus par l’équipe BioTrace et chargés depuis le dossier packs/ du dépôt par le pipeline de déploiement.

2. Rôles PostgreSQL

Créés par l’infrastructure (infra/), avant la première migration.

Rôle	Attributs	Usage
biotrace	Propriétaire des schémas, BYPASSRLS	Migrations dbmate uniquement (job du pipeline, ADR 0044)
biotrace_app	LOGIN, NOBYPASSRLS	API. SELECT, INSERT, UPDATE ; aucun DELETE ; aucune écriture sur le journal ni sur les tables globales
biotrace_powersync	LOGIN, REPLICATION, BYPASSRLS	Service PowerSync. SELECT sur les tables publiées uniquement

Pourquoi PowerSync contourne la RLS. La réplication logique ignore la RLS, mais l’instantané initial se fait par SELECT. Avec FORCE ROW LEVEL SECURITY et sans app.organisation_id, cet instantané serait vide. L’isolation vers les téléphones repose donc sur les règles de synchronisation, qui filtrent sur organisation_id (EC-032). Ce rôle n’a aucun droit d’écriture.

Paramètres serveur requis : wal_level = logical, max_replication_slots et max_wal_senders suffisants pour PowerSync.

-- infra/postgres/roles.sql (exécuté une fois par environnement)
CREATE ROLE biotrace LOGIN BYPASSRLS PASSWORD :'mdp_migrations';
CREATE ROLE biotrace_app LOGIN NOBYPASSRLS PASSWORD :'mdp_api';
CREATE ROLE biotrace_powersync LOGIN REPLICATION BYPASSRLS PASSWORD :'mdp_powersync';
GRANT CREATE ON DATABASE biotrace TO biotrace;

docker-compose (EC-032). L’utilisateur biotrace reste réservé aux migrations. L’API se connecte avec biotrace_app, PowerSync avec biotrace_powersync.

3. Variables de transaction

L’API pose, au début de chaque transaction, avec SET LOCAL :

Variable	Obligatoire	Usage
app.organisation_id	Oui	Filtre RLS
app.utilisateur_id	Oui pour toute écriture	Auteur dans le journal
app.appareil_id	Si la commande vient du terrain	Journal
app.commande_id	Si l’écriture traite une commande	Journal, lien avec sync.commande

Côté API, un intercepteur NestJS ouvre la transaction Kysely et pose ces variables. Aucun module fonctionnel n’accède à la base hors de cette transaction.

4. Tables du socle

Table	Nature	Rôle
app.organisation	Modifiable	Client de BioTrace. Lien avec l’organisation Keycloak
app.utilisateur	Modifiable	Utilisateur d’une organisation, lié au sub Keycloak
app.ensemble_permissions	Modifiable	Ensembles de permissions configurables par organisation
app.attribution_permissions	Modifiable	Ensembles attribués à un utilisateur, avec période
app.appareil	Modifiable	Téléphone enrôlé : code court, agent, statut, dernière séquence
app.code_enrolement	Modifiable	Code à usage unique, conservé en empreinte
app.fichier	Modifiable (statut seul)	Référence d’un objet du stockage S3 (EC-029)
app.parametre	Ajout seul	Valeurs de paramètres versionnées par organisation
app.pack, app.pack_version	Globales	Packs filières ; une version publiée est figée
app.taux_change	Globale, ajout seul	Taux datés en rapport d’entiers (EC-028)
app.compte_matiere	Modifiable	Comptes de la partie double : lots et comptes techniques
app.mouvement, app.mouvement_ligne	Ajout seul	Mouvements de matière
app.validation	Ajout seul	Mécanisme de validation multi-rôles (EC-012). Cibles ajoutées au Lot 1
sync.commande	Ajout seul	Commande brute, telle que reçue
sync.commande_resultat	Ajout seul	Issue de la commande, synchronisée vers l’appareil
sync.controle	Modifiable jusqu’à la levée	File de contrôle unique
sync.rejet	Ajout seul	Rejets de sécurité (EC-043), migration 20260930100000_sync_rejet.sql
journal.evenement	Ajout seul	Journal d’audit

4.1 Partie double de la matière

Chaque mouvement porte au moins deux lignes. Une ligne positive entre de la matière dans un compte, une ligne négative en sort. La somme des lignes d’un mouvement est nulle.

Opération	Lignes
Réception d’une collecte de 98 kg	Lot +98 000 g ; origine_producteur −98 000 g
Séchage : 100 kg frais donnent 18 kg séchés	Lot frais −100 000 g ; lot séché +18 000 g ; perte +82 000 g (eau évaporée, EC-015)
Jus avec ajout de 5 kg de sucre	Lot fruits −100 000 g ; intrant −5 000 g ; lot jus +70 000 g ; perte +35 000 g
Expédition	Lot −12 000 000 g ; expedition +12 000 000 g
Contre-passation	Mêmes lignes, signes inversés, avec contrepasse_mouvement_id et motif

Le stock d’un lot est la somme de ses lignes. Le bilan matière d’une campagne est la somme par compte et par produit. Le contrôle « sortie ≤ entrées + intrants » (EC-015) se lit directement sur les comptes intrant et perte.

La table lot (Lot 2) référencera un compte_matiere de nature lot. produit_id reçoit sa clé étrangère au Lot 1.

4.2 Mécanisme de validation (EC-012)

app.validation enregistre chaque validation : rôle de validation, validateur, décision, date, commentaire.

• La base vérifie que le validateur n’est pas l’auteur de l’objet validé, et qu’un même utilisateur ne valide pas deux fois le même objet sous deux rôles.
• Au Lot 1, la migration de la capacité ajoute la colonne capacite_version_id, le CHECK (num_nonnulls(...) = 1), l’unicité (capacite_version_id, role_validation) et la fonction qui lit l’auteur de la capacité.
• Chaque nouvel objet à valider suit le même schéma (EC-025, option 2).

5. Fonctions d’installation

Chaque table est installée par une fonction, pour que la RLS, le journal et l’ajout seul ne soient jamais oubliés :

Fonction	Effet
app.installer_table(table, ajout_seul, colonne_org, journaliser)	RLS activée et forcée, politique isolation, déclencheur de journal, déclencheur modifie_le ou d’ajout seul
app.installer_table_globale(table, ajout_seul)	Journal, ajout seul éventuel, lecture seule pour biotrace_app
app.organisation_par_keycloak(id_keycloak)	Retrouve organisation_id avant la pose de app.organisation_id (SECURITY DEFINER, réservée à biotrace_app)

Un test d’intégration parcourt le catalogue PostgreSQL et échoue si une table des schémas app ou sync n’a pas de politique RLS, sauf les trois tables globales.

6. Première migration

Fichier db/migrations/20260929020000_socle.sql, au format dbmate. Le fichier fait foi : le bloc ci-dessous en reprend le contenu, sauf les COMMENT ON (dictionnaire de données), et a été exécuté et corrigé (voir §10).

-- migrate:up

------------------------------------------------------------------
-- 1. Extensions et schémas
------------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE SCHEMA app;
CREATE SCHEMA sync;
CREATE SCHEMA journal;

------------------------------------------------------------------
-- 2. Fonctions communes
------------------------------------------------------------------
-- UUID v7 (RFC 9562). Sous PostgreSQL 18, remplacer par uuidv7().
CREATE FUNCTION app.uuid_v7() RETURNS uuid
LANGUAGE plpgsql VOLATILE AS $$
DECLARE
  horodatage bytea := substring(int8send((extract(epoch FROM clock_timestamp()) * 1000)::bigint) FROM 3);
  -- Hasard : gen_random_uuid() est natif (pas de pgcrypto). On écarte les octets de version et de variante.
  alea       bytea := uuid_send(gen_random_uuid());
  octets     bytea := horodatage || substring(alea FROM 1 FOR 6) || substring(alea FROM 8 FOR 1) || substring(alea FROM 10 FOR 3);
BEGIN
  octets := set_byte(octets, 6, (b'0111' || get_byte(octets, 6)::bit(4))::bit(8)::int);
  octets := set_byte(octets, 8, (b'10' || get_byte(octets, 8)::bit(6))::bit(8)::int);
  RETURN encode(octets, 'hex')::uuid;
END $$;

CREATE FUNCTION app.organisation_courante() RETURNS uuid
LANGUAGE sql STABLE AS $$
  SELECT nullif(current_setting('app.organisation_id', true), '')::uuid
$$;

CREATE FUNCTION app.tg_modifie_le() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.modifie_le := now();
  RETURN NEW;
END $$;

CREATE FUNCTION app.tg_ajout_seul() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'Table en ajout seul : % interdit sur %.%', TG_OP, TG_TABLE_SCHEMA, TG_TABLE_NAME
    USING ERRCODE = 'P0001';
END $$;

------------------------------------------------------------------
-- 3. Journal d'audit
------------------------------------------------------------------
CREATE TABLE journal.evenement (
  id              uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id uuid,                      -- nul pour les tables globales
  table_nom       text NOT NULL,
  ligne_id        uuid NOT NULL,
  operation       text NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
  avant           jsonb,
  apres           jsonb,
  utilisateur_id  uuid,
  appareil_id     uuid,
  commande_id     uuid,
  transaction_id  bigint NOT NULL DEFAULT txid_current(),
  horodatage      timestamptz NOT NULL DEFAULT clock_timestamp()
);
CREATE INDEX evenement_ligne_idx ON journal.evenement (organisation_id, table_nom, ligne_id);
CREATE INDEX evenement_date_idx  ON journal.evenement (organisation_id, horodatage);

CREATE TRIGGER ajout_seul BEFORE UPDATE OR DELETE ON journal.evenement
  FOR EACH ROW EXECUTE FUNCTION app.tg_ajout_seul();

-- SECURITY DEFINER : l'API n'a aucun droit d'écriture direct sur le journal.
CREATE FUNCTION journal.tg_journaliser() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, app, journal AS $$
DECLARE
  v_apres jsonb := CASE WHEN TG_OP <> 'DELETE' THEN to_jsonb(NEW) END;
  v_avant jsonb := CASE WHEN TG_OP <> 'INSERT' THEN to_jsonb(OLD) END;
  v       jsonb := coalesce(v_apres, v_avant);
BEGIN
  INSERT INTO journal.evenement
    (organisation_id, table_nom, ligne_id, operation, avant, apres,
     utilisateur_id, appareil_id, commande_id)
  VALUES (
    CASE WHEN TG_TABLE_NAME = 'organisation' THEN (v ->> 'id')::uuid
         ELSE (v ->> 'organisation_id')::uuid END,
    TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME,
    (v ->> 'id')::uuid,
    TG_OP, v_avant, v_apres,
    nullif(current_setting('app.utilisateur_id', true), '')::uuid,
    nullif(current_setting('app.appareil_id', true), '')::uuid,
    nullif(current_setting('app.commande_id', true), '')::uuid
  );
  RETURN NULL;
END $$;

ALTER TABLE journal.evenement ENABLE ROW LEVEL SECURITY;
CREATE POLICY isolation ON journal.evenement
  FOR SELECT USING (organisation_id = app.organisation_courante());

------------------------------------------------------------------
-- 4. Fonctions d'installation des tables
------------------------------------------------------------------
CREATE FUNCTION app.installer_table(
  t regclass,
  ajout_seul boolean DEFAULT false,
  colonne_org text DEFAULT 'organisation_id',
  journaliser boolean DEFAULT true
) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  EXECUTE format('ALTER TABLE %s ENABLE ROW LEVEL SECURITY', t);
  EXECUTE format('ALTER TABLE %s FORCE ROW LEVEL SECURITY', t);
  EXECUTE format(
    'CREATE POLICY isolation ON %s USING (%I = app.organisation_courante()) WITH CHECK (%I = app.organisation_courante())',
    t, colonne_org, colonne_org);
  IF journaliser THEN
    EXECUTE format(
      'CREATE TRIGGER journal AFTER INSERT OR UPDATE OR DELETE ON %s FOR EACH ROW EXECUTE FUNCTION journal.tg_journaliser()', t);
  END IF;
  IF ajout_seul THEN
    EXECUTE format(
      'CREATE TRIGGER ajout_seul BEFORE UPDATE OR DELETE ON %s FOR EACH ROW EXECUTE FUNCTION app.tg_ajout_seul()', t);
  ELSE
    EXECUTE format(
      'CREATE TRIGGER modifie_le BEFORE UPDATE ON %s FOR EACH ROW EXECUTE FUNCTION app.tg_modifie_le()', t);
  END IF;
END $$;

CREATE FUNCTION app.installer_table_globale(t regclass, ajout_seul boolean DEFAULT false)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  EXECUTE format(
    'CREATE TRIGGER journal AFTER INSERT OR UPDATE OR DELETE ON %s FOR EACH ROW EXECUTE FUNCTION journal.tg_journaliser()', t);
  IF ajout_seul THEN
    EXECUTE format(
      'CREATE TRIGGER ajout_seul BEFORE UPDATE OR DELETE ON %s FOR EACH ROW EXECUTE FUNCTION app.tg_ajout_seul()', t);
  ELSE
    EXECUTE format(
      'CREATE TRIGGER modifie_le BEFORE UPDATE ON %s FOR EACH ROW EXECUTE FUNCTION app.tg_modifie_le()', t);
  END IF;
END $$;

------------------------------------------------------------------
-- 5. Organisation, utilisateurs, permissions
------------------------------------------------------------------
CREATE TABLE app.organisation (
  id                        uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  code                      text NOT NULL UNIQUE CHECK (code ~ '^[A-Z0-9]{2,10}$' AND code <> 'EXT'),  -- « ext » est réservé aux comptes externes
  nom                       text NOT NULL,
  pays                      text NOT NULL CHECK (pays ~ '^[A-Z]{2}$'),
  langue                    text NOT NULL DEFAULT 'fr' CHECK (langue IN ('fr', 'en')),
  fuseau_horaire            text NOT NULL DEFAULT 'Africa/Lome',
  devise_defaut             text NOT NULL DEFAULT 'XOF' CHECK (devise_defaut ~ '^[A-Z]{3}$'),
  keycloak_organisation_id  text UNIQUE,
  organisme_certificateur   text,           -- EC-027 : pas de contrôle de format
  code_operateur            text,
  logo_fichier_id           uuid,           -- clé étrangère ajoutée après app.fichier
  cree_le                   timestamptz NOT NULL DEFAULT now(),
  modifie_le                timestamptz NOT NULL DEFAULT now()
);
SELECT app.installer_table('app.organisation', false, 'id');

-- Le code figure dans les identifiants Keycloak (coopa.agent01) : il ne change jamais.
CREATE FUNCTION app.tg_organisation_code_immuable() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.code IS DISTINCT FROM OLD.code THEN
    RAISE EXCEPTION 'Le code de l''organisation % est immuable', OLD.id USING ERRCODE = 'P0001';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER code_immuable BEFORE UPDATE ON app.organisation
  FOR EACH ROW EXECUTE FUNCTION app.tg_organisation_code_immuable();

CREATE TABLE app.utilisateur (
  id              uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id uuid NOT NULL REFERENCES app.organisation (id),
  keycloak_sub    uuid NOT NULL,
  identifiant     text NOT NULL CHECK (identifiant ~ '^[a-z0-9_-]{3,32}$'),   -- identifiant local, sans le code de l'organisation
  nom_affiche     text NOT NULL,
  telephone       text,
  statut          text NOT NULL DEFAULT 'actif' CHECK (statut IN ('actif', 'suspendu', 'desactive')),
  cree_le         timestamptz NOT NULL DEFAULT now(),
  modifie_le      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (organisation_id, id),
  UNIQUE (organisation_id, keycloak_sub),
  UNIQUE (organisation_id, identifiant)
);
SELECT app.installer_table('app.utilisateur');

CREATE TABLE app.ensemble_permissions (
  id              uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id uuid NOT NULL REFERENCES app.organisation (id),
  code            text NOT NULL CHECK (code ~ '^[a-z0-9_]+$'),
  libelle         text NOT NULL,
  permissions     text[] NOT NULL,          -- validées par l'API contre le catalogue du code
  sensible        boolean NOT NULL DEFAULT false,   -- impose le second facteur
  cree_le         timestamptz NOT NULL DEFAULT now(),
  modifie_le      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (organisation_id, id),
  UNIQUE (organisation_id, code)
);
SELECT app.installer_table('app.ensemble_permissions');

CREATE TABLE app.attribution_permissions (
  id               uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id  uuid NOT NULL,
  utilisateur_id   uuid NOT NULL,
  ensemble_id      uuid NOT NULL,
  debut            timestamptz NOT NULL DEFAULT now(),
  fin              timestamptz,
  attribue_par     uuid NOT NULL,
  cree_le          timestamptz NOT NULL DEFAULT now(),
  modifie_le       timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (organisation_id, utilisateur_id) REFERENCES app.utilisateur (organisation_id, id),
  FOREIGN KEY (organisation_id, ensemble_id)    REFERENCES app.ensemble_permissions (organisation_id, id),
  FOREIGN KEY (organisation_id, attribue_par)   REFERENCES app.utilisateur (organisation_id, id),
  CHECK (fin IS NULL OR fin > debut)
);
SELECT app.installer_table('app.attribution_permissions');

------------------------------------------------------------------
-- 6. Appareils et enrôlement
------------------------------------------------------------------
CREATE TABLE app.appareil (
  id                   uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id      uuid NOT NULL,
  code                 text NOT NULL UNIQUE CHECK (code ~ '^[A-Z0-9]{4,6}$'),   -- EC-009
  utilisateur_id       uuid NOT NULL,
  modele               text,
  gnss_bifrequence     boolean,                                             -- GPS-03, GPS-05
  version_application  text,
  statut               text NOT NULL DEFAULT 'actif' CHECK (statut IN ('actif', 'revoque', 'remplace')),
  enrole_le            timestamptz NOT NULL DEFAULT now(),
  revoque_le           timestamptz,
  revoque_par          uuid,
  motif_revocation     text,
  derniere_sequence    bigint NOT NULL DEFAULT 0 CHECK (derniere_sequence >= 0),
  derniere_synchro     timestamptz,
  cree_le              timestamptz NOT NULL DEFAULT now(),
  modifie_le           timestamptz NOT NULL DEFAULT now(),
  UNIQUE (organisation_id, id),
  FOREIGN KEY (organisation_id, utilisateur_id) REFERENCES app.utilisateur (organisation_id, id),
  FOREIGN KEY (organisation_id, revoque_par)    REFERENCES app.utilisateur (organisation_id, id),
  CHECK ((statut = 'revoque') = (revoque_le IS NOT NULL)),
  CHECK (revoque_le IS NULL OR motif_revocation IS NOT NULL)
);
SELECT app.installer_table('app.appareil');

CREATE TABLE app.code_enrolement (
  id              uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id uuid NOT NULL,
  utilisateur_id  uuid NOT NULL,
  empreinte_code  text NOT NULL CHECK (empreinte_code ~ '^[0-9a-f]{64}$'),   -- jamais le code en clair
  expire_le       timestamptz NOT NULL,
  utilise_le      timestamptz,
  appareil_id     uuid,
  cree_par        uuid NOT NULL,
  cree_le         timestamptz NOT NULL DEFAULT now(),
  modifie_le      timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (organisation_id, utilisateur_id) REFERENCES app.utilisateur (organisation_id, id),
  FOREIGN KEY (organisation_id, appareil_id)    REFERENCES app.appareil (organisation_id, id),
  FOREIGN KEY (organisation_id, cree_par)       REFERENCES app.utilisateur (organisation_id, id),
  CHECK ((utilise_le IS NULL) = (appareil_id IS NULL)),
  CHECK (cree_par <> utilisateur_id)
);
SELECT app.installer_table('app.code_enrolement');

------------------------------------------------------------------
-- 7. Fichiers (EC-029)
------------------------------------------------------------------
CREATE TABLE app.fichier (
  id              uuid PRIMARY KEY,         -- généré sur l'appareil ou par l'API
  organisation_id uuid NOT NULL REFERENCES app.organisation (id),
  cle_objet       text NOT NULL UNIQUE,
  type_mime       text NOT NULL,
  taille_octets   bigint CHECK (taille_octets >= 0),
  sha256          text NOT NULL CHECK (sha256 ~ '^[0-9a-f]{64}$'),
  statut          text NOT NULL DEFAULT 'attendu' CHECK (statut IN ('attendu', 'recu', 'empreinte_invalide')),
  appareil_id     uuid,
  depose_par      uuid,
  depose_le       timestamptz,
  cree_le         timestamptz NOT NULL DEFAULT now(),
  modifie_le      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (organisation_id, id),
  FOREIGN KEY (organisation_id, appareil_id) REFERENCES app.appareil (organisation_id, id),
  FOREIGN KEY (organisation_id, depose_par)  REFERENCES app.utilisateur (organisation_id, id),
  CHECK ((statut = 'recu') = (depose_le IS NOT NULL))
);
SELECT app.installer_table('app.fichier');

-- Un fichier déposé n'est jamais écrasé : seule la colonne statut évolue.
CREATE FUNCTION app.tg_fichier_fige() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.sha256 <> OLD.sha256 OR NEW.cle_objet <> OLD.cle_objet
     OR NEW.organisation_id <> OLD.organisation_id OR OLD.statut = 'recu' THEN
    RAISE EXCEPTION 'Fichier % figé', OLD.id USING ERRCODE = 'P0001';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER fichier_fige BEFORE UPDATE ON app.fichier
  FOR EACH ROW EXECUTE FUNCTION app.tg_fichier_fige();

ALTER TABLE app.organisation
  ADD CONSTRAINT organisation_logo_fk FOREIGN KEY (logo_fichier_id) REFERENCES app.fichier (id);

------------------------------------------------------------------
-- 8. Paramétrage et packs
------------------------------------------------------------------
CREATE TABLE app.parametre (
  id              uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id uuid NOT NULL REFERENCES app.organisation (id),
  cle             text NOT NULL CHECK (cle ~ '^[a-z0-9_]+(\.[a-z0-9_]+)*$'),
  version         integer NOT NULL CHECK (version > 0),
  valeur          jsonb NOT NULL,
  schema_ref      text NOT NULL,            -- JSON Schema de la valeur, dans packages/schemas
  applicable_du   timestamptz NOT NULL,
  motif           text NOT NULL,
  cree_par        uuid NOT NULL,
  cree_le         timestamptz NOT NULL DEFAULT now(),
  UNIQUE (organisation_id, cle, version),
  FOREIGN KEY (organisation_id, cree_par) REFERENCES app.utilisateur (organisation_id, id)
);
SELECT app.installer_table('app.parametre', true);

CREATE TABLE app.pack (
  id          uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  code        text NOT NULL UNIQUE CHECK (code ~ '^[a-z0-9_]+$'),
  libelle     text NOT NULL,
  cree_le     timestamptz NOT NULL DEFAULT now(),
  modifie_le  timestamptz NOT NULL DEFAULT now()
);
SELECT app.installer_table_globale('app.pack');

CREATE TABLE app.pack_version (
  id          uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  pack_id     uuid NOT NULL REFERENCES app.pack (id),
  version     text NOT NULL CHECK (version ~ '^[0-9]+\.[0-9]+\.[0-9]+$'),
  contenu     jsonb NOT NULL,
  sha256      text NOT NULL CHECK (sha256 ~ '^[0-9a-f]{64}$'),
  calibre     boolean NOT NULL DEFAULT false,          -- EC-007
  statut      text NOT NULL DEFAULT 'brouillon' CHECK (statut IN ('brouillon', 'publie', 'retire')),
  publie_le   timestamptz,
  cree_le     timestamptz NOT NULL DEFAULT now(),
  modifie_le  timestamptz NOT NULL DEFAULT now(),
  UNIQUE (pack_id, version),
  CHECK ((statut = 'brouillon') = (publie_le IS NULL))
);
SELECT app.installer_table_globale('app.pack_version');

-- Une version publiée est figée : seul le passage à « retiré » reste possible.
CREATE FUNCTION app.tg_pack_version_fige() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.statut <> 'brouillon' AND (
       NEW.contenu IS DISTINCT FROM OLD.contenu OR NEW.sha256 IS DISTINCT FROM OLD.sha256
       OR NEW.calibre IS DISTINCT FROM OLD.calibre OR NEW.version IS DISTINCT FROM OLD.version
       OR NOT (NEW.statut = OLD.statut OR (OLD.statut = 'publie' AND NEW.statut = 'retire'))) THEN
    RAISE EXCEPTION 'Version de pack % figée', OLD.id USING ERRCODE = 'P0001';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER pack_version_fige BEFORE UPDATE ON app.pack_version
  FOR EACH ROW EXECUTE FUNCTION app.tg_pack_version_fige();

------------------------------------------------------------------
-- 9. Devises (EC-028)
------------------------------------------------------------------
CREATE TABLE app.taux_change (
  id              uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  devise_source   text NOT NULL CHECK (devise_source ~ '^[A-Z]{3}$'),
  devise_cible    text NOT NULL CHECK (devise_cible ~ '^[A-Z]{3}$'),
  numerateur      bigint NOT NULL CHECK (numerateur > 0),     -- taux = numerateur / denominateur
  denominateur    bigint NOT NULL CHECK (denominateur > 0),
  applicable_du   date NOT NULL,
  source          text NOT NULL,
  cree_le         timestamptz NOT NULL DEFAULT now(),
  UNIQUE (devise_source, devise_cible, applicable_du),
  CHECK (devise_source <> devise_cible)
);
SELECT app.installer_table_globale('app.taux_change', true);

INSERT INTO app.taux_change (devise_source, devise_cible, numerateur, denominateur, applicable_du, source)
VALUES ('EUR', 'XOF', 655957, 1000, '1999-01-01', 'Parité fixe EUR/XOF');

------------------------------------------------------------------
-- 10. Matière en partie double
------------------------------------------------------------------
CREATE TABLE app.compte_matiere (
  id              uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id uuid NOT NULL REFERENCES app.organisation (id),
  nature          text NOT NULL CHECK (nature IN (
                    'lot', 'origine_producteur', 'origine_fournisseur', 'intrant',
                    'perte', 'purge', 'expedition', 'ecart_inventaire')),
  libelle         text NOT NULL,
  cree_le         timestamptz NOT NULL DEFAULT now(),
  modifie_le      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (organisation_id, id)
);
CREATE UNIQUE INDEX compte_matiere_technique_uq
  ON app.compte_matiere (organisation_id, nature) WHERE nature <> 'lot';
SELECT app.installer_table('app.compte_matiere');

CREATE TABLE app.mouvement (
  id                        uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id           uuid NOT NULL REFERENCES app.organisation (id),
  nature                    text NOT NULL CHECK (nature IN (
                              'reception', 'transfert', 'transformation', 'expedition',
                              'perte', 'inventaire', 'contrepassation')),
  date_operation            timestamptz NOT NULL,
  contrepasse_mouvement_id  uuid UNIQUE,
  motif                     text,
  commande_id               uuid,
  cree_par                  uuid NOT NULL,
  cree_le                   timestamptz NOT NULL DEFAULT now(),
  UNIQUE (organisation_id, id),
  FOREIGN KEY (organisation_id, contrepasse_mouvement_id) REFERENCES app.mouvement (organisation_id, id),
  FOREIGN KEY (organisation_id, cree_par)                 REFERENCES app.utilisateur (organisation_id, id),
  CHECK ((nature = 'contrepassation') = (contrepasse_mouvement_id IS NOT NULL)),
  CHECK (nature <> 'contrepassation' OR motif IS NOT NULL)
);
SELECT app.installer_table('app.mouvement', true);

CREATE TABLE app.mouvement_ligne (
  id                 uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id    uuid NOT NULL,
  mouvement_id       uuid NOT NULL,
  compte_matiere_id  uuid NOT NULL,
  produit_id         uuid NOT NULL,          -- clé étrangère ajoutée au Lot 1
  quantite_g         bigint NOT NULL CHECK (quantite_g <> 0),
  cree_le            timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (organisation_id, mouvement_id)      REFERENCES app.mouvement (organisation_id, id),
  FOREIGN KEY (organisation_id, compte_matiere_id) REFERENCES app.compte_matiere (organisation_id, id)
);
CREATE INDEX mouvement_ligne_mouvement_idx ON app.mouvement_ligne (mouvement_id);
CREATE INDEX mouvement_ligne_compte_idx    ON app.mouvement_ligne (organisation_id, compte_matiere_id, produit_id);
SELECT app.installer_table('app.mouvement_ligne', true);

-- Équilibre et contre-passation, vérifiés à la validation de la transaction.
CREATE FUNCTION app.tg_mouvement_equilibre() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_id       uuid;
  v_solde    bigint;
  v_lignes   integer;
  v_origine  uuid;
BEGIN
  IF TG_TABLE_NAME = 'mouvement' THEN
    v_id := NEW.id;
  ELSE
    v_id := NEW.mouvement_id;
  END IF;

  SELECT coalesce(sum(quantite_g), 0), count(*) INTO v_solde, v_lignes
  FROM app.mouvement_ligne WHERE mouvement_id = v_id;

  IF v_lignes < 2 OR v_solde <> 0 THEN
    RAISE EXCEPTION 'Mouvement % déséquilibré : solde % g sur % lignes', v_id, v_solde, v_lignes
      USING ERRCODE = 'P0002';
  END IF;

  SELECT contrepasse_mouvement_id INTO v_origine FROM app.mouvement WHERE id = v_id;
  IF v_origine IS NOT NULL AND (
       EXISTS (SELECT compte_matiere_id, produit_id, -quantite_g FROM app.mouvement_ligne WHERE mouvement_id = v_origine
               EXCEPT ALL
               SELECT compte_matiere_id, produit_id, quantite_g FROM app.mouvement_ligne WHERE mouvement_id = v_id)
    OR EXISTS (SELECT compte_matiere_id, produit_id, quantite_g FROM app.mouvement_ligne WHERE mouvement_id = v_id
               EXCEPT ALL
               SELECT compte_matiere_id, produit_id, -quantite_g FROM app.mouvement_ligne WHERE mouvement_id = v_origine)) THEN
    RAISE EXCEPTION 'La contre-passation % ne reprend pas exactement le mouvement %', v_id, v_origine
      USING ERRCODE = 'P0002';
  END IF;

  RETURN NULL;
END $$;

CREATE CONSTRAINT TRIGGER mouvement_equilibre
  AFTER INSERT ON app.mouvement DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION app.tg_mouvement_equilibre();
CREATE CONSTRAINT TRIGGER mouvement_ligne_equilibre
  AFTER INSERT ON app.mouvement_ligne DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION app.tg_mouvement_equilibre();

CREATE VIEW app.v_solde_compte WITH (security_invoker = true) AS
  SELECT organisation_id, compte_matiere_id, produit_id, sum(quantite_g) AS solde_g
  FROM app.mouvement_ligne
  GROUP BY organisation_id, compte_matiere_id, produit_id;

------------------------------------------------------------------
-- 11. Validation multi-rôles (EC-012) : squelette
------------------------------------------------------------------
-- Les colonnes cibles, le CHECK num_nonnulls et les unicités sont ajoutés
-- par chaque lot qui introduit un objet à valider (capacité au Lot 1).
CREATE TABLE app.validation (
  id               uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id  uuid NOT NULL,
  role_validation  text NOT NULL CHECK (role_validation ~ '^[a-z_]+$'),
  validateur_id    uuid NOT NULL,
  auteur_objet_id  uuid NOT NULL,           -- renseigné par le déclencheur du lot, jamais par l'API
  decision         text NOT NULL CHECK (decision IN ('approuve', 'refuse')),
  commentaire      text,
  cree_le          timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (organisation_id, validateur_id)   REFERENCES app.utilisateur (organisation_id, id),
  FOREIGN KEY (organisation_id, auteur_objet_id) REFERENCES app.utilisateur (organisation_id, id),
  CHECK (validateur_id <> auteur_objet_id),
  CHECK (decision = 'approuve' OR commentaire IS NOT NULL)
);
SELECT app.installer_table('app.validation', true);

------------------------------------------------------------------
-- 12. Synchronisation
------------------------------------------------------------------
CREATE TABLE sync.commande (
  id                    uuid PRIMARY KEY,   -- UUID v7 généré sur l'appareil
  organisation_id       uuid NOT NULL,
  appareil_id           uuid NOT NULL,
  utilisateur_id        uuid NOT NULL,
  sequence              bigint NOT NULL CHECK (sequence > 0),
  type                  text NOT NULL CHECK (type ~ '^[a-z_]+(\.[a-z_]+)+$'),
  version_schema        integer NOT NULL CHECK (version_schema > 0),
  cree_le_appareil      timestamptz NOT NULL,
  date_operation        timestamptz NOT NULL,
  recue_le              timestamptz NOT NULL DEFAULT now(),
  reference_provisoire  text,
  empreinte             text NOT NULL CHECK (empreinte ~ '^[0-9a-f]{64}$'),
  enveloppe             jsonb NOT NULL,     -- commande complète, telle que reçue
  UNIQUE (organisation_id, id),
  UNIQUE (appareil_id, sequence),
  UNIQUE (organisation_id, reference_provisoire),
  FOREIGN KEY (organisation_id, appareil_id)    REFERENCES app.appareil (organisation_id, id),
  FOREIGN KEY (organisation_id, utilisateur_id) REFERENCES app.utilisateur (organisation_id, id),
  CHECK (date_operation <= recue_le)
);
-- Pas de journal : la commande brute est elle-même la trace.
SELECT app.installer_table('sync.commande', true, 'organisation_id', false);

CREATE TABLE sync.commande_resultat (
  id               uuid PRIMARY KEY,        -- = id de la commande (PowerSync exige une colonne id)
  organisation_id  uuid NOT NULL,
  appareil_id      uuid NOT NULL,
  issue            text NOT NULL CHECK (issue IN ('acceptee', 'acceptee_controle', 'rejetee')),
  motifs           text[] NOT NULL DEFAULT '{}',
  alertes          text[] NOT NULL DEFAULT '{}',
  numero_officiel  text,
  entites          jsonb NOT NULL DEFAULT '{}',
  date_operation   timestamptz NOT NULL,
  traitee_le       timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (organisation_id, id)          REFERENCES sync.commande (organisation_id, id),
  FOREIGN KEY (organisation_id, appareil_id) REFERENCES app.appareil (organisation_id, id),
  CHECK ((issue = 'acceptee') = (cardinality(motifs) = 0))
);
CREATE INDEX commande_resultat_appareil_idx ON sync.commande_resultat (appareil_id);
SELECT app.installer_table('sync.commande_resultat', true);

CREATE TABLE sync.controle (
  id               uuid PRIMARY KEY DEFAULT app.uuid_v7(),
  organisation_id  uuid NOT NULL,
  commande_id      uuid,
  motif            text NOT NULL CHECK (motif IN (
                     'capacite_depassee', 'doublon_identite', 'polygone_invalide', 'chevauchement_parcelle',
                     'modification_concurrente', 'ecart_calcul', 'apres_revocation', 'hors_ligne_depasse',
                     'droit_manquant', 'horloge_invraisemblable', 'reference_inconnue', 'reference_inactive',
                     'dependance_en_controle', 'donnees_illisibles', 'version_client_obsolete',
                     'empreinte_incoherente', 'piece_manquante', 'erreur_signalee_agent',
                     'intrant_non_autorise', 'conflit_interets', 'parametre_introuvable')),
  details          jsonb NOT NULL DEFAULT '{}',
  -- Cibles métier ajoutées par lot : collecte_id, producteur_id, parcelle_version_id…
  statut           text NOT NULL DEFAULT 'ouvert' CHECK (statut IN ('ouvert', 'en_cours', 'leve')),
  decision         text CHECK (decision IN (
                     'regularisation', 'declassement', 'non_conformite', 'fusion',
                     'correction', 'confirmation', 'ecart_manuel', 'reprise_manuelle')),
  motif_decision   text,
  decide_par       uuid,
  decide_le        timestamptz,
  cree_le          timestamptz NOT NULL DEFAULT now(),
  modifie_le       timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (organisation_id, commande_id) REFERENCES sync.commande (organisation_id, id),
  FOREIGN KEY (organisation_id, decide_par)  REFERENCES app.utilisateur (organisation_id, id),
  CHECK ((statut = 'leve') = (decision IS NOT NULL)),
  CHECK ((decision IS NULL) = (decide_par IS NULL AND decide_le IS NULL AND motif_decision IS NULL))
);
CREATE INDEX controle_ouvert_idx ON sync.controle (organisation_id, statut, cree_le) WHERE statut <> 'leve';
SELECT app.installer_table('sync.controle');

CREATE FUNCTION sync.tg_controle_regles() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.statut = 'leve' THEN
    RAISE EXCEPTION 'Contrôle % déjà levé', OLD.id USING ERRCODE = 'P0001';
  END IF;
  IF NEW.decide_par IS NOT NULL AND EXISTS (
       SELECT 1 FROM sync.commande c
       WHERE c.id = NEW.commande_id AND c.utilisateur_id = NEW.decide_par) THEN
    RAISE EXCEPTION 'L''auteur de la commande ne peut pas lever son propre contrôle'
      USING ERRCODE = 'P0003';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER controle_regles BEFORE UPDATE ON sync.controle
  FOR EACH ROW EXECUTE FUNCTION sync.tg_controle_regles();

------------------------------------------------------------------
-- 13. Droits
------------------------------------------------------------------
GRANT USAGE ON SCHEMA app, sync, journal TO biotrace_app;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA app, sync TO biotrace_app;
GRANT SELECT ON journal.evenement TO biotrace_app;

-- Tables globales : lecture seule pour l'API.
REVOKE INSERT, UPDATE ON app.pack, app.pack_version, app.taux_change FROM biotrace_app;
-- Tables en ajout seul : pas d'UPDATE (le déclencheur l'interdit aussi).
REVOKE UPDATE ON app.parametre, app.mouvement, app.mouvement_ligne, app.validation,
                 sync.commande, sync.commande_resultat FROM biotrace_app;

ALTER DEFAULT PRIVILEGES IN SCHEMA app, sync
  GRANT SELECT, INSERT, UPDATE ON TABLES TO biotrace_app;
GRANT EXECUTE ON FUNCTION app.uuid_v7(), app.organisation_courante() TO biotrace_app;

-- Résolution de l'organisation à partir de l'identifiant Keycloak, avant que app.organisation_id soit posé.
-- Sans elle, la RLS masque toutes les organisations et l'API ne peut pas commencer sa transaction.
-- SECURITY DEFINER : elle ne révèle que l'identifiant correspondant à un identifiant Keycloak connu.
CREATE FUNCTION app.organisation_par_keycloak(p_keycloak_id text) RETURNS uuid
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = pg_catalog, app AS $$
  SELECT id FROM app.organisation WHERE keycloak_organisation_id = p_keycloak_id
$$;
REVOKE ALL ON FUNCTION app.organisation_par_keycloak(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.organisation_par_keycloak(text) TO biotrace_app;

------------------------------------------------------------------
-- 14. Publication PowerSync
------------------------------------------------------------------
GRANT USAGE ON SCHEMA app, sync TO biotrace_powersync;
GRANT SELECT ON app.organisation, app.appareil, app.parametre, app.pack, app.pack_version,
                app.taux_change, sync.commande_resultat TO biotrace_powersync;

CREATE PUBLICATION powersync FOR TABLE
  app.organisation, app.appareil, app.parametre, app.pack, app.pack_version,
  app.taux_change, sync.commande_resultat;
-- Les lots suivants ajoutent leurs tables : ALTER PUBLICATION powersync ADD TABLE …

------------------------------------------------------------------
-- 15. Commentaires (dictionnaire de données)
------------------------------------------------------------------
-- COMMENT ON en français sur chaque table, colonne, vue et fonction : voir le fichier de migration.

-- migrate:down
DROP PUBLICATION IF EXISTS powersync;
DROP SCHEMA sync CASCADE;
DROP SCHEMA journal CASCADE;
DROP SCHEMA app CASCADE;

7. Génération des types

Après chaque migration, en intégration continue :

dbmate up
kysely-codegen --dialect postgres --include-pattern "(app|sync|journal).*" --out-file packages/api-client/src/db.generated.ts

Le fichier généré est versionné. Une différence entre le fichier versionné et le fichier régénéré fait échouer l’intégration continue.

8. Tests d’intégration du socle

Scripts SQL dans db/tests/, lancés par pnpm db:tester sur une base PostgreSQL + PostGIS éphémère. Chaque test s’exécute dans une transaction annulée, avec les données de db/tests/_donnees.sql, et bascule sur biotrace_app ou biotrace_powersync par SET LOCAL ROLE.

id	Test	Résultat attendu
SB-01	Requête sans app.organisation_id	Aucune ligne, sur chaque table
SB-02	Insertion avec un organisation_id différent de celui de la transaction	Refus par la politique RLS
SB-03	Lecture des données d’une autre organisation	Aucune ligne
SB-04	DELETE sur n’importe quelle table	Refus de droit
SB-05	UPDATE d’un mouvement, d’une commande, d’un paramètre	Refus
SB-06	Mouvement déséquilibré de 1 g	Refus à la validation de la transaction
SB-07	Contre-passation qui ne reprend pas exactement l’original	Refus
SB-08	Chaque écriture sur une table métier	Une ligne de journal avec auteur, avant et après
SB-09	Insertion directe dans journal.evenement	Refus de droit
SB-10	Modification du contenu d’une version de pack publiée	Refus
SB-11	Validation par l’auteur de l’objet	Refus
SB-12	Levée d’un contrôle par l’auteur de la commande	Refus
SB-13	Une table de app ou sync sans politique RLS (hors tables globales)	Échec du test de catalogue
SB-14	Instantané PowerSync avec biotrace_powersync	Toutes les lignes publiées visibles
SB-15	Identifiant d’utilisateur hors format, ou en double dans l’organisation	Refus ; le même identifiant dans deux organisations est accepté (KC-13)
SB-16	Modification du code d’une organisation	Refus par la base (KC-17)
SB-17	Organisation de code EXT	Refus
SB-18	Résolution de l’organisation depuis l’identifiant Keycloak	Identifiant retrouvé sans organisation posée ; aucune donnée exposée
SB-19	Trace des rejets de sécurité (EC-043)	Écriture même si l’identifiant et la séquence sont déjà pris ; autre organisation refusée ; ni modification ni suppression ; présence au journal
SB-20	Résolution de l’organisation depuis son code (EC-045)	Identifiant retrouvé sans organisation posée ; code en minuscules inconnu ; aucune donnée exposée ; fonction refusée au rôle PowerSync

SB-08 couvre le critère de fin du Lot 0 « chaque écriture apparaît dans le journal d’audit ». Le critère « un paramètre modifié est versionné, l’ancienne version reste lisible » est couvert par app.parametre en ajout seul et par un test de lecture à une date antérieure.

9. Ce que les lots suivants ajoutent

Lot	Ajouts au schéma
0 bis	Catalogue des activités, modules, dépendances, activation par organisation, pack.type (séquence L0-2)
1	Campagne, produit, unité, numérotation (modèle, séquences), producteur, groupement, affectation d’agent, parcelle et versions de polygone (geometry(Polygon, 4326), colonne GeoJSON pour PowerSync), statut de conformité, certificat, contrat, capacité et solde ; cibles de validation et de controle
2	Lot (avec son compte de matière), collecte, transformation, expédition, registre des annulations, tâches pg-boss
3	Colonnes de métadonnées GPS-03, tables synchronisées du périmètre agent
4	Intrants, avances, paiements, inspections, non-conformités, formulaires versionnés

10. Corrections apportées à l’exécution

Le SQL de la première version n’avait jamais été exécuté. Son exécution, puis les tests SB-01 à SB-18, ont donné :

Point	Constat	Correction
gen_random_bytes introuvable	pgcrypto s’installe dans public, absent du search_path du déclencheur de journal (SECURITY DEFINER) : toute insertion échouait	pgcrypto retirée. Le hasard vient de gen_random_uuid(), natif ; les octets de version et de variante sont écartés
Organisation Keycloak illisible	Sans app.organisation_id, la RLS masque toutes les organisations : l’API ne pouvait pas traduire l’identifiant Keycloak (chaîne d’isolation, maillon 2)	Fonction app.organisation_par_keycloak (écart EC-036)
Multi-clients	Identifiant d’utilisateur, code immuable, code EXT	Appliqués (SB-15 à SB-17)
Dictionnaire de données	Aucun commentaire	COMMENT ON en français sur toutes les tables, colonnes, vues et fonctions ; pnpm db:commentaires couvre app, sync et journal

Points vérifiés, sans correction :

• Les app.uuid_v7() ont la version 7 et la variante RFC. Ils sont uniques, mais pas strictement croissants au sein d’une même milliseconde (le RFC 9562 l’autorise). Ne pas s’en servir pour ordonner des événements : utiliser sequence ou horodatage.
• sync.commande : la contrainte date_operation <= recue_le applique la règle 5 du §6.4 du protocole. L’API borne donc la date d’opération à l’heure de réception avant l’insertion ; une date future serait sinon un rejet, contraire au principe « aucun rejet métier ».
• Créer une organisation avec biotrace_app exige de poser d’abord app.organisation_id avec l’identifiant de la nouvelle organisation : la politique WITH CHECK compare id à la variable.
• Les tests détectent leurs défauts : chaque politique, droit et déclencheur a été retiré à tour de rôle, et le test concerné a échoué. Un droit INSERT accordé à tort sur le journal reste arrêté par la RLS (même code d’erreur) ; SB-09 contrôle donc aussi les droits.