Migration Script to Restore originaltenantid for Shadow Users in the central tenant

Problem Description

Shadow users require the originaltenantid custom field to function properly. However, when the custom field definition is deleted from the system, all references to that custom field are automatically removed from user objects, leaving shadow users without this critical field.

Example of Affected User

Current State (Broken):

{
  "username": "test_fdyrb",
  "id": "d06022af-1e9b-4b29-b418-25e48a673f76",
  "active": true,
  "type": "shadow",
  "customFields": {},
  "..."
}

Desired State (Fixed):

{
  "username": "test_fdyrb",
  "id": "d06022af-1e9b-4b29-b418-25e48a673f76",
  "active": true,
  "type": "shadow",
  "customFields": {
    "originaltenantid": "university"
  },
  "..."
}

Verification Query

Check affected users before running the script:

SELECT
  u.id,
  u.jsonb->>'username' as username,
  u.jsonb->>'type' as user_type,
  u.jsonb->'customFields' as custom_fields,
  ut.tenant_id
FROM ecs_mod_users.users u
INNER JOIN ecs_mod_users.user_tenant ut ON u.id = ut.user_id
WHERE
  u.jsonb->>'type' = 'shadow'
  AND (
    u.jsonb->'customFields' IS NULL
    OR NOT u.jsonb->'customFields' ? 'originaltenantid'
    OR u.jsonb->'customFields'->>'originaltenantid' IS NULL
    OR u.jsonb->'customFields'->>'originaltenantid' = ''
  );

How to Run

Prerequisites

Execute this script on the central tenant database
Ensure you have appropriate database backup of table mod-users.users before running

Solution

This migration script identifies shadow users missing the originaltenantid custom field and restores it by:

Finding all shadow users where customFields.originaltenantid is missing
Joining with the user_tenant table to retrieve the corresponding tenant_id
Updating the user's customFields to include the originaltenantid

SQL Migration Script

-- Migration script to restore originaltenantid custom field for shadow users
-- This script identifies shadow users that are missing the originaltenantid custom field
-- and restores it by looking up the tenant_id from the user_tenant table

DO $$
DECLARE
  updated_count INTEGER := 0;
BEGIN
  -- Update shadow users that don't have originaltenantid in customFields
  -- by joining with user_tenant table to get the tenant_id
  WITH shadow_users_to_fix AS (
    SELECT
      u.id,
      ut.tenant_id
    FROM
      ecs_mod_users.users u
      INNER JOIN ecs_mod_users.user_tenant ut ON u.id = ut.user_id
    WHERE
      -- User is a shadow user
      u.jsonb->>'type' = 'shadow'
      -- customFields exists but originaltenantid is missing or null
      AND (
        u.jsonb->'customFields' IS NULL
        OR NOT u.jsonb->'customFields' ? 'originaltenantid'
        OR u.jsonb->'customFields'->>'originaltenantid' IS NULL
        OR u.jsonb->'customFields'->>'originaltenantid' = ''
      )
  )
  UPDATE ecs_mod_users.users u
  SET jsonb = CASE
    -- If customFields doesn't exist, create it with originaltenantid
    WHEN u.jsonb->'customFields' IS NULL THEN
      jsonb_set(u.jsonb, '{customFields}', jsonb_build_object('originaltenantid', suf.tenant_id))
    -- If customFields exists, add/update originaltenantid within it
    ELSE
      jsonb_set(u.jsonb, '{customFields, originaltenantid}', to_jsonb(suf.tenant_id))
  END
  FROM shadow_users_to_fix suf
  WHERE u.id = suf.id;

  GET DIAGNOSTICS updated_count = ROW_COUNT;

  RAISE NOTICE 'Restored originaltenantid custom field for % shadow user(s)', updated_count;
END $$;