> ## 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.

# Day rate schedules service

> Storage, lifecycle, mapping, inheritance, and live-margin implementation in the Rates service

`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](https://help.stralos.app/api/rates.md#day-rate-schedules) for the full HTTP contract.

## Storage model

Three tables form the hierarchy:

```
rates_day_rate_schedules          (identity)
  └── rates_day_rate_schedule_revisions  (versioned commercial document)
        └── rates_day_rate_schedule_lines  (contractual line items)
```

### 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:

| Column | Purpose |
| - | - |
| `schedule_id` | The schedule in the set. Composite FK onto the new `(id, schedule_id, organisation_id)` unique of `rates_day_rate_schedule_revisions`. |
| `pinned_revision_id` | The published revision this project is pinned to. A database trigger (`rates_project_day_rate_pin_guard`) rejects a draft pin with `SQLSTATE 23514`, constraint `rates_project_day_rate_schedules_published_pin`. |
| `origin` | `auto` (added by init) or `added` (added manually by a member). |
| `added_by` | `NULL` when api-estimate's `estimate-rates` service login initialised the set. |
| `removed_at` / `removed_by` | Soft-delete pair, guarded by a check constraint. |

`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:

```text theme={null}
effectiveFrom <= D  AND  (effectiveTo IS NULL OR effectiveTo >= 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

| Route | Who | Effect |
| - | - | - |
| `GET /projects/:projectId/day-rate-set` | Any project member | Lists the live set, initialising it first when no row exists. Archived schedules stay in the set with `status: archived`. |
| `POST /projects/:projectId/day-rate-set` | Any project member | Adds a schedule pinned to its revision in force today (`origin: added`). Restores a removed schedule rather than duplicating it. 409 `day_rate_schedule_already_in_set`; 422 `day_rate_schedule_archived` / `day_rate_schedule_not_in_force`. |
| `DELETE /projects/:projectId/day-rate-set/:scheduleId` | Any project member | Soft-removes the schedule. Existing build-up lines keep their rates; init never re-adds it. |

### 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

```
draft ──publish──▶ current ──superseded by next publish──▶ superseded
```

`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:

```text theme={null}
sellRate = round_half_up_to_cents(expectedCost × (1 + percent / 100))
```

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

```text theme={null}
marginPct = (sellRate − expectedCost) / sellRate × 100
```

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.

## Related pages

* [Rates API](https://help.stralos.app/api/rates.md) — full HTTP contract including day rate schedules
* [Rate import service](https://help.stralos.app/internal/rates-import-service.md) — spreadsheet import implementation
* [Pricing rules service](https://help.stralos.app/internal/pricing-rules-service.md) — base-only rule enforcement for day-rate owners
* [Project rates service](https://help.stralos.app/internal/project-rates-service.md) — project-scoped rate options


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