Class: AddAnalyticsIndices

Inherits:
Object
  • Object
show all
Defined in:
lib/generators/ruby_cms/templates/db/migrate/20251205120000_add_analytics_indices.rb

Instance Method Summary collapse

Instance Method Details

#change ⇒ Object 



4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
# File 'lib/generators/ruby_cms/templates/db/migrate/20251205120000_add_analytics_indices.rb', line 4

def change
  # Ahoy visits indices for analytics queries
  add_index :ahoy_visits, :started_at, if_not_exists: true
  add_index :ahoy_visits, :ip, if_not_exists: true
  add_index :ahoy_visits, :referrer, if_not_exists: true

  # Ahoy events indices
  add_index :ahoy_events, :time, if_not_exists: true

  # Convert properties column from text to jsonb for better PostgreSQL performance
  # This enables GIN indexing and native JSON queries
  # Modern Ahoy (5.x) already creates `properties` as jsonb; only convert when an
  # older schema left it as text, otherwise the `= ''` comparison fails on jsonb.
  reversible do |dir|
    dir.up do
      properties_col = columns(:ahoy_events).find { |c| c.name == "properties" }
      if properties_col && properties_col.sql_type == "text"
        execute <<-SQL
          ALTER TABLE ahoy_events
          ALTER COLUMN properties TYPE jsonb
          USING CASE
            WHEN properties IS NULL THEN NULL
            WHEN properties = '' THEN '{}'::jsonb
            ELSE properties::jsonb
          END
        SQL
      end
    end

    dir.down do
      properties_col = columns(:ahoy_events).find { |c| c.name == "properties" }
      if properties_col && properties_col.sql_type == "jsonb"
        execute <<-SQL
          ALTER TABLE ahoy_events
          ALTER COLUMN properties TYPE text
          USING properties::text
        SQL
      end
    end
  end

  # GIN index for JSONB properties (PostgreSQL specific)
  add_index :ahoy_events, :properties, using: :gin, if_not_exists: true
end