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é 15 minutes)
• Système de fidélité avec 4 paliers (Bronze, Silver, Gold, 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 28 entités principales organisées en 4 domaines fonctionnels :

2.1 Diagramme Entités-Relations

Diagramme MCD - REWAPP

2.2 Entité Users (Utilisateurs)

Gestion des comptes clients de l'application mobile REWAPP.

2.3 Entité bank_accounts (Comptes Bancaires Plaid)

Comptes bancaires liés via Plaid OpenBanking. Permet la détection automatique des transactions bancaires.

2.4 Entité BankAccounts (Comptes Bancaires)

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

2.5 Entité partners (Partenaires/Commerces)

Commerces affiliés à REWAPP avec validation admin obligatoire.

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é recoins_transactions (Journal des Points)

Journal comptable des crédits/débits de recoines. 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é 15 minutes, usage unique, signature HMAC-SHA256.

2.9 Entité client_payouts (Virements)

Demandes de conversion recoines → virement bancaire. Seuil minimum 100 recoines, traitement sous 2-3 jours ouvrés.

2.10 Entité LoyaltyTiers (Paliers Fidélité)

Configuration des 4 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,
    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', '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,
    partner_id UUID NOT NULL REFERENCES partners(id) ON DELETE RESTRICT,
    bank_account_id UUID NOT NULL REFERENCES bank_accounts(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 recoins_transactions (
    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 partners USING GIST (
    ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
) WHERE latitude IS NOT NULL AND longitude IS NOT NULL;
CREATE INDEX idx_partners_category ON partners(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 recoins_transactions (FIFO expiration)
CREATE INDEX idx_recoins_expiry ON recoins_transactions(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,20€)
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 = 2,00€)
CREATE OR REPLACE FUNCTION calculate_withdrawal_amount(points INTEGER)
RETURNS DECIMAL AS $$
BEGIN
    RETURN ROUND(points * 0.200, 2);
END;
$$ LANGUAGE plpgsql;

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