# Design - EXP Bulk Baseline Seeding

**Status:** Historical implementation record; the current contract is
`docs/deploy-seed-contract.md`.
**Date:** 2026-06-13
**Scope:** EXP deploy seed apply path, bulk-eligible seed records, DB-native idempotent writes, and verification gates.
**Related:** PR #2859, `deployments/offline/runtime/seeds/exp-baseline.seed.yaml`, `deployments/offline/runtime/seeds/deploy-baseline.seed.yaml`, `deployments/offline/runtime/deploy_offline_env.py`, `scripts/apply_exp_bulk_baseline.sh`.

**Milestone:** EXP deploy seeding must stop spending Connected App API budget on baseline volume. The whole #2859-scale EXP seed apply must complete in sub-second time on an already-ready TiDB/backend deployment.

**Current implementation note (2026-07-18):** the baseline planner now has
complete coverage for both reviewed baseline sources. Deployment is bulk-only
and fails before database mutation when coverage is incomplete. REST fallback
and runtime-state handoff references in the historical rollout sections below
are not part of the current apply path.

## 0. Bottom Line

PR #2859 made the right data durable, but it pushed high-volume baseline data through the wrong apply mechanism.

The durable seed authority stays the tracked YAML. The apply path changes:

```text
seed YAML -> bulk seed planner -> complete-coverage preflight -> parallel DB upsert/verify
```

Unsupported or uncovered baseline records fail the apply before any DB write.
The API Seed runner remains available for separately invoked run-specific or
API-semantics-sensitive setup; it is not a deploy-baseline fallback.

The EXP deploy path now uses the same bulk-only helper for:

1. `deployments/offline/runtime/seeds/exp-baseline.seed.yaml`
2. `deployments/offline/runtime/seeds/deploy-baseline.seed.yaml`, when EXP asks for the shared deploy baseline

The helper requires complete bulk coverage before applying the plan. Runtime
state is retained for evidence and adapter normalization, not REST handoff.

The target contract is:

1. no per-record SOQL existence query for bulk-eligible EXP records
2. no per-record REST create/update for bulk-covered EXP records
3. no database mutation when the bulk summary has unsupported or uncovered rows
4. one set-oriented pre-resolve query per object/match group, executed in parallel
5. one set-oriented DB upsert per object, executed in parallel where dependencies allow
6. one set-oriented verification query per object, executed in parallel

## 1. Governing Principle

This repo owns PingCAP downstream seed content and deploy orchestration. It does not own generic runtime behavior.

The seed contract follows the existing downstream rule:

```text
tracked YAML owns seed intent
DB-native loader owns high-volume deploy materialization
API Seed runner owns separately invoked run-specific API-semantic records
incomplete deploy-baseline coverage fails before mutation
live staging exports are not deploy-time authority
```

One fact must have one durable source:

| Fact | Durable authority | Not authority |
|---|---|---|
| EXP baseline records | `deployments/offline/runtime/seeds/exp-baseline.seed.yaml` | ad hoc SQL values embedded in deploy scripts |
| record identity | deterministic seed key plus existing match pre-resolution | generated IDs captured from one environment |
| bulk apply eligibility | planner allowlist, table/column validation, and explicit unsupported list | row count accidents in a shell script |
| write result | DB loader summary plus set-oriented verification | partial bulk output emitted before coverage preflight |
| performance claim | timed local/EXP loader summary | anecdotal deploy duration |

## 2. Historical Starting State

Before this design, EXP deploy applied its baseline through the shared watchdog REST seed path:

```text
deploy_offline_env.py apply_exp_baseline_seed
  -> apply_seed_file
  -> scripts/apply_watchdog_seed.sh (retired)
  -> watchdog/runner/src/apply-seed.mjs
```

`apply-seed.mjs` is serial by design:

1. collapse duplicate record definitions by object and match
2. for each remaining record, issue a SOQL query by deterministic `match`
3. compare fields
4. issue a REST `PATCH` or `POST` when needed
5. update in-memory `record.*.id` context for later records

That is correct for a small relationship-aware seed. It is structurally wrong for hundreds of table-backed baseline rows.

