Blame · Line-by-line history

`0064_chat_integrations.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0064_chat_integrations.sqlBlame51 lines · 1 contributor

Normal view Raw

e3c90cdClaude	1	-- Chat integrations — Slack, Discord, Teams.
	2	--
	3	-- One row per (owner, kind, team, channel) triple. The same Gluecron user can
	4	-- bind multiple workspaces (e.g. their personal Slack + their company Slack)
	5	-- and multiple channels per workspace; uniqueness across all four columns
	6	-- prevents accidental duplicates on a re-install.
	7	--
	8	-- Columns of note:
	9	-- * `kind` — 'slack' \| 'discord' \| 'teams'. Validation lives in
	10	-- src/lib/chat-bot.ts; we keep the DB column free-form
	11	-- so adding a fourth provider only needs a code change.
	12	-- * `team_id` — Slack team_id / Discord guild_id / Teams tenant_id.
	13	-- Nullable for bot installs that don't expose one.
	14	-- * `channel_id` — Per-channel pinning. Nullable for "post to default".
	15	-- * `webhook_url` — Outbound Incoming-Webhook URL (Slack, Discord).
	16	-- For Teams this stores the connector URL.
	17	-- * `signing_secret` — Inbound signature verification key. Slack uses HMAC
	18	-- SHA-256; Discord uses Ed25519 (the public key goes
	19	-- here). Nullable for installs that only push out.
	20	--
	21	-- The unique index uses COALESCE so NULL slots collapse to the empty string,
	22	-- which keeps "one default-channel binding per workspace" enforceable while
	23	-- still letting an admin re-install cleanly.
	24
	25	CREATE TABLE IF NOT EXISTS chat_integrations (
	26	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	27	owner_user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	28	kind text NOT NULL,
	29	team_id text,
	30	channel_id text,
	31	webhook_url text,
	32	signing_secret text,
	33	enabled boolean NOT NULL DEFAULT true,
	34	created_at timestamp NOT NULL DEFAULT now(),
	35	last_used_at timestamp
	36	);
	37
	38	CREATE UNIQUE INDEX IF NOT EXISTS chat_integrations_unique
	39	ON chat_integrations (
	40	owner_user_id,
	41	kind,
	42	COALESCE(team_id, ''),
	43	COALESCE(channel_id, '')
	44	);
	45
	46	CREATE INDEX IF NOT EXISTS chat_integrations_owner
	47	ON chat_integrations (owner_user_id, kind);
	48
	49	CREATE INDEX IF NOT EXISTS chat_integrations_enabled
	50	ON chat_integrations (enabled)
	51	WHERE enabled = true;