> For the complete documentation index, see [llms.txt](https://crowdfunding-ugmbcc.gitbook.io/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://crowdfunding-ugmbcc.gitbook.io/docs/how-to-run/indexer-guide.md).

# Indexer Guide

This guide provides comprehensive instructions for setting up and running the CrowdFUNding blockchain indexer, including its architecture and key components.

### Overview

The indexer is a **Ponder** application that listens to blockchain events in real-time and makes the data queryable via GraphQL and REST APIs. It serves as the bridge between on-chain data and the backend cache.

**Key Responsibilities:**

* Subscribe to blockchain events in real-time
* Index events into a queryable database (SQLite/PostgreSQL)
* Provide GraphQL and REST APIs for data access
* Push data to backend via webhook (real-time sync)
* Serve as fallback data source for backend auto-sync

### Architecture

The indexer follows an event-driven architecture where blockchain events trigger handlers that update the local database and push to backend.

{% @mermaid/diagram content="flowchart TB
subgraph Blockchain\["Base Sepolia"]
Campaign\["Campaign.sol"]
Badge\["Badge.sol"]
end

```
subgraph Ponder["Ponder Indexer"]
    subgraph EventHandlers["Event Handlers"]
        CampaignHandler["src/Campaign.ts"]
        BadgeHandler["src/Badge.ts"]
    end

    subgraph Database["Database"]
        Tables["Tables:\n- campaigns\n- donations\n- withdrawals\n- badges"]
    end

    subgraph APIs["API Layer"]
        GraphQL["GraphQL Endpoint\n:42069/graphql"]
        REST["REST Endpoints\n:42069/api/*"]
    end

    subgraph Sync["Sync Layer"]
        Webhook["Webhook Sync\nsyncBackend.ts"]
    end
end

subgraph Backend["Backend (Express.js)"]
    BackendAPI["API Endpoints\n/api/sync/*"]
    PostgreSQL[(PostgreSQL)]
    AutoSync["Auto-Sync\n(Fallback)"]
end

Campaign --> |"Events"| CampaignHandler
Badge --> |"Events"| BadgeHandler
CampaignHandler --> |"Insert/Update"| Tables
BadgeHandler --> |"Insert/Update"| Tables
CampaignHandler -.->|"Push (PRIMARY)"| Webhook
BadgeHandler -.->|"Push (PRIMARY)"| Webhook
Webhook -->|"HTTP POST\nReal-time"| BackendAPI
BackendAPI --> PostgreSQL

Tables --> GraphQL
Tables --> REST
REST -.->|"Poll (FALLBACK)\nEvery 5 min"| AutoSync
AutoSync --> PostgreSQL

style Webhook fill:#4caf50
style AutoSync fill:#ff9800
style Ponder fill:#e1f5ff
style Backend fill:#fff4e1" %}
```

**Data Flow:**

1. **Blockchain → Ponder:** Events are indexed to local database (SQLite/PostgreSQL)
2. **Ponder → Backend (Webhook - PRIMARY):** Event handlers push data immediately via HTTP
3. **Ponder → Backend (Auto-Sync - FALLBACK):** Backend polls GraphQL API every 5 minutes
4. **Ponder → Clients:** GraphQL/REST APIs available for direct queries (if needed)

#### Key Architectural Decisions

The following table explains the major architectural choices:

| Decision               | Implementation                          | Benefit                                  |
| ---------------------- | --------------------------------------- | ---------------------------------------- |
| **Ponder Framework**   | TypeScript-based indexer                | Hot reload, type safety                  |
| **Event-Driven**       | Handler per event type                  | Clear separation of concerns             |
| **Dual Sync Strategy** | Webhook (push) + GraphQL API (pull)     | Real-time updates + fallback reliability |
| **Webhook-First Sync** | Push to backend on every event          | Real-time data propagation (< 1s)        |
| **GraphQL API**        | Query language for flexible data access | Powerful queries, auto-generated         |
| **REST API**           | Simple HTTP endpoints                   | Easy integration, fallback access        |
| **Portable DB**        | SQLite (dev) / PostgreSQL (prod)        | Easy development, scalable production    |

### Prerequisites

Before setting up the indexer, ensure you have:

* **Node.js** v18.14 or higher
* **npm** or **pnpm**
* An RPC endpoint (Alchemy or public)

### Project Structure

The indexer has a simple, focused structure:

```
├── ponder.config.ts          # Ponder configuration
├── ponder.schema.ts          # Database schema definition
├── ponder-env.d.ts           # TypeScript declarations
├── abis/
│   ├── Campaign.json         # Campaign contract ABI
│   └── Badge.json            # Badge contract ABI
├── src/
│   ├── Campaign.ts           # Campaign event handlers
│   ├── Badge.ts              # Badge event handlers
│   ├── api/
│   │   └── index.ts          # Custom REST endpoints
│   └── utils/
│       └── syncBackend.ts    # Webhook sync to backend
├── tsconfig.json             # TypeScript configuration
└── package.json              # Dependencies
```

### Database Schema

The indexer uses a schema defined in `ponder.schema.ts` that mirrors the on-chain data structure:

{% @mermaid/diagram content="erDiagram
CAMPAIGNS {
integer id PK
text name
text creatorName
hex owner
bigint balance
bigint targetAmount
bigint creationTime
}

```
DONATIONS {
    text id PK "txHash-logIndex"
    integer campaignId FK
    hex donor
    bigint amount
    bigint blockNumber
    bigint timestamp
    hex transactionHash
}

WITHDRAWALS {
    text id PK
    integer campaignId FK
    hex owner
    bigint amount
    bigint timestamp
    hex transactionHash
}

BADGES {
    integer tokenId PK
    hex owner
    text name
    bigint timestamp
    hex transactionHash
}

CAMPAIGNS ||--o{ DONATIONS : "receives"
CAMPAIGNS ||--o{ WITHDRAWALS : "has"" %}
```

#### Schema Definition

The following code shows how the schema is defined:

```typescript
// ponder.schema.ts
import { onchainTable, index, relations } from "ponder";

export const campaigns = onchainTable(
  "campaigns",
  (t) => ({
    id: t.integer().primaryKey(),
    name: t.text().notNull(),
    creatorName: t.text().notNull(),
    owner: t.hex().notNull(),
    balance: t.bigint().notNull(),
    targetAmount: t.bigint().notNull(),
    creationTime: t.bigint().notNull(),
  }),
  (table) => ({
    ownerIndex: index().on(table.owner),
  }),
);

export const donations = onchainTable(
  "donations",
  (t) => ({
    id: t.text().primaryKey(), // txHash-logIndex
    campaignId: t.integer().notNull(),
    donor: t.hex().notNull(),
    amount: t.bigint().notNull(),
    blockNumber: t.bigint().notNull(),
    timestamp: t.bigint().notNull(),
    transactionHash: t.hex().notNull(),
  }),
  (table) => ({
    campaignIdIndex: index().on(table.campaignId),
    donorIndex: index().on(table.donor),
  }),
);
```

### Environment Setup

Create a `.env.local` file in the project root:

```env
# ============================
# Blockchain RPC
# ============================
PONDER_RPC_URL_1=https://sepolia.base.org

# For better performance, use Alchemy:
# PONDER_RPC_URL_1=https://base-sepolia.g.alchemy.com/v2/YOUR_API_KEY

# ============================
# Backend Integration (Webhook Sync)
# ============================
BACKEND_URL=http://localhost:3300
PONDER_SYNC_API_KEY=your-sync-api-key

# ============================
# Database (Optional for Production)
# ============================
# By default, Ponder uses SQLite for development
# For production, configure PostgreSQL:
# DATABASE_URL=postgresql://user:password@host:5432/ponder
```

#### How to Get Environment Keys

| Variable                   | How to Obtain                                                     |
| -------------------------- | ----------------------------------------------------------------- |
| **PONDER\_RPC\_URL\_1**    | [Alchemy](https://alchemy.com) → Create App → Select Base Sepolia |
|                            | [Infura](https://infura.io) → Create Key → Enable Base            |
|                            | Or use public: `https://sepolia.base.org` (rate-limited)          |
| **BACKEND\_URL**           | Your backend server URL (e.g., `http://localhost:3300`)           |
| **PONDER\_SYNC\_API\_KEY** | Generate a secure random string for webhook authentication        |
| **DATABASE\_URL**          | PostgreSQL connection string for production                       |

### Installation

#### Step 1: Install Dependencies

```bash
npm install
# or
pnpm install
```

#### Step 2: Generate Types (Optional)

Generate TypeScript types from your schema:

```bash
npm run codegen
```

### Running the Indexer

#### Development Mode

Start the indexer with hot-reload:

```bash
npm run dev
```

The indexer will:

1. Connect to Base Sepolia RPC
2. Start indexing from the configured start block
3. Listen for new events in real-time
4. Serve APIs at `http://localhost:42069`

#### Production Mode

```bash
npm run start
```

### Event Handlers

Event handlers are the core of the indexer. Each handler processes a specific event type and updates the database accordingly.

#### Campaign Events

The following handlers process Campaign contract events:

```typescript
// src/Campaign.ts
import { ponder } from "@/generated";

// Handle new campaign creation
ponder.on("Campaign:CampaignCreated", async ({ event, context }) => {
  const { db } = context;

  await db.campaigns.insert({
    id: Number(event.args.id),
    name: event.args.name,
    creatorName: event.args.creatorName,
    owner: event.args.owner,
    balance: BigInt(0),
    targetAmount: event.args.targetAmount,
    creationTime: event.args.creationTime,
  });
});

// Handle donations
ponder.on("Campaign:DonationReceived", async ({ event, context }) => {
  const { db } = context;

  // Create unique donation ID from transaction
  const donationId = `${event.transaction.hash}-${event.log.logIndex}`;

  // Insert donation record
  await db.donations.insert({
    id: donationId,
    campaignId: Number(event.args.id),
    donor: event.args.donor,
    amount: event.args.amount,
    blockNumber: event.block.number,
    timestamp: event.block.timestamp,
    transactionHash: event.transaction.hash,
  });

  // Update campaign balance
  await db.campaigns.update({
    id: Number(event.args.id),
    balance: (prev) => prev.balance + event.args.amount,
  });
});

// Handle withdrawals
ponder.on("Campaign:FundWithdrawn", async ({ event, context }) => {
  const { db } = context;

  await db.withdrawals.insert({
    id: `${event.transaction.hash}-${event.log.logIndex}`,
    campaignId: Number(event.args.id),
    owner: event.args.owner,
    amount: event.args.amount,
    timestamp: event.block.timestamp,
    transactionHash: event.transaction.hash,
  });

  // Update campaign balance
  await db.campaigns.update({
    id: Number(event.args.id),
    balance: (prev) => prev.balance - event.args.amount,
  });
});
```

#### Badge Events

```typescript
// src/Badge.ts
import { ponder } from "@/generated";

ponder.on("Badge:BadgeMinted", async ({ event, context }) => {
  const { db } = context;

  await db.badges.insert({
    tokenId: Number(event.args.tokenId),
    owner: event.args.owner,
    name: event.args.name,
    timestamp: event.block.timestamp,
    transactionHash: event.transaction.hash,
  });
});
```

### API Endpoints

The indexer automatically provides both GraphQL and REST APIs based on your schema.

#### GraphQL Playground

Access the GraphQL playground at `http://localhost:42069` to explore and test queries.

#### REST Endpoints

The following REST endpoints are automatically generated:

| Method | Endpoint                       | Description        |
| ------ | ------------------------------ | ------------------ |
| GET    | `/health`                      | Health check       |
| GET    | `/api/campaigns`               | List all campaigns |
| GET    | `/api/campaigns/:id`           | Get campaign by ID |
| GET    | `/api/donations`               | List all donations |
| GET    | `/api/donations/user/:address` | User's donations   |
| GET    | `/api/badges`                  | List all badges    |
| GET    | `/api/badges/user/:address`    | User's badges      |

### GraphQL Examples

#### Get All Campaigns

The following query retrieves the most recent campaigns with pagination:

```graphql
query GetCampaigns {
  campaigns(orderBy: "creationTime", orderDirection: "desc", first: 10) {
    items {
      id
      name
      creatorName
      owner
      balance
      targetAmount
      creationTime
    }
    pageInfo {
      hasNextPage
      endCursor
    }
  }
}
```

#### Get Campaign with Donations

This query fetches a specific campaign along with its donation history:

```graphql
query GetCampaignDetails($id: Int!) {
  campaign(id: $id) {
    id
    name
    creatorName
    balance
    targetAmount
    donations(orderBy: "timestamp", orderDirection: "desc", first: 10) {
      items {
        id
        donor
        amount
        timestamp
        transactionHash
      }
    }
  }
}
```

#### Get User's Donation History

This query retrieves all donations made by a specific user:

```graphql
query GetUserDonations($donor: String!) {
  donations(
    where: { donor: $donor }
    orderBy: "timestamp"
    orderDirection: "desc"
  ) {
    items {
      id
      campaignId
      amount
      timestamp
      campaign {
        name
        creatorName
      }
    }
  }
}
```

#### Get Platform Statistics

This query retrieves overall platform metrics:

```graphql
query GetStats {
  campaignCount: campaigns(first: 0) {
    totalCount
  }
  donationCount: donations(first: 0) {
    totalCount
  }
  badgeCount: badges(first: 0) {
    totalCount
  }
}
```

### Configuration

#### ponder.config.ts

The configuration file defines which contracts to index and how:

```typescript
import { createConfig } from "ponder";
import { http } from "viem";

import { CampaignAbi } from "./abis/Campaign";
import { BadgeAbi } from "./abis/Badge";

export default createConfig({
  networks: {
    baseSepolia: {
      chainId: 84532,
      transport: http(process.env.PONDER_RPC_URL_1),
    },
  },
  contracts: {
    Campaign: {
      network: "baseSepolia",
      abi: CampaignAbi,
      address: "0x44e87aa98d721Dbcf368690bF5aAb1F3dD944dA9",
      startBlock: 12345678, // Deploy block
    },
    Badge: {
      network: "baseSepolia",
      abi: BadgeAbi,
      address: "0xaE32Df9Fb677aE68C5A1F956761a42e269Ebdc99",
      startBlock: 12345678,
    },
  },
});
```

### Deployment

#### Railway

Railway provides easy deployment for Ponder:

1. Connect your GitHub repository
2. Set environment variables:
   * `PONDER_RPC_URL_1`
   * `DATABASE_URL` (if using PostgreSQL)
3. Configure:
   * Build: `npm install && npm run build`
   * Start: `npm run start`

### Integration with Backend

The indexer integrates with the backend using a **dual synchronization strategy**:

#### 1. Webhook Sync (PRIMARY - Real-time)

The indexer **pushes data to the backend** immediately when events occur via webhook:

**File:** `indexer/src/utils/syncBackend.ts`

```typescript
// Webhook pushes data to backend in real-time
await axios.post(
  `${BACKEND_URL}/api/sync/campaigns`,
  {
    campaign: newCampaign,
  },
  {
    headers: { "x-api-key": PONDER_SYNC_API_KEY },
  },
);
```

**Configuration in backend's `.env`:**

```env
BACKEND_URL=http://localhost:3300
PONDER_SYNC_API_KEY=your-sync-api-key
```

#### 2. Auto-Sync (FALLBACK - Every 5 minutes)

The backend **polls the indexer** every 5 minutes as a fallback mechanism to catch any missed events:

**Configuration in backend's `.env`:**

```env
PONDER_URL=http://localhost:42069
SYNC_INTERVAL_MS=300000  # 5 minutes
# Or production URL:
# PONDER_URL=https://indexer.crowdfunding.example.com
```

#### Sync Flow Diagram

{% @mermaid/diagram content="sequenceDiagram
participant Blockchain as Base Sepolia
participant Ponder as Ponder Indexer
participant Webhook as Webhook (PRIMARY)
participant AutoSync as Auto-Sync (FALLBACK)
participant Backend as Backend API
participant DB as PostgreSQL

```
Note over Blockchain,DB: Real-time Path (< 1 second)
Blockchain->>Ponder: Event emitted
Ponder->>Ponder: Index to SQLite
Ponder->>Webhook: Push immediately
Webhook->>Backend: POST /api/sync/*
Backend->>DB: Insert/Update

Note over Blockchain,DB: Fallback Path (Every 5 minutes)
loop Every 5 minutes
    AutoSync->>Ponder: Query GraphQL API
    Ponder-->>AutoSync: All data
    AutoSync->>Backend: Process data
    Backend->>DB: Upsert (catch missed events)
end" %}
```

**Why Dual Sync?**

| Mechanism     | Purpose                | Frequency       | Data Volume   |
| ------------- | ---------------------- | --------------- | ------------- |
| **Webhook**   | Real-time updates      | Event-driven    | Only new data |
| **Auto-Sync** | Recovery & consistency | Every 5 minutes | All data      |

This ensures:

* ✅ **Real-time updates** via webhook (99% of syncs)
* ✅ **Data consistency** via auto-sync fallback (catches missed events)
* ✅ **Resource efficiency** (5-minute interval instead of continuous polling)
* ✅ **Automatic recovery** from network failures

### Troubleshooting

#### Indexer Not Starting

**Error:** `Failed to connect to RPC`

**Solution:** Verify `PONDER_RPC_URL_1` is valid and accessible.

#### Missing Events

**Warning:** `Block not found`

**Solution:** Ensure `startBlock` in config matches the contract deployment block.

#### Database Errors

**Error:** `SQLITE_CANTOPEN`

**Solution:** Ensure write permissions in the project directory.

#### GraphQL Errors

**Error:** `Field not found`

**Solution:** Run `npm run codegen` to regenerate types after schema changes.

### Performance Tips

The following practices improve indexer performance:

| Tip                          | Explanation                        |
| ---------------------------- | ---------------------------------- |
| Use Alchemy/Infura           | Public RPC is rate-limited         |
| Set accurate startBlock      | Skip unnecessary historical blocks |
| Use PostgreSQL in production | Better performance than SQLite     |
| Add database indexes         | Speed up frequent queries          |
| Use pagination               | Avoid loading large datasets       |