max / makenotwork

3.3 KB · 66 lines History Blame Raw

1	-- SyncKit Groups: invite links, so onboarding a member is one link instead of a
2	-- two-channel out-of-band exchange.
3	--
4	-- Adding a member needs their account email AND their pasted identity public key
5	-- (`POST /groups/{id}/members`). That is two channels before anything works, and
6	-- it is the first thing a group's second user hits. An invitation carries a
7	-- one-use token: the invitee posts their public key against it instead of the
8	-- admin collecting the key by hand.
9	--
10	-- The server is not a party to the key exchange, and this table is what keeps it
11	-- that way. It records two public keys and a pairing, which is exactly what
12	-- `add_member` already teaches the server. The Group Content Key never appears
13	-- here; the grant is still sealed client-side by the admin, after the admin
14	-- confirms the invitee's key fingerprint. Possession of a link is therefore an
15	-- invitation, not membership.
16	--
17	-- Design: wiki synckit-groups-design, synckit-roadmap ("content always encrypted,
18	-- organization is the product": an invitation is coordination metadata, reachable
19	-- only through named endpoints, never as a general store).
20
21	CREATE TABLE IF NOT EXISTS sync_group_invitations (
22	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
23	group_id UUID NOT NULL REFERENCES sync_groups(id) ON DELETE CASCADE,
24
25	-- SHA-256 of the invite token, hex. The token itself exists only in the link
26	-- the admin sends, so a read of this table hands out no live invitations and
27	-- a leaked backup cannot be redeemed. Unique: the lookup on accept is by
28	-- hash, and two invitations sharing a token would be ambiguous.
29	token_hash TEXT NOT NULL UNIQUE,
30
31	inviter_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
32
33	-- Both set together when the invitee accepts; NULL while the invitation is
34	-- outstanding, because until then it names nobody. The public key is the
35	-- whole point of the round trip: it is what the admin seals the GCK to.
36	invitee_user_id UUID REFERENCES users(id) ON DELETE CASCADE,
37	invitee_pubkey TEXT,
38	accepted_at TIMESTAMPTZ,
39
40	-- Set when the admin has confirmed the fingerprint and sealed the grant.
41	-- Terminal: the token opens nothing afterwards. Redemption is separate from
42	-- acceptance precisely so the admin's confirmation is a real gate rather than
43	-- a formality applied after the fact.
44	redeemed_at TIMESTAMPTZ,
45
46	-- An admin cancelling an invitation they no longer want outstanding.
47	revoked_at TIMESTAMPTZ,
48
49	-- An unredeemed invitation must stop being redeemable on its own. A token
50	-- that lives forever is a standing credential nobody remembers issuing.
51	expires_at TIMESTAMPTZ NOT NULL,
52
53	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
54	);
55
56	-- The admin's pending-invitations panel: newest first, per group.
57	CREATE INDEX IF NOT EXISTS idx_sync_group_invitations_group
58	ON sync_group_invitations (group_id, created_at DESC);
59
60	-- One outstanding invitation per (group, invitee). Partial, so it constrains only
61	-- live acceptances: an invitee who was invited, accepted, and was then removed can
62	-- be invited again, and revoked or redeemed rows never block a fresh invite.
63	CREATE UNIQUE INDEX IF NOT EXISTS idx_sync_group_invitations_pending_invitee
64	ON sync_group_invitations (group_id, invitee_user_id)
65	WHERE invitee_user_id IS NOT NULL AND redeemed_at IS NULL AND revoked_at IS NULL;
66