# Indexer Ideas

## Index.py

This script currently (on a polling loop every ~1 second):
- queries `block` table in postgres to find `last_processed_block`
- queries blockchain to find `latest_block_height`
- iterates through each block height btw `last_processed_block` and `latest_block_height`, running `process_block()` to index each block into postgres
- writes to: `block`, `tx`, `msg`, `msg_event`, `msg_event_attr`

## Some ETL process

Keeping "retirements table" as our use case in mind...

Want to write to a table that looks like:

Table name: retirements

Required data field	Location in GetTxsEvent
1	retirement date	(Postgresql/block)
2	project id	(Postgresql/msg_event_attr for newer batch_ids, otherwise REST API)
3	batch id	(postgresql/msg_event_attr)
4	credit class id	(postgresql/msg_event_attr)
5	amount retired	(postgresql/msg_event_attr)
6	issuer of the batch (REST API request)
7	batch start date (postgresql/msg_event_attr)
8	batch end date (postgresql/msg_event_attr)
9	retirement location	(postgresql/msg_event_attr)
10	project location (REST API request)
11	retired by (postgresql/msg_event_attr)
12	retirement reason (postgresql, tx.memo or event)

Three approaches:
1. single ETL batch job that `retirements` table that sources data from postgres and REST API on every row that is written
2. Two separate tables
a. `event_retire` which is a pure SQL ETL (`INSERT INTO SELECT`)
b. `retirements` which polls new rows in `event_retire` and joins them with extra data from a REST API to populate `retirements` table
3. single `retirements` table sourcing only from postgres, and all non tx/event data is queried from the client

In any above case, we will need to answer questions like:
- Manage how much data has been processed from each ETL process
- setting up a `last_processed` table, which store "table_name" and last processed block height for each ETL process OR
- do some joins / queries to figure that information out

## Appendix

Example SQL that can help us generate some kind of table:

INSERT INTO SELECT
  chain_num, block_height, tx_idx, msg_idx,
  MAX(CASE WHEN key = 'owner' THEN value ELSE null END) as owner,
  MAX(CASE WHEN key = 'batch_denom' THEN value ELSE null END) as batch_denom,
  MAX(CASE WHEN key = 'amount' THEN value ELSE null END) as amount,
  MAX(CASE WHEN key = 'jurisdiction' THEN value ELSE null END) as jurisdiction,
  MAX(CASE WHEN key = 'reason' THEN value ELSE null END) as reason,
  (SUM(CASE WHEN key = 'amount' THEN 1 ELSE 0 END) > 1) has_duplicates
from msg_event_attr
where type like 'regen.ecocredit.v1.EventRetire'
AND block_height >= '$START_HEIGHT'
AND block_height < '$END_HEIGHT'
group by 1,2,3,4