All 41 REST resources ported to Supabase Edge Functions over Postgres, every row copied and checked against the original, and the app switched over live. The ServiceNow instance was never written to.
The trigger was not a feature. It was where the data was sitting.
Everything — 593 score entries, 25 letters, 10 photos, two settled months, the pet and its furniture — lived on a free ServiceNow Personal Developer Instance. A PDI hibernates after a few idle days and, left inactive long enough, is reclaimed outright with everything on it. There is no vendor backup. tools/backup.js existed precisely because that risk was understood, but a backup script is a mitigation, not a fix.
Postgres also removes a whole class of problems the ServiceNow schema forced. Fixed-width string fields were a live hazard: u_pet_equipped is String(1000) and silently truncates, which is why the room layout had to be compacted into two-letter codes to fit. On Postgres text is unbounded and that entire constraint disappears.
The contract was held fixed on purpose, so the frontend barely moved.
u_love_* tables, fixed-width string fieldsu_love_authString(200000) columnu_match by hand in scripttext, real foreign keysThe 41 ServiceNow resources collapse into 29 functions (plus backup-full) because ServiceNow needed a separate resource record per HTTP method on the same path, while one function can branch on req.method. GET /config and PUT /config are now a single config function.
app.js already funnelled through two functions, snFetch and snPublicFetch. Those two now rewrite the old ServiceNow-style path onto a function slug — /entries/{id} becomes entries-id?id=…, /shop/buy/{id} becomes shop-buy?id=… — so all ~30 call sites are untouched. All 30 translations are unit-checked.Seven phases, in order. Each one had to pass before the next began.
12 Postgres tables mirroring the ServiceNow ones, plus indexes matching the queries that actually run — including a partial index on entries(match_id) where monthly_id is null, which is the exact shape of the "unsettled entries" query the app leans on. A private photos bucket, 5 MB cap, image/* only.
The first RLS pass was too permissive and was caught on review before any data existed. Details in the security section below. The corrected policies were then tested by attacking them with a real signed-in session, not by reading the config.
Auth, pairing and the config resource were built and tested end to end first — register, pair by code, read and write config, confirm the partner sees the same row — before the remaining 38 resources were written against the same pattern. Finding a structural mistake in one function is cheap; finding it in 29 is not.
Entries, categories, rewards, punishments, settle, history, shop, bag, claims, letters, photos, furniture, avatars. The business rules that CLAUDE.md records as having broken before were carried across verbatim and given their own tests:
A read-only pull from ServiceNow, imported into Supabase, then compared field by field against the live original — 58 checks, covering totals, per-person scores, claim flags, timestamps, furniture ids and photo bytes. Re-runnable: the importer can wipe and replay the whole couple.
A final read-only pull at 12:56, re-imported over a clean wipe, re-verified against live ServiceNow, then the branch merged to main and pushed. GitHub Pages rebuilt and both phones moved across. Confirmed by logging into the real site and checking scores, the pet, 情书, 签到, the 中秋 drop and both partners' weather.
partner B's seven unredeemed bag items came off a screenshot rather than an export — /bag only ever returned the caller's own rows, so they were unreadable remotely. That gap is now closed permanently; see phase 07.
The weekly job was still reading ServiceNow, which had taken no writes since the cutover — faithfully archiving a frozen copy while the live data accumulated unbacked. Since "the instance could be reclaimed" was the entire reason for moving, that undid most of the point.
It now reads a new backup-full endpoint, which is the Supabase counterpart of the ServiceNow r41 resource that was written but never deployed. Its bag query deliberately omits the character filter, so either partner's login captures both bags — the gap that forced partner B's items to be re-typed. Photo bytes are pulled into the snapshot rather than left as signed URLs, which expire.
Every figure read from both systems at the same moment of cutover and compared. This is the evidence for "nothing was lost".
| What | ServiceNow | Supabase | Result |
|---|---|---|---|
| Entries, all timesettled and unsettled | 593 | 593 | exact multiset |
| Lifetime points | 2094 | 2094 | match |
| Current score · partner A | 206 | 206 | match |
| Current score · partner B | 228 | 228 | match |
| Settled months2026-07, 2026-08 with results | 2 | 2 | pts + results |
| Letterstext, sender, opened, timestamp | 25 | 25 | no tz shift |
| Photosbase64 → Storage objects | 10 | 10 | all download |
| Furnitureincl. year-locked keepsakes | 16 | 16 | ids preserved |
| 小窝币 spentthe coin ledger | 800 | 800 | match |
| Pet the petspecies, EXP, baseline | dog · 1862 · 1382 | dog · 1862 · 1382 | match |
Room layoutpet_equipped blob | 12 pieces | 12 pieces | byte-identical |
| Categories / shop / rewards | 18 / 5 / 4 | 18 / 5 / 4 | values + flags |
| partner B's unredeemed bagunreadable remotely — see below | 7 | 7 | dates match |
| Goal the shared goal | 8000 | 8000 | match |
null for an empty note where Postgres returns "". The data was identical; the comparison was wrong. It was asserting the storage encoding instead of the meaning, which is the exact mistake CLAUDE.md §9.1 warns about. The comparison now normalises both and counts entries as a multiset, because the same person legitimately logs the same category twice in one day.Three were found before any real data moved; two surfaced after the switch. Recorded here in the same spirit as CLAUDE.md — what broke, and why it was possible.
decorThe bag.source_type check constraint allowed only purchase and reward. But furniture is a third kind, written by r40 as decor. Every piece the couple owns — including the year-locked 2026 中秋 keepsakes that cannot be re-bought once the year passes — would have been rejected at insert.
Worse, the two kinds do not even store the same thing: a purchase points at a shop row, while furniture stores a text catalog id like mooncake_box_27. One uuid column could never have held both, so they are now separate columns, coalesced back into the single itemId the API has always returned.
ServiceNow reference fields never blocked a delete; a shop item could be removed while bag rows still pointed at it, leaving a dangling reference nobody noticed. Real foreign keys refuse that delete instead — so deleting a shop item the couple had already bought began returning an error where it used to work.
Fixed with ON DELETE SET NULL rather than by dropping the constraint. Bag rows already snapshot the item's name and icon at purchase time, so the history survives intact and only the link clears. The same applied to deleting a category that had entries against it.
405Everything passed from the command line and then the app could not log in at all, reporting only "Failed to fetch". A browser preflights any cross-origin request carrying a custom header — and apikey is one — but the functions answered OPTIONS with "Method not allowed", so the real request was never sent.
Every function now runs through one wrapper that answers the preflight. This is the same class of problem as the CORS rule ServiceNow needed; it just had to be solved again on the new side.
Supabase revokes every existing session when a password changes — so the token the request was made with is dead the instant it works. The next call answered 401, the refresh token had been revoked too, and the app reported 当前密码不对: a successful change presenting itself as a typo. Changing a password twice in a row was impossible.
The endpoint now signs back in server-side and returns a fresh session for the client to adopt. A wrong current password answers 403 rather than 401, so "you mistyped it" and "your session is gone" stop being indistinguishable.
GlideRecord string queries ignore case, so cs had logged in as partner A for as long as the app existed. Postgres = does not. After the switch a lowercase username answered 账号不存在，请先注册 — the account looked deleted rather than mistyped.
Matching case-insensitively is only half of it, and the second half is the one that bites: the synthetic email Supabase Auth needs is derived from the username, so cs and partner A hash to two different addresses and the password check fails even once the profile is found. The address is now always built from the stored spelling — which is also why the app still displays partner A whatever you type.
The move was an opportunity to close two real holes, and the review found a third that the migration itself had introduced.
u_love_auth.u_password stored the password as typed. Supabase Auth hashes them, and issues expiring JWTs instead of a permanent bearer key.FOR ALL policy scoped by couple. That would have let anyone with a valid session token skip the Edge Functions entirely and write straight to Postgres — forging a score, maxing pet EXP, marking every reward claimed, rewriting settled history. Policies are now SELECT-only; all writes go through functions holding the service-role key.profiles update policy only checked id = auth.uid() — "it's your own row" — which did not stop someone changing their own match_id to another couple's and inheriting read access to everything in it. That bypassed the 6-digit pair code entirely. Both write policies on profiles were removed.PATCH a row directly. It returned HTTP 204, which looks like success — and changed nothing. The value was re-read afterwards to confirm.GET. It is still live and intact, now frozen at the cutover moment and serving as the rollback.app.js and read the device clock. They never touched the backend and are unaffected. What did have to survive is the record of which pieces were bought — verified above, all 16.The migration is done and live. These are the tail-end items.
db-migrate and migrate-import exist for this job alone. They are gated behind a secret, but they should not outlive their purpose.tools/backup.local.servicenow.json.test-full-system-v2.sh and its siblings now exercise a backend nothing uses.Rollback is one revert and a push. But ServiceNow stopped receiving writes at 12:56 on 11 September, so it is a snapshot that ages: reverting tomorrow costs a day of entries, reverting in a fortnight costs a fortnight. The realistic window is short, and after that it is an archive rather than a fallback.