ywxSd9Xm

1create table if not exists broadcast_members
2(
3    /** Prefer either a UUIDV1, or KSUID [sorted-indexing] **/
4    id                 CHAR(27) PRIMARY KEY,
5    /** Set allocated bytes from https://github.com/segmentio/ksuid/issues/23 **/
6    broadcastId        CHAR(27),
7    /** Should be adjusted to our maximum product name length specifications **/
8    name               VARCHAR(255) NOT NULL,
9    username           VARCHAR(255) NOT NULL,
10    /** Metadata **/
11    profileId          uuid         NOT NULL,
12    /* If for some reason broadcastEndedAt never gets set, we can use this to detect drift w/o relying on external sources like DynamoDB (i.e. month long broadcast) */
13    broadcastStartedAt timestamp    NOT NULL DEFAULT now(),
14    /* Set when the broadcast ends for mark and sweep */
15    broadcastEndedAt   timestamp,
16    /* Managed by weighted tsv trigger (to return most relevant results first */
17    weighted_tsv       tsvector
18);
19
20 
21CREATE FUNCTION broadcast_members_weighted_tsv_trigger() RETURNS trigger AS
22$$
23begin
24    new.weighted_tsv :=
25                setweight(to_tsvector('english', COALESCE(new.name, '')), 'A') ||
26                setweight(to_tsvector('english', COALESCE(new.username, '')), 'B');
27    return new;
28end
29$$ LANGUAGE plpgsql;
30
31 CREATE TRIGGER upd_tsvector
32    BEFORE INSERT OR UPDATE
33    ON broadcast_members
34    FOR EACH ROW
35EXECUTE PROCEDURE broadcast_members_weighted_tsv_trigger();
36
37 
38 CREATE INDEX broadcast_members_weighted_tsv_idx ON broadcast_members USING GIST (weighted_tsv);