Base de données

À quoi sert cette page

Tout l’état durable de Titreo E-Pay vit dans une base Firebird : les marchands, les sessions de paiement et leurs legs (jambes de paiement), la file d’événements asynchrones (outbox), les secrets PSP chiffrés, les clés d’API, les déduplications d’idempotence, les utilisateurs du back-office, etc.

Cette page explique trois choses :

1. Comment on parle à la base — un driver natif sans ORM, et pourquoi.
2. Comment on gère connexion et transactions — la classe FirebirdDb et l’unit of work FirebirdUnitOfWork (isolation READ_COMMITTED).
3. Ce que contient le schéma — chaque migration 001 → 020 et chaque table principale, colonne par colonne.

Le mapping entre les lignes SQL et les objets du domaine (sérialiseurs) est documenté en fin de page. Tous les chemins sont relatifs à app/. Le code de cette zone vit dans api/src/infrastructure/db/.

Où se branche cette couche

La base est un détail d’infrastructure. Le domaine ne sait pas qu’il existe une base Firebird : il manipule des entités (PaymentSession, PaymentLeg) via des ports (interfaces) implémentés ici par des repositories Firebird*. Voir Architecture hexagonale et Couche Infrastructure.

1. Le driver : pas d’ORM

L’accès se fait via node-firebird-driver-native (dépendance ^3.3.0 dans api/package.json) et des requêtes SQL écrites à la main. Pas de Prisma, pas de TypeORM, pas de query builder.

C’est une règle non négociable du projet (CLAUDE.md, règle 1) :

Pas d’ORM lourd : node-firebird-driver-native + requêtes SQL directes. Prisma ne supporte pas Firebird.

Conséquences concrètes que tout repaire doit connaître :

• Le driver renvoie les colonnes en MAJUSCULES (ID, MERCHANT_ID, TOTAL_AMOUNT…), parce que Firebird met les identifiants non quotés en majuscules. Les types *Row dans le code reflètent donc des champs majuscules.
• Les entiers BIGINT (montants en centimes) peuvent revenir en number ou en bigint selon la valeur ; le helper toNumber() dans serializers.ts normalise.
• Les requêtes sont paramétrées (?) — jamais d’interpolation de valeurs dans le SQL. La pagination utilise select first N … (syntaxe Firebird), pas LIMIT.

Driver natif = librairie cliente Firebird requise

node-firebird-driver-native charge la bibliothèque cliente Firebird (fbclient) du système via getDefaultLibraryFilename(). Sur des machines où elle n’est pas compilée (cas connu sur certains Mac), on passe par l’image Docker dev. Le runtime nécessite donc soit fbclient installé, soit le conteneur.

2. Connexion et transactions

FirebirdDb — la connexion (api/src/infrastructure/db/firebird-db.ts)

FirebirdDb encapsule le Client natif et l’Attachment (la connexion ouverte). On l’obtient par deux factories statiques :

// connexion à une base supposée existante
const db = await FirebirdDb.connect(config.firebird)

// connexion, ou création de la base si absente (utilisé par les migrations)
const db = await FirebirdDb.createIfMissing(config.firebird)

L’URI est construite au format Firebird host/port:database. createIfMissing tente de se connecter, et en cas d’échec crée la base avec forcedWrite: true.

Le cœur est withTransaction, qui ouvre une transaction, exécute le travail, commit en cas de succès et rollback en cas d’exception :

async withTransaction<T>(work: (tx: Transaction) => Promise<T>): Promise<T> {
  const tx = await this.attachment.startTransaction({
    isolation: TransactionIsolation.READ_COMMITTED,
    readCommittedMode: 'RECORD_VERSION',
    waitMode: 'WAIT',
    accessMode: 'READ_WRITE',
  })
  try {
    const result = await work(tx)
    await tx.commit()
    return result
  } catch (err) {
    if (tx.isValid) await tx.rollback().catch(() => undefined)
    throw err
  }
}

Les paramètres d’isolation sont importants pour un orchestrateur de paiement :

