Database (Prisma)

Nexgent uses PostgreSQL as the primary database with Prisma as the ORM. This provides type-safe database access with automatic migration management.

Schema Location

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

Entity Relationship Diagram

┌─────────────┐       ┌─────────────┐       ┌─────────────────┐
│    User     │──────<│    Agent    │──────<│   AgentWallet   │
│             │  1:N  │             │  1:N  │                 │
└─────────────┘       └─────────────┘       └────────┬────────┘
      │                     │                        │
      │ 1:N                 │ 1:N                    │ 1:N
      ▼                     ▼                        ▼
┌─────────────┐       ┌─────────────┐       ┌─────────────────┐
│   ApiKey    │       │ AgentBalance│       │ AgentPosition   │
└─────────────┘       └─────────────┘       └─────────────────┘
                            │                        │
                            │ 1:N                    │ 1:1
                            ▼                        ▼
                      ┌─────────────┐       ┌─────────────────┐
                      │AgentBalance │       │AgentTransaction │
                      │  Snapshot   │       │                 │
                      └─────────────┘       └────────┬────────┘
                                                     │
┌─────────────────┐                                  │ N:1
│ TradingSignal   │──────────────────────────────────┤
└────────┬────────┘                                  │
         │ 1:N                                       │
         ▼                                           ▼
┌─────────────────┐                         ┌───────────────────┐
│SignalExecution  │                         │AgentHistoricalSwap│
└─────────────────┘                         └───────────────────┘

Core Entities

User

Stores user accounts for authentication.

model User {
  id                  String    @id @default(uuid()) @db.Uuid
  email               String    @unique @db.VarChar(255)
  passwordHash        String?   @map("password_hash")
  createdAt           DateTime  @default(now())
  failedLoginAttempts Int       @default(0)
  lockedUntil         DateTime?
  agents              Agent[]
  apiKeys             ApiKey[]
}

Agent

Trading agents created by users. Contains trading configuration as JSON.

model Agent {
  id                         String   @id @default(uuid()) @db.Uuid
  userId                     String   @map("user_id") @db.Uuid
  name                       String   @db.VarChar(255)
  tradingMode                String   @default("simulation") // "simulation" | "live"
  automatedTradingSimulation Boolean  @default(true)
  automatedTradingLive       Boolean  @default(true)
  tradingConfig              Json?    // AgentTradingConfig
  // ... relations
}

AgentWallet

Links wallets to agents. Private keys are never stored in the database.

model AgentWallet {
  walletAddress String   @id @db.VarChar(44)
  agentId       String   @map("agent_id") @db.Uuid
  walletType    String   // "simulation" | "live"
  // ... relations
  
  @@unique([agentId, walletType])  // One wallet per mode
}

⚠️

Private keys are loaded from environment variables (WALLET_1, WALLET_2, etc.) at startup and stored only in memory.

AgentPosition

Tracks open positions with dynamic stop loss state.

model AgentPosition {
  id                        String   @id @default(uuid()) @db.Uuid
  agentId                   String
  walletAddress             String
  tokenAddress              String
  tokenSymbol               String
  purchaseTransactionId     String   @unique
  purchasePrice             Decimal  @db.Decimal(30, 18)
  purchaseAmount            Decimal  @db.Decimal(30, 18)
  currentStopLossPercentage Decimal? @db.Decimal(5, 2)
  peakPrice                 Decimal? @db.Decimal(30, 18)
  dcaCount                  Int      @default(0)
  totalInvestedSol          Decimal  @default(0) @db.Decimal(20, 8)
  // ... timestamps
  
  @@unique([agentId, walletAddress, tokenAddress])
}

AgentTransaction

Records all transactions (deposits, withdrawals, swaps, burns).

