Database level translations

[briri]

We will need to be able to store translations of certain data within the database and return the appropriate value in GraphQL responses.

We can take several different approaches for this based on the use case.

Use Case - infrequently changed text

In most situations, the text we want translated will be updated/added infrequently. For example question types, new tags for guidance, contributor roles, etc.

I think for these, we should wait to see what solution the UI #160 lands on. We can generate a script to retrieve these strings from the DB and write them to a static file that can then be synced up with whatever external system we decide to use to let users translate our UI text.

This script could be run as part of the build process and would not need the super admin to attempt to translate anything on the management screens within the UI.

Use Case - instant translation

In some situations like when adding a global notification about an outage, the admin creating the message will want that text translated immediately and available without the need to deploy.

To handle this, a new translation table could be created that has a complex foreign key to our other tables.

Note that the UI screen would need to allow the admin to add a separate entry for each language type that we support.

For example if we want to have different language translations for the questionTypes.description field and the UI requests the description of the TextArea question type for a user whose language/locale is Brazil, the backend could run a query like this:

SELECT `text` 
FROM `translations` 
WHERE `contextTable` = 'questionTypes' 
     AND `contextField` = 'description' 
     AND `contextId` = 123 
     AND `languageId` = 'pt-BR';

• create a translations table with the following fields (omitting the standard created, modified, etc. here):
  • contextTable - string - the name of the table (e.g. 'notifications', 'questionTypes', etc.)
  • contextField - string - the name of the field/property in the table (e.g. 'name', 'message', etc.)
  • contextId - int - the id of the record within the contextTable
  • languageId - string - the language code
  • text - text - the text value
• create a localeService that can be used to fetch translations for model properties that have translatable values.
• update the resolvers to fetch the locale info and pass it into the model
• update the models to check the locale and call the localeService to fetch translations if necessary