Migration Backfill — Operations Runbook For Alpha Trinity Asset v1.0.17+

Neutrinos Platform Platform Core Features
1

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 <placeholders>). Optional shell loops are in
Appendix A.

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 <domain> with your host. Swagger is at
<domain>/bpmservice/api-docs and <domain>/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 <token> 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:

SELECT service_name, version, applied_at
FROM <bpm-schema>.schema_version ORDER BY applied_at DESC LIMIT 10;
-- expect service_name = 'alpha_bpm_service', version = '1.0.17'

Repeat for <enquiry-schema> (alpha_enquiry_service) and <cm-schema>
(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:

SELECT to_regclass('<bpm-schema>.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:

SELECT "caseType", "caseDataTableName", "taskDataTableName", "signalDataTableName"
FROM <enquiry-schema>.enquiry_config WHERE "projectId" = '<project-uuid>';

Expect one row with all three names. Optionally check whether ti_* has a
parentProcessInstanceId column (needed for the optional TASKS_PARENT_PIID
scope):

SELECT EXISTS (SELECT 1 FROM information_schema.columns
  WHERE table_schema='<enquiry-schema>' AND table_name='<ti_*>'
    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:
<domain>/bpmservice/migration/bpm-lineage-backfill.

2.1 Baseline — POST /status {}

{ "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:

SELECT COUNT(DISTINCT l.processinstanceid) AS missing
FROM <bpm-schema>.processinstancelog l
LEFT JOIN <bpm-schema>.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:

{ "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). 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 {}

{ "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:

SELECT (SELECT COUNT(DISTINCT processinstanceid) FROM <bpm-schema>.processinstancelog),
       (SELECT COUNT(*) FROM <bpm-schema>.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: <domain>/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": "<project-uuid>", "scope": "ALL" }

{ "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:

{ "projectId": "<project-uuid>", "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).
After the main backfill, loop until done:

{ "projectId": "<project-uuid>", "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):

SELECT COUNT(*) FROM <enquiry-schema>.<ci_*>;   -- ≈ caseManager.cases
SELECT COUNT(*) FROM <enquiry-schema>.<ti_*>;
SELECT COUNT(*) FROM <enquiry-schema>.<si_*>;

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": "<project-uuid>" } 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), /down {}.
  • Enquiry — /caseservice/case/migration/enquiry-projection-backfill:
    /status/project {projectId, scope?}, /up/project
    (fields §3.2), /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.

# Mint IDS token
TOKEN=$(curl -s -X POST "<IDS_URL>/connect/token" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -d "client_id=<IDS_CLIENT_ID>" -d "client_secret=<IDS_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="<domain>/bpmservice/migration/bpm-lineage-backfill"
E="<domain>/caseservice/case/migration/enquiry-projection-backfill"
PID="<project-uuid>"

# 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
📨 Alpha Platform Trinity Asset (1.0.17) Release Notes - 26.06.1.0.17