title: Best practices for OAuth + REST API logging? What should an OAuth access logging statistics table look like?
body: <p>What are the best practices for logging an OAuth REST API? I would like to keep the database to a minimum as the http logs can store a lot. However, I also want to monitor usage and instantly detect abuse.</p>
  
  <p>Is there a custom <a href="http://httpd.apache.org/docs/2.0/mod/mod_log_config.html#formats" rel="nofollow">LogFormat</a> for Apache can be set to keep much of the data?</p>
  
  <p>If I was to make a table that logged usage of a REST API that used an OAuth security mechanism, what should that table look like? I am new to OAuth and want to make sure I don't overlook things.</p>
  
  <p>Here is what I have so far...</p>
  
  <pre><code>"access_log"
  access_method VARCHAR(8) -- PUT/GET/POST/DELETE/etc..
  request_uri VARCHAR(1024)
  token_id int references token.id
  user_id int references user.id
  </code></pre>
  
  <p>As a common starting point, let's assume the tables look like those listed in this <a href="http://djpate.com/2011/01/13/how-to-write-a-complete-oauth-provider-in-php5/" rel="nofollow">OAuth Provider example</a>.</p>
  
  <pre><code>"user"
  id  int , auto_increment
  login   varchar(50)
  
  "consumer"
  id  int , auto_increment
  consumer_key    varchar(250)
  consumer_secret varchar(250)
  active  tinyint(1)
  
  "consumer_nonce"
  id  int , auto_increment
  consumer_id int references consumer.id
  timestamp   bigint
  nonce   varchar(250)
  
  "token"
  id  int , auto_increment
  user_id int references user.id
  type    int references token_type.id
  consumer_id int references consumer.id
  token   varchar(250)
  token_secret    varchar(250)
  callback_url    varchar(250)
  verifier    varchar(250)
  
  "token_type" // 1 = Request, 2 = Access
  id  int , auto_increment
  type    varchar(50)
  </code></pre>