关系型数据库：PostgreSQL

使用 CloudBase PostgreSQL（PG 模式）设计表结构、通过 JS SDK 或 MCP 工具读写数据、配置权限，并落地 Web 后台管理场景

如何使用

查看如何使用Skill了解详细的使用方法。

测试 Skill

你可以使用以下提示词来测试：

• "帮我在 CloudBase PostgreSQL 中设计用户表并配置权限"
• "在 Web 应用中使用 CloudBase PostgreSQL 的 JS SDK 实现数据增删改查"
• "帮我排查 CloudBase PG 应用的数据库连接和权限配置问题"

使用 AI 开发、对接和管理 PostgreSQL 数据库

安装与查看

如果需要安装全部 CloudBase Skills，可执行：

npx skills add tencentcloudbase/cloudbase-skills

如果只安装当前 Skill，可执行：

npx skills add https://github.com/tencentcloudbase/skills --skill postgresql-development-cloudbase

当前 Skill 在线查看： postgresql-development-cloudbase

---

Skill 规则原文

查看 SKILL.md 原文

## 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=["storage","function","postgresql"], confirm="yes")` — optional `region` (e.g. `region="ap-shanghai"`) selects where the environment is created; it works as the `X-TC-Region` request context, so do **not** put `Region` into the request body. Omit it to use the current session region (site default: `ap-shanghai` domestic, `ap-singapore` intl); if you pass it, repeat it on the confirming call.
> - **Via CLI**: `tcb env create --alias my-env --package baas_personal --postgresql --region ap-shanghai --yes`
> - **Via Console**: [Create environment](https://console.cloud.tencent.com/tcb/env/create)

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:

  ```sql
  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](https://docs.cloudbase.net/api-reference/webv3-pg/initialization.md)):

```ts
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()`:

```ts
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):

```ts
// 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](https://docs.cloudbase.net/api-reference/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 原文）:

```ts
// 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
`);

// RPC — SETOF-returning functions chain .select()/.order()/.limit()/.single()/filters like a query
const { data, error } = await db.rpc("search_articles", { keyword })
  .select("title, published_at").order("published_at", { ascending: false }).limit(5);
const { data: one } = await db.rpc("search_articles", { keyword }).limit(1).single();
const { count } = await db.rpc("search_articles", { keyword }, { count: "exact", head: true });
```

### ⚠️ Critical: PG API is NOT the same as CloudBase NoSQL or other ORMs

CloudBase PG (`app.rdb()`) uses **postgREST-style** query helpers, **NOT** CloudBase NoSQL (`app.database()`) API and **NOT** common ORM conventions. Do NOT use:

| ❌ Wrong (NoSQL / ORM habit) | ✅ Correct (postgREST / PG) |
|-----------------------------|---------------------------|
| `.where({ field: value })` | `.match({ field: value })` 或 `.eq("field", value)` |
| `.where("field", "ilike", "%v%")` | `.ilike("field", "%v%")` |
| `.orderBy("field", { ascending: false })` | `.order("field", { ascending: false })` |
| `.count()` | `.select("*", { count: "exact" })` — 通过 `select` 的 `count` 参数获取总数，返回结果中有 `count` 字段 |
| `.offset(n)` | `.range(from, to)` — 注意 range 是包含两端的分页 |

**Golden rule**: `app.rdb()` 的查询链只使用上方 "Common query helpers" 列出的 helper 方法。如果你习惯写 `.where()` / `.orderBy()` / `.count()`，请立即改用对应的 postgREST 方法。Supabase 的 `@supabase/postgrest-js` 同样不使用这些方法名。

Storage (v3): use `app.storage.from('<bucket>').upload('<key>', file)` — check installed SDK surface before copying:

```ts
const { data } = await app.storage.from('covers').upload(`${file.name}`, file);
```

### Bucket existence is mandatory (Supabase parity)

CloudBase PG storage uses the `pgstore` backend and follows the same model as Supabase Storage: **every upload must target a bucket that already exists**. The browser SDK cannot create one. Before writing any upload code:

