Africa Bridge Pay Wiki

Appearance

🏦 Architecture Base de Données - Africa Bridge Pay (Production) ​

📋 Table des Matières ​

  1. Stack Technologique
  2. Schéma de Base de Données
  3. Sécurité & Conformité
  4. API Backend
  5. Migration depuis localStorage
  6. Déploiement

🛠 Stack Technologique ​

Base de Données : PostgreSQL 15+ ​

Pourquoi PostgreSQL pour une fintech ?

✅ ACID Compliance : Transactions garanties (Critical pour fintech)
✅ Row-Level Security : Isolation des données par client
✅ Audit Trail natif : Tracking des modifications
✅ JSON Support : Flexibilité pour métadonnées
✅ Mature & Fiable : Utilisé par Stripe, Robinhood, etc.
✅ pgcrypto : Chiffrement natif des données sensibles

Backend : Next.js 14+ API Routes + Prisma ORM ​

typescript
// Architecture recommandée
Next.js (Frontend + Backend)
├── Prisma ORM (Type-safe database access)
├── NextAuth.js (Authentication)
├── Zod (Validation)
└── PostgreSQL (Database)

Alternative : Supabase (PostgreSQL as a Service) ​

✅ Base de données PostgreSQL hébergée
✅ Auth intégrée (JWT, OAuth)
✅ Row Level Security automatique
✅ Realtime subscriptions
✅ Storage pour documents (factures, KYB)
✅ Edge Functions pour logique métier

🗄 Schéma de Base de Données ​

Modèle de Données (Prisma Schema) ​

prisma
// prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// ============================================
// AUTHENTIFICATION & UTILISATEURS
// ============================================

model User {
  id            String    @id @default(cuid())
  email         String    @unique
  passwordHash  String    // bcrypt hash
  name          String
  phone         String
  role          UserRole  @default(CLIENT)
  emailVerified DateTime?
  phoneVerified DateTime?
  
  // Métadonnées
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  lastLoginAt   DateTime?
  status        UserStatus @default(ACTIVE)
  
  // Relations
  company       Company?
  transactions  Transaction[]
  suppliers     Supplier[]
  auditLogs     AuditLog[]
  sessions      Session[]
  
  @@index([email])
  @@index([status])
}

enum UserRole {
  CLIENT
  ADMIN
  SUPER_ADMIN
  COMPLIANCE_OFFICER
}

enum UserStatus {
  ACTIVE
  SUSPENDED
  PENDING_VERIFICATION
  CLOSED
}

model Session {
  id           String   @id @default(cuid())
  userId       String
  token        String   @unique
  expiresAt    DateTime
  ipAddress    String?
  userAgent    String?
  createdAt    DateTime @default(now())
  
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  @@index([userId])
  @@index([token])
  @@index([expiresAt])
}

// ============================================
// KYB (Know Your Business)
// ============================================

model Company {
  id                String        @id @default(cuid())
  userId            String        @unique
  
  // Informations entreprise
  companyName       String
  registrationNumber String       @unique
  taxId             String?
  country           String        // ISO 3166-1 alpha-2 (SN, CI)
  city              String
  address           String
  postalCode        String?
  
  // Contact
  contactEmail      String
  contactPhone      String
  
  // Activité
  businessType      String
  annualRevenue     Float?
  website           String?
  
  // KYB Status
  kybStatus         KYBStatus     @default(PENDING)
  kybSubmittedAt    DateTime?
  kybApprovedAt     DateTime?
  kybRejectedAt     DateTime?
  kybRejectionReason String?
  
  // Documents (URLs vers storage)
  documents         Json          // { "rccm": "url", "id_carte": "url", ... }
  
  // Métadonnées
  createdAt         DateTime      @default(now())
  updatedAt         DateTime      @updatedAt
  
  // Relations
  user              User          @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  @@index([kybStatus])
  @@index([country])
}

enum KYBStatus {
  PENDING
  UNDER_REVIEW
  APPROVED
  REJECTED
  ADDITIONAL_INFO_REQUIRED
}

