-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathbackup - p_rsf schema.sql
More file actions
12155 lines (10746 loc) · 555 KB
/
backup - p_rsf schema.sql
File metadata and controls
12155 lines (10746 loc) · 555 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
/*
Navicat PostgreSQL Dump SQL
Source Server : RSF Jason
Source Server Type : PostgreSQL
Source Server Version : 160000 (160000)
Source Host : w0lxpfigrsfpg01:5432
Source Catalog : RSFDev
Source Schema : p_rsf
Target Server Type : PostgreSQL
Target Server Version : 160000 (160000)
File Encoding : 65001
Date: 18/02/2026 13:58:03
*/
-- ----------------------------
-- Type structure for gtrgm
-- ----------------------------
DROP TYPE IF EXISTS "p_rsf"."gtrgm";
CREATE TYPE "p_rsf"."gtrgm" (
INPUT = "p_rsf"."gtrgm_in",
OUTPUT = "p_rsf"."gtrgm_out",
INTERNALLENGTH = VARIABLE,
CATEGORY = U,
DELIMITER = ','
);
ALTER TYPE "p_rsf"."gtrgm" OWNER TO "postgres";
-- ----------------------------
-- Sequence structure for export_template_reports_export_template_report_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."export_template_reports_export_template_report_id_seq";
CREATE SEQUENCE "p_rsf"."export_template_reports_export_template_report_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for export_templates_export_template_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."export_templates_export_template_id_seq";
CREATE SEQUENCE "p_rsf"."export_templates_export_template_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for exporting_cohorts_exporting_cohort_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."exporting_cohorts_exporting_cohort_id_seq";
CREATE SEQUENCE "p_rsf"."exporting_cohorts_exporting_cohort_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for import_templates_import_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."import_templates_import_id_seq";
CREATE SEQUENCE "p_rsf"."import_templates_import_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for indicator_check_formulas_check_formula_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."indicator_check_formulas_check_formula_id_seq";
CREATE SEQUENCE "p_rsf"."indicator_check_formulas_check_formula_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for indicator_check_guidance_guidance_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."indicator_check_guidance_guidance_id_seq";
CREATE SEQUENCE "p_rsf"."indicator_check_guidance_guidance_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for indicator_checks_check_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."indicator_checks_check_id_seq";
CREATE SEQUENCE "p_rsf"."indicator_checks_check_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for indicator_formulas_formula_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."indicator_formulas_formula_id_seq";
CREATE SEQUENCE "p_rsf"."indicator_formulas_formula_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for indicator_object_logs_log_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."indicator_object_logs_log_id_seq";
CREATE SEQUENCE "p_rsf"."indicator_object_logs_log_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for indicator_option_groups_option_group_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."indicator_option_groups_option_group_id_seq";
CREATE SEQUENCE "p_rsf"."indicator_option_groups_option_group_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for indicators_indicator_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."indicators_indicator_id_seq";
CREATE SEQUENCE "p_rsf"."indicators_indicator_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for label_ids_label_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."label_ids_label_id_seq";
CREATE SEQUENCE "p_rsf"."label_ids_label_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for labels_label_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."labels_label_id_seq";
CREATE SEQUENCE "p_rsf"."labels_label_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for reporting_templates_template_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."reporting_templates_template_id_seq";
CREATE SEQUENCE "p_rsf"."reporting_templates_template_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for reports_report_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."reports_report_id_seq";
CREATE SEQUENCE "p_rsf"."reports_report_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for rsf_data_calculation_profiles_calculation_profile_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."rsf_data_calculation_profiles_calculation_profile_id_seq";
CREATE SEQUENCE "p_rsf"."rsf_data_calculation_profiles_calculation_profile_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for rsf_data_checks_evaluation_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."rsf_data_checks_evaluation_id_seq";
CREATE SEQUENCE "p_rsf"."rsf_data_checks_evaluation_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for rsf_data_cohort_sequence
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."rsf_data_cohort_sequence";
CREATE SEQUENCE "p_rsf"."rsf_data_cohort_sequence"
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for rsf_data_data_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."rsf_data_data_id_seq";
CREATE SEQUENCE "p_rsf"."rsf_data_data_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for rsf_pfcbl_ids_rsf_pfcbl_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."rsf_pfcbl_ids_rsf_pfcbl_id_seq";
CREATE SEQUENCE "p_rsf"."rsf_pfcbl_ids_rsf_pfcbl_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for rsf_program_facility_template_headers_header_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."rsf_program_facility_template_headers_header_id_seq";
CREATE SEQUENCE "p_rsf"."rsf_program_facility_template_headers_header_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Sequence structure for rsf_setup_checks_config_config_id_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "p_rsf"."rsf_setup_checks_config_config_id_seq";
CREATE SEQUENCE "p_rsf"."rsf_setup_checks_config_config_id_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Table structure for export_template_reports
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."export_template_reports";
CREATE TABLE "p_rsf"."export_template_reports" (
"export_template_report_id" int4 NOT NULL DEFAULT nextval('"p_rsf".export_template_reports_export_template_report_id_seq'::regclass),
"export_template_id" int4 NOT NULL,
"report_id" int4,
"sheet_name" varchar(30) COLLATE "pg_catalog"."default" NOT NULL,
"table_name" varchar(30) COLLATE "pg_catalog"."default" NOT NULL
)
;
-- ----------------------------
-- Table structure for export_templates
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."export_templates";
CREATE TABLE "p_rsf"."export_templates" (
"export_template_id" int4 NOT NULL DEFAULT nextval('"p_rsf".export_templates_export_template_id_seq'::regclass),
"template_title" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
"template_file" bytea,
"template_filename" varchar(125) COLLATE "pg_catalog"."default",
"template_notes" text COLLATE "pg_catalog"."default",
"created_by_user_id" text COLLATE "pg_catalog"."default" NOT NULL,
"modified_by_user_id" text COLLATE "pg_catalog"."default" NOT NULL,
"modification_time" timestamptz(6) NOT NULL DEFAULT now(),
"is_public" bool NOT NULL DEFAULT false
)
;
-- ----------------------------
-- Table structure for exporting_cohorts
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."exporting_cohorts";
CREATE TABLE "p_rsf"."exporting_cohorts" (
"exporting_cohort_id" int4 NOT NULL DEFAULT nextval('"p_rsf".exporting_cohorts_exporting_cohort_id_seq'::regclass),
"rsf_program_id" int4 NOT NULL,
"exporting_rsf_pfcbl_id" int4 NOT NULL,
"exporting_user_id" text COLLATE "pg_catalog"."default" NOT NULL,
"exporting_time" timestamp(6) NOT NULL DEFAULT now(),
"export_name" varchar(255) COLLATE "pg_catalog"."default",
"reporting_expiration_date" date DEFAULT ((now() + '14 days'::interval))::date,
"reporting_key" text COLLATE "pg_catalog"."default" NOT NULL,
"generated_with_report_id" int4,
"for_reporting_template_id" int4 NOT NULL,
"data_integrity_key" text COLLATE "pg_catalog"."default" NOT NULL,
"exporting_asof_date" date
)
;
COMMENT ON COLUMN "p_rsf"."exporting_cohorts"."exporting_rsf_pfcbl_id" IS 'parent rsf reporting ID';
-- ----------------------------
-- Table structure for indicator_check_formula_parameters
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_check_formula_parameters";
CREATE TABLE "p_rsf"."indicator_check_formula_parameters" (
"indicator_check_id" int4 NOT NULL,
"check_formula_id" int4 NOT NULL,
"for_pfcbl_category" text COLLATE "pg_catalog"."default" NOT NULL,
"check_grouping_pfcbl_category" text COLLATE "pg_catalog"."default",
"check_grouping_pfcbl_rank" int2,
"parameter_indicator_id" int4 NOT NULL,
"parameter_pfcbl_category" text COLLATE "pg_catalog"."default" NOT NULL,
"parameter_pfcbl_rank" int2 NOT NULL,
"parameter_pfcbl_hierarchy" text COLLATE "pg_catalog"."default" NOT NULL,
"is_calculation_trigger_parameter" bool NOT NULL DEFAULT false,
"parameter_trigger_by_reporting" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "p_rsf"."indicator_check_formula_parameters"."parameter_pfcbl_hierarchy" IS '-1 = parent; 0 = self; 1 = child';
COMMENT ON COLUMN "p_rsf"."indicator_check_formula_parameters"."is_calculation_trigger_parameter" IS 'true if its used in the FORMULA calculation (ie, an input data should trigger it to stale) whereas a MESSAGE parameter should not trigger a recalculation but is nevertheless a parameter that must be obtained on query along with the associated rsf_pfcbl_id entity';
-- ----------------------------
-- Table structure for indicator_check_formulas
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_check_formulas";
CREATE TABLE "p_rsf"."indicator_check_formulas" (
"indicator_check_id" int4 NOT NULL,
"formula" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'TRUE==TRUE'::text,
"formula_result_message" text COLLATE "pg_catalog"."default",
"formula_indicator_ids" int4[] NOT NULL DEFAULT ARRAY[]::integer[],
"formula_comments" text COLLATE "pg_catalog"."default",
"formula_version_number" int4 NOT NULL DEFAULT 0,
"formula_modification_time" timestamp(6) NOT NULL DEFAULT now(),
"auto_resolve" bool NOT NULL DEFAULT false,
"check_pfcbl_category" text COLLATE "pg_catalog"."default" NOT NULL,
"check_pfcbl_rank" int2 NOT NULL,
"parameter_pfcbl_ranks" int2[] NOT NULL,
"parent_grouping_pfcbl_rank" int2,
"parent_grouping_pfcbl_category" text COLLATE "pg_catalog"."default",
"check_formula_indicator_ids" int4[],
"check_message_indicator_ids" int4[],
"computation_group" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::text,
"formula_fx_date" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'calculation'::text,
"check_formula_id" int4 NOT NULL DEFAULT nextval('"p_rsf".indicator_check_formulas_check_formula_id_seq'::regclass),
"check_formula_title" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'Untitled check formula'::text,
"default_subscription" bool NOT NULL DEFAULT false,
"label_id" int4
)
;
COMMENT ON COLUMN "p_rsf"."indicator_check_formulas"."parameter_pfcbl_ranks" IS 'Includes parameter ranks, only';
COMMENT ON COLUMN "p_rsf"."indicator_check_formulas"."parent_grouping_pfcbl_rank" IS 'For grouping and/or child-level parameters';
-- ----------------------------
-- Table structure for indicator_check_guidance
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_check_guidance";
CREATE TABLE "p_rsf"."indicator_check_guidance" (
"indicator_check_guidance_id" int4 NOT NULL DEFAULT nextval('"p_rsf".indicator_check_guidance_guidance_id_seq'::regclass),
"indicator_check_id" int4 NOT NULL,
"for_indicator_id" int4 NOT NULL,
"guidance" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'No guidance specified'::text,
"is_resolving_guidance" bool NOT NULL DEFAULT false,
"user_id" text COLLATE "pg_catalog"."default" NOT NULL,
"update_time" timestamptz(6) NOT NULL DEFAULT (timeofday())::timestamp with time zone,
"for_pfcbl_category" text COLLATE "pg_catalog"."default" NOT NULL,
"overwrite_check_class" text COLLATE "pg_catalog"."default",
"is_ignoring_guidance" bool NOT NULL DEFAULT false,
"variance_threshold" numeric NOT NULL DEFAULT 0.0,
"is_priority_flag" bool NOT NULL DEFAULT false,
"check_formula_id" int4
)
;
COMMENT ON COLUMN "p_rsf"."indicator_check_guidance"."is_resolving_guidance" IS 'When set to true, enables users to refine check behavior to auto-resolve for any combination of check_ids and indicator_ids, using the specified guidance as the auto-resolving resolution comment and user_id as the auto-resolving user';
COMMENT ON COLUMN "p_rsf"."indicator_check_guidance"."variance_threshold" IS 'For checks that have a variance threshold, if the variance is within the specified threshold then this guidance will be aplied';
COMMENT ON COLUMN "p_rsf"."indicator_check_guidance"."check_formula_id" IS 'TBD: Do we care more about the application or the calculation; or both?';
-- ----------------------------
-- Table structure for indicator_check_types
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_check_types";
CREATE TABLE "p_rsf"."indicator_check_types" (
"check_type" text COLLATE "pg_catalog"."default" NOT NULL,
"check_type_name" text COLLATE "pg_catalog"."default",
"check_type_notes" text COLLATE "pg_catalog"."default",
"apply_on" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'data'::text
)
;
COMMENT ON COLUMN "p_rsf"."indicator_check_types"."apply_on" IS 'Either ''data'' or ''reporting'' -- is the check applied onto a specific data_id data point or the data_id generated for the sys_x_reporting that is generaically generated';
-- ----------------------------
-- Table structure for indicator_checks
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_checks";
CREATE TABLE "p_rsf"."indicator_checks" (
"indicator_check_id" int4 NOT NULL DEFAULT nextval('"p_rsf".indicator_checks_check_id_seq'::regclass),
"check_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
"check_class" varchar(255) COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'warning'::character varying,
"grouping" varchar(255) COLLATE "pg_catalog"."default",
"subgrouping" varchar(255) COLLATE "pg_catalog"."default",
"definition" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::text,
"is_system" bool NOT NULL DEFAULT false,
"version_number" int4 NOT NULL DEFAULT 0,
"modification_time" timestamp(6) NOT NULL DEFAULT now(),
"variance_tolerance_allowed" bool NOT NULL DEFAULT false,
"is_calculator_check" bool NOT NULL DEFAULT false,
"check_type" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'none'::text,
"check_pfcbl_category" text COLLATE "pg_catalog"."default",
"auto_resolve_system_check" bool,
"auto_subscribe" bool DEFAULT true
)
;
COMMENT ON COLUMN "p_rsf"."indicator_checks"."variance_tolerance_allowed" IS 'When true, indicates a % variance from an existing value and if outside that tolerance range, will apply the flag; and if not, flag ignored. Only relevant for system checks, notably system calculator overwrites or other "disagreement" type flags. Enabled through custom guidance application';
COMMENT ON COLUMN "p_rsf"."indicator_checks"."check_pfcbl_category" IS 'Null means is_system=true since a single system flag can be applied on any data point';
-- ----------------------------
-- Table structure for indicator_data_types
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_data_types";
CREATE TABLE "p_rsf"."indicator_data_types" (
"data_type" text COLLATE "pg_catalog"."default" NOT NULL
)
;
-- ----------------------------
-- Table structure for indicator_formula_parameters
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_formula_parameters";
CREATE TABLE "p_rsf"."indicator_formula_parameters" (
"indicator_id" int4 NOT NULL,
"calculate_pfcbl_category" text COLLATE "pg_catalog"."default" NOT NULL,
"calculate_grouping_pfcbl_category" text COLLATE "pg_catalog"."default",
"calculate_grouping_pfcbl_rank" int2,
"parameter_indicator_id" int4 NOT NULL,
"parameter_pfcbl_category" text COLLATE "pg_catalog"."default" NOT NULL,
"parameter_pfcbl_rank" int2 NOT NULL,
"parameter_pfcbl_hierarchy" text COLLATE "pg_catalog"."default" NOT NULL,
"parameter_is_current" bool NOT NULL DEFAULT false,
"parameter_is_previous" bool NOT NULL DEFAULT false,
"parameter_is_info" bool NOT NULL DEFAULT false,
"parameter_is_all" bool NOT NULL DEFAULT false,
"parameter_trigger_by_reporting" bool NOT NULL DEFAULT false,
"parameter_data_type" text COLLATE "pg_catalog"."default" NOT NULL,
"formula_id" int4 NOT NULL
)
;
-- ----------------------------
-- Table structure for indicator_formulas
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_formulas";
CREATE TABLE "p_rsf"."indicator_formulas" (
"indicator_id" int4 NOT NULL,
"formula" text COLLATE "pg_catalog"."default",
"formula_sort" text COLLATE "pg_catalog"."default",
"overwrite" varchar(255) COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'allow'::character varying,
"formula_indicator_ids" int4[],
"formula_indicator_id_requirements" int4[],
"formula_indicator_id_dependents" int4[],
"formula_calculation_rank" int2 NOT NULL DEFAULT 0,
"formula_grouping_pfcbl_rank" int2,
"formula_pfcbl_rank_range" int2[],
"dep-formula_calculated_by_indicator_id" int4,
"perform_calculation_by_row" bool DEFAULT false,
"modification_time" timestamp(6) NOT NULL DEFAULT now(),
"formula_fx_date" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'calculation'::text,
"computation_group" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT ''::text,
"computation_priority_rank" int2 NOT NULL DEFAULT 0,
"formula_unit_set_by_indicator_id" int4,
"formula_id" int4 NOT NULL DEFAULT nextval('"p_rsf".indicator_formulas_formula_id_seq'::regclass),
"formula_title" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'Untitled Formula'::text,
"is_primary_default" bool NOT NULL DEFAULT true,
"formula_notes" text COLLATE "pg_catalog"."default",
"modified_by_user_id" text COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "p_rsf"."indicator_formulas"."overwrite" IS 'when system calculator can overwite a user-submitted value. Default is to allow overwrites, assuming system calculator is most accurate and consistent: allow, deny, missing, unchanged';
COMMENT ON COLUMN "p_rsf"."indicator_formulas"."formula_indicator_ids" IS 'Indicator IDs required to calculate the formula';
COMMENT ON COLUMN "p_rsf"."indicator_formulas"."formula_indicator_id_requirements" IS 'All prerequisite data points';
COMMENT ON COLUMN "p_rsf"."indicator_formulas"."formula_indicator_id_dependents" IS 'All calculations that will be invalidated by changes to this indicator';
COMMENT ON COLUMN "p_rsf"."indicator_formulas"."perform_calculation_by_row" IS 'System will group "by" rsf_pfcbl_category when indicators of different categories are presented; but if only one category is presented, system will not use any grouping at all UNLESS perform_calculation_by_row=TRUE and then group at row-level or NULL (auto group). This allows using aggregate functions within a category row-space. Eg, sum(loan_outstanding,loan_comitted) whereas without the grouping, sum() would aggregate over the entire dataset and surely yield a wrong value. Row level calculations also needed when using functions that are not vectorized; performs a similar function as lapply() would to loop over values, but keeps formula definition much cleaner.
TRUE = Force row level grouping within auto-groups
FALSE = No grouping
NA = Auto ';
COMMENT ON COLUMN "p_rsf"."indicator_formulas"."formula_fx_date" IS 'Either "calculation" or "parameter"
"calculation" will use the FX rate date of the latest updated parameter that is used in the calculation (max parameter update date)
Whereas "parameter" will use the FX rate date for each individual parameter''s update date; for aggregated calculations, this may mean for example that an fx rate across different time periods would be used in constructing the overall aggrate value in the output fx terms.';
COMMENT ON COLUMN "p_rsf"."indicator_formulas"."computation_priority_rank" IS 'Zero is low priority. Used to segment formula_calculation_ranks into sub-ranks for independent calculation. Practically, is used to calculate currency_ratio calculations first since other subsequent calculations may rely on these (or not) in an unknowable way depending on whether any given formula must undertake an fx conversion due to its input data, as determined at the computation time.';
COMMENT ON COLUMN "p_rsf"."indicator_formulas"."formula_unit_set_by_indicator_id" IS 'When set, output result will be set to the unit of the relevant indicator. To ensure a 1:1 relationship that won''t conflict, the unit must be equal or lower pfcbl_rank to the formula''s rank. Indicators must also be of same data_type, When not set, it will default to the data_unit of the indicator; and for LCU values, will default to the entity''s LCU specification.';
-- ----------------------------
-- Table structure for indicator_object_logs
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_object_logs";
CREATE TABLE "p_rsf"."indicator_object_logs" (
"log_id" int4 NOT NULL DEFAULT nextval('"p_rsf".indicator_object_logs_log_id_seq'::regclass),
"table_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
"table_id" int4 NOT NULL,
"log_date" date NOT NULL DEFAULT (now())::date,
"version_number" int4 NOT NULL DEFAULT 1,
"log_object" jsonb NOT NULL DEFAULT '{}'::jsonb
)
;
-- ----------------------------
-- Table structure for indicator_options_group_keys
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_options_group_keys";
CREATE TABLE "p_rsf"."indicator_options_group_keys" (
"options_group_id" int4 NOT NULL,
"options_group_key" varchar(10) COLLATE "pg_catalog"."default" NOT NULL,
"label_id" int4 NOT NULL
)
;
-- ----------------------------
-- Table structure for indicator_options_groups
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_options_groups";
CREATE TABLE "p_rsf"."indicator_options_groups" (
"options_group_id" int4 NOT NULL DEFAULT nextval('"p_rsf".indicator_option_groups_option_group_id_seq'::regclass),
"options_group_name" varchar(32) COLLATE "pg_catalog"."default" NOT NULL,
"options_group_definition" text COLLATE "pg_catalog"."default",
"options_group_data_type" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'text'::text
)
;
-- ----------------------------
-- Table structure for indicator_sys_categories
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicator_sys_categories";
CREATE TABLE "p_rsf"."indicator_sys_categories" (
"indicator_sys_category" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
"is_hidden" bool NOT NULL DEFAULT false,
"comment" text COLLATE "pg_catalog"."default",
"class" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
"category_cascade" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "p_rsf"."indicator_sys_categories"."is_hidden" IS 'note: not yet implemented';
-- ----------------------------
-- Table structure for indicators
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."indicators";
CREATE TABLE "p_rsf"."indicators" (
"indicator_id" int4 NOT NULL DEFAULT nextval('"p_rsf".indicators_indicator_id_seq'::regclass),
"indicator_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
"indicator_sys_category" varchar(255) COLLATE "pg_catalog"."default",
"data_category" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
"data_type" varchar COLLATE "pg_catalog"."default" NOT NULL,
"data_unit" varchar(255) COLLATE "pg_catalog"."default",
"default_value" varchar(255) COLLATE "pg_catalog"."default",
"definition" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'Undefined'::text,
"label_id" int4 NOT NULL,
"indicator_options_group_id" int4,
"indicator_options_group_allows_blanks" bool,
"indicator_options_group_allows_multiples" bool,
"is_calculated" bool NOT NULL DEFAULT false,
"is_system" bool NOT NULL DEFAULT false,
"is_data_unit" bool NOT NULL DEFAULT false,
"is_system_calculated" bool NOT NULL DEFAULT false,
"is_setup" text COLLATE "pg_catalog"."default",
"modification_time" timestamptz(6) NOT NULL DEFAULT (timeofday())::timestamp with time zone,
"version_number" int4 NOT NULL DEFAULT 1,
"!dep-is_static_nonreporting" bool NOT NULL DEFAULT false,
"default_subscription" bool NOT NULL DEFAULT false,
"is_periodic_or_flow_reporting" bool NOT NULL DEFAULT false,
"classification" text COLLATE "pg_catalog"."default",
"sort_preference" int2,
"created_by_user_id" text COLLATE "pg_catalog"."default",
"modified_by_user_id" text COLLATE "pg_catalog"."default",
"pfcbl_rank" int2 NOT NULL
)
;
COMMENT ON COLUMN "p_rsf"."indicators"."default_value" IS 'If an entity submits a column with an NA value, then use a default, if defined. But if entity never submits any value (ever), then db_program_get_data will return NA';
COMMENT ON COLUMN "p_rsf"."indicators"."is_system" IS 'true when indicator is created within/by system processes, not externally defined';
COMMENT ON COLUMN "p_rsf"."indicators"."is_data_unit" IS 'If true, the data_value recorded in rsf_data is a unit of measure (which presumably defines the units of other data and is used in rsf_data_timeseries::data_unit_data_id)';
COMMENT ON COLUMN "p_rsf"."indicators"."is_system_calculated" IS 'If true calculated by the system independently AND uses the formula definition in indicator_formulas; if false, calcualted by the normal system calculator -- this is needed as queries to indicator formulas will pull these in and is_system_calculated informs the calculator to ignore these. Note: that some system indicators are indeed calculated by the system, but do not have is_system_calculated=true because the are calculated entirely outside of any indicator_formula definition';
COMMENT ON COLUMN "p_rsf"."indicators"."is_setup" IS 'true when it''s a required field use to setup/initialize an entity; fields to present to UI';
COMMENT ON COLUMN "p_rsf"."indicators"."!dep-is_static_nonreporting" IS 'When true, BEFORE INSERT on rsf_data will DENY any inclusion of these indicators. And therefore no values will be saved in rsf_data nor present in rsf_data_timeseries.
Rather, values for these indicators are calculated on-demand ad-hoc within the application later in the rsf_program_get_data() function.
The reason for this is that these indicators have values that might change beyond the reporting timelines of the entities that are capturing them. For example, the REPORTING_STATUS that is determined relative to the reporting_asof_date for which reporting_status is requested (rather than relative to the last known value, as is the case with all reported data in rsf_data).';
COMMENT ON COLUMN "p_rsf"."indicators"."default_subscription" IS 'Application layer will submit first in a partial cohort to trigger rsf_data_timeseries to only manage this indicator data first (this is used by currency unit reporting to ensure that if updates to currency data units will exist before other data on the same timeline so rsf_data_timeseries interprets their LCU data units according to the current update in the same timeline)';
COMMENT ON COLUMN "p_rsf"."indicators"."is_periodic_or_flow_reporting" IS 'Overwhelmingly, data is STOCK data and static (or implicitly interpreted to be stock, such as a loan repayment can be interpreted as a single "last payment made" rather than a flow of payments over time). This flags data that are flow: such as the QDD date or other data that are explicilty flow data associated with the reporting period.
As of 2023, this metric is NOT used by the calculator or database to determine changes. Eg, if a flow indicator reports $100 this quarter and next quarter also reports $100, it will be discarded as a non-change. Currently, as these indicators are rare it presents no issues. This may be a todo item to update';
-- ----------------------------
-- Table structure for label_ids
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."label_ids";
CREATE TABLE "p_rsf"."label_ids" (
"label_id" int4 NOT NULL DEFAULT nextval('"p_rsf".label_ids_label_id_seq'::regclass),
"label_id_group" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
"label_definition" text COLLATE "pg_catalog"."default"
)
;
-- ----------------------------
-- Table structure for label_keys
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."label_keys";
CREATE TABLE "p_rsf"."label_keys" (
"label_key" varchar(25) COLLATE "pg_catalog"."default" NOT NULL,
"label_key_name" varchar(50) COLLATE "pg_catalog"."default" NOT NULL,
"key_type" varchar(15) COLLATE "pg_catalog"."default" NOT NULL,
"key_type_template_id" int4
)
;
-- ----------------------------
-- Table structure for labels
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."labels";
CREATE TABLE "p_rsf"."labels" (
"label_id" int4 NOT NULL,
"label_key" varchar(25) COLLATE "pg_catalog"."default" NOT NULL,
"primary_label" text COLLATE "pg_catalog"."default" NOT NULL,
"secondary_labels" text[] COLLATE "pg_catalog"."default" NOT NULL DEFAULT ARRAY[]::text[],
"label_id_group" varchar(255) COLLATE "pg_catalog"."default" NOT NULL
)
;
-- ----------------------------
-- Table structure for program_settings
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."program_settings";
CREATE TABLE "p_rsf"."program_settings" (
"setting_name" text COLLATE "pg_catalog"."default" NOT NULL,
"default_value" text COLLATE "pg_catalog"."default" NOT NULL,
"setting_group" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'none'::text,
"default_data_type" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'text'::text,
"definition" text COLLATE "pg_catalog"."default"
)
;
-- ----------------------------
-- Table structure for reporting_cohort_info
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."reporting_cohort_info";
CREATE TABLE "p_rsf"."reporting_cohort_info" (
"reporting_cohort_id" int4 NOT NULL,
"metadata" jsonb NOT NULL DEFAULT '{}'::jsonb,
"upload_filename" text COLLATE "pg_catalog"."default",
"upload_file" bytea,
"data_count_reported" int4 NOT NULL DEFAULT 0,
"data_count_calculated" int4 NOT NULL DEFAULT 0,
"data_current_count_reported" int4 NOT NULL DEFAULT 0,
"data_current_count_calculated" int4 NOT NULL DEFAULT 0
)
;
-- ----------------------------
-- Table structure for reporting_cohorts
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."reporting_cohorts";
CREATE TABLE "p_rsf"."reporting_cohorts" (
"reporting_cohort_id" int4 NOT NULL DEFAULT nextval('"p_rsf".rsf_data_cohort_sequence'::regclass),
"reporting_asof_date" date NOT NULL,
"reporting_rsf_pfcbl_id" int4 NOT NULL,
"reporting_user_id" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'UNKNOWN'::text,
"reporting_time" timestamptz(6) NOT NULL DEFAULT now(),
"is_calculated_cohort" bool NOT NULL DEFAULT false,
"is_reported_cohort" bool NOT NULL DEFAULT true,
"data_asof_date" date NOT NULL,
"import_id" int4 NOT NULL,
"reporting_type" int2 NOT NULL DEFAULT 0
)
;
COMMENT ON COLUMN "p_rsf"."reporting_cohorts"."reporting_user_id" IS 'References: ARL.arlapplications.accounts.account_id';
COMMENT ON COLUMN "p_rsf"."reporting_cohorts"."data_asof_date" IS 'If the data timestamp is not quarter end, precisely.';
COMMENT ON COLUMN "p_rsf"."reporting_cohorts"."reporting_type" IS '0=Setup/System, 1=User Import, 2=Calculator';
-- ----------------------------
-- Table structure for reporting_import_template_headers
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."reporting_import_template_headers";
CREATE TABLE "p_rsf"."reporting_import_template_headers" (
"import_id" int4 NOT NULL,
"rsf_pfcbl_id" int4 NOT NULL,
"indicator_id" int4 NOT NULL,
"template_header" text COLLATE "pg_catalog"."default" NOT NULL,
"template_header_position" text COLLATE "pg_catalog"."default" NOT NULL
)
;
-- ----------------------------
-- Table structure for reporting_imports
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."reporting_imports";
CREATE TABLE "p_rsf"."reporting_imports" (
"import_id" int4 NOT NULL DEFAULT (nextval('"p_rsf".import_templates_import_id_seq'::regclass))::integer,
"import_rsf_pfcbl_id" int4 NOT NULL,
"import_pfcbl_category" text COLLATE "pg_catalog"."default" NOT NULL,
"import_user_id" text COLLATE "pg_catalog"."default" NOT NULL,
"import_time" timestamptz(6) NOT NULL DEFAULT (timeofday())::timestamp with time zone,
"import_completed" bool NOT NULL DEFAULT false,
"reporting_asof_date" date NOT NULL,
"template_id" int4 NOT NULL,
"file_name" text COLLATE "pg_catalog"."default" NOT NULL,
"file_data" bytea NOT NULL,
"is_finalized" bool NOT NULL DEFAULT false,
"finalized_by_user_id" text COLLATE "pg_catalog"."default",
"finalized_time" timestamptz(6),
"import_comments" text COLLATE "pg_catalog"."default",
"finalized_comments" text COLLATE "pg_catalog"."default",
"pfcbl_name" text COLLATE "pg_catalog"."default" NOT NULL,
"metadata" jsonb NOT NULL DEFAULT '{}'::jsonb
)
;
-- ----------------------------
-- Table structure for reporting_imports_deleted_archive
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."reporting_imports_deleted_archive";
CREATE TABLE "p_rsf"."reporting_imports_deleted_archive" (
"import_id" int4 NOT NULL,
"import_rsf_pfcbl_id" int4 NOT NULL,
"file_name" text COLLATE "pg_catalog"."default" NOT NULL,
"file_data" bytea,
"reporting_asof_date" date NOT NULL,
"import_user_id" text COLLATE "pg_catalog"."default" NOT NULL,
"import_comments" text COLLATE "pg_catalog"."default",
"deleting_user_id" text COLLATE "pg_catalog"."default" NOT NULL,
"deleted_time" timestamptz(6) NOT NULL DEFAULT (timeofday())::timestamp with time zone
)
;
-- ----------------------------
-- Table structure for reporting_templates
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."reporting_templates";
CREATE TABLE "p_rsf"."reporting_templates" (
"template_id" int4 NOT NULL DEFAULT nextval('"p_rsf".reporting_templates_template_id_seq'::regclass),
"template_name" varchar(255) COLLATE "pg_catalog"."default" NOT NULL DEFAULT ARRAY[]::integer[],
"template_key" varchar(255) COLLATE "pg_catalog"."default" NOT NULL DEFAULT upper(md5((now())::text)),
"description" text COLLATE "pg_catalog"."default",
"is_reportable" bool NOT NULL DEFAULT false,
"template_has_static_row_ids" bool NOT NULL DEFAULT false,
"is_setup_template" bool NOT NULL DEFAULT false,
"file_extension" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'xlsx'::text,
"is_system" bool NOT NULL DEFAULT false
)
;
COMMENT ON COLUMN "p_rsf"."reporting_templates"."template_has_static_row_ids" IS 'Template format reliably reports same entry on same row, always adding new entries to new rows. Ie, row 1 will always report the same entity, etc';
-- ----------------------------
-- Table structure for reports
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."reports";
CREATE TABLE "p_rsf"."reports" (
"report_id" int4 NOT NULL DEFAULT nextval('"p_rsf".reports_report_id_seq'::regclass),
"created_by_user_id" text COLLATE "pg_catalog"."default" NOT NULL,
"is_public" bool NOT NULL,
"report_title" text COLLATE "pg_catalog"."default" NOT NULL,
"report_notes" text COLLATE "pg_catalog"."default",
"for_program_sys_name" text COLLATE "pg_catalog"."default",
"for_facility_sys_names" jsonb,
"for_indicator_names" jsonb,
"for_asof_dates" jsonb,
"report_parameters" jsonb NOT NULL
)
;
-- ----------------------------
-- Table structure for rsf_clients
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_clients";
CREATE TABLE "p_rsf"."rsf_clients" (
"rsf_client_id" int4 NOT NULL,
"rsf_facility_id" int4 NOT NULL
)
;
-- ----------------------------
-- Table structure for rsf_data
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_data";
CREATE TABLE "p_rsf"."rsf_data" (
"data_id" int4 NOT NULL DEFAULT nextval('"p_rsf".rsf_data_data_id_seq'::regclass),
"rsf_pfcbl_id" int4 NOT NULL,
"indicator_id" int4 NOT NULL,
"reporting_asof_date" date NOT NULL,
"reporting_cohort_id" int4 NOT NULL,
"data_value" text COLLATE "pg_catalog"."default",
"data_unit" text COLLATE "pg_catalog"."default",
"data_submitted" text COLLATE "pg_catalog"."default",
"data_source_row_id" varchar(128) COLLATE "pg_catalog"."default",
"data_sys_flags" int2,
"data_sys_source" int2 NOT NULL DEFAULT 0
)
;
COMMENT ON COLUMN "p_rsf"."rsf_data"."data_submitted" IS 'Whatever was submitted via user input or sys (whereas data_value is the data of record and potentially normalized)';
COMMENT ON COLUMN "p_rsf"."rsf_data"."data_source_row_id" IS 'Row number (and/or reference) from uploaded Excel template that corresponds to reporting_cohort_id. NULL are system inputs that have no explicit or meaningful row_id';
COMMENT ON COLUMN "p_rsf"."rsf_data"."data_sys_flags" IS 'Flags:
0: reserved
1: reserved
2: deleted (won''t be present in rsf_data_current)
4: manual overwrite (if calculated, accept; no overwrite)
6: "reverted" ie, delete current and restore previously reported value.
8: ';
COMMENT ON COLUMN "p_rsf"."rsf_data"."data_sys_source" IS '0=Undefined; 1=Reported Cohort; 2=System Calculation Cohort; 3=System Generated';
-- ----------------------------
-- Table structure for rsf_data_calculation_evaluations
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_data_calculation_evaluations";
CREATE TABLE "p_rsf"."rsf_data_calculation_evaluations" (
"rsf_pfcbl_id" int4 NOT NULL,
"indicator_id" int4 NOT NULL,
"calculation_asof_date" date NOT NULL
)
;
-- ----------------------------
-- Table structure for rsf_data_calculation_validations
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_data_calculation_validations";
CREATE TABLE "p_rsf"."rsf_data_calculation_validations" (
"rsf_pfcbl_id" int4 NOT NULL,
"indicator_id" int4 NOT NULL,
"calculation_asof_date" date NOT NULL,
"data_id" int4 NOT NULL,
"validation_time" timestamptz(6) NOT NULL DEFAULT (timeofday())::timestamp with time zone
)
;
-- ----------------------------
-- Table structure for rsf_data_check_evaluations
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_data_check_evaluations";
CREATE TABLE "p_rsf"."rsf_data_check_evaluations" (
"rsf_pfcbl_id" int4 NOT NULL,
"check_asof_date" date NOT NULL,
"check_formula_id" int4 NOT NULL
)
;
-- ----------------------------
-- Table structure for rsf_data_checks
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_data_checks";
CREATE TABLE "p_rsf"."rsf_data_checks" (
"evaluation_id" int4 NOT NULL DEFAULT nextval('"p_rsf".rsf_data_checks_evaluation_id_seq'::regclass),
"data_id" int4 NOT NULL,
"rsf_pfcbl_id" int4 NOT NULL,
"indicator_id" int4 NOT NULL,
"check_asof_date" date NOT NULL,
"indicator_check_id" int4 NOT NULL,
"status_time" timestamptz(6) NOT NULL DEFAULT (timeofday())::timestamp with time zone,
"check_message" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'Check does not defined message. Update in settings'::text,
"check_status" text COLLATE "pg_catalog"."default" NOT NULL DEFAULT 'active'::character varying,
"check_status_comment" text COLLATE "pg_catalog"."default",
"check_status_user_id" text COLLATE "pg_catalog"."default",
"check_ignore" bool NOT NULL DEFAULT false,
"indicator_check_guidance_id" int4,
"check_data_id_is_current" bool NOT NULL,
"check_formula_id" int4,
"consolidated_from_indicator_id" int4,
"consolidated_from_indicator_check_id" int4,
"data_sys_flags" int2,
"data_value_unit" text COLLATE "pg_catalog"."default",
"archive_sys_name" text COLLATE "pg_catalog"."default"
)
;
COMMENT ON COLUMN "p_rsf"."rsf_data_checks"."check_ignore" IS 'TODO: remove this and consolidate "ignore" fully under guidance';
COMMENT ON COLUMN "p_rsf"."rsf_data_checks"."data_sys_flags" IS 'Flags on rsf_data are set via flagging';
-- ----------------------------
-- Table structure for rsf_data_checks_archive
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_data_checks_archive";
CREATE TABLE "p_rsf"."rsf_data_checks_archive" (
"archive_id" int4 NOT NULL,
"archive_time" timestamptz(6),
"sys_name" text COLLATE "pg_catalog"."default",
"rsf_pfcbl_id" int4,
"indicator_id" int4,
"indicator_check_id" int4,
"check_formula_id" int4,
"check_asof_date" date,
"check_status" text COLLATE "pg_catalog"."default",
"status_time" timestamptz(6),
"check_status_user_id" text COLLATE "pg_catalog"."default",
"check_status_comment" text COLLATE "pg_catalog"."default",
"check_message" text COLLATE "pg_catalog"."default",
"consolidated_from_indicator_id" int4,
"consolidated_from_indicator_check_id" int4,
"data_sys_flags" int2,
"data_value_unit" text COLLATE "pg_catalog"."default"
)
;
-- ----------------------------
-- Table structure for rsf_data_current
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_data_current";
CREATE TABLE "p_rsf"."rsf_data_current" (
"data_id" int4 NOT NULL,
"rsf_pfcbl_id" int4 NOT NULL,
"indicator_id" int4 NOT NULL,
"reporting_asof_date" date NOT NULL,
"data_value" text COLLATE "pg_catalog"."default",
"data_unit" text COLLATE "pg_catalog"."default",
"data_unit_data_id" int4
)
;
-- ----------------------------
-- Table structure for rsf_data_current_fx
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_data_current_fx";
CREATE TABLE "p_rsf"."rsf_data_current_fx" (
"rsf_pfcbl_id" int4 NOT NULL,
"indicator_id" int4 NOT NULL,
"reporting_asof_date" date NOT NULL,
"fx_data_id" int4 NOT NULL
)
;
COMMENT ON COLUMN "p_rsf"."rsf_data_current_fx"."fx_data_id" IS 'Current FX data value USED for this calculation';
-- ----------------------------
-- Table structure for rsf_data_current_lcu
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_data_current_lcu";
CREATE TABLE "p_rsf"."rsf_data_current_lcu" (
"lcu_unit_data_id" int4 NOT NULL,
"for_rsf_pfcbl_id" int4 NOT NULL,
"reporting_asof_date" date NOT NULL,
"data_unit_value" text COLLATE "pg_catalog"."default" NOT NULL,
"data_id_pfcbl_rank" int2 NOT NULL,
"is_defined_lcu" bool NOT NULL
)
;
COMMENT ON COLUMN "p_rsf"."rsf_data_current_lcu"."data_id_pfcbl_rank" IS 'Entity will inheret an LCU value that is greater than its own rank and lower than any rank that''s currently there. Eg, if current data_id is from Program and its Facility submits a value, it will be accepted; and if it then submits a value it will be accepted -- but if the facility then updates its value it will not since it already has a lower value present.';
COMMENT ON COLUMN "p_rsf"."rsf_data_current_lcu"."is_defined_lcu" IS 'If an entity submits a non-null defined unit (defined currency unit can only be applied at equal pfcbl_rank) then no inherited values can be accepted after that reporting date.';
-- ----------------------------
-- Table structure for rsf_data_current_names_and_ids
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_data_current_names_and_ids";
CREATE TABLE "p_rsf"."rsf_data_current_names_and_ids" (
"rsf_pfcbl_id" int4 NOT NULL,
"reporting_asof_date" date NOT NULL,
"sys_name" text COLLATE "pg_catalog"."default",
"id" text COLLATE "pg_catalog"."default",
"name" text COLLATE "pg_catalog"."default",
"nickname" text COLLATE "pg_catalog"."default",
"rank_id" text COLLATE "pg_catalog"."default",
"pfcbl_category" text COLLATE "pg_catalog"."default",
"pfcbl_name" text COLLATE "pg_catalog"."default",
"tranche_id" text COLLATE "pg_catalog"."default",
"series_id" text COLLATE "pg_catalog"."default"
)
;
-- ----------------------------
-- Table structure for rsf_facilities
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_facilities";
CREATE TABLE "p_rsf"."rsf_facilities" (
"rsf_facility_id" int4 NOT NULL,
"rsf_program_id" int4 NOT NULL
)
;
-- ----------------------------
-- Table structure for rsf_pfcbl_categories
-- ----------------------------
DROP TABLE IF EXISTS "p_rsf"."rsf_pfcbl_categories";
CREATE TABLE "p_rsf"."rsf_pfcbl_categories" (
"pfcbl_category" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
"pfcbl_rank" int2