Plan · Formulation - Formulas - Operating procedures - Ticket 1 : free-text procedures (general, per phase, per line) and phase rename (frontend + db + backend)

Selected

A1

Architecture

Target: three nullable TEXT columns on three existing tables, one Flyway migration at the next free version, three entity fields, and no other schema object. This stage produces the design document only; the migration and the entities are authored at the Backend stage, which is the lifecycle of this repository (Planner, DB Designer, human DB Review, then the Backend Builder writes the canonical migration). 1. formula.operating_procedure - TEXT, nullable, no default. AC1's general procedure. It sits beside formula.notes rather than inside it: V029 deliberately split the formula's free text by lifetime and purpose, notes being the formulator's lab notebook (E4, E5). TEXT and not VARCHAR(n) because AC13 forbids a length limit and the repository reserves VARCHAR(n) for limits that are business rules. 2. formula_phase.operating_procedure - TEXT, nullable, no default. AC2's phase procedure, on the phase row. A rename writes only label, so the procedure survives it (AC6); the phase's cascade and the service's delete of an emptied phase destroy the procedure with the phase, which is precisely the loss AC8 warns about (E1). 3. formula_phase_line.instruction - TEXT, nullable, no default. AC3's line instruction, on the line row, so a reorder, a phase-cell change, a drag into another phase and a phase rename all leave it on its line with no extra mechanism (E2). Empty and absent: NULL means 'not set'. The mock's contract serves '' for a text that is not set (E19, E21), so the mapping is NULL on read becomes '', and a blank or whitespace-only write becomes NULL. The design document states this in one line so the Backend stage does not end up with two spellings of empty. No CHECK constraint is added: '' and NULL would mean the same thing and the service normalises. Keys: nothing keys on the phase label. The mock keys the texts by phase id and line id (E20, E22, E23) and these columns sit on those very rows, so the key is each table's primary key and the Mock contract's phaseId/lineId decision is honoured by construction. Phase rename (AC6, AC7) - no schema change. formula_phase.label is already the stored name, at most 255 characters and trimmed by the service (E1, E17), and uq_formula_phase_formula_label on (formula_id, label) is already AC7's 'a name already used by another phase of the same formula', with the comparison the server applies today. A rename is a label update on one row that keeps id and sort_order. Renaming the 'No phase' group is the same update on the row whose label is '', and a collision is refused by the same constraint. The design document must say explicitly that no new uniqueness rule, no case-insensitive index and no label history table is added. History (AC11) - no schema change. formula_audit_event is append-only, cascades with its formula and already carries attribute with previous_value and new_value as TEXT (E6); ATTRIBUTE_MODIFIED is already seeded (E7); recordAttributeModified already writes a field key with resolved display strings (E8). AC11 asks for exactly 'how attribute changes are recorded today', so the Backend stage raises ATTRIBUTE_MODIFIED events with attribute keys for the general procedure, the procedure of a named phase, the instruction of the line holding a given raw material, and a phase rename. No new formula_audit_event_type row is seeded, so V035 carries no data step. Duplication (AC12) - no schema change, but two existing statements must learn the new columns. copyPhases and copyLines are native INSERT ... SELECT statements with explicit column lists (E9, E10): a new column is silently not copied until it is listed there, which is the one way this design can fail quietly. The general procedure is on the formula row, which copyComposition never touches (E11), so it is copied by the formula creation path instead. Both facts belong in the design document as impact on the Backend stage, with the note that the lot and the batch stay deliberately uncopied. Lock (AC9) - no schema change. Every procedure write goes through the existing guard that refuses a composition write when the formula's status row locks the composition (E12). Migration: one Flyway file, V035, three ADD COLUMN statements, one per table. V034 is the highest version at the base, the branch adds no migration and main has not moved since the index, so there is no version collision (E18). Each column is nullable with no default, which is a metadata-only change in PostgreSQL and cannot abort on populated tables - the compatibility reasoning V029 already wrote down for this repository's preview databases restored from production-shaped data (E5). No backfill, no narrowing, no new UNIQUE and no NOT NULL, so no existing row can violate anything and the migration is reversible by dropping three columns. Entities: ddl-auto is validate, so the entity field and the column must land together at the Backend stage - one String field on Formula, on FormulaPhase and on FormulaPhaseLine, each mapped to its column with no length attribute (E15, E16, E17). The design document gives the column-to-field mapping so that pairing is unambiguous. Indexes: none. A procedure text is never filtered, joined or sorted on; it is read only as part of a composition read, which is already served by idx_formula_phase_line_formula_id and by the leading column of uq_formula_phase_formula_label (E1, E3). A speculative index belongs in the document's 'consider later' section, not in the design. Readers: nothing existing changes, so every current entity, DTO, specification and frontend type is unaffected - an added nullable column is invisible until the Backend stage maps it. The frontend needs no change from this stage: the mock's five signatures stay the contract and the flip replaces their bodies at the Backend stage. Deliverable: one design document on the ticket branch, docs/superpowers/specs/2026-10-17-MP-723-operating-procedures-db-schema.md, covering the three columns with types and nullability, the illustrative DDL, the column-to-field and column-to-mock-field mappings, the four criteria that need no schema change (AC6, AC7, AC9, AC11), the AC12 copy-statement impact, the compatibility reasoning, and the open questions it leaves to the Backend stage.

