Bonnes pratiques base de données

Migrations et schéma

Toujours passer par Drizzle ORM pour modifier le schéma. Ne jamais exécuter de DDL manuellement en production.
Committer les fichiers de migration générés (apps/server/drizzle/) avec le code source.
Appliquer les migrations avant de déployer une nouvelle version du code qui en dépend.

# Cycle standard de modification de schéma
# 1. Modifier apps/server/src/db/schema/naivi.ts
# 2. Générer la migration
bun run db:generate
# 3. Relire le SQL généré dans apps/server/drizzle/
# 4. Appliquer
bun run db:migrate

Accès et sécurité

Utiliser un utilisateur PostgreSQL dédié avec des droits minimaux (SELECT/INSERT/UPDATE/DELETE sur les tables de l'app, pas de DDL).
Ne jamais exposer DATABASE_URL dans le code source ou les images Docker. Utiliser des Secrets Kubernetes.
Configurer le réseau PostgreSQL pour n'accepter que les connexions depuis les services autorisés (server + chatbot).
Activer SSL sur la connexion PostgreSQL en production.

Index et performances

Les index présents dans le schéma couvrent les accès fréquents:

Index	Table	Colonne(s)	Usage
`files_type_idx`	`files`	`type`	Filtrage par type (regular/qa)
`chunks_file_id_idx`	`chunks`	`file_id`	Accès aux chunks d'un fichier
`qa_pair_file_id_idx`	`qa_pair`	`file_id`	Accès aux Q&A d'un fichier
`file_assignments_assigned_to_idx`	`file_assignments`	`assigned_to`	Fichiers d'un utilisateur
`item_tags_item_idx`	`item_tags`	`(item_id, item_type)`	Tags d'un item
`indexes_role_active_idx`	`indexes`	`(role_id, is_active)`	Index FAISS actif par rôle

Ajoutez des index supplémentaires si des requêtes lentes apparaissent (profilez avec EXPLAIN ANALYZE).

Colonnes `embedding` (bytea)

Les colonnes chunks.embedding et qa_pair.embedding stockent des vecteurs float32 sérialisés (numpy). Ces colonnes peuvent atteindre plusieurs KB par ligne. Pour les très grandes bases:

Envisager un stockage externe (pgvector ou service dédié) si les embeddings deviennent un goulot d'étranglement.
La génération des embeddings est faite par le chatbot via Mistral API lors de la régénération d'index. Seuls les nouveaux chunks sans embedding sont recalculés.

Table `indexes` (FAISS sérialisé)

Les index FAISS sérialisés peuvent peser plusieurs MB chacun selon la taille de la base. Prévoir de l'espace disque.
Un seul index is_active = true par role_id à la fois. Les anciens index ne sont pas supprimés automatiquement; prévoir un nettoyage périodique.

-- Nettoyage des anciens index inactifs (à scheduler)
DELETE FROM indexes WHERE is_active = false
  AND created_at < NOW() - INTERVAL '7 days';

Sauvegardes

Configurer des sauvegardes automatiques (pg_dump ou WAL archiving) avec rétention adaptée.
Tester les restores régulièrement.
En Kubernetes, utiliser un opérateur PostgreSQL (ex: CloudNativePG, Zalando) pour les sauvegardes continues.

Surveillance

Activer pg_stat_statements pour identifier les requêtes lentes.
Surveiller la taille des tables (pg_total_relation_size) — chunks et indexes sont les tables les plus volumineuses.
Configurer des alertes sur les connexions actives et le taux d'erreur.

Database Usage Guidelines