#12745

pending completion

Build # #12745

Build Type

push

circleci

Committed by

web-flow

Commit Message

Merge pull request #2889 from mozilla/MNTOR-1039-Unsubscribe-endpoint

Opt-out mechanism for emails

Run Details

282 of 1415 branches covered (19.93%)

Branch coverage included in aggregate %.

82 of 82 new or added lines in 9 files covered. (100.0%)

959 of 3901 relevant lines covered (24.58%)

2.05 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

0.0

/src/db/tables/subscribers.js

/* This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/. */

import { destroyOAuthToken } from '../../utils/fxa.js'
import Knex from 'knex'
import knexConfig from '../knexfile.js'
import AppConstants from '../../app-constants.js'
import mozlog from '../../utils/log.js'
const knex = Knex(knexConfig)
const { DELETE_UNVERIFIED_SUBSCRIBERS_TIMER } = AppConstants
const log = mozlog('DB.subscribers')

async function getSubscriberByToken (token) {
  const res = await knex('subscribers')
    .where('primary_verification_token', '=', token)

  return res[0]
}

async function getSubscriberByTokenAndHash (token, emailSha1) {
  const res = await knex.table('subscribers')
    .first()
    .where({
      primary_verification_token: token,
      primary_sha1: emailSha1
    })
  return res
}

async function getSubscribersByHashes (hashes) {
  return await knex('subscribers').whereIn('primary_sha1', hashes).andWhere('primary_verified', '=', true)
}

async function getSubscriberById (id) {
  const [subscriber] = await knex('subscribers').where({
    id
  })
  const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)
  return subscriberAndEmails
}

async function getSubscriberByFxaUid (uid) {
  const [subscriber] = await knex('subscribers').where({
    fxa_uid: uid
  })
  const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)
  return subscriberAndEmails
}

async function getSubscriberByEmail (email) {
  const [subscriber] = await knex('subscribers').where({
    primary_email: email,
    primary_verified: true
  })
  const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)
  return subscriberAndEmails
}
/**
 * Update fxa_refresh_token and fxa_profile_json for subscriber
 *
 * @param {object} subscriber knex object in DB
 * @param {string} fxaAccessToken from Firefox Account Oauth
 * @param {string} fxaRefreshToken from Firefox Account Oauth
 * @param {string} fxaProfileData from Firefox Account
 * @returns {object} updated subscriber knex object in DB
 */
async function updateFxAData (subscriber, fxaAccessToken, fxaRefreshToken, fxaProfileData) {
  const fxaUID = JSON.parse(fxaProfileData).uid
  const updated = await knex('subscribers')
    .where('id', '=', subscriber.id)
    .update({
      fxa_uid: fxaUID,
      fxa_access_token: fxaAccessToken,
      fxa_refresh_token: fxaRefreshToken,
      fxa_profile_json: fxaProfileData
    })
    .returning('*')
  const updatedSubscriber = Array.isArray(updated) ? updated[0] : null
  if (updatedSubscriber && subscriber.fxa_refresh_token) {
    destroyOAuthToken({ refresh_token: subscriber.fxa_refresh_token })
  }
  return updatedSubscriber
}

/**
 * Update fxa_profile_json for subscriber
 *
 * @param {object} subscriber knex object in DB
 * @param {string} fxaProfileData from Firefox Account
 * @returns {object} updated subscriber knex object in DB
 */
async function updateFxAProfileData (subscriber, fxaProfileData) {
  await knex('subscribers').where('id', subscriber.id)
    .update({
      fxa_profile_json: fxaProfileData
    })
  return getSubscriberById(subscriber.id)
}

/**
 * Remove fxa tokens and profile data for subscriber
 *
 * @param {object} subscriber knex object in DB
 * @returns {object} updated subscriber knex object in DB
 */
async function removeFxAData (subscriber) {
  log.debug('removeFxAData', subscriber)
  const updated = await knex('subscribers')
    .where('id', '=', subscriber.id)
    .update({
      fxa_access_token: null,
      fxa_refresh_token: null,
      fxa_profile_json: null
    })
    .returning('*')
  const updatedSubscriber = Array.isArray(updated) ? updated[0] : null
  if (updatedSubscriber && subscriber.fxa_refresh_token) {
    await destroyOAuthToken({ refresh_token: subscriber.fxa_refresh_token })
  }
  if (updatedSubscriber && subscriber.fxa_access_token) {
    await destroyOAuthToken({ token: subscriber.fxa_access_token })
  }
  return updatedSubscriber
}

