@@ -141,17 +141,24 @@ ALTER TYPE api_key_scope ADD VALUE IF NOT EXISTS 'workspace_proxy:read';
141141ALTER TYPE api_key_scope ADD VALUE IF NOT EXISTS' workspace_proxy:update'
142142-- End enum extensions
143143
144+ -- Purge old API keys to speed up the migration for large deployments.
145+ -- Note: that problem should be solved in coderd once PR 20863 is released:
146+ -- https://github.com/coder/coder/blob/main/coderd/database/dbpurge/dbpurge.go#L85
147+
148+ DELETE FROM api_keysWHERE expires_at< NOW()- INTERVAL' 7 days'
149+
144150-- Add new columns without defaults; backfill; then enforce NOT NULL
145- ALTER TABLE api_keys ADD COLUMN scopes api_key_scope[];
146- ALTER TABLE api_keys ADD COLUMN allow_listtext [];
151+ ALTER TABLE api_keys
152+ ADD COLUMN scopes api_key_scope[],
153+ ADD COLUMN allow_listtext [];
147154
148155-- Backfill existing rows for compatibility
149- UPDATE api_keysSET scopes= ARRAY[scope::api_key_scope];
150- UPDATE api_keysSET allow_list= ARRAY[' *:*'
151-
152- -- Enforce NOT NULL
153- ALTER TABLE api_keys ALTER COLUMN scopesSET NOT NULL ;
154- ALTER TABLE api_keys ALTER COLUMN allow_listSET NOT NULL ;
156+ UPDATE api_keysSET
157+ scopes= ARRAY[scope::api_key_scope],
158+ allow_list= ARRAY[' *:*'
155159
156- -- Drop legacy single-scope column
157- ALTER TABLE api_keys DROP COLUMN scope;
160+ -- Enforce NOT NULL and drop legacy single-scope column
161+ ALTER TABLE api_keys
162+ ALTER COLUMN scopesSET NOT NULL ,
163+ ALTER COLUMN allow_listSET NOT NULL ,
164+ DROP COLUMN scope;