Data Layer Foundation: Implement Models for Entity Relationship Diagram

[SAUMILDHANKAR]

Description
Translate the approved ER diagram (see /docs/er-diagram.png) into concrete data models and migrations. These models form the backbone for all previously‑defined integration issues (user sync, Twilio webhooks, LMS progress, Auth0 auditing, admin dashboards). Use our chosen ORM (sequelize-orm) and follow naming / typing conventions agreed upon in the architecture docs.

Action Items

1. Model Definitions

   • users, user_roles, twilio_integration, message_sessions, messages, llm_requests, lms_modules, user_progress, auth0_logs, admin_dashboards.

2. Relations & Constraints

   • One‑to‑many and many‑to‑one links exactly as in the ERD (e.g., users ↔️ user_roles, users ↔️ message_sessions ↔️ messages, etc.).
   • Foreign‑key constraints, cascading rules, unique indexes on natural keys (email, phone_number, external_id).

3. Field‑Level Details

   • Correct data types (json for raw_content, external_metadata; timestamp for audit columns).
   • Default values for created_at, updated_at.
   • Enum or check constraints for status fields (completion_status, direction, channel).

4. Migrations / Seed

   • Create initial migration scripts.
   • Seed script with 2‑3 demo users, one Twilio sender, one LMS module, and a dummy admin dashboard config.

5. Validation & Tests

   • Unit tests to ensure models read / write as expected.
   • Referential‑integrity tests on delete / update cascades.

6. Documentation

   • Update README.md > Database section with model diagram, setup commands, and seed instructions.
   • Link this issue to dependent issues (Connect LLM Chatbot Backend to Twilio Messaging Pipeline #6 user sync, Set up Twilio account and obtain API credentials. #8 module fetch, webhook parsing, etc.).

Resources

• Related issue: https://github.com/OpenSourceFellows/amplify_server/issues/49
  

Acceptance Criteria

• All tables from the ERD exist in the dev database with correct columns and relations.
• Running npm run migrate (or equivalent) brings an empty db to the latest schema without errors.
• Seed script inserts demo data and passes all model unit tests.
• ERD relationships are enforced at the database level (foreign keys + cascade rules).
• README contains up‑to‑date schema diagram and migration instructions.

---

[SAUMILDHANKAR]

We need to break this issue up into smaller issues (Eg: models should be defined in separate issues, mentioning the specific columns and datatypes that are needed for the respective table)

[thao-1]

@SAUMILDHANKAR Oh I see, please let me know what I should work on this week for this issue

[SAUMILDHANKAR]

Hi @echelonnought, we need your help to break this into smaller issues. So if you could look into this async or next mentor meeting, would be very helpful. Thank you.