insforge/database/sdk-integration.md

Database SDK Integration

Use InsForge SDK to perform CRUD operations in your frontend application.

Setup

First, ensure your .env file is configured with your InsForge URL and anon key. Get the anon key with npx -y @insforge/cli secrets get ANON_KEY. See the main SKILL.md for framework-specific variable names and full setup steps.

import { createClient } from '@insforge/sdk'

const insforge = createClient({
  baseUrl: process.env.NEXT_PUBLIC_INSFORGE_URL,       // adjust prefix for your framework
  anonKey: process.env.NEXT_PUBLIC_INSFORGE_ANON_KEY   // adjust prefix for your framework
})

For trusted server-only database work that needs project-admin access:

import { createAdminClient } from '@insforge/sdk'

const admin = createAdminClient({
  baseUrl: process.env.INSFORGE_URL,
  apiKey: process.env.INSFORGE_API_KEY
})

CRUD Operations

Select

// Specific columns (default choice — smaller responses, less bandwidth)
const { data, error } = await insforge.database.from('posts').select('id, title')

// All columns — reserve for single-record detail views; bound list reads with .limit()
const { data } = await insforge.database
  .from('posts')
  .select()
  .order('created_at', { ascending: false })
  .order('id', { ascending: false })   // tie-breaker keeps page membership stable
  .limit(50)

// With relationships
const { data } = await insforge.database.from('posts').select('*, comments(id, content)')

Default to naming columns and bounding list reads. An unbounded select() re-downloads the whole table on every call — see Bandwidth-efficient reads.

Insert

// Single record (MUST use array format)
const { data, error } = await insforge.database
  .from('posts')
  .insert([{ title: 'Hello', content: 'World' }])
  .select()

// Bulk insert
const { data } = await insforge.database
  .from('posts')
  .insert([{ title: 'A' }, { title: 'B' }])
  .select()

When a table has RLS enabled, .insert(...).select() behaves like INSERT ... RETURNING: the new row must pass both the INSERT policy and the SELECT policy. If WITH CHECK looks correct but the call still fails with new row violates row-level security policy, make sure the returned row is also visible to the caller.

Update

const { data, error } = await insforge.database
  .from('posts')
  .update({ title: 'Updated' })
  .eq('id', postId)
  .select()

Delete

const { error } = await insforge.database
  .from('posts')
  .delete()
  .eq('id', postId)

RPC (Stored Procedures)

const { data, error } = await insforge.database.rpc('get_user_stats', { user_id: '123' })

Selecting a Schema

Queries target the public schema by default — reach for .schema() only when you need a different one. The table name stays bare; .schema() maps to PostgREST's Accept-Profile (reads) / Content-Profile (writes) headers and chains in front of from() and rpc():

const { data, error } = await insforge.database
  .schema('analytics')
  .from('events')
  .select('id, name, occurred_at')

// Writes chain the same way
const { error: insertError } = await insforge.database
  .schema('analytics')
  .from('events')
  .insert([{ name: 'signup' }])

const { data: rollup, error: rpcError } = await insforge.database
  .schema('analytics')
  .rpc('rollup', { day: '2026-01-01' })

To make every query use one schema, set the default when you create the client (omit it to stay on public):

const insforge = createClient({
  baseUrl: process.env.NEXT_PUBLIC_INSFORGE_URL,
  anonKey: process.env.NEXT_PUBLIC_INSFORGE_ANON_KEY,
  db: { schema: 'analytics' },
})

You must grant access yourself. On v2.2.3+ backends every non-internal schema you create is automatically reachable over the data API, but a new schema's tables stay unreadable to anon/authenticated until you grant them — exposure is not access. Grant in the same migration that creates the table, then RLS filters rows exactly as in public:

GRANT USAGE ON SCHEMA analytics TO anon, authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA analytics TO anon, authenticated;

Don't target InsForge-internal schemas. Platform schemas (auth, storage, system, payments, …) are never exposed over the data API — keep app data in public or your own schemas so you never collide with them. Pointing .schema() at an internal schema (or any schema an older backend doesn't expose) fails with PostgREST PGRST106 ("The schema must be one of the following: ...") rather than silently falling back to public.

Filters

Filter	Example
.eq(col, val)	.eq('status', 'active')
.neq(col, val)	.neq('status', 'deleted')
.gt(col, val)	.gt('age', 18)
.gte(col, val)	.gte('price', 100)
.lt(col, val)	.lt('stock', 10)
.lte(col, val)	.lte('score', 50)
.like(col, pattern)	.like('name', '%Widget%')
.ilike(col, pattern)	.ilike('email', '%@gmail.com')
.in(col, array)	.in('status', ['pending', 'active'])
.is(col, val)	.is('deleted_at', null)

Modifiers

Modifier	Example
.order(col, opts)	.order('created_at', { ascending: false })
.limit(n)	.limit(10)
.range(from, to)	.range(0, 9)
.single()	Returns object, throws if multiple
.maybeSingle()	Returns object or null

Pagination

const page = 1, pageSize = 10
const from = (page - 1) * pageSize
const to = from + pageSize - 1

const { data, count } = await insforge.database
  .from('posts')
  .select('id, title, created_at', { count: 'exact' })
  .range(from, to)
  .order('created_at', { ascending: false })

Bandwidth-efficient reads

Every byte a query returns counts against the project's monthly egress allowance. The most common way projects exhaust it is not traffic volume — it is a small number of wasteful query shapes, usually written once and left running:

