FeedAHelp / FeedAHelpless

First repo for the youtube vedio
https://feed-a-helpless-feedahelp.vercel.app
MIT License
1 stars 3 forks source link

459: Login Page form Backend #463

Open TanvirAlam opened 2 months ago

TanvirAlam commented 2 months ago

Related to #459

-- Users table to store general user information
CREATE TABLE users (
    id SERIAL PRIMARY KEY,                  -- Unique user ID
    email VARCHAR(255) UNIQUE NOT NULL,     -- User email (unique)
    password_hash TEXT,                     -- Hashed password (null for social logins)
    first_name VARCHAR(100),                -- First name
    last_name VARCHAR(100),                 -- Last name
    created_at TIMESTAMP DEFAULT NOW(),     -- Account creation date
    updated_at TIMESTAMP DEFAULT NOW()      -- Account update date
);

-- Social Accounts table to store social authentication details
CREATE TABLE social_accounts (
    id SERIAL PRIMARY KEY,                   -- Unique social account ID
    user_id INT REFERENCES users(id),        -- Reference to users table
    provider VARCHAR(100) NOT NULL,          -- Social provider name (e.g., google, facebook)
    provider_user_id VARCHAR(255) NOT NULL,  -- Unique ID provided by the social provider
    access_token TEXT,                       -- OAuth access token (can be refreshed if needed)
    refresh_token TEXT,                      -- OAuth refresh token (if applicable)
    token_expires_at TIMESTAMP,              -- Expiry time of the access token
    created_at TIMESTAMP DEFAULT NOW(),      -- Account creation date
    updated_at TIMESTAMP DEFAULT NOW(),      -- Account update date
    UNIQUE (provider, provider_user_id)      -- Ensure uniqueness per provider-user combination
);

-- User sessions table to track user login sessions
CREATE TABLE user_sessions (
    id SERIAL PRIMARY KEY,                   -- Unique session ID
    user_id INT REFERENCES users(id),        -- Reference to users table
    session_token TEXT NOT NULL,             -- Session token for login
    created_at TIMESTAMP DEFAULT NOW(),      -- Session creation date
    expires_at TIMESTAMP,                    -- Session expiration date/time
    last_active TIMESTAMP DEFAULT NOW()      -- Last activity time to track active sessions
);

-- Password Reset Tokens table for handling password reset requests
CREATE TABLE password_reset_tokens (
    id SERIAL PRIMARY KEY,                   -- Unique token ID
    user_id INT REFERENCES users(id),        -- Reference to users table
    reset_token TEXT NOT NULL,               -- Token used for resetting password
    expires_at TIMESTAMP NOT NULL,           -- Expiration date/time of the token
    used BOOLEAN DEFAULT FALSE,              -- Indicates whether the token has been used
    created_at TIMESTAMP DEFAULT NOW()       -- Token creation date
);
JAHIHA commented 2 months ago

@TanvirAlam I made the changes as specified but the only problem was with the id in the user table. If the int was to be used i would have to change the id of the wishes likes and shares files as well as the contributors as they are connected to the id of user. even if i change that the controllers need to be parsed to int form string or change the id of the files and the register file as well. even if also do that i need to change some of the testing rules because they only accept string as the id, so i opted to make it a varchar instead so there wont be any problems. If you want i can change the entire thing but it will take a conciderable time to fix everything.