Skip to main content
stralos-api-rates owns the day rate schedule resource. This page covers its storage model, mutation rules, margin calculation, and database guards. See Rates API — Day rate schedules for the full HTTP contract.

​
Storage model

Three tables form the hierarchy:

​
Schedules

rates_day_rate_schedules is the long-lived identity. It holds name, client_id, contract_ref, line_authority, and lifecycle status. A partial index makes the name unique among an organisation’s active schedules; an archived name can be reused. The line_authority column (internal | client_defined) is snapshotted into every revision at cut time. The product’s DELETE route is soft deletion: it changes status to archived and preserves the contractual tree, pricing rules, mappings, and existing base pins. Restore returns the schedule to active, subject to active-name uniqueness.

​
Revisions

rates_day_rate_schedule_revisions owns one version of the commercial document. Fields of note:
  • revision_number — server-assigned, monotonically increasing per schedule.
  • status — enforced by a partial unique index: at most one draft and at most one current per schedule.
  • effective_from / effective_to — validated by a database check (effective_to >= effective_from) and by an overlap query at publish time.
  • base_revision_id / adjustment_pct — set when the revision inherits from another schedule’s revision. The pair is all-or-nothing: a check constraint ensures both are present or both are absent.
  • base_ack_revision_id — newer published base revision the user has reviewed without rebasing. It is analysis state and may move on a published revision through the validated acknowledgement route.
  • Snapshot columns (name_snapshot, client_id_snapshot, contract_ref_snapshot, client_name_snapshot, org_name_snapshot, org_logo_ref, line_authority_snapshot) — copied from the schedule identity and identity-service lookups when the revision is created. No ordinary update route changes them.

​
Lines

rates_day_rate_schedule_lines stores each contractual line. Fields of note:
  • line_key — a stable UUID that identifies the same-kind line across revision cuts. Changing rate_kind mints a new key so inheritance sees a remove and add rather than conflating different commercial meanings.
  • line_code — nullable, unique per (organisation_id, revision_id) when set. Enforced by a partial unique index.
  • rate_kind — flat | cost_plus | fixed. A check constraint enforces the pairing with day_rate: flat/fixed require a non-null rate; cost_plus requires a null rate.
  • rate_option_id / activity_id — optional internal mapping pointers. A check constraint prevents both being set simultaneously.
  • derived_from_line_id — points to the published base-schedule line from which an inherited line was materialised. An ordinary same-schedule cut preserves that provenance instead of repointing it to the immediate copy. A referential FK clears the pointer (SET NULL) when the base line is deleted.
  • rate_overridden — set to true when an admin manually changes a derived line’s rate rather than inheriting the parent value with the revision adjustment.

​
Project day-rate set

Each project carries a day-rate set: the schedules it uses for build-up pricing, each pinned to one published revision. The set is stored in rates_project_day_rate_schedules (migration 0061).

​
Storage model

One live row per schedule the project uses, uniquely indexed on (organisation_id, project_id, schedule_id) where removed_at IS NULL. Removal is a soft delete; a removed row still counts as “the set exists”, preventing init from ever re-adding a schedule that a user chose to remove. Key columns: rates_day_rate_set_release is a singleton table written once at migration time. It records when the project day-rate set feature was released in this environment.

​
Set initialisation

PUT /projects/:projectId/day-rate-set/init builds the set for a project that has none. It pins every active organisation schedule and every active schedule of the project’s client, each to its revision in force on the project’s start date (origin: auto). The start date is the project’s creation day in its configured time zone. For projects created before migration 0061 ran (before rates_day_rate_set_release.released_at), the start date is the day of the first init call instead, so no schedule published after the project started is silently excluded. The operation is idempotent: once any row exists for the project (including removed rows), nothing is written. An advisory lock (stralos:day-rate-set:<projectId>) serialises concurrent inits. A member sends no body and api-rates reads the project from api-global with their bearer. The estimate-rates service login sends {clientId, createdAt, timeZone?} because it has no user bearer to read with.

