-
Notifications
You must be signed in to change notification settings - Fork 0
/
commands.sql
156 lines (133 loc) · 5.13 KB
/
commands.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
CREATE TABLE companies (
id UUID PRIMARY KEY default uuid_generate_v4(),
name VARCHAR(255) NOT NULL,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
CREATE TABLE apps (
id UUID PRIMARY KEY default uuid_generate_v4(),
name VARCHAR(255) NOT NULL,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
CREATE TABLE api_keys (
id UUID PRIMARY KEY default uuid_generate_v4(),
name VARCHAR(255) NOT NULL,
api_key VARCHAR(255) NOT NULL,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
CREATE TABLE users (
id UUID PRIMARY KEY default uuid_generate_v4(),
display_name VARCHAR(255) NOT NULL,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
CREATE TABLE channels (
id UUID PRIMARY KEY default uuid_generate_v4(),
name VARCHAR(255) NOT NULL,
last_message TEXT,
owner_user_id UUID REFERENCES users(id) on delete set null,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
CREATE TABLE messages (
id UUID PRIMARY KEY default uuid_generate_v4(),
message TEXT NOT NULL,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
CREATE TABLE developers (
id UUID PRIMARY KEY default uuid_generate_v4(),
display_name VARCHAR(255) NOT NULL,
created_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null
);
CREATE TABLE company_developer (
id UUID PRIMARY KEY default uuid_generate_v4(),
company_id UUID REFERENCES companies(id) on delete cascade,
developer_owner_id UUID REFERENCES developers(id) on delete cascade
);
CREATE TABLE app_user (
id UUID PRIMARY KEY default uuid_generate_v4(),
user_id UUID REFERENCES users(id) on delete cascade,
app_id UUID REFERENCES apps(id) on delete cascade,
external_user_id VARCHAR(255) NOT NULL,
unique(app_id, external_user_id)
);
CREATE TABLE developer_app (
id UUID PRIMARY KEY default uuid_generate_v4(),
developer_id UUID REFERENCES developers(id) on delete cascade,
app_id UUID REFERENCES apps(id) on delete cascade
);
CREATE TABLE company_app (
id UUID PRIMARY KEY default uuid_generate_v4(),
company_id UUID REFERENCES companies(id) on delete cascade,
app_id UUID REFERENCES apps(id) on delete cascade
);
CREATE TABLE app_channel (
id UUID PRIMARY KEY default uuid_generate_v4(),
channel_id UUID REFERENCES channels(id) on delete cascade,
app_id UUID REFERENCES apps(id) on delete cascade
);
CREATE TABLE api_key_app (
id UUID PRIMARY KEY default uuid_generate_v4(),
api_key_id UUID REFERENCES api_keys(id) on delete set null,
app_id UUID REFERENCES apps(id) on delete cascade
);
CREATE TABLE api_key_developer (
id UUID PRIMARY KEY default uuid_generate_v4(),
api_key_id UUID REFERENCES api_keys(id) on delete set null,
developer_id UUID REFERENCES developers(id) on delete cascade
);
CREATE TABLE channel_user (
id UUID PRIMARY KEY default uuid_generate_v4(),
user_id UUID REFERENCES users(id) on delete set null,
channel_id UUID REFERENCES channels(id) on delete cascade
unique(channel_id, user_id)
);
CREATE TABLE channel_message (
id UUID PRIMARY KEY default uuid_generate_v4(),
message_id UUID REFERENCES messages(id) on delete set null,
channel_id UUID REFERENCES channels(id) on delete cascade
);
CREATE TABLE user_message (
id UUID PRIMARY KEY default uuid_generate_v4(),
message_id UUID REFERENCES messages(id) on delete set null,
user_id UUID REFERENCES users(id) on delete cascade
);
CREATE OR REPLACE FUNCTION trigger_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = timezone('utc'::text, now());
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON apps
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON api_keys
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON companies
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON channels
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON messages
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();
CREATE TRIGGER set_timestamp
BEFORE UPDATE ON developers
FOR EACH ROW
EXECUTE PROCEDURE trigger_set_timestamp();