CAP-680 · step 5 of the plan
What CAP-680 Does
CAP-680 builds step 5: the machinery that copies every pre-cutover object into the per-account layout and rewrites the records that name it. This page is what it delivers. What it leaves behind is on the next page.
- Commits
- 10Across three repositories
- API
- 53 files+3,182 −355 in backendApi
- Web
- 11 files+1,621 −24 in capExpertApp
- Descriptors
- 2118 active, 2 skipped, 1 without write-back
- Console
- 11Admin endpoints behind AdminGuard
Two ledger tables, three background jobs and an admin console. The ledger is keyed on the destination object rather than the source, because one legacy object referenced by several accounts owes each of them its own copy — the path is the access model, so a shared object cannot stay shared.
The ledger
Two tables, added by 20260905110855-create-file-migration-tables.js.
file_migration_queue— one row per destination object. Carries the source and destination paths, the value the record held, the upload module, the owner scope and account, whether a thumbnail travels with it, and the outcome: status, attempts, last error, and the three timestampsclaimed_at,copied_at,written_back_at.file_migration_batches— the unit the runner works in and the unit a person retries. A file carries the batch it went through; a batch names the files that failed it, and countstotal_files,copied_filesandalready_present_filesseparately so a re-run is distinguishable from a first pass.
file_migration_queue_destination_uq is unique on destination_path
where the row is neither deleted nor skipped. That single index is what collapses the many
references sharing one destination into the one copy they owe, and what makes a re-seed a no-op
rather than a duplicate.
The 21 descriptors
MIGRATION_DESCRIPTORS in shared/modules/storage/migration-targets.ts is the mirror of the upload table: upload-targets.ts says where a new file goes, this says where every pre-cutover file came from. The two meet at UploadModule, and the destination is always derived through finalPrefixFor rather than spelled out again.
| Descriptor | Legacy folder | Write-back shape |
|---|---|---|
| camp_inventory_items.images | inventory/images | array |
| inventory_sticker_details.sticker_name | inventory/images | scalar |
| warranties.file | inventory/warranty | csv |
| document_manager.file_path | document-manager | scalar |
| feedbacks.attachment | feedback | array |
| chat_messages.text | chat-message-documents | scalar |
| transactions.sellerInvoice | inventory-invoice-pdf | jsonbKey |
| transactions.buyerInvoice | inventory-invoice-pdf | jsonbKey |
| transactions.poDocument | document-manager/purchase-orders | jsonbKey |
| transactions.poAttachments | transaction/po-attachment | jsonbArrayKey |
| transactions.cerDocument | cer-documents | jsonbKey |
| transactions.billOfSale | bill-of-sales | jsonbKey |
| transactions.shippingQuote | document-manager/shipping-documents | jsonbKey |
| transactions.bolDocument | document-manager/shipping-documents | jsonbKey |
| equipment_models.images | inventory/images | csv |
| equipment_models.files | inventory/user-manual | csv |
| equipment_categories.default_image | categories | scalar |
| bill_of_sale_requests.file_name | bill-of-sales | scalar |
| qr_codes.image | assets/QR | none — path derived from the id |
| ezestimator_requests.file_path | ez-estimator-requests | skipped — no upload module |
| capture_billings.invoice_path | — | skipped — owner ambiguous |
scalar is assigned. csv and array are rebuilt element by
element, preserving order. jsonbKey replaces one key in place. jsonbArrayKey
rebuilds an array of objects with one key swapped inside each element — added for
poAttachments, the only array-of-objects column in the set, which had gone without a
target and was being copied but left naming the legacy path.
What each descriptor moves, with a real file
One actual value out of clone1 per descriptor, and the destination its upload module gives it. {CAP} stands for the owning account, because the destination is resolved per row.
| Descriptor | Key / shape | An actual file today → where the copier sends it |
|---|---|---|
| camp_inventory_items.images | array element | inventory/images/00022jpe_1778195211181.jpegimg/{CAP}/assets/00022jpe_1778195211181.jpeg |
| inventory_sticker_details.sticker_name | whole value | inventory/images/00022jpe_1778195211181.jpegimg/{CAP}/assets/00022jpe_1778195211181.jpeg |
| warranties.file | comma list entry | inventory/warranty/CDL_USHV_Lafayette_Horizonpd_1774621762664.pdfdoc/{CAP}/assets/warranty/CDL_USHV_Lafayette_Horizonpd_1774621762664.pdf |
| document_manager.file_path | whole value | document-manager/01022026_Asset_7168_PMpd_1782934508186.pdfdoc/{CAP}/document-manager/01022026_Asset_7168_PMpd_1782934508186.pdf |
| feedbacks.attachment | array element | feedback/Cooper_Surgical_Cleartone_pd_1717612100050.pdfdoc/{CAP}/feedback/Cooper_Surgical_Cleartone_pd_1717612100050.pdf |
| chat_messages.text | whole value | chat-message-documents/1010SalesSheetpd_1771260090875.pdfdoc/{CAP}/chat-message/1010SalesSheetpd_1771260090875.pdf |
| transactions.additional_documents | sellerInvoice | doc/CAP-NJQJGDJ/copilot-invoice/sold_17887864806705WE6X.pdfdoc/{CAP}/invoice/sold_17887864806705WE6X.pdf |
| transactions.additional_documents | buyerInvoice | ezrfp-invoice-pdf/purchased_1680301533907.pdfdoc/{CAP}/invoice/purchased_1680301533907.pdf |
| transactions.additional_documents | poDocument | cer-documents/1015_Stretcher_Equi_Demojpe_1724265729018.jpegdoc/{CAP}/document-manager/purchase-orders/1015_Stretcher_Equi_Demojpe_1724265729018.jpeg |
| transactions.additional_documents | poAttachments[].attachment | cer-documents/396891798735333pd_1734545674644.pdfdoc/{CAP}/transaction/po-attachment/396891798735333pd_1734545674644.pdf |
| transactions.additional_documents | cerDocument | cer-documents/Bill_of_Salepd_1723044118308.pdfdoc/{CAP}/cer-documents/Bill_of_Salepd_1723044118308.pdf |
| transactions.additional_documents | billOfSale | bill-of-sales/Bill_of_sale_1719219486320Z5RTY.pdfdoc/{CAP}/bill-of-sales/Bill_of_sale_1719219486320Z5RTY.pdf |
| transactions.additional_documents | shippingQuote | document-manager/shipping-documents/1743547691705_1743547691705.pdfdoc/{CAP}/shipping-document/1743547691705_1743547691705.pdf |
| transactions.additional_documents | bolDocument | document-manager/shipping-documents/Bill of Lading 1765229943240_1765229943241.pdfdoc/{CAP}/shipping-document/Bill of Lading 1765229943240_1765229943241.pdf |
| equipment_models.images | comma list entry | inventory/images/00H99jpe_1755814330672.jpegimg/model-library/00H99jpe_1755814330672.jpeg |
| equipment_models.files | comma list entry | inventory-reference/files/000443_GEM_Premier_ChemSTAT_IFUpd_1706009038989.pdfdoc/model-library/spec-files/000443_GEM_Premier_ChemSTAT_IFUpd_1706009038989.pdf |
| equipment_categories.default_image | whole value | categories/default_Anesthesia_Equipment.jpgimg/category/default_Anesthesia_Equipment.jpg |
| bill_of_sale_requests.file_name | whole value | bill-of-sales/Bill_of_sale_1719219486320Z5RTY.pdfdoc/{CAP}/bill-of-sales/Bill_of_sale_1719219486320Z5RTY.pdf |
| qr_codes.image | derived from the id | assets/QR/000001.pngimg/qr/000001.png |
| ezestimator_requests.file_path | whole value | (table is empty)skipped — nothing to copy |
| capture_billings.invoice_path | whole value | (table is empty)skipped — nothing to copy |
Three descriptors declare a legacy folder the data does not match, and because finalPrefixFor derives the destination from the module, the copy lands in the domain the descriptor names rather than the one the file came from.
equipment_models.filesdeclaresinventory/user-manual. All 5,088 values are ininventory-reference/files.transactions.poDocumentdeclaresdocument-manager/purchase-orders, which holds 469 of them — the other 269 are incer-documents(135),transaction/po-attachment(90) andpa-documents(44), and would all be filed as purchase orders.transactions.buyerInvoicedeclaresinventory-invoice-pdf(305 values) but 656 sit inezrfp-invoice-pdf— and the app itself writes new ones todoc/<CAP>/copilot-invoice, so the majority would be filed under the wrong domain.
Nothing is lost — the record is rewritten to the new path, so it still reads — but the per-account tree is the access model, and a CER document filed under purchase orders puts the file in a folder its kind does not belong to.
ezestimator_requests and capture_billings are both empty tables in clone1 — zero rows. Their skipReason entries are correct and there is no work behind them to schedule.
Three stages
Seed — fill the queue
complete- Shape
- One set-based
INSERTper descriptor. Source paths, destinations and owners are all resolved in SQL, so a table with hundreds of thousands of references costs one statement rather than a row loop. - Two guards
ON CONFLICTon the destination collapses shared destinations into the one copy owed. ANOT EXISTScovers what that index cannot — a skipped row sits outside the partial index, so without it a re-seed would insert the work again. It matches on destination and source, because a stored value that gains a folder yields a different source but the same destination.- Why a lock
- Neither guard survives two seeders at once: both would pass
NOT EXISTSbefore either inserts. A Redis lock with a 600-second TTL makes the button safe to press twice. - Skipped rows
- A descriptor with a
skipReasonseeds asskippedcarrying that reason, so it appears in the report rather than being silently absent.
Run — copy the objects
complete- Shape
- A chain, not a loop. Each invocation takes one batch, copies it, and dispatches the next — so no invocation approaches the Cloud Tasks delivery ceiling, and a deploy mid-run costs at most the batch in flight.
- Parallelism
laneis what makes it parallel. Start dispatches one chain per lane and each link re-dispatches into its own lane, so the number of batches in flight stays at the configured count without any lane knowing about the others. Files copied at once isparallelBatches × fileConcurrency.- Stopping
file_migration_enabledis re-read at the head of every link. That is the whole of the stop mechanism. A rerun carries abatchIdand never chains, so retrying a batch cannot restart a stopped run.- Failure
- Errors are swallowed rather than rethrown: the outcome of every file is already recorded on its row, so a Cloud Tasks redelivery would repeat work whose result is known. A batch left
runningpast 30 minutes is a run that died; the log surfaces it and a rerun clears it. - Tuning
- Four environment variables, all required and range-checked by Joi at boot: batch size, batch delay, parallel batches, file concurrency. Deployment tuning rather than app state, so unlike the run switch the page does not write them.
Write back — rewrite the records
complete- Shape
- One statement per descriptor for the whole queue, so there is nothing to chain and nothing to divide between lanes — a handful of statements that finish in seconds.
- Rollback
- The ledger keeps both halves of every rewrite —
stored_valueis what the column held before,destination_pathwhat it holds after — so the rollback is the same SQL with the two swapped, rather than a second implementation that could drift from it. - The wider match
- A pass matches a wider set of statuses than it retires from, on purpose: the match is on the value the column actually holds, and that value is gone once the rewrite has run. A row retired without its column being touched is therefore picked up by the next press instead of being stranded beyond the reach of both directions.
- Declared omissions
- A descriptor with neither a
writeBacknor anoWriteBackis a gap, and every batch carrying one says so. Without that, its records would keep the legacy path while the ledger claimed otherwise. - Locking
- One short-lived, heartbeated lock per direction. Held outright, a process killed mid-pass would never reach its own release and every later attempt would be refused until it lapsed.
The console
capExpertApp/src/app/modules/developer/file-migration/ — 1,570 lines across the
component and its template, behind AdminGuard on eleven endpoints:
| Endpoint | Does |
|---|---|
| GET /file-migration/summary | Queue and batch totals, plus what is running |
| GET /file-migration/batches | Batch list, paged |
| GET /file-migration/batches/:id/files | The files in one batch |
| GET /file-migration | The file list, filterable |
| POST /file-migration/seed | Fill the queue |
| POST /file-migration/run | Set the run flag and dispatch one chain per lane |
| POST /file-migration/stop | Clear the run flag; the chain stops at its next link |
| POST /file-migration/write-back | Rewrite the records |
| POST /file-migration/roll-back | The same pass reversed |
| POST /file-migration/batches/:id/retry | Re-run one batch without restarting the run |
| POST /file-migration/files/:id/retry | Re-run one file |
A Redis activity key with a 30-second TTL, heartbeated every 10 seconds, names the background action in flight. The import role sets it while seeding, writing back or resetting; the primary role reports it, so the page can say what is busy. Short on purpose — a job killed mid-flight never runs its own cleanup, so the key has to lapse by itself rather than lock the page for minutes.
The list is coloured by what the record names rather than by ledger status alone, which is what makes a row whose copy succeeded but whose write-back did not visible at a glance.
Five correctness decisions worth knowing
- Soft-deleted rows are included. Nothing filters on
deleted_at. A soft-deleted row still names an object that is still in the bucket, and the row can be restored — so the file is owed a copy and the column is owed the rewrite. Skipping them would leave the only reference to those objects on a path the migration has moved away from. - The layout guard reads the path, not the config. A record written during a
rehearsal keeps
<prefix>/img/…after the prefix is cleared. Comparing against the prefix in force now would read those as legacy and queue every one again — so the test is on the path's own first two segments. No legacy folder hasimgordocthere, so it cannot swallow a real one. - Ownership is resolved per column, not per table.
transactions.additional_documentsholds one file per key and the keys do not share an owner — an invoice is issued to each side separately, while a PO and its attachments are the buyer's. Each key is therefore its own descriptor rather than one pass over the blob. - QR codes are derived, not stored. Nothing stores a QR code's path; every
reader computes it from the id. So the seeder computes it the same way, and
greatest(6, length(id))pads rather than truncates — plainlpad(id, 6)would turn 1000000 into 100000, which is another code's file rather than a missing one. - Two descriptors shared an upload module and hid an omission.
bill_of_sale_requests.file_namesharesBillOfSalewithtransactions.billOfSale, whose statement was retiring those rows on its behalf — marking them written back while the request still named the legacy path. It now has its own write-back target.