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

Selected

A1

Architecture

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

Rationale

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.

Design by area

Decided at the Mock stage and already delivered on this branch; no frontend change belongs to the DB stage. What this stage fixes for the frontend is only that nothing in the schema contradicts the approved shapes: name and comment are nullable so a cleared value round-trips as nothing, the code is unique case-insensitively across active and inactive groups so the existing field-level refusal keeps its meaning, the catalog's order is served by the LOWER(code) index so the paged read needs no client-side sort, and the member count is a COUNT rather than a stored number so it cannot disagree with the member list MP-703 will show.

The design document the db-designer writes must cover, in this order, and may not leave a row of it unstated.

1. Scope and inputs. The approved mock on this branch is the data contract (code, name, comment, active, member count, paged catalog over all groups: E14, E17, E18, E19) and the ticket's five rules are the invariants (E21). State explicitly that this document specifies the schema only and that the Flyway file is authored at the Backend stage.

2. The three tables, every column inline, exactly as the architecture section specifies: multisourcing_group (id UUID PK DEFAULT gen_random_uuid(); code VARCHAR(50) NOT NULL; name VARCHAR(150); comment VARCHAR(500); active BOOLEAN NOT NULL DEFAULT TRUE; the four audit columns, TIMESTAMPTZ and VARCHAR(100), nullable; CHECK (code = BTRIM(code) AND code <> '')), multisourcing_group_member (id; group_id NOT NULL FK ON DELETE RESTRICT; rawmaterial_id NOT NULL FK ON DELETE CASCADE; audit; UNIQUE (rawmaterial_id)) and multisourcing_group_composition (id; group_id NOT NULL FK ON DELETE CASCADE; substance_id NOT NULL FK ON DELETE RESTRICT; percentage NUMERIC(10,6) NOT NULL; audit; UNIQUE (group_id, substance_id); CHECK (percentage >= 0)). Each FK is named fk_<table>_<target> and states its ON DELETE; each unique constraint is named uk_<table>_<columns>.

3. Indexes, each with the query it serves: uq_multisourcing_group_code_lower ON multisourcing_group (LOWER(code)) for the duplicate-code refusal across active and inactive groups and for the catalog's case-insensitive order by code; idx_multisourcing_group_member_group ON (group_id) for the member count and the last-member check; idx_multisourcing_group_composition_substance ON (substance_id, percentage) for the candidate match. Anything else - an index on active, a trigram index for the catalog search - goes in a 'consider later' list with the reason it is not needed yet.

4. Mapping to the mock, field by field. A table that maps each field of the mock's catalog entry and identity request to its column: code -> multisourcing_group.code, name -> name (NULL when cleared), comment -> comment (NULL when cleared), active -> active, memberCount -> COUNT over multisourcing_group_member, the group's fingerprint entries -> multisourcing_group_composition rows (substanceId -> substance_id, percentage -> percentage NUMERIC(10,6)), and the membership itself -> one multisourcing_group_member row. State that nothing maps to an order or priority, because the ticket records none.

5. The identity rows' writer, once, in prose: the service inserts them with the group, from the founding material's ACTIVE composition (ingredient_composition and allergen_composition merged, one row per substance, every percentage present); nothing recomputes them; a later composition change of a member is the ticket's excluded interception and therefore a stated, accepted hole rather than an oversight.

6. The compatibility query, written out as illustrative SQL (the set-equality of Q3 in the architecture section), with a sentence on why the equality is on substance_id and never on a name.

7. Migration strategy. V035__Create_Multisourcing_Group_Tables.sql at the Backend stage: the three CREATE TABLEs, the three indexes, then DROP TABLE rawmaterial_multisourcing. V034 is the highest version at the base and main added no migration, so V035 is free; record that a migration landing on main before the Backend stage would move the number. Names stay unqualified because Flyway runs once per tenant schema, and note that the drop therefore runs per tenant. No backfill, no NOT NULL on existing rows, no new UNIQUE over existing data - the three tables are empty at creation; say so, since that is what makes the compatibility section short.

8. Entity mapping for the Backend stage: MultisourcingGroup, MultisourcingGroupMember, MultisourcingGroupComposition, each extending AuditableEntity with @GeneratedValue(GenerationType.UUID), @EntityListeners(SystemAwareAuditingEntityListener.class) and FK names identical to the migration's, following RawmaterialManufacturer. None of the three names, nor any field, may contain an AC9 identifier.

9. What the database does not enforce, and who does: the manufacturer precondition (rawmaterial_manufacturer), the active-composition and blank-percentage preconditions, the compatibility decision, the deactivation of an emptied group, and the field-level duplicate-code message - all service, per the invariant-placement table of backend/CLAUDE.md.

