internal/store/push.go

  1package store
  2
  3import (
  4	"database/sql"
  5	"errors"
  6	"time"
  7)
  8
  9// PushDevice is one Apple device an account has registered. Token is the
 10// APNs device token: an address, not a credential, but device-identifying
 11// and never logged or echoed in full.
 12type PushDevice struct {
 13	ID         int64
 14	UserID     int64
 15	Token      string
 16	Label      string
 17	CreatedAt  string
 18	LastSeenAt string
 19}
 20
 21// AddPushDevice registers a token to an account. A token already present
 22// changes hands rather than erroring: Apple reuses tokens, and a reinstall
 23// hands the same one to whichever account signs in next. The id is read
 24// back by token rather than taken from LastInsertId, which SQLite leaves
 25// unchanged when the DO UPDATE arm fires instead of the INSERT.
 26//
 27// The row id survives that handover, so queue rows written for the
 28// previous owner would still be delivered to the device — and an alert
 29// carries the repository name and item number in full. Undelivered rows
 30// go with the ownership, in the same transaction; sent and dead-lettered
 31// rows are history and stay.
 32func (s *Store) AddPushDevice(userID int64, token, label string) (int64, error) {
 33	tx, err := s.DB.Begin()
 34	if err != nil {
 35		return 0, err
 36	}
 37	defer tx.Rollback()
 38	var prev int64
 39	if err := tx.QueryRow("SELECT user_id FROM push_devices WHERE token = ?", token).Scan(&prev); err != nil && !errors.Is(err, sql.ErrNoRows) {
 40		return 0, err
 41	}
 42	if _, err := tx.Exec(`
 43		INSERT INTO push_devices (user_id, token, label) VALUES (?, ?, ?)
 44		ON CONFLICT(token) DO UPDATE SET user_id = excluded.user_id, label = excluded.label`,
 45		userID, token, label); err != nil {
 46		return 0, err
 47	}
 48	var id int64
 49	if err := tx.QueryRow("SELECT id FROM push_devices WHERE token = ?", token).Scan(&id); err != nil {
 50		return 0, err
 51	}
 52	if prev != 0 && prev != userID {
 53		if _, err := tx.Exec(
 54			"DELETE FROM push_queue WHERE device_id = ? AND sent_at IS NULL AND failed_at IS NULL", id); err != nil {
 55			return 0, err
 56		}
 57	}
 58	return id, tx.Commit()
 59}
 60
 61func (s *Store) PushDevices(userID int64) ([]PushDevice, error) {
 62	rows, err := s.DB.Query(`
 63		SELECT id, user_id, token, label, created_at, COALESCE(last_seen_at, '')
 64		FROM push_devices WHERE user_id = ? ORDER BY id`, userID)
 65	if err != nil {
 66		return nil, err
 67	}
 68	defer rows.Close()
 69	var out []PushDevice
 70	for rows.Next() {
 71		var d PushDevice
 72		if err := rows.Scan(&d.ID, &d.UserID, &d.Token, &d.Label, &d.CreatedAt, &d.LastSeenAt); err != nil {
 73			return nil, err
 74		}
 75		out = append(out, d)
 76	}
 77	return out, rows.Err()
 78}
 79
 80// RemovePushDevice deletes one of the account's own devices. Scoping the
 81// delete by user_id rather than checking ownership first means another
 82// account's id is ErrNotFound, which is the same answer as an id that
 83// never existed — a caller learns nothing about other accounts' devices.
 84func (s *Store) RemovePushDevice(userID, id int64) error {
 85	res, err := s.DB.Exec("DELETE FROM push_devices WHERE id = ? AND user_id = ?", id, userID)
 86	if err != nil {
 87		return err
 88	}
 89	n, err := res.RowsAffected()
 90	if err != nil {
 91		return err
 92	}
 93	if n == 0 {
 94		return ErrNotFound
 95	}
 96	return nil
 97}
 98
 99func (s *Store) PushEnabled(userID int64) (bool, error) {
100	var on int
101	err := s.DB.QueryRow("SELECT notify_push FROM users WHERE id = ?", userID).Scan(&on)
102	if errors.Is(err, sql.ErrNoRows) {
103		return false, ErrNotFound
104	}
105	return on != 0, err
106}
107
108func (s *Store) SetPushEnabled(userID int64, on bool) error {
109	v := 0
110	if on {
111		v = 1
112	}
113	_, err := s.DB.Exec("UPDATE users SET notify_push = ? WHERE id = ?", v, userID)
114	return err
115}
116
117// QueuedPush is one pending push, joined to the token it is bound for so
118// the drainer needs one query rather than two.
119type QueuedPush struct {
120	ID       int64
121	DeviceID int64
122	Token    string
123	// Username is the recipient. One device token is one install, and an
124	// install registers against every account signed in on it, so the
125	// alert has to name which of them it is for.
126	Username string
127	Title    string
128	Body     string
129	Path     string
130	Attempts int
131	// Badge is the recipient's unread inbox count, for the alert's badge.
132	// Counted here rather than at enqueue so a cleared inbox is reflected.
133	Badge int
134}
135
136// EnqueuePush writes one row per registered device, and nothing when the
137// account has push off or no devices — the same shape as
138// ActivityMailAddress returning "" when notify_mail is off. Mute, watch
139// and actor-exclusion are already settled by NotifyRecipients before a
140// caller reaches here.
141func (s *Store) EnqueuePush(userID int64, title, body, path string) error {
142	on, err := s.PushEnabled(userID)
143	if err != nil || !on {
144		return err
145	}
146	_, err = s.DB.Exec(`
147		INSERT INTO push_queue (device_id, title, body, path)
148		SELECT id, ?, ?, ? FROM push_devices WHERE user_id = ?`,
149		title, body, path, userID)
150	return err
151}
152
153func (s *Store) DuePush(limit int) ([]QueuedPush, error) {
154	rows, err := s.DB.Query(`
155		SELECT q.id, q.device_id, d.token, u.username, q.title, q.body, q.path, q.attempts,
156		       (SELECT COUNT(*) FROM inbox WHERE user_id = d.user_id AND read_at IS NULL)
157		FROM push_queue q
158		JOIN push_devices d ON d.id = q.device_id
159		JOIN users u ON u.id = d.user_id
160		WHERE q.sent_at IS NULL AND q.failed_at IS NULL
161		  AND (q.next_attempt_at IS NULL OR q.next_attempt_at <= ?)
162		ORDER BY q.id LIMIT ?`, fmtTime(time.Now()), limit)
163	if err != nil {
164		return nil, err
165	}
166	defer rows.Close()
167	var out []QueuedPush
168	for rows.Next() {
169		var p QueuedPush
170		if err := rows.Scan(&p.ID, &p.DeviceID, &p.Token, &p.Username, &p.Title, &p.Body, &p.Path, &p.Attempts, &p.Badge); err != nil {
171			return nil, err
172		}
173		out = append(out, p)
174	}
175	return out, rows.Err()
176}
177
178func (s *Store) MarkPushSent(id int64) error {
179	_, err := s.DB.Exec(
180		"UPDATE push_queue SET sent_at = strftime('%Y-%m-%dT%H:%M:%fZ','now'), attempts = attempts + 1 WHERE id = ?", id)
181	return err
182}
183
184func (s *Store) MarkPushFailed(id int64, errMsg string, nextAt *time.Time) error {
185	if nextAt == nil {
186		_, err := s.DB.Exec(
187			"UPDATE push_queue SET failed_at = strftime('%Y-%m-%dT%H:%M:%fZ','now'), attempts = attempts + 1, last_error = ? WHERE id = ?",
188			errMsg, id)
189		return err
190	}
191	_, err := s.DB.Exec(
192		"UPDATE push_queue SET attempts = attempts + 1, last_error = ?, next_attempt_at = ? WHERE id = ?",
193		errMsg, fmtTime(*nextAt), id)
194	return err
195}
196
197// DeletePushDeviceByToken drops a device Apple has told us is gone. The
198// queue rows cascade, so nothing is left retrying at a dead token.
199func (s *Store) DeletePushDeviceByToken(token string) error {
200	_, err := s.DB.Exec("DELETE FROM push_devices WHERE token = ?", token)
201	return err
202}