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/.

Scope

• User interface: The flip of the accepted mock: api/formula-premix.ts calls apiClient only, and lib/mock-premix-data.ts is deleted with its three preview devices; api/formula-composition.ts's line mirror gains premixFormulaId, premixCode, premixName, notIncluded and notIncludedLevel; nothing else in it changes and the discriminator does not come back; use-formula-description reads the live GET /v1/formulas/{id}/daughters instead of the mock fixture, so the existing impact alert counts real formulas; The three mock-stage limits the ticket requires gone disappear with the fixtures: a line can be added after a pre-mix line, no demo pre-mix lines are seeded into every formula, and the daughters alert lists only real formulas; No component, no column definition and no cost calculation changes: the grid, the (+) rows, the level dialog, the badge and lib/composition-math.ts keep their current behaviour and their current reading of the served figures; Every TODO(MP-691) marker disappears, each replaced by the apiClient call it named
• Database: One Flyway migration, V035, implementing the approved DB design's ALTER on formula_phase_line: three columns, one foreign key with an explicit ON DELETE RESTRICT, five named CHECKs and one index; No new table, no reference-data entity and so no admin-console entry; formula_premix_use (V028) is reused with its shape, keys and indexes untouched and simply starts receiving rows; formula_phase_line holds production rows: the one NOT NULL column carries a DEFAULT so the ALTER cannot abort, and nothing is narrowed, dropped, renamed or newly unique; No kind column is added and none exists - the discriminator is the reference field, per the human work request and the design's own decision; Entity changes land with the migration because ddl-auto is validate: three fields on FormulaPhaseLine plus a NotIncludedLevel enum
• API and backend: GET /v1/formulas/{formulaId}/composition: FormulaLineResponse loses kind and gains premixFormulaId, premixCode, premixName, notIncluded and notIncludedLevel; the composition query fetch-joins the referenced formula. Open to any authenticated user, as today; POST /v1/formulas/{formulaId}/lines and PUT /v1/formulas/{formulaId}/lines/{lineId}: the request records gain premixFormulaId, notIncluded and notIncludedLevel; both references are applied mutually exclusively; six new rules refuse with their own message keys; both writes reconcile the pre-mix usage record. Authenticated user, behind the existing locked-composition refusal; DELETE /v1/formulas/{formulaId}/lines/{lineId}: no contract change; reconciles the usage record after the delete, which is the half of AC10 that moves the count from 2 to 1; GET /v1/formulas/premix-candidates?q=&siteId= (new, FormulaController): pre-mix formulas whatever their status, with code, name, status code/label/colour and the pre-mix's own total as entered. Authenticated user, mirroring the raw-material candidate endpoint; GET /v1/formulas/{formulaId}/premix-tree?premixFormulaId=&siteId= (new, FormulaCompositionController): the referenced formula's ingredients to 5 levels with per-node share, name, INCI, per-site price/currency/unit and Not included pair. Authenticated user, read-only; PUT /v1/formulas/{id}: no contract change, two behaviour changes - turning isPremix off clears the formula's line levels, turning it on is refused while a ticked line has no level; the existing flag-off guard also becomes effective now that the usage record is written; GET /v1/formulas/{id}/daughters: unchanged code, now returning real rows - the alert stops reading zero; POST /v1/formulas in duplicate mode: the composition copy carries the three new columns and then writes the duplicate's own usage rows; lot and batch stay deliberately uncopied; New message keys in messages.properties and messages_fr.properties for the depth, cycle, both-references, not-a-pre-mix, level-required and batch-on-a-pre-mix-line refusals, plus the isPremix flip refusal; no new dependency and no new service, controller or repository beyond the two DTO families for the new reads

Milestones

1. Migration V035 and the line entity (db-engineer)
2. Line contract: drop kind, add the pre-mix and Not included components (backend-engineer) (after 1)
3. The six write rules and their messages (backend-engineer) (after 2)
4. Pre-mix usage record: one writer and its reconciliation (backend-engineer) (after 3)
5. Not included level across the isPremix flip (backend-engineer) (after 3)
6. GET /v1/formulas/premix-candidates (backend-engineer) (after 2)
7. GET /v1/formulas/{formulaId}/premix-tree (backend-engineer) (after 2)
8. Tests per criterion and the backend gate (backend-engineer) (after 4, 5, 6, 7)
9. Flip the mock pair to the API and delete the fixtures (frontend-engineer) (after 8)

Acceptance criteria