10. AC9 and the drop: the DROP statement necessarily names rawmaterial_multisourcing, so the document states that the identifier gate means no live code references it and that the removing migration is its one allowed occurrence. Without this sentence the Backend stage's own sweep looks like a gate failure.

11. Open items for DB Review, listed rather than guessed: whether the identity rows should be reconstructed when MP-703 or a later ticket intercepts a composition change, and whether the catalog search will need a trigram index once the catalog grows.

Decided at the Backend stage. What this stage fixes for it: the three tables and their constraints, the names of every table, column, FK, unique constraint and index, the NUMERIC(10,6) scale the compatibility comparison must use, the set-equality query shape for the join-candidate list, the fact that the member count is derived by COUNT and never stored, the single writer of the identity rows (group creation, in the same transaction, from the founding material's ACTIVE composition), and the sentence the AC9 gate needs about the DROP statement. Nothing about controllers, DTOs, mappers or services is decided here beyond where an invariant lives.

Alternatives considered

• A1 — Three new tables: multisourcing_group (code, name, comment, active, audit), multisourcing_group_member as a junction with UNIQUE (rawmaterial_id), and multisourcing_group_composition holding the group's own identity as one (substance_id, percentage) row per substance, written once at creation from the founding material's active composition. The legacy rawmaterial_multisourcing table is dropped in the same migration. Compatibility is a set-equality query over the identity rows; code uniqueness is a unique index on LOWER(code).

  Pros: Mirrors the ticket's own model: the group's identity is a composition, and it survives the group losing every member; Set equality over typed (substance_id, percentage NUMERIC(10,6)) rows is exact and indexable, with no opaque hash and no scale ambiguity; FK on substance keeps the identity referentially sound and auditable: one can always read what a group's composition is; Membership follows the house junction pattern (E7, E13), so no core table changes and MP-703 can hang member data off it later; No column records any ordering, which the ticket forbids. Cons: Three new tables rather than two, and the identity rows are derived data whose writer must be named (one write at creation, never recomputed); A member's INCI composition changing later makes the stored identity disagree with that member - the interception the ticket excludes.

• A2 — Two new tables only (group plus member junction); the group has no stored identity and compatibility is derived per request by joining every member's ACTIVE composition and comparing the substance/percentage sets.

  Pros: No derived data at all, so nothing can go stale; Smallest schema. Cons: A group with zero members has no identity, contradicting the ticket's rule that a group survives losing all its members and keeps the composition that defines it; The candidate query must unfold every member's composition on every open of the assign dialog, with no index that can serve the equality; The founding composition is unrecoverable once the last member leaves, so nothing records what the group ever was.

• A3 — Two new tables, with the group carrying a single denormalised fingerprint column (a canonical string or hash of the substance/percentage pairs) exactly as the mock store does, matched by equality.

  Pros: Cheapest candidate lookup: one equality on one indexed column; Closest to the mock's own record shape (E16). Cons: An opaque value: no one can read which substances define a group, and the backend/CLAUDE.md derivable-column smell applies with no reader able to verify it; The canonicalisation (ordering, trimming, the 6-decimal scale) becomes an undocumented contract that two independent writers could implement differently; Loses the substance FK, so a deleted or merged substance leaves a fingerprint nobody can resolve.

• A4 — Repurpose the legacy rawmaterial_multisourcing table by ALTER: rename or reuse its code column as the group code and add the group columns, instead of creating a group table.

  Pros: No new table for the group; Reuses a table that already references rawmaterial. Cons: Its row is one sourcing option of one raw material with a priority order (E1, E12); a group is a row shared by several materials, so the grain is wrong; AC9 requires the identifier rawmaterial_multisourcing to be gone from source, which keeping the table contradicts; Its unique key (raw_material_id, multisourcing_order) and NOT NULL order column would have to be dropped, and the ticket forbids recording an order at all.

API contract

No API contract in this plan.

Backend design

Decided at the Backend stage. What this stage fixes for it: the three tables and their constraints, the names of every table, column, FK, unique constraint and index, the NUMERIC(10,6) scale the compatibility comparison must use, the set-equality query shape for the join-candidate list, the fact that the member count is derived by COUNT and never stored, the single writer of the identity rows (group creation, in the same transaction, from the founding material's ACTIVE composition), and the sentence the AC9 gate needs about the DROP statement. Nothing about controllers, DTOs, mappers or services is decided here beyond where an invariant lives.

Backend changes

No file changes listed.

Data contract

multisourcing_group(id UUID PK, code VARCHAR(50) NOT NULL, name VARCHAR(150) NULL, comment VARCHAR(500) NULL, active BOOLEAN NOT NULL DEFAULT TRUE, created_date/modified_date TIMESTAMPTZ NULL, created_by/modified_by VARCHAR(100) NULL); unique on LOWER(code) across active and inactive rows; code trimmed and non-empty by CHECK. multisourcing_group_member(id UUID PK, group_id UUID NOT NULL -> multisourcing_group ON DELETE RESTRICT, rawmaterial_id UUID NOT NULL -> rawmaterial ON DELETE CASCADE, audit) with UNIQUE (rawmaterial_id) and no order column. multisourcing_group_composition(id UUID PK, group_id UUID NOT NULL -> multisourcing_group ON DELETE CASCADE, substance_id UUID NOT NULL -> substance ON DELETE RESTRICT, percentage NUMERIC(10,6) NOT NULL, audit) with UNIQUE (group_id, substance_id). Member count is derived by COUNT, never stored. Dropped: rawmaterial_multisourcing.

Database design

The design document the db-designer writes must cover, in this order, and may not leave a row of it unstated.

1. Scope and inputs. The approved mock on this branch is the data contract (code, name, comment, active, member count, paged catalog over all groups: E14, E17, E18, E19) and the ticket's five rules are the invariants (E21). State explicitly that this document specifies the schema only and that the Flyway file is authored at the Backend stage.

2. The three tables, every column inline, exactly as the architecture section specifies: multisourcing_group (id UUID PK DEFAULT gen_random_uuid(); code VARCHAR(50) NOT NULL; name VARCHAR(150); comment VARCHAR(500); active BOOLEAN NOT NULL DEFAULT TRUE; the four audit columns, TIMESTAMPTZ and VARCHAR(100), nullable; CHECK (code = BTRIM(code) AND code <> '')), multisourcing_group_member (id; group_id NOT NULL FK ON DELETE RESTRICT; rawmaterial_id NOT NULL FK ON DELETE CASCADE; audit; UNIQUE (rawmaterial_id)) and multisourcing_group_composition (id; group_id NOT NULL FK ON DELETE CASCADE; substance_id NOT NULL FK ON DELETE RESTRICT; percentage NUMERIC(10,6) NOT NULL; audit; UNIQUE (group_id, substance_id); CHECK (percentage >= 0)). Each FK is named fk_<table>_<target> and states its ON DELETE; each unique constraint is named uk_<table>_<columns>.

3. Indexes, each with the query it serves: uq_multisourcing_group_code_lower ON multisourcing_group (LOWER(code)) for the duplicate-code refusal across active and inactive groups and for the catalog's case-insensitive order by code; idx_multisourcing_group_member_group ON (group_id) for the member count and the last-member check; idx_multisourcing_group_composition_substance ON (substance_id, percentage) for the candidate match. Anything else - an index on active, a trigram index for the catalog search - goes in a 'consider later' list with the reason it is not needed yet.

4. Mapping to the mock, field by field. A table that maps each field of the mock's catalog entry and identity request to its column: code -> multisourcing_group.code, name -> name (NULL when cleared), comment -> comment (NULL when cleared), active -> active, memberCount -> COUNT over multisourcing_group_member, the group's fingerprint entries -> multisourcing_group_composition rows (substanceId -> substance_id, percentage -> percentage NUMERIC(10,6)), and the membership itself -> one multisourcing_group_member row. State that nothing maps to an order or priority, because the ticket records none.

5. The identity rows' writer, once, in prose: the service inserts them with the group, from the founding material's ACTIVE composition (ingredient_composition and allergen_composition merged, one row per substance, every percentage present); nothing recomputes them; a later composition change of a member is the ticket's excluded interception and therefore a stated, accepted hole rather than an oversight.

6. The compatibility query, written out as illustrative SQL (the set-equality of Q3 in the architecture section), with a sentence on why the equality is on substance_id and never on a name.

7. Migration strategy. V035__Create_Multisourcing_Group_Tables.sql at the Backend stage: the three CREATE TABLEs, the three indexes, then DROP TABLE rawmaterial_multisourcing. V034 is the highest version at the base and main added no migration, so V035 is free; record that a migration landing on main before the Backend stage would move the number. Names stay unqualified because Flyway runs once per tenant schema, and note that the drop therefore runs per tenant. No backfill, no NOT NULL on existing rows, no new UNIQUE over existing data - the three tables are empty at creation; say so, since that is what makes the compatibility section short.

8. Entity mapping for the Backend stage: MultisourcingGroup, MultisourcingGroupMember, MultisourcingGroupComposition, each extending AuditableEntity with @GeneratedValue(GenerationType.UUID), @EntityListeners(SystemAwareAuditingEntityListener.class) and FK names identical to the migration's, following RawmaterialManufacturer. None of the three names, nor any field, may contain an AC9 identifier.

9. What the database does not enforce, and who does: the manufacturer precondition (rawmaterial_manufacturer), the active-composition and blank-percentage preconditions, the compatibility decision, the deactivation of an emptied group, and the field-level duplicate-code message - all service, per the invariant-placement table of backend/CLAUDE.md.

10. AC9 and the drop: the DROP statement necessarily names rawmaterial_multisourcing, so the document states that the identifier gate means no live code references it and that the removing migration is its one allowed occurrence. Without this sentence the Backend stage's own sweep looks like a gate failure.

11. Open items for DB Review, listed rather than guessed: whether the identity rows should be reconstructed when MP-703 or a later ticket intercepts a composition change, and whether the catalog search will need a trigram index once the catalog grows.

Database changes

Files named by the steps

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

Components the review expects

• kind: create · path: docs/superpowers/specs/2026-10-14-mp-701-db-schema.md

Review checks

• 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'.
• id: RC12 · check: The document maps every field of the approved mock's catalog entry and identity request to a column (code, name, comment, active, member count, the identity entries) and shows that nothing in the mock requires a column the schema lacks - and nothing maps to an order.
• id: RC13 · check: The entity mapping is given field by field for the three entities (AuditableEntity, UUID generation, the auditing listener, FK names matching the migration), since ddl-auto is validate and the two must land together; no entity, field or table name contains an AC9 identifier.
• id: RC14 · check: The document states which rules stay in the service and why: the manufacturer precondition, the no-active-composition and blank-percentage refusals, the compatibility decision, the deactivation of an emptied group, and the field-level duplicate-code message.

Test files

No test files named.

Reuse to verify

• area: db · element: multisourcing_group, multisourcing_group_member, multisourcing_group_composition · action: create · why: Must be justified by the absence of any table holding a cross-material equivalence group: the legacy multisourcing table is a per-material code plus order and the only other group table is a substance classification. A fourth table, or a second home for a group's identity, w…
• area: db · element: rawmaterial_multisourcing table and its entity · action: remove · why: The dead stack's table is dropped in the same migration; it must not be repurposed, renamed or left standing, and the design must not re-introduce its priority order column.
• area: db · element: rawmaterial_composition, ingredient_composition, allergen_composition, rawmaterial_manufacturer · action: reuse · why: The preconditions and the composition the identity rule reads come from these tables unchanged; a copy of composition data, or a stored 'has manufacturer' flag, would be duplication.
• area: db · element: A nullable multisourcing_group_id column on rawmaterial · action: create · why: Rejected alternative A4/A1-variant: membership must not be a column on the core rawmaterial table; it is the junction with UNIQUE (rawmaterial_id), as the three sibling junction tables do.
• area: db · element: A denormalised composition fingerprint or hash column on the group · action: create · why: Rejected alternative A3: the identity is stored as typed substance/percentage rows with a substance FK, not as an opaque string whose canonicalisation two writers could implement differently.
• area: backend · element: Multisourcing group entities, repositories and endpoints · action: unknown · why: Decided at the Backend stage; this stage only fixes the columns, names and query shapes they must match.
• area: frontend · element: The delivered mock data layer and types · action: reuse · why: The approved mock is the data contract; the schema must serve it without asking for a frontend change.

Database designer takes over the Database stage from this plan.

RAG revision

9a774150b6f4

Catalog

89259674c242

Base SHA (RAG indexed)

5e62a467fa51

Feature base

5e62a467fa51

Branch head

e07342b83f73

Main

deb0851f0ad7

Main overlay

46d9b1be6a66

Branch overlay

a5f58ff1fdf9

Branch

claude/mock-MP-701-multisourcing-groups

PR

#132

Freshness

OVERLAY

RAG mode / profile

required

Stage binding

bb18c1b76e13

Context manifest

b50556c08711

Policy binding

cf104e3246a2

Writes

disabled

TicketSnapshot schema

ticket-snapshot/v2

TicketSnapshot hash

6bfae314e9f7

FeatureContract schema

feature-contract/v2.1

FeatureContract hash

46f9da7f4aa4

Contract

feature-contract/v2.1 · 46f9da7f4aa42d4bb12146f5298602202edc05f166a85bb94155c6315e576ec4

Aggregate

46f9da7f4aa42d4bb12146f5298602202edc05f166a85bb94155c6315e576ec4

RAG

RAG · OVERLAY

Cited evidence

Reuse and anti-duplication

Bound to

RAG revision

9a774150b6f4

Catalog

89259674c242

RAG indexed base

5e62a467fa51

Feature base

5e62a467fa51

Branch head

e07342b83f73

Main

deb0851f0ad7

Ticket snapshot

6bfae314e9f7

Contract sections

• architecture · 14913 B · 3f139e3b5034 · shown
• frontend-plan · 1583 B · 2ea64b06f473 · shown
• db-plan · 7482 B · 8f2fb1201249 · shown
• backend-plan · 2049 B · 56f739178db8 · shown
• evidence · 11431 B · b1a10c376b4b · shown
• review-plan · 6840 B · aa3e417eec13 · shown