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 Migration006CreateTooLateGetPayload = &migrate.Migration{
	Id: "006-create-too-late-get-payload",
	Up: []string{`
		CREATE TABLE IF NOT EXISTS ` + vars.TableTooLateGetPayload + ` (
			id          bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,

			slot            bigint NOT NULL,

			slot_start_timestamp   bigint NOT NULL,
			request_timestamp bigint NOT NULL,
			decode_timestamp  bigint NOT NULL,

			proposer_pubkey varchar(98) NOT NULL,
			block_hash      varchar(66) NOT NULL,
			ms_into_slot    bigint NOT NULL
		);

		CREATE UNIQUE INDEX IF NOT EXISTS ` + vars.TableTooLateGetPayload + `_slot_pk_hash_idx ON ` + vars.TableTooLateGetPayload + `(slot, proposer_pubkey, block_hash);
	`, `
		ALTER TABLE ` + vars.TableDeliveredPayload + ` ADD publish_ms bigint NOT NULL DEFAULT 0;
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration007BuilderSubmissionWasSimulated = &migrate.Migration{
	Id: "007-builder-submission-was-simulated",
	Up: []string{`
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD was_simulated boolean NOT NULL DEFAULT true;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD sim_req_error text NOT NULL DEFAULT '';
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration008Optimistic = &migrate.Migration{
	Id: "008-optimistic",
	Up: []string{
		`
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD optimistic_submission bool NOT NULL default false;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD decode_duration       bigint NOT NULL default 0;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD prechecks_duration    bigint NOT NULL default 0;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD simulation_duration   bigint NOT NULL default 0;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD redis_update_duration bigint NOT NULL default 0;
		ALTER TABLE ` + vars.TableBuilderBlockSubmission + ` ADD total_duration        bigint NOT NULL default 0;
	`, `
		ALTER TABLE ` + vars.TableBlockBuilder + ` ADD is_optimistic  bool NOT NULL default false;
		ALTER TABLE ` + vars.TableBlockBuilder + ` ADD collateral     NUMERIC(48, 0) NOT NULL default 0;
		ALTER TABLE ` + vars.TableBlockBuilder + ` ADD builder_id     varchar(98) NOT NULL default '';
	`, `
		CREATE TABLE IF NOT EXISTS ` + vars.TableBuilderDemotions + `(
			id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
			inserted_at timestamp NOT NULL default current_timestamp,
			
			submit_block_request          json,
			signed_beacon_block  	      json,
			signed_validator_registration json,

			epoch bigint NOT NULL,
			slot  bigint NOT NULL,
			
			builder_pubkey  varchar(98) NOT NULL,
			proposer_pubkey varchar(98) NOT NULL,
			
			value NUMERIC(48, 0),
			
			fee_recipient varchar(42) NOT NULL,
			
			block_hash varchar(66) NOT NULL,
			
			sim_error text NOT NULL,
			
			UNIQUE (builder_pubkey, block_hash)
		);
	`,
	},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

var Migration009BlockBuilderRemoveReference = &migrate.Migration{
	Id: "009-block-builder-remove-reference",
	Up: []string{`
		ALTER TABLE ` + vars.TableBlockBuilder + ` DROP CONSTRAINT "` + vars.TableBlockBuilder + `_last_submission_id_fkey";
	`},
	Down: []string{},

	DisableTransactionUp:   true,
	DisableTransactionDown: true,
}

Migration009BlockBuilderRemoveReference removes the foreign key constraint from the blockbuilders table to the latest submissions by a builder.

This reference makes it impossible to migrate to a new database without having all bids there first (which is the bulk of the data). Just removing the foreign key constraint is the easiest way to solve this constraint, without downsides.

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