Database Architecture

Overview

The Refresh App Web uses Neon Postgres, a serverless Postgres database with multi-region support, integrated with Drizzle ORM for type-safe queries and Row-Level Security (RLS) for tenant isolation.

Database Platform

Neon Postgres

Key Features:

• Serverless: Automatic scaling and connection pooling

• Multi-Region: Primary write region with read replicas

• Branching: Database branches for development

• Point-in-Time Recovery: 30-day backup retention

• Logical Replication: From staging to development

Architecture:

• Primary Region: North America (us-east-1)

• Read Replicas: Multiple regions for low-latency reads

• Compute: Auto-scaling based on load

• Storage: Separated from compute for efficient scaling

ORM & Query Builder

Drizzle ORM

Why Drizzle?

• Type-safe SQL queries

• Lightweight and performant

• Excellent TypeScript integration

• Edge-compatible (works in Cloudflare Workers)

• Schema migrations

Key Features:

// Type-safe queries
const user = await db.query.users.findFirst({
	where: eq(users.id, userId),
	with: {
		tenants: true,
		groups: true
	}
});

// Automatic type inference
type User = typeof users.$inferSelect;
type NewUser = typeof users.$inferInsert;

Database Schema

Core Tables

Authentication Tables:

• users - User accounts

• accounts - OAuth provider links

• sessions - Active sessions

• verification_tokens - Email verification

Tenant & Group Tables:

• tenants - Multi-tenant organizations

• groups - Sub-groups within tenants

• tenant_users - User-tenant memberships

• group_users - User-group memberships

Application Tables:

• employee_data - Employee records

• calendar_events - Calendar data from integrations

• productivity_activities - Productivity tracking

• wellness_activities - Wellness tracking

• crm_activities - CRM integration data

• analysis_reports - AI-generated reports

System Tables:

• cache - Application-level cache

• processing_jobs - Background job tracking

• subscriptions - Billing and subscription data

• privacy_policies - Policy versions and acceptance

Schema Organization

Schemas organized by domain in src/lib/server/db/schema/. See Developer Database Guide.

Row-Level Security (RLS)

RLS Overview

What is RLS? Row-Level Security is a Postgres feature that automatically filters rows based on the current user's context. In this application, RLS uses JWT claims to enforce tenant isolation.

JWT Integration

RLS policies use JWT claims (tenant_id) to filter rows. See Authentication Flow and Multi-Tenancy.

Multi-Region Architecture

Region Configuration

Primary (Write) Region:

• Location: North America (us-east-1)

• Purpose: All write operations

• Latency: Optimized for North American users

Read Replicas:

• Regions: Multiple (e.g., Europe, Asia, Australia)

• Purpose: Low-latency reads

• Sync: Near real-time replication from primary

Connection Routing

Environment Variables:

WRITE_DATABASE_URL=<primary-region-url>
READ_DATABASE_URLS={
  "NA": "<north-america-read-url>",
  "EU": "<europe-read-url>",
  "ASIA": "<asia-read-url>"
}

Routing Logic:

// Write operations always go to primary
const writeDb = createWriteDbClient(platform.env.WRITE_DATABASE_URL);

// Read operations routed to nearest replica
const readDb = createReadDbClient(event, neonJwt);

Database Migrations

Migration Strategy

Tool: Drizzle Kit

Workflow:

1. Update schema files in src/lib/server/db/schema/

2. Generate migration: pnpm db:generate

3. Review migration SQL in drizzle/ directory

4. Apply migration: pnpm db:migrate

Migration Safety

Best Practices:

• Always review generated SQL

• Test in development first

• Use transactions when possible

• Avoid breaking schema changes

• Keep migrations small and focused

Connection Management

Connection Pooling

Neon's Approach:

• Serverless driver with built-in pooling

• No persistent connections required

• Edge-optimized for Cloudflare Workers

Configuration:

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

const sql = neon(process.env.DATABASE_URL, {
	fetchOptions: {
		cache: 'no-store'
	}
});

Connection Limits

Neon Limits:

• Free Tier: 100 connections

• Paid Tier: 1000+ connections

• Auto-scaling: Automatically adjusts compute