• V035 matches the approved design's ALTER (three columns, FK with ON DELETE RESTRICT, five named CHECKs, one index); the entity carries the three fields so validate passes; the composition query fetch-joins the referenced formula.
• No kind component, no LINE_KIND_RAW_MATERIAL and no kind assertion remain, and no replacement appears; the response and both write requests carry the pre-mix trio and the Not included pair, named as the mock's types name them.
• Add and update refuse both references, a referenced formula that is not a pre-mix, depth over 5, a genuine cycle, a missing level on a pre-mix formula and a batch on a pre-mix line; a shared sub-pre-mix is accepted; the references are applied mutually exclusively; every key is in both files.
• After every add, update, delete and duplicate copy, the formula's formula_premix_use rows equal its lines' distinct pre-mix references; copyLines carries the three new columns with lot and batch still uncopied; the daughters endpoint reports 2 then 1.
• Turning the pre-mix flag off clears that formula's line levels; turning it on is refused while a ticked line has no level, with its own localized message; the existing refusal naming daughter codes still works.
• Pre-mix formulas are returned whatever their status, with code, name, status code/label/colour and the pre-mix's own total as entered; the live raw-material candidate endpoint is untouched.
• The walk returns every node to at most 5 levels with its share, the multiplied percentage, name, isPremix, INCI, per-site price/currency/unit and its Not included pair, resolved at the requested site through the existing per-site service.
• Each of AC1 to AC11 has a test naming it, each CHECK has its four cases, the two new endpoints have 401, validation and i18n coverage, and ./gradlew --no-daemon spotlessCheck and ./gradlew --no-daemon test pass.
• api/formula-premix.ts calls apiClient only with no TODO(MP-691) marker left, lib/mock-premix-data.ts is deleted, the line mirror carries the five new fields, the alert lists real formulas, the three mock-stage limits are gone, and npm run build and npm run lint pass.
• id: RC1 · check: V035 is the only new migration, its version is free on main, and it matches the approved design document on the branch line for line: three columns on formula_phase_line, one foreign key to formula with an explicit ON DELETE RESTRICT, five named CHECKs each with its IS NOT NULL guard, and one index on premix_formula_id. Nothing beyond the document is added, renamed or dropped, and…
• id: RC2 · check: not_included is NOT NULL with DEFAULT FALSE so the ALTER cannot abort on populated rows, not_included_level is constrained to THIS_PREMIX and FINAL_FORMULA and only when the flag is set, and formula_premix_use keeps its columns, keys, unique pair and indexes untouched. An empty line with neither reference is still accepted.
• id: RC3 · check: The entity matches the migration (ddl-auto is validate): premixFormula as a LAZY association, notIncluded non-null defaulting to false, notIncludedLevel mapped STRING with the two-value enum, and the composition query fetch-joins the referenced formula so the mapper never lazy-loads.
• …and 17 more in the full plan.

Risks and dependencies

• The exact component names of the pre-mix tree and candidate records are not enumerable from here: the branch commits are not in the read-only checkout, so only the overlay excerpts of the mock's types were readable. A mismatch would force a component change at the flip. — Slices 6 and 7 require the DTO components to be read off the mock's PremixTree, PremixIngredientNode and PremixLinePrice types at the branch head and matched exactly; RC5 checks that no hook or component had to be adapted.
• V035 may be taken by another ticket's migration landing on main before this one is authored; the index is exact today but main moves. — Slice 1 re-checks the next free version against main before authoring and renumbers if needed; the file name is the only thing that changes.
• The line response changes shape for an endpoint already merged on main - kind removed and five components added - in one release, so any other consumer of that response would see the change. — The only consumer is the composition grid's own data layer, which is flipped in the same branch; responses are not schema-validated by the shared client, and the H2 and controller ITs cover the new shape.
• The approved design left seven decisions to the reviewer, and the gate was passed without written answers to them - notably whether a pre-mix line may be the QSP line, whether a duplicate keeps its pre-mix lines, and the level handling on an isPremix flip. — Each is implemented as the approved document states it (allowed, kept, and the stated flip behaviour), recorded in decisions D4 and D5 so a reviewer can overrule one as a contained change rather than a redesign.
• A pre-mix line's price is walked level by level at read time, which multiplies queries on deep nesting and could be slow on a composition with many pre-mix lines. — The new index on premix_formula_id supports the walk, the depth is capped at 5, and the per-site resolution is already a batch call; caching would be a later ticket, not a column.
• The design's open item on a pre-mix whose ingredient has no typed percentage, or which is over-filled, would be priced low rather than showing the dash, because the served figures carry no way to say refused. The ticket states no criterion for that case. — Kept as the accepted behaviour: the client verdict is unchanged and AC4's three stated cases are served by price, currency and unit. Recorded as decision D6 so the Fullstack Review can raise it as a product question.
• Removing the discriminator and extending the line contract in the same slices as the new rules could let a replacement discriminator slip in under another name, or let a renamed exclusion reason change AC4's wording. — RC4, RC13 and RC18 check for any replacement discriminator under backend/ and that the existing exclusion reasons and the cost calculation are added to, never renamed.
• Implement the approved DB design exactly as approved: no column, CHECK, index or foreign key beyond it, and no change to formula, formula_phase, formula_premix_use, rawmaterial, rawmaterial_batch or rawmaterial_site
• No line discriminator anywhere: kind leaves the response, the mapper constant and the test assertion, and no replacement field, flag or computed value takes its place
• Nothing derived is stored: no price, reason, tree, percentage, total, code, name or INCI column
• One composition service, one composition controller surface and one cost calculation; no parallel route or service for pre-mix lines
• Response records must match the shapes the branch's mock api returns, so the flip changes no hook and no component; read them at the branch head before authoring the DTOs
• Every new messageKey exists in both messages.properties and messages_fr.properties, and the depth, cycle and both-references wording is the preview's wording verbatim
• V035 is the next free version at the indexed base; confirm it against main before authoring the file
• The live raw-material candidate search and POST /v1/raw-materials/site-data are reused untouched
• No new npm or Gradle dependency
• Out of scope for this ticket: the all-levels-expanded view (MP-440), the finished product's combined INCI list, regulatory documents reading Not included, currency or unit conversion, and any change to how a formula is marked as a pre-mix

Expected result

Backend builder takes over the Backend stage from this plan.

Read the full plan