Database Design

Database Design

**Referenced Files in This Document** - [backend/server.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/server.js) - [backend/src/config/index.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/config/index.js) - [backend/src/models/db.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/models/db.js) - [backend/src/models/migrate.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/models/migrate.js) - [backend/src/models/redis.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/models/redis.js) - [backend/src/models/queries.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/models/queries.js) - [backend/src/routes/agents.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/routes/agents.js) - [backend/src/routes/register.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/routes/register.js) - [backend/src/routes/verify.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/routes/verify.js) - [backend/src/services/badgeBuilder.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/services/badgeBuilder.js) - [backend/src/services/bagsAuthVerifier.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/services/bagsAuthVerifier.js) - [backend/src/middleware/errorHandler.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/middleware/errorHandler.js) - [backend/src/middleware/rateLimit.js](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/src/middleware/rateLimit.js) - [backend/package.json](https://github.com/RunTimeAdmin/AgentID/blob/main/backend/package.json)

Table of Contents

1. Introduction
2. Project Structure
3. Core Components
4. Architecture Overview
5. Detailed Component Analysis
6. Dependency Analysis
7. Performance Considerations
8. Troubleshooting Guide
9. Conclusion
10. Appendices

Introduction

This document provides comprehensive data model documentation for the AgentID database schema and Redis caching strategy. It focuses on the PostgreSQL tables and Redis integration used to power agent identity management, verification challenges, and community moderation.

Project Structure

The backend is organized around a layered architecture:

• Configuration and environment variables
• Database connectivity and migrations
• Query layer for reusable SQL operations
• Route handlers for API endpoints
• Services for external integrations and badge building
• Middleware for security, rate limiting, and error handling

graph TB
subgraph "Application Layer"
R1["Routes<br/>agents.js, register.js, verify.js"]
S1["Services<br/>badgeBuilder.js, bagsAuthVerifier.js"]
M1["Middleware<br/>rateLimit.js, errorHandler.js"]
end
subgraph "Data Access Layer"
Q1["Queries<br/>models/queries.js"]
D1["PostgreSQL Pool<br/>models/db.js"]
RD["Redis Client<br/>models/redis.js"]
end
subgraph "Configuration"
C1["Config<br/>config/index.js"]
end
R1 --> Q1
R1 --> S1
S1 --> Q1
S1 --> RD
Q1 --> D1
M1 --> R1
C1 --> D1
C1 --> RD

Loading

Core Components

PostgreSQL Schema: agent_identities

• Purpose: Primary agent registry with reputation metrics and metadata.
• Primary key: pubkey (VARCHAR(88))
• Notable fields:
  • name (VARCHAR(255), NOT NULL)
  • description (TEXT)
  • token_mint (VARCHAR(88))
  • bags_api_key_id (VARCHAR(255))
  • said_registered (BOOLEAN DEFAULT false)
  • said_trust_score (INTEGER DEFAULT 0)
  • capability_set (JSONB)
  • creator_x (VARCHAR(255))
  • creator_wallet (VARCHAR(88))
  • registered_at (TIMESTAMPTZ DEFAULT NOW())
  • last_verified (TIMESTAMPTZ)
  • status (VARCHAR(20) DEFAULT 'verified')
  • flag_reason (TEXT)
  • bags_score (INTEGER DEFAULT 0)
  • total_actions (INTEGER DEFAULT 0)
  • successful_actions (INTEGER DEFAULT 0)
  • failed_actions (INTEGER DEFAULT 0)
  • fee_claims_count (INTEGER DEFAULT 0)
  • fee_claims_sol (DECIMAL(18,9) DEFAULT 0)
  • swaps_count (INTEGER DEFAULT 0)
  • launches_count (INTEGER DEFAULT 0)

Constraints and indexes:

• Primary key on pubkey
• Indexes:
  • idx_agent_identities_status on status
  • idx_agent_identities_bags_score on bags_score DESC

PostgreSQL Schema: agent_verifications

• Purpose: Challenge-response tracking for PKI verification.
• Primary key: id (SERIAL)
• Foreign key: pubkey references agent_identities(pubkey)
• Notable fields:
  • nonce (VARCHAR(64), UNIQUE, NOT NULL)
  • challenge (TEXT NOT NULL)
  • expires_at (TIMESTAMPTZ NOT NULL)
  • completed (BOOLEAN DEFAULT false)
  • created_at (TIMESTAMPTZ DEFAULT NOW())

PostgreSQL Schema: agent_flags

• Purpose: Community moderation system for reporting agents.
• Primary key: id (SERIAL)
• Foreign key: pubkey references agent_identities(pubkey)
• Notable fields:
  • reporter_pubkey (VARCHAR(88))
  • reason (TEXT NOT NULL)
  • evidence (JSONB)
  • created_at (TIMESTAMPTZ DEFAULT NOW())
  • resolved (BOOLEAN DEFAULT false)

Relationship Model

erDiagram
AGENT_IDENTITIES {
varchar pubkey PK
varchar name
text description
varchar token_mint
varchar bags_api_key_id
boolean said_registered
integer said_trust_score
jsonb capability_set
varchar creator_x
varchar creator_wallet
timestamptz registered_at
timestamptz last_verified
varchar status
text flag_reason
integer bags_score
integer total_actions
integer successful_actions
integer failed_actions
integer fee_claims_count
decimal fee_claims_sol
integer swaps_count
integer launches_count
}
AGENT_VERIFICATIONS {
serial id PK
varchar pubkey FK
varchar nonce UK
text challenge
timestamptz expires_at
boolean completed
timestamptz created_at
}
AGENT_FLAGS {
serial id PK
varchar pubkey FK
varchar reporter_pubkey
text reason
jsonb evidence
timestamptz created_at
boolean resolved
}
AGENT_IDENTITIES ||--o{ AGENT_VERIFICATIONS : "has"
AGENT_IDENTITIES ||--o{ AGENT_FLAGS : "reported"

Loading

Architecture Overview

The system integrates PostgreSQL for durable persistence and Redis for high-throughput, short-lived caching. The application exposes REST endpoints that orchestrate database writes and reads, and Redis operations for badge caching.

Detailed Component Analysis

Database Connection and Pooling

• Connection pool uses the pg package with a connection string from configuration.
• Production SSL behavior sets rejectUnauthorized to false when NODE_ENV is production.
• Pool error events are logged without crashing the process.
• Query wrapper executes parameterized SQL and logs errors before rethrowing.

Migration Management

• Migration script creates tables and indexes in a transaction.
• Creates agent_identities, agent_verifications, and agent_flags.
• Adds indexes for performance on status, bags_score, pubkey, and resolved.

Redis Caching Strategy

• Redis client uses ioredis with retryStrategy, maxRetriesPerRequest, and enableOfflineQueue.
• Cache keys for badges follow the pattern: badge:{pubkey}.
• TTL for badges is configured via BADGE_CACHE_TTL (default 60 seconds).

Business Workflows

Registration Workflow

1. Validates request body fields
2. Verifies Bags signature using Ed25519
3. Prevents replay by ensuring nonce appears in message
4. Checks for existing agent
5. Attempts SAID binding and stores agent record

Verification Workflow

1. Issues a PKI challenge bound to an agent pubkey
2. Responds to challenge by verifying signature
3. Marks verification as completed upon success
4. Enforces expiration and completion checks

Badge Retrieval and Caching

1. Badge JSON retrieval follows cache-first strategy
2. Computes reputation and aggregates action stats
3. Stores badge JSON in Redis with TTL
4. Provides SVG and HTML widget variants

Dependency Analysis

External dependencies:

• PostgreSQL driver (pg)
• Redis client (ioredis)
• Express ecosystem
• Utility libraries: base58, tweetnacl

Performance Considerations

• PostgreSQL: Use indexes on frequently filtered columns
• Redis: Short TTL for badges balances freshness and load
• Application: Rate limiting prevents abuse

Troubleshooting Guide

• Database connectivity: Verify DATABASE_URL and SSL settings
• Migration failures: Ensure transactional migration runs to completion
• Redis connectivity: Monitor connect, error, and reconnect events
• API errors: Check global error handler logs

Conclusion

The AgentID data model centers on three core tables supporting identity, verification, and moderation. PostgreSQL provides durable storage with targeted indexes, while Redis caches frequently accessed badge data.

Appendices

Configuration Reference

• DATABASE_URL: PostgreSQL connection string
• REDIS_URL: Redis connection string
• BADGE_CACHE_TTL: Cache TTL for badges (seconds)
• CHALLENGE_EXPIRY_SECONDS: Expiration for verification challenges (seconds)