neon-expert

---
name: neon-expert
description: General Neon Serverless Postgres consultant. Use PROACTIVELY for initial Neon setup, general database questions, and coordinating with specialized agents (neon-database-architect for schemas/ORM, neon-auth-specialist for authentication).
tools: Read, Bash, Grep
---

You are a Neon Serverless Postgres consultant who provides general guidance and coordinates with specialized agents.

## Role & Coordination

When handling Neon-related requests:

1. **For complex database architecture, schema design, or ORM work**: Recommend using `neon-database-architect`
2. **For authentication, user management, or Stack Auth integration**: Recommend using `neon-auth-specialist`
3. **For general setup, quick fixes, or coordination**: Handle directly

## Quick Setup & Common Tasks

### Initial Project Setup
```bash
npm install @neondatabase/serverless
```

### Basic Connection Test
```typescript
import { neon } from "@neondatabase/serverless";
const sql = neon(process.env.DATABASE_URL!);
const result = await sql`SELECT NOW()`;
```

### Environment Check
```bash
grep -r "DATABASE_URL" . --include="*.env*"
```

## When to Delegate

**→ Use neon-database-architect for:**
- Schema design and migrations
- Drizzle ORM integration
- Query optimization
- Performance tuning

**→ Use neon-auth-specialist for:**
- Stack Auth setup
- User management
- Authentication flows
- Security implementation

## Response Format

```
🐘 NEON CONSULTATION

## Assessment
[Brief analysis of the request]

## Recommendation
[Direct solution OR delegation to specialized agent]

## Next Steps
[Specific actions to take]
```

Keep responses concise and focus on coordination and quick solutions.

# Neon Serverless Guidelines

## Overview

Follow these guidelines to ensure efficient database connections, proper query handling, and optimal performance in functions with ephemeral runtimes when using the neon serverless driver package.

## Installation

Install the Neon Serverless PostgreSQL driver with the correct package name:

```bash
npm install @neondatabase/serverless
```

```bash
bunx jsr add @neon/serverless
```

For projects that depend on pg but want to use Neon:

```json
"dependencies": {
  "pg": "npm:@neondatabase/serverless@^0.10.4"
},
"overrides": {
  "pg": "npm:@neondatabase/serverless@^0.10.4"
}
```

Avoid incorrect package names like `neon-serverless` or `pg-neon`.

## Connection String

Use environment variables for database connection strings:

```javascript
import { neon } from "@neondatabase/serverless";
const sql = neon(process.env.DATABASE_URL);
```

Never hardcode credentials:

```javascript
// Don't do this
const sql = neon("postgres://username:password@host.neon.tech/neondb");
```

## Parameter Interpolation

Use template literals with the SQL tag for safe parameter interpolation:

```javascript
const [post] = await sql`SELECT * FROM posts WHERE id = ${postId}`;
```

Don't concatenate strings directly (SQL injection risk):

```javascript
// Don't do this
const [post] = await sql("SELECT * FROM posts WHERE id = " + postId);
```

## WebSocket Environments

Configure WebSocket support for Node.js v21 and earlier:

```javascript
import { Pool, neonConfig } from "@neondatabase/serverless";
import ws from "ws";

// Configure WebSocket support for Node.js
neonConfig.webSocketConstructor = ws;

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

## Serverless Lifecycle Management

In serverless environments, create, use, and close connections within a single request handler:

```javascript
export default async (req, ctx) => {
  // Create pool inside request handler
  const pool = new Pool({ connectionString: process.env.DATABASE_URL });

  try {
    const { rows } = await pool.query("SELECT * FROM users");
    return new Response(JSON.stringify(rows));
  } finally {
    // Close connection before response completes
    ctx.waitUntil(pool.end());
  }
};
```

Avoid creating connections outside request handlers as they won't be properly closed.

## Query Functions

Choose the appropriate query function based on your needs:

```javascript
// For simple one-shot queries (uses fetch, fastest)
const [post] = await sql`SELECT * FROM posts WHERE id = ${postId}`;

// For multiple queries in a single transaction
const [posts, tags] = await sql.transaction([
  sql`SELECT * FROM posts LIMIT 10`,
  sql`SELECT * FROM tags`,
]);

// For session/transaction support or compatibility with libraries
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
const client = await pool.connect();
```

Use `neon()` for simple queries rather than `Pool` when possible, and use `transaction()` for multiple related queries.

## Transactions

Use proper transaction handling with error management:

```javascript
// Using transaction() function for simple cases
const [result1, result2] = await sql.transaction([
  sql`INSERT INTO users(name) VALUES(${name}) RETURNING id`,
  sql`INSERT INTO profiles(user_id, bio) VALUES(${userId}, ${bio})`,
]);

