Chainlit Tutorial Series 2025 Part #5 | ChatGPT Clone | Chat History using Postgre SQL Database

The most important video of this series, we will look at how we will build our Postgre SQL Database and Link it with our Chainlit LocaLM app so that we can store and resume our chat from chat history. This will mark the end of our current Tutorial Series

Download the Postgre SQL from this link
https://www.postgresql.org/download/

SQL Commands:

----------------------------------------------------

psql -U postgres -p 5532

-- Terminate active connections (needed for DROP)
REVOKE CONNECT ON DATABASE chainlit_db FROM public;
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'chainlit_db';

-- Drop the database
DROP DATABASE IF EXISTS chainlit_db;

CREATE DATABASE chainlit_db;
CREATE USER chainlit_user WITH PASSWORD 'securepassword';
GRANT ALL PRIVILEGES ON DATABASE chainlit_db TO chainlit_user;

\c chainlit_db

-- USERS
CREATE TABLE users (
id UUID PRIMARY KEY,
identifier TEXT NOT NULL,
"createdAt" TEXT,
metadata JSONB DEFAULT '{}'::jsonb
);

-- THREADS
CREATE TABLE threads (
id UUID PRIMARY KEY,
name TEXT,
"createdAt" TEXT ,
"userId" UUID,
"userIdentifier" TEXT,
tags TEXT[],
metadata JSONB DEFAULT '{}'::jsonb
);

-- FEEDBACKS
CREATE TABLE feedbacks (
id UUID PRIMARY KEY,
"forId" UUID,
value TEXT,
comment TEXT
);

-- ELEMENTS
CREATE TABLE elements (
id UUID PRIMARY KEY,
"threadId" UUID,
type TEXT,
"chainlitKey" TEXT,
url TEXT,
"objectKey" TEXT,
name TEXT,
display TEXT,
size TEXT,
language TEXT,
page TEXT,
"forId" UUID,
mime TEXT,
props JSONB DEFAULT '{}'::jsonb
);

CREATE TABLE steps (
id UUID PRIMARY KEY,
"threadId" UUID REFERENCES threads(id) ON DELETE CASCADE,
"parentId" UUID,
name TEXT,
type TEXT,
input TEXT,
output TEXT,
"isError" BOOLEAN,
streaming BOOLEAN,
"waitForAnswer" BOOLEAN,
"showInput" TEXT,
"defaultOpen" BOOLEAN,
"createdAt" TEXT NOT NULL,
start TEXT,
"end" TEXT,
metadata JSONB DEFAULT '{}'::jsonb,
generation TEXT,
tags TEXT[],
language TEXT
);

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO chainlit_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO chainlit_user;

ALTER TABLE users OWNER TO chainlit_user;
ALTER TABLE threads OWNER TO chainlit_user;
ALTER TABLE steps OWNER TO chainlit_user;
ALTER TABLE feedbacks OWNER TO chainlit_user;
ALTER TABLE elements OWNER TO chainlit_user;

-- Optional: Grant default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO chainlit_user;

Видео Chainlit Tutorial Series 2025 Part #5 | ChatGPT Clone | Chat History using Postgre SQL Database канала Asyrin