Modèle des référentiels du Lot 1

Ce document a servi de source aux COMMENT ON de la migration brouillon db/migrations/20260929030000_referentiels_lot1.sql, retirée par la migration A1 (EC-046) : ses tables public.* n’ont jamais porté de données. site, emplacement et campagne sont maintenant décrits par Données, à jour. Ce document reste la préparation de producteur, parcelle, certificat et statut_conformite, pas encore migrées (séquences A4 à A6) : à lire pour leurs sources et leur raisonnement, pas à recopier tel quel (le protocole de synchronisation, §7.3, exige par exemple groupement_id obligatoire sur producteur et parcelle, alors que ce document le propose facultatif).

Lecture

• Périmètre. Sept tables du Lot 1 : organisation, fichier, campagne, producteur, parcelle, certificat, statut_conformite.
• Source. Chaque colonne cite la section du cahier v1.2 (chapitre 11 sauf mention), de l’avenant n°1 (dans l’analyse critique) ou du découpage. Quand une source du cahier est corrigée par une source de priorité supérieure, la colonne suit la plus prioritaire et l’écart est signalé.
• Décision EC-xxx. Colonne ou contrainte sans source dans docs-sources/, fixée par une décision de Décisions de résorption des écarts.
• Types. Les poids sont en grammes, les pourcentages en centièmes de point, les montants en unités entières. Voir ADR 0010. Les autres choix de type suivent la décision EC-022 : uuid (v7), énumérés en text avec CHECK, timestamptz. Chaque montant porte sa devise ISO 4217 (EC-028).
• Obligatoire. « Non (requis pour générer) » signifie que la base accepte la valeur absente, mais que la génération des documents la vérifie (RG-101).
• Isolation. Toutes les tables portent organisation_id, pour l’isolation par Row-Level Security (EC-032). PowerSync ne passe pas par la RLS : l’isolation sur les téléphones filtre sur cette colonne (découpage, « Décisions techniques », ligne « Multi-clients », statut Recommandé).

Relations

