Convert JSON fields from text to jsonb

[bchrobot]

Spoke has a number of text-type Postgres columns that store JSON values. Rather than sprinkling try { JSON.parse(json_field_name) } catch {} throughout the codebase, we should just formalize in the schema that these are JSON fields.

We should use jsonb rather than json for performance reasons. We do not care about the exact format or ordering of the input JSON text so there is no reason to use json over jsonb.

Fields:

• campaign_contact.custom_fields
• all_tag.confirmation_steps (may be better to migrate to its own 1:many table)
• filtered_contact.custom_fields
• invite.payload (already json but needs to be standardized to jsonb)
• job_request.payload (needs confirmation that we're only using this for JSON content)
• organization.features

[lediur]

Could you provide a list of columns we should change, or some set of heuristics to determine whether a column needs changing? I think even a short list of examples would be a good starting place.

[bchrobot]

Could you provide a list of columns we should change, or some set of heuristics to determine whether a column needs changing? I think even a short list of examples would be a good starting place.

I went through the schema and added a list of fields to the description. Generally it's any text column with a default of '{}'::text.