Formulation - Formulas : use a pre-mix as an ingredient, with its price, its (+) detail and "Not included" (frontend + backend + db)

Objective. A1: extend MP-690's composition aggregate in place. V035 lands the approved design's three columns, foreign key, five CHECKs and index on formula_phase_line with the matching entity fields; FormulaLineResponse loses kind - the work request's backend half, with the mapper constant and one test assertion - and gains premixFormulaId, premixCode, premixName, notIncluded and notIncludedLevel, as do the add and update requests; the six pre-mix rules, the mutually exclusive reference assignment and the single writer of formula_premix_use all live in the existing FormulaCompositionService; two reads are added, premix-candidates and premix-tree; then the mock pair is flipped to apiClient and its fixtures deleted. Reused unchanged: formula_premix_use and its daughters reader, the per-site price and INCI resolution, the client-side cost calculation and every pre-mix component. Nothing derived is stored and no replacement discriminator appears. Left out: a parallel pre-mix service or route (A2), stored prices or trees (A3), and the ticket's own exclusions.

Approach. TARGET, LAYER BY LAYER. Everything below is on the existing ticket branch; the approved design document on the branch is the normative source for the DDL and must not be redesigned (E25). 1. SCHEMA (slice 1, db-engineer). One Flyway file, V035__Add_Premix_Reference_And_Not_Included_To_Formula_Phase_Line.sql, transcribed from the design document's ALTER on formula_phase_line: premix_formula_id UUID NULL with a foreign key to formula(id) and an explicit ON DELETE RESTRICT; not_included BOOLEAN NOT NULL DEFAULT FALSE (the DEFAULT is what lets the ALTER run on populated rows); not_included_level VARCHAR(20) NULL. Five named CHECKs, each with its IS NOT NULL guard: never both references, no direct self-reference (premix_formula_id <> formula_id), no picked batch on a pre-mix line, the level restricted to THIS_PREMIX and FINAL_FORMULA, and a level only when the flag is set. One index, idx_formula_phase_line_premix_formula_id, which the RESTRICT check, the tree walk and the usage reconciliation all need. Nothing else: no kind column (the work request and design decision D2), no new table, no reference-data entity. V034 is the highest version at the indexed base (E30); the builder confirms the number against main before authoring. 2. ENTITIES (slice 1). FormulaPhaseLine gains premixFormula (@ManyToOne LAZY on premix_formula_id, to Formula), notIncluded (@Builder.Default false, nullable=false) and notIncludedLevel (@Enumerated(STRING), length 20), plus a new enum NotIncludedLevel {THIS_PREMIX, FINAL_FORMULA} in domain/formula - the two codes the mock already exchanges (E22). ddl-auto is validate, so the fields and the migration land together (E6). findCompositionByFormulaId adds LEFT JOIN FETCH on the referenced formula so mapping never lazy-loads (E32). 3. READ CONTRACT (slice 2). FormulaLineResponse drops kind and gains premixFormulaId (UUID, null on a raw-material or empty line), premixCode and premixName (String, "" when absent, matching the record's existing "" convention for commercialName), notIncluded (Boolean) and notIncludedLevel (String code or null). FormulaCompositionMapper loses LINE_KIND_RAW_MATERIAL and fills the pre-mix trio from line.getPremixFormula() (E3, E4). FormulaCompositionServiceTest loses the one kind assertion (E5). This completes the human work request's backend half; no replacement discriminator of any name is introduced anywhere - the reference field is the answer. 4. WRITE CONTRACT (slices 2-3). UpdateFormulaLineRequest gains premixFormulaId, notIncluded and notIncludedLevel as plain nullable components, following the record's documented full-replacement carve-out (E20). AddFormulaLineRequest gains the same three so a line can be created already holding a pre-mix. applyValues is extended to apply the two references mutually exclusively - assigning a pre-mix clears the raw material and the picked batch, assigning a raw material clears the pre-mix - so a mixed edit returns the rule's message instead of hitting the CHECK (E13). 5. RULES (slice 3), all in FormulaCompositionService, each a BusinessRuleException with a new messageKey: (a) a line holds at most one reference, with the preview's wording; (b) the referenced formula exists and is_premix is true; (c) nesting depth at most 5, walking the referenced formula's lines; (d) no cycle - a depth-first walk from the edited formula over premix references that accepts a shared sub-pre-mix reached twice by different paths and refuses only a genuine loop, which is the behaviour the mock review demanded; (e) on a formula that is itself a pre-mix, a ticked line requires a level, and on any other formula the level must be null; (f) a pre-mix line carries a typed lot only, never a picked batch. The depth, cycle and both-references messages reproduce the preview's text verbatim (E21) so the message does not change between the optimistic refusal and the server's, in messages.properties and messages_fr.properties next to the existing exception.formula.line.* keys (E29). Errors propagate to GlobalExceptionHandler as ProblemDetail; nothing is caught broadly. 6. USAGE RECORD (slice 4). One writer: a private reconcile(formulaId) in FormulaCompositionService that makes the formula's formula_premix_use rows equal the distinct non-null premixFormulaId values of its lines, using findByFormula_Id (E9). Called after addLine, updateLine, deleteLine and after copyComposition. copyLines' explicit column list gains premix_formula_id, not_included and not_included_level, keeping lot and batch deliberately uncopied (E12), and copyComposition then reconciles the duplicate (E14). GET /v1/formulas/{id}/daughters and the flag-off guard are untouched and start reporting the truth (E10, E11), which is AC10 end to end and what makes the alert count move from 2 to 1. 7. THE isPremix FLIP (slice 5), in FormulaService#applyPremixChange (E11): turning the flag off clears notIncludedLevel on that formula's lines, since the level is meaningless there; turning it on is refused, with its own message key, while a ticked line has no level. This is the behaviour the approved design states for its open item on the flip; it is adopted, not invented here. 8. READ ENDPOINTS (slices 6-7). - GET /v1/formulas/premix-candidates?q=&siteId= on FormulaController, served by FormulaService over formula.is_premix (E19, E31): no status filter, so a pre-mix in Development is offered (AC1); each row carries code, name, status code, label and colour and the pre-mix's own total as entered, computed as the sum of its filled non-QSP line percentages plus a filled QSP line's derived share. It mirrors the raw-material candidate endpoint's parameters and ranking (E17) so the grid merges two ranked lists exactly where the mock merges them today (E24); the live raw-material search is not touched. - GET /v1/formulas/{formulaId}/premix-tree?premixFormulaId=&siteId= on FormulaCompositionController (E15), served by FormulaCompositionService: a depth-first walk of the referenced formula's lines capped at 5 levels, each node carrying its path, depth, share in its own pre-mix, the referenced name, whether the node is itself a pre-mix, its aggregated INCI, its pricePerUnit, currencyCode and unitCode at the requested site, and its own notIncluded pair. {formulaId} is the formula being edited and siteId is the site it is being read at, so the same pre-mix prices differently at another site (AC2, AC3); prices and INCI come from RawMaterialSiteDataService, which already resolves exactly these fields per site (E18). The percentage in the final formula is the line's percentage multiplied along the path, as the mock computes it. - The server serves figures, not verdicts: the dash with its reason and the excluded count stay in the one client-side cost calculation that already owns MP-690's exclusion reasons (E26), so AC4's foreign-currency, no-price and wrong-unit cases are the existing reasons applied to pre-mix nodes, and AC5's not-100% warning stays a client-side reading of the served total. 9. THE FLIP (slice 9, frontend-engineer). api/formula-premix.ts becomes apiClient calls against the endpoints its TODO(MP-691) markers name, keeping its refused-result shape by mapping a 400 ProblemDetail detail into { outcome: "refused", message } so the hook's toast-and-revert path and every component stay as they are (E24). lib/mock-premix-data.ts is deleted with its three preview devices, which is what removes the three mock-stage limits the ticket requires gone: a line can be added after a pre-mix line, no demo lines are seeded into every formula, and the daughters alert lists only real formulas (E23, E27). api/formula-composition.ts's line mirror gains the five new fields and loses nothing else; use-formula-description reads the live daughters endpoint instead of the fixture. The single pre-mix predicate over premixFormulaId stays the only derivation site (E28). 10. TESTS (slice 8). FormulaCompositionServiceTest for each rule and for the reconciliation; FormulaServiceTest for the flip; a repository H2 IT for the migration and its five CHECKs, following the existing H2 IT naming; a Keycloak and Postgres controller IT for the two new endpoints covering 401, validation and the i18n of each new message. The gate is ./gradlew --no-daemon spotlessCheck and ./gradlew --no-daemon test, then npm run build and npm run lint after the flip (AC13). DATA FLOW. Grid cell search -> formula-candidates plus premix-candidates, merged client-side -> PUT or POST on the line with premixFormulaId -> service rules -> line row plus reconciled usage rows -> composition response with the pre-mix trio -> (+) opens premix-tree at the current site -> client cost calculation renders the price, the dash with its reason, the footer count and the not-100% warning. ERROR HANDLING. Rule refusals are BusinessRuleException (400) with a messageKey present in both message files; a missing formula, line or referenced pre-mix is ResourceNotFoundException (404); every composition write stays behind the existing locked-status refusal (E14). Nothing new is caught or swallowed in the api layer: a refusal becomes the mock's refused result, any other failure keeps the existing error path. — The code leaves little room for anything else and the two earlier stages fixed the rest. There is exactly one composition line table, one line entity, one mapper, one write service and one locked-formula guard (E6, E7, E13, E14, E15), so a second family (A2) would duplicate the ordering, locking and audit logic and split the single-reference rule across two writers. The approved DB design stores nothing derived and was approved on that basis (E25), prices are per-site and already resolved per site (E17, E18), and AC3 makes that site-dependence observable - so A3 would both contradict the approved schema and be wrong at the second site. The accepted mock is the API contract: two level codes (E22), a depth of 5 with three exact messages the backend must reproduce (E21), a refused-result error shape and one TODO(MP-691) marker per function naming its endpoint (E24); A1 is the only shape that lets the flip change no hook and no component. AC10 needs the usage record written, not redesigned: the table, its repository and its only reader already exist (E8, E9, E10) and the flag-off guard becomes effective the moment rows appear (E11). Finally, the human work request's backend half is unavoidably part of this stage: kind is still on the response, in the mapper constant and in one test assertion at the base (E1, E2, E3, E4, E5), and no routine before this one may touch backend/.

