350ffdce29
Add a migration that runs just before 20260318000000 (add_permissioned_as).
For each trigger table, if the email column still exists, update edited_by
to the trigger's email when the user is not in the workspace but is a
superadmin. This ensures the subsequent permissioned_as migration stores
the raw email instead of an invalid u/{username} reference.
If 20260318000000 was already applied, the migration is a no-op (email
column is gone, guarded by information_schema check).
Co-authored-by: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
49 lines
1.5 KiB
SQL
49 lines
1.5 KiB
SQL
-- Pre-fix: before permissioned_as migration drops the email column, update edited_by
|
|
-- for triggers where the user (edited_by) is not in the workspace but is a superadmin.
|
|
-- This ensures the subsequent 20260318000000 migration stores the raw email as permissioned_as
|
|
-- (via the `edited_by LIKE '%@%'` branch).
|
|
-- For instances that already applied 20260318000000, this is a no-op (email column is gone);
|
|
-- the 20260401000000 migration handles those as a fallback.
|
|
|
|
DO $$
|
|
DECLARE
|
|
trigger_table TEXT;
|
|
has_email BOOLEAN;
|
|
BEGIN
|
|
FOREACH trigger_table IN ARRAY ARRAY[
|
|
'http_trigger',
|
|
'websocket_trigger',
|
|
'postgres_trigger',
|
|
'mqtt_trigger',
|
|
'kafka_trigger',
|
|
'nats_trigger',
|
|
'sqs_trigger',
|
|
'gcp_trigger',
|
|
'email_trigger'
|
|
]
|
|
LOOP
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM information_schema.columns
|
|
WHERE table_name = trigger_table AND column_name = 'email'
|
|
) INTO has_email;
|
|
|
|
IF has_email THEN
|
|
EXECUTE format($q$
|
|
UPDATE %I t
|
|
SET edited_by = t.email
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM usr u
|
|
WHERE u.username = t.edited_by
|
|
AND u.workspace_id = t.workspace_id
|
|
)
|
|
AND EXISTS (
|
|
SELECT 1 FROM password p
|
|
WHERE p.email = t.email
|
|
AND p.super_admin = true
|
|
)
|
|
$q$, trigger_table);
|
|
END IF;
|
|
END LOOP;
|
|
END;
|
|
$$;
|