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 tab_name VARCHAR, -- Cache; blown away every time we see a new message.
24 tab_id VARCHAR, -- Ditto.
25 PRIMARY KEY (channel, ts)
28 CREATE TABLE users_nagged (
29 userid VARCHAR NOT NULL,
30 ts VARCHAR NOT NULL, -- Of the invitation.
31 last_nag TIMESTAMP WITH TIME ZONE NOT NULL,
32 PRIMARY KEY (userid, ts)
35 CREATE TABLE group_membership_history (
36 -- (channel, ts) says what event we saw them at (even if they were not actually attending).
37 channel VARCHAR NOT NULL,
39 name VARCHAR NOT NULL, -- As written in the spreadsheet.
40 change_seen TIMESTAMP WITH TIME ZONE NOT NULL,
43 CREATE INDEX gmh_lookup_idx ON group_membership_history (channel, ts, name, change_seen DESC);
45 CREATE TABLE oauth_tokens (
46 token VARCHAR NOT NULL,
47 acquired TIMESTAMP WITH TIME ZONE NOT NULL,
48 expiry TIMESTAMP WITH TIME ZONE NOT NULL
50 CREATE INDEX oauth_tokens_exp_idx ON oauth_tokens ( expiry );
52 CREATE VIEW current_reactions AS
53 SELECT userid, channel, ts, reaction
54 FROM ( SELECT DISTINCT ON (channel, ts, userid, reaction)
55 userid, channel, ts, event_type, event_ts, reaction
56 FROM reaction_log ORDER BY channel, ts, userid, reaction, event_ts DESC ) t1
57 WHERE event_type = 'reaction_added';
59 CREATE VIEW current_group_membership_history AS
60 SELECT DISTINCT ON (channel,ts,name)
61 channel, ts, name, group_name, change_seen
62 FROM group_membership_history
63 ORDER BY channel, ts, name,change_seen DESC;
65 GRANT SELECT,UPDATE,INSERT,DELETE ON TABLE applied TO skvidarlang;
66 GRANT SELECT,INSERT ON TABLE reaction_log TO skvidarlang;
67 GRANT SELECT ON TABLE current_reactions TO skvidarlang;
68 GRANT SELECT,UPDATE,INSERT ON TABLE message_sheet_link TO skvidarlang;
69 GRANT SELECT,INSERT ON TABLE users_nagged TO skvidarlang;
70 GRANT SELECT,INSERT ON TABLE group_membership_history TO skvidarlang;
71 GRANT SELECT ON TABLE current_group_membership_history TO skvidarlang;
72 GRANT SELECT,INSERT ON TABLE oauth_tokens TO skvidarlang;