PostgreSQL Integration
Relevant source files
The following files were used as context for generating this wiki page:
The PostgreSQL integration for Quix allows the AI agent to connect to and query a PostgreSQL database directly from Slack. Users can interact with their database using natural language, enabling them to retrieve information, get table schemas, and list tables without writing SQL queries manually. The connection is established through a secure configuration modal in Slack, and the credentials are stored encrypted in the database.
This integration is defined as a non-OAuth-based connection, meaning it relies on manually entered credentials rather than an OAuth2 flow.
Sources: src/lib/constants.ts:114-124, README.md:1-3
Configuration
The PostgreSQL integration is configured via the Quix App Home in Slack. An admin user can add or edit the connection details, which are then persisted in the Quix database.
Sources: src/slack/app_home.service.ts:166-184
Configuration Flow
The process of configuring the PostgreSQL connection is initiated from the Slack App Home UI. An admin can either connect a new Postgres instance or edit an existing one. This action triggers a modal where the database credentials are provided.
The diagram below illustrates the configuration flow from the user's perspective.
Configuration Modal
When a user initiates the connection, a Slack modal is presented to collect the necessary database details.
Sources: src/slack/views/modals.ts:13-102
The following table details the fields in the configuration modal:
| Field | Block ID | Action ID | Description |
|---|---|---|---|
| Host | postgres_host | postgres_host | The hostname or IP address of the PostgreSQL server (e.g., localhost or db.example.com). |
| Port | postgres_port | postgres_port | The port number the PostgreSQL server is listening on. Defaults to 5432. |
| Database | postgres_database | postgres_database | The name of the database to connect to (e.g., mydb). |
| Username | postgres_username | postgres_user | The username for authenticating with the database. |
| Password | postgres_password | postgres_password | The password for the specified user. |
| SSL | postgres_ssl | postgres_ssl | A checkbox to enable or disable SSL for the connection. |
| Default Prompt | postgres_default_prompt | postgres_default_prompt | A custom instruction for the AI agent on how to behave when interacting with this Postgres database. |
Sources: src/slack/views/modals.ts:13-95
Data Model
The PostgreSQL connection details are stored in the postgres_configs table, which is represented by the PostgresConfig Sequelize model. This model has a direct relationship with the SlackWorkspace model.
Sources: src/database/models/postgres-config.model.ts
Entity Relationship Diagram
This diagram shows the relationship between the slack_workspaces and postgres_configs tables. Each Slack workspace can have at most one PostgreSQL configuration.
PostgresConfig Model Fields
| Field | Type | Constraints | Description |
|---|---|---|---|
id | UUID | Primary Key, Not Null | Unique identifier for the configuration record. |
team_id | STRING | Foreign Key, Not Null | References the team_id in the slack_workspaces table. |
host | STRING | Not Null | The database host. |
port | INTEGER | Not Null | The database port. |
user | STRING | Not Null | The username for the database connection. |
password | TEXT | Not Null | The encrypted password for the database connection. |
database | STRING | Not Null | The name of the database. |
ssl | BOOLEAN | Not Null, Default: false | Whether to use SSL for the connection. |
default_prompt | TEXT | Nullable | A custom prompt to guide the AI agent's interactions. |
created_at | DATE | Not Null | Timestamp of when the record was created. |
updated_at | DATE | Not Null | Timestamp of the last update. |
Sources: src/database/models/postgres-config.model.ts:16-72
Agent Tools
The integration provides the AI agent with a set of tools to interact with the configured PostgreSQL database. These tools are defined in the @clearfeed-ai/quix-postgres-agent package.
Sources: agent-packages/packages/postgres/src/tools.ts:6-40
Tool Summary
| Tool Name | Description | Parameters |
|---|---|---|
get_table_schema | Get the schema of a table. | tableName (string): The name of the table to get the schema of. |
list_tables | List all tables in the database. | None. |
query_database | Query the database with a SQL query. | query (string): The SQL query to execute. |
Sources: agent-packages/packages/postgres/src/tools.ts:9-39
Tool Usage Flow
When a user asks a question related to the database, the agent can use a combination of these tools to formulate an answer. For example, to answer "how many users are in the users table?", the agent might first list tables to confirm users exists, then get its schema, and finally construct and execute a SELECT COUNT(*) query.
Prompts
The integration utilizes prompts to guide the AI agent's behavior.
- Tool Selection Prompt: A general instruction is provided to the agent:
"You can use this tool to query the database. Always hide sensitive information when querying the database."This ensures the agent understands the tool's purpose and security considerations.
Sources: agent-packages/packages/postgres/src/tools.ts:44-47 - Default Prompt: An optional, user-configurable prompt set in the configuration modal. This allows admins to provide more specific, instance-level instructions to the agent.
Sources: src/slack/views/modals.ts:81-94
Summary
The PostgreSQL integration is a powerful feature that bridges the gap between Slack and a user's database. Through a secure and straightforward configuration process, it empowers the Quix AI agent with tools to inspect schemas, list tables, and execute SQL queries based on natural language commands. This allows for quick data retrieval and analysis directly within the collaborative environment of Slack.