Rationale

The code decides this one. Each of the three texts describes exactly one existing row, and that row's lifecycle is already the behaviour the acceptance criteria ask for: a phase is a row with a stored label that a rename updates in place (E1, E17), so AC6's 'the phase keeps its position and its procedure' is free; the phase row cascades to its lines and is deleted once its last line leaves (E1, E2), so AC8's 'its procedure will be deleted with it' is the row disappearing rather than a cleanup rule; and a line row keeps its identity through reorders, phase changes and drags (E2, E22), so AC3's 'the instruction stays on its line' needs no work at all. A2 and A3 would have to re-create each of those three guarantees with foreign keys, an exclusive-arc CHECK and extra copy statements, and would add one or two queries to every composition read, which is already indexed to fetch a whole composition in one pass (E3). The DB guidance's own order - reuse, then extend a column, then create a table - points the same way, and no second home for the concept is created. TEXT with no length is settled by AC13 and by the repository's rule that VARCHAR(n) is for a real business limit, with formula.notes as the live precedent; the general procedure is a separate column from notes because V029 split the formula's free text by purpose on purpose, and folding manufacturing instructions back into the lab notebook would undo that (E4, E5). Two criteria need no schema at all and are recorded so the designer does not invent any: AC7's 'already used' is exactly uq_formula_phase_formula_label on trimmed labels (E1), and AC11 is the existing append-only formula_audit_event with the already-seeded ATTRIBUTE_MODIFIED type and the recordAttributeModified mechanism the ticket names (E6, E7, E8) - which keeps V035 pure DDL with no reference-data seed. The one place where 'just add a column' is not enough is AC12: copyPhases and copyLines enumerate their column lists and copyComposition never touches the formula row (E9, E10, E11), so the design document must say both that the two statements gain the new columns and that the general procedure is copied by the formula creation path. A2's only real advantage is extensibility towards MP-725, and MP-725 is explicitly out of this ticket and blocked by it, so paying its cost now is speculative.

Design by area

Decided at the Backend stage (the mock-to-real flip). What this stage fixes for it: the three texts are stored on the formula, phase and line rows themselves, so the client's existing keying by phase id and line id maps one-to-one onto primary keys, and a procedure keeps following its phase through a rename and its line through a reorder exactly as the mock already behaves. A text that is not set is NULL in the database and '' in the API, which is the value the mock already renders as 'Not set', so no component changes. Texts stay unbounded, so no character cap appears in the editors (AC13).

Three columns, one per table that already owns the row the text describes. Full detail, with the reasoning, is in the architecture section's decision.architecture; this is the layer's specification.

Columns
1. formula.operating_procedure - TEXT, NULL allowed, no default. The formula's general procedure (AC1). A separate column from formula.notes, which V029 established as the lab notebook (E4, E5).
2. formula_phase.operating_procedure - TEXT, NULL allowed, no default. One named phase's procedure (AC2). On the phase row, so a rename keeps it (AC6) and the phase's disappearance destroys it (AC8).
3. formula_phase_line.instruction - TEXT, NULL allowed, no default. One composition line's instruction (AC3). On the line row, so it follows the line through reorders, phase changes and drags.

