Stralos Help Centre

stralos-api-rates implements organisation-scoped rate import paths. Every step is open to a signed-in member or admin (requireOrgMember); a service token never passes. Day-rate schedule imports stay admin-only. The current survey flow is upload → asynchronous survey → asynchronous per-block extraction → catalogue settlement and review → atomic commit. Reclassifying a heading can optionally resolve the reviewed catalogue decisions and run a replacement extraction with that vocabulary. The current region flow is upload → asynchronous parse → rectangle submission → asynchronous single-call extraction → immediately-terminal job → catalogue settlement and review → atomic commit. The confirmed-block and single-request parse contracts remain available for legacy callers.

Import stages

Three backend paths exist: survey, confirmed blocks, and a rectangular region. The current frontend uses the region path by default and retains survey for scanned or explicitly selected survey imports.

Survey flow

The survey flow runs through these stages:

Stage	Endpoint	What it does
1 — Upload	`POST /rate-imports/uploads`	Presigns an S3 PUT so the browser uploads the file directly. Returns an `uploadId`.
2 — Start survey	`POST /rate-imports/survey`	Persists a queued survey and returns `202` with a `surveyId`. The worker ingests the upload, deletes it after reading, and analyses the document structure.
2 — Poll survey	`GET /rate-imports/surveys/:surveyId`	Returns `queued`, `running`, `complete`, or `failed`; a complete response resolves candidates against the caller’s live catalogue.
3 — Extract	`POST /rate-imports/jobs`	Starts a polled extraction job from the surveyed blocks, initially without catalogue vocabulary. Returns `202` with a `jobId`. Rates are withheld from the poll response until the job is terminal.
3 — Poll	`GET /rate-imports/jobs/:jobId`	Returns the job status and per-block progress.
3 — Retry block	`POST /rate-imports/jobs/:jobId/blocks/:blockId/retry`	Re-runs a single failed block. Only failed blocks can be retried.
4 — Review	Client-side	Shows catalogue decisions above the extracted rows. Mapping or creating a record updates the current review in place.
4 — Optional re-read	`POST /rate-imports/entities`, then `POST /rate-imports/jobs`	Used only when the reviewer reclassifies a heading and chooses to read the rates again. Resolves the reviewed records, supplies their vocabulary, and starts a replacement job.
5 — Commit	`POST /rate-imports`	Atomically writes the reviewed rows.

The survey also accepts a legacy sheets body instead of an uploadId for callers that still parse the workbook client-side.

Confirmed-block flow (legacy client)

Stage	Endpoint	What it does
1 — Upload	`POST /rate-imports/uploads`	Same as the survey flow. Returns an `uploadId`.
2 — Parse	`POST /rate-imports/parses`	Ingest-only: reads the upload, runs deterministic block detection, and returns a `parseId`. No model call is made here.
2 — Poll parse	`GET /rate-imports/parses/:parseId`	Returns `queued`, `running`, `complete`, or `failed`. The complete response includes the detected blocks and the server’s copy of the grid.
3 — Analyse	`POST /rate-imports/analyses`	Takes a confirmed selection (`parseId`, `blocks`, optional `dismissedCells`), validates it against the parse record’s grid, and queues one narrow model call. Returns `202` with an `analysisId`.
3 — Poll analysis	`GET /rate-imports/analyses/:analysisId`	Returns `queued`, `running`, `complete`, or `failed`. The complete response resolves candidates against the caller’s live catalogue.
4 — Entities	`POST /rate-imports/entities`	Same as the survey flow, but the body names an `analysisId` instead of a `surveyId`.
5 — Extract	`POST /rate-imports/jobs`	Takes `{ analysisId, parseId }` instead of a client-supplied grid. The server reads sheets from the parse record and geometry from the analysis record.
5 — Poll	`GET /rate-imports/jobs/:jobId`	Same as the survey flow.
5 — Retry block	`POST /rate-imports/jobs/:jobId/blocks/:blockId/retry`	Same as the survey flow.
6 — Commit	`POST /rate-imports`	Atomically writes the reviewed rows. Same as the survey flow.

Analysis provider

Survey and extraction calls use an AI provider. Provider selection follows this order:

Condition	Provider
`STRALOS_AI_PROVIDER=bedrock`	Bedrock
`STRALOS_AI_PROVIDER=google`	Google Gemini (direct HTTPS)
`STRALOS_AI_PROVIDER=fixture`	Deterministic fixture parser
Lambda or `NODE_ENV=production`	Bedrock
Other local and test contexts	Fixture parser

Dev, test and prod set ai_provider = "google" in their tfvars, so the Bedrock row above is only the fallback when nothing is set. .env.example sets STRALOS_AI_PROVIDER=google as the local default too, so local review runs the same extraction path as the deployed environments: Gemini over HTTPS, with no AWS credentials, just a key. See the google section below for key setup. claude-code was retired with the Claude Code runner on 2026-10-01 and is now treated as unset. BEDROCK_MODEL_ID overrides the model. The Bedrock client uses AWS_REGION, defaulting to ap-southeast-2, and honours AWS_ENDPOINT_URL for local test endpoints. google calls Gemini’s generateContent API directly over HTTPS, with a default 300-second deadline. Before sending the JSON schema, it removes minLength, maxLength, minimum, maximum, minItems, maxItems, and default from a copy to stay within Gemini’s structured-output complexity budget. Structural keywords such as $schema, enum, required, additionalProperties, and anyOf remain. Every response is then validated against the complete Zod schema, so the removed constraints are still enforced server-side. The response text is assembled from every part that contains text. The Gemini call sets temperature: 0 to reduce sampling variation. This does not guarantee identical extraction results across repeated requests. Transient Gemini failures are retried within that single caller deadline. There are at most four attempts including the first. Statuses 429, 500, 502, 503, and 504, plus connection-level fetch failures, are retryable; 400, 401, 403, 404, 501, and an abort are terminal. Backoff starts at 500 ms, doubles up to 8 seconds, and uses equal jitter. A valid Retry-After value is honoured but clamped to the remaining budget. If sleeping would leave less than 1 second for another request, the sequence stops as budget-exhausted. Warning logs record attempt, status, willRetry, and either retryDelayMs or gaveUpBecause. The API key resolves from GOOGLE_API_KEY, then from the SSM SecureString named by GOOGLE_API_KEY_SSM_PARAMETER. The model resolves from GOOGLE_MODEL_ID, then from the SSM parameter named by GOOGLE_MODEL_ID_SSM_PARAMETER; when no model parameter name is configured, it defaults to gemini-3.6-flash. Standard environments provision both parameter names through stralos-core. The API key is cached for the life of the Lambda container. The fixture parser scans the first 30 rows of each sheet for a header containing name, unit, and cost-rate columns. It cannot see cross-tab layouts, block boundaries, or supplier headings. Serving it for the survey would return an empty result rather than a structure error, so the survey route rejects fixture requests with 422. The parse route does not reject the fixture provider. There is no provider on this path at all — detection is deterministic and runs regardless of provider configuration. Refusing fixture on the parse route would make a correctly configured deployment appear broken for no gain.

Provider concurrency

Network AI calls go through runStructured in aiRunner.ts, which holds a process-wide semaphore keyed to the provider’s real concurrency limit: 4 for both bedrock and google. There must be no second network-provider path: bypassing the gate allows concurrent jobs to overrun the provider limit. The deterministic fixture path is handled by each caller and does not enter runStructured or its semaphore. Bedrock logs successful calls with the provider, call site, prompt size, elapsed duration, stop reason, and token usage. Google logs both completion and failure with prompt and schema sizes, elapsed duration, and timeout details; its timeout logs include the prompt size. Google’s schemaBytes measures the stripped schema actually sent, and it also records finish reason and token usage. The semaphore uses a slot-handover design. release() hands the slot directly to the next waiter without ever decrementing the active count — decrementing first would leave a window in which a new caller could take the slot before the waiter resumed, producing the concurrent invoke the semaphore exists to prevent.

Upload and file reading

