feat(structures): add Region/Delegation/Structure schema + seed (ADR-0027 PR 1) #228

Merged

julien merged 1 commits from feat/adr-0027-pr1-org-hierarchy-schema into main 2026-05-26 11:15:11 +02:00

Conversation 0 Commits 1 Files Changed 6

+515 -9

julien commented 2026-05-26 11:13:11 +02:00

Owner

Copy Link

Copy Source

Summary

First implementation PR for ADR-0027 (Region / Delegation / Structure portal-side organisational hierarchy). Schema + seed + drift-gate extension only — no consumer code yet (the PrismaScopeResolver that dereferences Structure.code from UserScope.value lives in ADR-0026 PR 2, which depends on this PR landing first).

Independent of ADR-0026 PR 1 at the schema level — both can ship in parallel; ADR-0026 PR 2 depends on both.

What lands

File	Change
apps/portal-bff/prisma/schema.prisma	+3 models. Region (INSEE code PK + name + delegations[]). Delegation (dept code PK + name + regionCode FK + structures[]). Structure (portal code PK + name + kind discriminator + nullable unique finess/siret/codePaie + nullable delegationCode FK). All in the public schema; matches the ADR-0027 schema sketch verbatim.
apps/portal-bff/prisma/migrations/20260526143000_add_org_hierarchy/migration.sql	New hand-written migration. DDL for the 3 tables. CHECK ("kind" IN (...)) constraint mirroring STRUCTURE_KINDS. Indexes (FK columns + kind + uniques). Inline INSERT seed: Région Nouvelle-Aquitaine (75), Délégation Gironde (33), structures 0330800013 + 0330800021 (médico-social, FINESS = code) + siege (APF national, no delegation/finess).
apps/portal-bff/src/structures/structure-kind.ts	New. Closed-set catalogue: STRUCTURE_KINDS = [medico_social, antenne, dispositif, entreprise_adaptee, mouvement, administratif, siege] as const. type StructureKind derived from the union, isStructureKind type guard.
apps/portal-bff/src/structures/structure-kind.spec.ts	New. Jest spec — catalogue content, no duplicates, type guard true for catalogue values + false for typos / cascade-only values / empty, type narrowing at call site.
scripts/check-catalogue-drift.mjs	Extend. New extractStructureKinds(path) + findStructureKindViolationsInFile(path, validKinds, sourceText?). Property-literal scanner: detects kind: 'X' in object literals, restricted to files that import from structure-kind.ts (cheap text pre-filter — kind is a common property name on unrelated objects and we'd false-positive everywhere otherwise). Integrated into scanWorkspace. Error-message formatter switched on callee shape (@Foo('x') for decorators, kind: 'x' for property literals). Closing hint updated to reference both ADR-0025 and ADR-0027 catalogue locations.
scripts/check-catalogue-drift.spec.mjs	Extend. Fixture writes a synthesised structure-kind.ts alongside authorization.types.ts. New tests: extract STRUCTURE_KINDS, throw on missing constant, skip files without the import, no-violation on catalogue values, flag off-catalogue values, skip non-literal initialisers, line/column tracking, scanWorkspace aggregation (decorator + Structure.kind together), scanWorkspace exposes the structureKinds set. 22 tests total, all passing.

Defense in depth

Three layers stack for Structure.kind, deliberately:

1. TypeScript type union StructureKind — compile-time check at every typed assignment.
2. Postgres CHECK constraint in the migration — runtime enforcement at INSERT / UPDATE, defends raw SQL / casts / untrusted API input.
3. scripts/check-catalogue-drift.mjs — CI gate asserting every kind: 'X' literal in structure-context files is in the catalogue.

The Privilege / FunctionalRole catalogues from ADR-0025 only have layer 1 + layer 3 (no DB enforcement — those values aren't persisted as schema-checked columns). ADR-0027's Structure.kind is persisted, so layer 2 was practical to add — bulletproof against any code path that bypasses the type system.

Seed scope (and what's deliberately NOT in it)

Just what the 19 test-tenant personas reference per notes/test-tenant-role-assignments.md:

• Region 75 Nouvelle-Aquitaine (only region the personas exercise)
• Delegation 33 Gironde (only delegation)
• Structure 0330800013 (APF Bordeaux, medico-social, FINESS = code)
• Structure 0330800021 (Complexe Mérignac, medico-social)
• Structure siege (APF national, kind=siege, no FK to a delegation, no FINESS)

No placeholder entreprise_adaptee, antenne, or dispositif row — those kinds are valid per the catalogue but the test tenant doesn't exercise them, and adding gold-plate seed data would be misleading ("what is this row used for?"). The full APF inventory lands with ADR-0029's cascade sync; this seed is superseded (not extended) by that sync.

Notes for the reviewer

• Naming conflict with the existing User model. The current schema.prisma already has a User model — but it's the ADR-0020 user-directory cache (Entra oid as PK, written by UserDirectoryService.recordSignIn). ADR-0026 PR 1 introduces a different User (UUID PK, FK to Person, lastSignInAt). Out of scope here — ADR-0027 PR 1 doesn't touch User. Flagging now so ADR-0026 PR 1 can plan the migration path (likely: rename the existing User to UserDirectoryEntry or fold it into the new Person + User pair).
• Migration is hand-written, matching the style of the two existing migrations (init_audit_schema, users_directory). Timestamp 20260526143000 chosen so it sorts after the existing 20260514192014_users_directory.
• @@schema("public") required on every new model because the audit log uses multiSchema (per ADR-0013) — the public/audit split is configured at the datasource.
• Drift gate error-message formatter now switches on callee shape — decorator violations still print as @Foo('x'), property-literal violations print as kind: 'x' to match the offending code shape.
• No pnpm ci:check impact expected at the bff level beyond the new spec; pnpm ci:catalogue-drift continues to report clean (catalogues: 4 privileges, 24 roles, 7 structure kinds).

Test plan

• node scripts/check-catalogue-drift.mjs — clean (4 / 24 / 7).
• node --test scripts/check-catalogue-drift.spec.mjs — 22 tests passing.
• pnpm exec prettier --check clean on the touched files.
• On the dev VM: pnpm prisma migrate dev applies the migration cleanly against a fresh infra/local/dev.compose.yml postgres. pnpm prisma studio shows the seeded Region / Delegation / Structure rows.
• On the dev VM: pnpm exec nx test portal-bff runs the new structure-kind.spec.ts green.
• Review focus — the Structure model shape (kind discriminator, nullable unique columns, FK to Delegation), the inline seed values vs notes/test-tenant-role-assignments.md, the drift-gate property-literal scanner's restriction to files importing from structure-kind.ts.

What's next

Per ADR-0027 §"Phasing":

1. This PR — Region / Delegation / Structure schema + seed + drift gate. ✅
2. ADR-0026 PR 1 — Person / User / UserScope schema + PersonAndUserProvisioner + drift gate extension for Person.source + updated PrincipalBuilder. Independent of (1), can ship in parallel. Needs to resolve the existing-User-name collision.
3. ADR-0026 PR 2 — PrismaScopeResolver replacing StubScopeResolver + /admin/users/:id/scopes admin screen + prisma/seed.ts populating the 19 personas' user_scopes rows pointing at this PR's Structure.code values. Depends on both (1) and (2).
4. ADR-0029 (future) — Pléiades + Acteurs+ + cascade syncs + facet schemas + Pole / Service / per-source enrichment extensions to this PR's hierarchy.

## Summary First implementation PR for [ADR-0027](docs/decisions/0027-portal-side-organisational-hierarchy.md) (`Region` / `Delegation` / `Structure` portal-side organisational hierarchy). **Schema + seed + drift-gate extension only** — no consumer code yet (the `PrismaScopeResolver` that dereferences `Structure.code` from `UserScope.value` lives in ADR-0026 PR 2, which depends on this PR landing first). Independent of ADR-0026 PR 1 at the schema level — both can ship in parallel; ADR-0026 PR 2 depends on both. ## What lands | File | Change | | --- | --- | | `apps/portal-bff/prisma/schema.prisma` | **+3 models**. `Region` (INSEE code PK + name + delegations[]). `Delegation` (dept code PK + name + regionCode FK + structures[]). `Structure` (portal code PK + name + kind discriminator + nullable unique finess/siret/codePaie + nullable delegationCode FK). All in the `public` schema; matches the ADR-0027 schema sketch verbatim. | | `apps/portal-bff/prisma/migrations/20260526143000_add_org_hierarchy/migration.sql` | **New** hand-written migration. DDL for the 3 tables. `CHECK ("kind" IN (...))` constraint mirroring `STRUCTURE_KINDS`. Indexes (FK columns + kind + uniques). Inline INSERT seed: Région Nouvelle-Aquitaine (75), Délégation Gironde (33), structures `0330800013` + `0330800021` (médico-social, FINESS = code) + `siege` (APF national, no delegation/finess). | | `apps/portal-bff/src/structures/structure-kind.ts` | **New**. Closed-set catalogue: `STRUCTURE_KINDS = [medico_social, antenne, dispositif, entreprise_adaptee, mouvement, administratif, siege] as const`. `type StructureKind` derived from the union, `isStructureKind` type guard. | | `apps/portal-bff/src/structures/structure-kind.spec.ts` | **New**. Jest spec — catalogue content, no duplicates, type guard true for catalogue values + false for typos / cascade-only values / empty, type narrowing at call site. | | `scripts/check-catalogue-drift.mjs` | **Extend**. New `extractStructureKinds(path)` + `findStructureKindViolationsInFile(path, validKinds, sourceText?)`. Property-literal scanner: detects `kind: 'X'` in object literals, restricted to files that import from `structure-kind.ts` (cheap text pre-filter — `kind` is a common property name on unrelated objects and we'd false-positive everywhere otherwise). Integrated into `scanWorkspace`. Error-message formatter switched on callee shape (`@Foo('x')` for decorators, `kind: 'x'` for property literals). Closing hint updated to reference both ADR-0025 and ADR-0027 catalogue locations. | | `scripts/check-catalogue-drift.spec.mjs` | **Extend**. Fixture writes a synthesised `structure-kind.ts` alongside `authorization.types.ts`. New tests: extract STRUCTURE_KINDS, throw on missing constant, skip files without the import, no-violation on catalogue values, flag off-catalogue values, skip non-literal initialisers, line/column tracking, scanWorkspace aggregation (decorator + Structure.kind together), scanWorkspace exposes the structureKinds set. **22 tests total, all passing.** | ## Defense in depth Three layers stack for `Structure.kind`, deliberately: 1. **TypeScript type union** `StructureKind` — compile-time check at every typed assignment. 2. **Postgres `CHECK` constraint** in the migration — runtime enforcement at INSERT / UPDATE, defends raw SQL / casts / untrusted API input. 3. **`scripts/check-catalogue-drift.mjs`** — CI gate asserting every `kind: 'X'` literal in structure-context files is in the catalogue. The `Privilege` / `FunctionalRole` catalogues from ADR-0025 only have layer 1 + layer 3 (no DB enforcement — those values aren't persisted as schema-checked columns). ADR-0027's `Structure.kind` is persisted, so layer 2 was practical to add — bulletproof against any code path that bypasses the type system. ## Seed scope (and what's deliberately NOT in it) Just what the 19 test-tenant personas reference per `notes/test-tenant-role-assignments.md`: - `Region` 75 Nouvelle-Aquitaine (only region the personas exercise) - `Delegation` 33 Gironde (only delegation) - `Structure` 0330800013 (APF Bordeaux, medico-social, FINESS = code) - `Structure` 0330800021 (Complexe Mérignac, medico-social) - `Structure` `siege` (APF national, kind=siege, no FK to a delegation, no FINESS) **No** placeholder `entreprise_adaptee`, `antenne`, or `dispositif` row — those kinds are valid per the catalogue but the test tenant doesn't exercise them, and adding gold-plate seed data would be misleading ("what is this row used for?"). The full APF inventory lands with [ADR-0029](#)'s cascade sync; this seed is **superseded** (not extended) by that sync. ## Notes for the reviewer - **Naming conflict with the existing `User` model.** The current `schema.prisma` already has a `User` model — but it's the ADR-0020 user-directory cache (Entra `oid` as PK, written by `UserDirectoryService.recordSignIn`). ADR-0026 PR 1 introduces a different `User` (UUID PK, FK to `Person`, `lastSignInAt`). **Out of scope here** — ADR-0027 PR 1 doesn't touch `User`. Flagging now so ADR-0026 PR 1 can plan the migration path (likely: rename the existing `User` to `UserDirectoryEntry` or fold it into the new Person + User pair). - **Migration is hand-written**, matching the style of the two existing migrations (`init_audit_schema`, `users_directory`). Timestamp `20260526143000` chosen so it sorts after the existing `20260514192014_users_directory`. - **`@@schema("public")`** required on every new model because the audit log uses `multiSchema` (per ADR-0013) — the public/audit split is configured at the datasource. - **Drift gate error-message formatter** now switches on callee shape — decorator violations still print as `@Foo('x')`, property-literal violations print as `kind: 'x'` to match the offending code shape. - **No `pnpm ci:check` impact expected** at the bff level beyond the new spec; `pnpm ci:catalogue-drift` continues to report clean (`catalogues: 4 privileges, 24 roles, 7 structure kinds`). ## Test plan - [x] `node scripts/check-catalogue-drift.mjs` — clean (4 / 24 / 7). - [x] `node --test scripts/check-catalogue-drift.spec.mjs` — 22 tests passing. - [x] `pnpm exec prettier --check` clean on the touched files. - [ ] **On the dev VM**: `pnpm prisma migrate dev` applies the migration cleanly against a fresh `infra/local/dev.compose.yml` postgres. `pnpm prisma studio` shows the seeded Region / Delegation / Structure rows. - [ ] **On the dev VM**: `pnpm exec nx test portal-bff` runs the new `structure-kind.spec.ts` green. - [ ] **Review focus** — the `Structure` model shape (kind discriminator, nullable unique columns, FK to Delegation), the inline seed values vs `notes/test-tenant-role-assignments.md`, the drift-gate property-literal scanner's restriction to files importing from `structure-kind.ts`. ## What's next Per [ADR-0027 §"Phasing"](docs/decisions/0027-portal-side-organisational-hierarchy.md): 1. **This PR** — Region / Delegation / Structure schema + seed + drift gate. ✅ 2. **ADR-0026 PR 1** — `Person` / `User` / `UserScope` schema + `PersonAndUserProvisioner` + drift gate extension for `Person.source` + updated `PrincipalBuilder`. Independent of (1), can ship in parallel. **Needs to resolve the existing-`User`-name collision.** 3. **ADR-0026 PR 2** — `PrismaScopeResolver` replacing `StubScopeResolver` + `/admin/users/:id/scopes` admin screen + `prisma/seed.ts` populating the 19 personas' `user_scopes` rows pointing at this PR's `Structure.code` values. **Depends on both (1) and (2).** 4. **ADR-0029** (future) — Pléiades + Acteurs+ + cascade syncs + facet schemas + `Pole` / `Service` / per-source enrichment extensions to this PR's hierarchy.

julien added 1 commit 2026-05-26 11:13:12 +02:00

feat(structures): add Region/Delegation/Structure schema + seed (ADR-0027 PR 1) ... 9695c9080b

Schema (Prisma): three new public-schema models per ADR-0027 -
Region (INSEE code PK), Delegation (dept code PK + regionCode FK),
Structure (portal-internal code PK + kind discriminator + nullable
unique finess/siret/codePaie + nullable delegationCode FK).

Migration: hand-written, matches the style of init_audit_schema and
users_directory. DDL + CHECK constraint on Structure.kind mirroring
STRUCTURE_KINDS + indexes + inline seed (Region 75 Nouvelle-Aquitaine,
Delegation 33 Gironde, structures 0330800013 + 0330800021 medico-social
Bordeaux/Merignac + siege APF national). Seed is the minimum the 19
test-tenant personas reference; ADR-0029's cascade sync supersedes it
when it ships.

Catalogue (TypeScript): apps/portal-bff/src/structures/structure-kind.ts
exports STRUCTURE_KINDS as const + StructureKind type union +
isStructureKind type guard. Jest spec covers catalogue content,
type guard, narrowing.

Drift gate: extended to scan property-literal "kind: 'X'" expressions
in files that import from structure-kind.ts. Three-layer defense
in depth for Structure.kind = TS type union + Postgres CHECK +
CI gate. 22 spec tests passing.

No consumer code yet - PrismaScopeResolver that dereferences
Structure.code from UserScope.value lives in ADR-0026 PR 2, which
depends on this PR landing first.

Reviewer heads-up: schema.prisma already has a User model (ADR-0020
user-directory cache); ADR-0026 PR 1 introduces a different User
(UUID PK + Person FK). Naming collision flagged for that PR to
resolve - not touched here.

julien merged commit ba4cdcee7a into main 2026-05-26 11:15:11 +02:00

julien deleted branch feat/adr-0027-pr1-org-hierarchy-schema 2026-05-26 11:15:12 +02:00

julien referenced this issue from a commit 2026-05-26 12:09:01 +02:00

fix(structures): align Structure.delegation_code FK action with Prisma default

julien referenced this pull request 2026-05-26 12:10:51 +02:00

fix(structures): align Structure.delegation_code FK action with Prisma default #229

julien referenced this issue from a commit 2026-05-26 12:11:16 +02:00

fix(structures): align Structure.delegation_code FK action with Prisma default (#229)

Sign in to join this conversation.

Reviewers

No Reviewers

Labels

No items

No Label

Milestone

No items

No Milestone

Projects

Clear projects

No project

Assignees

Clear assignees

adastra-bot (AdAstra Bot) julien (Julien Gautier)

No Assignees

1 Participants

Notifications

Subscribe

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: julien/apf_portal#228