-
Notifications
You must be signed in to change notification settings - Fork 3
/
schema.sql
224 lines (203 loc) · 7.62 KB
/
schema.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
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
-- user
CREATE TABLE user_account
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
display_name varchar(255) NOT NULL,
email varchar(320) NOT NULL,
firebase_id varchar(128) NOT NULL,
phone_number varchar(255) NOT NULL,
photo_url text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (firebase_id),
UNIQUE (email)
);
-- permission_type
CREATE TYPE access_permission_type AS ENUM (
'ADMIN',
'MEMBER'
);
-- project
CREATE TABLE project
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name varchar(255) NOT NULL,
description text NOT NULL,
credits decimal NOT NULL DEFAULT 1.0,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
deleted_at timestamptz NULL
);
CREATE INDEX idx_project_name ON project (name) WHERE deleted_at IS NULL;
-- user-project many-to-many
CREATE TABLE user_project
(
user_id uuid NOT NULL,
project_id uuid NOT NULL,
permission access_permission_type NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (user_id, project_id),
FOREIGN KEY (user_id) REFERENCES user_account (id) ON DELETE CASCADE,
FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE CASCADE
);
CREATE INDEX idx_user_project_user_id ON user_project (user_id);
CREATE INDEX idx_user_project_project_id ON user_project (project_id);
-- project-invitation many-to-many
CREATE TABLE project_invitation
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
email varchar(320) NOT NULL,
project_id uuid NOT NULL,
permission access_permission_type NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE CASCADE
);
CREATE UNIQUE INDEX idx_project_invitation_email_project_id ON project_invitation (email, project_id);
-- application
CREATE TABLE application
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
description text NOT NULL,
name varchar(255) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
deleted_at timestamptz NULL,
project_id uuid NOT NULL,
FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE CASCADE
);
CREATE INDEX idx_application_project_id ON application (project_id) WHERE deleted_at IS NULL;
-- model_vendor
CREATE TYPE model_vendor AS ENUM (
'OPEN_AI',
'COHERE'
);
-- model_type
CREATE TYPE model_type AS ENUM (
'gpt-3.5-turbo',
'gpt-3.5-turbo-16k',
'gpt-4',
'gpt-4-32k',
'command',
'command-light',
'command-nightly',
'command-light-nightly'
);
-- prompt-config
CREATE TABLE prompt_config
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name varchar(255) NOT NULL,
model_parameters json NOT NULL,
model_type model_type NOT NULL,
model_vendor model_vendor NOT NULL,
provider_prompt_messages json NOT NULL,
expected_template_variables varchar(255) [] NOT NULL,
is_default boolean NOT NULL DEFAULT TRUE,
is_test_config boolean NOT NULL DEFAULT FALSE,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
deleted_at timestamptz NULL,
application_id uuid NOT NULL,
FOREIGN KEY (application_id) REFERENCES application (id) ON DELETE CASCADE,
UNIQUE (name, application_id)
);
CREATE INDEX idx_prompt_config_application_id ON prompt_config (application_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_prompt_config_is_default ON prompt_config (is_default) WHERE deleted_at IS NULL;
CREATE INDEX idx_prompt_config_created_at ON prompt_config (created_at) WHERE deleted_at IS NULL;
-- provider-model-pricing
-- we intentionally keep this model denormalized because providers can and will change their prices over time.
-- therefore, the pricing of model use are time specific.
CREATE TABLE provider_model_pricing
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
model_type model_type NOT NULL,
model_vendor model_vendor NOT NULL,
input_token_price numeric NOT NULL,
output_token_price numeric NOT NULL,
token_unit_size int NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
active_from_date date NOT NULL DEFAULT current_date,
active_to_date date NULL
);
CREATE INDEX idx_provider_model_pricing_active_from_date ON provider_model_pricing (active_from_date);
CREATE INDEX idx_provider_model_pricing_active_to_date ON provider_model_pricing (active_to_date);
CREATE INDEX idx_provider_model_pricing_model_type ON provider_model_pricing (model_type);
CREATE INDEX idx_provider_model_pricing_model_vendor ON provider_model_pricing (model_vendor);
-- prompt-finish-reason
CREATE TYPE prompt_finish_reason AS ENUM (
'DONE',
'ERROR',
'LIMIT'
);
-- prompt-request-record
CREATE TABLE prompt_request_record
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
is_stream_response boolean NOT NULL DEFAULT FALSE,
request_tokens int NOT NULL,
response_tokens int NOT NULL,
request_tokens_cost numeric NOT NULL,
response_tokens_cost numeric NOT NULL,
start_time timestamptz NOT NULL,
finish_time timestamptz NOT NULL,
finish_reason prompt_finish_reason NOT NULL DEFAULT 'DONE',
duration_ms int NULL,
prompt_config_id uuid NULL,
error_log text NULL,
created_at timestamptz NOT NULL DEFAULT now(),
deleted_at timestamptz NULL,
provider_model_pricing_id uuid NULL,
FOREIGN KEY (provider_model_pricing_id) REFERENCES provider_model_pricing (id) ON DELETE CASCADE,
FOREIGN KEY (prompt_config_id) REFERENCES prompt_config (id) ON DELETE CASCADE
);
CREATE INDEX idx_prompt_request_record_prompt_config_id ON prompt_request_record (
prompt_config_id
) WHERE deleted_at IS NULL;
CREATE INDEX idx_prompt_request_record_pricing_id ON prompt_request_record (
provider_model_pricing_id
) WHERE deleted_at IS NULL;
CREATE INDEX idx_prompt_request_record_start_time ON prompt_request_record (
start_time
) WHERE deleted_at IS NULL;
CREATE INDEX idx_prompt_request_record_finish_time ON prompt_request_record (
finish_time
) WHERE deleted_at IS NULL;
-- prompt-test-record
CREATE TABLE prompt_test_record
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
variable_values json NOT NULL,
response text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
prompt_request_record_id uuid NOT NULL,
FOREIGN KEY (prompt_request_record_id) REFERENCES prompt_request_record (id) ON DELETE CASCADE
);
CREATE INDEX idx_prompt_test_record_prompt_request_record_id ON prompt_test_record (
prompt_request_record_id
);
CREATE INDEX idx_prompt_test_record_created_at ON prompt_test_record (created_at);
-- api-key
CREATE TABLE api_key
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
name varchar(255) NOT NULL,
is_internal boolean NOT NULL DEFAULT FALSE,
created_at timestamptz NOT NULL DEFAULT now(),
deleted_at timestamptz NULL,
application_id uuid NOT NULL,
FOREIGN KEY (application_id) REFERENCES application (id) ON DELETE CASCADE
);
CREATE INDEX idx_api_key_application_id ON api_key (application_id) WHERE deleted_at IS NULL;
-- provider-key
CREATE TABLE provider_key
(
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
model_vendor model_vendor NOT NULL,
encrypted_api_key varchar(255) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
project_id uuid NOT NULL,
FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE CASCADE
);
CREATE INDEX idx_provider_key_project_id ON provider_key (project_id);
CREATE UNIQUE INDEX idx_provider_key_model_vendor_project_id ON provider_key (model_vendor, project_id);