-- System is the one root identity. Tasks that stored '' for the root, the -- identity-grants "root" key and the connection permits of the identity the -- config's bot_user used to seed all move to System, so the poller's new -- identity matches the stored rows and a re-poll dedups. -- The old root is the first identity imported from the config. -- +goose Up UPDATE tasks SET identity = '00000000-0000-5000-8000-000000000001' WHERE identity = ''; ALTER TABLE tasks ADD CONSTRAINT tasks_identity_set CHECK (identity <> ''); UPDATE resources r SET value = convert_to(m.doc::text, 'UTF8') FROM ( SELECT g.org_id, g.kind, (g.v - 'root') || jsonb_build_object('identities', CASE WHEN jsonb_typeof(g.v -> 'identities') = 'object' THEN g.v -> 'identities' ELSE '{}'::jsonb END || jsonb_build_object('00000000-0000-5000-8000-000000000001', CASE WHEN jsonb_typeof(g.v -> 'root') = 'array' THEN g.v -> 'root' ELSE '[]'::jsonb END)) AS doc FROM ( SELECT org_id, kind, convert_from(value, 'UTF8')::jsonb AS v FROM resources WHERE kind = 'identity-grants' ) g WHERE g.v ? 'root' ) m WHERE r.org_id = m.org_id AND r.kind = m.kind; UPDATE resources r SET value = convert_to(m.doc::text, 'UTF8') FROM ( SELECT f.org_id, f.kind, ( SELECT COALESCE(jsonb_agg( CASE WHEN c -> 'permits' ? old.id THEN jsonb_set(c, '{permits}', ((c -> 'permits') - old.id) || jsonb_build_object('00000000-0000-5000-8000-000000000001', (SELECT jsonb_agg(DISTINCT op) FROM jsonb_array_elements_text( COALESCE(c -> 'permits' -> '00000000-0000-5000-8000-000000000001', '[]'::jsonb) || (c -> 'permits' -> old.id)) op))) ELSE c END), '[]'::jsonb) FROM jsonb_array_elements(f.v) c ) AS doc FROM ( SELECT org_id, kind, convert_from(value, 'UTF8')::jsonb AS v FROM resources WHERE kind = 'forge-connections' ) f, ( SELECT identity_id AS id FROM identity_events WHERE request_id LIKE 'identity-import:%' AND identity_id <> '00000000-0000-5000-8000-000000000001' ORDER BY id LIMIT 1 ) old WHERE jsonb_typeof(f.v) = 'array' ) m WHERE r.org_id = m.org_id AND r.kind = m.kind; -- +goose Down ALTER TABLE tasks DROP CONSTRAINT tasks_identity_set;