/
schema.ddl
233 lines (218 loc) · 8.31 KB
/
schema.ddl
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
225
226
227
228
229
230
231
232
233
CREATE SEQUENCE IF NOT EXISTS public.event_offset;
CREATE SEQUENCE IF NOT EXISTS public.state_offset;
--
-- Event table for global events (ex: ACLs, permissions, orgs)
--
CREATE TABLE IF NOT EXISTS public.global_events(
ordering bigint NOT NULL DEFAULT nextval('event_offset'),
type text NOT NULL,
id text NOT NULL,
rev integer NOT NULL,
value JSONB NOT NULL,
instant timestamptz NOT NULL,
PRIMARY KEY(type, id, rev)
);
CREATE INDEX IF NOT EXISTS global_events_ordering_idx ON public.global_events (ordering);
--
-- Table for global states (ex: ACLs, permissions, orgs)
--
CREATE TABLE IF NOT EXISTS public.global_states (
ordering bigint NOT NULL DEFAULT nextval('state_offset'),
type text NOT NULL,
id text NOT NULL,
rev integer NOT NULL,
value JSONB NOT NULL,
instant timestamptz NOT NULL,
PRIMARY KEY(type, id)
);
CREATE INDEX IF NOT EXISTS global_states_ordering_idx ON public.global_states (ordering);
CREATE INDEX IF NOT EXISTS org_uuid_idx ON public.global_states((value->>'uuid')) WHERE type = 'organization';
--
-- Table for scoped events that belongs to a project
--
CREATE TABLE IF NOT EXISTS public.scoped_events(
ordering bigint NOT NULL DEFAULT nextval('event_offset'),
type text NOT NULL,
org text NOT NULL,
project text NOT NULL,
id text NOT NULL,
rev integer NOT NULL,
value JSONB NOT NULL,
instant timestamptz NOT NULL,
PRIMARY KEY(org, project, id, rev)
) PARTITION BY LIST (org);
CREATE INDEX IF NOT EXISTS scoped_events_type_idx ON public.scoped_events(type);
CREATE INDEX IF NOT EXISTS scoped_events_ordering_idx ON public.scoped_events (ordering);
--
-- Table for scoped states that belongs to a project
--
CREATE TABLE IF NOT EXISTS public.scoped_states(
ordering bigint NOT NULL DEFAULT nextval('state_offset'),
type text NOT NULL,
org text NOT NULL,
project text NOT NULL,
id text NOT NULL,
tag text NOT NULL,
rev integer NOT NULL,
value JSONB NOT NULL,
deprecated boolean NOT NULL,
instant timestamptz NOT NULL,
PRIMARY KEY(org, project, tag, id)
) PARTITION BY LIST (org);
CREATE INDEX IF NOT EXISTS scoped_states_type_idx ON public.scoped_states(type);
CREATE INDEX IF NOT EXISTS scoped_states_ordering_idx ON public.scoped_states (ordering);
CREATE INDEX IF NOT EXISTS project_uuid_idx ON public.scoped_states((value->>'uuid')) WHERE type = 'project';
--
-- Table for tombstones for scoped states
-- These tombstones are meant to inform streaming operations that a resource
-- has been deleted or doesn't have a certain type anymore so that the client
-- can take the appropriate action
-- The tombstones are deleted by Delta after a configured period
--
CREATE TABLE IF NOT EXISTS public.scoped_tombstones(
-- Primary key based on a sequence 'state_offset' shared so that states and tombstones
-- can be queried in the chronological order
ordering bigint NOT NULL DEFAULT nextval('state_offset'),
-- Identifiers of the resource
type text NOT NULL,
org text NOT NULL,
project text NOT NULL,
id text NOT NULL,
-- Tag of the state the tombstone is associated to
tag text NOT NULL,
-- Cause of the tombstone
cause JSONB NOT NULL,
-- Instant the tombstone was created
instant timestamptz NOT NULL,
PRIMARY KEY(ordering)
);
CREATE INDEX IF NOT EXISTS scoped_tombstones_idx ON public.scoped_tombstones(org, project, tag, id);
CREATE INDEX IF NOT EXISTS scoped_tombstones_deleted_idx ON public.scoped_tombstones((cause->>'deleted'));
--
-- Table for ephemeral scoped states that belongs to a project
--
CREATE TABLE IF NOT EXISTS public.ephemeral_states(
type text NOT NULL,
org text NOT NULL,
project text NOT NULL,
id text NOT NULL,
value JSONB NOT NULL,
instant timestamptz NOT NULL,
expires timestamptz NOT NULL,
PRIMARY KEY(org, project, id)
);
CREATE INDEX IF NOT EXISTS ephemeral_states_type_idx ON public.ephemeral_states(type);
--
-- Table for entity dependencies
--
CREATE TABLE IF NOT EXISTS public.entity_dependencies(
org text NOT NULL,
project text NOT NULL,
id text NOT NULL,
target_org text NOT NULL,
target_project text NOT NULL,
target_id text NOT NULL,
PRIMARY KEY(org, project, id, target_org, target_project, target_id),
CHECK (org != target_org or project != target_project or id != target_id)
);
CREATE INDEX IF NOT EXISTS entity_dependencies_reverse_idx ON public.entity_dependencies(target_org, target_project, target_id);
--
-- Table for projection offsets
--
CREATE TABLE IF NOT EXISTS public.projection_offsets(
name text,
module text,
project text,
resource_id text,
ordering bigint NOT NULL,
processed bigint NOT NULL,
discarded bigint NOT NULL,
failed bigint NOT NULL,
created_at timestamptz NOT NULL,
updated_at timestamptz NOT NULL,
PRIMARY KEY(name)
);
CREATE INDEX IF NOT EXISTS projection_offsets_project_idx ON public.projection_offsets(project);
CREATE INDEX IF NOT EXISTS projection_offsets_resource_id_idx ON public.projection_offsets(resource_id);
--
-- Table for composite offsets
--
CREATE TABLE IF NOT EXISTS public.composite_offsets(
project text NOT NULL,
view_id text NOT NULL,
rev integer NOT NULL,
source_id text NOT NULL,
target_id text NOT NULL,
run text NOT NULL,
ordering bigint NOT NULL,
processed bigint NOT NULL,
discarded bigint NOT NULL,
failed bigint NOT NULL,
created_at timestamptz NOT NULL,
updated_at timestamptz NOT NULL,
PRIMARY KEY(project, view_id, rev, source_id, target_id, run)
);
--
-- Table for projection restarts
--
CREATE TABLE IF NOT EXISTS public.projection_restarts(
ordering bigserial,
name text NOT NULL,
value JSONB NOT NULL,
instant timestamptz NOT NULL,
acknowledged boolean NOT NULL,
PRIMARY KEY(ordering)
);
--
-- Table for composite views restarts
--
CREATE TABLE IF NOT EXISTS public.composite_restarts(
ordering bigserial,
project text,
id text NOT NULL,
value JSONB NOT NULL,
instant timestamptz NOT NULL,
acknowledged boolean NOT NULL,
PRIMARY KEY(ordering)
);
--
-- Table for elem errors
--
CREATE TABLE IF NOT EXISTS public.failed_elem_logs(
ordering bigserial,
projection_name text NOT NULL,
projection_module text NOT NULL,
projection_project text,
projection_id text,
entity_type text NOT NULL,
elem_offset bigint NOT NULL,
elem_id text NOT NULL,
elem_project text,
rev integer NOT NULL,
error_type text NOT NULL,
message text NOT NULL,
stack_trace text NOT NULL,
instant timestamptz DEFAULT NOW(),
PRIMARY KEY(ordering)
);
CREATE INDEX IF NOT EXISTS failed_elem_logs_projection_name_idx ON public.failed_elem_logs(projection_name);
CREATE INDEX IF NOT EXISTS failed_elem_logs_projection_idx ON public.failed_elem_logs(projection_project, projection_id);
--
-- Table for project deletion result
--
CREATE TABLE IF NOT EXISTS public.deleted_project_reports(
ordering bigserial,
value JSONB NOT NULL,
PRIMARY KEY(ordering)
);
CREATE TABLE IF NOT EXISTS public.blazegraph_queries (
ordering bigserial,
project text NOT NULL,
view_id text NOT NULL,
instant timestamptz NOT NULL,
duration integer NOT NULL,
subject JSONB NOT NULL,
query text NOT NULL,
failed boolean NOT NULL,
PRIMARY KEY (ordering)
);