Ability to move sites between organizations

[pompydev]

Currently, once a site analytics has been created it is stuck to whichever organization it was created in. The ability to move sites to different organization from Site Settings would be very helpful.

[goldflag]

will add this

[didyouexpectthat]

I wanted to do this today. If you're running a self-hosted deployment and are comfortable working directly with PostgreSQL, this script allows you to move one or more sites from one organization to another safely.

The approach is:

• Identify sites by a domain pattern (e.g. %.example.com)
• Update the owning organization for those sites
• Update any site-scoped tables that redundantly store an organization reference
• Perform all actions inside a transaction

Get your organizations from psql:

SELECT id, name, slug
FROM public.organization
ORDER BY name;

Use id for __OLD_ORG_ID__ and __NEW_ORG_ID__

Find your sites

SELECT site_id, name, domain, organization_id
FROM public.sites
WHERE domain LIKE '%.example.com'
ORDER BY domain;

Put your pattern in __DOMAIN_PATTERN__

The psql transcation is

-- Move a set of sites (selected by domain pattern) from one organization to another.
--
-- SAFE BY DEFAULT:
--   • Runs inside a transaction
--   • Ends with ROLLBACK
--   • Replace ROLLBACK with COMMIT after verification
--
-- REQUIRED REPLACEMENTS:
--   __DOMAIN_PATTERN__   e.g. '%.example.com'
--   __OLD_ORG_ID__       source organization ID
--   __NEW_ORG_ID__       destination organization ID

BEGIN;

-- ==========================================================
-- 1) Confirm which sites will be moved
-- ==========================================================
WITH moving AS (
  SELECT site_id, domain, organization_id
  FROM public.sites
  WHERE domain LIKE '__DOMAIN_PATTERN__'
)
SELECT *
FROM moving
ORDER BY domain;

-- ==========================================================
-- 2) Sanity check: counts of site-owned data
-- ==========================================================
WITH moving AS (
  SELECT site_id
  FROM public.sites
  WHERE domain LIKE '__DOMAIN_PATTERN__'
)
SELECT 'funnels'::text AS tbl, site_id, COUNT(*) AS rows
FROM public.funnels
WHERE site_id IN (SELECT site_id FROM moving)
GROUP BY site_id

UNION ALL
SELECT 'goals', site_id, COUNT(*)
FROM public.goals
WHERE site_id IN (SELECT site_id FROM moving)
GROUP BY site_id

UNION ALL
SELECT 'gsc_connections', site_id, COUNT(*)
FROM public.gsc_connections
WHERE site_id IN (SELECT site_id FROM moving)
GROUP BY site_id

UNION ALL
SELECT 'user_aliases', site_id, COUNT(*)
FROM public.user_aliases
WHERE site_id IN (SELECT site_id FROM moving)
GROUP BY site_id

UNION ALL
SELECT 'user_profiles', site_id, COUNT(*)
FROM public.user_profiles
WHERE site_id IN (SELECT site_id FROM moving)
GROUP BY site_id

UNION ALL
SELECT 'import_status', site_id, COUNT(*)
FROM public.import_status
WHERE site_id IN (SELECT site_id FROM moving)
GROUP BY site_id

ORDER BY tbl, site_id;

-- ==========================================================
-- 3) Move the sites to the new organization
-- ==========================================================
UPDATE public.sites
SET organization_id = '__NEW_ORG_ID__'
WHERE domain LIKE '__DOMAIN_PATTERN__'
  AND organization_id = '__OLD_ORG_ID__';

-- ==========================================================
-- 4) Move per-site organization references that should follow
-- ==========================================================
UPDATE public.import_status i
SET organization_id = '__NEW_ORG_ID__'
WHERE i.site_id IN (
  SELECT site_id
  FROM public.sites
  WHERE domain LIKE '__DOMAIN_PATTERN__'
)
  AND i.organization_id = '__OLD_ORG_ID__';

-- ==========================================================
-- 5) Verify: sites now belong to the new organization
-- ==========================================================
SELECT site_id, domain, organization_id
FROM public.sites
WHERE domain LIKE '__DOMAIN_PATTERN__'
ORDER BY domain;

-- ==========================================================
-- 6) Verify: org distribution in import_status for moved sites
-- ==========================================================
SELECT site_id, organization_id, COUNT(*) AS rows
FROM public.import_status
WHERE site_id IN (
  SELECT site_id
  FROM public.sites
  WHERE domain LIKE '__DOMAIN_PATTERN__'
)
GROUP BY site_id, organization_id
ORDER BY site_id, organization_id;

-- ==========================================================
-- SAFE DEFAULT
-- ==========================================================
ROLLBACK;

Again, replace ROLLBACK with COMMIT only when you're satisfied with the output.