migrations

Documentation

Overview

Package migrations contains all the migration files

Index

• Variables

Variables

var Migration001InitDatabase = &migrate.Migration{
	Id: "001-init-database",
	Up: []string{`
		CREATE TABLE IF NOT EXISTS ` + vars.TableValidatorRegistration + ` (
			id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			pubkey        varchar(98) NOT NULL,
			fee_recipient varchar(42) NOT NULL,
			timestamp     bigint NOT NULL,
			gas_limit     bigint NOT NULL,
			signature     text NOT NULL
		);

		CREATE UNIQUE INDEX IF NOT EXISTS ` + vars.TableValidatorRegistration + `_pubkey_timestamp_uidx ON ` + vars.TableValidatorRegistration + `(pubkey, timestamp DESC);


		CREATE TABLE IF NOT EXISTS ` + vars.TableExecutionPayload + ` (
			id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			slot            bigint NOT NULL,
			proposer_pubkey varchar(98) NOT NULL,
			block_hash      varchar(66) NOT NULL,

			version text NOT NULL,
			payload json NOT NULL
		);

		CREATE UNIQUE INDEX IF NOT EXISTS ` + vars.TableExecutionPayload + `_slot_pk_hash_idx ON ` + vars.TableExecutionPayload + `(slot, proposer_pubkey, block_hash);


		CREATE TABLE IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + ` (
			id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			execution_payload_id bigint,

			-- simulation & verification results
			sim_success boolean NOT NULL,
			sim_error   text    NOT NULL,

			-- bidtrace data
			signature            text NOT NULL,

			slot        bigint NOT NULL,
			parent_hash varchar(66) NOT NULL,
			block_hash  varchar(66) NOT NULL,

			builder_pubkey         varchar(98) NOT NULL,
			proposer_pubkey        varchar(98) NOT NULL,
			proposer_fee_recipient varchar(42) NOT NULL,

			gas_used   bigint NOT NULL,
			gas_limit  bigint NOT NULL,

			num_tx int NOT NULL,
			value  NUMERIC(48, 0),

			-- helpers
			epoch        bigint NOT NULL,
			block_number bigint NOT NULL,
			was_most_profitable boolean NOT NULL
		);

		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_slot_idx ON ` + vars.TableBuilderBlockSubmission + `("slot");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_blockhash_idx ON ` + vars.TableBuilderBlockSubmission + `("block_hash");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_blocknumber_idx ON ` + vars.TableBuilderBlockSubmission + `("block_number");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_builderpubkey_idx ON ` + vars.TableBuilderBlockSubmission + `("builder_pubkey");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_simsuccess_idx ON ` + vars.TableBuilderBlockSubmission + `("sim_success");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_mostprofit_idx ON ` + vars.TableBuilderBlockSubmission + `("was_most_profitable");
		CREATE INDEX IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_executionpayloadid_idx ON ` + vars.TableBuilderBlockSubmission + `("execution_payload_id");


		CREATE TABLE IF NOT EXISTS ` + vars.TableDeliveredPayload + ` (
			id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			signed_blinded_beacon_block json,

			epoch bigint NOT NULL,
			slot  bigint NOT NULL,

			builder_pubkey         varchar(98) NOT NULL,
			proposer_pubkey        varchar(98) NOT NULL,
			proposer_fee_recipient varchar(42) NOT NULL,

			parent_hash  varchar(66) NOT NULL,
			block_hash   varchar(66) NOT NULL,
			block_number bigint NOT NULL,

			gas_used  bigint NOT NULL,
			gas_limit bigint NOT NULL,

			num_tx  int NOT NULL,
			value   NUMERIC(48, 0),

			UNIQUE (slot, proposer_pubkey, block_hash)
		);

		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_slot_idx ON ` + vars.TableDeliveredPayload + `("slot");
		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_blockhash_idx ON ` + vars.TableDeliveredPayload + `("block_hash");
		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_blocknumber_idx ON ` + vars.TableDeliveredPayload + `("block_number");
		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_proposerpubkey_idx ON ` + vars.TableDeliveredPayload + `("proposer_pubkey");
		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_builderpubkey_idx ON ` + vars.TableDeliveredPayload + `("builder_pubkey");
		CREATE INDEX IF NOT EXISTS ` + vars.TableDeliveredPayload + `_value_idx ON ` + vars.TableDeliveredPayload + `("value");


		CREATE TABLE IF NOT EXISTS ` + vars.TableBlockBuilder + ` (
			id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			builder_pubkey  varchar(98) NOT NULL,
			description    	text NOT NULL,

			is_high_prio    boolean NOT NULL,
			is_blacklisted  boolean NOT NULL,

			last_submission_id   bigint references ` + vars.TableBuilderBlockSubmission + `(id) on delete set null,
			last_submission_slot bigint NOT NULL,

			num_submissions_total    bigint NOT NULL,
			num_submissions_simerror bigint NOT NULL,
			num_submissions_topbid   bigint NOT NULL,

			num_sent_getpayload bigint NOT NULL DEFAULT 0,

			UNIQUE (builder_pubkey)
		);
		`},
	Down: []string{`
		DROP TABLE IF EXISTS ` + vars.TableBuilderBlockSubmission + `;
		DROP TABLE IF EXISTS ` + vars.TableDeliveredPayload + `;
		DROP TABLE IF EXISTS ` + vars.TableBlockBuilder + `;
		DROP TABLE IF EXISTS ` + vars.TableExecutionPayload + `;
		DROP TABLE IF EXISTS ` + vars.TableValidatorRegistration + `;
		`},
	DisableTransactionUp:   false,
	DisableTransactionDown: false,
}

var Migration002RemoveIsBestAddReceivedAt = &migrate.Migration{
	Id: "002-remove-isbest-add-receivedat",
	Up: []string{`
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD received_at timestamp;

		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` DROP COLUMN was_most_profitable;
		DROP INDEX IF EXISTS ` + vars.TableBuilderBlockSubmission + `_mostprofit_idx;

		ALTER TABLE ` + vars.TableBlockBuilder + ` DROP COLUMN num_submissions_topbid;
	`, `
		CREATE INDEX CONCURRENTLY IF NOT EXISTS ` + vars.TableBuilderBlockSubmission + `_received_idx ON ` + vars.TableBuilderBlockSubmission + `(received_at DESC);
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration003AddEligibleAtSignedAt = &migrate.Migration{
	Id: "003-add-eligibleat-add-signedat",
	Up: []string{`
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD eligible_at timestamp;
		ALTER TABLE ` + vars.TableDeliveredPayload + ` ADD signed_at timestamp;
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration004BlockedValidator = &migrate.Migration{
	Id: "004-blocked-validator",
	Up: []string{`
		CREATE TABLE IF NOT EXISTS ` + vars.TableBlockedValidator + ` (
			id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			pubkey        varchar(98) NOT NULL,
			is_blocked    boolean NOT NULL default false,
			notes         text NOT NULL default '',

			UNIQUE (pubkey)
		);
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration005RemoveBlockedValidator = &migrate.Migration{
	Id: "005-remove-blocked-validator",
	Up: []string{`
		DROP TABLE IF EXISTS ` + vars.TableBlockedValidator + `;
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migrations = migrate.MemoryMigrationSource{
	Migrations: []*migrate.Migration{
		Migration001InitDatabase,
		Migration002RemoveIsBestAddReceivedAt,
		Migration003AddEligibleAtSignedAt,
		Migration004BlockedValidator,
		Migration005RemoveBlockedValidator,
	},
}