---
name: postgrest-discovery
description: Discover PostgREST entities from the OpenAPI spec (cached locally), generate typed client code, and help users query the API
metadata:
  category: Implementation
---

# PostgREST API Discovery

Discover available entities from the PostgREST OpenAPI spec, generate a typed client, and help users build queries.

## Step 1: Fetch the OpenAPI spec

**This step runs before every other postgrest action in this skill. Never skip it.**

Make sure `bun run dev` + `vercel dev` are running on port 3000. Then fetch the spec through the local proxy:

```bash
mkdir -p .cache
curl -sS -w "%{http_code}\\n" -o .cache/openapi.json http://localhost:3000/api/postgrest/
```

Validate:

```bash
head -c 1 .cache/openapi.json | grep -q '{' && grep -q '"paths"' .cache/openapi.json && echo "OK"
```

If the curl returns anything other than `200`, or the file is not valid OpenAPI JSON:

- Delete the bad cache: `rm -f .cache/openapi.json`
- Tell the user:

  > The PostgREST proxy returned an error. This usually means one of the earlier setup steps wasn't completed yet:
  >
  > 1. **`/add-login`** — sets up authentication and writes credentials to `.env.local`
  > 2. **`/connect-to-humand-api`** — scaffolds the PostgREST client and proxy routes
  >
  > Want me to run them now?

- If the user confirms, invoke `/add-login` then `/connect-to-humand-api`, then re-run this step.
- **Do not proceed with stale or missing spec data.**

**Cache:** if `.cache/openapi.json` is already valid from this session, reuse it. Pass `--refresh` to force a fresh fetch.

## Step 2: Parse entities from the spec

The OpenAPI spec from PostgREST has this structure:

```json
{
  "paths": {
    "/users": { "get": { ... } },
    "/departments": { "get": { ... } }
  },
  "definitions": {
    "users": {
      "properties": {
        "id": { "format": "integer", "type": "integer" },
        "firstName": { "format": "character varying", "type": "string" }
      }
    }
  }
}
```

For each key in `paths`:
1. Extract the entity name (strip leading `/`, e.g., `/users` -> `users`)
2. Find the matching definition in `definitions`
3. For each property in the definition, map the type:
   - `integer` or format `bigint` -> `number`
   - `string` with format `character varying` or `text` -> `string`
   - `boolean` -> `boolean`
   - format `timestamp with time zone` or `timestamp without time zone` -> `string`
   - format `jsonb` or `json` -> `unknown`
   - Any other -> `string`
4. Note the primary key (usually `id` with type `integer`)

## Step 3: Check and update src/services/postgrest.ts

Read `src/services/postgrest.ts` and look for the merge markers:
- `// === Generated entities (managed by postgrest-discovery skill) ===`
- `// === Exported client (updated when entities change) ===`

**If markers are missing:** Add them. Place the generated entities marker after the `discover()` function. Place the exported client marker before the `export const postgrest` line.

**Compare discovered entities to what's in the file.** For each entity from the spec, check if there's already a `// --- Entity: {Name} ---` section. If any entity is missing, regenerate the entire section between the two markers.

### Generating entity code

For each entity (e.g., path `/users`, definition `users`):

1. Convert entity name to PascalCase for the interface (e.g., `users` -> `User`, `department_roles` -> `DepartmentRole`)
2. Generate an exported interface with all properties and mapped types
3. Generate a list function: `get{PluralName}(params?)` returning `PostgrestListResult<{Name}>`
4. Generate a singular function: `get{Name}(id)` returning `{Name}`

Example output for entity `users`:

```ts
// --- Entity: Users ---

export interface User {
    id: number;
    employeeInternalId: string;
    email: string;
    firstName: string;
    lastName: string;
    status: string;
    instanceId: number;
}

function getUsers(params?: Record<string, string>): Promise<PostgrestListResult<User>> {
    return get<User>('/users', params);
}

function getUser(id: number): Promise<User> {
    return getOne<User>('/users', { id: \\`eq.\\\${id}\\` });
}
```

**Update the exported client object** to include all entity functions plus the core `get`, `getOne`, and `discover`:

```ts
// === Exported client (updated when entities change) ===

export const postgrest = {
    get,
    getOne,
    discover,
    getUsers,
    getUser,
    // ... all other entity functions
};
```

## Step 4: Present findings

Tell the user what's available. Example format:

> **PostgREST API — Available entities:**
>
> - **users** — id (number), employeeInternalId (string), email (string), firstName (string), lastName (string), status (string), instanceId (number)
>
> The typed client at `src/services/postgrest.ts` has been updated with functions for all entities.
> Use `postgrest.getUsers()`, `postgrest.getUser(id)`, etc.

Then proceed to help with whatever the user originally asked.

## PostgREST Query Reference

For filtering operators, ordering, pagination, column selection, resource embedding, and the segmentation system → see [../../references/POSTGREST.md](../../references/POSTGREST.md).

## Rules

- ALWAYS run Step 1 before any other postgrest action in this skill. No exceptions.
- ALWAYS prefer PostgREST resource embedding (joins via `select=parent(child)`) when a request needs data from more than one entity. NEVER hydrate rows one-by-one with per-row follow-up queries — that's an N+1 pattern and it kills performance and multiplies round-trips.
- ALWAYS use `src/services/postgrest.ts` for API calls. NEVER call external URLs directly.
- When building features or writing application code, NEVER ask for API keys or credentials. Auth is automatic via the BFF proxy.
- NEVER install auth libraries. Auth is handled by the proxy.
- NEVER modify `api/postgrest/[...path].ts`.
- This API is READ-ONLY. Only GET requests are supported.
- All API calls go through `/api/postgrest/*`.
