code.vegaprotocol.io/vega@v0.79.0/datanode/sqlstore/migrations/0001_initial.sql (about) 1 -- +goose Up 2 create extension if not exists timescaledb; 3 4 CREATE DOMAIN HUGEINT AS NUMERIC(1000, 0); 5 6 create table blocks 7 ( 8 vega_time TIMESTAMP WITH TIME ZONE NOT NULL PRIMARY KEY, 9 height BIGINT NOT NULL, 10 hash BYTEA NOT NULL 11 ); 12 select create_hypertable('blocks', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 13 create index on blocks (height); 14 15 create table last_block 16 ( 17 onerow_check bool PRIMARY KEY DEFAULT TRUE, 18 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 19 height BIGINT NOT NULL, 20 hash BYTEA NOT NULL 21 ); 22 23 -- +goose StatementBegin 24 CREATE OR REPLACE FUNCTION update_last_block() 25 RETURNS TRIGGER 26 LANGUAGE PLPGSQL AS 27 $$ 28 BEGIN 29 Insert into last_block (vega_time, height, hash) VALUES(NEW.vega_time, NEW.height, NEW.hash) on conflict(onerow_check) do update 30 set 31 vega_time=EXCLUDED.vega_time, 32 height=EXCLUDED.height, 33 hash=EXCLUDED.hash; 34 RETURN NULL; 35 END; 36 $$; 37 -- +goose StatementEnd 38 39 CREATE TRIGGER update_last_block AFTER INSERT ON blocks FOR EACH ROW EXECUTE function update_last_block(); 40 41 42 43 create table chain 44 ( 45 id TEXT NOT NULL, 46 onerow_check bool PRIMARY KEY DEFAULT TRUE 47 ); 48 49 create type asset_status_type as enum('STATUS_UNSPECIFIED', 'STATUS_PROPOSED', 'STATUS_REJECTED', 'STATUS_PENDING_LISTING', 'STATUS_ENABLED'); 50 51 create table assets 52 ( 53 id BYTEA NOT NULL, 54 name TEXT NOT NULL, 55 symbol TEXT NOT NULL, 56 decimals INT, 57 quantum HUGEINT, 58 source TEXT, 59 erc20_contract TEXT, 60 lifetime_limit HUGEINT NOT NULL, 61 withdraw_threshold HUGEINT NOT NULL, 62 status asset_status_type NOT NULL, 63 tx_hash BYTEA NOT NULL, 64 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 65 PRIMARY KEY (id, vega_time) 66 ); 67 68 CREATE VIEW assets_current AS ( 69 SELECT DISTINCT ON (id) * FROM assets ORDER BY id, vega_time DESC 70 ); 71 72 create table parties 73 ( 74 id BYTEA NOT NULL PRIMARY KEY, 75 tx_hash BYTEA NOT NULL, 76 vega_time TIMESTAMP WITH TIME ZONE 77 ); 78 79 create table accounts 80 ( 81 id BYTEA PRIMARY KEY, 82 party_id BYTEA, 83 asset_id BYTEA NOT NULL, 84 market_id BYTEA, 85 type INT, 86 tx_hash BYTEA NOT NULL, 87 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 88 UNIQUE(party_id, asset_id, market_id, type) 89 ); 90 91 create table balances 92 ( 93 account_id bytea NOT NULL, 94 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 95 tx_hash BYTEA NOT NULL, 96 balance HUGEINT NOT NULL, 97 PRIMARY KEY(vega_time, account_id) INCLUDE (balance) 98 ); 99 100 select create_hypertable('balances', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 101 create index on balances (account_id, vega_time) INCLUDE(balance); 102 103 create table current_balances 104 ( 105 account_id bytea NOT NULL, 106 tx_hash BYTEA NOT NULL, 107 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 108 balance HUGEINT NOT NULL, 109 110 PRIMARY KEY(account_id) 111 ); 112 113 -- +goose StatementBegin 114 CREATE OR REPLACE FUNCTION update_current_balances() 115 RETURNS TRIGGER 116 LANGUAGE PLPGSQL AS 117 $$ 118 BEGIN 119 INSERT INTO current_balances(account_id, tx_hash, vega_time, balance) VALUES(NEW.account_id, NEW.tx_hash, NEW.vega_time, NEW.balance) 120 ON CONFLICT(account_id) DO UPDATE SET 121 balance=EXCLUDED.balance, 122 tx_hash=EXCLUDED.tx_hash, 123 vega_time=EXCLUDED.vega_time; 124 RETURN NULL; 125 END; 126 $$; 127 -- +goose StatementEnd 128 129 CREATE TRIGGER update_current_balances AFTER INSERT ON balances FOR EACH ROW EXECUTE function update_current_balances(); 130 131 CREATE MATERIALIZED VIEW conflated_balances 132 WITH (timescaledb.continuous, timescaledb.materialized_only = true) AS 133 SELECT account_id, time_bucket('1 hour', vega_time) AS bucket, 134 last(balance, vega_time) AS balance, 135 last(tx_hash, vega_time) AS tx_hash, 136 last(vega_time, vega_time) AS vega_time 137 FROM balances 138 GROUP BY account_id, bucket WITH NO DATA; 139 140 -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures 141 -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day ) 142 SELECT add_continuous_aggregate_policy('conflated_balances', start_offset => INTERVAL '1 day', 143 end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour'); 144 145 CREATE VIEW all_balances AS 146 ( 147 SELECT 148 balances.account_id, 149 balances.tx_hash, 150 balances.vega_time, 151 balances.balance 152 FROM balances 153 UNION ALL 154 SELECT 155 conflated_balances.account_id, 156 conflated_balances.tx_hash, 157 conflated_balances.vega_time, 158 conflated_balances.balance 159 FROM conflated_balances 160 WHERE conflated_balances.vega_time < (SELECT coalesce(min(balances.vega_time), 'infinity') FROM balances)); 161 162 create table ledger 163 ( 164 ledger_entry_time TIMESTAMP WITH TIME ZONE NOT NULL, 165 account_from_id bytea NOT NULL, 166 account_to_id bytea NOT NULL, 167 quantity HUGEINT NOT NULL, 168 tx_hash BYTEA NOT NULL, 169 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 170 transfer_time TIMESTAMP WITH TIME ZONE NOT NULL, 171 account_from_balance HUGEINT NOT NULL, 172 account_to_balance HUGEINT NOT NULL, 173 type TEXT, 174 PRIMARY KEY(ledger_entry_time) 175 ); 176 SELECT create_hypertable('ledger', 'ledger_entry_time', chunk_time_interval => INTERVAL '1 day'); 177 178 CREATE INDEX ON ledger (account_from_id, vega_time DESC); 179 CREATE INDEX ON ledger (account_to_id, vega_time DESC); 180 CREATE INDEX ON ledger (type, vega_time DESC); 181 182 DROP TABLE IF EXISTS orders_history; 183 184 CREATE TABLE orders ( 185 id BYTEA NOT NULL, 186 market_id BYTEA NOT NULL, 187 party_id BYTEA NOT NULL, -- at some point add REFERENCES parties(id), 188 side SMALLINT NOT NULL, 189 price HUGEINT NOT NULL, 190 size BIGINT NOT NULL, 191 remaining BIGINT NOT NULL, 192 time_in_force SMALLINT NOT NULL, 193 type SMALLINT NOT NULL, 194 status SMALLINT NOT NULL, 195 reference TEXT, 196 reason SMALLINT, 197 version INT NOT NULL, 198 batch_id INT NOT NULL, 199 pegged_offset HUGEINT, 200 pegged_reference SMALLINT, 201 lp_id BYTEA, 202 created_at TIMESTAMP WITH TIME ZONE NOT NULL, 203 updated_at TIMESTAMP WITH TIME ZONE, 204 expires_at TIMESTAMP WITH TIME ZONE, 205 tx_hash BYTEA NOT NULL, 206 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 207 seq_num BIGINT NOT NULL, 208 post_only BOOLEAN NOT NULL DEFAULT FALSE, 209 reduce_only BOOLEAN NOT NULL DEFAULT FALSE, 210 PRIMARY KEY(vega_time, seq_num) 211 ); 212 213 SELECT create_hypertable('orders', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 214 215 create index on orders (id, vega_time desc, seq_num desc); 216 create index on orders (created_at desc, id, vega_time desc, seq_num desc); 217 create index on orders (market_id, created_at desc, id, vega_time desc, seq_num desc); 218 create index on orders (party_id, created_at desc, id, vega_time desc, seq_num desc); 219 create index on orders (reference, created_at desc, id, vega_time desc, seq_num desc); 220 221 CREATE TABLE orders_live ( 222 id BYTEA NOT NULL, 223 market_id BYTEA NOT NULL, 224 party_id BYTEA NOT NULL, -- at some point add REFERENCES parties(id), 225 side SMALLINT NOT NULL, 226 price HUGEINT NOT NULL, 227 size BIGINT NOT NULL, 228 remaining BIGINT NOT NULL, 229 time_in_force SMALLINT NOT NULL, 230 type SMALLINT NOT NULL, 231 status SMALLINT NOT NULL, 232 reference TEXT, 233 reason SMALLINT, 234 version INT NOT NULL, 235 batch_id INT NOT NULL, 236 pegged_offset HUGEINT, 237 pegged_reference SMALLINT, 238 lp_id BYTEA, 239 created_at TIMESTAMP WITH TIME ZONE NOT NULL, 240 updated_at TIMESTAMP WITH TIME ZONE, 241 expires_at TIMESTAMP WITH TIME ZONE, 242 tx_hash BYTEA NOT NULL, 243 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 244 seq_num BIGINT NOT NULL, -- event sequence number in the block 245 post_only BOOLEAN NOT NULL DEFAULT FALSE, 246 reduce_only BOOLEAN NOT NULL DEFAULT FALSE, 247 PRIMARY KEY(id) 248 ); 249 250 CREATE INDEX ON orders_live (market_id, vega_time DESC); 251 CREATE INDEX ON orders_live (party_id, vega_time DESC); 252 CREATE INDEX ON orders_live (reference, vega_time DESC); 253 CREATE INDEX ON orders_live USING HASH (id); 254 255 -- +goose StatementBegin 256 257 CREATE OR REPLACE FUNCTION archive_orders() 258 RETURNS TRIGGER 259 LANGUAGE PLPGSQL AS 260 $$ 261 BEGIN 262 263 DELETE from orders_live 264 WHERE id = NEW.id; 265 266 -- As per https://github.com/vegaprotocol/specs-internal/blob/master/protocol/0024-OSTA-order_status.md 267 -- we consider an order 'live' if it either ACTIVE (status=1) or PARKED (status=8). Orders 268 -- with statuses other than this are discarded by core, so we consider them candidates for 269 -- eventual deletion according to the data retention policy by placing them in orders_history. 270 -- As per https://github.com/vegaprotocol/vega/issues/8149, only LIMIT type (1) orders with status active (1) and parked (8) 271 -- and time_in_force != IOC (3) and time_in_force != FOK (4) are considered live. 272 IF NEW.status IN (1, 8) AND NEW.type = 1 AND NEW.time_in_force NOT IN (3, 4) 273 THEN 274 INSERT INTO orders_live 275 VALUES(new.id, new.market_id, new.party_id, new.side, new.price, 276 new.size, new.remaining, new.time_in_force, new.type, new.status, 277 new.reference, new.reason, new.version, new.batch_id, new.pegged_offset, 278 new.pegged_reference, new.lp_id, new.created_at, new.updated_at, new.expires_at, 279 new.tx_hash, new.vega_time, new.seq_num, new.post_only, new.reduce_only); 280 END IF; 281 282 RETURN NEW; 283 284 END; 285 $$; 286 287 -- +goose StatementEnd 288 289 CREATE TRIGGER archive_orders BEFORE INSERT ON orders FOR EACH ROW EXECUTE function archive_orders(); 290 291 -- Manual updates to the order (e.g. user changing price level) increment the 'version' 292 -- this view contains the current state of each *version* of the order (e.g. it is 293 -- unique on (order ID, version) 294 CREATE OR REPLACE VIEW orders_current_versions AS ( 295 SELECT DISTINCT ON (id, version) * FROM orders ORDER BY id, version DESC, vega_time DESC 296 ); 297 298 CREATE OR REPLACE VIEW orders_current_desc 299 AS 300 SELECT DISTINCT ON (orders.created_at, orders.id) * 301 FROM orders 302 ORDER BY orders.created_at DESC, orders.id, orders.vega_time DESC, orders.seq_num DESC; 303 304 CREATE OR REPLACE VIEW orders_current_desc_by_market 305 AS 306 SELECT DISTINCT ON (orders.created_at, orders.market_id, orders.id) * 307 FROM orders 308 ORDER BY orders.created_at DESC, orders.market_id, orders.id, orders.vega_time DESC, orders.seq_num DESC; 309 310 CREATE OR REPLACE VIEW orders_current_desc_by_party 311 AS 312 SELECT DISTINCT ON (orders.created_at, orders.party_id, orders.id) * 313 FROM orders 314 ORDER BY orders.created_at DESC, orders.party_id, orders.id, orders.vega_time DESC, orders.seq_num DESC; 315 316 CREATE OR REPLACE VIEW orders_current_desc_by_reference 317 AS 318 SELECT DISTINCT ON (orders.created_at, orders.reference, orders.id) * 319 FROM orders 320 ORDER BY orders.created_at DESC, orders.reference, orders.id, orders.vega_time DESC, orders.seq_num DESC; 321 322 create table trades 323 ( 324 synthetic_time TIMESTAMP WITH TIME ZONE NOT NULL, 325 tx_hash BYTEA NOT NULL, 326 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 327 seq_num BIGINT NOT NULL, 328 id BYTEA NOT NULL, 329 market_id BYTEA NOT NULL, 330 price HUGEINT NOT NULL, 331 size BIGINT NOT NULL, 332 buyer BYTEA NOT NULL, 333 seller BYTEA NOT NULL, 334 aggressor SMALLINT, 335 buy_order BYTEA NOT NULL, 336 sell_order BYTEA NOT NULL, 337 type SMALLINT NOT NULL, 338 buyer_maker_fee HUGEINT, 339 buyer_infrastructure_fee HUGEINT, 340 buyer_liquidity_fee HUGEINT, 341 seller_maker_fee HUGEINT, 342 seller_infrastructure_fee HUGEINT, 343 seller_liquidity_fee HUGEINT, 344 buyer_auction_batch BIGINT, 345 seller_auction_batch BIGINT, 346 primary key (synthetic_time) 347 ); 348 349 SELECT create_hypertable('trades', 'synthetic_time', chunk_time_interval => INTERVAL '1 day'); 350 CREATE INDEX ON trades (market_id, synthetic_time DESC); 351 CREATE INDEX ON trades(buyer, synthetic_time desc); 352 CREATE INDEX ON trades(seller, synthetic_time desc); 353 354 CREATE MATERIALIZED VIEW trades_candle_1_minute 355 WITH (timescaledb.continuous) AS 356 SELECT market_id, time_bucket('1 minute', synthetic_time) AS period_start, 357 first(price, synthetic_time) AS open, 358 last(price, synthetic_time) AS close, 359 max(price) AS high, 360 min(price) AS low, 361 sum(size) AS volume, 362 last(synthetic_time, 363 synthetic_time) AS last_update_in_period 364 FROM trades 365 GROUP BY market_id, period_start WITH NO DATA; 366 367 -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures 368 -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day ) 369 SELECT add_continuous_aggregate_policy('trades_candle_1_minute', start_offset => INTERVAL '1 day', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '1 minute'); 370 371 CREATE MATERIALIZED VIEW trades_candle_5_minutes 372 WITH (timescaledb.continuous) AS 373 SELECT market_id, time_bucket('5 minutes', synthetic_time) AS period_start, 374 first(price, synthetic_time) AS open, 375 last(price, synthetic_time) AS close, 376 max(price) AS high, 377 min(price) AS low, 378 sum(size) AS volume, 379 last(synthetic_time, 380 synthetic_time) AS last_update_in_period 381 FROM trades 382 GROUP BY market_id, period_start WITH NO DATA; 383 384 -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures 385 -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day ) 386 SELECT add_continuous_aggregate_policy('trades_candle_5_minutes', start_offset => INTERVAL '1 day', end_offset => INTERVAL '5 minutes', schedule_interval => INTERVAL '5 minutes'); 387 388 CREATE MATERIALIZED VIEW trades_candle_15_minutes 389 WITH (timescaledb.continuous) AS 390 SELECT market_id, time_bucket('15 minutes', synthetic_time) AS period_start, 391 first(price, synthetic_time) AS open, 392 last(price, synthetic_time) AS close, 393 max(price) AS high, 394 min(price) AS low, 395 sum(size) AS volume, 396 last(synthetic_time, 397 synthetic_time) AS last_update_in_period 398 FROM trades 399 GROUP BY market_id, period_start WITH NO DATA; 400 401 -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures 402 -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day ) 403 SELECT add_continuous_aggregate_policy('trades_candle_15_minutes', start_offset => INTERVAL '1 day', end_offset => INTERVAL '15 minutes', schedule_interval => INTERVAL '15 minutes'); 404 405 CREATE MATERIALIZED VIEW trades_candle_1_hour 406 WITH (timescaledb.continuous) AS 407 SELECT market_id, time_bucket('1 hour', synthetic_time) AS period_start, 408 first(price, synthetic_time) AS open, 409 last(price, synthetic_time) AS close, 410 max(price) AS high, 411 min(price) AS low, 412 sum(size) AS volume, 413 last(synthetic_time, 414 synthetic_time) AS last_update_in_period 415 FROM trades 416 GROUP BY market_id, period_start WITH NO DATA; 417 418 -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures 419 -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day ) 420 SELECT add_continuous_aggregate_policy('trades_candle_1_hour', start_offset => INTERVAL '1 day', end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour'); 421 422 CREATE MATERIALIZED VIEW trades_candle_6_hours 423 WITH (timescaledb.continuous) AS 424 SELECT market_id, time_bucket('6 hours', synthetic_time) AS period_start, 425 first(price, synthetic_time) AS open, 426 last(price, synthetic_time) AS close, 427 max(price) AS high, 428 min(price) AS low, 429 sum(size) AS volume, 430 last(synthetic_time, 431 synthetic_time) AS last_update_in_period 432 FROM trades 433 GROUP BY market_id, period_start WITH NO DATA; 434 435 -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures 436 -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day ) 437 SELECT add_continuous_aggregate_policy('trades_candle_6_hours', start_offset => INTERVAL '1 day', end_offset => INTERVAL '6 hours', schedule_interval => INTERVAL '6 hours'); 438 439 CREATE MATERIALIZED VIEW trades_candle_1_day 440 WITH (timescaledb.continuous) AS 441 SELECT market_id, time_bucket('1 day', synthetic_time) AS period_start, 442 first(price, synthetic_time) AS open, 443 last(price, synthetic_time) AS close, 444 max(price) AS high, 445 min(price) AS low, 446 sum(size) AS volume, 447 last(synthetic_time, 448 synthetic_time) AS last_update_in_period 449 FROM trades 450 GROUP BY market_id, period_start WITH NO DATA; 451 452 SELECT add_continuous_aggregate_policy('trades_candle_1_day', start_offset => INTERVAL '3 days', end_offset => INTERVAL '1 day', schedule_interval => INTERVAL '1 day'); 453 454 CREATE VIEW trades_candle_block AS 455 SELECT market_id, vega_time as period_start, 456 first(price, synthetic_time) AS open, 457 last(price, synthetic_time) AS close, 458 max(price) AS high, 459 min(price) AS low, 460 sum(size) AS volume, 461 last(synthetic_time, 462 synthetic_time) AS last_update_in_period 463 FROM trades 464 GROUP BY market_id, vega_time; 465 466 CREATE TABLE network_limits ( 467 vega_time TIMESTAMP WITH TIME ZONE NOT NULL PRIMARY KEY, 468 tx_hash BYTEA NOT NULL, 469 can_propose_market BOOLEAN NOT NULL, 470 can_propose_asset BOOLEAN NOT NULL, 471 propose_market_enabled BOOLEAN NOT NULL, 472 propose_asset_enabled BOOLEAN NOT NULL, 473 genesis_loaded BOOLEAN NOT NULL, 474 propose_market_enabled_from TIMESTAMP WITH TIME ZONE NOT NULL, 475 propose_asset_enabled_from TIMESTAMP WITH TIME ZONE NOT NULL 476 ); 477 478 -- Create a function that always returns the first non-NULL value: 479 CREATE OR REPLACE FUNCTION public.first_agg (anyelement, anyelement) 480 RETURNS anyelement 481 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS 482 'SELECT $1'; 483 484 -- Then wrap an aggregate around it: 485 CREATE AGGREGATE public.first (anyelement) ( 486 SFUNC = public.first_agg 487 , STYPE = anyelement 488 , PARALLEL = safe 489 ); 490 491 -- Create a function that always returns the last non-NULL value: 492 CREATE OR REPLACE FUNCTION public.last_agg (anyelement, anyelement) 493 RETURNS anyelement 494 LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS 495 'SELECT $2'; 496 497 -- Then wrap an aggregate around it: 498 CREATE AGGREGATE public.last (anyelement) ( 499 SFUNC = public.last_agg 500 , STYPE = anyelement 501 , PARALLEL = safe 502 ); 503 504 create type auction_trigger_type as enum('AUCTION_TRIGGER_UNSPECIFIED', 'AUCTION_TRIGGER_BATCH', 'AUCTION_TRIGGER_OPENING', 'AUCTION_TRIGGER_PRICE', 'AUCTION_TRIGGER_LIQUIDITY', 'AUCTION_TRIGGER_LIQUIDITY_TARGET_NOT_MET', 'AUCTION_TRIGGER_UNABLE_TO_DEPLOY_LP_ORDERS'); 505 create type market_trading_mode_type as enum('TRADING_MODE_UNSPECIFIED', 'TRADING_MODE_CONTINUOUS', 'TRADING_MODE_BATCH_AUCTION', 'TRADING_MODE_OPENING_AUCTION', 'TRADING_MODE_MONITORING_AUCTION', 'TRADING_MODE_NO_TRADING'); 506 create type market_state_type as enum('STATE_UNSPECIFIED', 'STATE_PROPOSED', 'STATE_REJECTED', 'STATE_PENDING', 'STATE_CANCELLED', 'STATE_ACTIVE', 'STATE_SUSPENDED', 'STATE_CLOSED', 'STATE_TRADING_TERMINATED', 'STATE_SETTLED'); 507 508 create table market_data ( 509 synthetic_time TIMESTAMP WITH TIME ZONE NOT NULL, 510 tx_hash BYTEA NOT NULL, 511 vega_time timestamp with time zone not null, 512 seq_num BIGINT NOT NULL, 513 market bytea not null, 514 mark_price HUGEINT, 515 best_bid_price HUGEINT, 516 best_bid_volume HUGEINT, 517 best_offer_price HUGEINT, 518 best_offer_volume HUGEINT, 519 best_static_bid_price HUGEINT, 520 best_static_bid_volume HUGEINT, 521 best_static_offer_price HUGEINT, 522 best_static_offer_volume HUGEINT, 523 mid_price HUGEINT, 524 static_mid_price HUGEINT, 525 open_interest HUGEINT, 526 auction_end bigint, 527 auction_start bigint, 528 indicative_price HUGEINT, 529 indicative_volume HUGEINT, 530 market_trading_mode market_trading_mode_type, 531 auction_trigger auction_trigger_type, 532 extension_trigger auction_trigger_type, 533 target_stake HUGEINT, 534 supplied_stake HUGEINT, 535 price_monitoring_bounds jsonb, 536 market_value_proxy text, 537 liquidity_provider_fee_shares jsonb, 538 market_state market_state_type, 539 next_mark_to_market timestamp with time zone, 540 PRIMARY KEY (synthetic_time) 541 ); 542 543 select create_hypertable('market_data', 'synthetic_time', chunk_time_interval => INTERVAL '1 day'); 544 545 create index on market_data (market, vega_time); 546 547 create table current_market_data 548 ( 549 synthetic_time TIMESTAMP WITH TIME ZONE NOT NULL, 550 tx_hash BYTEA NOT NULL, 551 vega_time timestamp with time zone not null, 552 seq_num BIGINT NOT NULL, 553 market bytea not null, 554 mark_price HUGEINT, 555 best_bid_price HUGEINT, 556 best_bid_volume HUGEINT, 557 best_offer_price HUGEINT, 558 best_offer_volume HUGEINT, 559 best_static_bid_price HUGEINT, 560 best_static_bid_volume HUGEINT, 561 best_static_offer_price HUGEINT, 562 best_static_offer_volume HUGEINT, 563 mid_price HUGEINT, 564 static_mid_price HUGEINT, 565 open_interest HUGEINT, 566 auction_end bigint, 567 auction_start bigint, 568 indicative_price HUGEINT, 569 indicative_volume HUGEINT, 570 market_trading_mode market_trading_mode_type, 571 auction_trigger auction_trigger_type, 572 extension_trigger auction_trigger_type, 573 target_stake HUGEINT, 574 supplied_stake HUGEINT, 575 price_monitoring_bounds jsonb, 576 market_value_proxy text, 577 liquidity_provider_fee_shares jsonb, 578 market_state market_state_type, 579 next_mark_to_market timestamp with time zone, 580 PRIMARY KEY (market) 581 ); 582 583 -- +goose StatementBegin 584 CREATE OR REPLACE FUNCTION update_current_market_data() 585 RETURNS TRIGGER 586 LANGUAGE PLPGSQL AS 587 $$ 588 BEGIN 589 INSERT INTO current_market_data(synthetic_time,tx_hash,vega_time,seq_num,market,mark_price,best_bid_price,best_bid_volume, 590 best_offer_price,best_offer_volume,best_static_bid_price,best_static_bid_volume, 591 best_static_offer_price,best_static_offer_volume,mid_price,static_mid_price,open_interest, 592 auction_end,auction_start,indicative_price,indicative_volume,market_trading_mode, 593 auction_trigger,extension_trigger,target_stake,supplied_stake,price_monitoring_bounds, 594 market_value_proxy,liquidity_provider_fee_shares,market_state,next_mark_to_market) 595 VALUES(NEW.synthetic_time, NEW.tx_hash, NEW.vega_time, NEW.seq_num, NEW.market, 596 NEW.mark_price, NEW.best_bid_price, NEW.best_bid_volume, NEW.best_offer_price, 597 NEW.best_offer_volume, NEW.best_static_bid_price, NEW.best_static_bid_volume, 598 NEW.best_static_offer_price, NEW.best_static_offer_volume, NEW.mid_price, 599 NEW.static_mid_price, NEW.open_interest, NEW.auction_end, NEW.auction_start, 600 NEW.indicative_price, NEW.indicative_volume, NEW.market_trading_mode, 601 NEW.auction_trigger, NEW.extension_trigger, NEW.target_stake, NEW.supplied_stake, 602 NEW.price_monitoring_bounds, NEW.market_value_proxy, 603 NEW.liquidity_provider_fee_shares, NEW.market_state, NEW.next_mark_to_market) 604 ON CONFLICT(market) DO UPDATE SET 605 synthetic_time=EXCLUDED.synthetic_time,tx_hash=EXCLUDED.tx_hash,vega_time=EXCLUDED.vega_time,seq_num=EXCLUDED.seq_num,market=EXCLUDED.market,mark_price=EXCLUDED.mark_price, 606 best_bid_price=EXCLUDED.best_bid_price,best_bid_volume=EXCLUDED.best_bid_volume,best_offer_price=EXCLUDED.best_offer_price,best_offer_volume=EXCLUDED.best_offer_volume, 607 best_static_bid_price=EXCLUDED.best_static_bid_price,best_static_bid_volume=EXCLUDED.best_static_bid_volume,best_static_offer_price=EXCLUDED.best_static_offer_price, 608 best_static_offer_volume=EXCLUDED.best_static_offer_volume,mid_price=EXCLUDED.mid_price,static_mid_price=EXCLUDED.static_mid_price,open_interest=EXCLUDED.open_interest, 609 auction_end=EXCLUDED.auction_end,auction_start=EXCLUDED.auction_start,indicative_price=EXCLUDED.indicative_price,indicative_volume=EXCLUDED.indicative_volume, 610 market_trading_mode=EXCLUDED.market_trading_mode,auction_trigger=EXCLUDED.auction_trigger,extension_trigger=EXCLUDED.extension_trigger,target_stake=EXCLUDED.target_stake, 611 supplied_stake=EXCLUDED.supplied_stake,price_monitoring_bounds=EXCLUDED.price_monitoring_bounds, 612 market_value_proxy=EXCLUDED.market_value_proxy,liquidity_provider_fee_shares=EXCLUDED.liquidity_provider_fee_shares,market_state=EXCLUDED.market_state, 613 next_mark_to_market=EXCLUDED.next_mark_to_market; 614 615 RETURN NULL; 616 END; 617 $$; 618 -- +goose StatementEnd 619 620 CREATE TRIGGER update_current_market_data AFTER INSERT ON market_data FOR EACH ROW EXECUTE function update_current_market_data(); 621 622 CREATE TYPE node_status as enum('NODE_STATUS_UNSPECIFIED', 'NODE_STATUS_VALIDATOR', 'NODE_STATUS_NON_VALIDATOR'); 623 624 CREATE TABLE IF NOT EXISTS nodes ( 625 id BYTEA NOT NULL, 626 vega_pub_key BYTEA NOT NULL, 627 tendermint_pub_key BYTEA NOT NULL, 628 ethereum_address BYTEA NOT NULL, 629 info_url TEXT NOT NULL, 630 location TEXT NOT NULL, 631 status node_status NOT NULL, 632 name TEXT NOT NULL, 633 avatar_url TEXT NOT NULL, 634 tx_hash BYTEA NOT NULL, 635 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 636 PRIMARY KEY(id) 637 ); 638 639 640 CREATE TABLE IF NOT EXISTS nodes_announced ( 641 node_id BYTEA NOT NULL, 642 epoch_seq BIGINT NOT NULL, 643 added BOOLEAN NOT NULL, 644 tx_hash BYTEA NOT NULL, 645 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 646 PRIMARY KEY(node_id, epoch_seq, vega_time) 647 ); 648 649 CREATE TYPE validator_node_status as enum( 650 'VALIDATOR_NODE_STATUS_UNSPECIFIED', 651 'VALIDATOR_NODE_STATUS_TENDERMINT', 652 'VALIDATOR_NODE_STATUS_ERSATZ', 653 'VALIDATOR_NODE_STATUS_PENDING' 654 ); 655 656 CREATE TABLE IF NOT EXISTS ranking_scores ( 657 node_id BYTEA NOT NULL REFERENCES nodes(id), 658 epoch_seq BIGINT NOT NULL, 659 660 stake_score NUMERIC NOT NULL, 661 performance_score NUMERIC NOT NULL, 662 ranking_score NUMERIC NOT NULL, 663 voting_power INT NOT NULL, 664 665 previous_status validator_node_status NOT NULL, 666 status validator_node_status NOT NULL, 667 668 tx_hash BYTEA NOT NULL, 669 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 670 671 PRIMARY KEY (node_id, epoch_seq) 672 ); 673 674 CREATE TABLE IF NOT EXISTS reward_scores ( 675 node_id BYTEA NOT NULL REFERENCES nodes(id), 676 epoch_seq BIGINT NOT NULL, 677 678 validator_node_status validator_node_status NOT NULL, 679 680 raw_validator_score NUMERIC NOT NULL, 681 performance_score NUMERIC NOT NULL, 682 multisig_score NUMERIC NOT NULL, 683 validator_score NUMERIC NOT NULL, 684 normalised_score NUMERIC NOT NULL, 685 686 tx_hash BYTEA NOT NULL, 687 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 688 689 PRIMARY KEY (node_id, epoch_seq) 690 ); 691 692 CREATE TABLE rewards( 693 party_id BYTEA NOT NULL, 694 asset_id BYTEA NOT NULL, 695 market_id BYTEA NOT NULL, 696 reward_type TEXT NOT NULL, 697 epoch_id BIGINT NOT NULL, 698 amount HUGEINT, 699 percent_of_total FLOAT, 700 timestamp TIMESTAMP WITH TIME ZONE NOT NULL, 701 tx_hash BYTEA NOT NULL, 702 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 703 seq_num BIGINT NOT NULL, 704 primary key (vega_time, seq_num) 705 ); 706 707 create index on rewards (party_id, asset_id); 708 create index on rewards (asset_id); 709 create index on rewards (epoch_id); 710 711 SELECT create_hypertable('rewards', 'vega_time', chunk_time_interval => INTERVAL '1 day', migrate_data => true); 712 713 CREATE TABLE delegations( 714 party_id BYTEA NOT NULL, 715 node_id BYTEA NOT NULL, 716 epoch_id BIGINT NOT NULL, 717 amount HUGEINT, 718 tx_hash BYTEA NOT NULL, 719 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 720 seq_num BIGINT NOT NULL, 721 PRIMARY KEY(vega_time, seq_num) 722 ); 723 724 select create_hypertable('delegations', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 725 create index on delegations (party_id, node_id, epoch_id); 726 727 728 create table delegations_current 729 ( 730 party_id BYTEA NOT NULL, 731 node_id BYTEA NOT NULL, 732 epoch_id BIGINT NOT NULL, 733 amount HUGEINT, 734 tx_hash BYTEA NOT NULL, 735 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 736 seq_num BIGINT NOT NULL, 737 primary key (party_id, node_id, epoch_id) 738 ); 739 740 create index on delegations_current(node_id, epoch_id); 741 create index on delegations_current(epoch_id); 742 743 -- +goose StatementBegin 744 CREATE OR REPLACE FUNCTION update_current_delegations() 745 RETURNS TRIGGER 746 LANGUAGE PLPGSQL AS 747 $$ 748 BEGIN 749 INSERT INTO delegations_current(party_id,node_id,epoch_id,amount,tx_hash,vega_time,seq_num) 750 VALUES(NEW.party_id,NEW.node_id,NEW.epoch_id,NEW.amount,NEW.tx_hash,NEW.vega_time,NEW.seq_num) 751 ON CONFLICT(party_id, node_id, epoch_id) DO UPDATE SET 752 amount=EXCLUDED.amount, 753 tx_hash=EXCLUDED.tx_hash, 754 vega_time=EXCLUDED.vega_time, 755 seq_num=EXCLUDED.seq_num; 756 RETURN NULL; 757 END; 758 $$; 759 -- +goose StatementEnd 760 761 CREATE TRIGGER update_current_delegations AFTER INSERT ON delegations FOR EACH ROW EXECUTE function update_current_delegations(); 762 763 764 CREATE TABLE IF NOT EXISTS markets ( 765 id BYTEA NOT NULL, 766 tx_hash BYTEA NOT NULL, 767 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 768 instrument_id TEXT, 769 tradable_instrument JSONB, 770 decimal_places INT, 771 fees JSONB, 772 opening_auction JSONB, 773 price_monitoring_settings JSONB, 774 liquidity_monitoring_parameters JSONB, 775 trading_mode market_trading_mode_type, 776 state market_state_type, 777 market_timestamps JSONB, 778 position_decimal_places INT, 779 lp_price_range TEXT, 780 quadratic_slippage_factor NUMERIC, 781 linear_slippage_factor NUMERIC, 782 PRIMARY KEY (id, vega_time) 783 ); 784 785 SELECT create_hypertable('markets', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 786 787 DROP VIEW IF EXISTS markets_current; 788 789 CREATE TABLE IF NOT EXISTS markets_current ( 790 id BYTEA NOT NULL, 791 tx_hash BYTEA NOT NULL, 792 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 793 instrument_id TEXT, 794 tradable_instrument JSONB, 795 decimal_places INT, 796 fees JSONB, 797 opening_auction JSONB, 798 price_monitoring_settings JSONB, 799 liquidity_monitoring_parameters JSONB, 800 trading_mode market_trading_mode_type, 801 state market_state_type, 802 market_timestamps JSONB, 803 position_decimal_places INT, 804 lp_price_range TEXT, 805 quadratic_slippage_factor NUMERIC, 806 linear_slippage_factor NUMERIC, 807 PRIMARY KEY (id) 808 ); 809 810 -- +goose StatementBegin 811 CREATE OR REPLACE FUNCTION update_current_markets() 812 RETURNS TRIGGER 813 LANGUAGE PLPGSQL AS 814 $$ 815 BEGIN 816 INSERT INTO markets_current(id,tx_hash,vega_time,instrument_id,tradable_instrument,decimal_places,fees,opening_auction,price_monitoring_settings,liquidity_monitoring_parameters,trading_mode,state,market_timestamps,position_decimal_places,lp_price_range, linear_slippage_factor, quadratic_slippage_factor) 817 VALUES(NEW.id,NEW.tx_hash,NEW.vega_time,NEW.instrument_id,NEW.tradable_instrument,NEW.decimal_places,NEW.fees,NEW.opening_auction,NEW.price_monitoring_settings,NEW.liquidity_monitoring_parameters,NEW.trading_mode,NEW.state,NEW.market_timestamps,NEW.position_decimal_places,NEW.lp_price_range, NEW.linear_slippage_factor, NEW.quadratic_slippage_factor) 818 ON CONFLICT(id) DO UPDATE SET 819 tx_hash=EXCLUDED.tx_hash, 820 instrument_id=EXCLUDED.instrument_id, 821 tradable_instrument=EXCLUDED.tradable_instrument, 822 decimal_places=EXCLUDED.decimal_places, 823 fees=EXCLUDED.fees, 824 opening_auction=EXCLUDED.opening_auction, 825 price_monitoring_settings=EXCLUDED.price_monitoring_settings, 826 liquidity_monitoring_parameters=EXCLUDED.liquidity_monitoring_parameters, 827 trading_mode=EXCLUDED.trading_mode, 828 state=EXCLUDED.state, 829 market_timestamps=EXCLUDED.market_timestamps, 830 position_decimal_places=EXCLUDED.position_decimal_places, 831 lp_price_range=EXCLUDED.lp_price_range, 832 linear_slippage_factor=EXCLUDED.linear_slippage_factor, 833 quadratic_slippage_factor=EXCLUDED.quadratic_slippage_factor, 834 vega_time=EXCLUDED.vega_time; 835 RETURN NULL; 836 END; 837 $$; 838 -- +goose StatementEnd 839 840 CREATE TRIGGER update_current_markets 841 AFTER INSERT OR UPDATE ON markets 842 FOR EACH ROW EXECUTE function update_current_markets(); 843 844 CREATE TABLE epochs( 845 id BIGINT NOT NULL, 846 start_time TIMESTAMP WITH TIME ZONE NOT NULL, 847 expire_time TIMESTAMP WITH TIME ZONE NOT NULL, 848 end_time TIMESTAMP WITH TIME ZONE, 849 tx_hash BYTEA NOT NULL, 850 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 851 PRIMARY KEY(id, vega_time) 852 ); 853 854 create type deposit_status as enum('STATUS_UNSPECIFIED', 'STATUS_OPEN', 'STATUS_CANCELLED', 'STATUS_FINALIZED'); 855 856 create table if not exists deposits ( 857 id bytea not null, 858 status deposit_status not null, 859 party_id bytea not null, 860 asset bytea not null, 861 amount HUGEINT, 862 foreign_tx_hash text not null, 863 credited_timestamp timestamp with time zone not null, 864 created_timestamp timestamp with time zone not null, 865 tx_hash bytea not null, 866 vega_time timestamp with time zone not null, 867 primary key (id, party_id, vega_time) 868 ); 869 CREATE INDEX ON deposits(party_id); 870 871 select create_hypertable('deposits', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 872 873 CREATE VIEW deposits_current AS ( 874 -- Assume that party_id is always the same for a given deposit ID to allow filter to be pushed down 875 SELECT DISTINCT ON (id, party_id) * FROM deposits ORDER BY id, party_id, vega_time DESC 876 ); 877 878 create type withdrawal_status as enum('STATUS_UNSPECIFIED', 'STATUS_OPEN', 'STATUS_REJECTED', 'STATUS_FINALIZED'); 879 880 create table if not exists withdrawals ( 881 id bytea not null, 882 party_id bytea not null, 883 amount numeric, 884 asset bytea not null, 885 status withdrawal_status not null, 886 ref text not null, 887 foreign_tx_hash text not null, 888 created_timestamp timestamp with time zone not null, 889 withdrawn_timestamp timestamp with time zone not null, 890 ext jsonb not null, 891 tx_hash bytea not null, 892 vega_time timestamp with time zone not null, 893 primary key (id, party_id, vega_time) 894 ); 895 896 CREATE INDEX ON withdrawals(party_id); 897 898 select create_hypertable('withdrawals', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 899 900 CREATE VIEW withdrawals_current AS ( 901 -- Assume that party_id is always the same for a given withdrawal ID to allow filter to be pushed down 902 SELECT DISTINCT ON (id, party_id) * FROM withdrawals ORDER BY id, party_id, vega_time DESC 903 ); 904 905 CREATE TYPE proposal_state AS enum('STATE_UNSPECIFIED', 'STATE_FAILED', 'STATE_OPEN', 'STATE_PASSED', 'STATE_REJECTED', 'STATE_DECLINED', 'STATE_ENACTED', 'STATE_WAITING_FOR_NODE_VOTE'); 906 CREATE TYPE proposal_error AS enum('PROPOSAL_ERROR_UNSPECIFIED', 'PROPOSAL_ERROR_CLOSE_TIME_TOO_SOON', 'PROPOSAL_ERROR_CLOSE_TIME_TOO_LATE', 'PROPOSAL_ERROR_ENACT_TIME_TOO_SOON', 'PROPOSAL_ERROR_ENACT_TIME_TOO_LATE', 'PROPOSAL_ERROR_INSUFFICIENT_TOKENS', 'PROPOSAL_ERROR_INVALID_INSTRUMENT_SECURITY', 'PROPOSAL_ERROR_NO_PRODUCT', 'PROPOSAL_ERROR_UNSUPPORTED_PRODUCT', 'PROPOSAL_ERROR_NO_TRADING_MODE', 'PROPOSAL_ERROR_UNSUPPORTED_TRADING_MODE', 'PROPOSAL_ERROR_NODE_VALIDATION_FAILED', 'PROPOSAL_ERROR_MISSING_BUILTIN_ASSET_FIELD', 'PROPOSAL_ERROR_MISSING_ERC20_CONTRACT_ADDRESS', 'PROPOSAL_ERROR_INVALID_ASSET', 'PROPOSAL_ERROR_INCOMPATIBLE_TIMESTAMPS', 'PROPOSAL_ERROR_NO_RISK_PARAMETERS', 'PROPOSAL_ERROR_NETWORK_PARAMETER_INVALID_KEY', 'PROPOSAL_ERROR_NETWORK_PARAMETER_INVALID_VALUE', 'PROPOSAL_ERROR_NETWORK_PARAMETER_VALIDATION_FAILED', 'PROPOSAL_ERROR_OPENING_AUCTION_DURATION_TOO_SMALL', 'PROPOSAL_ERROR_OPENING_AUCTION_DURATION_TOO_LARGE', 'PROPOSAL_ERROR_MARKET_MISSING_LIQUIDITY_COMMITMENT', 'PROPOSAL_ERROR_COULD_NOT_INSTANTIATE_MARKET', 'PROPOSAL_ERROR_INVALID_FUTURE_PRODUCT', 'PROPOSAL_ERROR_MISSING_COMMITMENT_AMOUNT', 'PROPOSAL_ERROR_INVALID_FEE_AMOUNT', 'PROPOSAL_ERROR_INVALID_SHAPE', 'PROPOSAL_ERROR_INVALID_RISK_PARAMETER', 'PROPOSAL_ERROR_MAJORITY_THRESHOLD_NOT_REACHED', 'PROPOSAL_ERROR_PARTICIPATION_THRESHOLD_NOT_REACHED', 'PROPOSAL_ERROR_INVALID_ASSET_DETAILS', 'PROPOSAL_ERROR_UNKNOWN_TYPE', 'PROPOSAL_ERROR_UNKNOWN_RISK_PARAMETER_TYPE', 'PROPOSAL_ERROR_INVALID_FREEFORM', 'PROPOSAL_ERROR_INSUFFICIENT_EQUITY_LIKE_SHARE', 'PROPOSAL_ERROR_INVALID_MARKET', 'PROPOSAL_ERROR_TOO_MANY_MARKET_DECIMAL_PLACES', 'PROPOSAL_ERROR_TOO_MANY_PRICE_MONITORING_TRIGGERS', 'PROPOSAL_ERROR_ERC20_ADDRESS_ALREADY_IN_USE'); 907 CREATE TYPE vote_value AS enum('VALUE_UNSPECIFIED', 'VALUE_NO', 'VALUE_YES'); 908 909 910 CREATE TABLE proposals( 911 id BYTEA NOT NULL, 912 reference TEXT NOT NULL, 913 party_id BYTEA NOT NULL, -- TODO, once parties is properly populated REFERENCES parties(id), 914 state proposal_state NOT NULL, 915 terms JSONB NOT NULL, 916 rationale JSONB NOT NULL, 917 reason proposal_error, 918 error_details TEXT, 919 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 920 proposal_time TIMESTAMP WITH TIME ZONE, 921 required_majority NUMERIC(1000, 16) NOT NULL, 922 required_participation NUMERIC(1000, 16) NOT NULL, 923 required_lp_majority NUMERIC(1000, 16), 924 required_lp_participation NUMERIC(1000, 16), 925 tx_hash BYTEA NOT NULL, 926 PRIMARY KEY (id, vega_time) 927 ); 928 929 CREATE VIEW proposals_current AS ( 930 SELECT DISTINCT ON (id) * FROM proposals ORDER BY id, vega_time DESC 931 ); 932 933 CREATE TABLE votes( 934 proposal_id BYTEA NOT NULL, -- TODO think about this REFERENCES proposals(id), 935 party_id BYTEA NOT NULL, -- TODO, once parties is properly populated REFERENCES parties(id), 936 value vote_value NOT NULL, 937 total_governance_token_balance HUGEINT NOT NULL, 938 total_governance_token_weight NUMERIC(1000, 16) NOT NULL, 939 total_equity_like_share_weight NUMERIC(1000, 16) NOT NULL, 940 tx_hash BYTEA NOT NULL, 941 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 942 initial_time TIMESTAMP WITH TIME ZONE, 943 PRIMARY KEY (proposal_id, party_id, vega_time) 944 ); 945 946 CREATE INDEX ON votes(party_id); 947 948 CREATE VIEW votes_current AS ( 949 SELECT DISTINCT ON (proposal_id, party_id) * FROM votes ORDER BY proposal_id, party_id, vega_time DESC 950 ); 951 952 create table if not exists margin_levels ( 953 account_id bytea NOT NULL, 954 timestamp timestamp with time zone not null, 955 maintenance_margin HUGEINT, 956 search_level HUGEINT, 957 initial_margin HUGEINT, 958 collateral_release_level HUGEINT, 959 tx_hash BYTEA NOT NULL, 960 vega_time timestamp with time zone not null, 961 PRIMARY KEY(vega_time, account_id) 962 ); 963 964 select create_hypertable('margin_levels', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 965 966 create table current_margin_levels 967 ( 968 account_id bytea NOT NULL, 969 timestamp timestamp with time zone not null, 970 maintenance_margin HUGEINT, 971 search_level HUGEINT, 972 initial_margin HUGEINT, 973 collateral_release_level HUGEINT, 974 tx_hash BYTEA NOT NULL, 975 vega_time timestamp with time zone not null, 976 977 PRIMARY KEY(account_id) 978 ); 979 980 -- +goose StatementBegin 981 CREATE OR REPLACE FUNCTION update_current_margin_levels() 982 RETURNS TRIGGER 983 LANGUAGE PLPGSQL AS 984 $$ 985 BEGIN 986 INSERT INTO current_margin_levels(account_id, 987 timestamp, 988 maintenance_margin, 989 search_level, 990 initial_margin, 991 collateral_release_level, 992 tx_hash, 993 vega_time) VALUES(NEW.account_id, 994 NEW.timestamp, 995 NEW.maintenance_margin, 996 NEW.search_level, 997 NEW.initial_margin, 998 NEW.collateral_release_level, 999 NEW.tx_hash, 1000 NEW.vega_time) 1001 ON CONFLICT(account_id) DO UPDATE SET 1002 timestamp=EXCLUDED.timestamp, 1003 maintenance_margin=EXCLUDED.maintenance_margin, 1004 search_level=EXCLUDED.search_level, 1005 initial_margin=EXCLUDED.initial_margin, 1006 collateral_release_level=EXCLUDED.collateral_release_level, 1007 tx_hash=EXCLUDED.tx_hash, 1008 vega_time=EXCLUDED.vega_time; 1009 RETURN NULL; 1010 END; 1011 $$; 1012 -- +goose StatementEnd 1013 1014 CREATE TRIGGER update_current_margin_levels AFTER INSERT ON margin_levels FOR EACH ROW EXECUTE function update_current_margin_levels(); 1015 1016 1017 1018 CREATE MATERIALIZED VIEW conflated_margin_levels 1019 WITH (timescaledb.continuous, timescaledb.materialized_only = true) AS 1020 SELECT account_id, time_bucket('1 minute', vega_time) AS bucket, 1021 last(maintenance_margin, vega_time) AS maintenance_margin, 1022 last(search_level, vega_time) AS search_level, 1023 last(initial_margin, vega_time) AS initial_margin, 1024 last(collateral_release_level, vega_time) AS collateral_release_level, 1025 last(timestamp, vega_time) AS timestamp, 1026 last(tx_hash, vega_time) AS tx_hash, 1027 last(vega_time, vega_time) AS vega_time 1028 FROM margin_levels 1029 GROUP BY account_id, bucket WITH NO DATA; 1030 1031 -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures 1032 -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day ) 1033 SELECT add_continuous_aggregate_policy('conflated_margin_levels', start_offset => INTERVAL '1 day', 1034 end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '1 minute'); 1035 1036 CREATE VIEW all_margin_levels AS 1037 ( 1038 SELECT margin_levels.account_id, 1039 margin_levels."timestamp", 1040 margin_levels.maintenance_margin, 1041 margin_levels.search_level, 1042 margin_levels.initial_margin, 1043 margin_levels.collateral_release_level, 1044 margin_levels.tx_hash, 1045 margin_levels.vega_time 1046 FROM margin_levels 1047 UNION ALL 1048 SELECT conflated_margin_levels.account_id, 1049 conflated_margin_levels."timestamp", 1050 conflated_margin_levels.maintenance_margin, 1051 conflated_margin_levels.search_level, 1052 conflated_margin_levels.initial_margin, 1053 conflated_margin_levels.collateral_release_level, 1054 conflated_margin_levels.tx_hash, 1055 conflated_margin_levels.vega_time 1056 FROM conflated_margin_levels 1057 WHERE conflated_margin_levels.vega_time < (SELECT coalesce(min(margin_levels.vega_time), 'infinity') FROM margin_levels)); 1058 1059 create table if not exists risk_factors ( 1060 market_id bytea not null, 1061 short NUMERIC(1000, 16) not null, 1062 long NUMERIC(1000, 16) not null, 1063 tx_hash bytea not null, 1064 vega_time timestamp with time zone not null, 1065 primary key (market_id, vega_time) 1066 ); 1067 1068 CREATE VIEW risk_factors_current AS ( 1069 SELECT DISTINCT ON (market_id) * FROM risk_factors ORDER BY market_id, vega_time DESC 1070 ); 1071 1072 CREATE TABLE network_parameters ( 1073 key TEXT NOT NULL, 1074 value TEXT NOT NULL, 1075 tx_hash BYTEA NOT NULL, 1076 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 1077 PRIMARY KEY (key, vega_time) 1078 ); 1079 1080 drop view if exists network_parameters_current; 1081 1082 CREATE TABLE network_parameters_current ( 1083 key TEXT NOT NULL, 1084 value TEXT NOT NULL, 1085 tx_hash BYTEA NOT NULL, 1086 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 1087 PRIMARY KEY (key) 1088 ); 1089 1090 -- +goose StatementBegin 1091 CREATE OR REPLACE FUNCTION update_current_network_parameters() 1092 RETURNS TRIGGER 1093 LANGUAGE PLPGSQL AS 1094 $$ 1095 BEGIN 1096 INSERT INTO network_parameters_current(key, value, tx_hash, vega_time) 1097 VALUES(NEW.key, NEW.value, NEW.tx_hash, NEW.vega_time) 1098 ON CONFLICT(key) DO UPDATE SET value=EXCLUDED.value, tx_hash=EXCLUDED.tx_hash, vega_time=EXCLUDED.vega_time; 1099 RETURN NULL; 1100 END; 1101 $$; 1102 -- +goose StatementEnd 1103 1104 CREATE TRIGGER update_current_network_parameters AFTER INSERT OR UPDATE ON network_parameters 1105 FOR EACH ROW EXECUTE function update_current_network_parameters(); 1106 1107 CREATE TABLE checkpoints( 1108 hash TEXT NOT NULL, 1109 block_hash TEXT NOT NULL, 1110 block_height BIGINT NOT NULL, 1111 tx_hash BYTEA NOT NULL, 1112 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 1113 seq_num BIGINT NOT NULL, 1114 PRIMARY KEY(vega_time, seq_num) 1115 ); 1116 1117 SELECT create_hypertable('checkpoints', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 1118 1119 CREATE TYPE position_status_type AS enum('POSITION_STATUS_UNSPECIFIED', 'POSITION_STATUS_ORDERS_CLOSED', 'POSITION_STATUS_CLOSED_OUT', 'POSITION_STATUS_DISTRESSED'); 1120 1121 CREATE TABLE positions( 1122 market_id BYTEA NOT NULL, 1123 party_id BYTEA NOT NULL, 1124 open_volume BIGINT NOT NULL, 1125 realised_pnl NUMERIC NOT NULL, 1126 unrealised_pnl NUMERIC NOT NULL, 1127 average_entry_price NUMERIC NOT NULL, 1128 average_entry_market_price NUMERIC NOT NULL, 1129 loss NUMERIC NOT NULL, 1130 adjustment NUMERIC NOT NULL, 1131 tx_hash BYTEA NOT NULL, 1132 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 1133 pending_open_volume BIGINT NOT NULL, 1134 pending_realised_pnl NUMERIC NOT NULL, 1135 pending_unrealised_pnl NUMERIC NOT NULL, 1136 pending_average_entry_price NUMERIC NOT NULL, 1137 pending_average_entry_market_price NUMERIC NOT NULL, 1138 loss_socialisation_amount NUMERIC NOT NULL, 1139 distressed_status position_status_type NOT NULL, 1140 primary key (vega_time, party_id, market_id) 1141 ); 1142 1143 select create_hypertable('positions', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 1144 1145 CREATE INDEX ON positions(party_id, market_id, vega_time); 1146 1147 CREATE MATERIALIZED VIEW conflated_positions 1148 WITH (timescaledb.continuous, timescaledb.materialized_only = true) AS 1149 SELECT market_id, party_id, time_bucket('1 hour', vega_time) AS bucket, 1150 last(open_volume, vega_time) AS open_volume, 1151 last(realised_pnl, vega_time) AS realised_pnl, 1152 last(unrealised_pnl, vega_time) AS unrealised_pnl, 1153 last(average_entry_price, vega_time) AS average_entry_price, 1154 last(average_entry_market_price, vega_time) AS average_entry_market_price, 1155 last(loss, vega_time) AS loss, 1156 last(adjustment, vega_time) AS adjustment, 1157 last(tx_hash, vega_time) AS tx_hash, 1158 last(vega_time, vega_time) AS vega_time 1159 FROM positions 1160 GROUP BY market_id, party_id, bucket WITH NO DATA; 1161 1162 -- start_offset is set to a day, as data is append only this does not impact the processing time and ensures 1163 -- that the CAGG data will be correct on recovery in the event of a transient outage ( < 1 day ) 1164 SELECT add_continuous_aggregate_policy('conflated_positions', start_offset => INTERVAL '1 day', 1165 end_offset => INTERVAL '1 hour', schedule_interval => INTERVAL '1 hour'); 1166 1167 CREATE VIEW all_positions AS 1168 ( 1169 SELECT 1170 positions.market_id, 1171 positions.party_id, 1172 positions.open_volume, 1173 positions.realised_pnl, 1174 positions.unrealised_pnl, 1175 positions.average_entry_price, 1176 positions.average_entry_market_price, 1177 positions.loss, 1178 positions.adjustment, 1179 positions.tx_hash, 1180 positions.vega_time 1181 FROM positions 1182 UNION ALL 1183 SELECT 1184 conflated_positions.market_id, 1185 conflated_positions.party_id, 1186 conflated_positions.open_volume, 1187 conflated_positions.realised_pnl, 1188 conflated_positions.unrealised_pnl, 1189 conflated_positions.average_entry_price, 1190 conflated_positions.average_entry_market_price, 1191 conflated_positions.loss, 1192 conflated_positions.adjustment, 1193 conflated_positions.tx_hash, 1194 conflated_positions.vega_time 1195 FROM conflated_positions 1196 WHERE conflated_positions.vega_time < (SELECT coalesce(min(positions.vega_time), 'infinity') FROM positions)); 1197 1198 drop view if exists positions_current; 1199 1200 create table positions_current 1201 ( 1202 market_id BYTEA NOT NULL, 1203 party_id BYTEA NOT NULL, 1204 open_volume BIGINT NOT NULL, 1205 realised_pnl NUMERIC NOT NULL, 1206 unrealised_pnl NUMERIC NOT NULL, 1207 average_entry_price NUMERIC NOT NULL, 1208 average_entry_market_price NUMERIC NOT NULL, 1209 loss NUMERIC NOT NULL, 1210 adjustment NUMERIC NOT NULL, 1211 tx_hash BYTEA NOT NULL, 1212 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 1213 pending_open_volume BIGINT NOT NULL, 1214 pending_realised_pnl NUMERIC NOT NULL, 1215 pending_unrealised_pnl NUMERIC NOT NULL, 1216 pending_average_entry_price NUMERIC NOT NULL, 1217 pending_average_entry_market_price NUMERIC NOT NULL, 1218 loss_socialisation_amount NUMERIC NOT NULL, 1219 distressed_status position_status_type NOT NULL, 1220 primary key (party_id, market_id) 1221 ); 1222 1223 CREATE INDEX ON positions_current(market_id); 1224 1225 -- +goose StatementBegin 1226 CREATE OR REPLACE FUNCTION update_current_positions() 1227 RETURNS TRIGGER 1228 LANGUAGE PLPGSQL AS 1229 $$ 1230 BEGIN 1231 INSERT INTO positions_current(market_id,party_id,open_volume,realised_pnl,unrealised_pnl,average_entry_price,average_entry_market_price,loss,adjustment,tx_hash,vega_time,pending_open_volume,pending_realised_pnl,pending_unrealised_pnl,pending_average_entry_price,pending_average_entry_market_price, loss_socialisation_amount, distressed_status) 1232 VALUES(NEW.market_id,NEW.party_id,NEW.open_volume,NEW.realised_pnl,NEW.unrealised_pnl,NEW.average_entry_price,NEW.average_entry_market_price,NEW.loss,NEW.adjustment,NEW.tx_hash,NEW.vega_time,NEW.pending_open_volume,NEW.pending_realised_pnl,NEW.pending_unrealised_pnl,NEW.pending_average_entry_price,NEW.pending_average_entry_market_price, NEW.loss_socialisation_amount, NEW.distressed_status) 1233 ON CONFLICT(party_id, market_id) DO UPDATE SET 1234 open_volume=EXCLUDED.open_volume, 1235 realised_pnl=EXCLUDED.realised_pnl, 1236 unrealised_pnl=EXCLUDED.unrealised_pnl, 1237 average_entry_price=EXCLUDED.average_entry_price, 1238 average_entry_market_price=EXCLUDED.average_entry_market_price, 1239 loss=EXCLUDED.loss, 1240 adjustment=EXCLUDED.adjustment, 1241 tx_hash=EXCLUDED.tx_hash, 1242 vega_time=EXCLUDED.vega_time, 1243 pending_open_volume=EXCLUDED.pending_open_volume, 1244 pending_realised_pnl=EXCLUDED.pending_realised_pnl, 1245 pending_unrealised_pnl=EXCLUDED.pending_unrealised_pnl, 1246 pending_average_entry_price=EXCLUDED.pending_average_entry_price, 1247 pending_average_entry_market_price=EXCLUDED.pending_average_entry_market_price, 1248 loss_socialisation_amount=EXCLUDED.loss_socialisation_amount, 1249 distressed_status=EXCLUDED.distressed_status; 1250 RETURN NULL; 1251 END; 1252 $$; 1253 -- +goose StatementEnd 1254 1255 CREATE TRIGGER update_current_positions AFTER INSERT ON positions FOR EACH ROW EXECUTE function update_current_positions(); 1256 1257 create type oracle_spec_status as enum('STATUS_UNSPECIFIED', 'STATUS_ACTIVE', 'STATUS_DEACTIVATED'); 1258 1259 create table if not exists oracle_specs ( 1260 id bytea not null, 1261 created_at timestamp with time zone not null, 1262 updated_at timestamp with time zone not null, 1263 signers bytea[], 1264 filters jsonb, 1265 status oracle_spec_status not null, 1266 tx_hash bytea not null, 1267 vega_time timestamp with time zone not null, 1268 primary key (id, vega_time) 1269 ); 1270 1271 create table if not exists oracle_data ( 1272 signers bytea[], 1273 data jsonb not null, 1274 matched_spec_ids bytea[], 1275 broadcast_at timestamp with time zone not null, 1276 tx_hash bytea not null, 1277 vega_time timestamp with time zone not null, 1278 seq_num BIGINT NOT NULL, 1279 PRIMARY KEY(vega_time, seq_num) 1280 ); 1281 1282 create index if not exists idx_oracle_data_matched_spec_ids on oracle_data(matched_spec_ids); 1283 1284 drop view if exists oracle_data_current; 1285 1286 create table if not exists oracle_data_current ( 1287 signers bytea[], 1288 data jsonb not null, 1289 matched_spec_ids bytea[], 1290 broadcast_at timestamp with time zone not null, 1291 tx_hash bytea not null, 1292 vega_time timestamp with time zone not null, 1293 seq_num BIGINT NOT NULL, 1294 PRIMARY KEY(matched_spec_ids, data) 1295 ); 1296 1297 -- +goose StatementBegin 1298 1299 CREATE OR REPLACE FUNCTION update_current_oracle_data() 1300 RETURNS TRIGGER 1301 LANGUAGE PLPGSQL AS 1302 $$ 1303 BEGIN 1304 INSERT INTO oracle_data_current(signers,data,matched_spec_ids,broadcast_at,tx_hash,vega_time,seq_num) 1305 VALUES(NEW.signers,NEW.data,NEW.matched_spec_ids,NEW.broadcast_at,NEW.tx_hash,NEW.vega_time,NEW.seq_num) 1306 ON CONFLICT(matched_spec_ids, data) DO UPDATE SET 1307 signers=EXCLUDED.signers, 1308 broadcast_at=EXCLUDED.broadcast_at, 1309 tx_hash=EXCLUDED.tx_hash, 1310 vega_time=EXCLUDED.vega_time, 1311 seq_num=EXCLUDED.seq_num; 1312 RETURN NULL; 1313 END; 1314 $$; 1315 -- +goose StatementEnd 1316 1317 CREATE TRIGGER update_current_oracle_data AFTER INSERT ON oracle_data FOR EACH ROW EXECUTE function update_current_oracle_data(); 1318 1319 create type liquidity_provision_status as enum('STATUS_UNSPECIFIED', 'STATUS_ACTIVE', 'STATUS_STOPPED', 1320 'STATUS_CANCELLED', 'STATUS_REJECTED', 'STATUS_UNDEPLOYED', 'STATUS_PENDING'); 1321 1322 create table if not exists liquidity_provisions ( 1323 id bytea not null, 1324 party_id bytea, 1325 created_at timestamp with time zone not null, 1326 updated_at timestamp with time zone not null, 1327 market_id bytea, 1328 commitment_amount HUGEINT, 1329 fee NUMERIC(1000, 16), 1330 sells jsonb, 1331 buys jsonb, 1332 version bigint, 1333 status liquidity_provision_status not null, 1334 reference text, 1335 tx_hash bytea not null, 1336 vega_time timestamp with time zone not null, 1337 primary key (id, vega_time) 1338 ); 1339 1340 select create_hypertable('liquidity_provisions', 'vega_time', chunk_time_interval => INTERVAL '1 day'); 1341 1342 CREATE TABLE live_liquidity_provisions 1343 ( 1344 id BYTEA NOT NULL, 1345 party_id BYTEA, 1346 created_at TIMESTAMP WITH TIME ZONE NOT NULL, 1347 updated_at TIMESTAMP WITH TIME ZONE NOT NULL, 1348 market_id BYTEA, 1349 commitment_amount HUGEINT, 1350 fee NUMERIC(1000, 16), 1351 sells jsonb, 1352 buys jsonb, 1353 version BIGINT, 1354 status liquidity_provision_status NOT NULL, 1355 reference TEXT, 1356 tx_hash BYTEA NOT NULL, 1357 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 1358 PRIMARY KEY (id, vega_time) 1359 ); 1360 1361 -- +goose StatementBegin 1362 CREATE OR REPLACE FUNCTION update_live_liquidity_provisions() 1363 RETURNS TRIGGER 1364 LANGUAGE PLPGSQL AS 1365 $$ 1366 BEGIN 1367 1368 DELETE FROM live_liquidity_provisions 1369 WHERE id = NEW.id; 1370 1371 -- We take into consideration Liquidity provisions with statuses: 1372 -- Active (1), Undeployed (5), Pending (6) 1373 IF NEW.status IN('STATUS_ACTIVE', 'STATUS_UNDEPLOYED', 'STATUS_PENDING') 1374 THEN 1375 INSERT INTO live_liquidity_provisions(id, party_id, created_at, updated_at, 1376 market_id, commitment_amount, fee, sells, buys, version, status, reference, tx_hash, vega_time) 1377 VALUES(NEW.id, NEW.party_id, NEW.created_at, NEW.updated_at, 1378 NEW.market_id, NEW.commitment_amount, NEW.fee, NEW.sells, 1379 NEW.buys, NEW.version, NEW.status, NEW.reference, NEW.tx_hash, NEW.vega_time); 1380 END IF; 1381 1382 RETURN NEW; 1383 END; 1384 $$; 1385 -- +goose StatementEnd 1386 1387 CREATE TRIGGER update_live_liquidity_provisions AFTER INSERT ON liquidity_provisions 1388 FOR EACH ROW EXECUTE FUNCTION update_live_liquidity_provisions(); 1389 1390 1391 CREATE TYPE transfer_type AS enum('OneOff','Recurring','Unknown'); 1392 CREATE TYPE transfer_status AS enum('STATUS_UNSPECIFIED','STATUS_PENDING','STATUS_DONE','STATUS_REJECTED','STATUS_STOPPED','STATUS_CANCELLED'); 1393 1394 create table if not exists transfers ( 1395 id bytea not null, 1396 tx_hash bytea not null, 1397 vega_time timestamp with time zone not null, 1398 from_account_id bytea NOT NULL REFERENCES accounts(id), 1399 to_account_id bytea NOT NULL REFERENCES accounts(id), 1400 asset_id bytea not null, 1401 amount HUGEINT NOT NULL, 1402 reference TEXT, 1403 status transfer_status NOT NULL, 1404 transfer_type transfer_type NOT NULL, 1405 deliver_on TIMESTAMP WITH TIME ZONE, 1406 start_epoch BIGINT, 1407 end_epoch BIGINT, 1408 factor NUMERIC(1000, 16) , 1409 dispatch_metric INT, 1410 dispatch_metric_asset TEXT, 1411 dispatch_markets TEXT[], 1412 reason TEXT, 1413 primary key (id, vega_time) 1414 ); 1415 1416 create index on transfers (from_account_id); 1417 create index on transfers (to_account_id); 1418 1419 -- Assume that from/to account is never changed for a given xfer id 1420 CREATE VIEW transfers_current AS ( SELECT DISTINCT ON (id, from_account_id, to_account_id) * FROM transfers ORDER BY id, from_account_id, to_account_id, vega_time DESC); 1421 1422 create table if not exists key_rotations ( 1423 node_id bytea not null references nodes(id), 1424 old_pub_key bytea not null, 1425 new_pub_key bytea not null, 1426 block_height bigint not null, 1427 tx_hash bytea not null, 1428 vega_time timestamp with time zone not null, 1429 1430 primary key (node_id, vega_time) 1431 ); 1432 1433 create table if not exists ethereum_key_rotations ( 1434 node_id bytea not null references nodes(id), 1435 old_address bytea not null, 1436 new_address bytea not null, 1437 block_height bigint not null, 1438 tx_hash bytea not null, 1439 vega_time timestamp with time zone not null, 1440 seq_num BIGINT NOT NULL, 1441 primary key (seq_num, vega_time) 1442 ); 1443 1444 create type erc20_multisig_signer_event as enum('SIGNER_ADDED', 'SIGNER_REMOVED'); 1445 1446 create table if not exists erc20_multisig_signer_events( 1447 id bytea not null, 1448 validator_id bytea not null, 1449 signer_change bytea not null, 1450 submitter bytea not null, 1451 nonce text not null, 1452 event erc20_multisig_signer_event not null, 1453 tx_hash bytea not null, 1454 vega_time timestamp with time zone, 1455 epoch_id bigint not null, 1456 primary key (id) 1457 ); 1458 1459 create type stake_linking_type as enum('TYPE_UNSPECIFIED', 'TYPE_LINK', 'TYPE_UNLINK'); 1460 create type stake_linking_status as enum('STATUS_UNSPECIFIED', 'STATUS_PENDING', 'STATUS_ACCEPTED', 'STATUS_REJECTED'); 1461 1462 create table if not exists stake_linking( 1463 id bytea not null, 1464 stake_linking_type stake_linking_type not null, 1465 ethereum_timestamp timestamp with time zone not null, 1466 party_id bytea not null, 1467 amount HUGEINT, 1468 stake_linking_status stake_linking_status not null, 1469 finalized_at timestamp with time zone, 1470 foreign_tx_hash text not null, 1471 foreign_block_height bigint, 1472 foreign_block_time bigint, 1473 log_index bigint, 1474 ethereum_address text not null, 1475 tx_hash bytea not null, 1476 vega_time timestamp with time zone not null, 1477 primary key (id, vega_time) 1478 ); 1479 1480 drop view if exists stake_linking_current; 1481 1482 create table if not exists stake_linking_current( 1483 id bytea not null, 1484 stake_linking_type stake_linking_type not null, 1485 ethereum_timestamp timestamp with time zone not null, 1486 party_id bytea not null, 1487 amount HUGEINT, 1488 stake_linking_status stake_linking_status not null, 1489 finalized_at timestamp with time zone, 1490 foreign_tx_hash text not null, 1491 foreign_block_height bigint, 1492 foreign_block_time bigint, 1493 log_index bigint, 1494 ethereum_address text not null, 1495 tx_hash bytea not null, 1496 vega_time timestamp with time zone not null, 1497 primary key (id) 1498 ); 1499 1500 create index on stake_linking_current(party_id); 1501 1502 -- +goose StatementBegin 1503 CREATE OR REPLACE FUNCTION update_current_stake_linking() 1504 RETURNS TRIGGER 1505 LANGUAGE plpgsql AS 1506 $$ 1507 BEGIN 1508 INSERT INTO stake_linking_current (id, stake_linking_type, ethereum_timestamp, party_id, amount, stake_linking_status, finalized_at, foreign_tx_hash, foreign_block_height, foreign_block_time, log_index, ethereum_address, tx_hash, vega_time) 1509 VALUES (NEW.id, 1510 NEW.stake_linking_type, 1511 NEW.ethereum_timestamp, 1512 NEW.party_id, 1513 NEW.amount, 1514 NEW.stake_linking_status, 1515 NEW.finalized_at, 1516 NEW.foreign_tx_hash, 1517 NEW.foreign_block_height, 1518 NEW.foreign_block_time, 1519 NEW.log_index, 1520 NEW.ethereum_address, 1521 NEW.tx_hash, 1522 NEW.vega_time) 1523 ON CONFLICT(id) DO UPDATE SET 1524 stake_linking_type=EXCLUDED.stake_linking_type, 1525 ethereum_timestamp=EXCLUDED.ethereum_timestamp, 1526 party_id=EXCLUDED.party_id, 1527 amount=EXCLUDED.amount, 1528 stake_linking_status=EXCLUDED.stake_linking_status, 1529 finalized_at=EXCLUDED.finalized_at, 1530 foreign_tx_hash=EXCLUDED.foreign_tx_hash, 1531 foreign_block_height=EXCLUDED.foreign_block_height, 1532 foreign_block_time=EXCLUDED.foreign_block_time, 1533 log_index=EXCLUDED.log_index, 1534 ethereum_address=EXCLUDED.ethereum_address, 1535 tx_hash=EXCLUDED.tx_hash, 1536 vega_time=EXCLUDED.vega_time; 1537 RETURN NULL; 1538 END; 1539 $$; 1540 -- +goose StatementEnd 1541 1542 create trigger update_current_stake_linking 1543 after insert or update on stake_linking 1544 for each row execute procedure update_current_stake_linking(); 1545 1546 create type node_signature_kind as enum('NODE_SIGNATURE_KIND_UNSPECIFIED', 'NODE_SIGNATURE_KIND_ASSET_NEW', 'NODE_SIGNATURE_KIND_ASSET_WITHDRAWAL', 'NODE_SIGNATURE_KIND_ERC20_MULTISIG_SIGNER_ADDED', 'NODE_SIGNATURE_KIND_ERC20_MULTISIG_SIGNER_REMOVED', 'NODE_SIGNATURE_KIND_ASSET_UPDATE'); 1547 1548 create table if not exists node_signatures( 1549 resource_id bytea not null, 1550 sig bytea not null, 1551 kind node_signature_kind, 1552 tx_hash bytea not null, 1553 vega_time timestamp with time zone not null, 1554 primary key (resource_id, sig) 1555 ); 1556 1557 CREATE TYPE protocol_upgrade_proposal_status AS enum( 1558 'PROTOCOL_UPGRADE_PROPOSAL_STATUS_UNSPECIFIED', 1559 'PROTOCOL_UPGRADE_PROPOSAL_STATUS_PENDING', 1560 'PROTOCOL_UPGRADE_PROPOSAL_STATUS_APPROVED', 1561 'PROTOCOL_UPGRADE_PROPOSAL_STATUS_REJECTED'); 1562 1563 CREATE TABLE IF NOT EXISTS protocol_upgrade_proposals( 1564 upgrade_block_height BIGINT NOT NULL, 1565 vega_release_tag TEXT NOT NULL, 1566 approvers TEXT[] NOT NULL, 1567 status protocol_upgrade_proposal_status NOT NULL, 1568 tx_hash bytea not null, 1569 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 1570 PRIMARY KEY(vega_time, upgrade_block_height, vega_release_tag) 1571 ); 1572 1573 CREATE VIEW protocol_upgrade_proposals_current AS ( 1574 SELECT DISTINCT ON (upgrade_block_height, vega_release_tag) * 1575 FROM protocol_upgrade_proposals 1576 ORDER BY upgrade_block_height, vega_release_tag, vega_time DESC); 1577 1578 CREATE TABLE IF NOT EXISTS core_snapshots( 1579 block_height BIGINT NOT NULL, 1580 block_hash TEXT null, 1581 vega_core_version TEXT null, 1582 tx_hash bytea not null, 1583 vega_time TIMESTAMP WITH TIME ZONE NOT NULL, 1584 PRIMARY KEY(vega_time, block_height) 1585 ); 1586 1587 create table last_snapshot_span 1588 ( 1589 onerow_check bool PRIMARY KEY DEFAULT TRUE, 1590 from_height BIGINT NOT NULL, 1591 to_height BIGINT NOT NULL 1592 ); 1593 1594 CREATE INDEX ON accounts (tx_hash); 1595 CREATE INDEX ON assets (tx_hash); 1596 CREATE INDEX ON balances (tx_hash); 1597 CREATE INDEX ON delegations (tx_hash); 1598 CREATE INDEX ON deposits (tx_hash); 1599 CREATE INDEX ON erc20_multisig_signer_events (tx_hash); 1600 CREATE INDEX ON ethereum_key_rotations (tx_hash); 1601 CREATE INDEX ON key_rotations (tx_hash); 1602 CREATE INDEX ON ledger (tx_hash); 1603 CREATE INDEX ON liquidity_provisions (tx_hash); 1604 CREATE INDEX ON margin_levels (tx_hash); 1605 CREATE INDEX ON markets (tx_hash); 1606 CREATE INDEX ON network_parameters (tx_hash); 1607 CREATE INDEX ON node_signatures (tx_hash); 1608 CREATE INDEX ON nodes (tx_hash); 1609 CREATE INDEX ON oracle_data (tx_hash); 1610 CREATE INDEX ON oracle_specs (tx_hash); 1611 CREATE INDEX ON orders (tx_hash); 1612 CREATE INDEX ON parties (tx_hash); 1613 CREATE INDEX ON positions (tx_hash); 1614 CREATE INDEX ON proposals (tx_hash); 1615 CREATE INDEX ON protocol_upgrade_proposals (tx_hash); 1616 CREATE INDEX ON rewards (tx_hash); 1617 CREATE INDEX ON trades (tx_hash); 1618 CREATE INDEX ON transfers (tx_hash); 1619 CREATE INDEX ON votes (tx_hash); 1620 CREATE INDEX ON withdrawals (tx_hash); 1621 1622 -- +goose Down 1623 1624 DROP INDEX IF EXISTS accounts_idx_tx_hash; 1625 DROP INDEX IF EXISTS assets_idx_tx_hash; 1626 DROP INDEX IF EXISTS current_balances_idx_tx_hash; 1627 DROP INDEX IF EXISTS delegations_idx_tx_hash; 1628 DROP INDEX IF EXISTS deposits_idx_tx_hash; 1629 DROP INDEX IF EXISTS erc20_multisig_signer_events_idx_tx_hash; 1630 DROP INDEX IF EXISTS ethereum_key_rotations_idx_tx_hash; 1631 DROP INDEX IF EXISTS key_rotations_idx_tx_hash; 1632 DROP INDEX IF EXISTS ledger_idx_tx_hash; 1633 DROP INDEX IF EXISTS liquidity_provisions_idx_tx_hash; 1634 DROP INDEX IF EXISTS margin_levels_idx_tx_hash; 1635 DROP INDEX IF EXISTS markets_idx_tx_hash; 1636 DROP INDEX IF EXISTS network_parameters_idx_tx_hash; 1637 DROP INDEX IF EXISTS node_signatures_idx_tx_hash; 1638 DROP INDEX IF EXISTS nodes_idx_tx_hash; 1639 DROP INDEX IF EXISTS oracle_data_idx_tx_hash; 1640 DROP INDEX IF EXISTS oracle_specs_idx_tx_hash; 1641 DROP INDEX IF EXISTS orders_idx_tx_hash; 1642 DROP INDEX IF EXISTS parties_idx_tx_hash; 1643 DROP INDEX IF EXISTS positions_idx_tx_hash; 1644 DROP INDEX IF EXISTS proposals_idx_tx_hash; 1645 DROP INDEX IF EXISTS protocol_upgrade_proposals_idx_tx_hash; 1646 DROP INDEX IF EXISTS rewards_idx_tx_hash; 1647 DROP INDEX IF EXISTS trades_idx_tx_hash; 1648 DROP INDEX IF EXISTS transfers_idx_tx_hash; 1649 DROP INDEX IF EXISTS votes_idx_tx_hash; 1650 DROP INDEX IF EXISTS withdrawals_idx_tx_hash; 1651 1652 DROP TABLE IF EXISTS last_snapshot_span; 1653 1654 DROP AGGREGATE IF EXISTS public.first(anyelement); 1655 DROP AGGREGATE IF EXISTS public.last(anyelement); 1656 DROP FUNCTION IF EXISTS public.first_agg(anyelement, anyelement); 1657 DROP FUNCTION IF EXISTS public.last_agg(anyelement, anyelement); 1658 1659 DROP VIEW IF EXISTS protocol_upgrade_proposals_current; 1660 DROP TABLE IF EXISTS protocol_upgrade_proposals; 1661 DROP TYPE IF EXISTS protocol_upgrade_proposal_status; 1662 DROP TABLE IF EXISTS core_snapshots; 1663 DROP TABLE IF EXISTS ethereum_key_rotations; 1664 1665 DROP TABLE IF EXISTS key_rotations; 1666 1667 DROP VIEW IF EXISTS transfers_current; 1668 DROP TABLE IF EXISTS transfers; 1669 DROP TYPE IF EXISTS transfer_status; 1670 DROP TYPE IF EXISTS transfer_type; 1671 1672 1673 DROP TABLE IF EXISTS checkpoints; 1674 1675 drop trigger if exists update_current_network_parameters on network_parameters; 1676 drop function if exists update_current_network_parameters; 1677 drop table if exists network_parameters_current; 1678 DROP TABLE IF EXISTS network_parameters cascade; 1679 1680 drop trigger if exists update_current_stake_linking on stake_linking; 1681 drop function if exists update_current_stake_linking; 1682 DROP TABLE IF EXISTS stake_linking_current; 1683 DROP TABLE IF EXISTS stake_linking cascade; 1684 DROP TYPE IF EXISTS stake_linking_status; 1685 DROP TYPE IF EXISTS stake_linking_type; 1686 1687 DROP TABLE IF EXISTS node_signatures; 1688 DROP TYPE IF EXISTS node_signature_kind; 1689 1690 DROP TRIGGER update_live_liquidity_provisions ON liquidity_provisions; 1691 DROP FUNCTION update_live_liquidity_provisions; 1692 DROP TABLE live_liquidity_provisions; 1693 DROP TABLE IF EXISTS liquidity_provisions; 1694 DROP TRIGGER IF EXISTS update_current_liquidity_provisions ON liquidity_provisions; 1695 DROP FUNCTION IF EXISTS update_current_liquidity_provisions; 1696 DROP TABLE IF EXISTS current_liquidity_provisions; 1697 DROP TYPE IF EXISTS liquidity_provision_status; 1698 1699 drop trigger if exists update_current_oracle_data on oracle_data; 1700 drop function if exists update_current_oracle_data; 1701 DROP TABLE IF EXISTS oracle_data_current; 1702 DROP INDEX IF EXISTS idx_oracle_data_matched_spec_ids; 1703 DROP TABLE IF EXISTS oracle_data cascade; 1704 DROP TABLE IF EXISTS oracle_specs; 1705 DROP TYPE IF EXISTS oracle_spec_status; 1706 1707 DROP TABLE IF EXISTS positions_current; 1708 DROP INDEX IF EXISTS positions_party_id_market_id_vega_time_idx; 1709 DROP TABLE IF EXISTS positions cascade; 1710 DROP TRIGGER IF EXISTS update_current_positions ON positions; 1711 DROP FUNCTION IF EXISTS update_current_positions; 1712 DROP TYPE IF EXISTS position_status_type; 1713 1714 DROP VIEW IF EXISTS votes_current; 1715 DROP TABLE IF EXISTS votes; 1716 DROP VIEW IF EXISTS proposals_current; 1717 DROP TABLE IF EXISTS proposals; 1718 DROP TYPE IF EXISTS vote_value; 1719 DROP TYPE IF EXISTS proposal_error; 1720 DROP TYPE IF EXISTS proposal_state; 1721 1722 DROP TABLE IF EXISTS epochs; 1723 1724 DROP TRIGGER IF EXISTS update_current_delegations ON delegations; 1725 DROP FUNCTION IF EXISTS update_current_delegations; 1726 DROP TABLE IF EXISTS delegations_current; 1727 DROP TABLE IF EXISTS delegations; 1728 1729 DROP TABLE IF EXISTS rewards; 1730 1731 DROP TABLE IF EXISTS network_limits; 1732 DROP VIEW IF EXISTS orders_current; 1733 1734 DROP VIEW IF EXISTS orders_current_versions; 1735 DROP VIEW IF EXISTS orders_current_desc; 1736 DROP VIEW IF EXISTS orders_current_desc_by_reference; 1737 DROP VIEW IF EXISTS orders_current_desc_by_party; 1738 DROP VIEW IF EXISTS orders_current_desc_by_market; 1739 DROP VIEW IF EXISTS risk_factors_current; 1740 drop table if exists risk_factors; 1741 drop table if exists margin_levels cascade; 1742 DROP TRIGGER IF EXISTS update_current_margin_levels ON margin_levels; 1743 DROP FUNCTION IF EXISTS update_current_margin_levels; 1744 DROP TABLE IF EXISTS current_margin_levels; 1745 1746 drop trigger if exists update_current_deposits on deposits; 1747 drop function if exists update_current_deposits; 1748 -- +goose StatementBegin 1749 DO $$ 1750 BEGIN 1751 IF EXISTS (SELECT relname 1752 FROM pg_class 1753 WHERE relname='deposits_current' 1754 AND relkind = 'r') 1755 THEN 1756 DROP TABLE IF EXISTS deposits_current; 1757 ELSE 1758 DROP VIEW IF EXISTS deposits_current; 1759 END IF; 1760 END; 1761 $$; 1762 -- +goose StatementEnd 1763 DROP TABLE IF EXISTS deposits cascade; 1764 DROP TYPE IF EXISTS deposit_status; 1765 1766 drop trigger if exists update_current_withdrawals on withdrawals; 1767 drop function if exists update_current_withdrawals; 1768 -- +goose StatementBegin 1769 DO $$ 1770 BEGIN 1771 IF EXISTS (SELECT relname 1772 FROM pg_class 1773 WHERE relname='withdrawals_current' 1774 AND relkind = 'r') 1775 THEN 1776 DROP TABLE IF EXISTS withdrawals_current; 1777 ELSE 1778 DROP VIEW IF EXISTS withdrawals_current; 1779 END IF; 1780 END; 1781 $$; 1782 -- +goose StatementEnd 1783 DROP TABLE IF EXISTS withdrawals cascade; 1784 DROP TYPE IF EXISTS withdrawal_status; 1785 1786 1787 DROP TRIGGER IF EXISTS archive_orders ON orders; 1788 DROP FUNCTION IF EXISTS archive_orders; 1789 DROP TABLE IF EXISTS orders; 1790 DROP TABLE IF EXISTS orders_live; 1791 DROP TABLE IF EXISTS orders_history; 1792 1793 DROP TYPE IF EXISTS order_time_in_force; 1794 DROP TYPE IF EXISTS order_status; 1795 DROP TYPE IF EXISTS order_side; 1796 DROP TYPE IF EXISTS order_type; 1797 DROP TYPE IF EXISTS order_pegged_reference; 1798 1799 DROP TABLE IF EXISTS ranking_scores; 1800 DROP TABLE IF EXISTS reward_scores; 1801 DROP TYPE IF EXISTS validator_node_status; 1802 1803 DROP TABLE IF EXISTS nodes; 1804 DROP TABLE IF EXISTS nodes_announced; 1805 DROP TYPE IF EXISTS node_status; 1806 1807 DROP TRIGGER IF EXISTS update_current_markets ON markets; 1808 DROP FUNCTION IF EXISTS update_current_markets; 1809 DROP TABLE IF EXISTS markets_current; 1810 DROP TABLE IF EXISTS markets CASCADE; 1811 1812 DROP TABLE IF EXISTS markets; 1813 DROP VIEW IF EXISTS market_data_snapshot; 1814 DROP TRIGGER IF EXISTS update_current_market_data ON market_data; 1815 DROP FUNCTION IF EXISTS update_current_market_data; 1816 DROP TABLE IF EXISTS current_market_data; 1817 DROP TABLE IF EXISTS market_data; 1818 DROP TYPE IF EXISTS auction_trigger_type; 1819 DROP TYPE IF EXISTS market_trading_mode_type; 1820 DROP TYPE IF EXISTS market_state_type; 1821 1822 DROP TABLE IF EXISTS erc20_multisig_signer_events; 1823 DROP TYPE IF EXISTS erc20_multisig_signer_event; 1824 1825 DROP TABLE IF EXISTS ledger; 1826 DROP TABLE IF EXISTS balances cascade; 1827 DROP TRIGGER IF EXISTS update_current_balances ON balances; 1828 DROP FUNCTION IF EXISTS update_current_balances; 1829 DROP TABLE IF EXISTS current_balances; 1830 1831 DROP TABLE IF EXISTS accounts; 1832 DROP TABLE IF EXISTS parties; 1833 DROP VIEW IF EXISTS assets_current; 1834 DROP TABLE IF EXISTS assets; 1835 DROP TYPE IF EXISTS asset_status_type; 1836 DROP VIEW IF EXISTS trades_candle_block; 1837 DROP TABLE IF EXISTS trades cascade; 1838 DROP TABLE IF EXISTS chain; 1839 DROP TABLE IF EXISTS blocks cascade; 1840 DROP TABLE IF EXISTS last_block cascade; 1841 DROP TRIGGER IF EXISTS update_last_block ON balances; 1842 DROP FUNCTION IF EXISTS update_last_block; 1843 1844 1845 DROP DOMAIN IF EXISTS HUGEINT;