• Never poll with unbounded selects. A dashboard or hook that re-runs select() (all columns, no limit) on an interval re-downloads the entire table every cycle. A table with a few hundred rows of JSONB polled every 30 seconds costs ~1 GB/day with a single viewer. To watch for changes, subscribe with realtime instead of polling — realtime is channel pub/sub, so pair the subscription with a database trigger that calls realtime.publish(...) on writes (backend setup: insforge-cli realtime reference); table changes do not stream automatically. If you must poll, poll something cheap — select('updated_at', { count: 'exact' }).order('updated_at', { ascending: false }).limit(1) — and fetch full rows only when the count or newest updated_at moves: the count catches inserts and deletes, the timestamp catches edits (keep a system.update_updated_at() trigger on the table so edits advance it).
• Bound every list read. Always chain .limit(n) or .range(from, to) on queries that can return multiple rows. Render lists with pagination.
• Name your columns. select('id, title') instead of select() whenever the table has text/JSONB columns the view doesn't render. Fetch heavy columns (draft_data-style JSONB blobs, long text) only on the detail screen that needs them.
• Pause refresh loops when idle. If the UI auto-refreshes, stop the timer when the tab is hidden (document.visibilityState) — an admin page left open overnight is the classic silent egress burner.
• Let storage objects cache. Serve files with stable URLs so browsers and the CDN cache them, rather than re-downloading per render (cache-busting query params on every request defeat this).

Important Notes

• Insert requires array format: Always use insert([{...}]) not insert({...})
• Avoid large JSON/JSONB payloads in SDK reads/writes: PostgREST can consume excessive memory when rows contain multi-megabyte JSONB payloads. As a rule of thumb, treat JSONB around 1 MB or larger per row as a caution point for hot SDK paths, and normalize multi-megabyte or frequently accessed JSON into typed columns or child tables.
• Select only the columns you need: If a table has any large text/JSONB columns, avoid select('*') in list views. Fetch lightweight columns first, then lazy-load fields over ~1 MB on a detail screen or through a purpose-built RPC.
• All methods return { data, error } - always check for errors

---

InsForge SQL References

When creating tables via insforge db query (CLI), use these built-in references:

Reference	Description
auth.uid()	Returns current authenticated user's UUID
auth.users(id)	Reference to the built-in users table for foreign keys
auth.users.profile	JSONB profile metadata; use profile->>'name' / profile->>'avatar_url' in auth triggers
system.update_updated_at()	Built-in trigger function that auto-updates updated_at columns

Complete Example: Table with RLS and Triggers

-- Create table with user ownership
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL DEFAULT auth.uid() REFERENCES auth.users(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

-- Policies (see the insforge-cli database access-control reference for advanced patterns)
CREATE POLICY "users_can_insert_own_posts" ON posts
  FOR INSERT TO authenticated
  WITH CHECK (user_id = auth.uid());

-- Needed when app code uses .insert(...).select()
CREATE POLICY "users_can_read_own_posts" ON posts
  FOR SELECT TO authenticated
  USING (user_id = auth.uid());

CREATE POLICY "users_can_update_own_posts" ON posts
  FOR UPDATE TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());

CREATE POLICY "users_can_delete_own_posts" ON posts
  FOR DELETE TO authenticated
  USING (user_id = auth.uid());

-- Allow authenticated SDK callers to reach the table; RLS still filters rows
GRANT USAGE ON SCHEMA public TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON posts TO authenticated;

-- Auto-update updated_at on every UPDATE
CREATE TRIGGER posts_updated_at
  BEFORE UPDATE ON posts
  FOR EACH ROW
  EXECUTE FUNCTION system.update_updated_at();

For access-control best practices (RLS, infinite recursion prevention, SECURITY DEFINER, performance), see the insforge-cli skill's database/access-control.md reference.

Bulk Upsert (HTTP API)

Import CSV or JSON files directly into a table. No CLI equivalent yet — use the HTTP API.

POST /api/database/advance/bulk-upsert
Authorization: Bearer {admin-token-or-api-key}
Content-Type: multipart/form-data

Fields:
- file: CSV or JSON file (required)
- table: Target table name (required)
- upsertKey: Column for conflict resolution (optional)

Parameter	Effect
Without upsertKey	INSERT all records
With upsertKey	UPSERT — update existing rows on conflict, insert new ones

---

Best Practices

1. Generate TypeScript interfaces for every table schema

   • Use insforge db tables and insforge db query (CLI) to inspect the table schema
   • Create a corresponding TypeScript interface/type for type safety
   • This helps catch errors at compile time and improves developer experience

2. Normalize large JSONB data before building CRUD flows

   • Do not store large app state, document bodies, analytics payloads, or arrays of nested objects in a single JSONB column that the app reads/writes through PostgREST.
   • Prefer real columns for fields you filter, sort, display in lists, or update independently.
   • Move repeated nested objects into child tables with foreign keys and indexes.
   • Measure suspicious payloads with pg_column_size(jsonb_column) and enforce limits or warnings during ingestion.

Example: Generate Interface from Schema

// After checking table schema via `insforge db tables`
// Create a typed interface:

interface Post {
  id: string
  user_id: string
  title: string
  content: string | null
  created_at: string
  updated_at: string
}

// Cast data to the interface after select
const { data, error } = await insforge.database
  .from('posts')
  .select()

const posts = data as Post[]

Recommended Workflow

1. Check table schema     → insforge db tables / insforge db query
2. Check for large JSONB fields and normalize them if needed
3. Generate TypeScript interface for the table
4. Cast query results to the interface for type safety
5. Handle errors appropriately