Aller au contenu

Story TENANT-007: BYO-DB provisioning — resolver branch, Flyway-on-BYO & credential encryption

Module: tenant-admin Slice: S3b (Vertical Slice Decomposition §11) Side: [ADMIN] + [BACKEND] Version target: [V0.0.4] Priority: 7 Depends on: TENANT-004 Can develop concurrently with: TENANT-005, TENANT-008 Merge order: After TENANT-004 Estimated complexity: L PRD User Stories: TENANT-US-03 Wireframe: docs/wireframes/tenant-admin/TENANT-007/ (design ref ADM-002 étape 3 BYO-DB block + ProvisioningPipeline BYO failures)

Objective

Activate the BYO database profile for greenfield enterprise onboarding (NSIA production go-live target). A platform admin can mark a tenant as BYO-DB at creation and supply its PostgreSQL connection; provisioning then validates the connection, Flyway-migrates the (identical) tenant schema into that DB, runs a tenant-scoping smoke test, and stores the credentials envelope-encrypted. The datasource resolver (wired inert since TENANT-001) gains its real BYO branch. Greenfield only — migrating an existing SHARED tenant to BYO is out of V0 (deferred to V2). AUTH user tables stay on the platform DB regardless (AUTH-ADR-01).

Backend Scope

Entities

Uses the BYO columns already present on tenant (created Day 1 in TENANT-002): byo_db, byo_db_jdbc_url, byo_db_username, byo_db_password_ciphertext, byo_db_pool_size. No new tables.

Migrations