// ============================================
// FOURNISSEURS
// ============================================

model Supplier {
  id            String   @id @default(cuid())
  userId        String
  
  // Informations fournisseur
  name          String
  email         String?
  phone         String?
  country       String   // CN (China) ou IN (India)
  
  // Informations bancaires
  bankName      String
  accountNumber String   // Encrypted
  swiftCode     String?
  iban          String?
  
  // Informations additionnelles
  address       String?
  city          String?
  notes         String?  @db.Text
  
  // Métadonnées
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt
  isActive      Boolean  @default(true)
  
  // Relations
  user          User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  transactions  Transaction[]
  
  @@index([userId])
  @@index([country])
  @@index([isActive])
}

// ============================================
// TRANSACTIONS
// ============================================

model Transaction {
  id                String            @id @default(cuid())
  userId            String
  supplierId        String
  
  // Référence unique
  reference         String            @unique  // TRX-20240901-XXXXX
  
  // Montants
  invoiceAmount     Float             // Montant facture
  invoiceCurrency   Currency          // USD ou CNY
  
  // Calculs de conversion
  clientRate        Float             // Taux client (taux interbancaire + spread)
  amountInXOF       Float             // Montant converti
  serviceFee        Float             // Frais de service (0.5%)
  transferFee       Float             // Frais de transfert
  totalAmount       Float             // Montant total à payer
  
  // Facture
  invoiceNumber     String?
  invoiceUrl        String?           // URL du fichier uploadé
  
  // Statut
  status            TransactionStatus @default(PENDING)
  
  // Dates importantes
  createdAt         DateTime          @default(now())
  updatedAt         DateTime          @updatedAt
  paidAt            DateTime?
  processedAt       DateTime?
  completedAt       DateTime?
  cancelledAt       DateTime?
  
  // Informations de paiement
  paymentMethod     String?
  paymentReference  String?
  
  // Notes et suivi
  notes             String?           @db.Text
  adminNotes        String?           @db.Text
  rejectionReason   String?
  
  // Relations
  user              User              @relation(fields: [userId], references: [id])
  supplier          Supplier          @relation(fields: [supplierId], references: [id])
  statusHistory     TransactionStatusHistory[]
  
  @@index([userId])
  @@index([supplierId])
  @@index([status])
  @@index([createdAt])
  @@index([reference])
}

enum Currency {
  USD
  CNY
  XOF
}

enum TransactionStatus {
  PENDING           // Créée, en attente de paiement
  PAYMENT_RECEIVED  // Paiement reçu, en cours de traitement
  PROCESSING        // En cours de transfert
  COMPLETED         // Complétée avec succès
  CANCELLED         // Annulée
  FAILED            // Échouée
  REFUNDED          // Remboursée
}

// ============================================
// HISTORIQUE DES STATUTS
// ============================================

model TransactionStatusHistory {
  id            String            @id @default(cuid())
  transactionId String
  
  fromStatus    TransactionStatus?
  toStatus      TransactionStatus
  
  changedBy     String?           // userId ou "SYSTEM"
  reason        String?
  notes         String?           @db.Text
  
  createdAt     DateTime          @default(now())
  
  transaction   Transaction       @relation(fields: [transactionId], references: [id], onDelete: Cascade)
  
  @@index([transactionId])
  @@index([createdAt])
}

// ============================================
// AUDIT & COMPLIANCE
// ============================================

model AuditLog {
  id          String   @id @default(cuid())
  userId      String?
  
  action      String   // "CREATE_TRANSACTION", "UPDATE_USER", etc.
  entity      String   // "Transaction", "User", "Company"
  entityId    String
  
  changes     Json?    // { "before": {...}, "after": {...} }
  ipAddress   String?
  userAgent   String?
  
  createdAt   DateTime @default(now())
  
  user        User?    @relation(fields: [userId], references: [id], onDelete: SetNull)
  
  @@index([userId])
  @@index([entity, entityId])
  @@index([createdAt])
}

