Unable to UPSERT secrets on vault.secrets

[geoffreygarrett]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

*Disclaimer: I couldn't check the discord, invite wasn't working for me on linux.

Describe the bug

Unable to upsert secrets using the unique index secrets_name_idx.

INSERT INTO vault.secrets (id, name, secret, description)
    SELECT * FROM unnest($1, $2, $3, $4)
    ON CONFLICT ON CONSTRAINT secrets_name_idx
    DO UPDATE SET
        id = COALESCE(NULLIF(EXCLUDED.id, NULL), secrets.id),
        secret = EXCLUDED.secret,
        description = EXCLUDED.description,
        updated_at = CURRENT_TIMESTAMP
    RETURNING *;

Result:

message: "constraint \"secrets_name_idx\" for table \"secrets\" does not exist",

Which makes sense because the table definition is missing the following:

ADD CONSTRAINT secrets_name_idx UNIQUE USING INDEX secrets_name_idx;

It's unfortunately, a protected schema.

Note: I have also tried conflict resolution with name but it doesn't resolve with the secrets_name_idx (name IS NOT NULL);

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. Go to 'sql editor`
2. Run either.

INSERT INTO vault.secrets (name, secret, description)
VALUES ('exampleName', 'newSecretData', 'A description of the secret')
ON CONFLICT ON CONSTRAINT secrets_name_idx
DO UPDATE SET
    secret = EXCLUDED.secret,
    description = EXCLUDED.description,
    updated_at = CURRENT_TIMESTAMP
RETURNING *;

INSERT INTO vault.secrets (name, secret, description)
VALUES ('exampleName', 'newSecretData', 'A description of the secret')
ON CONFLICT (name) 
DO UPDATE SET
    secret = EXCLUDED.secret,
    description = EXCLUDED.description,
    updated_at = CURRENT_TIMESTAMP
RETURNING *;

Expected behavior

Successful upsert of secrets using secrets_name_idx or a constraint derived from it.