# PostgREST Reference for projects scaffolded with `humand-create-app --humand-api`. Covers the proxy setup, pagination, filtering, column selection, resource embedding, and the segmentation system. --- ## Integration - Proxy: `api/postgrest/[...path].ts` — Vercel Edge, read-only. DO NOT modify. - Client: `src/services/postgrest.ts` — `postgrest.get()`, `postgrest.getOne()`, `postgrest.discover()`. - Skill: `/postgrest-discovery` — **always runs first** when any feature needs PostgREST data. It checks `.env.local` for the M2M credentials, fetches the OpenAPI spec through the proxy, and only then exposes entities. - All PostgREST calls go through `/api/postgrest/*` — auth is automatic via the proxy. - M2M credentials: only `HUMAND_CLIENT_ID` and `HUMAND_CLIENT_SECRET` come from the user's M2M client and go into `.env.local`. The URL defaults (`HUMAND_API_URL=https://api-prod.humand.co` for the Humand main API / Janus, `POSTGREST_BASE_URL=https://views-cx.humand.co` for PostgREST views) are hardcoded and written by the CLI — never prompt the user for them. If the two M2M credentials are missing, create the client at the appropriate landing: Revenue → https://cx-huckathon-landing.vercel.app/build/revenue · Mejora interna → https://cx-huckathon-landing.vercel.app/build/mejora-interna. --- ## Pagination PostgREST uses offset-based pagination via query params (the cursor rule in the Pagination section applies to your own API endpoints, not to PostgREST views). - `limit=` — rows to return. - `offset=` — rows to skip. - To get the total row count, send the request header `Prefer: count=planned`. Use `planned` (uses DB statistics, fast) unless you need an exact count — `exact` does a full table scan. - The response always includes a `Content-Range` header: `0-14/47` means rows 0–14 were returned out of 47 total. ``` GET /api/postgrest/employees?limit=20&offset=40 Prefer: count=planned → Content-Range: 40-59/312 ``` --- ## Filtering Syntax: `?column=operator.value`. Multiple params are AND-ed together. | Operator | Meaning | Example | |---|---|---| | `eq` | equal | `?status=eq.active` | | `neq` | not equal | `?role=neq.admin` | | `gt` / `gte` | greater than / or equal | `?age=gte.18` | | `lt` / `lte` | less than / or equal | `?salary=lt.50000` | | `like` | SQL LIKE (case-sensitive, `*` = wildcard) | `?name=like.Ju*` | | `ilike` | SQL ILIKE (case-insensitive) | `?email=ilike.*@gmail.com` | | `in` | value in list | `?id=in.(1,2,3)` | | `is` | null / boolean check | `?deleted_at=is.null` | | `not` | negate any operator | `?name=not.eq.admin` | OR logic — wrap in `or=(...)`: ``` ?or=(status.eq.active,status.eq.pending) ``` **Gotcha:** reserved characters (`,` `.` `:` `(` `)`) inside values must be percent-encoded or double-quoted: `?name=eq."O'Brien"`. --- ## Column Selection Use `select=col1,col2` to return only the columns you need. Never use `select=*` in production — always be explicit to reduce payload size and prevent leaking sensitive columns. ``` GET /api/postgrest/employees?select=id,name,department,salary ``` You can alias columns in the response: ``` ?select=employee_id:id,full_name:name ``` Combine with filters and pagination freely: ``` GET /api/postgrest/employees?select=id,name,salary&department=eq.engineering&limit=20&offset=0 Prefer: count=planned ``` --- ## Resource Embedding (joins) **Hard rule:** when a request needs data from more than one entity, ALWAYS use resource embedding (`select=parent(child)`). NEVER issue a second round-trip to hydrate related rows — that pattern is **N+1** and it gets worse linearly as the top-level response grows. Docs: https://docs.postgrest.org/en/v12/references/api/resource_embedding.html ### Syntax `select=,()` — `` is the name of a related table/view (or a foreign-key column). PostgREST resolves the relationship from the schema's foreign keys. ``` GET /api/postgrest/employees?select=id,name,department(id,name) ``` Works for all cardinalities: | Relationship | Example | |---|---| | Many-to-one (FK on parent) | `/employees?select=name,department(name)` | | One-to-one (unique FK) | `/employees?select=name,profile(avatar_url)` | | One-to-many (FK on child) | `/departments?select=name,employees(id,name)` | | Many-to-many (via junction table) | `/actors?select=name,films(title)` | ### Nested embedding Chain embeds arbitrarily deep — each relation keeps its own `select`: ``` GET /api/postgrest/departments?select=name,employees(name,role(title)) ``` ### Aliasing embedded resources and columns Rename an embed or a column in the response: ``` GET /api/postgrest/films?select=title,director:directors(id,last_name:name) ``` Use an alias when the foreign-key name is unclear, when you want client-side naming, or when you need the same relation twice with different filters. ### Filtering embedded resources Filters on an embedded relation use dotted paths with the relation name (or alias) as the prefix: ``` GET /api/postgrest/departments?select=name,employees(name,role)&employees.role=eq.manager ``` Embedded filters do NOT remove the parent row — by default embeds are a LEFT JOIN, so departments with no matching manager still come back with `employees: []`. ### Top-level filtering with `!inner` To drop parents that have no matching children, mark the embed `!inner`: ``` GET /api/postgrest/films?select=title,actors!inner(first_name,last_name)&actors.first_name=eq.Jehanne ``` Equivalently, `=not.is.null` on a plain embed performs the same inner-join semantics (`actors=not.is.null` ≡ `actors!inner(*)`). Use whichever reads better. `=is.null` performs an anti-join — "only parents with no children": ``` GET /api/postgrest/employees?select=name,manager_assignments()&manager_assignments=is.null ``` ### Disambiguating multiple foreign keys When two FKs point at the same table (e.g. `orders.billing_address_id` and `orders.shipping_address_id`), name the FK explicitly with `!`: ``` GET /api/postgrest/addresses?select=name,billing_orders:orders!billing_address_id(name),shipping_orders:orders!shipping_address_id(name) ``` If PostgREST returns HTTP 300 ("multiple relationships found"), that's the error — pick one via the `!hint` form and re-send. ### Combining with pagination and counts `limit`, `offset`, and `Prefer: count=planned` still apply to the **top-level** resource. To page an embedded collection, use its dotted filter: `employees.limit=5`, `employees.offset=10`. The top-level `Content-Range` header counts top-level rows only — embedded collections are not paginated globally. ### Rules for this project - When the user request spans more than one entity (e.g. "list employees with their department", "orders and their customer"), ALWAYS use embedding in the `select` param. NEVER make a second round-trip to fetch related rows — that is an **N+1** query pattern. - Pick `!inner` or `=not.is.null` when the caller's intent is "only rows that have the related data". Default (LEFT JOIN) otherwise. - Add an alias when a column or relation name would look ugly on the frontend or when disambiguating multiple FKs. - If you ever find yourself writing a `useEffect` / `forEach` / `map` that fires a per-row PostgREST query, STOP — that's N+1. Rewrite the top-level call with embedding instead. --- ## Segmentation System Controls which users can see which content entities (posts, articles, forms, etc.). **Tables:** - `segmentations` — polymorphic. `segmentableType='user'`: user belongs to group+item. `segmentableType=`: content requires that group+item to be visible. - `user_segmentation_items` — pre-computed snapshot of a user's memberships. One row per user; `segmentations text[]` of `"groupId_itemId"` strings (e.g. `["3_5","4_7"]`). **Always use this for user lookups — never aggregate `Segmentations` directly.** - `user_segmentations` — direct assignment of a user to a specific entity, bypassing group matching. Not used for `post`. **Valid `segmentableType` values:** `aiChatbot`, `article`, `course`, `file`, `form`, `knowledge_library`, `onboarding_task`, `post`, `sports_pool_competition`, `ticket_topic`, `user`, `user_gathering_criterion`, `widget`, `work_environment_survey` **Matching logic — who can see entity `/`:** 1. Fetch requirements: `GET /segmentations?segmentableType=eq.&segmentableId=eq.` 2. Group rows by `groupId` → build `"groupId_itemId"` strings per group 3. Query with `ov` (Postgres `&&`, array overlap). Multiple params on the same column are ANDed: ``` GET /user_segmentation_items?segmentations=ov.{3_5,3_12}&segmentations=ov.{4_7} ``` Result: users where **(has 3_5 OR 3_12) AND (has 4_7)** — OR within a group, AND across groups. 4. For non-`post` types, union with direct assignments: `GET /user_segmentations?segmentableType=eq.&segmentableId=eq.`