---

name: truston-checklist-migration description: Migrate TrustOn inspection checklist items to a new version in the database

TrustOn Checklist Migration Skill

Automates upgrading CheckItem.itemSpec and creating WorkOrderRouting in the TrustOn database when checklist versions are released (e.g., v4.8 → v4.9.2).

When to use

• New checklist version released (v4.9.2, Centromash v4.9.2, v4.10, etc.)
• CheckItem specs need updating across all inspection orders
• Post ordering, items, or metadata has changed
• Need to add new check items or remove deprecated ones
• User requests to apply new checklist to specific vehicle/order

Problem

When checklist versions are released:

1. The database contains thousands of CheckItem records with old specs
2. Orders may be missing WorkOrderRouting records (causing 404 errors on /orders/{orderId}/routing endpoint)
3. Manual updates are error-prone and time-consuming

Solution

This skill provides:

CheckItem Migration:

• TypeScript Script: scripts/migrate_single_order_checklist.ts for single order migration (RECOMMENDED)
• HTTP Endpoint: POST /migrate/checkitems-v{VERSION} for batch/single order migration
• Query params: ?orderId=<id> (single), ?dryRun=true (test), ?confirm=true (production)
• Safe operations: Preserves deleted items, adds new items, updates order fields
• Migration scripts: TypeScript (primary), Python, SQL alternatives

Routing Creation (REQUIRED):

• TypeScript Script: scripts/create_routing_for_order.ts for automatic routing creation
• SQL Script: scripts/create_routing_for_order.sql for manual routing creation
• Automatic grouping: CheckItems grouped by post prefix (P3, P4, P5, P6)
• RouteStep generation: Creates steps with sequence, status, assignedCheckItemIds

Key Concepts

CheckItem Structure:

{
  "itemSpec": {
    "itemId": "P4.801",
    "title": "Охлаждающая жидкость: уровень/состояние",
    "order": 16,
    "block": "Подкапотное пространство",
    "priority": "P4",
    "fields": [...],
    "methodology": "..."
  }
}

Available Checklists:

• General Checklist: TrustOn Bundle v4.9.2.json - standard inspection checklist
• Centromash Checklist (current default): TrustOn Checklist v4.9.2 CM - centromash.v4.9.7.full-from-xlsx.json
  • Version: 4.0.9.7-final-centromash+full-xlsx
  • Items: 20 specialized checks
  • Focus: XLSX-aligned CM flow for body panels, glass, interior, underhood, underbody, and split suspension checks
  • Optimized for: Posts P1, P2, P3
• Legacy Centromash Bundles: TrustOn Checklist v4.9.2 CM - centromash.json, TrustOn Checklist v4.9.2 CM - centromash.v4.9.5.strict.json
  • Keep only for historical migrations or compatibility checks; do not use as the current default.

Migration Logic:

1. Load new bundle (v4.9.7.full-from-xlsx for current CM migrations)
2. Extract itemId → itemSpec mapping
3. For each order: iterate CheckItems, update itemSpec where code matches
4. Add new items (for example P4.CM.BODY.PANELS, P6.CM.SUSP.RL, P6.CM.SUSP.RR) if missing
5. Preserve deleted items (P3.000, P4.700-702, P4.809-810)
6. When replacing historical structures, explicitly handle P6.CM.SUSP.REAR -> P6.CM.SUSP.RL + P6.CM.SUSP.RR
7. Update checkItemToPostMap expectations and routing after the split

Instructions

Step 1: Prepare Bundle

Choose checklist type:

• Centromash: For Centromash diagnostic center orders → TrustOn Checklist v4.9.2 CM - centromash.v4.9.7.full-from-xlsx.json
• General: For standard inspections → TrustOn Bundle v4.9.2.json

Update script CHECKLIST_PATH (if using TypeScript script):

const CHECKLIST_PATH = path.join(
  __dirname,
  '../docs/checklist/checklist4/TrustOn Checklist v4.9.2 CM - centromash.v4.9.7.full-from-xlsx.json'
);

Bundle format:

{
  "schema": "TrustOn.ChecklistBundle.v2",
  "version": "4.0.9.7-final-centromash+full-xlsx",
  "items": [
    { "itemId": "P1.010", "title": "...", "order": 1, ... }
  ],
  "optionsRegistries": { ... },
  "checkItemToPostMap": { "P4.THK.001": "P4", ... }
}

Step 2: Create Migration Endpoint (NestJS)

