# Archive Node Schema Changes

> Reference of database schema changes applied to the Mina archive node during the Mesa hard fork — new columns, modified tables, and the SQL applied by the upgrade script.

Canonical URL: https://docs.minaprotocol.com/network-upgrades/mesa/appendix/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

```sql
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

```sql
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**.

```sql
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.

```sql
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**
```sql
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.