Rapport projet : kobike-tracking-notifier

Nom de projet suggéré : kobike-tracking-notifier Alternative si multi-marques à terme : mjy-shipment-notifier

⚠️ Version révisée (2026-07-13). Cette version abandonne l'approche initiale « export manuel vers Google Drive » et le mono-table. La source est lue directement depuis Tencent Docs via un déclenchement cloud événementiel (ADR 0001) et les données sont stockées dans un modèle relationnel multi-tables (ADR 0004).

1. Objectif

Automatiser l'envoi d'un email de suivi de colis aux clients dès qu'un numéro de tracking logistique apparaît sur une ligne de commande dans le fichier Tencent Docs, sans jamais envoyer deux fois le même email pour le même colis (couple numero_commande + tracking_number).

2. Contexte

• Le fichier source est un Tencent Docs (腾讯文档) : https://docs.qq.com/sheet/DQ1lxSE5hQ1V3WlJh, mis à jour manuellement par des collègues en Chine. L'accès en lecture nous sera accordé prochainement.
• Le champ clé du déclenchement est Logistics tracking number (物流跟踪号) : quand cette cellule passe de vide à remplie sur une ligne, il faut notifier le client par email.
• Pas de Google Drive / Google Sheets : l'export manuel .xlsx est abandonné (étape manuelle, retard, dépendance inutile).
• Pas de cron/polling pour le moment : le délai de réponse depuis la Chine est irrégulier ; on ne veut pas « appeler pour rien ». On part sur un déclenchement cloud événementiel (Tencent HiFlow) qui pousse la ligne modifiée vers n8n. Voir ADR 0001.
• Le document s'étend sur plusieurs feuilles (onglets), typiquement une par année/semestre → pris en compte par la table feuille_source (ADR 0004).
• Infrastructure cible : n8n, PostgreSQL et NocoDB en conteneurs Docker sur le même VPS, orchestrés via Coolify, sur le même réseau Docker interne (pas de Supabase, pas d'appel réseau externe pour la base).

3. Mapping des colonnes du fichier source

Le fichier est un tableur Tencent Docs - les en-têtes sont bilingues (chinois + anglais/français), collés sans séparateur clair. Mapping identifié colonne par colonne (inchangé) :

Ligne d'exemple observée (décodée) :

periode: 2026011
createur: 陈维 (Chen Wei)
date_commande: 2026/1/2
plateforme: KOBIKE
numero_commande: KO260107-1
nom: David Cerqueira
telephone: 617014180
pays: FR
region: Haut-de-Seine
ville: Clichy
adresse: 7 rue marguerite Yourcenar
code_postal: 92110
produit: N1 AIR-GR（油刹）
quantite: 1
date_expedition: 2026/1/7
entrepot: 米宝 (Mibao)
sku: AN1A-EU
quantite_expediee: 1
tracking_number: DG021887687500028
statut_logistique: OK received by the client
email: (vide dans cet exemple - d'où l'enrichissement Shopify/Shifter, ADR 0002)
numero_facture: KOBIKE账单-02.25.2026

⚠️ Points à vérifier avec les collègues avant implémentation :

1. Confirmer l'en-tête exact et la position de la colonne Customer email - vide sur l'exemple fourni. À terme elle sera renseignée ; en attendant, l'email est récupéré via Shopify puis Shifter (ADR 0002).
2. Confirmer le nom exact des colonnes (les en-têtes bilingues peuvent varier en formatage d'un export à l'autre).
3. Confirmer que Platform order number est la clé métier fiable (recommandé), NUMBER 序号 n'étant qu'une séquence par feuille.

4. Architecture cible (événementielle, sans cron)

[Tencent Docs - feuille modifiée]
            |
            v
[Tencent HiFlow : déclencheur "ligne ajoutée/modifiée dans la feuille"]
            |  (POST webhook, données de la ligne)
            v
[n8n : nœud Webhook]  <-- URL n8n publique + jeton secret
            |
            v
[n8n : normalisation de la ligne (node Code) -> JSON propre]
            |
            v
[n8n : UPSERT client -> UPSERT commande -> UPSERT ligne_commande (Postgres)]
            |
            v
[n8n : la ligne a-t-elle un tracking_number non vide ?] --non--> (stop)
            | oui
            v
[n8n : déjà notifié ? SELECT notification WHERE (numero_commande, tracking_number)]
            |  non
            v
[n8n : email connu ? client.email -> sinon Shopify API -> sinon Shifter API]  (ADR 0002)
        |  email trouvé                         |  email introuvable
        v                                        v
[n8n : Send Email (suivi colis)]        [n8n : alerte interne, PAS d'insert notification]
        |
        v
[n8n : INSERT notification ... ON CONFLICT (numero_commande, tracking_number) DO NOTHING]

Comme HiFlow ne pousse qu'une ligne à la fois (l'événement = un changement réel), on n'a plus besoin de la requête groupée sur 10k lignes qui était nécessaire dans l'approche « fichier complet ». Cette requête groupée reste utile uniquement pour le fallback polling (voir §6).

5. Schéma PostgreSQL

Le schéma complet (5 tables : feuille_source, client, commande, ligne_commande, notification) est défini et justifié dans ADR 0004.

Points clés :

• Anti-doublon : contrainte UNIQUE (numero_commande, tracking_number) sur notification, combinée à INSERT ... ON CONFLICT DO NOTHING. Robuste aux commandes multi-colis, tolérant à un double déclenchement HiFlow rapproché.
• Email manquant : on n'insère pas de notification → la ligne est réévaluée au prochain déclenchement une fois l'email disponible (vue v_lignes_a_notifier).
• Multi-feuilles : chaque commande référence sa feuille_source (une par période).

6. Fallback polling (non implémenté - pour mémoire)

Si HiFlow devenait inutilisable, on basculerait sur un polling de l'API WeCom Smart Sheet (Cron n8n). Dans ce seul cas, la vérification anti-doublon doit se faire en une seule requête groupée et non une par ligne :

SELECT numero_commande, tracking_number
FROM notification
WHERE (numero_commande, tracking_number) = ANY($1);   -- $1 : tableau de couples présents dans le batch

Le delta « déjà notifié / à notifier » se calcule ensuite côté n8n. Non prioritaire pour l'instant (voir décision ADR 0001).

7. Connexion PostgreSQL depuis n8n (infra Coolify)

• Host : nom du service Postgres tel que défini dans Coolify (pas localhost, pas d'IP publique - nom du conteneur sur le réseau Docker interne)
• Port : 5432 (interne, pas d'exposition publique)
• Database / User / Password : générés par Coolify, visibles dans l'onglet du service Postgres
• SSL : désactivé en principe (connexion interne au réseau Docker)
• Node n8n : Postgres (natif), pas de passage par HTTP Request.

8. Enrichissement de l'email client

Le fichier ne fournit pas (encore) l'email de façon fiable. La stratégie retenue : cache local PostgreSQL d'abord, puis fallback API Shopify, puis Shifter, détaillée dans ADR 0002. Guides de configuration des API :

• docs/guide/04-shopify-api.md
• docs/guide/05-shifter-api.md

9. Points ouverts / décisions à prendre

• Obtenir l'accès en lecture au Tencent Docs, puis configurer HiFlow (guide 03)
• Confirmer la position/nom réel de la colonne Customer email
• Définir le contenu de l'email (template, langue FR/EN selon pays, lien de suivi transporteur selon entrepot)
• Décider si l'entrepôt/transporteur (entrepot) détermine l'URL de tracking à inclure
• Gestion d'erreur : ordre envoi → insert notification (un échec après envoi entraîne au pire un renvoi au run suivant, acceptable ; l'inverse risquerait un non-envoi)

10. Prochaines étapes suggérées pour Claude Code

1. Créer les 5 tables Postgres (script SQL de l'ADR 0004)
2. Configurer le déclencheur HiFlow → webhook n8n (guide 03)
3. Construire le workflow n8n selon l'architecture §4 (UPSERT client/commande/ligne, check notification, enrichissement, envoi, insert)
4. Écrire le node Code n8n de normalisation d'une ligne Tencent → JSON
5. Créer le template d'email de notification
6. Tester sur une feuille de test avant d'activer sur les feuilles de production