PR #2859 added 506 raw EXP baseline records:

| Object | Count | Match key | Purpose |
|---|---:|---|---|
| `Partner_Region_Owner_Rule__c` | 6 | `Region__c` | owner lookup rows for `Lead_Assignment_Rule_modification_for_NA_EMEA` |
| `Countries_Region__c` | 250 | `Name` | country code to region mapping |
| `Country_Dictionary__c` | 250 | `Name` | country code to full name mapping |

The latest EXP run reported 724 applied records after duplicate collapse and failed near `Country_Dictionary__c` record 719/724 after hitting the Connected App hourly API limit. Given the current runner shape, that is roughly 1,400+ API calls for a successful run: one existence query plus one create/update per changed record, before login/preflight and any retries.

This repo already had a partial DB loader:

```text
scripts/apply_watchdog_loader_baseline.sh
  -> watchdog/runner/src/render-loader-baseline.mjs
  -> scripts/salesforce_migration/load_salesforce_snapshot_to_tidb.py
```

That path renders selected seed records into snapshot CSV files and loads them into TiDB with `LOAD DATA LOCAL INFILE`. It is scoped to shared watchdog objects such as `Account`, `Lead`, `Product2`, and `PricebookEntry`. The EXP seed path no longer depends on that loader. It uses a planner that reads the tracked seed YAML, requires complete coverage before mutation, and writes table-backed records directly with idempotent upserts.

## 3. Current Bulk Architecture

EXP uses one DB-native, bulk-only baseline phase:

```text
deploy_offline_env.py
  -> apply_seed_with_bulk(...)
  -> render bulk seed plan from seed YAML
  -> require complete coverage
  -> execute parallel DB bulk apply
  -> write bulk runtime state JSON
  -> verify EXP seed contract
```

The bulk phase consumes the same YAML records as the REST runner. It does not introduce a second SQL source file for dictionary values.

### 3.1 Bulk Eligibility

A record is bulk-eligible when all of these are true:

1. `object` maps to a normal table-backed sObject table in the deployed TiDB database
2. `match` is deterministic and materializes without calling REST
3. `fields` are direct writable columns or accepted loader-managed columns
4. every `{{ record.*.id }}` reference can be resolved from an earlier bulk plan row, an earlier REST-only row already loaded into base runtime state, or a set-oriented DB pre-resolve query
5. the record does not require `create_via_standard_action`
6. the record does not require a REST-side behavior trigger that the DB loader cannot reproduce

Implemented EXP bulk object allowlist for the #2859 baseline shape:

| Object | Disposition | Reason |
|---|---|---|
| `Account`, `Contact`, `Lead` | bulk | table-backed seed actors and sample business rows |
| `Campaign`, `CampaignMemberStatus` | bulk | table-backed campaign fixtures; member statuses layer after campaign IDs resolve |
| `Pricebook2`, `Product2`, `PricebookEntry` | bulk | product/catalog fixtures; pricebook entries layer after product and pricebook IDs resolve |
| `UserRole`, `PermissionSet`, `User`, `PermissionSetAssignment` | bulk | table-backed EXP identity fixtures; user passwords are normalized by DB hash copy plus the existing deploy alignment step |
| `ProcessDefinition`, `ProcessNode`, `ProcessAction`, `Quote`, `ProcessInstance`, `ProcessInstanceNode` | bulk | table-backed approval/process fixtures used by checked-in EXP baseline seed |
| `Report`, `Dashboard`, `_System_ConnectedApp`, `HubSpotOwnerBinding__c` | bulk | table-backed baseline metadata rows used by EXP deploy |
| `Country_Dictionary__c` | bulk | static dictionary rows; this includes the previous failure point at `YE` |
| `Countries_Region__c` | bulk | static dictionary rows |
| `Partner_Region_Owner_Rule__c` | bulk after user IDs are resolved | six stable routing rows, owner IDs reference seeded users |

The allowlist can expand only by adding planner tests and a deploy verification gate for the new object. The planner reports unsupported selected objects and seed aliases outside the selected object set. Either condition makes coverage incomplete and aborts apply before `apply_plan` can write rows.