None new. This story proves the existing db/migration/tenant/* set applies identically to a BYO DB (no dialect drift; same schema as SHARED).

Service Layer

  • TenantDataSourceRouter BYO branch: getConnection(tenantId) returns the per-tenant BYO datasource when tenant.byo_db = true, else SHARED. BYO pools cached per-tenant in a ConcurrentHashMap keyed by tenantId. This is the ONLY place (with the onboarding service) that knows the byo_db flag — all downstream code still sees getConnection(tenantId).
  • ByoDbProvisioner (BYO_DB provisioning step, previously a no-op in TENANT-004):
  • (a) SELECT 1 connection test → fail BYO_DB_UNREACHABLE;
  • (b) Flyway baseline + migrate the tenant schema → fail BYO_DB_MIGRATION;
  • (c) tenant_id-scoping smoke test (insert + read + delete on a fixture) → fail BYO_DB_SMOKE. On any failure the tenant stays PENDING with a French last_provisioning_error; the step is idempotent and re-runnable (AC-02.2).
  • Encryptor.envelope(plaintext) for BYO credentials: KEK in env (PAPILLON_KEK_BASE64) / hosted KMS in prod; per-tenant DEK encrypted by KEK; ciphertext = version‖nonce‖tag‖ct packed in BYTEA. Plaintext password NEVER logged, NEVER returned to the UI (API returns "***"), NEVER in any audit text.
  • TenantConfigRepository is the single chokepoint that reads/decrypts BYO credentials and audits each access.

API Endpoints

Extends existing endpoints (no new paths): - POST /api/control/tenants accepts the BYO block {byoDb:true, jdbcUrl, dbUsername, dbPassword, poolSize} when ticked; all four become required. - POST /api/control/tenants/{id}/provision now runs the real BYO_DB step for BYO tenants. - Detail GET returns byoDbPassword: "***" (never the real value).

Validation Rules

  • When byo_db = true: jdbc_url, db_username, db_password, pool_size all required (AC-03.1).
  • jdbc_url must be a postgresql JDBC URL.

Multi-Tenant Considerations

A BYO tenant's tenant-scoped rows (agency, tenant_settings, future contracts) live in their DB; the tenant row stays on the platform DB (routing needs it before resolving). No code path diverges past TenantAwareDataSource (NFR-08). Greenfield only — no SHARED→BYO data migration (V2). SCHEMA/DATABASE profiles remain V3; the resolver stays agnostic to the number of datasources.

Audit & Logging

  • BYO credential access audited via TenantConfigRepository. TENANT_PROVISIONED covers successful BYO activation.
  • Logback MaskingPatternConverter redacts JDBC URLs containing password= (Technical Risk R2); an integration test attempts to log and asserts the secret is absent.

Frontend Scope

Pages & Routes

Extends the minimal create form (/admin/tenants/nouveau) with an isolation block, and the provisioning detail (/admin/tenants/:id) ProvisioningPipeline with BYO step states.

Components

Reuse Input (incl. type="password" with show/hide toggle), radio group, InlineBanner, ProvisioningPipeline.

UI States (ALL REQUIRED)

State Behavior French Copy
Loading BYO step shows « Test de connexion… {n} s »; no UI timeout before 60 s (NFR-03) « ⏳ 2. Base de données (BYO) — En cours… »
Empty No run yet « Aucun provisionnement lancé. »
Error Failed BYO step shows French error + retry BYO_DB_UNREACHABLE: « Connexion à la base BYO impossible. Vérifiez l'URL et le mot de passe. » · BYO_DB_MIGRATION: « Migration Flyway échouée. Vérifiez les droits (ALTER, CREATE TABLE). » · BYO_DB_SMOKE: « Test d'isolation échoué. Vérifiez que le schéma est vide et les droits DML. »
Offline / low network Pipeline keeps last state; retry idempotent « Connexion lente — état non actualisé. »
Success BYO step ✅ → continues to KEYCLOAK → ACTIVE « Cabinet {legal_name} activé. »

Responsive Behavior

Desktop-first; isolation block stacks single-column ≤ 768 px.

Connectivity Behavior (CUSTOMER stories only)

N/A — admin-side. Long BYO Flyway runs report progress via the retry mechanism; step state is server-persisted so a dropped POST is recoverable with the same Idempotency-Key.

Interactions

  • « BYO-DB (clause contractuelle requise) » radio reveals the JDBC fields (progressive disclosure, design D2/D9).
  • Password field uses type="password" + « 👁 Afficher » toggle during entry; after save it renders « •••••••• » with label « Mot de passe (chiffré — non récupérable) » and a « Modifier » button (design D9).
  • Warning copy: « Les identifiants sont chiffrés (KEK). Ils ne seront jamais affichés après enregistrement. »

Acceptance Criteria

AC-03.1: BYO-DB radio unlocks JDBC fields
Given the create form
When I select "BYO-DB (clause contractuelle requise)"
Then fields {jdbc_url, db_username, db_password, pool_size} appear and all become required

AC-03.2: Connection validated before activation
When the form is submitted with BYO-DB
Then provisioning runs (a) SELECT 1 on the supplied connection, (b) Flyway migrate to baseline, (c) a tenant_id-scoping smoke test (insert + read + delete on a fixture)
And if any step fails, the tenant stays PENDING with a French failure reason (BYO_DB_UNREACHABLE / BYO_DB_MIGRATION / BYO_DB_SMOKE)

AC-03.3: Credentials encrypted at rest
When BYO-DB credentials are stored
Then db_password is envelope-encrypted with the platform KEK
And the plaintext NEVER appears in any log line, audit entry, or error message returned to the UI (API returns "***")

Compliance Rules

  • No INCERTAIN/PRÉLIMINAIRE obligation raised. Security control: BYO credentials envelope-encrypted (NFR-06 spirit); MinIO/secret handling per arch §7.2.

Standards & Conventions

  • docs/standards/multi-datasource-patterns.md — BYO branch, per-tenant pool cache, single chokepoint.
  • docs/standards/multi-tenant-model.md — DB profile taxonomy; BYO greenfield only in V0.
  • docs/standards/database-guidelines.md — Flyway baseline + migrate on supplied connection.
  • docs/standards/java-spring-guidelines.md — Encryptor, masking converter.
  • docs/standards/design-system.md — password field D9, ProvisioningPipeline.
  • docs/standards/critical-rules.md — secrets never logged; tenant isolation.

Testing Requirements

Unit Tests

  • Resolver returns BYO datasource when byo_db=true, SHARED otherwise.
  • Envelope encrypt/decrypt round-trip; ciphertext format version‖nonce‖tag‖ct.

Integration Tests

  • BYO provisioning against an ephemeral second PostgreSQLContainer: SELECT 1 → Flyway migrate → smoke insert/read/delete; idempotent retry per arch §13.2.2 (force BYO_DB failure, recover, only that step re-runs).
  • Secret-leak guard (Technical Risk R2): attempt to log a credential-bearing JDBC URL → masked; API detail returns "***".
  • Tenant-scoping smoke proves isolation on the BYO DB.

What QA Will Validate

  • Greenfield BYO tenant reaches ACTIVE; each BYO failure code surfaces with correct French copy + retry; password never echoed.

Out of Scope

  • SHARED → BYO migration of an existing tenant (V2).
  • SCHEMA / DATABASE profiles (V3).
  • Moving AUTH user tables off the platform DB (they stay there — AUTH-ADR-01).
  • Keycloak per-org IdP federation (AUTH-005 / V0.0.5).

Definition of Done

  • [ ] Resolver BYO branch live; per-tenant pool cache; no code divergence past TenantAwareDataSource
  • [ ] BYO_DB provisioning step: SELECT 1 → Flyway migrate → tenant_id smoke; idempotent + re-runnable
  • [ ] BYO credentials envelope-encrypted (KEK/DEK); plaintext never logged/returned/audited; API returns "***"
  • [ ] Create form BYO block (progressive disclosure) + password D9 behavior render at 360/768/1024
  • [ ] All 5 ProvisioningPipeline states incl. the three BYO error codes
  • [ ] French micro-copy matches design spec exactly
  • [ ] AC-03.1, AC-03.2, AC-03.3 pass
  • [ ] Secret-leak guard test passes; both DB profiles tested (matrix)
  • [ ] Unit + integration tests passing