From f0f4d32e1f531701cb0dd0450904a262dbe34f62 Mon Sep 17 00:00:00 2001
From: Felipe Artur <felipefac@gmail.com>
Date: Wed, 22 Oct 2025 12:30:45 -0300
Subject: [PATCH] Create ClickHouse ai_usage_events_daily table

---
 ...1022151631_create_ai_usage_events_daily.rb | 25 +++++++++++++++++++
 ...2151639_create_ai_usage_events_daily_mv.rb | 22 ++++++++++++++++
 ...22151702_backfill_ai_usage_events_daily.rb | 21 ++++++++++++++++
 3 files changed, 68 insertions(+)
 create mode 100644 db/click_house/migrate/main/20251022151631_create_ai_usage_events_daily.rb
 create mode 100644 db/click_house/migrate/main/20251022151639_create_ai_usage_events_daily_mv.rb
 create mode 100644 db/click_house/migrate/main/20251022151702_backfill_ai_usage_events_daily.rb

diff --git a/db/click_house/migrate/main/20251022151631_create_ai_usage_events_daily.rb b/db/click_house/migrate/main/20251022151631_create_ai_usage_events_daily.rb
new file mode 100644
index 00000000000000..bbbb56335d18c1
--- /dev/null
+++ b/db/click_house/migrate/main/20251022151631_create_ai_usage_events_daily.rb
@@ -0,0 +1,25 @@
+# frozen_string_literal: true
+
+class CreateAiUsageEventsDaily < ClickHouse::Migration
+  def up
+    execute <<-SQL
+      CREATE TABLE IF NOT EXISTS ai_usage_events_daily
+      (
+          `namespace_path` String DEFAULT '0/',
+          `date` Date32 DEFAULT toDate(now64()),
+          `event` UInt8 DEFAULT 0,
+          `user_id` UInt64 DEFAULT 0,
+          `occurrences` UInt64 DEFAULT 0
+      )
+      ENGINE = SummingMergeTree
+      PARTITION BY toYear(date)
+      ORDER BY (namespace_path, date, event, user_id)
+    SQL
+  end
+
+  def down
+    execute <<-SQL
+      DROP TABLE IF EXISTS ai_usage_events_daily
+    SQL
+  end
+end
diff --git a/db/click_house/migrate/main/20251022151639_create_ai_usage_events_daily_mv.rb b/db/click_house/migrate/main/20251022151639_create_ai_usage_events_daily_mv.rb
new file mode 100644
index 00000000000000..a6fd1ccf1887b6
--- /dev/null
+++ b/db/click_house/migrate/main/20251022151639_create_ai_usage_events_daily_mv.rb
@@ -0,0 +1,22 @@
+# frozen_string_literal: true
+
+class CreateAiUsageEventsDailyMv < ClickHouse::Migration
+  def up
+    execute <<-SQL
+      CREATE MATERIALIZED VIEW IF NOT EXISTS ai_usage_events_daily_mv TO ai_usage_events_daily
+      AS SELECT
+          namespace_path,
+          toDate(timestamp) AS date,
+          event,
+          user_id,
+          1 AS occurrences
+      FROM ai_usage_events
+    SQL
+  end
+
+  def down
+    execute <<-SQL
+      DROP VIEW IF EXISTS ai_usage_events_daily_mv
+    SQL
+  end
+end
diff --git a/db/click_house/migrate/main/20251022151702_backfill_ai_usage_events_daily.rb b/db/click_house/migrate/main/20251022151702_backfill_ai_usage_events_daily.rb
new file mode 100644
index 00000000000000..605f1e6e66a815
--- /dev/null
+++ b/db/click_house/migrate/main/20251022151702_backfill_ai_usage_events_daily.rb
@@ -0,0 +1,21 @@
+# frozen_string_literal: true
+
+class BackfillAiUsageEventsDaily < ClickHouse::Migration
+  def up
+    execute <<-SQL
+      INSERT INTO ai_usage_events_daily (namespace_path, date, event, user_id, occurrences)
+      SELECT
+          namespace_path,
+          toDate(timestamp) AS date,
+          event,
+          user_id,
+          count() AS occurrences
+      FROM ai_usage_events
+      GROUP BY namespace_path, date, event, user_id
+    SQL
+  end
+
+  def down
+    # NO-OP
+  end
+end
-- 
GitLab