The migration that turned the bilingual columns into JSONB

Every bilingual title, description and hint the earlier courses quoted

Catalog::Course#title and #description, Catalog::Lesson#title and #content_markdown, Catalog::Challenge#instruction, #solution and #hints: every one of these columns is jsonb. Most of them didn't start that way. courses.title, lessons.title, lessons.content_markdown and challenges.instruction began as plain string and text columns and were converted a day later; hints and solution came later still and were added as jsonb from the start. db/migrate/20260227224007_convert_course_fields_to_jsonb.rb is the migration where courses.title stopped being a plain string:

# db/migrate/20260227224007_convert_course_fields_to_jsonb.rb
def up
  add_column :courses, :title_jsonb, :jsonb, default: {}
  add_column :courses, :description_jsonb, :jsonb, default: {}

  execute <<-SQL
    UPDATE courses
    SET title_jsonb = jsonb_build_object('en', title, 'pt-BR', title),
        description_jsonb = jsonb_build_object('en', description, 'pt-BR', description)
  SQL

  change_column_null :courses, :title_jsonb, false

  remove_column :courses, :title
  remove_column :courses, :description

  rename_column :courses, :title_jsonb, :title
  rename_column :courses, :description_jsonb, :description
end

Reading the migration

It makes five moves, in order. First it adds a temporary jsonb column next to each old one. Then it backfills every row with jsonb_build_object, a Postgres function that builds a JSON object in SQL. It wraps the one existing string under two keys, 'en' and 'pt-BR', both identical at first because there was no translation yet. Third, it makes the new title column NOT NULL, now that every row has a value. Fourth, it drops the old string columns, and last, it renames the new columns to the old names.

The table ends up with the same column names, title and description, holding JSON instead of plain text.

jsonb_build_object, and why the migration doesn't just cast

title::jsonb on its own would fail, because plain text like Some Title isn't valid JSON. Even a properly quoted string would cast to a JSON string, not the {"en": ..., "pt-BR": ...} object the app's code expects. jsonb_build_object('en', title, 'pt-BR', title) builds the object key by key inside Postgres, in one UPDATE over every row, with no Ruby loop over the courses.

The migration knows how to undo itself

# same file
def down
  add_column :courses, :title_string, :string
  add_column :courses, :description_text, :text

  execute <<-SQL
    UPDATE courses
    SET title_string = title->>'en',
        description_text = description->>'en'
  SQL

  change_column_null :courses, :title_string, false
  remove_column :courses, :title
  remove_column :courses, :description
  rename_column :courses, :title_string, :title
  rename_column :courses, :description_text, :description
end

The same five moves run in reverse, with one new piece: title->>'en', Postgres's arrow operator, which pulls the 'en' value back out of the JSON object as plain text. Lesson 4 covers that operator. For now, notice it mirrors jsonb_build_object: one builds a JSON object from plain values, the other extracts a plain value from a JSON object.

What this course covers

Seven lessons, all about code in this app. Lesson 2 covers ActiveRecord::Store, Rails' built-in tool for this kind of column, on a disposable table, since this app doesn't use it. Lesson 3 shows why the app wrote its own Translatable concern instead. Lesson 4 queries JSONB directly in SQL with the same ->> operator, in production queries. Lesson 5 covers GIN indexes: what the app's indexes speed up, and what they don't. Lesson 6 covers JSONB's main reason to exist, data whose shape isn't fixed, in a part of this app that's running now.