Audit DB Tables for Temporary LLM Chat Storage and S3 Lifecycle Pathways

[PallaviChitrada10]

LLM chats contain important context that needs a specific lifecycle: they should be stored temporarily in our database, then moved to Amazon S3 for long-term tracking and processing.

This issue is to check our database tables across all environments (including Virginia and Ireland) to see if we are currently storing this chat data, or if we need to set up a new temporary storage process.

Objectives
Check the Database: Review current schemas to see if any tables are actively saving LLM chat histories or session logs.
Confirm Storage Status: Determine if chat data is being held; if not, create a table.
Plan S3 Offloading: Come up with how this temporary data can be cleared and cleanly routed to AWS S3.

[PallaviChitrada10]

[PallaviChitrada10]

Create a new table named: req_more_info, which stores the LLM conversation with any user for a particular request. Our conversation depends on the request table, have the req_id reference here. Have a unique ID for the conversation. Each conversation is reopened only for a maximum of 5 times, so there has to be a countdown. To introduce this conversation limit, think of how the schema should be, should we have a composite key with req_id and conversation_id? How are you doing the limit check? Also, add the time factor: when was the last conversation? created_at, updated_at fields.
Further, our conversations may be in other languages apart from English. How is the conversation stored? We need to store the original text without any translation. Would JSON work?

Look into the options and come up with a schema quickly; this is a high priority.

[PallaviChitrada10]

Each user is allowed up to 5 follow-up interactions with the chatbot per session. This includes the initial chatbot response followed by up to 5 user questions and 5 corresponding replies, making a total of 11 messages. After 30 minutes, the user can resume the conversation on the same request with another set of 5 interactions.

For the req_more_info table, should each req_id support multiple conversation records, or only one conversation with a reopen limit of 5?
The answer to this is 5 different conversations of the same request. Also, all the conversations should be encrypted, check the options for encryption within the database, and no one should read it.

[muthidhanush]

I created the req_more_info table to store encrypted LLM conversation data for each request. The table references req_id from the request table and includes a separate conversation_id to uniquely identify each chatbot conversation/session.To support the requirement of allowing only 5 conversations per request, I added conversation_count with a check constraint between 1 and 5, along with unique constraints to prevent duplicate conversation sessions and duplicate conversation counts for the same request.

The conversation_data column is currently designed as BYTEA so encrypted multilingual conversation content can be stored securely without translation. I also added last_conversation_time, created_at, and updated_at fields for conversation tracking and session management by the request microservice.

CREATE TABLE IF NOT EXISTS virginia_dev_saayam_rdbms.req_more_info (
req_more_info_id VARCHAR(255) PRIMARY KEY,
req_id VARCHAR(255) NOT NULL,
conversation_id VARCHAR(255) NOT NULL,
conversation_count INT NOT NULL CHECK (conversation_count BETWEEN 1 AND 5),
conversation_data BYTEA NOT NULL,
last_conversation_time TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE (req_id, conversation_id),
UNIQUE (req_id, conversation_count),
FOREIGN KEY (req_id)
REFERENCES virginia_dev_saayam_rdbms.request (req_id)
);

[PallaviChitrada10]

• Column Renaming: Please rename updated_at to last_updated_at.
• Timestamp: Set the default for last_updated_at to NOW() AT TIME ZONE 'UTC'.
• Trigger Implementation: Please attach the set_updated_at() trigger (referencing the function from our ddl/supporting_languages.sql file) to the req_more_info table to ensure last_updated_at automatically refreshes on every row update.
• Verify that created_at remains unchanged during subsequent updates.
• Write the test cases

[muthidhanush]

CREATE TABLE IF NOT EXISTS virginia_dev_saayam_rdbms.req_more_info (
req_more_info_id VARCHAR(255) PRIMARY KEY,
req_id VARCHAR(255) NOT NULL,
conversation_id VARCHAR(255) NOT NULL,
-- Allows only 5 conversations per request
conversation_count INT NOT NULL CHECK (conversation_count BETWEEN 1 AND 5),
-- Stores encrypted multilingual conversation data
conversation_data BYTEA NOT NULL,
-- Tracks the last conversation activity time
last_conversation_time TIMESTAMP,
-- Stores the record creation timestamp
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-- CHANGED: Renamed from updated_at to last_updated_at
-- CHANGED: Default timestamp set to UTC
last_updated_at TIMESTAMP DEFAULT (NOW() AT TIME ZONE 'UTC'),
-- Prevent duplicate conversation IDs for the same request
UNIQUE (req_id, conversation_id),
-- Ensures only 5 unique conversation slots per request
UNIQUE (req_id, conversation_count),
-- Request reference
FOREIGN KEY (req_id)
REFERENCES virginia_dev_saayam_rdbms.request (req_id)
);
-- ADDED: Uses existing set_updated_at() function from ddl_supporting_languages.sql
-- Automatically refreshes last_updated_at whenever a row is updated
-- created_at remains unchanged during updates
CREATE TRIGGER trg_req_more_info_set_updated_at
BEFORE UPDATE ON virginia_dev_saayam_rdbms.req_more_info
FOR EACH ROW
EXECUTE FUNCTION virginia_dev_saayam_rdbms.set_updated_at();

[muthidhanush]

Req_More_Info Table Schema Change Test Cases.pdf