43 lines
1.5 KiB
SQL
43 lines
1.5 KiB
SQL
-- Email delivery support for invites, password resets, and notification deduplication.
|
|
|
|
CREATE TABLE IF NOT EXISTS password_reset_tokens (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_hash VARCHAR(64) NOT NULL UNIQUE,
|
|
expires_at TIMESTAMP NOT NULL,
|
|
used_at TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_password_reset_tokens_user
|
|
ON password_reset_tokens(user_id, expires_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_invitations (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR(255) NOT NULL,
|
|
first_name VARCHAR(100),
|
|
last_name VARCHAR(100),
|
|
role VARCHAR(50) NOT NULL DEFAULT 'user',
|
|
invited_by_user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
token_hash VARCHAR(64) NOT NULL UNIQUE,
|
|
expires_at TIMESTAMP NOT NULL,
|
|
accepted_at TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_invitations_email
|
|
ON user_invitations(email, expires_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS notification_dispatch_log (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
notification_type VARCHAR(100) NOT NULL,
|
|
dedupe_key VARCHAR(255) NOT NULL UNIQUE,
|
|
metadata JSONB DEFAULT '{}'::jsonb,
|
|
sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_notification_dispatch_log_user_type
|
|
ON notification_dispatch_log(user_id, notification_type, sent_at DESC);
|