Paramètre	Valeur	Effet
isolation	READ_COMMITTED	On voit les commits des autres transactions au fil de l’eau ; pas de snapshot figé.
readCommittedMode	RECORD_VERSION	Lit la dernière version committée d’un enregistrement sans bloquer sur les écritures concurrentes.
waitMode	WAIT	En cas de conflit d’écriture, on attend plutôt que d’échouer immédiatement.
accessMode	READ_WRITE	Transaction en lecture/écriture.

dispose() ferme proprement l’attachment puis le client.

FirebirdUnitOfWork — l’unité de travail (api/src/infrastructure/db/firebird-uow.ts)

Certaines opérations doivent écrire dans plusieurs tables de façon atomique : par exemple créer/mettre à jour une session, écrire des entrées d’outbox, et marquer un événement webhook comme vu — le tout ou rien. C’est le rôle de l’unit of work, qui implémente le port UnitOfWork (api/src/application/ports/unit-of-work.ts).

run() ouvre une seule transaction via withTransaction, instancie les repositories transactionnels partageant cette même tx, et les passe au travail métier :

async run<T>(work: (repos: TransactionalRepos) => Promise<T>): Promise<T> {
  return this.db.withTransaction(async (tx) => {
    const repos: TransactionalRepos = {
      sessions: new FirebirdSessionRepo(this.db, this.aead, tx),
      outbox: new FirebirdOutboxRepo(this.db, this.aead, tx),
      webhookEvents: new FirebirdWebhookEventsRepo(this.db, tx),
    }
    return work(repos)
  })
}

Tous les repositories Firebird* suivent la même convention : un tx optionnel dans le constructeur. Si tx est fourni, ils s’y rattachent (cas unit of work) ; sinon ils ouvrent leur propre transaction courte via db.withTransaction. C’est ce qui rend l’orchestration atomique. Voir Outbox & orchestration et L’orchestrateur.

flowchart TD
  UC["Use case (application)"] --> UOW["FirebirdUnitOfWork.run()"]
  UOW --> TX["FirebirdDb.withTransaction (READ_COMMITTED)"]
  TX --> R1["FirebirdSessionRepo(tx)"]
  TX --> R2["FirebirdOutboxRepo(tx)"]
  TX --> R3["FirebirdWebhookEventsRepo(tx)"]
  R1 --> DB[("Firebird (.fdb)")]
  R2 --> DB
  R3 --> DB
  TX -->|"succès"| COMMIT["commit"]
  TX -->|"exception"| ROLLBACK["rollback"]

3. Le moteur de migrations

migrate.ts — application des migrations (api/src/infrastructure/db/migrate.ts)

Les migrations sont des fichiers .sql numérotés dans api/src/infrastructure/db/migrations/. Le moteur :

1. Crée la table de suivi migrations_log (version VARCHAR(64) PRIMARY KEY, applied_at TIMESTAMP) si absente.
2. Liste les versions déjà appliquées.
3. Lit tous les *.sql, triés par nom (d’où la numérotation 001, 002… qui garantit l’ordre).
4. Pour chaque fichier non appliqué : supprime les commentaires de ligne (--), découpe sur ; en fin de ligne en statements individuels, et exécute chaque statement dans sa propre transaction.
5. Insère la version dans migrations_log.

runMigrations() renvoie { applied, skipped }.

Migrations non transactionnelles globalement, et non versionnées dans Git

Chaque statement est dans sa propre transaction (Firebird ne supporte pas le DDL transactionnel multi-statement comme PostgreSQL). Si une migration échoue à mi-parcours, les statements déjà exécutés sont committés mais la version n’est pas marquée appliquée : une réexécution rejouera tout le fichier depuis le début, ce qui peut échouer sur un objet déjà créé. Écrire les migrations de façon idempotente ou réparer à la main en cas d’incident.

migrate-cli.ts — point d’entrée CLI (api/src/infrastructure/db/migrate-cli.ts)

Charge la config via loadConfig(), ouvre la base avec createIfMissing, lance runMigrations et affiche le résultat JSON. C’est ce que déclenche pnpm db:migrate (voir section finale).

4. Les migrations, une par une

