Mirror of https://github.com/roostorg/coop
github.com/roostorg/coop
1--
2-- PostgreSQL database dump
3--
4
5-- Dumped from database version 15.4 (Debian 15.4-2.pgdg120+1)
6-- Dumped by pg_dump version 15.4 (Debian 15.4-2.pgdg120+1)
7
8SET statement_timeout = 0;
9SET lock_timeout = 0;
10SET idle_in_transaction_session_timeout = 0;
11SET client_encoding = 'UTF8';
12SET standard_conforming_strings = on;
13SET check_function_bodies = false;
14SET xmloption = content;
15SET client_min_messages = warning;
16SET row_security = off;
17
18--
19-- Name: jobs; Type: SCHEMA; Schema: -; Owner: postgres
20--
21
22CREATE SCHEMA jobs;
23
24
25ALTER SCHEMA jobs OWNER TO CURRENT_USER;
26
27--
28-- Name: manual_review_tool; Type: SCHEMA; Schema: -; Owner: postgres
29--
30
31CREATE SCHEMA manual_review_tool;
32
33
34ALTER SCHEMA manual_review_tool OWNER TO CURRENT_USER;
35
36--
37-- Name: models_service; Type: SCHEMA; Schema: -; Owner: postgres
38--
39
40CREATE SCHEMA models_service;
41
42
43ALTER SCHEMA models_service OWNER TO CURRENT_USER;
44
45--
46-- Name: ncmec_reporting; Type: SCHEMA; Schema: -; Owner: postgres
47--
48
49CREATE SCHEMA ncmec_reporting;
50
51
52ALTER SCHEMA ncmec_reporting OWNER TO CURRENT_USER;
53
54--
55-- Name: reporting_rules; Type: SCHEMA; Schema: -; Owner: postgres
56--
57
58CREATE SCHEMA reporting_rules;
59
60
61ALTER SCHEMA reporting_rules OWNER TO CURRENT_USER;
62
63--
64-- Name: signal_auth_service; Type: SCHEMA; Schema: -; Owner: postgres
65--
66
67CREATE SCHEMA signal_auth_service;
68
69
70ALTER SCHEMA signal_auth_service OWNER TO CURRENT_USER;
71
72--
73-- Name: signals_service; Type: SCHEMA; Schema: -; Owner: postgres
74--
75
76CREATE SCHEMA signals_service;
77
78
79ALTER SCHEMA signals_service OWNER TO CURRENT_USER;
80
81--
82-- Name: user_management_service; Type: SCHEMA; Schema: -; Owner: postgres
83--
84
85CREATE SCHEMA user_management_service;
86
87
88ALTER SCHEMA user_management_service OWNER TO CURRENT_USER;
89
90--
91-- Name: user_statistics_service; Type: SCHEMA; Schema: -; Owner: postgres
92--
93
94CREATE SCHEMA user_statistics_service;
95
96
97ALTER SCHEMA user_statistics_service OWNER TO CURRENT_USER;
98
99--
100-- Name: appeals_routing_rule_status; Type: TYPE; Schema: manual_review_tool; Owner: postgres
101--
102
103CREATE TYPE manual_review_tool.appeals_routing_rule_status AS ENUM (
104 'LIVE'
105);
106
107
108ALTER TYPE manual_review_tool.appeals_routing_rule_status OWNER TO CURRENT_USER;
109
110--
111-- Name: routing_rule_status; Type: TYPE; Schema: manual_review_tool; Owner: postgres
112--
113
114CREATE TYPE manual_review_tool.routing_rule_status AS ENUM (
115 'LIVE'
116);
117
118
119ALTER TYPE manual_review_tool.routing_rule_status OWNER TO CURRENT_USER;
120
121--
122-- Name: action_type; Type: TYPE; Schema: public; Owner: postgres
123--
124
125CREATE TYPE public.action_type AS ENUM (
126 'CUSTOM_ACTION',
127 'ENQUEUE_TO_MRT',
128 'ENQUEUE_TO_NCMEC',
129 'ENQUEUE_AUTHOR_TO_MRT',
130 'REJECT_APPEAL',
131 'ACCEPT_APPEAL'
132);
133
134
135ALTER TYPE public.action_type OWNER TO CURRENT_USER;
136
137--
138-- Name: backtest_status; Type: TYPE; Schema: public; Owner: postgres
139--
140
141CREATE TYPE public.backtest_status AS ENUM (
142 'RUNNING',
143 'COMPLETE',
144 'CANCELED'
145);
146
147
148ALTER TYPE public.backtest_status OWNER TO CURRENT_USER;
149
150--
151-- Name: enum_conditions_type; Type: TYPE; Schema: public; Owner: postgres
152--
153
154CREATE TYPE public.enum_conditions_type AS ENUM (
155 'BASIC',
156 'CUSTOM'
157);
158
159
160ALTER TYPE public.enum_conditions_type OWNER TO CURRENT_USER;
161
162--
163-- Name: enum_jobs_status; Type: TYPE; Schema: public; Owner: postgres
164--
165
166CREATE TYPE public.enum_jobs_status AS ENUM (
167 'open',
168 'closed'
169);
170
171
172ALTER TYPE public.enum_jobs_status OWNER TO CURRENT_USER;
173
174--
175-- Name: enum_rule_alarm_status; Type: TYPE; Schema: public; Owner: postgres
176--
177
178CREATE TYPE public.enum_rule_alarm_status AS ENUM (
179 'ALARM',
180 'OK',
181 'INSUFFICIENT_DATA'
182);
183
184
185ALTER TYPE public.enum_rule_alarm_status OWNER TO CURRENT_USER;
186
187--
188-- Name: enum_rules_condition_set_conjunction; Type: TYPE; Schema: public; Owner: postgres
189--
190
191CREATE TYPE public.enum_rules_condition_set_conjunction AS ENUM (
192 'AND',
193 'NONE',
194 'OR',
195 'XOR'
196);
197
198
199ALTER TYPE public.enum_rules_condition_set_conjunction OWNER TO CURRENT_USER;
200
201--
202-- Name: enum_rules_status; Type: TYPE; Schema: public; Owner: postgres
203--
204
205CREATE TYPE public.enum_rules_status AS ENUM (
206 'BACKGROUND',
207 'DEPRECATED',
208 'DRAFT',
209 'LIVE',
210 'ARCHIVED'
211);
212
213
214ALTER TYPE public.enum_rules_status OWNER TO CURRENT_USER;
215
216--
217-- Name: enum_signals_type; Type: TYPE; Schema: public; Owner: postgres
218--
219
220CREATE TYPE public.enum_signals_type AS ENUM (
221 'CUSTOM',
222 'TEXT_MATCHING_CONTAINS_TEXT',
223 'TEXT_MATCHING_NOT_CONTAINS_TEXT',
224 'TEXT_MATCHING_CONTAINS_REGEX',
225 'TEXT_MATCHING_NOT_CONTAINS_REGEX',
226 'TEXT_SIMILARITY_SCORE',
227 'IMAGE_EXACT_MATCH',
228 'IMAGE_SIMILARITY_SCORE'
229);
230
231
232ALTER TYPE public.enum_signals_type OWNER TO CURRENT_USER;
233
234--
235-- Name: item_type_kind; Type: TYPE; Schema: public; Owner: postgres
236--
237
238CREATE TYPE public.item_type_kind AS ENUM (
239 'CONTENT',
240 'USER',
241 'THREAD'
242);
243
244
245ALTER TYPE public.item_type_kind OWNER TO CURRENT_USER;
246
247--
248-- Name: login_method_enum; Type: TYPE; Schema: public; Owner: postgres
249--
250
251CREATE TYPE public.login_method_enum AS ENUM (
252 'password',
253 'saml'
254);
255
256
257ALTER TYPE public.login_method_enum OWNER TO CURRENT_USER;
258
259--
260-- Name: model_family; Type: TYPE; Schema: public; Owner: postgres
261--
262
263CREATE TYPE public.model_family AS ENUM (
264 'embedded-text-kernelized-svm-001',
265 'EMBEDDED_IMAGE_KERNELIZED_SVM_001',
266 'EMBEDDED_TEXT_AND_IMAGE_ENSEMBLE_KERNELIZED_SVMS_001',
267 'GPT_4O_MINI_001'
268);
269
270
271ALTER TYPE public.model_family OWNER TO CURRENT_USER;
272
273--
274-- Name: model_status; Type: TYPE; Schema: public; Owner: postgres
275--
276
277CREATE TYPE public.model_status AS ENUM (
278 'READY_TO_TRAIN',
279 'TRAINING',
280 'FROZEN'
281);
282
283
284ALTER TYPE public.model_status OWNER TO CURRENT_USER;
285
286--
287-- Name: ncmec_report_error_status; Type: TYPE; Schema: public; Owner: postgres
288--
289
290CREATE TYPE public.ncmec_report_error_status AS ENUM (
291 'RETRYABLE_ERROR',
292 'PERMANENT_ERROR'
293);
294
295
296ALTER TYPE public.ncmec_report_error_status OWNER TO CURRENT_USER;
297
298--
299-- Name: policy_type; Type: TYPE; Schema: public; Owner: postgres
300--
301
302CREATE TYPE public.policy_type AS ENUM (
303 'HATE',
304 'VIOLENCE',
305 'HARRASSMENT',
306 'SEXUAL_CONTENT',
307 'SPAM',
308 'DRUG_SALES',
309 'WEAPON_SALES',
310 'TERRORISM',
311 'SEXUAL_EXPLOITATION',
312 'SELF_HARM_AND_SUICIDE',
313 'GROOMING',
314 'PROFANITY',
315 'PRIVACY',
316 'FRAUD_AND_DECEPTION'
317);
318
319
320ALTER TYPE public.policy_type OWNER TO CURRENT_USER;
321
322--
323-- Name: rule_type; Type: TYPE; Schema: public; Owner: postgres
324--
325
326CREATE TYPE public.rule_type AS ENUM (
327 'CONTENT',
328 'USER'
329);
330
331
332ALTER TYPE public.rule_type OWNER TO CURRENT_USER;
333
334--
335-- Name: text_bank_type; Type: TYPE; Schema: public; Owner: postgres
336--
337
338CREATE TYPE public.text_bank_type AS ENUM (
339 'STRING',
340 'REGEX'
341);
342
343
344ALTER TYPE public.text_bank_type OWNER TO CURRENT_USER;
345
346--
347-- Name: unknown_type; Type: TYPE; Schema: public; Owner: postgres
348--
349
350CREATE TYPE public.unknown_type AS ENUM (
351 'EDGE_CASE',
352 'NEEDS_CONTEXT'
353);
354
355
356ALTER TYPE public.unknown_type OWNER TO CURRENT_USER;
357
358--
359-- Name: user_penalty_severity; Type: TYPE; Schema: public; Owner: postgres
360--
361
362CREATE TYPE public.user_penalty_severity AS ENUM (
363 'NONE',
364 'LOW',
365 'MEDIUM',
366 'HIGH',
367 'SEVERE'
368);
369
370
371ALTER TYPE public.user_penalty_severity OWNER TO CURRENT_USER;
372
373--
374-- Name: reporting_rule_status; Type: TYPE; Schema: reporting_rules; Owner: postgres
375--
376
377CREATE TYPE reporting_rules.reporting_rule_status AS ENUM (
378 'DRAFT',
379 'BACKGROUND',
380 'LIVE',
381 'ARCHIVED'
382);
383
384
385ALTER TYPE reporting_rules.reporting_rule_status OWNER TO CURRENT_USER;
386
387--
388-- Name: check_org_id(); Type: FUNCTION; Schema: public; Owner: postgres
389--
390
391CREATE FUNCTION public.check_org_id() RETURNS trigger
392 LANGUAGE plpgsql
393 AS $$
394BEGIN
395 IF NEW.org_id <> (NEW.job_payload->>'orgId') THEN
396 RAISE EXCEPTION 'org_id column must match org_id inside job_payload';
397 END IF;
398 RETURN NEW;
399END;
400$$;
401
402
403ALTER FUNCTION public.check_org_id() OWNER TO CURRENT_USER;
404
405--
406-- Name: inherit_user_strike_count(); Type: FUNCTION; Schema: public; Owner: postgres
407--
408
409CREATE FUNCTION public.inherit_user_strike_count() RETURNS trigger
410 LANGUAGE plpgsql
411 AS $$
412DECLARE
413 parent_policy RECORD;
414BEGIN
415 IF NEW.parent_id IS NOT NULL THEN
416 -- Fetch the parent policy
417 SELECT *
418 INTO parent_policy
419 FROM policies
420 WHERE id = NEW.parent_id;
421
422 -- Check if the parent policy has appl set to true
423 IF parent_policy.apply_user_strike_count_config_to_children = true THEN
424 -- Set the child policy's user_strike_count to the parent's user_strike_count
425 NEW.user_strike_count = parent_policy.user_strike_count;
426 END IF;
427 END IF;
428 RETURN NEW;
429END;
430$$;
431
432
433ALTER FUNCTION public.inherit_user_strike_count() OWNER TO CURRENT_USER;
434
435--
436-- Name: update_action_versions_view(); Type: FUNCTION; Schema: public; Owner: postgres
437--
438
439CREATE FUNCTION public.update_action_versions_view() RETURNS void
440 LANGUAGE plpgsql
441 AS $$
442 BEGIN
443 REFRESH MATERIALIZED VIEW action_versions;
444 END;
445$$;
446
447
448ALTER FUNCTION public.update_action_versions_view() OWNER TO CURRENT_USER;
449
450--
451-- Name: update_action_versions_view_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
452--
453
454CREATE FUNCTION public.update_action_versions_view_trigger() RETURNS trigger
455 LANGUAGE plpgsql
456 AS $$
457 BEGIN
458 PERFORM update_action_versions_view();
459 RETURN NULL;
460 END;
461$$;
462
463
464ALTER FUNCTION public.update_action_versions_view_trigger() OWNER TO CURRENT_USER;
465
466--
467-- Name: update_api_keys_updated_at(); Type: FUNCTION; Schema: public; Owner: postgres
468--
469
470CREATE FUNCTION public.update_api_keys_updated_at() RETURNS trigger
471 LANGUAGE plpgsql
472 AS $$
473BEGIN
474 NEW.updated_at = NOW();
475 RETURN NEW;
476END;
477$$;
478
479
480ALTER FUNCTION public.update_api_keys_updated_at() OWNER TO CURRENT_USER;
481
482--
483-- Name: update_appeals_routing_rule_versions_view(); Type: FUNCTION; Schema: public; Owner: postgres
484--
485
486CREATE FUNCTION public.update_appeals_routing_rule_versions_view() RETURNS void
487 LANGUAGE plpgsql
488 AS $$
489 BEGIN
490 REFRESH MATERIALIZED VIEW manual_review_tool.appeals_routing_rule_versions;
491 END;
492$$;
493
494
495ALTER FUNCTION public.update_appeals_routing_rule_versions_view() OWNER TO CURRENT_USER;
496
497--
498-- Name: update_appeals_routing_rule_versions_view_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
499--
500
501CREATE FUNCTION public.update_appeals_routing_rule_versions_view_trigger() RETURNS trigger
502 LANGUAGE plpgsql
503 AS $$
504 BEGIN
505 PERFORM update_appeals_routing_rule_versions_view();
506 RETURN NULL;
507 END;
508$$;
509
510
511ALTER FUNCTION public.update_appeals_routing_rule_versions_view_trigger() OWNER TO CURRENT_USER;
512
513--
514-- Name: update_content_type_versions_view(); Type: FUNCTION; Schema: public; Owner: postgres
515--
516
517CREATE FUNCTION public.update_content_type_versions_view() RETURNS void
518 LANGUAGE plpgsql
519 AS $$
520 BEGIN
521 REFRESH MATERIALIZED VIEW content_type_versions;
522 END;
523$$;
524
525
526ALTER FUNCTION public.update_content_type_versions_view() OWNER TO CURRENT_USER;
527
528--
529-- Name: update_content_type_versions_view_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
530--
531
532CREATE FUNCTION public.update_content_type_versions_view_trigger() RETURNS trigger
533 LANGUAGE plpgsql
534 AS $$
535 BEGIN
536 PERFORM update_content_type_versions_view();
537 RETURN NULL;
538 END;
539$$;
540
541
542ALTER FUNCTION public.update_content_type_versions_view_trigger() OWNER TO CURRENT_USER;
543
544--
545-- Name: update_descendants_user_strike_count(); Type: FUNCTION; Schema: public; Owner: postgres
546--
547
548CREATE FUNCTION public.update_descendants_user_strike_count() RETURNS trigger
549 LANGUAGE plpgsql
550 AS $$
551DECLARE
552 descendant RECORD;
553BEGIN
554 IF NEW.apply_user_strike_count_config_to_children = true THEN
555 -- Recursively update all descendants
556 FOR descendant IN
557 WITH RECURSIVE descendants AS (
558 SELECT id
559 FROM policies
560 WHERE parent_id = NEW.id
561 UNION ALL
562 SELECT p.id
563 FROM policies p
564 JOIN descendants d ON p.parent_id = d.id
565 )
566 SELECT id FROM descendants
567 LOOP
568 UPDATE policies
569 SET user_strike_count = NEW.user_strike_count
570 WHERE id = descendant.id;
571 END LOOP;
572 END IF;
573 RETURN NEW;
574END;
575$$;
576
577
578ALTER FUNCTION public.update_descendants_user_strike_count() OWNER TO CURRENT_USER;
579
580--
581-- Name: update_item_type_versions_view(); Type: FUNCTION; Schema: public; Owner: postgres
582--
583
584CREATE FUNCTION public.update_item_type_versions_view() RETURNS void
585 LANGUAGE plpgsql
586 AS $$
587 BEGIN
588 REFRESH MATERIALIZED VIEW item_type_versions;
589 END;
590$$;
591
592
593ALTER FUNCTION public.update_item_type_versions_view() OWNER TO CURRENT_USER;
594
595--
596-- Name: update_item_type_versions_view_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
597--
598
599CREATE FUNCTION public.update_item_type_versions_view_trigger() RETURNS trigger
600 LANGUAGE plpgsql
601 AS $$
602 BEGIN
603 PERFORM update_item_type_versions_view();
604 RETURN NULL;
605 END;
606$$;
607
608
609ALTER FUNCTION public.update_item_type_versions_view_trigger() OWNER TO CURRENT_USER;
610
611--
612-- Name: update_policy_versions_view(); Type: FUNCTION; Schema: public; Owner: postgres
613--
614
615CREATE FUNCTION public.update_policy_versions_view() RETURNS void
616 LANGUAGE plpgsql
617 AS $$
618 BEGIN
619 REFRESH MATERIALIZED VIEW policy_versions;
620 END;
621$$;
622
623
624ALTER FUNCTION public.update_policy_versions_view() OWNER TO CURRENT_USER;
625
626--
627-- Name: update_policy_versions_view_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
628--
629
630CREATE FUNCTION public.update_policy_versions_view_trigger() RETURNS trigger
631 LANGUAGE plpgsql
632 AS $$
633 BEGIN
634 PERFORM update_policy_versions_view();
635 RETURN NULL;
636 END;
637$$;
638
639
640ALTER FUNCTION public.update_policy_versions_view_trigger() OWNER TO CURRENT_USER;
641
642--
643-- Name: update_reporting_rule_versions_view(); Type: FUNCTION; Schema: public; Owner: postgres
644--
645
646CREATE FUNCTION public.update_reporting_rule_versions_view() RETURNS void
647 LANGUAGE plpgsql
648 AS $$
649 BEGIN
650 REFRESH MATERIALIZED VIEW reporting_rules.reporting_rule_versions;
651 END;
652$$;
653
654
655ALTER FUNCTION public.update_reporting_rule_versions_view() OWNER TO CURRENT_USER;
656
657--
658-- Name: update_reporting_rule_versions_view_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
659--
660
661CREATE FUNCTION public.update_reporting_rule_versions_view_trigger() RETURNS trigger
662 LANGUAGE plpgsql
663 AS $$
664 BEGIN
665 PERFORM update_reporting_rule_versions_view();
666 RETURN NULL;
667 END;
668$$;
669
670
671ALTER FUNCTION public.update_reporting_rule_versions_view_trigger() OWNER TO CURRENT_USER;
672
673--
674-- Name: update_routing_rule_versions_view(); Type: FUNCTION; Schema: public; Owner: postgres
675--
676
677CREATE FUNCTION public.update_routing_rule_versions_view() RETURNS void
678 LANGUAGE plpgsql
679 AS $$
680 BEGIN
681 REFRESH MATERIALIZED VIEW manual_review_tool.routing_rule_versions;
682 END;
683$$;
684
685
686ALTER FUNCTION public.update_routing_rule_versions_view() OWNER TO CURRENT_USER;
687
688--
689-- Name: update_routing_rule_versions_view_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
690--
691
692CREATE FUNCTION public.update_routing_rule_versions_view_trigger() RETURNS trigger
693 LANGUAGE plpgsql
694 AS $$
695 BEGIN
696 PERFORM update_routing_rule_versions_view();
697 RETURN NULL;
698 END;
699$$;
700
701
702ALTER FUNCTION public.update_routing_rule_versions_view_trigger() OWNER TO CURRENT_USER;
703
704--
705-- Name: update_rule_versions_view(); Type: FUNCTION; Schema: public; Owner: postgres
706--
707
708CREATE FUNCTION public.update_rule_versions_view() RETURNS void
709 LANGUAGE plpgsql
710 AS $$
711 DECLARE
712 value_of_has_run_setting text := current_setting('coop.rule_versions_view_refreshed_on_this_transaction', true);
713 BEGIN
714 -- NB: this refresh must _not_ be done `CONCURRENTLY`, as we want the view to
715 -- be immediately consistent with the persisted rule definition, so that we
716 -- don't end up with rows in the data warehouse that specify an old rule version but
717 -- ran with a newer rule definition. The view changes so rarely that the
718 -- overhead here should be fine.
719 --
720 -- NB: the current_setting call first returns null if the setting has never
721 -- been accessed before. But, on subsequent calls -- even in different
722 -- transactions, where it's the first call of that transaction -- it seems
723 -- like the value returned is an empty string (not null).
724 IF value_of_has_run_setting IS NULL OR value_of_has_run_setting <> 'true' THEN
725 -- as a local, this variable will only last the length of the transaction.
726 SET LOCAL coop.rule_versions_view_refreshed_on_this_transaction = 'true';
727 REFRESH MATERIALIZED VIEW rule_versions;
728 END IF;
729 END;
730$$;
731
732
733ALTER FUNCTION public.update_rule_versions_view() OWNER TO CURRENT_USER;
734
735--
736-- Name: update_rule_versions_view_trigger(); Type: FUNCTION; Schema: public; Owner: postgres
737--
738
739CREATE FUNCTION public.update_rule_versions_view_trigger() RETURNS trigger
740 LANGUAGE plpgsql
741 AS $$
742 BEGIN
743 PERFORM update_rule_versions_view();
744 RETURN NULL;
745 END;
746$$;
747
748
749ALTER FUNCTION public.update_rule_versions_view_trigger() OWNER TO CURRENT_USER;
750
751--
752-- Name: update_signing_keys_updated_at(); Type: FUNCTION; Schema: public; Owner: postgres
753--
754
755CREATE FUNCTION public.update_signing_keys_updated_at() RETURNS trigger
756 LANGUAGE plpgsql
757 AS $$
758BEGIN
759 NEW.updated_at = NOW();
760 RETURN NEW;
761END;
762$$;
763
764
765ALTER FUNCTION public.update_signing_keys_updated_at() OWNER TO CURRENT_USER;
766
767--
768-- Name: versioning(); Type: FUNCTION; Schema: public; Owner: postgres
769--
770
771CREATE FUNCTION public.versioning() RETURNS trigger
772 LANGUAGE plpgsql
773 AS $_$
774DECLARE
775 sys_period text;
776 history_table text;
777 manipulate jsonb;
778 ignore_unchanged_values bool;
779 commonColumns text[];
780 time_stamp_to_use timestamptz := current_timestamp;
781 range_lower timestamptz;
782 transaction_info txid_snapshot;
783 existing_range tstzrange;
784 holder record;
785 holder2 record;
786 has_tracked_differences bool;
787 has_tracked_differences_query text;
788 name_col text;
789BEGIN
790 -- version 0.4.2
791
792 IF TG_WHEN != 'BEFORE' OR TG_LEVEL != 'ROW' THEN
793 RAISE TRIGGER_PROTOCOL_VIOLATED USING
794 MESSAGE = 'function "versioning" must be fired BEFORE ROW';
795 END IF;
796
797 IF TG_OP != 'INSERT' AND TG_OP != 'UPDATE' AND TG_OP != 'DELETE' THEN
798 RAISE TRIGGER_PROTOCOL_VIOLATED USING
799 MESSAGE = 'function "versioning" must be fired for INSERT or UPDATE or DELETE';
800 END IF;
801
802 IF TG_NARGS not in (3,4) THEN
803 RAISE INVALID_PARAMETER_VALUE USING
804 MESSAGE = 'wrong number of parameters for function "versioning"',
805 HINT = 'expected 3 or 4 parameters but got ' || TG_NARGS;
806 END IF;
807
808 sys_period := TG_ARGV[0];
809 history_table := TG_ARGV[1];
810 ignore_unchanged_values := TG_ARGV[3];
811
812 -- check if sys_period exists on original table
813 SELECT atttypid, attndims INTO holder FROM pg_attribute WHERE attrelid = TG_RELID AND attname = sys_period AND NOT attisdropped;
814 IF NOT FOUND THEN
815 RAISE 'column "%" of relation "%" does not exist', sys_period, TG_TABLE_NAME USING
816 ERRCODE = 'undefined_column';
817 END IF;
818 IF holder.atttypid != to_regtype('tstzrange') THEN
819 IF holder.attndims > 0 THEN
820 RAISE 'system period column "%" of relation "%" is not a range but an array', sys_period, TG_TABLE_NAME USING
821 ERRCODE = 'datatype_mismatch';
822 END IF;
823
824 SELECT rngsubtype INTO holder2 FROM pg_range WHERE rngtypid = holder.atttypid;
825 IF FOUND THEN
826 RAISE 'system period column "%" of relation "%" is not a range of timestamp with timezone but of type %', sys_period, TG_TABLE_NAME, format_type(holder2.rngsubtype, null) USING
827 ERRCODE = 'datatype_mismatch';
828 END IF;
829
830 RAISE 'system period column "%" of relation "%" is not a range but type %', sys_period, TG_TABLE_NAME, format_type(holder.atttypid, null) USING
831 ERRCODE = 'datatype_mismatch';
832 END IF;
833
834 IF TG_OP = 'UPDATE' OR TG_OP = 'DELETE' THEN
835 -- Ignore rows already modified in this transaction
836 transaction_info := txid_current_snapshot();
837 IF OLD.xmin::text >= (txid_snapshot_xmin(transaction_info) % (2^32)::bigint)::text
838 AND OLD.xmin::text <= (txid_snapshot_xmax(transaction_info) % (2^32)::bigint)::text THEN
839 IF TG_OP = 'DELETE' THEN
840 RETURN OLD;
841 END IF;
842
843 RETURN NEW;
844 END IF;
845
846 IF to_regclass(history_table) IS NULL THEN
847 RAISE 'relation "%" does not exist', history_table;
848 END IF;
849
850 -- check if history table has sys_period
851 IF NOT EXISTS(SELECT * FROM pg_attribute WHERE attrelid = history_table::regclass AND attname = sys_period AND NOT attisdropped) THEN
852 RAISE 'history relation "%" does not contain system period column "%"', history_table, sys_period USING
853 HINT = 'history relation must contain system period column with the same name and data type as the versioned one';
854 END IF;
855
856 EXECUTE format('SELECT $1.%I', sys_period) USING OLD INTO existing_range;
857
858 IF existing_range IS NULL THEN
859 RAISE 'system period column "%" of relation "%" must not be null', sys_period, TG_TABLE_NAME USING
860 ERRCODE = 'null_value_not_allowed';
861 END IF;
862
863 IF isempty(existing_range) OR NOT upper_inf(existing_range) THEN
864 RAISE 'system period column "%" of relation "%" contains invalid value', sys_period, TG_TABLE_NAME USING
865 ERRCODE = 'data_exception',
866 DETAIL = 'valid ranges must be non-empty and unbounded on the high side';
867 END IF;
868
869 IF TG_ARGV[2] = 'true' THEN
870 -- mitigate update conflicts
871 range_lower := lower(existing_range);
872 IF range_lower >= time_stamp_to_use THEN
873 time_stamp_to_use := range_lower + interval '1 microseconds';
874 END IF;
875 END IF;
876
877 WITH history AS
878 (SELECT attname, atttypid
879 FROM pg_attribute
880 WHERE attrelid = history_table::regclass
881 AND attnum > 0
882 AND NOT attisdropped),
883 main AS
884 (SELECT attname, atttypid
885 FROM pg_attribute
886 WHERE attrelid = TG_RELID
887 AND attnum > 0
888 AND NOT attisdropped)
889 SELECT
890 history.attname AS history_name,
891 main.attname AS main_name,
892 history.atttypid AS history_type,
893 main.atttypid AS main_type
894 INTO holder
895 FROM history
896 INNER JOIN main
897 ON history.attname = main.attname
898 WHERE
899 history.atttypid != main.atttypid;
900
901 IF FOUND THEN
902 RAISE 'column "%" of relation "%" is of type % but column "%" of history relation "%" is of type %',
903 holder.main_name, TG_TABLE_NAME, format_type(holder.main_type, null), holder.history_name, history_table, format_type(holder.history_type, null)
904 USING ERRCODE = 'datatype_mismatch';
905 END IF;
906
907 WITH history AS
908 (SELECT attname
909 FROM pg_attribute
910 WHERE attrelid = history_table::regclass
911 AND attnum > 0
912 AND NOT attisdropped),
913 main AS
914 (SELECT attname
915 FROM pg_attribute
916 WHERE attrelid = TG_RELID
917 AND attnum > 0
918 AND NOT attisdropped)
919 SELECT array_agg(quote_ident(history.attname)) INTO commonColumns
920 FROM history
921 INNER JOIN main
922 ON history.attname = main.attname
923 AND history.attname != sys_period;
924
925 -- If we're trying to ignore unchanged values, see if we can bail immediately
926 -- if NEW IS NOT DISTINCT FROM OLD, which should be faster than building,
927 -- parsing, and executing a dynamic query. But, if there is some difference
928 -- between NEW and OLD, do the more thorough check to see if the difference
929 -- is on a column we're actually tracking.
930 IF ignore_unchanged_values AND TG_OP = 'UPDATE' THEN
931 IF NEW IS NOT DISTINCT FROM OLD OR array_length(commonColumns, 1) = 0 THEN
932 RETURN OLD;
933 ELSE
934 has_tracked_differences_query := 'SELECT ';
935 FOREACH name_col IN ARRAY commonColumns LOOP
936 has_tracked_differences_query =
937 has_tracked_differences_query ||
938 '($1).' || name_col || ' IS DISTINCT FROM ($2).' || name_col ||
939 ' OR ';
940 END LOOP;
941 -- Above loop is gonna leave a trailing OR, so remove make it
942 -- syntactically valid by adding a trivially failing condition.
943 has_tracked_differences_query = has_tracked_differences_query || '(1 = 0);';
944
945 EXECUTE has_tracked_differences_query
946 USING NEW, OLD
947 INTO has_tracked_differences;
948
949 IF NOT has_tracked_differences THEN
950 -- Return NEW because there still are some changes that must be saved
951 -- to the source table; they just don't need to go to the history table.
952 RETURN NEW;
953 END IF;
954 END IF;
955 END IF;
956
957 EXECUTE ('INSERT INTO ' ||
958 history_table ||
959 '(' ||
960 array_to_string(commonColumns , ',') ||
961 ',' ||
962 quote_ident(sys_period) ||
963 ') VALUES ($1.' ||
964 array_to_string(commonColumns, ',$1.') ||
965 ',tstzrange($2, $3, ''[)''))')
966 USING OLD, range_lower, time_stamp_to_use;
967 END IF;
968
969 IF TG_OP = 'UPDATE' OR TG_OP = 'INSERT' THEN
970 manipulate := jsonb_set('{}'::jsonb, ('{' || sys_period || '}')::text[], to_jsonb(tstzrange(time_stamp_to_use, null, '[)')));
971
972 RETURN jsonb_populate_record(NEW, manipulate);
973 END IF;
974
975 RETURN OLD;
976END;
977$_$;
978
979
980ALTER FUNCTION public.versioning() OWNER TO CURRENT_USER;
981
982--
983-- Name: jsonb_object_union(jsonb); Type: AGGREGATE; Schema: public; Owner: postgres
984--
985
986CREATE AGGREGATE public.jsonb_object_union(jsonb) (
987 SFUNC = jsonb_concat,
988 STYPE = jsonb,
989 INITCOND = '{}'
990);
991
992
993ALTER AGGREGATE public.jsonb_object_union(jsonb) OWNER TO CURRENT_USER;
994
995SET default_tablespace = '';
996
997SET default_table_access_method = heap;
998
999--
1000-- Name: scheduled_jobs_info; Type: TABLE; Schema: jobs; Owner: postgres
1001--
1002
1003CREATE TABLE jobs.scheduled_jobs_info (
1004 job_name character varying(255) NOT NULL,
1005 last_run timestamp with time zone NOT NULL
1006);
1007
1008
1009ALTER TABLE jobs.scheduled_jobs_info OWNER TO CURRENT_USER;
1010
1011--
1012-- Name: appeals_routing_rule_history; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1013--
1014
1015CREATE TABLE manual_review_tool.appeals_routing_rule_history (
1016 id character varying(255) NOT NULL,
1017 org_id character varying(255) NOT NULL,
1018 name character varying(255) NOT NULL,
1019 description character varying(255),
1020 status manual_review_tool.appeals_routing_rule_status NOT NULL,
1021 creator_id character varying(255) NOT NULL,
1022 condition_set jsonb NOT NULL,
1023 sequence_number integer NOT NULL,
1024 destination_queue_id character varying(255) NOT NULL,
1025 sys_period tstzrange NOT NULL
1026);
1027
1028
1029ALTER TABLE manual_review_tool.appeals_routing_rule_history OWNER TO CURRENT_USER;
1030
1031--
1032-- Name: appeals_routing_rules; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1033--
1034
1035CREATE TABLE manual_review_tool.appeals_routing_rules (
1036 id character varying(255) NOT NULL,
1037 org_id character varying(255) NOT NULL,
1038 name character varying(255) NOT NULL,
1039 description character varying(255),
1040 status manual_review_tool.appeals_routing_rule_status DEFAULT 'LIVE'::manual_review_tool.appeals_routing_rule_status NOT NULL,
1041 created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
1042 creator_id character varying(255) NOT NULL,
1043 condition_set jsonb NOT NULL,
1044 sequence_number integer NOT NULL,
1045 destination_queue_id character varying(255) NOT NULL,
1046 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL
1047);
1048
1049
1050ALTER TABLE manual_review_tool.appeals_routing_rules OWNER TO CURRENT_USER;
1051
1052--
1053-- Name: appeals_routing_rule_versions; Type: MATERIALIZED VIEW; Schema: manual_review_tool; Owner: postgres
1054--
1055
1056CREATE MATERIALIZED VIEW manual_review_tool.appeals_routing_rule_versions AS
1057 WITH appeals_routing_rule_versions AS (
1058 SELECT appeals_routing_rules_1.id,
1059 appeals_routing_rules_1.name,
1060 appeals_routing_rules_1.org_id,
1061 appeals_routing_rules_1.description,
1062 appeals_routing_rules_1.status,
1063 appeals_routing_rules_1.creator_id,
1064 appeals_routing_rules_1.condition_set,
1065 appeals_routing_rules_1.sequence_number,
1066 appeals_routing_rules_1.destination_queue_id,
1067 appeals_routing_rules_1.sys_period
1068 FROM manual_review_tool.appeals_routing_rules appeals_routing_rules_1
1069 UNION ALL
1070 SELECT appeals_routing_rule_history.id,
1071 appeals_routing_rule_history.name,
1072 appeals_routing_rule_history.org_id,
1073 appeals_routing_rule_history.description,
1074 appeals_routing_rule_history.status,
1075 appeals_routing_rule_history.creator_id,
1076 appeals_routing_rule_history.condition_set,
1077 appeals_routing_rule_history.sequence_number,
1078 appeals_routing_rule_history.destination_queue_id,
1079 appeals_routing_rule_history.sys_period
1080 FROM manual_review_tool.appeals_routing_rule_history
1081 ), appeals_routing_rules AS (
1082 SELECT appeals_routing_rule_versions_1.id,
1083 max(lower(appeals_routing_rule_versions_1.sys_period)) AS max_period_start
1084 FROM appeals_routing_rule_versions appeals_routing_rule_versions_1
1085 GROUP BY appeals_routing_rule_versions_1.id
1086 )
1087 SELECT appeals_routing_rule_versions.id,
1088 appeals_routing_rule_versions.name,
1089 appeals_routing_rule_versions.org_id,
1090 appeals_routing_rule_versions.description,
1091 appeals_routing_rule_versions.status,
1092 appeals_routing_rule_versions.creator_id,
1093 appeals_routing_rule_versions.condition_set,
1094 appeals_routing_rule_versions.sequence_number,
1095 appeals_routing_rule_versions.destination_queue_id,
1096 lower(appeals_routing_rule_versions.sys_period) AS version,
1097 ((appeals_routing_rules.max_period_start = lower(appeals_routing_rule_versions.sys_period)) AND upper_inf(appeals_routing_rule_versions.sys_period)) AS is_current
1098 FROM (appeals_routing_rule_versions
1099 JOIN appeals_routing_rules ON (((appeals_routing_rules.id)::text = (appeals_routing_rule_versions.id)::text)))
1100 WITH DATA;
1101
1102
1103ALTER TABLE manual_review_tool.appeals_routing_rule_versions OWNER TO CURRENT_USER;
1104
1105--
1106-- Name: appeals_routing_rule_latest_versions; Type: VIEW; Schema: manual_review_tool; Owner: postgres
1107--
1108
1109CREATE VIEW manual_review_tool.appeals_routing_rule_latest_versions AS
1110 SELECT appeals_routing_rule_versions.id AS appeals_routing_rule_id,
1111 to_char((appeals_routing_rule_versions.version AT TIME ZONE 'UTC'::text), 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'::text) AS version
1112 FROM manual_review_tool.appeals_routing_rule_versions
1113 WHERE (appeals_routing_rule_versions.is_current = true);
1114
1115
1116ALTER TABLE manual_review_tool.appeals_routing_rule_latest_versions OWNER TO CURRENT_USER;
1117
1118--
1119-- Name: appeals_routing_rules_to_item_types; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1120--
1121
1122CREATE TABLE manual_review_tool.appeals_routing_rules_to_item_types (
1123 item_type_id character varying(255) NOT NULL,
1124 appeals_routing_rule_id character varying(255) NOT NULL
1125);
1126
1127
1128ALTER TABLE manual_review_tool.appeals_routing_rules_to_item_types OWNER TO CURRENT_USER;
1129
1130--
1131-- Name: manual_review_decisions; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1132--
1133
1134CREATE TABLE manual_review_tool.manual_review_decisions (
1135 id uuid NOT NULL,
1136 job_payload jsonb NOT NULL,
1137 queue_id character varying(255) NOT NULL,
1138 reviewer_id character varying(255) NOT NULL,
1139 org_id character varying(255) NOT NULL,
1140 created_at timestamp with time zone DEFAULT now() NOT NULL,
1141 decision_components jsonb[] NOT NULL,
1142 related_actions jsonb[] DEFAULT ARRAY[]::jsonb[] NOT NULL,
1143 enqueue_source_info jsonb,
1144 item_created_at timestamp with time zone,
1145 decision_reason text
1146);
1147ALTER TABLE ONLY manual_review_tool.manual_review_decisions ALTER COLUMN created_at SET STATISTICS 1500;
1148
1149
1150ALTER TABLE manual_review_tool.manual_review_decisions OWNER TO CURRENT_USER;
1151
1152--
1153-- Name: dim_mrt_decisions; Type: VIEW; Schema: manual_review_tool; Owner: postgres
1154--
1155
1156CREATE VIEW manual_review_tool.dim_mrt_decisions AS
1157 WITH decisions AS (
1158 SELECT manual_review_decisions.org_id,
1159 (dc.dc ->> 'type'::text) AS type,
1160 (action.value ->> 'id'::text) AS action_id,
1161 (policy.value ->> 'id'::text) AS policy_id,
1162 (((manual_review_decisions.job_payload -> 'payload'::text) -> 'item'::text) ->> 'itemId'::text) AS item_id,
1163 ((((manual_review_decisions.job_payload -> 'payload'::text) -> 'item'::text) -> 'itemTypeIdentifier'::text) ->> 'id'::text) AS item_type_id,
1164 manual_review_decisions.queue_id,
1165 manual_review_decisions.reviewer_id,
1166 manual_review_decisions.created_at,
1167 (manual_review_decisions.job_payload ->> 'id'::text) AS job_id
1168 FROM manual_review_tool.manual_review_decisions,
1169 (LATERAL unnest(manual_review_decisions.decision_components) dc(dc)
1170 CROSS JOIN LATERAL jsonb_array_elements((dc.dc -> 'actions'::text)) action(value)),
1171 LATERAL jsonb_array_elements((dc.dc -> 'policies'::text)) policy(value)
1172 WHERE ((dc.dc ->> 'type'::text) = 'CUSTOM_ACTION'::text)
1173 UNION ALL
1174 SELECT manual_review_decisions.org_id,
1175 (dc.dc ->> 'type'::text) AS type,
1176 NULL::text AS action_id,
1177 NULL::text AS policy_id,
1178 (((manual_review_decisions.job_payload -> 'payload'::text) -> 'item'::text) ->> 'itemId'::text) AS item_id,
1179 ((((manual_review_decisions.job_payload -> 'payload'::text) -> 'item'::text) -> 'itemTypeIdentifier'::text) ->> 'id'::text) AS item_type_id,
1180 manual_review_decisions.queue_id,
1181 manual_review_decisions.reviewer_id,
1182 manual_review_decisions.created_at,
1183 (manual_review_decisions.job_payload ->> 'id'::text) AS job_id
1184 FROM manual_review_tool.manual_review_decisions,
1185 LATERAL unnest(manual_review_decisions.decision_components) dc(dc)
1186 WHERE ((dc.dc ->> 'type'::text) <> 'CUSTOM_ACTION'::text)
1187 UNION ALL
1188 SELECT DISTINCT manual_review_decisions.org_id,
1189 'RELATED_ACTION'::text AS type,
1190 action.value AS action_id,
1191 policy.value AS policy_id,
1192 item_id.value AS item_id,
1193 (unnested_data.unnested_data ->> 'itemTypeId'::text) AS item_type_id,
1194 manual_review_decisions.queue_id,
1195 manual_review_decisions.reviewer_id,
1196 manual_review_decisions.created_at,
1197 (manual_review_decisions.job_payload ->> 'id'::text) AS job_id
1198 FROM manual_review_tool.manual_review_decisions,
1199 LATERAL unnest(manual_review_decisions.related_actions) unnested_data(unnested_data),
1200 LATERAL jsonb_array_elements_text((unnested_data.unnested_data -> 'itemIds'::text)) item_id(value),
1201 LATERAL jsonb_array_elements_text((unnested_data.unnested_data -> 'actionIds'::text)) action(value),
1202 LATERAL jsonb_array_elements_text((unnested_data.unnested_data -> 'policyIds'::text)) policy(value)
1203 )
1204 SELECT DISTINCT decisions.org_id,
1205 decisions.item_id,
1206 decisions.action_id,
1207 decisions.policy_id,
1208 decisions.type,
1209 decisions.item_type_id,
1210 decisions.queue_id,
1211 decisions.reviewer_id,
1212 date(decisions.created_at) AS ds,
1213 decisions.job_id,
1214 decisions.created_at AS decided_at
1215 FROM decisions
1216 WHERE ((decisions.item_id IS NOT NULL) AND (decisions.item_type_id IS NOT NULL));
1217
1218
1219ALTER TABLE manual_review_tool.dim_mrt_decisions OWNER TO CURRENT_USER;
1220
1221--
1222-- Name: dim_mrt_decisions_materialized; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1223--
1224
1225CREATE TABLE manual_review_tool.dim_mrt_decisions_materialized (
1226 org_id character varying(255),
1227 item_id text,
1228 action_id text,
1229 policy_id text,
1230 type text,
1231 item_type_id text,
1232 queue_id character varying(255),
1233 reviewer_id character varying(255),
1234 ds date,
1235 job_id text,
1236 decided_at timestamp with time zone
1237);
1238
1239
1240ALTER TABLE manual_review_tool.dim_mrt_decisions_materialized OWNER TO CURRENT_USER;
1241
1242--
1243-- Name: job_creations; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1244--
1245
1246CREATE TABLE manual_review_tool.job_creations (
1247 id character varying(255) NOT NULL,
1248 org_id character varying(255) NOT NULL,
1249 queue_id character varying(255) NOT NULL,
1250 item_id character varying(255) NOT NULL,
1251 item_type_id character varying(255) NOT NULL,
1252 created_at timestamp with time zone DEFAULT now() NOT NULL,
1253 enqueue_source_info jsonb NOT NULL,
1254 policy_ids text[] DEFAULT ARRAY[]::text[] NOT NULL
1255);
1256ALTER TABLE ONLY manual_review_tool.job_creations ALTER COLUMN created_at SET STATISTICS 1500;
1257
1258
1259ALTER TABLE manual_review_tool.job_creations OWNER TO CURRENT_USER;
1260
1261--
1262-- Name: flattened_job_creations; Type: VIEW; Schema: manual_review_tool; Owner: postgres
1263--
1264
1265CREATE VIEW manual_review_tool.flattened_job_creations AS
1266 SELECT job_creations.id,
1267 job_creations.org_id,
1268 job_creations.queue_id,
1269 job_creations.item_id,
1270 job_creations.item_type_id,
1271 job_creations.created_at,
1272 (job_creations.enqueue_source_info ->> 'kind'::text) AS source_kind,
1273 rule_id.value AS rule_id,
1274 policy_id.policy_id
1275 FROM ((manual_review_tool.job_creations
1276 LEFT JOIN LATERAL jsonb_array_elements_text((job_creations.enqueue_source_info -> 'rules'::text)) rule_id(value) ON (true))
1277 LEFT JOIN LATERAL unnest(job_creations.policy_ids) policy_id(policy_id) ON (true));
1278
1279
1280ALTER TABLE manual_review_tool.flattened_job_creations OWNER TO CURRENT_USER;
1281
1282--
1283-- Name: job_comments; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1284--
1285
1286CREATE TABLE manual_review_tool.job_comments (
1287 id character varying(255) NOT NULL,
1288 job_id character varying(255) NOT NULL,
1289 org_id character varying(255) NOT NULL,
1290 author_id character varying(255) NOT NULL,
1291 comment_text text NOT NULL,
1292 created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
1293);
1294
1295
1296ALTER TABLE manual_review_tool.job_comments OWNER TO CURRENT_USER;
1297
1298--
1299-- Name: manual_review_hidden_item_fields; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1300--
1301
1302CREATE TABLE manual_review_tool.manual_review_hidden_item_fields (
1303 org_id character varying(255) NOT NULL,
1304 item_type_id character varying(255) NOT NULL,
1305 hidden_fields text[] DEFAULT ARRAY[]::text[] NOT NULL
1306);
1307
1308
1309ALTER TABLE manual_review_tool.manual_review_hidden_item_fields OWNER TO CURRENT_USER;
1310
1311--
1312-- Name: manual_review_queues; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1313--
1314
1315CREATE TABLE manual_review_tool.manual_review_queues (
1316 id character varying(255) NOT NULL,
1317 name character varying(255) NOT NULL,
1318 created_at timestamp with time zone DEFAULT now() NOT NULL,
1319 updated_at timestamp with time zone DEFAULT now() NOT NULL,
1320 org_id character varying(255) NOT NULL,
1321 is_default_queue boolean DEFAULT false NOT NULL,
1322 description character varying(255),
1323 is_appeals_queue boolean DEFAULT false NOT NULL,
1324 auto_close_jobs boolean DEFAULT false,
1325 CONSTRAINT manual_review_queues_description_check CHECK (((description)::text <> ''::text))
1326);
1327
1328
1329ALTER TABLE manual_review_tool.manual_review_queues OWNER TO CURRENT_USER;
1330
1331--
1332-- Name: manual_review_tool_settings; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1333--
1334
1335CREATE TABLE manual_review_tool.manual_review_tool_settings (
1336 org_id character varying(255) NOT NULL,
1337 requires_policy_for_decisions boolean DEFAULT false NOT NULL,
1338 mrt_requires_decision_reason boolean DEFAULT false NOT NULL,
1339 hide_skip_button_for_non_admins boolean DEFAULT false,
1340 ignore_callback_url character varying(255),
1341 preview_jobs_view_enabled boolean DEFAULT false NOT NULL
1342);
1343
1344
1345ALTER TABLE manual_review_tool.manual_review_tool_settings OWNER TO CURRENT_USER;
1346
1347--
1348-- Name: moderator_skips; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1349--
1350
1351CREATE TABLE manual_review_tool.moderator_skips (
1352 org_id character varying(255) NOT NULL,
1353 queue_id character varying(255) NOT NULL,
1354 user_id character varying(255) NOT NULL,
1355 job_id character varying(255) NOT NULL,
1356 ts timestamp with time zone DEFAULT now() NOT NULL
1357);
1358
1359
1360ALTER TABLE manual_review_tool.moderator_skips OWNER TO CURRENT_USER;
1361
1362--
1363-- Name: queues_and_hidden_actions; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1364--
1365
1366CREATE TABLE manual_review_tool.queues_and_hidden_actions (
1367 queue_id character varying(255) NOT NULL,
1368 action_id character varying(255) NOT NULL,
1369 org_id character varying(255) NOT NULL
1370);
1371
1372
1373ALTER TABLE manual_review_tool.queues_and_hidden_actions OWNER TO CURRENT_USER;
1374
1375--
1376-- Name: routing_rule_history; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1377--
1378
1379CREATE TABLE manual_review_tool.routing_rule_history (
1380 id character varying(255) NOT NULL,
1381 org_id character varying(255) NOT NULL,
1382 name character varying(255) NOT NULL,
1383 description character varying(255),
1384 status manual_review_tool.routing_rule_status NOT NULL,
1385 creator_id character varying(255) NOT NULL,
1386 condition_set jsonb NOT NULL,
1387 sequence_number integer NOT NULL,
1388 destination_queue_id character varying(255) NOT NULL,
1389 sys_period tstzrange NOT NULL
1390);
1391
1392
1393ALTER TABLE manual_review_tool.routing_rule_history OWNER TO CURRENT_USER;
1394
1395--
1396-- Name: routing_rules; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1397--
1398
1399CREATE TABLE manual_review_tool.routing_rules (
1400 id character varying(255) NOT NULL,
1401 org_id character varying(255) NOT NULL,
1402 name character varying(255) NOT NULL,
1403 description character varying(255),
1404 status manual_review_tool.routing_rule_status DEFAULT 'LIVE'::manual_review_tool.routing_rule_status NOT NULL,
1405 created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
1406 creator_id character varying(255) NOT NULL,
1407 condition_set jsonb NOT NULL,
1408 sequence_number integer NOT NULL,
1409 destination_queue_id character varying(255) NOT NULL,
1410 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL
1411);
1412
1413
1414ALTER TABLE manual_review_tool.routing_rules OWNER TO CURRENT_USER;
1415
1416--
1417-- Name: routing_rule_versions; Type: MATERIALIZED VIEW; Schema: manual_review_tool; Owner: postgres
1418--
1419
1420CREATE MATERIALIZED VIEW manual_review_tool.routing_rule_versions AS
1421 WITH routing_rule_versions AS (
1422 SELECT routing_rules.id,
1423 routing_rules.name,
1424 routing_rules.org_id,
1425 routing_rules.description,
1426 routing_rules.status,
1427 routing_rules.creator_id,
1428 routing_rules.condition_set,
1429 routing_rules.sequence_number,
1430 routing_rules.destination_queue_id,
1431 routing_rules.sys_period
1432 FROM manual_review_tool.routing_rules
1433 UNION ALL
1434 SELECT routing_rule_history.id,
1435 routing_rule_history.name,
1436 routing_rule_history.org_id,
1437 routing_rule_history.description,
1438 routing_rule_history.status,
1439 routing_rule_history.creator_id,
1440 routing_rule_history.condition_set,
1441 routing_rule_history.sequence_number,
1442 routing_rule_history.destination_queue_id,
1443 routing_rule_history.sys_period
1444 FROM manual_review_tool.routing_rule_history
1445 ), routing_rules_max_period_starts AS (
1446 SELECT routing_rule_versions_1.id,
1447 max(lower(routing_rule_versions_1.sys_period)) AS max_period_start
1448 FROM routing_rule_versions routing_rule_versions_1
1449 GROUP BY routing_rule_versions_1.id
1450 )
1451 SELECT routing_rule_versions.id,
1452 routing_rule_versions.name,
1453 routing_rule_versions.org_id,
1454 routing_rule_versions.description,
1455 routing_rule_versions.status,
1456 routing_rule_versions.creator_id,
1457 routing_rule_versions.condition_set,
1458 routing_rule_versions.sequence_number,
1459 routing_rule_versions.destination_queue_id,
1460 lower(routing_rule_versions.sys_period) AS version,
1461 ((routing_rules_max_period_starts.max_period_start = lower(routing_rule_versions.sys_period)) AND upper_inf(routing_rule_versions.sys_period)) AS is_current
1462 FROM (routing_rule_versions
1463 JOIN routing_rules_max_period_starts ON (((routing_rules_max_period_starts.id)::text = (routing_rule_versions.id)::text)))
1464 WITH DATA;
1465
1466
1467ALTER TABLE manual_review_tool.routing_rule_versions OWNER TO CURRENT_USER;
1468
1469--
1470-- Name: routing_rule_latest_versions; Type: VIEW; Schema: manual_review_tool; Owner: postgres
1471--
1472
1473CREATE VIEW manual_review_tool.routing_rule_latest_versions AS
1474 SELECT routing_rule_versions.id AS routing_rule_id,
1475 to_char((routing_rule_versions.version AT TIME ZONE 'UTC'::text), 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'::text) AS version
1476 FROM manual_review_tool.routing_rule_versions
1477 WHERE (routing_rule_versions.is_current = true);
1478
1479
1480ALTER TABLE manual_review_tool.routing_rule_latest_versions OWNER TO CURRENT_USER;
1481
1482--
1483-- Name: routing_rules_to_item_types; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1484--
1485
1486CREATE TABLE manual_review_tool.routing_rules_to_item_types (
1487 item_type_id character varying(255) NOT NULL,
1488 routing_rule_id character varying(255) NOT NULL
1489);
1490
1491
1492ALTER TABLE manual_review_tool.routing_rules_to_item_types OWNER TO CURRENT_USER;
1493
1494--
1495-- Name: users_and_accessible_queues; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1496--
1497
1498CREATE TABLE manual_review_tool.users_and_accessible_queues (
1499 user_id character varying(255) NOT NULL,
1500 queue_id character varying(255) NOT NULL
1501);
1502
1503
1504ALTER TABLE manual_review_tool.users_and_accessible_queues OWNER TO CURRENT_USER;
1505
1506--
1507-- Name: users_and_favorite_mrt_queues; Type: TABLE; Schema: manual_review_tool; Owner: postgres
1508--
1509
1510CREATE TABLE manual_review_tool.users_and_favorite_mrt_queues (
1511 user_id character varying(255) NOT NULL,
1512 queue_id character varying(255) NOT NULL,
1513 org_id character varying(255) NOT NULL
1514);
1515
1516
1517ALTER TABLE manual_review_tool.users_and_favorite_mrt_queues OWNER TO CURRENT_USER;
1518
1519--
1520-- Name: models; Type: TABLE; Schema: models_service; Owner: postgres
1521--
1522
1523CREATE TABLE models_service.models (
1524 id character varying(255) NOT NULL,
1525 org_id character varying(255) NOT NULL,
1526 policy_id character varying NOT NULL,
1527 policy_semantic_version integer NOT NULL,
1528 status public.model_status NOT NULL,
1529 family public.model_family NOT NULL,
1530 version integer NOT NULL,
1531 item_requirement text,
1532 name character varying(100) NOT NULL
1533);
1534
1535
1536ALTER TABLE models_service.models OWNER TO CURRENT_USER;
1537
1538--
1539-- Name: org_to_partially_labeled_dataset; Type: TABLE; Schema: models_service; Owner: postgres
1540--
1541
1542CREATE TABLE models_service.org_to_partially_labeled_dataset (
1543 org_id text NOT NULL,
1544 partially_labeled_dataset_id text NOT NULL
1545);
1546
1547
1548ALTER TABLE models_service.org_to_partially_labeled_dataset OWNER TO CURRENT_USER;
1549
1550--
1551-- Name: unknown_labeled_items; Type: TABLE; Schema: models_service; Owner: postgres
1552--
1553
1554CREATE TABLE models_service.unknown_labeled_items (
1555 id character varying(255) NOT NULL,
1556 org_id character varying(255) NOT NULL,
1557 item_id character varying(255) NOT NULL,
1558 item_type_id character varying(255) NOT NULL,
1559 policy_id character varying(255) NOT NULL,
1560 policy_semantic_version integer NOT NULL,
1561 unknown_type public.unknown_type NOT NULL,
1562 sampling_strategy character varying(255) NOT NULL
1563);
1564
1565
1566ALTER TABLE models_service.unknown_labeled_items OWNER TO CURRENT_USER;
1567
1568--
1569-- Name: ncmec_org_settings; Type: TABLE; Schema: ncmec_reporting; Owner: postgres
1570--
1571
1572CREATE TABLE ncmec_reporting.ncmec_org_settings (
1573 org_id character varying(255) NOT NULL,
1574 username character varying(255) NOT NULL,
1575 password character varying(255) NOT NULL,
1576 contact_email character varying(255),
1577 more_info_url character varying(255),
1578 company_template character varying(255),
1579 legal_url character varying(255),
1580 created_at timestamp with time zone DEFAULT now() NOT NULL,
1581 updated_at timestamp with time zone DEFAULT now() NOT NULL,
1582 ncmec_preservation_endpoint character varying(255),
1583 ncmec_additional_info_endpoint character varying(255),
1584 policies_applied_to_actions_run_on_report_creation character varying(255)[],
1585 actions_to_run_upon_report_creation character varying(255)[],
1586 CONSTRAINT ncmec_org_settings_must_have_both_policy_and_actions_on_submiss CHECK ((((policies_applied_to_actions_run_on_report_creation IS NULL) AND (actions_to_run_upon_report_creation IS NULL)) OR ((policies_applied_to_actions_run_on_report_creation IS NOT NULL) AND (actions_to_run_upon_report_creation IS NOT NULL) AND (array_length(actions_to_run_upon_report_creation, 1) > 0) AND (array_length(policies_applied_to_actions_run_on_report_creation, 1) > 0))))
1587);
1588
1589
1590ALTER TABLE ncmec_reporting.ncmec_org_settings OWNER TO CURRENT_USER;
1591
1592--
1593-- Name: ncmec_reports; Type: TABLE; Schema: ncmec_reporting; Owner: postgres
1594--
1595
1596CREATE TABLE ncmec_reporting.ncmec_reports (
1597 org_id character varying(255) NOT NULL,
1598 report_id character varying(255) NOT NULL,
1599 user_id character varying(255) NOT NULL,
1600 created_at timestamp with time zone DEFAULT now() NOT NULL,
1601 updated_at timestamp with time zone DEFAULT now() NOT NULL,
1602 user_item_type_id character varying(255) NOT NULL,
1603 reported_media jsonb[] NOT NULL,
1604 report_xml xml NOT NULL,
1605 additional_files jsonb[],
1606 reviewer_id character varying(255),
1607 is_test boolean,
1608 reported_messages jsonb[],
1609 incident_type text,
1610 CONSTRAINT reported_media_check_non_empty CHECK ((array_length(reported_media, 1) > 0))
1611);
1612
1613
1614ALTER TABLE ncmec_reporting.ncmec_reports OWNER TO CURRENT_USER;
1615
1616--
1617-- Name: ncmec_reports_errors; Type: TABLE; Schema: ncmec_reporting; Owner: postgres
1618--
1619
1620CREATE TABLE ncmec_reporting.ncmec_reports_errors (
1621 job_id character varying(255) NOT NULL,
1622 user_id character varying(255) NOT NULL,
1623 user_type_id character varying(255) NOT NULL,
1624 status public.ncmec_report_error_status NOT NULL,
1625 retry_count integer NOT NULL,
1626 last_error character varying NOT NULL
1627);
1628
1629
1630ALTER TABLE ncmec_reporting.ncmec_reports_errors OWNER TO CURRENT_USER;
1631
1632--
1633-- Name: actions; Type: TABLE; Schema: public; Owner: postgres
1634--
1635
1636CREATE TABLE public.actions (
1637 id character varying(255) NOT NULL,
1638 name character varying(255) NOT NULL,
1639 description character varying(255),
1640 callback_url character varying(255),
1641 created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
1642 updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
1643 org_id character varying(255) NOT NULL,
1644 callback_url_headers jsonb,
1645 callback_url_body jsonb,
1646 penalty public.user_penalty_severity DEFAULT 'NONE'::public.user_penalty_severity NOT NULL,
1647 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL,
1648 action_type public.action_type DEFAULT 'CUSTOM_ACTION'::public.action_type NOT NULL,
1649 applies_to_all_items_of_kind public.item_type_kind[] DEFAULT ARRAY[]::public.item_type_kind[] NOT NULL,
1650 apply_user_strikes boolean DEFAULT false NOT NULL,
1651 custom_mrt_api_params jsonb[] DEFAULT '{}'::jsonb[] NOT NULL,
1652 CONSTRAINT callback_url_check CHECK ((((action_type = 'CUSTOM_ACTION'::public.action_type) AND (callback_url IS NOT NULL)) OR ((action_type <> 'CUSTOM_ACTION'::public.action_type) AND (callback_url IS NULL)))),
1653 CONSTRAINT valid_callback_args CHECK ((((callback_url_body IS NULL) OR (jsonb_typeof(callback_url_body) = 'object'::text)) AND ((callback_url_headers IS NULL) OR (jsonb_typeof(callback_url_headers) = 'object'::text))))
1654);
1655
1656
1657ALTER TABLE public.actions OWNER TO CURRENT_USER;
1658
1659--
1660-- Name: actions_history; Type: TABLE; Schema: public; Owner: postgres
1661--
1662
1663CREATE TABLE public.actions_history (
1664 id character varying(255) NOT NULL,
1665 name character varying(255) NOT NULL,
1666 description character varying(255),
1667 callback_url character varying(255),
1668 org_id character varying(255) NOT NULL,
1669 callback_url_headers jsonb,
1670 callback_url_body jsonb,
1671 penalty public.user_penalty_severity NOT NULL,
1672 sys_period tstzrange NOT NULL,
1673 action_type public.action_type DEFAULT 'CUSTOM_ACTION'::public.action_type NOT NULL,
1674 applies_to_all_items_of_kind public.item_type_kind[] DEFAULT ARRAY[]::public.item_type_kind[] NOT NULL
1675);
1676
1677
1678ALTER TABLE public.actions_history OWNER TO CURRENT_USER;
1679
1680--
1681-- Name: action_versions; Type: MATERIALIZED VIEW; Schema: public; Owner: postgres
1682--
1683
1684CREATE MATERIALIZED VIEW public.action_versions AS
1685 WITH action_versions AS (
1686 SELECT actions.id,
1687 actions.name,
1688 actions.description,
1689 actions.callback_url,
1690 actions.org_id,
1691 actions.callback_url_headers,
1692 actions.callback_url_body,
1693 actions.penalty,
1694 actions.sys_period,
1695 actions.applies_to_all_items_of_kind
1696 FROM public.actions
1697 UNION ALL
1698 SELECT actions_history.id,
1699 actions_history.name,
1700 actions_history.description,
1701 actions_history.callback_url,
1702 actions_history.org_id,
1703 actions_history.callback_url_headers,
1704 actions_history.callback_url_body,
1705 actions_history.penalty,
1706 actions_history.sys_period,
1707 actions_history.applies_to_all_items_of_kind
1708 FROM public.actions_history
1709 ), action_max_period_starts AS (
1710 SELECT action_versions_1.id,
1711 max(lower(action_versions_1.sys_period)) AS max_period_start
1712 FROM action_versions action_versions_1
1713 GROUP BY action_versions_1.id
1714 )
1715 SELECT action_versions.id,
1716 action_versions.name,
1717 action_versions.description,
1718 action_versions.callback_url,
1719 action_versions.org_id,
1720 action_versions.callback_url_headers,
1721 action_versions.callback_url_body,
1722 action_versions.penalty,
1723 action_versions.applies_to_all_items_of_kind,
1724 lower(action_versions.sys_period) AS version,
1725 ((action_max_period_starts.max_period_start = lower(action_versions.sys_period)) AND upper_inf(action_versions.sys_period)) AS is_current
1726 FROM (action_versions
1727 JOIN action_max_period_starts ON (((action_max_period_starts.id)::text = (action_versions.id)::text)))
1728 WITH DATA;
1729
1730
1731ALTER TABLE public.action_versions OWNER TO CURRENT_USER;
1732
1733--
1734-- Name: action_latest_versions; Type: VIEW; Schema: public; Owner: postgres
1735--
1736
1737CREATE VIEW public.action_latest_versions AS
1738 SELECT action_versions.id AS action_id,
1739 to_char((action_versions.version AT TIME ZONE 'UTC'::text), 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'::text) AS version
1740 FROM public.action_versions
1741 WHERE (action_versions.is_current = true);
1742
1743
1744ALTER TABLE public.action_latest_versions OWNER TO CURRENT_USER;
1745
1746--
1747-- Name: actions_and_item_types; Type: TABLE; Schema: public; Owner: postgres
1748--
1749
1750CREATE TABLE public.actions_and_item_types (
1751 created_at timestamp with time zone DEFAULT now() NOT NULL,
1752 updated_at timestamp with time zone DEFAULT now() NOT NULL,
1753 action_id character varying(255) NOT NULL,
1754 item_type_id character varying(255) NOT NULL,
1755 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL
1756);
1757
1758
1759ALTER TABLE public.actions_and_item_types OWNER TO CURRENT_USER;
1760
1761--
1762-- Name: actions_and_item_types_history; Type: TABLE; Schema: public; Owner: postgres
1763--
1764
1765CREATE TABLE public.actions_and_item_types_history (
1766 action_id character varying(255) NOT NULL,
1767 item_type_id character varying(255) NOT NULL,
1768 sys_period tstzrange NOT NULL
1769);
1770
1771
1772ALTER TABLE public.actions_and_item_types_history OWNER TO CURRENT_USER;
1773
1774--
1775-- Name: api_keys; Type: TABLE; Schema: public; Owner: postgres
1776--
1777
1778CREATE TABLE public.api_keys (
1779 id uuid DEFAULT gen_random_uuid() NOT NULL,
1780 org_id character varying(255) NOT NULL,
1781 key_hash character varying(255) NOT NULL,
1782 name character varying(255) NOT NULL,
1783 description text,
1784 is_active boolean DEFAULT true NOT NULL,
1785 created_at timestamp with time zone DEFAULT now() NOT NULL,
1786 updated_at timestamp with time zone DEFAULT now() NOT NULL,
1787 last_used_at timestamp with time zone,
1788 created_by character varying(255)
1789);
1790
1791
1792ALTER TABLE public.api_keys OWNER TO CURRENT_USER;
1793
1794--
1795-- Name: backtests; Type: TABLE; Schema: public; Owner: postgres
1796--
1797
1798CREATE TABLE public.backtests (
1799 id character varying(255) NOT NULL,
1800 rule_id character varying(255) NOT NULL,
1801 creator_id character varying(255) NOT NULL,
1802 sample_desired_size integer NOT NULL,
1803 sample_actual_size integer DEFAULT 0 NOT NULL,
1804 sample_start_at timestamp with time zone NOT NULL,
1805 sample_end_at timestamp with time zone NOT NULL,
1806 sampling_complete boolean DEFAULT false NOT NULL,
1807 content_items_processed integer DEFAULT 0 NOT NULL,
1808 content_items_matched integer DEFAULT 0 NOT NULL,
1809 created_at timestamp with time zone DEFAULT now() NOT NULL,
1810 updated_at timestamp with time zone NOT NULL,
1811 cancelation_date timestamp with time zone,
1812 status public.backtest_status GENERATED ALWAYS AS (
1813CASE
1814 WHEN (cancelation_date IS NOT NULL) THEN 'CANCELED'::public.backtest_status
1815 WHEN ((sampling_complete = false) OR (content_items_processed < sample_actual_size)) THEN 'RUNNING'::public.backtest_status
1816 ELSE 'COMPLETE'::public.backtest_status
1817END) STORED NOT NULL
1818);
1819
1820
1821ALTER TABLE public.backtests OWNER TO CURRENT_USER;
1822
1823--
1824-- Name: gdpr_delete_requests; Type: TABLE; Schema: public; Owner: postgres
1825--
1826
1827CREATE TABLE public.gdpr_delete_requests (
1828 request_id uuid NOT NULL,
1829 org_id character varying(255) NOT NULL,
1830 item_id text NOT NULL,
1831 item_type_id character varying(255) NOT NULL,
1832 created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
1833 fulfilled boolean DEFAULT false NOT NULL
1834);
1835
1836
1837ALTER TABLE public.gdpr_delete_requests OWNER TO CURRENT_USER;
1838
1839--
1840-- Name: invite_user_tokens; Type: TABLE; Schema: public; Owner: postgres
1841--
1842
1843CREATE TABLE public.invite_user_tokens (
1844 id integer NOT NULL,
1845 token character varying(255) NOT NULL,
1846 email character varying(255) NOT NULL,
1847 role character varying(255) NOT NULL,
1848 created_at timestamp with time zone DEFAULT now() NOT NULL,
1849 updated_at timestamp with time zone DEFAULT now() NOT NULL,
1850 org_id character varying(255)
1851);
1852
1853
1854ALTER TABLE public.invite_user_tokens OWNER TO CURRENT_USER;
1855
1856--
1857-- Name: invite_user_tokens_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
1858--
1859
1860CREATE SEQUENCE public.invite_user_tokens_id_seq
1861 AS integer
1862 START WITH 1
1863 INCREMENT BY 1
1864 NO MINVALUE
1865 NO MAXVALUE
1866 CACHE 1;
1867
1868
1869ALTER TABLE public.invite_user_tokens_id_seq OWNER TO CURRENT_USER;
1870
1871--
1872-- Name: invite_user_tokens_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
1873--
1874
1875ALTER SEQUENCE public.invite_user_tokens_id_seq OWNED BY public.invite_user_tokens.id;
1876
1877
1878--
1879-- Name: item_types; Type: TABLE; Schema: public; Owner: postgres
1880--
1881
1882CREATE TABLE public.item_types (
1883 id character varying(255) NOT NULL,
1884 org_id character varying(255) NOT NULL,
1885 name character varying(255) NOT NULL,
1886 description character varying(255),
1887 kind public.item_type_kind NOT NULL,
1888 fields jsonb[] NOT NULL,
1889 created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
1890 display_name_field character varying(255),
1891 creator_id_field character varying(255),
1892 thread_id_field character varying(255),
1893 parent_id_field character varying(255),
1894 created_at_field character varying(255),
1895 profile_icon_field character varying(255),
1896 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL,
1897 is_default_user boolean DEFAULT false NOT NULL,
1898 background_image_field character varying(255),
1899 is_deleted_field character varying(255),
1900 CONSTRAINT item_type_field_roles CHECK ((((kind = 'CONTENT'::public.item_type_kind) AND (profile_icon_field IS NULL) AND (background_image_field IS NULL)) OR ((kind = 'THREAD'::public.item_type_kind) AND (thread_id_field IS NULL) AND (parent_id_field IS NULL) AND (profile_icon_field IS NULL) AND (background_image_field IS NULL)) OR ((kind = 'USER'::public.item_type_kind) AND (thread_id_field IS NULL) AND (parent_id_field IS NULL) AND (creator_id_field IS NULL)))),
1901 CONSTRAINT valid_default_user CHECK (((is_default_user = false) OR (kind = 'USER'::public.item_type_kind))),
1902 CONSTRAINT valid_field_role_field_type CHECK ((((profile_icon_field IS NULL) OR jsonb_path_exists((array_to_json(fields))::jsonb, '$[*]?(@."name" == $"name" && @."type" == "IMAGE")'::jsonpath, jsonb_build_object('name', profile_icon_field))) AND ((background_image_field IS NULL) OR jsonb_path_exists((array_to_json(fields))::jsonb, '$[*]?(@."name" == $"name" && @."type" == "IMAGE")'::jsonpath, jsonb_build_object('name', background_image_field))) AND ((parent_id_field IS NULL) OR jsonb_path_exists((array_to_json(fields))::jsonb, '$[*]?(@."name" == $"name" && @."type" == "RELATED_ITEM")'::jsonpath, jsonb_build_object('name', parent_id_field))) AND ((thread_id_field IS NULL) OR jsonb_path_exists((array_to_json(fields))::jsonb, '$[*]?(@."name" == $"name" && @."type" == "RELATED_ITEM")'::jsonpath, jsonb_build_object('name', thread_id_field))) AND ((creator_id_field IS NULL) OR jsonb_path_exists((array_to_json(fields))::jsonb, '$[*]?(@."name" == $"name" && @."type" == "RELATED_ITEM")'::jsonpath, jsonb_build_object('name', creator_id_field))) AND ((display_name_field IS NULL) OR jsonb_path_exists((array_to_json(fields))::jsonb, '$[*]?(@."name" == $"name" && @."type" == "STRING")'::jsonpath, jsonb_build_object('name', display_name_field))) AND ((is_deleted_field IS NULL) OR jsonb_path_exists((array_to_json(fields))::jsonb, '$[*]?(@."name" == $"name" && @."type" == "BOOLEAN")'::jsonpath, jsonb_build_object('name', is_deleted_field))))),
1903 CONSTRAINT validate_content_parent_field_dependencies CHECK (((parent_id_field IS NULL) OR ((created_at_field IS NOT NULL) AND (thread_id_field IS NOT NULL)))),
1904 CONSTRAINT validate_content_thread_field_dependencies CHECK (((thread_id_field IS NULL) OR (created_at_field IS NOT NULL))),
1905 CONSTRAINT validate_thread_field_roles CHECK ((((parent_id_field IS NULL) OR (thread_id_field IS NOT NULL)) AND ((thread_id_field IS NULL) OR (created_at_field IS NOT NULL))))
1906);
1907
1908
1909ALTER TABLE public.item_types OWNER TO CURRENT_USER;
1910
1911--
1912-- Name: item_types_history; Type: TABLE; Schema: public; Owner: postgres
1913--
1914
1915CREATE TABLE public.item_types_history (
1916 id character varying(255) NOT NULL,
1917 org_id character varying(255) NOT NULL,
1918 name character varying(255) NOT NULL,
1919 description character varying(255),
1920 kind public.item_type_kind NOT NULL,
1921 fields jsonb[] NOT NULL,
1922 display_name_field character varying(255),
1923 creator_id_field character varying(255),
1924 thread_id_field character varying(255),
1925 parent_id_field character varying(255),
1926 created_at_field character varying(255),
1927 profile_icon_field character varying(255),
1928 sys_period tstzrange NOT NULL,
1929 is_default_user boolean DEFAULT false NOT NULL,
1930 background_image_field character varying(255),
1931 is_deleted_field character varying(255)
1932);
1933
1934
1935ALTER TABLE public.item_types_history OWNER TO CURRENT_USER;
1936
1937--
1938-- Name: item_type_versions; Type: MATERIALIZED VIEW; Schema: public; Owner: postgres
1939--
1940
1941CREATE MATERIALIZED VIEW public.item_type_versions AS
1942 WITH item_type_versions AS (
1943 SELECT item_types.id,
1944 item_types.name,
1945 item_types.description,
1946 item_types.fields,
1947 item_types.org_id,
1948 item_types.sys_period,
1949 item_types.kind,
1950 item_types.display_name_field,
1951 item_types.creator_id_field,
1952 item_types.thread_id_field,
1953 item_types.parent_id_field,
1954 item_types.created_at_field,
1955 item_types.is_deleted_field,
1956 item_types.profile_icon_field,
1957 item_types.background_image_field,
1958 item_types.is_default_user
1959 FROM public.item_types
1960 UNION ALL
1961 SELECT item_types_history.id,
1962 item_types_history.name,
1963 item_types_history.description,
1964 item_types_history.fields,
1965 item_types_history.org_id,
1966 item_types_history.sys_period,
1967 item_types_history.kind,
1968 item_types_history.display_name_field,
1969 item_types_history.creator_id_field,
1970 item_types_history.thread_id_field,
1971 item_types_history.parent_id_field,
1972 item_types_history.created_at_field,
1973 item_types_history.is_deleted_field,
1974 item_types_history.profile_icon_field,
1975 item_types_history.background_image_field,
1976 item_types_history.is_default_user
1977 FROM public.item_types_history
1978 ), item_type_max_period_starts AS (
1979 SELECT item_type_versions_1.id,
1980 max(lower(item_type_versions_1.sys_period)) AS max_period_start
1981 FROM item_type_versions item_type_versions_1
1982 GROUP BY item_type_versions_1.id
1983 )
1984 SELECT item_type_versions.id,
1985 item_type_versions.name,
1986 item_type_versions.description,
1987 item_type_versions.fields,
1988 item_type_versions.org_id,
1989 item_type_versions.kind,
1990 item_type_versions.display_name_field,
1991 item_type_versions.creator_id_field,
1992 item_type_versions.thread_id_field,
1993 item_type_versions.parent_id_field,
1994 item_type_versions.created_at_field,
1995 item_type_versions.is_deleted_field,
1996 item_type_versions.profile_icon_field,
1997 item_type_versions.background_image_field,
1998 item_type_versions.is_default_user,
1999 lower(item_type_versions.sys_period) AS version,
2000 ((item_type_max_period_starts.max_period_start = lower(item_type_versions.sys_period)) AND upper_inf(item_type_versions.sys_period)) AS is_current
2001 FROM (item_type_versions
2002 JOIN item_type_max_period_starts ON (((item_type_max_period_starts.id)::text = (item_type_versions.id)::text)))
2003 WITH DATA;
2004
2005
2006ALTER TABLE public.item_type_versions OWNER TO CURRENT_USER;
2007
2008--
2009-- Name: item_type_latest_versions; Type: VIEW; Schema: public; Owner: postgres
2010--
2011
2012CREATE VIEW public.item_type_latest_versions AS
2013 SELECT item_type_versions.id AS item_type_id,
2014 to_char((item_type_versions.version AT TIME ZONE 'UTC'::text), 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'::text) AS version
2015 FROM public.item_type_versions
2016 WHERE (item_type_versions.is_current = true);
2017
2018
2019ALTER TABLE public.item_type_latest_versions OWNER TO CURRENT_USER;
2020
2021--
2022-- Name: location_bank_locations; Type: TABLE; Schema: public; Owner: postgres
2023--
2024
2025CREATE TABLE public.location_bank_locations (
2026 id character varying(255) NOT NULL,
2027 bank_id character varying(255) NOT NULL,
2028 geometry jsonb NOT NULL,
2029 bounds jsonb,
2030 name character varying(255),
2031 google_place_info jsonb,
2032 created_at timestamp with time zone DEFAULT now() NOT NULL,
2033 updated_at timestamp with time zone DEFAULT now() NOT NULL
2034);
2035
2036
2037ALTER TABLE public.location_bank_locations OWNER TO CURRENT_USER;
2038
2039--
2040-- Name: location_banks; Type: TABLE; Schema: public; Owner: postgres
2041--
2042
2043CREATE TABLE public.location_banks (
2044 id character varying(255) NOT NULL,
2045 name character varying(255) NOT NULL,
2046 description character varying(255),
2047 org_id character varying(255) NOT NULL,
2048 owner_id character varying(255) NOT NULL,
2049 created_at timestamp with time zone DEFAULT now() NOT NULL,
2050 updated_at timestamp with time zone NOT NULL,
2051 full_places_api_responses jsonb[] DEFAULT ARRAY[]::jsonb[] NOT NULL
2052);
2053
2054
2055ALTER TABLE public.location_banks OWNER TO CURRENT_USER;
2056
2057--
2058-- Name: media_banks; Type: TABLE; Schema: public; Owner: postgres
2059--
2060
2061CREATE TABLE public.media_banks (
2062 id character varying(255) NOT NULL,
2063 name character varying(255) NOT NULL,
2064 description character varying(255),
2065 created_at timestamp with time zone NOT NULL,
2066 updated_at timestamp with time zone NOT NULL,
2067 org_id character varying(255) NOT NULL,
2068 owner_id character varying(255) NOT NULL
2069);
2070
2071
2072ALTER TABLE public.media_banks OWNER TO CURRENT_USER;
2073
2074--
2075-- Name: notifications; Type: TABLE; Schema: public; Owner: postgres
2076--
2077
2078CREATE TABLE public.notifications (
2079 id character varying(255) NOT NULL,
2080 "userId" character varying(255) NOT NULL,
2081 type character varying(255) NOT NULL,
2082 data jsonb NOT NULL,
2083 message text NOT NULL,
2084 "createdAt" timestamp with time zone DEFAULT now() NOT NULL,
2085 "readAt" timestamp with time zone
2086);
2087
2088
2089ALTER TABLE public.notifications OWNER TO CURRENT_USER;
2090
2091--
2092-- Name: org_settings; Type: TABLE; Schema: public; Owner: postgres
2093--
2094
2095CREATE TABLE public.org_settings (
2096 org_id character varying(255) NOT NULL,
2097 partial_items_endpoint character varying(255),
2098 has_reporting_rules_enabled boolean DEFAULT false,
2099 has_appeals_enabled boolean DEFAULT false,
2100 appeal_callback_url text,
2101 appeal_callback_headers jsonb,
2102 appeal_callback_body jsonb,
2103 allow_multiple_policies_per_action boolean DEFAULT false,
2104 user_strike_ttl_days integer DEFAULT 90,
2105 saml_enabled boolean DEFAULT false NOT NULL,
2106 sso_url character varying(255),
2107 cert text,
2108 is_demo_org boolean DEFAULT false NOT NULL,
2109 show_usage_statistics boolean DEFAULT false NOT NULL,
2110 partial_items_request_headers jsonb,
2111 CONSTRAINT saml_settings_constraint CHECK (((saml_enabled = false) OR ((saml_enabled = true) AND (sso_url IS NOT NULL) AND (cert IS NOT NULL))))
2112);
2113
2114
2115ALTER TABLE public.org_settings OWNER TO CURRENT_USER;
2116
2117--
2118-- Name: orgs; Type: TABLE; Schema: public; Owner: postgres
2119--
2120
2121CREATE TABLE public.orgs (
2122 id character varying(255) NOT NULL,
2123 email character varying(255) NOT NULL,
2124 name character varying(255) NOT NULL,
2125 website_url character varying(255) NOT NULL,
2126 api_key_id character varying(255),
2127 created_at timestamp with time zone NOT NULL,
2128 updated_at timestamp with time zone NOT NULL,
2129 on_call_alert_email character varying(255)
2130);
2131
2132
2133ALTER TABLE public.orgs OWNER TO CURRENT_USER;
2134
2135--
2136-- Name: policies; Type: TABLE; Schema: public; Owner: postgres
2137--
2138
2139CREATE TABLE public.policies (
2140 id character varying(255) NOT NULL,
2141 name character varying(255) NOT NULL,
2142 org_id character varying(255) NOT NULL,
2143 parent_id character varying(255),
2144 created_at timestamp with time zone DEFAULT now() NOT NULL,
2145 updated_at timestamp with time zone NOT NULL,
2146 penalty public.user_penalty_severity DEFAULT 'NONE'::public.user_penalty_severity NOT NULL,
2147 policy_text character varying,
2148 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL,
2149 policy_type public.policy_type,
2150 semantic_version integer DEFAULT 1 NOT NULL,
2151 enforcement_guidelines character varying,
2152 user_strike_count integer DEFAULT 1 NOT NULL,
2153 apply_user_strike_count_config_to_children boolean DEFAULT false NOT NULL
2154);
2155
2156
2157ALTER TABLE public.policies OWNER TO CURRENT_USER;
2158
2159--
2160-- Name: policy_history; Type: TABLE; Schema: public; Owner: postgres
2161--
2162
2163CREATE TABLE public.policy_history (
2164 id character varying(255) NOT NULL,
2165 name character varying(255) NOT NULL,
2166 org_id character varying(255) NOT NULL,
2167 parent_id character varying(255),
2168 penalty public.user_penalty_severity NOT NULL,
2169 policy_text character varying,
2170 sys_period tstzrange NOT NULL
2171);
2172
2173
2174ALTER TABLE public.policy_history OWNER TO CURRENT_USER;
2175
2176--
2177-- Name: policy_versions; Type: MATERIALIZED VIEW; Schema: public; Owner: postgres
2178--
2179
2180CREATE MATERIALIZED VIEW public.policy_versions AS
2181 WITH policy_versions AS (
2182 SELECT policies.id,
2183 policies.name,
2184 policies.org_id,
2185 policies.parent_id,
2186 policies.penalty,
2187 policies.sys_period
2188 FROM public.policies
2189 UNION ALL
2190 SELECT policy_history.id,
2191 policy_history.name,
2192 policy_history.org_id,
2193 policy_history.parent_id,
2194 policy_history.penalty,
2195 policy_history.sys_period
2196 FROM public.policy_history
2197 ), policies_max_period_starts AS (
2198 SELECT policy_versions_1.id,
2199 max(lower(policy_versions_1.sys_period)) AS max_period_start
2200 FROM policy_versions policy_versions_1
2201 GROUP BY policy_versions_1.id
2202 )
2203 SELECT policy_versions.id,
2204 policy_versions.name,
2205 policy_versions.org_id,
2206 policy_versions.parent_id,
2207 policy_versions.penalty,
2208 lower(policy_versions.sys_period) AS version,
2209 ((policies_max_period_starts.max_period_start = lower(policy_versions.sys_period)) AND upper_inf(policy_versions.sys_period)) AS is_current
2210 FROM (policy_versions
2211 JOIN policies_max_period_starts ON (((policies_max_period_starts.id)::text = (policy_versions.id)::text)))
2212 WITH DATA;
2213
2214
2215ALTER TABLE public.policy_versions OWNER TO CURRENT_USER;
2216
2217--
2218-- Name: policy_latest_versions; Type: VIEW; Schema: public; Owner: postgres
2219--
2220
2221CREATE VIEW public.policy_latest_versions AS
2222 SELECT policy_versions.id AS policy_id,
2223 to_char((policy_versions.version AT TIME ZONE 'UTC'::text), 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'::text) AS version
2224 FROM public.policy_versions
2225 WHERE (policy_versions.is_current = true);
2226
2227
2228ALTER TABLE public.policy_latest_versions OWNER TO CURRENT_USER;
2229
2230--
2231-- Name: rules; Type: TABLE; Schema: public; Owner: postgres
2232--
2233
2234CREATE TABLE public.rules (
2235 id character varying(255) NOT NULL,
2236 name character varying(255) NOT NULL,
2237 description character varying(255),
2238 status_if_unexpired character varying(255) DEFAULT 'DRAFT'::character varying NOT NULL,
2239 tags character varying(255)[] DEFAULT (ARRAY[]::character varying[])::character varying(255)[] NOT NULL,
2240 max_daily_actions integer,
2241 daily_actions_run integer DEFAULT 0,
2242 last_action_date date,
2243 created_at timestamp with time zone NOT NULL,
2244 updated_at timestamp with time zone NOT NULL,
2245 org_id character varying(255) NOT NULL,
2246 creator_id character varying(255) NOT NULL,
2247 expiration_time timestamp with time zone,
2248 condition_set jsonb NOT NULL,
2249 alarm_status public.enum_rule_alarm_status DEFAULT 'INSUFFICIENT_DATA'::public.enum_rule_alarm_status NOT NULL,
2250 alarm_status_set_at timestamp with time zone DEFAULT now() NOT NULL,
2251 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL,
2252 rule_type public.rule_type DEFAULT 'CONTENT'::public.rule_type NOT NULL,
2253 parent_id character varying(255),
2254 rate_limit_config jsonb
2255);
2256
2257
2258ALTER TABLE public.rules OWNER TO CURRENT_USER;
2259
2260--
2261-- Name: rules_and_actions; Type: TABLE; Schema: public; Owner: postgres
2262--
2263
2264CREATE TABLE public.rules_and_actions (
2265 created_at timestamp with time zone DEFAULT now() NOT NULL,
2266 updated_at timestamp with time zone DEFAULT now() NOT NULL,
2267 action_id character varying(255) NOT NULL,
2268 rule_id character varying(255) NOT NULL,
2269 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL
2270);
2271
2272
2273ALTER TABLE public.rules_and_actions OWNER TO CURRENT_USER;
2274
2275--
2276-- Name: rules_and_actions_history; Type: TABLE; Schema: public; Owner: postgres
2277--
2278
2279CREATE TABLE public.rules_and_actions_history (
2280 action_id character varying(255) NOT NULL,
2281 rule_id character varying(255) NOT NULL,
2282 sys_period tstzrange NOT NULL
2283);
2284
2285
2286ALTER TABLE public.rules_and_actions_history OWNER TO CURRENT_USER;
2287
2288--
2289-- Name: rules_and_item_types; Type: TABLE; Schema: public; Owner: postgres
2290--
2291
2292CREATE TABLE public.rules_and_item_types (
2293 created_at timestamp with time zone DEFAULT now() NOT NULL,
2294 updated_at timestamp with time zone DEFAULT now() NOT NULL,
2295 item_type_id character varying(255) NOT NULL,
2296 rule_id character varying(255) NOT NULL,
2297 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL
2298);
2299
2300
2301ALTER TABLE public.rules_and_item_types OWNER TO CURRENT_USER;
2302
2303--
2304-- Name: rules_and_item_types_history; Type: TABLE; Schema: public; Owner: postgres
2305--
2306
2307CREATE TABLE public.rules_and_item_types_history (
2308 item_type_id character varying(255) NOT NULL,
2309 rule_id character varying(255) NOT NULL,
2310 sys_period tstzrange NOT NULL
2311);
2312
2313
2314ALTER TABLE public.rules_and_item_types_history OWNER TO CURRENT_USER;
2315
2316--
2317-- Name: rules_and_policies; Type: TABLE; Schema: public; Owner: postgres
2318--
2319
2320CREATE TABLE public.rules_and_policies (
2321 created_at timestamp with time zone NOT NULL,
2322 updated_at timestamp with time zone NOT NULL,
2323 policy_id character varying(255) NOT NULL,
2324 rule_id character varying(255) NOT NULL,
2325 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL
2326);
2327
2328
2329ALTER TABLE public.rules_and_policies OWNER TO CURRENT_USER;
2330
2331--
2332-- Name: rules_and_policies_history; Type: TABLE; Schema: public; Owner: postgres
2333--
2334
2335CREATE TABLE public.rules_and_policies_history (
2336 policy_id character varying(255) NOT NULL,
2337 rule_id character varying(255) NOT NULL,
2338 sys_period tstzrange NOT NULL
2339);
2340
2341
2342ALTER TABLE public.rules_and_policies_history OWNER TO CURRENT_USER;
2343
2344--
2345-- Name: rules_history; Type: TABLE; Schema: public; Owner: postgres
2346--
2347
2348CREATE TABLE public.rules_history (
2349 id character varying(255) NOT NULL,
2350 name character varying(255) NOT NULL,
2351 status_if_unexpired character varying(255) NOT NULL,
2352 tags character varying(255)[] NOT NULL,
2353 max_daily_actions integer,
2354 org_id character varying(255) NOT NULL,
2355 creator_id character varying(255) NOT NULL,
2356 expiration_time timestamp with time zone,
2357 condition_set jsonb NOT NULL,
2358 sys_period tstzrange NOT NULL,
2359 description character varying(255),
2360 rule_type public.rule_type DEFAULT 'CONTENT'::public.rule_type NOT NULL,
2361 parent_id character varying(255),
2362 rate_limit_config jsonb
2363);
2364
2365
2366ALTER TABLE public.rules_history OWNER TO CURRENT_USER;
2367
2368--
2369-- Name: rule_versions; Type: MATERIALIZED VIEW; Schema: public; Owner: postgres
2370--
2371
2372CREATE MATERIALIZED VIEW public.rule_versions AS
2373 WITH action_association_versions AS (
2374 SELECT rules_and_actions.action_id,
2375 rules_and_actions.rule_id,
2376 rules_and_actions.sys_period
2377 FROM public.rules_and_actions
2378 UNION ALL
2379 SELECT rules_and_actions_history.action_id,
2380 rules_and_actions_history.rule_id,
2381 rules_and_actions_history.sys_period
2382 FROM public.rules_and_actions_history
2383 ), item_type_association_versions AS (
2384 SELECT rules_and_item_types.item_type_id,
2385 rules_and_item_types.rule_id,
2386 rules_and_item_types.sys_period
2387 FROM public.rules_and_item_types
2388 UNION ALL
2389 SELECT rules_and_item_types_history.item_type_id,
2390 rules_and_item_types_history.rule_id,
2391 rules_and_item_types_history.sys_period
2392 FROM public.rules_and_item_types_history
2393 ), policy_association_versions AS (
2394 SELECT rules_and_policies.policy_id,
2395 rules_and_policies.rule_id,
2396 rules_and_policies.sys_period
2397 FROM public.rules_and_policies
2398 UNION ALL
2399 SELECT rules_and_policies_history.policy_id,
2400 rules_and_policies_history.rule_id,
2401 rules_and_policies_history.sys_period
2402 FROM public.rules_and_policies_history
2403 ), rule_versions AS (
2404 SELECT rules.id,
2405 rules.name,
2406 rules.description,
2407 rules.status_if_unexpired,
2408 rules.tags,
2409 rules.max_daily_actions,
2410 rules.org_id,
2411 rules.creator_id,
2412 rules.expiration_time,
2413 rules.condition_set,
2414 rules.rule_type,
2415 rules.sys_period
2416 FROM public.rules
2417 UNION ALL
2418 SELECT rules_history.id,
2419 rules_history.name,
2420 rules_history.description,
2421 rules_history.status_if_unexpired,
2422 rules_history.tags,
2423 rules_history.max_daily_actions,
2424 rules_history.org_id,
2425 rules_history.creator_id,
2426 rules_history.expiration_time,
2427 rules_history.condition_set,
2428 rules_history.rule_type,
2429 rules_history.sys_period
2430 FROM public.rules_history
2431 ), rule_change_times AS (
2432 SELECT t.rule_id,
2433 unnest(t.change_times_with_duplicates) AS change_time
2434 FROM ( SELECT rule_versions_1.id AS rule_id,
2435 array_remove(((((((array_agg(DISTINCT lower(rule_versions_1.sys_period)) || array_agg(DISTINCT lower(action_association_versions.sys_period))) || array_agg(DISTINCT upper(action_association_versions.sys_period))) || array_agg(DISTINCT lower(item_type_association_versions.sys_period))) || array_agg(DISTINCT upper(item_type_association_versions.sys_period))) || array_agg(DISTINCT lower(policy_association_versions.sys_period))) || array_agg(DISTINCT upper(policy_association_versions.sys_period))), NULL::timestamp with time zone) AS change_times_with_duplicates
2436 FROM (((rule_versions rule_versions_1
2437 LEFT JOIN action_association_versions ON (((rule_versions_1.id)::text = (action_association_versions.rule_id)::text)))
2438 LEFT JOIN item_type_association_versions ON (((rule_versions_1.id)::text = (item_type_association_versions.rule_id)::text)))
2439 LEFT JOIN policy_association_versions ON (((rule_versions_1.id)::text = (policy_association_versions.rule_id)::text)))
2440 GROUP BY rule_versions_1.id) t
2441 GROUP BY t.rule_id, (unnest(t.change_times_with_duplicates))
2442 ), rule_max_change_times AS (
2443 SELECT rule_change_times_1.rule_id,
2444 max(rule_change_times_1.change_time) AS max_change_time
2445 FROM rule_change_times rule_change_times_1
2446 GROUP BY rule_change_times_1.rule_id
2447 )
2448 SELECT rule_versions.id,
2449 rule_versions.name,
2450 rule_versions.description,
2451 rule_versions.status_if_unexpired,
2452 rule_versions.tags,
2453 rule_versions.max_daily_actions,
2454 rule_versions.org_id,
2455 rule_versions.creator_id,
2456 rule_versions.expiration_time,
2457 rule_versions.rule_type,
2458 rule_versions.condition_set,
2459 ((rule_max_change_times.max_change_time = rule_change_times.change_time) AND upper_inf(rule_versions.sys_period)) AS is_current,
2460 ( SELECT COALESCE(array_agg(action_association_versions.action_id), '{}'::character varying[]) AS "coalesce"
2461 FROM action_association_versions
2462 WHERE (((action_association_versions.rule_id)::text = (rule_versions.id)::text) AND (action_association_versions.sys_period @> rule_change_times.change_time))) AS action_ids,
2463 ( SELECT COALESCE(array_agg(item_type_association_versions.item_type_id), '{}'::character varying[]) AS "coalesce"
2464 FROM item_type_association_versions
2465 WHERE (((item_type_association_versions.rule_id)::text = (rule_versions.id)::text) AND (item_type_association_versions.sys_period @> rule_change_times.change_time))) AS item_type_ids,
2466 ( SELECT COALESCE(array_agg(policy_association_versions.policy_id), '{}'::character varying[]) AS "coalesce"
2467 FROM policy_association_versions
2468 WHERE (((policy_association_versions.rule_id)::text = (rule_versions.id)::text) AND (policy_association_versions.sys_period @> rule_change_times.change_time))) AS policy_ids,
2469 rule_change_times.change_time AS version
2470 FROM ((rule_change_times
2471 JOIN rule_versions ON ((((rule_change_times.rule_id)::text = (rule_versions.id)::text) AND (rule_versions.sys_period @> rule_change_times.change_time))))
2472 JOIN rule_max_change_times ON (((rule_max_change_times.rule_id)::text = (rule_versions.id)::text)))
2473 ORDER BY rule_versions.id, rule_change_times.change_time
2474 WITH DATA;
2475
2476
2477ALTER TABLE public.rule_versions OWNER TO CURRENT_USER;
2478
2479--
2480-- Name: rules_latest_versions; Type: VIEW; Schema: public; Owner: postgres
2481--
2482
2483CREATE VIEW public.rules_latest_versions AS
2484 SELECT rule_versions.id AS rule_id,
2485 to_char((rule_versions.version AT TIME ZONE 'UTC'::text), 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'::text) AS version
2486 FROM public.rule_versions
2487 WHERE (rule_versions.is_current = true);
2488
2489
2490ALTER TABLE public.rules_latest_versions OWNER TO CURRENT_USER;
2491
2492--
2493-- Name: session; Type: TABLE; Schema: public; Owner: postgres
2494--
2495
2496CREATE TABLE public.session (
2497 sid character varying NOT NULL,
2498 sess json NOT NULL,
2499 expire timestamp(6) without time zone NOT NULL
2500);
2501
2502
2503ALTER TABLE public.session OWNER TO CURRENT_USER;
2504
2505--
2506-- Name: signing_keys; Type: TABLE; Schema: public; Owner: postgres
2507--
2508
2509CREATE TABLE public.signing_keys (
2510 org_id character varying(255) NOT NULL,
2511 key_data jsonb NOT NULL,
2512 created_at timestamp with time zone DEFAULT now() NOT NULL,
2513 updated_at timestamp with time zone DEFAULT now() NOT NULL
2514);
2515
2516
2517ALTER TABLE public.signing_keys OWNER TO CURRENT_USER;
2518
2519--
2520-- Name: text_banks; Type: TABLE; Schema: public; Owner: postgres
2521--
2522
2523CREATE TABLE public.text_banks (
2524 id character varying(255) NOT NULL,
2525 name character varying(255) NOT NULL,
2526 description character varying(255),
2527 created_at timestamp with time zone DEFAULT now() NOT NULL,
2528 updated_at timestamp with time zone NOT NULL,
2529 org_id character varying(255) NOT NULL,
2530 owner_id character varying(255),
2531 type public.text_bank_type DEFAULT 'STRING'::public.text_bank_type NOT NULL,
2532 strings character varying(2000)[] DEFAULT (ARRAY[]::character varying[])::character varying(2000)[] NOT NULL
2533);
2534
2535
2536ALTER TABLE public.text_banks OWNER TO CURRENT_USER;
2537
2538--
2539-- Name: user_strike_thresholds; Type: TABLE; Schema: public; Owner: postgres
2540--
2541
2542CREATE TABLE public.user_strike_thresholds (
2543 id integer NOT NULL,
2544 org_id character varying(255) NOT NULL,
2545 threshold integer NOT NULL,
2546 actions character varying(255)[] DEFAULT (ARRAY[]::character varying[])::character varying(255)[] NOT NULL,
2547 CONSTRAINT user_strike_thresholds_threshold_check CHECK ((threshold > 0))
2548);
2549
2550
2551ALTER TABLE public.user_strike_thresholds OWNER TO CURRENT_USER;
2552
2553--
2554-- Name: user_strike_thresholds_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
2555--
2556
2557ALTER TABLE public.user_strike_thresholds ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
2558 SEQUENCE NAME public.user_strike_thresholds_id_seq
2559 START WITH 1
2560 INCREMENT BY 1
2561 NO MINVALUE
2562 NO MAXVALUE
2563 CACHE 1
2564);
2565
2566
2567--
2568-- Name: users; Type: TABLE; Schema: public; Owner: postgres
2569--
2570
2571CREATE TABLE public.users (
2572 id character varying(255) NOT NULL,
2573 email character varying(255) NOT NULL,
2574 password character varying(255),
2575 first_name character varying(255) NOT NULL,
2576 last_name character varying(255) NOT NULL,
2577 role character varying(255) DEFAULT 'ADMIN'::character varying NOT NULL,
2578 approved_by_admin boolean DEFAULT false,
2579 rejected_by_admin boolean DEFAULT false,
2580 created_at timestamp with time zone NOT NULL,
2581 updated_at timestamp with time zone NOT NULL,
2582 org_id character varying(255) NOT NULL,
2583 login_methods public.login_method_enum[] DEFAULT ARRAY['password'::public.login_method_enum] NOT NULL,
2584 CONSTRAINT password_null_when_not_present CHECK ((((password IS NOT NULL) AND ('password'::public.login_method_enum = ANY (login_methods))) OR ((password IS NULL) AND (NOT ('password'::public.login_method_enum = ANY (login_methods))))))
2585);
2586
2587
2588ALTER TABLE public.users OWNER TO CURRENT_USER;
2589
2590--
2591-- Name: users_and_favorite_rules; Type: TABLE; Schema: public; Owner: postgres
2592--
2593
2594CREATE TABLE public.users_and_favorite_rules (
2595 user_id character varying(255) NOT NULL,
2596 rule_id character varying(255) NOT NULL,
2597 created_at timestamp with time zone DEFAULT now() NOT NULL,
2598 updated_at timestamp with time zone NOT NULL
2599);
2600
2601
2602ALTER TABLE public.users_and_favorite_rules OWNER TO CURRENT_USER;
2603
2604--
2605-- Name: view_maintenance_metadata; Type: TABLE; Schema: public; Owner: postgres
2606--
2607
2608CREATE TABLE public.view_maintenance_metadata (
2609 table_name text NOT NULL,
2610 last_insert timestamp without time zone DEFAULT '-infinity'::timestamp without time zone NOT NULL
2611);
2612
2613
2614ALTER TABLE public.view_maintenance_metadata OWNER TO CURRENT_USER;
2615
2616--
2617-- Name: reporting_rule_history; Type: TABLE; Schema: reporting_rules; Owner: postgres
2618--
2619
2620CREATE TABLE reporting_rules.reporting_rule_history (
2621 id character varying(255) NOT NULL,
2622 org_id character varying(255) NOT NULL,
2623 name character varying(255) NOT NULL,
2624 description character varying(255),
2625 status reporting_rules.reporting_rule_status NOT NULL,
2626 creator_id character varying(255) NOT NULL,
2627 condition_set jsonb NOT NULL,
2628 sys_period tstzrange NOT NULL
2629);
2630
2631
2632ALTER TABLE reporting_rules.reporting_rule_history OWNER TO CURRENT_USER;
2633
2634--
2635-- Name: reporting_rules; Type: TABLE; Schema: reporting_rules; Owner: postgres
2636--
2637
2638CREATE TABLE reporting_rules.reporting_rules (
2639 id character varying(255) NOT NULL,
2640 org_id character varying(255) NOT NULL,
2641 name character varying(255) NOT NULL,
2642 description character varying(255),
2643 status reporting_rules.reporting_rule_status DEFAULT 'DRAFT'::reporting_rules.reporting_rule_status NOT NULL,
2644 created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
2645 creator_id character varying(255) NOT NULL,
2646 condition_set jsonb NOT NULL,
2647 sys_period tstzrange DEFAULT tstzrange(CURRENT_TIMESTAMP, NULL::timestamp with time zone) NOT NULL
2648);
2649
2650
2651ALTER TABLE reporting_rules.reporting_rules OWNER TO CURRENT_USER;
2652
2653--
2654-- Name: reporting_rule_versions; Type: MATERIALIZED VIEW; Schema: reporting_rules; Owner: postgres
2655--
2656
2657CREATE MATERIALIZED VIEW reporting_rules.reporting_rule_versions AS
2658 WITH reporting_rule_versions AS (
2659 SELECT reporting_rules.id,
2660 reporting_rules.name,
2661 reporting_rules.org_id,
2662 reporting_rules.description,
2663 reporting_rules.status,
2664 reporting_rules.creator_id,
2665 reporting_rules.condition_set,
2666 reporting_rules.sys_period
2667 FROM reporting_rules.reporting_rules
2668 UNION ALL
2669 SELECT reporting_rule_history.id,
2670 reporting_rule_history.name,
2671 reporting_rule_history.org_id,
2672 reporting_rule_history.description,
2673 reporting_rule_history.status,
2674 reporting_rule_history.creator_id,
2675 reporting_rule_history.condition_set,
2676 reporting_rule_history.sys_period
2677 FROM reporting_rules.reporting_rule_history
2678 ), reporting_rules_max_period_starts AS (
2679 SELECT reporting_rule_versions_1.id,
2680 max(lower(reporting_rule_versions_1.sys_period)) AS max_period_start
2681 FROM reporting_rule_versions reporting_rule_versions_1
2682 GROUP BY reporting_rule_versions_1.id
2683 )
2684 SELECT reporting_rule_versions.id,
2685 reporting_rule_versions.name,
2686 reporting_rule_versions.org_id,
2687 reporting_rule_versions.description,
2688 reporting_rule_versions.status,
2689 reporting_rule_versions.creator_id,
2690 reporting_rule_versions.condition_set,
2691 lower(reporting_rule_versions.sys_period) AS version,
2692 ((reporting_rules_max_period_starts.max_period_start = lower(reporting_rule_versions.sys_period)) AND upper_inf(reporting_rule_versions.sys_period)) AS is_current
2693 FROM (reporting_rule_versions
2694 JOIN reporting_rules_max_period_starts ON (((reporting_rules_max_period_starts.id)::text = (reporting_rule_versions.id)::text)))
2695 WITH DATA;
2696
2697
2698ALTER TABLE reporting_rules.reporting_rule_versions OWNER TO CURRENT_USER;
2699
2700--
2701-- Name: reporting_rule_latest_versions; Type: VIEW; Schema: reporting_rules; Owner: postgres
2702--
2703
2704CREATE VIEW reporting_rules.reporting_rule_latest_versions AS
2705 SELECT reporting_rule_versions.id AS reporting_rule_id,
2706 to_char((reporting_rule_versions.version AT TIME ZONE 'UTC'::text), 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"'::text) AS version
2707 FROM reporting_rules.reporting_rule_versions
2708 WHERE (reporting_rule_versions.is_current = true);
2709
2710
2711ALTER TABLE reporting_rules.reporting_rule_latest_versions OWNER TO CURRENT_USER;
2712
2713--
2714-- Name: reporting_rules_to_actions; Type: TABLE; Schema: reporting_rules; Owner: postgres
2715--
2716
2717CREATE TABLE reporting_rules.reporting_rules_to_actions (
2718 action_id character varying(255) NOT NULL,
2719 reporting_rule_id character varying(255) NOT NULL,
2720 created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
2721);
2722
2723
2724ALTER TABLE reporting_rules.reporting_rules_to_actions OWNER TO CURRENT_USER;
2725
2726--
2727-- Name: reporting_rules_to_item_types; Type: TABLE; Schema: reporting_rules; Owner: postgres
2728--
2729
2730CREATE TABLE reporting_rules.reporting_rules_to_item_types (
2731 item_type_id character varying(255) NOT NULL,
2732 reporting_rule_id character varying(255) NOT NULL,
2733 created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
2734);
2735
2736
2737ALTER TABLE reporting_rules.reporting_rules_to_item_types OWNER TO CURRENT_USER;
2738
2739--
2740-- Name: reporting_rules_to_policies; Type: TABLE; Schema: reporting_rules; Owner: postgres
2741--
2742
2743CREATE TABLE reporting_rules.reporting_rules_to_policies (
2744 policy_id character varying(255) NOT NULL,
2745 reporting_rule_id character varying(255) NOT NULL,
2746 created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL
2747);
2748
2749
2750ALTER TABLE reporting_rules.reporting_rules_to_policies OWNER TO CURRENT_USER;
2751
2752
2753--
2754-- Name: open_ai_configs; Type: TABLE; Schema: signal_auth_service; Owner: postgres
2755--
2756
2757CREATE TABLE signal_auth_service.open_ai_configs (
2758 org_id character varying(255) NOT NULL,
2759 created_at timestamp with time zone DEFAULT now() NOT NULL,
2760 updated_at timestamp with time zone DEFAULT now() NOT NULL,
2761 api_key character varying(255) NOT NULL
2762);
2763
2764
2765ALTER TABLE signal_auth_service.open_ai_configs OWNER TO CURRENT_USER;
2766
2767
2768--
2769-- Name: models_eligible_as_signals; Type: TABLE; Schema: signals_service; Owner: postgres
2770--
2771
2772CREATE TABLE signals_service.models_eligible_as_signals (
2773 org_id character varying(255) NOT NULL,
2774 model_id character varying(255) NOT NULL,
2775 version integer NOT NULL
2776);
2777
2778
2779ALTER TABLE signals_service.models_eligible_as_signals OWNER TO CURRENT_USER;
2780
2781--
2782-- Name: org_default_user_interface_settings; Type: TABLE; Schema: user_management_service; Owner: postgres
2783--
2784
2785CREATE TABLE user_management_service.org_default_user_interface_settings (
2786 org_id character varying(255) NOT NULL,
2787 moderator_safety_mute_video boolean DEFAULT true NOT NULL,
2788 moderator_safety_grayscale boolean DEFAULT true NOT NULL,
2789 moderator_safety_blur_level integer DEFAULT 2 NOT NULL
2790);
2791
2792
2793ALTER TABLE user_management_service.org_default_user_interface_settings OWNER TO CURRENT_USER;
2794
2795--
2796-- Name: password_reset_tokens; Type: TABLE; Schema: user_management_service; Owner: postgres
2797--
2798
2799CREATE TABLE user_management_service.password_reset_tokens (
2800 hashed_token character varying(255) NOT NULL,
2801 created_at timestamp with time zone NOT NULL,
2802 org_id character varying(255) NOT NULL,
2803 user_id character varying(255) NOT NULL
2804);
2805
2806
2807ALTER TABLE user_management_service.password_reset_tokens OWNER TO CURRENT_USER;
2808
2809--
2810-- Name: user_interface_settings; Type: TABLE; Schema: user_management_service; Owner: postgres
2811--
2812
2813CREATE TABLE user_management_service.user_interface_settings (
2814 user_id character varying(255) NOT NULL,
2815 moderator_safety_mute_video boolean,
2816 moderator_safety_grayscale boolean,
2817 moderator_safety_blur_level integer,
2818 mrt_chart_configurations jsonb[]
2819);
2820
2821
2822ALTER TABLE user_management_service.user_interface_settings OWNER TO CURRENT_USER;
2823
2824--
2825-- Name: user_scores; Type: TABLE; Schema: user_statistics_service; Owner: postgres
2826--
2827
2828CREATE TABLE user_statistics_service.user_scores (
2829 org_id character varying(255) NOT NULL,
2830 user_id character varying(255) NOT NULL,
2831 score double precision NOT NULL,
2832 user_type_id character varying(255) NOT NULL
2833);
2834
2835
2836ALTER TABLE user_statistics_service.user_scores OWNER TO CURRENT_USER;
2837
2838--
2839-- Name: invite_user_tokens id; Type: DEFAULT; Schema: public; Owner: postgres
2840--
2841
2842ALTER TABLE ONLY public.invite_user_tokens ALTER COLUMN id SET DEFAULT nextval('public.invite_user_tokens_id_seq'::regclass);
2843
2844--
2845-- Name: scheduled_jobs_info scheduled_jobs_info_pkey; Type: CONSTRAINT; Schema: jobs; Owner: postgres
2846--
2847
2848ALTER TABLE ONLY jobs.scheduled_jobs_info
2849 ADD CONSTRAINT scheduled_jobs_info_pkey PRIMARY KEY (job_name);
2850
2851
2852--
2853-- Name: appeals_routing_rules appeals_routing_rules_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2854--
2855
2856ALTER TABLE ONLY manual_review_tool.appeals_routing_rules
2857 ADD CONSTRAINT appeals_routing_rules_pkey PRIMARY KEY (id);
2858
2859
2860--
2861-- Name: appeals_routing_rules_to_item_types appeals_routing_rules_to_item_types_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2862--
2863
2864ALTER TABLE ONLY manual_review_tool.appeals_routing_rules_to_item_types
2865 ADD CONSTRAINT appeals_routing_rules_to_item_types_pkey PRIMARY KEY (item_type_id, appeals_routing_rule_id);
2866
2867
2868--
2869-- Name: job_comments job_comments_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2870--
2871
2872ALTER TABLE ONLY manual_review_tool.job_comments
2873 ADD CONSTRAINT job_comments_pkey PRIMARY KEY (id);
2874
2875
2876--
2877-- Name: job_creations job_creations_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2878--
2879
2880ALTER TABLE ONLY manual_review_tool.job_creations
2881 ADD CONSTRAINT job_creations_pkey PRIMARY KEY (id);
2882
2883
2884--
2885-- Name: manual_review_decisions manual_review_decisions_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2886--
2887
2888ALTER TABLE ONLY manual_review_tool.manual_review_decisions
2889 ADD CONSTRAINT manual_review_decisions_pkey PRIMARY KEY (id);
2890
2891
2892--
2893-- Name: manual_review_hidden_item_fields manual_review_hidden_item_fields_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2894--
2895
2896ALTER TABLE ONLY manual_review_tool.manual_review_hidden_item_fields
2897 ADD CONSTRAINT manual_review_hidden_item_fields_pkey PRIMARY KEY (org_id, item_type_id);
2898
2899
2900--
2901-- Name: manual_review_queues manual_review_queues_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2902--
2903
2904ALTER TABLE ONLY manual_review_tool.manual_review_queues
2905 ADD CONSTRAINT manual_review_queues_pkey PRIMARY KEY (id);
2906
2907
2908--
2909-- Name: manual_review_tool_settings manual_review_tool_settings_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2910--
2911
2912ALTER TABLE ONLY manual_review_tool.manual_review_tool_settings
2913 ADD CONSTRAINT manual_review_tool_settings_pkey PRIMARY KEY (org_id);
2914
2915
2916--
2917-- Name: queues_and_hidden_actions queues_and_actions_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2918--
2919
2920ALTER TABLE ONLY manual_review_tool.queues_and_hidden_actions
2921 ADD CONSTRAINT queues_and_actions_pkey PRIMARY KEY (queue_id, action_id);
2922
2923
2924--
2925-- Name: routing_rules routing_rules_org_id_name_key; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2926--
2927
2928ALTER TABLE ONLY manual_review_tool.routing_rules
2929 ADD CONSTRAINT routing_rules_org_id_name_key UNIQUE (org_id, name) DEFERRABLE INITIALLY DEFERRED;
2930
2931
2932--
2933-- Name: routing_rules routing_rules_org_id_sequence_number_key; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2934--
2935
2936ALTER TABLE ONLY manual_review_tool.routing_rules
2937 ADD CONSTRAINT routing_rules_org_id_sequence_number_key UNIQUE (org_id, sequence_number) DEFERRABLE INITIALLY DEFERRED;
2938
2939
2940--
2941-- Name: routing_rules routing_rules_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2942--
2943
2944ALTER TABLE ONLY manual_review_tool.routing_rules
2945 ADD CONSTRAINT routing_rules_pkey PRIMARY KEY (id);
2946
2947
2948--
2949-- Name: routing_rules_to_item_types routing_rules_to_item_types_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2950--
2951
2952ALTER TABLE ONLY manual_review_tool.routing_rules_to_item_types
2953 ADD CONSTRAINT routing_rules_to_item_types_pkey PRIMARY KEY (item_type_id, routing_rule_id);
2954
2955
2956--
2957-- Name: users_and_accessible_queues user_to_accessible_queues_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2958--
2959
2960ALTER TABLE ONLY manual_review_tool.users_and_accessible_queues
2961 ADD CONSTRAINT user_to_accessible_queues_pkey PRIMARY KEY (user_id, queue_id);
2962
2963
2964--
2965-- Name: users_and_favorite_mrt_queues users_and_favorite_mrt_queues_pkey; Type: CONSTRAINT; Schema: manual_review_tool; Owner: postgres
2966--
2967
2968ALTER TABLE ONLY manual_review_tool.users_and_favorite_mrt_queues
2969 ADD CONSTRAINT users_and_favorite_mrt_queues_pkey PRIMARY KEY (user_id, queue_id);
2970
2971
2972--
2973-- Name: models models_pkey; Type: CONSTRAINT; Schema: models_service; Owner: postgres
2974--
2975
2976ALTER TABLE ONLY models_service.models
2977 ADD CONSTRAINT models_pkey PRIMARY KEY (id);
2978
2979
2980--
2981-- Name: org_to_partially_labeled_dataset org_to_partially_labeled_dataset_pkey; Type: CONSTRAINT; Schema: models_service; Owner: postgres
2982--
2983
2984ALTER TABLE ONLY models_service.org_to_partially_labeled_dataset
2985 ADD CONSTRAINT org_to_partially_labeled_dataset_pkey PRIMARY KEY (org_id, partially_labeled_dataset_id);
2986
2987
2988--
2989-- Name: models unique_org_name; Type: CONSTRAINT; Schema: models_service; Owner: postgres
2990--
2991
2992ALTER TABLE ONLY models_service.models
2993 ADD CONSTRAINT unique_org_name UNIQUE (org_id, name);
2994
2995
2996--
2997-- Name: unknown_labeled_items unknown_labeled_items_pkey; Type: CONSTRAINT; Schema: models_service; Owner: postgres
2998--
2999
3000ALTER TABLE ONLY models_service.unknown_labeled_items
3001 ADD CONSTRAINT unknown_labeled_items_pkey PRIMARY KEY (id);
3002
3003
3004--
3005-- Name: ncmec_org_settings ncmec_org_settings_pkey; Type: CONSTRAINT; Schema: ncmec_reporting; Owner: postgres
3006--
3007
3008ALTER TABLE ONLY ncmec_reporting.ncmec_org_settings
3009 ADD CONSTRAINT ncmec_org_settings_pkey PRIMARY KEY (org_id);
3010
3011
3012--
3013-- Name: ncmec_reports_errors ncmec_reports_errors_pkey; Type: CONSTRAINT; Schema: ncmec_reporting; Owner: postgres
3014--
3015
3016ALTER TABLE ONLY ncmec_reporting.ncmec_reports_errors
3017 ADD CONSTRAINT ncmec_reports_errors_pkey PRIMARY KEY (job_id);
3018
3019
3020--
3021-- Name: ncmec_reports ncmec_reports_pkey; Type: CONSTRAINT; Schema: ncmec_reporting; Owner: postgres
3022--
3023
3024ALTER TABLE ONLY ncmec_reporting.ncmec_reports
3025 ADD CONSTRAINT ncmec_reports_pkey PRIMARY KEY (report_id);
3026
3027
3028--
3029-- Name: actions_and_item_types actions_and_item_types_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3030--
3031
3032ALTER TABLE ONLY public.actions_and_item_types
3033 ADD CONSTRAINT actions_and_item_types_pkey PRIMARY KEY (action_id, item_type_id);
3034
3035
3036--
3037-- Name: actions actions_org_id_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres
3038--
3039
3040ALTER TABLE ONLY public.actions
3041 ADD CONSTRAINT actions_org_id_name_key UNIQUE (org_id, name);
3042
3043
3044--
3045-- Name: actions actions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3046--
3047
3048ALTER TABLE ONLY public.actions
3049 ADD CONSTRAINT actions_pkey PRIMARY KEY (id);
3050
3051
3052--
3053-- Name: api_keys api_keys_key_hash_key; Type: CONSTRAINT; Schema: public; Owner: postgres
3054--
3055
3056ALTER TABLE ONLY public.api_keys
3057 ADD CONSTRAINT api_keys_key_hash_key UNIQUE (key_hash);
3058
3059
3060--
3061-- Name: api_keys api_keys_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3062--
3063
3064ALTER TABLE ONLY public.api_keys
3065 ADD CONSTRAINT api_keys_pkey PRIMARY KEY (id);
3066
3067
3068--
3069-- Name: backtests backtests_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3070--
3071
3072ALTER TABLE ONLY public.backtests
3073 ADD CONSTRAINT backtests_pkey PRIMARY KEY (id);
3074
3075
3076--
3077-- Name: invite_user_tokens invite_user_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3078--
3079
3080ALTER TABLE ONLY public.invite_user_tokens
3081 ADD CONSTRAINT invite_user_tokens_pkey PRIMARY KEY (id);
3082
3083
3084--
3085-- Name: item_types item_types_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3086--
3087
3088ALTER TABLE ONLY public.item_types
3089 ADD CONSTRAINT item_types_pkey PRIMARY KEY (id);
3090
3091
3092--
3093-- Name: location_bank_locations location_bank_locations_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3094--
3095
3096ALTER TABLE ONLY public.location_bank_locations
3097 ADD CONSTRAINT location_bank_locations_pkey PRIMARY KEY (id);
3098
3099
3100--
3101-- Name: location_banks location_banks_org_id_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres
3102--
3103
3104ALTER TABLE ONLY public.location_banks
3105 ADD CONSTRAINT location_banks_org_id_name_key UNIQUE (org_id, name);
3106
3107
3108--
3109-- Name: location_banks location_banks_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3110--
3111
3112ALTER TABLE ONLY public.location_banks
3113 ADD CONSTRAINT location_banks_pkey PRIMARY KEY (id);
3114
3115
3116--
3117-- Name: media_banks media_banks_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3118--
3119
3120ALTER TABLE ONLY public.media_banks
3121 ADD CONSTRAINT media_banks_pkey PRIMARY KEY (id);
3122
3123
3124--
3125-- Name: notifications notifications_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3126--
3127
3128ALTER TABLE ONLY public.notifications
3129 ADD CONSTRAINT notifications_pkey PRIMARY KEY (id);
3130
3131
3132--
3133-- Name: item_types org_id_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres
3134--
3135
3136ALTER TABLE ONLY public.item_types
3137 ADD CONSTRAINT org_id_name_key UNIQUE (org_id, name);
3138
3139
3140--
3141-- Name: org_settings org_settings_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3142--
3143
3144ALTER TABLE ONLY public.org_settings
3145 ADD CONSTRAINT org_settings_pkey PRIMARY KEY (org_id);
3146
3147
3148--
3149-- Name: orgs orgs_email_key; Type: CONSTRAINT; Schema: public; Owner: postgres
3150--
3151
3152ALTER TABLE ONLY public.orgs
3153 ADD CONSTRAINT orgs_email_key UNIQUE (email);
3154
3155
3156--
3157-- Name: orgs orgs_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres
3158--
3159
3160ALTER TABLE ONLY public.orgs
3161 ADD CONSTRAINT orgs_name_key UNIQUE (name);
3162
3163
3164--
3165-- Name: orgs orgs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3166--
3167
3168ALTER TABLE ONLY public.orgs
3169 ADD CONSTRAINT orgs_pkey PRIMARY KEY (id);
3170
3171
3172--
3173-- Name: orgs orgs_website_url_key; Type: CONSTRAINT; Schema: public; Owner: postgres
3174--
3175
3176ALTER TABLE ONLY public.orgs
3177 ADD CONSTRAINT orgs_website_url_key UNIQUE (website_url);
3178
3179
3180--
3181-- Name: policies policies_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3182--
3183
3184ALTER TABLE ONLY public.policies
3185 ADD CONSTRAINT policies_pkey PRIMARY KEY (id);
3186
3187
3188--
3189-- Name: rules_and_actions rules_and_actions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3190--
3191
3192ALTER TABLE ONLY public.rules_and_actions
3193 ADD CONSTRAINT rules_and_actions_pkey PRIMARY KEY (action_id, rule_id);
3194
3195
3196--
3197-- Name: rules_and_item_types rules_and_item_types_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3198--
3199
3200ALTER TABLE ONLY public.rules_and_item_types
3201 ADD CONSTRAINT rules_and_item_types_pkey PRIMARY KEY (rule_id, item_type_id);
3202
3203
3204--
3205-- Name: rules_and_policies rules_and_policies_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3206--
3207
3208ALTER TABLE ONLY public.rules_and_policies
3209 ADD CONSTRAINT rules_and_policies_pkey PRIMARY KEY (policy_id, rule_id);
3210
3211
3212--
3213-- Name: rules rules_org_id_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres
3214--
3215
3216ALTER TABLE ONLY public.rules
3217 ADD CONSTRAINT rules_org_id_name_key UNIQUE (org_id, name);
3218
3219
3220--
3221-- Name: rules rules_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3222--
3223
3224ALTER TABLE ONLY public.rules
3225 ADD CONSTRAINT rules_pkey PRIMARY KEY (id);
3226
3227
3228--
3229-- Name: session session_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3230--
3231
3232ALTER TABLE ONLY public.session
3233 ADD CONSTRAINT session_pkey PRIMARY KEY (sid);
3234
3235
3236--
3237-- Name: signing_keys signing_keys_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3238--
3239
3240ALTER TABLE ONLY public.signing_keys
3241 ADD CONSTRAINT signing_keys_pkey PRIMARY KEY (org_id);
3242
3243
3244--
3245-- Name: text_banks text_banks_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3246--
3247
3248ALTER TABLE ONLY public.text_banks
3249 ADD CONSTRAINT text_banks_pkey PRIMARY KEY (id);
3250
3251
3252--
3253-- Name: user_strike_thresholds unique_org_threshold_pair; Type: CONSTRAINT; Schema: public; Owner: postgres
3254--
3255
3256ALTER TABLE ONLY public.user_strike_thresholds
3257 ADD CONSTRAINT unique_org_threshold_pair UNIQUE (org_id, threshold);
3258
3259
3260--
3261-- Name: user_strike_thresholds user_strike_thresholds_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3262--
3263
3264ALTER TABLE ONLY public.user_strike_thresholds
3265 ADD CONSTRAINT user_strike_thresholds_pkey PRIMARY KEY (id);
3266
3267
3268--
3269-- Name: users_and_favorite_rules users_and_favorite_rules_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3270--
3271
3272ALTER TABLE ONLY public.users_and_favorite_rules
3273 ADD CONSTRAINT users_and_favorite_rules_pkey PRIMARY KEY (user_id, rule_id);
3274
3275
3276--
3277-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: postgres
3278--
3279
3280ALTER TABLE ONLY public.users
3281 ADD CONSTRAINT users_email_key UNIQUE (email);
3282
3283
3284--
3285-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3286--
3287
3288ALTER TABLE ONLY public.users
3289 ADD CONSTRAINT users_pkey PRIMARY KEY (id);
3290
3291
3292--
3293-- Name: view_maintenance_metadata view_maintenance_metadata_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
3294--
3295
3296ALTER TABLE ONLY public.view_maintenance_metadata
3297 ADD CONSTRAINT view_maintenance_metadata_pkey PRIMARY KEY (table_name);
3298
3299
3300--
3301-- Name: reporting_rules reporting_rules_org_id_name_key; Type: CONSTRAINT; Schema: reporting_rules; Owner: postgres
3302--
3303
3304ALTER TABLE ONLY reporting_rules.reporting_rules
3305 ADD CONSTRAINT reporting_rules_org_id_name_key UNIQUE (org_id, name) DEFERRABLE INITIALLY DEFERRED;
3306
3307
3308--
3309-- Name: reporting_rules reporting_rules_pkey; Type: CONSTRAINT; Schema: reporting_rules; Owner: postgres
3310--
3311
3312ALTER TABLE ONLY reporting_rules.reporting_rules
3313 ADD CONSTRAINT reporting_rules_pkey PRIMARY KEY (id);
3314
3315
3316--
3317-- Name: reporting_rules_to_actions reporting_rules_to_actions_pkey; Type: CONSTRAINT; Schema: reporting_rules; Owner: postgres
3318--
3319
3320ALTER TABLE ONLY reporting_rules.reporting_rules_to_actions
3321 ADD CONSTRAINT reporting_rules_to_actions_pkey PRIMARY KEY (action_id, reporting_rule_id);
3322
3323
3324--
3325-- Name: reporting_rules_to_item_types reporting_rules_to_item_types_pkey; Type: CONSTRAINT; Schema: reporting_rules; Owner: postgres
3326--
3327
3328ALTER TABLE ONLY reporting_rules.reporting_rules_to_item_types
3329 ADD CONSTRAINT reporting_rules_to_item_types_pkey PRIMARY KEY (item_type_id, reporting_rule_id);
3330
3331
3332--
3333-- Name: reporting_rules_to_policies reporting_rules_to_policies_pkey; Type: CONSTRAINT; Schema: reporting_rules; Owner: postgres
3334--
3335
3336ALTER TABLE ONLY reporting_rules.reporting_rules_to_policies
3337 ADD CONSTRAINT reporting_rules_to_policies_pkey PRIMARY KEY (policy_id, reporting_rule_id);
3338
3339
3340
3341--
3342-- Name: open_ai_configs open_ai_configs_pkey; Type: CONSTRAINT; Schema: signal_auth_service; Owner: postgres
3343--
3344
3345ALTER TABLE ONLY signal_auth_service.open_ai_configs
3346 ADD CONSTRAINT open_ai_configs_pkey PRIMARY KEY (org_id);
3347
3348
3349--
3350-- Name: models_eligible_as_signals models_eligible_as_signals_pkey; Type: CONSTRAINT; Schema: signals_service; Owner: postgres
3351--
3352
3353ALTER TABLE ONLY signals_service.models_eligible_as_signals
3354 ADD CONSTRAINT models_eligible_as_signals_pkey PRIMARY KEY (model_id, version);
3355
3356
3357--
3358-- Name: org_default_user_interface_settings org_default_user_interface_settings_pkey; Type: CONSTRAINT; Schema: user_management_service; Owner: postgres
3359--
3360
3361ALTER TABLE ONLY user_management_service.org_default_user_interface_settings
3362 ADD CONSTRAINT org_default_user_interface_settings_pkey PRIMARY KEY (org_id);
3363
3364
3365--
3366-- Name: password_reset_tokens password_reset_tokens_pkey; Type: CONSTRAINT; Schema: user_management_service; Owner: postgres
3367--
3368
3369ALTER TABLE ONLY user_management_service.password_reset_tokens
3370 ADD CONSTRAINT password_reset_tokens_pkey PRIMARY KEY (hashed_token);
3371
3372
3373--
3374-- Name: user_interface_settings user_interface_settings_pkey; Type: CONSTRAINT; Schema: user_management_service; Owner: postgres
3375--
3376
3377ALTER TABLE ONLY user_management_service.user_interface_settings
3378 ADD CONSTRAINT user_interface_settings_pkey PRIMARY KEY (user_id);
3379
3380
3381--
3382-- Name: user_scores user_scores_pkey; Type: CONSTRAINT; Schema: user_statistics_service; Owner: postgres
3383--
3384
3385ALTER TABLE ONLY user_statistics_service.user_scores
3386 ADD CONSTRAINT user_scores_pkey PRIMARY KEY (org_id, user_id, user_type_id);
3387
3388--
3389-- Name: appeals_routing_rules_org_id_name_key; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3390--
3391
3392CREATE UNIQUE INDEX appeals_routing_rules_org_id_name_key ON manual_review_tool.appeals_routing_rules USING btree (org_id, name);
3393
3394
3395--
3396-- Name: appeals_routing_rules_org_id_sequence_number_key; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3397--
3398
3399CREATE UNIQUE INDEX appeals_routing_rules_org_id_sequence_number_key ON manual_review_tool.appeals_routing_rules USING btree (org_id, sequence_number);
3400
3401
3402--
3403-- Name: idx_decisions_job_payload_id; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3404--
3405
3406CREATE INDEX idx_decisions_job_payload_id ON manual_review_tool.manual_review_decisions USING btree (((job_payload ->> 'id'::text)));
3407
3408
3409--
3410-- Name: idx_job_item_id; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3411--
3412
3413CREATE INDEX idx_job_item_id ON manual_review_tool.manual_review_decisions USING btree (((((job_payload -> 'payload'::text) -> 'item'::text) ->> 'itemId'::text)));
3414
3415
3416--
3417-- Name: jobs_created_at_brin_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3418--
3419
3420CREATE INDEX jobs_created_at_brin_idx ON manual_review_tool.job_creations USING brin (created_at);
3421
3422
3423--
3424-- Name: manual_decisions_view_dec_at_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3425--
3426
3427CREATE INDEX manual_decisions_view_dec_at_idx ON manual_review_tool.dim_mrt_decisions_materialized USING btree (decided_at);
3428
3429
3430--
3431-- Name: manual_review_decisions_created_at_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3432--
3433
3434CREATE INDEX manual_review_decisions_created_at_idx ON manual_review_tool.manual_review_decisions USING btree (created_at);
3435
3436
3437--
3438-- Name: manual_review_decisions_org_id; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3439--
3440
3441CREATE INDEX manual_review_decisions_org_id ON manual_review_tool.manual_review_queues USING btree (org_id);
3442
3443
3444--
3445-- Name: manual_review_queue_is_default; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3446--
3447
3448CREATE UNIQUE INDEX manual_review_queue_is_default ON manual_review_tool.manual_review_queues USING btree (org_id, is_default_queue, is_appeals_queue) WHERE (is_default_queue = true);
3449
3450
3451--
3452-- Name: manual_review_queue_org_id; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3453--
3454
3455CREATE INDEX manual_review_queue_org_id ON manual_review_tool.manual_review_queues USING btree (org_id);
3456
3457
3458--
3459-- Name: manual_review_queue_unique_name; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3460--
3461
3462CREATE UNIQUE INDEX manual_review_queue_unique_name ON manual_review_tool.manual_review_queues USING btree (org_id, name, is_appeals_queue);
3463
3464
3465--
3466-- Name: manual_review_tool.appeals_routing_rule_versions_id_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3467--
3468
3469CREATE INDEX "manual_review_tool.appeals_routing_rule_versions_id_idx" ON manual_review_tool.appeals_routing_rule_versions USING btree (id);
3470
3471
3472--
3473-- Name: manual_review_tool.appeals_routing_rule_versions_id_is_current_; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3474--
3475
3476CREATE UNIQUE INDEX "manual_review_tool.appeals_routing_rule_versions_id_is_current_" ON manual_review_tool.appeals_routing_rule_versions USING btree (id, is_current) WHERE (is_current = true);
3477
3478
3479--
3480-- Name: manual_review_tool.appeals_routing_rule_versions_is_current_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3481--
3482
3483CREATE INDEX "manual_review_tool.appeals_routing_rule_versions_is_current_idx" ON manual_review_tool.appeals_routing_rule_versions USING btree (is_current);
3484
3485
3486--
3487-- Name: manual_review_tool.appeals_routing_rule_versions_version_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3488--
3489
3490CREATE INDEX "manual_review_tool.appeals_routing_rule_versions_version_idx" ON manual_review_tool.appeals_routing_rule_versions USING btree (version);
3491
3492
3493--
3494-- Name: manual_review_tool.routing_rule_versions_id_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3495--
3496
3497CREATE INDEX "manual_review_tool.routing_rule_versions_id_idx" ON manual_review_tool.routing_rule_versions USING btree (id);
3498
3499
3500--
3501-- Name: manual_review_tool.routing_rule_versions_id_is_current_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3502--
3503
3504CREATE UNIQUE INDEX "manual_review_tool.routing_rule_versions_id_is_current_idx" ON manual_review_tool.routing_rule_versions USING btree (id, is_current) WHERE (is_current = true);
3505
3506
3507--
3508-- Name: manual_review_tool.routing_rule_versions_is_current_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3509--
3510
3511CREATE INDEX "manual_review_tool.routing_rule_versions_is_current_idx" ON manual_review_tool.routing_rule_versions USING btree (is_current);
3512
3513
3514--
3515-- Name: manual_review_tool.routing_rule_versions_version_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3516--
3517
3518CREATE INDEX "manual_review_tool.routing_rule_versions_version_idx" ON manual_review_tool.routing_rule_versions USING btree (version);
3519
3520
3521--
3522-- Name: mrt_decisions_created_at_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3523--
3524
3525CREATE INDEX mrt_decisions_created_at_idx ON manual_review_tool.manual_review_decisions USING btree (created_at);
3526
3527
3528--
3529-- Name: mrt_decisions_org_id_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3530--
3531
3532CREATE INDEX mrt_decisions_org_id_idx ON manual_review_tool.manual_review_decisions USING btree (org_id);
3533
3534
3535--
3536-- Name: mrt_materialized_decisions_unique_row_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3537--
3538
3539CREATE UNIQUE INDEX mrt_materialized_decisions_unique_row_idx ON manual_review_tool.dim_mrt_decisions_materialized USING btree (COALESCE(job_id, ''::text), COALESCE(action_id, ''::text), COALESCE(policy_id, ''::text), COALESCE(type, ''::text), COALESCE(item_id, ''::text), COALESCE(item_type_id, ''::text));
3540
3541
3542--
3543-- Name: user_favorite_mrt_queues_idx; Type: INDEX; Schema: manual_review_tool; Owner: postgres
3544--
3545
3546CREATE INDEX user_favorite_mrt_queues_idx ON manual_review_tool.users_and_favorite_mrt_queues USING btree (user_id);
3547
3548
3549--
3550-- Name: idx_user_type_org_test; Type: INDEX; Schema: ncmec_reporting; Owner: postgres
3551--
3552
3553CREATE INDEX idx_user_type_org_test ON ncmec_reporting.ncmec_reports USING btree (user_id, user_item_type_id, org_id) WHERE (is_test = false);
3554
3555
3556--
3557-- Name: actions_unique_enqueue_to_mrt_action_type; Type: INDEX; Schema: public; Owner: postgres
3558--
3559
3560CREATE UNIQUE INDEX actions_unique_enqueue_to_mrt_action_type ON public.actions USING btree (org_id, action_type) WHERE (action_type = 'ENQUEUE_TO_MRT'::public.action_type);
3561
3562
3563--
3564-- Name: idx_api_keys_active; Type: INDEX; Schema: public; Owner: postgres
3565--
3566
3567CREATE INDEX idx_api_keys_active ON public.api_keys USING btree (org_id, is_active) WHERE (is_active = true);
3568
3569
3570--
3571-- Name: idx_api_keys_key_hash; Type: INDEX; Schema: public; Owner: postgres
3572--
3573
3574CREATE INDEX idx_api_keys_key_hash ON public.api_keys USING btree (key_hash);
3575
3576
3577--
3578-- Name: idx_api_keys_org_id; Type: INDEX; Schema: public; Owner: postgres
3579--
3580
3581CREATE INDEX idx_api_keys_org_id ON public.api_keys USING btree (org_id);
3582
3583
3584--
3585-- Name: idx_session_expire; Type: INDEX; Schema: public; Owner: postgres
3586--
3587
3588CREATE INDEX idx_session_expire ON public.session USING btree (expire);
3589
3590
3591--
3592-- Name: idx_signing_keys_org_id; Type: INDEX; Schema: public; Owner: postgres
3593--
3594
3595CREATE INDEX idx_signing_keys_org_id ON public.signing_keys USING btree (org_id);
3596
3597
3598--
3599-- Name: idx_unique_orgid_name; Type: INDEX; Schema: public; Owner: postgres
3600--
3601
3602CREATE UNIQUE INDEX idx_unique_orgid_name ON public.policies USING btree (org_id, name);
3603
3604
3605--
3606-- Name: item_type_versions_id_idx; Type: INDEX; Schema: public; Owner: postgres
3607--
3608
3609CREATE INDEX item_type_versions_id_idx ON public.item_type_versions USING btree (id);
3610
3611
3612--
3613-- Name: item_type_versions_id_is_current_idx; Type: INDEX; Schema: public; Owner: postgres
3614--
3615
3616CREATE UNIQUE INDEX item_type_versions_id_is_current_idx ON public.item_type_versions USING btree (id, is_current) WHERE (is_current = true);
3617
3618
3619--
3620-- Name: item_type_versions_is_current_idx; Type: INDEX; Schema: public; Owner: postgres
3621--
3622
3623CREATE INDEX item_type_versions_is_current_idx ON public.item_type_versions USING btree (is_current);
3624
3625
3626--
3627-- Name: item_type_versions_version_idx; Type: INDEX; Schema: public; Owner: postgres
3628--
3629
3630CREATE INDEX item_type_versions_version_idx ON public.item_type_versions USING btree (version);
3631
3632
3633--
3634-- Name: item_types_is_default_user; Type: INDEX; Schema: public; Owner: postgres
3635--
3636
3637CREATE UNIQUE INDEX item_types_is_default_user ON public.item_types USING btree (org_id, is_default_user) WHERE (is_default_user = true);
3638
3639
3640--
3641-- Name: location_bank_locations_bank_id_idx; Type: INDEX; Schema: public; Owner: postgres
3642--
3643
3644CREATE INDEX location_bank_locations_bank_id_idx ON public.location_bank_locations USING btree (bank_id);
3645
3646
3647--
3648-- Name: policy_versions_id_idx; Type: INDEX; Schema: public; Owner: postgres
3649--
3650
3651CREATE INDEX policy_versions_id_idx ON public.policy_versions USING btree (id);
3652
3653
3654--
3655-- Name: policy_versions_id_is_current_idx; Type: INDEX; Schema: public; Owner: postgres
3656--
3657
3658CREATE UNIQUE INDEX policy_versions_id_is_current_idx ON public.policy_versions USING btree (id, is_current) WHERE (is_current = true);
3659
3660
3661--
3662-- Name: policy_versions_is_current_idx; Type: INDEX; Schema: public; Owner: postgres
3663--
3664
3665CREATE INDEX policy_versions_is_current_idx ON public.policy_versions USING btree (is_current);
3666
3667
3668--
3669-- Name: policy_versions_version_idx; Type: INDEX; Schema: public; Owner: postgres
3670--
3671
3672CREATE INDEX policy_versions_version_idx ON public.policy_versions USING btree (version);
3673
3674
3675--
3676-- Name: rule_versions_id_idx; Type: INDEX; Schema: public; Owner: postgres
3677--
3678
3679CREATE INDEX rule_versions_id_idx ON public.rule_versions USING btree (id);
3680
3681
3682--
3683-- Name: rule_versions_id_is_current_idx; Type: INDEX; Schema: public; Owner: postgres
3684--
3685
3686CREATE UNIQUE INDEX rule_versions_id_is_current_idx ON public.rule_versions USING btree (id, is_current) WHERE (is_current = true);
3687
3688
3689--
3690-- Name: rule_versions_is_current_idx; Type: INDEX; Schema: public; Owner: postgres
3691--
3692
3693CREATE INDEX rule_versions_is_current_idx ON public.rule_versions USING btree (is_current);
3694
3695
3696--
3697-- Name: rule_versions_version_idx; Type: INDEX; Schema: public; Owner: postgres
3698--
3699
3700CREATE INDEX rule_versions_version_idx ON public.rule_versions USING btree (version);
3701
3702
3703--
3704-- Name: rules_and_actions_rule_id_idx; Type: INDEX; Schema: public; Owner: postgres
3705--
3706
3707CREATE INDEX rules_and_actions_rule_id_idx ON public.rules_and_actions USING btree (rule_id);
3708
3709
3710--
3711-- Name: user_favorite_rules_idx; Type: INDEX; Schema: public; Owner: postgres
3712--
3713
3714CREATE INDEX user_favorite_rules_idx ON public.users_and_favorite_rules USING btree (user_id);
3715
3716
3717--
3718-- Name: reporting_rules.reporting_rule_versions_id_idx; Type: INDEX; Schema: reporting_rules; Owner: postgres
3719--
3720
3721CREATE INDEX "reporting_rules.reporting_rule_versions_id_idx" ON reporting_rules.reporting_rule_versions USING btree (id);
3722
3723
3724--
3725-- Name: reporting_rules.reporting_rule_versions_id_is_current_idx; Type: INDEX; Schema: reporting_rules; Owner: postgres
3726--
3727
3728CREATE UNIQUE INDEX "reporting_rules.reporting_rule_versions_id_is_current_idx" ON reporting_rules.reporting_rule_versions USING btree (id, is_current) WHERE (is_current = true);
3729
3730
3731--
3732-- Name: reporting_rules.reporting_rule_versions_is_current_idx; Type: INDEX; Schema: reporting_rules; Owner: postgres
3733--
3734
3735CREATE INDEX "reporting_rules.reporting_rule_versions_is_current_idx" ON reporting_rules.reporting_rule_versions USING btree (is_current);
3736
3737
3738--
3739-- Name: reporting_rules.reporting_rule_versions_version_idx; Type: INDEX; Schema: reporting_rules; Owner: postgres
3740--
3741
3742CREATE INDEX "reporting_rules.reporting_rule_versions_version_idx" ON reporting_rules.reporting_rule_versions USING btree (version);
3743
3744
3745--
3746-- Name: manual_review_decisions tr_check_org_id; Type: TRIGGER; Schema: manual_review_tool; Owner: postgres
3747--
3748
3749CREATE TRIGGER tr_check_org_id BEFORE INSERT OR UPDATE ON manual_review_tool.manual_review_decisions FOR EACH ROW EXECUTE FUNCTION public.check_org_id();
3750
3751
3752--
3753-- Name: appeals_routing_rules update_appeals_routing_rule_versions_view; Type: TRIGGER; Schema: manual_review_tool; Owner: postgres
3754--
3755
3756CREATE TRIGGER update_appeals_routing_rule_versions_view AFTER INSERT OR DELETE OR UPDATE ON manual_review_tool.appeals_routing_rules FOR EACH STATEMENT EXECUTE FUNCTION public.update_appeals_routing_rule_versions_view_trigger();
3757
3758
3759--
3760-- Name: routing_rules update_appelas_routing_rule_versions_view; Type: TRIGGER; Schema: manual_review_tool; Owner: postgres
3761--
3762
3763CREATE TRIGGER update_appelas_routing_rule_versions_view AFTER INSERT OR DELETE OR UPDATE ON manual_review_tool.routing_rules FOR EACH STATEMENT EXECUTE FUNCTION public.update_appeals_routing_rule_versions_view_trigger();
3764
3765
3766--
3767-- Name: routing_rules update_routing_rule_versions_view; Type: TRIGGER; Schema: manual_review_tool; Owner: postgres
3768--
3769
3770CREATE TRIGGER update_routing_rule_versions_view AFTER INSERT OR DELETE OR UPDATE ON manual_review_tool.routing_rules FOR EACH STATEMENT EXECUTE FUNCTION public.update_routing_rule_versions_view_trigger();
3771
3772
3773--
3774-- Name: appeals_routing_rules versioning_trigger; Type: TRIGGER; Schema: manual_review_tool; Owner: postgres
3775--
3776
3777CREATE TRIGGER versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON manual_review_tool.appeals_routing_rules FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', '"manual_review_tool"."appeals_routing_rule_history"', 'true', 'true');
3778
3779
3780--
3781-- Name: routing_rules versioning_trigger; Type: TRIGGER; Schema: manual_review_tool; Owner: postgres
3782--
3783
3784CREATE TRIGGER versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON manual_review_tool.routing_rules FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', '"manual_review_tool"."routing_rule_history"', 'true', 'true');
3785
3786
3787--
3788-- Name: policies trg_inherit_user_strike_count; Type: TRIGGER; Schema: public; Owner: postgres
3789--
3790
3791CREATE TRIGGER trg_inherit_user_strike_count BEFORE INSERT ON public.policies FOR EACH ROW EXECUTE FUNCTION public.inherit_user_strike_count();
3792
3793
3794--
3795-- Name: policies trg_update_user_strike_count; Type: TRIGGER; Schema: public; Owner: postgres
3796--
3797
3798CREATE TRIGGER trg_update_user_strike_count AFTER UPDATE OF apply_user_strike_count_config_to_children, user_strike_count ON public.policies FOR EACH ROW WHEN ((new.apply_user_strike_count_config_to_children = true)) EXECUTE FUNCTION public.update_descendants_user_strike_count();
3799
3800
3801--
3802-- Name: api_keys trigger_update_api_keys_updated_at; Type: TRIGGER; Schema: public; Owner: postgres
3803--
3804
3805CREATE TRIGGER trigger_update_api_keys_updated_at BEFORE UPDATE ON public.api_keys FOR EACH ROW EXECUTE FUNCTION public.update_api_keys_updated_at();
3806
3807
3808--
3809-- Name: signing_keys trigger_update_signing_keys_updated_at; Type: TRIGGER; Schema: public; Owner: postgres
3810--
3811
3812CREATE TRIGGER trigger_update_signing_keys_updated_at BEFORE UPDATE ON public.signing_keys FOR EACH ROW EXECUTE FUNCTION public.update_signing_keys_updated_at();
3813
3814
3815--
3816-- Name: actions update_action_versions_view; Type: TRIGGER; Schema: public; Owner: postgres
3817--
3818
3819CREATE TRIGGER update_action_versions_view AFTER INSERT OR DELETE OR UPDATE ON public.actions FOR EACH STATEMENT EXECUTE FUNCTION public.update_action_versions_view_trigger();
3820
3821
3822--
3823-- Name: item_types update_item_type_versions_view; Type: TRIGGER; Schema: public; Owner: postgres
3824--
3825
3826CREATE TRIGGER update_item_type_versions_view AFTER INSERT OR DELETE OR UPDATE ON public.item_types FOR EACH STATEMENT EXECUTE FUNCTION public.update_item_type_versions_view_trigger();
3827
3828
3829--
3830-- Name: policies update_policy_versions_view; Type: TRIGGER; Schema: public; Owner: postgres
3831--
3832
3833CREATE TRIGGER update_policy_versions_view AFTER INSERT OR DELETE OR UPDATE ON public.policies FOR EACH STATEMENT EXECUTE FUNCTION public.update_policy_versions_view_trigger();
3834
3835
3836--
3837-- Name: rules update_rule_versions_view; Type: TRIGGER; Schema: public; Owner: postgres
3838--
3839
3840CREATE TRIGGER update_rule_versions_view AFTER INSERT OR DELETE OR UPDATE OF id, name, status_if_unexpired, tags, max_daily_actions, org_id, creator_id, expiration_time, condition_set, sys_period ON public.rules FOR EACH STATEMENT EXECUTE FUNCTION public.update_rule_versions_view_trigger();
3841
3842
3843--
3844-- Name: rules_and_actions update_rule_versions_view; Type: TRIGGER; Schema: public; Owner: postgres
3845--
3846
3847CREATE TRIGGER update_rule_versions_view AFTER INSERT OR DELETE OR UPDATE ON public.rules_and_actions FOR EACH STATEMENT EXECUTE FUNCTION public.update_rule_versions_view_trigger();
3848
3849
3850--
3851-- Name: rules_and_policies update_rule_versions_view; Type: TRIGGER; Schema: public; Owner: postgres
3852--
3853
3854CREATE TRIGGER update_rule_versions_view AFTER INSERT OR DELETE OR UPDATE ON public.rules_and_policies FOR EACH STATEMENT EXECUTE FUNCTION public.update_rule_versions_view_trigger();
3855
3856
3857--
3858-- Name: actions versioning_trigger; Type: TRIGGER; Schema: public; Owner: postgres
3859--
3860
3861CREATE TRIGGER versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON public.actions FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', 'actions_history', 'true', 'true');
3862
3863
3864--
3865-- Name: actions_and_item_types versioning_trigger; Type: TRIGGER; Schema: public; Owner: postgres
3866--
3867
3868CREATE TRIGGER versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON public.actions_and_item_types FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', 'actions_and_item_types_history', 'true');
3869
3870
3871--
3872-- Name: item_types versioning_trigger; Type: TRIGGER; Schema: public; Owner: postgres
3873--
3874
3875CREATE TRIGGER versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON public.item_types FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', 'item_types_history', 'true', 'true');
3876
3877
3878--
3879-- Name: policies versioning_trigger; Type: TRIGGER; Schema: public; Owner: postgres
3880--
3881
3882CREATE TRIGGER versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON public.policies FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', 'policy_history', 'true', 'true');
3883
3884
3885--
3886-- Name: rules versioning_trigger; Type: TRIGGER; Schema: public; Owner: postgres
3887--
3888
3889CREATE TRIGGER versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON public.rules FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', 'rules_history', 'true', 'true');
3890
3891
3892--
3893-- Name: rules_and_actions versioning_trigger; Type: TRIGGER; Schema: public; Owner: postgres
3894--
3895
3896CREATE TRIGGER versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON public.rules_and_actions FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', 'rules_and_actions_history', 'true', 'true');
3897
3898
3899--
3900-- Name: rules_and_item_types versioning_trigger; Type: TRIGGER; Schema: public; Owner: postgres
3901--
3902
3903CREATE TRIGGER versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON public.rules_and_item_types FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', 'rules_and_item_types_history', 'true');
3904
3905
3906--
3907-- Name: rules_and_policies versioning_trigger; Type: TRIGGER; Schema: public; Owner: postgres
3908--
3909
3910CREATE TRIGGER versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON public.rules_and_policies FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', 'rules_and_policies_history', 'true', 'true');
3911
3912
3913--
3914-- Name: reporting_rules reporting_rules_versioning_trigger; Type: TRIGGER; Schema: reporting_rules; Owner: postgres
3915--
3916
3917CREATE TRIGGER reporting_rules_versioning_trigger BEFORE INSERT OR DELETE OR UPDATE ON reporting_rules.reporting_rules FOR EACH ROW EXECUTE FUNCTION public.versioning('sys_period', '"reporting_rules"."reporting_rule_history"', 'true', 'true');
3918
3919
3920--
3921-- Name: reporting_rules update_reporting_rule_versions_view; Type: TRIGGER; Schema: reporting_rules; Owner: postgres
3922--
3923
3924CREATE TRIGGER update_reporting_rule_versions_view AFTER INSERT OR DELETE OR UPDATE ON reporting_rules.reporting_rules FOR EACH STATEMENT EXECUTE FUNCTION public.update_reporting_rule_versions_view_trigger();
3925
3926
3927--
3928-- Name: appeals_routing_rules appeals_routing_rules_destination_queue_id_fkey; Type: FK CONSTRAINT; Schema: manual_review_tool; Owner: postgres
3929--
3930
3931ALTER TABLE ONLY manual_review_tool.appeals_routing_rules
3932 ADD CONSTRAINT appeals_routing_rules_destination_queue_id_fkey FOREIGN KEY (destination_queue_id) REFERENCES manual_review_tool.manual_review_queues(id) ON DELETE CASCADE;
3933
3934
3935--
3936-- Name: appeals_routing_rules_to_item_types appeals_routing_rules_to_item_type_appeals_routing_rule_id_fkey; Type: FK CONSTRAINT; Schema: manual_review_tool; Owner: postgres
3937--
3938
3939ALTER TABLE ONLY manual_review_tool.appeals_routing_rules_to_item_types
3940 ADD CONSTRAINT appeals_routing_rules_to_item_type_appeals_routing_rule_id_fkey FOREIGN KEY (appeals_routing_rule_id) REFERENCES manual_review_tool.appeals_routing_rules(id) ON DELETE CASCADE;
3941
3942
3943--
3944-- Name: moderator_skips moderator_skips_queue_id_fkey; Type: FK CONSTRAINT; Schema: manual_review_tool; Owner: postgres
3945--
3946
3947ALTER TABLE ONLY manual_review_tool.moderator_skips
3948 ADD CONSTRAINT moderator_skips_queue_id_fkey FOREIGN KEY (queue_id) REFERENCES manual_review_tool.manual_review_queues(id) ON DELETE CASCADE;
3949
3950
3951--
3952-- Name: queues_and_hidden_actions queue_fkey; Type: FK CONSTRAINT; Schema: manual_review_tool; Owner: postgres
3953--
3954
3955ALTER TABLE ONLY manual_review_tool.queues_and_hidden_actions
3956 ADD CONSTRAINT queue_fkey FOREIGN KEY (queue_id) REFERENCES manual_review_tool.manual_review_queues(id) ON DELETE CASCADE;
3957
3958
3959--
3960-- Name: routing_rules routing_rules_destination_queue_id_fkey; Type: FK CONSTRAINT; Schema: manual_review_tool; Owner: postgres
3961--
3962
3963ALTER TABLE ONLY manual_review_tool.routing_rules
3964 ADD CONSTRAINT routing_rules_destination_queue_id_fkey FOREIGN KEY (destination_queue_id) REFERENCES manual_review_tool.manual_review_queues(id) ON DELETE CASCADE;
3965
3966
3967--
3968-- Name: routing_rules_to_item_types routing_rules_to_item_types_routing_rule_id_fkey; Type: FK CONSTRAINT; Schema: manual_review_tool; Owner: postgres
3969--
3970
3971ALTER TABLE ONLY manual_review_tool.routing_rules_to_item_types
3972 ADD CONSTRAINT routing_rules_to_item_types_routing_rule_id_fkey FOREIGN KEY (routing_rule_id) REFERENCES manual_review_tool.routing_rules(id) ON DELETE CASCADE;
3973
3974
3975--
3976-- Name: users_and_accessible_queues users_and_accessible_queues_queue_id_fkey; Type: FK CONSTRAINT; Schema: manual_review_tool; Owner: postgres
3977--
3978
3979ALTER TABLE ONLY manual_review_tool.users_and_accessible_queues
3980 ADD CONSTRAINT users_and_accessible_queues_queue_id_fkey FOREIGN KEY (queue_id) REFERENCES manual_review_tool.manual_review_queues(id) ON DELETE CASCADE;
3981
3982
3983--
3984-- Name: users_and_favorite_mrt_queues users_and_favorite_mrt_queues_queue_id_fkey; Type: FK CONSTRAINT; Schema: manual_review_tool; Owner: postgres
3985--
3986
3987ALTER TABLE ONLY manual_review_tool.users_and_favorite_mrt_queues
3988 ADD CONSTRAINT users_and_favorite_mrt_queues_queue_id_fkey FOREIGN KEY (queue_id) REFERENCES manual_review_tool.manual_review_queues(id) ON DELETE CASCADE;
3989
3990
3991--
3992-- Name: actions_and_item_types actions_and_item_types_action_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
3993--
3994
3995ALTER TABLE ONLY public.actions_and_item_types
3996 ADD CONSTRAINT actions_and_item_types_action_id_fkey FOREIGN KEY (action_id) REFERENCES public.actions(id) ON UPDATE CASCADE ON DELETE CASCADE;
3997
3998
3999--
4000-- Name: actions_and_item_types actions_and_item_types_item_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4001--
4002
4003ALTER TABLE ONLY public.actions_and_item_types
4004 ADD CONSTRAINT actions_and_item_types_item_type_id_fkey FOREIGN KEY (item_type_id) REFERENCES public.item_types(id) ON UPDATE CASCADE ON DELETE CASCADE;
4005
4006
4007--
4008-- Name: actions actions_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4009--
4010
4011ALTER TABLE ONLY public.actions
4012 ADD CONSTRAINT actions_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON DELETE CASCADE;
4013
4014
4015--
4016-- Name: api_keys api_keys_created_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4017--
4018
4019ALTER TABLE ONLY public.api_keys
4020 ADD CONSTRAINT api_keys_created_by_fkey FOREIGN KEY (created_by) REFERENCES public.users(id) ON DELETE SET NULL;
4021
4022
4023--
4024-- Name: api_keys api_keys_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4025--
4026
4027ALTER TABLE ONLY public.api_keys
4028 ADD CONSTRAINT api_keys_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON DELETE CASCADE;
4029
4030
4031--
4032-- Name: backtests backtests_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4033--
4034
4035ALTER TABLE ONLY public.backtests
4036 ADD CONSTRAINT backtests_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES public.users(id);
4037
4038
4039--
4040-- Name: backtests backtests_rule_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4041--
4042
4043ALTER TABLE ONLY public.backtests
4044 ADD CONSTRAINT backtests_rule_id_fkey FOREIGN KEY (rule_id) REFERENCES public.rules(id);
4045
4046
4047--
4048-- Name: invite_user_tokens invite_user_tokens_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4049--
4050
4051ALTER TABLE ONLY public.invite_user_tokens
4052 ADD CONSTRAINT invite_user_tokens_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON UPDATE CASCADE ON DELETE SET NULL;
4053
4054
4055--
4056-- Name: item_types item_types_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4057--
4058
4059ALTER TABLE ONLY public.item_types
4060 ADD CONSTRAINT item_types_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON DELETE CASCADE;
4061
4062
4063--
4064-- Name: location_bank_locations location_bank_locations_bank_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4065--
4066
4067ALTER TABLE ONLY public.location_bank_locations
4068 ADD CONSTRAINT location_bank_locations_bank_id_fkey FOREIGN KEY (bank_id) REFERENCES public.location_banks(id) ON DELETE CASCADE;
4069
4070
4071--
4072-- Name: location_banks location_banks_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4073--
4074
4075ALTER TABLE ONLY public.location_banks
4076 ADD CONSTRAINT location_banks_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id);
4077
4078
4079--
4080-- Name: location_banks location_banks_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4081--
4082
4083ALTER TABLE ONLY public.location_banks
4084 ADD CONSTRAINT location_banks_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.users(id);
4085
4086
4087--
4088-- Name: media_banks media_banks_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4089--
4090
4091ALTER TABLE ONLY public.media_banks
4092 ADD CONSTRAINT media_banks_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON UPDATE CASCADE ON DELETE SET NULL;
4093
4094
4095--
4096-- Name: media_banks media_banks_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4097--
4098
4099ALTER TABLE ONLY public.media_banks
4100 ADD CONSTRAINT media_banks_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;
4101
4102
4103--
4104-- Name: notifications notifications_userId_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4105--
4106
4107ALTER TABLE ONLY public.notifications
4108 ADD CONSTRAINT "notifications_userId_fkey" FOREIGN KEY ("userId") REFERENCES public.users(id) ON DELETE CASCADE;
4109
4110
4111--
4112-- Name: policies policies_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4113--
4114
4115ALTER TABLE ONLY public.policies
4116 ADD CONSTRAINT policies_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON DELETE CASCADE;
4117
4118
4119--
4120-- Name: policies policies_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4121--
4122
4123ALTER TABLE ONLY public.policies
4124 ADD CONSTRAINT policies_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES public.policies(id) ON UPDATE CASCADE ON DELETE CASCADE;
4125
4126
4127--
4128-- Name: rules_and_actions rules_and_actions_action_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4129--
4130
4131ALTER TABLE ONLY public.rules_and_actions
4132 ADD CONSTRAINT rules_and_actions_action_id_fkey FOREIGN KEY (action_id) REFERENCES public.actions(id) ON UPDATE CASCADE ON DELETE CASCADE;
4133
4134
4135--
4136-- Name: rules_and_actions rules_and_actions_rule_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4137--
4138
4139ALTER TABLE ONLY public.rules_and_actions
4140 ADD CONSTRAINT rules_and_actions_rule_id_fkey FOREIGN KEY (rule_id) REFERENCES public.rules(id) ON UPDATE CASCADE ON DELETE CASCADE;
4141
4142
4143--
4144-- Name: rules_and_item_types rules_and_item_types_item_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4145--
4146
4147ALTER TABLE ONLY public.rules_and_item_types
4148 ADD CONSTRAINT rules_and_item_types_item_type_id_fkey FOREIGN KEY (item_type_id) REFERENCES public.item_types(id) ON UPDATE CASCADE ON DELETE CASCADE;
4149
4150
4151--
4152-- Name: rules_and_item_types rules_and_item_types_rule_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4153--
4154
4155ALTER TABLE ONLY public.rules_and_item_types
4156 ADD CONSTRAINT rules_and_item_types_rule_id_fkey FOREIGN KEY (rule_id) REFERENCES public.rules(id) ON UPDATE CASCADE ON DELETE CASCADE;
4157
4158
4159--
4160-- Name: rules_and_policies rules_and_policies_policy_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4161--
4162
4163ALTER TABLE ONLY public.rules_and_policies
4164 ADD CONSTRAINT rules_and_policies_policy_id_fkey FOREIGN KEY (policy_id) REFERENCES public.policies(id) ON UPDATE CASCADE ON DELETE CASCADE;
4165
4166
4167--
4168-- Name: rules_and_policies rules_and_policies_rule_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4169--
4170
4171ALTER TABLE ONLY public.rules_and_policies
4172 ADD CONSTRAINT rules_and_policies_rule_id_fkey FOREIGN KEY (rule_id) REFERENCES public.rules(id) ON UPDATE CASCADE ON DELETE CASCADE;
4173
4174
4175--
4176-- Name: rules rules_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4177--
4178
4179ALTER TABLE ONLY public.rules
4180 ADD CONSTRAINT rules_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;
4181
4182
4183--
4184-- Name: rules rules_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4185--
4186
4187ALTER TABLE ONLY public.rules
4188 ADD CONSTRAINT rules_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON DELETE CASCADE;
4189
4190
4191--
4192-- Name: signing_keys signing_keys_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4193--
4194
4195ALTER TABLE ONLY public.signing_keys
4196 ADD CONSTRAINT signing_keys_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON DELETE CASCADE;
4197
4198
4199--
4200-- Name: text_banks text_banks_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4201--
4202
4203ALTER TABLE ONLY public.text_banks
4204 ADD CONSTRAINT text_banks_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON UPDATE CASCADE ON DELETE CASCADE;
4205
4206
4207--
4208-- Name: text_banks text_banks_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4209--
4210
4211ALTER TABLE ONLY public.text_banks
4212 ADD CONSTRAINT text_banks_owner_id_fkey FOREIGN KEY (owner_id) REFERENCES public.users(id) ON UPDATE CASCADE ON DELETE SET NULL;
4213
4214
4215--
4216-- Name: user_strike_thresholds user_strike_thresholds_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4217--
4218
4219ALTER TABLE ONLY public.user_strike_thresholds
4220 ADD CONSTRAINT user_strike_thresholds_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON DELETE CASCADE;
4221
4222
4223--
4224-- Name: users_and_favorite_rules users_and_favorite_rules_rule_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4225--
4226
4227ALTER TABLE ONLY public.users_and_favorite_rules
4228 ADD CONSTRAINT users_and_favorite_rules_rule_id_fkey FOREIGN KEY (rule_id) REFERENCES public.rules(id);
4229
4230
4231--
4232-- Name: users_and_favorite_rules users_and_favorite_rules_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4233--
4234
4235ALTER TABLE ONLY public.users_and_favorite_rules
4236 ADD CONSTRAINT users_and_favorite_rules_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id);
4237
4238
4239--
4240-- Name: users users_org_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
4241--
4242
4243ALTER TABLE ONLY public.users
4244 ADD CONSTRAINT users_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON DELETE CASCADE;
4245
4246
4247--
4248-- Name: reporting_rules_to_actions reporting_rules_to_actions_fkey; Type: FK CONSTRAINT; Schema: reporting_rules; Owner: postgres
4249--
4250
4251ALTER TABLE ONLY reporting_rules.reporting_rules_to_actions
4252 ADD CONSTRAINT reporting_rules_to_actions_fkey FOREIGN KEY (reporting_rule_id) REFERENCES reporting_rules.reporting_rules(id) ON DELETE CASCADE;
4253
4254
4255--
4256-- Name: reporting_rules_to_actions reporting_rules_to_actions_reporting_rule_id_fkey; Type: FK CONSTRAINT; Schema: reporting_rules; Owner: postgres
4257--
4258
4259ALTER TABLE ONLY reporting_rules.reporting_rules_to_actions
4260 ADD CONSTRAINT reporting_rules_to_actions_reporting_rule_id_fkey FOREIGN KEY (reporting_rule_id) REFERENCES reporting_rules.reporting_rules(id);
4261
4262
4263--
4264-- Name: reporting_rules_to_item_types reporting_rules_to_item_types_fkey; Type: FK CONSTRAINT; Schema: reporting_rules; Owner: postgres
4265--
4266
4267ALTER TABLE ONLY reporting_rules.reporting_rules_to_item_types
4268 ADD CONSTRAINT reporting_rules_to_item_types_fkey FOREIGN KEY (reporting_rule_id) REFERENCES reporting_rules.reporting_rules(id) ON DELETE CASCADE;
4269
4270
4271--
4272-- Name: reporting_rules_to_item_types reporting_rules_to_item_types_reporting_rule_id_fkey; Type: FK CONSTRAINT; Schema: reporting_rules; Owner: postgres
4273--
4274
4275ALTER TABLE ONLY reporting_rules.reporting_rules_to_item_types
4276 ADD CONSTRAINT reporting_rules_to_item_types_reporting_rule_id_fkey FOREIGN KEY (reporting_rule_id) REFERENCES reporting_rules.reporting_rules(id);
4277
4278
4279--
4280-- Name: reporting_rules_to_policies reporting_rules_to_policies_fkey; Type: FK CONSTRAINT; Schema: reporting_rules; Owner: postgres
4281--
4282
4283ALTER TABLE ONLY reporting_rules.reporting_rules_to_policies
4284 ADD CONSTRAINT reporting_rules_to_policies_fkey FOREIGN KEY (reporting_rule_id) REFERENCES reporting_rules.reporting_rules(id) ON DELETE CASCADE;
4285
4286
4287--
4288-- Name: reporting_rules_to_policies reporting_rules_to_policies_reporting_rule_id_fkey; Type: FK CONSTRAINT; Schema: reporting_rules; Owner: postgres
4289--
4290
4291ALTER TABLE ONLY reporting_rules.reporting_rules_to_policies
4292 ADD CONSTRAINT reporting_rules_to_policies_reporting_rule_id_fkey FOREIGN KEY (reporting_rule_id) REFERENCES reporting_rules.reporting_rules(id);
4293
4294
4295--
4296-- Name: open_ai_configs open_ai_configs_org_id_fkey; Type: FK CONSTRAINT; Schema: signal_auth_service; Owner: postgres
4297--
4298
4299ALTER TABLE ONLY signal_auth_service.open_ai_configs
4300 ADD CONSTRAINT open_ai_configs_org_id_fkey FOREIGN KEY (org_id) REFERENCES public.orgs(id) ON DELETE CASCADE;
4301
4302
4303
4304--
4305-- PostgreSQL database dump complete
4306--
4307