create a migration to convert all existing custom connector definitions in oss deployments to be custom definitions

[git-phu]

relates to #9652

create a migration to convert all existing custom connector definitions in oss deployments to be correctly configured as custom definitions based on the new schema changes

should be released together with the ui changes to use scoped connector definition endpoints

[xiaohansong]

Sounds like this needs to be prioritized to unblock custom connectors. Moving this to needs refining section so we can discuss current state and plan of execution.

[xiaohansong]

@git-phu and I went through code and discussed about the plan in a call. Current state is:

1. We have established a new table actor_definition_workspace_grant which stores 2 columns: actor_definition_id and workspace_id. Each record indicating one workspace have permission to access an actor definition.
2. We have created new APIs such as /v1/source_definitions/list_for_workspace, which intends to replace existing /v1/source_definitions/list. The implementation of list_for_workspace will query for all actor_definitions of which public column is marked true, and combined for those private ones only if there are corresponding records in actor_definition_workspace_grant. For comparison, the existing implementation we are using today for /v1/source_definitions/list will list everything under actor_definitions regardless of the value of public.
3. When creating a custom connector, we are currently using API /v1/source_definitions/create which does not create a record in actor_definition_workspace_grant table. As a result, if we are simply moving webapp to call new list api, the existing custom connectors will not show up because lack of permission records in actor_definition_workspace_grant. So, we also need to migrate webapp to call new API /v1/source_definitions/create_custom for custom connector creation, as this new API will take care of this missing step.

Because of such, this migration ticket is to populate actor_definition_workspace_grant table with following rules:

1. Find all custom actor definitions by select id from actor_definitions where public is false.
2. For each of existing workspace, we create a record in actor_definition_workspace_grant <workspace_id, actor_definition_id> so all existing workspace would have access to created custom source/destination definition. However, note going forward, the custom connector will only be accessible to the workspace where it is created.

For migration plan, this needs to be released along with the UI migration on all APIs (list_for_workspace on both source_definitions and destination_definitions, and create_custom instead of create on custom connectors). The change won't affect cloud because cloud does not have non-public actor definitions at this moment.

FYI @davinchia @josephkmh (assuming you will be working on the FE side #11606!)

[joeykmh]

Thanks @xiaohansong! I have updated the frontend ticket to include the specific APIs that need to be changed:

• /v1/source_definitions/list => /v1/source_definitions/list_for_workspace
• /v1/destination_definitions/list => /v1/destination_definitions/list_for_workspace
• /v1/source_definitions/create => /v1/source_definitions/create_custom
• /v1/destination_definitions/create => /v1/destination_definitions/create_custom

I found two other API endpoints that seem to be workspace scoped. I'm assuming we also need to migrate to using these - could you confirm?

• /v1/source_definitions/get_for_workspace (seems to replace /v1/source_definitions/get)
• /v1/destination_definitions/get_for_workspace (seems to replace /v1/destination_definitions/get)

[git-phu]

In the parent issue #9652 under the Describe the solution you’d like header, there is a New endpoints added section you can expand to see all the new APIs we should use (and also for each whether the route currently exists in cloud). But basically we need to update the frontend to use all of the listed new routes (there are no existing ui elements for the admin only routes so we can ignore those for this issue).

[git-phu]

@xiaohansong for the migration we also want to set custom = true for all the existing custom definitions we find

[joeykmh]

Thanks @git-phu! I missed that overview of new APIs in the Epic.

Just confirming: as far as I can tell we also have no UI for deleting custom connectors, so I'm also ignoring /v1/source_definitions/delete_custom and /v1/destination_definitions/delete_custom.

@xiaohansong the frontend changes are ready to go in https://github.com/airbytehq/airbyte/pull/19221/files, so you can use that branch and test locally to make sure the migration works 👍

[git-phu]

ah ok sounds good! I didn't realize we have no UI for deleting custom connectors