Postgres adapter

The Postgres adapter works with every Postgres-compatible database:

• Vanilla Postgres (self-hosted)
• Neon, Crunchy Bridge, Render, Supabase (via this adapter, or the Supabase adapter)
• AWS RDS Postgres, Aurora Postgres
• Google Cloud SQL Postgres
• Azure Database for Postgres
• Timescale, YugabyteDB, CockroachDB (partial — no window functions used)

When to use it

Use the Postgres adapter when you want low-latency, connection-pooled access to structured data. It’s the fastest and most featureful adapter.

Use the Supabase adapter instead if you want to use your Supabase anon/service key via PostgREST.

Config shape

{
  "type": "postgres",
  "config": {
    "connectionString": "postgresql://readonly:pass@host:5432/dbname",
    "ssl": true
  },
  "collections": {
    "properties": {
      "source": "listings",
      "searchable_fields": ["address", "city", "description"],
      "filterable_fields": ["beds", "baths", "price", "city", "amenities"],
      "display_fields": ["id", "address", "city", "beds", "baths", "price"],
      "max_limit": 10,
      "description": "Rental listings in LA available in the next 30 days"
    }
  }
}

Alternative — discrete fields:

{
  "type": "postgres",
  "config": {
    "host": "db.example.com",
    "port": 5432,
    "database": "production",
    "user": "spelo_readonly",
    "password": "${POSTGRES_PASSWORD}",
    "ssl": true
  }
}

Setup

1. Create a read-only user

   In your Postgres:

   CREATE USER spelo_readonly WITH PASSWORD 'strong-random-password';
   GRANT CONNECT ON DATABASE your_database TO spelo_readonly;
   GRANT USAGE ON SCHEMA public TO spelo_readonly;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO spelo_readonly;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public
     GRANT SELECT ON TABLES TO spelo_readonly;

   Caution

   If you scope to specific tables (recommended), replace ON ALL TABLES with ON TABLE listings, categories (etc.). Never grant on pg_catalog or system schemas.

2. Allow Spelo’s IPs (if your DB sits behind a firewall)

   Spelo’s egress IPs:

   • 44.198.24.X/29 (us-east, primary)
   • 52.53.72.X/29 (us-west)

   See spelo.ai/security/ips for the live list.

3. Enable TLS (if not already)

   Your connectionString should use ?sslmode=require. Set "ssl": true in the config. For self-signed certs, see the SSL / self-signed section below.

4. Paste the connection string in the dashboard

   Dashboard → Data → Postgres → paste → Test connection.

5. Map collections

   For each table you want searchable, add a collection. Example:

   Collection name: properties
   Source table: listings
   Searchable fields: address, city, description
   Filterable fields: beds, baths, price, city, amenities
   Display fields: id, address, city, beds, baths, price

6. Save

Field type support

Postgres type	Filter operators	Notes
int, bigint, numeric, real, double precision	eq, neq, gt, gte, lt, lte, in
text, varchar, char	eq, neq, contains, in	contains is ILIKE
boolean	eq, neq
date, timestamp, timestamptz	eq, neq, gt, gte, lt, lte	Pass ISO 8601 strings
text[], int[]	contains, in	contains uses @>
jsonb	contains	Shallow match
enum, custom types	eq, neq, in	Cast at query time

Free-text search

If a collection has searchable_fields, a voice query like “anywhere with a pool” becomes ILIKE '%pool%' across each listed column, OR’d together. For larger catalogs, set up Postgres full-text search on your side and use contains with tsquery in your column.

SSL / self-signed

If your DB uses a self-signed cert:

{
  "type": "postgres",
  "config": {
    "connectionString": "postgresql://...",
    "ssl": true
  }
}

The adapter passes rejectUnauthorized: false when ssl: true. This is a trade-off; if you want cert pinning, email support.

Security notes

• The adapter only emits SELECT statements. INSERT, UPDATE, DELETE, TRUNCATE, DROP, CREATE are impossible.
• Table and column identifiers are validated against ^[A-Za-z_][A-Za-z0-9_]*$ — anything else throws. No quoted/weird identifiers.
• Filter values are always parameterized ($1, $2). Never string-interpolated.
• max_limit caps at 10. Higher limits are rejected.

Performance tips

• Index your filter and search columns. CREATE INDEX ON listings (city, price); makes typical queries instant.
• Use tsvector + GIN for real full-text. Set searchable_fields: ["search_vector"] with a to_tsvector expression index if you have lots of text.
• Partial indexes for common filters, e.g. CREATE INDEX ON listings (city) WHERE active = true;.
• Keep display_fields narrow. Don’t return 50 columns when the AI only needs 6.

Troubleshooting

• connection refused → DB not reachable from Spelo’s egress. Whitelist our IPs.
• password authentication failed → typo in the connection string, or the user doesn’t exist.
• permission denied for table X → you forgot to GRANT SELECT on that table.
• relation "X" does not exist → source in the collection config doesn’t match your actual table name. Case-sensitive.
• Queries time out → missing index. The adapter times out at 10 seconds; fix the slow query or add an index.

More: Database connection errors.

Connect a different data source

Overview MySQL MS SQL MongoDB Supabase Firebase OAuth setup Airtable Google Sheets Shopify WooCommerce REST API Webhook TS adapter Schema introspection