# Neon PostgreSQL Database Standards

> **Scope:** nextjs-neon
> **Layer:** 2 (on keyword)
> **Keywords:** postgresql, postgres, sql, neon, database schema
> **Load When:** neon or postgresql keywords detected

**Verified against:** PostgreSQL 16/17 + EF Core 10 + @neondatabase/serverless. Last-verified: 2026-05-20.

---

Database schema design, migrations, and Row Level Security (RLS) patterns for Neon PostgreSQL.

---

## Schema Design

### Naming Conventions

| Element | Convention | Example |
|---------|-----------|---------|
| **Tables** | snake_case, plural | `users`, `user_profiles`, `order_items` |
| **Columns** | snake_case | `created_at`, `user_id`, `is_active` |
| **Primary Keys** | `id` | `id uuid primary key default gen_random_uuid()` |
| **Foreign Keys** | `{table}_id` | `user_id`, `order_id` |
| **Timestamps** | `created_at`, `updated_at` | Always include |
| **Boolean** | `is_` or `has_` prefix | `is_active`, `has_verified_email` |
| **Indexes** | `idx_{table}_{column}` | `idx_users_email` |

### Standard Table Template

```sql
create table public.users (
  id uuid primary key default gen_random_uuid(),
  email text unique not null,
  full_name text,
  avatar_url text,
  is_active boolean default true,
  created_at timestamptz default now() not null,
  updated_at timestamptz default now() not null
);

-- Updated timestamp trigger
create or replace function public.handle_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger users_updated_at
  before update on public.users
  for each row execute function public.handle_updated_at();
```

---

## Row Level Security (RLS)

### Enable RLS

```sql
-- Always enable RLS on user-facing tables
alter table public.profiles enable row level security;
```

### Common RLS Policies

#### User can read own profile

```sql
create policy "Users can view own profile"
  on public.profiles for select
  using (auth.user_id() = user_id);
```

#### User can update own profile

```sql
create policy "Users can update own profile"
  on public.profiles for update
  using (auth.user_id() = user_id)
  with check (auth.user_id() = user_id);
```

#### User can insert own profile

```sql
create policy "Users can insert own profile"
  on public.profiles for insert
  with check (auth.user_id() = user_id);
```

#### Admin can do anything

```sql
create policy "Admins have full access"
  on public.profiles for all
  using (
    exists (
      select 1 from public.user_roles
      where user_id = auth.user_id() and role = 'admin'
    )
  );
```

#### Public read access

```sql
create policy "Public profiles are viewable by everyone"
  on public.profiles for select
  using (is_public = true);
```

---

## Migrations

### Migration Management (EF Core)

```bash
# Create a new migration
dotnet ef migrations add CreateUsersTable

# Apply migrations to Neon database
dotnet ef database update

# Generate SQL script for review
dotnet ef migrations script --idempotent
```

### Migration Template (SQL for manual migrations)

```sql
-- Create table
create table if not exists public.example_table (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz default now() not null
);

-- Enable RLS
alter table public.example_table enable row level security;

-- Create policies
create policy "Example policy"
  on public.example_table for select
  using (true);

-- Create indexes
create index if not exists idx_example_table_name
  on public.example_table (name);

-- Rollback (always include)
-- drop table if exists public.example_table cascade;
```

---

## Relationships

### One-to-Many

```sql
create table public.posts (
  id uuid primary key default gen_random_uuid(),
  author_id uuid references public.users(id) on delete cascade,
  title text not null,
  content text,
  created_at timestamptz default now()
);

create index idx_posts_author_id on public.posts (author_id);
```

### Many-to-Many (Join Table)

```sql
create table public.user_teams (
  user_id uuid references public.users(id) on delete cascade,
  team_id uuid references public.teams(id) on delete cascade,
  role text not null default 'member',
  joined_at timestamptz default now(),
  primary key (user_id, team_id)
);
```

---

## Indexes

### When to Add Indexes

- Foreign keys (for joins)
- Columns used in WHERE clauses
- Columns used in ORDER BY
- Columns used in GROUP BY
- Text search columns (GIN index)

### Index Examples

```sql
-- B-tree (default, most common)
create index idx_users_email on public.users (email);

-- Partial index (for specific queries)
create index idx_users_active_email
  on public.users (email) where is_active = true;

-- Composite index (multiple columns)
create index idx_posts_author_created
  on public.posts (author_id, created_at desc);

-- GIN index (full-text search)
create index idx_posts_content_search
  on public.posts using gin(to_tsvector('english', content));
```

---

## Functions & Triggers

### Auto-update Timestamp

```sql
create or replace function public.handle_updated_at()
returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

-- Apply to any table
create trigger users_updated_at
  before update on public.users
  for each row execute function public.handle_updated_at();
```

### Soft Delete

```sql
create or replace function public.soft_delete()
returns trigger as $$
begin
  update public.users
  set deleted_at = now()
  where id = old.id;
  return null;
end;
$$ language plpgsql;

create trigger users_soft_delete
  instead of delete on public.users
  for each row execute function public.soft_delete();
```

---

## Database Queries (EF Core / SQL)

### Select (EF Core)

```csharp
var users = await db.Users
    .Where(u => u.IsActive)
    .OrderByDescending(u => u.CreatedAt)
    .Take(10)
    .Select(u => new { u.Id, u.Name, u.Email })
    .ToListAsync(ct);
```

### Insert (EF Core)

```csharp
var user = new User { Name = "John", Email = "john@example.com" };
db.Users.Add(user);
await db.SaveChangesAsync(ct);
```

### Update (EF Core)

```csharp
var user = await db.Users.FindAsync([userId], ct);
if (user is not null)
{
    user.Name = "John Doe";
    await db.SaveChangesAsync(ct);
}
```

### Delete (EF Core)

```csharp
await db.Users.Where(u => u.Id == userId).ExecuteDeleteAsync(ct);
```

### Joins (Navigation Properties)

```csharp
var posts = await db.Posts
    .Include(p => p.Author)
    .Select(p => new
    {
        p.Id,
        p.Title,
        Author = new { p.Author.Id, p.Author.Name, p.Author.AvatarUrl }
    })
    .ToListAsync(ct);
```

### Direct SQL via Npgsql (Next.js / serverless)

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

const sql = neon(process.env.DATABASE_URL!);
const users = await sql`SELECT id, name, email FROM users WHERE is_active = true ORDER BY created_at DESC LIMIT 10`;
```

---

## Performance Best Practices

1. **Use select() specific columns** - Don't select `*` unnecessarily
2. **Add indexes** - For foreign keys and WHERE/ORDER BY columns
3. **Limit results** - Use `.limit()` for pagination
4. **Use RLS efficiently** - Simple policies are faster
5. **Batch operations** - Use array inserts instead of loops
6. **Use materialized views** - For complex aggregations
7. **Enable statement timeout** - Prevent long-running queries

---

*Neon PostgreSQL Database Standards - MORPH-SPEC by Polymorphism Tech*