mirror of
https://github.com/block/buzz.git
synced 2026-10-28 04:51:40 +00:00
55 lines
2.9 KiB
SQL
55 lines
2.9 KiB
SQL
-- ── Channels ──────────────────────────────────────────────────────────────────
|
|
-- Conformance: "Channels and channel membership". `community_id` immutable.
|
|
-- Channel UUIDs stay valid wire identifiers, but they are NOT globally unique:
|
|
-- the PK is `(community_id, id)`, so the same UUID may legitimately exist in two
|
|
-- communities (conformance lists "same channel UUID collision in two
|
|
-- communities" as a required isolation test). Handlers always carry `ctx`, so
|
|
-- `(ctx.community, h)` names exactly one channel; a client-supplied `h` can
|
|
-- never reach another community's channel.
|
|
|
|
CREATE TABLE channels (
|
|
id UUID NOT NULL DEFAULT gen_random_uuid(),
|
|
community_id UUID NOT NULL REFERENCES communities(id),
|
|
name VARCHAR(255) NOT NULL,
|
|
channel_type channel_type NOT NULL DEFAULT 'stream',
|
|
visibility channel_visibility NOT NULL DEFAULT 'open',
|
|
description TEXT,
|
|
canvas TEXT,
|
|
created_by BYTEA NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
archived_at TIMESTAMPTZ,
|
|
deleted_at TIMESTAMPTZ,
|
|
nip29_group_id VARCHAR(255),
|
|
topic_required BOOLEAN NOT NULL DEFAULT FALSE,
|
|
max_members INT,
|
|
topic TEXT,
|
|
topic_set_by BYTEA,
|
|
topic_set_at TIMESTAMPTZ,
|
|
purpose TEXT,
|
|
purpose_set_by BYTEA,
|
|
purpose_set_at TIMESTAMPTZ,
|
|
participant_hash BYTEA,
|
|
ttl_seconds INT,
|
|
ttl_deadline TIMESTAMPTZ,
|
|
PRIMARY KEY (community_id, id),
|
|
CONSTRAINT chk_channels_id_not_nil CHECK (id <> '00000000-0000-0000-0000-000000000000'::uuid)
|
|
);
|
|
|
|
-- nip29 group id and DM participant hash are unique WITHIN a community, not globally.
|
|
CREATE UNIQUE INDEX idx_channels_nip29_group ON channels (community_id, nip29_group_id)
|
|
WHERE nip29_group_id IS NOT NULL;
|
|
CREATE UNIQUE INDEX idx_channels_dm_hash ON channels (community_id, participant_hash)
|
|
WHERE participant_hash IS NOT NULL;
|
|
CREATE INDEX idx_channels_community_type ON channels (community_id, channel_type);
|
|
CREATE INDEX idx_channels_community_visibility ON channels (community_id, visibility);
|
|
CREATE INDEX idx_channels_created_by ON channels (community_id, created_by);
|
|
CREATE INDEX idx_channels_ttl_expiry ON channels (ttl_deadline)
|
|
WHERE ttl_seconds IS NOT NULL AND archived_at IS NULL AND deleted_at IS NULL;
|
|
-- Tenant-independent channel-id → community lookups (Db::communities_of_channels,
|
|
-- Db::community_of_channel) carry no community_id predicate, so no
|
|
-- community_id-leading index can serve them. Covering + partial: index-only scan.
|
|
-- Not UNIQUE — the same channel id may exist under more than one community.
|
|
CREATE INDEX idx_channels_id_live ON channels (id) INCLUDE (community_id)
|
|
WHERE deleted_at IS NULL;
|