### 3.2 DB Read/Write Path

The bulk planner builds one object plan per object:

1. materialize templates from YAML
2. collapse duplicate definitions with the same object/match signature
3. resolve target table and loadable columns from runtime schema/config
4. pre-resolve existing records in set form
5. assign each row an `Id`
6. write rows to per-object stage data
7. execute the object write
8. verify row presence and expected field values

Existing rows are resolved without per-record SOQL:

```text
object plan -> temporary match stage -> SELECT target.Id, stage.seed_key ...
```

For single-field matches such as `Name`, the planner may use `WHERE Name IN (...)`. For composite matches, it must use a temporary stage table and a join. Duplicate existing matches fail closed.

Writes use deterministic `Id` and set-oriented upsert:

```text
INSERT INTO target_table (...)
SELECT ... FROM seed_stage
ON DUPLICATE KEY UPDATE
  field_a = VALUES(field_a),
  field_b = VALUES(field_b),
  LastModifiedDate = CURRENT_TIMESTAMP
```

The update side is sparse and seed-field scoped. Insert batches may include
loader-managed defaults required for new rows, but duplicate updates only write
columns that came from the seed record's `match` or `fields` payload, excluding
immutable create metadata. Rows with different specified field shapes are split
into separate bulk statements so an omitted field is not represented as SQL
`NULL` for an existing row. Explicit seed `null` remains explicit and is still
written.

The implementation may reuse the existing snapshot loader when it can express this contract. If the current snapshot loader's `REPLACE` semantics would delete/reinsert rows or hide match conflicts, EXP bulk seeding must add an upsert strategy instead of relying on `REPLACE` by default.

### 3.3 Parallelism Contract

Parallelism is object-level by default:

```text
schema/match pre-resolve: parallel per object
stage writes: parallel per object
target upserts: parallel per dependency layer
verification: parallel per object
```

Dependencies create layers, not a serial record loop. For example:

```text
Layer 0: users and identity prerequisites
Layer 1: country dictionaries, country-region mappings, product/catalog records
Layer 2: partner region owner rules that reference user IDs
Layer 3: later bulk records that reference earlier bulk-loaded rows
```

The planner must emit a dependency graph in its summary so reviewers can tell why an object was serialized.

### 3.4 Runtime State Contract

The bulk phase writes a runtime state file for evidence and downstream adapter
normalization:

```json
{
  "records": {
    "country_dictionary_US": {
      "id": "deterministic-or-existing-id",
      "object": "Country_Dictionary__c",
      "Name": "US",
      "Full_Name__c": "United States"
    }
  }
}
```

The deploy baseline path does not pass this state to an API fallback. A separate
run-specific API Seed invocation owns its own runtime context.

## 4. Key Decisions

### D-1. YAML remains the only seed data source

The bulk loader renders from `exp-baseline.seed.yaml`. It must not copy country dictionaries, region owner mappings, or catalog values into standalone SQL files.

Consequence: PR review still sees one declarative data contract, and the DB loader cannot drift into a hidden second seed authority.

### D-2. Bulk records use DB-native idempotency, not REST idempotency

The REST runner's idempotency is query-then-patch per record. EXP bulk idempotency is set-oriented:

1. pre-resolve existing IDs by match in one DB query per object/match group
2. assign deterministic IDs only for rows not already present
3. upsert by `Id`
4. fail on duplicate match rows before writing

Consequence: rerunning the same EXP deploy patches rows without duplicates, but it does not spend API quota to prove that property.

### D-3. `record.*.id` references must be resolved before apply

Bulk-loaded rows populate the runtime state used by dependency planning and
evidence. Later bulk records can reference `{{ record.some_key.id }}` without
depending on generated environment-specific IDs.

Consequence: the apply surface changes, but the seed authoring model does not.

### D-4. Bulk eligibility is explicit and test-gated

No object moves to DB bulk just because it has many rows. It moves when the planner proves table mapping, columns, match uniqueness, reference resolution, and verification.

Consequence: identity/security/system seed rows do not move by accident. If they are admitted, they must be explicit in the allowlist and covered by password normalization, match-resolution, and deploy verification gates.

