18 KiB
Modèle Physique de Données — SchotzimmO
Source de vérité physique des bases de données SchotzimmO. Document transversal à toute la plateforme. Ce document complète le modèle conceptuel (diagrammes ER des
design.md) en décrivant le détail physique : tables, colonnes, types MariaDB, nullabilité, clés, index, relations et règles.Architecture à deux niveaux de base (voir
socle-plateforme/design.md) :
- Base Socle (centrale, unique) : authentification, référentiel des réseaux et leurs informations de connexion, abonnements, catalogue des modules/packs, administration, KPI d'usage remontés par réseau. Aucune donnée métier produite par les mandataires.
- Base Réseau (une par réseau) : toutes les données métier cloisonnées d'un réseau — profil mandataire, parrainage/hiérarchie, et les données des Modules (Carnet d'adresses, KMS, CRM, Agenda, Facturation…).
Le document grandit module par module : les sections Socle et Réseau sont posées en premier, puis une sous-section Base Réseau est ajoutée pour chaque Module.
Conventions générales
- SGBD : MariaDB / MySQL, via le provider EF Core
Pomelo.EntityFrameworkCore.MySql. - Moteur & encodage : toutes les tables en InnoDB, charset
utf8mb4, collationutf8mb4_unicode_ci. - Nommage : tables en
snake_casepluriel ; colonnes ensnake_case. Mapping entité C# (PascalCase) vers table/colonne assuré par EF Core. - Clé primaire : colonne
id, typeINT UNSIGNED,AUTO_INCREMENT, sauf exception documentée (ex.mandataire_profiles.account_idréutilise l'identifiant du socle). - Clés étrangères : nommées
<entité>_id. Toute FK au sein d'une même base est une vraie contrainte FK indexée. Les références entre bases différentes (ex.account_iddu socle utilisé dans une base réseau) ne sont pas des contraintes FK MariaDB : ce sont des références logiques (mêmes valeurs d'identifiant), dont la cohérence est assurée applicativement. - Référence inter-bases :
account_idest généré par la Base Socle (accounts.id) et réutilisé tel quel comme identifiant du mandataire dans sa Base Réseau. Il n'y a jamais de jointure SQL traversant deux bases. - Dates & heures : type
DATETIME, valeurs stockées en UTC ; horodatage de création par défautCURRENT_TIMESTAMP. - Chaînes :
VARCHAR(n)dimensionné (e-mail 255, nom/prénom 100…) ;TEXTpour le texte libre long. - Booléens :
TINYINT(1)(0/1), mappés surboolC#. - Énumérations :
VARCHARà valeurs contraintes applicativement (ex.role,status), plutôt que le typeENUMnatif. - Montants :
DECIMAL(12,2). Distances/quantités :DECIMALdimensionné selon le Module. - Secrets : mots de passe et jetons stockés hachés. Aucun secret de connexion (chaîne de connexion d'une base réseau) n'est stocké en clair en base : la Base Socle ne stocke qu'une clé logique de connexion, résolue vers un secret fourni par configuration.
- Suppressions : par défaut physiques. Les cas de conservation (rétrogradation d'abonnement) reposent sur la présence des données et l'état d'accès calculé.
Légende des tableaux de colonnes
- Clé :
PK= clé primaire,FK= clé étrangère (même base),FK*= référence logique inter-bases (non contrainte),UK= unicité. - Null :
NON=NOT NULL,OUI= nullable.
PARTIE A — Base Socle (centrale, unique)
Référence : socle-plateforme/design.md. Cette base ne contient aucune donnée métier produite par les mandataires.
Vue d'ensemble :
| Table | Rôle |
|---|---|
networks |
Référentiel des réseaux + clé logique de connexion à leur base réseau + paramétrage |
accounts |
Comptes (authentification minimale) ; pivot d'identité, rattaché à un réseau |
packs |
Formules d'abonnement (Starter / Pro / Premium) |
module_catalog |
Catalogue des modules et pack requis pour chacun |
subscriptions |
Abonnement courant d'un compte à un pack |
password_resets |
Jetons de réinitialisation de mot de passe (hachés) |
network_usage_kpis |
Indicateurs d'usage remontés par chaque réseau (pas de donnée nominative) |
Table networks
Référentiel des réseaux. Chaque réseau pointe vers sa propre base de données via une clé logique de connexion et porte son paramétrage de personnalisation.
| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
id |
INT UNSIGNED |
NON | PK | auto | Identifiant du réseau |
code |
VARCHAR(40) |
NON | UK | — | Code stable du réseau (ex. iad, demo) |
name |
VARCHAR(100) |
NON | UK | — | Libellé affiché (ex. « IAD ») |
db_connection_key |
VARCHAR(60) |
NON | UK | — | Clé logique résolvant la chaîne de connexion de la base réseau via la configuration (jamais la chaîne elle-même) |
theme_settings |
TEXT |
OUI | — | NULL |
Paramétrage de personnalisation (thème, libellés) au format JSON |
is_active |
TINYINT(1) |
NON | — | 1 |
Réseau ouvert à l'inscription si actif et initialisé |
is_provisioned |
TINYINT(1) |
NON | — | 0 |
Base réseau initialisée (schéma + données de référence) |
created_at |
DATETIME |
NON | — | CURRENT_TIMESTAMP |
Date de création (UTC) |
Index : PRIMARY (id), UNIQUE (code), UNIQUE (name), UNIQUE (db_connection_key).
Relations : 1 — N vers accounts.network_id (même base). Référencé logiquement par toutes les tables des bases réseau.
Données de référence (seed) : iad (« IAD ») et demo (réseau de démonstration / tests).
Règles : un réseau n'est ouvert à l'inscription que si is_active = 1 ET is_provisioned = 1 (onboarding réseau mené par l'Administrateur au préalable).
Table accounts
Comptes des mandataires — authentification minimale uniquement. Le profil métier et le parrainage vivent dans la Base Réseau (mandataire_profiles). L'id est réutilisé comme account_id côté réseau.
| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
id |
INT UNSIGNED |
NON | PK | auto | Identifiant du compte ; réutilisé comme account_id dans la base réseau |
network_id |
INT UNSIGNED |
NON | FK | — | Réseau de rattachement (non modifiable après création) |
email |
VARCHAR(255) |
NON | UK | — | Adresse e-mail (login), normalisée en minuscules. Unique sur toute la plateforme |
password_hash |
VARCHAR(255) |
NON | — | — | Empreinte du mot de passe (salée) ; jamais en clair |
role |
VARCHAR(20) |
NON | — | 'mandataire' |
Rôle : mandataire ou admin |
is_active |
TINYINT(1) |
NON | — | 1 |
Compte actif (désactivation = 0) |
created_at |
DATETIME |
NON | — | CURRENT_TIMESTAMP |
Date de création (UTC) |
Index : PRIMARY (id), UNIQUE (email), INDEX (network_id).
Relations : network_id vers networks.id (même base). id référencé logiquement par mandataire_profiles.account_id dans la base réseau.
Règles :
- L'e-mail est unique sur l'ensemble de la plateforme (un e-mail = un seul compte, un seul réseau).
- Un rôle
adminest un administrateur SchotzimmO (transverse) ; il n'est pas rattaché aux données métier d'un réseau. - La désactivation d'un compte doit être propagée applicativement vers le profil réseau correspondant.
Table packs
Formules d'abonnement. Trois packs ordonnés par niveau, inclusifs.
| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
id |
INT UNSIGNED |
NON | PK | auto | Identifiant du pack |
pack_key |
VARCHAR(20) |
NON | UK | — | Clé stable : starter, pro, premium |
label |
VARCHAR(50) |
NON | — | — | Libellé affiché |
level |
TINYINT UNSIGNED |
NON | UK | — | Niveau d'inclusion : 0 < 1 < 2 |
is_free |
TINYINT(1) |
NON | — | 0 |
Pack gratuit (vrai pour Starter) |
Index : PRIMARY (id), UNIQUE (pack_key), UNIQUE (level).
Données de référence (seed) : starter (level 0, gratuit), pro (level 1), premium (level 2).
Table module_catalog
Catalogue des modules et pack minimal requis.
| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
id |
INT UNSIGNED |
NON | PK | auto | Identifiant de l'entrée catalogue |
module_key |
VARCHAR(40) |
NON | UK | — | Clé stable du module (ex. carnet-adresses, kms) |
label |
VARCHAR(80) |
NON | — | — | Libellé affiché |
required_pack_id |
INT UNSIGNED |
NON | FK | — | Pack minimal débloquant le module |
is_active |
TINYINT(1) |
NON | — | 1 |
Module proposé/activé sur la plateforme |
Index : PRIMARY (id), UNIQUE (module_key), INDEX (required_pack_id).
Relations : required_pack_id vers packs.id.
Données de référence (seed) : carnet-adresses, kms, facturation vers starter ; crm, agenda vers pro.
Table subscriptions
Abonnement d'un compte à un pack. Un seul abonnement actif par compte.
| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
id |
INT UNSIGNED |
NON | PK | auto | Identifiant de l'abonnement |
account_id |
INT UNSIGNED |
NON | FK | — | Compte abonné (même base socle) |
pack_id |
INT UNSIGNED |
NON | FK | — | Pack souscrit |
starts_at |
DATETIME |
NON | — | CURRENT_TIMESTAMP |
Début d'effet (UTC) |
ends_at |
DATETIME |
OUI | — | NULL |
Fin d'effet (UTC) ; NULL = sans échéance |
status |
VARCHAR(10) |
NON | — | 'actif' |
actif ou expire |
Index : PRIMARY (id), INDEX (account_id), INDEX (pack_id), INDEX (account_id, status).
Relations : account_id vers accounts.id ; pack_id vers packs.id (même base).
Règles : au plus un abonnement actif par compte ; rétrogradation conserve les données réseau (consultation seule déduite de la présence de données).
Table password_resets
Jetons de réinitialisation de mot de passe, stockés hachés, à usage unique et durée de vie limitée.
| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
id |
INT UNSIGNED |
NON | PK | auto | Identifiant du jeton |
account_id |
INT UNSIGNED |
NON | FK | — | Compte concerné |
token_hash |
VARCHAR(255) |
NON | — | — | Empreinte du jeton |
expires_at |
DATETIME |
NON | — | — | Échéance d'expiration (UTC) |
used |
TINYINT(1) |
NON | — | 0 |
Jeton déjà consommé |
created_at |
DATETIME |
NON | — | CURRENT_TIMESTAMP |
Date de création (UTC) |
Index : PRIMARY (id), INDEX (account_id), INDEX (token_hash).
Relations : account_id vers accounts.id.
Table network_usage_kpis
Indicateurs d'usage remontés par chaque réseau vers le socle pour les tableaux de bord d'administration. Contient des agrégats non nominatifs (jamais de donnée métier individuelle ; pas de jointure inter-bases).
| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
id |
INT UNSIGNED |
NON | PK | auto | Identifiant de la mesure |
network_id |
INT UNSIGNED |
NON | FK | — | Réseau concerné |
metric_key |
VARCHAR(60) |
NON | — | — | Clé de l'indicateur (ex. nb_mandataires_actifs, ca_total) |
metric_value |
DECIMAL(14,2) |
NON | — | — | Valeur agrégée |
period |
VARCHAR(7) |
OUI | — | NULL |
Période éventuelle au format AAAA-MM |
collected_at |
DATETIME |
NON | — | CURRENT_TIMESTAMP |
Date de remontée (UTC) |
Index : PRIMARY (id), INDEX (network_id), INDEX (network_id, metric_key, period).
Relations : network_id vers networks.id.
Règles : alimentée par un processus de remontée par réseau ; ne contient aucune donnée individuelle de mandataire.
Diagramme physique — Base Socle
erDiagram
networks ||--o{ accounts : "network_id"
networks ||--o{ network_usage_kpis : "network_id"
accounts ||--|| subscriptions : "account_id"
packs ||--o{ subscriptions : "pack_id"
packs ||--o{ module_catalog : "required_pack_id"
accounts ||--o{ password_resets : "account_id"
PARTIE B — Base Réseau (une par réseau)
Référence : socle-plateforme/design.md. Chaque réseau dispose d'une instance de ce schéma. Toutes les tables sont implicitement cloisonnées : la base entière appartient à un seul réseau, il n'y a donc pas de colonne network_id dans les tables réseau (le cloisonnement est physique). Les identifiants account_id proviennent de la Base Socle (FK*, référence logique).
Vue d'ensemble (Socle réseau ; les Modules ajouteront leurs tables) :
| Table | Rôle |
|---|---|
mandataire_profiles |
Profil métier du mandataire ; pivot du parrainage (auto-référence sponsor_account_id) |
declared_members |
Membres déclarés (parrain/filleul) non encore inscrits |
affiliation_requests |
Demandes de confirmation d'affiliation entre mandataires inscrits |
Table mandataire_profiles
Profil métier d'un mandataire au sein de son réseau. La clé primaire réutilise l'account_id de la Base Socle (pas d'auto-incrément). Porte l'arbre de parrainage via l'auto-référence sponsor_account_id.
| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
account_id |
INT UNSIGNED |
NON | PK, FK* | — | Identifiant du compte (provient de accounts.id du socle) |
first_name |
VARCHAR(100) |
NON | — | — | Prénom |
last_name |
VARCHAR(100) |
NON | — | — | Nom |
network_member_id |
VARCHAR(50) |
NON | — | — | Identifiant du mandataire propre à son réseau (obligatoire) |
legal_status |
VARCHAR(30) |
OUI | — | NULL |
Statut juridique (ex. auto-entrepreneur, eirl) |
siren |
VARCHAR(14) |
OUI | — | NULL |
Numéro SIREN/RSAC, optionnel |
sponsor_account_id |
INT UNSIGNED |
OUI | FK | NULL |
Parrain (auto-référence vers mandataire_profiles.account_id) ; NULL = racine |
created_at |
DATETIME |
NON | — | CURRENT_TIMESTAMP |
Date de création (UTC) |
Index : PRIMARY (account_id), UNIQUE (network_member_id), INDEX (sponsor_account_id).
Relations :
account_id: référence logique versaccounts.id(Base Socle), non contrainte en SQL.sponsor_account_idversmandataire_profiles.account_id(auto-référence, même base réseau) : vraie FK, intégrité du parrainage garantie par la base.
Règles :
- Comme la base est propre au réseau, parrain et filleul sont nécessairement du même réseau (cloisonnement physique).
- Absence de cycle vérifiée applicativement à l'écriture (parcours ascendant).
- Au plus un
sponsor_account_idvalidé par mandataire.
Table declared_members
Membres déclarés dans l'arbre de parrainage mais non encore inscrits. Sans donnée métier. Convertis en mandataire_profiles lors du Rapprochement_Email à l'inscription (le compte étant alors créé côté socle).
| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
id |
INT UNSIGNED |
NON | PK | auto | Identifiant du membre déclaré |
email |
VARCHAR(255) |
NON | — | — | E-mail normalisé, clé de Rapprochement_Email |
first_name |
VARCHAR(100) |
OUI | — | NULL |
Prénom déclaré (si connu) |
last_name |
VARCHAR(100) |
OUI | — | NULL |
Nom déclaré (si connu) |
declared_by_account_id |
INT UNSIGNED |
NON | FK | — | Mandataire ayant déclaré (référence mandataire_profiles.account_id) |
relation |
VARCHAR(10) |
NON | — | — | Lien déclaré : parrain ou filleul |
status |
VARCHAR(10) |
NON | — | 'declare' |
declare (supposé) ou invite (invitation envoyée) |
created_at |
DATETIME |
NON | — | CURRENT_TIMESTAMP |
Date de création (UTC) |
Index : PRIMARY (id), INDEX (declared_by_account_id), INDEX (email).
Relations : declared_by_account_id vers mandataire_profiles.account_id (même base).
Règles : clôturé/supprimé lors de la conversion en mandataire_profiles par Rapprochement_Email.
Table affiliation_requests
Demandes de confirmation d'affiliation entre deux mandataires déjà inscrits (cas de « perte de lien »).
| Colonne | Type | Null | Clé | Défaut | Description |
|---|---|---|---|---|---|
id |
INT UNSIGNED |
NON | PK | auto | Identifiant de la demande |
requester_account_id |
INT UNSIGNED |
NON | FK | — | Mandataire initiant la demande |
target_account_id |
INT UNSIGNED |
NON | FK | — | Mandataire dont l'approbation est requise |
proposed_relation |
VARCHAR(10) |
NON | — | — | Lien proposé : parrain ou filleul |
status |
VARCHAR(12) |
NON | — | 'en_attente' |
en_attente, approuvee ou refusee |
created_at |
DATETIME |
NON | — | CURRENT_TIMESTAMP |
Date de création (UTC) |
resolved_at |
DATETIME |
OUI | — | NULL |
Date d'approbation/refus (UTC) |
Index : PRIMARY (id), INDEX (requester_account_id), INDEX (target_account_id), INDEX (status).
Relations : requester_account_id et target_account_id vers mandataire_profiles.account_id (même base).
Règles : l'approbation ne doit pas créer de cycle (vérifié à la résolution). Le cloisonnement réseau est garanti par construction (base propre au réseau).
Diagramme physique — Base Réseau (socle réseau)
erDiagram
mandataire_profiles ||--o| mandataire_profiles : "sponsor_account_id"
mandataire_profiles ||--o{ declared_members : "declared_by_account_id"
mandataire_profiles ||--o{ affiliation_requests : "requester / target"
Note inter-bases :
mandataire_profiles.account_idcorrespond àaccounts.idde la Base Socle (référence logiqueFK*, jamais une FK SQL ni une jointure traversant deux bases).
Sections à venir (ajoutées au fil des Modules — tables de la Base Réseau)
- Carnet d'adresses (à venir)
- KMS (à venir)
- CRM (à venir)
- Agenda (à venir)
- Facturation (à venir)