Implementation steps

1. 1Migration V035 and the line entity (db-engineer)

   Backend builder

   • backend/applications/cosmoreg-core/src/main/resources/db/migration/V035__Add_Premix_Reference_And_Not_Included_To_Formula_Phase_Line.sql
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/domain/formula/FormulaPhaseLine.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/domain/formula/NotIncludedLevel.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/repository/formula/FormulaPhaseLineRepository.java
2. 2Line contract: drop kind, add the pre-mix and Not included components (backend-engineer)

   Backend builder · after step 1

   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/dto/formula/FormulaLineResponse.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/mapper/formula/FormulaCompositionMapper.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/dto/formula/UpdateFormulaLineRequest.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/dto/formula/AddFormulaLineRequest.java
   • backend/applications/cosmoreg-core/src/test/java/com/chromasoftware/cosmoreg/core/service/formula/FormulaCompositionServiceTest.java
3. 3The six write rules and their messages (backend-engineer)

   Backend builder · after step 2

   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/service/formula/FormulaCompositionService.java
   • backend/applications/cosmoreg-core/src/main/resources/messages.properties
   • backend/applications/cosmoreg-core/src/main/resources/messages_fr.properties
4. 4Pre-mix usage record: one writer and its reconciliation (backend-engineer)

   Backend builder · after step 3

   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/service/formula/FormulaCompositionService.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/repository/formula/FormulaPhaseLineRepository.java