async function setBreachesLastShownNow (subscriber) {
  // TODO: turn 2 db queries into a single query (also see #942)
  const nowDateTime = new Date()
  const nowTimeStamp = nowDateTime.toISOString()
  await knex('subscribers')
    .where('id', '=', subscriber.id)
    .update({
      breaches_last_shown: nowTimeStamp
    })
  return getSubscriberByEmail(subscriber.primary_email)
}

async function setAllEmailsToPrimary (subscriber, allEmailsToPrimary) {
  const updated = await knex('subscribers')
    .where('id', subscriber.id)
    .update({
      all_emails_to_primary: allEmailsToPrimary
    })
    .returning('*')
  const updatedSubscriber = Array.isArray(updated) ? updated[0] : null
  return updatedSubscriber
}

/**
 * OBSOLETE, preserved for backwards compatibility
 * TODO: Delete after monitor v2, only use setBreachResolution for v2
 *
 * @param {*} options {user, updatedResolvedBreaches}
 * @returns subscriber
 */
async function setBreachesResolved (options) {
  const { user, updatedResolvedBreaches } = options
  await knex('subscribers')
    .where('id', user.id)
    .update({
      breaches_resolved: updatedResolvedBreaches
    })
  return getSubscriberByEmail(user.primary_email)
}

/**
 * Set "breach_resolution" column with the latest breach resolution object
 * This column is meant to replace "breaches_resolved" column, which was used
 * for v1.
 *
 * @param {object} user user object that contains the id of a user
 * @param {object} updatedBreachesResolution {emailId: [{breachId: {isResolved: bool, resolutionsChecked: [BreachType]}}, {}...]}
 * @returns subscriber
 */
async function setBreachResolution (user, updatedBreachesResolution) {
  await knex('subscribers')
    .where('id', user.id)
    .update({
      breach_resolution: updatedBreachesResolution
    })
  return getSubscriberByEmail(user.primary_email)
}

async function setWaitlistsJoined (options) {
  const { user, updatedWaitlistsJoined } = options
  await knex('subscribers')
    .where('id', user.id)
    .update({
      waitlists_joined: updatedWaitlistsJoined
    })
  return getSubscriberByEmail(user.primary_email)
}

async function removeSubscriber (subscriber) {
  await knex('email_addresses').where({ subscriber_id: subscriber.id }).del()
  await knex('subscribers').where({ id: subscriber.id }).del()
}

async function removeSubscriberByToken (token, emailSha1) {
  const subscriber = await getSubscriberByTokenAndHash(token, emailSha1)
  if (!subscriber) {
    return false
  }
  await knex('subscribers')
    .where({
      primary_verification_token: subscriber.primary_verification_token,
      primary_sha1: subscriber.primary_sha1
    })
    .del()
  return subscriber
}

async function deleteUnverifiedSubscribers () {
  const expiredDateTime = new Date(Date.now() - DELETE_UNVERIFIED_SUBSCRIBERS_TIMER * 1000)
  const expiredTimeStamp = expiredDateTime.toISOString()
  const numDeleted = await knex('subscribers')
    .where('primary_verified', false)
    .andWhere('created_at', '<', expiredTimeStamp)
    .del()
  log.info('deleteUnverifiedSubscribers', { msg: `Deleted ${numDeleted} rows.` })
}

async function deleteSubscriberByFxAUID (fxaUID) {
  await knex('subscribers').where('fxa_uid', fxaUID).del()
}

async function updateBreachStats (id, stats) {
  await knex('subscribers')
    .where('id', id)
    .update({
      breach_stats: stats
    })
}

async function updateMonthlyEmailTimestamp (email) {
  const res = await knex('subscribers').update({ monthly_email_at: 'now' })
    .where('primary_email', email)
    .returning('monthly_email_at')

  return res
}

/**
 * Unsubscribe user from monthly unresolved breach emails
 *
 * @param {string} token User verification token
 */
async function updateMonthlyEmailOptout (token) {
  await knex('subscribers')
    .update('monthly_email_optout', true)
    .where('primary_verification_token', token)
}

function getSubscribersWithUnresolvedBreachesQuery () {
  return knex('subscribers')
    .whereRaw('monthly_email_optout IS NOT TRUE')
    .whereRaw("greatest(created_at, monthly_email_at) < (now() - interval '30 days')")
    .whereRaw("(breach_stats #>> '{numBreaches, numUnresolved}')::int > 0")
}

