Skip to main content

Archive Node Schema Changes

Upgrading archive nodes from Berkeley to Mesa​

Below we present details of what changed in the archive node database schema between Berkeley and Mesa versions.

Extended zkApp State Fields​

Both zkApp state tables have been modified to support additional state elements:

zkapp_states_nullable table

  • Added columns element8 through element31 (nullable integer fields)
  • Each new column references zkapp_field(id)
  • These fields allow zkApps to store additional state information beyond the original 8 elements
ALTER TABLE zkapp_states_nullable ADD COLUMN IF NOT EXISTS element8 INT REFERENCES zkapp_field(id);
...
ALTER TABLE zkapp_states_nullable ADD COLUMN IF NOT EXISTS element31 INT REFERENCES zkapp_field(id);

zkapp_states table

  • Added columns element8 through element31 (non-nullable integer fields)
  • Each new column references zkapp_field(id) with a default value pointing to the zero field
  • Unlike the nullable version, these fields are required and default to the zero field ID
ALTER TABLE zkapp_states ADD COLUMN IF NOT EXISTS element8 INT DEFAULT <zero_field_id> NOT NULL REFERENCES zkapp_field(id);
...
ALTER TABLE zkapp_states ADD COLUMN IF NOT EXISTS element31 INT DEFAULT <zero_field_id> NOT NULL REFERENCES zkapp_field(id);

This expansion allows zkApps to store up to 32 state elements instead of the previous 8, significantly increasing the state storage capacity for complex smart contracts.

Dropped element_ids Uniqueness (events and actions)​

The zkapp_events and zkapp_field_array tables store their contents in an unbounded int[] element_ids column. Mesa drops the UNIQUE constraint and the standalone btree index on that column. A btree key over a large array (a max-cost zkApp can produce ~1024 elements) exceeds PostgreSQL's 2704-byte index-row limit, which previously caused inserts to fail for such zkApps. As a consequence, these rows are no longer content-deduplicated.

ALTER TABLE zkapp_field_array DROP CONSTRAINT IF EXISTS zkapp_field_array_element_ids_key;
DROP INDEX IF EXISTS idx_zkapp_field_array_element_ids;
ALTER TABLE zkapp_events DROP CONSTRAINT IF EXISTS zkapp_events_element_ids_key;
DROP INDEX IF EXISTS idx_zkapp_events_element_ids;

Nullable events_id / actions_id​

The events_id and actions_id columns on zkapp_account_update_body become nullable. After the upgrade, an empty events or actions list is stored as NULL (no zkapp_events row is created) instead of referencing an empty-array row.

ALTER TABLE zkapp_account_update_body ALTER COLUMN events_id DROP NOT NULL;
ALTER TABLE zkapp_account_update_body ALTER COLUMN actions_id DROP NOT NULL;

Version Tracking​

The upgrade introduces a new migration_history table to keep track of the database schema version. The purpose of this table is to help with future database migrations. The table tracks which migration scripts were applied and when.

migration_history table

CREATE TABLE IF NOT EXISTS migration_history (
    commit_start_at   timestamptz NOT NULL DEFAULT now() PRIMARY KEY,
    protocol_version  text NOT NULL,
    migration_version text NOT NULL,
    description       text NOT NULL,
    status            migration_status NOT NULL
);

The migration_history table provides:

  • Migration tracking: Records which migrations have been applied
  • Timestamp tracking: Shows when each migration was executed
  • Idempotency: Prevents duplicate migration runs
  • Version identification: Easily identify the current database schema version

The table is created if it does not exist already. Rollback and upgrade scripts will insert a new row with the version number and timestamp when the script was applied.