Database Setup - Kuest Prediction Market

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

Go to https://supabase.com and create a new project
Wait for the project to initialize (usually 2-3 minutes)
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:

Authentication Tables

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

Events Tables

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

Orders Tables

User trading history and order management.

orders: All user orders (market, limit, conditional)
Order status tracking and fill information
Links to events and outcomes

Bookmarks Tables

User-saved events for quick access.

bookmarks: User event bookmarks
Timestamps for sorting

Notifications Tables

In-app and email notifications.

notifications: User notification queue
Read/unread status
Notification types and content

Affiliates Tables

Referral tracking and affiliate management.

affiliates: Affiliate relationships
Referral codes and conversion tracking

Settings Tables

Application configuration and cached data.

settings: Global application settings
Cached translations and sports menu
Tag definitions

Subgraph Tables

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

Supabase with pg_cron

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.

Self-Hosted or External Scheduler

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

Error: POSTGRES_URL is not set

The database connection string is missing. Add POSTGRES_URL to your .env file:

POSTGRES_URL="postgresql://user:password@host:5432/database"

Error: SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY must be set together

If you set one Supabase variable, you must set both. Either:

Set both variables for Supabase mode
Remove both and use S3 configuration instead

Migration fails with 'relation already exists'

A previous migration was partially applied. Options:

Safe: Manually insert the migration version into the migrations table
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');

Cron jobs not created

Check that:

pg_cron extension is installed: SELECT * FROM pg_extension WHERE extname = 'pg_cron';
pg_net extension is installed (Supabase only)
CRON_SECRET environment variable is set

If extensions are unavailable, use Vercel Cron Jobs or external schedulers.

Connection timeout errors

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

​Overview

​Database Requirements

​Configuration Options

Supabase

Self-Hosted

​Supabase Setup

​Step 1: Create a Supabase Project

​Step 2: Configure Environment Variables

​Step 3: Enable Extensions

​Step 4: Run Migrations

​Self-Hosted PostgreSQL Setup

​Step 1: Provision a PostgreSQL Database

​Step 2: Configure Connection String

​Step 3: Configure Storage

​Step 4: Run Migrations

​Database Schema

​Migration Process

​Automatic Migration Tracking

​Environment Detection

​Transaction Safety

​Scheduled Jobs

​Connection Pooling

​Troubleshooting

​Next Steps

Storage Configuration

Authentication

Environment Variables

Deploy to Vercel

Overview

Database Requirements

Configuration Options

Supabase Setup

Step 1: Create a Supabase Project

Step 2: Configure Environment Variables

Step 3: Enable Extensions

Step 4: Run Migrations

Self-Hosted PostgreSQL Setup

Step 1: Provision a PostgreSQL Database

Step 2: Configure Connection String

Step 3: Configure Storage

Step 4: Run Migrations

Database Schema

Migration Process

Automatic Migration Tracking

Environment Detection

Transaction Safety

Scheduled Jobs

Connection Pooling

Troubleshooting

Next Steps