36 lines
1.8 KiB
SQL
36 lines
1.8 KiB
SQL
-- OAuth account linkage.
|
|
--
|
|
-- One row per (provider, provider_user_id). A single user can have several
|
|
-- providers linked (e.g. signed up with email + later linked Google). The
|
|
-- provider's `sub` (Google) / `id` (Facebook) is the stable identity — email
|
|
-- is captured for display but is NOT the join key, since providers can let
|
|
-- users change the email on the account.
|
|
--
|
|
-- When a callback fires:
|
|
-- 1. Look up (provider, provider_user_id) — found → log that user in.
|
|
-- 2. Otherwise look up users.email — found → link new oauth_accounts row
|
|
-- to the existing user and log them in.
|
|
-- 3. Otherwise create a new user (password_hash NULL) + an oauth_accounts
|
|
-- row, log them in.
|
|
CREATE TABLE IF NOT EXISTS oauth_accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
provider VARCHAR(32) NOT NULL, -- "google" | "facebook"
|
|
provider_user_id TEXT NOT NULL, -- sub (Google) / id (Facebook)
|
|
email CITEXT,
|
|
name TEXT,
|
|
avatar_url TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (provider, provider_user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS oauth_accounts_user_id_idx ON oauth_accounts (user_id);
|
|
CREATE INDEX IF NOT EXISTS oauth_accounts_provider_idx ON oauth_accounts (provider);
|
|
|
|
-- Password hash must be optional now: OAuth-only users have no password.
|
|
-- Existing email/password users keep their hash; new OAuth users get NULL
|
|
-- and can set a password later via the forgot-password flow if they want
|
|
-- a fallback login method.
|
|
ALTER TABLE users ALTER COLUMN password_hash DROP NOT NULL;
|