36 lines
1.3 KiB
SQL
36 lines
1.3 KiB
SQL
-- migrations/0006_social_identities.sql
|
|
-- Phase 8: Social sign-in identities.
|
|
|
|
-- +goose Up
|
|
|
|
ALTER TABLE users ALTER COLUMN password_hash DROP NOT NULL;
|
|
|
|
CREATE TABLE user_identities (
|
|
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
provider text NOT NULL CHECK (provider IN ('google', 'apple')),
|
|
provider_subject text NOT NULL,
|
|
email citext NOT NULL,
|
|
email_verified boolean NOT NULL DEFAULT true,
|
|
display_name text,
|
|
avatar_url text,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
last_login_at timestamptz,
|
|
UNIQUE (provider, provider_subject)
|
|
);
|
|
|
|
CREATE INDEX user_identities_user_id_idx ON user_identities(user_id);
|
|
|
|
-- +goose Down
|
|
|
|
DROP TABLE IF EXISTS user_identities;
|
|
|
|
-- Social-only rows cannot be made password-login capable during downgrade.
|
|
-- This sentinel is deliberately not a valid PHC hash; email/password login
|
|
-- treats invalid hashes as invalid credentials.
|
|
UPDATE users
|
|
SET password_hash = '__social_only_downgrade_no_password__'
|
|
WHERE password_hash IS NULL;
|
|
|
|
ALTER TABLE users ALTER COLUMN password_hash SET NOT NULL;
|