1. Confirm a usable pgstore bucket exists for your target prefix (e.g. `covers`). The legacy NoSQL bucket exposed by `DescribeEnvs.Storages[]` (e.g. `6d63-…-1409864723`) is for the old NoSQL backend and does NOT serve pgstore uploads.
2. If no usable bucket exists, create one through the PG storage management surface (PG storage HTTP API / CLI / console / SQL on `storage.buckets` when appropriate). Do not assume traditional-mode storage tools or adding `covers/` as a JS path prefix will create a PG bucket.
3. The bucket name belongs in `from('<bucket>')`; the key passed to `upload(key, file)` is inside that bucket and must **not** repeat the bucket prefix. Correct: `app.storage.from('covers').upload('a.png', file)`. Wrong: `app.storage.from('covers').upload('covers/a.png', file)`.
4. **After creating the bucket, configure RLS on `storage.objects`** via `managePgDatabase(action="execute", confirm=true)`. The default RLS is deny all; without permissive policies the browser receives `STORAGE_PERMISSION_DENIED`. See `references/storage-pg.md` for the full bucket + RLS templates (per-user isolation and public-read buckets), and `cloud-storage-web/SKILL.md` "Post-bucket: storage RLS" section for the exact SQL policies.

Failure-mode cheat sheet (read DevTools network tab on the FAILED `POST .../v1/storages/get-objects-upload-info`):

| `code` returned by `/v1/storages/get-objects-upload-info` | Meaning | Fix |
| -------------------------------------------------------- | ------- | --- |
| `STORAGE_BUCKET_NOT_FOUND` | The bucket in the path does not exist in this PG environment. | Create the bucket via management surface, then retry. |
| `STORAGE_PERMISSION_DENIED` | The bucket exists but RLS on `storage.objects` blocks the upload. | Run `managePgDatabase(action="execute", confirm=true)` to configure storage RLS. See `cloud-storage-web/SKILL.md` "Post-bucket: storage RLS". |
| `INVALID_PARAM` for bucket/key | The SDK/API did not receive a valid bucket/key pair (for example `from()` missing the bucket, or key is empty). | Use `app.storage.from('covers').upload('a.png', file)`; bucket goes in `from()`, key goes in `upload()`. |
| `STORAGE_CONTENT_LENGTH_REQUIRED` | Your code stripped or omitted the `Content-Length` signed header. | Pass `headers: { 'Content-Length': String(file.size) }` to `uploadFile`, or use `app.storage.from('<bucket>').upload('<key>', file)` with a `Blob`/`File` so the SDK fills it in. |

If you see `PUT https://undefined/` and `net::ERR_NAME_NOT_RESOLVED` in DevTools, that is the symptom of one of the three rows above — the upstream metadata response had no `uploadUrl` field, and the SDK fed `undefined` into a follow-up `PUT`. Always inspect the upstream `get-objects-upload-info` response first; do not chase the `https://undefined/` URL itself.

Hard rule: never let an upload error be silently swallowed. If `uploadCoverImage()` rejects, the surrounding `createArticle()` flow MUST reject too — do not insert into PG with a fabricated cover URL, do not show a success toast, and do not retry with a guessed bucket name.

## ExecutePGSql / DDL Troubleshooting

- `ExecutePGSql` / `managePgDatabase(action="execute")` is an admin/control-plane path. Do not expose Tencent Cloud SecretKey or CloudBase API Key in frontend code.
- Execute one SQL statement per call. Split batches explicitly instead of sending semicolon-joined multi-statements.
- Some DDL (`CREATE` / `ALTER` / `DROP` / `GRANT` / `REVOKE` / `TRUNCATE` / `COMMENT`) can fail directly with transient `InternalError`. If that happens, retry once by wrapping the DDL in `DO $$ BEGIN EXECUTE '...'; END $$` and escaping single quotes inside the string.
- When validating permissions, use the user-facing role (`anon` or `authenticated`) when the tool/API supports a role parameter. Default admin execution can hide missing GRANT/RLS policies.

## HTTP API Fallback

