1-- +migrate Up
  2
  3-- History
  4CREATE TABLE block (
  5    eth_block_num BIGINT PRIMARY KEY,
  6    timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  7    hash BYTEA NOT NULL
  8);
  9
 10CREATE TABLE coordinator (
 11    item_id SERIAL PRIMARY KEY,
 12    bidder_addr BYTEA NOT NULL,
 13    forger_addr BYTEA NOT NULL,
 14    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 15    url VARCHAR(200) NOT NULL
 16);
 17
 18CREATE TABLE batch (
 19    batch_num BIGINT PRIMARY KEY,
 20    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 21    forger_addr BYTEA NOT NULL, -- fake foreign key for coordinator
 22    fees_collected BYTEA NOT NULL,
 23    state_root BYTEA NOT NULL,
 24    num_accounts BIGINT NOT NULL,
 25    exit_root BYTEA NOT NULL,
 26    forge_l1_txs_num BIGINT,
 27    slot_num BIGINT NOT NULL,
 28    total_fees_usd NUMERIC
 29);
 30
 31CREATE TABLE bid (
 32    slot_num BIGINT NOT NULL,
 33    bid_value BYTEA NOT NULL,
 34    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 35    bidder_addr BYTEA NOT NULL, -- fake foreign key for coordinator
 36    PRIMARY KEY (slot_num, bid_value)
 37);
 38
 39CREATE TABLE token (
 40    item_id SERIAL PRIMARY KEY,
 41    token_id INT UNIQUE NOT NULL,
 42    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 43    eth_addr BYTEA UNIQUE NOT NULL,
 44    name VARCHAR(20) NOT NULL,
 45    symbol VARCHAR(10) NOT NULL,
 46    decimals INT NOT NULL,
 47    usd NUMERIC,
 48    usd_update TIMESTAMP WITHOUT TIME ZONE
 49);
 50
 51CREATE TABLE account (
 52    idx BIGINT PRIMARY KEY,
 53    token_id INT NOT NULL REFERENCES token (token_id) ON DELETE CASCADE,
 54    batch_num BIGINT NOT NULL REFERENCES batch (batch_num) ON DELETE CASCADE,
 55    bjj BYTEA NOT NULL,
 56    eth_addr BYTEA NOT NULL
 57);
 58
 59CREATE TABLE exit_tree (
 60    item_id SERIAL PRIMARY KEY,
 61    batch_num BIGINT REFERENCES batch (batch_num) ON DELETE CASCADE,
 62    account_idx BIGINT REFERENCES account (idx) ON DELETE CASCADE,
 63    merkle_proof BYTEA NOT NULL,
 64    balance BYTEA NOT NULL,
 65    instant_withdrawn BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL,
 66    delayed_withdraw_request BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL,
 67    delayed_withdrawn BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL
 68);
 69
 70-- +migrate StatementBegin
 71CREATE FUNCTION set_token_usd_update() 
 72    RETURNS TRIGGER 
 73AS 
 74$BODY$
 75BEGIN
 76    IF NEW."usd" IS NOT NULL AND NEW."usd_update" IS NULL THEN
 77        NEW."usd_update" = timezone('utc', now());
 78    END IF;
 79    RETURN NEW;
 80END;
 81$BODY$
 82LANGUAGE plpgsql;
 83-- +migrate StatementEnd
 84CREATE TRIGGER trigger_token_usd_update BEFORE UPDATE OR INSERT ON token
 85FOR EACH ROW EXECUTE PROCEDURE set_token_usd_update();
 86
 87CREATE SEQUENCE tx_item_id;
 88
 89CREATE TABLE tx (
 90    -- Generic TX
 91    item_id INTEGER PRIMARY KEY DEFAULT nextval('tx_item_id'),
 92    is_l1 BOOLEAN NOT NULL,
 93    id BYTEA,
 94    type VARCHAR(40) NOT NULL,
 95    position INT NOT NULL,
 96    from_idx BIGINT,
 97    to_idx BIGINT NOT NULL,
 98    amount BYTEA NOT NULL,
 99    amount_f NUMERIC NOT NULL,
100    token_id INT NOT NULL REFERENCES token (token_id),
101    amount_usd NUMERIC, -- Value of the amount in USD at the moment the tx was inserted in the DB
102    batch_num BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL, -- Can be NULL in the case of L1 txs that are on the queue but not forged yet.
103    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
104    -- L1
105    to_forge_l1_txs_num BIGINT,
106    user_origin BOOLEAN,
107    from_eth_addr BYTEA,
108    from_bjj BYTEA,
109    load_amount BYTEA,
110    load_amount_f NUMERIC,
111    load_amount_usd NUMERIC,
112    -- L2
113    fee INT,
114    fee_usd NUMERIC,
115    nonce BIGINT
116);
117
118-- +migrate StatementBegin
119CREATE FUNCTION fee_percentage(NUMERIC)
120    RETURNS NUMERIC 
121AS 
122$BODY$
123DECLARE perc NUMERIC;
124BEGIN
125    SELECT CASE 
126        WHEN $1 = 0 THEN 0
127        WHEN $1 = 1 THEN 3.162278e-24
128        WHEN $1 = 2 THEN 1.000000e-23
129        WHEN $1 = 3 THEN 3.162278e-23
130        WHEN $1 = 4 THEN 1.000000e-22
131        WHEN $1 = 5 THEN 3.162278e-22
132        WHEN $1 = 6 THEN 1.000000e-21
133        WHEN $1 = 7 THEN 3.162278e-21
134        WHEN $1 = 8 THEN 1.000000e-20
135        WHEN $1 = 9 THEN 3.162278e-20
136        WHEN $1 = 10 THEN 1.000000e-19
137        WHEN $1 = 11 THEN 3.162278e-19
138        WHEN $1 = 12 THEN 1.000000e-18
139        WHEN $1 = 13 THEN 3.162278e-18
140        WHEN $1 = 14 THEN 1.000000e-17
141        WHEN $1 = 15 THEN 3.162278e-17
142        WHEN $1 = 16 THEN 1.000000e-16
143        WHEN $1 = 17 THEN 3.162278e-16
144        WHEN $1 = 18 THEN 1.000000e-15
145        WHEN $1 = 19 THEN 3.162278e-15
146        WHEN $1 = 20 THEN 1.000000e-14
147        WHEN $1 = 21 THEN 3.162278e-14
148        WHEN $1 = 22 THEN 1.000000e-13
149        WHEN $1 = 23 THEN 3.162278e-13
150        WHEN $1 = 24 THEN 1.000000e-12
151        WHEN $1 = 25 THEN 3.162278e-12
152        WHEN $1 = 26 THEN 1.000000e-11
153        WHEN $1 = 27 THEN 3.162278e-11
154        WHEN $1 = 28 THEN 1.000000e-10
155        WHEN $1 = 29 THEN 3.162278e-10
156        WHEN $1 = 30 THEN 1.000000e-09
157        WHEN $1 = 31 THEN 3.162278e-09
158        WHEN $1 = 32 THEN 1.000000e-08
159        WHEN $1 = 33 THEN 1.100694e-08
160        WHEN $1 = 34 THEN 1.211528e-08
161        WHEN $1 = 35 THEN 1.333521e-08
162        WHEN $1 = 36 THEN 1.467799e-08
163        WHEN $1 = 37 THEN 1.615598e-08
164        WHEN $1 = 38 THEN 1.778279e-08
165        WHEN $1 = 39 THEN 1.957342e-08
166        WHEN $1 = 40 THEN 2.154435e-08
167        WHEN $1 = 41 THEN 2.371374e-08
168        WHEN $1 = 42 THEN 2.610157e-08
169        WHEN $1 = 43 THEN 2.872985e-08
170        WHEN $1 = 44 THEN 3.162278e-08
171        WHEN $1 = 45 THEN 3.480701e-08
172        WHEN $1 = 46 THEN 3.831187e-08
173        WHEN $1 = 47 THEN 4.216965e-08
174        WHEN $1 = 48 THEN 4.641589e-08
175        WHEN $1 = 49 THEN 5.108970e-08
176        WHEN $1 = 50 THEN 5.623413e-08
177        WHEN $1 = 51 THEN 6.189658e-08
178        WHEN $1 = 52 THEN 6.812921e-08
179        WHEN $1 = 53 THEN 7.498942e-08
180        WHEN $1 = 54 THEN 8.254042e-08
181        WHEN $1 = 55 THEN 9.085176e-08
182        WHEN $1 = 56 THEN 1.000000e-07
183        WHEN $1 = 57 THEN 1.100694e-07
184        WHEN $1 = 58 THEN 1.211528e-07
185        WHEN $1 = 59 THEN 1.333521e-07
186        WHEN $1 = 60 THEN 1.467799e-07
187        WHEN $1 = 61 THEN 1.615598e-07
188        WHEN $1 = 62 THEN 1.778279e-07
189        WHEN $1 = 63 THEN 1.957342e-07
190        WHEN $1 = 64 THEN 2.154435e-07
191        WHEN $1 = 65 THEN 2.371374e-07
192        WHEN $1 = 66 THEN 2.610157e-07
193        WHEN $1 = 67 THEN 2.872985e-07
194        WHEN $1 = 68 THEN 3.162278e-07
195        WHEN $1 = 69 THEN 3.480701e-07
196        WHEN $1 = 70 THEN 3.831187e-07
197        WHEN $1 = 71 THEN 4.216965e-07
198        WHEN $1 = 72 THEN 4.641589e-07
199        WHEN $1 = 73 THEN 5.108970e-07
200        WHEN $1 = 74 THEN 5.623413e-07
201        WHEN $1 = 75 THEN 6.189658e-07
202        WHEN $1 = 76 THEN 6.812921e-07
203        WHEN $1 = 77 THEN 7.498942e-07
204        WHEN $1 = 78 THEN 8.254042e-07
205        WHEN $1 = 79 THEN 9.085176e-07
206        WHEN $1 = 80 THEN 1.000000e-06
207        WHEN $1 = 81 THEN 1.100694e-06
208        WHEN $1 = 82 THEN 1.211528e-06
209        WHEN $1 = 83 THEN 1.333521e-06
210        WHEN $1 = 84 THEN 1.467799e-06
211        WHEN $1 = 85 THEN 1.615598e-06
212        WHEN $1 = 86 THEN 1.778279e-06
213        WHEN $1 = 87 THEN 1.957342e-06
214        WHEN $1 = 88 THEN 2.154435e-06
215        WHEN $1 = 89 THEN 2.371374e-06
216        WHEN $1 = 90 THEN 2.610157e-06
217        WHEN $1 = 91 THEN 2.872985e-06
218        WHEN $1 = 92 THEN 3.162278e-06
219        WHEN $1 = 93 THEN 3.480701e-06
220        WHEN $1 = 94 THEN 3.831187e-06
221        WHEN $1 = 95 THEN 4.216965e-06
222        WHEN $1 = 96 THEN 4.641589e-06
223        WHEN $1 = 97 THEN 5.108970e-06
224        WHEN $1 = 98 THEN 5.623413e-06
225        WHEN $1 = 99 THEN 6.189658e-06
226        WHEN $1 = 100 THEN 6.812921e-06
227        WHEN $1 = 101 THEN 7.498942e-06
228        WHEN $1 = 102 THEN 8.254042e-06
229        WHEN $1 = 103 THEN 9.085176e-06
230        WHEN $1 = 104 THEN 1.000000e-05
231        WHEN $1 = 105 THEN 1.100694e-05
232        WHEN $1 = 106 THEN 1.211528e-05
233        WHEN $1 = 107 THEN 1.333521e-05
234        WHEN $1 = 108 THEN 1.467799e-05
235        WHEN $1 = 109 THEN 1.615598e-05
236        WHEN $1 = 110 THEN 1.778279e-05
237        WHEN $1 = 111 THEN 1.957342e-05
238        WHEN $1 = 112 THEN 2.154435e-05
239        WHEN $1 = 113 THEN 2.371374e-05
240        WHEN $1 = 114 THEN 2.610157e-05
241        WHEN $1 = 115 THEN 2.872985e-05
242        WHEN $1 = 116 THEN 3.162278e-05
243        WHEN $1 = 117 THEN 3.480701e-05
244        WHEN $1 = 118 THEN 3.831187e-05
245        WHEN $1 = 119 THEN 4.216965e-05
246        WHEN $1 = 120 THEN 4.641589e-05
247        WHEN $1 = 121 THEN 5.108970e-05
248        WHEN $1 = 122 THEN 5.623413e-05
249        WHEN $1 = 123 THEN 6.189658e-05
250        WHEN $1 = 124 THEN 6.812921e-05
251        WHEN $1 = 125 THEN 7.498942e-05
252        WHEN $1 = 126 THEN 8.254042e-05
253        WHEN $1 = 127 THEN 9.085176e-05
254        WHEN $1 = 128 THEN 1.000000e-04
255        WHEN $1 = 129 THEN 1.100694e-04
256        WHEN $1 = 130 THEN 1.211528e-04
257        WHEN $1 = 131 THEN 1.333521e-04
258        WHEN $1 = 132 THEN 1.467799e-04
259        WHEN $1 = 133 THEN 1.615598e-04
260        WHEN $1 = 134 THEN 1.778279e-04
261        WHEN $1 = 135 THEN 1.957342e-04
262        WHEN $1 = 136 THEN 2.154435e-04
263        WHEN $1 = 137 THEN 2.371374e-04
264        WHEN $1 = 138 THEN 2.610157e-04
265        WHEN $1 = 139 THEN 2.872985e-04
266        WHEN $1 = 140 THEN 3.162278e-04
267        WHEN $1 = 141 THEN 3.480701e-04
268        WHEN $1 = 142 THEN 3.831187e-04
269        WHEN $1 = 143 THEN 4.216965e-04
270        WHEN $1 = 144 THEN 4.641589e-04
271        WHEN $1 = 145 THEN 5.108970e-04
272        WHEN $1 = 146 THEN 5.623413e-04
273        WHEN $1 = 147 THEN 6.189658e-04
274        WHEN $1 = 148 THEN 6.812921e-04
275        WHEN $1 = 149 THEN 7.498942e-04
276        WHEN $1 = 150 THEN 8.254042e-04
277        WHEN $1 = 151 THEN 9.085176e-04
278        WHEN $1 = 152 THEN 1.000000e-03
279        WHEN $1 = 153 THEN 1.100694e-03
280        WHEN $1 = 154 THEN 1.211528e-03
281        WHEN $1 = 155 THEN 1.333521e-03
282        WHEN $1 = 156 THEN 1.467799e-03
283        WHEN $1 = 157 THEN 1.615598e-03
284        WHEN $1 = 158 THEN 1.778279e-03
285        WHEN $1 = 159 THEN 1.957342e-03
286        WHEN $1 = 160 THEN 2.154435e-03
287        WHEN $1 = 161 THEN 2.371374e-03
288        WHEN $1 = 162 THEN 2.610157e-03
289        WHEN $1 = 163 THEN 2.872985e-03
290        WHEN $1 = 164 THEN 3.162278e-03
291        WHEN $1 = 165 THEN 3.480701e-03
292        WHEN $1 = 166 THEN 3.831187e-03
293        WHEN $1 = 167 THEN 4.216965e-03
294        WHEN $1 = 168 THEN 4.641589e-03
295        WHEN $1 = 169 THEN 5.108970e-03
296        WHEN $1 = 170 THEN 5.623413e-03
297        WHEN $1 = 171 THEN 6.189658e-03
298        WHEN $1 = 172 THEN 6.812921e-03
299        WHEN $1 = 173 THEN 7.498942e-03
300        WHEN $1 = 174 THEN 8.254042e-03
301        WHEN $1 = 175 THEN 9.085176e-03
302        WHEN $1 = 176 THEN 1.000000e-02
303        WHEN $1 = 177 THEN 1.100694e-02
304        WHEN $1 = 178 THEN 1.211528e-02
305        WHEN $1 = 179 THEN 1.333521e-02
306        WHEN $1 = 180 THEN 1.467799e-02
307        WHEN $1 = 181 THEN 1.615598e-02
308        WHEN $1 = 182 THEN 1.778279e-02
309        WHEN $1 = 183 THEN 1.957342e-02
310        WHEN $1 = 184 THEN 2.154435e-02
311        WHEN $1 = 185 THEN 2.371374e-02
312        WHEN $1 = 186 THEN 2.610157e-02
313        WHEN $1 = 187 THEN 2.872985e-02
314        WHEN $1 = 188 THEN 3.162278e-02
315        WHEN $1 = 189 THEN 3.480701e-02
316        WHEN $1 = 190 THEN 3.831187e-02
317        WHEN $1 = 191 THEN 4.216965e-02
318        WHEN $1 = 192 THEN 4.641589e-02
319        WHEN $1 = 193 THEN 5.108970e-02
320        WHEN $1 = 194 THEN 5.623413e-02
321        WHEN $1 = 195 THEN 6.189658e-02
322        WHEN $1 = 196 THEN 6.812921e-02
323        WHEN $1 = 197 THEN 7.498942e-02
324        WHEN $1 = 198 THEN 8.254042e-02
325        WHEN $1 = 199 THEN 9.085176e-02
326        WHEN $1 = 200 THEN 1.000000e-01
327        WHEN $1 = 201 THEN 1.100694e-01
328        WHEN $1 = 202 THEN 1.211528e-01
329        WHEN $1 = 203 THEN 1.333521e-01
330        WHEN $1 = 204 THEN 1.467799e-01
331        WHEN $1 = 205 THEN 1.615598e-01
332        WHEN $1 = 206 THEN 1.778279e-01
333        WHEN $1 = 207 THEN 1.957342e-01
334        WHEN $1 = 208 THEN 2.154435e-01
335        WHEN $1 = 209 THEN 2.371374e-01
336        WHEN $1 = 210 THEN 2.610157e-01
337        WHEN $1 = 211 THEN 2.872985e-01
338        WHEN $1 = 212 THEN 3.162278e-01
339        WHEN $1 = 213 THEN 3.480701e-01
340        WHEN $1 = 214 THEN 3.831187e-01
341        WHEN $1 = 215 THEN 4.216965e-01
342        WHEN $1 = 216 THEN 4.641589e-01
343        WHEN $1 = 217 THEN 5.108970e-01
344        WHEN $1 = 218 THEN 5.623413e-01
345        WHEN $1 = 219 THEN 6.189658e-01
346        WHEN $1 = 220 THEN 6.812921e-01
347        WHEN $1 = 221 THEN 7.498942e-01
348        WHEN $1 = 222 THEN 8.254042e-01
349        WHEN $1 = 223 THEN 9.085176e-01
350        WHEN $1 = 224 THEN 1.000000e+00
351        WHEN $1 = 225 THEN 1.000000e+01
352        WHEN $1 = 226 THEN 1.000000e+02
353        WHEN $1 = 227 THEN 1.000000e+03
354        WHEN $1 = 228 THEN 1.000000e+04
355        WHEN $1 = 229 THEN 1.000000e+05
356        WHEN $1 = 230 THEN 1.000000e+06
357        WHEN $1 = 231 THEN 1.000000e+07
358        WHEN $1 = 232 THEN 1.000000e+08
359        WHEN $1 = 233 THEN 1.000000e+09
360        WHEN $1 = 234 THEN 1.000000e+10
361        WHEN $1 = 235 THEN 1.000000e+11
362        WHEN $1 = 236 THEN 1.000000e+12
363        WHEN $1 = 237 THEN 1.000000e+13
364        WHEN $1 = 238 THEN 1.000000e+14
365        WHEN $1 = 239 THEN 1.000000e+15
366        WHEN $1 = 240 THEN 1.000000e+16
367        WHEN $1 = 241 THEN 1.000000e+17
368        WHEN $1 = 242 THEN 1.000000e+18
369        WHEN $1 = 243 THEN 1.000000e+19
370        WHEN $1 = 244 THEN 1.000000e+20
371        WHEN $1 = 245 THEN 1.000000e+21
372        WHEN $1 = 246 THEN 1.000000e+22
373        WHEN $1 = 247 THEN 1.000000e+23
374        WHEN $1 = 248 THEN 1.000000e+24
375        WHEN $1 = 249 THEN 1.000000e+25
376        WHEN $1 = 250 THEN 1.000000e+26
377        WHEN $1 = 251 THEN 1.000000e+27
378        WHEN $1 = 252 THEN 1.000000e+28
379        WHEN $1 = 253 THEN 1.000000e+29
380        WHEN $1 = 254 THEN 1.000000e+30
381        WHEN $1 = 255 THEN 1.000000e+31
382    END INTO perc;
383    RETURN perc;
384END;
385$BODY$
386LANGUAGE plpgsql;
387-- +migrate StatementEnd
388
389-- +migrate StatementBegin
390CREATE FUNCTION set_tx()
391    RETURNS TRIGGER 
392AS 
393$BODY$
394DECLARE
395	_value NUMERIC;
396	_usd_update TIMESTAMP;
397    _tx_timestamp TIMESTAMP;
398BEGIN
399    -- Validate L1/L2 constrains
400    IF NEW.is_l1 AND (( -- L1 mandatory fields
401        NEW.user_origin IS NULL OR
402        NEW.from_eth_addr IS NULL OR
403        NEW.from_bjj IS NULL OR
404        NEW.load_amount IS NULL OR
405        NEW.load_amount_f IS NULL
406    ) OR (NOT NEW.user_origin AND NEW.batch_num IS NULL)) THEN -- If is Coordinator L1, must include batch_num
407        RAISE EXCEPTION 'Invalid L1 tx.';
408    ELSIF NOT NEW.is_l1 THEN
409        IF NEW.fee IS NULL THEN
410            NEW.fee = (SELECT 0);
411        END IF;
412        IF NEW.batch_num IS NULL OR NEW.nonce IS NULL THEN
413            RAISE EXCEPTION 'Invalid L2 tx.';
414        END IF;
415    END IF;
416    -- If is L2, add token_id
417    IF NOT NEW.is_l1 THEN
418        NEW."token_id" = (SELECT token_id FROM account WHERE idx = NEW."from_idx");
419    END IF;
420    -- Set value_usd
421    SELECT INTO _value, _usd_update, _tx_timestamp 
422        usd / POWER(10, decimals), usd_update, timestamp FROM token INNER JOIN block on token.eth_block_num = block.eth_block_num WHERE token_id = NEW.token_id;
423    IF _tx_timestamp - interval '24 hours' < _usd_update AND _tx_timestamp + interval '24 hours' > _usd_update THEN
424        NEW."amount_usd" = (SELECT _value * NEW.amount_f);
425        IF NOT NEW.is_l1 THEN
426            NEW."fee_usd" = (SELECT NEW."amount_usd" * fee_percentage(NEW.fee::NUMERIC));
427        ELSE 
428            NEW."load_amount_usd" = (SELECT _value * NEW.load_amount_f);
429        END IF;
430    END IF;
431    RETURN NEW;
432END;
433$BODY$
434LANGUAGE plpgsql;
435-- +migrate StatementEnd
436CREATE TRIGGER trigger_set_tx BEFORE INSERT ON tx
437FOR EACH ROW EXECUTE PROCEDURE set_tx();
438
439-- +migrate StatementBegin
440CREATE FUNCTION forge_l1_user_txs() 
441    RETURNS TRIGGER 
442AS 
443$BODY$
444BEGIN
445    IF NEW.forge_l1_txs_num IS NOT NULL THEN
446        UPDATE tx 
447        SET item_id = nextval('tx_item_id'), batch_num = NEW.batch_num 
448        WHERE id IN (
449            SELECT id FROM tx 
450            WHERE user_origin AND NEW.forge_l1_txs_num = to_forge_l1_txs_num 
451            ORDER BY position
452            FOR UPDATE
453        ); 
454    END IF;
455    RETURN NEW;
456END;
457$BODY$
458LANGUAGE plpgsql;
459-- +migrate StatementEnd
460CREATE TRIGGER trigger_forge_l1_txs AFTER INSERT ON batch
461FOR EACH ROW EXECUTE PROCEDURE forge_l1_user_txs();
462
463CREATE TABLE rollup_vars (
464    eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
465    forge_l1_timeout BYTEA NOT NULL,
466    fee_l1_user_tx BYTEA NOT NULL,
467    fee_add_token BYTEA NOT NULL,
468    tokens_hez BYTEA NOT NULL,
469    governance BYTEA NOT NULL
470);
471
472CREATE TABLE consensus_vars (
473    eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
474    slot_deadline INT NOT NULL,
475    close_auction_slots INT NOT NULL,
476    open_auction_slots INT NOT NULL,
477    min_bid_slots VARCHAR(200) NOT NULL,
478    outbidding INT NOT NULL,
479    donation_address BYTEA NOT NULL,
480    governance_address BYTEA NOT NULL,
481    allocation_ratio VARCHAR(200)
482);
483
484-- L2
485CREATE TABLE tx_pool (
486    tx_id BYTEA PRIMARY KEY,
487    from_idx BIGINT NOT NULL,
488    to_idx BIGINT,
489    to_eth_addr BYTEA,
490    to_bjj BYTEA,
491    token_id INT NOT NULL REFERENCES token (token_id) ON DELETE CASCADE,
492    amount BYTEA NOT NULL,
493    amount_f NUMERIC NOT NULL,
494    fee SMALLINT NOT NULL,
495    nonce BIGINT NOT NULL,
496    state CHAR(4) NOT NULL,
497    signature BYTEA NOT NULL,
498    timestamp TIMESTAMP WITHOUT TIME ZONE DEFAULT timezone('utc', now()),
499    batch_num BIGINT,
500    rq_from_idx BIGINT,
501    rq_to_idx BIGINT,
502    rq_to_eth_addr BYTEA,
503    rq_to_bjj BYTEA,
504    rq_token_id INT,
505    rq_amount BYTEA,
506    rq_fee SMALLINT,
507    rq_nonce BIGINT,
508    tx_type VARCHAR(40) NOT NULL
509);
510
511CREATE TABLE account_creation_auth (
512    eth_addr BYTEA PRIMARY KEY,
513    bjj BYTEA NOT NULL,
514    signature BYTEA NOT NULL,
515    timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT timezone('utc', now())
516);
517
518-- +migrate Down
519DROP TABLE account_creation_auth;
520DROP TABLE tx_pool;
521DROP TABLE consensus_vars;
522DROP TABLE rollup_vars;
523DROP TABLE account;
524DROP TABLE tx;
525DROP TABLE token;
526DROP TABLE bid;
527DROP TABLE exit_tree;
528DROP TABLE batch;
529DROP TABLE coordinator;
530DROP TABLE block;