DOC
Accueil/ Projets/ KOBIKE Tracking Notifier/ ADR 0004 : Modèle de données relationnel (multi-tables)

ADR 0004 : Modèle de données relationnel (multi-tables)

  • Statut : Accepté
  • Date : 2026-07-13
  • Auteur : Antigravity

Contexte

Le rapport_0001 initial proposait une table unique tracking_notifications. Ce modèle est trop plat pour les besoins réels :

  • Un même client peut passer plusieurs commandes → dupliquer nom/email/téléphone sur chaque ligne est source d'incohérences.
  • Une commande (Platform order number, ex. KO260107-1) peut comporter plusieurs lignes/expéditions (plusieurs SKU, plusieurs colis, donc plusieurs numéros de tracking).
  • Le document Tencent Docs comporte plusieurs feuilles (onglets), typiquement une par période (année ou semestre). Il faut savoir de quelle feuille/période provient chaque commande.
  • On veut un suivi des commandes déjà traitées (historique), pas seulement un journal d'emails.

Nous adoptons donc un modèle relationnel normalisé.

Décision

Vue d'ensemble des entités

feuille_source 1 ─── * commande * ─── 1 client
                          │
                          │ 1
                          ▼
                          * ligne_commande   (le tracking_number vit ici)
                          │
                          │ 1
                          ▼
                          * notification     (journal des emails envoyés - anti-doublon)
  • client : une personne physique, dédoublonnée par email (ou téléphone à défaut).
  • commande : l'en-tête d'une commande, identifiée par numero_commande (Platform order number). Porte le client et l'adresse de livraison.
  • ligne_commande : une ligne d'expédition (SKU + tracking). C'est ici que se trouve tracking_number, car une commande peut être expédiée en plusieurs colis.
  • notification : le journal des emails de suivi effectivement envoyés. Sert de garde anti-doublon.
  • feuille_source : une feuille/onglet du Tencent Docs (une par période), pour tracer la provenance et gérer le multi-feuilles.

Schéma PostgreSQL