### D-5. Performance is a release gate, not a hope

For the #2859-scale EXP seed:

1. bulk DB write phase target: `<= 1000 ms`
2. post-bulk verification target: `<= 1000 ms`
3. REST calls for bulk objects: `0`
4. REST SOQL hydration for bulk objects: `0`
5. total Connected App API calls during EXP seed: bounded by REST-only records, not by total baseline row count

The deploy summary must print these numbers.

## 5. Historical Implementation Runway

### Phase 1 - Bulk planner and state handoff

Deliverables:

1. add an EXP bulk seed renderer/planner, either by extending `render-loader-baseline.mjs` or by adding a sibling renderer
2. emit:
   - bulk plan JSON
   - stage/snapshot files or direct object write batches
   - runtime state JSON
   - object dependency graph
3. expose `--base-runtime-state-file` in the seed CLI or wire `baseRuntimeState` through the EXP deploy wrapper
4. keep the REST runner as fallback for non-bulk objects

Historical gate, now replaced by the bulk planner dry-run:

```bash
bash scripts/apply_exp_bulk_baseline.sh \
  --seed-file deployments/offline/runtime/seeds/exp-baseline.seed.yaml \
  --output-dir /tmp/exp-baseline-plan \
  --state-file /tmp/exp-baseline-plan/runtime-state.json \
  --summary-file /tmp/exp-baseline-plan/summary.json \
  --dry-run
```

Plus planner unit tests for duplicate collapse, template materialization, deterministic IDs, and bulk-state handoff.

### Phase 2 - DB apply for #2859 reference data

Deliverables:

1. admit `Country_Dictionary__c`, `Countries_Region__c`, and `Partner_Region_Owner_Rule__c`
2. resolve owner user IDs before owner-rule write
3. add target-table mapping for these objects through runtime metadata/config
4. write set-oriented verification:
   - expected row count per object
   - no duplicate match values
   - `Lead_SetCountryCodeFromCountry` lookup rows exist
   - `Lead_Assignment_Rule_modification_for_NA_EMEA` owner regions exist

Gate:

```bash
scripts/apply_exp_bulk_baseline.sh --seed-file deployments/offline/runtime/seeds/exp-baseline.seed.yaml --dry-run
```

The dry-run summary must show the three #2859 objects as bulk-planned and must show zero per-record REST hydration for them.

### Phase 3 - Cover the whole #2859 EXP baseline

Deliverables:

1. admit every #2859 collapsed baseline object that maps cleanly to a runtime TiDB table
2. pre-resolve existing IDs before updating runtime state so warm reruns keep relationship references pointed at the real existing rows
3. normalize seed user passwords without shelling through the REST API:
   - default local/manual mode may hash via the NexusCRM Go password hasher
   - deploy mode copies the existing admin password hash and then runs the existing seeded-user password alignment step
4. skip REST fallback entirely when the bulk summary has zero unsupported rows
5. add planner tests that fail when duplicate collapse, dependency layering, or unresolved references regress

Gate:

For the #2859-scale seed, the deploy summary must show:

1. bulk row count is 724 collapsed records / 843 seed aliases
2. unsupported row count is zero
3. REST fallback is skipped
4. DB apply phase `<= 1000 ms` on EXP TiDB
5. no Connected App hourly API limit risk from baseline row volume

### Phase 4 - Converge shared and EXP loader paths

Done for all shared/EXP baseline callers: `apply_exp_baseline_seed` and
`apply_shared_deploy_baseline_seed` both use `apply_seed_with_bulk`. Complete
bulk coverage is mandatory, and no deployment path invokes the retired REST
runner.

## 6. Verification Plan

Docs/design validation:

```bash
git diff --check
```

Planner validation:

```bash
uv run --with pyyaml --with pymysql python -m unittest \
  tests.test_exp_baseline_seed_contract \
  tests.test_exp_bulk_baseline
```

Seed dry-run validation:

```bash
scripts/apply_exp_bulk_baseline.sh \
  --seed-file deployments/offline/runtime/seeds/exp-baseline.seed.yaml \
  --dry-run
```