// ============================================
// TAUX DE CHANGE (Historique)
// ============================================

model ExchangeRate {
  id          String   @id @default(cuid())
  
  currency    Currency
  rate        Float    // Taux interbancaire
  margin      Float    // Marge appliquée (ex: 0.018 = 1.8%)
  clientRate  Float    // Taux client final
  
  source      String?  // "MANUAL", "API_XE", etc.
  
  createdAt   DateTime @default(now())
  validFrom   DateTime
  validUntil  DateTime?
  
  @@index([currency])
  @@index([validFrom])
  @@index([createdAt])
}

// ============================================
// NOTIFICATIONS
// ============================================

model Notification {
  id        String   @id @default(cuid())
  userId    String
  
  type      String   // "TRANSACTION_COMPLETED", "KYB_APPROVED", etc.
  title     String
  message   String   @db.Text
  
  read      Boolean  @default(false)
  readAt    DateTime?
  
  metadata  Json?    // Données additionnelles
  
  createdAt DateTime @default(now())
  
  @@index([userId])
  @@index([read])
  @@index([createdAt])
}

🔒 Sécurité & Conformité ​

1. Chiffrement des Données Sensibles ​

sql
-- Activer pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Chiffrer les numéros de compte bancaire
CREATE FUNCTION encrypt_account_number(account_text TEXT) 
RETURNS TEXT AS $$
BEGIN
  RETURN encode(
    pgp_sym_encrypt(account_text, current_setting('app.encryption_key')),
    'base64'
  );
END;
$$ LANGUAGE plpgsql;

-- Déchiffrer
CREATE FUNCTION decrypt_account_number(encrypted_text TEXT) 
RETURNS TEXT AS $$
BEGIN
  RETURN pgp_sym_decrypt(
    decode(encrypted_text, 'base64'),
    current_setting('app.encryption_key')
  );
END;
$$ LANGUAGE plpgsql;

2. Row-Level Security (RLS) ​

sql
-- Activer RLS sur la table Transaction
ALTER TABLE "Transaction" ENABLE ROW LEVEL SECURITY;

-- Policy : Un client ne voit que ses propres transactions
CREATE POLICY user_transactions ON "Transaction"
  FOR ALL
  TO authenticated_user
  USING ("userId" = current_setting('app.current_user_id')::text);

-- Policy : Les admins voient tout
CREATE POLICY admin_all_transactions ON "Transaction"
  FOR ALL
  TO authenticated_admin
  USING (true);

3. Audit Trail Automatique ​

