supabase db diff generates incorrect migrations for partitioned tables

[ProjectINT]

Bug Report: supabase db diff generates incorrect migrations for partitioned tables

Describe the bug

supabase db diff generates incorrect migration files when working with partitioned tables in declarative schema mode. The command attempts to drop inherited constraints that are automatically created by PostgreSQL's partitioning mechanism, leading to errors like:

ERROR: cannot drop inherited constraint "table_name_fkey1" of relation "table_name"

Additionally, the diff generates duplicate foreign key constraints with suffixes (fkey1, fkey2, ..., fkeyN) for each partition, even though these constraints are correctly inherited from the parent partitioned table.

To Reproduce

Setup:

1. Create a partitioned table with foreign key constraints:

-- supabase/schemas/photos/03_photos.sql
CREATE TABLE public.photos (
  id uuid DEFAULT gen_random_uuid(),
  bucket text NOT NULL,
  account_id text NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
  PRIMARY KEY (id, bucket)
) PARTITION BY LIST (bucket);

CREATE TABLE public.photos_avatars (LIKE public.photos INCLUDING DEFAULTS);
ALTER TABLE public.photos ATTACH PARTITION public.photos_avatars FOR VALUES IN ('avatars');

CREATE TABLE public.photos_brands (LIKE public.photos INCLUDING DEFAULTS);
ALTER TABLE public.photos ATTACH PARTITION public.photos_brands FOR VALUES IN ('brands');
-- ... create 12 more partitions

2. Create tables referencing the partitioned table:

-- supabase/schemas/photos/05_photos_foreign_keys.sql
ALTER TABLE public.users 
  ADD CONSTRAINT users_avatar_id_avatar_bucket_fkey 
  FOREIGN KEY (avatar_id, avatar_bucket) 
  REFERENCES public.photos(id, bucket) 
  ON DELETE SET NULL;

ALTER TABLE public.companies 
  ADD CONSTRAINT companies_logo_id_logo_bucket_fkey 
  FOREIGN KEY (logo_id, logo_bucket) 
  REFERENCES public.photos(id, bucket) 
  ON DELETE SET NULL;
-- ... 23 more tables with FK to photos

3. Configure declarative schemas in supabase/config.toml:

[db]
schema_paths = [
  "./schemas/photos/03_photos.sql",
  "./schemas/user/04_users.sql",
  "./schemas/companies/04_companies.sql",
  "./schemas/photos/05_photos_foreign_keys.sql"
]

Execute:

npx supabase db reset
npx supabase db diff -f test_partitions

Observe:

The generated migration contains hundreds of statements attempting to:

• Drop inherited constraints (users_avatar_id_avatar_bucket_fkey1, fkey2, fkey3, ..., fkey14)
• Add back the same constraints referencing individual partitions
• PostgreSQL rejects these operations with "cannot drop inherited constraint" error

Example output:

Found drop statements in schema diff. Please double check if these are expected:
alter table "public"."users" drop constraint "users_avatar_id_avatar_bucket_fkey1"
alter table "public"."users" drop constraint "users_avatar_id_avatar_bucket_fkey2"
alter table "public"."users" drop constraint "users_avatar_id_avatar_bucket_fkey3"
...
alter table "public"."users" drop constraint "users_avatar_id_avatar_bucket_fkey14"

With 25 tables and 14 partitions, this generates 350 duplicate FK constraints (25 × 14).

Expected behavior

When using declarative schemas with partitioned tables, db diff should:

1. Recognize inherited constraints: Constraints with numeric suffixes (fkey1, fkey2, etc.) are automatically created by PostgreSQL for each partition
2. Not generate DROP statements for inherited constraints
3. Only track parent table constraints in migrations, allowing PostgreSQL to handle inheritance automatically
4. Generate empty/minimal diff when the declarative schema matches the actual database state

The expected migration should be empty or contain only legitimate schema changes.

Screenshots

Current behavior - Generated migration with 350+ duplicate constraints:

-- Generated by: npx supabase db diff -f test_partitions

-- Attempts to drop inherited constraints (THIS FAILS)
alter table "public"."users" drop constraint "users_avatar_id_avatar_bucket_fkey1";
alter table "public"."users" drop constraint "users_avatar_id_avatar_bucket_fkey2";
alter table "public"."users" drop constraint "users_avatar_id_avatar_bucket_fkey3";
-- ... fkey4 through fkey14 for each partition

-- Attempts to recreate constraints pointing to individual partitions
alter table "public"."users" add constraint "users_avatar_id_avatar_bucket_fkey1" 
  FOREIGN KEY (avatar_id, avatar_bucket) 
  REFERENCES photos_avatars(id, bucket) ON DELETE SET NULL;

alter table "public"."users" add constraint "users_avatar_id_avatar_bucket_fkey2" 
  FOREIGN KEY (avatar_id, avatar_bucket) 
  REFERENCES photos_brands(id, bucket) ON DELETE SET NULL;
-- ... duplicates for all 14 partitions × 25 tables = 350 constraints

Verification that constraints are inherited:

postgres=# \d users
                                     Table "public.users"
   Column    |  Type  | Collation | Nullable |      Default       
-------------+--------+-----------+----------+--------------------
 avatar_id   | uuid   |           |          | 
 avatar_bucket | text |           |          | 
Foreign-key constraints:
    "users_avatar_id_avatar_bucket_fkey" FOREIGN KEY (avatar_id, avatar_bucket) 
      REFERENCES photos(id, bucket) ON DELETE SET NULL

postgres=# SELECT conname, coninhcount, conislocal 
           FROM pg_constraint 
           WHERE conrelid = 'users'::regclass 
           AND conname LIKE '%fkey%';
                    conname                     | coninhcount | conislocal 
-----------------------------------------------+-------------+------------
 users_avatar_id_avatar_bucket_fkey            |           0 | t
(1 row)

Workaround attempts

All of these were tried and failed to resolve the issue:

1. ❌ Using ATTACH PARTITION instead of PARTITION OF - PostgreSQL still creates inherited constraints
2. ❌ Using INCLUDING DEFAULTS instead of INCLUDING ALL - Helps avoid some duplicates but doesn't solve the core issue

A minimal reproduction case is available at: [your-repo-link-here]

Workaround (temporary)

Until fixed, the only workaround is to:

1. Avoid using partitioned tables with declarative schemas
2. Or manually edit every generated migration to remove inherited constraint operations
3. Or use non-declarative schema management (migrations only)

None of these are viable for production use.

---

Environment:

• Node.js: v24.2.0
• npm: 11.3.0
• OS: Ubuntu 24.04.3 LTS (Noble Numbat) - WSL2
• Architecture: x86_64

[ProjectINT]

Similar issue: #2970

[7ttp]

Hi @ProjectINT Thanks for reporting this!
i do confirm i was able to reproduce this on my end.
ill create a pr shortly.

[SumitKumar-17]

I suppose the problem was that
migra was treating inherited constraints on partitioned tables as independent constraints. This caused it to generate DROP CONSTRAINT statements for constraints that were inherited (which Postgres forbids dropping directly) and ADD CONSTRAINT statements to recreate them (which creates duplicates).
@7ttp are you working on this or can I work on this ?

[7ttp]

Hi @SumitKumar-17
Thanks for your interest in tackling this! 💚
Am already about halfway through a fix.