postgresql-development-cloudbase

Sibling skills (local only)

Sibling CloudBase skills ship beside this skill. Use local relative paths such as ../auth-tool-cloudbase/SKILL.md.

If a referenced sibling skill file is missing from this environment, ask the user to install the full CloudBase plugin (or the missing skill). Do not HTTP-fetch remote skill or protocol markdown into the agent context.

CloudBase PostgreSQL Development

Activation Contract

Use this first when

• The task says CloudBase PG, PostgreSQL, Postgres, PG mode, RLS, JS SDK v3 PostgreSQL, app.rdb(), queryPgDatabase, or managePgDatabase.
• A Web app or CMS must persist business data in CloudBase PostgreSQL instead of NoSQL or MySQL.

Then also read

• Web auth provider readiness -> ../auth-tool-cloudbase/SKILL.md
• Web login implementation -> ../auth-web-cloudbase/SKILL.md
• General Web implementation and verification -> ../web-development/SKILL.md
• Browser storage upload -> ../cloud-storage-web/SKILL.md
• Raw HTTP API details only when SDK coverage is blocked -> ../http-api-cloudbase/SKILL.md
• PG reference index -> references/index.md
• PG mode overview -> references/pg-mode-overview.md
• Auth / GRANT / RLS details -> references/auth-and-rls.md
• End-to-end PG app closure -> references/app-workflow.md
• PG storage details — MUST read before writing any bucket / upload / URL code -> references/storage-pg.md
• HTTP API fallback -> references/http-api.md
• Troubleshooting -> references/troubleshooting.md

Do NOT use first

• relational-database-mcp-cloudbase / queryMysqlDatabase / manageMysqlDatabase: those are MySQL-oriented.
• cloudbase-document-database-web-sdk / collection APIs for business data that must live in CloudBase PG.

Required Flow

🚨 CRITICAL: PG mode API is NOT the same as NoSQL

CloudBase PG (app.rdb(), app.storage.from('bucket')) uses different API method names than CloudBase NoSQL (app.database(), app.uploadFile()). Low-capability models often paste legacy NoSQL/auth snippets from training; reject that path immediately. If this task is PG-backed, do not write app.database(), db.collection(...), app.uploadFile(), getLoginState(), or route guards based on auth.getUser(). Use app.rdb(), PG storage v3, and auth.getSession() instead. If you are used to writing .where(), .orderBy(), .count() from other ORMs or NoSQL — stop and read the table below.

❌ Do NOT use these (NoSQL / ORM habits)	✅ Use these in PG mode
.where({ field: value })	.match({ field: value }) or .eq("field", value)
.where("field", "ilike", "%v%")	.ilike("field", "%v%")
.orderBy("field", { ascending: false })	.order("field", { ascending: false })
.count()	.select("*", { count: "exact" }) — count is in response
.offset(n)	.range(from, to)
app.uploadFile() (legacy NoSQL upload)	app.storage.from('bucket').upload(key, file)
app.getTempFileURL() (legacy NoSQL URL)	app.storage.from('bucket').createSignedUrl(key, expiresIn)
app.storage.from() (no bucket name)	app.storage.from('bucket') — must pass bucket name

If you find yourself typing .where() or .orderBy() or .count() — stop and use the correct method from the right column.

0. First, confirm this environment actually has PostgreSQL provisioned. Call envQuery(action="info", envId=...) and read the derived EnvInfo.RuntimeBackends block ({ postgresql, nosql, mysql }) along with EnvInfo.RuntimeMode. It is only safe to apply this skill's PG-specific guidance when RuntimeBackends.postgresql === true (equivalently, EnvInfo.PostgreSQL is non-empty AND/OR EnvInfo.Meta contains postgresql=enable).
   • PG mode is a new-environment mode selected when creating a CloudBase environment with PostgreSQL. Do not try to "upgrade" a legacy environment in place; create/select a PG-mode environment instead.
   • If RuntimeBackends.postgresql === false, STOP — this is a legacy NoSQL-only env: switch to cloudbase-document-database-web-sdk for browser data and cloud-storage-web (with app.uploadFile()) for uploads. Do not write app.rdb() code, do not enable RLS, do not create a pgstore bucket here.
   • If both postgresql and nosql are true (the common case in a PG environment), they coexist. Apply this skill to NEW business data the task asks you to put in PG (e.g. articles / role tables explicitly described as PG). Existing NoSQL collections, the bucket reported in EnvInfo.Storages[], and any managePermissions(resourceType="noSqlDatabase") rules continue to govern the legacy NoSQL data — do NOT migrate or rewrite them unless the task explicitly asks.
   • RuntimeBackends.mysql === false is the only hard "do not use" signal: when MySQL is absent, do not use manageMysqlDatabase / queryMysqlDatabase and do not consult the relational-database-mcp-cloudbase skill; those are MySQL-specific and have nothing to do with CloudBase PG.
   • Note: in a PG env, EnvInfo.Storages[] is the legacy NoSQL bucket. It still works for legacy app.uploadFile() flows but is NOT a usable pgstore bucket — never reuse it as the <bucket> segment in app.storage.from('<bucket>').upload('<key>', file).

