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.

Scope

• User interface: None at this stage: no frontend file changes, and the approved mock on the branch stays exactly as the human DB Review finds it; The mock's procedures shape is the data contract this schema serves; every one of its fields maps to one column, so no shape change is implied; At the Backend stage's flip, only the mock api bodies and the mock store are replaced - the hook, the components and the types keep their contract; One behaviour becomes real with the schema: the texts survive a reload, which the mock cannot do; nothing else the user sees changes
• Database: Three new nullable TEXT columns: formula.operating_procedure (AC1), formula_phase.operating_procedure (AC2), formula_phase_line.instruction (AC3); One Flyway file planned at the next free version V035 with three ADD COLUMN statements - authored by the Backend Builder after DB Review, not at this stage; No new table, no new index, no new foreign key, no new enum and no reference-data seed; no existing column, constraint or index is changed; Four criteria need no schema change and the document says so explicitly: the phase rename and its refusal rule (AC6, AC7), the lock (AC9) and the History entries (AC11); One entity field per table to land with the migration at the Backend stage, since ddl-auto is validate; The only artefact of this stage is the design document on the ticket branch
• API and backend: Expected at the Backend stage: the Flyway file V035 with the three ADD COLUMN statements, plus one entity field each on Formula, FormulaPhase and FormulaPhaseLine (ddl-auto is validate, so they land together); Five endpoints the mock already fixes: read the formula's procedures, write the general procedure, write a phase's procedure, write a line's instruction, patch a phase label; The phase rename must be a phase-level write that keeps the phase row, its sort_order and its procedure - not a loop of per-line relabels, which would move and delete the phase; copyPhases and copyLines must list the two new composition columns or AC12 fails silently; the general procedure is copied by the formula creation path, since copyComposition never touches the formula row; AC11 reuses formula_audit_event with the seeded ATTRIBUTE_MODIFIED type and the existing recordAttributeModified mechanism - no new event type row; Every procedure write reuses the existing composition lock refusal (AC9), and AC7's refusal reuses the existing per-formula label uniqueness; Backend verification gates (spotlessCheck, test) apply at that stage; nothing under backend/ changes at this stage

Milestones

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

Acceptance criteria

• The document covers the three columns with types and nullability, the mappings to the mock's fields and to the entity fields, the criteria needing no schema change, the AC12 copy-statement impact, the V035 version and the compatibility reasoning; four DB critics clean; ticket at DB Review.
• 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.
• …and 3 more in the full plan.

Risks and dependencies

• AC12 can fail silently: the duplication copy statements enumerate their columns, so a new column is simply not copied and a duplicate loses its procedures with no error. — The design document names both statements and the formula creation path as required changes, a review check (RC9) targets it, and the Backend stage covers a duplicate with a test.
• ddl-auto is validate, so a migration landing without its entity field - or the reverse - fails the application at startup rather than at review. — The document carries the column-to-field mapping for all three entities and states that field and column land together in the Backend stage's change (RC11).
• A phase procedure is lost whenever its phase loses its last line, because the service deletes the emptied phase row and the cascade takes the text with it. — This is the product answer the ticket chose: AC8's confirmation warns the user first. The document states the behaviour explicitly so it is deliberate, not discovered later.
• Preview databases are restored with production-shaped data, so a careless NOT NULL or a backfill on these three columns could abort the migration. — Every column is nullable with no default and no backfill, which is metadata-only in PostgreSQL - the reasoning V029 already recorded for this repository (RC7).
• MP-725's structured steps may later want a table of their own, and a reader could read these three columns as the place to put them. — The document scopes the columns as the free-text layer of this ticket and leaves structured steps to MP-725, which is explicitly excluded and blocked by this ticket.
• An ADVISORY review observation is still open on the branch: inside the procedure and instruction areas, the shared editor footer's Cancel and Save stay right-aligned in a full-width grid row and can sit off-screen. — Not DB work and not a regression of this delta: the reviewer's verdict was CLEAN, it is routed as a recommendation and the ledger lists no open requirement. Carried for the next frontend touch.
• Design document only: no Flyway file, no entity, no backend or frontend code is written at this stage, and nothing under backend/ or db/migration changes
• The approved mock on the branch is the data contract: every field of its procedures shape must map to exactly one column, and no field may be added or renamed here
• No new table, no new reference-data table and no Java enum: the three texts extend existing rows
• No length limit on any of the three columns (AC13); TEXT, never VARCHAR(n)
• No new uniqueness rule for the phase label: uq_formula_phase_formula_label is AC7's rule as it stands
• No new formula_audit_event_type row: AC11 reuses the seeded ATTRIBUTE_MODIFIED type, so the migration stays pure DDL
• Next free Flyway version is V035; the Backend Builder authors that file after a human approves the design
• Every column is added nullable with no default and no backfill, so the migration is metadata-only on populated tables

Expected result

Database designer takes over the Database stage from this plan.

Read the full plan