POST /rate-imports/uploads presigns an S3 PUT. The organisation comes from the token, never the body. That is enforced, not a convention: the organisation id becomes the key prefix, so accepting it from a caller would let one tenant write into another’s bucket path. POST /rate-imports/survey does not read the upload on the request path. It stores the uploadId and queues a kind: "survey" message. The worker calls the file service, which deletes the object after reading it and before returning. Once that response arrives, the worker persists the returned sheets and clears the upload reference. For a PDF up to the file service’s 3 MiB return cap, the original bytes exist only in that worker invocation. Bedrock and Gemini receive that native file. Larger PDFs continue text-only and carry a warning from the file service. The complete poll response relays the persisted sheets to the client so the later extraction job remains stateless. When the file service reports that it kept a copy of the file (stored), the survey or parse worker records it in the same write as the sheets and stores its id on the survey or parse row. The complete poll response returns it as importFileId (null when nothing was kept, for example for sheets posted directly). See Kept import files. The POST returns 202 { surveyId }; clients poll GET /rate-imports/surveys/:surveyId through queued → running → complete | failed. This is necessary because API Gateway allows about 29 seconds while document ingestion and the survey model can take several minutes. A failed poll response includes a safe error message for the client to display. The completed response schema always emits status: "complete", so a client can stop polling reliably.

Block detection

Before any model call, detectBlocks (in deterministic.ts) scans the workbook and locates candidate rate blocks using cell content only — no model involvement, no heading text matching. A block is a run of rows where at least one column contains long distinct strings (names) and at least one column parses as currency or a number (prices). The detector assigns each block a confidence score (0–1). Blocks at or above 0.7 are candidates passed to the model for adjudication. Filler row bridging. Rate tables in spreadsheets are not always contiguous. Between priced rows sit section headings (a lone text label) and runs of blank template rows that carry a unit and a markup but have no name and show $- (Excel’s accounting dash for zero). The detector bridges both: a block continues through section headings and blank template rows, stopping only when a new column header appears. Without bridging, the first filler row ended the block and split one table into two, losing the tail of sections like “Other Materials” and “Subcontractors”. Coverage is not a precision gate. The detector reports uncovered — priced cells no block accounts for — as an honesty mechanism. But complete coverage (zero uncovered cells) is not treated as a signal that the workbook can be answered without a model call. A shape detector cannot tell a rate library from a priced bill of quantities: both are name/unit/rate, and the difference is what the document is for. When no blocks have been confirmed by the model, the service always escalates rather than returning detected rows directly. One definition of “uncovered.” A cell is priced when it parses as a number — a bare 1.6 qualifies, not only a currency-formatted cell. A block covers a cell when the cell falls inside the block’s row range and in a column the block accounts for (cost, pivot columns, or columns explicitly marked as non-prices). The same rule applies to both the parse poll’s uncovered field and the job’s coverage validation; the two paths share a single implementation in coverage.ts. Before this was unified, the proposal used a row-level check that counted only currency-formatted cells, while the job used a cell-level check over the lexical predicate — a disagreement that could report zero gaps on the proposal and refuse twenty cells at analysis time, all from the same document. The model’s role is therefore bounded: given a digest of the candidate blocks, it decides which ones define prices and what the harvested labels mean. The grid itself is not sent to the model. When the file service returns the original PDF, the survey worker sends that file to the model and skips the detector-based adjudication shortcut. Without an original file, the shortcut may still handle detected candidates; if it cannot, the worker falls back to the full-document text prompt. PDF sent to the model as itself. When the file service returns the original file alongside the sheets (a PDF no larger than 3 MiB), surveyDocumentFrom converts it into a StructuredDocument (base64 data, MIME type, and file name). The survey worker passes this document directly to runStructured alongside the prompt, so Bedrock and Gemini receive the native file. On the attached-document path the adjudication step (surveyByAdjudication) is skipped entirely: it reads the detector’s text reconstruction and never sees the file, so running it first would answer the whole survey from flattened text and return before the attachment was ever sent. When the file exceeds the return cap, the service omits the bytes, adds a warning, and the survey runs text-only. The adjudication shortcut is taken only when no original file is in hand (spreadsheet, oversized-PDF, redelivery, and legacy paths). A truncated payload — where the file service’s byteLength disagrees with the decoded length of contentBase64 — throws rather than sending a partial document to the model, because a partial PDF neither fails nor reads correctly.

Survey and entity resolution (stage 2–3)

The survey identifies:

Blocks — row ranges in each sheet that hold unit rates. A sheet can contain multiple blocks. Blocks cannot overlap within a sheet and a block’s header row cannot sit inside its own data range or inside another block’s data range. These constraints are enforced server-side by validateSurvey; an inconsistent model response returns 502.
Candidates — the suppliers or organisation pricing scope (source), locations, and categories the document implies. Each location candidate can carry a parentCandidateId pointing to its source candidate so ownership can be checked.
Unclassified headings — cross-tab column headings the model cannot classify with certainty (e.g. a heading could be a location, a shift condition, or neither). These are returned to the caller for a human decision. Every unclassified heading in the survey must be decided before POST /rate-imports/entities is accepted; the server enforces this.

The complete survey poll resolves candidates against the catalogue using the caller’s token, so the review panel knows which records already exist. Resolution cannot run in the worker because the worker has no caller credential, and repeating it on each poll reflects catalogue records created while the survey was running. Matching is trimmed and case-insensitive: a document heading HIRE Plant resolves to the catalogue record Hire Plant. An exact-spelling match wins outright; where the catalogue holds both spellings, the document’s own spelling is the answer and the near-match is not a competitor. Two case-variants with no exact hit is ambiguous — selecting one silently would bind rates to a record nobody reviewed. A single active match is matched. Multiple active matches are ambiguous. A sole inactive match is inactive_match and is never silently reused. No matches is new. A location is matched directly against the location catalogue. With a parent source, only locations whose partyId belongs to a matched supplier are candidates; without one, only organisation-owned locations (partyId: null) are candidates. An unmatched supplier cannot make another company’s location match. If the catalogue is unavailable at survey time, the route degrades to returning unresolved candidates rather than failing the request. The response includes a warning explaining that existing records could not be checked. The survey includes deterministic warnings the model does not need to observe:

If no supplier or price source is named in the document, a warning is always added. This is the most common reason an import cannot be completed and is knowable without a model call.
If the adjudication excluded confident blocks (blocks with confidence ≥ 0.7 that were judged to consume rates rather than define them), each excluded block is named in a warning. An absent block is otherwise invisible on the review screen, and the reviewer cannot distinguish “no more rates here” from “something was excluded”.

POST /rate-imports/entities creates or maps the confirmed records. It is idempotent: a complete key replays its stored result and writes nothing; a fresh in-flight key returns 409 to prevent double-submits; a stale in-flight key (heartbeat older than 60 s) is treated as a crashed run and may be resumed. A location whose parent source creation failed is refused rather than created as an orphan. When creating a source with action: "create", the decision fields object accepts a sourceKind field: "supplier" (default) or "internal". An "internal" source represents the organisation’s own price file and resolves to scope: "organisation" with no supplier id or catalogue row. A "supplier" source creates a company with the supplier role via POST /parties and resolves to scope: "supplier" with that party id. The internal path accepts the existing source decision shape for compatibility but creates no record. A supplier requires a name; address fields (addressLine1, city, region, countryCode) are accepted but optional and are not written anywhere — no location is created from them. Creating a location under a supplier uses POST /parties/:id/locations, so Global records that company in partyId. Creating one under organisation pricing uses POST /locations, which leaves partyId null. Mapping a location resolves it directly and applies the same ownership invariant before returning its locationId. Mapping (action: map) is validated by an org-scoped lookup in api-global. A new candidate’s empty match list cannot be an allow-list, so the server looks up the targetId directly rather than checking list membership. That lookup 404s for IDs from another organisation, which is what prevents cross-tenant mapping. Unit tokens in category candidates are translated through the unit catalogue. A token can be a code, a symbol, or a display name. Anything that cannot be translated is named verbatim rather than silently narrowing what the category can price. observedUnits on each survey block retains the document’s own wording; it is evidence the reviewer reads. allowedUnitCodes on a proposed new category carries catalogue codes, not document tokens. Both fields come from the same raw observations, but allowedUnitCodes is mapped through the same normalisation table the write path uses before being sent to the category editor. A category created with a raw token (hr, cube) instead of a catalogue code (hour, cubic_metre) would fail validation at save time with a misleading message about submitted values no longer being valid.

Confirmed-block extraction

The legacy job body may carry confirmedBlocks directly. The service uses that geometry exactly as given, with no re-typing, confidence test, or model fallback. The current selection-first path sends confirmed geometry to the analysis endpoint and later starts a job with analysisId and parseId.

Confirmed block schema

A confirmed block extends the survey’s importBlock shape with full column-role information:

