PostgreSQL

Read reference data straight from a PostgreSQL database — a reporting replica, a data warehouse, an ERP export schema — into the session context. One read-only SELECT per source; the result becomes a context table or a scalar.

Direction: context only.

1. Create the connection

Settings → Connections → Add Connection, type PostgreSQL:

Field	Value
Connection DSN	postgresql://user@host:5432/dbname?sslmode=require — user, host, port, database, and sslmode in the DSN; no password
Password	The database password, stored encrypted separately from the DSN

Test opens a connection and runs SELECT 1 inside a read-only transaction.

POST /api/integration-connections
{
  "name": "ERP reporting DB",
  "type": "postgres",
  "base_url": "postgresql://reporting@db.example.com:5432/erp?sslmode=require",
  "credentials": "<password>"
}

Use a read-only role

Expecto enforces read-only access twice (below), but a database role with SELECT on the needed views only is the third and strongest layer — create one for Expecto instead of reusing an application user.

2. Declare context sources

{
  "sources": [
    {
      "name": "prev_postings",
      "connection_id": "<connection uuid>",
      "query": {
        "kind": "sql",
        "sql": "SELECT account, SUM(amount) AS amount FROM postings WHERE fund = {{ context.fund_number }} AND period = {{ context.prev_period }} GROUP BY account"
      },
      "produces": { "table": "prev_postings" }
    },
    {
      "name": "exchange_rate",
      "connection_id": "<connection uuid>",
      "query": {
        "kind": "sql",
        "sql": "SELECT rate FROM fx WHERE currency = 'USD' AND period = {{ session.period }}"
      },
      "produces": { "scalar": "exchange_rate" }
    }
  ]
}

The query object has exactly two keys: kind: "sql" and sql.

How the SQL is protected

1. At save time the template is parsed with SQLGlot (PostgreSQL dialect) and must be one statement that is a SELECT or WITH … SELECT. DML, DDL, multiple statements, and CTEs that smuggle an INSERT … RETURNING are rejected with a 422 naming the problem.
2. At fetch time each {{ … }} placeholder becomes a positional bind parameter ($1, $2, …); values are never interpolated into the SQL text. The statement runs inside BEGIN TRANSACTION READ ONLY with a statement_timeout of 15 s. Connecting has a 10 s timeout.

Consequences for writing queries:

• Write placeholders without quotes: period = {{ session.period }}, not '{{ session.period }}' — the value is bound, not pasted.
• A placeholder cannot stand in for an identifier (table or column name) or a keyword; only for a value.
• Cast on the SQL side if the column type differs from the bound text: fund_id = {{ context.fund_number }}::int.

Limits

Limit	Value	When exceeded
Rows per source	10 000	query_error: "Query returned more than 10000 rows; narrow it with a filter"
Statement time	15 s	query_error: "Query exceeded the 15s time limit"
Connect time	10 s	connection_error

What a source produces

• Table — every selected column becomes a column of the context table context._tables.<name> (DuckDB table ctx_<name> inside rules and transformers). Values are converted to JSON-friendly types: numeric and Decimal to numbers, dates and timestamps to ISO strings, UUID to text.
• Scalar — the query must return exactly one column; the first row's value becomes context.<name>. No rows is an empty_result error.

For a required table target (per the project's context schema) an empty result set is an empty_result error; for an optional one it yields an empty table.

3. Schema, test, and fetch

The remaining steps are the same as for every context provider:

• Declare the produced scalar/table in the project's context schema with filled_by: "integration" — see BuiltAPI › Wire it into the context schema.
• Dry-run a source with Test query / POST /api/integration-connections/test-source / MCP test_context_source, passing sample values such as {"context.fund_number": "F-1001"}.
• At session creation the fetch runs in the worker, blocks the pipeline until it completes, and can be re-run with POST /api/sessions/{id}/context/fetch — see BuiltAPI › What happens at session creation.

Troubleshooting

error_kind / message	Cause	Fix
connection_error	Host unreachable, port closed, TLS required but sslmode missing, connect timeout	Check the DSN host/port and sslmode; make sure the database accepts connections from the Expecto egress.
auth_error	Wrong password or role not allowed to log in	Rotate the password in Edit; check pg_hba.conf for the role.
query_error: "query.sql must be a single read-only SELECT"	The template contains DML/DDL or several statements	Keep one SELECT; move writes elsewhere.
query_error: "…produces a scalar; the query must select exactly one column"	A scalar source selects several columns	Select only the value column.
query_error: "…more than 10000 rows"	Unbounded result	Add a WHERE on the period/fund, aggregate, or produce a narrower view.
empty_result	The filter matched nothing and the target is required	Check the placeholder values in the session context; mark the target optional if absence is legitimate.