ed3c7e2b-537c-42f1-84f1-96da176a3368

Build # ed3c7e2b-537c-42f1-84f1-96da176a3368

Build Type

push

circleci

Committed by GitHub

Commit Message

Merge pull request #2935 from mozilla/MNTOR-1180

Run Details

282 of 1601 branches covered (17.61%)

Branch coverage included in aggregate %.

7 of 7 new or added lines in 2 files covered. (100.0%)

959 of 4316 relevant lines covered (22.22%)

3.7 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 deleteResolutionsWithEmail (id, email) {
  const [subscriber] = await knex('subscribers').where({
    id
  })
  const { breach_resolution: breachResolution } = subscriber
  // if email exists in breach resolution, remove it
  if (breachResolution[email]) {
    delete breachResolution[email]
  }

  return await setBreachResolution(subscriber, breachResolution)
}

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,
  deleteResolutionsWithEmail
}

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 deleteResolutionsWithEmail (id, email) {
229	const [subscriber] = await knex('subscribers').where({	×
230	id
231	})
232	const { breach_resolution: breachResolution } = subscriber	×
233	// if email exists in breach resolution, remove it
234	if (breachResolution[email]) {	×
235	delete breachResolution[email]	×
236	}
237
238	return await setBreachResolution(subscriber, breachResolution)	×
239	}
240
241	async function updateBreachStats (id, stats) {
242	await knex('subscribers')	×
243	.where('id', id)
244	.update({
245	breach_stats: stats
246	})
247	}
248
249	async function updateMonthlyEmailTimestamp (email) {
250	const res = await knex('subscribers').update({ monthly_email_at: 'now' })	×
251	.where('primary_email', email)
252	.returning('monthly_email_at')
253
254	return res	×
255	}
256
257	/**
258	* Unsubscribe user from monthly unresolved breach emails
259	*
260	* @param {string} token User verification token
261	*/
262	async function updateMonthlyEmailOptout (token) {
263	await knex('subscribers')	×
264	.update('monthly_email_optout', true)
265	.where('primary_verification_token', token)
266	}
267
268	function getSubscribersWithUnresolvedBreachesQuery () {
269	return knex('subscribers')	×
270	.whereRaw('monthly_email_optout IS NOT TRUE')
271	.whereRaw("greatest(created_at, monthly_email_at) < (now() - interval '30 days')")
272	.whereRaw("(breach_stats #>> '{numBreaches, numUnresolved}')::int > 0")
273	}
274
275	async function getSubscribersWithUnresolvedBreaches (limit = 0) {	×
276	let query = getSubscribersWithUnresolvedBreachesQuery()	×
277	.select('primary_email', 'primary_verification_token', 'breach_stats', 'signup_language')
278	if (limit) {	×
279	query = query.limit(limit).orderBy('created_at')	×
280	}
281	return await query	×
282	}
283
284	async function getSubscribersWithUnresolvedBreachesCount () {
285	const query = getSubscribersWithUnresolvedBreachesQuery()	×
286	const count = parseInt((await query.count({ count: '*' }))[0].count)	×
287	return count	×
288	}
289
290	/** Private */
291
292	async function joinEmailAddressesToSubscriber (subscriber) {
293	if (subscriber) {	×
294	const emailAddressRecords = await knex('email_addresses').where({	×
295	subscriber_id: subscriber.id
296	})
297	subscriber.email_addresses = emailAddressRecords.map(	×
298	emailAddress => ({ id: emailAddress.id, email: emailAddress.email })	×
299	)
300	}
301	return subscriber	×
302	}
303	export {
304	getSubscriberByToken,
305	getSubscribersByHashes,
306	getSubscriberByTokenAndHash,
307	getSubscriberById,
308	getSubscriberByFxaUid,
309	getSubscriberByEmail,
310	getSubscribersWithUnresolvedBreachesQuery,
311	getSubscribersWithUnresolvedBreaches,
312	getSubscribersWithUnresolvedBreachesCount,
313	updateFxAData,
314	removeFxAData,
315	updateFxAProfileData,
316	setBreachesLastShownNow,
317	setAllEmailsToPrimary,
318	setBreachesResolved,
319	setBreachResolution,
320	setWaitlistsJoined,
321	updateBreachStats,
322	updateMonthlyEmailTimestamp,
323	updateMonthlyEmailOptout,
324	removeSubscriber,
325	removeSubscriberByToken,
326	deleteUnverifiedSubscribers,
327	deleteSubscriberByFxAUID,
328	deleteResolutionsWithEmail
329	}

mozilla / blurts-server / ed3c7e2b-537c-42f1-84f1-96da176a3368

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