Add controller in apps/api/src/inspection/migrate.controller.ts:

Features:

• Load ITEM_SPECS from bundle constant
• Support query params: orderId, dryRun, confirm
• Dry-run mode: returns count of changes without persisting
• Confirm mode: applies migration to all/single orders
• Returns: migration summary (updated count, new items added, etc.)

Step 3: Execute Migration

Option A: TypeScript Script (RECOMMENDED for single orders)

# Migrate single order by orderId:
pnpm tsx scripts/migrate_single_order_checklist.ts <orderId>

# Example (Ford Explorer, Centromash v4.9.7.full-from-xlsx):
pnpm tsx scripts/migrate_single_order_checklist.ts bea653ec-d5d2-4c7f-a3e6-358c68284212

Features:

• Uses Prisma 7 with pg adapter
• Loads checklist from docs/checklist/checklist4/TrustOn Checklist v4.9.2 CM - centromash.v4.9.7.full-from-xlsx.json
• Updates existing CheckItems by matching code to itemId
• Adds new items not present in order
• Preserves unmatched items with warnings
• Shows detailed migration summary
• Current API default bundle already resolves to v4.9.7.full-from-xlsx via apps/api/src/inspection/item-specs.constant.ts and apps/api/src/orders/template-catalog.service.ts

Option B: HTTP Endpoint (for batch migrations)

# Single order test:
curl -X POST http://localhost:3000/migrate/checkitems-v492?orderId=<uuid>

# Dry-run on all orders:
curl -X POST http://localhost:3000/migrate/checkitems-v492?dryRun=true

# Production migration:
curl -X POST http://localhost:3000/migrate/checkitems-v492?confirm=true

Option C: CLI Command

pnpm --filter @truston/api migrate:checkitems-v492 --all --confirm

Option D: Direct SQL

jq -r '.items[] | @json' "$BUNDLE_PATH" | while read SPEC_JSON; do
  ITEM_ID=$(echo "$SPEC_JSON" | jq -r '.itemId')
  UPDATE "CheckItem" SET "itemSpec" = '$SPEC_JSON' WHERE code = '$ITEM_ID'
done

Step 4: Create Routing for Order

CRITICAL: After migrating CheckItems, you MUST create routing (WorkOrderRouting) for the order to work with the API endpoint /orders/{orderId}/routing.

Option A: TypeScript Script (RECOMMENDED)

# Create routing automatically based on CheckItems:
pnpm tsx scripts/create_routing_for_order.ts <orderId>

# Example:
pnpm tsx scripts/create_routing_for_order.ts bea653ec-d5d2-4c7f-a3e6-358c68284212

What it does:

• Gets all posts from the order's service station
• Groups CheckItems by post prefix (P3, P4, P5, P6, etc.)
• Creates WorkOrderRouting record
• Creates RouteStep records for each post with assigned CheckItems
• Returns routing summary
• For current CM flow, expect rear suspension as two distinct items: P6.CM.SUSP.RL and P6.CM.SUSP.RR

Option B: SQL Script

# Use pre-generated SQL for specific order:
psql -h <host> -U <user> -d <database> -f scripts/create_routing_for_order.sql

Important Notes:

• RouteStep schema does NOT have updatedAt field (Prisma schema only has createdAt)
• Post assignment is automatic based on CheckItem code prefix
• Special handling: PH.* items go to P3 (Фотозона)
• Each RouteStep gets assignedCheckItemIds array

Routing Structure:

WorkOrderRouting
├── orderId (unique)
└── route (RouteStep[])
    ├── postId
    ├── sequence (1, 2, 3, 4...)
    ├── status (pending, in_progress, completed)
    └── assignedCheckItemIds (CheckItem.id[])

Step 5: Verify Results

Check CheckItems migration:

SELECT
  COUNT(*) as total_updated,
  COUNT(DISTINCT "order"::text) as unique_orders
FROM "CheckItem"
WHERE "itemSpec"->>'order' IS NOT NULL;

-- Check specific post (e.g., P4)
SELECT code, "itemSpec"->>'order' as "order", "itemSpec"->>'title' as title
FROM "CheckItem"
WHERE code LIKE 'P4.%'
GROUP BY code, "order"
ORDER BY ("itemSpec"->>'order')::int;

Check routing creation:

-- Verify routing exists
SELECT * FROM "WorkOrderRouting" WHERE "orderId" = '<orderId>';

