19 lines
824 B
SQL
19 lines
824 B
SQL
ALTER TABLE users
|
|
ADD COLUMN IF NOT EXISTS profile_visibility VARCHAR(20) NOT NULL DEFAULT 'public'
|
|
CHECK (profile_visibility IN ('public', 'friends'));
|
|
|
|
CREATE TABLE IF NOT EXISTS friendships (
|
|
id SERIAL PRIMARY KEY,
|
|
requester_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
addressee_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'pending'
|
|
CHECK (status IN ('pending', 'accepted')),
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (requester_id <> addressee_id),
|
|
UNIQUE (requester_id, addressee_id)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_friendships_pair
|
|
ON friendships (LEAST(requester_id, addressee_id), GREATEST(requester_id, addressee_id));
|