GitLab Secrets Manager ADR 010: シークレットローテーションメタデータへの Rails ActiveRecord の使用

CREATE TABLE rotation_infos (
  id SERIAL PRIMARY KEY,
  project_id INTEGER NOT NULL REFERENCES projects(id),
  secret_name VARCHAR NOT NULL,

  -- Rotation scheduling
  rotation_interval_days INTEGER,  -- Computed days for standard intervals (30, 60, 90)
  rotation_interval_raw_value VARCHAR NOT NULL,  -- Original input: "30", "60", "90", or cron syntax
  next_reminder_at TIMESTAMP NOT NULL,

  -- Notification tracking
  last_reminder_sent_at TIMESTAMP,

  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

-- Indexes for performance
CREATE UNIQUE INDEX idx_rotation_infos_project_secret ON rotation_infos(project_id, secret_name);
CREATE INDEX idx_rotation_infos_reminder_at ON rotation_infos(next_reminder_at);

class SecretRotationReminderWorker
  def perform
    # Single database query to find all secrets due for rotation
    RotationInfo
      .where('next_reminder_at <= ?', Time.current)
      .find_in_batches(batch_size: 1000) do |batch|

      batch.each do |rotation_info|
        # Verify secret exists in OpenBao before sending reminder
        if secret_exists_in_vault?(rotation_info)
          send_rotation_reminder(rotation_info)
          rotation_info.update!(
            last_reminder_sent_at: Time.current,
            next_reminder_at: calculate_next_reminder(rotation_info)
          )
        else
          # Handle orphaned rotation records
          rotation_info.destroy
        end
      end
    end
  end
end

# Rejected approach - requires constant OpenBao queries even when no rotations are due
class RotationCheckerWithOpenBao
  def perform
    Project.find_each do |project|
      # LIST operation for every project, every hour
      secrets = vault_client.logical.list("#{project.path}/secret/metadata")
      next if secrets.blank?

      secrets.each do |secret|
        # READ operation for each secret to check rotation date
        metadata = vault_client.logical.read("#{project.path}/secret/metadata/#{secret}")
        rotation_date = metadata.data[:custom_metadata][:next_rotation_at]

        if Time.parse(rotation_date) <= Time.current
          send_rotation_reminder(project, secret)
        end
      end
    end
  end
end