-- Feuilles/onglets du Tencent Docs (le doc s'étend sur plusieurs feuilles, ~1 par année/semestre)
CREATE TABLE feuille_source (
  id                BIGSERIAL PRIMARY KEY,
  nom_feuille       TEXT NOT NULL,            -- ex : "2026 S1"
  periode           TEXT,                     -- ex : "2026-S1" ou "2026011"
  tencent_sheet_id  TEXT UNIQUE,              -- identifiant technique de l'onglet (tab id / sub_id)
  derniere_synchro  TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Clients (dédoublonnés)
CREATE TABLE client (
  id          BIGSERIAL PRIMARY KEY,
  nom         TEXT,
  email       TEXT,
  telephone   TEXT,
  pays        TEXT,
  source      TEXT,                            -- 'tencent' | 'shopify' | 'shifter' | 'manuel'
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Un email unique au plus (insensible à la casse), mais l'email peut être NULL au départ
CREATE UNIQUE INDEX uq_client_email ON client (lower(email)) WHERE email IS NOT NULL;
CREATE INDEX idx_client_telephone ON client (telephone);

-- Commandes (en-tête)
CREATE TABLE commande (
  id                 BIGSERIAL PRIMARY KEY,
  numero_commande    TEXT NOT NULL UNIQUE,     -- Platform order number, ex : KO260107-1
  client_id          BIGINT REFERENCES client(id),
  feuille_source_id  BIGINT REFERENCES feuille_source(id),
  plateforme         TEXT,                     -- ex : KOBIKE
  createur           TEXT,                     -- 制单人
  date_commande      DATE,
  pays               TEXT,
  region             TEXT,                     -- State/Province 州/省
  ville              TEXT,
  adresse            TEXT,
  code_postal        TEXT,
  devise             TEXT,
  numero_facture     TEXT,                     -- INVOICE NUMBER
  revendeur          TEXT,                     -- Revendeur ? (à confirmer)
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_commande_numero ON commande (numero_commande);

-- Lignes de commande / expéditions - le tracking_number vit ici
CREATE TABLE ligne_commande (
  id                 BIGSERIAL PRIMARY KEY,
  commande_id        BIGINT NOT NULL REFERENCES commande(id) ON DELETE CASCADE,
  sku                TEXT,
  produit            TEXT,                     -- ex : "N1 AIR-GR（油刹）"
  quantite           INTEGER,
  quantite_expediee  INTEGER,
  prix_unitaire      NUMERIC(12,2),
  subvention         NUMERIC(12,2),            -- Activity subsidies 活动补贴
  entrepot           TEXT,                     -- 发货仓库 (ex : 米宝 Mibao)
  date_expedition    DATE,
  tracking_number    TEXT,                     -- ⚠️ champ déclencheur
  statut_logistique  TEXT,
  remarques          TEXT,
  created_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at         TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_ligne_commande_id ON ligne_commande (commande_id);
CREATE INDEX idx_ligne_tracking ON ligne_commande (tracking_number);

-- Journal des notifications envoyées (garde anti-doublon)
CREATE TABLE notification (
  id                 BIGSERIAL PRIMARY KEY,
  commande_id        BIGINT REFERENCES commande(id),
  ligne_commande_id  BIGINT REFERENCES ligne_commande(id),
  numero_commande    TEXT NOT NULL,
  tracking_number    TEXT NOT NULL,
  email_destinataire TEXT,
  canal              TEXT NOT NULL DEFAULT 'email',
  statut             TEXT NOT NULL DEFAULT 'envoye',   -- 'envoye' | 'echec'
  date_envoi         TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT uq_notif_commande_tracking UNIQUE (numero_commande, tracking_number)
);

Clé unique anti-doublon : (numero_commande, tracking_number)

C'est la décision demandée. Justification :

  • numero_commande seul est trop strict : si une commande part en plusieurs colis (plusieurs tracking numbers), on ne notifierait le client que pour le premier colis.
  • tracking_number seul est fragile : on ne peut pas garantir qu'un transporteur ne réémettra jamais un identifiant réutilisé ou nul/malformé.
  • Le couple (numero_commande, tracking_number) identifie exactement « un envoi de suivi pour ce colis de cette commande » - c'est la granularité métier de la notification.

Protection à l'insertion :

INSERT INTO notification (numero_commande, tracking_number, email_destinataire, statut, ...)
VALUES ($1, $2, $3, 'envoye', ...)
ON CONFLICT (numero_commande, tracking_number) DO NOTHING;

Cas « email manquant » - ne pas polluer l'anti-doublon

Si l'email est introuvable (ni dans client, ni Shopify, ni Shifter), on n'insère PAS de ligne dans notification. On lève une alerte interne (voir ADR 0002) et la ligne sera réévaluée au prochain déclenchement - quand l'email aura été renseigné (colonne Customer email remplie, portail client, ou fiche Shifter mise à jour). Écrire un statut email_manquant dans notification bloquerait au contraire l'envoi futur à cause de la contrainte UNIQUE. Le suivi des lignes en attente d'email se fait via une vue :

CREATE VIEW v_lignes_a_notifier AS
SELECT lc.*, c.numero_commande, cl.email
FROM ligne_commande lc
JOIN commande c   ON c.id = lc.commande_id
LEFT JOIN client cl ON cl.id = c.client_id
LEFT JOIN notification n
       ON n.numero_commande = c.numero_commande
      AND n.tracking_number = lc.tracking_number
      AND n.statut = 'envoye'
WHERE lc.tracking_number IS NOT NULL
  AND lc.tracking_number <> ''
  AND n.id IS NULL;                         -- pas encore notifié avec succès

Gestion des feuilles multiples (multi-onglets par période)

Le Tencent Docs grossit par feuilles (une par année/semestre). Chaque commande référence sa feuille_source. Le déclencheur HiFlow (ADR 0001) est configuré par feuille ; à l'ajout d'un nouvel onglet, on ajoute un nouveau déclencheur pointant sur ce tencent_sheet_id et on insère la ligne correspondante dans feuille_source. Aucune migration de schéma n'est nécessaire pour absorber une nouvelle période.

Conséquences

  • Positives :
    • Modèle normalisé : pas de duplication client, historique de commandes propre, suivi par période.
    • Anti-doublon robuste au niveau colis, tolérant aux commandes multi-colis.
    • Réutilisable directement par l'application de gestion client (ADR 0003) et par la logique d'enrichissement email (ADR 0002).
  • Négatives :
    • Plus de tables à créer/maintenir qu'un modèle mono-table.
    • La logique n8n doit faire des UPSERT (client → commande → ligne_commande) avant d'évaluer l'envoi.