Field	Type	Description
`columns.name`	integer	0-based column index of the rate-name column. Required.
`columns.unit`	integer	0-based column index of the unit column. Optional.
`columns.code`	integer	0-based column index of the item-code column. Optional.
`columns.supplier`	integer	0-based column index of the supplier column. Optional.
`columns.locationScope`	integer	0-based column index of the location-scope column. Optional.
`columns.condition`	integer	0-based column index of the condition column. Optional.
`columns.cost`	integer	0-based column index of the cost column. Optional for a crosstab; required for a table.
`pivotColumns`	integer[]	0-based column indices whose header labels are pivot values (for example, supplier names across the top of a matrix). Empty for a table layout.
`pivotMeaning`	string	What the pivot-column headers represent. Required when `layout === "crosstab"`. One of `supplier`, `location`, `condition`, `size`, `date`, or `other`.
`headerGroups`	array	Grouping rows above the data rows that label spans of pivot columns. Required on submission — an empty array means “reviewed, no grouping row”; an absent field would mean “nobody looked”, and the two are treated differently. At most one grouping row per block in this version.
`headerGroups[].row`	integer	1-based row number of the grouping row. Must be above `firstDataRow`.
`headerGroups[].meaning`	string	What the group labels represent. Same values as `pivotMeaning`. Must differ from the block’s own `pivotMeaning`.
`headerGroups[].spans`	array	Non-overlapping, non-gapped spans covering every `pivotColumn`. Each span has `fromColumn`, `toColumn` (both inclusive, 0-based), and `label`.
`ignoredColumns`	integer[]	Columns that contain numbers but are not prices — densities, markups, percentages — so the coverage check does not flag them. Required; send `[]` when there are none.

Column indices are bounded to 0–16383 (XFD, the last column Excel and SheetJS can produce). The 40-column cap that applied under the legacy bounds-only path was a request-size bound, not a column-address bound, and has been removed from this path.

Dismissed cells

dismissedCells on the job request lets the canvas record priced cells the reviewer has looked at and decided are not rates. The server validates each dismissal against the grid and returns the canonical list on the job view, so the decision is auditable after the fact.

Field	Type	Description
`sheet`	string	Sheet name.
`row`	integer	1-based row number.
`column`	integer	0-based column index.
`reason`	string	Optional explanation, up to 200 characters.

Up to 500 cells may be dismissed per job. The server persists them in canonical (sheet, row, column) order regardless of the order submitted.

Selection validation (422 with a machine-readable code)

Before a confirmed block is accepted, validateConfirmedSelection runs structural and coverage checks. The legacy job path validates against the submitted sheets; the selection-first analysis path validates against the server-held parse record. A failure returns 422 with a machine-readable code in the response body alongside the message. Checks run in this order — structural errors are caught before the coverage sweep, because a block with broken geometry would produce misleading “uncovered cell” errors:

Code	Condition
`duplicate_block_id`	Two blocks in the request share the same `id`.
`block_sheet_not_found`	A block names a sheet not present in `sheets`.
`column_out_of_range`	A column role or pivot/ignored column index is ≥ the sheet’s actual width.
`pivot_meaning_missing`	A crosstab block omits `pivotMeaning`.
`header_row_missing`	A crosstab block has no header row from which to read its pivot labels.
`pivot_columns_missing`	A crosstab block has no `pivotColumns`.
`header_row_out_of_range`	A grouping row number is not between row 1 and the block’s `firstDataRow` (exclusive).
`header_group_not_applicable`	A table block (no pivot columns) carries a `headerGroups` entry.
`meaning_collision`	A `pivotMeaning` or header-group meaning names the same field as an existing column role on the block.
`span_out_of_range`	A header-group span falls outside the sheet’s columns.
`span_overlap`	Two spans in a header group overlap.
`span_gap`	A pivot column is not covered by any span in a header group.
`dismissal_duplicate`	The same cell appears more than once in `dismissedCells`.
`dismissal_not_priced`	A dismissed cell carries no price.
`column_role_is_pivot`	One column is assigned both a row-level role and a pivot role.
`uncovered_cell`	A priced cell is inside no block’s row/column scope and has not been dismissed.
`selection_exceeds_rate_cap`	The projected rate count exceeds the 2,000-rate per-job limit.
`selection_exceeds_decision_cap`	The projected supplier, location, and category decisions exceed the 200-decision entity gate.

Coverage is cell-level: a block covers a cell only when the cell is in its row range AND in a column the block accounts for (cost, pivot, or ignored). A priced cell in an unclaimed column of an otherwise-covered row is flagged. To skip a cell that carries a number but not a rate, add its column to ignoredColumns or dismiss the specific cell. The projected rate count is computed before extraction runs. An over-limit selection is refused with selection_exceeds_rate_cap before any block is queued, naming the projected count and the 2,000-rate cap.

The legacy confirmed-block body validates against its own sheets. The current selection-first flow instead validates the selection against the server-held parse record, then starts extraction with analysisId and parseId only.

Deterministic extraction for confirmed blocks

Confirmed blocks bypass the model entirely. tryDeterministic runs each block exactly as given with typeProvidedBlock and no confidence test. A cell the extractor cannot place produces a typed ConfirmedExtractionError naming the column, rather than escalating to the model. This error is non-retryable: the block goes to failed with the error message and code, and waits for a corrected retry. No provider budget is spent.

Analysis step (selection-first and region flows)

POST /rate-imports/analyses accepts either a confirmed selection or a plain rectangle. The body must carry exactly one of blocks or region; supplying both or neither is a 400.

Confirmed-block path (`blocks`)

POST /rate-imports/analyses takes { parseId, blocks, dismissedCells }. The blocks array must be non-empty and carry at least one confirmed block; an analysis of an empty selection has no purpose question to ask. Before any message is enqueued, the route validates the confirmed selection against the parse record’s grid server-side. A caller cannot pass a coverage check by posting a different grid to the analysis than the one the parse produced. If validation fails, the route answers 422 with a machine-readable code the canvas maps to the control that fixes the problem. A failure at this point is synchronous — the caller is told immediately rather than being given a 202 that polls into a failure. The route returns 202 { analysisId }. The worker runs one model call that asks only the purpose question: which of the confirmed blocks defines reusable prices rather than a priced schedule for a specific job. Everything downstream of that answer is deterministic. GET /rate-imports/analyses/:analysisId is discriminated on status the same way the parse poll is. A queued or running response carries no result fields. A complete response includes:

blocks — the blocks the model judged to be rate sources.
candidates — supplier, location, and category candidates, resolved against the caller’s live catalogue on each poll.
unclassified — cross-tab column headings the model could not classify.
excludedBlocks — blocks the model judged to be priced schedules rather than rate sources. Each entry carries the block’s id, cellCount (the number of priced cells it contained), and a reason. This field is always present so the reviewer can see what was excluded and why. An excluded block is not silently absent.
warnings — includes a warning when a kept block count of zero results, and when the candidate count exceeds the entity gate’s limit.

If the candidate count exceeds the maximum entity decisions allowed, the analysis completes with a warning naming the count and the limit. The analysis does not truncate candidates, because dropping candidates would silently un-create the memberships the rates need. A failed response carries { code, message }. code is machine-readable; message is safe to surface to a user.

Region path (`region`)

The region arm replaces the blocks array with a single rectangle: { sheet, firstRow, lastRow, firstColumn, lastColumn }. Rows are 1-based; columns are 0-based, matching the grid a person reads and the sourceRow each extracted rate carries. dismissedCells is rejected alongside region during request-schema validation with 400 Bad Request. Coverage is a property of a confirmed selection (every priced cell inside a block or explicitly ruled out); a rectangle makes no coverage claim, so accepting dismissals would record an answer to a question nobody asked. The route validates the rectangle synchronously against the server’s grid before enqueuing:

The named sheet must exist in the parse record.
lastRow must be within the sheet’s row count.
lastColumn must be within the sheet’s width.
The rectangle must be within the size limits below.

If any check fails, the route returns 422 without enqueuing anything. An out-of-range corner is a 422 the canvas can point at, not a silently clamped selection that would extract a different area from the one the person drew. Rectangle size limits. A rectangle is refused synchronously at the route, and re-checked by the worker before spending a model call, when it exceeds either of these limits:

Limit	Value	Code
Maximum cells	20,000	`region_too_large`
Maximum prompt characters	450,000	`region_too_large`