​
Revision in force

A revision is in force on date D when it is published (status current or superseded) and its effective date interval covers D:
A current revision with a future effectiveFrom is not yet in force; the superseded revision it will replace still covers dates before it.

​
Set mutations

​
Service reads (estimate-rates login)

Two routes are restricted to the estimate-rates service login:
  • GET /day-rate-set/projects?scheduleId=<id> — which projects hold a schedule, with each pinned revision. The update dialog uses this to list in-progress estimates.
  • POST /day-rate-set/move-pins — moves up to 200 projects’ pin for a schedule to a different published revision. Idempotent per project; reports moved, unchanged, or not_in_set. A draft revision returns 422 day_rate_revision_not_published.
The set readers bypass the project_membership RLS policy through a security-definer function (rates_project_day_rate_set_holders), which confines the read to the tenant’s rows under FORCE ROW LEVEL SECURITY.

​
Atomic schedule edit

PUT /day-rate-schedules/:scheduleId/revisions/:revisionId/edit is the schedule page’s single save. The whole body commits in one transaction or nothing is saved. Fields editable on any revision:
  • schedule: name, client, contract reference, line wording.
  • mappings: rate option or activity per line. Neither id clears an existing mapping.
Fields editable on a draft only:
  • revision: effective dates, terms text.
  • lines.add / lines.update / lines.delete: up to 500 entries each.
  • costPlusPercent: per-line cost-plus percentage written as a base pricing rule.
Each line’s base pricing rule is written from its kind as part of every save: a Fixed line receives the fixed base rule; a Cost-plus line receives a cost-plus rule from its percentage. Callers do not manage rules directly. Concurrency token: updatedAt in the request must equal the later of the schedule’s and the revision’s updatedAt at load time, or the updatedAt returned by the previous save. A change since that token → 409 day_rate_revision_changed. Invalid input → 422 day_rate_edit_invalid with one issue per offending field (path such as lines.add[2].dayRate); nothing is saved. The response carries the saved schedule identity, revision, and lines, plus the next updatedAt to use as the token on the following save.

​
Database guards

​
Revision immutability trigger

rates_guard_day_rate_revision_update() runs BEFORE UPDATE on rates_day_rate_schedule_revisions. It rejects any draft-to-draft update that touches fields outside effective_from, effective_to, terms_text, updated_at, and the five identity snapshot columns (name_snapshot, client_id_snapshot, client_name_snapshot, contract_ref_snapshot, line_authority_snapshot). It permits only the controlled publish/supersede transitions after that. The one non-contractual exception is a validated base_ack_revision_id update, which may acknowledge a published revision on the same pinned base schedule. Migration 0061 widened the draft arm to also allow updates to the five identity snapshot columns. This means a schedule identity edit (name, client, contract reference, line wording) now also refreshes the snapshots on the schedule’s current draft, so the draft shows what it will be issued under. Published revisions keep their original snapshots unchanged.

​
Line immutability trigger

rates_guard_day_rate_line_write() runs BEFORE INSERT, BEFORE UPDATE, and BEFORE DELETE on rates_day_rate_schedule_lines. It:
  1. Skips updates that change only rate_option_id or activity_id — these analysis mappings stay mutable after publication.
  2. Allows a referential-action SET NULL on derived_from_line_id from a nested trigger (depth > 1) without a revision status check.
  3. For all other writes, fetches the owning revision’s status under a FOR UPDATE lock and rejects the write with SQLSTATE 55000 when the revision is not a draft.
For a draft update, the trigger also prevents ownership or provenance changes, manages rate_overridden, and mints line_key when rate_kind changes. Runtime callers cannot write the server-managed fields directly. The FOR UPDATE on the revision inside the trigger closes the edit-vs-publish race at the database layer without requiring the application to hold a separate advisory lock for line mutations (though the publish path does hold one for the schedule).

