CREATE TABLE group_seen ( group_id uuid NOT NULL, user_id uuid NOT NULL, device_id uuid NOT NULL REFERENCES devices(id) ON DELETE CASCADE, sequence bigint NOT NULL CHECK (sequence > 0), PRIMARY KEY (group_id,device_id), FOREIGN KEY (group_id,user_id) REFERENCES group_members(group_id,user_id) ON DELETE CASCADE ); CREATE INDEX group_seen_membership ON group_seen(group_id,user_id);