AI / ML
AI Price-Sheet Parsing: System Design for Validation and Retries
By Vivek Jalondhara · 6 min read
Published
Design an LLM price-sheet workflow with OpenRouter, Node.js, Prisma, and PostgreSQL, including source review, job states, and safe retries.
A model response is only one step in a price-sheet import. The application also needs to know which vendor supplied the file, which source row produced a price, whether a reviewer accepted it, and what to do if processing stops halfway through. A plausible JSON object does not answer those questions.
This educational design uses Sheet Parser's confirmed stack: React.js, Node.js, TypeScript, OpenRouter LLMs, Prisma, and PostgreSQL. The suggested job model and validation policy are illustrative extensions of its vendor and project pricing domain. They are not claims about deployed infrastructure or measured extraction accuracy.
Choose a workflow before choosing an agent
A price import usually has known stages: receive a source, extract candidate rows, validate them, review discrepancies, and accept a revision. A fixed workflow can coordinate those stages while using an LLM for interpretation. Calling a model does not require giving it control over the entire application.
An agent becomes a different option when the system must choose its next action dynamically, such as investigating an unfamiliar document using a bounded set of tools. That flexibility needs limits on tool access, time, and cost. For the example here, keep orchestration in application code and let the model propose data for review.
Separate source records, extraction attempts, and accepted prices
Store the vendor association and source identity before extraction. Give each processing attempt its own status and record which source revision, prompt revision, and model configuration it used. A successful attempt can produce a draft; it should not silently replace accepted catalog values.
Preserve row or cell references where the parser can supply them, and make unresolved references visible. The review screen should show enough source context to check a value, including its unit and currency. A short description such as 'cable, 10' is ambiguous if one sheet prices per metre and another prices per roll.
Validate meaning as well as output shape
Structured output can help a supported OpenRouter endpoint return fields in a declared JSON shape. Support is endpoint-dependent, so confirm it for the selected route. Even a correctly shaped response may contain a price copied from the wrong column. Keep application validation and source review after the provider call.
Define the accepted decimal format, currency list, unit vocabulary, and treatment of missing values. In the example policy, missing data stays missing; a blank price does not become zero. Preserve decimal text until it reaches storage with an explicit precision policy. Do not guess whether a comma is a thousands separator or a decimal separator when the source is unclear.
Make a long-running import recoverable
If processing outlasts a normal request budget, persist the job before returning an identifier to the browser. Define states such as queued, running, awaiting review, accepted, and failed. A failed extraction should retain enough information to explain the failure without presenting partially parsed rows as accepted prices.
The status endpoint must check access to the job's workspace and project. The UI can resume from a job identifier after a reload, while the worker has a plan for recovering abandoned work. A timeout on the browser request alone does not establish whether an external provider has finished or whether a result was saved.
Design retries around a stable operation identity
A worker may save its result and stop before acknowledging the job. A second delivery must recognize the work already completed. Give the logical import a stable identifier, distinguish it from attempt identifiers, and enforce uniqueness where duplicate accepted output would be harmful. A file hash may help identify a source, but it does not by itself distinguish two deliberate imports under different policies.
Classify failures before retrying. A temporary provider failure may justify a bounded retry with backoff; invalid source data needs correction or review. Keep attempt history and a retry limit. Database constraints can prevent some duplicate records, but they do not guarantee that an external model call will be billed or executed only once.
Keep provider calls outside the acceptance transaction
Perform extraction and review before opening the transaction that accepts a draft. At acceptance, check that the draft is still eligible, write the selected catalog or project-price changes, and record the accepted revision together. A conditional transition must prevent two concurrent acceptance requests from applying the same draft twice.
Choose how project pricing relates to a changing vendor catalog. A project might reference the latest vendor price, or retain an accepted snapshot until its team chooses an update. Document that product policy and test it when a new vendor sheet arrives. The right relationship depends on the workflow; neither copying every value nor always using the latest value is universally correct.
Evaluate the pipeline with difficult, authorized examples
Create a small evaluation set containing repeated headers, merged cells, missing currencies, unit mismatches, duplicate rows, and ambiguous separators. Use synthetic or otherwise authorized sheets. Compare extracted values with reviewed expectations and track the reasons a row needs correction, rather than treating valid JSON as a successful extraction.
Evaluate changes to prompts and model routing against the same examples, including failure behavior and review effort. Record observed latency and cost per accepted import when data is available. No benchmark is claimed here: the goal of this design is to make correctness and operating behavior measurable before promoting a draft pipeline into a dependable product feature.
Validate a draft price without inventing missing values
This example accepts a deliberately small price shape and a limited currency list. It preserves decimal text and allows a missing price to remain a review case. It does not infer separators, taxes, units, discounts, or source accuracy.
Illustrative example · TypeScript · Simplified for explanation
type PriceDraft = {
sku: string;
unitPrice: string | null;
currency: string | null;
};
function parseDraft(value: unknown): PriceDraft {
if (!value || typeof value !== "object" || Array.isArray(value)) {
throw new Error("Expected a price object");
}
const { sku, unitPrice, currency } = value as Record<string, unknown>;
if (typeof sku !== "string" || !sku.trim() || sku.length > 80) {
throw new Error("Invalid SKU");
}
if (unitPrice !== null && (typeof unitPrice !== "string" ||
!/^(0|[1-9][0-9]{0,8})(\.[0-9]{1,4})?$/.test(unitPrice))) {
throw new Error("Expected a non-negative decimal string or null");
}
if (currency !== null && (typeof currency !== "string" ||
!["INR", "USD", "EUR"].includes(currency))) {
throw new Error("Unsupported currency");
}
if (unitPrice !== null && currency === null) {
throw new Error("A price needs a currency");
}
return { sku: sku.trim(), unitPrice, currency };
}- Receive a vendor sheet
- Extract a draft with an LLM
- Validate and review against source
- Commit accepted vendor and project prices
Conceptual flow for the example above.
Tradeoff to keep in mind
Passing these checks means the shape is acceptable, not that the price is correct. Bound the response size and row count before parsing, preserve source references separately, and use a decimal database type with an explicit precision policy when storing accepted prices.