| 1 | -- Public channels and guests (docs.g1t.sh/guides/chat/, "Public channels" |
| 2 | -- and "Guests"). |
| 3 | -- |
| 4 | -- A public channel is open to anyone signed in to g1t, not only the |
| 5 | -- workspace's members: they read it, join it and talk in it. Only a |
| 6 | -- channel that is not private can be public. |
| 7 | ALTER TABLE channels ADD COLUMN public INTEGER NOT NULL DEFAULT 0; |
| 8 | |
| 9 | -- Someone outside the workspace asked into one of its channels: a guest. |
| 10 | -- They join it when they accept; until then the channel is not theirs to |
| 11 | -- read. One open invitation per person and channel. |
| 12 | CREATE TABLE channel_invitations ( |
| 13 | id TEXT PRIMARY KEY, |
| 14 | channel_id TEXT NOT NULL, |
| 15 | workspace_id TEXT NOT NULL, |
| 16 | -- 'user:<id>' |
| 17 | invitee TEXT NOT NULL, |
| 18 | invited_by TEXT NOT NULL, |
| 19 | created_at TEXT NOT NULL, |
| 20 | answered_at TEXT, |
| 21 | -- 1 accepted, 0 declined or revoked; null while open. |
| 22 | accepted INTEGER |
| 23 | ); |
| 24 | CREATE UNIQUE INDEX channel_invitations_open ON channel_invitations (channel_id, invitee) WHERE answered_at IS NULL; |
| 25 | CREATE INDEX channel_invitations_by_invitee ON channel_invitations (invitee, answered_at); |
| 26 | CREATE INDEX channel_invitations_by_channel ON channel_invitations (channel_id, accepted); |
| 27 | |
| 28 | -- Someone asking to be let into a channel they cannot open: a private one, |
| 29 | -- or one kept to the workspace when they are not in it. Whoever could add |
| 30 | -- them answers. Asked by the channel's name from its link, and stored only |
| 31 | -- when there is such a channel, so asking says nothing of one that is not |
| 32 | -- theirs to see. One open request per person and channel. |
| 33 | CREATE TABLE channel_requests ( |
| 34 | id TEXT PRIMARY KEY, |
| 35 | channel_id TEXT NOT NULL, |
| 36 | workspace_id TEXT NOT NULL, |
| 37 | -- 'user:<id>' |
| 38 | requester TEXT NOT NULL, |
| 39 | note TEXT, |
| 40 | created_at TEXT NOT NULL, |
| 41 | answered_at TEXT, |
| 42 | answered_by TEXT, |
| 43 | -- 1 let in, 0 turned down or withdrawn; null while open. |
| 44 | approved INTEGER |
| 45 | ); |
| 46 | CREATE UNIQUE INDEX channel_requests_open ON channel_requests (channel_id, requester) WHERE answered_at IS NULL; |
| 47 | CREATE INDEX channel_requests_by_channel ON channel_requests (channel_id, answered_at); |
| 48 | |
| 49 | -- People removed from a channel by its owners or the workspace's: someone |
| 50 | -- outside the workspace removed from a public channel cannot join it again |
| 51 | -- on their own. Asking them in again lifts it. |
| 52 | CREATE TABLE channel_removals ( |
| 53 | channel_id TEXT NOT NULL, |
| 54 | principal TEXT NOT NULL, |
| 55 | removed_by TEXT NOT NULL, |
| 56 | removed_at TEXT NOT NULL, |
| 57 | PRIMARY KEY (channel_id, principal) |
| 58 | ); |