Created
November 16, 2025 21:09
-
-
Save cmackenzie1/eda1e6f95aadd61166c6c064dd8b7a4b to your computer and use it in GitHub Desktop.
PostgreSQL DDL for users, invitations, friends, and password based authnetication
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| CREATE TABLE users ( | |
| id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, | |
| email TEXT NOT NULL UNIQUE, | |
| name TEXT, | |
| password_hash TEXT, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| activated_at TIMESTAMPTZ, | |
| email_verified_at TIMESTAMPTZ | |
| ); | |
| CREATE TABLE sessions ( | |
| id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, | |
| user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, | |
| token TEXT NOT NULL UNIQUE, | |
| expires_at TIMESTAMPTZ NOT NULL, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| ip_address INET, | |
| user_agent TEXT | |
| ); | |
| CREATE TABLE password_reset_tokens ( | |
| id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, | |
| user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE, | |
| token TEXT NOT NULL UNIQUE, | |
| expires_at TIMESTAMPTZ NOT NULL, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| used_at TIMESTAMPTZ | |
| ); | |
| CREATE TABLE invitations ( | |
| id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, | |
| inviter_id BIGINT NOT NULL REFERENCES users(id), | |
| invitee_id BIGINT NOT NULL REFERENCES users(id), | |
| token TEXT NOT NULL UNIQUE, | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| expires_at TIMESTAMPTZ NOT NULL, | |
| accepted_at TIMESTAMPTZ | |
| ); | |
| CREATE TABLE friend_requests ( | |
| id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, | |
| requester_id BIGINT NOT NULL REFERENCES users(id), | |
| recipient_id BIGINT NOT NULL REFERENCES users(id), | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| accepted_at TIMESTAMPTZ, | |
| rejected_at TIMESTAMPTZ, | |
| UNIQUE(requester_id, recipient_id), | |
| CHECK (requester_id != recipient_id) | |
| ); | |
| CREATE TABLE friendships ( | |
| id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, | |
| user_id_1 BIGINT NOT NULL REFERENCES users(id), | |
| user_id_2 BIGINT NOT NULL REFERENCES users(id), | |
| created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), | |
| CHECK (user_id_1 < user_id_2), | |
| UNIQUE(user_id_1, user_id_2) | |
| ); | |
| CREATE INDEX idx_sessions_token ON sessions(token); | |
| CREATE INDEX idx_sessions_user_id ON sessions(user_id); | |
| CREATE INDEX idx_sessions_expires_at ON sessions(expires_at); | |
| CREATE INDEX idx_password_reset_tokens_token ON password_reset_tokens(token); | |
| CREATE INDEX idx_password_reset_tokens_user_id ON password_reset_tokens(user_id); | |
| CREATE INDEX idx_invitations_token ON invitations(token); | |
| CREATE INDEX idx_invitations_invitee_id ON invitations(invitee_id); | |
| CREATE INDEX idx_friend_requests_recipient ON friend_requests(recipient_id); | |
| CREATE INDEX idx_friend_requests_requester ON friend_requests(requester_id); | |
| CREATE INDEX idx_friendships_user_id_1 ON friendships(user_id_1); | |
| CREATE INDEX idx_friendships_user_id_2 ON friendships(user_id_2); | |
| -- Trigger function to update updated_at | |
| CREATE OR REPLACE FUNCTION update_updated_at_column() | |
| RETURNS TRIGGER AS $$ | |
| BEGIN | |
| NEW.updated_at = NOW(); | |
| RETURN NEW; | |
| END; | |
| $$ LANGUAGE plpgsql; | |
| -- Apply trigger to tables with updated_at | |
| CREATE TRIGGER update_users_updated_at | |
| BEFORE UPDATE ON users | |
| FOR EACH ROW | |
| EXECUTE FUNCTION update_updated_at_column(); | |
| CREATE TRIGGER update_sessions_updated_at | |
| BEFORE UPDATE ON sessions | |
| FOR EACH ROW | |
| EXECUTE FUNCTION update_updated_at_column(); | |
| CREATE TRIGGER update_invitations_updated_at | |
| BEFORE UPDATE ON invitations | |
| FOR EACH ROW | |
| EXECUTE FUNCTION update_updated_at_column(); | |
| CREATE TRIGGER update_friend_requests_updated_at | |
| BEFORE UPDATE ON friend_requests | |
| FOR EACH ROW | |
| EXECUTE FUNCTION update_updated_at_column(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment