Transitioning an application from development to a live production state is rarely a matter of just swapping environment variables.
It is an engineering trial by fire.
This afternoon, a founder reached out to me. He was having issues moving his project from a messy local sandbox to a secure, enterprise-grade production environment.
What followed was a series of engineering hurdles that forced us to confront some of the architectural decisions that are easy to ignore during development.
Here is the exact chronicle of the hurdles we faced, how we tackled them, and the architectural lessons learned along the way.
🧱 The Architecture
The application relies on a decoupled, highly responsive cloud stack:
- Authentication & Registration: Clerk OAuth, Google, GitHub, and email/password
- Backend & Core Database: Supabase (PostgreSQL)
- Serverless Logic: Supabase Edge Functions
- Webhook Verification: Svix
Because Clerk is an external authentication provider, our core backend database doesn't automatically know when a user signs up.
To bridge that gap, we built a Supabase Edge Function utilizing the official svix security protocol.
The moment a user signs up on the live site through Clerk OAuth, a secure webhook intercepts the event, verifies it, extracts the user's core data, and synchronizes an account profile row into our clean profiles table.
Simple enough.
Until we started the actual migration.
🛑 Hurdle 1: TypeScript Is Not SQL
When it came time to create our production database tables, we initially hit a roadblock trying to feed our frontend application's TypeScript interfaces directly into the Supabase SQL editor.
It sounds obvious in hindsight, but it is an easy mistake to make when you're moving quickly.
TypeScript types are not PostgreSQL schemas.
PostgreSQL expects actual SQL definitions, constraints, relationships, and data types.
So we completely translated the application's data model into standard relational SQL.
Complex frontend object arrays such as LineItem[] were mapped safely into PostgreSQL jsonb structures where appropriate.
For high-precision financial metrics such as invoice subtotals, tax rates, and totals, we used PostgreSQL's numeric type rather than relying on floating-point types that can introduce rounding problems.
The lesson here was simple:
Your frontend interface may describe your data, but it doesn't define how your database should store it.
🛑 Hurdle 2: The Password & OAuth Migration Gap
Then came the authentication migration.
Clerk strictly separates development environments from production instances.
That means you can't simply press a button and move your development users into production.
We also discovered another complication.
If you try to provision existing accounts through a standard API migration using only an email address, password-based users can end up with authentication problems or be pushed into a password-less configuration.
We didn't want that.
The goal was for users to arrive in production and simply log in as if nothing had happened.
So we pulled a raw CSV dataset of our legacy users from the Clerk development dashboard.
From there, we built a custom Node.js backend execution script that analyzed each row and handled the different authentication types accordingly.
OAuth Users
These were provisioned into Clerk production while preserving their OAuth-based authentication flow.
Password Users
For password-based accounts, we used the available password_digest bcrypt values from the exported dataset to preserve their existing password credentials during the migration.
That meant existing users wouldn't suddenly be greeted with:
“We've migrated your account. Please reset your password.”
The objective was a seamless migration.
From the user's perspective, nothing should have changed.
🛑 Hurdle 3: The Ghost of 23 Phantom Rows
This was our biggest mystery.
With the users safely migrated into Clerk production, we moved on to the operational application data.
Invoices.
Business profiles.
Products.
Other application records.
To keep track of everything, we built a progress-ledger migration script that mapped development records to their corresponding production users.
Then the script slammed on the brakes.
🛑 HARD STOP: 23 row(s) in business_profiles
reference a user_id with no production mapping.
Twenty-three rows.
Not one.
Not two.
Twenty-three.
We immediately wrote and executed a live diagnostic query to identify the owners of those records.
And every single affected row returned the same unsettling message:
🔴 NO EMAIL FOUND IN DEV PROFILES TABLE
The businesses existed.
Their creators were gone.
So what happened?
The Discovery
We uncovered a fascinating architectural loophole.
During early testing, dummy accounts had been manually deleted from the Clerk development dashboard to clear out old sign-up flows.
But there was a problem.
Clerk and our application database were two separate systems.
Deleting a user from Clerk did not automatically delete the corresponding business profile in Supabase.
Clerk knew the user was gone.
Our database didn't.
Those business profiles had effectively become orphaned data.
They were still carrying a raw Clerk user_id, but there was no longer a corresponding user profile we could use to identify the owner.
And because the profile didn't contain an email address that could independently identify the user, there was no reliable way to map those records to a production account.
It was mathematically impossible to recover the relationship from the information we had.
The migration script couldn't guess.
And it shouldn't.
🧹 The Solution
We updated the migration pipeline to detect these dead references and filter those records out in memory before they could pollute the production environment.
But we didn't want to stop at cleaning up the immediate problem.
We wanted to make sure this class of problem couldn't quietly happen again.
So we rewrote our production database relationships with strict foreign-key constraints and cascading deletes.
user_id text
references public.profiles(id)
on delete cascade
not null
Now the database itself understands the relationship.
If a user profile is deleted, PostgreSQL can automatically clean up the dependent records associated with that user.
This is a much safer approach than relying entirely on application-level cleanup logic.
The database should enforce the relationships that matter.
🎉 Production Ready
After all of that, our final dry-run simulations passed successfully.
Every live user account was mapped correctly.
Password authentication was preserved.
OAuth routing paths were locked in.
Operational business records were mapped to their legitimate owners.
Orphaned development records were excluded.
And the production database now had much stricter relational safety mechanisms in place.
The biggest lesson from this migration?
A database will quietly accept disconnected data during a relaxed development phase.
You can have deleted users with surviving business records.
You can have test data that looks harmless.
You can have relationships that exist only because everyone assumes the other system will handle them.
Everything can appear fine.
Until you try to move the entire system into production.
Then the migration script forces you to confront the truth.
And sometimes, that truth comes in the form of 23 phantom rows. 🏁
Vaya con Dios Reader🥂.