An answer over MAX_IMPORT_RATES (2,000) is refused by the worker with region_exceeds_rate_cap, naming the count. The worker serialises the rectangle as TSV labelled with real 1-based sheet-row numbers and asks the model for rates in a single runStructured call. It carries neither the file name nor the sheet name, so only the content the person chose influences the answer. The grid comes from the parse record — the grid the corners were validated against — never from the analysis row. A rate outside firstRow..lastRow was read from a line the model was never shown, so it is dropped and counted in a warning rather than clamped. sourceSheet is written from the region, never asked for. Before the region schema parses the model answer, repairModelBounds normalises out-of-bounds field values — see Model output repair. A single unreadable value costs at most the row it belongs to, not the whole rectangle. The region analysis result carries rates (the raw extracted rates in importedRateSchema shape) in addition to the standard blocks, candidates, unclassified, excludedBlocks, and warnings fields. analysisView does not publish rates; they are stored for the job route to consume. excludedBlocks is always [] — a rectangle makes no coverage claim, so there is nothing to set aside.

Model output repair

Before either extraction path parses its model answer, repairModelBounds in rateImport.ts normalises the raw response. The same function runs on the full-workbook path (parseRateWorkbook) and on the region path (extractRegion). A separate repair table on either path would be a second copy of the same field bounds — the first one to drift would silently lose documents on the path nobody edited. The repair classifies each field into one of three categories: Values that cost the row. A row whose sourceRow, costRate, name, unit, or sourceSheet is unreadable is dropped and counted in a warning. None of these can be repaired into something honest:

sourceRow is written verbatim into the created rate’s note, drives cross-tab detection, and is the region path’s containment check. A clamped row number lies in three places at once.
costRate is the price. A repaired price is a made-up price.
A blank name, unit, or sourceSheet would pass the region schema and fail the pipeline schema, losing the document between two checks that each looked complete.

Values that cost the field. conversionFactor, physics fields (bulkingPercent, compactionPercent, maxTonnes, maxCubicMetres), an out-of-taxonomy rateGroup, and null in any .optional() field are dropped. The row survives without them. Values that are clamped. confidence outside [0, 1] is set to 0 (the lowest). It is advisory and min-aggregated across a banded group. Losing a real price to a model writing 95 for 0.95 would be the wrong trade-off. Text bounds. Fields that exceed their maximum string length are shortened to fit (required fields) or dropped (optional fields). Padding-only overruns — where trimming brings the value inside the bound — are normalised silently. The model’s own warnings array is trimmed to at most 20 entries of 300 characters each; dropped entries are reported in a warning so the reviewer knows the list was cut. Every repair and every discarded row is reported through the warnings field on the analysis result. Confirmed-block jobs carry those warnings into their job blocks and on to review. Region extraction records them on the analysis result; the current region frontend does not carry analysis warnings into the review screen. Absent fields are not repaired. A value that is present but out of bounds is one bad row. A required field missing from every row is a structurally wrong model answer; that must keep failing loudly rather than quietly importing rows with holes.

Analysis worker

The analysis worker follows the same heartbeat pattern as the parse worker. startAnalysisHeartbeat beats every 30 seconds while the model call runs. The claim window uses the same sizing as the parse claim window, and canClaimAnalysis defaults to parseClaimTtlMs(). A redelivered message finds an abandoned row reclaimable within the first redelivery window. The dispatch route and the enqueue-failure arm both write through the RLS pool. rates_rate_import_analyses is FORCE ROW LEVEL SECURITY, so a transaction that does not set app.current_org matches zero rows and reports success, which would leave the row queued for ever. Both writes use withTenant or runScoped.

Job route discriminator

POST /rate-imports/jobs serves two contracts on one route:

Selection path — body contains analysisId or parseId. The route reads sheets from the parse record and geometry from the analysis record. Neither comes from the request body. The selection was already validated against the server’s grid when the analysis was created.
Legacy path — body contains neither key. The legacy schema is used; coverage is validated against the client-supplied sheets.

The discriminator keys on the presence of either analysisId or parseId rather than analysisId alone. A body carrying parseId beside a legacy grid would otherwise fall through to the permissive legacy schema, which strips the parseId and re-validates coverage against the client’s copy — which is the exact guarantee the selection path exists to close. A job row on the selection path records analysis_id and parse_id in place of survey_id. Entity keys follow the same pattern. A reader of a job row can tell which flow produced it. Region analyses produce an already-finished job. When the analysis carries a region column rather than blocks, the route builds one block whose state is done and writes the rates the analysis’s single model call produced directly onto that block. No queue message is sent and no worker runs. jobStatusFor reads complete off the blocks immediately, so polling the job returns complete on the first request. A region job with no rates is refused at job-create time with analysis_region_no_rates before the row is written. Three 422 codes guard the region job path:

Code	Condition
`analysis_region_unreadable`	The stored region geometry cannot be deserialised. Re-run the analysis.
`analysis_region_rates_unreadable`	The rates stored on the analysis cannot be deserialised. Re-run the analysis.
`analysis_region_no_rates`	The analysis read no rates inside the selected area. Select a different area and re-analyse.

rememberSelection saves no template for a region analysis. A rectangle takes seconds to redraw; the geometry reuse that matters is the twenty minutes of column-by-column confirmation the confirmed-block path asks for. Saving an empty-geometry template would occupy the one (organisation, fingerprint) slot for that shape and silently block any later confirmed import of the same document.

Error handling for analysis routes

POST /rate-imports/analyses returns 404 if the named parseId is not found.
POST /rate-imports/analyses returns 409 if the named parse has not completed.
POST /rate-imports/analyses returns 422 if the confirmed selection fails coverage validation against the parse record’s grid, or if the submitted region is out of bounds or too large (region_too_large).
POST /rate-imports/analyses returns 400 if dismissedCells is supplied alongside region, or when the body supplies both blocks and region or neither one.
GET /rate-imports/analyses/:analysisId returns 404 if the id is not found in the caller’s organisation.
If the enqueue step fails after the analysis row is created, the route marks the analysis failed and returns 502. The caller is not given a 202 for an analysis nothing will advance.

Extraction jobs

POST /rate-imports/jobs returns 202 with a jobId. Survey and confirmed-block jobs queue each block for the extraction worker in deployed environments. Region jobs are created already complete from the rates stored on the analysis. Progress for queued jobs is persisted after every block so a poll served by a different container sees real movement. Block states:

State	Meaning
`pending`	Not yet started.
`running`	Being extracted now.
`done`	Rates extracted.
`empty`	Block contained no priced rows. Not a failure — an unpriced section is a valid document state.
`failed`	Extraction failed.

Automatic retries cover only transient provider faults: provider_unavailable and timeout are retried up to twice. Deterministic failures (including confirmed-block extraction errors) go straight to failed and wait for POST .../blocks/:blockId/retry. Re-running a deterministic failure would only spend the provider slot every other pending block is queued behind. A job whose blocks all failed has status failed. A mixed job (some blocks done or empty, some failed) completes with status complete and carries the failures as warnings. Rates are withheld from the poll response until the job reaches a terminal state (complete or failed), so a poller cannot read half an extraction as final. Each rate in the terminal response includes a unitCode field carrying the resolved catalogue code for that row’s unit. The review screen filters eligible categories by comparing row units against each category’s allowedUnitCodes, and that comparison requires catalogue codes on both sides. Without unitCode on the job response the comparison falls back to the document’s raw token (hr, cube), which matches nothing and leaves the category combobox empty with no explanation. The terminal job response also includes dismissedCells — the canonical list of cells the person dismissed, in sheet/row/column order. This list is persisted on the job so the decision is auditable after the fact. Transience is determined from structured error codes and HTTP status codes, not from a regex over the error message.

Job dispatch and extraction worker

Lambda freezes the request execution environment after the HTTP response, so background promises started by the API cannot reliably finish. Deployed imports therefore put one message per extraction block on SQS and use a dedicated worker Lambda from the same deployment package. A block is the unit of progress and retry; putting the complete job in one message would replay successful blocks whenever one block failed. RATE_IMPORT_QUEUE_URL is required on Lambda. If it is absent, job creation marks the job failed and returns 502 instead of returning a jobId that can never progress, and survey dispatch fails with 502 rather than accepting an upload it cannot process. When the queue URL and AWS_LAMBDA_FUNCTION_NAME are both absent, the long-running pnpm dev server runs both survey and extraction blocks in-process. SQS delivery is at least once. The worker claims a block under a row lock only while it is pending, or while its existing running claim is stale. A completed block is acknowledged without another provider call. Workers re-read and lock the job after extraction, then replace only their own block in the stored array so concurrent blocks cannot overwrite each other. Each block records claimedAt. A running claim becomes reclaimable after twice RATE_IMPORT_WORKER_TIMEOUT_SECONDS (300 seconds by default). The work queue visibility timeout uses the same two-times window, so a crashed worker’s message becomes visible when the block becomes eligible to be claimed again. The queue sends a message to its dead-letter queue after two receives; each block has already had its internal transient retries by then. The same worker consumes the dead-letter queue, marks a still-pending or running block as failed, and emits a SUPPORT_ALERT record. Finished work is left unchanged. Survey jobs use the same queue and dead-letter drain with a kind discriminator. If a batch enqueue partially fails, the route marks the whole job failed. Messages accepted before the failure are safely discarded because the worker refuses to mutate a failed or cancelled job. Tenant-scoped transactions are required. rates_rate_import_jobs uses FORCE RLS and its policy reads app.current_org. Claim, persist, and dead-letter transactions therefore run through withTenant, keyed to the verified organisation carried on the SQS message. Without that context, a claim reads zero rows, acknowledges the message, and leaves the block pending. Explicit organisation filters remain for clarity; RLS is the isolation boundary. A worker that drains the queues while leaving a block at pending with attempts=0 and no worker log is the characteristic missing-tenant-context symptom. A local superuser can bypass RLS, so reproduce this boundary with the deployed database role.

