000010_blocks_created_by.up.sql

TLDR

The file 000010_blocks_created_by.up.sql adds a new column called created_by to the blocks and blocks_history tables in the SQL database. It also updates the created_by column in the blocks table with data from the modified_by column in the blocks_history table.

{{- /* addColumnIfNeeded tableName columnName datatype constraint) */ -}}
{{ addColumnIfNeeded "blocks" "created_by" "varchar(36)" ""}}
{{ addColumnIfNeeded "blocks_history" "created_by" "varchar(36)" ""}}

UPDATE {{.prefix}}blocks SET created_by = 
    COALESCE(NULLIF((select modified_by from {{.prefix}}blocks_history where {{.prefix}}blocks_history.id = {{.prefix}}blocks.id ORDER BY {{.prefix}}blocks_history.insert_at ASC limit 1), ''), 'system') 
WHERE created_by IS NULL;