max / makenotwork
| 1 | -- Landing signups gain an unsubscribe state. |
| 2 | -- |
| 3 | -- The table had insert, admin read and count, and no way out. Under GDPR, |
| 4 | -- withdrawing consent has to be as easy as giving it, and giving it is one form |
| 5 | -- field on the landing page. |
| 6 | -- |
| 7 | -- The row is marked rather than deleted. A deleted address is one we would |
| 8 | -- happily re-add on the next import, so the record of the opt-out IS the thing |
| 9 | -- that honours it. Reads that pick recipients filter on unsubscribed_at IS NULL. |
| 10 | -- |
| 11 | -- This is step 1 of the plan in wiki [[mnw-mailing-lists]] and deliberately does |
| 12 | -- not anticipate the lists/list_subscriptions/consent_events tables from step 2. |
| 13 | -- When those land, this column backfills into a subscription state. |
| 14 | email_signups ADD COLUMN IF NOT EXISTS unsubscribed_at TIMESTAMPTZ; |
| 15 | |
| 16 | -- Partial index: every recipient query filters on this, and the unsubscribed |
| 17 | -- rows are the ones we never scan. |
| 18 | NOT EXISTS idx_email_signups_subscribed |
| 19 | ON email_signups (created_at DESC) |
| 20 | WHERE unsubscribed_at IS NULL; |
| 21 |