Types and nullability. TEXT because AC13 forbids a length limit and the repository reserves VARCHAR(n) for limits that are real business rules, with formula.notes as the precedent (E4, E5). NULL means 'not set'; the mock serves '' for the same state (E19, E21), so the mapper reads NULL as '' and writes a blank as NULL. No CHECK and no default: '' and NULL would carry one meaning and the service normalises.

Constraints. None added. The three columns are free text with no integrity the database can assert. The existing constraints already do the work the criteria need: fk_formula_phase_formula ON DELETE CASCADE and the service's delete of an emptied phase take the phase procedure with its phase (AC8), the composite FK from formula_phase_line cascades the line's instruction, and uq_formula_phase_formula_label on (formula_id, label) is AC7's 'already used' rule on trimmed labels (E1, E2).

Indexes. None. A procedure text is never filtered, joined or sorted on, and it is read only as part of a composition read that idx_formula_phase_line_formula_id and the leading column of uq_formula_phase_formula_label already serve (E1, E3). Any index on these columns belongs in the document's 'consider later' section.

Migration strategy. One file, V035, next free version: V034 is the highest at the base, the branch adds no migration and main has not moved since the index, so no collision is pending (E18). Three ADD COLUMN statements, each nullable with no default - metadata-only in PostgreSQL, so it cannot abort on populated tables, which matters because preview databases are restored with production-shaped data (E5). No backfill, no narrowing, no new UNIQUE, no NOT NULL; rolling back is dropping three columns. The file itself is written by the Backend Builder after a human approves this design, so no db/migration path is touched by this stage.

Entity mapping (ddl-auto validate, landing with the migration at the Backend stage): one String field on Formula, on FormulaPhase and on FormulaPhaseLine, each mapped to its column with no length attribute (E15, E16, E17). The document carries the column-to-field table so the pairing cannot drift.

Compatibility. Nothing existing changes, so no current reader breaks: an added nullable column is invisible to every entity, DTO, specification and frontend type until the Backend stage maps it. Existing rows read NULL, which the API serves as '' - the same 'Not set' the mock already renders.

What the document must also record, because the schema alone does not deliver it: the two enumerated copy statements must list the new phase and line columns or AC12 fails silently, and the general procedure is copied by the formula creation path since copyComposition never touches the formula row (E9, E10, E11); AC11 maps onto the existing append-only audit event with the seeded ATTRIBUTE_MODIFIED type and needs no new event type (E6, E7, E8); AC9's refusal is the existing composition lock guard (E12).

Decided at the Backend stage. What this stage fixes for it: the storage shape (three nullable TEXT columns - formula.operating_procedure, formula_phase.operating_procedure, formula_phase_line.instruction), the NULL-means-not-set convention that the API serves as '', the column-to-entity mapping that must land with the migration, and the four criteria that need no schema change (AC6 and AC7 on the existing label and its per-formula unique constraint, AC9 on the existing lock guard, AC11 on the existing attribute-modified audit event). It also fixes two things the Backend stage must not discover late: the duplication copy statements enumerate their columns, so the new ones must be added there for AC12, and the general procedure is copied by the formula creation path rather than by copyComposition. The endpoint contract itself is already pinned by the approved mock's signatures on the branch.

Alternatives considered

• A1 — Extend the three tables that already own the rows the texts describe: one nullable TEXT column each on formula (general procedure), formula_phase (phase procedure) and formula_phase_line (line instruction). No new table, no new index, no new reference data, no change to an existing column. The phase rename reuses formula_phase.label and its existing per-formula unique constraint, History reuses formula_audit_event with the seeded ATTRIBUTE_MODIFIED type, and the lock reuses the existing composition guard. One Flyway file at the next free version V035 with three ADD COLUMN statements, authored …

  Pros: Smallest correct change: three columns instead of a new table, which is the repository's stated preference for a value that belongs to an existing row; AC6 comes free: a rename writes only label, so the phase keeps its id, its position and its procedure; AC8 comes free: the phase row's cascade and the emptied-phase delete already destroy the procedure with its phase; AC3 comes free: an instruction on the line row follows that line through reorders, phase changes, drags and renames; No extra read cost: the composition read already fetches the phases and lines of a formula, so the texts arrive with it and need no index; AC12 is two extra column references in statements that already exist. Cons: Three ALTERs across three tables rather than one new object; A long text is loaded with every composition read, since a column cannot be fetched lazily here; MP-725's structured steps will still need their own table later, so the free-text layer stays separate from it.

