Lee ChanyangBackend Engineer

Language

← Back to project list

Data Pipeline

On-Chain Activity Rewards & Statistics Data Pipeline

I connected contract approval, on-chain event collection, reward statistics, and replayable batches into a single data flow.

Company
Algorith Capital
Service
PoET platform
Period
October 2023–March 2024 · Approximately 9 weeks
Role
Backend Engineer
Responsibility
User/Admin API · DB · Data Pipeline/Batch · Azure Ops
Team
BE 5 · FE 2
  • NestJS
  • TypeScript
  • PostgreSQL
  • MySQL
  • TypeORM
  • Redis
  • Azure
  • ethers.js
  • ChatGPT

Project Overview

I built a reward statistics flow by separating contract approval from on-chain history scanning

The core challenge was to avoid using dApp team submissions directly as reward sources. Only contracts that passed review and approval could feed statistics batches and user-facing read models.

I owned the backend and data pipeline scope, separating submission and approval data, state-change history, user reward statistics, batches that resume from the last processed position, and duplicate-execution safeguards. The 670 million+ figure describes only the source transaction table size. I do not present throughput or P95 as outcome metrics because supporting evidence was not preserved.

  • Only contracts that passed the approval gate could become inputs to reward statistics batches.
  • I describe the large source history in terms of the ranges followed with cursors and bounded search windows, without overstating it as total throughput.
  • I treated rescanning historical ranges, discovering new targets, and resuming after interruption as normal batch operating scenarios.

Ownership

My direct responsibilities

  • Defined approved-contract and reward/leaderboard read-model contracts across the shared backend, Statistics Worker, and frontend
  • Designed models for dApp contract submission, review, approval states, and change history
  • Implemented automatic discovery of approved contracts and tokens and their inclusion in independent statistics jobs
  • Implemented batches that scan a source table of 670 million+ transactions using the last processed position and a bounded search range, while backfilling history for new targets
  • Converted Transfer history into per-token game history and reward, transaction, and price-converted aggregates for user statistics and leaderboards

Constraints

Constraints to address first

  • Submission and approval data had to be separated from operational targets, with state-change history retained, to keep unverified contracts out of the reward system.
  • Source transactions had accumulated to approximately 670 million+ records, requiring bounded searches from the last processed position rather than full-table reads on every run.
  • The pipeline needed to catch up with historical backlogs while new data continued to arrive, then resume from the last processed position after a failure.
  • As game contracts and tokens could be added, relying on manual configuration to discover batch targets risked omissions.
  • Reexecuting any processing stage must not duplicate history or aggregates, and result persistence and cursor advancement needed to commit together.

Implementation Timeline

The progression from contract approval to large-scale batch hardening

I divided the phases using the code and batch Git history, varying date precision according to the strength of the evidence. Select a phase to see the problem identified at that stage and how it led to the architecture and verification criteria.

Phase 01 · Contract Registration, Review & Approval

Contract Registration, Review & Approval

Phase 01

I physically separated unverified contracts from the operational reward system

A contract submitted by a dApp team could not immediately become a reward target. Only contracts reviewed and approved by an operator could move into operational data and batch inputs.

From Contract Submission to Operational InclusionMermaid

Rendering the diagram.

  1. 01

    Requirements Analysis

    I reframed submission, review, approval, rejection and deletion, verification-code issuance, and approval notification emails as one state-transition problem.

    • Unverified contracts entering reward statistics could corrupt user rewards and leaderboards.
    • State changes had to retain the administrator and reason so that later reviews could trace who made each decision and why.
  2. 02

    Domain Modeling

    I separated application forms, dApp application metadata, contract submissions, review attachments, state-transition logs, and operational dApps and contracts.

    • Application data preserves the facts under review, while operational data contains only approved dApps and contracts.
    • State-transition logs retain before/after states, administrators, and reasons in an append-only manner.
  3. 03

    Architecture Decisions

    I chose separate application and operational tables with a single admin transition entry point instead of a single status flag.

    • Unapproved contracts do not exist in operational tables, so batches cannot read pre-approval data.
    • Verification codes, operational contract creation, logo transfer, and email notification are triggered only at approval.
  4. 04

    Implementation & Verification

    The archived code shows application-registration transactions, approval state transitions, approved function signature filters, duplicate-address blocking, and state-log persistence.

    • Application registration groups file uploads and application persistence into a transactional flow, with compensating deletion of uploaded files on failure.
    • Approval logic creates operational contracts only from enabled contracts and approved functions, rejecting duplicate addresses.
  5. 05

    Operations & Recovery

    Operators identify where processing stopped using the application state, review logs, approved operational contract links, and email send time.

    • Approval emails and logo transfers are post-transaction side effects. Their failure does not roll back approval itself and is tracked through a separate state.
    • Because the original Git history is unavailable, this phase is presented only on the basis of archived code and ADR documentation.

Project Infrastructure

I divided project infrastructure into API, admin, and statistics batch execution boundaries

I distinguished the execution units and external dependencies of the user API, admin service, and statistics batches.

On-Chain Statistics Infrastructure ArchitectureMermaid

Rendering the diagram.

  • I separated approval state into PostgreSQL, large-scale source data and aggregates into MySQL, and duplicate-execution prevention into Redis locks.
  • The user API reads aggregate results; only the Statistics Worker follows source transactions and chain receipts.
  • Approval logs, cursors, locks, and cumulative aggregate values serve as operational checkpoints.

Outcome

Verifiable outcomes

  • Implemented contract registration, review, and approval workflows with audit-logged state transitions
  • Established a data gate admitting only approved contracts and tokens into reward statistics batches
  • Operated incremental batches scanning 670 million+ source transactions with primary-key cursors and bounded search ranges
  • Established resumable batch execution boundaries using statistics job cursors, Redis locks, and self-rescheduling
  • Implemented on-chain Transfer event parsing and the flow through transaction history, game history, and aggregate results

Limitations

Limitations and unimplemented scope

  • The source table size is reported as an approximate 670 million+ rows observed directly in operation. Exact row counts and processing-time logs were not preserved, so I do not use precise figures.
  • I do not infer TPS or daily throughput from code structure alone; actual throughput depended on database performance and data distribution at the time.
  • The original Git history for the submission/approval API and admin areas is not available locally, so those phases are verified only against archived code and ADRs.
  • I use evidence centered on files I created or whose authorship scope can be verified, rather than the team's entire repository.
  • Actual token and transaction data and secrets are not disclosed.