I'm thinking about using migrations only for tables, and then using a script that diffs everything else to local files - if the view, function, trigger etc differs from the file, drop it and recreate it. So, on deploy we'd run prisma migrate, and then our own script that synchronizes non-table items. This would allow us to treat all non-table objects as checked in source code - a single source of truth. Migrations for these kinds of objects just drop and recreate them anyway. Is this a reasonable approach? Are there any tools that work like this that I could use so I don't have to write my own?

If you want things checked into code, then don't use the UI to manipulate the DB. Just do everything as sequential migration files. I don't know anything about how Prisma deals with DB schema; I just manage my DB old-school DBA style.

@vick yes, I'm planning to not use the UI. This is more about not having to create a source file with new copy of the function each time I modify it - I can keep a history with git instead of keeping each version around forever. Just wondering if there any any obvious drawbacks with this approach

You want your DDL files to be immutable, and applied in order. You're not tearing down and rebuilding your production DB every time you make a change and you want your migration files to reflect that. This lets you recreate the production DB exactly as it is if you need to for development copies. Also, Supabase tracks the applied migrations on your production DB, so re-running one of them or running some DDL outside of that scheme will confuse the heck out of it. Don't fight the opinions of your tool chain, work with them.

@vick > You're not tearing down and rebuilding your production DB every time you make a change and you want your migration files to reflect that. This approach would do the exact same thing as the migration files. When changing a trigger in a migration for example, you drop it and then create it again. My tool would check if there's a difference, and if there is it would to the same thing as the migration > Supabase tracks the applied migrations on your production DB, so re-running one of them or running some DDL outside of that scheme will confuse the heck out of it. That's an interesting point, thank you