English · Español

Teoría 02 — Modelo de datos¶

🇪🇸 Doce tablas SQLite, ni una más. Cada una con una responsabilidad clara y una clave foránea explícita. Las migraciones son código versionado en src/miniportal/migrations/ mediante alembic; no se "modifica el schema a mano". El principio rector: el portal sirve a un solo aprendiz (learner) local hoy, pero el schema admite N sin reescritura — la diferencia entre 1 y N vive en students.id, no en el código.

Por qué SQLite, por qué ahora¶

La Fase 41 construye un portal del learner. Los datos son pequeños (un único learner, ~40 fases, cientos de entradas de journal, miles de respuestas de examen a lo largo de la vida del currículo). El despliegue es local — el portátil de Borja, servido en localhost. De ahí caen tres propiedades:

Escritores concurrentes: uno (el learner).
Total de filas tras 2 años de estudio: estimado en < 50 k entre todas las tablas.
Estrategia de copia de seguridad: copiar el archivo.

SQLite es la única opción correcta. Postgres sería sobreingeniería — un proceso servidor, un pool de conexiones, un almacén de credenciales — para una carga que SQLite resuelve en microsegundos. El portal fija sqlite3 (stdlib) para el transporte y alembic para las migraciones.

La política de migración no es negociable. Cada cambio de schema es un archivo versionado en src/miniportal/migrations/versions/<rev>_<slug>.py. Las ediciones a mano de instance/portal.db están prohibidas — rompen la reproducibilidad, la regla dura §0.5.

Las doce tablas¶

El modelo de datos tiene doce tablas. Se reparten en cuatro preocupaciones:

Identidad — students, credentials, sessions.
Estado del currículo — progress, journal_entries, notes.
Evaluación — quizzes, quiz_attempts, exam_questions, exam_responses.
Memoria — review_cards, audit_log.

Cada tabla se presenta como una sentencia CREATE TABLE en dialecto SQLite, seguida de la lista de índices y la elección de cascada de claves foráneas.

`students`¶

CREATE TABLE students (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    username        TEXT NOT NULL UNIQUE,
    display_name    TEXT NOT NULL,
    locale          TEXT NOT NULL CHECK (locale IN ('en', 'es')),
    role            TEXT NOT NULL CHECK (role IN ('student', 'admin', 'teacher')),
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_login_at   TIMESTAMP
);

CREATE UNIQUE INDEX ix_students_username ON students(username);

La columna locale persiste el idioma de la interfaz por estudiante. La columna role distingue al único admin (el propio Borja, en el MVP) de futuros estudiantes. Existe un rol teacher en el schema, pero el MVP no lo ejercita — la columna mira hacia adelante, no es aspiracional; borrarla más tarde es más difícil que añadir un CHECK ahora.

`credentials`¶