Bounded input

The parse and survey schemas accept up to 20 sheets, 2,000 rows per sheet, 40 cells per row, and 300 characters per cell. The 40-cells-per-row cap applies to client-posted bodies; the confirmed-block path addresses ingested grids and accepts column indices up to 16,383 (XFD). The model prompt stops adding rows when its 450,000-character workbook-content budget would be exceeded. A parse result contains at most 2,000 rates and 20 warnings. HTTP request bodies are limited to 5 MB. Parse, survey, and job-create routes are limited to 10 requests per minute. Per-organisation queue fair share. Starting a new parse, survey, analysis, or extraction job is refused with 429 rate_import_busy when the requesting organisation already has two or more jobs across those tables in the queued or running state. Rows that have not been updated for more than one hour stop counting, so a crashed worker cannot permanently block imports for that organisation. Wait for one active import to reach a terminal state before starting another. When a job runs in multi-block mode (Stage 1 has already surveyed the workbook), each block is extracted in its own model call and receives only the sheet that block names, truncated after its last data row. Rows are sliced from the start of the sheet so that the 1-based row numbers the model reports stay absolute — dropping leading rows would renumber them and produce a silent wrong answer. If a block names a sheet that is not present in the submitted workbook (a survey/extract disagreement), that block’s call falls back to the full workbook so the extraction is expensive rather than silently empty.

Banded rates

When a price list quotes one item at several prices depending on the quantity ordered, extraction collapses those rows into a single rate carrying a tiered pricing rule rather than writing each row as a separate rate and letting the last one win. The shared groupBandedRates assembler performs this recognition. Whole-document surveys (including PDFs), legacy bounds-only blocks, and confirmed-block jobs reach it through parseRateWorkbook; the selection-first region path calls it from extractRegion. Both reader response schemas carry quantityBand, so either path can assemble a schedule. How recognition works. The model transcribes each band heading verbatim into the quantityBand field on the full-workbook or region response schema — for example <20m², 20-50m², 50-100m². Only a reader of the document can tell that 20-50m² is a quantity band rather than part of the item name, so the model is asked only to transcribe. bandedRates.ts then parses those strings into minQuantity thresholds deterministically — a parse of "20-50m²" into minQuantity: 20 is testable and wrong in ways a test can name, unlike a model-emitted band table. The full-workbook prompt and quantityBand schema description instruct the model to use numeric quantity ranges only. Supplier, quarry, branch, location, zone, plant, month, and “All” headings belong to cross-tab pricing, not quantity bands. This is model guidance; the string schema does not itself enforce that meaning. groupBandedRates parses the returned headings before assembling bands. Grouping. Rows are grouped into one banded rate only when they share the same name, code, category, unit, supplier, location, condition, conversion factor, rate group, and source sheet. The group becomes one ImportedRate with a banding object carrying tierBehaviour and bands. Each band holds minQuantity and unitPrice. The representative costRate on the option is always bands[0].unitPrice, so a client that edits a band and forgets the scalar cannot create an option whose price disagrees with its own pricing rule. Tier behaviour. The default is whole_quantity: the entire quantity is charged at the rate for the band it falls in. Civil rates are normally quoted this way. The progressive alternative charges each band separately up to the quantity. After importing, reviewers can change this in Pricing rules. Notices. Whole-document and confirmed-block jobs retain every parseRateWorkbook warning on the job block, and the terminal job response flattens those warnings for review. The selection-first region path is different: its general analysis warnings are not copied into the immediately complete job; bandingNotices are rebuilt from the stored rates and placed on that job block instead. These notices say how many rates were read as banded, that the whole-quantity charging basis is in use, and what happens when the quantity exceeds the top band. A reviewer who needs the progressive alternative is told where to change it. Grouping rules and refusals. A group must start at zero (minQuantity: 0 on the first band) — no quantity below the lowest band has a price, and inventing one is not allowed. A group where two rows claim the same threshold is refused and both rows import as ordinary rates. A single row with a band heading is not a band table; it imports as an ordinary rate. A group with gaps or overlaps remains banded as threshold pricing. Refusals, gaps or overlaps, and unreadable headings add parser warnings. Those warnings reach review on whole-document and confirmed-block jobs; the region path exposes only its rebuilt bandingNotices. Every emitted ordinary row has the transient band heading stripped. What does not qualify. A bare number such as 20 or 20 m² never parses as a band heading, to avoid silently turning an ordinary price list into a band table. A band must be expressed as a comparison or range — <20m², 20-50m², >100m, over 100 m, and equivalent wordings. Physical size in the item name (a 20mm asphalt mix, a G6 Chipseal grade) is not a quantity band and goes in the name; quantityBand is omitted for those rows. No migration required. rates_rate_import_analyses.result and the job blocks column are JSONB, and rates_pricing_rules already supports the tiered kind.

Quarry dual-unit extraction

A quarry price list commonly quotes each product twice — a price per m³ and a price per tonne — with a density column beside them. The two columns are the same money quoted two ways: price per m³ = price per tonne × density. They are one rate, not two. The extraction prompt instructs the model (and the region prompt carries the same rule) to emit one rate per product in this shape:

unit is tonne and costRate is the per-tonne price.
conversionFactor carries the density as written in the document (tonnes per m³).
The per-m³ price is derived (costRate × conversionFactor) and is never stored as a second rate.

The direction is not a preference. The rate calculator in stralos-domain applies volumeCubicMetres × density to convert a volume into tonnes before applying the per-tonne price. Storing the per-m³ price against the same conversionFactor would misprice by the density — typically 1.5× to 2.0× on quarry materials. Why the rule exists. unit is not part of the slot key ([identity, scope, supplierId, locationId, condition]). Emitting both price columns as separate rows therefore produces two rows for the same slot, and the commit refuses the entire file with a 422 duplicate-slot error. The model resolves the ambiguity by reading only the per-tonne column and carrying the density. Override of the cross-tab rule. The extraction prompt normally emits one rate per priced cell in a cross-tab. The dual-unit rule explicitly overrides this for quarry matrices where the cross-tab columns are the two price denominations rather than separate suppliers or locations. Single price column. If a document supplies a density but only one price column, the model still emits one rate and keeps the density in conversionFactor.

Price contract

Import payloads contain a cost rate only, and the catalogue is cost-only by design — there is no overhead, markup, finalCost, or finalRate column left to write. Project pricing owns every commercial adjustment. Each committed row becomes a fixed-basis rate option with explicit ownership: scope: "organisation" and no supplier id for the organisation’s own price, or scope: "supplier" with a Global party id for a supplier price. locationId is a real Global location or null for All locations. When set, an organisation-scoped location must have partyId: null; a supplier-scoped location must belong to that supplier. An unresolved or contradictory owner is reported rather than stored.

Category suggestions and material property enrichment

AI extraction can return category and material suggestions alongside the extracted rates. After deterministic extraction, a separate, narrower AI call supplies suggestions for unresolved categories and missing quarry-material properties — density and percentages. That call uses the extracted rows and any supplied file evidence; it does not repeat extraction or change prices. categorySuggestions.ts owns this logic. suggestCategoriesForExtractedRates sends only the pending rates — those with an unresolvable category, or a resolvable quarry category with missing physics fields — together with the organisation’s category catalogue. The model returns a suggestedCategory name and, for bulk material candidates, a suggestedMaterialProperties object with optional conversionFactor (density in t/m³), optional bulkingPercent, optional compactionPercent, and a required basis that describes the material assumption and states the numbers as typical estimates. checkedCategorySuggestions then validates each answer before it is carried forward:

suggestedCategory is kept only when it matches exactly one catalogue entry by name and the rate’s unit is in that category’s allowedUnitCodes. A name that is ambiguous, unknown, or whose allowed units exclude the rate’s unit is discarded.
suggestedMaterialProperties is kept only for rates whose resolved or suggested category has parentGroup === "quarry_materials". Properties explicitly supplied by the file — including zero — are never overwritten; only the absent fields are filled from the suggestion. For density, the suggestion is kept in suggestedMaterialProperties rather than promoted to the ordinary conversionFactor field, so the review screen can display it clearly before it is applied.
Rates already matched by name to an existing catalogue item are excluded from the suggestion call and property check. AI advice does not replace their stored category or physics. A reviewer can still explicitly choose a valid category through the existing recategorisation workflow.

If the model call fails, the extracted rates are returned unchanged with a warning: “AI suggestions could not be prepared. Choose the categories and check missing material properties for these rates.” A failed call never discards or blocks extracted rates. The category catalogue snapshot is size-guarded before being included in the prompt. When the serialised catalogue exceeds 100,000 characters, the snapshot is omitted and no suggestions are made. Both suggestedCategory and suggestedMaterialProperties are advisory extraction metadata:

The review screen uses a valid suggestedCategory only when the document category has no resolution or confirmed mapping, and places the chosen category in the ordinary category field before commit.
The review screen copies suggested density and percentages into missing ordinary fields only for new bulk-material items, preserving explicit file values, including zero. If the reviewer changes the category away from bulk materials, unchanged AI estimates are removed before submission.
The commit validates and stores the ordinary reviewed fields. It does not apply either suggestion object as a fallback, and it does not persist the basis explanation on the rate item. Existing items retain their stored physical properties.

The suggestion call does not run when the provider resolves to fixture.

Category resolution

The parse route fetches the caller’s rate category catalogue from the Global API and uses the import-only category-name arm of the shared catalogue resolver for each detected rate. A row whose category name cannot be resolved is reported in unresolvedCategories. A row whose category resolves but whose unit is not in that category’s allowedUnitCodes is reported in unresolvedUnits. Each review row carries a unitCode field: the server-resolved catalogue code for the row’s unit token. The review screen compares this against allowedUnitCodes to decide which categories a row may be mapped into, rather than comparing the document’s raw token. Without this, a category created with allowedUnitCodes: ["hour"] was filtered out of its own mapping combobox for rows whose document read hr — “No matches”, request never sent. The server publishes what it resolved so there is one alias table with one owner. A matched review row also carries existingOptionUnits: the unit tokens of every live option already on the matched rate item. The commit’s recategorisation check judges a category change against the whole item, not just the row being imported. A category that suits the row’s own unit can still be refused if a price the spreadsheet never mentions prices in a unit the new category does not allow. existingOptionUnits supplies data for a pre-commit unit check. The current frontend statusOf path bypasses that check for matched rows, so this field does not guarantee a visible warning. The refusal rule itself stays server-side; assertRecategorisationLicensed remains the authority, and a physics-licensing refusal (bulking, capacities, a factor a group forbids) still surfaces at commit. Both match sites populate existingOptionUnits from the shared optionUnitsByItem helper. /rate-imports/parse annotates matches inline; annotateExistingItems does it for the analysis path. Using one helper keeps the two review screens consistent — a second copy of the query would let them disagree about what a matched row is allowed to become. Rows that create a new item omit the field entirely. For project imports (requests carrying a projectId query parameter), a matched row’s category field is replaced with the matched item’s current catalogue category. The spreadsheet’s own category heading is discarded for that row. This keeps the review screen’s category control aligned with where the item actually lives in the catalogue, rather than reflecting a stale heading from the document. Matched rows also have suggestedCategory and suggestedMaterialProperties cleared, for both library and project imports. Rows with no match are unaffected and carry the document’s category text as extracted. The commit route fetches one catalogue snapshot before opening the transaction and accepts an optional categoryMappings array. Each entry supplies a from (category name) and toCategoryId (UUID). For each rate row the commit:

Checks categoryMappings for a matching from entry. If found, classifies using that category ID.
Otherwise attempts automatic resolution by category name.
If resolution fails for a row that would create an item, records that row as a gate failure. A matched row instead keeps its stored category when the document name is absent, unknown, or ambiguous.

The imported unit must be in the category that wins. A unit mismatch on a creating row is a pre-mutation gate failure. For a matched row, an unambiguous catalogue answer moves the existing item to that category inside the transaction. The move is refused if the target category does not license the incoming unit, a populated item-physics field, or any live price that would remain incompatible after the import. A price slot replaced by this import is checked using its new values, and multiplier dependants of a replaced Standard price inherit that replacement rather than blocking it. Before any mutation, the service runs the creating-row category-resolution gate in request order. The reviewer’s existingRateItemId choice is authoritative: a UUID treats the row as reuse, null forces creation (refused when the name is already in use), and omission uses the two-arm default match (option (supplierId, sourceItemCode, normalised name) first, then normalised name). A null supplier id is the organisation pricing group. If the default match finds no item and the row cannot create one safely, the transaction is refused and nothing commits. Rows that the gate identifies as genuinely creating must resolve to a compatible category before any mutation starts; matched-row recategorisation and its whole-item compatibility checks run in the same atomic transaction. Classification is never trusted from the spreadsheet itself. The AI model is not asked to classify rate groups; an explicit rateGroup field in the spreadsheet is ignored at commit. A newly created item receives its group from the resolved catalogue category.

File service

The upload path delegates file handling to stralos-api-rate-extract. POST /api/rates/rate-imports/uploads calls that service to obtain a presigned PUT URL; the browser uploads directly to S3. A subsequent parse call instructs the service to ingest the uploaded file and return its sheet and row content. The file service URL and the shared secret that guards it resolve in this order:

Variable	Purpose	Resolution order
`RATE_EXTRACT_URL`	File service base URL	Direct env var, then SSM parameter named by `RATE_EXTRACT_URL_SSM_PARAMETER`. With neither, the client throws a configuration error; there is no localhost fallback.
`RATE_EXTRACT_KEY`	Shared secret sent as `x-rate-extract-key`	Direct env var, then SSM SecureString named by `RATE_EXTRACT_KEY_SSM_PARAMETER`, then omitted

In deployed environments, neither RATE_EXTRACT_URL nor RATE_EXTRACT_KEY is set as a plain environment variable. The Lambda receives RATE_EXTRACT_URL_SSM_PARAMETER and RATE_EXTRACT_KEY_SSM_PARAMETER instead, and the client fetches both values from SSM at call time. The Terraform module grants the Lambda execution role read access to both parameters; the key is stored as a SecureString. The file service Function URL uses AUTH_TYPE NONE. The shared secret is the sole guard because this service cannot sign SigV4 requests — no AWS SDK can be added to this repository. The client treats a connection failure as 502 Bad Gateway with the message “The file service is unreachable; retry shortly.” A 422 from the file service (for example, a password-protected PDF) passes the service’s message through to the caller unchanged. Any other non-2xx response from the file service is also surfaced as 502.

Matching and atomic commit

Analysis provides a suggested existing item for review by matching on normalised name — the only dimension available before the reviewer has bound a source. The name uniqueness index (rates_rate_items_organisation_id_name_unique) guarantees at most one live item per normalised name, so the suggestion and the commit’s name-fallback arm always agree. At commit, a supplied UUID binds to that exact item, explicit null forces a new item, and an omitted choice uses the two-arm default. Binding controls item identity; it does not discard a valid category answer. A resolvable answer can recategorise the bound item, subject to the atomic compatibility checks described above. The primary default-match arm looks through current options for (supplierId, sourceItemCode, normalised name): the supplier’s own code for the row, scoped to that supplier so two suppliers reusing the same string do not collide. Organisation pricing forms the null-supplier group. An organisation-scope option stores no sourceItemCode or sourceItemName (the commit drops both for scope: "organisation" rows, and a database check constraint enforces it), so an organisation row reaches an existing item through the name arm. When that finds nothing (code-less rows, or no existing option carries that code for that owner), the fallback arm matches on normalised name against items directly. Because the name index is unique, both arms cannot disagree about which item they mean. Sending existingRateItemId: null for a name that is already in use is refused with 422. A rate item’s normalised name is its identity, and same-name rows are never auto-merged. The response names the conflicting rows and states the two remedies: point the row at the existing item, or give it a different name. A code is not itself a unique identifier: a real rate file may carry PM on Project Manager, Contract Manager, and Senior Project Manager. The primary arm’s (source, code) narrows candidates; the name discriminates among them. All selected rows are written in one organisation-scoped transaction. Any failure rolls back the complete request. The commit body can link the written options to the imported file. importFileId (both routes) must name a live kept file of the caller’s organisation and is stored as source_import_file_id. sourceDocumentId (project imports only) names the api-documents project document holding the file and is stored as source_document_id. When a field is absent, an updated option keeps the link it had.

