Skip to main content

​
Overview

Kuest Prediction Market uses PostgreSQL as its primary database with Drizzle ORM for type-safe database operations. The application supports both Supabase and self-hosted PostgreSQL deployments.

​
Database Requirements

  • PostgreSQL: Version 14 or higher
  • Extensions (optional):
    • pg_cron: For database-level scheduled jobs
    • pg_net: For HTTP requests from database (Supabase only)
    • uuid-ossp or custom ULID function: For ID generation

​
Configuration Options

You have two main deployment options:

Supabase

Managed PostgreSQL with built-in storage, auth helpers, and scheduled jobs

Self-Hosted

Your own PostgreSQL instance with external storage (S3, MinIO, R2)

​
Supabase Setup

​
Step 1: Create a Supabase Project

  1. Go to https://supabase.com and create a new project
  2. Wait for the project to initialize (usually 2-3 minutes)
  3. Navigate to Project Settings → API

​
Step 2: Configure Environment Variables

Add these variables to your .env file:
.env
SUPABASE_URL="https://your-project.supabase.co"
SUPABASE_SERVICE_ROLE_KEY="your-service-role-key"
POSTGRES_URL="postgresql://postgres:[YOUR-PASSWORD]@db.your-project.supabase.co:5432/postgres"
The POSTGRES_URL for Supabase uses the direct database connection, not the pooled connection. Find this under Project Settings → Database → Connection string → Direct connection.

​
Step 3: Enable Extensions

Run these SQL commands in the Supabase SQL Editor: 
-- Required for ULID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Required for scheduled jobs (if available)
CREATE EXTENSION IF NOT EXISTS pg_cron WITH SCHEMA extensions;

-- Required for HTTP requests from cron jobs
CREATE EXTENSION IF NOT EXISTS pg_net;
Some Supabase plans may not have pg_cron or pg_net available. The application will detect this and skip scheduler setup. You can use Vercel Cron Jobs or external schedulers instead.

​
Step 4: Run Migrations

npm run db:push
This command will:
  • Connect to your database
  • Create a migrations tracking table
  • Apply all pending migrations in order
  • Set up the storage bucket (if using Supabase Storage)
  • Configure cron jobs (if extensions are available)

​
Self-Hosted PostgreSQL Setup

​
Step 1: Provision a PostgreSQL Database

Choose a hosting provider:
  • Neon - Serverless Postgres with free tier
  • Railway - Simple deployment platform
  • Digital Ocean - Managed databases
  • AWS RDS - Enterprise-grade
  • Self-hosted via Docker

​
Step 2: Configure Connection String

Add to your .env file:
.env
POSTGRES_URL="postgresql://user:password@host:5432/database?sslmode=require"
For Vercel deployments, you can also use POSTGRES_URL_NON_POOLING for migrations. The migration script automatically prefers this over POSTGRES_URL.

​
Step 3: Configure Storage

Since you’re not using Supabase, configure S3-compatible storage:
.env
S3_BUCKET="kuest-assets"
S3_REGION="us-east-1"
S3_ACCESS_KEY_ID="your-access-key"
S3_SECRET_ACCESS_KEY="your-secret-key"
See Storage Configuration for details.

​
Step 4: Run Migrations

npm run db:push
The migration script automatically detects you’re not using Supabase and:
  • Skips Supabase-specific policies
  • Replaces service_role with CURRENT_USER in policies
  • Skips storage bucket creation
  • Skips database scheduler setup

​
Database Schema

The application creates the following table groups:
Managed by Better Auth with custom fields for Web3 integration.
  • users: User accounts with wallet addresses, settings, and affiliate tracking
  • sessions: Active user sessions with device information
  • accounts: OAuth provider connections
  • wallets: Connected Web3 wallets (SIWE)
  • verifications: Email verification and 2FA codes
  • two_factors: TOTP secrets and backup codes
Prediction market events and outcomes.
  • events: Market events synced from Kuest CLOB
  • Event metadata including titles, descriptions, images
  • Resolution data and timestamps
  • Category and tag relationships
User trading history and order management.
  • orders: All user orders (market, limit, conditional)
  • Order status tracking and fill information
  • Links to events and outcomes
User-saved events for quick access.
  • bookmarks: User event bookmarks
  • Timestamps for sorting
In-app and email notifications.
  • notifications: User notification queue
  • Read/unread status
  • Notification types and content
