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.

Implementation steps

1. 1The Multisourcing group schema design document

   Database designer

   • docs/superpowers/specs/2026-10-14-mp-701-db-schema.md

Open the full technical plan