Fields migrations

[ijreilly]

Fields migrations 

Context

1. Objects and fields

• A standard object is an object created for every workspace at their creation. Currently we have three: Person, Company, Opportunity. Users are able to create objects on their own: custom objects (ex: “Listing”).

• Each object has their own fields to contain data (ex: age, name, address..).
  Each standard object has their own different variety of standard fields described in their .workspace-entity file (ex: company.workspace-entity.ts): company has address, number of employees, website domain name.., while person has email, phone, job title…
  All custom objects share a set of standard fields agnostic to the object: name, attachments, created at…
  It is then possible to add custom fields for both standard and custom objects: a field “country” for Company for instance. 

• Each field has a field type (FieldMetadataType): Number, Text, Boolean, Json… Some types can be “composite” like Links or Address, and represent a set of subfields (ex street, zipcode, …): while they will be represented by a single field in the product and in the field metadata description, the data will be split among various columns on the object’s table (ex addressStreet, addressZipcode, … on the table Person).
  Each field type has its own behavior in the product, notably for display and input (ex fields of type date will have a display transforming data based on the timezone, while the input will show a datepicker).

• The object and field definitions (types, names, …) represent the “metadata” and are stored in the shared tables metadata.objectMetadata and metadata.fieldMetadata.

2. Metadata synchronization

• When a workspace is created, it reads into the various standard objects files declarations (ex: company.workspace-entity.ts) to instantiate the standard object and their standard fields. Decorators (@WorkspaceIsDeprecated(), @WorkspaceIsNullable(), …) represent additional information for the standard field construction.

• We often need to “synchronize the metadata” on the existing active workspaces, when a metadata change has been introduced for instance (ex: a new standard field birthdate is added on the standard object Person). The script reads into the metadata declarations and identifies any difference between the declaration and the actual state of the workspace to correct it (ex: this workspace does not have a standard field birthdate on their standard object Person). It is actually the same script that is used when a workspace is created.
  To synchronize the metadata we run the command yarn command:prod workspace:sync-metadata -f.

Migration cases

1. Migration of field type with migration of data

Example: replace a type Email (= text with constraints like contains @, .com…) with a type Emails (composite type: primaryEmail of type Email, secondaryEmails of type array of Email). We want all standard and custom fields of type Email to be replaced with fields of type emails, without losing the data. This change should be silent for users, except that the graphql api may be broken if we switch to or from a composite field, but at this stage we are fine with this. 

Some technical hints / suggested steps

• Introduce the new field type (see 3.) and prevent creation of more fields with the deprecated type (example PR)

• In a subsequent PR, 

  • update the standard fields to replace the deprecated type with the new type (example PR): all the workspaces created from now on will benefit from the new type, and all the existing workspaces will have the same setup thanks to the following command:

  • create a command  (see toolbox 2. - example migration script + full PR) to handle 

    • the migration of the field type 

    • the migration of the data 

    • the mirroring of the behavior on the views (the new field must appear in the same views with the same position as the deprecated one)

  • (potentially) bring changes to the messaging workstream (check Notions 2.)

  • It’s important that after this PR is merged, the command is run on the workspaces before running sync-metadata on them, otherwise it would detect a difference on the field type and fail trying to offset it

• In an ultimate PR belonging to the next version of twenty, remove all code regarding the deprecated field

2. Migration of standard field type without migration of data

Example: replace a type Address (= text but with a different display) used in standard object Company with a new Address type (composite type: street, city, country, zipcode…).
We don’t want (because we can’t or don’t have time to do it cleanly) to migrate the existing data on behalf of the users (in our Address example, it would be too costly to set a good algorithm to migrate the address data from a plain text to data split between street, city, country, zipcode etc.). 

For the new users, we will have them benefit from the new field with the type, while for the existing users we will introduce and spotlight the new field with the new type without deleting the existing field with the deprecated type, as it contains all the data; it will be switched to a custom field though. Here we let the users take care of the data migration themselves.

Technical hints / suggested steps

• If needed, introduce the new field type (see 3.). If the “outgoing” type is being deprecated, prevent creation of more fields with it (example PR)