-- Check route steps
SELECT
  wr.id as routing_id,
  rs."postId",
  p.code as post_code,
  p.name as post_name,
  rs.sequence,
  rs.status,
  array_length(rs."assignedCheckItemIds", 1) as check_items_count
FROM "WorkOrderRouting" wr
JOIN "RouteStep" rs ON rs."workOrderRoutingId" = wr.id
JOIN "Post" p ON p.id = rs."postId"
WHERE wr."orderId" = '<orderId>'
ORDER BY rs.sequence;

Test API endpoint:

# Should return 200 with routing data (not 404):
curl https://api.thetasystem.com/orders/<orderId>/routing

Migration Workflow

1. TypeSpec contracts updated
   ↓
2. pnpm gen (generates new bundle)
   ↓
3. Create migration endpoint/script
   ↓
4. Test on single order (dryRun or single orderId)
   ↓
5. Verify order field updates, especially any split mappings such as `P6.CM.SUSP.REAR -> P6.CM.SUSP.RL/RR`
   ↓
6. Create routing for order (CRITICAL STEP)
   ↓
7. Test API endpoint GET /orders/{orderId}/routing
   ↓
8. Execute on all orders (confirm=true)
   ↓
9. Verify counts match expectations
   ↓
10. Commit migration script to separate branch

Data Preservation Rules

• Deleted items: Keep as-is (P3.000, P4.700-702, P4.809-810)
• New items: Add with full spec if missing
• Updated items: Replace entire itemSpec (order + metadata)
• Unmatched items: Log warnings, preserve unchanged
• Split items: When a historical item is replaced by multiple current items, preserve old records unless the migration explicitly deprecates them, and create the new split records with full specs.

Files to Create

# CheckItem Migration
scripts/migrate_single_order_checklist.ts        # TypeScript migration script (PRIMARY)
apps/api/src/inspection/migrate.controller.ts    # HTTP endpoint (batch migrations)
apps/api/src/inspection/migrate-checkitems.command.ts  # CLI command
scripts/migrate_checkitem_v{VERSION}.py          # Python alternative
scripts/migrate-checkitems.sql                   # Direct SQL

# Routing Creation (REQUIRED after migration)
scripts/create_routing_for_order.ts              # TypeScript routing script (PRIMARY)
scripts/create_routing_for_order.sql             # SQL routing script

TypeScript Migration Script Template:

• Import dotenv/config to load DATABASE_URL
• Use PrismaPg adapter with Pool (Prisma 7 requirement)
• Read checklist bundle JSON from docs/checklist/checklist4/
• Build itemId → itemSpec mapping
• Update existing CheckItems by code
• Create new CheckItems with required fields (title, claimTemplate, required)
• Close pool connection after migration

TypeScript Routing Script Template:

• Import dotenv/config to load DATABASE_URL
• Use PrismaPg adapter with Pool (Prisma 7 requirement)
• Get order and service station posts
• Group CheckItems by post prefix (P3, P4, P5, P6)
• Create WorkOrderRouting record
• Create RouteStep records (WITHOUT updatedAt field)
• Each RouteStep includes: postId, sequence, status, assignedCheckItemIds
• Close pool connection after creation

Testing Checklist

CheckItem Migration:

• [ ] Dry-run returns correct count of changes
• [ ] Order field updates for key items (P4.801, P6.010, etc.)
• [ ] New items added to all orders
• [ ] P6.CM.SUSP.REAR replacement logic creates P6.CM.SUSP.RL and P6.CM.SUSP.RR where required
• [ ] Deleted items preserved
• [ ] Post order sequence maintained (1-25 for P4)
• [ ] Run on single order first
• [ ] Verify on 2-3 random orders before batch
• [ ] Check database row counts match

Routing Creation:

• [ ] WorkOrderRouting record created for order
• [ ] RouteStep records created for all relevant posts
• [ ] CheckItems correctly grouped by post prefix
• [ ] Sequence numbers are sequential (1, 2, 3, 4...)
• [ ] assignedCheckItemIds arrays populated
• [ ] All RouteStep statuses set to 'pending'
• [ ] API endpoint /orders/{orderId}/routing returns 200 (not 404)
• [ ] No updatedAt field errors in RouteStep creation

Common Issues

Issue: PrismaClient initialization error (Prisma 7) Solution:

import 'dotenv/config';
import { PrismaPg } from '@prisma/adapter-pg';
import { Pool } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const adapter = new PrismaPg(pool);
const prisma = new PrismaClient({ adapter });

