fix(catalog): retire a specification that only orders reference

flowchart LR
  D["DELETE /internal/catalog/service-specifications/{id}"] --> I{"service_instance rows?"}
  I -- "yes" --> L{"any live?"}
  L -- "yes" --> X["409 in use"]
  L -- "no" --> R["retired"]
  I -- "no" --> O{"service_order rows?<br/>(new check)"}
  O -- "yes: e.g. a partner-hosted deploy" --> R
  O -- "no" --> H["hard delete"]
  O -. "before: went straight to hard delete,<br/>FK service_order_service_specification_id_fkey, 503" .-> H

Deleting an app that had been deployed to a partner zone failed with 503. SRM keeps no service_instance row for an instance a partner hosts, only its service_order, so delete_or_retire saw no instances and tried a hard delete that the order's foreign key refused.

Now a specification that only orders reference becomes retired, as one with terminal instances already does. One with nothing referencing it is still deleted.

	Result
unit + integration	485 passed; mypy, ruff, import-linter clean
new Postgres test	fails without the fix with ForeignKeyViolationError ... service_order_service_specification_id_fkey, passes with it
testbed, ns oop, OP-1	app deployed to Paris (OP-2) and deleted: DELETE /apps was 503, now 204, then GET 404; SRM row retired

Found while testing FM fixes on the three-operator testbed. Targets refactor/tf-sdk-wiring like SRM !37 (merged) and !39 (merged). Until !39 (merged) merges this branch's pipeline shows the same 6 fixture failures as the base.

🤖 Generated with Claude Code