#	Fichier	Ce qu’elle ajoute
001	001_init.sql	Schéma initial : tables merchants, payment_sessions, payment_legs (FK + ON DELETE CASCADE), outbox. Index sur statut/expiration de session, sur payment_legs.session_id, sur outbox(status, next_attempt_at) et outbox(idempotency_key).
002	002_webhook_secret.sql	merchants.webhook_secret VARCHAR(128) — secret de signature des webhooks sortants (legacy, par-marchand).
003	003_psp_configs.sql	Table merchant_psp_configs : secrets PSP chiffrés (secret_key_enc, webhook_secret_enc), clé publique, account_ref. Contrainte UNIQUE(merchant_id, provider_type).
004	004_api_keys.sql	Table api_keys : hash de clé + préfixe + 4 derniers caractères, scopes, dates last_used_at / revoked_at / expires_at. Index unique sur key_hash.
005	005_psp_config_metadata.sql	Ajoute secret_key_last4 et webhook_secret_last4 à merchant_psp_configs (affichage masqué côté console).
006	006_idempotency_keys.sql	Table idempotency_keys : empreinte requête + réponse mise en cache. Unique sur (api_key_id, method, path, idem_key).
007	007_audit_logs.sql	Table audit_logs : action, acteur (api_key_id, actor_scopes), cible, IP, user-agent, payload.
008	008_users.sql	Tables admin_users, merchant_users, user_sessions (sessions de back-office, hash de token, expiration).
009	009_merchant_gift_provider.sql	merchants.gift_provider_type VARCHAR(64) — fournisseur de cartes cadeaux (ex. titreo).
010	010_gift_cvv.sql	payment_legs.gift_cvv VARCHAR(8) — CVV de carte cadeau, en clair (corrigé en 018).
011	011_webhook_events_seen.sql	Table webhook_events_seen : déduplication des webhooks PSP entrants, PK (provider_type, event_id).
012	012_users_roles_invites.sql	Renomme les rôles merchant_users (admin→developer, member→ops), ajoute invited_by_user_id, crée merchant_user_invites.
013	013_merchant_branding.sql	Ajoute theme_tokens BLOB TEXT, brand_logo_url, branding_updated_at à merchants (white-label console/checkout).
014	014_fix_branding_and_roles.sql	Re-normalise les rôles merchant_users (rejoue les UPDATE de 012 par sécurité).
015	015_outbox_lock.sql	Ajoute LOCKED_BY / LOCKED_AT à outbox — verrou applicatif pour traitement concurrent par les workers.
016	016_webhook_events_per_merchant.sql	Rend la déduplication webhook par marchand : ajoute merchant_id, change la PK en (provider_type, merchant_id, event_id).
017	017_login_lockout.sql	Ajoute failed_login_attempts + locked_until à merchant_users et admin_users (anti brute-force).
018	018_encrypt_gift_cvv.sql	Supprime gift_cvv, ajoute gift_cvv_enc VARCHAR(512) — le CVV est désormais chiffré au repos.
019	019_webhook_deliveries.sql	Table webhook_deliveries : journal des webhooks sortants (statut, code HTTP, tentatives, signature).
020	020_merchant_psp_nullable.sql	Rend merchants.psp_provider_type nullable : ce n’est plus qu’un indice de PSP primaire pour le manifest bootstrap ; chaque marchand configure ses PSP via merchant_psp_configs.

Lecture en diagonale

Les migrations 002, 005, 009, 010, 013, 015, 017, 018, 020 sont des ALTER TABLE (évolution de tables existantes). Les autres créent de nouvelles tables. L’état courant d’une table = 001_init + tous les ALTER qui la touchent ensuite.

5. Les tables principales

Les noms de tables sont en minuscules dans le SQL ; le driver renvoie les colonnes en majuscules. Montants toujours en plus petite unité monétaire (centimes), type BIGINT. Identifiants VARCHAR(64) générés applicativement.

merchants

Le marchand (client de la plateforme). Source : 001 + 002, 009, 013, 020.

Colonne	Type	Rôle
id	VARCHAR(64) PK	Identifiant marchand.
name	VARCHAR(255)	Nom (réutilisé comme brand_name).
psp_provider_type	VARCHAR(64) nullable (depuis 020)	Indice de PSP primaire (manifest bootstrap).
gift_provider_type	VARCHAR(64)	Fournisseur cartes cadeaux.
webhook_url / webhook_secret	VARCHAR(512) / VARCHAR(128)	Endpoint + secret de signature des webhooks sortants.
session_ttl_minutes	INTEGER (def. 30)	Durée de vie des sessions.
active	BOOLEAN	Marchand actif.
theme_tokens	BLOB TEXT	JSON de tokens de thème (white-label).
brand_logo_url, branding_updated_at	VARCHAR(4096), TIMESTAMP	Branding.

