db diff: Incorrect migration order for UDF used in CHECK constraint (Declarative Schema)

[jumski]

Describe the bug

When using declarative schemas where a table's CHECK constraint depends on a User-Defined Function (UDF) defined in a separate, numerically earlier file (e.g., 00_function.sql and 01_table.sql), the supabase db diff command generates a migration file with incorrect statement ordering. The table definition appears before the required UDF definition, causing subsequent migration application to fail.

To Reproduce

There is a repo that reproduces this bug at https://github.com/pgflow-dev/dbdiff-udf-check-repro

Steps to reproduce the behavior:

1. Have two schema files in supabase/schemas: 00_function.sql defining a UDF, and 01_table.sql defining a table with a CHECK constraint using that UDF.
2. Run the command npx supabase db diff -f initial.
3. Inspect the generated migration file in supabase/migrations. Observe that the ALTER TABLE statement (with the CHECK constraint) appears before the CREATE FUNCTION statement.
4. Run npx supabase migration up. See error ERROR: function only_letters(text) does not exist (SQLSTATE 42883).

Generated migration snippet

Expected behavior

The migration file generated by supabase db diff should respect the dependencies defined implicitly by the file ordering in supabase/schemas. The UDF definition should appear before the table definition that uses it in a CHECK constraint, allowing supabase migration up to succeed.

System information

Supabase CLI version: 2.22.6

CRASH ID: 095a7affb19546f3be2f26baf9b22cd5

Additional context

• Setting db.migrations.schema_paths in config.toml does not affect this issue.
• Using the --use-pg-schema flag results in a different error (check constraints that depend on UDFs: not implemented), as pg-schema-diff does not support UDFs in check constraints.

[jumski]

Tried also with --use-pgadmin without luck:

❯ npx supabase@latest db diff --local --schema pgflow --use-pgadmin --file initial --debug
Supabase CLI 2.22.6
2025/04/25 06:33:45 PG Send: {"Type":"StartupMessage","ProtocolVersion":196608,"Parameters":{"database":"postgres","user":"postgres"}}
2025/04/25 06:33:45 PG Recv: {"Type":"AuthenticationSASL","AuthMechanisms":["SCRAM-SHA-256"]}
2025/04/25 06:33:45 PG Send: {"Type":"SASLInitialResponse","AuthMechanism":"SCRAM-SHA-256","Data":"n,,n=,r=jPVEdRa34S9XTqRtTnmd91SK"}
2025/04/25 06:33:45 PG Recv: {"Type":"AuthenticationSASLContinue","Data":"r=jPVEdRa34S9XTqRtTnmd91SKiY0PYh8Xb3RyT4D3u2Fn0ANl,s=a/WlGi6iO0BqjbE406yxIw==,i=4096"}
2025/04/25 06:33:45 PG Send: {"Type":"SASLResponse","Data":"c=biws,r=jPVEdRa34S9XTqRtTnmd91SKiY0PYh8Xb3RyT4D3u2Fn0ANl,p=2RUe7RUdm/6wjyIDPY1eROt9MSoWQxaap6kGACA+EE0="}
2025/04/25 06:33:45 PG Recv: {"Type":"AuthenticationSASLFinal","Data":"v=39TuglAgsdX9C6EsWmSmoGkEbXzST4+hitaPnUtiT6A="}
2025/04/25 06:33:45 PG Recv: {"Type":"AuthenticationOK"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"application_name","Value":""}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"client_encoding","Value":"UTF8"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"DateStyle","Value":"ISO, MDY"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"default_transaction_read_only","Value":"off"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"in_hot_standby","Value":"off"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"integer_datetimes","Value":"on"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"IntervalStyle","Value":"postgres"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"is_superuser","Value":"off"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"server_encoding","Value":"UTF8"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"server_version","Value":"15.8"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"session_authorization","Value":"postgres"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"standard_conforming_strings","Value":"on"}
2025/04/25 06:33:45 PG Recv: {"Type":"ParameterStatus","Name":"TimeZone","Value":"UTC"}
2025/04/25 06:33:45 PG Recv: {"Type":"BackendKeyData","ProcessID":202,"SecretKey":447323594}
2025/04/25 06:33:45 PG Recv: {"Type":"ReadyForQuery","TxStatus":"I"}
Initialising schema...
+ ulimit -nshadow database...
+ '[' '!' -z '' ']'
+ export ERL_CRASH_DUMP=/tmp/erl_crash.dump
+ ERL_CRASH_DUMP=/tmp/erl_crash.dump
+ '[' false = true ']'
+ echo 'Running migrations'
+ sudo -E -u nobody /app/bin/migrate
+ '[' true = true ']'abase...
+ echo 'Seeding selfhosted Realtime'
+ sudo -E -u nobody /app/bin/realtime eval 'Realtime.Release.seeds(Realtime.Repo)'
+ echo 'Starting Realtime'...
+ ulimit -n
+ exec /app/bin/realtime eval '{:ok, _} = Application.ensure_all_started(:realtime)
{:ok, _} = Realtime.Tenants.health_check("realtime-dev")'
[os_mon] cpu supervisor port (cpu_sup): Erlang has closed
[os_mon] memory supervisor port (memsup): Erlang has closed
Seeding globals from roles.sql...
2025/04/25 06:33:50 PG Send: {"Type":"Terminate"}
⣷ Diffing schema: pgflow

CRASH ID: f82a23ec52b64e51b69306c5a9ae0cb3