postgresql-development-cloudbase

What it does

The skill instructs the agent to work with CloudBase PostgreSQL/PG mode, covering schema setup, DDL/DML via managePgDatabase and execute, migrations workflow, GRANT and RLS policies, and frontend CRUD using the CloudBase JS SDK v3 (app.rdb()). It enforces PG-specific APIs over NoSQL patterns, emphasizes using migration-based schema changes, and prescribes how to handle authentication (user/password, session retrieval) and authorization (auth.uid(), auth.role(), auth.jwt()). It also specifies how to verify table existence before CREATE, how to fetch and inspect schema, and how to validate RLS through inserting and querying with session-derived user IDs. The workflow includes inspecting surfaces, using queryPgDatabase and managePgDatabase for schema state, and using migration tooling for DDL.

How it works

• Verify environment supports PostgreSQL via envQuery(action="info", envId=...).
• Use PG tools: queryPgDatabase for reads, managePgDatabase for DDL/DML; do not use MySQL tools.
• Understand roles: map publishable keys to anon; authenticated tokens to authenticated; API keys to service_role; never expose secret keys in frontend.
• Perform schema changes through the versioned migration workflow: create a local file cloudbase/migrations/<version>_<name>.sql, preview with managePgDatabase(action=planMigration, ...), apply with managePgDatabase(action=applyMigration, ...), then verify with listMigrations.
• Before CREATE TABLE, check existing schema with queryPgDatabase(action="sql", sql="SELECT column_name, data_type FROM information_schema.columns WHERE table_name='xxx'") and adjust with ALTER or DROP/RECREATE via a new migration if mismatches
• Authentication: fetch login config via queryAppAuth(action="getLoginConfig"); enable usernamePassword if needed via manageAppAuth(action="patchLoginStrategy", patch={ usernamePassword: true }). Use auth.signInWithPassword(...) in web login, and rely on auth.getSession() for state in route guards.
• Frontend data access via CloudBase JS SDK v3: app.rdb().from(table); upgrade SDK if app.rdb is missing.
• Do not fetch a Bearer token manually; rely on app.rdb() session handling.
• RLS policies: use auth.uid() and auth.role() helpers; test by inserting with author_id = session.user.id and reading back via queryPgDatabase; adjust policy if necessary.
• HTTP API fallback only after reading OpenAPI and ensuring auth model compatibility; base URL is documented, not guessed.
• Keep cover images in CloudBase Storage; store only final URLs/metadata in PG.
• Validate end-to-end: build/typecheck and browser E2E for login/CRUD, then re-read with queryPgDatabase.

When to use it

• Use when building, debugging, or evaluating CloudBase PostgreSQL / CloudBase PG / PG mode apps, including schema setup, RLS-style permissions, and Web CMS/admin CRUD flows backed by CloudBase PG.

What it can touch

• Tools explicitly named: queryPgDatabase, managePgDatabase, app.rdb(), auth.getSession(), auth.uid(), auth.role(), auth.jwt(), auth.email(), queryAppAuth, manageAppAuth.

Caveats

• Emphasizes that PG mode API differs from NoSQL; avoid legacy NoSQL patterns in code.
• DDL should go through migration workflow; using execute for DDL is blocked unless allowDdlViaExecute=true is set.
• auth.uid() returns text, not uuid; adjust comparisons accordingly.
• Do not use current_user in RLS policies; use auth.uid() for identity checks.
• If environment is not PG-enabled, STOP and switch to NoSQL/document-database or storage flows as appropriate.