Database Feature Implementation Docs: ORM Implementation & Data Models

[echelonnought]

Our application needs to store and manipulate data. We use PostgreSQL for storing our data, retrieving, writing, and managing that data becomes cumbersome as an application grows. To make things easier, we can use an ORM, or object-relational mapper.

For the AI Chatbot server db, we will implement an ORM called Sequelize. This will let us programmatically define our database tables, keep track of schemas, and read and write data in a way that is efficient to developers.

There are many tasks associated with this feature. Roughly in order, they are:

• Adding the node package and setting up the db client
• Creating a process to handle migrations
• Creating each data model
• Creating some seed data to make development easier.

Defining our Data Models

This diagram shows the first implementation of the models we will use.

Here are the various ways that the tables relate to each other:

1. One-to-Many (1:N) Relationships

a) users → user_roles
users.id (1) → user_roles.user_id (many)
One user can have multiple roles (e.g., applicant + reviewer)
Implemented as foreign key in user_roles

b) users → message_sessions
users.id (1) → message_sessions.user_id (many)
One user can have multiple chat sessions
Nullable for unauthenticated Twilio users

c) message_sessions → messages
message_sessions.id (1) → messages.session_id (many)
One conversation session contains many messages
Core messaging relationship

d) message_sessions → llm_requests
message_sessions.id (1) → llm_requests.session_id (many)
Each session may generate multiple LLM calls
Tracks all bot thinking processes

e) users → user_progress
users.id (1) → user_progress.user_id (many)
One user can progress through multiple LMS modules

f) lms_modules → user_progress
lms_modules.id (1) → user_progress.module_id (many)
One module can be in progress by many users

g) users → auth0_logs
users.id (1) → auth0_logs.user_id (many)
One user generates multiple auth events

2. Many-to-Many (M:N) Relationships:

a) users ↔️ lms_modules (through user_progress)
Junction table: user_progress
users.id ↔️ user_progress.user_id
lms_modules.id ↔️ user_progress.module_id
Tracks which users accessed which modules

b) users ↔️ roles (through user_roles)
While technically 1:N per above, this can become M:N if:
One role can be assigned to many users
One user can have many roles
No separate junction table needed in current design

3. One-to-One (1:1) Relationships:

a) Twilio Integration (Singleton)
The twilio_integration table is effectively 1:1 with your application
Contains global Twilio config (only 1 row expected)

b) Admin Dashboard Configs
Each admin_dashboards record is 1:1 with a dashboard view
Though multiple dashboards can exist, each has unique config

Some Important Conventions

To maintain a consistent style, we will maintain the following convention for our models:

A data model should be thought of as a class in the code base and use CamelCasing.
A data model is singular, the name of the corresponding table is plural.
A table should use snake_casing.

For example:

Table Name	Model Name
users	User
lms_modules	LmsModule
user_requests	UserRequest
message_sessions	MessageSession

Further Information

Sequelize Docs
Table Associations
Why we should use migrations