Neon Database Provisioning

Guide for determining if a viniapp needs a database and how to provision one using Neon.

When to Apply

Reference this documentation when:

•Building a viniapp that needs to persist user data
•Creating leaderboards, scores, or rankings
•Storing user preferences or settings
•Managing application state that survives sessions
•Building social features (comments, likes, follows)
•Any feature requiring data that persists beyond client-side storage

Step 1: Determine If Database Is Needed

Ask these questions about the viniapp:

Feature	Needs Database?
Leaderboards / High scores	Yes
User profiles / preferences	Yes
Comments / Posts / Social content	Yes
Transaction history	Yes
Game progress that persists	Yes
Multi-user interactions	Yes
Analytics / Usage tracking	Yes
Static content only	No
Single-session games	No
Wallet-only interactions	No
Read-only blockchain data	No

Decision Rule: If the app needs to remember ANYTHING about users or content across sessions that isn't stored on-chain, it needs a database.

Step 2: Create Neon Project

Use the Neon MCP tool to create a new project:

code

Tool: neon___create_project
Parameters:
  name: "viniapp-{app-id}-{app-name}" (must)

This will return:

•Project ID
•Branch ID
•Database name (default: neondb)
•Connection string with credentials

Step 3: Save Credentials to Viniapp

Add these environment variables to packages/nextjs/.env:

env

# Neon Database
DATABASE_URL="postgresql://user:password@host/neondb?sslmode=require"
POSTGRES_HOST="ep-xxx.region.aws.neon.tech"
POSTGRES_DATABASE="neondb"
POSTGRES_USER="neondb_owner"
POSTGRES_PASSWORD="your-password"

Important:

•Use the pooled connection string for serverless (contains -pooler in host)

Step 4: Create Database Schema

Use the Neon MCP tools to create tables:

code

Tool: neon___run_sql
Parameters:
  projectId: "your-project-id"
  sql: "CREATE TABLE users (id SERIAL PRIMARY KEY, fid INTEGER UNIQUE, ...);"

Common Schema Patterns for Viniapps

User Table (links Farcaster FID):

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  fid INTEGER UNIQUE NOT NULL,
  username VARCHAR(50),
  display_name VARCHAR(100),
  pfp_url TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Leaderboard Table:

sql

CREATE TABLE leaderboard (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  score INTEGER NOT NULL,
  game_mode VARCHAR(50),
  achieved_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_leaderboard_score ON leaderboard(score DESC);

User Preferences:

sql

CREATE TABLE preferences (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) UNIQUE,
  settings JSONB DEFAULT '{}',
  updated_at TIMESTAMP DEFAULT NOW()
);

Step 5: Install Database Client

Add a PostgreSQL client to the Next.js package:

bash

cd packages/nextjs
yarn add @neondatabase/serverless

Step 6: Create Database Utility

Create packages/nextjs/utils/db.ts:

typescript

import { neon } from '@neondatabase/serverless';

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

export { sql };

Step 7: Test Database Connection

⛔ CRITICAL: Verify the database is working before proceeding.

7.1 Test with MCP Tool

Run a simple query to verify the connection:

code

Tool: neon___run_sql
Parameters:
  projectId: "your-project-id"
  sql: "SELECT 1 as test;"

Expected result: Returns [{ test: 1 }]

7.2 Verify Tables Exist

code

Tool: neon___get_database_tables
Parameters:
  projectId: "your-project-id"

⛔ STOP if no tables are returned - go back and create the schema.

7.3 Test API Route

Create a simple health check API route to verify the app can connect:

Create packages/nextjs/app/api/health/route.ts:

typescript

import { sql } from '~~/utils/db';
import { NextResponse } from 'next/server';

export async function GET() {
  try {
    const result = await sql`SELECT NOW() as server_time`;
    return NextResponse.json({ 
      status: 'ok', 
      database: 'connected',
      serverTime: result[0].server_time 
    });
  } catch (error) {
    console.error('Database connection error:', error);
    return NextResponse.json({ 
      status: 'error', 
      database: 'disconnected',
      error: error instanceof Error ? error.message : 'Unknown error'
    }, { status: 500 });
  }
}

7.4 Verification Checklist

Before proceeding, confirm ALL of these:

• neon___run_sql with SELECT 1 succeeds
• neon___get_database_tables shows your tables
• .env file contains valid DATABASE_URL
• utils/db.ts file exists with neon client
• Health check API route returns { status: 'ok', database: 'connected' }

⛔ DO NOT proceed to build features until all checks pass.

Step 8: Use in API Routes

Example API route packages/nextjs/app/api/user/route.ts:

typescript

import { sql } from '~~/utils/db';
import { NextResponse } from 'next/server';

export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const fid = searchParams.get('fid');
  
  if (!fid) {
    return NextResponse.json({ error: 'FID required' }, { status: 400 });
  }
  
  const users = await sql`SELECT * FROM users WHERE fid = ${fid}`;
  return NextResponse.json(users[0] || null);
}

export async function POST(request: Request) {
  const { fid, username, displayName, pfpUrl } = await request.json();
  
  const result = await sql`
    INSERT INTO users (fid, username, display_name, pfp_url)
    VALUES (${fid}, ${username}, ${displayName}, ${pfpUrl})
    ON CONFLICT (fid) DO UPDATE SET
      username = EXCLUDED.username,
      display_name = EXCLUDED.display_name,
      pfp_url = EXCLUDED.pfp_url,
      updated_at = NOW()
    RETURNING *
  `;
  
  return NextResponse.json(result[0]);
}

