Train Tracking RAG Chatbot

On going tests on the chatbot

Table of content

1. Overview
2. Technical issues
3. Start development server
4. Features
5. Process flowchart
6. Models history management
7. Database schema/definition
8. Topics

Overview

The Train Tracking RAG Chatbot is an AI-powered chatbot that helps users find train schedules, check delays, and get station details. It uses machine learning to turn user questions into database searches and provide accurate answers.

Technical issues

1. Cloudflare worker AI API error undefined: undefined (suddenly fixed by Cloudflare).
2. Unstable Text-to-SQL model response.
3. Unstable Data-to-Text model response (Improved by add data simulating function + Arabic systemPrompt).
4. Errors in train schedule database data (Fixed).

Start development server

1. Start the wrangler worker development server.

   npm run dev

2. Start the terminal based chat to communicate with the server.

   cd chat-with-ai
   npm run chat

3. Preview local project with cloudflare tunnel (optional).

   cloudflared tunnel --url http://localhost:8787
   curl "https://api.telegram.org/bot{bot_token}/setWebhook?url={tunnal_link}/webhook"

Features

1. Reply to user inquiries
2. Follow up on delays
3. Automatic tracking of the most used trains
4. Share your current location feature (To improve delays club)

Process flowchart

Simply: Natural language → SQL → Database → Natural language response Flowchart:

Models history management

Database schema/definition

1. Table: schedule

CREATE TABLE public.schedule (
    id bigint NOT NULL,
    train_id smallint NULL,
    station_id smallint NULL,
    stop_order smallint NULL,
    arrival_time time WITHOUT time zone NULL,
    departure_time time WITHOUT time zone NULL,
    CONSTRAINT schedule_pkey PRIMARY KEY (id),
    CONSTRAINT schedule_station_id_fkey FOREIGN KEY (station_id) REFERENCES stations (id),
    CONSTRAINT schedule_train_id_fkey FOREIGN KEY (train_id) REFERENCES trains (id)
)
TABLESPACE pg_default;

2. Table: stations

CREATE TABLE public.stations (
    id smallint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    en_name text NOT NULL,
    ar_name text NOT NULL,
    latitude double precision NULL,
    longitude double precision NULL,
    CONSTRAINT stations_pkey PRIMARY KEY (id),
    CONSTRAINT stations_ar_name_key UNIQUE (ar_name),
    CONSTRAINT stations_station_name_key UNIQUE (en_name)
)
TABLESPACE pg_default;

3. Table: trains

CREATE TABLE public.trains (
    id smallint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    train_number text NULL,
    train_type_id smallint NULL,
    start_station_id smallint NULL,
    end_station_id smallint NULL,
    stops smallint NULL,
    CONSTRAINT trains_pkey PRIMARY KEY (id),
    CONSTRAINT trains_train_number_key UNIQUE (train_number),
    CONSTRAINT trains_end_station_id_fkey FOREIGN KEY (end_station_id) REFERENCES stations (id),
    CONSTRAINT trains_start_station_id_fkey FOREIGN KEY (start_station_id) REFERENCES stations (id),
    CONSTRAINT trains_train_type_id_fkey FOREIGN KEY (train_type_id) REFERENCES types (id)
)
TABLESPACE pg_default;

4. Table: types

CREATE TABLE public.types (
    id smallint GENERATED BY DEFAULT AS IDENTITY NOT NULL,
    type_name text NULL,
    other_names text[] NULL,
    CONSTRAINT types_pkey PRIMARY KEY (id),
    CONSTRAINT types_type_name_key UNIQUE (type_name)
)
TABLESPACE pg_default;

5. Materialized View: train_schedule_view

CREATE MATERIALIZED VIEW public.train_schedule_view AS
SELECT
    t.train_number,
    ty.type_name AS train_type,
    array_agg(s.en_name ORDER BY sch.stop_order) AS stop_points,
    array_agg(COALESCE(to_char(sch.arrival_time::interval, 'HH12:MI am'), to_char(sch.departure_time::interval, 'HH12:MI am'))
    ORDER BY sch.stop_order) AS arrival_time
FROM
    trains t
    JOIN types ty ON t.train_type_id = ty.id
    JOIN schedule sch ON t.id = sch.train_id
    JOIN stations s ON sch.station_id = s.id
GROUP BY
    t.train_number,
    ty.type_name;

Topics

• Compare Between Prompting in Ar Vs En