# Dated conversion-rate seed — application runbook

142 currency period rows for `Dated_Conversion_Rate__c`, mirrored from
`deployments/offline/runtime/reference-data/dated-conversion-rate.json`
(regenerate this bundle whenever that file changes).

## Where each environment gets this data

| Environment | Path |
| --- | --- |
| local / test / watchdog / exp / staging | `pack_reference_data` seed module (automatic at deploy) |
| staging (bundle rehearsal) | `--patch-dir docs/migration/patch-bundles/20260730_dated_conversion_rate_seed` on a train **after** the object exists |
| production | **operator-run**, see below |

## Production application

Ordinary production promotion is runtime-only: no pack apply, no `--patch-dir`,
no seed dispatcher. Production data patches reach production the same way every
prior data bundle did — an operator session against the production database
executing `001_seed_dated_conversion_rate.sql`, after the release train that
creates the `Dated_Conversion_Rate__c` table has been promoted. That single
file carries its own preflight (see below), so the hand-run path is guarded
without depending on the patch-bundle driver. (A governed,
non-manual production reference-data channel is a platform gap tracked
separately; until it exists, this runbook is the delivery contract.)

Properties that make the hand-run safe:

- **Fail-closed**: if the table does not exist the statement errors and nothing
  is partially applied — apply after the object's train.
- **Idempotent**: keyed on the same stable UUIDs the seed module derives;
  re-running updates rates in place and never duplicates rows.
- **Never revives, and refuses rather than writing blind**: a row deleted
  through the UI stays deleted — the upsert leaves `IsDeleted` alone, matching
  the `pack_reference_data` applier. Because that makes a collision dangerous
  instead of merely surprising, `001` **opens with a fail-closed preflight**
  that aborts before its own `INSERT` if a seeded `Name` or `Id` sits on a
  soft-deleted row. The guard is inside `001` rather than a sibling file
  precisely because production runs this file by hand: a `000_`-style guard
  would sit on the `--patch-dir` path only, which production does not use. It
  checks both routes — a row pre-dating the seed collides on `Name`, while a
  row whose `Name` was edited before deletion still collides on the seed `Id`.
  The abort surfaces as `ERROR 1242 ... Subquery returns more than 1 row`, and
  `mysql` stops at the first error so nothing below it runs; the preceding
  statement lists the offending ids. Restore them, or hard-delete them if the
  retirement was intended, then re-run.
- **Verification**: `SELECT Iso_Code__c, COUNT(*) FROM Dated_Conversion_Rate__c
  WHERE COALESCE(IsDeleted, 0) = 0 GROUP BY Iso_Code__c` must return CNY 47,
  JPY 47, SGD 47, USD 1. The `IsDeleted` filter is load-bearing now that the
  bundle no longer revives: without it a soft-deleted row still counts toward
  47 and the check passes while a live period is missing.

Until the rows are applied, the flow is inert for non-USD records (empty
lookup leaves `Rate__c` untouched — the pre-existing state); USD needs no data.

## Ongoing maintenance

Each month, close the current open-ended period (set its `Next_Start_Date__c`
to the new month's start) and append the new row — the same invariant the
source platform maintains automatically. The consuming flow tolerates a missed
close-out deterministically (newest applicable period wins), but keep the
table non-overlapping. The open-ended latest period is by design: it is
exactly how the source system's dated-rate table behaves between monthly
updates — the latest rate applies forward until finance adds the next period.