# 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=<n>` — rows to return.
- `offset=<n>` — 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=<cols>,<relation>(<cols>)` — `<relation>` 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, `<relation>=not.is.null` on a plain embed performs the same inner-join semantics (`actors=not.is.null` ≡ `actors!inner(*)`). Use whichever reads better.

`<relation>=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 `!<fk_name_or_column>`:

```
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>`: 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 `<type>/<id>`:**
1. Fetch requirements: `GET /segmentations?segmentableType=eq.<type>&segmentableId=eq.<id>`
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.<type>&segmentableId=eq.<id>`