• A2 — Create one polymorphic table, formula_operating_procedure, holding every procedure as a row: id, formula_id, a nullable phase_id, a nullable line_id, a scope discriminator and the text. The three composition tables stay untouched and the procedures are read with one or two extra queries per composition.

  Pros: One object owns every procedure, so the concept has a single home; Room to grow into MP-725's structured steps without touching the composition tables; A text can be attached or detached without writing to a phase or line row. Cons: Needs a three-way exclusive-arc CHECK plus three foreign keys, each with its own ON DELETE, to re-create the lifecycle the parent rows already give for free; Adds one or two queries (or joins) to every composition read, and a new index to serve them; Duplication becomes a third copy statement that must remap both the new phase ids and the new line ids, which the label join cannot do for lines; The scope discriminator is a new value set the code alone fixes and no admin manages, so it is an enum the repository would rather not add; Stores one data column per row in a table whose key is harder to get right than the column it replaces.

• A3 — Create two 1:1 child tables, one for the phase procedure and one for the line instruction, each with a unique foreign key to its parent, and put the general procedure on the formula row.

  Pros: Keeps the phase and line rows narrow; A text is read only when it is asked for. Cons: Two new tables for two nullable texts with no lifecycle of their own - the 1:1 split the DB guidance calls duplication; Three reads instead of two for a composition, each needing its own index; AC12 needs two further copy statements, each remapping parent ids; Every reader of a phase or line that wants its text now needs a join.

API contract

No API contract in this plan.

Backend design

Decided at the Backend stage. What this stage fixes for it: the storage shape (three nullable TEXT columns - formula.operating_procedure, formula_phase.operating_procedure, formula_phase_line.instruction), the NULL-means-not-set convention that the API serves as '', the column-to-entity mapping that must land with the migration, and the four criteria that need no schema change (AC6 and AC7 on the existing label and its per-formula unique constraint, AC9 on the existing lock guard, AC11 on the existing attribute-modified audit event). It also fixes two things the Backend stage must not discover late: the duplication copy statements enumerate their columns, so the new ones must be added there for AC12, and the general procedure is copied by the formula creation path rather than by copyComposition. The endpoint contract itself is already pinned by the approved mock's signatures on the branch.

Backend changes

No file changes listed.

Data contract

Each field of the mock's procedures shape maps to exactly one column. general -> formula.operating_procedure. byPhaseId[phaseId] -> formula_phase.operating_procedure of the row with that id. byLineId[lineId] -> formula_phase_line.instruction of the row with that id. formulaId is the formula row's id and is not stored again. The phase label a rename writes stays formula_phase.label, unchanged in type and constraint. Texts are unbounded; NULL in the database is '' in the API, and a blank write is stored as NULL. Ids are UUIDs, which the mock already treats as UUID strings.

Database design

Three columns, one per table that already owns the row the text describes. Full detail, with the reasoning, is in the architecture section's decision.architecture; this is the layer's specification.

Columns
1. formula.operating_procedure - TEXT, NULL allowed, no default. The formula's general procedure (AC1). A separate column from formula.notes, which V029 established as the lab notebook (E4, E5).
2. formula_phase.operating_procedure - TEXT, NULL allowed, no default. One named phase's procedure (AC2). On the phase row, so a rename keeps it (AC6) and the phase's disappearance destroys it (AC8).
3. formula_phase_line.instruction - TEXT, NULL allowed, no default. One composition line's instruction (AC3). On the line row, so it follows the line through reorders, phase changes and drags.

Types and nullability. TEXT because AC13 forbids a length limit and the repository reserves VARCHAR(n) for limits that are real business rules, with formula.notes as the precedent (E4, E5). NULL means 'not set'; the mock serves '' for the same state (E19, E21), so the mapper reads NULL as '' and writes a blank as NULL. No CHECK and no default: '' and NULL would carry one meaning and the service normalises.

