{"action":"created","comment":{"url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/comments/26079662","id":26079662,"diff_hunk":"@@ -0,0 +1,278 @@\n+\n+-- A new table to hold a history of account lifecycle events.\n+-- Events are added in simple increasing auto-increment order.\n+CREATE TABLE eventLog (\n+  seqnum BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,\n+  uid BINARY(16) NOT NULL,\n+  typ ENUM('create', 'verify', 'reset', 'delete') NOT NULL,\n+  iat INT UNSIGNED NOT NULL\n+) ENGINE=InnoDB;\n+\n+\n+-- When an account is created, log a \"create\" event.\n+-- Pre-verified accounts may also get a \"verify\" event.\n+CREATE PROCEDURE `createAccount_2` (\n+    IN `uid` BINARY(16) ,\n+    IN `normalizedEmail` VARCHAR(255),\n+    IN `email` VARCHAR(255),\n+    IN `emailCode` BINARY(16),\n+    IN `emailVerified` TINYINT(1),\n+    IN `kA` BINARY(32),\n+    IN `wrapWrapKb` BINARY(32),\n+    IN `authSalt` BINARY(32),\n+    IN `verifierVersion` TINYINT UNSIGNED,\n+    IN `verifyHash` BINARY(32),\n+    IN `verifierSetAt` BIGINT UNSIGNED,\n+    IN `createdAt` BIGINT UNSIGNED,\n+    IN `locale` VARCHAR(255)\n+)\n+BEGIN\n+    DECLARE EXIT HANDLER FOR SQLEXCEPTION\n+    BEGIN\n+        ROLLBACK;\n+        RESIGNAL;\n+    END;\n+\n+    START TRANSACTION;\n+\n+    INSERT INTO accounts(\n+        uid,\n+        normalizedEmail,\n+        email,\n+        emailCode,\n+        emailVerified,\n+        kA,\n+        wrapWrapKb,\n+        authSalt,\n+        verifierVersion,\n+        verifyHash,\n+        verifierSetAt,\n+        createdAt,\n+        locale\n+    )\n+    VALUES(\n+        uid,\n+        LOWER(normalizedEmail),\n+        email,\n+        emailCode,\n+        emailVerified,\n+        kA,\n+        wrapWrapKb,\n+        authSalt,\n+        verifierVersion,\n+        verifyHash,\n+        verifierSetAt,\n+        createdAt,\n+        locale\n+    );\n+\n+    INSERT INTO eventLog(\n+        seqnum,\n+        uid,\n+        typ,\n+        iat\n+    )\n+    VALUES(\n+        NULL,","path":"db/schema/patch-008-009.sql","position":76,"original_position":76,"commit_id":"8c14c4d6fef0c60a36214085521b425c04662210","original_commit_id":"8c14c4d6fef0c60a36214085521b425c04662210","user":{"login":"chilts","id":3048,"avatar_url":"https://avatars.githubusercontent.com/u/3048?v=3","gravatar_id":"","url":"https://api.github.com/users/chilts","html_url":"https://github.com/chilts","followers_url":"https://api.github.com/users/chilts/followers","following_url":"https://api.github.com/users/chilts/following{/other_user}","gists_url":"https://api.github.com/users/chilts/gists{/gist_id}","starred_url":"https://api.github.com/users/chilts/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/chilts/subscriptions","organizations_url":"https://api.github.com/users/chilts/orgs","repos_url":"https://api.github.com/users/chilts/repos","events_url":"https://api.github.com/users/chilts/events{/privacy}","received_events_url":"https://api.github.com/users/chilts/received_events","type":"User","site_admin":false},"body":"Is this to be explicit for some reason? Otherwise this will do.\r\n\r\n```sql\r\nINSERT INTO eventLog(uid, typ, iat) VALUES (uid, \"create\", UNIX_TIMESTAMP());\r\n```\r\n","created_at":"2015-03-09T21:42:39Z","updated_at":"2015-03-09T21:42:39Z","html_url":"https://github.com/mozilla/fxa-auth-db-mysql/pull/20#discussion_r26079662","pull_request_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/20","_links":{"self":{"href":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/comments/26079662"},"html":{"href":"https://github.com/mozilla/fxa-auth-db-mysql/pull/20#discussion_r26079662"},"pull_request":{"href":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/20"}}},"pull_request":{"url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/20","id":30737838,"html_url":"https://github.com/mozilla/fxa-auth-db-mysql/pull/20","diff_url":"https://github.com/mozilla/fxa-auth-db-mysql/pull/20.diff","patch_url":"https://github.com/mozilla/fxa-auth-db-mysql/pull/20.patch","issue_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/20","number":20,"state":"open","locked":false,"title":"Explicitly track account lifecycle events in the db","user":{"login":"rfk","id":34695,"avatar_url":"https://avatars.githubusercontent.com/u/34695?v=3","gravatar_id":"","url":"https://api.github.com/users/rfk","html_url":"https://github.com/rfk","followers_url":"https://api.github.com/users/rfk/followers","following_url":"https://api.github.com/users/rfk/following{/other_user}","gists_url":"https://api.github.com/users/rfk/gists{/gist_id}","starred_url":"https://api.github.com/users/rfk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rfk/subscriptions","organizations_url":"https://api.github.com/users/rfk/orgs","repos_url":"https://api.github.com/users/rfk/repos","events_url":"https://api.github.com/users/rfk/events{/privacy}","received_events_url":"https://api.github.com/users/rfk/received_events","type":"User","site_admin":false},"body":"Here's an idea that @dannycoates and I have been kicking around, for how the auth-server might interact with the upcoming notification server (https://github.com/mozilla/fxa-notification-server).\r\n\r\nThe idea here is to transactionally track our major account-lifecycle events in the database.  There's a simple \"eventLog\" table to which we append create, verify, reset and delete events as they happen, with an auto-increment sequence number to ensure they're stored properly and consistently ordered.\r\n\r\nWhen the notification server is ready, some background process in the server can read from this queue and schlep the events on over there asynchronously.  Details of that TBD but I added a couple of the low-level functions it'll probably need.\r\n\r\nThe advantage of this approach is that we won't lose creation or deletion events, and we're decoupled from the notification server.  The disadvantage is we're doing queueing and logging in the db, blech.  But since these events should be rare, and the data we keep for them small, it seems like a worthwhile tradeoff overall.  At least it's nice and simple!\r\n\r\n@chilts thoughts?  @ckolos thoughts?  I remain open to the suggestion that this is a terrible idea :-)\r\n\r\nPossible optimization: we could keep only the most recent event for any (uid, typ) pair, rather than e.g. a hundred \"reset\" events for a user who resets their account a hundred times.  But doing so would cost an additional index on this table, or some slow background compaction process.  And eventually we might solve this in a more general way as part of the evolving notifications infra, so we don't need to make this solution awesome right now, it just needs to work.\r\n\r\n(This is a WIP and should not be merged yet, although if we all agree on the approach then I'd like to get it out on train-33).","created_at":"2015-03-09T05:58:08Z","updated_at":"2015-03-09T21:42:39Z","closed_at":null,"merged_at":null,"merge_commit_sha":"84528c40f7d0b8c28df807cd9f15128e8cbccef8","assignee":{"login":"rfk","id":34695,"avatar_url":"https://avatars.githubusercontent.com/u/34695?v=3","gravatar_id":"","url":"https://api.github.com/users/rfk","html_url":"https://github.com/rfk","followers_url":"https://api.github.com/users/rfk/followers","following_url":"https://api.github.com/users/rfk/following{/other_user}","gists_url":"https://api.github.com/users/rfk/gists{/gist_id}","starred_url":"https://api.github.com/users/rfk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rfk/subscriptions","organizations_url":"https://api.github.com/users/rfk/orgs","repos_url":"https://api.github.com/users/rfk/repos","events_url":"https://api.github.com/users/rfk/events{/privacy}","received_events_url":"https://api.github.com/users/rfk/received_events","type":"User","site_admin":false},"milestone":null,"commits_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/20/commits","review_comments_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/20/comments","review_comment_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/comments{/number}","comments_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/20/comments","statuses_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/statuses/8c14c4d6fef0c60a36214085521b425c04662210","head":{"label":"mozilla:rfk/eventlog","ref":"rfk/eventlog","sha":"8c14c4d6fef0c60a36214085521b425c04662210","user":{"login":"mozilla","id":131524,"avatar_url":"https://avatars.githubusercontent.com/u/131524?v=3","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"repo":{"id":23937466,"name":"fxa-auth-db-mysql","full_name":"mozilla/fxa-auth-db-mysql","owner":{"login":"mozilla","id":131524,"avatar_url":"https://avatars.githubusercontent.com/u/131524?v=3","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/mozilla/fxa-auth-db-mysql","description":"MySql backend for the fxa-auth-db-server","fork":false,"url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql","forks_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/forks","keys_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/teams","hooks_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/hooks","issue_events_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/events{/number}","events_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/events","assignees_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/assignees{/user}","branches_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/branches{/branch}","tags_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/tags","blobs_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/git/refs{/sha}","trees_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/statuses/{sha}","languages_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/languages","stargazers_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/stargazers","contributors_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/contributors","subscribers_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/subscribers","subscription_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/subscription","commits_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/commits{/sha}","git_commits_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/git/commits{/sha}","comments_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/comments{/number}","issue_comment_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/comments{/number}","contents_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/contents/{+path}","compare_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/merges","archive_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/downloads","issues_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues{/number}","pulls_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls{/number}","milestones_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/milestones{/number}","notifications_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/labels{/name}","releases_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/releases{/id}","created_at":"2014-09-11T22:06:52Z","updated_at":"2015-03-06T08:05:27Z","pushed_at":"2015-03-09T05:46:49Z","git_url":"git://github.com/mozilla/fxa-auth-db-mysql.git","ssh_url":"git@github.com:mozilla/fxa-auth-db-mysql.git","clone_url":"https://github.com/mozilla/fxa-auth-db-mysql.git","svn_url":"https://github.com/mozilla/fxa-auth-db-mysql","homepage":"","size":410,"stargazers_count":1,"watchers_count":1,"language":"JavaScript","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":6,"mirror_url":null,"open_issues_count":7,"forks":6,"open_issues":7,"watchers":1,"default_branch":"master"}},"base":{"label":"mozilla:master","ref":"master","sha":"66c8ee90172a1124fe113425ec70b9a7e4338edf","user":{"login":"mozilla","id":131524,"avatar_url":"https://avatars.githubusercontent.com/u/131524?v=3","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"repo":{"id":23937466,"name":"fxa-auth-db-mysql","full_name":"mozilla/fxa-auth-db-mysql","owner":{"login":"mozilla","id":131524,"avatar_url":"https://avatars.githubusercontent.com/u/131524?v=3","gravatar_id":"","url":"https://api.github.com/users/mozilla","html_url":"https://github.com/mozilla","followers_url":"https://api.github.com/users/mozilla/followers","following_url":"https://api.github.com/users/mozilla/following{/other_user}","gists_url":"https://api.github.com/users/mozilla/gists{/gist_id}","starred_url":"https://api.github.com/users/mozilla/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mozilla/subscriptions","organizations_url":"https://api.github.com/users/mozilla/orgs","repos_url":"https://api.github.com/users/mozilla/repos","events_url":"https://api.github.com/users/mozilla/events{/privacy}","received_events_url":"https://api.github.com/users/mozilla/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/mozilla/fxa-auth-db-mysql","description":"MySql backend for the fxa-auth-db-server","fork":false,"url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql","forks_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/forks","keys_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/keys{/key_id}","collaborators_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/teams","hooks_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/hooks","issue_events_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/events{/number}","events_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/events","assignees_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/assignees{/user}","branches_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/branches{/branch}","tags_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/tags","blobs_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/git/refs{/sha}","trees_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/git/trees{/sha}","statuses_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/statuses/{sha}","languages_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/languages","stargazers_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/stargazers","contributors_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/contributors","subscribers_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/subscribers","subscription_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/subscription","commits_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/commits{/sha}","git_commits_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/git/commits{/sha}","comments_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/comments{/number}","issue_comment_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/comments{/number}","contents_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/contents/{+path}","compare_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/compare/{base}...{head}","merges_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/merges","archive_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/downloads","issues_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues{/number}","pulls_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls{/number}","milestones_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/milestones{/number}","notifications_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/labels{/name}","releases_url":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/releases{/id}","created_at":"2014-09-11T22:06:52Z","updated_at":"2015-03-06T08:05:27Z","pushed_at":"2015-03-09T05:46:49Z","git_url":"git://github.com/mozilla/fxa-auth-db-mysql.git","ssh_url":"git@github.com:mozilla/fxa-auth-db-mysql.git","clone_url":"https://github.com/mozilla/fxa-auth-db-mysql.git","svn_url":"https://github.com/mozilla/fxa-auth-db-mysql","homepage":"","size":410,"stargazers_count":1,"watchers_count":1,"language":"JavaScript","has_issues":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":6,"mirror_url":null,"open_issues_count":7,"forks":6,"open_issues":7,"watchers":1,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/20"},"html":{"href":"https://github.com/mozilla/fxa-auth-db-mysql/pull/20"},"issue":{"href":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/20"},"comments":{"href":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/issues/20/comments"},"review_comments":{"href":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/20/comments"},"review_comment":{"href":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/pulls/20/commits"},"statuses":{"href":"https://api.github.com/repos/mozilla/fxa-auth-db-mysql/statuses/8c14c4d6fef0c60a36214085521b425c04662210"}}}}