Local TiDB integration validation:

```bash
export NEXUSCRM_OFFICIAL_APP_PACKS_ROOT=/Users/ywang/projects/nexuscrm-app-packs
scripts/apply_exp_bulk_baseline.sh \
  --seed-file deployments/offline/runtime/seeds/exp-baseline.seed.yaml
```

Local profile evidence from a warm local stack against the #2859-size
`origin/main` EXP seed snapshot, measuring the whole seed path:

| Path | Scope | Wall time | Backend HTTP requests | Result |
| --- | --- | ---: | ---: | --- |
| Old REST runner | 724 collapsed records | `10.810s` | 734 total; 727 query requests | 723 unchanged, 1 updated, 0 failed |
| New DB bulk path | 724 collapsed records / 843 aliases | `0.539s` | 0 seed REST fallback requests | bulk complete, fallback skipped, 0 unsupported |

The new wall time includes seed JSON render, bulk planner/apply, DB
verification, and the seeded-user password alignment query. The bulk executor's
internal duration was `255ms`; the password alignment query took `6ms`.

This is a `20.1x` whole-path wall-clock speedup on a warm local stack. It is
also conservative for the old path: the database already contained the rows, so
old REST mostly paid existence-query cost. A cold old run would add create/update
requests for missing or changed rows, while the DB path remains a set-oriented
upsert.

Local profile evidence from the current checked-in EXP deploy seed files,
measuring EXP baseline plus shared deploy baseline:

| Path | Scope | Wall time | Result |
| --- | --- | ---: | --- |
| Old REST runner | 238 collapsed records | `6.548s` | 238 records materialized through REST |
| New DB bulk path | 238 collapsed records / 357 aliases | `0.887s` | bulk complete for both files, REST fallback skipped |

That is a `7.4x` whole-path warm-rerun speedup for current checked-in EXP
seeding. The checked-in EXP baseline alone planned 346 aliases into 227 bulk
rows; the shared deploy baseline planned 11 aliases into 11 bulk rows. Both
reported zero unsupported rows and complete coverage.

Earlier diagnostic evidence isolated the high-volume bulk-eligible slice:

| Path | Scope | Result |
| --- | --- | --- |
| New DB bulk path | 661 bulk rows / 780 seed aliases | `0.434s` wall, `124ms` internal DB apply |
| Old REST runner | same 661 rows / 780 seed aliases | `11.167s` wall, 668 backend HTTP requests |

That slice showed a `25.7x` wall-clock speedup on the exact seed slice that caused the
Connected App limit issue. It is also conservative for the old path: the local
database already contained the records, so the old runner reported 661
`unchanged` rows after issuing 664 SOQL/query requests plus 4 preflight/helper
requests. A cold old run would add create/update calls for missing or changed
rows, while the DB path remains a set-oriented upsert.

An earlier whole-seed local run showed only `10.442s` vs `12.659s` because it
still measured the remaining REST fallback work after partial bulk load. That
was the wrong headline metric. The release gate for this design is the whole-path
profile above: complete bulk coverage must skip REST fallback for the #2859
baseline shape.

EXP deploy validation:

1. deploy summary includes bulk row count, alias count, unsupported count, DB apply duration, and fallback decision
2. no failure at `Country_Dictionary__c` row 719/724 because that row is no longer a REST operation
3. lead country-code and region assignment flows can query their dictionary/owner rows
4. rerunning seed reports unchanged/updated rows without duplicate match rows

## 7. Non-Goals

1. This doc does not move generic seed behavior upstream.
2. This doc does not make live staging a deploy-time data source.
3. This doc does not bulk-load secrets or Named Credential secret values.
4. This doc does not require rewriting the full watchdog API runner.

## 8. Open Questions

1. Should custom object table mapping stay purely schema-discovered, or should future nonstandard objects require an explicit seed-loader mapping file?
2. Should non-EXP shared watchdog loader callers retire onto this planner, or keep the older snapshot loader until they need the same profiling guarantee?
3. Should the deploy summary parse backend access logs to print measured REST request counts, or is bulk summary plus fallback decision enough?