In the modern landscape of web development, the promise of "managed backends" and "no-code" builders often comes with a hidden cost: the loss of direct control. For one developer operating a specialized travel directory, this trade-off recently reached a breaking point. The project, which encompasses a 750-page directory, hundreds of listings, and a meticulously maintained log of affiliate booking clicks, had reached a level of complexity where the constraints of its original environment were no longer sustainable.
The front end of the directory had long been hosted on Vercel, a standard choice for high-performance web applications. However, the heart of the operation—the database—resided within Lovable Cloud. While Lovable provides a managed backend powered by Supabase, it effectively acts as a "walled garden." Users are provided with an app builder and a query tool, but the underlying infrastructure remains largely inaccessible. There was no administrative dashboard, no direct connection string for external tools, and no ability to perform a standard pg_dump to export data. Furthermore, the limitations of the free tier meant that excessive use of the provided query tool would result in a twenty-minute lockout, a significant hurdle for any intensive data management task.
This week, the developer successfully completed a high-stakes migration, moving the entire database to a self-owned Supabase project. The process, executed with the assistance of Claude Code on a local home server, serves as a masterclass in the pitfalls of "schema drift" and the philosophical difference between a database’s recorded history and its actual state.
The migration files weren’t the database
The initial strategy for the migration seemed straightforward: replay the project’s migration history. In theory, a Supabase project’s migration folder contains a chronological record of every SQL command ever executed to shape the database. The repository in question contained 89 such files. However, theory and reality diverged almost immediately.
The automated replay process failed at file 29 due to a missing row that a subsequent command expected to find. It failed again at file 30 because of a naming conflict; a table titled favorites was being created for a second time by a different file, this time with a conflicting set of columns.
Upon closer inspection, the developer discovered a chaotic history. Of the 89 files, eighteen had been authored by hand rather than generated by the Lovable platform. Some of these manual scripts had been executed on the live database via a SQL editor and never properly integrated into the migration flow. Others had apparently never been run at all. It became clear that the migrations folder was not a factual record of the database’s evolution; it was a fragmented narrative written by multiple authors over time, none of whom had verified the story against the actual production environment.
Faced with a "story" that didn’t match the "reality," the developer pivoted to a more forensic approach. Rather than relying on the migration logs, they turned to the Postgres catalog itself. Postgres maintains an internal description of every object it houses—from tables and columns to complex triggers, security policies, and permissions. By querying the live catalog directly, the developer generated a 438-statement script that described the database exactly as it existed in its current state. This script, when executed, rebuilt the entire structure in a single, clean transaction.
To verify the accuracy of this new structure, a "fingerprinting" system was developed. By running a comprehensive query against both the old and new databases and hashing the results across eleven categories—including 439 columns, 108 constraints, 109 indexes, and 101 row-level security policies—the developer could mathematically prove the structures were identical. When all eleven hashes matched, the structural phase of the migration was deemed a success.
Rows: the checksum caught the transport
With the architecture in place, the focus shifted to the content: the listings, pages, and approximately 800 images that constitute the directory’s value. The verification method remained rigorous; for every table, the developer hashed the text of every row on both the source and the destination.
The initial results were puzzling. While seven tables matched perfectly, three did not. A granular investigation revealed a subtle but critical issue in how data was being transported. Several columns in the database were defined as a plain numeric type. In Postgres, this type preserves the exact precision of the stored data. For example, a travel rating stored as 5.0 remains 5.0, and a geographical latitude ending in a zero, such as 40.056930, retains that final digit.
However, the Lovable query tool used for the export returned results in JSON format. The JSON standard does not distinguish between an integer and a floating-point number in a way that preserves trailing zeros. Consequently, during the transport process, 5.0 was truncated to 5. While a human reader might consider these values equivalent, a checksum does not. More importantly, the change would have altered the visual presentation of the site, turning a professional "5.0" rating into a plain "5."
The solution was to ensure that every row left the live database as explicit text, bypassing the precision-stripping tendencies of the JSON intermediary. Once this adjustment was made, the three problematic tables matched to the last digit.
Pages: the site wasn’t deterministic
The final validation of a database copy is the output of the application it supports. Because this travel directory utilizes static site generation—prerendering its 745 pages by reading directly from the database—the developer was able to run a "diff" between two full builds: one generated from the original database and one from the new copy.
The comparison initially flagged 71 differences. Every discrepancy occurred in the "nearby places" section of the pages. The underlying query for this feature sorted listings by their rating, but because many listings shared the same rating, the sort order was technically undefined for those "tied" rows. In such cases, Postgres returns rows in the order they appear on the disk.
A database that has been actively edited for a year stores its data differently than a freshly loaded copy. Consequently, the "nearby places" were reshuffling simply because the data occupied different physical sectors on the server. This revealed a pre-existing bug: the live site was non-deterministic. The "nearby places" box could change between any two deployments without any underlying data changes. By adding a secondary sort criteria—"then by name"—the developer made the process deterministic. A subsequent comparison of 1,309 generated files returned zero differences.
The copy surfaced bugs the original was hiding
One of the unexpected benefits of the migration was its role as a diagnostic tool. Running the site’s server code against a fresh, clean database forced the execution of code paths that had remained dormant for months. This process revealed that the live site was, in fact, partially broken.
A contact form on the directory had been silently failing since May. A previous security update had modified four "notify me" triggers to call a function that did not exist in the production database. Every time a user attempted to send a message or post a review, the database encountered an error on insertion and aborted the save. Because the form was rarely used, the error had gone unnoticed.
Furthermore, a second bug was discovered within the notification logic itself. The function responsible for sending alerts was designed to read a secret key from the Supabase vault via a REST API. However, the vault is not exposed over that specific API, resulting in a consistent 401 Unauthorized error. The migration process allowed the developer to identify and fix the first bug on the live site immediately, while the second was resolved within the new project environment.
The check I didn’t write
Before the final switch, the developer conducted a full rehearsal using a private preview deployment. This stage was designed to catch any "human-level" errors that automated hashes might miss. While the automated checks had been exhaustive, the rehearsal immediately revealed a glaring issue: the admin dashboard reported zero users, despite the database containing twenty.
The discrepancy was traced to a Row-Level Security (RLS) policy. The dashboard calculated the user count from a profiles table. This table had a security policy that allowed profile visibility only if the user had written a public review, which required a subquery into the reviews table. On the new database, the permissions were set such that signed-in users were blocked from reading the reviews table entirely. Postgres, following its strict security protocols, refused the subquery and returned zero results for the dashboard.
The developer realized that while their "fingerprint" had checked table-level permissions, it had completely ignored column-level permissions. In July, an administrator had locked down the reviews table by revoking access to the entire table and then painstakingly granting it back for 155 individual columns to protect sensitive data like booking confirmations.
This oversight led to a significant realization: "Identical" is not an absolute state between two databases; it is a reflection of the specific metrics chosen for comparison. The hashes were correct about everything they were asked to check, but they hadn’t been asked to check the columns. After identifying the 155 missing grants and broadening the comparison to include schemas, types, and storage settings, the two environments were finally, truly synchronized.
The switch
The final transition was an exercise in planned simplicity. With the structure verified and the bugs squashed, a final export of every table was performed as text and loaded into the new project. A final row-by-row comparison confirmed that all 34 tables were identical.
The switch itself required only a single code commit to update the site’s database proxy settings and the adjustment of two environment variables on Vercel. Because the original database remained untouched throughout the process, the developer maintained a "way back"—a fail-safe that would allow the site to be reverted to its original state within minutes if any unforeseen issues arose.
Post-migration checks confirmed the success of the move. Every sitemap page returned a 200 OK status, with titles, canonical links, and structured data remaining unchanged. For the first time since May, the contact form functioned correctly, and notifications were successfully delivered. By the time the switch was complete, no new data had been written to the old database, ensuring a perfect handoff.
The move from a managed, restricted environment to a self-owned infrastructure was more than a technical task; it was a reclamation of the site’s foundation. Through a combination of AI-assisted labor and rigorous manual verification, the developer transformed a "black box" backend into a transparent, deterministic, and fully understood system.
