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 - Access-pattern design or slow-query work -> `../postgresql-best-practices-cloudbase/SKILL.md` - 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 `queryEnv(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=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`.