File size: 1,827 Bytes
383cb38 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | -- User Accounts Table
-- Stores linked authentication providers for each user
-- Allows users to sign in with multiple methods (Google, GitHub, Email/Password)
CREATE TABLE IF NOT EXISTS user_accounts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
provider VARCHAR(50) NOT NULL, -- 'google', 'github', 'credentials'
provider_account_id VARCHAR(255), -- OAuth provider's user ID
access_token TEXT, -- OAuth access token (encrypted in production)
refresh_token TEXT, -- OAuth refresh token (encrypted in production)
expires_at TIMESTAMP, -- Token expiration
token_type VARCHAR(50), -- 'Bearer', etc.
scope TEXT, -- OAuth scopes granted
id_token TEXT, -- OpenID Connect ID token
session_state TEXT, -- OAuth session state
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
-- Ensure one provider account per user
UNIQUE(provider, provider_account_id),
-- Ensure user can only link one account per provider type
UNIQUE(user_id, provider)
);
-- Indexes for faster lookups
CREATE INDEX IF NOT EXISTS idx_user_accounts_user_id ON user_accounts(user_id);
CREATE INDEX IF NOT EXISTS idx_user_accounts_provider ON user_accounts(provider, provider_account_id);
-- Update timestamp trigger
CREATE TRIGGER update_user_accounts_updated_at BEFORE UPDATE ON user_accounts
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- Comments for documentation
COMMENT ON TABLE user_accounts IS 'Linked authentication providers for users';
COMMENT ON COLUMN user_accounts.provider IS 'Authentication provider: google, github, credentials';
COMMENT ON COLUMN user_accounts.provider_account_id IS 'Unique identifier from the OAuth provider';
|