Okay, Had some time to put into this. Much happier with this approach as it doesn't require any file moving and generally leaves src blocks to their own devices.
The short version is that specifying ":dir 'attach" for a block uses the directory from (org-attach-dir) as its working directory and any generated path that is a descendant of that directory will be converted to an "attachment:" link.

ob-core.el/babel: Special handling for attachment links in src blocks

* ob-core.el (org-babel-execute-src-block): Specifying the symbol
'attach` as the value of the `:dir' header now functions as
":dir (org-attach-dir)"
(org-babel-result-to-file): Optional `TYPE' argument accepts symbol
'attachment` to fixup up paths under `DEFAULT-DIRECTORY' and use the
link type "attachment:" when that is detected.
(org-babel-insert-result): Pass symbol `attachment' as `TYPE' to
`org-babel-result-to-file' when header `:dir' is set to symbol
`attach'
(org-babel-load-in-session, org-babel-initiate-session) ":dir 'attach"
sets `default-directory' with "(org-attach-dir t)"
* org-attach.el (org-attach-dir): Added autoload header to simplify
dependencies necessary to support this feature (called in
`org-babel-execute-src-block').

On Sun, Sep 5, 2021 at 6:56 AM Ryan Scott <ryan@vicarious-living.com> wrote:
Yeah your second example is what I'm thinking. It makes this all a fairly concise extension of that existing mechanism and does away with the file move after execution. 

On Sun, Sep 5, 2021, 06:21 Ihor Radchenko <yantar92@gmail.com> wrote:
Ryan Scott <ryan@vicarious-living.com> writes:

> It might make sense to fix up inserted "file:" links that are under the
> attachment directory to be "attachment:" style links by default anyway, no?
> Then just being able to set the working directory to the attachment
> directory easily would get the rest of the way there.

I am not sure. If the user explicitly states that :dir is the attachment
dir, it would make sense. However, what if the :dir is set explicitly
like below?

* Headline
:PROPERTIES:
:DIR: /actual/literal/path/to/attachment/dir
:END:

#+begin_src emacs-lisp :dir /actual/literal/path/to/attachment/dir
...

#+RESULTS:
attachment:...

The results will be indeed inside the attachment directory. However, the
:DIR: property may be changed at some point and the existing attachment:
link will not point to real file.

> So I suppose that would then mean having the :dir header accept the symbol
> `attach' or something like that?
> I'll play around and see what that looks like.

The above example should lead to more expected behaviour if the user
explicitly states that :dir is the attachment dir (even if it is going
to be changed in future):

* Headline
:PROPERTIES:
:DIR: /actual/literal/path/to/attachment/dir
:END:

#+begin_src emacs-lisp :dir 'attach
...

#+RESULTS:
attachment:...

Best,
Ihor