payment_sessions

La session de paiement (le panier à régler). Voir Couche Domain et Machines à états.

Colonne	Type	Rôle
id	VARCHAR(64) PK	Identifiant session.
merchant_id	FK merchants(id)	Propriétaire.
reference	VARCHAR(128)	Référence côté marchand ; UNIQUE(merchant_id, reference).
total_amount	BIGINT	Montant total (centimes).
currency	VARCHAR(3)	Devise ISO.
status	VARCHAR(32)	État de la machine à états session.
metadata	VARCHAR(8192)	JSON libre.
expires_at	TIMESTAMP	Expiration ; indexée pour le cleanup.

payment_legs

Une « jambe » de paiement : un instrument (carte cadeau ou PSP) appliqué à une partie du montant. Une session split-tender a plusieurs legs. Source : 001 + 010/018.

Colonne	Type	Rôle
id	VARCHAR(64) PK	Identifiant leg.
session_id	FK payment_sessions(id) ON DELETE CASCADE	Session parente.
leg_type	VARCHAR(16)	Type (gift / psp…).
amount, currency	BIGINT, VARCHAR(3)	Part du montant portée par ce leg.
status	VARCHAR(32)	État de la machine à états leg.
provider_type, provider_ref	VARCHAR	Adapter et référence côté fournisseur.
error_code, error_message	VARCHAR	Dernière erreur.
captured_at / canceled_at / released_at / refunded_at	TIMESTAMP	Horodatages d’étape.
card_token, card_last4, emitter	VARCHAR	Données carte (cadeau / bancaire).
balance_before, balance_after	BIGINT	Solde carte cadeau avant/après.
transaction_id, auth_id, payment_method	VARCHAR	Réfs PSP.
gift_cvv_enc	VARCHAR(512)	CVV carte cadeau chiffré (depuis 018).
requires_action, action_url	BOOLEAN, VARCHAR(1024)	3-D Secure / redirection.
leg_position	INTEGER	Ordre d’application (séquencement).

outbox

File d’effets de bord asynchrones (appels PSP/Titreo, webhooks) garantissant la cohérence transactionnelle. Source : 001 + 015. Voir Outbox & orchestration et Workers.

Colonne	Type	Rôle
id	VARCHAR(64) PK	Identifiant entrée.
session_id, leg_id	VARCHAR(64)	Cibles.
action	VARCHAR(32)	Effet à exécuter.
payload	VARCHAR(16384)	JSON de paramètres.
status	VARCHAR(16)	pending / done / …
attempts, max_attempts	INTEGER (def. 0 / 5)	Compteur de retries.
next_attempt_at	TIMESTAMP	Backoff ; indexé avec status.
idempotency_key	VARCHAR(255)	Clé d’idempotence de l’effet ; indexée.
last_error	VARCHAR(2000)	Dernière erreur.
locked_by, locked_at	VARCHAR(64), TIMESTAMP	Verrou worker (depuis 015).

merchant_psp_configs

Configuration PSP par marchand, avec secrets chiffrés au repos. Source : 003 + 005. Voir Sécurité.

Colonne	Type	Rôle
id	VARCHAR(64) PK	Identifiant config.
merchant_id	FK merchants(id) cascade	Propriétaire.
provider_type	VARCHAR(64)	Adapter (stripe, adyen…) ; UNIQUE(merchant_id, provider_type).
secret_key_enc, webhook_secret_enc	VARCHAR(2048)	Secrets chiffrés AEAD (jamais en clair).
secret_key_last4, webhook_secret_last4	VARCHAR(8)	Aperçu masqué (affichage console).
public_key, account_ref	VARCHAR	Clé publique, référence de compte.
active	BOOLEAN	Config active.

Chiffrement, pas hachage

