Faculytics Docs

Data Model

Entity-relationship diagram and schema documentation for the Faculytics database.

The database schema reflects the institutional hierarchy derived from Moodle's category structure, the questionnaire management system, and supporting modules (ChatKit, system config).

Core Domain

Constraints & Idempotency

  • Dimension Registry: Composite unique on (code, questionnaireType). Allows the same code (e.g., 'PLANNING') across different questionnaire types.
  • Enrollment: Composite unique on (user, course). Prevents duplicate enrollments.
  • User Institutional Role: Composite unique on (user, moodleCategory, role).
  • Questionnaire Version: Composite unique on (questionnaire, versionNumber).
  • Questionnaire Submission: Composite unique on (respondent, faculty, questionnaireVersion, semester, course). Indexed on (faculty, semester), (department, semester), (program, semester), (campus, semester).
  • Questionnaire Draft: Partial unique indexes handling nullable course_id and soft deletes.

Analytics Pipeline Indexes

  • Analysis Pipeline: Composite index on (semester, status) for scoped pipeline lookups.
  • Sentiment Run / Topic Model Run / Recommendation Run: Indexed on pipeline for per-pipeline run queries.
  • Sentiment Result: Indexed on run and submission for gate processing and per-submission lookups.
  • Topic / Topic Assignment: Indexed on run / topic / submission for topic model result traversal.
  • Submission Embedding: Indexed on submission for upsert lookups. Uses pgvector VectorType (768-dim).

Institutional Snapshots

QUESTIONNAIRE_SUBMISSION stores denormalized snapshots of institutional data at submission time (faculty name, department code, program code, campus code, semester, academic year). This decouples historical submissions from future hierarchy changes — if a department is renamed, existing submission reports retain the original values.

Notes

  • All entities except CHATKIT_THREAD and CHATKIT_THREAD_ITEM extend CustomBaseEntity (UUID pk, createdAt, updatedAt, deletedAt with soft-delete filter).
  • REFRESH_TOKEN stores userId as a string rather than a foreign key relation.
  • SYSTEM_CONFIG is a standalone key-value store with no relationships.