Files

39 lines
1.7 KiB
SQL

ALTER TABLE group_members ADD COLUMN joined_version bigint;
UPDATE group_members m SET joined_version=g.membership_version
FROM chat_groups g WHERE g.id=m.group_id AND m.state='active';
ALTER TABLE group_members ADD CHECK ((state='active') = (joined_version IS NOT NULL));
CREATE TABLE group_epochs (
group_id uuid NOT NULL REFERENCES chat_groups(id) ON DELETE CASCADE,
id uuid NOT NULL,
membership_version bigint NOT NULL,
creator_device uuid NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
created_at timestamptz NOT NULL DEFAULT now(),
retired_at timestamptz,
PRIMARY KEY (group_id,id)
);
CREATE UNIQUE INDEX group_epochs_current ON group_epochs(group_id,membership_version) WHERE retired_at IS NULL;
CREATE TABLE group_epoch_devices (
group_id uuid NOT NULL,
epoch_id uuid NOT NULL,
device_id uuid NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
welcome bytea CHECK (octet_length(welcome) BETWEEN 16 AND 65536),
PRIMARY KEY (group_id,epoch_id,device_id),
FOREIGN KEY (group_id,epoch_id) REFERENCES group_epochs(group_id,id) ON DELETE CASCADE
);
CREATE TABLE group_messages (
sequence bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
group_id uuid NOT NULL,
epoch_id uuid NOT NULL,
sender_device uuid NOT NULL,
message_id uuid NOT NULL,
ciphertext bytea NOT NULL CHECK (octet_length(ciphertext) BETWEEN 16 AND 65536),
created_at timestamptz NOT NULL DEFAULT now(),
FOREIGN KEY (group_id,epoch_id,sender_device) REFERENCES group_epoch_devices(group_id,epoch_id,device_id) ON DELETE CASCADE,
UNIQUE (group_id,epoch_id,sender_device,message_id)
);
CREATE INDEX group_messages_cursor ON group_messages(group_id,sequence);
CREATE INDEX group_messages_expiry ON group_messages(created_at);