package store import ( "context" "crypto/sha256" "database/sql" "encoding/base64" "errors" "strings" "time" "pageswiki/internal/ordoschnellic" ) const NotificationPushEventType = ordoschnellic.PushEventType func (s *Store) ensureNotificationPushSchema() error { if s.sqlBackend == storePostgreSQL { return s.ensurePostgreSQLPushStorage(context.Background()) } _, err := s.DB.Exec(`CREATE TABLE IF NOT EXISTS notification_push_keys(id INTEGER PRIMARY KEY CHECK(id=1),private_key TEXT NOT NULL,public_key TEXT NOT NULL); CREATE TABLE IF NOT EXISTS notification_push_subscriptions( id TEXT PRIMARY KEY,domain_uuid TEXT NOT NULL,owner_uuid TEXT NOT NULL,session_hash TEXT NOT NULL, subscription TEXT NOT NULL,expires_at_ms INTEGER NOT NULL,foreground_until_ms INTEGER NOT NULL DEFAULT 0); CREATE INDEX IF NOT EXISTS notification_push_owner ON notification_push_subscriptions(domain_uuid,owner_uuid); CREATE INDEX IF NOT EXISTS notification_push_expiry ON notification_push_subscriptions(expires_at_ms); CREATE TABLE IF NOT EXISTS notification_push_queue( subscription_id TEXT NOT NULL,notification_uuid TEXT NOT NULL,created_at_ms INTEGER NOT NULL,next_at_ms INTEGER NOT NULL, attempts INTEGER NOT NULL DEFAULT 0,lease_until_ms INTEGER NOT NULL DEFAULT 0,PRIMARY KEY(subscription_id,notification_uuid)); CREATE INDEX IF NOT EXISTS notification_push_due ON notification_push_queue(next_at_ms,lease_until_ms); CREATE INDEX IF NOT EXISTS notification_push_age ON notification_push_queue(created_at_ms); CREATE TRIGGER IF NOT EXISTS notification_push_enqueue AFTER INSERT ON notification_inbox BEGIN INSERT OR IGNORE INTO notification_push_queue(subscription_id,notification_uuid,created_at_ms,next_at_ms) SELECT id,NEW.uuid,NEW.created_at_ms,NEW.created_at_ms FROM notification_push_subscriptions WHERE domain_uuid=NEW.domain_uuid AND owner_uuid=NEW.recipient_uuid AND expires_at_ms>NEW.created_at_ms; END;`) return err } func (s *Store) EnsureNotificationPushEvent() error { if !s.SupportsNotificationPush() { return ErrBackendFeatureUnavailable } now := time.Now().UnixMilli() _, err := s.DB.Exec(`INSERT OR IGNORE INTO event_definitions(uuid,name,event_type,event_key,payload,schedule_kind,interval_seconds,next_run_at_ms,enabled,priority,max_attempts,created_at_ms,updated_at_ms) VALUES('builtin.notification.push','Browser notifications',?,'local','{}','interval',30,?,1,-3,3,?,?)`, NotificationPushEventType, now, now, now) return err } // Never stores the browser's bearer token. Delivery is bound to its revocable session. func (s *Store) SaveNotificationPush(ctx context.Context, domain, owner, token, endpoint, subscription string, focused bool) error { if !s.SupportsNotificationPush() { return ErrBackendFeatureUnavailable } sum := sha256.Sum256([]byte(token)) hash := base64.RawURLEncoding.EncodeToString(sum[:]) sum = sha256.Sum256([]byte(endpoint)) id := base64.RawURLEncoding.EncodeToString(sum[:]) tx, err := s.DB.BeginTx(ctx, nil) if err != nil { return err } defer tx.Rollback() if _, err = tx.ExecContext(ctx, `UPDATE domain_memberships SET state=state WHERE domain_uuid=? AND principal_uuid=?`, domain, owner); err != nil { return err } var count int if err = tx.QueryRowContext(ctx, `SELECT COUNT(*) FROM notification_push_subscriptions WHERE domain_uuid=? AND owner_uuid=? AND id<>? AND expires_at_ms>?`, domain, owner, id, time.Now().UnixMilli()).Scan(&count); err != nil { return err } if count >= 8 { return ErrNotificationInput } until := int64(0) if focused { until = time.Now().Add(90 * time.Second).UnixMilli() } result, err := tx.ExecContext(ctx, `INSERT INTO notification_push_subscriptions(id,domain_uuid,owner_uuid,session_hash,subscription,expires_at_ms,foreground_until_ms) VALUES(?,?,?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET session_hash=excluded.session_hash,subscription=excluded.subscription,expires_at_ms=excluded.expires_at_ms,foreground_until_ms=excluded.foreground_until_ms WHERE domain_uuid=excluded.domain_uuid AND owner_uuid=excluded.owner_uuid`, id, domain, owner, hash, subscription, time.Now().Add(30*24*time.Hour).UnixMilli(), until) if err != nil { return err } n, _ := result.RowsAffected() if n != 1 { return ErrNotificationInput } return tx.Commit() } func (s *Store) DeleteNotificationPush(ctx context.Context, domain, owner, endpoint string) error { if !s.SupportsNotificationPush() { return ErrBackendFeatureUnavailable } sum := sha256.Sum256([]byte(endpoint)) id := base64.RawURLEncoding.EncodeToString(sum[:]) _, err := s.DB.ExecContext(ctx, `DELETE FROM notification_push_subscriptions WHERE id=? AND domain_uuid=? AND owner_uuid=?`, id, domain, owner) return err } type NotificationPushDelivery = ordoschnellic.PushDelivery func (s *Store) AcknowledgeNotificationPush(ctx context.Context, domain, owner, endpoint string, ids []string) error { if !s.SupportsNotificationPush() { return ErrBackendFeatureUnavailable } if len(ids) == 0 { return nil } if len(ids) > 30 { return ErrNotificationInput } sum := sha256.Sum256([]byte(endpoint)) id := base64.RawURLEncoding.EncodeToString(sum[:]) args := []any{id, domain, owner} for _, v := range ids { args = append(args, v) } _, err := s.DB.ExecContext(ctx, `DELETE FROM notification_push_queue WHERE subscription_id IN (SELECT id FROM notification_push_subscriptions WHERE id=? AND domain_uuid=? AND owner_uuid=?) AND notification_uuid IN (`+strings.TrimSuffix(strings.Repeat("?,", len(ids)), ",")+`)`, args...) return err } func (s *Store) DeferFocusedNotificationPush(ctx context.Context, d *NotificationPushDelivery) error { return s.DeferFocusedNotificationPushAt(ctx, d, time.Now()) } func (s *Store) DeferFocusedNotificationPushAt(ctx context.Context, d *NotificationPushDelivery, at time.Time) error { if !s.SupportsNotificationPush() { return ErrBackendFeatureUnavailable } _, err := s.DB.ExecContext(ctx, `UPDATE notification_push_queue SET attempts=attempts-1,lease_until_ms=0,next_at_ms=? WHERE subscription_id=? AND notification_uuid=? AND attempts=?`, at.Add(ordoschnellic.PushFocusDelay).UnixMilli(), d.ID, d.Notification, d.Attempt) return err } func (s *Store) ClaimNotificationPush(ctx context.Context) (*NotificationPushDelivery, error) { return s.ClaimNotificationPushAt(ctx, time.Now()) } func (s *Store) ClaimNotificationPushAt(ctx context.Context, at time.Time) (*NotificationPushDelivery, error) { if !s.SupportsNotificationPush() { return nil, ErrBackendFeatureUnavailable } now := at.UnixMilli() tx, err := s.DB.BeginTx(ctx, nil) if err != nil { return nil, err } defer tx.Rollback() // Atomic claim across scheduler workers/processes. Outbound I/O is never inside this transaction. var d NotificationPushDelivery err = tx.QueryRowContext(ctx, `UPDATE notification_push_queue SET lease_until_ms=?,attempts=attempts+1 WHERE (subscription_id,notification_uuid)=(SELECT subscription_id,notification_uuid FROM notification_push_queue WHERE next_at_ms<=? AND lease_until_ms<=? ORDER BY next_at_ms LIMIT 1) RETURNING subscription_id,notification_uuid,attempts`, at.Add(ordoschnellic.PushLeaseDuration).UnixMilli(), now, now).Scan(&d.ID, &d.Notification, &d.Attempt) if errors.Is(err, sql.ErrNoRows) { return nil, nil } if err != nil { return nil, err } err = tx.QueryRowContext(ctx, `SELECT s.domain_uuid,s.owner_uuid,s.subscription,s.foreground_until_ms>? FROM notification_push_subscriptions s JOIN auth_sessions a ON a.token_hash=s.session_hash AND a.domain_uuid=s.domain_uuid AND a.principal_uuid=s.owner_uuid JOIN principals p ON p.uuid=s.owner_uuid JOIN domain_memberships m ON m.domain_uuid=s.domain_uuid AND m.principal_uuid=s.owner_uuid JOIN domains d ON d.uuid=s.domain_uuid JOIN notification_inbox n ON n.uuid=? AND n.domain_uuid=s.domain_uuid AND n.recipient_uuid=s.owner_uuid WHERE s.id=? AND s.expires_at_ms>? AND a.expires_at_ms>? AND a.revoked_at_ms=0 AND p.state IN ('active','readonly') AND m.state='active' AND d.enabled=1 AND COALESCE(d.placement,'local')='local' AND n.status='unread' AND n.created_at_ms>?`, now, d.Notification, d.ID, now, now, now-24*60*60*1000).Scan(&d.Domain, &d.Owner, &d.Subscription, &d.Focused) if errors.Is(err, sql.ErrNoRows) { d.Subscription = "" } else if err != nil { return nil, err } if err = tx.Commit(); err != nil { return nil, err } return &d, nil } func (s *Store) FinishNotificationPush(ctx context.Context, d *NotificationPushDelivery, retry, expired bool) error { return s.FinishNotificationPushAt(ctx, d, retry, expired, time.Now()) } func (s *Store) FinishNotificationPushAt(ctx context.Context, d *NotificationPushDelivery, retry, expired bool, at time.Time) error { if !s.SupportsNotificationPush() { return ErrBackendFeatureUnavailable } if expired { if _, err := s.DB.ExecContext(ctx, `DELETE FROM notification_push_subscriptions WHERE id=?`, d.ID); err != nil { return err } } if next, again := ordoschnellic.RetryAt(d.Attempt, at); retry && again { _, err := s.DB.ExecContext(ctx, `UPDATE notification_push_queue SET next_at_ms=?,lease_until_ms=0 WHERE subscription_id=? AND notification_uuid=? AND attempts=?`, next.UnixMilli(), d.ID, d.Notification, d.Attempt) return err } _, err := s.DB.ExecContext(ctx, `DELETE FROM notification_push_queue WHERE subscription_id=? AND notification_uuid=? AND attempts=?`, d.ID, d.Notification, d.Attempt) return err } // CleanupNotificationPush only removes bounded stale delivery state, not Inbox. func (s *Store) CleanupNotificationPush(ctx context.Context, at time.Time) error { if !s.SupportsNotificationPush() { return ErrBackendFeatureUnavailable } if s.ReadOnly { return ErrReadOnly } _, err := s.DB.ExecContext(ctx, `DELETE FROM notification_push_subscriptions WHERE id IN (SELECT id FROM notification_push_subscriptions WHERE expires_at_ms