secret_key_enc / webhook_secret_enc sont chiffrés (AEAD via infrastructure/crypto/aead) : on doit pouvoir les déchiffrer pour appeler le PSP. Le firebird-merchant-psp-config-repo ne déchiffre qu’à la demande explicite ({ decrypt: true }), avec des données associées (AAD) liant le secret à son marchand/provider. Détails dans Sécurité.

api_keys

Clés d’API marchand (auth des appels publics). Source : 004.

Colonne	Type	Rôle
id	VARCHAR(64) PK	Identifiant clé.
merchant_id	FK cascade	Propriétaire.
name	VARCHAR(128)	Libellé.
key_hash	VARCHAR(128)	Hash de la clé (la clé en clair n’est jamais stockée) ; UNIQUE.
key_prefix, key_last4	VARCHAR(16), VARCHAR(8)	Préfixe + aperçu masqué.
scopes	VARCHAR(512)	Portées (CSV).
last_used_at, revoked_at, expires_at	TIMESTAMP	Cycle de vie.

idempotency_keys

Cache de réponses pour la déduplication des requêtes HTTP idempotentes. Source : 006. Voir Idempotence.

Colonne	Type	Rôle
id	VARCHAR(64) PK	Identifiant.
api_key_id, method, path, idem_key	—	Clé composite UNIQUE(api_key_id, method, path, idem_key).
request_hash	VARCHAR(128)	Empreinte de la requête (détecte un rejeu avec corps différent).
response_status, response_body	INTEGER, VARCHAR(16384)	Réponse mémorisée à rejouer.
expires_at	TIMESTAMP	Expiration ; indexée pour cleanup.

webhook_deliveries (webhooks sortants)

Journal des notifications envoyées au marchand. Source : 019.

Colonne	Type	Rôle
id	VARCHAR(64) PK	Identifiant livraison.
merchant_id, outbox_entry_id, session_id	VARCHAR(64)	Contexte.
event_type	VARCHAR(64)	Type d’événement.
url, payload, signature	VARCHAR	Cible, corps, signature HMAC.
status, http_status, error_message, attempts	—	Résultat et retries.
created_at, delivered_at	TIMESTAMP	Horodatages.

webhook_events_seen (webhooks entrants)

Déduplication des événements reçus des PSP. Source : 011 + 016.

Colonne	Type	Rôle
provider_type, merchant_id, event_id	VARCHAR	PK composite (provider_type, merchant_id, event_id) depuis 016.
received_at, processed_at	TIMESTAMP	Réception / traitement ; received_at indexé.

Cette table garantit qu’un même événement PSP n’est traité qu’une fois — pilier de l’Idempotence.

Utilisateurs du back-office : admin_users, merchant_users, user_sessions

Source : 008 + 012, 017. Voir Couche HTTP (auth).

Table	Colonnes clés
admin_users	id PK, email UNIQUE, password_hash, role, email_verified_at, last_login_at, disabled_at, + failed_login_attempts/locked_until (017).
merchant_users	id PK, merchant_id FK cascade, email UNIQUE, password_hash, role (owner/developer/ops), tokens de vérif email, invited_by_user_id (012), failed_login_attempts/locked_until (017).
user_sessions	id PK, user_id + user_kind (admin/merchant), token_hash UNIQUE, expires_at, last_seen_at, ip, user_agent, revoked_at.

Divergence de nommage avec la spec

Le périmètre demandait une table users. Il n’existe pas de table users : l’authentification est répartie en admin_users (administrateurs plateforme) et merchant_users (utilisateurs marchand), unifiées au niveau session par user_sessions.user_kind. Si une doc parle d’une table users unique, elle est obsolète.

merchant_user_invites

Invitations d’utilisateurs marchand. Source : 012.

Colonne	Type	Rôle
id	VARCHAR(64) PK	Identifiant invitation.
merchant_id	FK cascade	Marchand.
email, role	VARCHAR	Destinataire et rôle proposé.
token_hash	VARCHAR(128)	Hash du jeton d’invitation ; UNIQUE.
expires_at, invited_by_user_id	—	Validité, émetteur.
accepted_at, revoked_at	TIMESTAMP	Cycle de vie.

audit_logs

Journal d’audit applicatif. Source : 007. id PK, merchant_id, api_key_id, actor_scopes, action, target_type/target_id, ip, user_agent, payload. Indexé sur (merchant_id, created_at) et action.

