genie-tcb-db-integrator
Integrate TCB (Tencent CloudBase) managed PostgreSQL database into web apps. Provides frontend SDK-first CRUD via PG REST API with Row Level Security (RLS), plus LLM-only scripts for DDL, migrations,
SKILL.md.codebuddy/skills/genie-tcb-db-integrator/SKILL.md
GitHubGenie TCB Database Integration (Serverless PostgreSQL)
Integrate TCB managed PostgreSQL into web apps using a serverless-first architecture: frontend JS SDK for CRUD (no backend needed), RLS for per-user data isolation, and LLM shell scripts for schema management.
Architecture Overview
Priority 1 (PRIMARY): Frontend JS SDK + RLS
app.rdb().from('items').select/insert/update/delete
→ TCB PG REST API (PostgREST-based)
Security: RLS (anon / authenticated / auth.uid())
No backend needed for standard CRUD.
Priority 2 (ADVANCED): RPC — Custom PostgreSQL Functions
LLM creates function via tcb-pg-function.sh → frontend calls rpc() helper (HTTP)
For complex queries, aggregations, multi-table operations.
Function runs with caller's RLS permissions (or SECURITY DEFINER for elevated access).
Priority 3 (RESTRICTED): LLM Agent scripts
tcb-pg-migrate.sh / tcb-pg-query.sh / tcb-pg-function.sh
→ auth-proxy → api-server → exec-pgsql
For: DDL, RLS setup, function creation, ad-hoc analytics, debug.
NEVER exposed via web app HTTP API endpoints.
Prerequisites
- Frontend: React app with CloudBase JS SDK (
@cloudbase/js-sdk) - TCB Environment: Must be initialized first (see Setup)
- Auth (optional but recommended):
genie-tcb-auth-integratorfor user-scoped data
MANDATORY: TCB Environment User Confirmation
DO NOT run ensure-cloudbase-env.sh or any TCB setup without explicit user approval.
Before ANY database work, you MUST follow this exact sequence:
Check if
/workspace/.env.tcbexists:cat /workspace/.env.tcb 2>/dev/nullIf
.env.tcbexists and containsCLOUDBASE_ENV_ID: TCB is ready, proceed.If
.env.tcbdoes NOT exist: You MUST STOP and useask_followup_questionto ask the user:The project does not have a TCB (Tencent CloudBase) environment yet. This is required for database features (data storage, queries, RLS). Would you like to enable TCB database for this project?Options:
- Enable TCB Database — Creates a TCB environment with managed PostgreSQL
- Skip — Do not enable TCB database at this time
ONLY if user explicitly selects "Enable TCB Database", run:
bash /workspace/.genie/scripts/bash/ensure-cloudbase-env.sh --project-dir /workspaceIf user selects "Skip", do NOT create the environment. Inform the user that database features require TCB and stop the database setup.
NEVER assume the user wants TCB enabled. NEVER skip the confirmation step.
ensure-cloudbase-env.shautomatically installs@cloudbase/js-sdk— no need to install separately.
Script Paths
IMPORTANT: Before calling any script, define these variables. All examples below use them.
# Define once at the start of any task
DB_SCRIPTS="/workspace/.codebuddy/skills/genie-tcb-db-integrator/scripts"
PROJECT_DIR="/workspace"
| Script | Variable | Purpose |
|---|---|---|
$DB_SCRIPTS/tcb-pg-migrate.sh |
DDL | CREATE TABLE, ALTER, RLS policies, indexes |
$DB_SCRIPTS/tcb-pg-query.sh |
Query | Ad-hoc SQL, schema inspection (tables/columns/indexes/rls) |
$DB_SCRIPTS/tcb-pg-function.sh |
Functions | Create/list/show/grant/revoke/rename/drop functions |
If scripts are not found at the above path, try:
# Alternative: find the skill directory dynamically
DB_SCRIPTS=$(find /workspace -path "*genie-tcb-db-integrator/scripts" -type d 2>/dev/null | head -1)
1. Creating Tables (LLM Agent)
Always include: id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, user_id TEXT NOT NULL DEFAULT auth.uid(), created_at TIMESTAMPTZ DEFAULT now().
bash $DB_SCRIPTS/tcb-pg-migrate.sh \
--project-dir $PROJECT_DIR \
--sql "CREATE TABLE items (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id TEXT NOT NULL DEFAULT auth.uid(),
title TEXT NOT NULL,
content TEXT,
status TEXT DEFAULT 'draft',
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
)"
Table comment (shown in Genie DB management page):
bash $DB_SCRIPTS/tcb-pg-migrate.sh \
--project-dir $PROJECT_DIR \
--sql "COMMENT ON TABLE items IS 'User content items with draft/publish workflow'"
IMPORTANT: exec-pgsql does NOT support multiple SQL statements in one call. Each statement must be a separate invocation.
2. RLS — Row Level Security (LLM Agent)
RLS controls who can read/write which rows at the database level. After enabling RLS, all operations not explicitly allowed by policies are denied by default.
Enable RLS
bash $DB_SCRIPTS/tcb-pg-migrate.sh \
--project-dir $PROJECT_DIR \
--sql "ALTER TABLE items ENABLE ROW LEVEL SECURITY"
Roles
| Role | Description |
|---|---|
anon |
Unauthenticated users (accessKey only, no login) |
authenticated |
Logged-in users (access_token from TCB Auth) |
Key function: auth.uid()
Returns the current user's identity. Use in policies to enforce data ownership.
Policy syntax
-- For SELECT, UPDATE, DELETE:
CREATE POLICY name ON table FOR operation TO role USING (condition);
-- For INSERT (and UPDATE write-check):
CREATE POLICY name ON table FOR INSERT TO role WITH CHECK (condition);
IMPORTANT: Use DROP POLICY IF EXISTS name ON table before CREATE POLICY — PostgreSQL has no CREATE POLICY IF NOT EXISTS.
RLS Pattern 1: Read all, modify own data
Use case: User comments, public profiles
# Everyone can read all data
...tcb-pg-migrate.sh --sql "CREATE POLICY select_all ON items FOR SELECT TO authenticated USING (true)"
# Only owner can insert (user_id auto-bound)
...tcb-pg-migrate.sh --sql "CREATE POLICY insert_own ON items FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid())"
# Only owner can update own data
...tcb-pg-migrate.sh --sql "CREATE POLICY update_own ON items FOR UPDATE TO authenticated USING (user_id = auth.uid())"
# Only owner can delete own data
...tcb-pg-migrate.sh --sql "CREATE POLICY delete_own ON items FOR DELETE TO authenticated USING (user_id = auth.uid())"
RLS Pattern 2: Owner-only (private data)
Use case: User settings, order management
...tcb-pg-migrate.sh --sql "CREATE POLICY select_own ON items FOR SELECT TO authenticated USING (user_id = auth.uid())"
...tcb-pg-migrate.sh --sql "CREATE POLICY insert_own ON items FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid())"
...tcb-pg-migrate.sh --sql "CREATE POLICY update_own ON items FOR UPDATE TO authenticated USING (user_id = auth.uid())"
...tcb-pg-migrate.sh --sql "CREATE POLICY delete_own ON items FOR DELETE TO authenticated USING (user_id = auth.uid())"
RLS Pattern 3: Public read (including anonymous)
Use case: Announcements, help docs
...tcb-pg-migrate.sh --sql "CREATE POLICY select_anon ON items FOR SELECT TO anon, authenticated USING (true)"
RLS Pattern 4: Read all, no modification
Use case: Product catalog, system config
...tcb-pg-migrate.sh --sql "CREATE POLICY select_all ON items FOR SELECT TO authenticated USING (true)"
# No INSERT/UPDATE/DELETE policies → all writes denied by default
RLS Pattern 5: Published + own drafts
Use case: Blog articles, product listings
...tcb-pg-migrate.sh --sql "CREATE POLICY select_published_or_own ON items FOR SELECT TO authenticated USING (status = 'published' OR user_id = auth.uid())"
...tcb-pg-migrate.sh --sql "CREATE POLICY insert_own ON items FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid())"
...tcb-pg-migrate.sh --sql "CREATE POLICY update_own ON items FOR UPDATE TO authenticated USING (user_id = auth.uid())"
...tcb-pg-migrate.sh --sql "CREATE POLICY delete_own ON items FOR DELETE TO authenticated USING (user_id = auth.uid())"
RLS Pattern 6: Team shared data
Use case: Multi-tenant, team collaboration
...tcb-pg-migrate.sh --sql "CREATE POLICY select_team ON items FOR SELECT TO authenticated USING (team_id IN (SELECT team_id FROM team_members WHERE user_id = auth.uid()))"
...tcb-pg-migrate.sh --sql "CREATE POLICY insert_team ON items FOR INSERT TO authenticated WITH CHECK (team_id IN (SELECT team_id FROM team_members WHERE user_id = auth.uid()))"
...tcb-pg-migrate.sh --sql "CREATE POLICY update_team ON items FOR UPDATE TO authenticated USING (team_id IN (SELECT team_id FROM team_members WHERE user_id = auth.uid()))"
...tcb-pg-migrate.sh --sql "CREATE POLICY delete_team ON items FOR DELETE TO authenticated USING (team_id IN (SELECT team_id FROM team_members WHERE user_id = auth.uid()))"
RLS Pattern 7: Insert only, no modification
Use case: Feedback, audit logs
...tcb-pg-migrate.sh --sql "CREATE POLICY select_own ON items FOR SELECT TO authenticated USING (user_id = auth.uid())"
...tcb-pg-migrate.sh --sql "CREATE POLICY insert_own ON items FOR INSERT TO authenticated WITH CHECK (user_id = auth.uid())"
# No UPDATE/DELETE policies → data cannot be modified after submission
RLS Pattern 8: No permissions (server-only)
Use case: Internal logs, admin data
# Enable RLS but create NO policies → all client operations denied
...tcb-pg-migrate.sh --sql "ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY"
# Data only accessible via LLM scripts (admin role) or cloud functions
Best Practice: DEFAULT auth.uid() + WITH CHECK
Always use DEFAULT auth.uid() on user_id column AND WITH CHECK (user_id = auth.uid()) on INSERT policy. This double protection:
DEFAULT: auto-fills user_id so client doesn't need to pass itWITH CHECK: prevents malicious clients from forging another user's user_id
3. Frontend SDK — CRUD Operations
The frontend uses CloudBase JS SDK app.rdb() for all data operations. Under the hood, the SDK calls the TCB PG REST API (https://{envId}.api.tcloudbasegateway.com/v1/rdb/rest/{table}) with the user's access_token. RLS enforces security at the database level.
SDK Setup
// src/lib/database.ts — copy from skill lib/database.ts
import cloudbase from '@cloudbase/js-sdk'
const app = cloudbase.init({
env: import.meta.env.VITE_CLOUDBASE_ENV_ID,
region: import.meta.env.VITE_CLOUDBASE_REGION || 'ap-shanghai',
accessKey: import.meta.env.VITE_CLOUDBASE_PUBLISH_KEY,
})
// IMPORTANT: must pass { database: 'public' } to use the public schema
// Without this, SDK uses envId as schema name → "Invalid schema" error
export const db = app.rdb({ database: 'public' })
export const auth = app.auth()
export default app
SELECT — Query data
// Basic query with filters + order + limit
const { data, error } = await db
.from('items')
.select('id, title, created_at')
.eq('status', 'published')
.order('created_at', { ascending: false })
.limit(20)
// Select specific columns only
const { data } = await db.from('items').select('id, title, status')
// Single record (returns object, not array)
const { data } = await db.from('items').select('*').eq('id', 42).single()
// Maybe single (returns null if not found, no error)
const { data } = await db.from('items').select('*').eq('id', 42).maybeSingle()
// Pagination with total count
const from = (page - 1) * pageSize
const to = from + pageSize - 1
const { data, count } = await db
.from('items')
.select('*', { count: 'exact' })
.order('created_at', { ascending: false })
.range(from, to)
// Count only (no data returned)
const { count } = await db.from('items').select('*', { count: 'exact', head: true })
// Multiple filters (AND)
const { data } = await db
.from('items')
.select('*')
.gte('created_at', '2024-01-01')
.ilike('title', '%search%')
.in('status', ['published', 'featured'])
.neq('user_id', 'some-id')
// OR conditions
const { data } = await db
.from('items')
.select('*')
.or('status.eq.published,status.eq.featured')
// NULL check
const { data } = await db.from('items').select('*').is('deleted_at', null)
// NOT NULL
const { data } = await db.from('items').select('*').not('deleted_at', 'is', null)
// Multi-column match (shorthand for multiple .eq())
const { data } = await db.from('items').select('*').match({ status: 'published', lang: 'en' })
// Foreign key join
const { data } = await db.from('items').select(`
id, title, created_at,
category:categories(id, name)
`).eq('status', 'published')
// Nested join
const { data } = await db.from('categories').select(`
name,
items(title, user:users(name, email))
`)
// Full-text search
const { data } = await db
.from('items')
.select('*')
.textSearch('content', 'search query', { type: 'websearch', config: 'simple' })
// Array/JSONB contains
const { data } = await db.from('items').select('*').contains('tags', ['tech', 'ai'])
const { data } = await db.from('items').select('*').containedBy('tags', ['tech', 'ai', 'web'])
INSERT — Create data
// Single insert (user_id auto-filled by DEFAULT auth.uid())
// NOTE: Do NOT chain .select() after .insert() — TCB PG REST API does not support
// Prefer: return=representation, which causes 406 error.
const { error } = await db
.from('items')
.insert({ title: 'New Item', content: 'Hello', status: 'draft' })
// Batch insert
const { error } = await db.from('items').insert([
{ title: 'Item A', content: 'Content A' },
{ title: 'Item B', content: 'Content B' },
])
// Upsert — update on conflict (merge duplicates)
const { error } = await db
.from('items')
.upsert({ id: 42, title: 'Updated or Created', content: '...' })
// Upsert — ignore on conflict
const { error } = await db
.from('items')
.upsert({ id: 42, title: 'Skip if exists' }, { ignoreDuplicates: true })
UPDATE — Modify data
// Update by ID (RLS enforces owner-only)
const { error } = await db
.from('items')
.update({ title: 'Updated Title', status: 'published' })
.eq('id', 42)
// Batch update by condition
const { error } = await db
.from('items')
.update({ status: 'archived' })
.eq('status', 'draft')
.lt('created_at', '2024-01-01')
DELETE — Remove data
// Delete by ID (RLS enforces owner-only)
const { error } = await db.from('items').delete().eq('id', 42)
// Batch delete by condition
const { error } = await db
.from('items')
.delete()
.eq('status', 'archived')
.lt('created_at', '2023-01-01')
Filter operators reference
| Method | SQL | Example |
|---|---|---|
.eq(col, val) |
= |
.eq('status', 'published') |
.neq(col, val) |
!= |
.neq('status', 'draft') |
.gt(col, val) |
> |
.gt('price', 100) |
.gte(col, val) |
>= |
.gte('created_at', '2024-01-01') |
.lt(col, val) |
< |
.lt('price', 50) |
.lte(col, val) |
<= |
.lte('age', 18) |
.like(col, pat) |
LIKE |
.like('title', '%cloud%') |
.ilike(col, pat) |
ILIKE |
.ilike('title', '%cloud%') (case-insensitive) |
.is(col, val) |
IS |
.is('deleted_at', null) |
.in(col, arr) |
IN |
.in('id', [1, 2, 3]) |
.contains(col, val) |
@> |
.contains('tags', ['tech']) |
.containedBy(col, val) |
<@ |
.containedBy('tags', ['a','b','c']) |
.overlaps(col, val) |
&& |
.overlaps('tags', ['tech']) |
.not(col, op, val) |
NOT |
.not('status', 'eq', 'deleted') |
.or(filters) |
OR |
.or('status.eq.published,featured.eq.true') |
.match(obj) |
multi = |
.match({ status: 'published', lang: 'en' }) |
.textSearch(col, q) |
@@ |
.textSearch('content', 'query') |
Modifier methods
| Method | Description | Example |
|---|---|---|
.order(col, opts) |
Sort results | .order('created_at', { ascending: false }) |
.limit(n) |
Limit row count | .limit(20) |
.range(from, to) |
Pagination (0-based inclusive) | .range(0, 19) |
.single() |
Return single object (error if != 1 row) | .eq('id', 42).single() |
.maybeSingle() |
Return object or null (error if > 1 row) | .eq('id', 42).maybeSingle() |
.select(cols, opts) |
Column selection + count | .select('*', { count: 'exact' }) |
4. Frontend RPC — Custom PostgreSQL Functions
For complex queries (aggregations, multi-table joins, custom logic), LLM creates PostgreSQL functions, then frontend calls them via the rpc() helper from database.ts.
IMPORTANT:
@cloudbase/js-sdk@3.xdoes NOT have adb.rpc()method. Therpc()helper calls the TCB PG REST API (/v1/rdb/rest/rpc/{function}) directly via HTTP. Do NOT usedb.rpc()— it will throwTypeError: db.rpc is not a function.
Function volatility types
| Type | Description | GET support |
|---|---|---|
VOLATILE |
Can modify DB, different results each call (default) | No |
STABLE |
Read-only within a statement, can read DB | Yes |
IMMUTABLE |
Always same result for same input, no DB access | Yes |
Creating functions (LLM Agent)
Use tcb-pg-function.sh create:
# Search function (read-only, STABLE)
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace create \
--name search_items --args "keyword text" --returns "SETOF items" \
--language sql --volatility STABLE \
--comment "Search items by keyword (case-insensitive)" \
--body "SELECT * FROM items WHERE title ILIKE '%' || keyword || '%';"
# Stats function returning JSON
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace create \
--name get_item_stats --returns json --language sql --volatility STABLE \
--comment "Get aggregate statistics of all items" \
--body "SELECT json_build_object('total', COUNT(*), 'latest', MAX(created_at)) FROM items;"
# Simple calculation (IMMUTABLE)
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace create \
--name add_numbers --args "a integer, b integer" --returns integer \
--language sql --volatility IMMUTABLE \
--body "SELECT a + b;"
# plpgsql function with defaults
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace create \
--name get_recent_items --args "days_ago integer DEFAULT 30, max_results integer DEFAULT 10" \
--returns "SETOF items" --language plpgsql --volatility STABLE \
--comment "Get recently created items within N days" \
--body "RETURN QUERY SELECT * FROM items WHERE created_at >= NOW() - (days_ago || ' days')::interval ORDER BY created_at DESC LIMIT max_results;"
# SECURITY DEFINER function (bypasses RLS — use with caution)
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace create \
--name get_global_stats --returns json --language sql --volatility STABLE \
--security-definer \
--comment "Global stats across all users (admin only)" \
--body "SELECT json_build_object('total_users', COUNT(DISTINCT user_id), 'total_items', COUNT(*)) FROM items;"
Script create options:
| Option | Required | Description |
|---|---|---|
--name |
Yes | Function name |
--body |
Yes | Function body SQL |
--args |
No | Arguments (e.g., "keyword text, limit_num integer") |
--returns |
No | Return type (default: void). Use SETOF table for table results |
--language |
No | sql (default) or plpgsql |
--volatility |
No | IMMUTABLE, STABLE, or VOLATILE |
--comment |
No | Function description (shown in Genie DB management) |
--security-definer |
No | Run with creator's permissions (bypasses RLS) |
Calling functions from frontend
Use the rpc() helper exported from database.ts (calls PG REST API directly via HTTP):
import { rpc } from '@/lib/database'
// Basic call with parameters
const result = await rpc('add_numbers', { a: 1, b: 2 })
// → 3
// Function returning table rows — filter/order/limit via options
const items = await rpc('search_items', { keyword: 'hello' }, {
select: 'id,title',
order: 'created_at.desc',
limit: 10,
})
// Stats function returning JSON
const stats = await rpc('get_item_stats')
// → { total: 42, latest: '2026-04-23T...' }
// No parameters
const data = await rpc('get_item_stats')
rpc() function signature:
rpc<T>(functionName: string, params?: Record<string, any>, options?: {
select?: string // Column selection on result: 'id,title'
order?: string // Sort: 'created_at.desc'
limit?: number // Max rows
offset?: number // Skip rows
}) → Promise<T>
Do NOT use
db.rpc()—@cloudbase/js-sdk@3.xdoes not have this method. Always use therpc()helper.
Function permissions — Execute & Security
Function permissions have two dimensions:
1. EXECUTE permission — who can call the function
By default, PUBLIC (everyone including anonymous) can call any function. Use REVOKE + GRANT to restrict:
# Revoke from everyone, then grant only to authenticated users
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace revoke --name search_items --args "keyword text" --role PUBLIC
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace grant --name search_items --args "keyword text" --role authenticated
# Grant to both anon and authenticated (public-readable function)
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace grant --name get_public_info --role anon
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace grant --name get_public_info --role authenticated
| Execute permission | Who can call | Use case |
|---|---|---|
PUBLIC (default) |
Anyone (anon + authenticated) | Public stats, info |
authenticated only |
Logged-in users | User-specific operations |
anon, authenticated |
Explicitly both | Same as PUBLIC but explicit |
2. Security mode — whose permissions apply inside the function
| Mode | SQL inside function runs as | RLS behavior | Use case |
|---|---|---|---|
SECURITY INVOKER (default) |
Caller's role (anon/authenticated) | RLS enforced, auth.uid() = caller |
User data queries |
SECURITY DEFINER |
Function creator's role (admin) | Bypasses RLS, sees all data | Global stats, leaderboards |
# Change a function to SECURITY DEFINER
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace set-definer --name get_leaderboard --args "top_n integer"
# Change back to SECURITY INVOKER
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace set-invoker --name get_leaderboard --args "top_n integer"
3. Query and manage functions
# List all functions with permissions
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace list
# Show details for a specific function
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace show --name search_items
# Drop a function
bash $DB_SCRIPTS/tcb-pg-function.sh \
--project-dir /workspace drop --name old_function
4. Common permission patterns
| Pattern | Execute | Security | Example |
|---|---|---|---|
| Public read, RLS enforced | PUBLIC |
INVOKER |
get_published_items() |
| Authenticated only, RLS enforced | authenticated |
INVOKER |
search_my_items(keyword) |
| Public leaderboard, bypass RLS | PUBLIC |
DEFINER |
get_leaderboard(top_n) |
| Admin stats, bypass RLS, login required | authenticated |
DEFINER |
get_admin_stats() |
| Internal only, no client access | revoke from all | INVOKER |
Helper functions |
WARNING: SECURITY DEFINER bypasses RLS — only use for functions that genuinely need cross-user data access. Always restrict EXECUTE to the minimum required roles. Validate inputs to prevent data leakage.
Supported parameter types
| Type | JS Example |
|---|---|
integer |
{ id: 1 } |
text / varchar |
{ name: 'test' } |
boolean |
{ active: true } |
json / jsonb |
{ data: { key: 'value' } } |
integer[] |
{ ids: [1, 2, 3] } |
text[] |
{ names: ['a', 'b'] } |
5. HTTP REST API Reference
The PG REST API follows PostgREST protocol. Base URL: https://{envId}.api.tcloudbasegateway.com/v1/rdb/rest/.
All requests require Authorization: Bearer <access_token>.
Query (GET)
GET /v1/rdb/rest/{table}?select={cols}&{filter}&order={col.asc|desc}&limit={n}&offset={n}
Filter syntax: column=operator.value (e.g., status=eq.published, price=gt.100, name=ilike.*search*)
Operators: eq, neq, gt, gte, lt, lte, like, ilike, is, in, not, or, cs (contains), cd (contained by), ov (overlaps)
Headers:
Prefer: count=exact— include total count inContent-Rangeheader
Insert (POST)
POST /v1/rdb/rest/{table}
Content-Type: application/json
Prefer: return=representation
{"title": "New Item", "content": "..."}
Batch: send JSON array [{...}, {...}].
Upsert: add Prefer: resolution=merge-duplicates (ON CONFLICT DO UPDATE) or resolution=ignore-duplicates (ON CONFLICT DO NOTHING).
Update (PATCH)
PATCH /v1/rdb/rest/{table}?{filter}
Content-Type: application/json
Prefer: return=representation
{"title": "Updated"}
WHERE condition is mandatory — returns 400 without filter.
Delete (DELETE)
DELETE /v1/rdb/rest/{table}?{filter}
Prefer: return=representation
WHERE condition is mandatory — returns 400 without filter.
RPC (POST)
POST /v1/rdb/rest/rpc/{function_name}
Content-Type: application/json
{"param1": "value1", "param2": "value2"}
GET (for IMMUTABLE/STABLE functions): GET /v1/rdb/rest/rpc/{function_name}?param1=value1
Chain filters on result: POST /v1/rdb/rest/rpc/search_items?select=title&order=created_at.desc&limit=5
Error codes
| Code | HTTP | Description |
|---|---|---|
INVALID_PARAM |
400 | Invalid request parameters |
INVALID_REQUEST |
400 | Missing WHERE, SQL error, etc. |
PERMISSION_DENIED |
401 | Authentication failed |
PERMISSION_DENIED |
403 | RLS policy denied |
RESOURCE_NOT_FOUND |
404 | Table not found |
DATABASE_23505 |
409 | Unique constraint violation |
SYS_ERR |
500 | Internal error |
6. LLM Query Script (Analytics / Debug)
For ad-hoc queries during LLM conversation (NOT for web app use):
bash $DB_SCRIPTS/tcb-pg-query.sh \
--project-dir $PROJECT_DIR \
--sql "SELECT COUNT(*) FROM items WHERE created_at >= CURRENT_DATE"
bash $DB_SCRIPTS/tcb-pg-query.sh \
--project-dir $PROJECT_DIR \
--sql "SELECT status, COUNT(*) as cnt FROM items GROUP BY status ORDER BY cnt DESC"
7. Error Handling
const { error } = await db.from('items').insert({ title: 'Test' })
if (error) {
switch (error.code) {
case 'PERMISSION_DENIED':
// 401: Need login / 403: RLS denied
break
case 'DATABASE_23505':
// Unique constraint violation
break
case 'RESOURCE_NOT_FOUND':
// Table doesn't exist
break
default:
console.error(error.message)
}
}
8. Scripts Reference
All scripts are located at $DB_SCRIPTS (see "Script Paths" section above) and require --project-dir /workspace.
tcb-pg-migrate.sh — DDL & RLS
Execute a single DDL/DML SQL statement (CREATE TABLE, ALTER TABLE, RLS policies, indexes, etc.):
# Create a table
bash .../scripts/tcb-pg-migrate.sh --project-dir /workspace \
--sql "CREATE TABLE items (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id TEXT NOT NULL DEFAULT auth.uid(),
title TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
)"
# Enable RLS
bash .../scripts/tcb-pg-migrate.sh --project-dir /workspace \
--sql "ALTER TABLE items ENABLE ROW LEVEL SECURITY"
# Create RLS policy
bash .../scripts/tcb-pg-migrate.sh --project-dir /workspace \
--sql "CREATE POLICY select_own ON items FOR SELECT TO authenticated USING (user_id = auth.uid())"
# Add table comment
bash .../scripts/tcb-pg-migrate.sh --project-dir /workspace \
--sql "COMMENT ON TABLE items IS 'User content items'"
# Add column
bash .../scripts/tcb-pg-migrate.sh --project-dir /workspace \
--sql "ALTER TABLE items ADD COLUMN status TEXT DEFAULT 'draft'"
# Create index
bash .../scripts/tcb-pg-migrate.sh --project-dir /workspace \
--sql "CREATE INDEX idx_items_user_id ON items(user_id)"
tcb-pg-query.sh — Ad-hoc queries & schema inspection
Execute SQL or use built-in shortcuts (LLM conversation only):
QUERY="$DB_SCRIPTS/tcb-pg-query.sh"
# === Built-in shortcuts ===
# List all tables
bash $QUERY --project-dir /workspace tables
# Show columns for a table
bash $QUERY --project-dir /workspace columns --table items
# Show indexes
bash $QUERY --project-dir /workspace indexes --table items
# Show RLS policies (one table or all)
bash $QUERY --project-dir /workspace rls --table items
bash $QUERY --project-dir /workspace rls
# Count rows
bash $QUERY --project-dir /workspace count --table items
# Sample data (default 10 rows)
bash $QUERY --project-dir /workspace sample --table items --limit 5
# === Custom SQL ===
bash $QUERY --project-dir /workspace \
--sql "SELECT status, COUNT(*) as cnt FROM items GROUP BY status ORDER BY cnt DESC"
bash $QUERY --project-dir /workspace \
--sql "SELECT * FROM items WHERE created_at >= NOW() - INTERVAL '7 days' LIMIT 20"
tcb-pg-function.sh — Create & manage functions
Unified script for all function operations:
SCRIPT="$DB_SCRIPTS/tcb-pg-function.sh"
# === CREATE ===
# Simple SQL function
bash $SCRIPT --project-dir /workspace create \
--name add_numbers --args "a integer, b integer" --returns integer \
--language sql --volatility IMMUTABLE --body "SELECT a + b;"
# Search function returning table rows
bash $SCRIPT --project-dir /workspace create \
--name search_items --args "keyword text" --returns "SETOF items" \
--language sql --volatility STABLE \
--comment "Search items by keyword" \
--body "SELECT * FROM items WHERE title ILIKE '%' || keyword || '%';"
# Stats function returning JSON
bash $SCRIPT --project-dir /workspace create \
--name get_stats --returns json --language sql --volatility STABLE \
--comment "Aggregate statistics" \
--body "SELECT json_build_object('total', COUNT(*), 'latest', MAX(created_at)) FROM items;"
# plpgsql function with default params
bash $SCRIPT --project-dir /workspace create \
--name get_recent --args "days integer DEFAULT 30, limit_n integer DEFAULT 10" \
--returns "SETOF items" --language plpgsql --volatility STABLE \
--body "RETURN QUERY SELECT * FROM items WHERE created_at >= NOW() - (days || ' days')::interval ORDER BY created_at DESC LIMIT limit_n;"
# SECURITY DEFINER function (bypasses RLS)
bash $SCRIPT --project-dir /workspace create \
--name get_leaderboard --args "top_n integer DEFAULT 10" \
--returns "SETOF items" --language sql --volatility STABLE \
--security-definer --comment "Top N leaderboard (bypasses RLS)" \
--body "SELECT * FROM items ORDER BY score DESC LIMIT top_n;"
# === LIST all functions ===
bash $SCRIPT --project-dir /workspace list
# === SHOW details for one function ===
bash $SCRIPT --project-dir /workspace show --name search_items
# === GRANT execute permission ===
bash $SCRIPT --project-dir /workspace grant --name search_items --args "keyword text" --role authenticated
bash $SCRIPT --project-dir /workspace grant --name get_stats --role anon
# === REVOKE execute permission ===
bash $SCRIPT --project-dir /workspace revoke --name search_items --args "keyword text" --role PUBLIC
# === CHANGE security mode ===
bash $SCRIPT --project-dir /workspace set-definer --name get_leaderboard --args "top_n integer"
bash $SCRIPT --project-dir /workspace set-invoker --name get_leaderboard --args "top_n integer"
# === DROP a function ===
bash $SCRIPT --project-dir /workspace drop --name old_function
bash $SCRIPT --project-dir /workspace drop --name old_function --args "param1 text"
# === RENAME a function ===
bash $SCRIPT --project-dir /workspace rename --name old_name --new-name new_name
# === UPDATE comment ===
bash $SCRIPT --project-dir /workspace comment --name search_items --comment "Updated description"
# === VIEW source code only ===
bash $SCRIPT --project-dir /workspace source --name search_items
All tcb-pg-function.sh actions:
| Action | Required options | Description |
|---|---|---|
create |
--name, --body |
Create or replace a function |
list |
(none) | List all user functions with permissions |
show |
--name |
Show full details (args, returns, security, ACL, source) |
source |
--name |
Show function source code only |
grant |
--name, --role |
Grant EXECUTE to a role |
revoke |
--name, --role |
Revoke EXECUTE from a role |
set-definer |
--name |
Change to SECURITY DEFINER (bypasses RLS) |
set-invoker |
--name |
Change to SECURITY INVOKER (RLS enforced) |
rename |
--name, --new-name |
Rename a function |
comment |
--name, --comment |
Update function description |
drop |
--name |
Drop a function |
All tcb-pg-query.sh actions:
| Action | Required options | Description |
|---|---|---|
--sql "..." |
(none) | Execute custom SQL |
tables |
(none) | List all tables with comments and column counts |
columns |
--table |
Show columns, types, defaults, nullable, comments |
indexes |
--table |
Show indexes and their definitions |
rls |
--table (optional) |
Show RLS policies (for one table or all) |
count |
--table |
Count rows in a table |
sample |
--table, --limit (optional) |
Sample rows (default 10) |
TCB SDK Known Limitations
1. app.rdb() must pass { database: 'public' }
The CloudBase JS SDK defaults to using the envId as the PostgREST schema name. Since TCB managed PostgreSQL uses the public schema, you MUST pass { database: 'public' }:
// CORRECT
export const db = app.rdb({ database: 'public' })
// WRONG — causes "Invalid schema: gn-xxxxx" error
export const db = app.rdb()
2. Do NOT chain .select() after .insert() or .upsert()
TCB PG REST API does not support the Prefer: return=representation header, which the SDK sends when .select() is chained after a write operation. This causes a 406 Not Acceptable error.
// CORRECT
const { error } = await db.from('items').insert({ title: 'New' })
// WRONG — returns 406 error on TCB
const { data } = await db.from('items').insert({ title: 'New' }).select()
If you need the inserted row data, query it separately after insert:
const { error } = await db.from('items').insert({ title: 'New' })
if (!error) {
const { data } = await db.from('items').select('*').order('id', { ascending: false }).limit(1).single()
}
3. Anonymous session auto-creation
CloudBase JS SDK automatically creates an anonymous session when initialized with accessKey. This means auth.getUser() may return a user object with provider: 'anonymous' even when the user has not explicitly logged in. The genie-tcb-auth-integrator auth-context.tsx handles this by filtering anonymous users.
If you write custom auth checks, always filter:
const { data } = await auth.getUser()
const user = data?.user
if (!user || !user.email || user.app_metadata?.provider === 'anonymous') {
// Not a real logged-in user
}
4. Do NOT pass null values in .insert()
Omit fields instead of passing null to let PostgreSQL use column defaults (e.g., DEFAULT auth.uid() for user_id, DEFAULT now() for created_at):
// CORRECT — omit user_id, let DEFAULT auth.uid() fill it
const { error } = await db.from('items').insert({ title: 'New', content: 'Hello' })
// WRONG — explicit null overrides the DEFAULT
const { error } = await db.from('items').insert({ title: 'New', user_id: null })
5. db.rpc() does NOT exist in @cloudbase/js-sdk@3.x
The SDK only supports table-level CRUD (from/select/insert/update/delete). To call PostgreSQL functions (RPC), use the rpc() helper exported from database.ts, which calls the PG REST API endpoint directly via HTTP:
// CORRECT — use the rpc() helper
import { rpc } from '@/lib/database'
const result = await rpc('add_numbers', { a: 1, b: 2 })
// WRONG — db.rpc is not a function in @cloudbase/js-sdk@3.x
const { data } = await db.rpc('add_numbers', { a: 1, b: 2 }) // TypeError!
MANDATORY: Dangerous SQL User Confirmation
Before executing any high-risk SQL, you MUST show the SQL to the user and get explicit confirmation via ask_followup_question.
High-risk SQL includes:
DROP TABLE/DROP FUNCTION— permanently deletes data or functionsTRUNCATE— deletes all rows without recoveryDELETEwithout WHERE clause — deletes all rowsALTER TABLE DROP COLUMN— permanently removes a column and its dataALTER TABLE ... ENABLE ROW LEVEL SECURITYon a table with existing data — may lock out accessREVOKE ALLon functions or tables — may break running applicationsSECURITY DEFINERfunctions — bypasses RLS, potential data leakage- Any SQL that modifies or deletes existing user data
Safe SQL (no confirmation needed):
CREATE TABLE/CREATE FUNCTION— creates new objectsCREATE POLICY/DROP POLICY IF EXISTS— RLS policy managementALTER TABLE ADD COLUMN— adds a column (no data loss)COMMENT ON— adds descriptionsCREATE INDEX— improves performanceINSERT— adds data- Read-only queries (
SELECT,SHOW, etc.)
Confirmation flow:
LLM identifies high-risk SQL
↓
STOP and use ask_followup_question:
"I need to execute the following SQL which will [describe impact]:
[show the exact SQL]
This operation is irreversible. Proceed?"
Options: Execute / Cancel
↓
ONLY if user selects "Execute", run the script
Key Rules
- Frontend SDK is the primary path — use
app.rdb()for all web app CRUD - Never expose raw SQL via web app API — auth-proxy
/pg/queryis LLM-only - Always enable RLS on every table — it's the security boundary
- Always include
user_id TEXT DEFAULT auth.uid()for user-scoped tables - One SQL statement per script call —
exec-pgsqldoesn't support batching - Use
DROP POLICY IF EXISTSbeforeCREATE POLICY— noIF NOT EXISTSfor policies - RPC functions respect RLS by default — use
SECURITY DEFINERonly when necessary - TCB environment is shared with
genie-tcb-auth-integrator— no separate setup needed - Dangerous SQL requires user confirmation — see "Dangerous SQL User Confirmation" section above