Issue: Missing required field title when creating CheckItem Solution: Include all required fields in create:

await prisma.checkItem.create({
  data: {
    orderId,
    code: item.itemId,
    title: item.title,
    claimTemplate: `Claim for ${item.itemId}`,
    required: item.required,
    itemSpec: item as any,
  },
});

Issue: Order field not updating Solution: Ensure itemSpec is loaded as JSON object, not string

Issue: Unmatched items in logs Solution: Check itemId format matches between bundle and database

Issue: 404 Not Found on /orders/{orderId}/routing endpoint Solution: Routing not created for order. Run routing creation script:

pnpm tsx scripts/create_routing_for_order.ts <orderId>

Issue: RouteStep creation fails with "updatedAt does not exist" Solution: RouteStep table does NOT have updatedAt field. Remove it from INSERT:

// ❌ Wrong (has updatedAt)
await prisma.routeStep.create({
  data: { ...fields, createdAt: new Date(), updatedAt: new Date() }
});

// ✅ Correct (only createdAt)
await prisma.routeStep.create({
  data: { ...fields, createdAt: new Date() }
});

Example Results

Example 1: Single Order (Ford Explorer, Centromash v4.9.7.full-from-xlsx)

Order ID: bea653ec-d5d2-4c7f-a3e6-358c68284212
VIN: 1FM5K8HC0LGA87502
Status: in_progress
Checklist version: 4.0.9.7-final-centromash+full-xlsx

Updated items: 0 (order had no existing items)
New items added: 20
- ✓ P1.CM.RECEPTION: Приемка на станции
- ✓ P1.CM.WASH: Мойка
- ✓ PH.PHOTO.STANDARD: Фотофиксация (стандартный набор)
- ✓ P4.THK.002: Проверка ультрафиолетовым сканером
- ✓ P4.THK.001: Проверка толщиномером
- ✓ P4.GLASS.INSPECT: Осмотр остекления
- ✓ P4.LIGHT.INSPECT: Осмотр световых приборов
- ✓ P4.MISC.BOOT: Багажное отделение
- ✓ P4.INT.INSPECT: Осмотр салона
- ✓ P4.600: OBD скан всех блоков
- ✓ P4.CM.BODY.PANELS: Осмотр кузовных панелей (Centromash)
- ✓ P4.CM.UNDERHOOD: Подкапотное пространство
- ✓ P5.CM.BRAKE_STAND: Тормозной стенд (Centromash)
- ✓ P6.CM.UNDERBODY: Подъемник: защиты и днище
- ✓ P6.CM.LIFT: Подъемник (Centromash)
- ✓ P6.CM.TESTDRIVE: Тест-драйв
- ✓ P6.CM.SUSP.FL: Подвеска переднего левого колеса
- ✓ P6.CM.SUSP.FR: Подвеска переднего правого колеса
- ✓ P6.CM.SUSP.RL: Подвеска заднего левого колеса
- ✓ P6.CM.SUSP.RR: Подвеска заднего правого колеса

Total items after migration: 20

Routing creation:

Routing ID: 41c3983c-b04b-463b-9066-bcf556a1ed2a
Order ID: bea653ec-d5d2-4c7f-a3e6-358c68284212

Route steps:
  1. P1 - Приемка / мойка
     Status: pending
     Items: 2 (P1.CM.RECEPTION, P1.CM.WASH)

  2. P2 - Кузовной осмотр
     Status: pending
     Items: 10 (PH.PHOTO.STANDARD, P4.THK.001, P4.THK.002, P4.CM.BODY.PANELS, etc.)

  3. P3 - Подъемник / тормозной стенд
     Status: pending
     Items: 8 (P5.CM.BRAKE_STAND, P6.CM.LIFT, P6.CM.UNDERBODY, P6.CM.SUSP.FL, P6.CM.SUSP.FR, P6.CM.SUSP.RL, P6.CM.SUSP.RR, P6.CM.TESTDRIVE)

API endpoint test:

curl https://api.thetasystem.com/orders/bea653ec-d5d2-4c7f-a3e6-358c68284212/routing
# Returns 200 OK with routing data ✅

Example 2: Batch Migration (26 orders, general checklist)

• 2763 CheckItems updated
• 52 new items added (P4.MISC.BOOT, P4.MISC.HOOD)
• 52 deleted items preserved
• Order range: 1-206
• Post 4 sequence: 25 items, orders 1-25