Constraints. None added. The three columns are free text with no integrity the database can assert. The existing constraints already do the work the criteria need: fk_formula_phase_formula ON DELETE CASCADE and the service's delete of an emptied phase take the phase procedure with its phase (AC8), the composite FK from formula_phase_line cascades the line's instruction, and uq_formula_phase_formula_label on (formula_id, label) is AC7's 'already used' rule on trimmed labels (E1, E2).

Indexes. None. A procedure text is never filtered, joined or sorted on, and it is read only as part of a composition read that idx_formula_phase_line_formula_id and the leading column of uq_formula_phase_formula_label already serve (E1, E3). Any index on these columns belongs in the document's 'consider later' section.

Migration strategy. One file, V035, next free version: V034 is the highest at the base, the branch adds no migration and main has not moved since the index, so no collision is pending (E18). Three ADD COLUMN statements, each nullable with no default - metadata-only in PostgreSQL, so it cannot abort on populated tables, which matters because preview databases are restored with production-shaped data (E5). No backfill, no narrowing, no new UNIQUE, no NOT NULL; rolling back is dropping three columns. The file itself is written by the Backend Builder after a human approves this design, so no db/migration path is touched by this stage.

Entity mapping (ddl-auto validate, landing with the migration at the Backend stage): one String field on Formula, on FormulaPhase and on FormulaPhaseLine, each mapped to its column with no length attribute (E15, E16, E17). The document carries the column-to-field table so the pairing cannot drift.

Compatibility. Nothing existing changes, so no current reader breaks: an added nullable column is invisible to every entity, DTO, specification and frontend type until the Backend stage maps it. Existing rows read NULL, which the API serves as '' - the same 'Not set' the mock already renders.

What the document must also record, because the schema alone does not deliver it: the two enumerated copy statements must list the new phase and line columns or AC12 fails silently, and the general procedure is copied by the formula creation path since copyComposition never touches the formula row (E9, E10, E11); AC11 maps onto the existing append-only audit event with the seeded ATTRIBUTE_MODIFIED type and needs no new event type (E6, E7, E8); AC9's refusal is the existing composition lock guard (E12).

Database changes

Files named by the steps

• docs/superpowers/specs/2026-10-17-MP-723-operating-procedures-db-schema.md

Components the review expects

• kind: create · path: docs/superpowers/specs/2026-10-17-MP-723-operating-procedures-db-schema.md

Review checks