- PG HTTP API is in the CloudBase relational database HTTP API family, together with MySQL. In MCP docs/search this appears under `mysqldb`.
- Before writing raw `fetch()` code, query OpenAPI docs: `searchKnowledgeBase(mode="openapi", apiName="mysqldb", query="PostgreSQL fetch insert update rpc")`.
- Do not construct `/api/v1/rdb/rest` or `/api/v1/rdb/rest/rpc` from memory. A guessed path that returns 404 is a hard blocker; switch back to JS SDK v3 or read the OpenAPI contract.
- If environment variables expose `TCB_HTTP_API_BASE_URL` / `VITE_TCB_HTTP_API_BASE_URL`, treat them as the base only. The path, method, headers, and auth model must still come from OpenAPI docs or an existing working helper.

## Frontend Guardrails

Avoid dynamic helper traps:

- Do not write `function getAuth() { return (await import("./backend")).auth; }`; either use a top-level static import or make the function `async`.
- Do not write `typeof import !== "undefined"` in Vite; use `import.meta.env` directly.
- Do not keep editing after Vite reports a transform error. Fix syntax first, rerun build, then test the browser flow.
- Do not spend time reverse-engineering unrelated SDK internals when a documented v3 surface exists. Use the documented `app.rdb()` / `app.storage.from()` APIs first.

## Quick Checks

- PG schema exists and matches the service code.
- Username login is enabled and code uses username APIs, not email APIs.
- Data writes reach CloudBase PG via JS SDK v3 `app.rdb()` or a documented HTTP API path, not local state, mock arrays, or guessed 404 endpoints.
- Browser PG code must not depend on `user.getIdToken()` or invented token helpers. If raw HTTP is unavoidable, first inspect the installed CloudBase Web SDK/auth API and prove the request succeeds with the current user session.
- Editor permission is enforced outside the UI.
- A pgstore bucket that matches the upload path (e.g. `covers`) exists BEFORE any browser upload runs. If it does not, create it via a management surface; the v3 SDK will not create one for you.
- Storage upload returns a usable URL and that URL is persisted with the article. Upload errors must propagate — do not insert an article row with a placeholder cover URL.

## Reference index

All packaged reference files (required for skill lint reachability):

- [index.md](https://cnb.cool/tencent/cloud/cloudbase/CloudBase-AI-Toolkit/-/git/raw/main/config/source/skills/postgresql-development-cloudbase/references/index.md)
- [pg-mode-overview.md](https://cnb.cool/tencent/cloud/cloudbase/CloudBase-AI-Toolkit/-/git/raw/main/config/source/skills/postgresql-development-cloudbase/references/pg-mode-overview.md)
- [auth-and-rls.md](https://cnb.cool/tencent/cloud/cloudbase/CloudBase-AI-Toolkit/-/git/raw/main/config/source/skills/postgresql-development-cloudbase/references/auth-and-rls.md)
- [app-workflow.md](https://cnb.cool/tencent/cloud/cloudbase/CloudBase-AI-Toolkit/-/git/raw/main/config/source/skills/postgresql-development-cloudbase/references/app-workflow.md)
- [storage-pg.md](https://cnb.cool/tencent/cloud/cloudbase/CloudBase-AI-Toolkit/-/git/raw/main/config/source/skills/postgresql-development-cloudbase/references/storage-pg.md)
- [http-api.md](https://cnb.cool/tencent/cloud/cloudbase/CloudBase-AI-Toolkit/-/git/raw/main/config/source/skills/postgresql-development-cloudbase/references/http-api.md)
- [rls-patterns.md](https://cnb.cool/tencent/cloud/cloudbase/CloudBase-AI-Toolkit/-/git/raw/main/config/source/skills/postgresql-development-cloudbase/references/rls-patterns.md)
- [troubleshooting.md](https://cnb.cool/tencent/cloud/cloudbase/CloudBase-AI-Toolkit/-/git/raw/main/config/source/skills/postgresql-development-cloudbase/references/troubleshooting.md)

参考文件

当前 Skill 的详细规则文件（源仓库 CNB 镜像，国内可直连）：

• references/app-workflow.md
• references/auth-and-rls.md
• references/http-api.md
• references/index.md
• references/pg-mode-overview.md
• references/rls-patterns.md
• references/storage-pg.md
• references/troubleshooting.md