# FablePool — Entity-Relationship Documentation

This document describes the complete data model behind FablePool's database schema: all 26
entities, their relationships, constraints, indexes, and the cross-cutting patterns
(versioning, soft delete, generic targets, audit logging) that the Django apps implement.

Apps and entity ownership:

| Django app       | Entities                                                                                  |
|------------------|-------------------------------------------------------------------------------------------|
| `apps.core`      | MediaAsset, AuditLog (+ abstract `TimeStampedModel`, `SoftDeleteModel` mixins)             |
| `apps.accounts`  | User, Profile, Role                                                                        |
| `apps.taxonomy`  | Topic, Tag                                                                                 |
| `apps.content`   | Problem, ProblemVersion, Hint, Solution, Course, CourseVersion, Module, Lesson             |
| `apps.learning`  | Enrollment, Progress, ProblemAttempt                                                       |
| `apps.community` | Review, ReviewComment, DiscussionThread, DiscussionComment, Vote, Bookmark, Report, Notification |

---

## 1. High-level ER diagram

```mermaid
erDiagram
    USER ||--|| PROFILE : "has"
    USER }o--o{ ROLE : "granted"
    USER ||--o{ MEDIA_ASSET : "uploads"
    USER ||--o{ AUDIT_LOG : "acts in"

    TOPIC ||--o{ TOPIC : "parent of"
    TOPIC ||--o{ TAG : "groups"
    TOPIC ||--o{ PROBLEM : "categorises"
    TOPIC ||--o{ COURSE : "categorises"

    USER ||--o{ PROBLEM : "owns"
    PROBLEM ||--o{ PROBLEM_VERSION : "has versions"
    PROBLEM |o--o| PROBLEM_VERSION : "published_version"
    PROBLEM }o--o{ TAG : "tagged with"
    PROBLEM ||--o{ PROBLEM : "forked from (origin)"
    PROBLEM_VERSION ||--o{ HINT : "ordered hints"
    PROBLEM_VERSION ||--o{ SOLUTION : "solutions"
    PROBLEM_VERSION }o--o{ PROBLEM : "prerequisites"

    USER ||--o{ COURSE : "owns"
    COURSE ||--o{ COURSE_VERSION : "has versions"
    COURSE |o--o| COURSE_VERSION : "published_version"
    COURSE }o--o{ TAG : "tagged with"
    COURSE ||--o{ COURSE : "forked from (origin)"
    COURSE_VERSION ||--o{ MODULE : "ordered modules"
    MODULE ||--o{ LESSON : "ordered lessons"
    LESSON }o--o{ PROBLEM : "includes"

    USER ||--o{ ENROLLMENT : ""
    COURSE ||--o{ ENROLLMENT : ""
    USER ||--o{ PROGRESS : ""
    LESSON ||--o{ PROGRESS : ""
    USER ||--o{ PROBLEM_ATTEMPT : ""
    PROBLEM_VERSION ||--o{ PROBLEM_ATTEMPT : ""

    USER ||--o{ REVIEW : "writes"
    PROBLEM_VERSION ||--o{ REVIEW : "reviewed in"
    COURSE_VERSION ||--o{ REVIEW : "reviewed in"
    REVIEW ||--o{ REVIEW_COMMENT : ""
    USER ||--o{ DISCUSSION_THREAD : "starts"
    DISCUSSION_THREAD ||--o{ DISCUSSION_COMMENT : ""
    DISCUSSION_COMMENT ||--o{ DISCUSSION_COMMENT : "replies"
    USER ||--o{ VOTE : ""
    USER ||--o{ BOOKMARK : ""
    USER ||--o{ REPORT : "files"
    USER ||--o{ NOTIFICATION : "receives"
```

`VOTE`, `BOOKMARK`, `REPORT`, and `DISCUSSION_THREAD` target arbitrary entities via
generic relations (see §6), which Mermaid cannot express directly.

---

## 2. Global conventions

- **Primary keys**: `BigAutoField` everywhere (`DEFAULT_AUTO_FIELD`). External identity is
  exposed via slugs (content) or usernames (users); numeric IDs never leak semantics.
- **Timestamps**: every concrete model inherits `TimeStampedModel` (`created_at`,
  `updated_at`, both indexed where queried).
- **Soft delete**: container entities (`Problem`, `Course`, `DiscussionComment`) inherit
  `SoftDeleteModel`, which adds a nullable `deleted_at` and a default manager filtering
  `deleted_at IS NULL` plus an `all_objects` manager. Soft-deleted rows keep their slugs
  reserved; hard deletion is an explicit admin action that is audit-logged.
- **Structured content**: all rich content lives in `JSONField` columns holding documents
  conforming to `schemas/v1/*.json` (validated at the application layer by
  `apps.content.validators` before save).
- **Licensing**: content containers carry a `license` SPDX string, defaulting to
  `CC-BY-SA-4.0`. Forks must keep a compatible license; enforced in the service layer.

---

## 3. Entity catalogue

### 3.1 `apps.accounts`

#### User
Custom user (extends `AbstractUser`).

| Field | Type | Notes |
|---|---|---|
| `username` | citext-like unique | login + public handle |
| `email` | unique, indexed | verified before contributor role grant |
| `display_name` | varchar(120) | public name shown in attribution |
| `reputation` | integer, default 0 | denormalised; recomputed by background job |
| `locale` | varchar(16), default `en` | i18n preference |
| `roles` | M2M → Role | role grants; admin-only mutation |

Indexes: `(reputation DESC)` for leaderboards; unique on `username`, `email`.

#### Role
| Field | Type | Notes |
|---|---|---|
| `slug` | unique | one of `learner`, `contributor`, `reviewer`, `moderator`, `admin` (extensible) |
| `name`, `description` | varchar/text | display metadata |

Roles are data, not enum columns, so deployments can add custom roles. Permission checks
resolve `user.roles` plus Django's `is_staff`/`is_superuser`.

#### Profile
One-to-one with User (`on_delete=CASCADE`).

| Field | Notes |
|---|---|
| `bio` | text, rendered as plain Markdown subset |
| `website`, `location`, `pronouns` | optional public fields |
| `avatar` | `FileField` (added in migration `0002_profile_avatar`), S3/MinIO-backed |
| `show_email` | bool, default false |

### 3.2 `apps.taxonomy`

#### Topic
Hierarchical subject tree (adjacency list: `parent` FK → self, `on_delete=PROTECT`).

| Field | Notes |
|---|---|
| `slug` | unique |
| `name`, `description` | display |
| `parent` | nullable self-FK; depth is validated ≤ 4 in `clean()` |

Index: `(parent, slug)`.

#### Tag
| Field | Notes |
|---|---|
| `slug` | unique |
| `name`, `description` | display |
| `topic` | nullable FK → Topic (`on_delete=SET_NULL`), groups tags under a subject |

### 3.3 `apps.content`

#### Problem (container) and ProblemVersion (immutable snapshot)

**Problem** — identity, ownership, fork lineage:

| Field | Notes |
|---|---|
| `slug` | unique, stable across versions |
| `owner` | FK → User (`on_delete=PROTECT`) — attribution must survive |
| `topic` | FK → Topic (`SET_NULL`) |
| `tags` | M2M → Tag |
| `origin` | nullable self-FK (`SET_NULL`) — fork source, for attribution chains |
| `license` | SPDX string, default `CC-BY-SA-4.0` |
| `published_version` | nullable FK → ProblemVersion (`SET_NULL`, `related_name="+"`) |
| `is_locked` | bool — moderators freeze contested content |
| `deleted_at` | soft delete |

Invariant: **a Problem is publicly visible iff `published_version` is non-null and points
at a version with `status="published"`.** Publication only happens through the review
workflow; this is enforced in the service layer and double-checked by a DB trigger-style
constraint in the API layer.

**ProblemVersion** — write-once after leaving `draft`:

| Field | Notes |
|---|---|
| `problem` | FK → Problem (`CASCADE`) |
| `version_number` | positive int; `UniqueConstraint(problem, version_number)` |
| `title`, `summary` | denormalised for listings/search |
| `problem_type` | choices: `multiple_choice`, `numeric`, `expression`, `proof`, `code`, `ordering`, `matching`, `widget` |
| `difficulty` | smallint 1–5, `CheckConstraint(1 ≤ difficulty ≤ 5)` |
| `content` | JSON — full `problem-content` document (schemas/v1) |
| `answer_spec` | JSON — extracted answer block for fast grading (never exposed via public API) |
| `prerequisites` | M2M → Problem (`related_name="required_by"`) |
| `status` | lifecycle, see §4 |
| `author` | FK → User (`PROTECT`) — version author may differ from container owner |
| `changelog` | text — human-readable diff summary |

Indexes: `(problem, -version_number)`, `(status, created_at)`, `(difficulty)`,
GIN index on `content` for containment queries (Postgres).

#### Hint
| Field | Notes |
|---|---|
| `version` | FK → ProblemVersion (`CASCADE`, `related_name="hints"`) |
| `order` | smallint; `UniqueConstraint(version, order)` |
| `content` | JSON content-document |

#### Solution
| Field | Notes |
|---|---|
| `version` | FK → ProblemVersion (`CASCADE`, `related_name="solutions"`) |
| `author` | FK → User (`PROTECT`) — community solutions are attributable |
| `content` | JSON content-document |
| `is_official` | bool — at most one official per version (partial unique index) |

#### Course / CourseVersion / Module / Lesson

**Course** mirrors Problem (slug, owner, topic, tags, origin, license,
`published_version` → CourseVersion, soft delete, `is_locked`).

**CourseVersion**: `course` FK, `version_number` (unique with course), `title`, `summary`,
`description` (JSON content-document), `status` (same lifecycle as ProblemVersion),
`author`, `changelog`. The whole module/lesson tree hangs off a CourseVersion, so a
published course is a frozen snapshot and editing happens on a new draft version.

**Module**: `course_version` FK (`CASCADE`, `related_name="modules"`), `order`
(unique within version), `title`, `summary`.

**Lesson**: `module` FK (`CASCADE`, `related_name="lessons"`), `order` (unique within
module), `title`, `kind` (`lesson` | `problem_set` | `quiz` | `project`), `content`
(JSON `lesson-content` document), `problems` M2M → Problem (resolution order is carried in
the content document; the M2M exists for referential integrity and reverse lookup).

### 3.4 `apps.learning`

#### Enrollment
| Field | Notes |
|---|---|
| `user` / `course` | FKs; `UniqueConstraint(user, course)` |
| `course_version` | FK → CourseVersion — version pinned at enrollment, upgraded explicitly |
| `status` | `active`, `completed`, `dropped` |

#### Progress
Per user × lesson, also carries spaced-repetition scheduling state.

| Field | Notes |
|---|---|
| `user` / `lesson` | FKs; `UniqueConstraint(user, lesson)` |
| `state` | `not_started`, `in_progress`, `completed` |
| `score` | nullable float 0–1 for quizzes |
| `completed_at` | nullable |
| `due_at`, `interval_days`, `ease_factor` | SRS scheduling (SM-2-style); `due_at` indexed for the review queue |

#### ProblemAttempt
Append-only.

| Field | Notes |
|---|---|
| `user` | FK |
| `problem_version` | FK → ProblemVersion (`PROTECT` — attempts must not lose their target) |
| `attempt_number` | per (user, problem_version); `UniqueConstraint(user, problem_version, attempt_number)` |
| `submitted_answer` | JSON (shape depends on `problem_type`) |
| `is_correct` | nullable bool (`NULL` = pending async grading, e.g. code sandbox) |
| `hints_used`, `time_spent_ms` | telemetry for difficulty calibration |

Index: `(user, created_at DESC)` for activity feeds and streak computation.

### 3.5 `apps.community`

#### Review / ReviewComment
A Review targets exactly one version — `CheckConstraint` requires exactly one of
`problem_version` / `course_version` non-null.

| Review field | Notes |
|---|---|
| `reviewer` | FK → User (`PROTECT`) |
| `verdict` | `pending`, `approve`, `request_changes`, `reject` |
| `summary` | text |
| unique | `(reviewer, problem_version)` and `(reviewer, course_version)` partial uniques — one review per reviewer per version |

ReviewComment: `review` FK, `author`, `body`, `block_ref` (anchor into the content
document, e.g. `blocks[3]`), `resolved` bool.

#### DiscussionThread / DiscussionComment
Thread targets any entity via generic FK (`content_type`, `object_id`, indexed together);
fields: `author`, `title`, `is_locked`, `is_pinned`. Comments: `thread` FK, `author`,
nullable `parent` self-FK (one-level-deep replies enforced in `clean()`), `body`,
soft-deletable (tombstoned, not removed, to keep threads readable).

#### Vote / Bookmark
Both are (user × generic target) rows with `UniqueConstraint(user, content_type,
object_id)`. Vote adds `value` ∈ {−1, +1} (`CheckConstraint`); Bookmark adds a free-text
`note`. Vote tallies feed reputation recomputation.

#### Report
| Field | Notes |
|---|---|
| `reporter` | FK → User |
| target | generic FK |
| `reason` | `spam`, `plagiarism`, `incorrect`, `inappropriate`, `other` |
| `details` | text |
| `status` | `open`, `triaged`, `resolved`, `dismissed` (indexed) |
| `handled_by`, `resolution_note` | moderator outcome |

#### Notification
`user` FK, `kind` (dotted event name, e.g. `review.approved`), `payload` JSON,
`read_at` nullable. Index `(user, read_at, created_at DESC)` for the unread inbox.

### 3.6 `apps.core`

#### MediaAsset
`uploader` FK, `file`, `kind` (`image`/`audio`/`video`/`attachment`), `mime_type`,
`size_bytes`, `sha256` (indexed — dedupe and integrity), `alt_text` (required for images;
accessibility), `license`. Referenced from content documents by asset ID, never by raw URL.

#### AuditLog
Append-only; rows are never updated or deleted.

| Field | Notes |
|---|---|
| `actor` | nullable FK → User (`SET_NULL` — log survives account deletion) |
| `action` | dotted verb, e.g. `problem.published`, `report.dismissed`, `role.granted` |
| `content_type` / `object_id` | generic target |
| `metadata` | JSON context (old/new values, request ID) |
| `ip_address` | `GenericIPAddressField`, nullable |

Index: `(content_type, object_id, created_at DESC)` and `(actor, created_at DESC)`.

---

## 4. Version lifecycle

Both `ProblemVersion.status` and `CourseVersion.status` follow one state machine:

```mermaid
stateDiagram-v2
    [*] --> draft
    draft --> submitted : author submits
    submitted --> in_review : reviewer claims
    in_review --> changes_requested : verdict request_changes
    changes_requested --> submitted : author revises (same version while draft-editable fields only)
    in_review --> rejected : verdict reject
    in_review --> accepted : quorum of approve verdicts
    accepted --> published : moderator/auto publish — container.published_version updated
    published --> archived : superseded by newer published version
    rejected --> [*]
```

Rules enforced in the service layer and asserted by API tests:

1. `content`/`answer_spec` are mutable **only** in `draft` and `changes_requested`.
2. Publishing version *N* sets the previously published version to `archived` atomically
   (single transaction) and writes an `AuditLog` row.
3. Rollback = re-publishing an earlier `archived` version; nothing is ever destructively
   reverted.
4. Every transition appends to `AuditLog` with old/new status in `metadata`.

---

## 5. Forking & attribution

`Problem.origin` / `Course.origin` form attribution chains. Forking copies the latest
published version's content into a new container's version 1 `draft`, records
`origin`, and the OER export (see `api/openapi.yaml`, `oer-package.json`) embeds the full
chain so attribution survives export/import across instances.

---

## 6. Generic-relation targets

| Model | Allowed targets (validated in `clean()`) |
|---|---|
| DiscussionThread | Problem, Course, Lesson |
| Vote | Problem, Course, Solution, DiscussionComment |
| Bookmark | Problem, Course, Lesson |
| Report | Problem, Course, Solution, DiscussionThread, DiscussionComment, User |
| AuditLog | any |

---

## 7. `on_delete` policy summary

| Relation class | Policy | Rationale |
|---|---|---|
| Version → container | `CASCADE` | versions are meaningless without their container |
| Attribution FKs (owner, author, reviewer) | `PROTECT` | attribution is a license requirement |
| Attempt → version | `PROTECT` | learner history must not silently vanish |
| Categorisation (topic, tag via M2M) | `SET_NULL` / M2M | taxonomy is reorganisable |
| AuditLog.actor | `SET_NULL` | logs outlive accounts (GDPR-deletion keeps the row, drops the actor) |
| Profile → User | `CASCADE` | profile is an extension of the account |

---

## 8. Index strategy summary

- Unique constraints back every natural key (slugs, usernames, `(problem, version_number)`,
  `(user, course)`, `(user, lesson)`, `(user, ct, object_id)` for votes/bookmarks).
- Hot read paths: `published_version` FK lookups, `(status, created_at)` review queues,
  `(user, read_at)` notification inbox, `due_at` SRS queue, `(content_type, object_id)`
  on all generic relations.
- Postgres GIN indexes on `ProblemVersion.content` and `Tag` join tables support
  filtering until Meilisearch indexing (milestone 4) takes over full-text concerns.