Cache ERC-8004 agent data in users table — eliminate RPC calls on page load

[realproject7]

Problem

Agent-related pages (/agents, /profile/[address]) make 3-6 RPC calls per page load to detect agent status and fetch metadata:

1. agentIdByWallet(address) — reverse lookup
2. balanceOf(address) — ERC-721 ownership check
3. tokenOfOwnerByIndex(address, 0) — get owned token ID
4. agentURI(agentId) — fetch metadata JSON
5. getAgentWallet(agentId) — get bound wallet
6. ownerOf(agentId) — get NFT owner

This is slow, wasteful, and doesn't scale. The data rarely changes — only on registration or management actions.

Solution

Cache agent data in the existing users table. Only make RPC calls during registration/management transactions, then persist to DB.

1. Add agent columns to users table

New migration:

ALTER TABLE users ADD COLUMN IF NOT EXISTS agent_id INTEGER UNIQUE;
ALTER TABLE users ADD COLUMN IF NOT EXISTS agent_name TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS agent_description TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS agent_genre TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS agent_llm_model TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS agent_wallet TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS agent_owner TEXT;
ALTER TABLE users ADD COLUMN IF NOT EXISTS agent_registered_at TIMESTAMPTZ;

CREATE INDEX IF NOT EXISTS idx_users_agent_id ON users (agent_id) WHERE agent_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_users_agent_wallet ON users (agent_wallet) WHERE agent_wallet IS NOT NULL;

2. Write to DB on registration

After successful register() tx in AgentRegister.tsx:

• Call a new API endpoint POST /api/user/agent-register with the agent data
• This upserts the agent columns on the user's row (matched by wallet address)

3. Write to DB on management actions

After successful setAgentURI(), setAgentWallet(), unsetAgentWallet() in AgentManage.tsx:

• Call POST /api/user/agent-update to sync the changed fields to DB

4. Read from DB on page load

Replace RPC-based detection in agents/page.tsx and AgentDashboard.tsx:

• Use existing getUserFromDB(address) or a new getAgentByWallet(address) helper
• If agent_id IS NOT NULL → user is a registered agent, show management UI
• If agent_owner = address → user is the NFT owner
• If agent_wallet = address → user is the bound agent wallet
• No RPC calls needed for detection or metadata display

5. Update detectWriterType()

In lib/contracts/erc8004.ts, detectWriterType() should:

1. Check DB first: SELECT agent_id FROM users WHERE agent_wallet = $address OR primary_address = $address
2. Only fall back to RPC if no DB record found
3. This speeds up storyline indexing too

6. Profile page

The profile trust dashboard already reads from dbUser. Agent metadata will be available from the same row — no separate getAgentMetadata() RPC call needed.

7. Fallback for externally registered agents

If a wallet connects that has an ERC-8004 agent ID but no DB record (registered from another app):

• Make the RPC calls once to detect and fetch metadata
• Persist to DB immediately
• Subsequent visits read from DB

API endpoints to create

POST /api/user/agent-register

// Body: { walletAddress, agentId, name, description, genre, llmModel, agentWallet?, agentOwner }
// Upserts agent columns on the user row

POST /api/user/agent-update

// Body: { walletAddress, fields: { agent_name?, agent_description?, agent_genre?, agent_llm_model?, agent_wallet? } }
// Updates specific agent columns

Files to modify

• New migration: supabase/migrations/00029_users_agent_columns.sql
• lib/supabase.ts — add agent column types to User interface
• lib/contracts/erc8004.ts — update detectWriterType() to check DB first
• src/app/agents/page.tsx — replace RPC detection with DB lookup
• src/components/AgentRegister.tsx — persist to DB after registration tx
• src/components/AgentManage.tsx — persist to DB after management actions
• src/components/AgentDashboard.tsx — replace RPC detection with DB lookup
• New: src/app/api/user/agent-register/route.ts
• New: src/app/api/user/agent-update/route.ts

Branch

task/589-agent-db-cache

Acceptance criteria

• Agent columns added to users table with migration
• Agent data persisted to DB on registration
• Agent data updated in DB on management actions (URI update, wallet change/unset)
• Page load detection reads from DB — no RPC calls for known agents
• Externally registered agents detected via RPC on first visit, then cached
• detectWriterType() checks DB before RPC fallback
• Profile page reads agent metadata from user row
• Build passes