Databases
Modeling Vendor Price Revisions in PostgreSQL
By Vivek Jalondhara · 5 min read
Published
Keep vendor prices traceable to their source without silently changing the prices already selected for a project.
A vendor sends a replacement price sheet after a project has already selected several items. Updating every catalog row in place is tempting, but it changes what the project appears to have selected. The application needs to distinguish the current catalog from a historical choice.
This educational design uses Sheet Parser’s vendor catalog and project pricing domain. It describes one possible revision model, not the application’s private schema or a claim that a particular review process is deployed.
Separate the item from the observation
A vendor item represents a continuing identity. A price revision records what a particular source said about that item. Keep these concepts separate even when the first import supplies both. A corrected description should not require inventing another physical product, while a changed amount should not erase the earlier observation.
For the example, a revision carries its own identifier, a vendor-item reference, an import identifier, and a source-row reference. Keep the original source available through an access-controlled reference. Reprocessing a sheet may produce another interpretation of the same row; that is a new extraction attempt, not automatically a new accepted catalog price.
Make units and precision part of the value
An amount without a currency and selling unit is incomplete. A sheet may price a single device, a pack, or a length of cable. Store the supplied unit beside the amount and record any conversion separately. Do not replace an unknown currency with the workspace default simply to make a row pass validation.
PostgreSQL numeric supports exact decimal storage; the declared scale still determines rounding. Select precision from the accepted input contract, and reject excess precision when silent rounding would hide an extraction error. Preserve decimal strings at the JavaScript boundary rather than converting them through floating-point Number values. Negative adjustments need a separate explicit policy.
Pin a project to an accepted revision
Imagine a project selecting revision A and a later import proposing revision B. The project should continue referencing A until someone deliberately updates its selection. A project-price association can therefore point to a revision ID instead of asking for the latest vendor amount every time the page loads.
The interface can show that a newer accepted revision exists without applying it automatically. An update should display the previous source, proposed source, and changed fields. If users need an editable project-specific amount, store that override separately with its reason; otherwise the shared catalog loses the distinction between supplied and negotiated data.
Keep relationships within the workspace
The illustrative SQL makes workspace identity part of both sides of the item relationship. Its composite foreign key prevents a revision from naming an item belonging to another workspace. That protects a database relationship; the API must still authorize who can create, review, or attach the revision.
A database constraint cannot determine whether the vendor’s number was read correctly. Keep uncertain extractions in a review area before inserting accepted revisions. Treat source identity, decimal validation, and reviewer decisions as separate concerns so a syntactically valid row does not silently become a trusted price.
Publish a revision set deliberately
An import can contain several related prices. Define whether accepting it means publishing all valid rows together or accepting rows individually. For an all-or-nothing review, create the accepted revisions and switch the catalog’s active-import reference in one database transaction. Readers then resolve the selected import rather than guessing the newest row from timestamps.
Concurrent reviewers need a conflict rule. A conditional update against the previously reviewed import ID can reject an acceptance based on stale state. The losing reviewer should refresh the comparison, not silently overwrite the newer selection.
Test the history the user expects
Build a small fixture with one vendor item, two accepted revisions, and two projects selecting different revisions. Change the active catalog revision and confirm that neither project reference changes. Then attempt a cross-workspace reference and verify that the relationship is rejected.
Also exercise missing currency, duplicate extraction attempts, a source correction, and an intentionally removed catalog item. Keeping history increases storage and makes deletion more deliberate. The benefit is a concrete explanation of where a project’s amount came from, without pretending that the database can verify the source’s commercial meaning.
Keep a revision attached to the correct workspace
Educational schema for accepted revisions. Source storage, review state, project associations, and write permissions are intentionally outside this fragment.
Illustrative example · SQL · Simplified for explanation
CREATE TABLE vendor_items (
workspace_id uuid NOT NULL,
id uuid NOT NULL,
PRIMARY KEY (workspace_id, id)
);
CREATE TABLE price_revisions (
workspace_id uuid NOT NULL,
id uuid NOT NULL,
item_id uuid NOT NULL,
amount numeric(18, 4) NOT NULL
CHECK (amount >= 0 AND amount <> 'NaN'::numeric),
currency text NOT NULL,
unit text NOT NULL,
source_row text NOT NULL,
PRIMARY KEY (workspace_id, id),
FOREIGN KEY (workspace_id, item_id)
REFERENCES vendor_items (workspace_id, id)
);- Interpret the source row
- Review amount, currency, and unit
- Accept a price revision
- Attach the revision to a project
Conceptual flow for the example above.
Tradeoff to keep in mind
This schema preserves a relationship, not immutability. Restrict updates and deletion of accepted revisions through database privileges and the write path. Currency codes and units need separate validation.