1-- +migrate Up
  2CREATE TABLE block (
  3    eth_block_num BIGINT PRIMARY KEY,
  4    timestamp TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  5    hash BYTEA NOT NULL
  6);
  7
  8CREATE TABLE coordinator (
  9    forger_addr BYTEA NOT NULL,
 10    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 11    withdraw_addr BYTEA NOT NULL,
 12    url VARCHAR(200) NOT NULL,
 13    PRIMARY KEY (forger_addr, eth_block_num)
 14);
 15
 16CREATE TABLE batch (
 17    batch_num BIGINT PRIMARY KEY,
 18    eth_block_num BIGINT REFERENCES block (eth_block_num) ON DELETE CASCADE,
 19    forger_addr BYTEA NOT NULL, -- fake foreign key for coordinator
 20    fees_collected BYTEA NOT NULL,
 21    state_root BYTEA NOT NULL,
 22    num_accounts BIGINT NOT NULL,
 23    exit_root BYTEA NOT NULL,
 24    forge_l1_txs_num BIGINT,
 25    slot_num BIGINT NOT NULL
 26);
 27
 28CREATE TABLE exit_tree (
 29    batch_num BIGINT REFERENCES batch (batch_num) ON DELETE CASCADE,
 30    account_idx BIGINT,
 31    merkle_proof BYTEA NOT NULL,
 32    balance BYTEA NOT NULL,
 33    instant_withdrawn BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL,
 34    delayed_withdraw_request BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL,
 35    delayed_withdrawn BIGINT REFERENCES batch (batch_num) ON DELETE SET NULL,
 36    PRIMARY KEY (batch_num, account_idx)
 37);
 38
 39CREATE TABLE bid (
 40    slot_num BIGINT NOT NULL,
 41    bid_value BYTEA NOT NULL,
 42    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 43    forger_addr BYTEA NOT NULL, -- fake foreign key for coordinator
 44    PRIMARY KEY (slot_num, bid_value)
 45);
 46
 47CREATE TABLE token (
 48    token_id INT PRIMARY KEY,
 49    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 50    eth_addr BYTEA UNIQUE NOT NULL,
 51    name VARCHAR(20) NOT NULL,
 52    symbol VARCHAR(10) NOT NULL,
 53    decimals INT NOT NULL,
 54    usd NUMERIC,
 55    usd_update TIMESTAMP
 56);
 57
 58-- +migrate StatementBegin
 59CREATE FUNCTION set_token_usd_update() 
 60    RETURNS TRIGGER 
 61AS 
 62$BODY$
 63BEGIN
 64    IF NEW."usd" IS NOT NULL AND NEW."usd_update" IS NULL THEN
 65        NEW."usd_update" = timezone('utc', now());
 66    END IF;
 67    RETURN NEW;
 68END;
 69$BODY$
 70LANGUAGE plpgsql;
 71-- +migrate StatementEnd
 72CREATE TRIGGER trigger_token_usd_update BEFORE UPDATE OR INSERT ON token
 73FOR EACH ROW EXECUTE PROCEDURE set_token_usd_update();
 74
 75CREATE TABLE tx (
 76    -- Generic TX
 77    is_l1 BOOLEAN NOT NULL,
 78    id BYTEA PRIMARY KEY,
 79    type VARCHAR(40) NOT NULL,
 80    position INT NOT NULL,
 81    from_idx BIGINT NOT NULL,
 82    to_idx BIGINT NOT NULL,
 83    amount BYTEA NOT NULL,
 84    amount_f NUMERIC NOT NULL,
 85    token_id INT NOT NULL REFERENCES token (token_id),
 86    amount_usd NUMERIC, -- Value of the amount in USD at the moment the tx was inserted in the DB
 87    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.
 88    eth_block_num BIGINT NOT NULL REFERENCES block (eth_block_num) ON DELETE CASCADE,
 89    -- L1
 90    to_forge_l1_txs_num BIGINT,
 91    user_origin BOOLEAN,
 92    from_eth_addr BYTEA,
 93    from_bjj BYTEA,
 94    load_amount BYTEA,
 95    load_amount_f NUMERIC,
 96    load_amount_usd NUMERIC,
 97    -- L2
 98    fee INT,
 99    fee_usd NUMERIC,
100    nonce BIGINT
101);
102
103CREATE INDEX tx_order ON tx (batch_num, position);
104
105-- +migrate StatementBegin
106CREATE FUNCTION set_tx()
107    RETURNS TRIGGER 
108AS 
109$BODY$
110DECLARE token_value NUMERIC := (SELECT usd FROM token WHERE token_id = NEW.token_id);
111BEGIN
112    -- Validate L1/L2 constrains
113    IF NEW.is_l1 AND (( -- L1 mandatory fields
114        NEW.user_origin IS NULL OR
115        NEW.from_eth_addr IS NULL OR
116        NEW.from_bjj IS NULL OR
117        NEW.load_amount IS NULL OR
118        NEW.load_amount_f IS NULL
119    ) OR (NOT NEW.user_origin AND NEW.batch_num IS NULL)) THEN -- If is Coordinator L1, must include batch_num
120        RAISE EXCEPTION 'Invalid L1 tx.';
121    ELSIF NOT NEW.is_l1 THEN
122        IF NEW.fee IS NULL THEN
123            NEW.fee = (SELECT 0);
124        END IF;
125        IF NEW.batch_num IS NULL OR NEW.nonce IS NULL THEN
126            RAISE EXCEPTION 'Invalid L2 tx.';
127        END IF;
128    END IF;
129    -- If is L2, add token_id
130    IF NEW.token_id IS NULL THEN
131        NEW."token_id" = (SELECT token_id FROM account WHERE idx = NEW."from_idx");
132    END IF;
133    -- Set value_usd
134    NEW."amount_usd" = (SELECT token_value * NEW.amount_f);
135    NEW."load_amount_usd" = (SELECT token_value * NEW.load_amount_f);
136    IF NOT NEW.is_l1 THEN
137        NEW."fee_usd" = (SELECT token_value * NEW.amount_f * CASE
138            WHEN NEW.fee = 0 THEN 0	
139            WHEN NEW.fee >= 1 AND NEW.fee <= 32 THEN POWER(10,-24+(NEW.fee::float/2))	
140            WHEN NEW.fee >= 33 AND NEW.fee <= 223 THEN POWER(10,-8+(0.041666666666667*(NEW.fee::float-32)))	
141            WHEN NEW.fee >= 224 AND NEW.fee <= 255 THEN POWER(10,NEW.fee-224) END);
142    END IF;
143    RETURN NEW;
144END;
145$BODY$
146LANGUAGE plpgsql;
147-- +migrate StatementEnd
148CREATE TRIGGER trigger_set_tx BEFORE INSERT ON tx
149FOR EACH ROW EXECUTE PROCEDURE set_tx();
150
151-- +migrate StatementBegin
152CREATE FUNCTION forge_l1_user_txs() 
153    RETURNS TRIGGER 
154AS 
155$BODY$
156BEGIN
157    IF NEW.forge_l1_txs_num IS NOT NULL THEN
158        UPDATE tx 
159        SET batch_num = NEW.batch_num
160        WHERE user_origin AND NEW.forge_l1_txs_num = to_forge_l1_txs_num;
161    END IF;
162    RETURN NEW;
163END;
164$BODY$
165LANGUAGE plpgsql;
166-- +migrate StatementEnd
167CREATE TRIGGER trigger_forge_l1_txs AFTER INSERT ON batch
168FOR EACH ROW EXECUTE PROCEDURE forge_l1_user_txs();
169
170CREATE TABLE account (
171    idx BIGINT PRIMARY KEY,
172    token_id INT NOT NULL REFERENCES token (token_id) ON DELETE CASCADE,
173    batch_num BIGINT NOT NULL REFERENCES batch (batch_num) ON DELETE CASCADE,
174    bjj BYTEA NOT NULL,
175    eth_addr BYTEA NOT NULL
176);
177
178CREATE TABLE rollup_vars (
179    eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
180    forge_l1_timeout BYTEA NOT NULL,
181    fee_l1_user_tx BYTEA NOT NULL,
182    fee_add_token BYTEA NOT NULL,
183    tokens_hez BYTEA NOT NULL,
184    governance BYTEA NOT NULL
185);
186
187CREATE TABLE consensus_vars (
188    eth_block_num BIGINT PRIMARY KEY REFERENCES block (eth_block_num) ON DELETE CASCADE,
189    slot_deadline INT NOT NULL,
190    close_auction_slots INT NOT NULL,
191    open_auction_slots INT NOT NULL,
192    min_bid_slots VARCHAR(200) NOT NULL,
193    outbidding INT NOT NULL,
194    donation_address BYTEA NOT NULL,
195    governance_address BYTEA NOT NULL,
196    allocation_ratio VARCHAR(200)
197);
198
199-- +migrate Down
200DROP TABLE consensus_vars;
201DROP TABLE rollup_vars;
202DROP TABLE account;
203DROP TABLE tx;
204DROP TABLE token;
205DROP TABLE bid;
206DROP TABLE exit_tree;
207DROP TABLE batch;
208DROP TABLE coordinator;
209DROP TABLE block;