| 1 | -- A workspace's own emoji, and who may add them. Reactions use the |
| 2 | -- `reactions` table from 0001. |
| 3 | |
| 4 | -- An emoji's image is kept by the SHA-256 of its bytes in the avatars KV |
| 5 | -- namespace, under `emoji/<file>`, and served from the usercontent origin |
| 6 | -- at `/emoji/<file>`. An alias names another emoji and shows its image, so |
| 7 | -- it keeps that emoji's file too. A removed emoji keeps its row, so its |
| 8 | -- name can be used again and what it was stays on record. |
| 9 | CREATE TABLE custom_emoji ( |
| 10 | workspace_id TEXT NOT NULL, |
| 11 | name TEXT NOT NULL, |
| 12 | alias_of TEXT, |
| 13 | file TEXT NOT NULL, |
| 14 | content_type TEXT NOT NULL CHECK (content_type IN ('image/png', 'image/gif', 'image/webp')), |
| 15 | bytes INTEGER NOT NULL, |
| 16 | created_by TEXT NOT NULL, |
| 17 | created_at TEXT NOT NULL, |
| 18 | deleted_at TEXT |
| 19 | ); |
| 20 | |
| 21 | -- One live emoji per name in a workspace; the picker lists them by name. |
| 22 | CREATE UNIQUE INDEX custom_emoji_by_name ON custom_emoji (workspace_id, name) WHERE deleted_at IS NULL; |
| 23 | |
| 24 | -- A workspace's chat settings. No row: the defaults. |
| 25 | CREATE TABLE chat_settings ( |
| 26 | workspace_id TEXT PRIMARY KEY, |
| 27 | -- Who may add emoji: every member, or only owners. |
| 28 | emoji_upload TEXT NOT NULL DEFAULT 'members' CHECK (emoji_upload IN ('members', 'admins')) |
| 29 | ); |
| 30 | |
| 31 | -- Whether any live emoji still shows an image, before it is forgotten. |
| 32 | CREATE INDEX custom_emoji_by_file ON custom_emoji (file) WHERE deleted_at IS NULL; |