• id: RC1 · check: The design places the general procedure on the formula row, the phase procedure on the formula_phase row and the line instruction on the formula_phase_line row, as one nullable column each, and introduces no new table, junction or child table for them.
• id: RC2 · check: All three columns are TEXT with no length limit anywhere in the document, and the document states the reason (no cap per the criterion, matching the formula's notes field).
• id: RC3 · check: The document states the empty-value convention once and unambiguously: NULL means not set, the API serves an empty string, a blank write is stored as NULL, and no CHECK or default is added.
• id: RC4 · check: Every field of the approved mock's procedures shape maps to exactly one column, with nothing unstored and nothing invented: the general text, the per-phase text keyed by phase id and the per-line text keyed by line id.
• id: RC5 · check: The document shows the phase rename needs no schema change: the existing label column is the stored name, and the existing per-formula unique constraint on (formula_id, label) is the already-used refusal rule on trimmed labels. No new uniqueness rule, case-insensitive index or label-history table is added.
• id: RC6 · check: The document states that a phase procedure disappears with its phase row - by the existing cascade and the service's delete of an emptied phase - and that no orphan copy or soft delete is kept, which is what the user is warned about.
• id: RC7 · check: The migration is named at the next free version V035, with three ADD COLUMN statements, each nullable with no default, no backfill and no new NOT NULL or UNIQUE, and the document gives the populated-table reasoning. No file under db/migration is created at this stage.
• id: RC8 · check: The document justifies no new index on the three columns by naming the composition read that already serves them, and keeps any speculative index in a consider-later section.
• id: RC9 · check: The document tells the Backend stage that the two enumerated duplication copy statements must list the new phase and line columns, and that the general procedure is copied by the formula creation path because the composition copy never touches the formula row.
• id: RC10 · check: The document maps the History entries onto the existing append-only audit event table with the already-seeded attribute-modified type, and seeds no new event-type row, so the migration carries no data step.
• id: RC11 · check: The document records the column-to-entity-field mapping for all three tables and states that field and column must land together because ddl-auto is validate.
• id: RC12 · check: The document states that procedure writes are refused on a locked composition through the existing composition lock guard, with no new flag or column for the lock.
• id: RC13 · check: No frontend file is changed by this stage and the document implies no change to the mock's shapes: the approved mock stays the contract and only the api bodies are replaced at the flip.
• id: RC14 · check: The only file added by this stage is the design document on the ticket branch; nothing under backend/, db/migration or frontend/ is touched.

Test files

No test files named.

Reuse to verify

• area: db · element: formula_operating_procedure (one polymorphic procedures table) · action: create · why: Rejected alternative A2: it must not appear in the design. It would re-create with foreign keys and an exclusive-arc CHECK the lifecycle the parent rows already give, add queries to every composition read and need a third copy statement remapping phase and line ids.
• area: db · element: formula_phase_procedure / formula_phase_line_instruction (1:1 child tables) · action: create · why: Rejected alternative A3: two new tables for two nullable texts with no lifecycle of their own is the 1:1 split the DB guidance calls duplication.
• area: db · element: formula.notes · action: reuse · why: Unchanged and not reused as storage: V029 split the formula's free text by purpose, so the general procedure is its own column and must not be folded back into the lab notebook.
• area: db · element: uq_formula_phase_formula_label on (formula_id, label) · action: reuse · why: It already is the rename refusal rule; a second uniqueness rule or a case-insensitive variant would duplicate it and change today's comparison.
• area: db · element: formula_audit_event_type seeded codes · action: reuse · why: The attribute-modified code already exists; seeding a new procedure event type would duplicate the vocabulary and add a data step to the migration.
• area: backend · element: FormulaPhaseRepository.copyPhases and FormulaPhaseLineRepository.copyLines · action: extend · why: The only silent failure in this design: both statements enumerate their columns, so duplication drops the new texts until they are listed there.
• area: frontend · element: api/formula-procedures.ts mock signatures · action: reuse · why: They stay the API contract the schema serves; the design must not imply a renamed or restructured field.

Database designer takes over the Database stage from this plan.

RAG revision

6ff9aee4994f

Catalog

ead4cae607ae

Base SHA (RAG indexed)

3830b223cba4

Feature base

3830b223cba4

Branch head

ed0ae3200407

Main

3830b223cba4

Main overlay

none

Branch overlay

ffcdf347abc8

Branch

claude/mock-MP-723-operating-procedures

PR

#151

Freshness

EXACT

RAG mode / profile

required

Stage binding

2182ae3e1ae0

Context manifest

5f1e7e61490f

Policy binding

85afed9311b4

Writes

disabled

TicketSnapshot schema

ticket-snapshot/v2

TicketSnapshot hash

d551b207e9ee

TicketSnapshot size

18059 B

TicketSnapshot fetched

2026-10-17T09:52:57Z

FeatureContract schema

feature-contract/v2.1

FeatureContract hash

95b63d982b03

Contract

feature-contract/v2.1 · 95b63d982b03406e2d1068f5516b0a398c644e5ab53e4c8464a720e01a07c4b0

Aggregate

95b63d982b03406e2d1068f5516b0a398c644e5ab53e4c8464a720e01a07c4b0

RAG

RAG · EXACT

Cited evidence

Reuse and anti-duplication

Bound to

RAG revision

6ff9aee4994f

Catalog

ead4cae607ae

RAG indexed base

3830b223cba4

Feature base

3830b223cba4

Branch head

ed0ae3200407

Main

3830b223cba4

Ticket snapshot

d551b207e9ee

Contract sections

• architecture · 13342 B · dd185901a1e9 · shown
• frontend-plan · 1342 B · 7bdb3ccdf81c · shown
• db-plan · 5841 B · 00a4bddf9b92 · shown
• backend-plan · 2268 B · 778303071ea8 · shown
• evidence · 13520 B · ed92f494ba40 · shown
• review-plan · 6117 B · ac87165994b9 · shown