CREATE TABLE credentials (
    student_id                  INTEGER NOT NULL PRIMARY KEY,
    argon2_hash                 BLOB,
    salt                        BLOB NOT NULL,
    must_set_on_next_login      BOOLEAN NOT NULL DEFAULT 1,
    password_set_at             TIMESTAMP,
    last_changed_at             TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

argon2_hash es nullable — esa nulabilidad es la expresión a nivel de schema del flujo "sin contraseña por defecto" (teoría 03). salt es por credencial, no global; el pepper (a nivel de servidor) es ambiental y nunca aterriza aquí. La cascada es ON DELETE CASCADE: borrar a un estudiante borra la fila de credencial. Cualquier cosa más permisiva filtraría hashes.

`sessions`¶

CREATE TABLE sessions (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id      INTEGER NOT NULL,
    token_hash      BLOB NOT NULL,
    ip              TEXT,
    user_agent      TEXT,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at      TIMESTAMP NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

CREATE INDEX ix_sessions_token_hash ON sessions(token_hash);
CREATE INDEX ix_sessions_expires_at ON sessions(expires_at);

La tabla de sesiones almacena hashes de los tokens de sesión, no los tokens en sí. La cookie lleva el bearer token; el servidor hashea al recibirla y compara. Schemas más grandes usan un vault de tokens separado; para el MVP el patrón hash-en-fila basta — itsdangerous se encarga del firmado, y la fila solo guarda el hash para fines de revocación.

expires_at tiene su propio índice porque la tarea de limpieza (DELETE FROM sessions WHERE expires_at < CURRENT_TIMESTAMP) es la operación más frecuente del panel de admin.

`progress`¶

CREATE TABLE progress (
    student_id      INTEGER NOT NULL,
    phase_n         SMALLINT NOT NULL CHECK (phase_n BETWEEN 0 AND 41),
    status          TEXT NOT NULL CHECK (status IN ('not_started', 'in_progress', 'done')),
    opened_at       TIMESTAMP,
    closed_at       TIMESTAMP,
    checkpoint_json TEXT,
    PRIMARY KEY (student_id, phase_n),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

Clave primaria compuesta — la fila se identifica unívocamente por el par (estudiante, fase). checkpoint_json espeja el contenido de learners/<name>/phase-NN/checkpoint.json (convención de archivos de Fase 0) hacia la base de datos, para que el portal pueda renderizar el progreso sin parsear el sistema de archivos en cada carga de página. La versión del sistema de archivos es la canónica; la tabla es un cache desnormalizado.

`journal_entries`¶

CREATE TABLE journal_entries (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id      INTEGER NOT NULL,
    day             DATE NOT NULL,
    body_markdown   TEXT NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

CREATE INDEX ix_journal_student_day ON journal_entries(student_id, day);

La convención de learners/<name>/journal/YYYY-MM-DD.md es un archivo por día, append-only dentro del día. La tabla lo espeja: puede haber múltiples filas en journal_entries por (estudiante, día), cada una un append. Reconstruir el archivo diario significa SELECT body_markdown FROM journal_entries WHERE student_id = ? AND day = ? ORDER BY created_at, unidas por \n\n---\n\n.

`notes`¶

CREATE TABLE notes (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id      INTEGER NOT NULL,
    phase_n         SMALLINT,
    page_path       TEXT,
    body_markdown   TEXT NOT NULL,
    tags            TEXT,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);

CREATE INDEX ix_notes_student_phase ON notes(student_id, phase_n);
CREATE INDEX ix_notes_student_page ON notes(student_id, page_path);

phase_n y page_path son ambas nullables. Una nota puede ser "sobre la fase 11" (con ámbito de fase), "sobre docs/phase-15/theory/02-attention.md" (con ámbito de página), o independiente. tags es un string separado por comas — desnormalizado a propósito; el volumen es demasiado pequeño para justificar una tabla de unión note_tags.

`quizzes`¶

CREATE TABLE quizzes (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    slug            TEXT NOT NULL UNIQUE,
    phase_n         SMALLINT NOT NULL,
    body_yaml       TEXT NOT NULL
);

CREATE UNIQUE INDEX ix_quizzes_slug ON quizzes(slug);

Las definiciones de los quizzes viven en data/quizzes/*.yaml, versionadas en el repo. Un loader al arrancar las lee y hace upsert en la tabla. El YAML es la fuente canónica; la fila es un cache. Esto significa que cambiar un quiz implica editar YAML + correr migraciones + reiniciar — el mismo flujo que el resto del currículo. El contenido del quiz vive en la historia de git; se puede hacer git blame a una pregunta de quiz.

`quiz_attempts`¶

CREATE TABLE quiz_attempts (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id      INTEGER NOT NULL,
    quiz_id         INTEGER NOT NULL,
    started_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at    TIMESTAMP,
    score_pct       REAL,
    answers_json    TEXT,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (quiz_id) REFERENCES quizzes(id) ON DELETE RESTRICT
);

La elección de cascada aquí es deliberada. ON DELETE CASCADE para el estudiante (borrar a Borja elimina sus intentos) pero ON DELETE RESTRICT para el quiz (no se puede borrar un quiz que tiene intentos — soft-delete vía una columna deprecated_at en quizzes es el arreglo futuro cuando haga falta).

`exam_questions`¶

CREATE TABLE exam_questions (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    slug            TEXT NOT NULL UNIQUE,
    phase_n         SMALLINT NOT NULL,
    prompt          TEXT NOT NULL,
    answer_key      TEXT NOT NULL,
    rubric          TEXT
);

CREATE UNIQUE INDEX ix_exam_questions_slug ON exam_questions(slug);

Las preguntas de examen son la evaluación de alto riesgo, distinta de los quizzes. La Fase 30 (respuestas de examen estructuradas) define el formato de la rúbrica. La columna rubric es una especificación de evaluador estructurado codificada en JSON — véase la teoría de Fase 30 para el schema. La capstone del tutor de gramática (Fase 32 bajo §A13) usa preguntas de examen del tipo "conjuga el verbo eat en past simple, 3^rd person singular" con answer_key = "ate".

`exam_responses`¶

CREATE TABLE exam_responses (
    id                          INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id                  INTEGER NOT NULL,
    exam_question_id            INTEGER NOT NULL,
    response_text               TEXT NOT NULL,
    correct                     BOOLEAN NOT NULL,
    correct_at_first_try        BOOLEAN NOT NULL,
    attempted_at                TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    feedback                    TEXT,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (exam_question_id) REFERENCES exam_questions(id) ON DELETE RESTRICT
);

CREATE INDEX ix_exam_responses_student ON exam_responses(student_id);
CREATE INDEX ix_exam_responses_question ON exam_responses(exam_question_id);

correct_at_first_try es la columna que abre la puerta a la repetición espaciada (spaced repetition) (teoría 05). Una respuesta incorrecta en cualquier intento de reintento siembra una fila en review_cards. Una respuesta correcta a la primera nunca entra a la repetición espaciada — ya se sabe.

`review_cards`¶

CREATE TABLE review_cards (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id          INTEGER NOT NULL,
    exam_question_id    INTEGER NOT NULL,
    ease                REAL NOT NULL DEFAULT 2.5,
    interval_days       INTEGER NOT NULL DEFAULT 1,
    due_on              DATE NOT NULL,
    last_reviewed_at    TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (exam_question_id) REFERENCES exam_questions(id) ON DELETE RESTRICT
);

CREATE UNIQUE INDEX ix_review_cards_student_question
    ON review_cards(student_id, exam_question_id);
CREATE INDEX ix_review_cards_due ON review_cards(student_id, due_on);

El estado de SM-2 se captura en tres columnas: ease (el factor de facilidad, empieza en 2.5), interval_days (el número de días hasta la próxima revisión) y due_on (hoy + intervalo). La teoría 05 deriva la regla de actualización. El índice único (student_id, exam_question_id) impone el invariante de que una pregunta fallida produce una tarjeta por estudiante, no una por fallo.

`audit_log`¶

CREATE TABLE audit_log (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id      INTEGER,
    action          TEXT NOT NULL,
    details_json    TEXT NOT NULL,
    at              TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE SET NULL
);

CREATE INDEX ix_audit_log_student ON audit_log(student_id);
CREATE INDEX ix_audit_log_at ON audit_log(at);

El log de auditoría registra acciones relevantes para la seguridad: login_success, login_failure, password_set, password_reset_requested, quiz_submitted, exam_submitted, admin_create_student. student_id es nullable y usa ON DELETE SET NULL — la fila de auditoría sobrevive a la eliminación del estudiante (forense crítica) pero pierde el enlace FK. La amenaza T8 de la Fase 37 (almacenamiento de trazas como vector de inyección) gobierna el contenido de details_json; el filtro antiinyección se aplica antes del insert.

Política de migración vía alembic¶

Los cambios de schema siguen esta disciplina:

Editar src/miniportal/models.py (los modelos declarativos de SQLAlchemy).
Ejecutar alembic revision --autogenerate -m "add review_cards.ease_floor" — produce un archivo de migración versionado.
Inspeccionar el SQL generado. Corregir a mano lo que el autogenerate hiciera mal (por ejemplo, no detecta cambios en CHECK constraints).
Ejecutar alembic upgrade head contra una copia de los datos de producción. Verificar.
Hacer commit del archivo de migración y del cambio de modelo en un único commit.

Los downgrades se escriben pero rara vez se usan; la política es "arreglar hacia adelante, no hacer rollback". Una migración que resulta estar mal se corrige con una nueva migración, no con alembic downgrade. La DB es lo bastante pequeña como para que incluso migraciones destructivas puedan probarse contra una copia en segundos.

Resumen de elecciones de cascada¶

Padre	Hijo	Al borrar padre	Razón
`students`	`credentials`	CASCADE	Una credencial sin dueño carece de sentido.
`students`	`sessions`	CASCADE	Igual.
`students`	`progress`	CASCADE	Igual.
`students`	`journal_entries`	CASCADE	Igual.
`students`	`notes`	CASCADE	Igual.
`students`	`quiz_attempts`	CASCADE	Igual.
`students`	`exam_responses`	CASCADE	Igual.
`students`	`review_cards`	CASCADE	Igual.
`students`	`audit_log`	SET NULL	Supervivencia forense.
`quizzes`	`quiz_attempts`	RESTRICT	Los quizzes no se borran; se deprecan vía columna.
`exam_questions`	`exam_responses`	RESTRICT	Igual; preservar el historial.
`exam_questions`	`review_cards`	RESTRICT	Igual.

Lo que este schema NO modela¶

Multi-tenant orgs. No hay tabla organizations. El portal es de una sola organización (el currículo es la organización). Añadir orgs es una migración futura si/cuando el portal se hospede.
Roles más allá de tres. student/admin/teacher basta. Las tablas RBAC se posponen.
Soft deletes. Todo es hard delete, controlado por cascadas FK. El soft-delete solo se vuelve necesario a una escala que la Fase 41 no anticipa.
Contenido versionado. Los quizzes y las preguntas de examen no están versionados en la DB; git maneja el versionado. Las columnas son la versión actual.

Recapitulación en un párrafo¶

Doce tablas, SQLite, migraciones alembic, elecciones explícitas de cascada, claves primarias tipadas como FK, columnas JSON para datos genuinamente anidados (details_json, answers_json, checkpoint_json) y solo esas. El schema admite N learners porque students.id es la clave foránea en todas partes; el despliegue es de un solo learner solo por convención. La siguiente teoría cubre el ciclo de vida de las credentials y el contrato del cifrado en reposo del minivault.

Siguiente: theory/03-auth-and-vault.md — parámetros de Argon2id, sin contraseña por defecto, minivault AES-GCM, cookies de sesión.

Teoría 02 — Modelo de datos¶

Por qué SQLite, por qué ahora¶

Las doce tablas¶

students¶

credentials¶

sessions¶

progress¶

journal_entries¶

notes¶

quizzes¶

quiz_attempts¶

exam_questions¶

exam_responses¶

review_cards¶

audit_log¶