async function getSubscribersWithUnresolvedBreaches (limit = 0) {
  let query = getSubscribersWithUnresolvedBreachesQuery()
    .select('primary_email', 'primary_verification_token', 'breach_stats', 'signup_language')
  if (limit) {
    query = query.limit(limit).orderBy('created_at')
  }
  return await query
}

async function getSubscribersWithUnresolvedBreachesCount () {
  const query = getSubscribersWithUnresolvedBreachesQuery()
  const count = parseInt((await query.count({ count: '*' }))[0].count)
  return count
}

/** Private */

async function joinEmailAddressesToSubscriber (subscriber) {
  if (subscriber) {
    const emailAddressRecords = await knex('email_addresses').where({
      subscriber_id: subscriber.id
    })
    subscriber.email_addresses = emailAddressRecords.map(
      emailAddress => ({ id: emailAddress.id, email: emailAddress.email })
    )
  }
  return subscriber
}
export {
  getSubscriberByToken,
  getSubscribersByHashes,
  getSubscriberByTokenAndHash,
  getSubscriberById,
  getSubscriberByFxaUid,
  getSubscriberByEmail,
  getSubscribersWithUnresolvedBreachesQuery,
  getSubscribersWithUnresolvedBreaches,
  getSubscribersWithUnresolvedBreachesCount,
  updateFxAData,
  removeFxAData,
  updateFxAProfileData,
  setBreachesLastShownNow,
  setAllEmailsToPrimary,
  setBreachesResolved,
  setBreachResolution,
  setWaitlistsJoined,
  updateBreachStats,
  updateMonthlyEmailTimestamp,
  updateMonthlyEmailOptout,
  removeSubscriber,
  removeSubscriberByToken,
  deleteUnverifiedSubscribers,
  deleteSubscriberByFxAUID
}

