1CREATE OR REPLACE FUNCTION trigger_set_timestamp() RETURNS TRIGGER
2LANGUAGE plpgsql AS $func$
3BEGIN
4 NEW.updated_at = NOW();
5 RETURN NEW;
6END $func$;
7
8-- tbl artifact_versions
9CREATE TABLE IF NOT EXISTS artifact_versions (
10 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
11 image TEXT NOT NULL,
12 tag TEXT,
13 sha256_digest TEXT NOT NULL CHECK (length(sha256_digest) = 64),
14 UNIQUE (image, sha256_digest),
15 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
16 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
17);
18
19CREATE OR REPLACE TRIGGER artifact_versions_timestamp BEFORE
20UPDATE ON artifact_versions FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
21
22-- fn_artifact_version_id_for gets the unique id for a new or existing artifact_version. if two callers try to insert the same record
23-- at the same time, the conflict is resolved by retrying, ie the second caller will find the existing id instead
24CREATE OR REPLACE FUNCTION fn_artifact_version_id_for(_artifact UUID, _tag TEXT , _sha256_digest TEXT, OUT _artifact_version_id UUID)
25LANGUAGE plpgsql AS $func$
26BEGIN
27 LOOP
28 SELECT id
29 FROM artifact_versions
30 WHERE artifact = _artifact
31 INTO _artifact_version_id
32 FOR SHARE; -- lock row until end of tx
33 EXIT WHEN FOUND;
34
35 INSERT INTO artifact_versions (artifact, tag, sha256_digest)
36 VALUES (_artifact, _tag, _sha256_digest)
37 ON CONFLICT (artifact, sha256_digest) DO UPDATE SET id = excluded.id WHERE false
38 RETURNING id
39 INTO _artifact_version_id;
40 EXIT WHEN FOUND;
41 END LOOP;
42END $func$;
43
44-- tbl artifacts
45CREATE TABLE IF NOT EXISTS artifacts (
46 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
47 project TEXT,
48 repository TEXT,
49 artifact_version UUID NOT NULL REFERENCES artifact_versions(id) ON DELETE RESTRICT, -- prevent delete of artifact_version if it is still present in a registry
50 UNIQUE (project, repository, artifact_version),
51 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
52 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
53);
54
55CREATE OR REPLACE TRIGGER artifacts_timestamp BEFORE
56UPDATE ON artifacts FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
57
58-- fn_artifact_id_for gets the unique id for a new or existing artifact. if two callers try to insert the same record
59-- at the same time, the conflict is resolved by retrying, ie the second caller will find the existing id instead
60CREATE OR REPLACE FUNCTION fn_artifact_id_for(_image TEXT, OUT _artifact_id UUID)
61LANGUAGE plpgsql AS $func$
62BEGIN
63 LOOP
64 SELECT id
65 FROM artifacts
66 WHERE image = _image
67 INTO _artifact_id
68 FOR SHARE; -- lock row until end of tx
69 EXIT WHEN FOUND;
70
71 INSERT INTO artifacts (image)
72 VALUES (_image)
73 ON CONFLICT (image) DO UPDATE SET id = excluded.id WHERE false
74 RETURNING id
75 INTO _artifact_id;
76 EXIT WHEN FOUND;
77 END LOOP;
78END $func$;
79
80-- tbl clusters
81CREATE TABLE IF NOT EXISTS clusters (
82 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
83 name TEXT,
84 endpoint TEXT,
85 version_major INT NOT NULL,
86 version_minor INT NOT NULL,
87 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
88 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
89);
90
91CREATE OR REPLACE TRIGGER clusters_timestamp BEFORE
92UPDATE ON clusters FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
93
94-- tbl artifact_installations
95CREATE TABLE IF NOT EXISTS artifact_installations (
96 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
97 cluster UUID NOT NULL REFERENCES clusters(id),
98 artifact_version UUID NOT NULL REFERENCES artifact_versions(id),
99 UNIQUE (cluster, artifact_version),
100 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
101 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
102);
103
104CREATE OR REPLACE TRIGGER artifact_installations_timestamp BEFORE
105UPDATE ON artifact_installations FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
106
107-- tbl config_sources
108CREATE TABLE IF NOT EXISTS config_sources (
109 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
110 name TEXT,
111 url TEXT,
112 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
113 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
114);
115
116CREATE OR REPLACE TRIGGER config_sources_timestamp BEFORE
117UPDATE ON config_sources FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
118
119-- tbl synced_clusters
120CREATE TABLE IF NOT EXISTS synced_clusters (
121 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
122 cluster UUID NOT NULL REFERENCES clusters(id),
123 config_source UUID NOT NULL REFERENCES config_sources(id),
124 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
125 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
126);
127
128CREATE OR REPLACE TRIGGER synced_clusters_timestamp BEFORE
129UPDATE ON synced_clusters FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
130
131-- tbl observed_states
132CREATE TABLE IF NOT EXISTS observed_states (
133 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
134 observed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
135 cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
136 artifact_version UUID NOT NULL REFERENCES artifact_versions(id) ON DELETE CASCADE,
137 UNIQUE (cluster, artifact_version),
138 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
139 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
140);
141
142CREATE OR REPLACE TRIGGER observed_states_timestamp BEFORE
143UPDATE ON observed_states FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
144
145-- tbl desired_states
146CREATE TABLE IF NOT EXISTS desired_states (
147 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
148 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
149 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
150);
151
152CREATE OR REPLACE TRIGGER desired_states_timestamp BEFORE
153UPDATE ON desired_states FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
154
155-- tbl watched_resources
156CREATE TABLE IF NOT EXISTS watched_resources (
157 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
158 cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
159 api_version TEXT NOT NULL,
160 kind TEXT NOT NULL,
161 resource JSONB NOT NULL,
162 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
163 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
164);
165
166CREATE UNIQUE INDEX IF NOT EXISTS idx_uid ON watched_resources ((resource ['metadata'] ['uid']));
167
168CREATE INDEX IF NOT EXISTS idx_gvknn ON watched_resources (
169 cluster,
170 (resource ['apiVersion']),
171 (resource ['kind']),
172 (resource ['metadata'] ['namespace']),
173 (resource ['metadata'] ['name'])
174);
175
176CREATE INDEX IF NOT EXISTS idx_gin ON watched_resources USING GIN (resource);
177
178CREATE OR REPLACE TRIGGER watched_resources_timestamp BEFORE
179UPDATE ON watched_resources FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
180
181-- tbl remote_commands
182CREATE TABLE IF NOT EXISTS remote_commands (
183 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
184 cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
185 command_type TEXT NOT NULL,
186 command_args TEXT NOT NULL DEFAULT '',
187 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
188 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
189);
190
191CREATE INDEX IF NOT EXISTS idx_cluster ON remote_commands (cluster);
192
193CREATE OR REPLACE TRIGGER remote_commands_timestamp BEFORE
194UPDATE ON remote_commands FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
195
196
197-- TODO(dk185217): experimental. this is essentially just the complete history, append only. it surely wont
198-- scale, but should provide interesting data early
199-- tbl watched_resource_observations
200CREATE TABLE IF NOT EXISTS watched_resource_observations (
201 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
202 cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
203 api_version TEXT NOT NULL,
204 kind TEXT NOT NULL,
205 resource JSONB NOT NULL,
206 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
207 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
208);
209
210CREATE INDEX IF NOT EXISTS idx_uid ON watched_resource_observations ((resource ['metadata'] ['uid']));
211CREATE INDEX IF NOT EXISTS idx_gvknn ON watched_resource_observations (
212 cluster,
213 (resource ['apiVersion']),
214 (resource ['kind']),
215 (resource ['metadata'] ['namespace']),
216 (resource ['metadata'] ['name'])
217);
218
219CREATE OR REPLACE TRIGGER watched_resource_observations_timestamp BEFORE
220UPDATE ON watched_resource_observations FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
221
222-- tbl cluster_labels
223CREATE TABLE IF NOT EXISTS cluster_labels (
224 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
225 cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
226 key TEXT NOT NULL,
227 value TEXT NOT NULL,
228 UNIQUE (cluster, key),
229 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
230 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
231);
232
233CREATE INDEX IF NOT EXISTS idx_cluster_id_inc_key_value ON cluster_labels (cluster) INCLUDE (key, value);
234CREATE INDEX IF NOT EXISTS idx_key_value ON cluster_labels (key, value);
235
236CREATE OR REPLACE TRIGGER cluster_labels_timestamp BEFORE
237UPDATE ON cluster_labels FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
238
239-- tbl artifact_labels
240CREATE TABLE IF NOT EXISTS artifact_labels (
241 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
242 artifact UUID NOT NULL REFERENCES artifacts(id) ON DELETE CASCADE,
243 key TEXT NOT NULL,
244 value TEXT NOT NULL,
245 UNIQUE (artifact, key),
246 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
247 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
248);
249
250CREATE INDEX IF NOT EXISTS idx_artifact_id_inc_key_value ON artifact_labels (artifact) INCLUDE (key, value);
251
252CREATE OR REPLACE TRIGGER artifact_labels_timestamp BEFORE
253UPDATE ON artifact_labels FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
254
255-- tbl kinform_pubsub_subscriptions
256-- TODO(dk18517): this table is currently a temp. cache, drop at startup, but not in future
257DROP TABLE IF EXISTS kinform_pubsub_subscriptions;
258CREATE TABLE IF NOT EXISTS kinform_pubsub_subscriptions (
259 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
260 subscription TEXT NOT NULL,
261 -- TODO(dk18517): ref. int. via fk on projects
262 project TEXT NOT NULL,
263 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
264 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
265);
266
267CREATE UNIQUE INDEX IF NOT EXISTS idx_uid ON kinform_pubsub_subscriptions (subscription, project);
268
269CREATE OR REPLACE TRIGGER kinform_pubsub_subscriptions_timestamp BEFORE
270UPDATE ON kinform_pubsub_subscriptions FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
271
272-- tbl kinform_sessions
273CREATE TABLE IF NOT EXISTS kinform_sessions (
274 id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
275 cluster UUID NOT NULL REFERENCES clusters(id) ON DELETE CASCADE,
276 session UUID NOT NULL UNIQUE,
277 last_heartbeat TIMESTAMPTZ NOT NULL DEFAULT NOW(),
278 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
279 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
280);
281
282CREATE INDEX IF NOT EXISTS idx_last_heartbeat ON kinform_sessions (last_heartbeat);
283
284CREATE OR REPLACE TRIGGER kinform_sessions_timestamp BEFORE
285UPDATE ON kinform_sessions FOR EACH ROW EXECUTE PROCEDURE trigger_set_timestamp();
View as plain text