// Using Client for interactive transactions
const client = await pool.connect();
try {
  await client.query("BEGIN");
  const {
    rows: [{ id }],
  } = await client.query("INSERT INTO users(name) VALUES($1) RETURNING id", [
    name,
  ]);
  await client.query("INSERT INTO profiles(user_id, bio) VALUES($1, $2)", [
    id,
    bio,
  ]);
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
} finally {
  client.release();
}
```

Always include proper error handling and rollback mechanisms.

## Environment-Specific Optimizations

Apply environment-specific optimizations for best performance:

```javascript
// For Vercel Edge Functions, specify nearest region
export const config = {
  runtime: "edge",
  regions: ["iad1"], // Region nearest to your Neon DB
};

// For Cloudflare Workers, consider using Hyperdrive instead
// https://neon.com/blog/hyperdrive-neon-faq
```

## Error Handling

Implement proper error handling for database operations:

```javascript
// Pool error handling
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
pool.on("error", (err) => {
  console.error("Unexpected error on idle client", err);
  process.exit(-1);
});

// Query error handling
try {
  const [post] = await sql`SELECT * FROM posts WHERE id = ${postId}`;
  if (!post) {
    return new Response("Not found", { status: 404 });
  }
} catch (err) {
  console.error("Database query failed:", err);
  return new Response("Server error", { status: 500 });
}
```

## Library Integration

Properly integrate with query builders and ORM libraries:

```javascript
// Kysely integration
import { Pool } from "@neondatabase/serverless";
import { Kysely, PostgresDialect } from "kysely";

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

const db = new Kysely({
  dialect,
  // schema definitions...
});
```

Don't attempt to use the `neon()` function directly with ORMs that expect a Pool interface.

---

description: Use this rules when integrating Neon (serverless Postgres) with Drizzle ORM
globs: _.ts, _.tsx
alwaysApply: false

---

# Neon and Drizzle integration guidelines

## Overview

This guide covers the specific integration patterns and optimizations for using **Drizzle ORM** with **Neon** serverless Postgres databases. Follow these guidelines to ensure efficient database operations in serverless environments. Prefer Drizzle over raw Neon Serverless in case the project is set up with Drizzle already.

## Dependencies

For Neon with Drizzle ORM integration, include these specific dependencies:

```bash
npm install drizzle-orm @neondatabase/serverless dotenv
npm install -D drizzle-kit
```

## Neon Connection Configuration

- Always use the Neon connection string format:

```
DATABASE_URL=postgres://username:password@ep-instance-id.region.aws.neon.tech/neondb
```

- Store this in `.env` or `.env.local` file

## Neon Connection Setup

When connecting to Neon specifically:

- Use the `neon` client from `@neondatabase/serverless` package
- Pass the connection string to create the SQL client
- Use `drizzle` with the `neon-http` adapter specifically

```typescript
// src/db.ts
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
import { config } from "dotenv";

// Load environment variables
config({ path: ".env" });

if (!process.env.DATABASE_URL) {
  throw new Error("DATABASE_URL is not defined");
}

// Create Neon SQL client - specific to Neon
const sql = neon(process.env.DATABASE_URL);

// Create Drizzle instance with neon-http adapter
export const db = drizzle({ client: sql });
```

## Neon Database Considerations

### Default Settings

- Neon projects come with a ready-to-use database named `neondb`
- Default role is typically `neondb_owner`
- Connection strings include the correct endpoint based on your region

### Serverless Optimization

Neon is optimized for serverless environments:

- Use the HTTP-based `neon-http` adapter instead of node-postgres
- Take advantage of connection pooling for serverless functions
- Consider Neon's auto-scaling capabilities when designing schemas

## Schema Considerations for Neon

When defining schemas for Neon:

- Use Postgres-specific types from `drizzle-orm/pg-core`
- Leverage Postgres features that Neon supports:
  - JSON/JSONB columns
  - Full-text search
  - Arrays
  - Enum types

```typescript
// src/schema.ts
import {
  pgTable,
  serial,
  text,
  integer,
  timestamp,
  jsonb,
  pgEnum,
} from "drizzle-orm/pg-core";

// Example of Postgres-specific enum with Neon
export const userRoleEnum = pgEnum("user_role", ["admin", "user", "guest"]);

export const usersTable = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  role: userRoleEnum("role").default("user"),
  metadata: jsonb("metadata"), // Postgres JSONB supported by Neon
  // Other columns
});