model AgentTransaction {
  id                  String          @id @default(uuid()) @db.Uuid
  agentId             String
  walletAddress       String?
  transactionType     TransactionType // DEPOSIT, WITHDRAWAL, SWAP, BURN
  transactionValueUsd Decimal         @db.Decimal(20, 8)
  transactionTime     DateTime
  inputMint           String?
  inputAmount         Decimal?        @db.Decimal(30, 18)
  outputMint          String?
  outputAmount        Decimal?        @db.Decimal(30, 18)
  transactionHash     String?         // On-chain hash (null for simulation)
  swapPayload         Json?           // Jupiter response
  isDca               Boolean         @default(false)
  // ... relations
}

TradingSignal

Trading signals that trigger agent executions.

model TradingSignal {
  id               Int      @id @default(autoincrement())
  tokenAddress     String
  symbol           String?
  signalType       String   // e.g., "buy", "sell"
  signalStrength   Int      // 1-100
  activationReason String?
  source           String?
  // ... relations
}

Repository Pattern

Each entity has a repository interface (domain layer) and implementation (infrastructure layer).

Interface Example

// src/domain/agents/agent.repository.ts
export interface IAgentRepository {
  findById(id: string): Promise<Agent | null>;
  findByUserId(userId: string): Promise<Agent[]>;
  findWalletByAgentId(agentId: string, walletType: 'simulation' | 'live'): Promise<AgentWallet | null>;
  findWalletByAddress(address: string): Promise<AgentWallet | null>;
  create(data: Prisma.AgentCreateInput): Promise<Agent>;
  update(id: string, data: Prisma.AgentUpdateInput): Promise<Agent>;
  delete(id: string): Promise<void>;
}

Implementation Example

// src/infrastructure/database/repositories/agent.repository.ts
export class AgentRepository implements IAgentRepository {
  async findById(id: string): Promise<Agent | null> {
    return prisma.agent.findUnique({
      where: { id },
      include: { wallets: true },
    });
  }
  
  async findByUserId(userId: string): Promise<Agent[]> {
    return prisma.agent.findMany({
      where: { userId },
      include: { wallets: true },
    });
  }
  // ...
}

Transactions

Critical operations use Prisma transactions for atomicity:

await prisma.$transaction(async (tx) => {
  // All operations use the transaction client
  await tx.agentTransaction.create({ data: transactionData });
  await tx.agentBalance.upsert({ ... });
  await tx.agentPosition.create({ ... });
});

After a transaction commits, Redis caches are updated to maintain consistency (write-through pattern).

Migrations

Prisma migrations track schema changes over time.

Running Migrations

# Development: Create and apply migrations
pnpm --filter backend db:migrate
 
# Production: Apply pending migrations
pnpm --filter backend db:migrate:deploy
 
# Generate Prisma client after schema changes
pnpm --filter backend db:generate

Migration History

migrations/
├── 20251211112932_init/
├── 20251212112507_remove_price_sol_from_trading_signals/
├── 20251213231745_add_swap_payload/
├── 20251220051448_add_agent_balance_snapshots/
├── 20251220230728_add_dca_fields_to_positions/
├── 20260106120000_add_automated_trading/
└── ...

Decimal Precision

Financial amounts use Decimal(30, 18) for maximum precision:

purchasePrice  Decimal @db.Decimal(30, 18)  // Token prices (very small)
purchaseAmount Decimal @db.Decimal(30, 18)  // Token amounts
profitLossUsd  Decimal @db.Decimal(20, 8)   // USD amounts

💡

Some token prices are extremely small (e.g., 6.632e-11 SOL per token). The high precision prevents rounding errors in P/L calculations.

Indexing Strategy

Frequently queried columns have indexes:

@@index([agentId])
@@index([walletAddress])
@@index([agentId, walletAddress])
@@index([tokenAddress])
@@index([signalId])
@@index([transactionTime])

Database URL

Configure via environment variable:

DATABASE_URL="postgresql://user:password@localhost:5432/nexgent?schema=public"

Domain Services Caching (Redis)