> ## Documentation Index
> Fetch the complete documentation index at: https://help.stralos.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Rate import service

> Backend contract for AI-assisted rate spreadsheet imports

`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](#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.

> [!NOTE]
>
> 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](#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](https://help.stralos.app/api/rates.md#rate-options)** 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:

1. Checks `categoryMappings` for a matching `from` entry. If found, classifies using that category ID.
2. Otherwise attempts automatic resolution by category name.
3. 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](https://help.stralos.app/api/rates.md#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](#selection-validation-422-with-a-machine-readable-code) 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](#bounded-input)).

Provider errors are logged at warning level. They do not expose workbook cells in the response.

See [Rates API](https://help.stralos.app/api/rates.md#rate-imports) for request and response fields and [Managing the rates catalogue](https://help.stralos.app/guides/admins/rates-catalogue.md#import-rates-from-a-file) 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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.