[BE-INFRA-01] Migrations PostgreSQL (15) : 10 tables, index optimisés, triggers immutabilité, partitionnement auth_audit_logs

[K0lux]

section: Database — PostgreSQL | prio: P0 | est: 5h

Pourquoi

Les index sont la différence entre < 2ms et > 500ms à 2M users. Le partitionnement de auth_audit_logs évite les scans de table sur 500M+ lignes.

Acceptance Criteria

• users : id UUID PK, username TEXT UNIQUE (index LOWER), username_set BOOL, last_username_change_at, display_name TEXT, phone_number TEXT UNIQUE NULLABLE, email TEXT UNIQUE NULLABLE (sparse), password_hash TEXT, roles TEXT[] DEFAULT {BUYER}, status TEXT DEFAULT "PENDING_VERIFICATION", google_id TEXT UNIQUE NULLABLE, avatar_url, bio, preferred_language, timezone, verified_at, last_login_at, onboarding_completed_at, is_quantify_team BOOL DEFAULT false, deleted_at, scheduled_deletion_at, created_at, updated_at
• Index users : CREATE UNIQUE INDEX users_username_lower ON users(LOWER(username)) WHERE deleted_at IS NULL ; CREATE UNIQUE INDEX users_email_sparse ON users(email) WHERE email IS NOT NULL AND deleted_at IS NULL ; CREATE UNIQUE INDEX users_google_id ON users(google_id) WHERE google_id IS NOT NULL ; index phone, status
• sessions : id UUID PK, user_id FK, refresh_token_hash TEXT UNIQUE, ip_address INET, user_agent, device_info JSONB, remember_me, used_once BOOL DEFAULT false, used_at TIMESTAMPTZ, expires_at, revoked_at, created_at — INDEX (user_id, revoked_at, expires_at) WHERE revoked_at IS NULL
• otp_codes : id UUID PK, user_id FK, target TEXT, type TEXT, code_hash TEXT, expires_at, used_at, attempts — INDEX PARTIEL (target, type) WHERE used_at IS NULL AND expires_at > NOW()
• password_reset_tokens, contacts (INDEX (owner_id,status) et (contact_id,status)), username_history, notification_preferences, user_role_history (append-only), marketplace_role_data, phone_blacklist
• auth_audit_logs : partitionnement par mois (PARTITION BY RANGE(created_at)), rétention 90j avec job purge des partitions anciennes
• Triggers : update_updated_at sur users, immutability_username_set (une fois true → jamais false), immutability_password_hash (UPDATE uniquement via proc dédiée)
• pgBouncer configuré en transaction mode, pool_size=50 par pod, max_client_conn=5000
• Migrations via golang-migrate, up + down, testées avec testcontainers

Fichiers

migrations/
├── 000001_create_users.up.sql
├── 000002_create_sessions.up.sql
├── 000003_create_otp_codes.up.sql
├── 000004_create_password_reset_tokens.up.sql
├── 000005_create_contacts.up.sql
├── 000006_create_username_history.up.sql
├── 000007_create_notification_preferences.up.sql
├── 000008_create_user_role_history.up.sql
├── 000009_create_marketplace_role_data.up.sql
├── 000010_create_phone_blacklist.up.sql
└── 000011_create_auth_audit_logs_partitioned.up.sql

Scale

pgBouncer transaction mode = 50 connexions PG pour 5000 clients. Index LOWER(username) = lookup insensible à la casse O(log n). Partitionnement audit_logs = requêtes rapides même à 500M lignes.