Modèle de Données
MCD - MLD

1.1 Introduction

Le modèle de données de REWAPP est conçu pour supporter l'ensemble des fonctionnalités de la plateforme de cashback nouvelle génération :

• Cashback bancaire direct avec virement sous 2-3 semaines
• Cashback commerçant via QR code (validité 60 secondes)
• Système de fidélité avec 5 paliers (Bronze, Silver, Gold, Platine, Diamant)
• Gestion des partenaires avec validation admin obligatoire
• Traçabilité complète des transactions et mouvements de points

1.2 Principes de Modélisation

• Normalisation : Respect de la 3ème forme normale (3NF) pour éviter la redondance des données
• Performance : Index stratégiques sur les colonnes fréquemment interrogées
• Intégrité : Contraintes de clés étrangères, contraintes CHECK, triggers
• Audit : Traçabilité complète via AuditLogs pour toutes les modifications sensibles
• Sécurité : Chiffrement AES-256 des données sensibles, hashage bcrypt des mots de passe
• Scalabilité : Partitionnement des tables volumineuses, stratégie d'archivage

1.3 Technologies de Stockage

1.4 Entités Principales

Le modèle comporte 16 entités principales organisées en 4 domaines fonctionnels :

2.1 Diagramme Entités-Relations

@startuml REWAPP_MCD
!define PRIMARY_KEY(x) <b>x</b>
!define FOREIGN_KEY(x) <i>x</i>

entity "Users" as users {
  PRIMARY_KEY(id) : UUID
  --
  email : VARCHAR(255) <<unique>>
  password_hash : VARCHAR(255)
  first_name : VARCHAR(100)
  last_name : VARCHAR(100)
  phone : VARCHAR(20)
  status : ENUM
  loyalty_tier : ENUM
  total_points_earned : INTEGER
}

entity "Cards" as cards {
  PRIMARY_KEY(id) : UUID
  --
  FOREIGN_KEY(user_id) : UUID
  bank_name : VARCHAR(100)
  card_token : VARCHAR(255)
  card_last4 : VARCHAR(4)
  is_primary : BOOLEAN
}

entity "Merchants" as merchants {
  PRIMARY_KEY(id) : UUID
  --
  name : VARCHAR(255)
  siret : VARCHAR(14) <<unique>>
  cashback_rate : DECIMAL
  status : ENUM
  validation_status : ENUM
}

entity "Transactions" as transactions {
  PRIMARY_KEY(id) : UUID
  --
  FOREIGN_KEY(user_id) : UUID
  FOREIGN_KEY(merchant_id) : UUID
  amount : DECIMAL
  points_credited : INTEGER
  status : ENUM
}

entity "PointsLedger" as points_ledger {
  PRIMARY_KEY(id) : UUID
  --
  FOREIGN_KEY(user_id) : UUID
  type : ENUM
  amount : INTEGER
  expiry_date : DATE
}

entity "QRCodes" as qr_codes {
  PRIMARY_KEY(id) : UUID
  --
  FOREIGN_KEY(user_id) : UUID
  points_amount : INTEGER
  status : ENUM
  expires_at : TIMESTAMP
}

