72 lines
2.4 KiB
SQL
72 lines
2.4 KiB
SQL
-- Provider identities allow passwordless platform users while legacy credentials remain valid.
|
|
ALTER TABLE users
|
|
ALTER COLUMN username DROP NOT NULL,
|
|
ALTER COLUMN password_hashed DROP NOT NULL;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_constraint WHERE conname = 'users_credentials_pair_check'
|
|
) THEN
|
|
ALTER TABLE users ADD CONSTRAINT users_credentials_pair_check
|
|
CHECK (
|
|
(username IS NULL AND password_hashed IS NULL)
|
|
OR (username IS NOT NULL AND password_hashed IS NOT NULL)
|
|
);
|
|
END IF;
|
|
END $$;
|
|
|
|
CREATE TABLE IF NOT EXISTS provider_identities (
|
|
id UUID PRIMARY KEY,
|
|
user_uuid UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
provider VARCHAR(50) NOT NULL,
|
|
provider_user_id VARCHAR(255) NOT NULL,
|
|
display_name VARCHAR(255),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
CONSTRAINT provider_identities_provider_user_unique
|
|
UNIQUE (provider, provider_user_id),
|
|
CONSTRAINT provider_identities_user_provider_unique
|
|
UNIQUE (user_uuid, provider)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS provider_identities_user_idx
|
|
ON provider_identities (user_uuid);
|
|
|
|
CREATE TABLE IF NOT EXISTS api_keys (
|
|
id UUID PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
key_type VARCHAR(20) NOT NULL,
|
|
user_uuid UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
service_name VARCHAR(255),
|
|
key_prefix VARCHAR(20) NOT NULL,
|
|
key_hash CHAR(64) NOT NULL UNIQUE,
|
|
scopes JSONB NOT NULL DEFAULT '[]'::JSONB,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
expires_at TIMESTAMPTZ,
|
|
revoked_at TIMESTAMPTZ,
|
|
last_used_at TIMESTAMPTZ,
|
|
CONSTRAINT api_keys_type_check
|
|
CHECK (key_type IN ('user', 'service')),
|
|
CONSTRAINT api_keys_scopes_check
|
|
CHECK (jsonb_typeof(scopes) = 'array'),
|
|
CONSTRAINT api_keys_owner_check
|
|
CHECK (
|
|
(key_type = 'user' AND user_uuid IS NOT NULL
|
|
AND service_name IS NULL AND scopes = '[]'::JSONB)
|
|
OR
|
|
(key_type = 'service' AND user_uuid IS NULL
|
|
AND service_name IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS api_keys_prefix_idx
|
|
ON api_keys (key_prefix);
|
|
|
|
CREATE INDEX IF NOT EXISTS api_keys_user_idx
|
|
ON api_keys (user_uuid, created_at DESC)
|
|
WHERE key_type = 'user';
|
|
|
|
CREATE INDEX IF NOT EXISTS api_keys_service_idx
|
|
ON api_keys (service_name, created_at DESC)
|
|
WHERE key_type = 'service';
|