Indexer Database Design

This document outlines the database schema design for the Indexer.

Goals

• Speed: Meet immediate requirements quickly.
• Extensibility (Secondary): Easy to support additional metrics in the future.

Custom Types (Enums)

To ensure data integrity and query readability, we use PostgreSQL enums for fixed sets of values.

-- Enum for operation types
CREATE TYPE operation_type_enum AS ENUM (
    'DEPOSIT',
    'WITHDRAW',
    'BORROW',
    'REPAY',
    'LIQUIDATION'
);

-- Enum for operation status
CREATE TYPE operation_status_enum AS ENUM (
    'PENDING',
    'SUCCESS',
    'FAILURE'
);

Core Entities

Users

Stores unique user account IDs referenced by other tables.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    account_id TEXT NOT NULL UNIQUE
);

Design Decision: Integer Primary Key vs. Account ID We use an auto-incrementing integer (id) as the primary key instead of the NEAR account_id string for the following reasons:

1. Storage Efficiency: SERIAL (integer) takes 4 bytes in foreign keys, whereas a NEAR account_id can be up to 64 bytes.
2. Join Performance: Joins on integers are generally faster than joins on variable-length text fields.
3. Index Size: B-Tree indexes are more compact with integer keys.

Assets

Stores information about supported assets (collateral and lendable tokens).

CREATE TABLE assets (
    id SERIAL PRIMARY KEY,
    contract_id TEXT NOT NULL UNIQUE -- e.g., 'usdc.fakes.testnet'
);

Operations Log

Operations

A central log for every relevant on-chain operation. This table stores metadata common to all operations.

CREATE TABLE operations (
    id BIGSERIAL PRIMARY KEY,
    -- The user whose account is directly affected by this operation.
    user_id INTEGER NOT NULL REFERENCES users(id),
    -- The user who originally signed the transaction.
    signer_id INTEGER NOT NULL REFERENCES users(id),
    tx_hash TEXT,
    block_height BIGINT NOT NULL,
    block_timestamp TIMESTAMPTZ NOT NULL,
    operation_type operation_type_enum NOT NULL,
    status operation_status_enum NOT NULL
);

CREATE INDEX ON operations (user_id);

Receipt Mapping

Maps any receipt ID in a promise chain to the single operation it belongs to.

CREATE TABLE receipt_to_operation_map (
    receipt_id TEXT PRIMARY KEY,
    operation_id BIGINT NOT NULL REFERENCES operations(id),
    status operation_status_enum NOT NULL
);

CREATE INDEX ON receipt_to_operation_map (operation_id);

Workflow: Linking Receipts to Operations

1. Initial Processing: When the ingester processes the first receipt of a multi-step operation (e.g., WithdrawCollateral), it creates a row in the operations table with status = 'PENDING'.
2. RPC Lookup: The ingester makes an RPC call to retrieve all receipts associated with the transaction. (Note: The current lake framework does not expose the full receipt chain, so we rely on RPC nodes).
3. Mapping Creation: Entries are created in receipt_to_operation_map for the current receipt ID and any generated promise_receipt_id, linking them to the newly created operation_id.
4. Callback Processing:
   • Intermediate callbacks (e.g., Pyth price checks) can be ignored if not relevant.
   • The final callback (e.g., on_withdraw_collateral_callback) is correlated using the transaction hash and receipt ID to update the operation status.
   • RPC data helps disambiguate cases where multiple identical operations (same user, same amount) occur close together.

Operation Details

Specific tables for detailed data related to each operation type.

-- For deposits
CREATE TABLE deposits (
    operation_id BIGINT PRIMARY KEY REFERENCES operations(id),
    asset_id INTEGER NOT NULL REFERENCES assets(id),
    amount NUMERIC(40, 0) NOT NULL -- NUMERIC for precision with large integers
);

-- For withdrawals
CREATE TABLE withdrawals (
    operation_id BIGINT PRIMARY KEY REFERENCES operations(id),
    asset_id INTEGER NOT NULL REFERENCES assets(id),
    amount NUMERIC(40, 0) NOT NULL
);

-- For borrowing actions
CREATE TABLE borrows (
    operation_id BIGINT PRIMARY KEY REFERENCES operations(id),
    amount NUMERIC(40, 0) NOT NULL
);

Transaction Failures

Tracks user-initiated transactions that failed before generating a successful execution receipt.

CREATE TABLE transaction_failures (
    tx_hash TEXT PRIMARY KEY,
    signer_id INTEGER NOT NULL REFERENCES users(id),
    block_height BIGINT NOT NULL,
    block_timestamp TIMESTAMPTZ NOT NULL,
    operation_type operation_type_enum NOT NULL,
    amount NUMERIC(40, 0),
    asset_id INTEGER REFERENCES assets(id)
);