Storage migration · running order
Move every stored file into a per-account folder in a new bucket, without changing a single existing database table or column. Nine steps, three gates, reversible at every point until the last one.
Two buckets run side by side for the whole migration. Files are copied, never moved, so the old bucket is a working rollback target right up to the final step.
Today · keep readable
capexpert-prod-bucket
Target
capexpert-media
Four top-level areas. The account number does the separating; everything else is there so a path can be read at a glance.
capexpert-media/ ├── img/ │ ├── CAP-12345678/ │ │ ├── assets/ │ │ │ ├── image1_sticker_uuid.jpg │ │ │ └── thumb/ │ │ │ └── image1_sticker_uuid.jpg ← made by the thumbnail service │ │ └── user/profile/ ← the person is named in the table, not the path │ │ ├── me.jpg │ │ └── thumb/ │ │ └── me.jpg │ ├── model-library/ ← no account: shared catalogue │ │ ├── ct-scanner.jpg │ │ └── thumb/ │ │ └── ct-scanner.jpg │ ├── category/ ← no account: shared catalogue │ │ ├── imaging.png │ │ └── thumb/ │ │ └── imaging.png │ └── temp/ ← staging, before the user saves │ └── assets/ │ ├── image1_sticker_uuid.jpg │ └── thumb/ │ └── image1_sticker_uuid.jpg └── doc/ ├── CAP-12345678/ │ ├── assets/warranty/warrantyX.pdf │ ├── assets/service-contract/sc-2291.pdf │ ├── ezrfp-quote/quote-4471.pdf │ ├── cad/floor-3-layout.dwg │ ├── w9/vendor-w9.pdf │ ├── purchasing-formulary/formulary-2026.xlsx │ └── user/documents/ ← the documents table, which has no account column │ └── signed-nda.pdf ├── model-library/ │ ├── spec-files/ │ │ └── ct-scanner-spec.pdf │ └── mfr-files/ │ └── siemens-reference.pdf ├── miscellaneous/ ← the same three kinds, but shared: no account │ ├── cad/standard-room-template.dwg │ ├── w9/capexpert-w9.pdf │ └── purchasing-formulary/baseline-formulary.xlsx ├── global/ ← belongs to nobody: exports, reports, samples │ └── capture/export/ │ └── capture-2026-08-31.csv └── temp/ ← staging, before the user saves └── cad/ └── floor-3-layout.dwg
img/ it holds every picture an account owns, undivided — the account boundary is what this migration is for. Under doc/ it groups the documents that hang off an asset.assets/, documents keep the folder name the code already uses, so nothing has to be re-classified by hand. Only the leading inventory segment is renamed — inventory-reference and inventory-invoice-pdf are different folders and keep their names.img/, spec sheets and manufacturer reference under doc/. Library data shared by every account, so no account number applies.temp stands where the owner would — so a staged file’s path already says what it will become, thumb/ included. Nothing deletes these, so an upload the user abandons stays here.Most files belong to an account. Four kinds do not, and each gets its own shape in the owner slot. This is one setting per folder, and it decides the whole path.
| Owner | Where it goes | What lives there | Copied across? |
|---|---|---|---|
| An account | <tree>/CAP-12345678/… | Inventory pictures, warranties, service contracts, quotes — the bulk of everything. | Yes, account by account. |
| The shared catalogue | img/model-library/… img/category/… doc/model-library/… |
Equipment models and categories. One copy, seen by every account. | Yes, in a single pass — there is no account to loop over. |
| A person | <tree>/CAP-12345678/user/… | Profile photos, and the documents table, which has no account column at all — it reaches one through the person. The person is not in the path; the table records them. | Yes, in the same single pass. |
| Nobody | doc/global/… | Data exports, reports, the EULA, sample files. | No — see below. |
Why a profile photo sits under the account but is tagged to the person. The path puts it inside the owning account, so deleting that account’s folder takes its people’s files with it. The person is recorded in the table instead of the path, which keeps “delete everything belonging to this person” an indexed lookup. The documents table has no account column at all, and reaches one through whoever created the row.
One side effect worth knowing before this ships: the upload function's folder setting defaults to the profile folder. Any caller that forgets to say which folder it wants will now fail loudly for want of a person, instead of quietly filing an equipment picture among the profile photos. That is the better failure, but it will expose callers that were leaning on the default.
Exports are not copied at all. Around a quarter of the folder list is one-shot download files — capture exports, transaction exports, reports. Every one can be regenerated from live data, and nobody re-downloads last quarter's spreadsheet.
So new exports write to the new bucket from cutover onward, and old ones are never copied — they stay in the old bucket, read to the end.
They are kept indefinitely, the same as today. Nothing in this plan deletes an export. Because they now sit under one folder, putting a retention rule on them later is a single bucket setting and no code at all — so it stays an easy decision to change your mind about.
This takes roughly a quarter of the work off the table, and it is the single biggest reduction available anywhere in this plan.
This is the one thing worth reading twice, because everything else in the plan follows from it.
The database will keep storing just the file name — image1.jpg — the way it does today. That means nothing in the row says where the file actually is. The full path has to be worked out fresh on every read, from the file name plus whose account is asking.
That works for the ordinary case, and the ordinary case is nearly everything. It breaks in six places, all of them real: a transferred item, where the picture belongs to the account that sent it; a marketplace listing seen by a different account; the documents table, which has no account column at all; chat attachments in a shared room; the export folders, which have no account; and links already sent out by email.
So the new table is not just a to-do list of files to copy — it is the lookup for those six cases. That is what earns it a place, and it is why the design holds together under the no-changes rule.
img/CAP-12345678/assets/image1.jpg
Built from the file name and the account making the request. Covers everything an account owns, which is the overwhelming majority of reads.
final_path ?? file_path
For the six cases above, where the reader is not the owner. One lookup on the file name — and the row answers where the file is, not only where it is headed, which is the point of keeping those two apart.
inventory/images/image1.jpg
Everything not yet copied. This is what makes the whole migration invisible to users: while it runs, nothing 404s. Removing this fallback is the very last thing you do.
Additive, holds no primary data, and can be emptied and rebuilt at any time. No existing table is touched.
Two guards, because the destination does not exist yet for every file. Anything uploaded before its owning account is known has no destination to key on, and the database treats two empty values as different from each other — so a single guard on the destination would have quietly permitted unlimited duplicates of precisely the files staging exists for. That is the same fault a guard on the account plus file name would have had, for the same reason.
So it is split in two: one row per destination for every row that has one, and one row per staging address while it does not. Between them every row is covered at every moment of its life. A transferred file still gets two rows — one source, two destinations — which is correct, and which a guard on the file name alone would have blocked.
| Column | What it holds |
|---|---|
| id | The row’s own number. Handed back when an upload link is issued, and given back on save so the right row can be marked — that is the only link between a staged file and the record that will reference it. |
| file_name | The bare name, exactly as the existing tables store it. |
| file_path | Where the file actually is, as uploaded — by the browser through an upload link, or by the server itself. inventory/images/1.jpg for anything taken before the switch, a new-bucket address after it. Written once and never rewritten: this is what the copier reads from, and without it the copier has to guess, because a finished path like img/CAP-1/assets/a.jpg has lost which old folder it came from — every picture folder collapses into assets. |
| new_bucket_source_path + | Beyond the original list. Where the file belongs — img/CAP-12345678/assets/1.jpg, img/model-library/1.png. Always account-shaped and never a staging path, because staging is where a file sits, not where it belongs. Carries {SCOPE} in the account slot until the account is settled, and the save fills that in. |
| final_path + | Beyond the original list. Where the file actually got to, written the moment the copy completes. Empty until then — so this being empty and “not copied yet” are two ways of saying one thing, rather than two facts to keep in step. |
| is_new_bucket + | Beyond the original list. Whether there is a copy to make between buckets. False for every old file, and also for a file written in the new shape into the old bucket — right path, wrong place, still to be copied. |
| account_id | The owning account, as the ordinary numeric id every other table already uses. Empty for three of the four owner kinds. |
| account_capex_id + | Beyond the original list. The account’s CAP‑number, kept beside the numeric id purely so the save can replace {SCOPE} without looking the account up. One column that removes a join from the busiest write in the migration. |
| user_id + | A second column beyond the original list. The other owner. Set for profile photos and personal documents, empty otherwise. This is the column that answers delete everything belonging to this person — the account column cannot, and searching the path text for it is not an answer. |
| owner_scope + | A third column beyond the original list. One of exactly four words — account, library, user, global — naming which of the four owner kinds this row has. Never empty, which is what makes "how many catalogue files are still pending" a question you can actually ask. The account column cannot answer it, being empty for three of the four. |
| module_name | Which part of the app the file came from — assets, copilot-quotes, document-manager. Recorded when the upload link is handed out. |
| is_committed + | Beyond the original list, and the one that makes staging actually work. The record referencing this file was saved. See the note below — one flag is not enough. |
| is_copied | The file has reached its final place. This and the flag above are what the cron reads. |
| skipped_reason | Why a file could not be copied. Clearing it puts the file back in the queue. |
| created_by + | Beyond the original list. Who asked for the upload link. Distinct from user_id, which is the person the file belongs to and is empty unless the file is personal — one records the actor, the other the owner, and they are the same person only by coincidence. |
| created_at · updated_at · deleted_at | The usual three. |
Three path columns, not two nullable ones taking turns. The earlier shape had a temp_path and a destination that swapped roles depending on which was empty — and the live table proved the cost: file_path and source_path held the identical value on every row, so the table could not say where an object was without also reading a flag. Now the source is the source, the destination is the destination, and a destination that is empty is the staging state. The whole read rule is is_copied ? final_path : file_path.
Which bucket is live, and whether the upload is itself the save, decide everything. This is the table to check an implementation against.
| Bucket | Upload is the save? | file_path | new_bucket_source_path | final_path |
|---|---|---|---|---|
| old | no | inventory/images/1.jpg | img/{SCOPE}/assets/1.jpg | — |
| old | yes | img/CAP-12345678/assets/1.jpg | img/CAP-12345678/assets/1.jpg | — |
| new | no | img/temp/assets/1.jpg | img/{SCOPE}/assets/1.jpg | — |
| new | yes | img/CAP-12345678/assets/1.jpg | img/CAP-12345678/assets/1.jpg | img/CAP-12345678/assets/1.jpg |
Row two is the one worth reading twice: the new layout is written into the old bucket when the upload is itself the save. Only the bucket is behind, not the shape — so the object has the right path in the wrong place and still waits for the copy, which is why its destination is empty. That is also why is_new_bucket is a column rather than something worked out from the paths: two of those rows have identical paths and sit in different buckets, and the only honest way to know which is to have been told.
One of each owner kind, plus one still in staging. final_path is left out here because it is empty on every one of them — nothing has been copied yet, and it records where a file got to, not where it is headed.
| owner_scope | file_name | file_path | new_bucket_source_path | account_capex_id | user_id |
|---|---|---|---|---|---|
| account | img1.jpg | inventory/images/img1.jpg | img/CAP-12345678/assets/img1.jpg | CAP-12345678 | — |
| account · staged | pump.jpg | img/temp/assets/pump.jpg | img/{SCOPE}/assets/pump.jpg | — | — |
| library | ct-scanner.jpg | inventory/images/ct-scanner.jpg | img/model-library/ct-scanner.jpg | — | — |
| user | me.jpg | profile/me.jpg | img/CAP-12345678/user/profile/me.jpg | CAP-12345678 | 4471 |
| user-root | scan.pdf | chatbot-uploads/4471/scan.pdf | doc/chatbot-uploads/4471/scan.pdf | — | 4471 |
This is the case staging exists for, and it needs two flags rather than one.
One flag cannot do it. If the only flag is “has this been copied yet”, the cron has no way to tell the save happened but the copy failed from the person picked five pictures, never saved, and closed the tab. It would move both into the account folder — and the second one is referenced by no record at all.
So saving sets a separate flag first, before the copy is attempted:
The consequence: every module has to be wired up. There is no longer a safety net that quietly moves whatever a module forgot to confirm — that safety net is exactly what would have moved the abandoned pictures. So each of the 24 places that hands out an upload link has to hand the id back on save.
Abandoned uploads become a report, not a backlog. After 30 days the cron labels them “never saved”, per account, so they stop counting as outstanding work. They are still not deleted — nothing in this plan deletes anything.
Files the server makes itself skip staging. Exports, signed PDFs, QR codes and mail attachments go straight to their final place: there is no save step for them to wait for.
Read the first column and you know how to read the rest of the row. That is the whole point of it: account_id is filled in on two rows out of five, so grouping or filtering by it alone silently omits the other three — and only user_id can find one person’s files, because the path does not name them.
Each one answers a question nothing else in the table can — and three of the five exist because of failure cases rather than features. Everything else you might reach for is already covered.
| Column | The question it answers | Why nothing else can |
|---|---|---|
| new_bucket_source_path | Where does this file belong? | The source has lost it: inventory/images/a.jpg does not say which account owns it, and every picture folder collapses into one on the way across. |
| final_path | Where did it actually get to? | The destination is an intention; this is the fact. Keeping them apart is what makes a half-finished copy visible rather than indistinguishable from a finished one. |
| is_new_bucket | Is there anything to copy at all? | Guessing it by comparing two paths is a text test on data, and it is wrong the first time a folder name appears in both buckets. |
| created_by | Who took this action? | user_id is the owner and is empty for anything not personal, so it cannot answer who uploaded. |
| account_capex_id | What replaces {SCOPE}? | account_id is the numeric id; the path needs the CAP‑number, and looking it up would put a join in the busiest write of the migration. |
| is_committed | Was the record actually saved? | Without it, five pictures uploaded and abandoned look identical to a save whose copy failed — and the cron would move both into the account folder. |
| user_id | Delete everything belonging to this person. | The account column is empty for personal files, and searching the path text is not an answer. |
| owner_scope account · library user · global | How many catalogue files are still pending? | The account column is empty for three of the four owner kinds, so it cannot group them. |
What does not need a column. Which catalogue branch a file sits in is already told by the owner kind plus the module name — and nobody ever deletes "the model library", so it needs no owner of its own. The CAP‑number is already inside the path. Files belonging to nobody have nothing to record.
That is eighteen columns in total: your nine, these eight, and the row's own id.
One gap that is not a column. Handing out an upload link is only half the writes. A second function writes everything the server generates itself — mail attachments, signed PDFs, QR codes, exports, catalogue pictures — and it is called from 17 files. It carries no owner at all today, and its default folder is an account folder.
It needs the new path rule, but not a row in the table: every file it writes has a path that can be worked out from context, so the lookup is never needed for them. That is what keeps it to one line per call site instead of a rewrite.
Steps 1 through 6 change nothing a user can see — they can all ship on ordinary release days. Behaviour changes at step 7.
Same region and storage class as the old bucket, or every copy becomes a cross-region transfer charge instead of a free operation. Copy the CORS settings across, or browser uploads fail with an unhelpful network error. No lifecycle rules at all — nothing in this plan deletes anything, and an abandoned upload is a report rather than a clean-up job.
One migration, one table, seventeen columns. Roll it back once before you rely on it — a migration you have never reversed is a migration you cannot reverse. While the shape is still settling, edit this migration in place and re-run it rather than stacking ALTERs on a table that holds no primary data and can be rebuilt at any moment.
Today both apps have the bucket baked into their build. A small endpoint returns the address instead, and the apps ask for it at start-up, keeping the last answer for offline use. The build-time value stays as the fallback, so a slow or failed call degrades to today's behaviour rather than an app with no pictures.
The mobile API starts returning the full path alongside the bare file name — alongside, never instead of. The apps prefer the full path when it is there and fall back to building their own when it is not.
Thumbnails are made by a service that lives outside all five repositories — confirm who owns and deploys it before starting this step.
Write a second one rather than changing the existing one. The old bucket’s service keeps running exactly as it does today, with no redeploy and no chance of breaking production. The new one watches the new bucket only, and owes nothing to the old flat layout.
img/ and inserts thumb/ before the file name, so account pictures, catalogue pictures, category pictures and profile photos are all handled without a case for each.One place in the code that answers "given this file name and this account, where is the file?" — trying the three tiers in order. Every place that currently glues an address together by hand calls that instead. Around 40 such places in the API, all passing the folder name already, so the change is mechanical.
This is also where the module name starts being recorded when an upload link is handed out, and where the account is threaded through the upload endpoint — which today does not receive it at all.
Gate — before step 7
Browse the app with both layouts live and the fallback counter running. No broken pictures anywhere, and the old-bucket fallback logging steadily. If reading is not already tolerant, stop here — moving uploads now creates files nothing can find.
Change one setting and redeploy. From this moment new files land in the new bucket and existing files still read from the old one. Both work.
Uploads go to img/temp/… or doc/temp/… first and only move to the account folder when the user saves the record. If they never save, the file is never filed and never moves: final_path stays empty, and an empty destination is what permanently excludes a row from the queue.
report.zip becomes reportzi, because it strips punctuation before splitting off the extension. The mobile API already fixed this; port that fix rather than writing a second one.Gate — before step 8
Upload one picture through the interface and save it. The file is in the account folder, the thumbnail is beside it, and its temp/ prefix is empty. It renders in both the web app and the mobile app.
Then check the database write counters against the reading you took in step 2. They must not have moved. That is the direct evidence for the no-changes rule — anything else means something is writing paths into an existing column.
For each account, read the rows that reference files, add them to the queue, and let the cron copy them. Smallest account first — the first one through exercises every path at a size where a mistake costs minutes.
inventory/images/img1.jpg does not say who owns it. The only way to know is to read the rows that point at it — so this step is a read-only walk through the database, which is exactly what the no-changes rule permits.Gate — before step 9
The queue is empty and the old-bucket fallback has been silent for a full 24 hours. Nothing else counts: file counts can match while a name nobody copied still resolves through the fallback. This is the only honest signal that everything is across.
Drop write access, keep read access. Keep it readable for a long time. Mobile builds already on people's phones build addresses from the old folder names and can only work against this bucket — retention is the compatibility layer.
Worth stating plainly, because the list of things being left alone is the reason this plan is small.
Every step before the last is a config change away from where it started.
| What you see | What you do |
|---|---|
| New uploads landing in the wrong place | Unset the new-bucket setting and redeploy. Uploads go back to the old bucket; whatever reached the new one just sits there harmlessly. |
| Pictures not loading | The old-bucket fallback already covers every file not yet copied. Check the old-bucket setting is present and that the API can still read it. |
| The copier behaving badly | Pause the scheduled job. The old bucket is untouched, so nothing has been lost — only postponed. |
| Wrong rows in the new table | Clear the skipped reason to re-queue, or empty the table and start again. It holds no primary data. |
Five questions were open. Four are settled, and the fifth has an answer that is not yet a number.
| Question | Answer | What it changes |
|---|---|---|
| Who owns the thumbnail service? | You do. | Step 5 is unblocked. It still ships as a second service on the new bucket, leaving the existing one untouched. |
| Do the JSON picture-link columns hold our files? | Yes — so they are copied across. | Two corrections: there are six of those columns plus a selected image, not two. And the risk is already handled — see below. |
| Existing thumbnails: copy or regenerate? | Copy them. | Already how it works, thumbnail before picture, which is what makes the whole copy cost no image processing. |
| How long does the old bucket stay readable? | A minimum of one year. | So not before 31 August 2027 — and longer if old app versions are still calling in. A floor, not a target. |
The JSON columns turned out to be safe either way, which retires the thing that worried me about them.
The rule that stops a path being prefixed twice is “leave anything containing a slash alone”. An outside web address contains slashes. So a scraped URL is skipped by the query that finds files to copy, and would pass through untouched even if it were not. A column holding a mix of our file names and outside addresses is handled correctly with nobody having to sort them out first.
One real unknown remains, and it is small: whether those arrays hold plain names or objects. Step 8 checks that with one query before writing the one that matters, because reading them wrongly would produce nonsense file names.
How big is the old bucket, in numbers? “Very big” sets the shape — pace the copying, expect it to run for days rather than hours — but it does not set the batch size or how often the job should run. Guess those and you get either a job that never finishes or one that saturates the bucket.
It is two commands, and it is the last thing standing between this plan and starting step 1.