Database Reference

Database schema and specifications for the Fox English learning service.

Database System

• Production: MySQL
• Testing: SQLite (in-memory via better-sqlite3)
• Configuration: api/src/infrastructure/database/config.ts

Documentation

• Table Schemas - Detailed table structure and field descriptions
• DDL Script - Complete SQL schema definition
• Migrations - Database migration scripts

Tables

Articles

Stores English learning articles and their metadata.

Key Fields:

• id (CHAR(36)) - UUID primary key
• title (VARCHAR(255)) - User-editable article title
• url (VARCHAR(2048)) - Article source URL
• body (TEXT) - Article content
• memo (TEXT) - User's personal notes
• studied_at (DATETIME) - When the article was/will be studied
• created_at, updated_at - Timestamps

Questions

Stores comprehension questions related to articles.

Key Fields:

• id (CHAR(36)) - UUID primary key
• article_id (CHAR(36)) - Foreign key to articles
• sort (INT) - Display order
• body (TEXT) - Question text
• answer (TEXT) - Answer text
• created_at, updated_at - Timestamps

Relationship: Each question belongs to one article (many-to-one)

Repository Pattern

• Interface: api/src/domain/repositories/ArticleRepository.ts
• MySQL Implementation: api/src/infrastructure/repositories/MySQLArticleRepository.ts
• SQLite Implementation (Testing): api/src/__tests__/helpers/SQLiteArticleRepository.ts

Related Skills

• api - API endpoints and data models