• Update the standard fields:

  • update the field with a -“Old” name suffix and “ (Deprecated)” label suffix, and add a decorator @WorkspaceIsDeprecated. This will rename the field to make room for the new one that needs to bear the same name, and set the deprecated field as a custom one. (ex: address → addressOld - example PR: check company.workspace-entity.ts)

  • add a new field with the right name and type (same example PR)

  • once this is merged we should synchronize the metadata on the workspaces

• Create a command (see Toolbox 2.) to add the new field in the views, next to the deprecated field (example PR). This should be run after the sync-metadata command was run (see Context 2.)

3. Introduction of new field type

Example: introduce a new field type “Array”

Technical hints / suggested steps

• Create the new FieldMetadataType

• Handle the display and input for the field in the table view 

• Handle the create, update and delete of records for this type of field 

4. Introduction of new standard field 

Example: add a new field “Birthdate” on standard object Person

In some cases we will want these fields to be deactivated by default not to pollute the workspaces too much with potentially unused fields. These fields will be easy for the users to fall upon when they enter the flow to create a custom field. 

Technical hints / suggested steps

• Update workspace-entity with the new field for the object (example PR: check company.workspace-entity.ts’ address field).
  If the new field needs to be deactivated by default, add @IsInactive decorator.

• Run sync-metadata command on the workspaces (see Context 2.)

5. Deprecation of a standard field 

Example: deprecate standard field “annualRecurringRevenue” on Company

We want to stop creating this field for the new users, but we don’t want to delete the field altogether for the existing users as it may contain data; instead it should be converted into a custom field for the existing users.

Technical hints / suggested steps

• Update object’s workspace-entity file to add @WorkspaceIsDeprecated decorator (example PR: check company.workspace-entity.ts’ addressOld field).

• Run sync-metadata command on the workspaces (see Context 2.)

Toolbox

1. QA

Features to check

• Sign in (to a pre-existing workspace - reset your db from main if needed)

• Run migration command on pre-existing workspace multiple times (should never break)

• Check fieldMetadata and object’s table both have the expected data and columns

• Sign up (create a new workspace)

• Run migration command on new workspace (should not do anything)

• Reset the database (yarn nx database:reset twenty-server then connect to the workspace)

• Demo seeds - we seed demo environment with data everyday so we should check this still works (npx nx workspace:seed:demo then connect to the workspace)

• On the object table view, add a filter based on the new field

• Import data aiming at backfilling the new field (from table view, “Options” > “Import”)

• Export data for an object containing the new field (from table view, “Options” > “Export”)

• Messaging and calendar sync features (see Notions 2.)

2. Running commands on workspaces

Migration scripts can be written in .command.ts files (example PR).

This type of command is usually added to a group of commands that need to be run to allow for the upgrade to a new version of twenty, ex: upgrade:0-23. 
Run yarn command:prod upgrade-0.23.

Notions

1. Views

Each object has one or more “views” that can be configured by the user and that feature a set of fields in chosen positions (horizontally among the list of fields) and width. Those views are defined in the workspace_$schema.views and workspace_$schema.viewField tables.

2. Messaging and calendar sync 

Users can synchronize data from their gmail account (“Settings” > “Account” > “Emails”), which will trigger the creation of People and Company records based on the emails and events. If changes are brought to some fields’ column names of fields used in the gmail synchronization operation (ex: Company’s domainName field), a change needs to be made not to break the synchronization process by allowing the fill of the column for both their deprecated and new names. (example PR: check create-company-and-contact.service.ts: if the domainName field is still of type “Text”, fill the “domainName” column; if it is of type “Links”, it means the migration has been performed already, and we should now fill “domainNamePrimaryLinkUrl” column).
To test this feature locally, in addition to running your server, you need to 

• Run a worker: npx nx run twenty-server:worker

• Launch the crons (only once):

  • npx nx run twenty-server:command cron:messaging:messages-import

  • npx nx run twenty-server:command cron:messaging:message-list-fetch 

  • npx nx run twenty-server:command cron:calendar:calendar-event-list-fetch

3. Background mocks and data prefill

To help users project themselves and understand the product, we instantiated some background mocks to show during sign-in flow and some “fake” data to prefill the new workspaces. Those mocks and data may need adaptation due to field migration (example PR: check sign-in-background-mock and standard-objects-prefill-data folders).

[lucasbordeau]

@ijreilly Didn't know about this documentation, I created this related issue #6508

[ijreilly]

I slightly updated your issue to mention this doc ! @lucasbordeau