Schema & ERD

Nexgent uses PostgreSQL with Prisma ORM for type-safe database access. The schema is designed around trading agents, their wallets, positions, and transaction history.

Schema Overview

Entity	Purpose
User	Authentication and account management
Agent	Trading agents with configuration
AgentWallet	Wallet assignments (simulation/live)
AgentBalance	Token balances per wallet
AgentPosition	Open positions with stop loss state
AgentTransaction	All transaction records
AgentHistoricalSwap	Completed trade history with P/L
AgentBalanceSnapshot	Hourly portfolio snapshots
TradingSignal	Trading signals from sources
SignalExecution	Signal processing status per agent
ApiKey	API keys for programmatic access

Entity Relationship Diagram

The ERD below shows all database entities organized into logical layers, with relationships clearly marked. Key relationships use cardinality notation (1:N, N:1, 1:1) to indicate one-to-many, many-to-one, and one-to-one relationships.

Diagram Legend

PK = Primary Key
FK = Foreign Key (relationship to another table)
1:N = One-to-Many (one parent, many children)
N:1 = Many-to-One (many children, one parent)
1:1 = One-to-One (unique relationship)

Relationship Summary

Relationship	Type	Description
User → ApiKey	1:N	User can have multiple API keys
User → Agent	1:N	User can create multiple agents
Agent → AgentWallet	1:N	Agent has wallets (simulation/live)
Agent → SignalExecution	1:N	Agent processes multiple signals
TradingSignal → SignalExecution	1:N	Signal can be processed by multiple agents
AgentWallet → AgentBalance	1:N	Wallet holds multiple token balances
AgentWallet → AgentPosition	1:N	Wallet can have multiple open positions
AgentWallet → AgentTransaction	1:N	Wallet has transaction history
AgentWallet → BalanceSnapshot	1:N	Wallet has hourly snapshots
AgentPosition → AgentTransaction	1:1	Position linked to purchase transaction
AgentTransaction → AgentHistoricalSwap	1:N	Transaction can be purchase or sale in swaps
TradingSignal → AgentTransaction	1:N	Signal can trigger multiple transactions
TradingSignal → AgentHistoricalSwap	1:N	Signal can result in multiple completed trades
AgentBalance → BalanceSnapshot	1:N	Balance contributes to snapshots

Note: The diagram shows all direct relationships. Some indirect relationships exist through foreign keys (e.g., AgentHistoricalSwap references AgentTransaction via purchaseTransactionId and saleTransactionId).

Core Relationships

User → Agents (1:N)

Users can create multiple agents. Each agent belongs to one user.

model User {
  id     String  @id @default(uuid())
  agents Agent[]
}
 
model Agent {
  id     String @id @default(uuid())
  userId String @map("user_id")
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

Agent → Wallets (1:N)

Each agent can have multiple wallets (one per trading mode).

model Agent {
  wallets AgentWallet[]
}
 
model AgentWallet {
  walletAddress String @id
  agentId       String
  walletType    String // "simulation" | "live"
  agent         Agent  @relation(fields: [agentId], references: [id], onDelete: Cascade)
  
  @@unique([agentId, walletType])  // One wallet per mode per agent
}

Wallet → Positions/Transactions (1:N)

Wallets own positions and transactions. Deleting a wallet cascades to its data.

model AgentWallet {
  positions    AgentPosition[]
  transactions AgentTransaction[]
  balances     AgentBalance[]
}

Signal → Executions (1:N)

Each signal can be processed by multiple agents, tracked via SignalExecution.

model TradingSignal {
  id               Int               @id @default(autoincrement())
  signalExecutions SignalExecution[]
}
 
model SignalExecution {
  id       String        @id @default(uuid())
  signalId Int
  agentId  String
  status   String        // "success" | "failed" | "skipped"
  signal   TradingSignal @relation(fields: [signalId], references: [id])
  
  @@unique([signalId, agentId])  // One execution per agent per signal
}

Key Design Decisions

1. UUID Primary Keys

Most entities use UUIDs for distributed-friendly IDs:

id String @id @default(uuid()) @db.Uuid

Exception: TradingSignal uses auto-increment for simpler signal references.

2. Wallet as Natural Key

AgentWallet uses wallet address as primary key since it's already unique:

model AgentWallet {
  walletAddress String @id @db.VarChar(44)
}

3. Trading Config as JSON

Agent trading configuration is stored as JSON for flexibility:

model Agent {
  tradingConfig Json?  // AgentTradingConfig type
}

JSON storage allows schema evolution without migrations. The application layer validates against TypeScript types.

4. Cascade Deletes

Parent deletions cascade to children:

agent Agent @relation(fields: [agentId], references: [id], onDelete: Cascade)

5. Decimal Precision

Financial values use high-precision decimals:

Precision	Use Case
`Decimal(30, 18)`	Token prices, amounts (can be very small)
`Decimal(20, 8)`	USD/SOL values
`Decimal(10, 4)`	Percentages
`Decimal(5, 2)`	Stop loss percentages

Schema Location

packages/backend/src/infrastructure/database/
├── schema.prisma      # Schema definition
├── client.ts          # Prisma client singleton
├── migrations/        # Migration history
└── repositories/      # Repository implementations

Shared Package Tables Reference