internal/store/push.go
225 lines · 7675 bytes
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}