Skip to content

Using PostgreSQL

Jordi Cabot edited this page Mar 4, 2022 · 5 revisions

Xatkit can make use of a PostgreSQL database instead of the internal mapDB one. This page details how to configure it.

Requirements

  • A working installation of Xatkit (Check Build Xatkit)
  • A working installation of PostgreSQL 14 or greater

Create the PostgreSQL schema to store Xatkit monitoring information

Xatkit expect these two tables to exist in the databse and uses them to store the running bot's data:

CREATE TABLE monitoring_session  (
   id SERIAL PRIMARY KEY, 
   session_uuid VARCHAR UNIQUE,
   bot_id int NOT NULL
);


CREATE TABLE monitoring_entry  (
   id SERIAL PRIMARY KEY,
   session_id INT NOT NULL,
   instant TIMESTAMP DEFAULT NOW() NOT NULL,
   utterance VARCHAR NOT NULL,
   intent VARCHAR,
   origin VARCHAR,
   confidence REAL NOT NULL,
   FOREIGN KEY  (session_id) REFERENCES monitoring_session (id)
);

Notes:

  • The bot_id field in monitoring_session could point to a bot table if you're reusing the same database to keep track of several bots
  • Do not forget to add the corresponding DROP table statements if you're recreating them

You will also need to have at least a database user Xatkit could use to connect and insert and query data in these tables.

For instance, you would create a new user called monitoring as:

CREATE USER monitoring WITH PASSWORD 'testmonitoring';

GRANT SELECT, INSERT, UPDATE, DELETE, USAGE ON ALL TABLES IN SCHEMA public TO monitoring;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO monitoring;

Once this is all set, Xatkit will take of inserting all the monitoring data in these tables, in a fully automated and transparent way.

Xatkit database options

Add the following dependency to your pom.xml file:

<dependency>
    <groupId>com.xatkit</groupId>
    <artifactId>xatkit-logs-postgres</artifactId>
    <version>0.0.1-SNAPSHOT</version>
</dependency>

In order to make the bot work with PostgreSQL you will need to set up ALL of the following options.

OPTION DEFINITION
xatkit.logs.database com.xatkit.core.recognition.RecognitionMonitorPostgreSQL
xatkit.postgresql.url Where to find the database. Example: jdbc:postgresql://localhost:5432/test_monitoring for atest_monitoring db in a postgresql instance listening on port 5432 in our localhost)
xatkit.postgresql.user ID of the user to connect to the database. Example: the monitoring user defined above
xatkit.postgresql.password Password for the user. Example: testmonitoring for the monitoring user
xatkit.postgresql.bot_id Identifier for the bot for which we are storing the monitoring data.
xatkit.recognition.enable_monitoring Allows to enable/disable intent recognition monitoring (default false so set it to true to activate the monitoring)

Analyzing the monitoring data

Standard SQL can be used to retrieve and analyze this monitoring data at will. Nevertheless, the bot itself exposes a more digested version of the data via its own monitoring API.

Clone this wiki locally