Merge pull request #156 from thirdweb-dev/02-19-keep_track_of_last_co…

…mmitted_block_in_memory

Track  last committed block in memory

Merge pull request #154 from thirdweb-dev/02-18-fix_duplicate_inserts

fix duplicate inserts

create an endpoint to query token balances (#136)

### TL;DR

Added a new API endpoint to fetch token balances by type (ERC20, ERC721, ERC1155) for a given address.

### What changed?

- Added `/balances/:owner/:type` endpoint to retrieve token balances
- Created materialized views in ClickHouse to track token balances from transfer events
- Implemented balance querying with support for pagination and filtering
- Added support for hiding zero balances
- Introduced token balance data structures and query filters

### How to test?

1. Start the API server
2. Query the endpoint: `/balances/{address}/{token_type}`
   - `token_type`: "erc20", "erc721", or "erc1155"
   - Optional query params:
     - `hide_zero_balances`: true/false
     - `token_address`: specific token contract
     - `page` and `limit`: for pagination
     - `sort_by` and `sort_order`: for sorting

### Why make this change?

To provide a convenient way to fetch token balances for addresses across different token standards, enabling better wallet integration and portfolio tracking capabilities.

Merge pull request #145 from thirdweb-dev/vt-abi-decoding-param

feat: add abi_decoding_enabled flag to transactions and events endpoints

feat: get blocks API handle (#140)

### TL;DR
Added a new `/blocks` endpoint to retrieve block data with filtering, sorting, and aggregation capabilities.

### What changed?
- Created a new `/blocks` endpoint in the API
- Implemented block data retrieval with support for:
  - Filtering by chain ID and other block parameters
  - Sorting and pagination
  - Aggregation functions
  - Group by operations
- Updated the storage interface to return QueryResult for block operations
- Added block scanning functionality to handle database rows

### How to test?
1. Start the API server
2. Make GET requests to `/blocks` with optional query parameters:
   - `chainId`: Specify the blockchain network
   - `filter`: Apply filters to block data
   - `sort_by`: Sort results by specific fields
   - `sort_order`: Choose ascending or descending order
   - `page` and `limit`: Control pagination
   - `aggregate`: Apply aggregation functions
   - `group_by`: Group results by specific fields

### Why make this change?
To provide a standardized way to query and analyze block data from the blockchain, enabling users to retrieve block information with flexible filtering and aggregation options. This enhancement aligns with the existing transaction and event query capabilities of the API.

make reorg handling backward looking if it has caught up with the cha…

…in (#143)

### TL;DR
Improved reorg handler block range calculation to support both forward and backward scanning based on the current sync state.

### What changed?
- Added `getReorgCheckRange` function to dynamically determine the block range for reorg checks
- When catching up (large gap between latest checked and committed blocks), the handler scans forward
- When caught up (small gap), the handler looks backward from the latest committed block
- Added protection to prevent negative block numbers when scanning backward
- Added early exit when the latest checked block equals the target block

### How to test?
- Run the new test cases:
  - `TestReorgHandlerRangeIsForwardLookingWhenItIsCatchingUp`
  - `TestReorgHandlerRangeIsBackwardLookingWhenItIsCaughtUp`
  - `TestReorgHandlerRangeStartIs0WhenRangeIsLargerThanProcessedBlocks`
- Verify the reorg handler correctly determines scan ranges in different sync states

### Why make this change?
The previous implementation always scanned forward, which could miss reorgs when the system was caught up with the chain head. This change ensures more thorough reorg detection by adapting the scan direction based on the current sync state, improving the reliability of reorg detection.

improve delete performance for clickhouse (#132)

### TL;DR
Improved the deletion logic in case of reorgs in ClickHouse by implementing partition-aware deletes and disabling lightweight delete synchronization.

### What changed?
- Added `lightweight_deletes_sync` setting (value 0) to ClickHouse configuration. This will make the deletes async, so processing does not need to wait for it to finish
- Split generic `deleteBatch` function into specific delete functions for blocks, logs, transactions, and traces, which will improve performance due to having all of the order by keys defined
- Implemented two-step deletion process:
  1. Query for existing records that need deletion
  2. Execute targeted deletes using partition values and primary keys
- Added proper error handling and batch processing for deletions


### Why make this change?
The previous deletion method was inefficient and could cause performance issues in ClickHouse.

fix: substring correctly (#130)

### TL;DR
Fixed incorrect string slicing in ClickHouse filter parameters for 'gte' and 'lte' conditions.

### What changed?
Modified the string slicing length in `addFilterParams` function from `-3` to `-4` for 'gte' and 'lte' suffixes to properly extract the base column name from filter parameters.

### How to test?
1. Execute queries using filters with 'gte' and 'lte' suffixes
2. Verify that the generated SQL queries contain the correct column names
3. Confirm that filter conditions are properly applied to the results

### Why make this change?
The previous implementation was incorrectly slicing the column names for 'gte' and 'lte' conditions, which could lead to malformed SQL queries. This fix ensures proper column name extraction and maintains consistency with the filter parameter naming convention.

Fix force range for reorg checks (#126)

### TL;DR
Added block range validation and optimized block header queries to prevent unnecessary reorg checks.

### What changed?
- Added validation to skip reorg checks when the most recent and last checked block numbers are equal
- Modified the `LookbackBlockHeaders` query to include a lower bound on block numbers, creating a specific range for querying
- Enhanced logging to better indicate when reorg checks are skipped

### How to test?
1. Run the system with chain monitoring enabled
2. Verify logs show skipped reorg checks when blocks are equal
3. Confirm block header queries return results only within the specified range
4. Validate that reorg detection still functions correctly for actual reorgs

### Why make this change?
The previous implementation would perform unnecessary reorg checks when block numbers were identical and could potentially query more blocks than needed. These changes improve efficiency by avoiding redundant operations and limiting the query scope to only relevant block ranges.

decode events (#125)

### TL;DR
Added event decoding functionality for Ethereum logs, enabling the parsing and interpretation of indexed and non-indexed event parameters.

### What changed?
- Added `ConstructEventABI` function to parse event signatures into ABI format
- Implemented log decoding functionality to handle both indexed and non-indexed event parameters
- Created new `DecodedLog` and `DecodedLogData` structures to represent decoded event data
- Enhanced log handlers to support automatic event decoding when a signature is provided
- Added support for converting various data types (bytes, numerics) to hexadecimal format

### How to test?
1. Make a GET request to `/{chainId}/events/{contract}/{signature}` with a valid event signature
2. Verify that the response includes decoded event data with:
   - Event name and signature
   - Indexed parameters properly decoded
   - Non-indexed parameters converted to appropriate formats
   - All byte and numeric values properly converted to hex

### Why make this change?
To improve the readability and usability of event log data by automatically decoding raw event parameters into their proper types and formats. This makes it easier for developers to work with and understand event data without having to manually decode the raw logs.