Support alerts

Conditions the product absorbs but the team must be notified of emit a structured log line carrying the SUPPORT_ALERT marker. The first such condition is a workbook that exceeds the 20-sheet cap: truncating quietly would destroy the signal needed to decide whether to raise the cap. Every alert log line carries marker: "SUPPORT_ALERT" plus a stable kind field so a CloudWatch metric filter can select one kind rather than all alerts. The recommended filter pattern is:

{ $.marker = "SUPPORT_ALERT" }

Apply this filter to the service’s log group with an alarm that publishes to the existing support SNS topic. Until that filter exists, alerts are visible in logs only. The log line is the only delivery. The alert mechanism never throws: an undeliverable alert must not fail the import that triggered it. SUPPORT_EMAIL controls the recipient field on each alert. An unset SUPPORT_EMAIL does not silence the log line.

Parse job queue and worker

Parse jobs ride the same SQS queue as surveys and extraction blocks, distinguished by a kind: "parse" discriminator. They inherit the survey’s claim, heartbeat, and dead-letter machinery rather than introducing a second mechanism.

Storage

Parse results are stored in rates_rate_import_parses, a new table separate from rates_rate_import_surveys. Both tables share their operational columns (status, heartbeat, error columns, timestamps) but nothing else. A parse result is a static detection output; a survey result is a model answer resolved against a live catalogue on every poll. Combining the two would conflate records that have opposite re-run costs: a survey re-run spends a model call; a parse re-run is free.

`gridUsability`

gridUsability is read from ingest provenance, not from the block count. A Textract reconstruction of a scanned PDF can yield detectable blocks with correct numbers but approximate coordinates. Routing on block count would send those coordinates to the selection canvas, where a person would be asked to confirm geometry that is an OCR guess. The two values are:

Value	Meaning
`selectable`	Native spreadsheet grid. Coordinates are exact.
`reconstruction`	OCR-rebuilt grid. Coordinates are estimates from Textract and must not be presented for correction in the selection canvas. The current frontend sends PDFs straight to survey before parsing. A legacy or direct-API PDF parse may still report reconstruction; it must not open the selection canvas, and an older `?parse=` link asks the reviewer to re-upload for the whole-document flow.

Ingest provenance persistence

The worker persists ingest provenance (the gridUsability source) alongside the sheets at the instant both are known. Without stored provenance, a reclaimed parse could still run detection but could not describe whether the result is selectable or a reconstruction. Defaulting gridUsability to selectable on a reclaimed row would silently route a scanned PDF onto the selection canvas with unchecked coordinates. The same write records the kept file, when the file service reports one, and stores its id on the parse row as import_file_id. If neither an upload nor stored provenance is available (both are missing), the parse fails terminally rather than guessing.

Claim and heartbeat

A parse writes nothing between claiming and completing its ingest, so the longest legitimate silence is the full Textract wait. The worker heartbeats throughout ingest to distinguish a live worker from a crashed one. The heartbeat interval and the stale-claim window are kept in a verified invariant: the window must exceed the maximum latency of a single heartbeat (interval + beatTimeout) by a safe margin, and must stay below the SQS visibility timeout so the single redelivery can actually fire. A beat sets statement_timeout and lock_timeout inside its own transaction-local scope. This bounds worst-case staleness for a live worker to interval + beatTimeout rather than leaving the window open-ended. A single-flight guard on the heartbeat drops a tick that arrives while a beat is still open, rather than queuing it. A beat still open at the next tick means the database is under load; adding a connection at that moment would make things worse. Dropping costs nothing because a beat only moves a timestamp forward.

Stale-claim reclaim

A running parse whose heartbeat is older than the claim window can be reclaimed by a redelivered message. The redelivered worker re-runs detection against the already-persisted sheets and ingest provenance — it does not re-ingest. The upload was deleted on first read and is no longer available.

Dead-letter handling

The same worker drains the dead-letter queue. It marks a pending or running parse failed and emits a SUPPORT_ALERT record. A parse that is already complete is left unchanged; a healthy worker’s duplicate can reach the dead-letter queue at maxReceiveCount 2 while the parse is already done, and overwriting a completed result as failed would be wrong. The alert does not fire when nothing changed.

Error codes

A parse failure carries error.code and error.message. The code parse_failed is this service’s own code and means the worker threw or the file service was unreachable. Any other code comes from the file service and describes why it rejected the document. Both fields are clamped to 80 and 500 characters respectively on write and on read, so a long upstream string cannot wedge the row in running or make the poll answer 500.

Selection reuse

Confirmed-block analyses can preserve geometry when an organisation imports the same supplier’s workbook repeatedly. Region analyses do not save or apply these templates. A shape fingerprint is computed from the parse record before any model call. It uses the ordered, normalised sheet names and each sheet’s effective width, plus the normalised text of every proposed block’s header row; the block entries are sorted before hashing. Data values, row counts, header-row positions, and other block geometry are deliberately excluded, so a price-only re-export still matches.

How the offer is built

A completed parse poll response includes a suggestedSelection field. When the service finds a stored confirmed-block template whose fingerprint matches the incoming document, it returns the template; otherwise the field is null. Confirmed-block clients may present that offer. The current region frontend leaves it unused because a saved block has no rectangular column bounds, and survey responses do not carry suggestions. The offer contains:

Field	Type	Description
`selectionId`	UUID	Identifies the stored suggestion in the response. Applying it means submitting its `blocks` and `dismissedCells` through the analysis request; no request accepts `selectionId`.
`label`	`string \| null`	The file name from the import that created the template. Null when two differently-named files have committed over the same shape.
`sourceScope`	`"organisation" \| "supplier" \| null`	The retained source kind. `null` means no single source has been learned or the source became ambiguous.
`supplierId`	`UUID \| null`	The retained supplier for `sourceScope: "supplier"`. It is null for organisation pricing and when no unambiguous source is retained.
`lastUsedAt`	ISO 8601 datetime	When the stored selection was last committed.
`blocks`	array	The confirmed block geometry to pre-fill.
`dismissedCells`	array	Cells the reviewer dismissed on the prior import.

Label and source nullability

label and the source pair can be null independently. A confirmed-block client can resolve one nullable subject from sourceScope and supplierId, then the label, and combine that subject with the optional date:

Subject	Date	Sentence
Present	Present	This looks like the file you imported on 12 May 2026 (Fulton Hogan).
Present	Absent	This looks like a file you have imported before (Fulton Hogan).
Absent	Present	This looks like a selection you used on 12 May 2026.
Absent	Absent	This looks like a selection you have used before.

label becomes null permanently once two differently-named files have committed over the same shape. It cannot be re-learned from a later import because its null is unambiguous evidence that the shape is shared across documents. Source presence is determined by sourceScope, not supplierId: organisation pricing is a learned source with a null supplier id. A null scope is overloaded (a quarry matrix importing from fourteen suppliers also produces null), so the service uses a separate source_ambiguous marker — an internal column not present in the API response — to distinguish “never learned” from “cannot be learned”. The source_ambiguous marker is set only when a stored source pair and an incoming source pair are both present and different. Once set, both source fields are permanently null for that shape and are never re-learned, even if a later import has one source.

Fingerprint scope

Templates are organisation-scoped. Two organisations whose suppliers happen to use the same layout do not share templates. The fingerprint is versioned; a changed fingerprint algorithm never matches a row computed under an earlier version, so the failure mode is “no suggestion offered” rather than a mis-matched shape. A confirmed-block client may pre-fill its canvas from the reuse offer. It must still submit through the analysis path, where validateConfirmedSelection runs against the new grid every time. A stale template that no longer fits the document fails with a column-out-of-range or uncovered-cell error rather than being silently trimmed. The region canvas does not apply the offer.

Storage and commit

A selection is saved only after a successful commit whose analysisId identifies a confirmed-block analysis. The save runs after the rates transaction and does not turn an already successful import into a failure. Templates use a unique (organisation, fingerprint) key, so another confirmed-block import of the same shape updates the stored geometry instead of creating a duplicate. Region analyses are deliberately skipped; survey and legacy commits carry no analysisId and save no template.

Kept import files

