bvandeusen
a509ec538b
Adds users.email (optional, lowercase-unique via partial index), smtp_config singleton, and password_resets tokens. Plus the queries U3-T2 / T3 / T4 use: - ChangeUserPassword: self-service password change. HTTP layer verifies the current password before this fires. - UpdateUserProfile: set display_name + email together. - RegenerateApiToken: invalidate old API token. - GetUserByEmail: case-insensitive lookup for forgot-password. - GetSMTPConfig + UpdateSMTPConfig: admin SMTP settings CRUD. - CreatePasswordReset / GetPasswordReset / UsePasswordReset (:execrows for atomic claim) / DeleteExpiredPasswordResets (cron-style cleanup, not yet wired). Email column is nullable; users without email have admin-reset as their only password-recovery path. The lower() unique index allows many NULLs and prevents case-insensitive duplicates. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
45 lines
1.8 KiB
SQL
45 lines
1.8 KiB
SQL
-- User management U3: self-service profile + email + email-based
|
|
-- password reset infrastructure.
|
|
--
|
|
-- - users.email: optional; required for the forgot-password flow.
|
|
-- Lowercase-unique via a partial index to allow many NULL emails
|
|
-- while preventing case-insensitive duplicates.
|
|
-- - smtp_config singleton: SMTP server settings + from-address +
|
|
-- enabled flag. The actual mailer reads this row at send time so
|
|
-- config edits take effect without a server restart.
|
|
-- - password_resets: short-lived single-use tokens for the
|
|
-- forgot-password flow. 24h TTL enforced at issue time.
|
|
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS email text;
|
|
CREATE UNIQUE INDEX IF NOT EXISTS users_email_unique
|
|
ON users (lower(email))
|
|
WHERE email IS NOT NULL;
|
|
|
|
CREATE TABLE smtp_config (
|
|
id boolean PRIMARY KEY DEFAULT true,
|
|
enabled boolean NOT NULL DEFAULT false,
|
|
host text NOT NULL DEFAULT '',
|
|
port int NOT NULL DEFAULT 587,
|
|
username text NOT NULL DEFAULT '',
|
|
password text NOT NULL DEFAULT '',
|
|
from_address text NOT NULL DEFAULT '',
|
|
from_name text NOT NULL DEFAULT 'Minstrel',
|
|
use_tls boolean NOT NULL DEFAULT true,
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
|
CONSTRAINT smtp_config_singleton CHECK (id = true)
|
|
);
|
|
INSERT INTO smtp_config (id) VALUES (true)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
CREATE TABLE password_resets (
|
|
token text PRIMARY KEY,
|
|
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
|
expires_at timestamptz NOT NULL,
|
|
used_at timestamptz
|
|
);
|
|
CREATE INDEX password_resets_user_id_idx ON password_resets (user_id);
|
|
CREATE INDEX password_resets_active_idx
|
|
ON password_resets (expires_at)
|
|
WHERE used_at IS NULL;
|