52 lines
2.3 KiB
SQL
52 lines
2.3 KiB
SQL
|
-- +goose Up
|
||
|
|
-- +goose StatementBegin
|
||
|
|
-- request_integrations.capability_id must carry the capability SUB-ID
|
||
|
|
-- ("arr"/"seerr"), matching the value the host passes to
|
||
|
|
-- requireCapability("request_router.v1", id) when dispensing the plugin. Rows
|
||
|
|
-- created before the fix stored the capability TYPE "request_router.v1", which
|
||
|
|
-- resolves to no plugin capability (every save/options/fulfill call 500s with
|
||
|
|
-- "no fulfillment backend configured" / ErrCapabilityNotFound).
|
||
|
|
--
|
||
|
|
-- Backfill the sub-id from each row's bound installation's request_router.v1
|
||
|
|
-- capability. Rows with installation_id NULL (created under the pre-plugin
|
||
|
|
-- direct-to-arr system and never re-bound) are left untouched — an admin
|
||
|
|
-- re-saves them in Admin -> Requests to bind an installation and set the sub-id.
|
||
|
|
--
|
||
|
|
-- A deterministic correlated subquery (ORDER BY ... LIMIT 1) is used instead of a
|
||
|
|
-- join so that, in the unusual case where one installation exposes more than one
|
||
|
|
-- request_router.v1 capability, the same sub-id is chosen for every row rather
|
||
|
|
-- than letting Postgres pick arbitrarily from the join cross-product.
|
||
|
|
UPDATE public.request_integrations ri
|
||
|
|
SET capability_id = (
|
||
|
|
SELECT pc.capability_id
|
||
|
|
FROM public.plugin_capabilities pc
|
||
|
|
WHERE pc.plugin_installation_id = ri.installation_id
|
||
|
|
AND pc.capability_type = 'request_router.v1'
|
||
|
|
ORDER BY pc.capability_id
|
||
|
|
LIMIT 1
|
||
|
|
)
|
||
|
|
WHERE ri.capability_id = 'request_router.v1'
|
||
|
|
AND ri.installation_id IS NOT NULL
|
||
|
|
AND EXISTS (
|
||
|
|
SELECT 1
|
||
|
|
FROM public.plugin_capabilities pc
|
||
|
|
WHERE pc.plugin_installation_id = ri.installation_id
|
||
|
|
AND pc.capability_type = 'request_router.v1'
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Drop the misleading column default (the capability type). The application now
|
||
|
|
-- always supplies the sub-id on insert; the default would silently reintroduce
|
||
|
|
-- the unresolvable type for any row that omitted capability_id.
|
||
|
|
ALTER TABLE public.request_integrations
|
||
|
|
ALTER COLUMN capability_id DROP DEFAULT;
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
-- +goose StatementBegin
|
||
|
|
-- Restore the prior column default. The data backfill is intentionally not
|
||
|
|
-- reverted: the sub-id is the correct value, and rewriting it back to the
|
||
|
|
-- capability type would re-break fulfillment.
|
||
|
|
ALTER TABLE public.request_integrations
|
||
|
|
ALTER COLUMN capability_id SET DEFAULT 'request_router.v1';
|
||
|
|
-- +goose StatementEnd
|