Overview Functional design Technical design Data design Projection engine System design Open questions

A conversion is persisted as two rows (FROM side + TO side) that share a conversion_id UUID. Both rows carry signed integer delta_cases equal in absolute value. New enum item_conversion_role. New tables app_data.unsaved_item_conversions (per-user, versioned) and app_data.saved_item_conversions (per-timeframe), plus a latest_unsaved_item_conversions view mirroring the existing pattern. New table item_relationships.supc_projected_opco_metrics holds per-opco projected metrics for TO items missing from master. New nullable column cross_purchase_ratio on item_relationships.item_pair_relationships. All in one Liquibase changelog: 020-item-conversion-tables.xml.

The conversion design slots between three existing schemas without modifying any of them. This section quotes just enough of the live schema (verified on main) to make the new design self-contained.

PK: (timeframe_id, itm_nbr)

Per-timeframe item registry. Items reference four choice-group types via UUID FKs (strict, relaxed, strict_presentation, relaxed_presentation). Conversion never writes here.

From 001-master-schema.xml + 015-choice-groups-restructuring.xml.

PK: (timeframe_id, opco_itm_id) · FK itm_nbr → items

Per-opco realisation of an item with metrics: cases, net_sales, gross_prft, gross_prft_vlcty (all DOUBLE PRECISION). The FROM side of every conversion must exist here for the user's opco; per-case metrics derived from this row are the primary projection input.

PK: (timeframe_id, opco_itm_id, username, version)

Per-user, versioned record of ADDED / DELETED / NONE against an opco_itm_id. References to master_data.opco_items are logical only — no DB FK. We follow that convention in the new tables.

PK: (timeframe_id, opco_itm_id)

Per-timeframe "current saved state" — no user dimension. Save flow promotes the latest unsaved row into here.

Mirror-pattern source for the new latest_unsaved_item_conversions view. From 007-new-tables.xml:

WITH latest_unsaved_changes AS ( SELECT DISTINCT ON (unsaved_changes.timeframe_id, unsaved_changes.opco_itm_id, unsaved_changes.username) unsaved_changes.timeframe_id, unsaved_changes.opco_itm_id, unsaved_changes.username, unsaved_changes.user_action, unsaved_changes.action_id, unsaved_changes.created_at, unsaved_changes.updated_at FROM app_data.unsaved_changes ORDER BY unsaved_changes.timeframe_id, unsaved_changes.opco_itm_id, unsaved_changes.username, unsaved_changes.version DESC ) SELECT uc.* FROM latest_unsaved_changes uc LEFT JOIN app_data.saved_changes sc ON sc.timeframe_id = uc.timeframe_id AND sc.opco_itm_id = uc.opco_itm_id WHERE COALESCE(sc.user_action, 'NONE'::opco_item_action) IS DISTINCT FROM uc.user_action;

Two tricks worth absorbing: DISTINCT ON … ORDER BY version DESC for "latest per key", and IS DISTINCT FROM in the WHERE clause to drop rows whose unsaved value already matches the saved state (i.e. the user has effectively reverted their change). The new view reuses both.
  • Primary keys: pk_<table>
  • Foreign keys: fk_<base_table>_<referenced_table>
  • Indexes: idx_<table>_<columns>
  • Unique constraints: uq_<table>_<columns>
  • Check constraints: ck_<table>_<short_predicate>
  • Liquibase changelogs: NNN-feature-name.xml, three-digit zero-padded sequence. Next slot: 020-item-conversion-tables.xml.
  • Enum types live in their owning schema (per the convention established by 016-relocate-enum-types-to-owning-schemas.xml).

Every conversion is conceptually one action performed by one user on one opco within one timeframe. It involves exactly two items: a FROM whose effective case count goes down and a TO whose effective case count goes up, in matching absolute magnitudes.

Two reasonable ways to persist that:

Effective-cases computation is a clean SUM(delta_cases) GROUP BY opco_itm_id — no UNION over from/to columns. Mirrors the existing unsaved_changes shape (one row per opco_itm_id), so the rest of the codebase (queries, materialised views, agent SQL) extends naturally. The TO-side row tolerates a NULL opco_itm_id cleanly when the TO item isn't in master_data — only the TO row has the nullable column, the FROM row keeps its NOT NULL. Indexes on opco_itm_id support "what conversions affect this opco-item" queries directly. A single conversion is two physical rows — need to remember to fetch both sides via conversion_id when displaying or editing. Constraint to keep both sides in sync (matching |delta_cases|, opposite signs) lives at the application layer, not the DB.

