fix: normalize captain usage bucket timestamps to whole seconds

db/migrate/20260630102817_create_captain_usages.rb

@@ -0,0 +1,20 @@
+class CreateCaptainUsages < ActiveRecord::Migration[7.1]
+  def change
+    create_table :captain_usages do |t|
+      t.references :account, null: false
+      t.references :assistant, null: false
+      t.integer :usage_type, null: false
+      # Usage is binned into 15-minute UTC buckets so it can be re-sliced into
+      # any caller timezone (including :45 offset zones). Do not widen to hourly.
+      t.datetime :bucket_started_at, null: false
+      t.integer :credits_used, null: false, default: 0
+
+      t.timestamps
+    end
+
+    add_index :captain_usages,
+              [:account_id, :assistant_id, :usage_type, :bucket_started_at],
+              unique: true,
+              name: 'index_captain_usages_unique_bucket'
+  end
+end

db/schema.rb

@@ -10,7 +10,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema[7.1].define(version: 2026_06_20_000000) do
+ActiveRecord::Schema[7.1].define(version: 2026_06_30_102817) do
   # These extensions should be enabled to support this database
   enable_extension "pg_stat_statements"
   enable_extension "pg_trgm"
@@ -431,6 +431,19 @@ ActiveRecord::Schema[7.1].define(version: 2026_06_20_000000) do
     t.index ["enabled"], name: "index_captain_scenarios_on_enabled"
   end
 
+  create_table "captain_usages", force: :cascade do |t|
+    t.bigint "account_id", null: false
+    t.bigint "assistant_id", null: false
+    t.integer "usage_type", null: false
+    t.datetime "bucket_started_at", null: false
+    t.integer "credits_used", default: 0, null: false
+    t.datetime "created_at", null: false
+    t.datetime "updated_at", null: false
+    t.index ["account_id", "assistant_id", "usage_type", "bucket_started_at"], name: "index_captain_usages_unique_bucket", unique: true
+    t.index ["account_id"], name: "index_captain_usages_on_account_id"
+    t.index ["assistant_id"], name: "index_captain_usages_on_assistant_id"
+  end
+
   create_table "categories", force: :cascade do |t|
     t.integer "account_id", null: false
     t.integer "portal_id", null: false

enterprise/app/jobs/captain/conversation/response_builder_job.rb

@@ -77,6 +77,10 @@ class Captain::Conversation::ResponseBuilderJob < ApplicationJob
         create_messages
         Rails.logger.info("[CAPTAIN][ResponseBuilderJob] Incrementing response usage for #{account.id}")
         account.increment_response_usage
+        # Granular usage reporting. Only assistant + copilot responses are logged for now;
+        # the other increment_response_usage call sites (task services, audio transcription)
+        # map to usage_types that are intentionally not recorded yet.
+        Captain::Usage.log(account: account, assistant: @assistant, usage_type: :assistant_response)
       end
     end
   end

enterprise/app/models/captain/assistant.rb

@@ -35,6 +35,7 @@ class Captain::Assistant < ApplicationRecord
   has_many :messages, as: :sender, dependent: :nullify
   has_many :copilot_threads, dependent: :destroy_async
   has_many :scenarios, class_name: 'Captain::Scenario', dependent: :destroy_async
+  has_many :usages, class_name: 'Captain::Usage', dependent: :destroy_async
 
   store_accessor :config, :temperature, :feature_faq, :feature_memory, :feature_contact_attributes, :product_name
 

enterprise/app/models/captain/usage.rb