Branding

Le branding n’est pas une table dédiée : il vit dans les colonnes theme_tokens / brand_logo_url / branding_updated_at de merchants (migration 013), parsées en objet MerchantBranding par firebird-merchant-repo.ts.

6. Mapping SQL ↔ domaine : serializers.ts

Le fichier api/src/infrastructure/db/serializers.ts traduit les lignes SQL brutes (SessionRow, LegRow, OutboxRow, champs majuscules) en objets du domaine. Fonctions clés :

Fonction	Signature (entrée → sortie)	Rôle
rowToLeg	(LegRow, decryptCvv?) → PaymentLeg	Reconstruit un PaymentLeg ; déchiffre GIFT_CVV_ENC si une fonction de déchiffrement est fournie.
rowToSession	(SessionRow, legs) → PaymentSession	Reconstruit la session avec ses legs ; parse METADATA (JSON).
rowToOutbox	(OutboxRow) → OutboxEntry	Reconstruit une entrée d’outbox ; parse PAYLOAD (JSON).

Détails à connaître :

• toNumber() normalise number | bigint → number (montants).
• Money.of(montant, devise) reconstruit les valeurs monétaires du domaine.
• rowToLeg passe par PaymentLeg.fromSnapshot(...) et rowToSession par PaymentSession.fromSnapshot(...) : on reconstruit un agrégat valide depuis un instantané, sans rejouer la machine à états.

// chemin d'appel typique (lecture d'une session)
//  FirebirdSessionRepo.findById
//   → SELECT payment_sessions (1 ligne) + SELECT payment_legs (N lignes)
//   → rowToLeg(legRow, decryptCvv) pour chaque leg          (serializers.ts)
//   → rowToSession(sessionRow, legs)                         (serializers.ts)
//   → PaymentSession (agrégat domaine, prêt pour un use case)

7. Comment migrer

En local

# depuis app/api/ — applique toutes les migrations non encore appliquées
pnpm db:migrate

La sortie est le JSON { applied: [...], skipped: [...] }. Idempotent : relancer ne réapplique pas ce qui est déjà dans migrations_log.

Via Docker (recette dev)

Le service api-migrate dans docker-compose.dev.yml (profil tools) exécute pnpm db:migrate contre le conteneur Firebird, avec FB_HOST=firebird et FB_DATABASE=/var/lib/firebird/data/orchpay.fdb :

docker compose -f docker-compose.dev.yml --profile tools run --rm api-migrate

Le script dev-up.sh l’invoque automatiquement au démarrage de l’environnement ($COMPOSE run --rm api-migrate).

Ajouter une migration

Créer api/src/infrastructure/db/migrations/0NN_description.sql avec le numéro suivant. Le tri par nom de fichier garantit l’ordre — garder la numérotation à deux/trois chiffres et la zéro-padder. Séparer les statements par ; en fin de ligne. Écrire le DDL de façon défensive (la réexécution partielle après échec rejoue tout le fichier).

Fichiers de la zone

Fichier (api/src/infrastructure/db/)	Rôle	Exports clés
firebird-db.ts	Connexion + transactions	FirebirdDb, FirebirdConfig
firebird-uow.ts	Unit of work transactionnel	FirebirdUnitOfWork
serializers.ts	Mapping lignes SQL → domaine	rowToLeg, rowToSession, rowToOutbox, types *Row
migrate.ts	Moteur de migrations	runMigrations, MigrateResult
migrate-cli.ts	Point d’entrée CLI (pnpm db:migrate)	(script)
migrations/001…020.sql	Définition/évolution du schéma	(DDL)
firebird-*-repo.ts	Repositories (un par agrégat)	classes Firebird*Repo

Voir aussi

• Couche Infrastructure — vue d’ensemble des adapters et repositories
• Architecture hexagonale — pourquoi la base est derrière des ports
• Outbox & orchestration — rôle de la table outbox
• Idempotence — idempotency_keys, webhook_events_seen
• Sécurité — chiffrement des secrets PSP et CVV
• Workers — consommation de l’outbox, cleanup
• Parcours d’appel — du HTTP jusqu’à la persistance