Migration 0063 adds rates_rate_import_files, a tenant table with forced row-level security. It records each copy the file service kept after reading an upload: file_name, s3_key, content_type, size_bytes, sha256, uploaded_by, and soft-delete columns. Migration 0063 also adds import_file_id to the survey and parse tables and source_import_file_id to rates_rate_options. recordImportFile in src/imports/importFiles.ts inserts the row idempotently on (organisation_id, s3_key), so a redelivered message answers the row the first delivery wrote. The organisation purge deletes these rows through the platform role. src/routes/rateImportFiles.ts serves the two read routes that api-estimate’s Tender check uses to find and read the file a price came from, without copying it into the project’s documents:

POST /rate-import-files/lookup joins up to 2,000 options to their live kept files and returns one entry per file, sorted by name.
GET /rate-import-files/:importFileId/text returns the stored sheets of the file’s newest parse, or newest survey when there is no parse, as page text. No S3 read happens. Output stops at 300,000 characters (MAX_IMPORT_FILE_TEXT_CHARS) or 500 pages and sets truncated. A missing, deleted or other-organisation file returns 404.

Both routes run with the caller’s token, so RLS confines every read to the caller’s organisation. See Kept import files for the wire contract.

Error handling

Invalid request data returns 400.
A caller who is not a signed-in member or admin of the organisation receives 403.
On POST /rate-imports/entities, a create that api-global refuses comes back as a failed entry, not a failed request. code: "admin_required" means api-global keeps that create for admins (a new category, or one of the company’s own locations), and name is what to ask the admin to add. code: "location_address_required" means api-global refuses a location without an address and coordinates.
Database uniqueness or relationship conflicts return 409.
In-flight idempotency key on entities returns 409 with Retry-After: 2.
A Rates Library commit that sends sourceDocumentId returns 422 source_document_requires_project.
A commit whose importFileId is not a live file of the organisation returns 422 import_file_not_found.
A commit row with no locationId key returns 422 location_required, naming the first such row and its field. An explicit null (All locations) and a location UUID are both accepted; an absent key is never read as All locations.
Duplicate price slots, invalid mappings, unresolved creating rows, or an incompatible matched-item recategorisation return 422; no rows are written.
Confirmed-block selection violations return 422 with a machine-readable code field — see Selection validation for the full code list.
An invalid confirmed selection at job creation returns 422 with a machine-readable code (for example header_row_missing for a crosstab block with no header row). No blocks are queued.
An out-of-bounds region submitted to POST /rate-imports/analyses returns 422. An oversized region returns 422 with region_too_large. Supplying dismissedCells with a region is invalid request data and returns 400.
A region job whose stored geometry or stored rates cannot be deserialised returns 422 with analysis_region_unreadable or analysis_region_rates_unreadable. A region analysis that produced no rates returns 422 with analysis_region_no_rates. No job row is written in any of these cases.
Survey or extraction provider failures return 502 with a safe user-facing message.
Missing queue configuration on Lambda returns 502 when survey or extraction work cannot be dispatched.
Parse, survey, and job-create routes return 429 when the per-route rate limit is exceeded.
Starting a new parse, survey, analysis, or extraction job returns 429 rate_import_busy when the organisation already has two or more in-flight imports (see Bounded input).

Provider errors are logged at warning level. They do not expose workbook cells in the response. See Rates API for request and response fields and Managing the rates catalogue for the user workflow.

Async survey import

The survey route (POST /api/rates/rate-imports/survey) runs in two stages. The request creates the survey row and enqueues one message, then returns 202 with the surveyId. A separate Lambda worker drains the queue and does the AI extraction. This split exists because reading a scanned PDF goes to Textract and is polled to completion, which can take minutes. The request has a hard 29-second budget before API Gateway closes the connection, so documents that need OCR could never be ingested when reading happened on the request. The worker is not subject to that limit.

Upload reading

When the caller submits an uploadId, the survey row stores that id and the worker asks the file service to read the upload. The file service deletes the object after reading it, before its response returns. Once the worker receives that response, it persists the extracted sheets and clears the upload reference before starting the model run. For a PDF no larger than 3 MiB, the original bytes are available only in that invocation; Bedrock and Gemini receive the file directly. A larger PDF runs text-only with a warning. After the sheets have been persisted, a redelivery can continue from them but cannot reattach the original PDF. A failure after the file service deletes the object but before the worker persists those sheets cannot resume from either source. The kept file, when there is one, is recorded in the same write as the sheets. A caller that posts its sheets directly (legacy path) has nothing to read. The worker skips to the model call for those rows.

Timeout budgets

The presign and ingest calls use separate timeouts:

Call	Timeout
Presign (`POST /rate-extract/uploads`)	15 s
Ingest (`POST /rate-extract/ingest`)	600 s

The presign runs on the request, where waiting longer than API Gateway’s 29-second limit only turns a fast failure into a slow one. Ingest runs on the worker, where it can use the file service’s full ceiling.

Worker timeout

The worker Lambda timeout is configured by rate_import_worker_timeout (default 900 s). The minimum accepted value is 400 s. Values below 400 s can kill a model call that is still running; a worker that gives up first would re-drive work that was about to succeed.

Queue visibility

The queue’s visibility timeout is set to 2× the worker timeout. This is the stale-claim window: a redelivered message becomes visible exactly when the previous holder’s claim can be safely declared abandoned.

Block outcome logging

Every block outcome is logged when the worker finishes: success outcomes at info level, failure outcomes at error level. The failure arm is logged first so CloudWatch searches on error return results even when the row already holds the failure state.

Error handling for survey routes

POST /api/rates/rate-imports/survey returns 422 when the fixture provider is active (the fixture parser cannot handle cross-tab layouts or block boundaries).
If the enqueue step fails after the row is created, the route marks the survey failed and returns 502. The caller is not given a 202 for a survey nothing will advance. The failed-status write runs through runScoped, not the owner pool, because rates_rate_import_surveys uses FORCE ROW LEVEL SECURITY (migration 0026) and an unscoped write on the owner pool is RLS-rejected in every deployed environment. If that write itself fails, the error is logged; the caller still receives 502 either way.
Polling the survey via GET /api/rates/rate-imports/surveys/:surveyId returns 404 if the id is not found in the caller’s organisation.

​Import stages

​Survey flow

​Confirmed-block flow (legacy client)

​Analysis provider

​Provider concurrency

​Upload and file reading

​Block detection

​Survey and entity resolution (stage 2–3)

​Confirmed-block extraction

​Confirmed block schema

​Dismissed cells

​Selection validation (422 with a machine-readable code)

​Deterministic extraction for confirmed blocks

​Analysis step (selection-first and region flows)

​Confirmed-block path (blocks)

​Region path (region)

​Model output repair

​Analysis worker

​Job route discriminator

​Error handling for analysis routes

​Extraction jobs

​Job dispatch and extraction worker

​Bounded input

​Banded rates

​Quarry dual-unit extraction

​Price contract

​Category suggestions and material property enrichment

​Category resolution

​File service

​Matching and atomic commit

​Support alerts

​Parse job queue and worker

​Storage

​gridUsability

​Ingest provenance persistence

​Claim and heartbeat

​Stale-claim reclaim

​Dead-letter handling

​Error codes

​Selection reuse

​How the offer is built

​Label and source nullability

​Fingerprint scope

​Storage and commit

​Kept import files

​Error handling

​Async survey import

​Upload reading

​Timeout budgets

​Worker timeout

​Queue visibility

​Block outcome logging

​Error handling for survey routes

Import stages

Survey flow

Confirmed-block flow (legacy client)

Analysis provider

Provider concurrency

Upload and file reading

Block detection

Survey and entity resolution (stage 2–3)

Confirmed-block extraction

Confirmed block schema

Dismissed cells

Selection validation (422 with a machine-readable code)

Deterministic extraction for confirmed blocks

Analysis step (selection-first and region flows)

Confirmed-block path (`blocks`)

Region path (`region`)

Model output repair

Analysis worker

Job route discriminator

Error handling for analysis routes

Extraction jobs

Job dispatch and extraction worker

Bounded input

Banded rates

Quarry dual-unit extraction

Price contract

Category suggestions and material property enrichment

Category resolution

File service

Matching and atomic commit

Support alerts

Parse job queue and worker

Storage

`gridUsability`

Ingest provenance persistence

Claim and heartbeat

Stale-claim reclaim

Dead-letter handling

Error codes

Selection reuse

How the offer is built

Label and source nullability

Fingerprint scope

Storage and commit

Kept import files

Error handling

Async survey import

Upload reading

Timeout budgets

Worker timeout

Queue visibility

Block outcome logging

Error handling for survey routes