{"action":"created","comment":{"url":"https://api.github.com/repos/findOctave/cortex/pulls/comments/234866141","pull_request_review_id":176596886,"id":234866141,"node_id":"MDI0OlB1bGxSZXF1ZXN0UmV2aWV3Q29tbWVudDIzNDg2NjE0MQ==","diff_hunk":"@@ -0,0 +1,36 @@\n+/**\n+ * A job that continously snapshots data into a read-only store/table to allow\n+ * for higher throughput. More of a \"materialized view\" pattern than a\n+ * traditional cache-aside caching technique.\n+ */\n+import db from 'db'\n+import * as cliniko from 'cliniko'\n+import * as log from '../log'\n+\n+const upsert = async ({ doc, tableName }) => {\n+  const data = { id: doc.id, body: JSON.stringify(doc) }\n+  try {\n+    await db.insert(data).into(tableName)\n+  } catch (err) {\n+    await db\n+      .table(tableName)\n+      .where({ id: doc.id })\n+      .update(data)\n+  }\n+  log.success(`Cached Cliniko record ${doc.id} into ${tableName}`)\n+}\n+\n+export const snapshot = async resource => {\n+  const tableName = `snapshot_cliniko_${resource}`\n+  const collection = await cliniko.find({ resource, all: true })\n+  await db.schema.createTableIfNotExists(tableName, table => {\n+    table.increments('id')\n+    table.json('body')","path":"apps/jobs/lib/data-cleanup/cache.js","position":28,"original_position":28,"commit_id":"062421ffb35edcfeb590215145c74601d19346d3","original_commit_id":"062421ffb35edcfeb590215145c74601d19346d3","user":{"login":"craigspaeth","id":555859,"node_id":"MDQ6VXNlcjU1NTg1OQ==","avatar_url":"https://avatars1.githubusercontent.com/u/555859?v=4","gravatar_id":"","url":"https://api.github.com/users/craigspaeth","html_url":"https://github.com/craigspaeth","followers_url":"https://api.github.com/users/craigspaeth/followers","following_url":"https://api.github.com/users/craigspaeth/following{/other_user}","gists_url":"https://api.github.com/users/craigspaeth/gists{/gist_id}","starred_url":"https://api.github.com/users/craigspaeth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/craigspaeth/subscriptions","organizations_url":"https://api.github.com/users/craigspaeth/orgs","repos_url":"https://api.github.com/users/craigspaeth/repos","events_url":"https://api.github.com/users/craigspaeth/events{/privacy}","received_events_url":"https://api.github.com/users/craigspaeth/received_events","type":"User","site_admin":false},"body":"PG recently added first-class JSON support which is nice for being able to use it as a document store similar to MongoDB when needed. Since we're essentially just using PG as an ephemeral cache here I don't think it makes sense to do propper object-relational-mapping, and we can just try to do the most straightforward persisting of JSON blobs instead.\r\n\r\nI do believe Octave will need to have more structured/relational data down the line, so I opted not to immediately reach for a NoSQL database if we can avoid having an extra DB in the ecosystem.","created_at":"2018-11-20T04:29:11Z","updated_at":"2018-11-20T04:30:03Z","html_url":"https://github.com/findOctave/cortex/pull/332#discussion_r234866141","pull_request_url":"https://api.github.com/repos/findOctave/cortex/pulls/332","author_association":"CONTRIBUTOR","_links":{"self":{"href":"https://api.github.com/repos/findOctave/cortex/pulls/comments/234866141"},"html":{"href":"https://github.com/findOctave/cortex/pull/332#discussion_r234866141"},"pull_request":{"href":"https://api.github.com/repos/findOctave/cortex/pulls/332"}}},"pull_request":{"url":"https://api.github.com/repos/findOctave/cortex/pulls/332","id":232186503,"node_id":"MDExOlB1bGxSZXF1ZXN0MjMyMTg2NTAz","html_url":"https://github.com/findOctave/cortex/pull/332","diff_url":"https://github.com/findOctave/cortex/pull/332.diff","patch_url":"https://github.com/findOctave/cortex/pull/332.patch","issue_url":"https://api.github.com/repos/findOctave/cortex/issues/332","number":332,"state":"open","locked":false,"title":"Continously create snapshots of Cliniko resources for caching purpose","user":{"login":"craigspaeth","id":555859,"node_id":"MDQ6VXNlcjU1NTg1OQ==","avatar_url":"https://avatars1.githubusercontent.com/u/555859?v=4","gravatar_id":"","url":"https://api.github.com/users/craigspaeth","html_url":"https://github.com/craigspaeth","followers_url":"https://api.github.com/users/craigspaeth/followers","following_url":"https://api.github.com/users/craigspaeth/following{/other_user}","gists_url":"https://api.github.com/users/craigspaeth/gists{/gist_id}","starred_url":"https://api.github.com/users/craigspaeth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/craigspaeth/subscriptions","organizations_url":"https://api.github.com/users/craigspaeth/orgs","repos_url":"https://api.github.com/users/craigspaeth/repos","events_url":"https://api.github.com/users/craigspaeth/events{/privacy}","received_events_url":"https://api.github.com/users/craigspaeth/received_events","type":"User","site_admin":false},"body":"This gets the ball rolling on some caching practices we can use to work around Cliniko rate limiting. This will continuously (every 10 minutes alongside lead syncing for now) write snapshots of Cliniko endpoints to JSON data in PostgreSQL (or the no-install-overhead SQLite in dev).\r\n\r\nWe can then use these snapshots to return (at most 10 minutes old) stale data for read-only purposes. The hope is this continuous pipeline will avoid the invalidation problems that come with [traditional cache-aside](https://blog.cdemi.io/design-patterns-cache-aside-pattern/) patterns by changing it to an eventual consistency problem like we [deal](https://github.com/findOctave/cortex/pull/331) with [elsewhere](https://github.com/findOctave/cortex/pull/324).\r\n\r\nI'm implementing this in querying appointment types for now as a test drive (because it's a simple query that gets actual traffic). If successful, down the line we can choose to query the database snapshots instead of Cliniko as we hit rate-limiting/performance problems.\r\n\r\nMore comments inline.","created_at":"2018-11-20T04:08:55Z","updated_at":"2018-11-20T04:30:03Z","closed_at":null,"merged_at":null,"merge_commit_sha":"eb1a1610e82af430933d6b9592fb179d82a1b90f","assignee":null,"assignees":[],"requested_reviewers":[{"login":"lionsdwarf","id":10677428,"node_id":"MDQ6VXNlcjEwNjc3NDI4","avatar_url":"https://avatars1.githubusercontent.com/u/10677428?v=4","gravatar_id":"","url":"https://api.github.com/users/lionsdwarf","html_url":"https://github.com/lionsdwarf","followers_url":"https://api.github.com/users/lionsdwarf/followers","following_url":"https://api.github.com/users/lionsdwarf/following{/other_user}","gists_url":"https://api.github.com/users/lionsdwarf/gists{/gist_id}","starred_url":"https://api.github.com/users/lionsdwarf/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/lionsdwarf/subscriptions","organizations_url":"https://api.github.com/users/lionsdwarf/orgs","repos_url":"https://api.github.com/users/lionsdwarf/repos","events_url":"https://api.github.com/users/lionsdwarf/events{/privacy}","received_events_url":"https://api.github.com/users/lionsdwarf/received_events","type":"User","site_admin":false}],"requested_teams":[],"labels":[],"milestone":null,"commits_url":"https://api.github.com/repos/findOctave/cortex/pulls/332/commits","review_comments_url":"https://api.github.com/repos/findOctave/cortex/pulls/332/comments","review_comment_url":"https://api.github.com/repos/findOctave/cortex/pulls/comments{/number}","comments_url":"https://api.github.com/repos/findOctave/cortex/issues/332/comments","statuses_url":"https://api.github.com/repos/findOctave/cortex/statuses/062421ffb35edcfeb590215145c74601d19346d3","head":{"label":"craigspaeth:cliniko-cache","ref":"cliniko-cache","sha":"062421ffb35edcfeb590215145c74601d19346d3","user":{"login":"craigspaeth","id":555859,"node_id":"MDQ6VXNlcjU1NTg1OQ==","avatar_url":"https://avatars1.githubusercontent.com/u/555859?v=4","gravatar_id":"","url":"https://api.github.com/users/craigspaeth","html_url":"https://github.com/craigspaeth","followers_url":"https://api.github.com/users/craigspaeth/followers","following_url":"https://api.github.com/users/craigspaeth/following{/other_user}","gists_url":"https://api.github.com/users/craigspaeth/gists{/gist_id}","starred_url":"https://api.github.com/users/craigspaeth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/craigspaeth/subscriptions","organizations_url":"https://api.github.com/users/craigspaeth/orgs","repos_url":"https://api.github.com/users/craigspaeth/repos","events_url":"https://api.github.com/users/craigspaeth/events{/privacy}","received_events_url":"https://api.github.com/users/craigspaeth/received_events","type":"User","site_admin":false},"repo":{"id":136385310,"node_id":"MDEwOlJlcG9zaXRvcnkxMzYzODUzMTA=","name":"cortex","full_name":"craigspaeth/cortex","private":false,"owner":{"login":"craigspaeth","id":555859,"node_id":"MDQ6VXNlcjU1NTg1OQ==","avatar_url":"https://avatars1.githubusercontent.com/u/555859?v=4","gravatar_id":"","url":"https://api.github.com/users/craigspaeth","html_url":"https://github.com/craigspaeth","followers_url":"https://api.github.com/users/craigspaeth/followers","following_url":"https://api.github.com/users/craigspaeth/following{/other_user}","gists_url":"https://api.github.com/users/craigspaeth/gists{/gist_id}","starred_url":"https://api.github.com/users/craigspaeth/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/craigspaeth/subscriptions","organizations_url":"https://api.github.com/users/craigspaeth/orgs","repos_url":"https://api.github.com/users/craigspaeth/repos","events_url":"https://api.github.com/users/craigspaeth/events{/privacy}","received_events_url":"https://api.github.com/users/craigspaeth/received_events","type":"User","site_admin":false},"html_url":"https://github.com/craigspaeth/cortex","description":"The beginning of something great","fork":true,"url":"https://api.github.com/repos/craigspaeth/cortex","forks_url":"https://api.github.com/repos/craigspaeth/cortex/forks","keys_url":"https://api.github.com/repos/craigspaeth/cortex/keys{/key_id}","collaborators_url":"https://api.github.com/repos/craigspaeth/cortex/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/craigspaeth/cortex/teams","hooks_url":"https://api.github.com/repos/craigspaeth/cortex/hooks","issue_events_url":"https://api.github.com/repos/craigspaeth/cortex/issues/events{/number}","events_url":"https://api.github.com/repos/craigspaeth/cortex/events","assignees_url":"https://api.github.com/repos/craigspaeth/cortex/assignees{/user}","branches_url":"https://api.github.com/repos/craigspaeth/cortex/branches{/branch}","tags_url":"https://api.github.com/repos/craigspaeth/cortex/tags","blobs_url":"https://api.github.com/repos/craigspaeth/cortex/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/craigspaeth/cortex/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/craigspaeth/cortex/git/refs{/sha}","trees_url":"https://api.github.com/repos/craigspaeth/cortex/git/trees{/sha}","statuses_url":"https://api.github.com/repos/craigspaeth/cortex/statuses/{sha}","languages_url":"https://api.github.com/repos/craigspaeth/cortex/languages","stargazers_url":"https://api.github.com/repos/craigspaeth/cortex/stargazers","contributors_url":"https://api.github.com/repos/craigspaeth/cortex/contributors","subscribers_url":"https://api.github.com/repos/craigspaeth/cortex/subscribers","subscription_url":"https://api.github.com/repos/craigspaeth/cortex/subscription","commits_url":"https://api.github.com/repos/craigspaeth/cortex/commits{/sha}","git_commits_url":"https://api.github.com/repos/craigspaeth/cortex/git/commits{/sha}","comments_url":"https://api.github.com/repos/craigspaeth/cortex/comments{/number}","issue_comment_url":"https://api.github.com/repos/craigspaeth/cortex/issues/comments{/number}","contents_url":"https://api.github.com/repos/craigspaeth/cortex/contents/{+path}","compare_url":"https://api.github.com/repos/craigspaeth/cortex/compare/{base}...{head}","merges_url":"https://api.github.com/repos/craigspaeth/cortex/merges","archive_url":"https://api.github.com/repos/craigspaeth/cortex/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/craigspaeth/cortex/downloads","issues_url":"https://api.github.com/repos/craigspaeth/cortex/issues{/number}","pulls_url":"https://api.github.com/repos/craigspaeth/cortex/pulls{/number}","milestones_url":"https://api.github.com/repos/craigspaeth/cortex/milestones{/number}","notifications_url":"https://api.github.com/repos/craigspaeth/cortex/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/craigspaeth/cortex/labels{/name}","releases_url":"https://api.github.com/repos/craigspaeth/cortex/releases{/id}","deployments_url":"https://api.github.com/repos/craigspaeth/cortex/deployments","created_at":"2018-06-06T20:57:53Z","updated_at":"2018-11-19T18:38:58Z","pushed_at":"2018-11-20T04:06:41Z","git_url":"git://github.com/craigspaeth/cortex.git","ssh_url":"git@github.com:craigspaeth/cortex.git","clone_url":"https://github.com/craigspaeth/cortex.git","svn_url":"https://github.com/craigspaeth/cortex","homepage":null,"size":5613,"stargazers_count":0,"watchers_count":0,"language":"JavaScript","has_issues":false,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":0,"mirror_url":null,"archived":false,"open_issues_count":0,"license":{"key":"mit","name":"MIT License","spdx_id":"MIT","url":"https://api.github.com/licenses/mit","node_id":"MDc6TGljZW5zZTEz"},"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"findOctave:master","ref":"master","sha":"7790eb9e2dc17ba118f7f60d6f3c6ef1f361eaa8","user":{"login":"findOctave","id":40005440,"node_id":"MDEyOk9yZ2FuaXphdGlvbjQwMDA1NDQw","avatar_url":"https://avatars2.githubusercontent.com/u/40005440?v=4","gravatar_id":"","url":"https://api.github.com/users/findOctave","html_url":"https://github.com/findOctave","followers_url":"https://api.github.com/users/findOctave/followers","following_url":"https://api.github.com/users/findOctave/following{/other_user}","gists_url":"https://api.github.com/users/findOctave/gists{/gist_id}","starred_url":"https://api.github.com/users/findOctave/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/findOctave/subscriptions","organizations_url":"https://api.github.com/users/findOctave/orgs","repos_url":"https://api.github.com/users/findOctave/repos","events_url":"https://api.github.com/users/findOctave/events{/privacy}","received_events_url":"https://api.github.com/users/findOctave/received_events","type":"Organization","site_admin":false},"repo":{"id":136378049,"node_id":"MDEwOlJlcG9zaXRvcnkxMzYzNzgwNDk=","name":"cortex","full_name":"findOctave/cortex","private":false,"owner":{"login":"findOctave","id":40005440,"node_id":"MDEyOk9yZ2FuaXphdGlvbjQwMDA1NDQw","avatar_url":"https://avatars2.githubusercontent.com/u/40005440?v=4","gravatar_id":"","url":"https://api.github.com/users/findOctave","html_url":"https://github.com/findOctave","followers_url":"https://api.github.com/users/findOctave/followers","following_url":"https://api.github.com/users/findOctave/following{/other_user}","gists_url":"https://api.github.com/users/findOctave/gists{/gist_id}","starred_url":"https://api.github.com/users/findOctave/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/findOctave/subscriptions","organizations_url":"https://api.github.com/users/findOctave/orgs","repos_url":"https://api.github.com/users/findOctave/repos","events_url":"https://api.github.com/users/findOctave/events{/privacy}","received_events_url":"https://api.github.com/users/findOctave/received_events","type":"Organization","site_admin":false},"html_url":"https://github.com/findOctave/cortex","description":"The main Octave repo","fork":false,"url":"https://api.github.com/repos/findOctave/cortex","forks_url":"https://api.github.com/repos/findOctave/cortex/forks","keys_url":"https://api.github.com/repos/findOctave/cortex/keys{/key_id}","collaborators_url":"https://api.github.com/repos/findOctave/cortex/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/findOctave/cortex/teams","hooks_url":"https://api.github.com/repos/findOctave/cortex/hooks","issue_events_url":"https://api.github.com/repos/findOctave/cortex/issues/events{/number}","events_url":"https://api.github.com/repos/findOctave/cortex/events","assignees_url":"https://api.github.com/repos/findOctave/cortex/assignees{/user}","branches_url":"https://api.github.com/repos/findOctave/cortex/branches{/branch}","tags_url":"https://api.github.com/repos/findOctave/cortex/tags","blobs_url":"https://api.github.com/repos/findOctave/cortex/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/findOctave/cortex/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/findOctave/cortex/git/refs{/sha}","trees_url":"https://api.github.com/repos/findOctave/cortex/git/trees{/sha}","statuses_url":"https://api.github.com/repos/findOctave/cortex/statuses/{sha}","languages_url":"https://api.github.com/repos/findOctave/cortex/languages","stargazers_url":"https://api.github.com/repos/findOctave/cortex/stargazers","contributors_url":"https://api.github.com/repos/findOctave/cortex/contributors","subscribers_url":"https://api.github.com/repos/findOctave/cortex/subscribers","subscription_url":"https://api.github.com/repos/findOctave/cortex/subscription","commits_url":"https://api.github.com/repos/findOctave/cortex/commits{/sha}","git_commits_url":"https://api.github.com/repos/findOctave/cortex/git/commits{/sha}","comments_url":"https://api.github.com/repos/findOctave/cortex/comments{/number}","issue_comment_url":"https://api.github.com/repos/findOctave/cortex/issues/comments{/number}","contents_url":"https://api.github.com/repos/findOctave/cortex/contents/{+path}","compare_url":"https://api.github.com/repos/findOctave/cortex/compare/{base}...{head}","merges_url":"https://api.github.com/repos/findOctave/cortex/merges","archive_url":"https://api.github.com/repos/findOctave/cortex/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/findOctave/cortex/downloads","issues_url":"https://api.github.com/repos/findOctave/cortex/issues{/number}","pulls_url":"https://api.github.com/repos/findOctave/cortex/pulls{/number}","milestones_url":"https://api.github.com/repos/findOctave/cortex/milestones{/number}","notifications_url":"https://api.github.com/repos/findOctave/cortex/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/findOctave/cortex/labels{/name}","releases_url":"https://api.github.com/repos/findOctave/cortex/releases{/id}","deployments_url":"https://api.github.com/repos/findOctave/cortex/deployments","created_at":"2018-06-06T19:40:44Z","updated_at":"2018-11-19T16:32:14Z","pushed_at":"2018-11-20T04:08:56Z","git_url":"git://github.com/findOctave/cortex.git","ssh_url":"git@github.com:findOctave/cortex.git","clone_url":"https://github.com/findOctave/cortex.git","svn_url":"https://github.com/findOctave/cortex","homepage":"https://octave-cortex-staging.herokuapp.com/","size":5586,"stargazers_count":1,"watchers_count":1,"language":"JavaScript","has_issues":true,"has_projects":true,"has_downloads":true,"has_wiki":true,"has_pages":false,"forks_count":5,"mirror_url":null,"archived":false,"open_issues_count":4,"license":{"key":"mit","name":"MIT License","spdx_id":"MIT","url":"https://api.github.com/licenses/mit","node_id":"MDc6TGljZW5zZTEz"},"forks":5,"open_issues":4,"watchers":1,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/findOctave/cortex/pulls/332"},"html":{"href":"https://github.com/findOctave/cortex/pull/332"},"issue":{"href":"https://api.github.com/repos/findOctave/cortex/issues/332"},"comments":{"href":"https://api.github.com/repos/findOctave/cortex/issues/332/comments"},"review_comments":{"href":"https://api.github.com/repos/findOctave/cortex/pulls/332/comments"},"review_comment":{"href":"https://api.github.com/repos/findOctave/cortex/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/findOctave/cortex/pulls/332/commits"},"statuses":{"href":"https://api.github.com/repos/findOctave/cortex/statuses/062421ffb35edcfeb590215145c74601d19346d3"}},"author_association":"CONTRIBUTOR"}}
{"actor":{"display_login":"craigspaeth"}}