redux-scraper/db/migrate/20250203235035_create_unified_domain_tables.rb

# typed: strict
class CreateUnifiedDomainTables < ActiveRecord::Migration[7.2]
  extend T::Sig

  POST_TYPES = %w[
    Domain::Post::FaPost
    Domain::Post::E621Post
    Domain::Post::InkbunnyPost
    Domain::Post::SofurryPost
    Domain::Post::WeasylPost
  ]

  USER_TYPES = %w[
    Domain::User::FaUser
    Domain::User::E621User
    Domain::User::InkbunnyUser
    Domain::User::SofurryUser
    Domain::User::WeasylUser
  ]

  POST_FILE_TYPES = %w[Domain::PostFile Domain::PostFile::InkbunnyPostFile]

  GROUP_JOIN_TYPES = %w[
    Domain::PostGroupJoin::InkbunnyPoolJoin
    Domain::PostGroupJoin::E621PoolJoin
  ]

  GROUP_TYPES = %w[Domain::PostGroup::InkbunnyPool Domain::PostGroup::E621Pool]

  sig { void }
  def change
    up_only { execute "SET DEFAULT_TABLESPACE = mirai" }
    up_only { execute "CREATE EXTENSION IF NOT EXISTS fuzzystrmatch" }

    create_enum("domain_post_type", POST_TYPES)
    create_enum("domain_user_type", USER_TYPES)
    create_enum("domain_post_file_type", POST_FILE_TYPES)
    create_enum("domain_post_group_type", GROUP_TYPES)
    create_enum("domain_post_group_join_type", GROUP_JOIN_TYPES)

    create_table :domain_posts do |t|
      t.enum :type, null: false, enum_type: "domain_post_type"
      t.timestamp :posted_at
      t.jsonb :json_attributes, default: {}
      t.timestamps

      t.index :type
      t.index :posted_at
    end

    create_table :domain_users do |t|
      t.enum :type, null: false, enum_type: "domain_user_type"
      t.jsonb :json_attributes, default: {}
      t.timestamps

      t.index :type
    end

    create_table :domain_user_search_names do |t|
      t.references :user, null: false, foreign_key: { to_table: :domain_users }

      t.string :name, null: false
      t.virtual :dmetaphone_primary,
                type: :string,
                as: "dmetaphone(name)",
                stored: true
      t.virtual :dmetaphone_alt,
                type: :string,
                as: "dmetaphone_alt(name)",
                stored: true

      t.index %i[user_id name], unique: true

      t.index :name, using: :gin, opclass: :gin_trgm_ops
      t.index :dmetaphone_primary, using: :gin, opclass: :gin_trgm_ops
      t.index :dmetaphone_alt, using: :gin, opclass: :gin_trgm_ops
    end

    create_table :domain_user_avatars do |t|
      t.references :user, null: false, foreign_key: { to_table: :domain_users }
      t.jsonb :json_attributes, default: {}
      t.timestamps
    end

    create_table :domain_post_files do |t|
      t.enum :type, null: false, enum_type: "domain_post_file_type"
      t.references :post, null: false, foreign_key: { to_table: :domain_posts }
      t.references :log_entry,
                   null: true,
                   index: {
                     unique: true,
                   },
                   foreign_key: {
                     to_table: :http_log_entries,
                   }
      t.binary :blob_sha256, null: true
      t.jsonb :json_attributes, default: {}
      t.timestamps

      t.index :type
    end

    create_table :domain_user_post_creations, id: false do |t|
      t.references :user,
                   null: false,
                   foreign_key: {
                     to_table: :domain_users,
                   },
                   index: false

      t.references :post,
                   null: false,
                   foreign_key: {
                     to_table: :domain_posts,
                   },
                   index: false

      t.index %i[user_id post_id], unique: true
      t.index %i[post_id user_id]
    end

    create_table :domain_user_post_favs, id: false do |t|
      t.references :user,
                   null: false,
                   foreign_key: {
                     to_table: :domain_users,
                   },
                   index: false
      t.references :post,
                   null: false,
                   foreign_key: {
                     to_table: :domain_posts,
                   },
                   index: false
      t.boolean :removed, null: false, default: false
      t.index %i[user_id post_id], unique: true
      t.index %i[post_id user_id]
    end

    create_table :domain_user_user_follows, id: false do |t|
      t.references :from,
                   null: false,
                   foreign_key: {
                     to_table: :domain_users,
                   },
                   index: false
      t.references :to,
                   null: false,
                   foreign_key: {
                     to_table: :domain_users,
                   },
                   index: false
      t.index %i[from_id to_id], unique: true
      t.index %i[to_id from_id]
    end

    reversible do |dir|
      dir.up {}

      dir.down {}
    end

    create_table :domain_post_groups do |t|
      t.enum :type, null: false, enum_type: "domain_post_group_type"
      t.jsonb :json_attributes, default: {}
      t.timestamps

      t.index :type
    end

    create_table :domain_post_group_joins, id: false do |t|
      t.enum :type, null: false, enum_type: "domain_post_group_join_type"
      t.references :post,
                   index: false,
                   null: false,
                   foreign_key: {
                     to_table: :domain_posts,
                   }
      t.references :group,
                   index: false,
                   null: false,
                   foreign_key: {
                     to_table: :domain_post_groups,
                   }
      t.jsonb :json_attributes, default: {}
      t.timestamps

      t.index %i[post_id group_id], unique: true
      t.index %i[group_id post_id]
      t.index :type
    end
  end
end