postgresql-best-practices-cloudbase

CloudBase PostgreSQL Best Practices

This skill turns a working CloudBase PG implementation into a reviewable access design. It makes query paths, indexes, row authorization, and launch capacity explicit before code is considered complete.

Sibling skills (local only)

Sibling CloudBase skills ship beside this skill. Use local relative paths such as ../postgresql-development-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.

When to use

• A feature needs a new or changed access pattern.
• Application code performs repeated, serial, or high-frequency PG queries.
• A SQL query, RPC, endpoint, or polling path is slow.
• A high-traffic launch needs a database readiness review.

Do NOT use for

• PG environment setup, app.rdb() syntax, migrations, GRANTs, or base RLS correctness: use ../postgresql-development-cloudbase/SKILL.md.
• Login provider or UI implementation: use the matching auth skill.
• Confirmed NoSQL collection work: use the document database skill.
• Platform alarm diagnosis or slow-log collection: use ../ops-inspector/SKILL.md.

Workflow

1. Inventory access paths

List every changed endpoint, job, RPC, or UI flow and its reads and writes. Record filters, joins, sort keys, expected cardinality, and request frequency.

Complete when: every changed path has a concrete query inventory; unknown volume or peak concurrency is marked as an assumption for the user.

2. Design schema, indexes, and row authorization together

Derive columns and indexes from the query inventory. Every frequent filter, join, and stable ordering path must have a deliberate index decision. For user-owned rows, use the role as the policy gate and identity as the row predicate:

CREATE POLICY orders_select_own ON public.orders
  FOR SELECT TO authenticated
  USING ((SELECT auth.uid()) = owner_id);