#04 — rgz-db

EN PRODUCTION

Priorité: 🔴 CRITIQUE · Type: TYPE A · Conteneur: rgz-db · Code: config/postgres/

Dépendances: Aucune

Description

rgz-db est le moteur de persistance de toute la plateforme RGZ. Il s'agit d'une instance PostgreSQL 16 avec l'extension TimescaleDB activée. PostgreSQL stocke toutes les données métier (abonnés, revendeurs, sessions, facturation, conformité), tandis que TimescaleDB transforme les tables de métriques en hypertables, permettant des requêtes temporelles haute performance (partitionnement automatique par chunks de 7 jours, compression native, rétention automatique).

Le schéma de la base de données respecte strictement les conventions de API_Contracts_RGZ.md : tous les identifiants sont des UUIDs v4 (jamais d'auto-increment integer — LL#8), tous les champs enum sont protégés par des contraintes CHECK PostgreSQL (LL#16), et les colonnes correspondent exactement aux contrats définis (LL#5). Les tables métriques (sla_results, snmp_metrics) sont des hypertables TimescaleDB partitionnées sur leur colonne measured_at.

La base de données est le point de départ de la phase 0 du développement (aucune dépendance). Tous les autres services de la catégorie A (et au-delà) en dépendent. Les sauvegardes automatiques sont assurées par l'outil #75 (pg-dump-backup) via Celery Beat à 03h00 UTC chaque nuit, avec une rétention de 30 jours.

La sécurité suit la règle SEC-08 : connexion SSL obligatoire (sslmode=require), rôles PostgreSQL séparés par service (lecture seule rgz_ro, lecture/écriture rgz_rw, append-only pour les logs immutables rgz_append). Le mot de passe est injecté via variable d'environnement, jamais dans le Dockerfile.

Architecture Interne

docker-compose.core.yml
      │
      ▼
  rgz-db (postgres:16-timescaledb)
      │
      ├── Volume: rgz-pg-data → /var/lib/postgresql/data (persistant)
      ├── Port: 5432 (interne rgz-net uniquement, JAMAIS exposé hôte)
      │
      ├── Extensions:
      │   ├── timescaledb     (métriques haute perf)
      │   ├── uuid-ossp       (gen_random_uuid())
      │   └── pg_stat_statements (monitoring requêtes)
      │
      └── Schéma public:
          ├── subscribers + subscriber_devices
          ├── resellers + reseller_sites
          ├── radius_sessions
          ├── vouchers + payments + invoices
          ├── sla_results (hypertable)
          ├── snmp_metrics (hypertable)
          ├── audit_logs (append-only)
          └── immutable_logs (SHA-256, append-only)

Configuration

Variables d'environnement

Variable	Exemple	Description
`POSTGRES_DB`	`rgz`	Nom de la base de données
`POSTGRES_USER`	`rgz`	Utilisateur principal
`POSTGRES_PASSWORD`	`changeme-strong-password`	Mot de passe (jamais dans Dockerfile)
`POSTGRES_INITDB_ARGS`	`--encoding=UTF8 --locale=fr_FR.UTF-8`	Paramètres d'initialisation

Fichiers de configuration

config/postgres/
├── postgresql.conf          # Tuning performances (shared_buffers, work_mem, etc.)
├── pg_hba.conf              # Règles d'accès: scram-sha-256, sslmode=require
└── init/
    ├── 01-extensions.sql    # CREATE EXTENSION timescaledb, uuid-ossp
    ├── 02-schema.sql        # Toutes les tables avec CHECK constraints
    ├── 03-hypertables.sql   # SELECT create_hypertable() pour métriques
    ├── 04-roles.sql         # Rôles séparés rgz_ro, rgz_rw, rgz_append
    └── 05-indexes.sql       # Index optimisés (BRIN sur timestamps, GiST géo)

Schéma de la Base de Données

Table subscribers

sql

CREATE TABLE subscribers (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subscriber_ref      TEXT UNIQUE NOT NULL,  -- "RGZ-0197979964"
    msisdn              TEXT UNIQUE NOT NULL,
    msisdn_e164         TEXT UNIQUE NOT NULL,
    full_name           TEXT NOT NULL,
    id_document_type    TEXT NOT NULL CHECK (id_document_type IN ('CNI','CIP','PASSPORT')),
    id_document_hash    TEXT,                  -- SHA-256 du scan OCR
    status              TEXT NOT NULL DEFAULT 'pending'
                        CHECK (status IN ('pending','active','suspended','blocked')),
    consent_given_at    TIMESTAMP WITH TIME ZONE,
    total_sessions      INTEGER DEFAULT 0,
    total_data_mb       BIGINT DEFAULT 0,
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Table subscriber_devices (tracking — pas de limite de slots)

sql

CREATE TABLE subscriber_devices (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subscriber_id   UUID NOT NULL REFERENCES subscribers(id) ON DELETE CASCADE,
    mac_address     TEXT NOT NULL,
    mac_type        TEXT NOT NULL DEFAULT 'unknown'
                    CHECK (mac_type IN ('permanent','randomized','unknown')),
    oui_vendor      TEXT,
    first_seen_at   TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_seen_at    TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_nas_id     TEXT,
    session_count   INTEGER DEFAULT 0,
    UNIQUE(subscriber_id, mac_address)
);

Table resellers

sql

CREATE TABLE resellers (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug            TEXT UNIQUE NOT NULL,           -- "access_kossou"
    name            TEXT NOT NULL,
    classification  TEXT NOT NULL CHECK (classification IN ('V1','V2','V3')),
    status          TEXT NOT NULL DEFAULT 'candidate'
                    CHECK (status IN ('candidate','validated','active','suspended','terminated')),
    vlan_id         INTEGER UNIQUE,                 -- 100-499
    commission_rate NUMERIC(5,4) DEFAULT 0.015,     -- 1.5%
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Table reseller_sites

sql

CREATE TABLE reseller_sites (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    reseller_id     UUID NOT NULL REFERENCES resellers(id),
    site_number     INTEGER NOT NULL,
    nas_id          TEXT UNIQUE NOT NULL,
    ssid            TEXT NOT NULL,
    latitude        DOUBLE PRECISION,
    longitude       DOUBLE PRECISION,
    city            TEXT,
    quarter         TEXT,
    ap_ip           INET,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(reseller_id, site_number)
);

Table radius_sessions

sql

CREATE TABLE radius_sessions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    subscriber_id   UUID NOT NULL REFERENCES subscribers(id),
    subscriber_ref  TEXT NOT NULL,
    mac_address     TEXT NOT NULL,
    nas_id          TEXT NOT NULL,
    forfait_id      UUID REFERENCES vouchers(id),
    session_start   TIMESTAMP WITH TIME ZONE NOT NULL,
    session_stop    TIMESTAMP WITH TIME ZONE,
    bytes_in        BIGINT DEFAULT 0,
    bytes_out       BIGINT DEFAULT 0,
    is_active       BOOLEAN DEFAULT TRUE
);

Tables métriques (TimescaleDB hypertables)

sql

-- sla_results — partitionné par semaine
CREATE TABLE sla_results (
    id              UUID NOT NULL DEFAULT gen_random_uuid(),
    reseller_id     UUID REFERENCES resellers(id),
    nas_id          TEXT,
    measured_at     TIMESTAMP WITH TIME ZONE NOT NULL,
    latency_ms      INTEGER,
    packet_loss     NUMERIC(5,2),
    is_up           BOOLEAN DEFAULT TRUE
);
SELECT create_hypertable('sla_results', 'measured_at', chunk_time_interval => INTERVAL '7 days');

-- snmp_metrics — partitionné par semaine
CREATE TABLE snmp_metrics (
    id              UUID NOT NULL DEFAULT gen_random_uuid(),
    device_ip       INET NOT NULL,
    nas_id          TEXT,
    measured_at     TIMESTAMP WITH TIME ZONE NOT NULL,
    metric_name     TEXT NOT NULL,
    metric_value    DOUBLE PRECISION
);
SELECT create_hypertable('snmp_metrics', 'measured_at', chunk_time_interval => INTERVAL '7 days');

Healthcheck

bash

# Healthcheck Docker
pg_isready -U $POSTGRES_USER -d $POSTGRES_DB

# Depuis l'hôte (si port exposé en dev)
docker exec rgz-db pg_isready -U rgz -d rgz

# Connexion psql interactive
docker exec -it rgz-db psql -U rgz -d rgz

# Vérifier l'extension TimescaleDB
docker exec rgz-db psql -U rgz -d rgz -c "\dx timescaledb"

# Lister les hypertables
docker exec rgz-db psql -U rgz -d rgz \
  -c "SELECT hypertable_name, num_chunks FROM timescaledb_information.hypertables;"

# Stats connexions actives
docker exec rgz-db psql -U rgz -d rgz \
  -c "SELECT count(*), state FROM pg_stat_activity GROUP BY state;"

Sécurité

Règle	Implémentation
SEC-08 SSL	`sslmode=require` dans toutes les `DATABASE_URL` des services
SEC-08 Rôles	`rgz_ro` (SELECT), `rgz_rw` (SELECT+INSERT+UPDATE), `rgz_append` (INSERT seul sur audit_logs)
LL#8 UUID	`gen_random_uuid()` partout, jamais de `SERIAL` ou `BIGSERIAL`
LL#16 CHECK	Contraintes CHECK sur tous les champs enum (status, classification, etc.)
LL#5 Colonnes	Colonnes correspondent exactement aux contrats API

bash

# Créer les rôles de sécurité (init/04-roles.sql)
CREATE ROLE rgz_ro   NOLOGIN;
CREATE ROLE rgz_rw   NOLOGIN;
CREATE ROLE rgz_append NOLOGIN;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO rgz_ro;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO rgz_rw;
GRANT INSERT ON audit_logs, immutable_logs TO rgz_append;

Commandes Utiles

bash

# Démarrer (premier lancement — initialise le schéma)
docker compose -f /home/claude-dev/RGZ/docker-compose.core.yml up -d rgz-db

# Vérifier l'état
docker compose -f /home/claude-dev/RGZ/docker-compose.core.yml ps rgz-db

# Logs PostgreSQL
docker logs rgz-db -f --tail=100

# Backup manuel
docker exec rgz-db pg_dump -U rgz rgz | gzip > /backup/rgz-$(date +%Y%m%d).sql.gz

# Restaurer un backup
gunzip < /backup/rgz-20260221.sql.gz | docker exec -i rgz-db psql -U rgz -d rgz

# Taille de la base
docker exec rgz-db psql -U rgz -d rgz \
  -c "SELECT pg_size_pretty(pg_database_size('rgz'));"

# Vacuum analyze (maintenance)
docker exec rgz-db psql -U rgz -d rgz -c "VACUUM ANALYZE;"

# Lister les tables avec taille
docker exec rgz-db psql -U rgz -d rgz \
  -c "SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) FROM pg_class WHERE relkind='r' ORDER BY pg_total_relation_size(oid) DESC LIMIT 20;"

Implémentation TODO

[x] Service défini dans docker-compose.core.yml
[x] Volume persistant rgz-pg-data configuré
[x] Variables d'env dans .env.example
[ ] config/postgres/postgresql.conf — Tuning perf (shared_buffers=256MB, etc.)
[ ] config/postgres/pg_hba.conf — scram-sha-256 + sslmode=require
[ ] config/postgres/init/01-extensions.sql — timescaledb, uuid-ossp
[ ] config/postgres/init/02-schema.sql — Toutes les tables (10+ tables)
[ ] config/postgres/init/03-hypertables.sql — sla_results, snmp_metrics
[ ] config/postgres/init/04-roles.sql — Rôles séparés ro/rw/append
[ ] config/postgres/init/05-indexes.sql — Index BRIN timestamps, GiST géo
[ ] Tests de migration schéma
[ ] Validation contraintes CHECK avec données de test

Dernière mise à jour: 2026-02-21

#04 — rgz-db ​

Description ​

Architecture Interne ​

Configuration ​

Variables d'environnement ​

Fichiers de configuration ​

Schéma de la Base de Données ​

Table subscribers ​

Table subscriber_devices (tracking — pas de limite de slots) ​

Table resellers ​

Table reseller_sites ​

Table radius_sessions ​

Tables métriques (TimescaleDB hypertables) ​

Healthcheck ​

Sécurité ​

Commandes Utiles ​

Implémentation TODO ​

#04 — rgz-db

Description

Architecture Interne

Configuration

Variables d'environnement

Fichiers de configuration

Schéma de la Base de Données

Table subscribers

Table subscriber_devices (tracking — pas de limite de slots)

Table resellers

Table reseller_sites

Table radius_sessions

Tables métriques (TimescaleDB hypertables)

Healthcheck

Sécurité

Commandes Utiles

Implémentation TODO