Database Setup - Studley AI

Overview

Studley AI uses PostgreSQL as its primary database, with support for both Neon Database and Supabase PostgreSQL. This guide covers setup, migrations, and best practices.

Database Provider Options

Supabase

Recommended for production. Includes auth, storage, and real-time features.

Neon

Serverless PostgreSQL with excellent scaling and branching.

Prerequisites

Required Extensions

Your PostgreSQL database must support:

pgcrypto - For UUID generation
PostgreSQL 12+ recommended

Connection Pooling

Studley AI uses connection pooling for optimal performance:

Max connections: 10
Idle timeout: 30 seconds
Connection timeout: 2 seconds

Initial Setup

Option 1: Supabase (Recommended)

Create Supabase Project

Go to Supabase Dashboard
Click “New Project”
Enter project details:
- Name: studley-ai
- Database Password: (save this securely)
- Region: (choose closest to your users)

Get Connection Details

Navigate to Project Settings > Database and copy:

Connection String → SUPABASE_POSTGRES_URL
Also get from Project Settings > API:
- Project URL → NEXT_PUBLIC_SUPABASE_URL
- anon public key → NEXT_PUBLIC_SUPABASE_ANON_KEY
- service_role key → SUPABASE_SERVICE_ROLE_KEY

Configure Environment

Add to your .env.local:

SUPABASE_POSTGRES_URL="postgresql://postgres:[password]@db.xxx.supabase.co:5432/postgres"
DATABASE_URL="postgresql://postgres:[password]@db.xxx.supabase.co:5432/postgres"
NEXT_PUBLIC_SUPABASE_URL="https://xxxxx.supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
SUPABASE_SERVICE_ROLE_KEY="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."

Option 2: Neon Database

Create Neon Project

Go to Neon Console
Click “Create Project”
Choose your region and PostgreSQL version

Get Connection String

Copy the connection string from the Neon dashboard.

DATABASE_URL="postgresql://user:[email protected]/studley?sslmode=require"

Option 3: Local PostgreSQL

# Run PostgreSQL with Docker
docker run -d \
  --name studley-postgres \
  -e POSTGRES_USER=studley \
  -e POSTGRES_PASSWORD=studley \
  -e POSTGRES_DB=studley \
  -p 5432:5432 \
  postgres:15-alpine

# Connection string
DATABASE_URL="postgresql://studley:studley@localhost:5432/studley"

Running Migrations

Studley AI uses SQL migration scripts for database schema management.

Migration Scripts Location

All migration scripts are in the scripts/ directory:

scripts/
├── 001_initialize_database.sql      # Core tables (users, sessions, etc.)
├── 002_create_admin_config_table.sql
├── 004_create_shared_sessions_table.sql
├── 007_create_rate_limit_table.sql
├── 007_create_feedback_tables.sql
├── 010_create_user_profiles.sql     # Supabase user profiles
├── 013_create_ai_workspace_tables.sql
├── 025_create_shared_materials_table.sql
├── 026_create_notifications_table.sql
└── ...

Execute Migrations

Using psql
Using Supabase Dashboard
Using Node.js

Run migrations in order using psql:

# Set your database URL
export DATABASE_URL="postgresql://user:pass@host:5432/studley"

# Run initial migration
psql $DATABASE_URL -f scripts/001_initialize_database.sql

# Run subsequent migrations in order
psql $DATABASE_URL -f scripts/002_create_admin_config_table.sql
psql $DATABASE_URL -f scripts/004_create_shared_sessions_table.sql
# ... continue with other scripts

Go to SQL Editor in Supabase Dashboard
Create a new query
Copy contents of scripts/001_initialize_database.sql
Click “Run”
Repeat for each migration script in order

Create a migration runner script:

migrate.js

const { neon } = require('@neondatabase/serverless');
const fs = require('fs');
const path = require('path');

const sql = neon(process.env.DATABASE_URL);

