Subgraph Enhancement: Add createdAt timestamp to Account entity

[timfong888]

Problem

The current Filecoin Pay subgraph schema lacks a createdAt timestamp on the Account entity. This prevents us from:

1. Querying accounts by creation date range - Cannot answer "which wallets were created between Jan 1-15?"
2. Backfilling historical new wallet data - Must derive from first rail creation (expensive query)
3. Building cohort analysis - Cannot group users by signup week/month

Current State

The subgraph tracks new accounts via aggregated counters:

• DailyMetric.newAccounts - First-time accounts that day
• WeeklyMetric.newPayers - First-time payers that week

But the Account entity only has:

type Account @entity {
  id: Bytes!
  address: Bytes!
  totalRails: BigInt!
  totalTokens: BigInt!
  totalApprovals: BigInt!
  # NO createdAt field
}

Proposed Solution

Add timestamp fields to the Account entity:

type Account @entity {
  id: Bytes!
  address: Bytes!
  createdAt: BigInt!           # Block timestamp when first seen
  createdAtBlock: BigInt!      # Block number when first seen
  firstPayerRailAt: BigInt     # When they first became a payer (optional)
  firstPayeeRailAt: BigInt     # When they first became a payee (optional)
  totalRails: BigInt!
  totalTokens: BigInt!
  totalApprovals: BigInt!
}

Implementation

In helpers.ts, update createOrLoadAccountByAddress:

export const createOrLoadAccountByAddress = (
  address: Address, 
  timestamp: GraphBN,
  blockNumber: GraphBN
): AccountWithIsNew => {
  let account = Account.load(address);

  if (!account) {
    account = new Account(address);
    account.address = address;
    account.createdAt = timestamp;        // NEW
    account.createdAtBlock = blockNumber; // NEW
    account.totalRails = ZERO_BIG_INT;
    account.totalApprovals = ZERO_BIG_INT;
    account.totalTokens = ZERO_BIG_INT;
    account.save();
    return new AccountWithIsNew(account, true);
  }

  return new AccountWithIsNew(account, false);
};

Use Cases Enabled

1. Query accounts created in date range:

{
  accounts(where: { createdAt_gte: "1704067200", createdAt_lte: "1704672000" }) {
    address
    createdAt
  }
}

2. Backfill historical new wallet metrics for any arbitrary period

3. Cohort analysis - Group users by creation week for retention analysis

Subgraph Location

Source: FilOzone/filecoin-pay-explorer/packages/subgraph

---

Note: This is a tracking issue for a subgraph enhancement. Implementation would be in the FilOzone repo.