[Machine Translation] Create project_id column in Production Postgres tables

[rudouglas]

Background

Follow the instructions in the Readme to set up your environment. This sets up a local docker container for a PGAdmin instance to manipulate the DB.

<!> Please use the https://github.com/newrelic/docs-website/tree/feature/machine-translation feature branch

In order to reduce code duplication and effort, we will be adding a new column called project_id to a couple of existing tables. This is so we don't need separate tables for each type of translation, and can re-use the same scripts with a little tweaking.

Edit Tables

Adding project_id to the following tables is necessary for us to know whether a page needs to be sent to Machine or Human translation project in Smartling, and also identify how it's been translated when we download it.

• translations
• jobs

We can do that in the PGAdmin console via the Query Tool:

⚠️ SPOILER - Click to see image!

⚠️ SPOILER - Click to see SQL Code!

```sql ALTER TABLE IF EXISTS public.jobs ADD COLUMN project_id TEXT NOT NULL; ALTER TABLE IF EXISTS public.translations ADD COLUMN project_id TEXT NOT NULL; ```

Edit Resource Creation Code

We will also want to alter the SQL code for the Terraform resource creation in creation_and_cleanup.sql, adding the following to the CREATE TABLE translations() and CREATE TABLE jobs() functions:

⚠️ SPOILER - Click to see Code!

- `project_id TEXT NOT NULL`

Testing Scripts

🛑 You can use the MT Project ID for this but only test with a 1 word change, see here

The reason for this is we have a 2 million word limit per year specifically for Machine Translation

Average is about 850 words per document. total is about 1.6 million. For MT it’s a total of 2 million words can be translated over a year. we have approximately 1800 pages x 850 (avg word count per page) = 1.6 million

Acceptance Criteria

note: All Deven team members should be given access to AWS. - @zstix

• [x] Created new columns in the tables of the Production environment
  • will also need to update values for the column. create the new project_id column, and add human translation project id as the value for every existing record.
• [x] Edited the Resource Creation Code
• [x] Test the scripts via the [testing containers]()

Useful Links

• Setup Testing Environment
• Current database schema
• AWS RDS Database
• Database Repo - the physical resources in AWS are terraformed, the database schema is manumatic. so if we a new table, or have to add a new field, need to expand the current script for that.

[github-actions[bot]]

Hi @rudouglas 👋 Thank you for filing an issue! We'll triage your issue and let you know if we have questions, and then route it to the appropriate team so we can get it solved.

[moonlight-komorebi]

tables updated. im going to update documentation as well as part of this ticket (since its not anywhere else 🙈 ).

[moonlight-komorebi]

gonna call this done since the criteria are met & sprint end. documentation is almost ready.