99a350aa05
Revamped the documentation structure and content to enhance usability and organization. Added detailed sections on architecture, pipeline, security, API reference, deployment steps, compliance, and supported modules. Introduced new visuals like cards, accordions, and callouts for improved readability and navigation.
282 lines
7.7 KiB
Plaintext
282 lines
7.7 KiB
Plaintext
---
|
|
title: Modèle de Données
|
|
description: Structure et organisation des données
|
|
---
|
|
|
|
## 📊 Modèle de Données
|
|
|
|
### Conceptuel (MCD)
|
|
|
|
Le Modèle Conceptuel de Données décrit les grandes entités du système et leurs relations métier, incluant la gestion des accès et la modération.
|
|
|
|
```mermaid
|
|
erDiagram
|
|
USER ||--o{ CONTENT : "publie"
|
|
USER ||--o{ REPORT : "signale"
|
|
USER ||--o{ USER_ROLE : "possede"
|
|
USER ||--o{ SESSION : "detient"
|
|
USER ||--o{ API_KEY : "genere"
|
|
USER ||--o{ AUDIT_LOG : "genere"
|
|
USER ||--o{ FAVORITE : "ajoute"
|
|
|
|
CONTENT ||--o{ CONTENT_TAG : "possede"
|
|
TAG ||--o{ CONTENT_TAG : "est_lie_a"
|
|
CONTENT ||--o{ REPORT : "est_signale"
|
|
CONTENT ||--o{ FAVORITE : "est_mis_en"
|
|
TAG ||--o{ REPORT : "est_signale"
|
|
|
|
CATEGORY ||--o{ CONTENT : "catégorise"
|
|
|
|
ROLE ||--o{ USER_ROLE : "attribue_a"
|
|
ROLE ||--o{ ROLE_PERMISSION : "possede"
|
|
PERMISSION ||--o{ ROLE_PERMISSION : "est_lie_a"
|
|
|
|
USER {
|
|
string username
|
|
string email
|
|
string display_name
|
|
string status
|
|
}
|
|
CONTENT {
|
|
string title
|
|
string type
|
|
string storage_key
|
|
}
|
|
TAG {
|
|
string name
|
|
string slug
|
|
}
|
|
ROLE {
|
|
string name
|
|
string slug
|
|
}
|
|
REPORT {
|
|
string reason
|
|
string status
|
|
}
|
|
SESSION {
|
|
string refresh_token
|
|
boolean is_valid
|
|
}
|
|
API_KEY {
|
|
string name
|
|
string prefix
|
|
boolean is_active
|
|
}
|
|
AUDIT_LOG {
|
|
string action
|
|
string entity_type
|
|
jsonb details
|
|
}
|
|
```
|
|
|
|
### Logique (MLD)
|
|
|
|
Le Modèle Logique de Données précise les tables, les colonnes et les clés étrangères (FK).
|
|
|
|
```mermaid
|
|
erDiagram
|
|
users {
|
|
uuid uuid PK
|
|
varchar username
|
|
bytea email
|
|
varchar email_hash
|
|
varchar display_name
|
|
varchar password_hash
|
|
user_status status
|
|
bytea two_factor_secret
|
|
boolean is_two_factor_enabled
|
|
varchar terms_version
|
|
varchar privacy_version
|
|
timestamp gdpr_accepted_at
|
|
timestamp last_login_at
|
|
timestamp created_at
|
|
timestamp updated_at
|
|
timestamp deleted_at
|
|
}
|
|
contents {
|
|
uuid id PK
|
|
uuid user_id FK
|
|
uuid category_id FK
|
|
content_type type
|
|
varchar title
|
|
varchar storage_key
|
|
varchar mime_type
|
|
integer file_size
|
|
integer views
|
|
integer usage_count
|
|
timestamp created_at
|
|
timestamp updated_at
|
|
timestamp deleted_at
|
|
}
|
|
categories {
|
|
uuid id PK
|
|
varchar name
|
|
varchar slug
|
|
varchar description
|
|
varchar icon_url
|
|
timestamp created_at
|
|
timestamp updated_at
|
|
}
|
|
favorites {
|
|
uuid user_id PK, FK
|
|
uuid content_id PK, FK
|
|
timestamp created_at
|
|
}
|
|
tags {
|
|
uuid id PK
|
|
varchar name
|
|
varchar slug
|
|
timestamp created_at
|
|
timestamp updated_at
|
|
}
|
|
contents_to_tags {
|
|
uuid content_id PK, FK
|
|
uuid tag_id PK, FK
|
|
}
|
|
roles {
|
|
uuid id PK
|
|
varchar name
|
|
varchar slug
|
|
varchar description
|
|
timestamp created_at
|
|
}
|
|
permissions {
|
|
uuid id PK
|
|
varchar name
|
|
varchar slug
|
|
varchar description
|
|
timestamp created_at
|
|
}
|
|
roles_to_permissions {
|
|
uuid role_id PK, FK
|
|
uuid permission_id PK, FK
|
|
}
|
|
users_to_roles {
|
|
uuid user_id PK, FK
|
|
uuid role_id PK, FK
|
|
}
|
|
reports {
|
|
uuid id PK
|
|
uuid reporter_id FK
|
|
uuid content_id FK
|
|
uuid tag_id FK
|
|
report_reason reason
|
|
text description
|
|
report_status status
|
|
timestamp expires_at
|
|
timestamp created_at
|
|
timestamp updated_at
|
|
}
|
|
sessions {
|
|
uuid id PK
|
|
uuid user_id FK
|
|
varchar refresh_token
|
|
varchar user_agent
|
|
varchar ip_hash
|
|
boolean is_valid
|
|
timestamp expires_at
|
|
timestamp created_at
|
|
timestamp updated_at
|
|
}
|
|
api_keys {
|
|
uuid id PK
|
|
uuid user_id FK
|
|
varchar key_hash
|
|
varchar name
|
|
varchar prefix
|
|
boolean is_active
|
|
timestamp last_used_at
|
|
timestamp expires_at
|
|
timestamp created_at
|
|
timestamp updated_at
|
|
}
|
|
audit_logs {
|
|
uuid id PK
|
|
uuid user_id FK
|
|
varchar action
|
|
varchar entity_type
|
|
uuid entity_id
|
|
jsonb details
|
|
varchar ip_hash
|
|
varchar user_agent
|
|
timestamp created_at
|
|
}
|
|
|
|
users ||--o{ favorites : "user_id"
|
|
contents ||--o{ favorites : "content_id"
|
|
categories ||--o{ contents : "category_id"
|
|
users ||--o{ contents : "user_id"
|
|
users ||--o{ users_to_roles : "user_id"
|
|
roles ||--o{ users_to_roles : "role_id"
|
|
roles ||--o{ roles_to_permissions : "role_id"
|
|
permissions ||--o{ roles_to_permissions : "permission_id"
|
|
contents ||--o{ contents_to_tags : "content_id"
|
|
tags ||--o{ contents_to_tags : "tag_id"
|
|
users ||--o{ reports : "reporter_id"
|
|
contents ||--o{ reports : "content_id"
|
|
tags ||--o{ reports : "tag_id"
|
|
users ||--o{ sessions : "user_id"
|
|
users ||--o{ api_keys : "user_id"
|
|
users ||--o{ audit_logs : "user_id"
|
|
```
|
|
|
|
### Physique (MPD)
|
|
|
|
Le Modèle Physique de Données détaille l'implémentation spécifique pour **PostgreSQL**.
|
|
|
|
```mermaid
|
|
erDiagram
|
|
users {
|
|
uuid uuid "DEFAULT gen_random_uuid()"
|
|
bytea email "ENCRYPTED, NOT NULL"
|
|
varchar email_hash "UNIQUE, INDEXED"
|
|
varchar username "UNIQUE, NOT NULL"
|
|
varchar password_hash "NOT NULL"
|
|
bytea two_factor_secret "ENCRYPTED"
|
|
boolean is_two_factor_enabled "DEFAULT false"
|
|
timestamp gdpr_accepted_at "NULLABLE"
|
|
timestamp deleted_at "SOFT DELETE"
|
|
}
|
|
contents {
|
|
uuid id "DEFAULT gen_random_uuid()"
|
|
uuid user_id "REFERENCES users(uuid)"
|
|
varchar storage_key "UNIQUE, NOT NULL"
|
|
integer file_size "NOT NULL"
|
|
timestamp deleted_at "SOFT DELETE"
|
|
}
|
|
reports {
|
|
uuid id "DEFAULT gen_random_uuid()"
|
|
uuid reporter_id "REFERENCES users(uuid)"
|
|
timestamp expires_at "RGPD PURGE"
|
|
}
|
|
api_keys {
|
|
uuid id "DEFAULT gen_random_uuid()"
|
|
varchar key_hash "UNIQUE, INDEXED, SHA-256"
|
|
varchar prefix "NOT NULL"
|
|
}
|
|
audit_logs {
|
|
uuid id "DEFAULT gen_random_uuid()"
|
|
jsonb details "STORED AS JSONB"
|
|
varchar ip_hash "RGPD COMPLIANT"
|
|
}
|
|
sessions {
|
|
uuid id "DEFAULT gen_random_uuid()"
|
|
varchar refresh_token "UNIQUE, NOT NULL"
|
|
varchar ip_hash "RGPD COMPLIANT"
|
|
}
|
|
```
|
|
|
|
#### Sécurité et Chiffrement
|
|
- **Chiffrement PGP (Native)** : Les colonnes `email` et `two_factor_secret` sont stockées au format `bytea` et chiffrées/déchiffrées via les fonctions `pgp_sym_encrypt` et `pgp_sym_decrypt` de PostgreSQL (via l'extension `pgcrypto`).
|
|
- **Hachage aveugle (Blind Indexing)** : La colonne `email_hash` stocke un hash (SHA-256) de l'email pour permettre les recherches d'unicité et les recherches rapides sans déchiffrer la donnée.
|
|
|
|
#### Index et Optimisations
|
|
- **Index B-Tree** systématiques sur toutes les clés étrangères (`user_id`, `role_id`, etc.).
|
|
- **Index sur `deleted_at`** : Pour optimiser les requêtes excluant les données supprimées logiciellement.
|
|
- **Index unique sur `email_hash`** et `username`.
|
|
|
|
#### Conformité RGPD
|
|
- **Soft Delete** : Implémenté via `deleted_at` pour permettre le "droit à l'oubli" tout en conservant l'intégrité référentielle temporaire.
|
|
- **Purge Automatique** : La colonne `expires_at` dans la table `reports` permet de programmer la suppression automatique des données de signalement après traitement.
|