Useful Neon MCP Commands

Command	Purpose
`neon___create_project`	Create new database project
`neon___list_projects`	List existing projects
`neon___describe_project`	Get project details
`neon___run_sql`	Execute SQL queries
`neon___run_sql_transaction`	Execute multiple SQL statements
`neon___get_database_tables`	List all tables
`neon___describe_table_schema`	Get table structure
`neon___get_connection_string`	Get connection details
`neon___create_branch`	Create database branch for testing

Step 9: Data Ownership & Security

⛔ CRITICAL: Analyze data ownership BEFORE building API routes.

9.1 Data Classification

For each table, determine the ownership model:

Data Type	Ownership	Auth Required?	Example
User-owned	Single user	Yes, verify owner	Todos, notes, settings
User-created public	Creator, but public read	Yes for write, No for read	Posts, comments
Shared/collaborative	Multiple users	Yes, verify membership	Shared lists, teams
Public	None	No	Leaderboards, public stats
System	App-owned	Admin only	Config, analytics

9.2 Schema Design for Ownership

Always include owner_fid for user-owned data:

sql

CREATE TABLE todos (
  id SERIAL PRIMARY KEY,
  owner_fid INTEGER NOT NULL,  -- Farcaster ID of owner
  title VARCHAR(255) NOT NULL,
  completed BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Index for fast user lookups
CREATE INDEX idx_todos_owner ON todos(owner_fid);

For shared data, use a junction table:

sql

CREATE TABLE lists (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  created_by_fid INTEGER NOT NULL
);

CREATE TABLE list_members (
  list_id INTEGER REFERENCES lists(id),
  fid INTEGER NOT NULL,
  role VARCHAR(20) DEFAULT 'member', -- 'owner', 'admin', 'member'
  PRIMARY KEY (list_id, fid)
);

9.3 API Route Security Patterns

⚠️ NEVER trust client-provided FID for ownership checks.

Use Quick Auth to get the authenticated FID (see farcaster-docs skill).

Pattern 1: User-Owned Data (CRUD)

typescript

// ALWAYS filter by authenticated user's FID
const fid = await requireAuth(request);

// READ - only user's data
const items = await sql`SELECT * FROM todos WHERE owner_fid = ${fid}`;

// CREATE - set owner to authenticated user
await sql`INSERT INTO todos (owner_fid, title) VALUES (${fid}, ${title})`;

// UPDATE/DELETE - verify ownership in WHERE clause
await sql`UPDATE todos SET completed = true WHERE id = ${id} AND owner_fid = ${fid}`;
await sql`DELETE FROM todos WHERE id = ${id} AND owner_fid = ${fid}`;

Pattern 2: Public Read, Authenticated Write

typescript

// GET - no auth required for public data
export async function GET() {
  const posts = await sql`SELECT * FROM posts ORDER BY created_at DESC LIMIT 50`;
  return NextResponse.json(posts);
}

// POST - requires auth, sets owner
export async function POST(request: Request) {
  const fid = await requireAuth(request);
  const { content } = await request.json();
  
  const result = await sql`
    INSERT INTO posts (author_fid, content) 
    VALUES (${fid}, ${content}) 
    RETURNING *
  `;
  return NextResponse.json(result[0]);
}

Pattern 3: Shared Access

typescript

// Verify user has access to resource
async function verifyAccess(fid: number, listId: number): Promise<boolean> {
  const access = await sql`
    SELECT 1 FROM list_members 
    WHERE list_id = ${listId} AND fid = ${fid}
  `;
  return access.length > 0;
}

export async function GET(request: Request) {
  const fid = await requireAuth(request);
  const listId = parseInt(request.url.split('/').pop()!);
  
  if (!await verifyAccess(fid, listId)) {
    return NextResponse.json({ error: 'Access denied' }, { status: 403 });
  }
  
  const items = await sql`SELECT * FROM list_items WHERE list_id = ${listId}`;
  return NextResponse.json(items);
}

9.4 Security Checklist

Before building API routes, verify:

• Each table has clear ownership model defined
• User-owned tables have owner_fid column indexed
• All mutating endpoints (POST/PUT/DELETE) require authentication
• Read endpoints for private data require authentication
• Ownership verified in SQL WHERE clause, not just API logic
• No endpoint exposes other users' private data

⛔ DO NOT proceed with API routes until ownership model is defined.

Best Practices

•Use Branches for Development: Create a branch for testing schema changes before applying to main
•Index Frequently Queried Columns: Especially for leaderboards and lookups
•Use Connection Pooling: The -pooler endpoint handles serverless cold starts better
•Store FID as Primary Identifier: Link all user data to Farcaster FID for easy lookups
•Use JSONB for Flexible Data: Good for user settings and dynamic attributes
•Implement Soft Deletes: Use deleted_at timestamp instead of actual deletion

Troubleshooting

Connection Issues:

•Verify DATABASE_URL is set correctly
•Check SSL mode is require
•Use pooled connection string for serverless

Permission Errors:

•Default role neondb_owner has full permissions
•For restricted access, create additional roles

Performance:

•Add indexes for frequently queried columns
•Use EXPLAIN ANALYZE to debug slow queries
•Consider read replicas for high-traffic apps