📐 Base de données — Schéma complet + RLS

PostgreSQL via Supabase. Migration : supabase/migrations/20260406145132_*.sql

---

Tables

email_analyses

Historique des analyses email par utilisateur.

Colonne	Type	Défaut	Description
id	UUID	gen_random_uuid()	Clé primaire
user_id	UUID	—	FK → auth.users (CASCADE DELETE)
legacy_id	BIGINT	null	ID migration (Date.now())
timestamp	TIMESTAMPTZ	now()	Date/heure de l'analyse
email_content	TEXT	null	Début des headers (max 500 chars)
spf	TEXT	'unknown'	Résultat SPF
dkim	TEXT	'unknown'	Résultat DKIM
dmarc	TEXT	'unknown'	Résultat DMARC
ip_reputation	INTEGER	50	Score réputation IP (0–100)
sender_score	INTEGER	50	Score expéditeur (0–100)
threat_level	TEXT	'low'	low / medium / high
domain	TEXT	null	Domaine expéditeur
ip_address	TEXT	null	IP source principale
from_address	TEXT	null	Adresse complète expéditeur
subject	TEXT	null	Sujet de l'email
delivered_to	TEXT	null	Destinataire
message_id	TEXT	null	Message-ID (sans chevrons)
return_path	TEXT	null	Return-Path (sans chevrons)
formatted_date	TEXT	null	Date formatée (Europe/Zurich)
ip_reputations	JSONB	'[]'	Résultats IPQS/AbuseIPDB/VirusTotal
received_chain	JSONB	'[]'	Chaîne SMTP [{from, by}]
marked_spam	BOOLEAN	false	Marquage manuel spam
created_at	TIMESTAMPTZ	now()	Date d'insertion

Structure ip_reputations (JSONB) :

[{
  "ip": "203.0.113.1",
  "ipQualityScore": { "fraud_score": 45, "country": "FR", "isp": "Orange SA", "is_vpn": false },
  "abuseIPDB": { "abuse_confidence_score": 10, "country": "FR", "total_reports": 2 },
  "virusTotal": { "malicious": 0, "suspicious": 0, "harmless": 65, "asn": 3215 }
}]

---

phone_checks

Historique des vérifications téléphoniques par utilisateur.

Colonne	Type	Défaut	Description
id	UUID	gen_random_uuid()	Clé primaire
user_id	UUID	—	FK → auth.users (CASCADE DELETE)
phone_number	TEXT	—	Numéro saisi (format original)
normalized	TEXT	—	Numéro normalisé (33XXXXXXXXX)
is_blocked	BOOLEAN	false	Bloqué selon ARCEP
match_count	INTEGER	0	Nombre de patterns correspondants
matches	JSONB	'[]'	Patterns ARCEP déclenchés
timestamp	TIMESTAMPTZ	now()	Date de vérification
marked_spam	BOOLEAN	false	Marquage spam (auto ou manuel)
manually_blocked	BOOLEAN	false	Blocage manuel par l'utilisateur
community_reported	BOOLEAN	false	Signalé à la communauté
check_type	TEXT	'phone'	phone ou sms
database_info	JSONB	''	Version et taille base ARCEP
created_at	TIMESTAMPTZ	now()	Date d'insertion

Structure matches (JSONB) :

[{ "name": "Démarchage commercial", "action": "block", "pattern": "336123#####" }]

Structure database_info (JSONB) :

{ "version": "v2026.02.26.17.26", "total_blocked": 14000000 }

---

community_reports

Signalements communautaires partagés entre tous les utilisateurs.

Colonne	Type	Défaut	Description
id	UUID	gen_random_uuid()	Clé primaire
reporter_id	UUID	—	FK → auth.users (CASCADE DELETE)
report_type	TEXT	—	email ou phone (CHECK constraint)
identifier	TEXT	—	Email ou numéro signalé
reason	TEXT	null	Raison du signalement
report_count	INTEGER	1	Nombre de signalements cumulés
confirmed_spam	BOOLEAN	false	Confirmé comme spam
created_at	TIMESTAMPTZ	now()	Date création
updated_at	TIMESTAMPTZ	now()	Date mise à jour

Index unique : (report_type, identifier) → upsert avec ON CONFLICT pour incrémenter report_count.

---

Row Level Security (RLS)

email_analyses et phone_checks

-- Lecture : uniquement ses propres données
CREATE POLICY "Users can view own data"
  ON public.email_analyses FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

-- Insertion : uniquement pour soi-même
CREATE POLICY "Users can insert own data"
  ON public.email_analyses FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = user_id);

-- Modification : uniquement ses propres données
CREATE POLICY "Users can update own data"
  ON public.email_analyses FOR UPDATE TO authenticated
  USING (auth.uid() = user_id);

-- Suppression : uniquement ses propres données
CREATE POLICY "Users can delete own data"
  ON public.email_analyses FOR DELETE TO authenticated
  USING (auth.uid() = user_id);

community_reports

-- Lecture : accessible à tous les authentifiés
CREATE POLICY "Anyone can view community reports"
  ON public.community_reports FOR SELECT TO authenticated
  USING (true);

-- Écriture : uniquement par le reporter
CREATE POLICY "Users can insert own reports"
  ON public.community_reports FOR INSERT TO authenticated
  WITH CHECK (auth.uid() = reporter_id);

CREATE POLICY "Users can update own reports"
  ON public.community_reports FOR UPDATE TO authenticated
  USING (auth.uid() = reporter_id);

CREATE POLICY "Users can delete own reports"
  ON public.community_reports FOR DELETE TO authenticated
  USING (auth.uid() = reporter_id);

---

Realtime

-- Publication Supabase Realtime
ALTER PUBLICATION supabase_realtime ADD TABLE public.community_reports;

---

Queries fréquentes

// Récupérer analyses email (tri par date, max 200)
const { data } = await supabase
  .from('email_analyses')
  .select('*')
  .order('timestamp', { ascending: false })
  .limit(200);

// Marquer comme spam
await supabase
  .from('email_analyses')
  .update({ marked_spam: true })
  .eq('id', id);

// Signalement communautaire (upsert)
await supabase
  .from('community_reports')
  .upsert({
    reporter_id: userId,
    report_type: 'phone',
    identifier: normalizedNumber,
    reason: 'Signalement communautaire',
  }, { onConflict: 'report_type,identifier' });

// Suppression de toutes ses analyses
// RLS garantit que seules les lignes user_id = auth.uid() sont affectées
await supabase
  .from('email_analyses')
  .delete()
  .neq('id', '00000000-0000-0000-0000-000000000000');

---

Suppression en cascade

Toutes les tables ont ON DELETE CASCADE sur user_id → auth.users.
La suppression d'un compte supprime automatiquement toutes ses données associées.

---

Alias camelCase (compatibilité)

Les hooks useEmailAnalyses.ts et usePhoneChecks.ts définissent une fonction toCompat() qui ajoute des alias camelCase sur les colonnes snake_case de la DB :

DB (snake_case)	Alias frontend (camelCase)
email_content	emailContent
ip_reputation	ipReputation
threat_level	threatLevel
from_address	from
delivered_to	deliveredTo
message_id	messageId
ip_reputations	ipReputations
received_chain	receivedChain
marked_spam	markedSpam
phone_number	phoneNumber
is_blocked	isBlocked
manually_blocked	manuallyBlocked