App-Address to App-Index Lookup for Min-Balance

[tzaffi]

Minimum Balance Calculation and App-Boxes

(Repurposed from the description of #1067 )

It would be nice to solve the long-standing issue #808 and provide minimum balance information for accounts, including app-accounts. App-Boxes affect the min balance calculation for an app-account holding boxes, and as #1067 introduces their basic information to indexer, it is worthwhile to consider implications of various design choices.

The New Minimum Balance Calculation

The new boxes-included min-balance calculation requires knowing:

• the number of boxes for an app (totalBoxes)
• the total storage utilized in those boxes (totalBoxBytes) which is comprised of bytes used for keys and values
  The current PR allows calculating this information for a particular app. EG:

WITH boxes AS (
    SELECT COUNT(*) as total_boxes, SUM(LENGTH(name) + LENGTH(value)) as total_box_bytes
    FROM app_box
    WHERE app = {$app_index}
    GROUP BY app
) 
SELECT {$box_flat_minbalance} * total_boxes + {$box_byte_minbalance} * total_box_bytes AS app_box_cost
FROM boxes;

So at first blush it seems that if we know how to currently calculate min-balance in Indexer, we can do so when we incorporate the box information. However, this is not the case:

Missing App Account ↠ App Index Mapping

Since min balance is technically associated to an account, when we calculate the min-balance for an app, we are really calculating it for the app-account. If we pre-calculate the box associated min-balance on every change, we can then calculate the app id's address on the fly and add it to the associated address'es min balance. But if we want to calculate the min-balance at the time of querying, joining against the new app_box table, we'll need to know what app index (if any) is associated with an account. Currently we don't store this information (AFAIK), so we would need to introduce a lookup table such as:

CREATE TABLE address_app(
    addr bytea PRIMARY KEY, app bigint NOT NULL
);

[tzaffi]

After some discussions, this is going to be addressed by #1067. In particular:

• All the necessary data for computing the present Min-Balance is present in the JSONB column account.account_data
• The relevant box fields for computing Min-Balance (TotalBoxes and TotalBoxBytes in userBalance.go) will be added to account.account_data as well.