chatbot-database-system

A relational database design and full SQL implementation for an AI chatbot platform, built in MySQL 8.0. The schema models user accounts, role-based access, subscription plans, payments, conversations and messages, an FAQ knowledge base, and a feedback/rating loop — together with audit triggers, stored procedures, and a set of analytical queries.

This project was originally developed as a university database lab project. It has since been reorganized into a clean, browsable repository structure for portfolio presentation.

The core SQL work remains unchanged, including the schema, constraints, triggers, stored procedures, queries, and sample data. Only the repository structure and documentation have been improved.

Entity-Relationship Diagram

The full-size diagram (and an editable SVG source) is in docs/ERD.png / docs/ERD.svg. Design notes on the schema — including why two tables have no foreign keys, and the meaning of the custom 2234 error code — are in docs/schema-notes.md.

Schema overview

Table	Purpose
users	Accounts, with role (user / admin), unique username/email
plans	Subscription tiers (name, price, duration)
payments	Payment transactions, linked to a user
subscriptions	Links a user, a plan, and the payment that funded it
chats	A conversation session belonging to a user
messages	Individual messages within a chat (user or bot sender)
faqs	Standalone question/answer knowledge base
feedback	User ratings (1–5) and comments on individual bot messages
payment_logs	Audit log of every insert/update/delete on payments after the payment triggers are installed
plan_change_logs	Audit log of subscription plan changes

Constraints used throughout the schema include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK (e.g. non-negative prices, rating between 1 and 5, end date after start date), and ENUM for fixed-value columns (role, status, sender_type, operation, upgrade_type).

Repository structure

chatbot-database-system/
├── README.md
├── LICENSE
├── .gitignore
├── run_all.sql                  one-shot script to run everything in order
├── docs/
│   ├── ERD.png                  entity-relationship diagram (image)
│   ├── ERD.svg                  entity-relationship diagram (vector source)
│   └── schema-notes.md          design rationale for non-obvious decisions
└── sql/
    ├── 01_schema/
    │   ├── 01_create_database.sql
    │   ├── 02_create_tables.sql       8 core domain tables
    │   └── 03_create_log_tables.sql   payment_logs, plan_change_logs
    ├── 02_sample_data/
    │   └── seed_data.sql               sample rows for core domain tables
    ├── 03_queries/
    │   ├── conversations_by_user.sql
    │   ├── high_value_payments.sql
    │   ├── username_pattern_match.sql
    │   ├── non_phone_pattern_messages.sql
    │   ├── potential_customers.sql
    │   ├── successful_payment_totals.sql
    │   └── active_chat_users.sql
    ├── 04_procedures/
    │   ├── search_keyword.sql          keyword search across chats
    │   └── user_plan_loyalty.sql       renewal/loyalty report by plan
    └── 05_triggers/
        ├── payment_validation_and_logging.sql   5 triggers on payments
        └── plan_change_logging.sql              1 trigger on subscriptions

Getting started

Option A — MySQL Workbench

1. Open MySQL Workbench and connect to a local MySQL 8.0 server.
2. Open sql/01_schema/01_create_database.sql and execute it (creates and selects the chatbot_system database).
3. Open and execute, in order: 02_create_tables.sql, 03_create_log_tables.sql.
4. Open and execute the trigger files in sql/05_triggers/.
5. Open and execute sql/02_sample_data/seed_data.sql.
6. Open and execute the procedure files in sql/04_procedures/ to register search_keyword and user_plan_loyalty.
7. Run any file in sql/03_queries/ to see the answer to that question.

Option B — MySQL CLI

From the repository root:

mysql -u root -p < sql/01_schema/01_create_database.sql
mysql -u root -p chatbot_system < sql/01_schema/02_create_tables.sql
mysql -u root -p chatbot_system < sql/01_schema/03_create_log_tables.sql
mysql -u root -p chatbot_system < sql/05_triggers/payment_validation_and_logging.sql
mysql -u root -p chatbot_system < sql/05_triggers/plan_change_logging.sql
mysql -u root -p chatbot_system < sql/02_sample_data/seed_data.sql
mysql -u root -p chatbot_system < sql/04_procedures/search_keyword.sql
mysql -u root -p chatbot_system < sql/04_procedures/user_plan_loyalty.sql

Then run any individual query file the same way, e.g.:

mysql -u root -p chatbot_system < sql/03_queries/potential_customers.sql

Or, to run the entire project in one go using the MySQL client's SOURCE command:

mysql -u root -p
mysql> SOURCE run_all.sql;

(Run mysql from the repository root so the relative paths in run_all.sql resolve correctly.)

What each query/procedure/trigger does

File	Description
conversations_by_user.sql	All chats joined to their user, sorted by username ascending, then chat start time descending
high_value_payments.sql	Payments over 100,000 Toman with status completed
username_pattern_match.sql	Users whose username starts with ai_ and contains at least one digit
non_phone_pattern_messages.sql	Messages with a long digit sequence that is not a valid Iranian mobile number
potential_customers.sql	Creates and populates potential_customers: users with more than 1,000 chats and less than 1,000,000 Toman in completed payments
successful_payment_totals.sql	Per-user total of completed payments, filtered to totals over 500,000 Toman
active_chat_users.sql	Users ranked by chat count (desc) and, within that, message count (desc), restricted to chats started in the last 7 days
search_keyword.sql	Procedure: given a keyword, returns every user with a matching chat, the keyword's occurrence count per chat, broken down by chat date
user_plan_loyalty.sql	Procedure: given a numeric threshold, returns per-user-per-plan renewal count, average payment, total paid, and a loyal_to flag
payment_validation_and_logging.sql	5 triggers validating payment amounts (must be >= 0 and < 100,000,000) and logging every insert/update/delete to payment_logs; invalid amounts raise a custom error (SQLSTATE 45000, errno 2234)
plan_change_logging.sql	Trigger that detects a subscription plan change and logs the user, old/new plan, upgrade direction, lifetime payments, and feedback-rating breakdown to plan_change_logs

Skills demonstrated

• Relational schema design and normalization across 10 interrelated tables
• Constraint enforcement: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, ENUM
• Multi-table joins, aggregation, GROUP BY / HAVING, and multi-key ORDER BY
• Regular expressions in SQL (REGEXP) for pattern validation, including Latin and Persian digit support
• Stored procedures with input parameters and conditional (IF) output columns
• Triggers (BEFORE/AFTER, INSERT/UPDATE/DELETE) implementing custom validation and audit logging
• Custom error signaling with SIGNAL SQLSTATE and a project-specific error code
• Designing and populating realistic, edge-case-aware sample data
• Translating a written specification into a verifiable, demo-able SQL implementation

License

This project is licensed under the MIT License.