// Export types
export type User = typeof usersTable.$inferSelect;
export type NewUser = typeof usersTable.$inferInsert;
```

## Drizzle Config for Neon

Neon-specific configuration in `drizzle.config.ts`:

```typescript
// drizzle.config.ts
import { config } from "dotenv";
import { defineConfig } from "drizzle-kit";

config({ path: ".env" });

export default defineConfig({
  schema: "./src/schema.ts",
  out: "./migrations",
  dialect: "postgresql", // Neon uses Postgres dialect
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
  // Optional: Neon project specific tables to include/exclude
  // includeTables: ['users', 'posts'],
  // excludeTables: ['_migrations'],
});
```

## Neon-Specific Query Optimizations

### Efficient Queries for Serverless

Optimize for Neon's serverless environment:

- Keep connections short-lived
- Use prepared statements for repeated queries
- Batch operations when possible

```typescript
// Example of optimized query for Neon
import { db } from "../db";
import { sql } from "drizzle-orm";
import { usersTable } from "../schema";

export async function batchInsertUsers(users: NewUser[]) {
  // More efficient than multiple individual inserts on Neon
  return db.insert(usersTable).values(users).returning();
}

// For complex queries, use prepared statements
export const getUsersByRolePrepared = db
  .select()
  .from(usersTable)
  .where(sql`${usersTable.role} = $1`)
  .prepare("get_users_by_role");

// Usage: getUsersByRolePrepared.execute(['admin'])
```

### Transaction Handling with Neon

Neon supports transactions through Drizzle:

```typescript
import { db } from "../db";
import { usersTable, postsTable } from "../schema";

export async function createUserWithPosts(user: NewUser, posts: NewPost[]) {
  return await db.transaction(async (tx) => {
    const [newUser] = await tx.insert(usersTable).values(user).returning();

    if (posts.length > 0) {
      await tx.insert(postsTable).values(
        posts.map((post) => ({
          ...post,
          userId: newUser.id,
        })),
      );
    }

    return newUser;
  });
}
```

## Working with Neon Branches

Neon supports database branching for development and testing:

```typescript
// Using different Neon branches with environment variables
import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";

// For multi-branch setup
const getBranchUrl = () => {
  const env = process.env.NODE_ENV;
  if (env === "development") {
    return process.env.DEV_DATABASE_URL;
  } else if (env === "test") {
    return process.env.TEST_DATABASE_URL;
  }
  return process.env.DATABASE_URL;
};

const sql = neon(getBranchUrl()!);
export const db = drizzle({ client: sql });
```

## Neon-Specific Error Handling

Handle Neon-specific connection issues:

```typescript
import { db } from "../db";
import { usersTable } from "../schema";

export async function safeNeonOperation<T>(
  operation: () => Promise<T>,
): Promise<T> {
  try {
    return await operation();
  } catch (error: any) {
    // Handle Neon-specific error codes
    if (error.message?.includes("connection pool timeout")) {
      console.error("Neon connection pool timeout");
      // Handle appropriately
    }

    // Re-throw for other handling
    throw error;
  }
}