erDiagram
  ORGANISATION ||--o{ CAMPAGNE : "possède"
  ORGANISATION ||--o{ PRODUCTEUR : "enrôle"
  ORGANISATION ||--o{ CERTIFICAT : "détient ou déclare"
  ORGANISATION ||--o{ FICHIER : "possède"
  ORGANISATION }o--o| FICHIER : "logo"
  CERTIFICAT }o--o| FICHIER : "PDF"
  PRODUCTEUR ||--o{ PARCELLE : "exploite"
  PARCELLE }o--|| PRODUIT : "cultive"
  PRODUCTEUR }o--o| GROUPEMENT : "appartient à"
  PRODUCTEUR }o--o| UTILISATEUR : "agent collecteur"
  STATUT_CONFORMITE }o--o| PRODUCTEUR : "unité"
  STATUT_CONFORMITE }o--o| PARCELLE : "unité"
  STATUT_CONFORMITE }o..o| ZONE_CUEILLETTE : "unité"
  CERTIFICAT }o--o| ORGANISATION : "titulaire (opérateur)"
  CERTIFICAT }o..o| GROUPEMENT : "titulaire"
  CERTIFICAT }o..o| FOURNISSEUR : "titulaire"
  ORGANISATION ||--o{ STATUT_CONFORMITE : "isolation"
  GROUPEMENT {
    uuid id PK "à créer ensuite"
  }
  PRODUIT {
    uuid id PK "à créer ensuite"
  }
  UTILISATEUR {
    uuid id PK "à créer ensuite"
  }
  ZONE_CUEILLETTE {
    uuid id PK "à créer ensuite"
  }
  FOURNISSEUR {
    uuid id PK "à créer ensuite"
  }

Les traits pleins sont des clés étrangères. Les pointillés sont des clés étrangères non posées, car la table cible n’existe pas encore. Une référence à plusieurs cibles possibles porte une clé étrangère nullable par cible, avec un CHECK « exactement une renseignée » (EC-025). Les cinq entités à un seul attribut restent à modéliser : elles sont citées pour situer les références.

Table organisation

L’opérateur titulaire du certificat. Le cahier l’appelle « Entreprise » ; le découpage et l’avenant disent « organisation ».

Colonne	Type PostgreSQL	Obligatoire	Description	Source
id	uuid	Oui	Identifiant stable, public, jamais réutilisé.	Cahier §11 « Entreprise » ; EX-010 (§9.1)
nom	text	Oui	Raison sociale de l’opérateur.	Cahier §11 « Entreprise » ; RG-101 (§5.8)
code	text	Oui	Code opérateur, unique, de 3 à 10 caractères.	Cahier §11 « Entreprise »
activites	text[]	Oui	Liste des activités parmi production, achat, nettoyage, transformation, vente. Toute combinaison est valide. Le retrait d’une activité conserve les données.	Cahier §11 ; RG-001, RG-004 (§5.1) ; §2.1
pays	text	Non (requis pour générer)	Pays de l’opérateur.	Cahier §11 ; RG-101
region	text	Non (requis pour générer)	Région.	Cahier §11
ville	text	Non (requis pour générer)	Ville.	Cahier §11
adresse	text	Non (requis pour générer)	Adresse imprimée sur les documents.	Cahier §11 ; RG-101 ; §7.1
telephone	text	Non (requis pour générer)	Téléphone de contact.	Cahier §11 ; RG-101
email	text	Non (requis pour générer)	Adresse électronique de contact.	Cahier §11 ; RG-101
numero_agrement_bio	text	Non (requis pour générer)	Numéro d’agrément biologique, imprimé sur les documents.	Cahier §11 ; RG-101 ; §7.1
organisme_certificateur	text	Non (requis pour générer)	Nom de l’organisme certificateur.	Cahier §11 ; RG-101
code_organisme	text	Non (requis pour générer)	Code de l’organisme certificateur, imprimé sur les documents. Aucun contrôle de format (EC-027) : le certificat porte son propre code d’organisme.	Cahier §11 ; §7.1 ; §3.5
numero_fiscal	text	Non	Numéro fiscal, mention légale.	Cahier §11
rccm	text	Non	Numéro d’immatriculation au registre du commerce.	Cahier §11
banque	text	Non	Nom de la banque, mention bancaire.	Cahier §11
compte	text	Non	Numéro de compte bancaire.	Cahier §11
iban	text	Non	IBAN.	Cahier §11
devise	char(3)	Oui	Code ISO 4217 de la devise de l’organisation. Valeur par défaut XOF. Les montants sont des entiers dans cette devise.	Cahier §11 ; découpage, « Décisions techniques », « Calculs en entiers »
langue_defaut	text	Oui	Langue par défaut de l’entreprise : fr ou en.	Cahier §11 ; EX-072 (§10.6) ; découpage Lot 0
prefixe_lot	text	Non	Préfixe entreprise de trois lettres du numéro de lot. Segment facultatif du modèle de numérotation, désactivé par défaut (EC-010).	Cahier §11 ; §3.5
format_numerotation	jsonb	Non	Modèle de numérotation paramétrable par organisation, composé de segments (EC-010). Format par défaut : {AA}{code campagne}/{nature}/{produit}/{NNNN}, séquence sur 4 chiffres. Le préfixe entreprise est un segment facultatif. Chaque campagne en fige une copie à l’ouverture.	Analyse critique, « Arbitrages de flexibilité » ; avenant n°1 ; découpage Lot 1
source_conformite	text	Oui	Source des décisions de conformité : interne ou externe. Une seule source active à la fois.	Analyse critique, « Module contrôle interne » ; avenant, « Certification »
seuils_eligibilite_groupe	jsonb	Non	Seuils paramétrables et versionnés, critères alternatifs (EC-014) : surfaces maximales en m² (cultures, serres, prairies), chiffre d’affaires et production standard avec leur devise, taille du groupe, coût de certification.	Cahier §11 ; §2.4 ; RG-024, RG-025 ; analyse critique, « Erreurs réglementaires »
taux_controle_interne	jsonb	Non	Taux de ré-inspection, d’échantillonnage et de contrôle non annoncé. Paramètres par organisation et par référentiel, initialisés à 5 %, 2 % et 10 % (EC-005). Les contrôles non annoncés sont un sous-ensemble des ré-inspections. Chaque nombre est arrondi à l’entier supérieur.	Cahier §11 ; RG-026
logo_fichier_id	uuid	Non	Fichier du logo, référence vers la table fichier (EC-029).	Cahier §11 (« logo ») ; décision EC-029
cree_le	timestamptz	Oui	Date de création de la ligne.	Décision EC-022
modifie_le	timestamptz	Oui	Date de dernière modification.	Décision EC-022

Contraintes. code unique et de 3 à 10 caractères (cahier §11). activites limitée aux cinq valeurs (cahier §2.2). Les tables enfants pointent vers id.

Table fichier

Référence vers un fichier du stockage objet compatible S3, dans la même région que la base (EC-029). La base ne garde que la référence. Table en ajout seul : un fichier déposé n’est jamais écrasé, une nouvelle version crée un nouvel objet, ce qui garde la preuve pour les audits.

Colonne	Type PostgreSQL	Obligatoire	Description	Source
id	uuid	Oui	Identifiant du fichier.	Décision EC-029
organisation_id	uuid	Oui	Organisation propriétaire.	Décision EC-032
cle_objet	text	Oui	Clé de l’objet dans le stockage, unique.	Décision EC-029
type_mime	text	Oui	Type MIME du fichier.	Décision EC-029
taille_octets	bigint	Oui	Taille en octets.	Décision EC-029
empreinte_sha256	char(64)	Oui	Empreinte SHA-256 du contenu, en hexadécimal minuscule, pour la preuve en audit.	Décision EC-029
depose_le	timestamptz	Oui	Date de dépôt.	Décision EC-029

Contraintes. Ni modification ni suppression ni vidage. Sur le téléphone, les fichiers restent en file locale jusqu’à la synchronisation, puis sont envoyés par une adresse d’envoi signée, sans passer par PowerSync.

Table campagne

Période de référence d’une filière.

Colonne	Type PostgreSQL	Obligatoire	Description	Source
id	uuid	Oui	Identifiant stable, public.	Cahier §11 « Campagne » ; EX-010
organisation_id	uuid	Oui	Organisation propriétaire.	Cahier §11 (« entreprise »)
nom	text	Oui	Nom de la campagne.	Cahier §11 ; §3.4
code	text	Oui	Code de campagne, inséré dans le numéro de lot. Unique par organisation, toutes années confondues (EC-023) : deux campagnes la même année ne produisent pas deux numéros identiques.	Avenant n°1, « Identifiant de campagne dans le numéro de lot » ; découpage Lot 1
annee	smallint	Oui	Année d’ouverture de la campagne, dont les deux derniers chiffres forment « AA » du numéro (EC-023). Elle ne change plus après la création.	Décision EC-023
filiere	text	Oui	Filière de la campagne.	Cahier §11 ; §3.4
version_pack	text	Oui	Version du pack de la filière, figée à l’ouverture de la campagne.	Découpage, « Décisions techniques », « Paramétrage et packs »
date_ouverture	date	Oui	Date d’ouverture.	Cahier §11 ; §3.4
date_cloture_prevue	date	Oui	Date de clôture prévisionnelle.	Cahier §11 ; §3.4
date_cloture_reelle	date	Non	Date de clôture effective, renseignée à la clôture.	Cahier §11
format_numerotation	jsonb	Oui	Copie figée, à l’ouverture, du modèle de numérotation de l’organisation en vigueur (EC-010).	Décision EC-010
statut	text	Oui	ouverte, en_cours_de_cloture ou close. Plusieurs campagnes peuvent être ouvertes en même temps.	Cahier §11 ; §3.4
cree_le	timestamptz	Oui	Date de création de la ligne.	Décision EC-022
modifie_le	timestamptz	Oui	Date de dernière modification.	Décision EC-022

Contraintes. Unicité de code par organisation (EC-023, EC-031). annee égale à l’année de date_ouverture (EC-023). Une campagne close n’est plus modifiable (cahier §3.1, RG-121, RG-124).

Colonnes du cahier non reprises au Lot 1. bilan_archive (produit à la clôture, hors Lot 1). prix_base, prime_bio, regle_ristourne (paramètres de paiement, module paiements du Lot 4 selon le découpage).

Table producteur

Objet pivot partagé avec le futur système de contrôle interne (SCI).

Colonne	Type PostgreSQL	Obligatoire	Description	Source
id	uuid	Oui	Identifiant stable, public, jamais réutilisé. Clé d’échange avec le SCI.	Cahier §11 « Producteur » ; EX-010 ; RG-010
organisation_id	uuid	Oui	Organisation qui enrôle le producteur.	Cahier §11 (« entreprise »)
code	text	Oui	Code producteur, unique dans l’organisation, définitif, jamais réutilisé.	Cahier §11 ; RG-010
nom_complet	text	Oui	Nom du producteur.	Cahier §11
telephone	text	Non	Téléphone. Donnée personnelle : accès réservé aux rôles qui en ont l’usage.	Cahier §11 ; EX-063 (§10.7)
village	text	Non	Village.	Cahier §11
canton	text	Non	Canton.	Cahier §11
zone_production	text	Non	Zone de production.	Cahier §11
groupement_id	uuid	Non	Groupement d’appartenance. Table à créer ensuite, clé étrangère non posée.	Cahier §11 (« groupement ») ; découpage Lot 1
date_inscription	date	Non	Date d’inscription du producteur, transmise au SCI.	Cahier §9.4 (« Liste des membres »)
type_piece_identite	text	Non	Type de la pièce d’identité du producteur.	Découpage Lot 1 ; analyse critique, « Incohérences internes et modèle de données »
numero_piece_identite	text	Non	Numéro de la pièce d’identité, pour le signalement des doublons (EC-031). Donnée personnelle sensible.	Découpage Lot 1 ; EX-063
chiffre_affaires_annuel	bigint	Non	Chiffre d’affaires annuel, surveillé s’il est renseigné. Entier dans la plus petite unité de sa devise (EC-028).	Cahier §11 ; §2.4 ; RG-024
chiffre_affaires_devise	char(3)	Non	Devise ISO 4217 du chiffre d’affaires. Renseignée si et seulement si le montant l’est.	Décision EC-028
production_standard	bigint	Non	Production standard annuelle, surveillée s’il est renseignée. Entier dans la plus petite unité de sa devise (EC-028).	Cahier §11 ; §2.4 ; RG-024
production_standard_devise	char(3)	Non	Devise ISO 4217 de la production standard. Renseignée si et seulement si le montant l’est.	Décision EC-028
agent_collecteur_id	uuid	Non	Agent collecteur rattaché. Table des utilisateurs à créer ensuite, clé étrangère non posée.	Cahier §11 (« agent_collecteur »)
en_controle_doublon	boolean	Oui	Vrai quand une autre fiche de l’organisation porte la même pièce d’identité (EC-031). Le doublon est signalé, non bloquant : tant qu’il n’est pas résolu par fusion, aucune capacité ni aucun paiement n’est ouvert pour cette fiche.	Décision EC-031 ; AV1-02
actif	boolean	Oui	Faux quand le producteur est désactivé : il disparaît des listes de saisie sans altérer l’historique. Valeur par défaut vrai.	Cahier §11 ; RG-011
cree_le	timestamptz	Oui	Date de création de la ligne.	Décision EC-022
modifie_le	timestamptz	Oui	Date de dernière modification.	Décision EC-022

Contraintes. (organisation_id, code) unique (RG-010). Suppression interdite, désactivation seule (RG-011). Le couple type et numéro de pièce d’identité n’est pas unique : un doublon est signalé par en_controle_doublon, jamais refusé, car un enregistrement fait hors-ligne ne peut pas être rejeté (EC-031, AV1-02).

Colonnes du cahier non reprises ici. statut_certification, organisme, numero_certificat, dates, source_statut, reference_decision_sci passent dans statut_conformite et certificat (EC-024).

Table parcelle

Unité de production d’un producteur pour un produit. Porte le plafond de collecte et l’éligibilité.

Colonne	Type PostgreSQL	Obligatoire	Description	Source
id	uuid	Oui	Identifiant stable, public, jamais réutilisé.	Cahier §11 « Parcelle » ; EX-010
organisation_id	uuid	Oui	Organisation propriétaire.	Découpage, « Multi-clients »
producteur_id	uuid	Oui	Producteur exploitant la parcelle.	Cahier §11 ; RG-012
produit_id	uuid	Oui	Produit cultivé. Table à créer ensuite, clé étrangère non posée.	Cahier §11 ; §3.1
code_parcelle	text	Oui	Code de la parcelle, transmis au SCI.	Cahier §11 ; §9.4
type_surface	text	Oui	cultures, serres ou prairies_permanentes. Chaque type a son seuil d’éligibilité.	Cahier §11 ; RG-024
geometrie	geometry(Polygon, 4326)	Non	Contour de la parcelle en WGS 84. Absent tant que la parcelle n’est pas cartographiée. Valide au sens de ST_IsValid pour recevoir une capacité.	Avenant n°1, « Polygones » ; découpage, « PostGIS » et Lot 1 (critère de fin)
surface_mesuree_m2	integer	Non	Surface calculée depuis la géométrie via geography, en m². Colonne générée, recalculée à chaque modification de la géométrie.	Avenant n°1 ; découpage, « PostGIS »
surface_declaree_m2	integer	Non	Surface déclarée, qui ne sert plus qu’à contrôler l’écart avec la surface mesurée. En m², entier (EC-030).	Avenant n°1 ; découpage Lot 1 ; cahier §11 (« surface_ha »)
rendement_ref_g_ha	bigint	Non	Rendement de référence en grammes par hectare. Requis pour calculer une capacité. Révisable, avec historique. En g/ha, entier (EC-030).	Cahier §11 ; RG-020, RG-023
actif	boolean	Oui	Faux quand la parcelle est désactivée. Valeur par défaut vrai.	Cahier §11
cree_le	timestamptz	Oui	Date de création de la ligne.	Décision EC-022
modifie_le	timestamptz	Oui	Date de dernière modification.	Décision EC-022

Contraintes et index. Géométrie valide (ST_IsValid) et index spatial GiST pour détecter les chevauchements avec ST_Intersects (découpage, « PostGIS »). Unicité de code_parcelle par organisation (EC-031).

Colonnes du cahier non reprises ici. qualite_bio et date_conversion passent dans statut_conformite. latitude et longitude sont remplacées par le polygone. capacite_campagne est calculée par campagne, non stockée (EC-012). L’écart entre surface déclarée et mesurée est calculé (découpage Lot 1). L’historique des rendements (RG-023) demandera une table à part, à modéliser.

Table certificat

Certificat d’un opérateur, d’un groupement ou d’un fournisseur, pour un référentiel.

Colonne	Type PostgreSQL	Obligatoire	Description	Source
id	uuid	Oui	Identifiant du certificat.	Avenant n°1, « Certification »
organisation_id	uuid	Oui	Organisation qui enregistre le certificat.	Découpage, « Multi-clients »
operateur_id	uuid	Non	Opérateur titulaire. Exactement un des trois titulaires est renseigné (EC-025).	Avenant n°1 (« d’un opérateur ou d’un groupement ») ; décision EC-025
groupement_id	uuid	Non	Groupement titulaire. Table à créer ensuite, clé étrangère non posée.	Avenant n°1 ; décision EC-025
fournisseur_id	uuid	Non	Fournisseur titulaire (RG-013). Table à créer ensuite, clé étrangère non posée.	RG-013 ; décision EC-025
referentiel	text	Oui	Code du référentiel : bio UE, NOP, JAS, Naturland, Fairtrade, Rainforest Alliance, etc. Liste paramétrable.	Avenant n°1, « Certification »
organisme_nom	text	Oui	Organisme certificateur.	Avenant n°1 ; RG-013
organisme_code	text	Oui	Code de l’organisme. Sans contrôle de format (EC-027).	Avenant n°1 (« organisme et code »)
numero	text	Oui	Numéro du certificat.	Avenant n°1 ; RG-013
perimetre	jsonb	Non	Périmètre : activités, produits, sites.	Avenant n°1
date_debut	date	Oui	Début de validité.	Avenant n°1 (« validité ») ; RG-013
date_fin	date	Oui	Fin de validité. Alerte 60 jours avant.	Avenant n°1 ; RG-013, RG-016
statut	text	Oui	valide, suspendu ou retire. « Expiré » n’est pas stocké : il est calculé depuis date_fin à la date de l’opération (EC-025).	Avenant n°1 ; RG-013
fichier_id	uuid	Non	PDF du certificat, référence vers la table fichier (EC-029).	Avenant n°1 ; décision EC-029
cree_le	timestamptz	Oui	Date de création de la ligne.	Décision EC-022
modifie_le	timestamptz	Oui	Date de dernière modification.	Décision EC-022

Règles. Aucune vente sous un référentiel sans certificat valide à la date de l’opération, pour chaque maillon (avenant, EC-017). Un certificat expiré, calculé depuis date_fin, bloque la revendication du référentiel (découpage Lot 1, critère de fin). Le texte libre est proscrit (RG-013).

Table statut_conformite

Statut par unité et par référentiel. Table en ajout seul : un changement de statut ajoute une ligne, car l’historique est conservé (avenant n°1, « Certification »).

Colonne	Type PostgreSQL	Obligatoire	Description	Source
id	uuid	Oui	Identifiant de la ligne d’historique.	Avenant n°1, « Certification »
organisation_id	uuid	Oui	Organisation propriétaire.	Découpage, « Multi-clients »
producteur_id	uuid	Non	Producteur concerné. Exactement une des trois unités est renseignée (EC-025).	Avenant n°1 (« Unité ») ; décision EC-025
parcelle_id	uuid	Non	Parcelle concernée.	Avenant n°1 ; décision EC-025
zone_cueillette_id	uuid	Non	Zone de cueillette concernée. Table à créer ensuite, clé étrangère non posée.	Avenant n°1 ; décision EC-025
referentiel	text	Oui	Code du référentiel concerné.	Avenant n°1 (« par référentiel »)
etat	text	Oui	conversion_annee_1, conversion_annee_2_et_plus, bio, conventionnel, suspendu, retire. « Retiré » (certification retirée par sanction) est distinct de « conventionnel » (EC-026).	Avenant n°1, « Certification » ; cahier §9.4
debut_conversion	date	Non	Début de la conversion.	Avenant n°1 ; cahier §11 (« date_conversion »)
date_eligibilite	date	Non	Date à laquelle l’unité devient éligible.	Avenant n°1
date_effet	date	Oui	Date d’effet du statut.	Cahier §9.4 (« date d’effet »)
source	text	Oui	interne (SCI de BioTrace) ou externe (outil tiers). Une seule source active par organisation.	Avenant n°1 ; EX-012 (§9.1) ; cahier §11 (« source_statut »)
reference_decision	text	Non	Référence de la décision du SCI.	Cahier §11 (« reference_decision_sci ») ; §9.4
mis_a_jour_le	timestamptz	Oui	Date de mise à jour reçue de la source.	EX-012

Contraintes. Ajout seul : ni modification ni suppression (avenant : « L’historique est conservé »). source cohérente avec organisation.source_conformite, vérifiée par un déclencheur. Pas de cree_le ni de modifie_le, car la table n’est jamais modifiée.

Critères de fin du Lot 1 et colonnes concernées

Critère (découpage, Lot 1)	Colonnes
Deux campagnes du même produit la même année produisent des numéros distincts.	campagne.code, campagne.annee
Une parcelle sans polygone valide ne peut pas recevoir de capacité.	parcelle.geometrie, parcelle.rendement_ref_g_ha
Un certificat expiré bloque la revendication du référentiel correspondant.	certificat.date_fin, certificat.statut (« expiré » calculé), statut_conformite.etat

Tables à modéliser ensuite

groupement, produit, fournisseur, client, zone_cueillette, utilisateur, site, emplacement, historique du rendement de référence, table des contrats. groupement, produit, fournisseur, zone_cueillette et utilisateur apparaissent dans le diagramme comme cibles de références.