Referral tracking and affiliate management.
  • affiliates: Affiliate relationships
  • Referral codes and conversion tracking
Application configuration and cached data.
  • settings: Global application settings
  • Cached translations and sports menu
  • Tag definitions
Cached blockchain data from The Graph.
  • Market volume statistics
  • Leaderboard data
  • Performance metrics

​
Migration Process

The migration system (scripts/migrate.js) provides:

​
Automatic Migration Tracking

// migrations table structure
CREATE TABLE migrations (
  version TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ DEFAULT NOW()
);
Migrations are applied in alphabetical order and tracked to prevent re-application.

​
Environment Detection

The script automatically detects your deployment mode: 
// Supabase mode
if (SUPABASE_URL && SUPABASE_SERVICE_ROLE_KEY) {
  // Use Supabase-specific SQL
  // Set up storage buckets
  // Configure pg_cron jobs
}

// Self-hosted mode
else {
  // Rewrite service_role policies
  // Skip storage bucket creation
  // Skip scheduler setup
}

​
Transaction Safety

Each migration runs in a transaction: 
await sql.begin(async (tx) => {
  await tx.unsafe(migrationSql)
  await tx`INSERT INTO migrations (version) VALUES (${version})`
})
If a migration fails, the entire transaction is rolled back.

​
Scheduled Jobs

When using Supabase with pg_cron and pg_net extensions enabled, the migration script automatically sets up:
  • sync-events (every 5 minutes): Fetch new events from Kuest CLOB
  • sync-resolution (every 5 minutes): Check for resolved markets
  • sync-translations (every 10 minutes): Update translated content
  • sync-volume (twice per hour): Update volume statistics
  • clean-jobs (every 15 minutes): Clean up old job queue entries
  • clean-cron-details (daily): Remove old cron execution logs
Jobs are automatically recreated on each npm run db:push to ensure they use the latest configuration.
For self-hosted PostgreSQL or if pg_cron is unavailable, configure external schedulers:Vercel Cron Jobs (vercel.json):
{
  "crons": [
    {
      "path": "/api/sync/events",
      "schedule": "1-59/5 * * * *"
    },
    {
      "path": "/api/sync/resolution",
      "schedule": "3-59/5 * * * *"
    },
    {
      "path": "/api/sync/translations",
      "schedule": "*/10 * * * *"
    },
    {
      "path": "/api/sync/volume",
      "schedule": "14,44 * * * *"
    }
  ]
}
All sync endpoints require Authorization: Bearer <CRON_SECRET> header.

​
Connection Pooling

The application uses postgres-js with connection pooling:
src/lib/drizzle.ts
const client = postgres(url, {
  prepare: false,
  connect_timeout: 10,
  idle_timeout: 20,
})
  • prepare: false: Disables prepared statements (required for PgBouncer)
  • connect_timeout: 10 seconds to establish connection
  • idle_timeout: 20 seconds before closing idle connections
For production deployments, use connection pooling middleware like PgBouncer or Supabase’s built-in pooler to handle high concurrency.

​
Troubleshooting

The database connection string is missing. Add POSTGRES_URL to your .env file:
POSTGRES_URL="postgresql://user:password@host:5432/database"
If you set one Supabase variable, you must set both. Either:
  1. Set both variables for Supabase mode
  2. Remove both and use S3 configuration instead
A previous migration was partially applied. Options:
  1. Safe: Manually insert the migration version into the migrations table
  2. Destructive: Drop the database and re-run migrations (loses all data)
-- Option 1: Mark migration as applied
INSERT INTO migrations (version) VALUES ('2025_08_28_004_auth');
Check that:
  1. pg_cron extension is installed: SELECT * FROM pg_extension WHERE extname = 'pg_cron';
  2. pg_net extension is installed (Supabase only)
  3. CRON_SECRET environment variable is set
If extensions are unavailable, use Vercel Cron Jobs or external schedulers.
Increase the connection timeout in your database configuration:
const client = postgres(url, {
  connect_timeout: 30, // Increase from 10
  idle_timeout: 60,    // Increase from 20
})
Also check:
  • Database server is accessible
  • Firewall rules allow connections
  • SSL/TLS configuration is correct

​
Next Steps

Storage Configuration

Set up file storage for user uploads

Authentication

Configure wallet authentication

Environment Variables

Complete variable reference

Deploy to Vercel

Deploy your application

Build docs developers (and LLMs) love

Get started for freeTalk to us