internal/store/push.go

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