sql
-- Trigger pour logger toutes les modifications
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO "AuditLog" ("action", "entity", "entityId", "changes", "createdAt")
  VALUES (
    TG_OP,
    TG_TABLE_NAME,
    NEW.id,
    jsonb_build_object(
      'before', to_jsonb(OLD),
      'after', to_jsonb(NEW)
    ),
    NOW()
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Appliquer sur Transaction
CREATE TRIGGER transaction_audit
AFTER INSERT OR UPDATE OR DELETE ON "Transaction"
FOR EACH ROW EXECUTE FUNCTION log_changes();

4. Backup & Recovery ​

bash
# Backup quotidien automatique
pg_dump -h localhost -U postgres -d africa_bridge_pay \
  --format=custom \
  --file=backup_$(date +%Y%m%d).dump

# Restore
pg_restore -h localhost -U postgres -d africa_bridge_pay backup_20240901.dump

🚀 API Backend (Next.js API Routes) ​

Structure du Backend ​

app/
├── api/
│   ├── auth/
│   │   ├── login/route.ts
│   │   ├── register/route.ts
│   │   ├── logout/route.ts
│   │   └── refresh/route.ts
│   ├── users/
│   │   ├── profile/route.ts
│   │   └── [id]/route.ts
│   ├── companies/
│   │   ├── route.ts              # GET, POST
│   │   ├── [id]/route.ts         # GET, PATCH, DELETE
│   │   └── kyb/
│   │       ├── submit/route.ts
│   │       └── approve/route.ts
│   ├── suppliers/
│   │   ├── route.ts
│   │   └── [id]/route.ts
│   ├── transactions/
│   │   ├── route.ts
│   │   ├── [id]/route.ts
│   │   ├── calculate/route.ts
│   │   └── [id]/status/route.ts
│   ├── exchange-rates/
│   │   └── route.ts
│   └── admin/
│       ├── transactions/route.ts
│       └── users/route.ts

Exemple : API Transaction Create ​

typescript
// app/api/transactions/route.ts
import { NextRequest, NextResponse } from 'next/server';
import { prisma } from '@/lib/prisma';
import { getServerSession } from 'next-auth';
import { authOptions } from '@/lib/auth';
import { z } from 'zod';

const createTransactionSchema = z.object({
  supplierId: z.string(),
  invoiceAmount: z.number().positive(),
  invoiceCurrency: z.enum(['USD', 'CNY']),
  invoiceNumber: z.string().optional(),
  notes: z.string().optional(),
});

export async function POST(req: NextRequest) {
  try {
    // 1. Vérifier l'authentification
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
    }

    // 2. Valider les données
    const body = await req.json();
    const validated = createTransactionSchema.parse(body);

    // 3. Vérifier que le fournisseur appartient à l'utilisateur
    const supplier = await prisma.supplier.findFirst({
      where: {
        id: validated.supplierId,
        userId: session.user.id,
      },
    });

    if (!supplier) {
      return NextResponse.json(
        { error: 'Supplier not found' },
        { status: 404 }
      );
    }

    // 4. Récupérer le taux de change actuel
    const exchangeRate = await prisma.exchangeRate.findFirst({
      where: {
        currency: validated.invoiceCurrency,
        validFrom: { lte: new Date() },
        validUntil: { gte: new Date() },
      },
      orderBy: { createdAt: 'desc' },
    });

    if (!exchangeRate) {
      return NextResponse.json(
        { error: 'Exchange rate not available' },
        { status: 500 }
      );
    }

    // 5. Calculer les montants
    const amountInXOF = validated.invoiceAmount * exchangeRate.clientRate;
    const serviceFee = amountInXOF * 0.005; // 0.5%
    const transferFee = validated.invoiceCurrency === 'USD' ? 15000 : 9000;
    const totalAmount = amountInXOF + serviceFee + transferFee;

    // 6. Générer référence unique
    const reference = `TRX-${new Date().toISOString().split('T')[0].replace(/-/g, '')}-${Math.random().toString(36).substring(2, 8).toUpperCase()}`;

    // 7. Créer la transaction (avec audit automatique via trigger)
    const transaction = await prisma.transaction.create({
      data: {
        userId: session.user.id,
        supplierId: validated.supplierId,
        reference,
        invoiceAmount: validated.invoiceAmount,
        invoiceCurrency: validated.invoiceCurrency,
        exchangeRate: exchangeRate.clientRate,
        amountInXOF,
        serviceFee,
        transferFee,
        totalAmount,
        invoiceNumber: validated.invoiceNumber,
        notes: validated.notes,
        status: 'PENDING',
        statusHistory: {
          create: {
            toStatus: 'PENDING',
            changedBy: session.user.id,
            reason: 'Transaction created',
          },
        },
      },
      include: {
        supplier: true,
        statusHistory: true,
      },
    });

    // 8. Créer une notification
    await prisma.notification.create({
      data: {
        userId: session.user.id,
        type: 'TRANSACTION_CREATED',
        title: 'Transaction créée',
        message: `Votre transaction ${reference} a été créée avec succès.`,
        metadata: { transactionId: transaction.id },
      },
    });

    return NextResponse.json(transaction, { status: 201 });
  } catch (error) {
    if (error instanceof z.ZodError) {
      return NextResponse.json(
        { error: 'Validation error', details: error.errors },
        { status: 400 }
      );
    }

    console.error('Transaction creation error:', error);
    return NextResponse.json(
      { error: 'Internal server error' },
      { status: 500 }
    );
  }
}

