> ## Documentation Index
> Fetch the complete documentation index at: https://docs.flowyte.com/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL database (Postgres & MySQL)

> Connect your own Postgres or MySQL database and let the agent read (and optionally write) live records on a call — you choose the exact columns.

Connect your own **PostgreSQL** or **MySQL** database and the agent can look up and update live
records mid-call — check an appointment, confirm a balance, capture a request. Unlike the curated
providers, a database has no pre-built actions: you **discover** its schema and **map** the exact
queries you want as [skills](/concepts/skills). Nothing runs that you didn't author and approve.

<Note>
  Authenticate every request with a secret API key: `Authorization: Bearer flowyte_sk_…`. The `kind`
  is `postgres` or `mysql`.
</Note>

## How it fits together

<Steps>
  <Step title="Create a least-privilege database user">
    Generate the SQL to create a scoped user, then run it on your database — the agent never uses
    your admin credentials.
  </Step>

  <Step title="Test, then connect">
    Validate the credentials, then connect with them. They're encrypted at rest and never echoed back.
  </Step>

  <Step title="Discover the schema and scope it">
    Introspect the database, then optionally block tables/columns the agent must never see.
  </Step>

  <Step title="Bind queries as skills">
    Map a read (or a scoped write) onto a skill — a frozen, parameterized statement the agent calls.
  </Step>
</Steps>

## 1. Generate a scoped database user

`GET /integrations/{kind}/sql/scripts` returns copy-paste SQL to create a **least-privilege**
database user: a read-only role (SELECT only) and, if you need writes, a script scoped to **only**
the tables your write skills touch — never blanket write. If you don't pass a password, a strong one
is generated and returned once.

```bash theme={null}
curl "https://builder.flowyte.com/api/v1/integrations/postgres/sql/scripts?database=appdb&tables=appointments" \
  -H "Authorization: Bearer flowyte_sk_…"
# → { "data": { "readOnly": { … }, "write": { … }, "generatedPassword": "…" } }
```

Run the returned scripts on your database as an admin, then use the scoped user's credentials below.

## 2. Test the connection

`POST /integrations/{kind}/sql/test` runs an ordered set of checks — reachability and TLS,
authentication, reading the schema, a timed read probe, a read-only-session proof, and a live-call
latency verdict — **without storing anything**. It always returns `200` with the check results and
an overall `ok` flag; a host that resolves to a private, loopback, or metadata address is refused.

```bash theme={null}
curl -X POST https://builder.flowyte.com/api/v1/integrations/postgres/sql/test \
  -H "Authorization: Bearer flowyte_sk_…" -H "Content-Type: application/json" \
  -d '{ "credentials": {
        "host": "db.example.com", "port": "5432", "database": "appdb",
        "user": "flowyte_ro", "password": "…", "sslmode": "verify-full"
      } }'
# → { "data": { "ok": true, "checks": [ … ] } }
```

<Warning>
  `sslmode` must be `require`, `verify-ca`, or `verify-full` — `disable` is refused at the connect
  boundary. Prefer `verify-full` and supply `ca_pem` when your database uses a private CA.
</Warning>

## 3. Connect

Pass the same credentials to the generic connect endpoint. The `kind` is `postgres` or `mysql`.

```bash theme={null}
curl -X POST https://builder.flowyte.com/api/v1/integrations/postgres/connect \
  -H "Authorization: Bearer flowyte_sk_…" -H "Content-Type: application/json" \
  -d '{ "credentials": {
        "host": "db.example.com", "port": "5432", "database": "appdb",
        "user": "flowyte_ro", "password": "…", "sslmode": "verify-full"
      } }'
# → { "data": { "status": "connected" } }
```

## 4. Discover the schema and scope it

Introspect the database into a normalized schema, then read it (or search it) to find what to map:

```bash theme={null}
curl -X POST https://builder.flowyte.com/api/v1/integrations/postgres/discover \
  -H "Authorization: Bearer flowyte_sk_…"

curl "https://builder.flowyte.com/api/v1/integrations/postgres/search?q=appointment%20status" \
  -H "Authorization: Bearer flowyte_sk_…"
```

Set **data scoping** to keep sensitive tables and columns out of reach — they disappear from the
schema browser and are refused at bind time. Scoping is a privacy control you set per connection and
it survives re-discovery.

```bash theme={null}
curl -X PATCH https://builder.flowyte.com/api/v1/integrations/postgres/scoping \
  -H "Authorization: Bearer flowyte_sk_…" -H "Content-Type: application/json" \
  -d '{ "blockedTables": ["payments"], "blockedColumns": { "customers": ["ssn", "card_last4"] } }'
```

## 5. Bind a query as a skill

Map an operation onto a skill with [a binding](/guides/map-integration-fields): a **read** filters on
one indexed column; a **write** is a single-row insert or an update-by-unique-key. The binding
compiles to a frozen, parameterized statement — the agent supplies the parameters and never sees raw
SQL or the full schema. Writes land disabled for review, and columns you scoped out are rejected.

```bash theme={null}
curl -X POST https://builder.flowyte.com/api/v1/agents/AGENT_ID/integrations/postgres/bindings \
  -H "Authorization: Bearer flowyte_sk_…" -H "Content-Type: application/json" \
  -d '{
        "toolName": "Look up an appointment",
        "operation": "appointments.read",
        "inputs": [ { "param": "caller_phone", "arg": "phone", "required": true } ],
        "projection": [
          { "path": ["appointment", "starts_at"], "leaf": "appointment_time" },
          { "path": ["appointment", "status"],    "leaf": "appointment_status" }
        ],
        "enabled": true
      }'
```

Prefer describing the goal instead? `POST /agents/{id}/integrations/postgres/bindings/auto` takes a
plain-language goal and the AI assistant proposes the binding for you to review.

## In the API

| Action                   | Endpoint                                                              | Scope                                      |
| ------------------------ | --------------------------------------------------------------------- | ------------------------------------------ |
| Generate setup scripts   | `GET /integrations/{kind}/sql/scripts`                                | `integrations:read`                        |
| Test a connection        | `POST /integrations/{kind}/sql/test`                                  | `integrations:write`                       |
| Connect                  | `POST /integrations/{kind}/connect`                                   | `integrations:write`                       |
| Discover the schema      | `POST /integrations/{kind}/discover`                                  | `integrations:write`                       |
| Search / read the schema | `GET /integrations/{kind}/search` · `GET /integrations/{kind}/schema` | `integrations:read`                        |
| Get / set data scoping   | `GET` · `PATCH /integrations/{kind}/scoping`                          | `integrations:read` · `integrations:write` |
| Bind a query as a skill  | `POST /agents/{agentId}/integrations/{kind}/bindings`                 | `skills:write`                             |
| Disconnect               | `DELETE /integrations/{kind}`                                         | `integrations:write`                       |

<Warning>
  Binding edits the **draft**. [Publish](/concepts/agents) so live callers get real data.
</Warning>

Full walkthrough: [Connect a SQL database](/guides/connect-sql-database).