' Relations
users ||--o{ cards : possède
users ||--o{ transactions : effectue
users ||--o{ points_ledger : accumule
users ||--o{ qr_codes : génère
merchants ||--o{ transactions : reçoit
transactions ||--o{ points_ledger : génère

@enduml

2.2 Entité Users (Utilisateurs)

Gestion des comptes clients de l'application mobile REWAPP.

2.3 Entité Cards (Cartes Bancaires)

Cartes bancaires liées via solution OpenBanking (Budget Insight, Plaid, Tink). Permet la détection automatique des transactions.

2.4 Entité BankAccounts (Comptes Bancaires)

IBAN pour virements cashback bancaire. Stockage chiffré AES-256.

2.5 Entité Merchants (Partenaires)

Commerces affiliés à REWAPP.

2.6 Entité Transactions

Historique des achats détectés automatiquement via la solution bancaire. Génère des points selon le taux du partenaire et le palier de l'utilisateur.

2.7 Entité PointsLedger (Journal des Points)

Journal comptable des crédits/débits de points. Implémente la méthode FIFO pour l'expiration (points les plus anciens dépensés en premier).

2.8 Entité QRCodes

Codes QR générés pour paiement cashback commerçant. Validité 60 secondes, usage unique, signature HMAC-SHA256.

2.9 Entité Withdrawals (Virements)

Demandes de conversion points → virement bancaire. Seuil minimum 100 points, délai 2-3 semaines maximum.

2.10 Entité LoyaltyTiers (Paliers Fidélité)

Configuration des 5 paliers de fidélité avec bonus cashback progressifs.

2.11-2.16 Autres Entités

3.1 Schéma Relationnel PostgreSQL

Le schéma PostgreSQL implémente le MCD avec les optimisations suivantes :

• UUID comme clé primaire (sécurité, distribution)
• Contraintes CHECK pour validation des données
• Index stratégiques pour les requêtes fréquentes
• Triggers pour audit automatique
• Partitionnement pour les tables volumineuses

3.2 Scripts DDL des Tables

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    birth_date DATE,
    email_verified BOOLEAN DEFAULT FALSE,
    two_factor_enabled BOOLEAN DEFAULT FALSE,
    biometric_enabled BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) DEFAULT 'active'
        CHECK (status IN ('active', 'suspended', 'deleted', 'pending_verification')),
    loyalty_tier VARCHAR(20) DEFAULT 'bronze'
        CHECK (loyalty_tier IN ('bronze', 'silver', 'gold', 'platinum', 'diamond')),
    total_points_earned INTEGER DEFAULT 0,
    total_spent_eur DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    deleted_at TIMESTAMP
);

CREATE TABLE transactions (
    id UUID DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    merchant_id UUID NOT NULL REFERENCES merchants(id) ON DELETE RESTRICT,
    card_id UUID NOT NULL REFERENCES cards(id) ON DELETE RESTRICT,
    external_transaction_id VARCHAR(255) UNIQUE,
    amount DECIMAL(10,2) NOT NULL CHECK (amount > 0),
    currency VARCHAR(3) DEFAULT 'EUR',
    transaction_date TIMESTAMP NOT NULL,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'validated', 'cancelled', 'refunded')),
    points_credited INTEGER DEFAULT 0,
    loyalty_tier_bonus DECIMAL(5,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (id, transaction_date)
) PARTITION BY RANGE (transaction_date);

CREATE TABLE points_ledger (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    transaction_id UUID,
    qr_code_id UUID,
    withdrawal_id UUID,
    type VARCHAR(20) NOT NULL 
        CHECK (type IN ('credit', 'debit', 'expiration', 'adjustment')),
    amount INTEGER NOT NULL,
    balance_after INTEGER NOT NULL,
    source VARCHAR(50) NOT NULL
        CHECK (source IN ('transaction', 'qr_payment', 'withdrawal', 
                          'campaign', 'bonus', 'expiration', 'admin_adjustment')),
    expiry_date DATE,
    created_at TIMESTAMP DEFAULT NOW()
);

3.3 Index et Optimisations

-- Index Table Users
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_users_loyalty_tier ON users(loyalty_tier);

-- Index Table Merchants (avec géolocalisation)
CREATE INDEX idx_merchants_location ON merchants USING GIST (
    ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
) WHERE latitude IS NOT NULL AND longitude IS NOT NULL;
CREATE INDEX idx_merchants_category ON merchants(category, status);

-- Index Table Transactions
CREATE INDEX idx_transactions_user_id ON transactions(user_id, transaction_date DESC);
CREATE INDEX idx_transactions_merchant_id ON transactions(merchant_id, transaction_date DESC);

-- Index Table PointsLedger (FIFO expiration)
CREATE INDEX idx_points_ledger_expiry ON points_ledger(expiry_date) 
    WHERE type = 'credit' AND expiry_date IS NOT NULL;

-- Index Table QRCodes (expiration)
CREATE INDEX idx_qr_codes_expires ON qr_codes(expires_at) WHERE status = 'active';

3.4 Fonctions Métier

-- Fonction de calcul des points (1 point = 0,10€)
CREATE OR REPLACE FUNCTION calculate_points(
    amount_eur DECIMAL, cashback_rate DECIMAL, tier_bonus DECIMAL
) RETURNS INTEGER AS $$
BEGIN
    RETURN FLOOR(amount_eur * (cashback_rate / 100) * (1 + tier_bonus / 100) * 10);
END;
$$ LANGUAGE plpgsql;

-- Fonction de calcul montant virement (10 points = 0,95€)
CREATE OR REPLACE FUNCTION calculate_withdrawal_amount(points INTEGER)
RETURNS DECIMAL AS $$
BEGIN
    RETURN ROUND(points * 0.095, 2);
END;
$$ LANGUAGE plpgsql;

-- Fonction de calcul montant QR code (10 points = 1,05€)
CREATE OR REPLACE FUNCTION calculate_qr_amount(points INTEGER)
RETURNS DECIMAL AS $$
BEGIN
    RETURN ROUND(points * 0.105, 2);
END;
$$ LANGUAGE plpgsql;