Application Best Practices:

• Use connection pooling

• Close connections after use

• Avoid long-running transactions

• Use read replicas for reads

Logical Replication

Staging → Development Replication

Purpose:

• Keep development database in sync with staging

• Realistic testing data

• Schema changes flow from staging to dev

Configuration: Managed in Neon dashboard:

• Source: Staging database

• Target: Development database

• Replication: Logical replication (async)

Considerations:

• Only schema changes replicated

• Data may differ (dev can have test data)

• One-way replication (staging → dev)

Caching Strategy

Application-Level Cache

Cache Table:

CREATE TABLE cache (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL
);

Usage:

// Store in cache
await db.insert(cache).values({
	key: 'session:user:123',
	value: sessionData,
	expires_at: new Date(Date.now() + 3600000) // 1 hour
});

// Retrieve from cache
const cached = await db.query.cache.findFirst({
	where: and(eq(cache.key, cacheKey), gte(cache.expires_at, new Date()))
});

In-Memory Cache

Session Cache:

• LRU cache for user sessions

• TTL: 1 hour

• Reduces database queries

• Background refresh

Performance Optimization

Query Optimization

Best Practices:

1. Use indexes on frequently queried columns

2. Select only needed columns (avoid SELECT *)

3. Use batch operations for bulk inserts

4. Leverage prepared statements

Example:

// Bad: Select all columns
const users = await db.select().from(usersTable);

// Good: Select specific columns
const users = await db
	.select({
		id: usersTable.id,
		email: usersTable.email
	})
	.from(usersTable);

Index Strategy

Key Indexes:

• Primary keys (automatic)

• Foreign keys for joins

• Tenant ID for RLS queries

• Timestamp fields for filtering

Example:

export const employeeData = pgTable(
	'employee_data',
	{
		id: text('id').primaryKey(),
		tenantId: text('tenant_id').notNull(), // Indexed for RLS
		userId: text('user_id').notNull(), // Indexed for joins
		createdAt: timestamp('created_at').defaultNow()
	},
	(table) => ({
		tenantIdIdx: index('employee_data_tenant_id_idx').on(table.tenantId),
		userIdIdx: index('employee_data_user_id_idx').on(table.userId)
	})
);

Backup & Recovery

Neon Backups

Automatic Backups:

• Continuous backup (WAL archiving)

• 30-day retention

• Point-in-time recovery (PITR)

Recovery Options:

1. Restore to Timestamp: Restore database to specific point in time

2. Clone Branch: Create new branch from backup

3. Logical Replication: Replicate to new database

Disaster Recovery Plan

RTO (Recovery Time Objective): < 1 hour RPO (Recovery Point Objective): < 5 minutes

Steps:

1. Identify issue and timestamp

2. Create new Neon branch from backup

3. Test restored database

4. Update connection strings

5. Deploy updated configuration

6. Verify functionality

Security Considerations

Connection Security

TLS Encryption:

• All connections encrypted in transit

• TLS 1.2+ required

• Certificate validation enforced

Credential Management:

• Database URLs stored in AWS SSM

• Never committed to git

• Rotated periodically

• Separate credentials per environment

Access Control

User Roles:

• authenticated: Standard application user (via RLS)

• admin: Database administrator (manual operations)

RLS Enforcement:

• All application queries filtered by tenant

• No cross-tenant data access

• Enforced at database level (not just application)

Audit Logging

Future Enhancement:

• Track all data modifications

• Log user actions with tenant context

• Compliance and security auditing

Monitoring & Alerts

Metrics

Neon Dashboard:

• Connection count

• Query performance

• CPU usage

• Storage usage

• Replication lag

Alert Triggers:

• High connection count (> 80%)

• Slow queries (> 1s)

• High CPU usage (> 80%)

• Replication lag (> 5 minutes)

Query Analysis

Slow Query Log:

• Enabled in Neon dashboard

• Identifies performance bottlenecks

• Optimization opportunities

Related Documentation

• Authentication Flow - JWT generation for RLS

• Multi-Tenancy - Tenant isolation strategy

• Security - Security model

• Developer Database Guide - Development workflow

---

Last updated: October 2025