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

Objective. A1: extend the three tables that already own the rows the texts describe - one nullable TEXT column each on formula (AC1), formula_phase (AC2) and formula_phase_line (AC3). No new table, no new index, no reference-data seed, no change to an existing column. Four criteria need no schema at all: the rename and its refusal reuse formula_phase.label and its per-formula unique constraint (AC6, AC7), the lock reuses the existing composition guard (AC9), and History reuses the append-only audit event with the seeded ATTRIBUTE_MODIFIED type (AC11). AC12 needs no schema change but does need the two enumerated duplication copy statements extended, and the general procedure copied by the formula creation path - the document says both. One Flyway file planned at the next free version V035: three ADD COLUMN statements, nullable with no default, metadata-only on populated tables. Rejected: a polymorphic procedures table (A2) and 1:1 child tables (A3). This stage delivers the design document only; the migration, entity fields and endpoints belong to the Backend stage.

Approach. 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. — 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.

Implementation steps

1. 1DB design document for the three operating-procedure columns

   Database designer

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

Open the full technical plan