​
Revision lifecycle

POST .../publish runs inside an advisory lock on the schedule (pg_advisory_xact_lock on the schedule id hash). Within that lock it:
  1. Checks the draft’s effectiveFrom is strictly later than the current revision’s effectiveFrom.
  2. Runs an overlap query against all published revisions for the schedule (excluding the current one, whose interval is about to close).
  3. Calls rates_publish_day_rate_revision(org, draft_id, user_id) — a stored function that performs the status transition atomically.

​
Revision cut and content copy

POST /day-rate-schedules/:id/revisions is the only way to create a revision after the first. The source must be a published revision. The service:
  1. Resolves fresh identity snapshots from the identity service for the schedule’s current clientId.
  2. Acquires the advisory lock on the schedule to prevent concurrent cuts.
  3. Verifies no draft already exists.
  4. Inserts the new revision row.
  5. Copies all lines while preserving line_key, line_code, description, unit, rate_kind, day_rate, rate_option_id, activity_id, derived_from_line_id, rate_overridden, and sort_order. Source rows are locked so the copy is consistent with the immutability trigger.
  6. Copies revision- and line-owned pricing rules through the shared rule-copy primitive, recording copy origins and local override markers for later three-way rebases.

​
Margin calculation

GET /day-rate-schedule-revisions/:id/margin is a read-only endpoint available to all authenticated org members. It computes expectedCost, sellRate, marginPct, and unavailableReason per line on the fly — nothing is persisted.

​
Rate option cost path

For lines mapped to a rateOptionId:
  1. Load the selected option and all standard-condition options sharing the same (rateItemId, scope, supplierId, locationId) combination.
  2. Determine whether either the selected or standard option has a tiered or matrix pricing rule via rates_pricing_rules. If so, return rate_option_rule_requires_inputs — the rule requires input quantities not available in day-rate context.
  3. Call normalisedCostRate(option, item, lineUnit) from @stralos-co-nz/domain for the Standard option, passing the schedule line’s own unit as the explicit conversion target.
  4. Round to two decimal places with half-up rounding.
The schedule line’s unit is the explicit conversion target. When an option is priced in another unit, the margin path converts directly from the option’s unit to the line’s unit within the known unit families. When the domain cannot perform the conversion (for example a quarry option missing its density), normalisedCostRate returns an exclusion and the margin endpoint reports unit_conversion_unavailable. The equal-unit case is an identity conversion and is unaffected. The dimension key rateItemId:scope:supplierId:locationId is used to match the standard condition option, so a non-standard condition option (e.g. a discount tier) maps through its corresponding standard for cost resolution.

​
Activity cost path

For lines mapped to an activityId, the margin endpoint calls the Activities API with concurrency capped at eight simultaneous requests. A 404 from the Activities API is treated as activity_unavailable rather than a request-level error — a mapping can outlive its activity.

​
Cost-plus sell rate

For cost_plus lines, the sell rate is:
The percent is read from rates_pricing_rules where owner_type is day_rate_schedule_line (line-level override) or day_rate_schedule_revision (revision-level default), slot is base, condition is standard, and rule_kind is cost_plus. Line-level takes precedence over revision-level. The table is probed for existence first because it belongs to a later migration phase; a missing table returns an empty result rather than an error.

​
Margin formula

Rounded to one decimal place using integer arithmetic on cent values. Returns null when sellRate is zero (sell_rate_zero).

​
Tenant isolation

All three tables use ENABLE ROW LEVEL SECURITY with FORCE ROW LEVEL SECURITY. The tenant_isolation policy restricts all reads and writes to rows whose organisation_id matches current_setting('app.current_org'). Composite foreign keys (revision_id, organisation_id on lines; schedule_id, organisation_id on revisions) prevent cross-org row binding even when RLS is bypassed by a superuser or maintenance role.