flagon-io/g1t

Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.

Chat and workspace agents: channels, DMs and named agents you talk to1d ago		1	-- Chat: channels, direct messages, their members and their messages.
		2	-- People and agents are members alike, keyed `user:<id>` or `agent:<id>`.
		3	-- Workspaces are kept by id, so renaming one changes nothing here.
		4
		5	-- A channel, or a direct message (kind `dm`, no name). A direct message's
		6	-- `dm_key` is its members' keys, sorted and joined, so the same people
		7	-- always find the same conversation.
		8	CREATE TABLE channels (
		9	id TEXT PRIMARY KEY,
		10	workspace_id TEXT NOT NULL,
		11	kind TEXT NOT NULL CHECK (kind IN ('channel', 'dm')),
		12	name TEXT,
		13	topic TEXT,
		14	private INTEGER NOT NULL DEFAULT 0,
		15	dm_key TEXT,
		16	created_by TEXT NOT NULL,
		17	created_at TEXT NOT NULL,
		18	archived_at TEXT,
		19	last_message_at TEXT
		20	);
		21
		22	CREATE UNIQUE INDEX channels_by_name ON channels (workspace_id, name) WHERE name IS NOT NULL;
		23	CREATE UNIQUE INDEX channels_by_dm_key ON channels (workspace_id, dm_key) WHERE dm_key IS NOT NULL;
		24	-- Browse channels: a workspace's public ones.
		25	CREATE INDEX channels_by_workspace ON channels (workspace_id, kind, private);
		26
		27	CREATE TABLE channel_members (
		28	channel_id TEXT NOT NULL,
		29	principal TEXT NOT NULL,
		30	role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'member')),
		31	starred INTEGER NOT NULL DEFAULT 0,
		32	muted INTEGER NOT NULL DEFAULT 0,
		33	-- The newest message they have read; everything after it is unread.
		34	last_read_id TEXT,
		35	joined_at TEXT NOT NULL,
		36	PRIMARY KEY (channel_id, principal)
		37	);
		38
		39	-- The sidebar: every channel one member is in.
		40	CREATE INDEX channel_members_by_principal ON channel_members (principal, channel_id);
		41
		42	-- Ids are time-sortable, so ordering by id is ordering by time.
		43	CREATE TABLE messages (
		44	id TEXT PRIMARY KEY,
		45	channel_id TEXT NOT NULL,
		46	author TEXT NOT NULL,
		47	kind TEXT NOT NULL DEFAULT 'text' CHECK (kind IN ('text', 'card')),
		48	body TEXT NOT NULL DEFAULT '',
		49	-- A MessageCard as JSON, for kind `card`.
		50	card TEXT,
		51	-- The handles it @mentions, lowercased, as ` a b ` so one is found with
		52	-- LIKE '% a %' (src/mentions.ts).
		53	mentions TEXT NOT NULL DEFAULT '',
		54	thread_root TEXT,
		55	reply_count INTEGER NOT NULL DEFAULT 0,
		56	last_reply_at TEXT,
		57	created_at TEXT NOT NULL,
		58	edited_at TEXT,
		59	deleted_at TEXT
		60	);
		61
		62	CREATE INDEX messages_by_channel ON messages (channel_id, id);
		63	CREATE INDEX messages_by_thread ON messages (thread_root, id) WHERE thread_root IS NOT NULL;
		64
		65	-- Reactions, for later.
		66	CREATE TABLE reactions (
		67	message_id TEXT NOT NULL,
		68	principal TEXT NOT NULL,
		69	emoji TEXT NOT NULL,
		70	created_at TEXT NOT NULL,
		71	PRIMARY KEY (message_id, principal, emoji)
		72	);
		73
		74	-- Who has been put in a workspace's #general once, so someone who leaves
		75	-- it is not put back (src/index.ts, `sidebar`).
		76	CREATE TABLE general_joined (
		77	workspace_id TEXT NOT NULL,
		78	principal TEXT NOT NULL,
		79	joined_at TEXT NOT NULL,
		80	PRIMARY KEY (workspace_id, principal)
		81	);
		82
		83	-- People chatting is included on every plan, but metered raw, so the
		84	-- daily reconciliation sees what chat really costs: messages written and
		85	-- their bytes, per workspace per UTC day.
		86	CREATE TABLE chat_meter (
		87	workspace_id TEXT NOT NULL,
		88	day TEXT NOT NULL,
		89	messages INTEGER NOT NULL DEFAULT 0,
		90	bytes INTEGER NOT NULL DEFAULT 0,
		91	PRIMARY KEY (workspace_id, day)
		92	);