What devices has a user signed up with?
Query
-- get device_registrations for a user
select device_registration_id, apid, device_token
from device_registrations
where user_id='01e47ed0-fdb4-11e4-9d12-005056a30036'
and active = true
;
Example Output
-[ RECORD 1 ]----------+-------------------------------------
device_registration_id | 6ee488c9-5ced-4802-9c08-3c21314e9959
apid | <optional-string>
device_token | <optional-string>
Note: apid is an android specific token needed for sending push notifications and device_token is an iOS specific string needed for sending push notifications. They’re stored on the same table and treated as a scala Either in keen.
What notifications did we send to a user?
Notifications are generated from alerts (stored in the alert table). When we send a push notification to the phone we record it in the sent_individual_alert_notifications table.
-- get sent individual alert notifications for a user
select *
from sent_individual_alert_notifications as sian
where sian.device_registration_id in (
select device_registration_id
from device_registrations
where user_id='583f0109-48d8-4fa7-9eea-45b1c8ac6f6f'
)
order by sent_at desc
limit 10
;
Example Output
-[ RECORD 10 ]---------+-------------------------------------
alert_id | c7a79fd1-a2bc-4b1d-963b-92de5cde6a4b
device_registration_id | fcd17f93-fd32-4e78-ad97-da6ae5662579
sent_at | 2015-06-09 09:53:57.341-05
opened_app_from | t