export async function GET(req: NextRequest) {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
    }

    const { searchParams } = new URL(req.url);
    const page = parseInt(searchParams.get('page') || '1');
    const limit = parseInt(searchParams.get('limit') || '10');
    const status = searchParams.get('status');

    const where: any = { userId: session.user.id };
    if (status) where.status = status;

    const [transactions, total] = await Promise.all([
      prisma.transaction.findMany({
        where,
        include: {
          supplier: true,
        },
        orderBy: { createdAt: 'desc' },
        skip: (page - 1) * limit,
        take: limit,
      }),
      prisma.transaction.count({ where }),
    ]);

    return NextResponse.json({
      transactions,
      pagination: {
        page,
        limit,
        total,
        totalPages: Math.ceil(total / limit),
      },
    });
  } catch (error) {
    console.error('Fetch transactions error:', error);
    return NextResponse.json(
      { error: 'Internal server error' },
      { status: 500 }
    );
  }
}

🔄 Migration depuis localStorage ​

Script de Migration ​

typescript
// scripts/migrate-to-db.ts
import { prisma } from '../lib/prisma';
import bcrypt from 'bcrypt';

interface LocalStorageData {
  user: any;
  company: any;
  suppliers: any[];
  transactions: any[];
}

async function migrateUser(data: LocalStorageData) {
  const { user, company } = data;

  // 1. Créer l'utilisateur
  const hashedPassword = await bcrypt.hash('temporary-password-123', 10);
  
  const dbUser = await prisma.user.create({
    data: {
      email: user.email,
      passwordHash: hashedPassword,
      name: user.name,
      phone: user.phone,
      role: user.role === 'admin' ? 'ADMIN' : 'CLIENT',
      emailVerified: new Date(),
    },
  });

  // 2. Créer la company si existe
  if (company) {
    await prisma.company.create({
      data: {
        userId: dbUser.id,
        companyName: company.companyName,
        registrationNumber: company.registrationNumber,
        taxId: company.taxId,
        country: company.country,
        city: company.city,
        address: company.address,
        postalCode: company.postalCode,
        contactEmail: company.contactEmail,
        contactPhone: company.contactPhone,
        businessType: company.businessType,
        kybStatus: company.kybStatus || 'APPROVED',
        documents: company.documents || {},
      },
    });
  }

  // 3. Migrer les fournisseurs
  const supplierMap = new Map();
  
  for (const supplier of data.suppliers || []) {
    const dbSupplier = await prisma.supplier.create({
      data: {
        userId: dbUser.id,
        name: supplier.name,
        email: supplier.email,
        phone: supplier.phone,
        country: supplier.country,
        bankName: supplier.bankName,
        accountNumber: supplier.accountNumber, // TODO: encrypt
        swiftCode: supplier.swiftCode,
        iban: supplier.iban,
        address: supplier.address,
        city: supplier.city,
        notes: supplier.notes,
      },
    });
    
    supplierMap.set(supplier.id, dbSupplier.id);
  }

  // 4. Migrer les transactions
  for (const transaction of data.transactions || []) {
    await prisma.transaction.create({
      data: {
        userId: dbUser.id,
        supplierId: supplierMap.get(transaction.supplierId),
        reference: transaction.reference,
        invoiceAmount: transaction.invoiceAmount,
        invoiceCurrency: transaction.invoiceCurrency,
        exchangeRate: transaction.exchangeRate,
        amountInXOF: transaction.amountInXOF,
        serviceFee: transaction.serviceFee,
        transferFee: transaction.transferFee,
        totalAmount: transaction.totalAmount,
        invoiceNumber: transaction.invoiceNumber,
        status: transaction.status,
        notes: transaction.notes,
      },
    });
  }

  console.log(`✅ Migration completed for user: ${user.email}`);
}

// Exécuter
const localData = JSON.parse(process.argv[2]);
migrateUser(localData).catch(console.error);

🚀 Déploiement ​

Option 1 : Vercel + Supabase (Recommandé pour MVP) ​

