declarative: a table's REPLICA IDENTITY is not included in the generated table.sql, causing it to get rolled back by declarative sync

[tolgap]

Describe the bug
When you change a table's REPLICA IDENTITY, it does not get inserted into the table's schema.sql by declarative generate.

To Reproduce

Given a an initial migration:

-- 01_initial.sql
create table tournify_competition.advancement_rules (
  advancement_rule_id uuid not null default gen_random_uuid (),
  tournament_id uuid not null,
  source_phase_id uuid not null,
  target_phase_component_id uuid null,
  target_phase_id uuid not null,
  constraint advancement_rules_pkey primary key (advancement_rule_id),
  constraint advancement_rules_source_phase_id_fkey foreign KEY (source_phase_id) references tournify_competition.phases (phase_id) on delete set null,
  constraint advancement_rules_target_phase_component_id_fkey foreign KEY (target_phase_component_id) references tournify_competition.phase_components (phase_component_id) on delete CASCADE,
  constraint advancement_rules_target_phase_id_fkey foreign KEY (target_phase_id) references tournify_competition.phases (phase_id) on delete set null,
  constraint advancement_rules_tournament_id_fkey foreign KEY (tournament_id) references tournify_tournaments.tournaments (tournament_id) on delete CASCADE
) TABLESPACE pg_default;

create index IF not exists advancement_rules_source_phase_id_idx on tournify_competition.advancement_rules using btree (source_phase_id) TABLESPACE pg_default;

create index IF not exists advancement_rules_tournament_id_idx on tournify_competition.advancement_rules using btree (tournament_id) TABLESPACE pg_default;

create unique INDEX IF not exists advancement_rules_replication_idx on tournify_competition.advancement_rules using btree (tournament_id, advancement_rule_id) TABLESPACE pg_default;

If I add the following migration to publish messages for this table using a specific index (because I need those columns returned for DELETE events)

-- 02_publish.sql
ALTER PUBLICATION supabase_realtime ADD TABLE
  tournify_competition.advancement_rules;

ALTER TABLE tournify_competition.advancement_rules REPLICA IDENTITY USING INDEX advancement_rules_replication_idx;

Expected behavior
I would expect a declarative generate and then a declarative sync to not generate any new migrations.

but the following migration is generated:

-- 20260428123714_declarative_sync.sql
DROP INDEX tournify_competition.advancement_rules_replication_idx;
ALTER TABLE tournify_competition.advancement_rules REPLICA IDENTITY DEFAULT;
CREATE UNIQUE INDEX advancement_rules_replication_idx ON tournify_competition.advancement_rules (tournament_id, advancement_rule_id);

System information
Rerun the failing command with --create-ticket flag.

• Version of OS: MacOS 26.4.2
• Version of CLI: v2.95.4
• Version of Docker: v4.69.0
• Versions of services:

   SERVICE IMAGE          | LOCAL                  | LINKED 
  ------------------------|------------------------|--------
   supabase/postgres      | 15.8.1.085             | -      
   supabase/gotrue        | v2.188.1               | -      
   postgrest/postgrest    | v14.10                 | -      
   supabase/realtime      | v2.86.3                | -      
   supabase/storage-api   | v1.54.1                | -      
   supabase/edge-runtime  | v1.73.13               | -      
   supabase/studio        | 2026.04.27-sha-4afbe9c | -      
   supabase/postgres-meta | v0.96.4                | -      
   supabase/logflare      | 1.39.1                 | -      
   supabase/supavisor     | 2.7.4                  | -      

Additional information:

I have ran supabase db reset --local many many times, and also running supabase db schema declarative generate and sync --no-cache.

It keeps wanting to rollback the UNIQUE INDEX and the REPLICA IDENTITY.

[avallete]

Hey there !

Thanks for the report , confirmed this is a bug in pg-delta.

What's happening: When you set a table to use a custom index for replica identity, the tool forgets which index was chosen. Every time it runs, it thinks something is wrong, drops and recreates the index, and resets replica identity back to the default so it never settles and keeps regenerating the same migration.

End result matches exactly what you saw: each declarative sync regenerates a migration that drops your custom replica-identity index, resets the table to DEFAULT, and recreates the index, never converging.

Working on a fix.