Database

PostgreSQL database with instant REST APIs and pgvector support

Overview

InsForge provides a PostgreSQL database with automatic REST API generation using PostgREST-style endpoints. Every table you create instantly gets CRUD endpoints with filtering, sorting, and pagination.

Key Features

• Instant REST APIs - Auto-generated endpoints for all tables
• PostgREST-style Queries - Powerful filtering with eq, gt, lt, like, etc.
• Row Level Security - Fine-grained access control at the database level
• pgvector Support - Built-in vector embeddings for AI applications
• Relationships - Foreign keys and automatic joins
• Real-time Subscriptions - Listen to database changes via WebSockets

Table Management

Creating Tables

Create tables via the API with automatic id, createdAt, and updatedAt fields:

cURL

curl -X POST https://your-app.region.insforge.app/api/database/tables \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "tableName": "posts",
    "columns": [
      {
        "name": "title",
        "type": "string",
        "nullable": false
      },
      {
        "name": "content",
        "type": "string",
        "nullable": true
      },
      {
        "name": "published",
        "type": "boolean",
        "nullable": false,
        "defaultValue": "false"
      },
      {
        "name": "userId",
        "type": "uuid",
        "nullable": false,
        "foreignKey": {
          "table": "auth.users",
          "column": "id",
          "onDelete": "CASCADE"
        }
      }
    ],
    "rlsEnabled": true
  }'

Supported Column Types

Type	PostgreSQL	Description
string	TEXT	Variable-length text
integer	INTEGER	Whole numbers
float	DOUBLE PRECISION	Decimal numbers
boolean	BOOLEAN	True/false values
datetime	TIMESTAMP WITH TIME ZONE	Date and time
uuid	UUID	Universally unique identifier
json	JSONB	JSON data
file	TEXT	File URLs from storage

Getting Table Schema

curl https://your-app.region.insforge.app/api/database/tables/posts/schema \
  -H "Authorization: Bearer YOUR_ACCESS_TOKEN"

Response:

{
  "tableName": "posts",
  "columns": [
    {
      "name": "id",
      "type": "uuid",
      "nullable": false,
      "unique": true,
      "default": "gen_random_uuid()",
      "isPrimaryKey": true,
      "foreignKey": null
    },
    {
      "name": "title",
      "type": "string",
      "nullable": false,
      "unique": false,
      "default": null,
      "isPrimaryKey": false,
      "foreignKey": null
    },
    {
      "name": "createdAt",
      "type": "datetime",
      "nullable": false,
      "unique": false,
      "default": "now()",
      "isPrimaryKey": false,
      "foreignKey": null
    }
  ]
}

CRUD Operations

All CRUD operations use the TypeScript SDK with the {data, error} response structure.

Query Records (SELECT)

Basic Query

const { data, error } = await client.database
  .from('posts')
  .select();

if (error) {
  console.error('Error:', error);
} else {
  console.log('Posts:', data);
}

Filter Operators

Operator	Method	Example
Equal	.eq('field', value)	status=eq.active
Not equal	.neq('field', value)	status=neq.draft
Greater than	.gt('field', value)	age=gt.18
Greater or equal	.gte('field', value)	score=gte.50
Less than	.lt('field', value)	price=lt.100
Less or equal	.lte('field', value)	quantity=lte.10
Pattern match	.like('field', pattern)	name=like.*John*
In array	.in('field', [values])	status=in.(active,pending)
Is null	.is('field', null)	deletedAt=is.null

Create Records (INSERT)

Insert requests must be an array, even for single records.

Single Record

const { data, error } = await client.database
  .from('posts')
  .insert([
    {
      title: 'My First Post',
      content: 'Hello world!',
      published: true
    }
  ]);

Update Records (UPDATE)

Update by ID

const { data, error } = await client.database
  .from('posts')
  .update({ published: true })
  .eq('id', '123e4567-e89b-12d3-a456-426614174000')
  .select();

Delete Records (DELETE)

Delete by ID

const { data, error } = await client.database
  .from('posts')
  .delete()
  .eq('id', '123e4567-e89b-12d3-a456-426614174000');

Response Headers

Query endpoints return useful headers:

• X-Total-Count - Total number of matching records
• Content-Range - Range of records returned (e.g., “0-99/1234”)

const response = await fetch('/api/database/records/posts?limit=10', {
  headers: { 'Authorization': `Bearer ${token}` }
});

const totalCount = response.headers.get('X-Total-Count');
const contentRange = response.headers.get('Content-Range');

pgvector for Embeddings

InsForge includes pgvector for storing and querying vector embeddings.

Creating a Table with Embeddings

CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content TEXT NOT NULL,
  embedding vector(1536),  -- OpenAI ada-002 dimension
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now()
);

CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops);

Storing Embeddings

1. Generate Embedding

   Use the AI API to generate embeddings:

   const { data: embedding } = await client.ai.embeddings.create({
     model: 'openai/text-embedding-3-small',
     input: 'Your text here'
   });
   
2. Store in Database

   Insert the embedding vector:

   const { data, error } = await client.database
     .from('documents')
     .insert([{
       content: 'Your text here',
       embedding: embedding.data[0].embedding
     }]);
   

Similarity Search

Query similar vectors using SQL functions:

SELECT id, content, 
  1 - (embedding <=> query_embedding) as similarity
FROM documents
ORDER BY embedding <=> query_embedding
LIMIT 10;

Vector operators:

• <=> - Cosine distance
• <-> - Euclidean distance
• <#> - Inner product

Row Level Security (RLS)

RLS provides database-level access control per row.

Architecture

Example: User-owned Posts

1. Enable RLS

   Enable RLS when creating the table:

   {
     "tableName": "posts",
     "rlsEnabled": true,
     "columns": [...]
   }
   
2. Create Policy

   Users can only see their own posts:

   CREATE POLICY "Users can view own posts"
   ON posts FOR SELECT
   USING (auth.user_id() = user_id);
   
3. Test Access

   Queries automatically filter by user:

   // Authenticated as user A
   const { data } = await client.database
     .from('posts')
     .select();
   // Only returns posts where userId = A
   

API Reference

Base URL

GET    /api/database/records/{tableName}
POST   /api/database/records/{tableName}
PATCH  /api/database/records/{tableName}
DELETE /api/database/records/{tableName}

Query Parameters

Parameter	Type	Description
limit	integer	Max records (1-1000, default 100)
offset	integer	Skip records (pagination)
order	string	Sort order (e.g., “createdAt.desc”)
select	string	Columns to return (comma-separated)
{field}	string	Filter by field (e.g., “status=eq.active”)

Error Responses

interface ErrorResponse {
  error: string;       // Error code
  message: string;     // Human-readable message
  statusCode: number;  // HTTP status
  nextActions?: string; // Suggested fix
}

Common errors:

• 400 - Invalid query parameters
• 404 - Table not found
• 422 - Validation error (invalid field type)

Best Practices

Always Use Arrays for Insert

Even for single records, wrap in array: insert([{...}])

Use .select() to Get Created Data

Chain .select() after insert/update to return the modified records

Enable RLS for User Data

Protect sensitive data with Row Level Security policies

Index Frequently Queried Columns

Add indexes for columns used in WHERE clauses and joins

Use Foreign Keys

Maintain data integrity with foreign key constraints

Next Steps

Authentication

Secure your data with user authentication

Real-time

Subscribe to database changes

AI Integration

Generate embeddings for similarity search

Functions

Add custom business logic