1	/* This Source Code Form is subject to the terms of the Mozilla Public
2	* License, v. 2.0. If a copy of the MPL was not distributed with this
3	* file, You can obtain one at http://mozilla.org/MPL/2.0/. */
4
5	import { destroyOAuthToken } from '../../utils/fxa.js'
6	import Knex from 'knex'
7	import knexConfig from '../knexfile.js'
8	import AppConstants from '../../app-constants.js'
9	import mozlog from '../../utils/log.js'
10	const knex = Knex(knexConfig)	×
11	const { DELETE_UNVERIFIED_SUBSCRIBERS_TIMER } = AppConstants	×
12	const log = mozlog('DB.subscribers')	×
13
14	async function getSubscriberByToken (token) {
15	const res = await knex('subscribers')	×
16	.where('primary_verification_token', '=', token)
17
18	return res[0]	×
19	}
20
21	async function getSubscriberByTokenAndHash (token, emailSha1) {
22	const res = await knex.table('subscribers')	×
23	.first()
24	.where({
25	primary_verification_token: token,
26	primary_sha1: emailSha1
27	})
28	return res	×
29	}
30
31	async function getSubscribersByHashes (hashes) {
32	return await knex('subscribers').whereIn('primary_sha1', hashes).andWhere('primary_verified', '=', true)	×
33	}
34
35	async function getSubscriberById (id) {
36	const [subscriber] = await knex('subscribers').where({	×
37	id
38	})
39	const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)	×
40	return subscriberAndEmails	×
41	}
42
43	async function getSubscriberByFxaUid (uid) {
44	const [subscriber] = await knex('subscribers').where({	×
45	fxa_uid: uid
46	})
47	const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)	×
48	return subscriberAndEmails	×
49	}
50
51	async function getSubscriberByEmail (email) {
52	const [subscriber] = await knex('subscribers').where({	×
53	primary_email: email,
54	primary_verified: true
55	})
56	const subscriberAndEmails = await joinEmailAddressesToSubscriber(subscriber)	×
57	return subscriberAndEmails	×
58	}
59	/**
60	* Update fxa_refresh_token and fxa_profile_json for subscriber
61	*
62	* @param {object} subscriber knex object in DB
63	* @param {string} fxaAccessToken from Firefox Account Oauth
64	* @param {string} fxaRefreshToken from Firefox Account Oauth
65	* @param {string} fxaProfileData from Firefox Account
66	* @returns {object} updated subscriber knex object in DB
67	*/
68	async function updateFxAData (subscriber, fxaAccessToken, fxaRefreshToken, fxaProfileData) {
69	const fxaUID = JSON.parse(fxaProfileData).uid	×
70	const updated = await knex('subscribers')	×
71	.where('id', '=', subscriber.id)
72	.update({
73	fxa_uid: fxaUID,
74	fxa_access_token: fxaAccessToken,
75	fxa_refresh_token: fxaRefreshToken,
76	fxa_profile_json: fxaProfileData
77	})
78	.returning('*')
79	const updatedSubscriber = Array.isArray(updated) ? updated[0] : null	×
80	if (updatedSubscriber && subscriber.fxa_refresh_token) {	×
81	destroyOAuthToken({ refresh_token: subscriber.fxa_refresh_token })	×
82	}
83	return updatedSubscriber	×
84	}
85
86	/**
87	* Update fxa_profile_json for subscriber
88	*
89	* @param {object} subscriber knex object in DB
90	* @param {string} fxaProfileData from Firefox Account
91	* @returns {object} updated subscriber knex object in DB
92	*/
93	async function updateFxAProfileData (subscriber, fxaProfileData) {
94	await knex('subscribers').where('id', subscriber.id)	×
95	.update({
96	fxa_profile_json: fxaProfileData
97	})
98	return getSubscriberById(subscriber.id)	×
99	}
100
101	/**
102	* Remove fxa tokens and profile data for subscriber
103	*
104	* @param {object} subscriber knex object in DB
105	* @returns {object} updated subscriber knex object in DB
106	*/
107	async function removeFxAData (subscriber) {
108	log.debug('removeFxAData', subscriber)	×
109	const updated = await knex('subscribers')	×
110	.where('id', '=', subscriber.id)
111	.update({
112	fxa_access_token: null,
113	fxa_refresh_token: null,
114	fxa_profile_json: null
115	})
116	.returning('*')
117	const updatedSubscriber = Array.isArray(updated) ? updated[0] : null	×
118	if (updatedSubscriber && subscriber.fxa_refresh_token) {	×
119	await destroyOAuthToken({ refresh_token: subscriber.fxa_refresh_token })	×
120	}
121	if (updatedSubscriber && subscriber.fxa_access_token) {	×
122	await destroyOAuthToken({ token: subscriber.fxa_access_token })	×
123	}
124	return updatedSubscriber	×
125	}
126
127	async function setBreachesLastShownNow (subscriber) {
128	// TODO: turn 2 db queries into a single query (also see #942)
129	const nowDateTime = new Date()	×
130	const nowTimeStamp = nowDateTime.toISOString()	×
131	await knex('subscribers')	×
132	.where('id', '=', subscriber.id)
133	.update({
134	breaches_last_shown: nowTimeStamp
135	})
136	return getSubscriberByEmail(subscriber.primary_email)	×
137	}
138
139	async function setAllEmailsToPrimary (subscriber, allEmailsToPrimary) {
140	const updated = await knex('subscribers')	×
141	.where('id', subscriber.id)
142	.update({
143	all_emails_to_primary: allEmailsToPrimary
144	})
145	.returning('*')
146	const updatedSubscriber = Array.isArray(updated) ? updated[0] : null	×
147	return updatedSubscriber	×
148	}
149
150	/**
151	* OBSOLETE, preserved for backwards compatibility
152	* TODO: Delete after monitor v2, only use setBreachResolution for v2
153	*
154	* @param {*} options {user, updatedResolvedBreaches}
155	* @returns subscriber
156	*/
157	async function setBreachesResolved (options) {
158	const { user, updatedResolvedBreaches } = options	×
159	await knex('subscribers')	×
160	.where('id', user.id)
161	.update({
162	breaches_resolved: updatedResolvedBreaches
163	})
164	return getSubscriberByEmail(user.primary_email)	×
165	}
166
167	/**
168	* Set "breach_resolution" column with the latest breach resolution object
169	* This column is meant to replace "breaches_resolved" column, which was used
170	* for v1.
171	*
172	* @param {object} user user object that contains the id of a user
173	* @param {object} updatedBreachesResolution {emailId: [{breachId: {isResolved: bool, resolutionsChecked: [BreachType]}}, {}...]}
174	* @returns subscriber
175	*/
176	async function setBreachResolution (user, updatedBreachesResolution) {
177	await knex('subscribers')	×
178	.where('id', user.id)
179	.update({
180	breach_resolution: updatedBreachesResolution
181	})
182	return getSubscriberByEmail(user.primary_email)	×
183	}
184
185	async function setWaitlistsJoined (options) {
186	const { user, updatedWaitlistsJoined } = options	×
187	await knex('subscribers')	×
188	.where('id', user.id)
189	.update({
190	waitlists_joined: updatedWaitlistsJoined
191	})
192	return getSubscriberByEmail(user.primary_email)	×
193	}
194
195	async function removeSubscriber (subscriber) {
196	await knex('email_addresses').where({ subscriber_id: subscriber.id }).del()	×
197	await knex('subscribers').where({ id: subscriber.id }).del()	×
198	}
199
200	async function removeSubscriberByToken (token, emailSha1) {
201	const subscriber = await getSubscriberByTokenAndHash(token, emailSha1)	×
202	if (!subscriber) {	×
203	return false	×
204	}
205	await knex('subscribers')	×
206	.where({
207	primary_verification_token: subscriber.primary_verification_token,
208	primary_sha1: subscriber.primary_sha1
209	})
210	.del()
211	return subscriber	×
212	}
213
214	async function deleteUnverifiedSubscribers () {
215	const expiredDateTime = new Date(Date.now() - DELETE_UNVERIFIED_SUBSCRIBERS_TIMER * 1000)	×
216	const expiredTimeStamp = expiredDateTime.toISOString()	×
217	const numDeleted = await knex('subscribers')	×
218	.where('primary_verified', false)
219	.andWhere('created_at', '<', expiredTimeStamp)
220	.del()
221	log.info('deleteUnverifiedSubscribers', { msg: `Deleted ${numDeleted} rows.` })	×
222	}
223
224	async function deleteSubscriberByFxAUID (fxaUID) {
225	await knex('subscribers').where('fxa_uid', fxaUID).del()	×
226	}
227
228	async function updateBreachStats (id, stats) {
229	await knex('subscribers')	×
230	.where('id', id)
231	.update({
232	breach_stats: stats
233	})
234	}
235
236	async function updateMonthlyEmailTimestamp (email) {
237	const res = await knex('subscribers').update({ monthly_email_at: 'now' })	×
238	.where('primary_email', email)
239	.returning('monthly_email_at')
240
241	return res	×
242	}
243
244	/**
245	* Unsubscribe user from monthly unresolved breach emails
246	*
247	* @param {string} token User verification token
248	*/
249	async function updateMonthlyEmailOptout (token) {
250	await knex('subscribers')	×
251	.update('monthly_email_optout', true)
252	.where('primary_verification_token', token)
253	}
254
255	function getSubscribersWithUnresolvedBreachesQuery () {
256	return knex('subscribers')	×
257	.whereRaw('monthly_email_optout IS NOT TRUE')
258	.whereRaw("greatest(created_at, monthly_email_at) < (now() - interval '30 days')")
259	.whereRaw("(breach_stats #>> '{numBreaches, numUnresolved}')::int > 0")
260	}
261
262	async function getSubscribersWithUnresolvedBreaches (limit = 0) {	×
263	let query = getSubscribersWithUnresolvedBreachesQuery()	×
264	.select('primary_email', 'primary_verification_token', 'breach_stats', 'signup_language')
265	if (limit) {	×
266	query = query.limit(limit).orderBy('created_at')	×
267	}
268	return await query	×
269	}
270
271	async function getSubscribersWithUnresolvedBreachesCount () {
272	const query = getSubscribersWithUnresolvedBreachesQuery()	×
273	const count = parseInt((await query.count({ count: '*' }))[0].count)	×
274	return count	×
275	}
276
277	/** Private */
278
279	async function joinEmailAddressesToSubscriber (subscriber) {
280	if (subscriber) {	×
281	const emailAddressRecords = await knex('email_addresses').where({	×
282	subscriber_id: subscriber.id
283	})
284	subscriber.email_addresses = emailAddressRecords.map(	×
285	emailAddress => ({ id: emailAddress.id, email: emailAddress.email })	×
286	)
287	}
288	return subscriber	×
289	}
290	export {
291	getSubscriberByToken,
292	getSubscribersByHashes,
293	getSubscriberByTokenAndHash,
294	getSubscriberById,
295	getSubscriberByFxaUid,
296	getSubscriberByEmail,
297	getSubscribersWithUnresolvedBreachesQuery,
298	getSubscribersWithUnresolvedBreaches,
299	getSubscribersWithUnresolvedBreachesCount,
300	updateFxAData,
301	removeFxAData,
302	updateFxAProfileData,
303	setBreachesLastShownNow,
304	setAllEmailsToPrimary,
305	setBreachesResolved,
306	setBreachResolution,
307	setWaitlistsJoined,
308	updateBreachStats,
309	updateMonthlyEmailTimestamp,
310	updateMonthlyEmailOptout,
311	removeSubscriber,
312	removeSubscriberByToken,
313	deleteUnverifiedSubscribers,
314	deleteSubscriberByFxAUID
315	}

mozilla / blurts-server / #12745

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous