timmapuramreddy/ER_DIAGRAM.md

TiQer Standalone - Entity Relationship Diagram

Last Updated: December 31, 2024 Database: PostgreSQL 14 Schema: tiqer_standalone

Overview

This document describes the complete database schema for TiQer Standalone, including all entities, relationships, and constraints across 7 Django applications.

Total Entities: 20+ models Database Type: Relational (PostgreSQL) Schema: Custom schema tiqer_standalone (not default public)

Complete ER Diagram

📊 Diagram: Complete Entity Relationship Diagram | Open in Mermaid Editor

📝 View Mermaid Code (click to expand)

erDiagram
    %% Authentication App
    User ||--o{ Organization : "creates"
    User ||--o{ OrganizationMember : "belongs_to"
    User ||--o{ ClassRoom : "creates"
    User ||--o{ Participant : "enrolls_as"
    User ||--o{ Quiz : "creates"
    User ||--o{ TiQerSession : "teaches"
    User ||--o{ TiQerDeviceSync : "connects"
    User ||--o{ TiQerResponse : "submits"
    User ||--o{ LoginAttempt : "attempts"

    %% Organizations App
    Organization ||--o{ OrganizationMember : "has"
    Organization ||--o{ ClassRoom : "owns"
    Organization ||--o{ Quiz : "owns"
    Organization ||--o{ TiQerSession : "owns"

    %% Classes App
    ClassRoom ||--o{ Participant : "has"
    ClassRoom ||--o{ TiQerSession : "hosts"

    %% Quizzes App
    Quiz ||--o{ Question : "contains"
    Quiz ||--o{ TiQerSession : "used_in"
    Question ||--o{ Option : "has"
    Question ||--o{ TiQerAnswer : "answered_by"

    %% Sessions App
    TiQerSession ||--o{ TiQerDeviceSync : "tracks"
    TiQerSession ||--o{ TiQerResponse : "collects"
    TiQerSession ||--|| TiQerSessionState : "has"
    TiQerSession ||--o{ TiQerStateChangeHistory : "logs"
    TiQerSession ||--o{ TiQerConflictLog : "logs_conflicts"
    TiQerSession ||--o{ TiQerDeviceActivity : "monitors"
    TiQerSession ||--|| TiQerSessionLock : "locks"
    TiQerSession ||--|| TiQerStateSyncLock : "sync_locks"
    TiQerSession ||--o{ ArUcoCard : "uses"

    %% Conflict Resolution
    TiQerConflictLog ||--o{ TiQerConflictResolution : "resolves"

    %% Responses App
    TiQerResponse ||--o{ TiQerAnswer : "contains"
    Participant ||--o{ TiQerResponse : "submits"
    Option ||--o{ TiQerAnswer : "selected_in"

    %% ArUco App
    ArUcoCard ||--o{ TiQerDeviceSync : "scanned_by"

    User {
        int id PK
        string email UK "Unique, indexed"
        string username UK "Unique, indexed"
        string password "Hashed"
        string first_name
        string last_name
        string user_type "admin/teacher/student"
        string phone_number
        string profile_image_url
        boolean is_active
        boolean is_staff
        boolean is_superuser
        datetime date_joined
        datetime last_login
    }

    LoginAttempt {
        int id PK
        int user_id FK
        string ip_address
        string user_agent
        boolean success
        string failure_reason
        datetime attempted_at
    }

    Organization {
        int id PK
        string org_token UK "26-char ULID, indexed"
        string name
        string org_type "School/College/Corporate/Individual"
        text description
        string logo_url
        string city
        string state
        string country
        int created_by_id FK
        datetime created_at
        datetime updated_at
        boolean is_deleted
    }

    OrganizationMember {
        int id PK
        int organization_id FK "indexed"
        int user_id FK "indexed"
        string role "admin/teacher/student"
        datetime joined_at
        boolean is_active
        datetime created_at
    }

    ClassRoom {
        int id PK
        string class_token UK "26-char ULID, indexed"
        string name
        string class_type "educational/training/workshop"
        text description
        int organization_id FK "indexed"
        int created_by_id FK
        datetime start_date
        datetime end_date
        boolean is_active
        datetime created_at
        datetime updated_at
        boolean is_deleted
    }

    Participant {
        int id PK
        int user_id FK "indexed"
        int class_instance_id FK "indexed"
        string roll_number "Optional"
        string student_name
        string student_email
        datetime enrolled_at
        boolean is_active
        datetime created_at
        datetime updated_at
        boolean is_deleted
    }

    Quiz {
        int id PK
        string quiz_token UK "26-char ULID, indexed"
        string title
        text description
        int organization_id FK "indexed"
        int created_by_id FK
        string quiz_mode "quiz/poll"
        string quiz_type "MCQ/True-False/Open-Ended"
        int time_limit "seconds, nullable"
        boolean is_published
        boolean is_public
        int total_questions
        float total_marks
        datetime created_at
        datetime updated_at
        boolean is_deleted
    }

    Question {
        int id PK
        int quiz_id FK "indexed"
        text question_text
        string question_type "mcq/true_false/open_ended"
        string image_url "nullable"
        int marks
        int question_order
        int time_limit "seconds, nullable"
        datetime created_at
        datetime updated_at
        boolean is_deleted
    }

    Option {
        int id PK
        int question_id FK "indexed"
        text option_text
        string option_image_url "nullable"
        int option_position "0-based"
        boolean is_correct
        datetime created_at
        datetime updated_at
    }

    TiQerSession {
        int id PK
        string session_token UK "26-char ULID, indexed"
        string session_name "nullable"
        int quiz_id FK "indexed"
        int teacher_id FK
        int classroom_id FK "indexed"
        int organization_id FK "indexed"
        string session_mode "Multi-Device/Single-Device"
        string status "Setup/Active/Paused/Ended"
        datetime start_time "nullable"
        datetime end_time "nullable"
        int total_questions
        int current_question_index
        boolean allow_late_join
        datetime created_at
        datetime updated_at
        boolean is_deleted
    }

    TiQerSessionState {
        int id PK
        int tiqer_session_id FK "Unique, One-to-one with TiQerSession"
        int current_question_index
        string session_status "Active/Paused/Ended"
        int timer_remaining "seconds, nullable"
        datetime last_update
        string updated_by_device_id "nullable"
        datetime created_at
        datetime updated_at
    }

    TiQerStateChangeHistory {
        int id PK
        int tiqer_session_id FK "indexed"
        int previous_question_index
        int new_question_index
        string action "navigate_next/navigate_prev/jump/start/end"
        int changed_by_user_id FK
        string changed_by_device_id
        datetime changed_at
    }

    TiQerConflictLog {
        int id PK
        int tiqer_session_id FK "indexed"
        string conflict_type "simultaneous_navigation/state_mismatch/device_desync"
        text conflict_details "JSON"
        string device_id_1
        string device_id_2 "nullable"
        datetime detected_at
        boolean is_resolved
    }

    TiQerConflictResolution {
        int id PK
        int conflict_log_id FK "indexed"
        string resolution_strategy "teacher_wins/latest_timestamp/device_priority/merge_states"
        int winning_device_id "nullable"
        text resolution_details "JSON"
        datetime resolved_at
        int resolved_by_user_id FK "nullable"
    }

    TiQerDeviceSync {
        int id PK
        int tiqer_session_id FK "indexed"
        int user_id FK "indexed"
        string device_id UK "Unique per session"
        string device_type "Web App/Mobile App/Tablet"
        string device_name "nullable"
        string connection_status "Connected/Disconnected/Reconnecting"
        datetime last_heartbeat
        int network_quality "0-100, nullable"
        boolean is_stale
        datetime created_at
        datetime updated_at
    }

    TiQerDeviceActivity {
        int id PK
        int tiqer_session_id FK "indexed"
        int user_id FK
        string device_id
        string activity_type "join/leave/heartbeat/navigation/answer_submit"
        text activity_data "JSON, nullable"
        datetime timestamp
    }

    TiQerSessionLock {
        int id PK
        int tiqer_session_id FK "Unique, One-to-one with TiQerSession"
        boolean is_locked
        int locked_by_user_id FK "nullable"
        string locked_by_device_id "nullable"
        datetime lock_acquired_at "nullable"
        datetime lock_expires_at "nullable"
    }

    TiQerStateSyncLock {
        int id PK
        int tiqer_session_id FK "Unique, One-to-one with TiQerSession"
        boolean is_syncing
        string sync_operation "update_state/navigate/end_session, nullable"
        datetime sync_started_at "nullable"
        datetime sync_completed_at "nullable"
    }

    ArUcoCard {
        int id PK
        string aruco_id UK "Marker ID (e.g., 001, 002)"
        string card_type "student_id/answer_option"
        string assigned_value "student_roll/option_letter (A/B/C/D)"
        int organization_id FK "indexed, nullable"
        int session_id FK "indexed, nullable"
        boolean is_active
        int total_uses
        datetime last_used_at "nullable"
        datetime created_at
        datetime updated_at
    }

    TiQerResponse {
        int id PK
        int tiqer_session_id FK "indexed"
        int participant_id FK "indexed"
        int user_id FK "indexed"
        datetime started_at
        datetime submitted_at "nullable"
        string response_status "in_progress/submitted/timed_out"
        float total_score "nullable"
        int correct_answers
        int incorrect_answers
        int unanswered
        boolean scanned_via_aruco
        datetime created_at
        datetime updated_at
    }

    TiQerAnswer {
        int id PK
        int tiqer_response_id FK "indexed"
        int question_id FK "indexed"
        int selected_option_id FK "indexed, nullable"
        text answer_text "nullable, for open-ended"
        boolean is_correct "nullable"
        float marks_awarded "nullable"
        int time_taken "seconds, nullable"
        boolean scanned_via_aruco
        datetime aruco_scan_timestamp "nullable"
        datetime answered_at
        datetime created_at
        datetime updated_at
    }

Entity Groups

Core Entities (Always Required)

User - All users (admin/teacher/student)
Organization - Multi-tenant organizations
ClassRoom - Classrooms/courses (Django model: Class)
Quiz - Quiz metadata
Question - Quiz questions
Option - Question options

Session Management Entities

TiQerSession - Quiz session instance
TiQerSessionState - Current session state (Mirror Mode)
TiQerDeviceSync - Connected devices
ArUcoCard - Physical card assignments

Response Collection Entities

TiQerResponse - Student response container
TiQerAnswer - Individual question answers

Supporting Entities

OrganizationMember - Org membership
Participant - ClassRoom enrollment
LoginAttempt - Security auditing

Advanced Session Entities (Conflict Resolution)

TiQerStateChangeHistory - State change audit trail
TiQerConflictLog - Conflict detection log
TiQerConflictResolution - Conflict resolution records
TiQerDeviceActivity - Device activity log
TiQerSessionLock - Session-level locking
TiQerStateSyncLock - Sync operation locking

Key Relationships

One-to-Many Relationships

Parent	Child	Relationship
User	Organization	One user creates many organizations
User	ClassRoom	One user creates many classes
User	Quiz	One user creates many quizzes
User	TiQerSession	One teacher runs many sessions
Organization	ClassRoom	One org has many classes
Organization	Quiz	One org owns many quizzes
ClassRoom	Participant	One class has many students
Quiz	Question	One quiz has many questions
Question	Option	One question has many options
TiQerSession	TiQerDeviceSync	One session tracks many devices
TiQerSession	TiQerResponse	One session collects many responses
TiQerResponse	TiQerAnswer	One response has many answers

One-to-One Relationships

Entity A	Entity B	Relationship
TiQerSession	TiQerSessionState	Each session has exactly one current state
TiQerSession	TiQerSessionLock	Each session has exactly one lock record
TiQerSession	TiQerStateSyncLock	Each session has exactly one sync lock

Many-to-Many Relationships

Entity A	Through Table	Entity B
User	OrganizationMember	Organization
User	Participant	ClassRoom

Indexes and Constraints

Primary Keys

All tables have auto-incrementing id as primary key

Unique Constraints

User.email - Unique, indexed
User.username - Unique, indexed
Organization.org_token - Unique ULID (26 chars), indexed
ClassRoom.class_token - Unique ULID, indexed
Quiz.quiz_token - Unique ULID, indexed
TiQerSession.session_token - Unique ULID, indexed
ArUcoCard.aruco_id - Unique marker ID, indexed
TiQerDeviceSync.device_id - Unique per session

Foreign Key Indexes

OrganizationMember.organization_id - Indexed
OrganizationMember.user_id - Indexed
ClassRoom.organization_id - Indexed
Participant.user_id - Indexed
Participant.class_instance_id - Indexed
Quiz.organization_id - Indexed
Question.quiz_id - Indexed
Option.question_id - Indexed
TiQerSession.quiz_id - Indexed
TiQerSession.classroom_id - Indexed
TiQerSession.organization_id - Indexed
TiQerDeviceSync.tiqer_session_id - Indexed
TiQerDeviceSync.user_id - Indexed
TiQerResponse.tiqer_session_id - Indexed
TiQerResponse.participant_id - Indexed
TiQerAnswer.tiqer_response_id - Indexed
TiQerAnswer.question_id - Indexed

Composite Indexes (Recommended)

(TiQerSession.organization_id, TiQerSession.status) - For filtering active sessions
(TiQerResponse.tiqer_session_id, TiQerResponse.participant_id) - For response lookup
(TiQerAnswer.tiqer_response_id, TiQerAnswer.question_id) - For answer lookup
(TiQerDeviceSync.tiqer_session_id, TiQerDeviceSync.connection_status) - For active devices

Soft Delete Pattern

All core entities implement soft delete using is_deleted boolean field:

Models with Soft Delete: Organization, ClassRoom, Participant, Quiz, Question, TiQerSession, TiQerResponse
Default Behavior: is_deleted=False (active)
Deletion: Set is_deleted=True instead of actual DELETE
Queries: Filter is_deleted=False in default queryset

Audit Fields Pattern

Most entities include audit trail fields:

created_at - Timestamp when record was created
updated_at - Timestamp when record was last modified
created_by_id - Foreign key to User who created the record
updated_by_id - Foreign key to User who last modified (optional)

ULID Token Pattern

Several entities use ULID (Universally Unique Lexicographically Sortable Identifier) for public-facing tokens:

Length: 26 characters
Format: Base32 encoded (0-9, A-Z, case-insensitive)
Sortable: Lexicographically sortable by timestamp
Collision-resistant: Highly unlikely collisions

Models using ULID:

Organization.org_token
ClassRoom.class_token
Quiz.quiz_token
TiQerSession.session_token

Field Types Reference

Field Type	PostgreSQL Type	Usage
`int id`	`SERIAL`	Auto-increment primary key
`string email`	`VARCHAR(254)`	Email addresses
`string username`	`VARCHAR(150)`	Usernames
`string password`	`VARCHAR(128)`	Hashed passwords
`text description`	`TEXT`	Long text fields
`datetime`	`TIMESTAMP WITH TIME ZONE`	Dates and times
`boolean`	`BOOLEAN`	True/false flags
`float`	`NUMERIC(5, 2)`	Decimal numbers (scores, marks)
`JSON`	`JSONB`	Structured data (conflict logs)

Database Constraints

Check Constraints

User.user_type ∈ {'admin', 'teacher', 'student'}
Organization.org_type ∈ {'School', 'College', 'Corporate', 'Individual'}
ClassRoom.class_type ∈ {'educational', 'training', 'workshop'}
Quiz.quiz_mode ∈ {'quiz', 'poll'}
Quiz.quiz_type ∈ {'MCQ', 'True-False', 'Open-Ended'}
TiQerSession.status ∈ {'Setup', 'Active', 'Paused', 'Ended'}
TiQerSession.session_mode ∈ {'Multi-Device', 'Single-Device'}
TiQerDeviceSync.connection_status ∈ {'Connected', 'Disconnected', 'Reconnecting'}
ArUcoCard.card_type ∈ {'student_id', 'answer_option'}

NOT NULL Constraints

All foreign keys (except nullable relationships)
All token fields (org_token, class_token, etc.)
All status/type enum fields
created_at, updated_at timestamps

Cascade Behavior

ON DELETE CASCADE: When parent is deleted, children are deleted
- Quiz → Question → Option (full cascade)
- TiQerSession → TiQerDeviceSync (cascade)
- TiQerSession → TiQerResponse → TiQerAnswer (cascade)
ON DELETE PROTECT: Prevent deletion if children exist
- User → TiQerSession (protect - can't delete teacher with active sessions)
- Organization → Quiz (protect)
ON DELETE SET NULL: Set foreign key to NULL when parent deleted
- ArUcoCard.organization_id (nullable)

Schema Migration Strategy

Initial Migrations (Phase 1)

Create authentication models (User)
Create organizations models
Create classes models (depends on organizations)
Create quizzes models (depends on organizations)

Secondary Migrations (Phase 2)

Create sessions models (depends on quizzes, classes)
Create responses models (depends on sessions)
Create ArUco models

Advanced Migrations (Phase 3)

Add conflict resolution models
Add device activity tracking
Add session locking

Sample Queries

Get all active sessions for a teacher

SELECT * FROM tiqer_standalone.tiqersession
WHERE teacher_id = 123
  AND status IN ('Active', 'Paused')
  AND is_deleted = FALSE
ORDER BY start_time DESC;

Get all participants in a class

SELECT u.first_name, u.last_name, p.roll_number, p.enrolled_at
FROM tiqer_standalone.participant p
JOIN tiqer_standalone."user" u ON p.user_id = u.id
WHERE p.class_instance_id = 456
  AND p.is_active = TRUE
  AND p.is_deleted = FALSE
ORDER BY p.roll_number;

Get quiz analytics (correctness rate)

SELECT
  q.title,
  COUNT(a.id) as total_answers,
  SUM(CASE WHEN a.is_correct = TRUE THEN 1 ELSE 0 END) as correct_answers,
  ROUND(100.0 * SUM(CASE WHEN a.is_correct = TRUE THEN 1 ELSE 0 END) / COUNT(a.id), 2) as correctness_rate
FROM tiqer_standalone.quiz q
JOIN tiqer_standalone.question qs ON qs.quiz_id = q.id
JOIN tiqer_standalone.tiqeranswer a ON a.question_id = qs.id
WHERE q.id = 789
GROUP BY q.id, q.title;

Get currently connected devices for a session

SELECT
  u.username,
  d.device_type,
  d.device_name,
  d.connection_status,
  d.last_heartbeat
FROM tiqer_standalone.tiqerdevicesync d
JOIN tiqer_standalone."user" u ON d.user_id = u.id
WHERE d.tiqer_session_id = 101
  AND d.connection_status = 'Connected'
  AND d.is_stale = FALSE
ORDER BY d.last_heartbeat DESC;

Maintained by: Mohan Reddy Last Review: December 31, 2024