Decision: two-row. The two cons are real but cheap to manage in the API layer (single transaction inserts both rows); the pros compound across every consumer of the data.

Each row carries:

The conversion record carries a snapshot of the ratio at the moment the conversion was made. The live ratio lives on item_relationships.item_pair_relationships and is refreshed by the (future) Redshift job. Snapshotting on the conversion lets us answer questions like "how often do users override the system's suggestion?" and "what did the data say at the time the merchandiser decided?" — purely audit / analytics, not for math.

The full picture, including the existing tables we read from but never write to. New tables and columns are highlighted with the <<new>> stereotype.

@startuml !theme plain hide circle skinparam linetype ortho skinparam roundCorner 8 ' ========== existing: master_data ========== package "master_data" as md { entity "items" as items { * timeframe_id : INT <<PK>> * itm_nbr : VARCHAR(50) <<PK>> -- hierarchy_id : INT relaxed_choice_group_id : UUID relaxed_presentation_choice_group_id : UUID itm_desc : TEXT sysco_brnd_ind : VARCHAR(1) } entity "opco_items" as opco_items { * timeframe_id : INT <<PK,FK>> * opco_itm_id : BIGINT <<PK>> -- itm_nbr : VARCHAR(50) <<FK>> opco_id : VARCHAR(50) cases : DOUBLE PRECISION net_sales : DOUBLE PRECISION gross_prft : DOUBLE PRECISION gross_prft_vlcty : DOUBLE PRECISION rec_type : rec_type } } ' ========== existing: app_data ========== package "app_data" as ad { entity "unsaved_changes" as uc { * timeframe_id : INT <<PK,FK>> * opco_itm_id : BIGINT <<PK>> * username : VARCHAR(50) <<PK,FK>> * version : INT <<PK>> -- user_action : opco_item_action action_id : VARCHAR(36) created_at / updated_at } entity "saved_changes" as sc { * timeframe_id : INT <<PK,FK>> * opco_itm_id : BIGINT <<PK>> -- user_action : opco_item_action } entity "timeframes" as tf { * id : INT <<PK>> } entity "users" as users { * username : VARCHAR(50) <<PK>> } ' ========== NEW ========== entity "unsaved_item_conversions" as uic <<new>> #LightYellow { * conversion_id : UUID <<PK>> * role : item_conversion_role <<PK>> * version : INT <<PK>> -- * timeframe_id : INT <<FK>> * username : VARCHAR(50) <<FK>> * opco_id : VARCHAR(50) * supc : VARCHAR(50) opco_itm_id : BIGINT /' nullable for TO '/ * delta_cases : INT /' signed '/ sa_type : VARCHAR(10) cross_purchase_ratio : NUMERIC(5,4) created_at / updated_at } entity "saved_item_conversions" as sic <<new>> #LightYellow { * conversion_id : UUID <<PK>> * role : item_conversion_role <<PK>> -- * timeframe_id : INT <<FK>> * opco_id : VARCHAR(50) * supc : VARCHAR(50) opco_itm_id : BIGINT * delta_cases : INT sa_type : VARCHAR(10) cross_purchase_ratio : NUMERIC(5,4) } } ' ========== existing+new: item_relationships ========== package "item_relationships" as ir { entity "supc_store" as ss { * supc : VARCHAR(20) <<PK>> -- bc_name / ig_name / ag_name item_description sysco_brand_indicator prioritized_nb_indicator avg_gpv } entity "item_pair_relationships" as ipr { * id : BIGSERIAL <<PK>> -- * from_supc : VARCHAR(20) * to_supc : VARCHAR(20) sa_type : VARCHAR(10) cross_purchase_ratio : NUMERIC(5,4) <<new>> } entity "supc_projected_opco_metrics" as spm <<new>> #LightYellow { * supc : VARCHAR(20) <<PK>> * opco_id : VARCHAR(50) <<PK,NULL-distinct>> -- projected_per_case_gp : NUMERIC projected_per_case_net_sales : NUMERIC projected_gross_prft_vlcty : NUMERIC projection_source : projection_source last_refreshed_at : TIMESTAMP } } ' relationships items ||--o{ opco_items tf ||--o{ uc tf ||--o{ sc users ||--o{ uc tf ||--o{ uic users ||--o{ uic tf ||--o{ sic ' logical (no DB FK) opco_items }o..o{ uc : "opco_itm_id (logical)" opco_items }o..o{ sc : "opco_itm_id (logical)" opco_items }o..o{ uic : "opco_itm_id (logical, nullable on TO)" opco_items }o..o{ sic : "opco_itm_id (logical, nullable on TO)" uic ||--o| sic : "promoted on save\n(conversion_id)" ss }o..o{ uic : "supc (logical)" ss }o..o{ sic : "supc (logical)" ss ||--o{ ipr : "supc" ss ||--o{ spm : "supc" @enduml

Two important shapes to read off the diagram:

 CREATE TYPE app_data.item_conversion_role AS ENUM ( 'FROM', 'TO' ); CREATE TYPE item_relationships.projection_source AS ENUM ( 'opco_observed', -- T1, this opco's actual opco_items row 'cross_opco_harmonic_mean',-- T1, other opcos' rows, harmonic mean 'hierarchy_average', -- T2, BC/IG/AG average across opco_items 'network_average', -- T3, hierarchy average computed once globally 'none' -- no projection possible (no hierarchy data) );

Both follow the convention established by 016-relocate-enum-types-to-owning-schemas.xml — enums live in the schema that owns them. Each CREATE TYPE is wrapped in a Liquibase preConditions block that checks pg_type, matching the pattern used for opco_item_action.

 CREATE TABLE app_data.unsaved_item_conversions ( conversion_id UUID NOT NULL, role app_data.item_conversion_role NOT NULL, version INTEGER NOT NULL DEFAULT 1, timeframe_id INTEGER NOT NULL, username VARCHAR(50) NOT NULL, opco_id VARCHAR(50) NOT NULL, supc VARCHAR(50) NOT NULL, opco_itm_id BIGINT, delta_cases INTEGER NOT NULL, sa_type VARCHAR(10), cross_purchase_ratio NUMERIC(5,4), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_unsaved_item_conversions PRIMARY KEY (conversion_id, role, version), CONSTRAINT fk_unsaved_item_conversions_timeframe FOREIGN KEY (timeframe_id) REFERENCES app_data.timeframes(id) ON DELETE CASCADE, CONSTRAINT fk_unsaved_item_conversions_user FOREIGN KEY (username) REFERENCES app_data.users(username) ON DELETE CASCADE, CONSTRAINT ck_unsaved_item_conversions_sign CHECK ( (role = 'FROM' AND delta_cases < 0) OR (role = 'TO' AND delta_cases > 0) ), CONSTRAINT ck_unsaved_item_conversions_from_has_id CHECK (role = 'TO' OR opco_itm_id IS NOT NULL), CONSTRAINT ck_unsaved_item_conversions_ratio CHECK (cross_purchase_ratio IS NULL OR (cross_purchase_ratio >= 0 AND cross_purchase_ratio <= 1)) ); CREATE INDEX idx_unsaved_item_conversions_user_timeframe ON app_data.unsaved_item_conversions (username, timeframe_id); CREATE INDEX idx_unsaved_item_conversions_opco_itm ON app_data.unsaved_item_conversions (opco_itm_id) WHERE opco_itm_id IS NOT NULL; CREATE INDEX idx_unsaved_item_conversions_supc_opco ON app_data.unsaved_item_conversions (supc, opco_id); UUID generated by the API on conversion create. Identical on both FROM and TO rows of the same conversion. Survives the unsaved → saved promotion. Enum FROM or TO. Each conversion has exactly one row of each role per (conversion_id, version). Starts at 1. The user editing a conversion appends a new pair of rows with version + 1; old versions are retained for audit. The latest_unsaved_item_conversions view exposes only the highest version. FK to app_data.timeframes. The conversion is tied to a timeframe because per-case metrics, choice groups, and the user's simulation context are all timeframe-scoped. FK to app_data.users. Unsaved state is per-user. No FK to master_data.opcos — consistent with how the existing schema handles opco references in app_data (string keys, logical reference). Canonical SUPC string. Always populated, even when opco_itm_id is NULL (TO items missing from master_data). The supc value identifies the item universally; the opco_itm_id is just a master_data-side resolution. Nullable. Must be NOT NULL on FROM rows (you can only move volume that exists in opco_items), enforced by ck_..._from_has_id. NULL on TO rows when the TO item isn't stocked by the opco — projections come from supc_projected_opco_metrics instead. Signed integer. ck_..._sign enforces FROM < 0 and TO > 0. Equal absolute magnitude across the two rows of a single conversion is application-enforced (single INSERT statement / transaction). Audit snapshot of which IRP relationship type the user picked (C, I, SA1). Nullable for cases where the conversion was free-text-entered without an IRP candidate (open question — see open questions). Audit snapshot of what the live ratio said at conversion time. NUMERIC(5,4) for [0.0000, 1.0000] with bps precision. NULL when no ratio exists yet (v1 default, until Redshift job is wired up).

The natural identity of a row is (conversion_id, role, version). Both unsaved_changes (composite PK) and unsaved_simulations (surrogate id) patterns exist in app_data; composite suits conversions better because the components have meaning and queries frequently filter by (conversion_id, version).

Alice converts 95 cases of SUPC 0010165 (FROM) to SUPC 0013060 (TO) in opco 042, timeframe 12. Initial creation produces one INSERT of two rows:

INSERT INTO app_data.unsaved_item_conversions ( conversion_id, role, version, timeframe_id, username, opco_id, supc, opco_itm_id, delta_cases, sa_type, cross_purchase_ratio ) VALUES ('c1a-...', 'FROM', 1, 12, 'alice', '042', '0010165', 554321, -95, 'C', 0.3500), ('c1a-...', 'TO', 1, 12, 'alice', '042', '0013060', 554322, 95, 'C', 0.3500);

Alice then changes her mind and bumps it to 120 cases. A second INSERT writes version = 2:

INSERT INTO app_data.unsaved_item_conversions (...) VALUES ('c1a-...', 'FROM', 2, ..., -120, 'C', 0.3500), ('c1a-...', 'TO', 2, ..., +120, 'C', 0.3500);

Version 1 is retained for audit. The latest_unsaved_item_conversions view returns only version 2.

If Alice removes the conversion before saving, both versions are hard-deleted (no tombstone needed — saved state is empty, so the absence of the rows is the absence of the conversion). If Alice has already saved and wants to undo, she creates a new conversion in the opposite direction (different conversion_id) — see Q2 in open questions.

 CREATE TABLE app_data.saved_item_conversions ( conversion_id UUID NOT NULL, role app_data.item_conversion_role NOT NULL, timeframe_id INTEGER NOT NULL, opco_id VARCHAR(50) NOT NULL, supc VARCHAR(50) NOT NULL, opco_itm_id BIGINT, delta_cases INTEGER NOT NULL, sa_type VARCHAR(10), cross_purchase_ratio NUMERIC(5,4), created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_saved_item_conversions PRIMARY KEY (conversion_id, role), CONSTRAINT fk_saved_item_conversions_timeframe FOREIGN KEY (timeframe_id) REFERENCES app_data.timeframes(id) ON DELETE CASCADE, CONSTRAINT ck_saved_item_conversions_sign CHECK ( (role = 'FROM' AND delta_cases < 0) OR (role = 'TO' AND delta_cases > 0) ), CONSTRAINT ck_saved_item_conversions_from_has_id CHECK (role = 'TO' OR opco_itm_id IS NOT NULL), CONSTRAINT ck_saved_item_conversions_ratio CHECK (cross_purchase_ratio IS NULL OR (cross_purchase_ratio >= 0 AND cross_purchase_ratio <= 1)) ); CREATE INDEX idx_saved_item_conversions_timeframe ON app_data.saved_item_conversions (timeframe_id); CREATE INDEX idx_saved_item_conversions_opco_itm ON app_data.saved_item_conversions (opco_itm_id) WHERE opco_itm_id IS NOT NULL; CREATE INDEX idx_saved_item_conversions_supc_opco ON app_data.saved_item_conversions (supc, opco_id);

Differences from the unsaved table:

Reasonable case for adding a saved_item_conversions_history table mirroring the existing saved_changes_history pattern (from 004-history-table-additions.xml). Deferred to open questions — depends on whether anyone actually needs "show me every edit Alice made to this conversion before she saved it".

Mirrors the existing latest_unsaved_changes pattern: latest version per (conversion_id, role, username) plus a filter that drops rows whose contents already match the saved state.

CREATE OR REPLACE VIEW app_data.latest_unsaved_item_conversions AS WITH latest AS ( SELECT DISTINCT ON (uic.conversion_id, uic.role, uic.username) uic.conversion_id, uic.role, uic.username, uic.version, uic.timeframe_id, uic.opco_id, uic.supc, uic.opco_itm_id, uic.delta_cases, uic.sa_type, uic.cross_purchase_ratio, uic.created_at, uic.updated_at FROM app_data.unsaved_item_conversions uic ORDER BY uic.conversion_id, uic.role, uic.username, uic.version DESC ) SELECT l.* FROM latest l LEFT JOIN app_data.saved_item_conversions sic ON sic.conversion_id = l.conversion_id AND sic.role = l.role WHERE COALESCE(sic.delta_cases, 0) IS DISTINCT FROM l.delta_cases OR COALESCE(sic.supc, '') IS DISTINCT FROM l.supc OR COALESCE(sic.opco_itm_id, -1) IS DISTINCT FROM COALESCE(l.opco_itm_id, -1);

The filter logic differs in spirit from latest_unsaved_changes (which compares enum values) — here we drop rows when the unsaved record's "shape" (delta, target SUPC, target opco_itm_id) already matches what's saved. In practice the saved-conversion pattern is "new conversions are always new conversion_ids", so the view's filter is mainly a guard against re-saving an already-saved conversion that has had no further unsaved edits.

The Java side maps this view to an immutable read-only entity (mirroring LatestUnsavedChange.java). Repositories use it for "what does Alice's pending state look like" reads, and the save flow uses it to discover which rows to promote.

Holds per-opco projected per-case metrics for SUPCs that appear in IRP pairs but are missing from master_data.opco_items for the user's opco. Populated by the daily item-rel ETL (algorithm details in projection engine). The conversion projection layer reads this when computing impact for a TO row whose opco_itm_id is NULL.

CREATE TABLE item_relationships.supc_projected_opco_metrics ( supc VARCHAR(20) NOT NULL, opco_id VARCHAR(50), projected_per_case_gp NUMERIC(12,4), projected_per_case_net_sales NUMERIC(12,4), projected_gross_prft_vlcty NUMERIC(12,4), projection_source item_relationships.projection_source NOT NULL, last_refreshed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_supc_projected_opco_metrics PRIMARY KEY (supc, COALESCE(opco_id, '')), ); CREATE INDEX idx_supc_projected_opco_metrics_supc ON item_relationships.supc_projected_opco_metrics (supc);

Standard SQL doesn't allow expressions in PRIMARY KEY; PostgreSQL does support UNIQUE INDEX on expressions but not PK. Practical alternatives:

  • (a) Use a non-NULL sentinel like opco_id = '' for network-level rows; PK is (supc, opco_id).
  • (b) Use the PG-15+ NULLS NOT DISTINCT clause: UNIQUE NULLS NOT DISTINCT (supc, opco_id) as a unique constraint (not PK).

v1 chooses (a) — empty string for network-level rows. It's portable and reads cleanly. The doc'd PK above is illustrative; the real migration uses opco_id VARCHAR(50) NOT NULL DEFAULT '' with a normal composite PK.

 SUPC string. Always populated. Soft reference to supc_store.supc. Empty string for network-level (T3) rows, otherwise the opco identifier. The application layer maps '' ↔ NULL at read time so the rest of the codebase sees a clean nullable. Projected per-case GP and net sales. Same unit as opco_items.gross_prft / cases etc. Projected weekly GPV — used for parity with the existing opco_items.gross_prft_vlcty field (and downstream consumers that may need it). Enum identifying which tier produced the projection. Drives the UI's confidence badge. When the daily ETL last computed this row. Helps surface stale projections after a master-data refresh (see Q4 in open questions).

The PoC already created this table (per chandimas-work/ notes) with columns id, from_supc, to_supc, sa_type, sa_lot_number, sa_lot_string. v1 adds one nullable column:

ALTER TABLE item_relationships.item_pair_relationships ADD COLUMN cross_purchase_ratio NUMERIC(5,4), ADD CONSTRAINT ck_item_pair_relationships_ratio CHECK (cross_purchase_ratio IS NULL OR (cross_purchase_ratio >= 0 AND cross_purchase_ratio <= 1));

Populated by a future Redshift job (out of v1 scope — see Q5 in open questions). v1 ships with the column present and NULL; the UI falls back to "—" everywhere it would show the ratio.

The UI needs "what's the effective case count of this opco_itm_id in this opco for this user right now?" — a number that respects both the master_data baseline and the pending deltas from unsaved/saved conversions and unsaved/saved adds/deletes.

A natural design instinct is a SQL view effective_opco_items. We rejected it for v1 because:

  • PostgreSQL views can't take parameters; "for this user" would force a parameterised function instead, complicating querying.
  • The math is trivial — opco_items.cases + Σ(deltas) — and the inputs are already fetched for other reasons (the UI loads pending changes for the panel anyway).
  • Keeping it in the service layer lets us add policy (e.g. "what if user X is reviewing user Y's saved state?") without DDL changes.

The formula, for opco_itm_id X in opco O for user U in timeframe T:

\text{effective\_cases}(X, O, U, T) = \underbrace{C_\text{master}}_{\text{opco\_items.cases}} + \underbrace{\Delta_\text{saved}^{X}}_{\text{Σ saved conversions for X}} + \underbrace{\Delta_\text{unsaved}^{X,U}}_{\text{Σ latest-unsaved conversions for X, user U}}

Where each Σ is over delta_cases from saved_item_conversions and latest_unsaved_item_conversions respectively, joined on opco_itm_id = X. ADDED/DELETED contributions from unsaved_changes/saved_changes only affect whether the item is "in the assortment" (count-based), not its case figure, so they don't enter this formula.

SELECT oi.opco_itm_id, oi.cases::INT AS master_cases, COALESCE(SUM(sic.delta_cases), 0) AS saved_delta, COALESCE(SUM(luic.delta_cases), 0) AS unsaved_delta, oi.cases::INT + COALESCE(SUM(sic.delta_cases), 0) + COALESCE(SUM(luic.delta_cases), 0) AS effective_cases FROM master_data.opco_items oi LEFT JOIN app_data.saved_item_conversions sic ON sic.opco_itm_id = oi.opco_itm_id AND sic.timeframe_id = oi.timeframe_id LEFT JOIN app_data.latest_unsaved_item_conversions luic ON luic.opco_itm_id = oi.opco_itm_id AND luic.timeframe_id = oi.timeframe_id AND luic.username = :username WHERE oi.timeframe_id = :timeframe_id AND oi.opco_itm_id = ANY(:opco_itm_ids) GROUP BY oi.opco_itm_id, oi.cases; @startuml !theme plain skinparam state { BackgroundColor #FAFAFA BorderColor #888 FontName Helvetica } [*] --> Drafting : user opens Convert,\npicks FROM + TO + slider state "Unsaved · v1" as v1 #PaleTurquoise { Drafting : delta_cases stored\n(both FROM and TO rows) } state "Unsaved · v2..vN" as vN #PaleTurquoise { } v1 --> vN : user edits\n(new version row pair) vN --> vN : further edits v1 --> Removed : user removes\n(hard-delete rows) vN --> Removed : user removes Removed --> [*] state "Saved" as saved #LightGreen { saved : single row pair\nin saved_item_conversions\n(no version, no username) } v1 --> saved : save flow:\ninsert into saved,\ndelete from unsaved vN --> saved : save flow uses latest version saved --> Reversed : user wants undo\nafter save Reversed : NEW conversion_id\nin opposite direction\n(FROM/TO swapped,\ndelta_cases sign flipped) Reversed --> [*] note right of saved Saved conversions are immutable. Multiple users' saved conversions coexist in the timeframe — deltas accumulate additively. end note note right of vN Old versions retained in unsaved_item_conversions until save or remove. latest_unsaved view returns only the highest version. end note @enduml

Notes on each transition:

All schema changes ship as one new changelog file, slotted at the next sequential number per the naming convention.

<?xml version="1.0" encoding="UTF-8"?> <databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.20.xsd"> <!-- 1. enum types --> <changeSet id="020-create-type-item-conversion-role" author="aot"> <preConditions onFail="MARK_RAN"> <sqlCheck expectedResult="0"> SELECT COUNT(*) FROM pg_type WHERE typname = 'item_conversion_role' </sqlCheck> </preConditions> <sql> CREATE TYPE app_data.item_conversion_role AS ENUM ('FROM', 'TO'); </sql> </changeSet> <changeSet id="020-create-type-projection-source" author="aot"> <preConditions onFail="MARK_RAN"> <sqlCheck expectedResult="0"> SELECT COUNT(*) FROM pg_type WHERE typname = 'projection_source' </sqlCheck> </preConditions> <sql> CREATE TYPE item_relationships.projection_source AS ENUM ( 'opco_observed', 'cross_opco_harmonic_mean', 'hierarchy_average', 'network_average', 'none' ); </sql> </changeSet> <!-- 2. unsaved_item_conversions (table + constraints + indexes) --> <changeSet id="020-create-table-unsaved-item-conversions" author="aot"> … </changeSet> <!-- 3. saved_item_conversions --> <changeSet id="020-create-table-saved-item-conversions" author="aot"> … </changeSet> <!-- 4. latest_unsaved_item_conversions view --> <changeSet id="020-create-view-latest-unsaved-item-conversions" author="aot"> … </changeSet> <!-- 5. supc_projected_opco_metrics --> <changeSet id="020-create-table-supc-projected-opco-metrics" author="aot"> … </changeSet> <!-- 6. cross_purchase_ratio on item_pair_relationships --> <changeSet id="020-add-cross-purchase-ratio-to-item-pair-relationships" author="aot"> … </changeSet> </databaseChangeLog>

The six changes form one logical migration ("introduce item conversion persistence"). Rolling out partial state (e.g. tables without the view) would leave the API in a broken intermediate. Liquibase still permits per-changeSet rollback within the file.

 Each createTable changeSet writes the table, primary key, foreign keys, check constraints, and indexes in the order they appear in the DDL above. Add rollback blocks to every changeSet (Liquibase requirement for non-auto-rollbackable changes like CREATE TYPE and views). The view changeSet uses <createView> with replaceIfExists="true" so re-applies are safe. Run Spotless after authoring (per aot-api/AGENTS.md): ./gradlew.bat spotlessApply. idx_unsaved_item_conversions_user_timeframe(username, timeframe_id) idx_unsaved_item_conversions_opco_itm + idx_saved_item_conversions_opco_itm (partial WHERE NOT NULL) idx_unsaved_item_conversions_supc_opco + idx_saved_item_conversions_supc_opco idx_saved_item_conversions_timeframe Composite PK (conversion_id, role, version) serves as the index — DISTINCT ON walks it in reverse

One entity per table. Style mirrors UnsavedChange.java / SavedChange.java. Sketches:

package com.sysco.eat.api.data.embeddable; public enum ItemConversionRole { FROM, TO } @Entity @Table(name = "unsaved_item_conversions", schema = "app_data") @IdClass(UnsavedItemConversion.UnsavedItemConversionId.class) @Builder @AllArgsConstructor @NoArgsConstructor @Data public class UnsavedItemConversion { @Id @Column(name = "conversion_id", columnDefinition = "uuid") private UUID conversionId; @Id @Enumerated(EnumType.STRING) @Column(name = "role", columnDefinition = "app_data.item_conversion_role") private ItemConversionRole role; @Id @Column(name = "version") private Integer version; @Column(name = "timeframe_id") private Integer timeframeId; @Column(name = "username") private String username; @Column(name = "opco_id") private String opcoId; @Column(name = "supc") private String supc; @Column(name = "opco_itm_id") private Long opcoItmId; // nullable @Column(name = "delta_cases") private Integer deltaCases; @Column(name = "sa_type") private String saType; @Column(name = "cross_purchase_ratio") private BigDecimal crossPurchaseRatio; @Column(name = "created_at", updatable = false) private OffsetDateTime createdAt; @Column(name = "updated_at") private OffsetDateTime updatedAt; @Data @NoArgsConstructor @AllArgsConstructor public static class UnsavedItemConversionId implements Serializable { private UUID conversionId; private ItemConversionRole role; private Integer version; } } @Entity @Table(name = "saved_item_conversions", schema = "app_data") @IdClass(SavedItemConversion.SavedItemConversionId.class) public class SavedItemConversion { @Id private UUID conversionId; @Id @Enumerated(EnumType.STRING) private ItemConversionRole role; // … same columns minus username + version … } @Entity @Immutable @Table(name = "latest_unsaved_item_conversions", schema = "app_data") @IdClass(LatestUnsavedItemConversion.LatestUnsavedItemConversionId.class) public class LatestUnsavedItemConversion { @Id private UUID conversionId; @Id @Enumerated(EnumType.STRING) private ItemConversionRole role; @Id private String username; // … rest of columns … }