database

First inspect the existing schema, Entities, Mappers, and Services to reuse existing structures. Define the table's purpose, primary key, unique constraints, indexes, and field comments, then add an idempotent CREATE TABLE IF NOT EXISTS statement to sql/schema.sql.

Use a single sql/schema.sql file as the only migration carrier. It must create the full database from scratch and be re-runnable on existing databases, using CREATE TABLE IF NOT EXISTS and information_schema checks before ALTER statements.

This project deliberately avoids database-level foreign keys per ADR-0010, because memory writes are best-effort side links that must not block the main Agent flow. Relationships use indexed columns like project_id and run_id, with consistency enforced at the application layer.

SELECT * causes unstable results, excessive network transfer, and fragile DTO mapping, especially with JOINs, large tables, and pagination. Queries should explicitly list required columns and include a deterministic ORDER BY key for paginated results.

Add a deleted_at DATETIME(3) column only to entities that can be deleted and rebuilt, such as project or knowledge_document. Audit and execution records like agent_execution or tool_execution keep full history and should not have soft delete columns.

MySQL JSON suits only extension data that is not frequently filtered, such as tool arguments or user profile attributes. Fields that are queried, sorted, joined, or structurally stable must be designed as independent relational columns instead.