Creating a PG-mode environment

If step 0 shows RuntimeBackends.postgresql === false and you need PostgreSQL, create a new environment with PG enabled:

• Via MCP: manageEnv(action="create", alias="my-env", packageId="baas_personal", resources=["flexdb","storage","function","postgresql"], confirm="yes") — do not pass region; CreateEnv does not accept it.
• Via CLI: tcb env create --alias my-env --package baas_personal --postgresql --yes
• Via Console: Create environment

1. Inspect the existing app surfaces first: src/lib/backend.*, src/lib/auth.*, src/lib/*service.*, route guards, and the handlers bound to existing forms.

2. Check PG state through MCP: use queryPgDatabase for schema/read-only inspection and managePgDatabase for DDL/DML. Do not switch to MySQL tools. For the complete route map, read references/index.md.

3. Understand PG roles before writing code: Publishable Key maps to anon; a logged-in user's access token maps to authenticated; API Key maps to service_role and bypasses RLS. Never expose API Key / service_role credentials in frontend code. See references/auth-and-rls.md.

4. Use schema management (managePgDatabase) before writing CRUD code. Schema DDL (CREATE / ALTER / DROP / TRUNCATE) must go through the versioned migration workflow — never default to execute for table creation. Then apply GRANT + RLS (via execute or the same migration SQL bundle) before browser access. The minimum SQL bundle is: CREATE TABLE, GRANT SELECT/INSERT/UPDATE/DELETE TO authenticated, GRANT USAGE, SELECT ON SEQUENCE ... TO authenticated when using serial/bigserial, ALTER TABLE ... ENABLE ROW LEVEL SECURITY, and CREATE POLICY ... USING / WITH CHECK. See references/auth-and-rls.md for the full template.

   Default schema-change workflow (local file first, then remote history):

   1. Choose migrationVersion = 14-digit UTC timestamp YYYYMMDDHHMMSS and migrationName = snake_case (e.g. add_users).
   2. Write local file cloudbase/migrations/<migrationVersion>_<migrationName>.sql with the DDL (and optional rollback SQL in comments or a paired file). This path must match CloudBase CLI MIGRATIONS_DIR (tcb db pg migration *). If an older workspace still has root migrations/, move those files into cloudbase/migrations/ before mixed MCP+CLI use.
   3. Optional preview: managePgDatabase(action=planMigration, migrationName=..., migrationVersion=..., sql=...).
   4. Apply: managePgDatabase(action=applyMigration, migrationName=..., migrationVersion=..., sql=..., confirm=true) — reuse the same version/name as the local file. If the local file is missing, MCP auto-writes cloudbase/migrations/<version>_<name>.sql; if an existing file's content differs from sql, apply fails closed (LOCAL_MIGRATION_FILE_MISMATCH) and does not Push. MCP waits for the async task by default (up to 10 minutes, same as CLI); override with taskPollTimeoutMs or set waitForTask=false if the host tool-call timeout is short.
   5. Verify: managePgDatabase(action=listMigrations) and confirm the remote history records the same migrationVersion.
   6. Then write frontend CRUD / RLS checks.

   Out-of-order / backfill versions: Prefer a migrationVersion strictly newer than LatestVersion. If you must apply a version older than Latest (branch merge / cherry-pick), pass includeAll=true on planMigration / applyMigration — same as CLI tcb db pg migration up --include-all. Do not use this for routine work.

   If applyMigration returns MIGRATION_TASK_TIMEOUT or MIGRATION_TASK_PENDING: the task may still be running (large DDL / lock waits). Call describeMigrationTask(taskId=...) first for Status/Phase/Reason, then listMigrations. Do not re-push the same migrationVersion, and do not fall back to execute until the task is terminal and list confirms the version never landed.

   Other migration actions:

   • managePgDatabase(action=migrationDetail, migrationVersion=...) — inspect a single migration
   • managePgDatabase(action=fetchMigration) — pull remote history SQL into cloudbase/migrations/ (CLI tcb db pg migration fetch parity). Optional migrationVersion for one file; omit for full history. Existing local files are skipped unless force=true (overwrite / checksum realign). Prefer this over hand-copying SQL from migrationDetail to avoid checksum drift.
   • managePgDatabase(action=rollbackMigration, lastN=..., confirm=true) — roll back the last N applied migrations
   • managePgDatabase(action=repairMigration, migrationVersion=..., migrationName=..., repairStatus=..., repairReason=...) — repair history records

   execute is for DML and ops SQL, not default DDL: use managePgDatabase(action=execute, confirm=true) for INSERT / UPDATE / DELETE, and for GRANT / CREATE POLICY / storage RLS when those are not part of a migration. If you attempt schema DDL via execute, the tool soft-blocks with DDL_USE_APPLY_MIGRATION unless you explicitly set allowDdlViaExecute=true (escape hatch only).

   🚨 CRITICAL: Inspect table existence and column names before CREATE TABLE. CREATE TABLE IF NOT EXISTS silently skips when the table already exists, even if the column names are wrong. Always call queryPgDatabase(action="sql", sql="SELECT column_name, data_type FROM information_schema.columns WHERE table_name='xxx'") first to check whether the table exists and what exact column names it uses. If the table already exists with mismatched column names (e.g. user_id instead of uid), you must either:

   • ALTER TABLE to add/rename/drop columns (via applyMigration with a new version), or
   • DROP TABLE IF EXISTS ... CASCADE and recreate via applyMigration (only when data loss is acceptable, e.g. disposable/evaluation environments).
   • Do NOT rely on CREATE TABLE IF NOT EXISTS silent skip — it will cause all downstream CRUD queries to fail with wrong field names.
   • After DDL, re-query the schema and compare every column name used by frontend code, insert/update payloads, filters, ordering, and RLS policies.

5. Check username-password auth before coding login:

   • Call queryAppAuth(action="getLoginConfig").
   • If loginMethods.usernamePassword !== true, call manageAppAuth(action="patchLoginStrategy", patch={ usernamePassword: true }).
   • In Web login code, use auth.signInWithPassword({ username, password }) for plain usernames like admin or editor.
   • Do not assume auth.signUp({ username, password }) can directly create username/password users. Confirm queryAppAuth sdkHints and the installed @cloudbase/js-sdk behavior first; if direct username signup is unsupported, implement registration through a backend/management boundary instead of exposing secret keys in the browser.

6. Implement Web auth state with auth.getSession() before writing CRUD:

   • Route guards must check data.session, not auth.getUser() and not deprecated getLoginState().
   • Treat login as successful only when signInWithPassword(...) returns no error and includes data.session.
   • Get the UID for author_id / role rows from data.session.user.id (fall back to sub/uid only after inspecting the actual session object).
   • Do not use auth.getUser() as proof of login; it can return a non-null wrapper or anonymous-looking user data when there is no real username/password session.

7. Implement browser-side business data with the CloudBase JS SDK v3 PostgreSQL API first: app.rdb().from(table). Use the latest @cloudbase/js-sdk when app.rdb is missing (xxx.rdb is not a function means the SDK is too old).

8. Do not manually fetch a CloudBase Auth bearer token from browser code for PG CRUD. In particular, do not call non-canonical helpers such as currentUser.getIdToken() unless you have verified that exact method exists in the installed SDK. Prefer app.rdb() so the SDK carries the active session.

9. Use the official CloudBase PG SQL auth helpers in policies: auth.uid() for JWT sub, auth.role() for anon / authenticated / service_role, auth.jwt() for full claims, and auth.email() when email is needed. Still verify the policy through the real app session before claiming it works:

   • Log in through the real app path.
   • Insert a test row using author_id = session.user.id.
   • Read it back with queryPgDatabase.
   • If INSERT/SELECT fails, inspect the exact RLS error and fix the policy or switch to a server/RPC boundary. Do not leave browser-facing tables with broken RLS.
   • ⚠️ auth.uid() returns text, not uuid. Prefer owner columns as varchar(64) / text. If comparing to a uuid column, use auth.uid()::uuid (only when JWT sub is a valid UUID) or you will get operator does not exist: uuid = text. This differs from Supabase. See references/auth-and-rls.md.
   • ⚠️ Do NOT use current_user or current_setting(...) in RLS policies. current_user in PostgreSQL returns the database role name (e.g. authenticated), NOT the CloudBase auth user ID. Always use auth.uid() for user identity checks. If you are unsure whether the auth helpers are available, run SELECT proname FROM pg_proc WHERE pronamespace = 'auth'::regnamespace to list all available auth.* functions.

10. Use PG HTTP API only as a fallback after reading OpenAPI docs and verifying the auth model in the installed SDK. Do not guess URLs such as /api/v1/rdb/rest; the documented base is https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/<table> and auth is Authorization: Bearer <Publishable Key | access_token | API Key>.

11. Keep cover images in CloudBase Storage. Store only the final file URL or file metadata in PG.

12. Verify both layers before claiming done: project build/typecheck and browser E2E for login/CRUD, then read back rows with queryPgDatabase. When debugging RLS, run SQL as authenticated / anon if the tool supports role simulation; admin/default execution can bypass the user-facing failure.

Exploration Budget

• Optimize for a working user flow before broad research.
• If the task is a Web app with PG-backed CRUD, read references/app-workflow.md and follow that closure path before looking up optional HTTP API details.
• Do not query the same documentation family more than twice for the same question. If the second lookup does not unblock you, inspect the installed SDK surface or the exact runtime error instead.
• Once you choose app.rdb() for browser CRUD, stop researching raw PG HTTP APIs unless app.rdb() is missing or demonstrably fails.
• After a DDL failure, retry SQL at most twice. Then call queryPgDatabase(action="objects") to find the schema-qualified table name, then queryPgDatabase(action="schema", objectName="public.your_table"), read the exact error, and simplify the schema or permission plan.
• Avoid long task-management loops for targeted repairs. Read the active files, execute the minimum platform setup, edit code, and verify.
• File read budget: Do NOT read the same file more than 2 times. If you need to re-read a file after 2 reads, use Grep for targeted search or Read with explicit offset/limit to target specific line ranges. Move on to editing or verifying instead of re-reading.

Data Model Rules

• Use CloudBase Auth / CloudBase PG built-in auth identity as the user source. Do not copy an extra identity table unless the app needs one.

• Keep business roles in PG when the app needs admin/editor behavior, e.g. user_roles with uid, username, and role. The uid value must be the same value the Web session uses as session.user.id, and must match any database policy expression you use.

• Keep content tables in PG, e.g. articles or posts with owner UID columns.

• Prefer snake_case physical columns (author_id, author_name, cover_image, created_at, updated_at) for PG tables. If UI fields are camelCase, map them explicitly at the service boundary.

• Treat the schema returned by queryPgDatabase(action="schema", objectName="public.your_table") as the source of truth. objectName is required and must be schema-qualified; if you do not know it yet, call queryPgDatabase(action="objects") first. If an existing table has authorid/updatedat, either use those exact column names in code or explicitly migrate/drop-recreate the table before writing code that expects author_id/updated_at.

• CREATE TABLE IF NOT EXISTS does not change an existing incompatible schema. In evaluation or disposable environments, prefer a deliberate DROP TABLE IF EXISTS ... CASCADE followed by CREATE TABLE ... when you need a known schema.

• After DDL, query the table schema again and compare every column used by frontend code, insert/update payloads, filters, ordering, and RLS policies.

• Backend permission must exist in the database or server/RPC layer. Hiding buttons in the UI is not enough.

• Do not leave a browser-facing table with RLS enabled and zero policies. PostgreSQL denies user reads/writes by default in that state, so app.rdb().from("articles").insert(...) can fail while the UI only shows a generic save failure. If you enable RLS, create and verify SELECT/INSERT/UPDATE/DELETE policies before testing the app.

• Use CloudBase PG's official SQL auth helpers in policies: auth.uid() (JWT sub, returns text not uuid), auth.role() (anon / authenticated / service_role), auth.jwt() (full claims), and auth.email() when relevant. Prefer owner columns such as owner_id varchar(64) DEFAULT auth.uid() so the database, not the browser, assigns ownership. If an owner column is already uuid, compare with auth.uid()::uuid (only when sub is a valid UUID).

• Standard owner-table template — copy this shape for any user-owned business table:

  CREATE TABLE articles (
    id          BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner_id    TEXT        NOT NULL DEFAULT auth.uid(),
    title       TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
  );
  

  • owner_id is TEXT, not uuid — auth.uid() returns text (e.g. EchhGXFadSANiCSaVim2wQ); declaring it uuid fails at table-create time with a type mismatch.
  • owner_id carries DEFAULT auth.uid() — ownership is decided server-side. App code must not send it; the INSERT policy rejects any forged owner value.

• Seeding demo data: RLS denies anonymous browser writes. Insert demo/seed rows through the management plane — managePgDatabase(action="execute", confirm=true) with an explicit owner_id value (e.g. 'system-demo'); do not rely on DEFAULT auth.uid() for seed rows, and never ship seed INSERTs in frontend code.

• If you need detailed GRANT/RLS rules, read references/rls-patterns.md before writing policies.

• For admin/editor flows, make admin able to operate all rows and editor only rows where owner UID matches the current user.

JS SDK v3 PostgreSQL Patterns

Table name rules (important):

• ✅ db.from("articles") — recommended
• ✅ db.from("public.articles") — also valid (single schema prefix)
• ❌ db.from("public.public.articles") — WRONG, double schema prefix, will fail with PGRST205
• objectName="public.articles" in queryPgDatabase() is the MCP tool format — do NOT copy this into db.from().

Use static imports and one shared app.rdb() client (SDK init reference: webv3-pg/initialization.md):

import cloudbase from "@cloudbase/js-sdk";
const app = cloudbase.init({
  env: import.meta.env.VITE_CLOUDBASE_ENV_ID,
  accessKey: import.meta.env.VITE_PUBLISHABLE_KEY, // publishable key, see auth-web-cloudbase prerequisites
  auth: { detectSessionInUrl: true },
});
export const auth = app.auth;
export const db = app.rdb();

Minimal auth helpers — only use auth.getSession(), never auth.getUser():

async function getActiveSession() {
  const { data, error } = await auth.getSession();
  if (error || !data?.session || data.session.user?.is_anonymous) return null;
  return data.session;
}

Canonical CRUD shapes (copy these exactly):

// READ
const { data, error } = await db.from("articles").select("*");
// CREATE — omit owner_id/author_id when the table defines DEFAULT auth.uid()
const { data, error } = await db.from("articles").insert({ title, status: "draft" });
// UPDATE
await db.from("articles").update({ status }).eq("id", id);
// DELETE
await db.from("articles").delete().eq("id", id);
// RPC
const { data } = await db.rpc("function_name", { id });

Common query helpers: .eq(), .neq(), .gt(), .gte(), .lt(), .lte(), .like(), .ilike(), .in(), .is(), .contains(), .textSearch(), .or(), .not(), .match(), .order(), .limit(), .range(), .single().

Full cookbook (official webv3-pg API — copy these, do not re-derive from .d.ts). Source: webv3-pg/postgresql/fetch.md — fetch / insert / update / delete / upsert / filters / modifiers / rpc share the same path prefix, one page per verb（URL 加 .md 可取 raw markdown 原文）:

// COUNT only — no rows returned, count comes back on the result object
const { count, error } = await db.from("articles").select("*", { count: "exact", head: true });

// Pagination — .range(from, to) is INCLUSIVE on both ends; page 2 of 20 = .range(20, 39)
const { data, error } = await db.from("articles").select("*")
  .order("created_at", { ascending: false }).range(0, 19);

// INSERT and return the inserted row — ⚠️ .select() only returns rows when the
// table has a single auto-increment primary key; otherwise data is empty/null
const { data, error } = await db.from("articles").insert({ title, status: "draft" }).select();

// INSERT many rows at once (array form)
await db.from("articles").insert([{ title: "a" }, { title: "b" }]);

// UPSERT — include the primary key in values; onConflict names the unique-index column(s)
await db.from("articles").upsert({ id: 1, title: "new" }, { onConflict: "id" });

// Join query — PostgREST embedded resources via FK relationship
const { data, error } = await db.from("articles").select(`
  title,
  categories ( name ),
  created_by:users!articles_created_by_fkey ( name ) // multiple FKs to the same table need the constraint name
`);