@@ -0,0 +1,73 @@
+# == Schema Information
+#
+# Table name: captain_usages
+#
+#  id                :bigint           not null, primary key
+#  bucket_started_at :datetime         not null
+#  credits_used      :integer          default(0), not null
+#  usage_type        :integer          not null
+#  created_at        :datetime         not null
+#  updated_at        :datetime         not null
+#  account_id        :bigint           not null
+#  assistant_id      :bigint           not null
+#
+# Indexes
+#
+#  index_captain_usages_on_account_id            (account_id)
+#  index_captain_usages_on_assistant_id          (assistant_id)
+#  index_captain_usages_unique_bucket            (account_id,assistant_id,usage_type,bucket_started_at) UNIQUE
+#
+class Captain::Usage < ApplicationRecord
+  self.table_name = 'captain_usages'
+
+  # Width of each reporting bucket. Usage is binned into 15-minute UTC buckets so
+  # it can be re-sliced into any caller timezone, including :45 offset zones
+  # (e.g. Nepal +5:45). Do not widen this without revisiting timezone slicing.
+  BUCKET_SIZE = 15.minutes
+
+  # Only :assistant_response and :copilot_response are logged today. The
+  # remaining values are reserved for future usage sources:
+  #   editor_actions, label_suggestion, audio_transcription
+  enum usage_type: {
+    assistant_response: 0,
+    copilot_response: 1
+  }
+
+  belongs_to :assistant, class_name: 'Captain::Assistant'
+  belongs_to :account
+
+  # log is the only sanctioned write path; it uses upsert, which skips model
+  # validations. Column invariants are enforced via NOT NULL at the DB level.
+  #
+  # Records credit usage against the 15-minute bucket that contains +occurred_at+.
+  # Concurrent calls are safe: the row is created or its counter incremented in a
+  # single atomic statement keyed on the unique (account, assistant, type, bucket).
+  def self.log(account:, assistant:, usage_type:, credits: 1, occurred_at: Time.current)
+    now = Time.current
+    # rubocop:disable Rails/SkipsModelValidations
+    upsert(
+      {
+        account_id: account.id,
+        assistant_id: assistant.id,
+        usage_type: usage_types.fetch(usage_type.to_s),
+        bucket_started_at: bucket_for(occurred_at),
+        credits_used: credits,
+        created_at: now,
+        updated_at: now
+      },
+      unique_by: 'index_captain_usages_unique_bucket',
+      on_duplicate: Arel.sql(
+        'credits_used = captain_usages.credits_used + EXCLUDED.credits_used, updated_at = EXCLUDED.updated_at'
+      )
+    )
+    # rubocop:enable Rails/SkipsModelValidations
+  end
+
+  # Floors +time+ to the start of its 15-minute UTC bucket. Subseconds are
+  # dropped so the bucket timestamp is stable across calls within the same
+  # bucket; otherwise distinct microseconds break the unique-key upsert.
+  def self.bucket_for(time)
+    epoch = time.to_i
+    Time.at(epoch - (epoch % BUCKET_SIZE.to_i)).utc
+  end
+end

enterprise/app/models/enterprise/concerns/account.rb

@@ -13,6 +13,7 @@ module Enterprise::Concerns::Account
     has_many :captain_assistant_responses, dependent: :destroy_async, class_name: 'Captain::AssistantResponse'
     has_many :captain_documents, dependent: :destroy_async, class_name: 'Captain::Document'
     has_many :captain_custom_tools, dependent: :destroy_async, class_name: 'Captain::CustomTool'
+    has_many :captain_usages, dependent: :destroy_async, class_name: 'Captain::Usage'
 
     has_many :copilot_threads, dependent: :destroy_async
     has_many :companies, dependent: :destroy_async

enterprise/app/services/captain/copilot/chat_service.rb

@@ -29,6 +29,10 @@ class Captain::Copilot::ChatService < Llm::BaseAiService
       "#{self.class.name} Assistant: #{@assistant.id}, Incrementing response usage for account #{@account.id}"
     )
     @account.increment_response_usage
+    # Granular usage reporting. Only assistant + copilot responses are logged for now;
+    # the other increment_response_usage call sites (task services, audio transcription)
+    # map to usage_types that are intentionally not recorded yet.
+    Captain::Usage.log(account: @account, assistant: @assistant, usage_type: :copilot_response)
 
     response
   end