-
Notifications
You must be signed in to change notification settings - Fork 33
Expand file tree
/
Copy pathinitialise_local.sql
More file actions
207 lines (193 loc) · 6.72 KB
/
Copy pathinitialise_local.sql
File metadata and controls
207 lines (193 loc) · 6.72 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
DROP TABLE IF EXISTS household;
DROP TABLE IF EXISTS computed_household;
DROP TABLE IF EXISTS policy;
DROP TABLE IF EXISTS economy;
DROP TABLE IF EXISTS reform_impact;
DROP TABLE IF EXISTS analysis;
DROP TABLE IF EXISTS user_policies;
DROP TABLE IF EXISTS tracers;
DROP TABLE IF EXISTS report_output_runs;
DROP TABLE IF EXISTS simulation_runs;
DROP TABLE IF EXISTS legacy_report_output_aliases;
CREATE TABLE IF NOT EXISTS household (
id INTEGER PRIMARY KEY,
country_id VARCHAR(3) NOT NULL,
label VARCHAR(255),
api_version VARCHAR(255) NOT NULL,
household_json JSONB NOT NULL,
household_hash VARCHAR(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS computed_household (
household_id INT NOT NULL,
policy_id INT NOT NULL,
country_id VARCHAR(3) NOT NULL,
api_version VARCHAR(10) NOT NULL,
computed_household_json JSONB NOT NULL,
status VARCHAR(32),
PRIMARY KEY (household_id, policy_id, country_id)
);
CREATE TABLE IF NOT EXISTS policy (
id INTEGER PRIMARY KEY,
country_id VARCHAR(3) NOT NULL,
label VARCHAR(255),
api_version VARCHAR(10) NOT NULL,
policy_json JSONB NOT NULL,
policy_hash VARCHAR(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS economy (
economy_id INTEGER PRIMARY KEY,
policy_id INT NOT NULL,
country_id VARCHAR(3) NOT NULL,
region VARCHAR(32),
time_period VARCHAR(32),
options_json JSON NOT NULL,
options_hash VARCHAR(255) NOT NULL,
api_version VARCHAR(10) NOT NULL,
economy_json JSON,
status VARCHAR(32) NOT NULL,
message VARCHAR(255)
);
CREATE TABLE IF NOT EXISTS reform_impact (
reform_impact_id INTEGER PRIMARY KEY,
baseline_policy_id INT NOT NULL,
reform_policy_id INT NOT NULL,
country_id VARCHAR(3) NOT NULL,
region VARCHAR(32) NOT NULL,
dataset VARCHAR(255) NOT NULL,
time_period VARCHAR(32) NOT NULL,
options_json JSON NOT NULL,
options_hash VARCHAR(255) NOT NULL,
api_version VARCHAR(10) NOT NULL,
reform_impact_json JSON NOT NULL,
status VARCHAR(32) NOT NULL,
message VARCHAR(255),
start_time DATETIME NOT NULL,
end_time DATETIME,
execution_id VARCHAR(255) NOT NULL
);
CREATE TABLE IF NOT EXISTS analysis (
prompt_id INTEGER PRIMARY KEY,
prompt LONGTEXT NOT NULL,
analysis LONGTEXT,
status VARCHAR(32) NOT NULL
);
-- The dataset row below was added while the table is in prod;
-- we must allow NULL values for this column
CREATE TABLE IF NOT EXISTS user_policies (
id INTEGER PRIMARY KEY,
country_id VARCHAR(3) NOT NULL,
reform_id INTEGER NOT NULL,
reform_label VARCHAR(255),
baseline_id INTEGER NOT NULL,
baseline_label VARCHAR(255),
user_id VARCHAR(255) NOT NULL,
year VARCHAR(32) NOT NULL,
geography VARCHAR(255) NOT NULL,
dataset VARCHAR(255),
number_of_provisions INTEGER NOT NULL,
api_version VARCHAR(32) NOT NULL,
added_date BIGINT NOT NULL,
updated_date BIGINT NOT NULL,
budgetary_impact VARCHAR(255),
type VARCHAR(255)
);
CREATE TABLE IF NOT EXISTS user_profiles (
user_id INTEGER PRIMARY KEY,
auth0_id VARCHAR(255) NOT NULL UNIQUE,
username VARCHAR(255) UNIQUE,
primary_country VARCHAR(3) NOT NULL,
user_since BIGINT NOT NULL
);
CREATE TABLE IF NOT EXISTS tracers (
id INTEGER PRIMARY KEY,
household_id INT NOT NULL,
policy_id INT NOT NULL,
country_id VARCHAR(3) NOT NULL,
api_version VARCHAR(10) NOT NULL,
tracer_output JSON NOT NULL
);
CREATE TABLE IF NOT EXISTS simulations (
id INTEGER PRIMARY KEY,
country_id VARCHAR(3) NOT NULL,
api_version VARCHAR(10) NOT NULL,
-- VARCHAR(255) to accommodate both household IDs and geography codes
population_id VARCHAR(255) NOT NULL,
population_type VARCHAR(50) NOT NULL,
policy_id INT NOT NULL,
status VARCHAR(32) NOT NULL DEFAULT 'pending',
output JSON DEFAULT NULL,
error_message TEXT DEFAULT NULL,
simulation_spec_json JSON DEFAULT NULL,
simulation_spec_schema_version INT DEFAULT NULL,
active_run_id CHAR(36) DEFAULT NULL,
latest_successful_run_id CHAR(36) DEFAULT NULL
);
CREATE TABLE IF NOT EXISTS report_outputs (
id INTEGER PRIMARY KEY,
country_id VARCHAR(3) NOT NULL,
simulation_1_id INT NOT NULL,
simulation_2_id INT DEFAULT NULL,
api_version VARCHAR(10) NOT NULL,
status VARCHAR(32) NOT NULL DEFAULT 'pending',
output JSON DEFAULT NULL,
error_message TEXT DEFAULT NULL,
year VARCHAR(255) DEFAULT '2025',
report_kind VARCHAR(64) DEFAULT NULL,
report_spec_json JSON DEFAULT NULL,
report_spec_schema_version INT DEFAULT NULL,
report_spec_status VARCHAR(32) DEFAULT NULL,
report_identity_hash VARCHAR(64) DEFAULT NULL,
report_identity_schema_version INT DEFAULT NULL,
active_run_id CHAR(36) DEFAULT NULL,
latest_successful_run_id CHAR(36) DEFAULT NULL
);
CREATE TABLE IF NOT EXISTS report_output_runs (
id CHAR(36) PRIMARY KEY,
report_output_id INT NOT NULL,
run_sequence INT NOT NULL,
status VARCHAR(32) NOT NULL,
output JSON DEFAULT NULL,
error_message TEXT DEFAULT NULL,
trigger_type VARCHAR(32) NOT NULL,
requested_at DATETIME DEFAULT NULL,
started_at DATETIME DEFAULT NULL,
finished_at DATETIME DEFAULT NULL,
source_run_id CHAR(36) DEFAULT NULL,
report_spec_snapshot_json JSON DEFAULT NULL,
country_package_version VARCHAR(255) DEFAULT NULL,
policyengine_version VARCHAR(255) DEFAULT NULL,
data_version VARCHAR(255) DEFAULT NULL,
runtime_app_name VARCHAR(255) DEFAULT NULL,
report_cache_version VARCHAR(255) DEFAULT NULL,
simulation_cache_version VARCHAR(255) DEFAULT NULL,
requested_version_override VARCHAR(255) DEFAULT NULL,
resolved_dataset VARCHAR(255) DEFAULT NULL,
resolved_options_hash VARCHAR(255) DEFAULT NULL,
UNIQUE (report_output_id, run_sequence)
);
CREATE TABLE IF NOT EXISTS simulation_runs (
id CHAR(36) PRIMARY KEY,
simulation_id INT NOT NULL,
report_output_run_id CHAR(36) DEFAULT NULL,
input_position TINYINT DEFAULT NULL,
run_sequence INT NOT NULL,
status VARCHAR(32) NOT NULL,
output JSON DEFAULT NULL,
error_message TEXT DEFAULT NULL,
trigger_type VARCHAR(32) NOT NULL,
requested_at DATETIME DEFAULT NULL,
started_at DATETIME DEFAULT NULL,
finished_at DATETIME DEFAULT NULL,
source_run_id CHAR(36) DEFAULT NULL,
simulation_spec_snapshot_json JSON DEFAULT NULL,
country_package_version VARCHAR(255) DEFAULT NULL,
policyengine_version VARCHAR(255) DEFAULT NULL,
data_version VARCHAR(255) DEFAULT NULL,
runtime_app_name VARCHAR(255) DEFAULT NULL,
simulation_cache_version VARCHAR(255) DEFAULT NULL,
UNIQUE (simulation_id, run_sequence)
);
CREATE TABLE IF NOT EXISTS legacy_report_output_aliases (
legacy_report_output_id INT PRIMARY KEY,
canonical_report_output_id INT NOT NULL
);