max / makenotwork

2.6 KB · 46 lines History Blame Raw

1	-- Who exported their followers, and when.
2	--
3	-- `POST /api/export/followers` streams a creator's whole follower and
4	-- project-subscriber set, addresses included, and recorded nothing.
5	-- `site-docs/public/legal/mailing-list-data-processing.md` promises a
6	-- subscriber that an erasure reaches an exported copy, and that promise rested
7	-- on a person remembering who had exported. This is the row that makes it a
8	-- query (`159a7a20`, option b, 2026-08-30).
9	--
10	-- No addresses and no list id. The endpoint is `AuthUser`-scoped and exports
11	-- everything the creator has, so there is no list to name -- and holding a
12	-- second copy of anyone's address to answer a question about copies of
13	-- addresses is the thing this is meant to avoid.
14	--
15	-- The erasure query is: the creators this subscriber follows or subscribes to,
16	-- intersected with the creators who exported after that relationship began.
17	--
18	-- ACCEPTED BLIND SPOT, decided rather than discovered: `db::follows` hard-deletes
19	-- on unfollow, so a subscriber who has already unfollowed leaves no relationship
20	-- row to intersect against and this cannot see them. Answering that needs a
21	-- stored set of exported addresses, hashed or otherwise, and that second copy
22	-- was declined. Do not quietly add one; reopen the decision if the case turns
23	-- up in practice.
24	CREATE TABLE IF NOT EXISTS follower_exports (
25	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
26	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
27	-- What the export was about to hand over. Counted at request time, because
28	-- the export streams from a spawned task and a run that ends partway still
29	-- means addresses left the building -- which is the only thing the erasure
30	-- follow-up cares about.
31	--
32	-- Nullable, and NULL means "this happened and we could not say how big".
33	-- A count that fails to read must not refuse the export -- the creator is
34	-- entitled to their own data, and a privacy record that can block a lawful
35	-- request is the worse failure -- so the row is written either way, and a
36	-- missing number says so rather than claiming zero.
37	follower_count BIGINT CHECK (follower_count IS NULL OR follower_count >= 0),
38	subscriber_count BIGINT CHECK (subscriber_count IS NULL OR subscriber_count >= 0),
39	requested_at TIMESTAMPTZ NOT NULL DEFAULT now()
40	);
41
42	-- The erasure query asks "which of these creators exported since a given
43	-- moment", so the moment is the second column.
44	CREATE INDEX IF NOT EXISTS idx_follower_exports_user
45	ON follower_exports (user_id, requested_at DESC);
46