proxmox/docs/04-configuration/ACTIVITY_FEED_SPEC.md

# Activity Feed — Event Schema and Ingestion

**Purpose:** Canonical event model and ingestion spec for the normalized activity feed: transfers, app events, and bridge stitching. Table: `activity_events` (migration 0014).

**References:** [indexer-architecture.md](../../explorer-monorepo/docs/specs/indexing/indexer-architecture.md), [heatmap-chains.ts](../../smom-dbis-138/services/token-aggregation/src/config/heatmap-chains.ts) (ALT vs B/SBS), [cross-chain-bridges.ts](../../smom-dbis-138/services/token-aggregation/src/config/cross-chain-bridges.ts) (`getRouteFromRegistry`).

---

## 1. Table: `activity_events`

| Column | Type | Description |
|--------|------|-------------|
| `id` | uuid | Primary key (default gen_random_uuid()) |
| `chain_id` | integer | 138 or 651940 (and others when indexed) |
| `transaction_hash` | varchar(66) | Tx hash |
| `log_index` | integer | Log index (0 for tx-level) |
| `block_number` | bigint | Block number |
| `block_timestamp` | timestamptz | Block time |
| `actor` | varchar(42) | Wallet that initiated the action |
| `subject` | varchar(42) | Optional: user/account/tokenId/resource |
| `event_type` | varchar(32) | TRANSFER, APP_ACTION, CLAIM, BRIDGE_OUT, BRIDGE_IN |
| `contract_address` | varchar(42) | Contract that emitted or was called |
| `data` | jsonb | Parsed event fields |
| `routing` | jsonb | `{ "path": "ALT" | "CCIP", "fromChain", "toChain", "bridgeTxHash"?: string }` |
| `created_at` | timestamptz | Insert time |

**Unique:** `(chain_id, transaction_hash, log_index)`.

---

## 2. Ingestion

### 2.1 Transfers

- **Source:** Existing `token_transfers` (and ERC-721/1155 logs when indexed).
- **Mapping:** For each row: insert into `activity_events` with `event_type = 'TRANSFER'`, `actor = from_address`, `subject = to_address` (or token id for NFT), `data = { from, to, value, tokenContract }`, `contract_address = token_contract`. `routing` = NULL for same-chain transfers.
- **Backfill:** Migration `0015_activity_events_backfill_from_token_transfers.up.sql` runs a one-time `INSERT ... SELECT` from `token_transfers` with `ON CONFLICT DO NOTHING`. Run after 0014.
- **Real-time:** The Track 2 token indexer (`explorer-monorepo/backend/indexer/track2/token_indexer.go`) inserts into `activity_events` on each new token transfer (same row as `token_transfers`), so the feed stays current without a separate job.

### 2.2 App events

- **Source:** Application-lifecycle events (create, complete, settle, redeem, etc.) from your contracts.
- **Registry:** Maintain a mapping (event signature → `event_type` + parser). Example: `0x...` → `APP_ACTION`, parse `data` from log topics/data.
- **Insert:** Decode log; set `event_type`, `actor` (e.g. tx from), `subject` (e.g. orderId), `data` (decoded fields), `contract_address`.

### 2.3 Bridge stitching

- **Source:** Bridge contracts (AlltraAdapter, CCIP WETH9/WETH10); events such as lock/burn on source, mint/release on destination.
- **Routing:** Use [getRouteFromRegistry](../../smom-dbis-138/services/token-aggregation/src/config/cross-chain-bridges.ts) or [config/routing-registry.json](../../config/routing-registry.json): 138↔651940 → `path: "ALT"`, 138↔others → `path: "CCIP"`.
- **Insert:** For each bridge event, set `routing = { path: "ALT"|"CCIP", fromChain, toChain, bridgeTxHash }`. Optionally correlate "bridge out" and "bridge in" with a shared `data.correlationId` so the API can return one stitched feed item per cross-chain move.

---

## 3. Activity feed API

**Endpoint:** `GET /api/v1/track2/activity` (Track 2 auth required). Implemented in `explorer-monorepo/backend/api/track2/endpoints.go` (`HandleActivityFeed`).

**Query params:**

- `address` — filter by actor or subject (user feed).
- `event_type` — filter by event type (e.g. `TRANSFER`, `APP_ACTION`).
- `chain_id` — filter by chain (default: server chain).
- `page`, `limit` (default 50, max 100) — pagination.

**Queries:**

- **By user:** `address=0x...` → `WHERE actor = $address OR subject = $address` (paginated).
- **By token/NFT:** Use `event_type=TRANSFER` and `contract_address` or filter client-side by `data.tokenContract` / `data.tokenId`.
- **Global:** Omit `address`; optional `event_type`; pagination by `(block_timestamp DESC, id DESC)`.

**Pagination:** Page/limit (offset-based); limit 50 per page, max 100.

**Example (by user):**

```sql
SELECT * FROM activity_events
WHERE actor = $1 OR subject = $1
ORDER BY block_timestamp DESC, id DESC
LIMIT 50 OFFSET $2;
```

---

## 4. Event type enum (logical)

| event_type | Description |
|------------|-------------|
| TRANSFER | ERC-20/721/1155 transfer |
| APP_ACTION | App-lifecycle (create, complete, settle, etc.) |
| CLAIM | Claim/mint from drop or contract |
| BRIDGE_OUT | Lock/burn on source chain |
| BRIDGE_IN | Mint/release on destination chain |

---

## 5. Migrations

- **0014 — table:** [0014_activity_events.up.sql](../../explorer-monorepo/backend/database/migrations/0014_activity_events.up.sql) (down: `0014_activity_events.down.sql`).
- **0015 — backfill:** [0015_activity_events_backfill_from_token_transfers.up.sql](../../explorer-monorepo/backend/database/migrations/0015_activity_events_backfill_from_token_transfers.up.sql): one-time backfill from `token_transfers`; down is no-op.

Run with your existing migration runner (e.g. golang-migrate, node-pg-migrate) against the explorer/backend DB.