​
Overview

InsForge uses a powerful database stack that automatically generates REST APIs from your PostgreSQL schema, eliminating the need to write backend CRUD code.

​
Technology Stack

​
Core Components

ComponentTechnologyVersionPurpose
DatabasePostgreSQL15.13ACID-compliant relational database
REST APIPostgREST12.2.12Auto-generates RESTful APIs from public schema only
Query EnginePostgREST DSL-Powerful filtering with operators and functions
SecurityRow Level Security-Fine-grained access control at the row level
RolesPostgreSQL Roles-anon (read-only), authenticated (CRUD), project_admin (full)
SDK@insforge/sdkLatestType-safe JavaScript/TypeScript client

​
How It Works

​
1. Schema Definition

When you create a table through the InsForge API or migrations:
  • Table structure is stored in PostgreSQL
  • PostgREST discovers the schema via database introspection
  • REST endpoints are instantly available

​
2. API Generation

PostgREST automatically creates endpoints:
  • 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

HTTP requests are converted to optimized SQL: 
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

​
PostgREST Features

Instant APIs

Every table gets full CRUD endpoints automatically without writing any backend code

Advanced Filtering

Complex queries with operators like eq, gt, like, in, is, or

Relationship Embedding

Join related tables in single requests using foreign key relationships

Bulk Operations

Insert, update, or delete multiple records in a single atomic transaction

Computed Fields

Expose database functions and views as API endpoints

Real-time Updates

NOTIFY/LISTEN for schema changes without restart

​
Database Structure

InsForge uses two PostgreSQL databases:

​
Main Database (insforge)

All application and system tables reside in the public schema:
Table TypePurposeExamples
User TablesCreated by developersAny table you create
System TablesInternal InsForge tablesPrefixed with _

​
Analytics Database (_insforge)

Separate database for analytics with _analytics schema for Logflare integration.

​
System Tables (in public schema)

TablePurpose
_accountsCore user authentication records
usersUser profile data (references _accounts)
_oauth_connectionsOAuth provider connections
_storage_bucketsStorage bucket configuration
_storageFile metadata and references
_ai_configsAI model configurations per project
_ai_usageAI token usage tracking
_configSystem configuration key-value store
_metadataApplication metadata
_mcp_usageMCP tool usage tracking
_edge_functionsEdge function definitions
logsActivity and audit logs

​
Query Syntax

PostgREST provides a powerful query syntax that maps to SQL:

​
Operators

OperatorSQL EquivalentExample
eq=?id=eq.123
neq!=?status=neq.deleted
gt>?age=gt.18
gte>=?price=gte.100
lt<?created=lt.2024-01-01
lte<=?quantity=lte.10
likeLIKE?name=like.*john*
ilikeILIKE?email=ilike.*gmail*
inIN?status=in.(active,pending)
isIS?deleted_at=is.null

​
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))

​
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

​
Data Types

PostgreSQL types are automatically mapped to JSON:
InsForge TypePostgreSQL TypeJSON TypeNotes
stringTEXTstringText of any length
integerINTEGERnumber32-bit integers
floatDOUBLE PRECISIONnumberDecimal numbers
booleanBOOLEANbooleantrue/false
dateDATEstringISO 8601 date format
datetimeTIMESTAMPTZstringISO 8601 with timezone
uuidUUIDstringAuto-generated unique identifier
jsonJSONBobject/arrayStructured JSON data with indexing

​
Best Practices

Use Indexes

Create indexes on frequently queried columns

Leverage RLS

Implement Row Level Security for data isolation

Batch Operations

Use bulk inserts/updates for better performance

Select Columns

Query only needed columns to reduce payload

Use Views

Create views for complex queries

Monitor Performance

Use EXPLAIN ANALYZE for slow queries

​
Limitations

  • No Custom Business Logic: PostgREST handles CRUD only
  • SQL Knowledge Helpful: Complex queries benefit from SQL understanding
  • Schema Changes: Require PostgREST reload (automatic via NOTIFY)
  • File Handling: Use storage API for files, not database

​
Comparison with Traditional APIs

AspectTraditional REST APIInsForge + PostgREST
Development TimeWrite CRUD for each tableInstant APIs from schema
MaintenanceUpdate code for schema changesAutomatic updates
PerformanceDepends on implementationOptimized SQL generation
ConsistencyVaries by developerUniform API patterns
DocumentationManual updates neededAuto-generated OpenAPI
SecurityCustom implementationBuilt-in RLS + JWT