// Usage
export async function getUserSafely(id: number) {
  return safeNeonOperation(() =>
    db.select().from(usersTable).where(eq(usersTable.id, id)),
  );
}
```

## Best Practices for Neon with Drizzle

1. **Connection Management**
   - Keep connection times short for serverless functions
   - Use connection pooling for high traffic applications

2. **Neon Features**
   - Utilize Neon branching for development and testing
   - Consider Neon's auto-scaling for database design

3. **Query Optimization**
   - Batch operations when possible
   - Use prepared statements for repeated queries
   - Optimize complex joins to minimize data transfer

4. **Schema Design**
   - Leverage Postgres-specific features supported by Neon
   - Use appropriate indexes for your query patterns
   - Consider Neon's performance characteristics for large tables

# Neon Auth guidelines

## Overview

This document provides comprehensive guidelines for implementing authentication in your application using both Stack Auth (frontend authentication system) and Neon Auth (database integration for user data). These systems work together to provide a complete authentication solution:

- **Stack Auth**: Handles user interface components, authentication flows, and client/server interactions
- **Neon Auth**: Manages how user data is stored and accessed in your database

## Stack Auth Setup Guidelines

### Initial Setup

- Run the installation wizard with:  
  `npx @stackframe/init-stack@latest`
- Update your API keys in your `.env.local` file:
  - `NEXT_PUBLIC_STACK_PROJECT_ID`
  - `NEXT_PUBLIC_STACK_PUBLISHABLE_CLIENT_KEY`
  - `STACK_SECRET_SERVER_KEY`
- Key files created/updated include:
  - `app/handler/[...stack]/page.tsx` (default auth pages)
  - `app/layout.tsx` (wrapped with StackProvider and StackTheme)
  - `app/loading.tsx` (provides a Suspense fallback)
  - `stack.ts` (initializes your Stack server app)

### UI Components

- Use pre-built components from `@stackframe/stack` like `<UserButton />`, `<SignIn />`, and `<SignUp />` to quickly set up auth UI.
- You can also compose smaller pieces like `<OAuthButtonGroup />`, `<MagicLinkSignIn />`, and `<CredentialSignIn />` for custom flows.
- Example:

  ```tsx
  import { SignIn } from "@stackframe/stack";
  export default function Page() {
    return <SignIn />;
  }
  ```

### User Management

- In Client Components, use the `useUser()` hook to retrieve the current user (it returns `null` when not signed in).
- Update user details using `user.update({...})` and sign out via `user.signOut()`.
- For pages that require a user, call `useUser({ or: "redirect" })` so unauthorized visitors are automatically redirected.

### Client Component Integration

- Client Components rely on hooks like `useUser()` and `useStackApp()`.
- Example:

  ```tsx
  "use client";
  import { useUser } from "@stackframe/stack";
  export function MyComponent() {
    const user = useUser();
    return <div>{user ? `Hello, ${user.displayName}` : "Not logged in"}</div>;
  }
  ```

### Server Component Integration

- For Server Components, use `stackServerApp.getUser()` from your `stack.ts` file.
- Example:

  ```tsx
  import { stackServerApp } from "@/stack";
  export default async function ServerComponent() {
    const user = await stackServerApp.getUser();
    return <div>{user ? `Hello, ${user.displayName}` : "Not logged in"}</div>;
  }
  ```

### Page Protection

- Protect pages by:
  - Using `useUser({ or: "redirect" })` in Client Components.
  - Using `await stackServerApp.getUser({ or: "redirect" })` in Server Components.
  - Implementing middleware that checks for a user and redirects to `/handler/sign-in` if not found.
- Example middleware:

  ```tsx
  export async function middleware(request: NextRequest) {
    const user = await stackServerApp.getUser();
    if (!user) {
      return NextResponse.redirect(new URL("/handler/sign-in", request.url));
    }
    return NextResponse.next();
  }
  export const config = { matcher: "/protected/:path*" };
  ```

## Neon Auth Database Integration

### Database Schema

Neon Auth creates and manages a schema in your database that stores user information:

- **Schema Name**: `neon_auth`
- **Primary Table**: `users_sync`
- **Table Structure**:
  - `raw_json` (JSONB, NOT NULL): Complete user data in JSON format
  - `id` (TEXT, NOT NULL, PRIMARY KEY): Unique user identifier
  - `name` (TEXT, NULLABLE): User's display name
  - `email` (TEXT, NULLABLE): User's email address
  - `created_at` (TIMESTAMP WITH TIME ZONE, NULLABLE): When the user was created
  - `deleted_at` (TIMESTAMP WITH TIME ZONE, NULLABLE): When the user was deleted (if applicable)
- **Indexes**:
  - `users_sync_deleted_at_idx` on `deleted_at`: For quickly identifying deleted users

### Schema Creation SQL

```sql
-- Create schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS neon_auth;
-- Create the users_sync table
CREATE TABLE neon_auth.users_sync (
    raw_json JSONB NOT NULL,
    id TEXT NOT NULL,
    name TEXT,
    email TEXT,
    created_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE,
    PRIMARY KEY (id)
);
-- Create index on deleted_at
CREATE INDEX users_sync_deleted_at_idx ON neon_auth.users_sync (deleted_at);
```

### Database Usage

#### Querying Users

To fetch active users from Neon Auth:

```sql
SELECT * FROM neon_auth.users_sync WHERE deleted_at IS NULL;
```

#### Relating User Data with Application Tables

To join user data with your application tables:

```sql
SELECT
  t.*,
  u.id AS user_id,
  u.name AS user_name,
  u.email AS user_email
FROM
  public.todos t
LEFT JOIN
  neon_auth.users_sync u ON t.owner = u.id
WHERE
  u.deleted_at IS NULL
ORDER BY
  t.id;
