Storage reorganisation · execution

Execution

How the plan is actually delivered: one table that decides every path, a staging lifecycle that survives a failure, fifteen migrations that converted the stored values, and a copier that moves what came before.

Repositories: 4backendApi, capExpertApp, dbMigrations, thumbnailService
Migrations applied: 15In clone1, from 2026-09-01 onward
Columns retyped: 9Widened, or split into an array
Backfilled: 17 + 32Columns, plus JSON key paths
Copier descriptors: 2118 active, 2 skipped, 1 without write-back

One table decides every path

The whole scheme rests on there being exactly one place that turns a module name into a folder. That place is UPLOAD_MODULE_TARGETS in backendApi/src/shared/modules/storage/upload-targets.ts. A module name goes in, a prefix comes out; nothing else in the product builds a storage path.

Two functions read it. tempPrefixFor gives the staging prefix and finalPrefixFor gives the filed one, and they differ only in what occupies the owner slot. That is deliberate: it means a staged path already says what it will become, so filing needs no lookup beyond the account.

The guard that makes it reliable

uploadTargetFor throws when a target whose scope requires a branch does not name one. A misconfigured module therefore fails at presign, loudly, rather than quietly writing a level too high. The rule is stated as its exception — UNBRANCHED_SCOPES — so a scope added later is validated by default instead of being forgotten.

Three derived sets fall out of the same table rather than being listed again: OWNERLESS_PREFIXES, THUMB_FOLDER_KEYS from the thumb flags, and the migration destinations, which come through finalPrefixFor so the prefix rule is never restated. Adding a new upload kind is two edits: the enum value, and one row here.

The staging lifecycle

A browser upload cannot be written to its final home, because the record referencing it does not exist yet — and a file filed under an account whose row was never saved is litter nobody can find. So an upload lands in temp, which takes the owner slot and leaves everything below it intact:

img/temp/assets            →  img/<capexId>/assets
doc/temp/export/1/assets   →  doc/<capexId>/export/1/assets

tempPrefixFor(module, owner) builds the staging prefix; the client uploads to a pre-signed URL.
The record is saved with whatever path it was handed.
commitUploads(values, owner) copies each staged object to its filed home and returns Map<originalValue, finalPath>.
The caller writes those returned paths back onto the record. Skipping this is the classic bug: the row keeps a temp/ path forever.

Three properties worth stating

Filing copies; it does not move. fileStagedObject leaves the staged object in place, which is what keeps a failed filing readable and a resubmit safe.

commitUploads never throws. A file that could not be copied is left out of the map and logged, so the record keeps the staged path it already had — the object is still there and still reads. Losing someone's record to protect a file copy is the wrong trade.

It is a no-op on unchanged records. Values that are not staged are already where they belong and come back untouched, which is what makes it safe to call on every save.

When the owner is not known yet

Sometimes the account is unknown at presign time. Rather than collapse the segment — img//assets is a path nobody meant and nothing can guard against once written — a placeholder stands in: img/{SCOPE}/assets/a.jpg. withScope resolves it on save, awaitingScope is true while it remains, and the storage service refuses to write such a destination.

Reclaiming what was abandoned

TempUploadCleanupJob runs daily. A staged object goes only when two independent answers agree: nothing in the schema names its path — every column that can hold one is listed in path-references.ts — and the upload ledger accounts for the object, either filed with its copy verifiably present, or never committed at all. Anything else is kept and reported, because a file still referenced is a record about to break rather than a file to tidy away.

Converting the stored values

Fifteen migrations are applied in clone1. The eight that do the path conversion run in a fixed order, because the widening has to precede every statement that writes a longer value.

Migration	What it does
101440	Offline purchase-order paths on `transactions`, prefixed with their upload folder
101441	Archives `equipment_models.images` to `images_old` and clears the values that were not file names — scraped vendor JSON, and the string `{}`
101442	Widens the path columns; five become `varchar[]`, each element trimmed and the empties dropped
101443	Widens `warranties.file` to `TEXT`, dropping and restoring the three triggers that depend on the column
101444	Equipment model and category pictures
101445	The remaining eleven columns, plus `docusign_requests.attachments` by request type
101446	Ten tables holding a file name inside JSON
101447	Inventory stickers — trigger suspended, then `camp_inventory_items.images` rebuilt once
101448	The warranty search key takes the file's name rather than its address

Four further migrations followed: the invoice paths on transactions, the presigned_url_links ledger and its recreation with sweep columns, the two file_migration tables plus the run switch, and — most recently — replacing the ledger's reference columns with a single ref_data jsonb.

Two details that prevent silent damage

Order inside a list is preserved. Comma lists and arrays were rebuilt entry by entry in their original order, because the first picture is the one shown as the preview — a reshuffle would have changed what customers see on thousands of rows.

An empty array is not an empty string. A value that was nothing but separators became NULL rather than an array of blanks: an empty array reads as present in JS and would defeat every .length guard.

The JSON columns

Thirty-two key paths across ten JSON columns hold a file name. Each row was read, rewritten in memory and written back only where a key actually changed, with array order preserved. One key path needed a sibling test: copilot_parent_requests.action_logs → equipments[].changes[].new is only a file when the sibling key is quote or attachments — without that test a status or a zip code would be prefixed as though it were a file.

email_logs.data was deliberately left alone. It is a frozen copy of what the mailer was sent, and rewriting it would falsify the record.

Moving what came before

The migrations gave every row a complete path, but that path still points into the legacy flat folder. Getting the objects themselves into the new layout is the copier, built on CAP-680. It is three stages driven by two ledger tables.

Seed. One set-based INSERT per descriptor fills file_migration_queue with one row per destination object — not per source and not per database row, because one legacy object referenced by several accounts owes each of them its own copy.
Run. The runner takes one batch, copies its objects, and dispatches the next. The chain rather than a loop is what keeps every invocation well inside the Cloud Tasks delivery ceiling, so a deploy mid-run costs at most the batch in flight.
Write back. One statement per descriptor rewrites the records to name the new path.

Rehearsal is a first-class mode

FILE_MIGRATION_TARGET_PREFIX puts every destination under a scratch folder. It is baked into destination_path at seed time rather than applied at copy time, so the ledger names where the object actually is — which makes a rehearsal a genuine end-to-end test rather than one that stops short of the database.

The architecture, its guards and its console are set out in full on CAP-680, and what it does not reach on CAP-680 Gaps.

Thumbnails

thumb: true on an upload target means the thumbnail service writes a small copy at <same folder>/thumb/<same file name> and makes both readable without a signed URL. Not every picture wants one: a QR code is printed onto a physical label, so a thumbnail would be a pointless object beside a file that must never move.

THUMB_FOLDER_KEYS is derived from the thumb flags rather than listed again, so marking a module thumbnailed is one edit. The thumbnail service and capExpertApp's thumbPathFor each mirror that set — if the three ever disagree, the app asks for objects the service never wrote.

The one that bit during testing

The path builder dropped the img/ segment, so every thumbnail would have landed at CAP-1234/assets/thumb/a.jpg — a prefix nothing reads. It would have looked like it worked. Caught by tests, not by reading.