Convert array-type columns to dedicated tables

[bchrobot]

Is your feature request related to a problem? Please describe.

A/B testing -- it is difficult to guarantee correct ordering of array elements when reading and writing. The campaign editor does not return the script options in a deterministic order, making it labor intensive to edit or delete a specific script version; you must compare the text of the script option, not just its order (e.g. v1, v2, v3). Making edits to script versions and writing them back to the database is also prone to mistakes. unnest() must be used with with ordinality and array_agg() must be used with an order by. A dedicated table with stored values that can be used for ordering (e.g. id) and targeted editing avoids these issues.

Data warehousing -- array-type columns have limited support in OLAP database systems and pipeline tools like Stitch or Google Datastream. Stitch handles arrays by turning them into their own table automatically. Datastream does not support them at all, nor does it support syncing generated columns or views, the two options for working around array-type columns.

Describe the solution you'd like

Interaction step script options should have their own table.

create table script_option (
  id serial primary key,
  interaction_step_id integer not null references interaction_step (id),
  script text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz -- optional
);

-- preserve ordering on insert using unnest() with ordinality
insert into script_option (interaction_step_id, script)
select
  interaction_step.id as interaction_step_id,
  so.script_option as script,
  interaction_step.created_at,
  interaction_step.updated_at
from interaction_step, unnest(interaction_step.script_options, created_at, updated_at) with ordinality so(script_option, nr)
order by
  interaction_step.id asc,
  so.nr asc;

-- TODO: install updated_at trigger on table

Message error codes could either be just a text column -- I don't think we have ever seen a case where a message had more than one error code -- rather than create a new table for it. This could also be deferred until #1544.

Describe alternatives you've considered

Defer working on interaction_step.script_options until a larger discussion happens about scripts, A/B testing, etc.

Additional context

At least one customer is impacted by the data warehousing piece of this.