2 channel VARCHAR NOT NULL,
5 color VARCHAR NOT NULL DEFAULT 'yellow',
6 PRIMARY KEY (channel, ts, name)
9 CREATE TABLE reaction_log (
10 userid VARCHAR NOT NULL,
11 channel VARCHAR NOT NULL,
13 event_type VARCHAR NOT NULL,
14 event_ts VARCHAR NOT NULL,
15 reaction VARCHAR NOT NULL
17 CREATE INDEX lookup_idx ON reaction_log USING btree (channel, ts, userid, reaction, event_ts DESC);
19 CREATE TABLE message_sheet_link (
20 channel VARCHAR NOT NULL,
22 sheet_title VARCHAR NOT NULL,
23 PRIMARY KEY (channel, ts)
26 CREATE TABLE users_nagged (
27 userid VARCHAR NOT NULL,
28 last_nag TIMESTAMP WITH TIME ZONE NOT NULL,
32 CREATE VIEW current_reactions AS
33 SELECT userid, channel, ts, reaction
34 FROM ( SELECT DISTINCT ON (channel, ts, userid, reaction)
35 userid, channel, ts, event_type, event_ts, reaction
36 FROM reaction_log ORDER BY channel, ts, userid, reaction, event_ts DESC ) t1
37 WHERE event_type = 'reaction_added';
39 GRANT SELECT,UPDATE,INSERT,DELETE ON TABLE applied TO skvidarlang;
40 GRANT SELECT,INSERT ON TABLE reaction_log TO skvidarlang;
41 GRANT SELECT ON TABLE current_reactions TO skvidarlang;
42 GRANT SELECT,UPDATE,INSERT ON TABLE message_sheet_link TO skvidarlang;
43 GRANT SELECT,INSERT ON TABLE users_nagged TO skvidarlang;