# Migration Backfill For Server Side Sorting in Alpha - Operations Runbook For Alpha Trinity Asset v1.0.17+
**URL:** https://community.neutrinos.com/t/migration-backfill-for-server-side-sorting-in-alpha-operations-runbook-for-alpha-trinity-asset-v1-0-17/2133
**Category:** Platform Core Features
**Tags:** neutrinos-alpha
**Created:** [July 1, 2026, 4:56am UTC](https://community.neutrinos.com/t/migration-backfill-for-server-side-sorting-in-alpha-operations-runbook-for-alpha-trinity-asset-v1-0-17/2133 "2026-07-01T04:56:06Z")
**Posts on this page:** 1
**Page:** 1
### Author:  [@doomedCode](https://community.neutrinos.com/u/doomedCode)
#### Post date: [July 1, 2026, 4:56am UTC](https://community.neutrinos.com/t/migration-backfill-for-server-side-sorting-in-alpha-operations-runbook-for-alpha-trinity-asset-v1-0-17/2133/1 "2026-07-01T04:56:06Z")
Populates historical data so newer inbox features (chiefly **server-side inbox
sorting**) work on data created before those features shipped. New records are
indexed automatically; only the historical backlog needs this.
Two backfills, run in this order:
| # | Backfill | Service | Populates |
| --- | --- | --- | --- |
| 1 | **BPM lineage** (env-wide) | `alpha-jbpm-service` | `process_instance_lineage` (BPM DB) — subprocess parent resolution |
| 2 | **Enquiry projection** (per project) | `alpha-case-service` | `ci_*`, `ti_*`, `si_*` (Enquiry DB) — the read model the sorted inbox queries |
Both are **batched, resumable, idempotent** : call the `up` endpoint repeatedly
until it returns `hasMore: false`. A crash or timeout is safe — send the same
request again; it resumes from the saved cursor and never duplicates rows.
**You need:** (1) API access via each service’s Swagger UI or any HTTP client —
every step gives the method, path, and JSON body; (2) a SQL client for the
verification queries (plain SQL with ``). Optional shell loops are in
[Appendix A](#appendix-a--optional-automation-scripts).
**Skip the backfill** for a brand-new environment, or when all data was created
after the read-model tables went live. If you only added a sortable case column,
run just the enquiry `CASES` scope (Step B).
* * *
## 1. Access and prerequisites
### 1.1 The two services
- **`alpha-jbpm-service`** — a thin adapter/wrapper in front of the jBPM engine (it
talks to the BPM DB and proxies jBPM’s REST server); **not jBPM itself**. The
lineage backfill runs here and writes the BPM DB directly.
- **`alpha-case-service`** — owns Case Manager and the enquiry read model. The
enquiry backfill runs here.
Base paths default to `bpmservice` and `caseservice` (set per deployment via each
service’s `BASE_PATH`); replace `` with your host. Swagger is at
`/bpmservice/api-docs` and `/caseservice/api-docs` when
`ENABLE_SWAGGER=true` (tags **Migration — BPM lineage backfill** / **Migration —
Enquiry projection backfill**). All backfill operations are**POST**.
### 1.2 Authentication
Both services use the standard IDS client-credentials **bearer token** — send
`Authorization: Bearer ` on every call (in Swagger, **Authorize** and paste
it). `alpha-jbpm-service`, being a jBPM wrapper, also expects `user`/`password`
headers to be present; these are normally filled from its `JBPM_USER_NAME` /
`JBPM_PASSWORD`, so the bearer token is usually all you send.
### 1.3 Databases
| Data | Service | Database (env) | Schema (env) |
| --- | --- | --- | --- |
| BPM lineage (`process_instance_lineage`, `processinstancelog`) | jbpm | `BPM_DB_NAME` | `ALPHA_BPM_DB_SCHEMA_NAME` (default `public`) |
| Enquiry (`ci_*`, `ti_*`, `si_*`, `enquiry_config`) | case | `ENQUIRY_DB_NAME` | `ENQUIRY_DB_SCHEMA_NAME` |
| Case Manager (sources, job cursor) | case | `CM_DB_NAME` | `CM_DB_SCHEMA_NAME` |
Typically one Postgres server (shared `DB_HOST` / `DB_PORT` / `DB_USERNAME` /
`DB_PASSWORD`).
### 1.4 Confirm the release is installed (asset 1.0.17+)
The endpoints and their DDL ship in asset **1.0.17+** , applied by the
migration-service (not by these endpoints). The migration-service writes a
`schema_version` table into **every schema it migrates — including BPM, Enquiry,
and Case Manager** (columns `service_name`, `version`, `applied_at`). Confirm a
`1.0.17` row in each:
```sql
SELECT service_name, version, applied_at
FROM .schema_version ORDER BY applied_at DESC LIMIT 10;
-- expect service_name = 'alpha_bpm_service', version = '1.0.17'
```
Repeat for `` (`alpha_enquiry_service`) and ``
(`alpha_case_service`). 1.0.17 ships the BPM DDL that creates
`process_instance_lineage`; the Enquiry row appears even though this release adds
no enquiry tables. If a schema has no `schema_version`, migrations weren’t applied
there — fix that first. Then confirm the lineage table exists:
```sql
SELECT to_regclass('.process_instance_lineage') IS NOT NULL AS exists; -- t
```
### 1.5 Enquiry tables for the project
The `ci_*` / `ti_*` / `si_*` tables and their `enquiry_config` row are created when
the project’s inbox/enquiry config is saved in Studio. Read the (hashed) table
names from `enquiry_config` — don’t guess:
```sql
SELECT "caseType", "caseDataTableName", "taskDataTableName", "signalDataTableName"
FROM .enquiry_config WHERE "projectId" = '';
```
Expect one row with all three names. Optionally check whether `ti_*` has a
`parentProcessInstanceId` column (needed for the optional `TASKS_PARENT_PIID`
scope):
```sql
SELECT EXISTS (SELECT 1 FROM information_schema.columns
WHERE table_schema='' AND table_name=''
AND column_name='parentProcessInstanceId');
```
### 1.6 Run order
Run **Step A (lineage) first** — the enquiry task backfill resolves parents more
accurately when lineage already exists — then **Step B (enquiry)** per project.
* * *
## 2. Step A — BPM lineage backfill
Environment-wide, on `alpha-jbpm-service`. All `POST`; bodies may be `{}`. Base:
`/bpmservice/migration/bpm-lineage-backfill`.
### 2.1 Baseline — `POST /status` `{}`
```json
{ "lineageTableExists": true, "distinctLogProcesses": 125000,
"lineageRows": 0, "missingLineageRows": 125000, "remainingUnits": 48000,
"job": { "status": "idle", "cursorLogId": 0, "lineageWritten": 0, "lastError": null } }
```
Confirm `lineageTableExists: true` and record `missingLineageRows` (the gap).
Cross-check in SQL:
```sql
SELECT COUNT(DISTINCT l.processinstanceid) AS missing
FROM .processinstancelog l
LEFT JOIN .process_instance_lineage pil ON pil.instance_id = l.processinstanceid
WHERE pil.instance_id IS NULL;
```
### 2.2 Run the backfill — `POST /up`
Send `{"dryRun":true}` first for a no-write preview. Then loop the real request
until `hasMore: false`:
```json
{ "batchSize": 5000, "maxBatches": 20, "batchDelayMs": 500 }
```
Each call handles at most `batchSize × maxBatches` processes. Repeat in Swagger
(Execute → check `hasMore` → Execute again) or script it
([Appendix A](#appendix-a--optional-automation-scripts)). After each call read
`hasMore` (false = done), `processedThisRun` / `lineageWrittenThisRun` (progress),
and `errorsThisRun` (should stay `0` — if persistently high, stop and check
service logs).
Request fields, all optional: `batchSize` (5000), `batchDelayMs` (500),
`maxBatches` (20), `dryRun` (false), `clampPageLevels` (5000), `clampMaxPages`
(20). Lower `batchSize` / `maxBatches` if you hit gateway timeouts.
### 2.3 Verify — `POST /verify` `{}`
```json
{ "distinctLogProcesses": 125000, "lineageRows": 125000, "missingLineageRows": 0, "ok": true }
```
Success = `ok: true` (`missingLineageRows: 0`). `/verify` returns `400` if the
table is missing; `/status` always returns `200`. SQL cross-check:
```sql
SELECT (SELECT COUNT(DISTINCT processinstanceid) FROM .processinstancelog),
(SELECT COUNT(*) FROM .process_instance_lineage);
```
If `up` reports `hasMore: false` but `missingLineageRows > 0`, some parents have no
log row of their own (orphaned history) and can’t become eligible — decide whether
to ignore them.
* * *
## 3. Step B — Enquiry projection backfill (inbox sorting migration)
Per project, on `alpha-case-service`. `caseType` is resolved from the project. All
`POST`. Base: `/caseservice/case/migration/enquiry-projection-backfill`.
Scope `ALL` runs three phases automatically: **CASES → TASKS → SIGNALS**.
| Scope | Backfills | Use when |
| --- | --- | --- |
| `ALL` (default) | cases, tasks, signals | normal full backfill |
| `CASES` / `TASKS` / `SIGNALS` | that one only | re-run a single phase |
| `TASKS_PARENT_PIID` | fills `parentProcessInstanceId` on `ti_*` | **optional extra pass** , not part of `ALL`; run after, only if the column exists |
### 3.1 Baseline — `POST /status/project`
Body: `{ "projectId": "", "scope": "ALL" }`
```json
{ "caseType": "loanApplication", "caseManager": { "cases": 8200, "signals": 1500 },
"remainingUnits": 8200, "parentProcessInstanceIdColumnPresent": true,
"job": { "status": "idle", "phase": "CASES", "cursorOffset": 0,
"casesProcessed": 0, "tasksProcessed": 0, "signalsProcessed": 0, "lastError": null } }
```
Confirm `caseType` is populated; note `caseManager.cases` / `signals` (the totals).
`remainingUnits` is work left for the current phase; it hits `0` when the scope
finishes.
### 3.2 Run the backfill — `POST /up/project`
Preview with `"dryRun": true`, then loop until `hasMore: false`:
```json
{ "projectId": "", "scope": "ALL", "batchSize": 100, "maxBatches": 20, "batchDelayMs": 200 }
```
`phase` advances CASES → TASKS → SIGNALS across calls. Watch `phase`, `hasMore`,
`processedThisRun{cases,tasks,signals}`, `errorsThisRun`.
Fields: `projectId` ( **required** ); `scope` (`ALL`); `batchSize` (100, env
`BACKFILL_BATCH_SIZE`); `batchDelayMs` (200, env `BACKFILL_BATCH_DELAY_MS`);
`maxBatches` (20); `resetCursor` (false — set `true` to restart the scope);
`dryRun` (false).
> **TASKS sizing:** the TASKS phase iterates cases and loads _all_ of each case’s
> tasks, so 100 cases can write far more than 100 task rows. Use a smaller
> `batchSize` (25–50) if cases have many tasks.
### 3.3 Optional — fill parentProcessInstanceId
Only if the `ti_*` column exists ([§1.5](#15-enquiry-tables-for-the-project)).
After the main backfill, loop until done:
```json
{ "projectId": "", "scope": "TASKS_PARENT_PIID", "batchSize": 500, "maxBatches": 20, "batchDelayMs": 100 }
```
### 3.4 Verify
No separate verify endpoint — re-run `/status/project`. Done when
`remainingUnits: 0` and `job.status: "completed"`. Spot-check row counts (names
from `enquiry_config`):
```sql
SELECT COUNT(*) FROM .; -- ≈ caseManager.cases
SELECT COUNT(*) FROM .;
SELECT COUNT(*) FROM .;
```
* * *
## 4. Pilot, rollback, troubleshooting
### 4.1 Pilot before a large run
Run a small capped batch, read `durationMs` (server wall-clock) from the `up`
response, and extrapolate:
`minutes ≈ (total / processedThisRun) × (durationMs / 60000)`, using
`missingLineageRows` (lineage) or `caseManager.cases` (enquiry) as total. Pilot
bodies: lineage `{"batchSize":200,"maxBatches":5}`; enquiry CASES
`{"scope":"CASES","batchSize":100,"maxBatches":10}`; enquiry TASKS use 50–100 cases
(`{"scope":"TASKS","batchSize":50,"maxBatches":2}`) since task cost is non-linear.
Then tune `batchSize` / `maxBatches` for the full loop.
### 4.2 Rollback (destructive — non-prod only)
Truncates materialized tables; does not touch source data. `POST /down` `{}`
(lineage) truncates `process_instance_lineage` and resets the job. `POST /down/project` `{ "projectId": "" }` truncates the project’s `ci_*` /
`ti_*` / `si_*` (response lists `truncatedTables`) and deletes its cursor. Verify
counts are `0`, then re-run `up`.
### 4.3 Troubleshooting
- **Timed out / crashed:** send the same `up` request again — it resumes from the
cursor. Check `/status` first if unsure whether the last batch committed.
- **Per-row errors:** a bad row doesn’t stop the run; watch `errorsThisRun` and
`job.lastError`, check service logs. Stop only if errors stay high every call.
- **Clamped chains:** a `runaway` block (`clampedRows`, …) means chains deeper than
`ALPHA_LINEAGE_MAX_DEPTH` (default 31) were capped. Still counts as complete
(`ok: true` with `clampedRows > 0` is fine). Raise the cap and re-run only if you
genuinely have deeper nesting.
- **`400` from enquiry:** unknown `projectId`, missing `enquiry_config`,
`TASKS_PARENT_PIID` without the column, or a bad field value.
- **Concurrency:** one `up` loop at a time per backfill/project (single cursor).
Lineage and a _different_ project’s enquiry can run together.
* * *
## 5. Reference
### 5.1 Endpoints (all POST)
- **Lineage** — `/bpmservice/migration/bpm-lineage-backfill`: `/status` `{}`,
`/verify` `{}`, `/up` (fields [§2.2](#22-run-the-backfill--post-up)), `/down` `{}`.
- **Enquiry** — `/caseservice/case/migration/enquiry-projection-backfill`:
`/status/project` `{projectId, scope?}`, `/up/project`
(fields [§3.2](#32-run-the-backfill--post-upproject)), `/down/project` `{projectId}`.
### 5.2 Environment variables (service config)
- **Shared DB:** `DB_HOST` / `DB_PORT` / `DB_USERNAME` / `DB_PASSWORD`.
- **alpha-jbpm-service:** `BASE_PATH` (`bpmservice`), `BPM_DB_NAME`,
`ALPHA_BPM_DB_SCHEMA_NAME` (`public`), `ALPHA_LINEAGE_MAX_DEPTH` (`31`),
`JBPM_USER_NAME` / `JBPM_PASSWORD`, `IDS_ENABLE`, `ENABLE_SWAGGER`.
- **alpha-case-service:** `BASE_PATH` (`caseservice`), `CM_DB_NAME` /
`CM_DB_SCHEMA_NAME` (holds the `enquiry_projection_backfill_job` cursor),
`ENQUIRY_DB_NAME` / `ENQUIRY_DB_SCHEMA_NAME`, `BACKFILL_BATCH_SIZE` (`100`),
`BACKFILL_BATCH_DELAY_MS` (`200`), `IDS_ENABLE`, `IDS_URL` / `IDS_CLIENT_ID` /
`IDS_CLIENT_SECRET`, `ENABLE_SWAGGER`.
* * *
## Appendix A — optional automation scripts
Not required — the runbook runs from Swagger or any HTTP client. These
`bash` + `curl` loops just automate the repeat-until-`hasMore:false` step.
```bash
# Mint IDS token
TOKEN=$(curl -s -X POST "/connect/token" \
-H "Content-Type: application/x-www-form-urlencoded" \
-d "client_id=" -d "client_secret=" \
-d "grant_type=client_credentials" | jq -r .access_token)
JBPM=(-H "Content-Type: application/json" -H "Authorization: Bearer $TOKEN" -H "user: svc" -H "password: svc")
CASE=(-H "Content-Type: application/json" -H "Authorization: Bearer $TOKEN")
L="/bpmservice/migration/bpm-lineage-backfill"
E="/caseservice/case/migration/enquiry-projection-backfill"
PID=""
# Step A — lineage
while :; do R=$(curl -s -X POST "$L/up" "${JBPM[@]}" -d '{"batchSize":5000,"maxBatches":20,"batchDelayMs":500}')
echo "$R" | jq '{hasMore,processedThisRun,errorsThisRun,durationMs}'
["$(echo "$R" | jq -r .hasMore)" = false] && break; sleep 2; done
# Step B — enquiry (scope ALL; use TASKS_PARENT_PIID for the optional pass)
while :; do R=$(curl -s -X POST "$E/up/project" "${CASE[@]}" -d "{\"projectId\":\"$PID\",\"scope\":\"ALL\",\"batchSize\":100,\"maxBatches\":20,\"batchDelayMs\":200}")
echo "$R" | jq '{phase,hasMore,processedThisRun,errorsThisRun,durationMs}'
["$(echo "$R" | jq -r .hasMore)" = false] && break; sleep 2; done
```