database

hokita's avatarfrom hokita

Database schemas, table specifications, and migration information. Use when working with database structure or queries.

⭐0stars🔀0forks📁View on GitHub🕐Updated Nov 28, 2025
[SQL and Databases]

When & Why to Use This Skill

This Claude skill provides a comprehensive technical reference for database schemas, table specifications, and migration workflows. It is designed to assist developers and data analysts in understanding complex data structures, writing precise SQL queries, and managing database versioning with ease, specifically optimized for the Fox English learning service architecture.

Use Cases

  • SQL Query Development: Quickly reference table fields like 'article_id' and 'studied_at' to write accurate queries for fetching learning content and user progress.
  • Database Migration Management: Access DDL scripts and migration history to plan, implement, and track changes to the database structure without data loss.
  • Backend Architecture Alignment: Use the repository pattern documentation to ensure MySQL and SQLite implementations remain consistent with the domain repository interfaces.
  • Data Modeling & Analysis: Understand the one-to-many relationships between articles and questions to build comprehensive reporting tools or new educational features.
nameDatabase
descriptionDatabase schemas, table specifications, and migration information. Use when working with database structure or queries.

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

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
  • api - API endpoints and data models