Open-source platform for 3D AI agents. Turn text or a photo into a rigged, animated GLB avatar, give it an LLM brain, memory and a wallet, and embed it anywhere with one web component. Remote MCP server, x402 payments, Three.js, browser-native.
JavaScript 3d-generationai-agentsanimationavatardigital-humangenerative-aiglbgltfimage-to-3dllmmcpmcp-servermodel-context-protocolsolanatext-to-3dthreejsweb-componentswebglwebxrx402
// GET /api/pulse — the Money Pulse: a real, platform-wide stream of public
// wallet activity across every three.ws agent.
//
// EVERY row is a real event with an explorer-verifiable signature (or a real
// launch record). There are NO synthetic events — if the platform is quiet, the
// feed is honestly empty.
//
// Sources (all real):
// · agent_custody_events — tips received (event_type='tip'), trades + snipes
// (event_type='spend', category in 'trade'/'snipe'), and agent-to-agent
// payments (category='x402'). These are already-public, on-chain movements.
// · pump_agent_mints — coins launched by an agent.
// · skill_purchases — real marketplace purchases (skill / time-pass) confirmed
// on-chain and settled in $THREE. Surfaced as 'purchase' beats the instant a
// sale lands, straight from the authoritative purchase ledger.
//
// Privacy is load-bearing (see prompts/agent-wallets/07): private withdrawals,
// spend-limit changes, key-recovery and vanity-swap custody events are OWNER-ONLY
// and NEVER appear here. The global feed also honours each agent's is_public flag
// and the per-agent `meta.pulse_opt_out` toggle (owner-controlled, server-side).
//
// Views:
// GET /api/pulse — global live feed (keyset paginated)
// GET /api/pulse?since=<cursor> — delta poll: only events newer than cursor
// GET /api/pulse?agent_id=<id> — one agent's public "wallet story"
// GET /api/pulse?view=stats — aggregate money intelligence
// GET /api/pulse?view=marketplace — marketplace commerce viability metrics
// GET /api/pulse?view=trading — trading viability: activity, cost, realized P&L
// GET /api/pulse?view=event-markets : Event Markets: live count, biggest mover, closing soon
// GET /api/pulse?view=graph&window=… — money-flow topology: who paid whom, for what
// GET /api/pulse?view=agent-summary&agent_id=<id> — one wallet's lifetime summary
//
// Filters: type=all|tips|launches|trades|payments, network=mainnet|devnet.
import { sql, isDbUnavailableError } from './_lib/db.js';
import { cors, json, method, error, serverError, rateLimited } from './_lib/http.js';
import { limits, clientIp } from './_lib/rate-limit.js';
import { isUuid } from './_lib/validate.js';
import { thumbnailUrl } from './_lib/r2.js';
import { explorerTxUrl, explorerAccountUrl } from './_lib/avatar-wallet.js';
import { cacheGet, cacheSet } from './_lib/cache.js';
import { marketplaceFeeBps } from './_lib/marketplace-platform-fee.js';
import { shapeTradingWindow, shapeTradingPnl, shapeTradingSeries, solFromLamports } from './_lib/pulse-trading.js';
import { MARKET_PAID_KINDS } from './_lib/marketplace-kinds.js';
import { pulseEventMarkets } from './_lib/event-markets/live-stats.js';
// The custody categories that are safe to surface publicly. Everything else
// (withdraw, vanity_swap, limit_change, key_recover) is owner-private and is
// excluded by the WHERE clauses below — never add those here.
const PUBLIC_SPEND_CATEGORIES = ['trade', 'snipe', 'x402'];
// The live FEED additionally surfaces marketplace skill purchases (category
// 'marketplace') as 'purchase' beats — real, value-delivering commerce. This is a
// feed-only superset: the aggregate money math above stays on PUBLIC_SPEND_CATEGORIES
// so marketplace's $THREE-denominated rows never distort the SOL volume counters.
const FEED_SPEND_CATEGORIES = [...PUBLIC_SPEND_CATEGORIES, 'marketplace'];
// $THREE is the only coin the marketplace prices in. Real skill purchases settle
// in $THREE (6 decimals), so marketplace GMV is denominated here. A purchase is
// "paid" only when status='confirmed' and it's not a free trial.
const THREE_MINT = process.env.THREE_TOKEN_MINT || 'FeMbDoX7R1Psc4GEcvJdsbNbZA3bfztcyDCatJVJpump';
const THREE_DECIMALS = 6;
// atomic $THREE → whole-token float (keeps the fractional part Number-division gives).
const threeFromAtomic = (atomic) => Number(atomic || 0) / 10 ** THREE_DECIMALS;
// type= query param → the set of `kind`s it admits.
const TYPE_KINDS = {
all: ['tip', 'trade', 'snipe', 'payment', 'purchase', 'launch'],
tips: ['tip'],
trades: ['trade', 'snipe'],
payments: ['payment'],
purchases: ['purchase'],
launches: ['launch'],
};
const FEED_TTL_S = 12; // global feed page cache (short — it's a live feed)
const STATS_TTL_S = 45; // aggregate stats cache
const MAX_LIMIT = 60;
// Keyset cursor = base64("<epoch_ms>|<row_id>"). row_id is the unified id of the
// last row on a page; comparing (ts, row_id) gives a stable total order across
// the UNION so pagination never skips or duplicates a row.
function encodeCursor(tsIso, rowId) {
const ms = new Date(tsIso).getTime();
return Buffer.from(`${ms}|${rowId}`, 'utf8').toString('base64url');
}
function decodeCursor(raw) {
if (!raw || typeof raw !== 'string') return null;
try {
const [ms, rowId] = Buffer.from(raw, 'base64url').toString('utf8').split('|');
const n = Number(ms);
if (!Number.isFinite(n) || !rowId) return null;
return { ts: new Date(n).toISOString(), rowId };
} catch {
return null;
}
}
const r2Url = (key, visible) =>
key && visible ? thumbnailUrl(key) : null;
// Shape one DB row into the public pulse event the client renders. Pure.
function shapeEvent(r) {
const avatarPublic = r.avatar_vis === 'public' || r.avatar_vis === 'unlisted';
const amountLamports = r.amount_lamports != null ? String(r.amount_lamports) : null;
const amountRaw = r.amount_raw != null ? String(r.amount_raw) : null;
const sol = amountLamports != null ? Number(amountLamports) / 1e9 : null;
const usd = r.usd != null ? Number(r.usd) : null;
const sig = r.signature || null;
return {
id: r.row_id,
kind: r.kind, // tip | trade | snipe | payment | launch
ts: r.ts,
network: r.network,
agent: r.agent_id
? {
id: r.agent_id,
name: r.agent_name || 'Agent',
url: `/agents/${r.agent_id}`,
avatar_thumbnail_url: r2Url(r.thumb_key, avatarPublic),
solana_address: r.agent_addr || null,
solana_vanity_prefix: r.vanity_prefix || null,
solana_vanity_suffix: r.vanity_suffix || null,
}
: null,
asset: r.asset || null,
amount_lamports: amountLamports,
amount_raw: amountRaw,
sol,
usd,
signature: sig,
explorer: sig ? explorerTxUrl(sig, r.network) : null,
// launch-only fields
mint: r.mint || null,
symbol: r.symbol || null,
coin_name: r.coin_name || null,
// Trade direction, derived from which leg the agent spent: a BUY spends the
// SOL/USDC/THREE quote leg (asset != mint), a SELL spends the token itself
// (asset == mint). Only meaningful for coin-moving trade/snipe rows; null
// otherwise. Lets a coin page colour each marker without an extra column.
side: (r.kind === 'trade' || r.kind === 'snipe') && r.mint
? (r.asset === r.mint ? 'sell' : 'buy')
: null,
skill: r.skill || null, // marketplace 'purchase' beats carry the skill name
mint_explorer: r.mint ? explorerAccountUrl(r.mint, r.network) : null,
// the counterparty of a tip / payment, when public on-chain
counterparty: r.counterparty || null,
};
}
// ── feed view ───────────────────────────────────────────────────────────────
async function handleFeed(req, res, { network, type, agentId, mint, cursor, since }) {
const kinds = TYPE_KINDS[type] || TYPE_KINDS.all;
const limit = Math.min(MAX_LIMIT, Math.max(1, Number(new URL(req.url, 'http://x').searchParams.get('limit')) || 30));
// Privacy gate. A private agent never appears — in the global feed OR scoped to
// its own id (err toward privacy; its owner still has the full private custody
// trail elsewhere). The per-agent `pulse_opt_out` toggle additionally suppresses
// an agent from the GLOBAL discovery feed only — its own profile/HUD still shows
// its already-public on-chain history. Deleted agents are always out.
const visGate = agentId
? sql`ai.deleted_at IS NULL AND ai.is_public = true`
: sql`ai.deleted_at IS NULL AND ai.is_public = true AND COALESCE((ai.meta->>'pulse_opt_out')::boolean, false) = false`;
const agentFilterCe = agentId ? sql`AND ce.agent_id = ${agentId}` : sql``;
const agentFilterPam = agentId ? sql`AND pam.agent_id = ${agentId}` : sql``;
const agentFilterSp = agentId ? sql`AND sp.agent_id = ${agentId}` : sql``;
// One UNION ALL of identical columns. Casts on the launch side make the null
// columns type-compatible with the custody side.
const feedCte = sql`
SELECT
ce.created_at AS ts,
(CASE
WHEN ce.event_type = 'tip' THEN 'tip'
WHEN ce.category = 'x402' THEN 'payment'
WHEN ce.category = 'snipe' THEN 'snipe'
WHEN ce.category = 'marketplace' THEN 'purchase'
ELSE 'trade'
END) AS kind,
'c' || ce.id::text AS row_id,
ce.network AS network,
ai.id AS agent_id,
ai.name AS agent_name,
ai.meta->>'solana_address' AS agent_addr,
ai.meta->>'solana_vanity_prefix' AS vanity_prefix,
ai.meta->>'solana_vanity_suffix' AS vanity_suffix,
av.thumbnail_key AS thumb_key,
av.visibility AS avatar_vis,
ce.asset AS asset,
ce.amount_lamports AS amount_lamports,
ce.amount_raw AS amount_raw,
ce.usd AS usd,
ce.signature AS signature,
tk.mint AS mint,
COALESCE(tm.symbol, ptok.symbol) AS symbol,
COALESCE(tm.name, ptok.name) AS coin_name,
NULLIF(ce.meta->>'skill', '') AS skill,
NULLIF(ce.meta->>'from', '') AS counterparty
FROM agent_custody_events ce
JOIN agent_identities ai ON ai.id = ce.agent_id AND ${visGate}
LEFT JOIN avatars av ON av.id = ai.avatar_id AND av.deleted_at IS NULL
-- Resolve the traded token once: it lives in meta.mint (buys + sells) and,
-- for sells, additionally in the asset column (which holds the mint). SOL /
-- USDC / THREE assets are the quote/settlement leg, not a traded token.
LEFT JOIN LATERAL (
SELECT COALESCE(
NULLIF(ce.meta->>'mint', ''),
CASE WHEN ce.asset NOT IN ('SOL', 'USDC', 'THREE') THEN ce.asset END
) AS mint
) tk ON true
-- Name the mint: general Helius/Jupiter cache first, then our own launches.
-- token_metadata.mint is a PK and pump_agent_mints is unique on (mint,
-- network), so neither join can fan the row out.
LEFT JOIN token_metadata tm ON tm.mint = tk.mint
LEFT JOIN pump_agent_mints ptok ON ptok.mint = tk.mint AND ptok.network = ce.network
WHERE ce.network = ${network}
AND ce.status IN ('ok', 'confirmed')
AND (
ce.event_type = 'tip'
OR (ce.event_type = 'spend' AND ce.category = ANY(${FEED_SPEND_CATEGORIES}))
)
${agentFilterCe}
UNION ALL
SELECT
pam.created_at AS ts,
'launch' AS kind,
'l' || pam.id::text AS row_id,
pam.network AS network,
ai.id AS agent_id,
ai.name AS agent_name,
ai.meta->>'solana_address' AS agent_addr,
ai.meta->>'solana_vanity_prefix' AS vanity_prefix,
ai.meta->>'solana_vanity_suffix' AS vanity_suffix,
av.thumbnail_key AS thumb_key,
av.visibility AS avatar_vis,
NULL::text AS asset,
NULL::bigint AS amount_lamports,
NULL::numeric AS amount_raw,
NULL::numeric AS usd,
NULL::text AS signature,
pam.mint AS mint,
pam.symbol AS symbol,
pam.name AS coin_name,
NULL::text AS skill,
NULL::text AS counterparty
FROM pump_agent_mints pam
JOIN agent_identities ai ON ai.id = pam.agent_id AND ${visGate}
LEFT JOIN avatars av ON av.id = ai.avatar_id AND av.deleted_at IS NULL
WHERE pam.network = ${network}
${agentFilterPam}
UNION ALL
-- Real marketplace purchases (skill / time-pass) confirmed on-chain and
-- settled in $THREE on Solana mainnet. Read straight from skill_purchases,
-- the same authoritative ledger the marketplace stats reconcile against, so a
-- confirmed sale becomes a 'purchase' beat the moment it lands, not only when
-- the synthetic circulation loop happens to be running. Free trials are
-- excluded (MARKET_PAID_KINDS): a beat means value actually changed hands.
-- The earning SELLER agent fronts the row, mirroring the top-earners math.
-- These rows are always Solana mainnet, so the branch yields nothing on the
-- devnet feed.
SELECT
COALESCE(sp.confirmed_at, sp.created_at) AS ts,
'purchase' AS kind,
'p' || sp.id::text AS row_id,
'mainnet'::text AS network,
ai.id AS agent_id,
ai.name AS agent_name,
ai.meta->>'solana_address' AS agent_addr,
ai.meta->>'solana_vanity_prefix' AS vanity_prefix,
ai.meta->>'solana_vanity_suffix' AS vanity_suffix,
av.thumbnail_key AS thumb_key,
av.visibility AS avatar_vis,
'THREE'::text AS asset,
NULL::bigint AS amount_lamports,
sp.amount::numeric AS amount_raw,
NULL::numeric AS usd,
sp.tx_signature AS signature,
NULL::text AS mint,
NULL::text AS symbol,
NULL::text AS coin_name,
sp.skill AS skill,
NULL::text AS counterparty
FROM skill_purchases sp
JOIN agent_identities ai ON ai.id = sp.agent_id AND ${visGate}
LEFT JOIN avatars av ON av.id = ai.avatar_id AND av.deleted_at IS NULL
WHERE ${network} = 'mainnet'
AND sp.status = 'confirmed'
AND sp.kind = ANY(${MARKET_PAID_KINDS})
AND sp.currency_mint = ${THREE_MINT}
AND sp.chain = 'solana'
${agentFilterSp}
`;
// Keyset bounds. `since` (delta poll) returns only rows strictly newer than
// the cursor; `cursor` (load-older) returns rows strictly older. Both compare
// on (ts, row_id) so ties at the same timestamp never skip a row.
const cur = decodeCursor(cursor);
const sinceCur = decodeCursor(since);
const olderBound = cur
? sql`AND (feed.ts < ${cur.ts} OR (feed.ts = ${cur.ts} AND feed.row_id < ${cur.rowId}))`
: sql``;
const newerBound = sinceCur
? sql`AND (feed.ts > ${sinceCur.ts} OR (feed.ts = ${sinceCur.ts} AND feed.row_id > ${sinceCur.rowId}))`
: sql``;
// Coin-scoped feed: every agent transaction in one token, newest first. The
// unified `feed.mint` column already resolves the traded mint on every branch
// (trade/snipe via meta.mint or the asset leg, launch via pump_agent_mints), so
// one equality narrows the whole UNION to a single coin. Powers the "agent
// transactions" panel + chart markers on the coin detail page.
const mintFilter = mint ? sql`AND feed.mint = ${mint}` : sql``;
const rows = await sql`
WITH feed AS (${feedCte})
SELECT * FROM feed
WHERE feed.kind = ANY(${kinds})
${mintFilter}
${olderBound}
${newerBound}
ORDER BY feed.ts DESC, feed.row_id DESC
LIMIT ${limit + 1}
`;
const hasMore = rows.length > limit;
const page = rows.slice(0, limit).map(shapeEvent);
const last = page[page.length - 1];
const nextCursor = hasMore && last ? encodeCursor(last.ts, last.id) : null;
// The newest row's cursor — clients hand it back as `since` to poll the delta.
const headCursor = page[0] ? encodeCursor(page[0].ts, page[0].id) : (sinceCur ? since : null);
return {
events: page,
has_more: hasMore,
next_cursor: nextCursor,
head_cursor: headCursor,
network,
type,
agent_id: agentId || null,
mint: mint || null,
};
}
// ── money-flow graph view ─────────────────────────────────────────────────────
// The TOPOLOGY of the agent economy: who paid whom, for what, and how much.
//
// Every other economy surface here is a leaderboard: totals per agent, ranked.
// A ranking cannot show the shape of the flow, and the shape is what diagnoses
// the platform's real failure mode. Capital dispersion looks perfectly healthy
// on a leaderboard (busy agents, real volume) while the money is in fact
// draining one-way into wallets that receive and never spend again. On a graph
// that is a glance: a node with inbound edges and no outbound ones.
//
// Two edge sources, both already-public on-chain movements with an
// explorer-verifiable signature. There is no third "estimated" or "inferred"
// edge type; if the platform is quiet the graph is honestly empty.
//
// · agent-to-agent x402 payments — the row's agent SPENT, meta.to is the payee
// wallet and meta.service names the skill that was bought.
// · tips — the row's agent RECEIVED, meta.from is the payer wallet.
//
// Marketplace purchases are deliberately excluded: they settle in $THREE from a
// human buyer, so they are not an agent-to-agent edge and would silently mix a
// second denomination into the USD flow totals.
//
// Privacy: identical gate to the live feed on the side we own (no deleted, no
// private, no pulse_opt_out). A counterparty wallet is named ONLY when it
// resolves to a public agent; anything else stays an address-only node, which is
// exactly what the feed already exposes as `counterparty`.
const GRAPH_WINDOWS = { '24h': '24 hours', '7d': '7 days', '30d': '30 days', '90d': '90 days' };
const GRAPH_TTL_S = 60;
// Aggregated edges are ranked by value, so the cap keeps the biggest flows. When
// it bites we say so in the payload rather than presenting a truncated graph as
// the whole economy.
const GRAPH_MAX_EDGES = 400;
const shortAddr = (a) => (a && a.length > 12 ? `${a.slice(0, 4)}…${a.slice(-4)}` : a || '');
async function handleGraph(network, windowKey) {
const interval = GRAPH_WINDOWS[windowKey] || GRAPH_WINDOWS['30d'];
const pubAgent = sql`deleted_at IS NULL AND is_public = true
AND COALESCE((meta->>'pulse_opt_out')::boolean, false) = false`;
// One directed-transfer CTE, then aggregate to edges. Counterparty resolution
// runs through a LATERAL with LIMIT 1 so two identities sharing a wallet can
// never fan a single payment into two edges.
const rows = await sql`
WITH transfers AS (
SELECT
ce.created_at AS ts,
'payment'::text AS kind,
payer.id::text AS from_agent,
NULL::text AS from_addr,
payee.id AS to_agent,
ce.meta->>'to' AS to_addr,
ce.amount_lamports AS lamports,
ce.usd AS usd,
ce.signature AS signature,
NULLIF(ce.meta->>'service', '') AS service
FROM agent_custody_events ce
JOIN agent_identities payer
ON payer.id = ce.agent_id
AND payer.deleted_at IS NULL AND payer.is_public = true
AND COALESCE((payer.meta->>'pulse_opt_out')::boolean, false) = false
LEFT JOIN LATERAL (
SELECT ai.id::text AS id FROM agent_identities ai
WHERE ai.meta->>'solana_address' = ce.meta->>'to' AND ${pubAgent}
LIMIT 1
) payee ON true
WHERE ce.network = ${network}
AND ce.event_type = 'spend' AND ce.category = 'x402'
AND ce.status IN ('ok', 'confirmed')
AND NULLIF(ce.meta->>'to', '') IS NOT NULL
AND ce.created_at > now() - ${interval}::interval
UNION ALL
SELECT
ce.created_at, 'tip'::text,
tipper.id, ce.meta->>'from',
recv.id::text, NULL::text,
ce.amount_lamports, ce.usd, ce.signature, NULL::text
FROM agent_custody_events ce
JOIN agent_identities recv
ON recv.id = ce.agent_id
AND recv.deleted_at IS NULL AND recv.is_public = true
AND COALESCE((recv.meta->>'pulse_opt_out')::boolean, false) = false
LEFT JOIN LATERAL (
SELECT ai.id::text AS id FROM agent_identities ai
WHERE ai.meta->>'solana_address' = ce.meta->>'from' AND ${pubAgent}
LIMIT 1
) tipper ON true
WHERE ce.network = ${network}
AND ce.event_type = 'tip'
AND ce.status IN ('ok', 'confirmed')
AND NULLIF(ce.meta->>'from', '') IS NOT NULL
AND ce.created_at > now() - ${interval}::interval
)
SELECT
COALESCE('a:' || from_agent, 'w:' || from_addr) AS from_key,
COALESCE('a:' || to_agent, 'w:' || to_addr) AS to_key,
from_agent, to_agent, from_addr, to_addr,
COUNT(*)::int AS count,
COALESCE(SUM(lamports), 0)::text AS lamports,
COALESCE(SUM(usd), 0)::float8 AS usd,
MAX(ts) AS last_ts,
(ARRAY_AGG(signature ORDER BY ts DESC))[1] AS last_signature,
ARRAY_REMOVE(ARRAY_AGG(DISTINCT kind), NULL) AS kinds,
ARRAY_REMOVE(ARRAY_AGG(DISTINCT service), NULL) AS services
FROM transfers
WHERE COALESCE(from_agent, from_addr) IS NOT NULL
AND COALESCE(to_agent, to_addr) IS NOT NULL
GROUP BY 1, 2, from_agent, to_agent, from_addr, to_addr
ORDER BY usd DESC, count DESC
LIMIT ${GRAPH_MAX_EDGES + 1}
`;
const truncated = rows.length > GRAPH_MAX_EDGES;
const edgeRows = truncated ? rows.slice(0, GRAPH_MAX_EDGES) : rows;
// Name the agent nodes in one round trip. Only ids that survived the public
// gate above reach here, so this join adds no new disclosure.
const agentIds = [
...new Set(edgeRows.flatMap((r) => [r.from_agent, r.to_agent]).filter(Boolean)),
];
const agentRows = agentIds.length
? await sql`
SELECT ai.id::text AS id, ai.name, ai.meta->>'solana_address' AS addr,
av.thumbnail_key AS thumb_key, av.visibility AS avatar_vis
FROM agent_identities ai
LEFT JOIN avatars av ON av.id = ai.avatar_id AND av.deleted_at IS NULL
WHERE ai.id::text = ANY(${agentIds})
`
: [];
const agentById = new Map(agentRows.map((a) => [a.id, a]));
// Assemble nodes from the edges so the two collections can never disagree.
const nodes = new Map();
const nodeFor = (key, agentId, addr) => {
const existing = nodes.get(key);
if (existing) return existing;
const agent = agentId ? agentById.get(agentId) : null;
const address = agent?.addr || addr || null;
const node = {
id: key,
kind: agent ? 'agent' : 'wallet',
agent_id: agent?.id || null,
name: agent?.name || shortAddr(addr),
url: agent ? `/agents/${agent.id}` : null,
avatar_thumbnail_url: agent
? r2Url(agent.thumb_key, agent.avatar_vis === 'public' || agent.avatar_vis === 'unlisted')
: null,
address,
address_short: shortAddr(address),
explorer: address ? explorerAccountUrl(address, network) : null,
in_usd: 0, out_usd: 0, in_sol: 0, out_sol: 0, in_count: 0, out_count: 0,
partners_in: 0, partners_out: 0,
};
nodes.set(key, node);
return node;
};
// Service totals come from their own aggregation, never from the edges: one
// edge can cover several skills, so folding its USD into each of them would
// report more revenue per service than actually settled.
const serviceRows = await sql`
SELECT NULLIF(ce.meta->>'service', '') AS name,
COUNT(*)::int AS count,
COALESCE(SUM(ce.usd), 0)::float8 AS usd
FROM agent_custody_events ce
JOIN agent_identities payer
ON payer.id = ce.agent_id
AND payer.deleted_at IS NULL AND payer.is_public = true
AND COALESCE((payer.meta->>'pulse_opt_out')::boolean, false) = false
WHERE ce.network = ${network}
AND ce.event_type = 'spend' AND ce.category = 'x402'
AND ce.status IN ('ok', 'confirmed')
AND NULLIF(ce.meta->>'service', '') IS NOT NULL
AND ce.created_at > now() - ${interval}::interval
GROUP BY 1
ORDER BY usd DESC
`;
const edges = edgeRows.map((r) => {
const sol = Number(r.lamports || 0) / 1e9;
const usd = Number(r.usd || 0);
const from = nodeFor(r.from_key, r.from_agent, r.from_addr);
const to = nodeFor(r.to_key, r.to_agent, r.to_addr);
from.out_usd += usd; from.out_sol += sol; from.out_count += r.count; from.partners_out += 1;
to.in_usd += usd; to.in_sol += sol; to.in_count += r.count; to.partners_in += 1;
return {
from: r.from_key,
to: r.to_key,
count: r.count,
sol,
usd,
kinds: r.kinds || [],
services: r.services || [],
last_ts: r.last_ts,
last_signature: r.last_signature || null,
explorer: r.last_signature ? explorerTxUrl(r.last_signature, network) : null,
};
});
const nodeList = [...nodes.values()];
return {
network,
window: GRAPH_WINDOWS[windowKey] ? windowKey : '30d',
generated_at: new Date().toISOString(),
nodes: nodeList,
edges,
services: serviceRows.map((s) => ({ name: s.name, count: s.count, usd: Number(s.usd || 0) })),
totals: {
nodes: nodeList.length,
edges: edges.length,
transfers: edges.reduce((n, e) => n + e.count, 0),
usd: edges.reduce((n, e) => n + e.usd, 0),
sol: edges.reduce((n, e) => n + e.sol, 0),
payments: edges.filter((e) => e.kinds.includes('payment')).reduce((n, e) => n + e.count, 0),
tips: edges.filter((e) => e.kinds.includes('tip')).reduce((n, e) => n + e.count, 0),
named_agents: nodeList.filter((n) => n.kind === 'agent').length,
},
// Honest about its own limits: a capped graph must never read as the whole economy.
truncated,
max_edges: GRAPH_MAX_EDGES,
};
}
// ── aggregate stats view ──────────────────────────────────────────────────────
async function handleStats(network) {
// Public-agent gate, reused across every aggregate below so each counter honours
// the same privacy contract as the live feed (no deleted, no private, no opt-out).
const pubAgent = sql`ai.deleted_at IS NULL AND ai.is_public = true
AND COALESCE((ai.meta->>'pulse_opt_out')::boolean, false) = false`;
// 24h tip flow (count + SOL + USD where priced), platform-wide, public agents.
const [tip24] = await sql`
SELECT
COUNT(*)::int AS count,
COALESCE(SUM(ce.amount_lamports), 0)::text AS lamports,
COALESCE(SUM(ce.usd), 0)::float8 AS usd
FROM agent_custody_events ce
JOIN agent_identities ai ON ai.id = ce.agent_id AND ${pubAgent}
WHERE ce.network = ${network} AND ce.event_type = 'tip'
AND ce.status IN ('ok', 'confirmed')
AND ce.created_at > now() - interval '24 hours'
`;
// Top earning agents by tips received (rolling 7d). Lifetime would bury new
// agents under early movers; 7d keeps "who's hot right now" honest.
const topEarners = await sql`
SELECT ai.id AS agent_id, ai.name AS agent_name,
av.thumbnail_key AS thumb_key, av.visibility AS avatar_vis,
ai.meta->>'solana_address' AS agent_addr,
COUNT(*)::int AS tip_count,
COALESCE(SUM(ce.amount_lamports), 0)::text AS lamports,
COALESCE(SUM(ce.usd), 0)::float8 AS usd
FROM agent_custody_events ce
JOIN agent_identities ai ON ai.id = ce.agent_id AND ${pubAgent}
LEFT JOIN avatars av ON av.id = ai.avatar_id AND av.deleted_at IS NULL
WHERE ce.network = ${network} AND ce.event_type = 'tip'
AND ce.status IN ('ok', 'confirmed')
AND ce.created_at > now() - interval '7 days'
GROUP BY ai.id, ai.name, av.thumbnail_key, av.visibility, ai.meta->>'solana_address'
ORDER BY SUM(COALESCE(ce.usd, ce.amount_lamports / 1e9 * 0)) DESC NULLS LAST,
SUM(ce.amount_lamports) DESC NULLS LAST
LIMIT 6
`;
// Busiest wallets — most public events (any kind) in the last 24h.
const busiest = await sql`
SELECT ai.id AS agent_id, ai.name AS agent_name,
av.thumbnail_key AS thumb_key, av.visibility AS avatar_vis,
ai.meta->>'solana_address' AS agent_addr,
COUNT(*)::int AS events
FROM agent_custody_events ce
JOIN agent_identities ai ON ai.id = ce.agent_id AND ${pubAgent}
LEFT JOIN avatars av ON av.id = ai.avatar_id AND av.deleted_at IS NULL
WHERE ce.network = ${network}
AND ce.status IN ('ok', 'confirmed')
AND (ce.event_type = 'tip' OR (ce.event_type = 'spend' AND ce.category = ANY(${PUBLIC_SPEND_CATEGORIES})))
AND ce.created_at > now() - interval '24 hours'
GROUP BY ai.id, ai.name, av.thumbnail_key, av.visibility, ai.meta->>'solana_address'
ORDER BY COUNT(*) DESC
LIMIT 6
`;
const [launch24] = await sql`
SELECT COUNT(*)::int AS count
FROM pump_agent_mints pam
JOIN agent_identities ai ON ai.id = pam.agent_id AND ${pubAgent}
WHERE pam.network = ${network} AND pam.created_at > now() - interval '24 hours'
`;
// 24h money tempo in one pass: total volume (SOL + priced USD), distinct active
// wallets, and the trade/snipe/payment breakdown — so each spend category gets
// its own honest counter instead of being lumped together.
const [tempo24] = await sql`
SELECT
COUNT(*) FILTER (WHERE ce.event_type = 'spend' AND ce.category = 'trade')::int AS trades,
COUNT(*) FILTER (WHERE ce.event_type = 'spend' AND ce.category = 'snipe')::int AS snipes,
COUNT(*) FILTER (WHERE ce.event_type = 'spend' AND ce.category = 'x402')::int AS payments,
COUNT(DISTINCT ce.agent_id)::int AS active_wallets,
COALESCE(SUM(ce.amount_lamports), 0)::text AS lamports,
COALESCE(SUM(ce.usd), 0)::float8 AS usd
FROM agent_custody_events ce
JOIN agent_identities ai ON ai.id = ce.agent_id AND ${pubAgent}
WHERE ce.network = ${network}
AND ce.status IN ('ok', 'confirmed')
AND (ce.event_type = 'tip' OR (ce.event_type = 'spend' AND ce.category = ANY(${PUBLIC_SPEND_CATEGORIES})))
AND ce.created_at > now() - interval '24 hours'
`;
// 24h marketplace demand — REAL skill purchases settled in $THREE. This is the
// honest viability signal: value is delivered AND the platform earns a take-rate,
// unlike the x402 counter which only measures agent-to-agent settlement plumbing.
// Sourced from skill_purchases (the same table /marketplace confirms against).
// Same "real sale" predicate as handleMarketplace(): money and party counts must
// agree on which rows are sales, or a non-paid confirmed row inflates one side.
const paidRow24 = sql`sp.status = 'confirmed' AND sp.kind = ANY(${MARKET_PAID_KINDS})`;
const [mkt24] = await sql`
SELECT
COUNT(*) FILTER (WHERE ${paidRow24})::int AS purchases,
COUNT(*) FILTER (WHERE sp.kind = 'trial')::int AS trials,
COALESCE(SUM(sp.amount) FILTER (WHERE ${paidRow24}), 0)::text AS gmv_atomic,
COUNT(DISTINCT sp.user_id) FILTER (WHERE ${paidRow24})::int AS buyers,
COUNT(DISTINCT sp.agent_id) FILTER (WHERE ${paidRow24})::int AS sellers
FROM skill_purchases sp
JOIN agent_identities ai ON ai.id = sp.agent_id AND ${pubAgent}
WHERE sp.currency_mint = ${THREE_MINT} AND sp.chain = 'solana'
AND sp.created_at > now() - interval '24 hours'
`;
// The single biggest tip of the last 24h — the day's headline moment.
const [bigTip] = await sql`
SELECT ai.id AS agent_id, ai.name AS agent_name,
av.thumbnail_key AS thumb_key, av.visibility AS avatar_vis,
ai.meta->>'solana_address' AS agent_addr,
ce.amount_lamports::text AS lamports, ce.usd::float8 AS usd, ce.created_at AS ts
FROM agent_custody_events ce
JOIN agent_identities ai ON ai.id = ce.agent_id AND ${pubAgent}
LEFT JOIN avatars av ON av.id = ai.avatar_id AND av.deleted_at IS NULL
WHERE ce.network = ${network} AND ce.event_type = 'tip'
AND ce.status IN ('ok', 'confirmed')
AND ce.created_at > now() - interval '24 hours'
ORDER BY COALESCE(ce.usd, ce.amount_lamports / 1e9) DESC NULLS LAST
LIMIT 1
`;
// Freshest coins off the launcher — real records from pump_agent_mints.
const recentLaunches = await sql`
SELECT pam.mint, pam.symbol, pam.name AS coin_name, pam.created_at AS ts,
ai.id AS agent_id, ai.name AS agent_name,
av.thumbnail_key AS thumb_key, av.visibility AS avatar_vis,
ai.meta->>'solana_address' AS agent_addr
FROM pump_agent_mints pam
JOIN agent_identities ai ON ai.id = pam.agent_id AND ${pubAgent}
LEFT JOIN avatars av ON av.id = ai.avatar_id AND av.deleted_at IS NULL
WHERE pam.network = ${network}
ORDER BY pam.created_at DESC
LIMIT 5
`;
// 7-day daily pulse — a zero-filled series the page renders as a sparkline so the
// headline isn't a single flat number. Wallet events + launches, per UTC day.
const series = await sql`
WITH days AS (
SELECT generate_series(
date_trunc('day', now()) - interval '6 days',
date_trunc('day', now()),
interval '1 day'
) AS day
),
ev AS (
SELECT date_trunc('day', ce.created_at) AS day, COUNT(*)::int AS n
FROM agent_custody_events ce
JOIN agent_identities ai ON ai.id = ce.agent_id AND ${pubAgent}
WHERE ce.network = ${network}
AND ce.status IN ('ok', 'confirmed')
AND (ce.event_type = 'tip' OR (ce.event_type = 'spend' AND ce.category = ANY(${PUBLIC_SPEND_CATEGORIES})))
AND ce.created_at > date_trunc('day', now()) - interval '6 days'
GROUP BY 1
),
lc AS (
SELECT date_trunc('day', pam.created_at) AS day, COUNT(*)::int AS n
FROM pump_agent_mints pam
JOIN agent_identities ai ON ai.id = pam.agent_id AND ${pubAgent}
WHERE pam.network = ${network}
AND pam.created_at > date_trunc('day', now()) - interval '6 days'
GROUP BY 1
)
SELECT to_char(days.day, 'Dy') AS label,
to_char(days.day, 'YYYY-MM-DD') AS day,
COALESCE(ev.n, 0) AS events,
COALESCE(lc.n, 0) AS launches
FROM days
LEFT JOIN ev ON ev.day = days.day
LEFT JOIN lc ON lc.day = days.day
ORDER BY days.day
`;
const shapeAgent = (r) => ({
id: r.agent_id,
name: r.agent_name || 'Agent',
url: `/agents/${r.agent_id}`,
avatar_thumbnail_url: r2Url(r.thumb_key, r.avatar_vis === 'public' || r.avatar_vis === 'unlisted'),
solana_address: r.agent_addr || null,
tip_count: r.tip_count != null ? Number(r.tip_count) : undefined,
events: r.events != null ? Number(r.events) : undefined,
sol: r.lamports != null ? Number(r.lamports) / 1e9 : undefined,
usd: r.usd != null ? Number(r.usd) : undefined,
});
const trades = Number(tempo24?.trades || 0);
const snipes = Number(tempo24?.snipes || 0);
const payments = Number(tempo24?.payments || 0);
return {
network,
tips_24h: {
count: Number(tip24?.count || 0),
sol: Number(tip24?.lamports || 0) / 1e9,
usd: Number(tip24?.usd || 0),
},
launches_24h: Number(launch24?.count || 0),
// Kept for backward-compat: the combined swap/snipe/payment count.
trades_24h: trades + snipes + payments,
trades_only_24h: trades,
snipes_24h: snipes,
payments_24h: payments,
// Real marketplace demand — paid skill purchases settled in $THREE. The honest
// counterpart to payments_24h: every unit here delivered value and earned a fee.
marketplace_24h: {
purchases: Number(mkt24?.purchases || 0),
trials: Number(mkt24?.trials || 0),
gmv_three: threeFromAtomic(mkt24?.gmv_atomic),
buyers: Number(mkt24?.buyers || 0),
sellers: Number(mkt24?.sellers || 0),
},
active_wallets_24h: Number(tempo24?.active_wallets || 0),
volume_24h: {
sol: Number(tempo24?.lamports || 0) / 1e9,
usd: Number(tempo24?.usd || 0),
},
biggest_tip_24h: bigTip
? {
agent: shapeAgent(bigTip),
sol: Number(bigTip.lamports || 0) / 1e9,
usd: bigTip.usd != null ? Number(bigTip.usd) : null,
ts: bigTip.ts,
}
: null,
series_7d: series.map((r) => ({
label: r.label,
day: r.day,
events: Number(r.events || 0),
launches: Number(r.launches || 0),
})),
recent_launches: recentLaunches.map((r) => ({
mint: r.mint,
symbol: r.symbol || null,
coin_name: r.coin_name || null,
ts: r.ts,
mint_explorer: r.mint ? explorerAccountUrl(r.mint, network) : null,
agent: shapeAgent(r),
})),
top_earners: topEarners.map(shapeAgent),
busiest_wallets: busiest.map(shapeAgent),
};
}
// ── per-agent lifetime summary ─────────────────────────────────────────────────
async function handleAgentSummary(network, agentId) {
const [agent] = await sql`
SELECT id, name, deleted_at, is_public FROM agent_identities WHERE id = ${agentId}
`;
// Private/deleted agents expose no public summary — matches the feed gate.
if (!agent || agent.deleted_at || agent.is_public === false) return null;
const [tips] = await sql`
SELECT COUNT(*)::int AS count,
COALESCE(SUM(amount_lamports), 0)::text AS lamports,
COALESCE(SUM(usd), 0)::float8 AS usd,
COALESCE(MAX(amount_lamports), 0)::text AS biggest_lamports,
COALESCE(MAX(usd), 0)::float8 AS biggest_usd
FROM agent_custody_events
WHERE agent_id = ${agentId} AND network = ${network}
AND event_type = 'tip' AND status IN ('ok', 'confirmed')
`;
// Public outflow = trades + snipes + agent-to-agent payments (never withdraws).
const [outflow] = await sql`
SELECT COUNT(*)::int AS count,
COALESCE(SUM(amount_lamports), 0)::text AS lamports,
COALESCE(SUM(usd), 0)::float8 AS usd
FROM agent_custody_events
WHERE agent_id = ${agentId} AND network = ${network}
AND event_type = 'spend' AND category = ANY(${PUBLIC_SPEND_CATEGORIES})
AND status IN ('ok', 'confirmed')
`;
const [launches] = await sql`
SELECT COUNT(*)::int AS count FROM pump_agent_mints
WHERE agent_id = ${agentId} AND network = ${network}
`;
return {
agent_id: agentId,
network,
tips: {
count: Number(tips?.count || 0),
sol: Number(tips?.lamports || 0) / 1e9,
usd: Number(tips?.usd || 0),
biggest_sol: Number(tips?.biggest_lamports || 0) / 1e9,
biggest_usd: Number(tips?.biggest_usd || 0),
},
outflow: {
count: Number(outflow?.count || 0),
sol: Number(outflow?.lamports || 0) / 1e9,
usd: Number(outflow?.usd || 0),
},
launches: Number(launches?.count || 0),
};
}
// ── marketplace viability dashboard ────────────────────────────────────────────
// The numbers that actually answer "is agent-to-agent commerce viable?" — not raw
// payment count (vanity) but GMV, repeat-buyer rate, unique trading pairs, average
// ticket and the platform take-rate. All sourced from real, confirmed skill_purchases
// rows (each backed by an on-chain $THREE transfer), gated to public seller agents.
async function handleMarketplace(network) {
const pubAgent = sql`ai.deleted_at IS NULL AND ai.is_public = true
AND COALESCE((ai.meta->>'pulse_opt_out')::boolean, false) = false`;
const feeBps = marketplaceFeeBps();
// One definition of "a real marketplace sale", shared by EVERY money and party
// aggregate below. It has to be shared: filtering the counts on status+kind but
// the money on status alone is what let non-sale rows (a trial, or a bundle
// access record, both of which are 'confirmed' but not a paid kind) land in
// gmv/fees/buyers/sellers while being excluded from the purchase count. That
// also skewed avg_ticket, which divides a GMV by a count that excluded part of
// what produced it. These are published on a transparency page, so they are
// derived from one predicate rather than kept in sync by hand.
const paidRow = sql`sp.status = 'confirmed' AND sp.kind = ANY(${MARKET_PAID_KINDS})`;
// One windowed aggregate, reused for 24h and 7d so both cards read identically.
const windowAgg = async (interval) => {
const [r] = await sql`
SELECT
COUNT(*) FILTER (WHERE ${paidRow})::int AS purchases,
-- Deliberately status-free: this counts trials STARTED in the window, so a
-- trial that has since lapsed still counts. It is a demand signal, not money.
COUNT(*) FILTER (WHERE sp.kind = 'trial')::int AS trials,
COALESCE(SUM(sp.amount) FILTER (WHERE ${paidRow}), 0)::text AS gmv_atomic,
COUNT(DISTINCT sp.user_id) FILTER (WHERE ${paidRow})::int AS buyers,
COUNT(DISTINCT sp.agent_id) FILTER (WHERE ${paidRow})::int AS sellers,
COUNT(DISTINCT (sp.user_id, sp.agent_id)) FILTER (WHERE ${paidRow})::int AS pairs,
COALESCE(SUM(sp.platform_fee_amount) FILTER (WHERE ${paidRow}), 0)::text AS fee_atomic
FROM skill_purchases sp
JOIN agent_identities ai ON ai.id = sp.agent_id AND ${pubAgent}
WHERE sp.currency_mint = ${THREE_MINT} AND sp.chain = 'solana'
AND sp.created_at > now() - ${interval}::interval
`;
const purchases = Number(r?.purchases || 0);
const gmv = threeFromAtomic(r?.gmv_atomic);
// Take-rate is the fee ACTUALLY charged on-chain (persisted per row by the
// purchase splitter), never a GMV × rate estimate — so the number is real
// even when some purchases predate the fee or skipped it.
return {
purchases,
trials: Number(r?.trials || 0),
gmv_three: gmv,
buyers: Number(r?.buyers || 0),
sellers: Number(r?.sellers || 0),
pairs: Number(r?.pairs || 0),
avg_ticket_three: purchases > 0 ? gmv / purchases : 0,
take_rate_three: threeFromAtomic(r?.fee_atomic),
};
};
// Repeat-buyer rate (7d): the single strongest viability signal — do buyers come
// back, or is every purchase a one-off? Counts buyers with ≥2 paid purchases.
const repeatPromise = sql`
WITH b AS (
SELECT sp.user_id, COUNT(*) AS n
FROM skill_purchases sp
JOIN agent_identities ai ON ai.id = sp.agent_id AND ${pubAgent}
WHERE sp.currency_mint = ${THREE_MINT} AND sp.chain = 'solana'
AND sp.status = 'confirmed' AND sp.kind = ANY(${MARKET_PAID_KINDS})
AND sp.created_at > now() - interval '7 days'
GROUP BY sp.user_id
)
SELECT COUNT(*)::int AS buyers, COUNT(*) FILTER (WHERE n >= 2)::int AS repeat_buyers FROM b
`;
// Top skills by 7d GMV — what is the market actually paying for?
const topSkillsPromise = sql`
SELECT sp.skill,
COUNT(*)::int AS purchases,
COALESCE(SUM(sp.amount), 0)::text AS gmv_atomic,
COUNT(DISTINCT sp.user_id)::int AS buyers
FROM skill_purchases sp
JOIN agent_identities ai ON ai.id = sp.agent_id AND ${pubAgent}
WHERE sp.currency_mint = ${THREE_MINT} AND sp.chain = 'solana'
AND sp.status = 'confirmed' AND sp.kind = ANY(${MARKET_PAID_KINDS})
AND sp.created_at > now() - interval '7 days'
GROUP BY sp.skill
ORDER BY SUM(sp.amount) DESC NULLS LAST
LIMIT 6
`;
// Top sellers by 7d earnings — the supply side that's actually clearing.
const topSellersPromise = sql`
SELECT ai.id AS agent_id, ai.name AS agent_name,
av.thumbnail_key AS thumb_key, av.visibility AS avatar_vis,
ai.meta->>'solana_address' AS agent_addr,
COUNT(*)::int AS sales, COALESCE(SUM(sp.amount), 0)::text AS gmv_atomic
FROM skill_purchases sp
JOIN agent_identities ai ON ai.id = sp.agent_id AND ${pubAgent}
LEFT JOIN avatars av ON av.id = ai.avatar_id AND av.deleted_at IS NULL
WHERE sp.currency_mint = ${THREE_MINT} AND sp.chain = 'solana'
AND sp.status = 'confirmed' AND sp.kind = ANY(${MARKET_PAID_KINDS})
AND sp.created_at > now() - interval '7 days'
GROUP BY ai.id, ai.name, av.thumbnail_key, av.visibility, ai.meta->>'solana_address'
ORDER BY SUM(sp.amount) DESC NULLS LAST
LIMIT 6
`;
// 7-day GMV + purchase sparkline, zero-filled per UTC day.
const seriesPromise = sql`
WITH days AS (
SELECT generate_series(
date_trunc('day', now()) - interval '6 days',
date_trunc('day', now()),
interval '1 day'
) AS day
),
ev AS (
SELECT date_trunc('day', sp.created_at) AS day,
COUNT(*) FILTER (WHERE ${paidRow})::int AS purchases,
COALESCE(SUM(sp.amount) FILTER (WHERE ${paidRow}), 0)::text AS gmv_atomic
FROM skill_purchases sp
JOIN agent_identities ai ON ai.id = sp.agent_id AND ${pubAgent}
WHERE sp.currency_mint = ${THREE_MINT} AND sp.chain = 'solana'
AND sp.created_at > date_trunc('day', now()) - interval '6 days'
GROUP BY 1
)
SELECT to_char(days.day, 'Dy') AS label, to_char(days.day, 'YYYY-MM-DD') AS day,
COALESCE(ev.purchases, 0) AS purchases, COALESCE(ev.gmv_atomic, '0') AS gmv_atomic
FROM days LEFT JOIN ev ON ev.day = days.day
ORDER BY days.day
`;
const [d24, d7, [repeat], topSkills, topSellers, series] = await Promise.all([
windowAgg('24 hours'),
windowAgg('7 days'),
repeatPromise,
topSkillsPromise,
topSellersPromise,
seriesPromise,
]);
const buyers7 = Number(repeat?.buyers || 0);
const repeatBuyers7 = Number(repeat?.repeat_buyers || 0);
return {
network,
fee_bps: feeBps,
fee_pct: feeBps / 100,
window_24h: d24,
window_7d: d7,
repeat_buyer_rate_7d: buyers7 > 0 ? repeatBuyers7 / buyers7 : 0,
repeat_buyers_7d: repeatBuyers7,
buyers_7d: buyers7,
series_7d: series.map((r) => ({
label: r.label,
day: r.day,
purchases: Number(r.purchases || 0),
gmv_three: threeFromAtomic(r.gmv_atomic),
})),
top_skills: topSkills.map((r) => ({
skill: r.skill,
purchases: Number(r.purchases || 0),
buyers: Number(r.buyers || 0),
gmv_three: threeFromAtomic(r.gmv_atomic),
})),
top_sellers: topSellers.map((r) => ({
id: r.agent_id,
name: r.agent_name || 'Agent',
url: `/agents/${r.agent_id}`,
avatar_thumbnail_url: r2Url(r.thumb_key, r.avatar_vis === 'public' || r.avatar_vis === 'unlisted'),
solana_address: r.agent_addr || null,
sales: Number(r.sales || 0),
gmv_three: threeFromAtomic(r.gmv_atomic),
})),
};
}
// ── trading viability dashboard ────────────────────────────────────────────────
// The numbers that answer "is funded agent trading actually happening, what does it
// cost, and is it making money?" Activity + cost come from agent_custody_events
// (category='trade' — the exact source the headline Trades counter reconciles with,
// so the panel can never drift from it). Realized P&L comes from CLOSED positions in
// the strategy + sniper position tables, where exit-minus-entry is computed against
// real fills. Heartbeat buys that never close show up as cost with no P&L — which is
// the honest story: profitability lives in positions that round-trip, not in volume.
async function handleTrading(network) {
const pubAgent = sql`ai.deleted_at IS NULL AND ai.is_public = true
AND COALESCE((ai.meta->>'pulse_opt_out')::boolean, false) = false`;
// One windowed aggregate over the custody ledger, reused for 24h + 7d. Buys carry
// SOL out in amount_lamports (asset='SOL'); sells carry only token base units, so
// "SOL deployed" = SUM(amount_lamports) is exactly the SOL spent acquiring coins.
const windowAgg = async (interval) => {
const [r] = await sql`
SELECT
COUNT(*)::int AS trades,
COUNT(*) FILTER (WHERE ce.amount_lamports IS NOT NULL)::int AS buys,
COUNT(*) FILTER (WHERE ce.amount_lamports IS NULL)::int AS sells,
COALESCE(SUM(ce.amount_lamports), 0)::text AS deployed_lamports,
COALESCE(SUM(ce.usd), 0)::float8 AS deployed_usd,
COUNT(DISTINCT ce.agent_id)::int AS traders
FROM agent_custody_events ce
JOIN agent_identities ai ON ai.id = ce.agent_id AND ${pubAgent}
WHERE ce.network = ${network}
AND ce.event_type = 'spend' AND ce.category = 'trade'
AND ce.status IN ('ok', 'confirmed')
AND ce.created_at > now() - ${interval}::interval
`;
return shapeTradingWindow(r);
};
// Realized P&L over 7d from CLOSED positions across both real position tables.
// `realized_pnl_lamports` is signed (exit − entry); a win is strictly positive.
const pnlPromise = sql`
WITH closed AS (
SELECT sp.realized_pnl_lamports AS pnl
FROM agent_strategy_positions sp
JOIN agent_identities ai ON ai.id = sp.agent_id AND ${pubAgent}
WHERE sp.network = ${network} AND sp.status = 'closed'
AND sp.realized_pnl_lamports IS NOT NULL
AND sp.closed_at > now() - interval '7 days'
UNION ALL
SELECT snp.realized_pnl_lamports AS pnl
FROM agent_sniper_positions snp
JOIN agent_identities ai ON ai.id = snp.agent_id AND ${pubAgent}
WHERE snp.network = ${network} AND snp.status = 'closed'
AND snp.realized_pnl_lamports IS NOT NULL
AND snp.closed_at > now() - interval '7 days'
)
SELECT COUNT(*)::int AS closed_count,
COUNT(*) FILTER (WHERE pnl > 0)::int AS wins,
COALESCE(SUM(pnl), 0)::text AS net_lamports
FROM closed
`;
// Top trading agents (7d) by trade count, then SOL deployed — the wallets actually
// putting capital to work. Same public-agent gate as every other rail.
const topTradersPromise = sql`
SELECT ai.id AS agent_id, ai.name AS agent_name,
av.thumbnail_key AS thumb_key, av.visibility AS avatar_vis,
ai.meta->>'solana_address' AS agent_addr,
COUNT(*)::int AS trades,
COALESCE(SUM(ce.amount_lamports), 0)::text AS deployed_lamports
FROM agent_custody_events ce
JOIN agent_identities ai ON ai.id = ce.agent_id AND ${pubAgent}
LEFT JOIN avatars av ON av.id = ai.avatar_id AND av.deleted_at IS NULL
WHERE ce.network = ${network}
AND ce.event_type = 'spend' AND ce.category = 'trade'
AND ce.status IN ('ok', 'confirmed')
AND ce.created_at > now() - interval '7 days'
GROUP BY ai.id, ai.name, av.thumbnail_key, av.visibility, ai.meta->>'solana_address'
ORDER BY COUNT(*) DESC, SUM(ce.amount_lamports) DESC NULLS LAST
LIMIT 6
`;
// 7-day trade-count + SOL-deployed sparkline, zero-filled per UTC day.
const seriesPromise = sql`
WITH days AS (
SELECT generate_series(
date_trunc('day', now()) - interval '6 days',
date_trunc('day', now()),
interval '1 day'
) AS day
),
ev AS (
SELECT date_trunc('day', ce.created_at) AS day,
COUNT(*)::int AS trades,
COALESCE(SUM(ce.amount_lamports), 0)::text AS deployed_lamports
FROM agent_custody_events ce
JOIN agent_identities ai ON ai.id = ce.agent_id AND ${pubAgent}
WHERE ce.network = ${network}
AND ce.event_type = 'spend' AND ce.category = 'trade'
AND ce.status IN ('ok', 'confirmed')
AND ce.created_at > date_trunc('day', now()) - interval '6 days'
GROUP BY 1
)
SELECT to_char(days.day, 'Dy') AS label, to_char(days.day, 'YYYY-MM-DD') AS day,
COALESCE(ev.trades, 0) AS trades, COALESCE(ev.deployed_lamports, '0') AS deployed_lamports
FROM days LEFT JOIN ev ON ev.day = days.day
ORDER BY days.day
`;
const [d24, d7, [pnl], topTraders, series] = await Promise.all([
windowAgg('24 hours'),
windowAgg('7 days'),
pnlPromise,
topTradersPromise,
seriesPromise,
]);
return {
network,
window_24h: d24,
window_7d: d7,
realized_pnl_7d: shapeTradingPnl(pnl),
series_7d: shapeTradingSeries(series),
top_traders: topTraders.map((r) => ({
id: r.agent_id,
name: r.agent_name || 'Agent',
url: `/agents/${r.agent_id}`,
avatar_thumbnail_url: r2Url(r.thumb_key, r.avatar_vis === 'public' || r.avatar_vis === 'unlisted'),
solana_address: r.agent_addr || null,
trades: Number(r.trades || 0),
deployed_sol: solFromLamports(r.deployed_lamports),
})),
};
}
export default async function handler(req, res) {
if (cors(req, res, { methods: 'GET,OPTIONS', origins: '*' })) return;
if (!method(req, res, ['GET'])) return;
const rl = await limits.publicIp(clientIp(req));
if (!rl.success) return rateLimited(res, rl);
const url = new URL(req.url, 'http://x');
const network = url.searchParams.get('network') === 'devnet' ? 'devnet' : 'mainnet';
const view = url.searchParams.get('view') || 'feed';
const agentId = url.searchParams.get('agent_id') || null;
if (agentId && !isUuid(agentId)) return error(res, 400, 'validation_error', 'agent_id must be a uuid');
const mint = url.searchParams.get('mint') || null;
if (mint && !/^[1-9A-HJ-NP-Za-km-z]{32,44}$/.test(mint)) return error(res, 400, 'validation_error', 'mint must be a base58 address');
const typeRaw = url.searchParams.get('type') || 'all';
const type = TYPE_KINDS[typeRaw] ? typeRaw : 'all';
const cursor = url.searchParams.get('cursor');
const since = url.searchParams.get('since');
try {
if (view === 'event-markets') {
const cacheKey = 'pulse:event-markets';
let body = await cacheGet(cacheKey);
if (body === null) {
body = await pulseEventMarkets();
await cacheSet(cacheKey, body, 10);
}
res.setHeader('cache-control', 'public, max-age=10');
return json(res, 200, { data: body });
}
if (view === 'stats') {
const cacheKey = `pulse:stats:${network}`;
let body = await cacheGet(cacheKey);
if (body === null) {
body = await handleStats(network);
await cacheSet(cacheKey, body, STATS_TTL_S);
}
res.setHeader('cache-control', 'public, max-age=20');
return json(res, 200, { data: body });
}
if (view === 'marketplace') {
const cacheKey = `pulse:marketplace:${network}`;
let body = await cacheGet(cacheKey);
if (body === null) {
body = await handleMarketplace(network);
await cacheSet(cacheKey, body, STATS_TTL_S);
}
res.setHeader('cache-control', 'public, max-age=20');
return json(res, 200, { data: body });
}
if (view === 'trading') {
const cacheKey = `pulse:trading:${network}`;
let body = await cacheGet(cacheKey);
if (body === null) {
body = await handleTrading(network);
await cacheSet(cacheKey, body, STATS_TTL_S);
}
res.setHeader('cache-control', 'public, max-age=20');
return json(res, 200, { data: body });
}
if (view === 'graph') {
const windowKey = GRAPH_WINDOWS[url.searchParams.get('window')]
? url.searchParams.get('window')
: '30d';
const cacheKey = `pulse:graph:${network}:${windowKey}`;
let body = await cacheGet(cacheKey);
if (body === null) {
body = await handleGraph(network, windowKey);
await cacheSet(cacheKey, body, GRAPH_TTL_S);
}
res.setHeader('cache-control', 'public, max-age=30');
return json(res, 200, { data: body });
}
if (view === 'agent-summary') {
if (!agentId) return error(res, 400, 'validation_error', 'agent_id is required for agent-summary');
const body = await handleAgentSummary(network, agentId);
if (!body) return error(res, 404, 'not_found', 'agent not found');
res.setHeader('cache-control', 'public, max-age=20');
return json(res, 200, { data: body });
}
// Default: feed. A delta poll (`since`) is never cached — it must be live.
// A first page (no cursor/since) of the GLOBAL feed is cached briefly to
// shield the DB from a thundering herd of pollers.
const isFirstGlobalPage = !cursor && !since && !agentId && !mint;
if (isFirstGlobalPage) {
// `limit` is part of the cache key: handleFeed returns exactly `limit` rows
// (plus has_more / next_cursor computed from it), so two callers asking for
// different page sizes must NOT share a cached body. Omitting it let a
// limit=1 self-heal probe poison the shared feed page with a 1-row body.
// Clamp identically to handleFeed so the key matches the body it produced.
const limit = Math.min(MAX_LIMIT, Math.max(1, Number(url.searchParams.get('limit')) || 30));
const cacheKey = `pulse:feed:${network}:${type}:${limit}`;
let body = await cacheGet(cacheKey);
if (body === null) {
body = await handleFeed(req, res, { network, type, agentId, mint, cursor, since });
await cacheSet(cacheKey, body, FEED_TTL_S);
}
res.setHeader('cache-control', 'public, max-age=8');
return json(res, 200, { data: body });
}
const body = await handleFeed(req, res, { network, type, agentId, mint, cursor, since });
return json(res, 200, { data: body });
} catch (e) {
if (isDbUnavailableError(e)) {
console.warn('[api/pulse] db unavailable:', e?.message);
return serverError(res, 503, 'service_unavailable', e);
}
console.error('[api/pulse] failed', e?.message, e?.stack);
return serverError(res, 502, 'pulse_failed', e);
}
}