def signup(token):
"""Validates the given token, and then adds them to the users table if
validation succeeds. Returns the INTERNAL user id on success. Otherwise, None."""
info = validate_token(token)
if info is None:
return None
with engine().begin() as con:
email = info['email'] if info['email_verified'] else None
query = select([users.c.id]).where(
and_(users.c.external_id == info['sub'],
users.c.external_type == 'GOOGLE'))
res = con.execute(query)
if res.rowcount >= 1:
row = res.fetchone()
return row[0]
else:
res = con.execute(users.insert().values( # pylint: disable=no-value-for-parameter
external_id=info['sub'],
external_type='GOOGLE'))
uid = res.inserted_primary_key[0]
if email is not None:
con.execute(confirmed_emails.insert().values( # pylint: disable=no-value-for-parameter
user_id=uid,
email=email))
return uid
def list_flags(uid):
"""Receives a list of ids as JSON in the message body. Returns a list of flags (True/False) for each id."""
try:
ids = request.get_json()
if not isinstance(ids, list):
raise ValueError()
except ValueError:
abort_json(400)
if len(ids) == 0:
return {'flags': []}
with engine().begin() as con:
uflags = select([flags_.c.id, flags_.c.lead_id])\
.where(flags_.c.user_id == uid).alias('uflags')
query = select([leads.c.id, text('not isnull(uflags.id) as flagged')])\
.select_from(leads.outerjoin(uflags))\
.where(leads.c.id.in_(ids))
res = con.execute(query)
result = {row['id']: row['flagged'] for row in res}
return {'flags': [result[id] if id in result else False for id in ids]}
def delete_alert(uid, alert_id):
with engine().begin() as con:
query = alerts_.delete().where( # pylint: disable=no-value-for-parameter
and_(alerts_.c.id == alert_id, alerts_.c.user_id == uid))
res = con.execute(query)
if res.rowcount == 0:
return abort_json(404)
return {'status': 'ok'}
def put_flag(uid, lead_id):
with engine().begin() as con:
query = flags_.insert().values( # pylint: disable=no-value-for-parameter
lead_id=lead_id, user_id=uid)
try:
res = con.execute(query)
return {'status': 'ok', 'rows': res.rowcount}
except IntegrityError:
# invalid lead_id
return abort_json(404)
def lookup_alert(uid, alert_id):
with engine().begin() as con:
query = select([alerts_])\
.where(and_(alerts_.c.id == alert_id, alerts_.c.user_id == uid))
res = con.execute(query)
if res.rowcount == 0:
# does not exist or no access
return abort_json(404)
response = format_alert(res.fetchone())
response['confirmed'] = is_confirmed(uid, response['recipient'], con)
return flask.jsonify(response)
def get_lead(uid, lead_id):
with engine().begin() as con:
query = build_lead_selection(uid, where=[leads.c.id == lead_id])
resultset = con.execute(query)
result = resultset.fetchone()
if result is not None:
result = dict(result)
# now we load comments for it
ratings_query = select([crowd_ratings])\
.where(crowd_ratings.c.lead_id == lead_id)
ratings = con.execute(ratings_query)
result['ratings'] = [dict(row) for row in ratings.fetchall()]
return flask.jsonify(result)
else:
return abort_json(404, 'no such id')
def list_alerts(uid):
with engine().begin() as con:
query = select([alerts_])\
.where(alerts_.c.user_id == uid)
res = con.execute(query)
if res.rowcount == 0:
return {'alerts': []}
results = res.fetchall()
alert_list = [format_alert(r) for r in results]
confirmed = is_confirmed(uid, [alert['recipient']
for alert in alert_list], con)
for alert in alert_list:
alert['confirmed'] = confirmed[alert['recipient']]
return {'alerts': alert_list}
def create_alert(uid):
try:
data = request.get_json()
assert EMAIL_REGEX.fullmatch(data['recipient']) is not None
except AssertionError:
return abort_json(400, 'Unable to read or validate alert data')
with engine().begin() as con:
# before beginning, check if this email belongs to another existing users
is_taken = email_taken(uid, data['recipient'], con)
if is_taken:
return is_taken
# we're going to abuse the DB to do validation & type checking. the only exception is the email, which is validated above
try:
query = alerts_.insert().values( # pylint: disable=no-value-for-parameter
filter=data['filter'],
recipient=data['recipient'],
federal_source=data['sources'].get('federal', None),
regional_source=data['sources'].get('regional', None),
local_source=data['sources'].get('local', None),
frequency=data['frequency'],
user_id=uid
)
res = con.execute(query)
assert len(res.inserted_primary_key) > 0
alert_id = res.inserted_primary_key[0]
except Exception as e:
print(e)
return abort_json(400, 'Unable to create alert in database')
base = {'id': alert_id}
try:
conf_sent = send_confirmation(uid, data['recipient'], con)
except ConfirmationPendingError:
return {'notes': ['A confirmation for this recipient is already pending.'], **base}
else:
if conf_sent:
return {'status': 'ok', 'notes': [CONFIRMATION_NOTE], **base}
else:
return {'status': 'ok', **base}
def resend_confirmation(alert_id, uid):
with engine().begin() as con:
query = select([alerts_]).where(
and_(alerts_.c.id == alert_id, alerts_.c.user_id == uid))
res = con.execute(query)
if res.rowcount == 0:
return abort_json(400, 'This email address is already confirmed.')
alert = res.fetchone()
try:
send_confirmation(
uid, alert['recipient'], con, min_delay=timedelta(minutes=5))
except ConfirmationPendingError as err:
return abort_json(400, err.message)
else:
return {'status': 'ok'}
def delete_alert_via_link():
token = request.args.get('token', None)
if token is None:
return abort_json(400, 'Missing token')
try:
contents = read_private_alert_token(token)
with engine().begin() as con:
send_query = sent_alerts.select().where(and_(sent_alerts.c.id == contents['send'], sent_alerts.c.user_id == contents['user']))
res = con.execute(send_query)
sent = res.fetchone()
# test (sqlite) database doesn't support CTEs on DELETEs so rather
# than have this untested we're just going to use 2 queries. this
# endpoint shouldn't be hit often so this cost should be low
query = alerts_.delete().where(alerts_.c.id == sent['alert_id'])
res = con.execute(query)
if res.rowcount == 0:
return abort_json(404, 'No such alert')
except BadSignature:
return abort_json(400, 'Invalid token')
return {'status': 'ok'}
def confirm_email():
token = request.args.get('token')
serializer = URLSafeTimedSerializer(current_app.secret_key)
try:
# tokens only work for 24hr
confirmation_id = serializer.loads(token,
max_age=24 * 60 * 60,
salt='confirm')
with engine().begin() as con:
res = con.execute(
select([
pending_confirmations
]).where(pending_confirmations.c.id == confirmation_id))
if res.rowcount == 0:
raise NoSuchConfirmation(
'No pending confirmation for that address')
confirmation = res.fetchone()
con.execute(confirmed_emails.insert().values( # pylint: disable=no-value-for-parameter
user_id=confirmation['user_id'],
email=confirmation['email']))
con.execute(pending_confirmations.delete().where( # pylint: disable=no-value-for-parameter
and_(
pending_confirmations.c.user_id == confirmation['user_id'],
pending_confirmations.c.email == confirmation['email'])))
return {'status': 'ok'}
except NoSuchConfirmation as e:
return abort_json(400, e.message)
except BadSignature as e:
print(e)
return abort_json(
400,
'That token is invalid or has expired. You can request another confirmation email on the Alerts page.'
)
def unsubscribe_all_alerts():
token = request.args.get('token', None)
if token is None:
return abort_json(400, 'Missing token')
try:
contents = read_private_alert_token(token)
with engine().begin() as con:
send_query = sent_alerts.select().where(and_(sent_alerts.c.id == contents['send'], sent_alerts.c.user_id == contents['user']))
res = con.execute(send_query)
sent = res.fetchone()
# see note in delete_alert_via_link
query = confirmed_emails.delete().where(confirmed_emails.c.email == sent['recipient'])
res = con.execute(query)
query = alerts_.delete().where(alerts_.c.recipient == sent['recipient'])
res = con.execute(query)
if res.rowcount == 0:
return abort_json(404, 'No such alert')
except BadSignature:
return abort_json(400, 'Invalid token')
return {'status': 'ok'}
def update_alert(uid, alert_id):
try:
data = request.get_json()
assert EMAIL_REGEX.fullmatch(data['recipient']) is not None
except AssertionError:
return abort_json(400, 'Unable to read or validate alert data')
with engine().begin() as con:
is_taken = email_taken(uid, data['recipient'], con)
if is_taken:
return is_taken
query = alerts_.update().values( # pylint: disable=no-value-for-parameter
filter=data['filter'],
recipient=data['recipient'],
federal_source=data['sources'].get('federal', None),
regional_source=data['sources'].get('regional', None),
local_source=data['sources'].get('local', None),
frequency=data['frequency']
).where(and_(alerts_.c.id == alert_id, alerts_.c.user_id == uid))
res = con.execute(query)
if res.rowcount == 0:
return abort_json(404)
try:
conf_sent = send_confirmation(uid, data['recipient'], con)
except ConfirmationPendingError:
# confirmation email already pending
return {'status': 'ok', 'notes': ['A confirmation email is pending.']}
if conf_sent:
return {'status': 'ok', 'notes': [CONFIRMATION_NOTE]}
else:
return {'status': 'ok'}
def delete_flag(uid, lead_id):
with engine().begin() as con:
query = flags_.delete().where( # pylint: disable=no-value-for-parameter
and_(flags_.c.lead_id == lead_id, flags_.c.user_id == uid))
res = con.execute(query)
return {'status': 'ok', 'rows': res.rowcount}
def filter_leads(uid, flagged=False):
"""Queries should have the form /api/leads?filter=...&from=...&to=...&source=...&page=n where:
- filter defines the keyword(s) to use to search
- from / to are start / end dates to search within
- federal / regional / local define source filters which are matched on equality. "exclude" is a special value that indicates they should not be included.
- page is a number from 1 to ...
"""
filter_ = request.args.get('filter', None)
from_ = request.args.get('from', None)
to = request.args.get('to', None)
page = request.args.get('page', 1, int)
query = build_filtered_lead_selection(filter_,
from_,
to,
request.args,
page,
uid,
flagged_only=flagged)
with engine().begin() as con:
result = con.execute(query)
results = list(result.fetchall())
res_map = {
res['id']: {
'ratings': [],
**dict(res.items())
}
for res in results
}
if len(results) == 0:
# no need to do more queries. return empty result
return flask.jsonify({
'num_pages': 0,
'num_results': 0,
'page': 1,
'leads': []
})
# count total results so we know the page count
count_query = build_filtered_lead_selection(
filter_,
from_,
to,
request.args,
page=None,
uid=uid,
fields=[text('count(*) as num_results')],
flagged_only=flagged)
result = con.execute(count_query)
meta = dict(result.fetchone().items())
print(meta)
meta['num_pages'] = ceil(meta['num_results'] / PAGE_SIZE)
meta['page'] = page
if 'flagged' in meta:
del meta['flagged']
ratings_query = select([crowd_ratings]).where(
crowd_ratings.c.lead_id.in_(tuple(res_map.keys())))
ratings = con.execute(ratings_query)
for rating in ratings:
lead = res_map[rating['lead_id']]
lead['ratings'].append(dict(rating.items()))
result = {'leads': list(res_map.values()), **meta}
return flask.jsonify(result)
def trigger_alerts():
from api.api import build_filtered_lead_selection
if request.remote_addr not in current_app.config['ALERT_TRIGGER_WHITELIST']:
return abort_json(401, 'Unauthorized')
freq = request.args.get('frequency', None)
if freq is not None and freq not in FREQS:
return abort_json(400, 'Invalid frequency specified.')
with engine().connect() as con:
# select all alerts where:
# 1. the recipient email is confirmed
# 2. the alert hasn't been sent in the current time period
confirmed = select(
[confirmed_emails.c.user_id, confirmed_emails.c.email]).cte()
where = tuple_(alerts_.c.user_id, alerts_.c.recipient).in_(confirmed)
if freq is not None:
where = and_(where, alerts_.c.frequency == FREQS[freq])
query = select([alerts_, func.max(sent_alerts.c.send_date).label('last_sent')])\
.select_from(alerts_.outerjoin(sent_alerts, sent_alerts.c.alert_id == alerts_.c.id))\
.where(where)\
.group_by(alerts_.c.id)
results = con.execute(query)
# these results satisfy #1, but not #2 yet
# however, we need to go row by row anyway because MySQL cannot match
# on column values (only plaintext)
for result in results:
row = dict(result)
if row['last_sent'] is not None and row['last_sent'] >= min_date_threshold(row['frequency']):
# has been sent more recently than we allow
print(
f"Last trigger for {row['id']} is too recent ({row['last_sent']}, {min_date_threshold(row['frequency'])})")
continue
with con.begin():
query = build_filtered_lead_selection(
filter_=row['filter'],
from_=None,
to=None,
sources={
key: row[f"{key}_source"]
for key in ['federal', 'regional', 'local']
},
page=None,
fields=[leads.c.id, annotated_leads.c.name],
where=[
annotated_leads.c.published_dt >= min_date_threshold(
row['frequency'], fudge=timedelta(0))
]
)
lead_results = con.execute(query)
lead_results = list(dict(row) for row in lead_results)
if len(lead_results) == 0:
print(f"Skipping alert {row['id']}. No new results.")
continue
# record alert sending
sent_alert = {
k: v
for k, v in row.items()
if k not in ['id', 'last_sent']
}
sent_alert['alert_id'] = row['id']
sent_alert['send_date'] = datetime.now()
sent_alert['db_link'] = build_db_url(sent_alert)
query = sent_alerts.insert().values( # pylint: disable=no-value-for-parameter
**sent_alert)
res = con.execute(query)
send_id = res.inserted_primary_key[0]
sent_alert['send_id'] = send_id
templates = render_alert(sent_alert, [{
'name': lead['name'],
'link': f'{BASE_URL}/lead/{lead["id"]}'
} for lead in islice(lead_results, None)])
sent_contents = [
{'send_id': send_id,
'lead_id': lead['id']}
for lead in lead_results
]
con.execute(sent_alert_contents.insert( # pylint: disable=no-value-for-parameter
), *sent_contents)
send_alert(sent_alert, *templates)
return {'status': 'ok'}