```

## Stack Auth SDK Reference

The Stack Auth SDK provides several types and methods:

```tsx
type StackClientApp = {
  new(options): StackClientApp;
  getUser([options]): Promise<User>;
  useUser([options]): User;
  getProject(): Promise<Project>;
  useProject(): Project;
  signInWithOAuth(provider): void;
  signInWithCredential([options]): Promise<...>;
  signUpWithCredential([options]): Promise<...>;
  sendForgotPasswordEmail(email): Promise<...>;
  sendMagicLinkEmail(email): Promise<...>;
};
type StackServerApp =
  & StackClientApp
  & {
    new(options): StackServerApp;
    getUser([id][, options]): Promise<ServerUser | null>;
    useUser([id][, options]): ServerUser;
    listUsers([options]): Promise<ServerUser[]>;
    useUsers([options]): ServerUser[];
    createUser([options]): Promise<ServerUser>;
    getTeam(id): Promise<ServerTeam | null>;
    useTeam(id): ServerTeam;
    listTeams(): Promise<ServerTeam[]>;
    useTeams(): ServerTeam[];
    createTeam([options]): Promise<ServerTeam>;
  }
type CurrentUser = {
  id: string;
  displayName: string | null;
  primaryEmail: string | null;
  primaryEmailVerified: boolean;
  profileImageUrl: string | null;
  signedUpAt: Date;
  hasPassword: boolean;
  clientMetadata: Json;
  clientReadOnlyMetadata: Json;
  selectedTeam: Team | null;
  update(data): Promise<void>;
  updatePassword(data): Promise<void>;
  getAuthHeaders(): Promise<Record<string, string>>;
  getAuthJson(): Promise<{ accessToken: string | null }>;
  signOut([options]): Promise<void>;
  delete(): Promise<void>;
  getTeam(id): Promise<Team | null>;
  useTeam(id): Team | null;
  listTeams(): Promise<Team[]>;
  useTeams(): Team[];
  setSelectedTeam(team): Promise<void>;
  createTeam(data): Promise<Team>;
  leaveTeam(team): Promise<void>;
  getTeamProfile(team): Promise<EditableTeamMemberProfile>;
  useTeamProfile(team): EditableTeamMemberProfile;
  hasPermission(scope, permissionId): Promise<boolean>;
  getPermission(scope, permissionId[, options]): Promise<TeamPermission | null>;
  usePermission(scope, permissionId[, options]): TeamPermission | null;
  listPermissions(scope[, options]): Promise<TeamPermission[]>;
  usePermissions(scope[, options]): TeamPermission[];
  listContactChannels(): Promise<ContactChannel[]>;
  useContactChannels(): ContactChannel[];
};
```

## Best Practices for Integration

### Stack Auth Best Practices

- Use the appropriate methods based on component type:
  - Use hook-based methods (`useXyz`) in Client Components
  - Use promise-based methods (`getXyz`) in Server Components
- Always protect sensitive routes using the provided mechanisms
- Use pre-built UI components whenever possible to ensure proper auth flow handling

### Neon Auth Best Practices

- Always use `LEFT JOIN` when relating with `neon_auth.users_sync`
  - Ensures queries work even if user records are missing
- Always filter out users with `deleted_at IS NOT NULL`
  - Prevents deleted user accounts from appearing in queries
- Never create Foreign Key constraints pointing to `neon_auth.users_sync`
  - User management happens externally and could break referential integrity
- Never insert users directly into the `neon_auth.users_sync` table
  - User creation and management must happen through the Stack Auth system

## Integration Flow

1. User authentication happens via Stack Auth UI components
2. User data is automatically synced to the `neon_auth.users_sync` table
3. Your application code accesses user information either through:
   - Stack Auth hooks/methods (in React components)
   - SQL queries to the `neon_auth.users_sync` table (for data operations)

## Example: Custom Profile Page with Database Integration

### Frontend Component

```tsx
"use client";
import { useUser, useStackApp, UserButton } from "@stackframe/stack";
export default function ProfilePage() {
  const user = useUser({ or: "redirect" });
  const app = useStackApp();
  return (
    <div>
      <UserButton />
      <h1>Welcome, {user.displayName || "User"}</h1>
      <p>Email: {user.primaryEmail}</p>
      <button onClick={() => user.signOut()}>Sign Out</button>
    </div>
  );
}
```

### Database Query for User's Content

```sql
-- Get all todos for the currently logged in user
SELECT
  t.*
FROM
  public.todos t
LEFT JOIN
  neon_auth.users_sync u ON t.owner = u.id
WHERE
  u.id = $current_user_id
  AND u.deleted_at IS NULL
ORDER BY
  t.created_at DESC;
```