Avantages :

  • ✅ Setup en 10 minutes
  • ✅ PostgreSQL géré (Supabase)
  • ✅ Scaling automatique
  • ✅ Auth intégrée
  • ✅ Storage pour documents
  • ✅ Free tier généreux
bash
# 1. Créer projet Supabase
npx supabase init
npx supabase db push

# 2. Déployer sur Vercel
vercel --prod

# 3. Configurer variables d'environnement
DATABASE_URL=postgresql://postgres:[password]@db.xxx.supabase.co:5432/postgres
NEXTAUTH_SECRET=xxx
NEXTAUTH_URL=https://your-domain.vercel.app

Option 2 : Railway + PostgreSQL ​

bash
# 1. Railway PostgreSQL
railway add postgresql

# 2. Déployer
railway up

# 3. Variables auto-configurées

Option 3 : Self-Hosted (Production complète) ​

Stack :

  • DigitalOcean / AWS EC2
  • PostgreSQL 15 (Managed ou Self-hosted)
  • Nginx (Reverse proxy)
  • PM2 (Process manager)
  • Let's Encrypt (SSL)

📊 Métriques & Monitoring ​

1. Prisma Metrics ​

typescript
// lib/prisma.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: [
    { level: 'query', emit: 'event' },
    { level: 'error', emit: 'stdout' },
    { level: 'warn', emit: 'stdout' },
  ],
});

prisma.$on('query', (e) => {
  console.log('Query: ' + e.query);
  console.log('Duration: ' + e.duration + 'ms');
});

export default prisma;

2. Sentry (Error Tracking) ​

typescript
import * as Sentry from '@sentry/nextjs';

Sentry.init({
  dsn: process.env.SENTRY_DSN,
  environment: process.env.NODE_ENV,
  tracesSampleRate: 1.0,
});

3. Analytics Transactions ​

sql
-- Dashboard admin : Transactions par jour
SELECT 
  DATE(created_at) as date,
  COUNT(*) as total_transactions,
  SUM(total_amount) as total_volume,
  AVG(total_amount) as avg_transaction
FROM "Transaction"
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE(created_at)
ORDER BY date DESC;

🎯 Checklist de Migration ​

Phase 1 : Setup (Semaine 1) ​

  • [ ] Créer compte Supabase
  • [ ] Installer Prisma
  • [ ] Définir schéma Prisma
  • [ ] Migration initiale
  • [ ] Créer seed data

Phase 2 : API (Semaine 2-3) ​

  • [ ] Auth endpoints (login, register, logout)
  • [ ] User CRUD
  • [ ] Company KYB
  • [ ] Suppliers CRUD
  • [ ] Transactions CRUD
  • [ ] Exchange rates

Phase 3 : Frontend Migration (Semaine 4) ​

  • [ ] Remplacer localStorage par API calls
  • [ ] Gérer loading states
  • [ ] Error handling
  • [ ] Optimistic updates

Phase 4 : Sécurité & Tests (Semaine 5) ​

  • [ ] Row-Level Security
  • [ ] Encryption données sensibles
  • [ ] Tests unitaires API
  • [ ] Tests E2E
  • [ ] Audit de sécurité

Phase 5 : Déploiement (Semaine 6) ​

  • [ ] Setup CI/CD
  • [ ] Deploy staging
  • [ ] Load testing
  • [ ] Deploy production
  • [ ] Monitoring

💰 Coûts Estimés ​

Supabase + Vercel (Startup) ​

Supabase Pro : $25/mois
Vercel Pro : $20/mois
Total : ~$45/mois

Inclut :
- 8 GB database
- 100 GB bandwidth
- 50 GB storage
- Unlimited deployments

Railway (Alternative) ​

PostgreSQL : $5-20/mois (selon usage)
App hosting : $5-10/mois
Total : ~$10-30/mois

📚 Ressources ​

🎉 Avec cette architecture, Africa Bridge Pay sera prêt pour la production avec une base solide, sécurisée et scalable !

Documentation Africa Bridge Pay

Copyright © 2026 Africa Bridge Pay