Skip to main content
Deltalytix uses PostgreSQL with Prisma ORM for data persistence. The application requires connection pooling for optimal performance.

​
PostgreSQL Setup

​
Requirements

  • PostgreSQL 14 or higher
  • Connection pooling (PgBouncer or Supabase Pooler)
  • At least 1GB RAM allocated to database

​
Connection URLs

Deltalytix requires two connection strings:
​
DATABASE_URL
string
required
Pooled connection - Used for application queries at runtime
DATABASE_URL="postgresql://user:password@host:6543/database?pgbouncer=true&connection_limit=1"
Key parameters:
  • Port 6543 - Default PgBouncer port (or your pooler port)
  • pgbouncer=true - Enables transaction mode compatibility
  • connection_limit=1 - Prevents pool exhaustion in serverless environments
​
DIRECT_URL
string
required
Direct connection - Used for migrations and schema changes
DIRECT_URL="postgresql://user:password@host:5432/database"
Key parameters:
  • Port 5432 - Standard PostgreSQL port
  • No pooling parameters - Direct database access
If using Supabase, find these URLs in your project settings under Database > Connection String. Use “Connection pooling” for DATABASE_URL and “Direct connection” for DIRECT_URL.

​
Prisma Configuration

The Prisma schema is located at prisma/schema.prisma: 
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
  schemas   = ["public"]
}

generator client {
  provider = "prisma-client"
  output   = "./generated/prisma"
}

​
Connection Pooling with PgBouncer

The application uses Prisma’s PostgreSQL adapter for connection pooling:
server/billing.ts
import { PrismaClient } from '@/prisma/generated/prisma/client'
import { PrismaPg } from '@prisma/adapter-pg'
import pg from 'pg'

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
})

const adapter = new PrismaPg(pool)
const prisma = new PrismaClient({ adapter })
This configuration:
  • Creates a connection pool managed by node-postgres
  • Wraps the pool with Prisma’s PostgreSQL adapter
  • Enables efficient connection reuse in serverless environments

​
Database Schema

Deltalytix maintains a comprehensive schema with the following core models:

​
User Management

  • User - User accounts linked to Supabase Auth
  • DashboardLayout - Customizable dashboard configurations
  • Notification - User notifications

​
Trading Data

  • Trade - Individual trade records with P&L, images, tags
  • Order - Order execution details
  • Account - Trading accounts with prop firm rules
  • Group - Account groupings
  • Payout - Account payout tracking
  • TradeAnalytics - Computed metrics (MAE, MFE, risk/reward)
  • HistoricalData - Market data for analysis

​
Subscriptions

  • Subscription - Individual user subscriptions
  • TeamSubscription - Team-based subscriptions
  • BusinessSubscription - Business-tier subscriptions
  • SubscriptionFeedback - Cancellation feedback

​
Collaboration

  • Team - Team structures
  • TeamManager - Team access control
  • TeamInvitation - Pending team invitations
  • Business - Business entities
  • BusinessManager - Business access control
  • BusinessInvitation - Pending business invitations

​
Additional Features

  • Mood - Daily mood tracking and journaling
  • Tag - Custom trade tags
  • Post - Feature requests and bug reports
  • Comment - Discussion threads
  • Vote - Feature voting
  • Shared - Shared dashboard links
  • Referral - Referral tracking
  • TickDetails - Instrument tick specifications
  • FinancialEvent - Economic calendar events
  • Synchronization - Third-party service sync status

​
Running Migrations

​
Initial Setup

  1. Install dependencies: 
    npm install
  2. Generate Prisma Client: 
    npx prisma generate
  3. Run migrations: 
    npx prisma migrate deploy
Ensure DIRECT_URL is configured before running migrations. Migrations cannot run through connection poolers.

​
Development Workflow

Create a new migration: 
npx prisma migrate dev --name descriptive_migration_name
This will:
  1. Create a new migration file
  2. Apply it to your database
  3. Regenerate Prisma Client
Reset database (development only): 
npx prisma migrate reset
migrate reset will delete all data. Only use in development.

​
Production Deployment

Apply pending migrations: 
npx prisma migrate deploy
This command:
  • Applies all pending migrations
  • Does not create new migrations
  • Safe for CI/CD pipelines

​
Database Management

​
Prisma Studio

Launch a GUI to browse and edit data: 
npx prisma studio
Access at http://localhost:5555

​
Introspection

Pull schema changes from database: 
npx prisma db pull
Use this when:
  • Syncing schema from another environment
  • Reverse-engineering an existing database

​
Schema Validation

Validate schema syntax: 
npx prisma validate

​
Format Schema

Format schema.prisma file: 
npx prisma format

​
Backup and Restore

​
Backup Database

pg_dump -h host -U username -d database -F c -f backup.dump

​
Restore Database

pg_restore -h host -U username -d database backup.dump
Always test backups in a non-production environment before relying on them.

​
Connection Pool Settings

pgbouncer.ini
[databases]
deltalytix = host=localhost port=5432 dbname=deltalytix

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3

​
Supabase Pooler

Supabase automatically configures connection pooling. Use the provided connection pooling URL from your project settings.

​
Troubleshooting

​
Connection Pool Exhausted

Error: Error: Connection pool exhausted Solution:
  • Ensure connection_limit=1 in DATABASE_URL
  • Increase pool size in PgBouncer/Supabase settings
  • Check for connection leaks (unclosed transactions)

​
Migration Failed

Error: Migration failed to apply Solution:
  • Verify DIRECT_URL is set correctly (not pooled)
  • Check database permissions (CREATE, ALTER, DROP)
  • Review migration logs: prisma migrate status

​
Schema Out of Sync

Error: Prisma schema is not in sync with database Solution: 
npx prisma migrate deploy  # Apply pending migrations
npx prisma generate        # Regenerate client

​
SSL Connection Issues

Error: SSL connection failed Solution: Add SSL parameters to connection string: 
postgresql://user:password@host:5432/db?sslmode=require

​
Performance Optimization

​
Indexes

The schema includes strategic indexes on:
  • Foreign keys for relationship queries
  • Email addresses for user lookups
  • Account numbers for trade filtering
  • Date ranges for analytics queries
  • Status fields for subscription checks

​
Query Optimization

  • Use select to limit returned fields
  • Leverage include for efficient joins
  • Implement pagination with take and skip
  • Use findUnique instead of findFirst when possible

​
Connection Management

  • Always use connection pooling in production
  • Set appropriate pool sizes based on load
  • Monitor active connections
  • Close connections in serverless functions (handled by Prisma adapter)

​
Next Steps

Build docs developers (and LLMs) love

Get started for freeTalk to us