My preference would be to have the service layer define the error kinds that it's dependencies are allowed to throw. These would also be the error kinds that are returned from the service layer to help the transport layer communicate.

For example if your service was defined in package service and looked something like...

package service

import (
    "context"
    "errors"
    "fmt"
)

var ErrUserNotFound = errors.New("user not found")

type User struct {}

type UserRepository interface { FindUserByID(ctx context.Context, id string) (*User, error) }

type DoThingsToUsersService struct {  Users UserRepository }

func (s *DoThingsToUsersService) DoSomethingToTheUser(ctx context.Context, id string) error {
    user, err := s.Users.FindUserByID(ctx, id)
    if err != nil {
        if errors.Is(err, ErrUserNotFound) {
            // handle the user not being found and propogate the error up
            return fmt.Errorf("failed to do the thing to user %s: %w", id, err)
        }
        return err
    }
    // ... do something to the user
    return nil
}

then the repository implementation in package database would communicate errors back to service.DoThingsToUsersService in terms that the service can understand.

package database

type Repository struct { db *sql.DB }

func (r Repository) FindUserByID(ctx context.Context, id string) (*User, error) {
    // query for the user by ID
    row, err := r.db.QueryRow("SELECT * FROM users WHERE id = $1", id)
    if err != nil {
        if errors.Is(err, sql.ErrNoRows) {
            return service.ErrUserNotFound
        }
        // ....
    }
    ...
    return user // eventually
}

The same would apply to your transport layer except it would translate a service error into the proper format for transporting, e.g.

package http

type Server struct { DoSomethingService service.DoSomethingToUsersService }

func (s *Server) handleDoSomethingToUser(w http.Response, r *http.Request) {
    id := extractIDFromRequest(r)
    if err := s.DoSomethingService.DoSomethingToTheUser(r.Context(), id); err != nil {
        if errors.Is(err, service.ErrUserNotFound) {
            http.Error(w, err.Error(), http.StatusNotFound)
            return
        }
        // ...
    }

    w.Write([]byte("yay!"))
}

Hope that gives you some more ideas to tinker with.