Database Architecture

​

Overview

​

Technology Stack

​

Core Components

• PostgreSQL — The primary data store with full SQL support
• PostgREST — Automatically generates REST APIs from your schema
• PgBouncer — Connection pooler for efficient concurrent access
• JWT Auth — Token-based authentication integrated into queries

​

How It Works

​

1. Schema Definition

• Table structure is stored in PostgreSQL
• PostgREST discovers the schema via database introspection
• REST endpoints are instantly available

​

2. API Generation

GET    /api/database/records/{table}  — Query records
POST   /api/database/records/{table}  — Insert records
PATCH  /api/database/records/{table}  — Update records
DELETE /api/database/records/{table}  — Delete records

​

3. Query Translation

GET /api/database/records/products?price=gte.100&category=eq.electronics
↓
SELECT * FROM products WHERE price >= 100 AND category = 'electronics'

​

4. Security Layer

• JWT tokens are validated on each request
• User ID extracted from token
• RLS policies applied based on user context
• Only authorized rows are returned

​

5. Response Format

• Results returned as JSON arrays
• HTTP status codes indicate success/failure
• Headers include pagination info

​

Query Syntax

​

Operators

​

Complex Queries

// Combine conditions with AND
GET /api/database/records/products?price=gte.100&category=eq.electronics

// OR conditions
GET /api/database/records/products?or=(price.lt.50,on_sale.is.true)

// Nested conditions
GET /api/database/records/orders?and=(status.eq.pending,or=(priority.eq.high,created_at.lt.2024-01-01))

​

Database Structure

​

Schema Layout

​

Data Types

​

Performance Optimizations

​

Connection Pooling

• PgBouncer manages database connections
• Reduces connection overhead
• Handles concurrent requests efficiently

​

Query Optimization

• PostgREST generates optimized SQL
• Uses prepared statements
• Leverages PostgreSQL query planner

​

Indexing Strategy

• Automatic indexes on primary keys
• Foreign key indexes for joins
• Custom indexes via migrations

​

Caching

• ETags for conditional requests
• Client-side caching support
• Response compression

​

Best Practices

1. Use RLS policies for data security instead of application-level checks
2. Add indexes on frequently queried columns
3. Use select parameter to limit returned fields for better performance
4. Prefer JSONB over JSON for structured data
5. Use transactions via RPC functions for complex operations

​

Limitations

• No Custom Business Logic — PostgREST handles CRUD only; use Functions for custom logic
• SQL Knowledge Helpful — Complex queries benefit from SQL understanding
• Schema Changes — Require PostgREST reload (automatic via NOTIFY)
• File Handling — Use Storage for files, not the database