Aller au contenu

Story TENANT-002: Platform schema — country_profile (seed CI), tenant & tenant_settings tables + tenant config read service

Module: tenant-admin Slice: S1 (Vertical Slice Decomposition §11) Side: [BACKEND] Version target: [V0.0.0] Priority: 2 Depends on: TENANT-001 Can develop concurrently with: None (foundation for create/provision) Merge order: After TENANT-001, before TENANT-003 Estimated complexity: M PRD User Stories: N/A - Technical Story (enables TENANT-US-01/02) Wireframe: N/A — backend only

Objective

Create the data model that makes a tenant a real, scoped entity: the tenant directory table and the country_profile lookup (both on the platform DB), plus the tenant-scoped tenant_settings table (on the tenant DB). The full V1 tenant column set is created Day 1 (per arch §0.2) so that no later migration touches tenant-scoped tables between V0.0.0 and V0.0.4. Seed the CI country profile read-only. Expose a TenantConfigService so downstream modules (OP reads renewal_window_days; NOTIF reads channel + sender IDs) can resolve tenant configuration. No write/edit endpoints in this slice.

Backend Scope

Entities

tenant (platform DB) — full column set created now (do NOT add columns by later migration on tenant-scoped tables): id UUID PK · slug TEXT UNIQUE (derived from legal_name, numeric suffix on collision per OQ-04) · legal_name TEXT · commercial_name TEXT · country_code CHAR(2) NOT NULL (no DB default; app enforces 'CI' in V0) · group_id UUID NULL (reserved V2, unused) · agrement_number TEXT · ncc TEXT · address_line1 TEXT · address_line2 TEXT NULL · address_city TEXT · address_postal_code TEXT NULL · address_country CHAR(2) · primary_contact_name TEXT · primary_contact_email TEXT · primary_contact_phone TEXT (E.164) · support_email TEXT NULL · logo_object_key TEXT NULL · primary_color TEXT NULL (#RRGGBB) · plan TEXT (STARTER|GROWTH|ENTERPRISE) · byo_db BOOLEAN NOT NULL DEFAULT FALSE · byo_db_jdbc_url TEXT NULL (encrypted) · byo_db_username TEXT NULL (encrypted) · byo_db_password_ciphertext BYTEA NULL · byo_db_pool_size INT NULL · status TEXT (PENDING|ACTIVE|SUSPENDED|DELETED) · last_provisioning_error TEXT NULL · activated_at TIMESTAMPTZ NULL · suspended_at TIMESTAMPTZ NULL · suspension_reason_code TEXT NULL · suspension_reason_text TEXT NULL · deleted_at TIMESTAMPTZ NULL · created_at TIMESTAMPTZ · updated_at TIMESTAMPTZ.

country_profile (platform DB): country_code CHAR(2) PK · display_name_fr TEXT · currency_code CHAR(3) · phone_dial_code TEXT · payment_providers JSONB · notif_sms_providers JSONB · notif_wa_providers JSONB · e_invoicing_provider TEXT · e_invoicing_endpoint TEXT NULL · data_authority TEXT · cima_variation_ref TEXT NULL · locale TEXT · working_calendar JSONB NULL. Columns for FNE/e-invoicing exist but are NOT pre-reserved beyond the above — no BILL-specific columns in V0 (arch §0.2).

tenant_settings (tenant DB, tenant-scoped): tenant_id UUID PK · renewal_window_days INT DEFAULT 30 · min_auth_level INT DEFAULT 1 · default_channel TEXT DEFAULT 'WHATSAPP' (WHATSAPP|SMS) · whatsapp_template_id TEXT NULL · notif_daily_cap INT NULL · notif_sms_sender_id TEXT NULL · notif_wa_business_id TEXT NULL · audit_retention_years_override INT NULL · source_file_retention_days_override INT NULL · quittance_retention_years_override INT NULL · updated_at TIMESTAMPTZ.

Migrations

  • db/migration/platform/V1__country_profile.sql — CREATE TABLE country_profile.
  • db/migration/platform/V2__tenant.sql — CREATE TABLE tenant with: PK(id); UNIQUE(slug); partial UK UNIQUE (ncc) WHERE status <> 'DELETED' (TR-001 dup-NCC, soft-deleted releases NCC); IDX(country_code); IDX(status); IDX(suspended_at). NO unique constraint on legal_name (V2 group forward-compat). group_id present, nullable, no FK.
  • db/migration/platform/R__country_profile_seed_ci.sql — repeatable seed for CI: display_name_fr « Côte d'Ivoire », currency_code XOF, phone_dial_code +225, data_authority ARTCI, e_invoicing_provider FNE_CI, locale fr-CI, notif_sms_providers [ORANGE_SMS_CI], notif_wa_providers [WhatsApp Cloud API], payment_providers [ORANGE_MONEY_CI, WAVE_CI]. CI ONLY (no SN/BJ/CM/GA in V0).
  • db/migration/tenant/V1__tenant_settings.sql — CREATE TABLE tenant_settings; PK(tenant_id).
  • Ship a hand-written db/undo/U*.sql per migration for the dev environment (database-guidelines).

Service Layer

  • PlatformAdminTenantRepository — platform-DB access via @Qualifier("platformJdbcClient") JdbcClient only. Methods needed now: insert(tenant), findById, findByNcc(includeDeleted=false), existsByNccActive, findSimilarLegalNames(name) (for fuzzy warn in TENANT-003). NEVER a CrudRepository.
  • CountryProfileService.lookup(countryCode) — seeded read-only; returns the CI profile; throws a typed not-found used by provisioning step CP_BIND.
  • TenantConfigService — resolves per-tenant config consumed by other modules: renewalWindowDays(tenantId), defaultChannel(tenantId), smsSenderId(tenantId), waBusinessId(tenantId), status(tenantId). Backed by tenant_settings + tenant. Cache wiring (Redis TenantConfigCache, TTL 5 min) may be stubbed in V0.0.0 but the method surface must be stable — caches are invalidated by lifecycle/settings events in later slices.
  • SlugGenerator — derives slug from legal_name; appends numeric suffix on collision (OQ-04).

API Endpoints

None in this slice. (GET /api/control/tenants/{id} detail and GET /api/tenant/me are introduced minimally with their consuming slices; the read service is what V0.0.0 needs.)

Validation Rules

  • country_code must be 'CI' at the application layer in V0 (DB column allows any ISO-2 so V2 needs no migration).
  • audit_retention_years_override ≥ 10, source_file_retention_days_override ≥ 90, quittance_retention_years_override ≥ 10 when non-null (TR-009 floors) — enforced at the service boundary. These floors carry [LEGAL-REVIEW] (TV-03).

Multi-Tenant Considerations

tenant and country_profile are platform-DB tables accessed ONLY through the qualified JdbcClient (chicken-and-egg: the directory must exist before routing resolves). tenant_settings is tenant-scoped — every query MUST include tenant_id (critical-rules #1). No FK from tenant-scoped tables to tenant across DB boundaries (FK exists only in the shared-DB profile; for BYO the relationship is enforced via TenantContext). country_code drives country-specific config, never agency.

Audit & Logging

No business action here, so no audit event. (TENANT_CREATED is emitted in TENANT-003.)

Frontend Scope

N/A — backend only.

Acceptance Criteria

AC-T002.1: Schema applies on both profiles
Given the Flyway runner from TENANT-001
When the app starts
Then platform migrations V1/V2 + the CI seed apply to the platform DB
And tenant migration V1 (tenant_settings) applies to the shared tenant DB
And the same tenant migration set is provably applicable to a fresh BYO DB (CI matrix runs platform+tenant against a second container)

AC-T002.2: CI country profile is seeded and resolvable
Given a started app
When CountryProfileService.lookup("CI") is called
Then it returns currency XOF, dial code +225, data_authority ARTCI, e_invoicing_provider FNE_CI
And lookup of any non-CI code returns not-found (no other country seeded in V0)

AC-T002.3: NCC uniqueness ignores soft-deleted tenants
Given a tenant with NCC "1234567 X" and status ACTIVE
Then existsByNccActive("1234567 X") is true
And after that tenant is DELETED, existsByNccActive("1234567 X") is false (partial UK releases the NCC for re-onboarding)

AC-T002.4: Settings floors enforced at the service boundary
Given tenant_settings for a tenant
When source_file_retention_days_override is set to 30
Then the service rejects with a French floor message (« La durée minimale de rétention des dossiers sources est 90 jours. »)
And the default (NULL → 90) is returned by TenantConfigService when no override is set

Compliance Rules

  • TV-03 retention floors (audit_retention_years ≥ 10, source_file_retention_days ≥ 90, quittance_retention_years ≥ 10) — status PRÉLIMINAIRE → carry [LEGAL-REVIEW]. Analogy to ARTCI décision 2023-0877 is fragile; floors are Papillon's prudent baseline pending sectoral ARTCI deliberation (Loi 2013-450). Enforce the floor; do not let a tenant go below.
  • TV-02 / FNE fields — ncc + postal address captured so BILL (V1) can issue compliant FNE invoices. PRÉLIMINAIRE.

Standards & Conventions

  • docs/standards/database-guidelines.md — migrations, partial unique indexes, undo scripts.
  • docs/standards/multi-datasource-patterns.md — platform JdbcClient (Pattern 1).
  • docs/standards/multi-tenant-model.md — tenant directory on platform DB; tenant-scoped tables.
  • docs/standards/java-spring-guidelines.md — Spring Data JDBC, package com.altarys.papillon.pcs.controlplane.tenant.
  • docs/standards/critical-rules.md — #1 tenant isolation, #2 money as XOF integer minor units.
  • docs/standards/compliance-discipline.md — [LEGAL-REVIEW] handling.

Testing Requirements

Unit Tests

  • SlugGenerator collision → numeric suffix.
  • Retention-floor validation rejects below-floor values with French messages.

Integration Tests

  • Migration matrix: platform+tenant migrations apply cleanly on a fresh platform DB and a fresh second (BYO-analogue) container (proves V0.0.4 non-disruptive).
  • Partial UK on ncc releases value after soft-delete.
  • CountryProfileService.lookup("CI") returns seeded values; non-CI → not-found.
  • Tenant-scoped query on tenant_settings always includes tenant_id.

What QA Will Validate

  • Seed present after bootRun; schema columns match the V1 superset (no later migration needed for V0.0.4).

Out of Scope

  • Tenant create/provision logic, endpoints, and form (TENANT-003 / TENANT-004).
  • agency table (TENANT-005 / V0.0.2).
  • BYO credential encryption + decryption (TENANT-007 / V0.0.4).
  • Settings/branding edit endpoints, compliance-doc tables, DPA expiry (all V1).
  • Additional country profiles SN/BJ/CM/GA (V2).
  • group table + FK (V2).

Definition of Done

  • [ ] platform V1__country_profile, V2__tenant + CI repeatable seed apply
  • [ ] tenant V1__tenant_settings applies on shared tenant DB and a fresh BYO-analogue DB
  • [ ] full V1 tenant column superset present Day 1 (no later migration on tenant-scoped tables for V0.0.4)
  • [ ] partial UK on ncc (WHERE status <> 'DELETED'); no UNIQUE on legal_name; group_id nullable, no FK
  • [ ] CountryProfileService.lookup + TenantConfigService + PlatformAdminTenantRepository (JdbcClient only) implemented
  • [ ] retention floors enforced with French messages; [LEGAL-REVIEW] noted (TV-03)
  • [ ] Tenant isolation verified on tenant_settings; both DB profiles tested
  • [ ] Unit + integration tests passing