Raw Materials - Multisourcing - Ticket 1 : dedicated tab and group management

Objective. Three new tables (A1): multisourcing_group with code, name, comment, active and audit columns and a unique index on LOWER(code), so a duplicate code is refused in any letter case across active and inactive groups; multisourcing_group_member as a junction with UNIQUE (rawmaterial_id) - the house way to say at most one group per raw material - and with no order column; and multisourcing_group_composition holding the group's identity as one (substance_id, percentage NUMERIC(10,6)) row per substance, written once at creation from the founding material's active composition so the identity survives the group losing every member. The legacy rawmaterial_multisourcing table is dropped in the same V035 migration. Reused unchanged: rawmaterial, the three composition tables, rawmaterial_manufacturer, the AuditableEntity audit columns and the junction pattern. Left out: a column on rawmaterial, a fingerprint hash column, any ordering column, the Flyway file and the entities (Backend stage), and every rule the service owns.

Approach. Target: three new tables in the cosmoreg-core schema plus the removal of the legacy stack's table. Illustrative DDL only - this stage produces a design document; the canonical Flyway file is authored at the Backend stage as V035__Create_Multisourcing_Group_Tables.sql (V034 is the highest version at the base and main added none, E9, E20), and entity and migration land together because ddl-auto is validate (E10). 1. multisourcing_group - the aggregate. id UUID PRIMARY KEY DEFAULT gen_random_uuid(); code VARCHAR(50) NOT NULL - the length is a named business rule (AC-E2: any text up to 50 characters), which is why it is not TEXT (E10); name VARCHAR(150), comment VARCHAR(500) - both nullable: AC-E3's blank values are stored as NULL, which is what the mock's write path does (E19); active BOOLEAN NOT NULL DEFAULT TRUE - declared inside the CREATE TABLE per the convention (E11); set FALSE when the last member leaves, and the row is never deleted (AC8); audit columns created_date/modified_date TIMESTAMPTZ and created_by/modified_by VARCHAR(100), nullable, supplied by AuditableEntity and SystemAwareAuditingEntityListener (E13); CONSTRAINT chk_multisourcing_group_code_trimmed CHECK (code = BTRIM(code) AND code <> '') - makes the whitespace-insensitivity of the uniqueness rule a property of the data rather than of one service method, and keeps the mandatory code from being blank; CREATE UNIQUE INDEX uq_multisourcing_group_code_lower ON multisourcing_group (LOWER(code)) - the AC-E2 and AC6 refusal, case-insensitive across active and inactive rows, and it also serves the catalog's ORDER BY LOWER(code) (the mock's sort=code,asc,ignorecase, E14). No second plain UNIQUE (code): the functional index subsumes it. An index on active is deliberately left to 'consider later' - the catalog does not filter on it and the candidate query is already narrowed by the identity rows. 2. multisourcing_group_member - membership, one row per member. id UUID PRIMARY KEY DEFAULT gen_random_uuid(); group_id UUID NOT NULL, CONSTRAINT fk_multisourcing_group_member_group FOREIGN KEY (group_id) REFERENCES multisourcing_group (id) ON DELETE RESTRICT - a group is never deleted, so a cascade would describe something that must not happen; rawmaterial_id UUID NOT NULL, CONSTRAINT fk_multisourcing_group_member_rawmaterial FOREIGN KEY (rawmaterial_id) REFERENCES rawmaterial (id) ON DELETE CASCADE - a deleted raw material has no membership; audit columns as above; CONSTRAINT uk_multisourcing_group_member_rawmaterial UNIQUE (rawmaterial_id) - this is the 'at most one group per raw material' rule, enforced by the database exactly as rawmaterial_manufacturer enforces its own (E7), and it serves the lookup 'which group does this material belong to'; CREATE INDEX idx_multisourcing_group_member_group ON multisourcing_group_member (group_id) - serves the member count shown on the identity card and in the catalog, and the 'did the last member just leave' check of AC8; No order, priority or rank column: the ticket states this ticket gives a group members, not a ranking, and that nothing here should record it; the sourcing priority arrives with MP-703. 3. multisourcing_group_composition - the group's identity, one row per substance. id UUID PRIMARY KEY DEFAULT gen_random_uuid(); group_id UUID NOT NULL, CONSTRAINT fk_multisourcing_group_composition_group FOREIGN KEY (group_id) REFERENCES multisourcing_group (id) ON DELETE CASCADE; substance_id UUID NOT NULL, CONSTRAINT fk_multisourcing_group_composition_substance FOREIGN KEY (substance_id) REFERENCES substance (id) ON DELETE RESTRICT; percentage NUMERIC(10,6) NOT NULL - the same type and scale as ingredient_composition.percentage_used and allergen_composition.percentage_used (E4, E5) and as the mock's 6-decimal rounding (E15), so two equal compositions always compare equal; NOT NULL is what makes the blank-percentage refusal structural (E6); audit columns as above; CONSTRAINT uk_multisourcing_group_composition_substance UNIQUE (group_id, substance_id) - one row per substance, which is sound because a substance cannot sit in both the ingredient and the allergen list of one composition (stated on the ticket, s17) and the rule merges the two lists (E15); CONSTRAINT chk_multisourcing_group_composition_percentage CHECK (percentage >= 0); CREATE INDEX idx_multisourcing_group_composition_substance ON multisourcing_group_composition (substance_id, percentage) - leading column first, serving the candidate query's lookup by (substance, percentage) pair; Writer, named once: the service inserts these rows in the same transaction as the group, from the founding material's ACTIVE composition (E3). Nothing else writes them and nothing recomputes them; interception of a later composition change is the ticket's excluded scope, and the design document says so where the derivable-data rule of backend/CLAUDE.md asks (E10). Named queries the indexes exist for. Q1 which group does this raw material belong to - multisourcing_group_member by rawmaterial_id (uk_...). Q2 the All groups catalog page - multisourcing_group ORDER BY LOWER(code) with limit and offset plus the total, and the member count per group from multisourcing_group_member (idx_..._group); the search narrows code and name (ILIKE); a trigram index is 'consider later', not core. Q3 join candidates (AC4) - the joiner's N identity pairs are matched against multisourcing_group_composition: the groups whose rows all match and whose own row count is also N, intersected with active = TRUE and with the absence of a member row for this material. The (substance_id, percentage) index serves the match, uk_..._substance guarantees the counts are comparable, and the equality is on the substance id, never on a name (E15). Q4 duplicate code (AC6, AC-E2) - uq_multisourcing_group_code_lower, plus a service pre-check so the user gets the field-level DUPLICATE_GROUP_CODE message rather than a raw constraint error (E14, and the invariant-placement table of E10). Q5 the last member left (AC8) - count members by group_id, then set active = FALSE; the row stays. Entities the Backend stage must land with the migration (ddl-auto validate, E10): MultisourcingGroup, MultisourcingGroupMember and MultisourcingGroupComposition, each extending AuditableEntity with @GeneratedValue(GenerationType.UUID) and SystemAwareAuditingEntityListener, FK joins named exactly as the migration names them (E13). None of the new names contains any AC9 identifier. Legacy sweep. DROP TABLE rawmaterial_multisourcing (no other table references it, E1) together with its entity, DTOs, mapper, repository, service and controller, in the same V035 migration and the same Backend-stage change (ticket s6; inherited Mock decision D7). The design document must state the one awkward consequence: that DROP statement necessarily spells the identifier AC9 greps for, so the gate has to read AC9 as 'no live code references it' with the removing migration as its single allowed occurrence. What the schema deliberately does not express, because the service owns it (E10's invariant table): the no-manufacturer refusal (read from rawmaterial_manufacturer, E7), the no-active-composition and blank-percentage refusals (read from rawmaterial_composition and its entry tables, E3, E6), the compatibility decision itself, and the choice to deactivate a group when its last member leaves. Multi-tenancy: names stay unqualified and no tenant column is added; Flyway runs the migration once per tenant schema, and the legacy drop therefore runs per tenant too (E9). — The evidence decides three things. First, nothing to reuse exists: the catalog's only multisourcing table is a per-material code plus priority order (E1) and the only other group table is a substance classification with neither code nor comment (E2), so group storage is net-new - which is also what the inherited Mock contract's db-plan concluded. Second, membership is a solved shape in this repository: rawmaterial_manufacturer expresses at most one per raw material as a junction with UNIQUE (rawmaterial_id), named FKs and an index (E7), mapped by an AuditableEntity with a UUID PK (E13), and rawmaterial_supplier and rawmaterial_internal_status repeat it; a nullable column on rawmaterial would be the one deviation from that pattern on the domain's busiest table, so A1 keeps the junction. Third, the group's identity must outlive its members: the ticket states that a group's identity is the composition its members share and that a group losing its last member becomes inactive and is never deleted (s5, restated on the branch in E21), which rules out A2, and the mock already stores that identity on the group record rather than deriving it (E16). Between stored rows (A1) and a stored fingerprint (A3) the rows win on auditability and exactness: the comparison the rule names is substance-by-substance at a 6-decimal scale (E15), which NUMERIC(10,6) columns express exactly as the composition tables already do (E4, E5), while a hash hides what a group is and loses the substance FK; backend/CLAUDE.md's derivable-column rule is satisfied by naming the single writer (creation) rather than by refusing to store it (E10). A4 is refused on grain and on AC9 (E1, E12). Group Code uniqueness is case-insensitive across active and inactive groups by AC-E2 and by the product owner's own test (reusing an emptied group's code in lower case must still be refused), which a unique index on LOWER(code) guarantees for every writer, not only for the service; functional and partial unique indexes are already house style (E9). No ordering column appears anywhere, because the ticket says this ticket gives a group members, not a ranking.