5. 5Not included level across the isPremix flip (backend-engineer)

   Backend builder · after step 3

   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/service/formula/FormulaService.java
   • backend/applications/cosmoreg-core/src/main/resources/messages.properties
   • backend/applications/cosmoreg-core/src/main/resources/messages_fr.properties
6. 6GET /v1/formulas/premix-candidates (backend-engineer)

   Backend builder · after step 2

   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/controller/formula/FormulaController.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/service/formula/FormulaService.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/dto/formula/PremixCandidateResponse.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/repository/formula/FormulaRepository.java
7. 7GET /v1/formulas/{formulaId}/premix-tree (backend-engineer)

   Backend builder · after step 2

   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/controller/formula/FormulaCompositionController.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/service/formula/FormulaCompositionService.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/dto/formula/PremixTreeResponse.java
   • backend/applications/cosmoreg-core/src/main/java/com/chromasoftware/cosmoreg/core/dto/formula/PremixTreeNodeResponse.java
8. 8Tests per criterion and the backend gate (backend-engineer)

   Backend builder · after step 4, 5, 6, 7

   • backend/applications/cosmoreg-core/src/test/java/com/chromasoftware/cosmoreg/core/service/formula/FormulaCompositionServiceTest.java
   • backend/applications/cosmoreg-core/src/test/java/com/chromasoftware/cosmoreg/core/service/formula/FormulaServiceTest.java
   • backend/applications/cosmoreg-core/src/test/java/com/chromasoftware/cosmoreg/core/repository/formula/FormulaPhaseLinePremixH2IT.java
   • backend/applications/cosmoreg-core/src/test/java/com/chromasoftware/cosmoreg/core/controller/formula/FormulaPremixCompositionIT.java
9. 9Flip the mock pair to the API and delete the fixtures (frontend-engineer)

   Backend builder · after step 8

   • frontend/src/domains/formulation/features/formulas/api/formula-premix.ts
   • frontend/src/domains/formulation/features/formulas/lib/mock-premix-data.ts
   • frontend/src/domains/formulation/features/formulas/api/formula-composition.ts
   • frontend/src/domains/formulation/features/formulas/description/hooks/use-formula-composition.ts
   • frontend/src/domains/formulation/features/formulas/description/hooks/use-formula-description.ts

Open the full technical plan