| 1 | -- SPDX-License-Identifier: AGPL-3.0-or-later |
| 2 | -- |
| 3 | -- S34 — Site admin. Two pieces: |
| 4 | -- |
| 5 | -- 1. users.is_site_admin — flips a user into the site-admin role. |
| 6 | -- The first admin is bootstrapped via the |
| 7 | -- `shithubd admin bootstrap-admin <username>` CLI subcommand; |
| 8 | -- subsequent toggles happen in the /admin/users/{id} UI. |
| 9 | -- |
| 10 | -- 2. transactional_email_log — sibling to S29's notification_email_log. |
| 11 | -- Captures auth resets, 2FA notices, transfer-offer emails, etc. |
| 12 | -- so the admin email-queue view can surface delivery health for |
| 13 | -- non-notification mail. We log envelope metadata and delivery |
| 14 | -- status; the message body is intentionally NOT persisted (PII |
| 15 | -- containment + retention story is simpler that way). |
| 16 | -- |
| 17 | -- Impersonation state lives in the session cookie itself (S02 cookie |
| 18 | -- store, not a DB row), so this migration doesn't touch sessions. |
| 19 | |
| 20 | -- +goose Up |
| 21 | ALTER TABLE users ADD COLUMN is_site_admin boolean NOT NULL DEFAULT false; |
| 22 | CREATE INDEX users_site_admin_idx ON users (is_site_admin) WHERE is_site_admin = true; |
| 23 | |
| 24 | CREATE TYPE transactional_email_status AS ENUM ( |
| 25 | 'queued', 'sent', 'soft_bounced', 'hard_bounced', 'dropped' |
| 26 | ); |
| 27 | |
| 28 | CREATE TABLE transactional_email_log ( |
| 29 | id bigserial PRIMARY KEY, |
| 30 | -- recipient_user_id is nullable so unsubscribed-recipient flows |
| 31 | -- (e.g. transfer offer to a username that hasn't signed up) still |
| 32 | -- get logged for the audit trail. |
| 33 | recipient_user_id bigint REFERENCES users(id) ON DELETE SET NULL, |
| 34 | recipient_email citext NOT NULL, |
| 35 | -- kind groups events for the admin filter: 'password_reset', |
| 36 | -- 'verify_email', 'admin_cleared_2fa', 'transfer_offer', etc. |
| 37 | kind text NOT NULL, |
| 38 | subject text NOT NULL, |
| 39 | -- Provider-supplied id when the backend exposes one (Postmark |
| 40 | -- MessageID); empty string for stdout/SMTP backends. |
| 41 | provider_id text NOT NULL DEFAULT '', |
| 42 | status transactional_email_status NOT NULL DEFAULT 'queued', |
| 43 | -- error_summary captures the failure reason for the bounce/drop |
| 44 | -- triage path. Cleared on successful re-send. |
| 45 | error_summary text, |
| 46 | sent_at timestamptz NOT NULL DEFAULT now(), |
| 47 | delivered_at timestamptz, |
| 48 | |
| 49 | CONSTRAINT transactional_email_log_kind_length CHECK (char_length(kind) BETWEEN 1 AND 64), |
| 50 | CONSTRAINT transactional_email_log_subject_length CHECK (char_length(subject) BETWEEN 1 AND 500) |
| 51 | ); |
| 52 | |
| 53 | -- Admin "recent failures" surface: scan failed rows in time order. |
| 54 | CREATE INDEX transactional_email_log_status_idx |
| 55 | ON transactional_email_log (status, sent_at DESC) |
| 56 | WHERE status IN ('soft_bounced', 'hard_bounced', 'dropped'); |
| 57 | |
| 58 | -- Admin filter by kind + recency. |
| 59 | CREATE INDEX transactional_email_log_kind_sent_idx |
| 60 | ON transactional_email_log (kind, sent_at DESC); |
| 61 | |
| 62 | -- +goose Down |
| 63 | DROP TABLE IF EXISTS transactional_email_log; |
| 64 | DROP TYPE IF EXISTS transactional_email_status; |
| 65 | DROP INDEX IF EXISTS users_site_admin_idx; |
| 66 | ALTER TABLE users DROP COLUMN IF EXISTS is_site_admin; |
| 67 |