async function runMigrations() {
  const scriptsDir = path.join(__dirname, 'scripts');
  const files = fs.readdirSync(scriptsDir)
    .filter(f => f.endsWith('.sql'))
    .sort();
  
  for (const file of files) {
    console.log(`Running ${file}...`);
    const sqlContent = fs.readFileSync(
      path.join(scriptsDir, file),
      'utf-8'
    );
    await sql(sqlContent);
    console.log(`✓ ${file} completed`);
  }
  
  console.log('All migrations completed!');
}

runMigrations().catch(console.error);

Run with:

node migrate.js

Core Schema Overview

Authentication Tables

-- Users table
CREATE TABLE users (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  name TEXT,
  email TEXT UNIQUE NOT NULL,
  "emailVerified" TIMESTAMP,
  password TEXT NOT NULL,
  image TEXT,
  credits INTEGER DEFAULT 5000,
  "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sessions table
CREATE TABLE sessions (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "sessionToken" TEXT UNIQUE NOT NULL,
  "userId" TEXT NOT NULL,
  expires TIMESTAMP NOT NULL,
  FOREIGN KEY ("userId") REFERENCES users(id) ON DELETE CASCADE
);

Application Tables

-- Credit usage tracking
CREATE TABLE credit_usage (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "userId" TEXT NOT NULL,
  amount INTEGER NOT NULL,
  type TEXT NOT NULL,
  description TEXT,
  "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- AI generations
CREATE TABLE generations (
  id TEXT PRIMARY KEY DEFAULT gen_random_uuid()::text,
  "userId" TEXT NOT NULL,
  type TEXT NOT NULL,
  topic TEXT NOT NULL,
  content TEXT NOT NULL,
  "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Rate limiting
CREATE TABLE generation_rate_limits (
  id BIGSERIAL PRIMARY KEY,
  identifier TEXT NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

Verify Setup

Test your database connection:

test-db.js

const { Pool } = require('@neondatabase/serverless');

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

async function testConnection() {
  try {
    const client = await pool.connect();
    const result = await client.query('SELECT NOW()');
    console.log('✓ Database connected:', result.rows[0]);
    
    // Check if tables exist
    const tables = await client.query(`
      SELECT tablename FROM pg_tables 
      WHERE schemaname = 'public'
      ORDER BY tablename
    `);
    
    console.log('\nTables:');
    tables.rows.forEach(row => console.log(`  - ${row.tablename}`));
    
    client.release();
  } catch (err) {
    console.error('✗ Database connection failed:', err);
  } finally {
    await pool.end();
  }
}

testConnection();

Run with:

node test-db.js

Backup and Maintenance

Supabase
Neon
Manual Backup

Supabase provides automatic daily backups. Access them via:

Project Settings > Database
Scroll to “Backups”
Download or restore as needed

Create manual backups with pg_dump:

# Full database backup
pg_dump $DATABASE_URL > backup_$(date +%Y%m%d).sql

# Restore from backup
psql $DATABASE_URL < backup_20260307.sql

Troubleshooting

Connection timeout errors

Error: Connection terminated unexpectedlySolutions:

Check firewall rules allow PostgreSQL port (5432)
Verify SSL mode: add ?sslmode=require to connection string
Increase connection timeout in lib/db.ts

UUID generation errors

Error: function gen_random_uuid() does not existSolution: Enable the pgcrypto extension:

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

Migration already applied

Error: Table already existsSolution: Migrations use IF NOT EXISTS. If you see errors:

Check if migration was already run
Review migration script for idempotency
Skip to next migration if table exists

Permission denied errors

Error: permission denied for schema publicSolution: Grant necessary permissions:

GRANT ALL ON SCHEMA public TO your_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO your_user;

Next Steps

Authentication Setup

Configure Supabase auth and providers

Vercel Deployment

Deploy your application to production

AI Configuration

Set up Groq AI models

Environment Variables

Review all configuration options

Setup

Configuration

​Overview

​Database Provider Options

Supabase

Neon

​Prerequisites

​Initial Setup

​Option 1: Supabase (Recommended)

​Option 2: Neon Database

​Option 3: Local PostgreSQL

​Running Migrations

​Migration Scripts Location

​Execute Migrations

​Core Schema Overview

​Authentication Tables

​Application Tables

​Verify Setup

​Backup and Maintenance

​Troubleshooting

​Next Steps