Skip to main content

Knowledge > Runbooks > Customer Ops > Merge Duplicates

Merge Duplicate Church Records

Consolidate two duplicate church entries in the churches table into a single canonical record.

Prerequisites​

  • Supabase access
  • Explicit founder approval — this is a manual data operation affecting public directory data
  • Both church UUIDs identified

CRITICAL Rules​

  • Never DELETE either church row — set directory_visible = false on the duplicate.
  • Always keep the row with the most complete data and/or premium subscription as the canonical.
  • Foreign key safety: multiple tables reference church_id. Update them all before hiding the duplicate.
  • Test every query with SELECT first.

Steps​

1. Identify both records and choose the canonical one​

SELECT id, name, slug, email, address, city, state, phone, website,
       directory_visible, created_at
FROM churches
WHERE name ILIKE '%[church-name]%'
   OR slug IN ('[slug1]', '[slug2]')
ORDER BY created_at;

Choose the canonical record (the one to keep) based on:

  • Has a premium subscription (premium_churches row)
  • Was claimed first (church_admin_sessions with earlier created_at)
  • Has more complete / accurate data
  • Has the most recognizable slug

Note: canonical UUID = keep this one. duplicate UUID = will be hidden.

2. Copy missing data from duplicate to canonical​

Review each field on both records. For fields that are better on the duplicate:

UPDATE churches
SET
  phone = '[value-from-duplicate]',     -- example
  website = '[value-from-duplicate]',   -- example
  updated_at = now()
WHERE id = '[canonical-uuid]';

Do this field by field. Don't overwrite good data with worse data.

3. Update premium_churches if the duplicate has the subscription​

-- Check which record has the premium subscription
SELECT church_id, plan, tier, stripe_subscription_id
FROM premium_churches
WHERE church_id IN ('[canonical-uuid]', '[duplicate-uuid]');

If the duplicate has the subscription and the canonical does not:

UPDATE premium_churches
SET church_id = '[canonical-uuid]',
    updated_at = now()
WHERE church_id = '[duplicate-uuid]';

4. Update church_admin_sessions if the duplicate has sessions​

SELECT church_id, token, created_at FROM church_admin_sessions
WHERE church_id IN ('[canonical-uuid]', '[duplicate-uuid]');

If the duplicate has sessions that should transfer to the canonical:

UPDATE church_admin_sessions
SET church_id = '[canonical-uuid]'
WHERE church_id = '[duplicate-uuid]';

5. Update church_voice_agents if applicable​

SELECT church_id FROM church_voice_agents
WHERE church_id = '[duplicate-uuid]';

If exists, update to canonical:

UPDATE church_voice_agents
SET church_id = '[canonical-uuid]'
WHERE church_id = '[duplicate-uuid]';

6. Update organization_settings if applicable​

UPDATE organization_settings
SET church_id = '[canonical-uuid]'
WHERE church_id = '[duplicate-uuid]';

7. Check for any other foreign key references​

-- Find all tables referencing church_id
SELECT table_name FROM information_schema.columns
WHERE column_name = 'church_id'
ORDER BY table_name;

Review each table and update if the duplicate UUID is referenced.

8. Hide the duplicate​

UPDATE churches
SET directory_visible = false,
    updated_at = now()
WHERE id = '[duplicate-uuid]';

9. Verify​

-- Canonical should have all data, directory_visible = true
SELECT id, name, slug, directory_visible, updated_at FROM churches WHERE id = '[canonical-uuid]';

-- Duplicate should be hidden
SELECT id, name, directory_visible FROM churches WHERE id = '[duplicate-uuid]';

-- Premium subscription should point to canonical
SELECT church_id, plan FROM premium_churches WHERE church_id IN ('[canonical-uuid]', '[duplicate-uuid]');

Verification​

On PewSearch, the canonical record should appear in search results. The duplicate listing should no longer appear. If the admin previously used the duplicate's token, generate a new session for the canonical UUID.

See Also​