Scope

• User interface: None at this stage: no file under frontend/ is touched, and the delivered mock is treated as the contract the schema must serve rather than as something to revise; The schema keeps every field the mock exposes reachable without a UI change at the flip: code, name, comment, active, member count, and a page of all groups ordered by code ignoring case; Two mock-only constructs disappear at the flip, not here: the in-browser store and the eligibility arguments the mock api takes; the swap markers already enumerate those caller edits; One known frontend question stays open and is not schema work: whether an inactive group's identity card should show its status (raised on the ticket on 2026-10-11, awaiting the product owner); active is stored either way
• Database: Three new tables (multisourcing_group, multisourcing_group_member, multisourcing_group_composition) and one table dropped (rawmaterial_multisourcing); no existing table is altered, so no populated-table compatibility step is needed; One new design document on the ticket branch: docs/superpowers/specs/2026-10-14-mp-701-db-schema.md; no SQL and no Java is written at this stage; The Backend stage inherits a ready-to-author migration: V035__Create_Multisourcing_Group_Tables.sql, which also drops the legacy table, plus three entities whose fields must match it column for column (ddl-auto is validate); Existing readers are untouched: rawmaterial, rawmaterial_composition, ingredient_composition, allergen_composition, rawmaterial_manufacturer and every pricing table keep their shape, which is why leaving a group cannot affect pricing data (AC4); The only reader removed is the never-mounted legacy stack (entity, DTOs, mapper, repository, service, controller), swept with its table at the Backend stage
• API and backend: None at this stage: nothing under backend/src is written or changed, and no migration file is added; The Backend stage inherits the canonical migration to author, V035__Create_Multisourcing_Group_Tables.sql, with the three CREATE TABLEs, the three named indexes and the DROP of rawmaterial_multisourcing; It inherits three entities that must match that migration column for column because ddl-auto is validate: MultisourcingGroup, MultisourcingGroupMember, MultisourcingGroupComposition, each extending AuditableEntity with a UUID primary key; It inherits the rules the database cannot hold: the compatibility decision over the identity rows, the manufacturer and composition preconditions, the deactivation of an emptied group, and the field-level duplicate-code refusal that the unique index backs; It also owns the backend half of the dead stack - the legacy entity, its two request DTOs, its mapper, repository, service and controller - swept with its table in the same change; The endpoint shapes are already fixed by the approved mock's swap markers (a paged group list sorted by code ignoring case, and an identity update carrying code, name and comment); this stage adds no new endpoint requirement

