7 files changed, 98 insertions, 1 deletions
diff --git a/db/click_house/migrate/main/20230705124511_create_events.rb b/db/click_house/migrate/main/20230705124511_create_events.rb
index cd60ade5d4d..66df09bc97f 100644
--- a/db/click_house/migrate/main/20230705124511_create_events.rb
+++ b/db/click_house/migrate/main/20230705124511_create_events.rb
@@ -6,7 +6,7 @@ class CreateEvents < ClickHouse::Migration
       CREATE TABLE IF NOT EXISTS events
       (
         id UInt64 DEFAULT 0,
-        path String DEFAULT '',
+        path String DEFAULT '0/', -- the event_namespace_paths MV depends on this format
         author_id UInt64 DEFAULT 0,
         target_id UInt64 DEFAULT 0,
         target_type LowCardinality(String) DEFAULT '',
diff --git a/db/click_house/migrate/20231211130023_drop_contribution_analytics_mv.rb b/db/click_house/migrate/main/20231211130023_drop_contribution_analytics_mv.rb
index b8b4f7fb5c0..b8b4f7fb5c0 100644
--- a/db/click_house/migrate/20231211130023_drop_contribution_analytics_mv.rb
+++ b/db/click_house/migrate/main/20231211130023_drop_contribution_analytics_mv.rb
diff --git a/db/click_house/migrate/20231211143660_drop_contribution_analytics_table.rb b/db/click_house/migrate/main/20231211143660_drop_contribution_analytics_table.rb
index 45d16ac50fe..45d16ac50fe 100644
--- a/db/click_house/migrate/20231211143660_drop_contribution_analytics_table.rb
+++ b/db/click_house/migrate/main/20231211143660_drop_contribution_analytics_table.rb
diff --git a/db/click_house/migrate/main/20231212085143_create_event_authors_table.rb b/db/click_house/migrate/main/20231212085143_create_event_authors_table.rb
new file mode 100644
index 00000000000..438539bc118
--- /dev/null
+++ b/db/click_house/migrate/main/20231212085143_create_event_authors_table.rb
@@ -0,0 +1,23 @@
+# frozen_string_literal: true
+
+class CreateEventAuthorsTable < ClickHouse::Migration
+  def up
+    execute <<~SQL
+      CREATE TABLE IF NOT EXISTS event_authors
+      (
+        author_id UInt64 DEFAULT 0,
+        deleted UInt8 DEFAULT 0,
+        last_event_at DateTime64(6, 'UTC') DEFAULT now64()
+      )
+      ENGINE = ReplacingMergeTree(last_event_at, deleted)
+      PRIMARY KEY (author_id)
+      ORDER BY (author_id)
+    SQL
+  end
+
+  def down
+    execute <<~SQL
+      DROP TABLE IF EXISTS event_authors
+    SQL
+  end
+end
diff --git a/db/click_house/migrate/main/20231212090756_create_event_namespace_paths_table.rb b/db/click_house/migrate/main/20231212090756_create_event_namespace_paths_table.rb
new file mode 100644
index 00000000000..857ec41021b
--- /dev/null
+++ b/db/click_house/migrate/main/20231212090756_create_event_namespace_paths_table.rb
@@ -0,0 +1,24 @@
+# frozen_string_literal: true
+
+class CreateEventNamespacePathsTable < ClickHouse::Migration
+  def up
+    execute <<~SQL
+      CREATE TABLE IF NOT EXISTS event_namespace_paths
+      (
+        namespace_id UInt64 DEFAULT 0,
+        path String DEFAULT '',
+        deleted UInt8 DEFAULT 0,
+        last_event_at DateTime64(6, 'UTC') DEFAULT now64()
+      )
+      ENGINE = ReplacingMergeTree(last_event_at, deleted)
+      PRIMARY KEY (namespace_id)
+      ORDER BY (namespace_id)
+    SQL
+  end
+
+  def down
+    execute <<~SQL
+      DROP TABLE IF EXISTS event_namespace_paths
+    SQL
+  end
+end
diff --git a/db/click_house/migrate/main/20231212090973_create_event_authors_materialized_view.rb b/db/click_house/migrate/main/20231212090973_create_event_authors_materialized_view.rb
new file mode 100644
index 00000000000..d7ece25c2b1
--- /dev/null
+++ b/db/click_house/migrate/main/20231212090973_create_event_authors_materialized_view.rb
@@ -0,0 +1,23 @@
+# frozen_string_literal: true
+
+class CreateEventAuthorsMaterializedView < ClickHouse::Migration
+  def up
+    execute <<~SQL
+      CREATE MATERIALIZED VIEW IF NOT EXISTS event_authors_mv
+      TO event_authors
+      AS
+      SELECT
+        author_id,
+        argMax(deleted, events.updated_at) as deleted,
+        max(events.updated_at) as last_event_at
+      FROM events
+      GROUP BY author_id
+    SQL
+  end
+
+  def down
+    execute <<~SQL
+      DROP VIEW IF EXISTS event_authors_mv
+    SQL
+  end
+end
diff --git a/db/click_house/migrate/main/20231212092970_create_event_namespace_paths_materialized_view.rb b/db/click_house/migrate/main/20231212092970_create_event_namespace_paths_materialized_view.rb
new file mode 100644
index 00000000000..11cdb73ed8a
--- /dev/null
+++ b/db/click_house/migrate/main/20231212092970_create_event_namespace_paths_materialized_view.rb
@@ -0,0 +1,27 @@
+# frozen_string_literal: true
+
+class CreateEventNamespacePathsMaterializedView < ClickHouse::Migration
+  def up
+    # The path contains the same data as traversal_ids, ancestor namespace ids separated by
+    # the / character. Here we extract the last id value from the path string and store it
+    # as namespace id. Reasoning: batching over the table requires an integer column.
+    execute <<~SQL
+      CREATE MATERIALIZED VIEW IF NOT EXISTS event_namespace_paths_mv
+      TO event_namespace_paths
+      AS
+      SELECT
+        splitByChar('/', path)[length(splitByChar('/', path)) - 1] AS namespace_id,
+        path,
+        argMax(deleted, events.updated_at) as deleted,
+        max(events.updated_at) as last_event_at
+      FROM events
+      GROUP BY namespace_id, path
+    SQL
+  end
+
+  def down
+    execute <<~SQL
+      DROP VIEW IF EXISTS event_namespace_paths_mv
+    SQL
+  end
+end