Milestones

1. The Multisourcing group schema design document

Acceptance criteria

• The document covers the three tables with every column, constraint and index and the named query behind each, the mapping to the approved mock, the V035 strategy with the legacy drop and the AC9 sentence, the entity mapping and the open items; the DB critics are clean and the ticket is at DB Review.
• id: RC1 · check: The design document specifies all three tables with every column inline - multisourcing_group, multisourcing_group_member, multisourcing_group_composition - and no existing table is altered.
• id: RC2 · check: Group Code: VARCHAR(50) NOT NULL with the length justified as a business rule, a CHECK that it is trimmed and non-empty, and a unique index on LOWER(code) that holds across active and inactive groups - so reusing an emptied group's code in a different letter case is refused by the database, not only by the service.
• id: RC3 · check: Membership is a junction row with UNIQUE (rawmaterial_id), which is how at most one group per raw material is guaranteed, and it carries no order, priority or rank column of any kind.
• id: RC4 · check: The group's composition identity is stored as one (substance_id, percentage) row per substance with percentage NUMERIC(10,6) NOT NULL, unique per (group_id, substance_id), and the document says the comparison is on substance_id and never on any substance name.
• id: RC5 · check: The writer of the identity rows is named exactly once (group creation, same transaction, from the founding material's ACTIVE composition), nothing recomputes them, and the document states that a later composition change of a member is the ticket's excluded interception.
• id: RC6 · check: active BOOLEAN NOT NULL DEFAULT TRUE is declared inside the CREATE TABLE, and the document states that the last member leaving sets it FALSE while the row and its identity rows survive, and that nothing deletes a group.
• id: RC7 · check: name VARCHAR(150) and comment VARCHAR(500) are nullable, and the document says a cleared value is stored as NULL.
• id: RC8 · check: Every FK states an explicit ON DELETE with a reason (group RESTRICT because a group is never deleted; rawmaterial CASCADE; substance RESTRICT), and every constraint and index is named in the house form.
• id: RC9 · check: Each index traces to a query the document names - the duplicate-code refusal and the catalog's case-insensitive order, the member count and last-member check, the candidate match by (substance, percentage) - and speculative indexes sit in a 'consider later' list.
• id: RC10 · check: The migration strategy names V035__Create_Multisourcing_Group_Tables.sql as the next free version (V034 is the highest at the base, main added none), states that no backfill or populated-table step is needed because the tables are created empty, keeps names unqualified because Flyway runs once per tenant schema, and records that a migration landing on main first would move the nu…
• id: RC11 · check: The legacy sweep is specified: DROP TABLE rawmaterial_multisourcing in the same migration, nothing references it, and the document states that the DROP statement is the single allowed occurrence of that identifier so the AC9 gate reads as 'no live code references it'.
• …and 3 more in the full plan.

Risks and dependencies

• The migration that drops rawmaterial_multisourcing necessarily spells an AC9 identifier, so the Backend stage's own sweep can read as a gate failure. — The design document states that AC9 means no live code references the identifier and that the removing DROP statement is its single allowed occurrence; RC11 checks that sentence exists.
• The stored group identity disagrees with a member whose INCI composition is edited after the group was formed, leaving a group whose members are no longer interchangeable. — The ticket excludes interception of a composition change, so the design names the single writer (creation) and records the hole explicitly as an open item for DB Review rather than implying a refresh that nothing performs.
• V035 may be taken by another ticket landing a migration on main before this ticket's Backend stage runs, which Flyway would only surface later. — Main carries no migration since the index (E20) and V034 is the highest at the base (E9); the design records the version as next free at design time and asks the Backend stage to re-check it.
• Percentages compared at a different scale or rounding than the mock's would make two identical compositions look different and empty the candidate list. — percentage is NUMERIC(10,6), the exact type the composition tables use and the scale the mock rounds to (E4, E5, E15), and the design states the comparison is on that typed value.
• Flyway runs once per tenant schema, so the legacy drop and the three creations run per tenant and a tenant whose schema has drifted could fail the migration. — Names stay unqualified as V034 prescribes (E9), the three tables are new and empty, and the design notes the drop as the one statement touching an existing object.
• A reviewer could read the identity table as a second home for composition data, or ask for a member order now that a member table exists. — The design states that the rows are the group's declared identity, not a copy of any material's composition, and that no ordering column exists because the ticket gives a group members, not a ranking (MP-703 owns the priority).
• This stage produces one design document under docs/superpowers/specs/ on the ticket branch; it writes no SQL file and no Java
• Illustrative SQL only: the canonical Flyway migration is authored at the Backend stage, as V035 (V034 is the highest at the base, nothing landed on main)
• Nothing under frontend/ changes: the approved mock is the data contract the schema must serve, not something to revise
• No ordering, priority or rank column anywhere - the ticket forbids recording it in this ticket
• No new reader is broken: the three tables are new and the only existing object removed is the never-mounted legacy stack
• Group Code: mandatory, at most 50 characters, no imposed format, unique case- and whitespace-insensitively across active and inactive groups
• Name and Comment are nullable so that a cleared value is stored as NULL (AC-E3)
• Percentages are NUMERIC(10,6), the scale the composition tables and the mock both use
• Names must contain none of the AC9 identifiers; the only permitted occurrence of rawmaterial_multisourcing is the statement that drops it
• Every FK states ON DELETE, every index names its query, and the service keeps the contextual rules (manufacturer, composition, deactivation)

Expected result

Database designer takes over the Database stage from this plan.

Read the full plan