moai-domain-db-docs

name	moai-domain-db-docs
description	Parses DB migration files (Prisma, Alembic, Rails, raw SQL) and keeps .moai/project/db/schema.md, erd.mmd, migrations.md in sync. Powers the PostToolUse hook and /moai db refresh/verify subcommands.
license	Apache-2.0
compatibility	Designed for Claude Code
allowed-tools	Read, Write, Edit, Grep, Glob, Bash, TaskCreate, TaskUpdate
user-invocable	false
metadata	{"version":"1.0.0","category":"domain","status":"active","updated":"2026-04-20","tags":"db, schema, migration, documentation, sync, drift"}
triggers	{"keywords":["db docs","schema sync","migration parse","erd update"],"agents":["expert-backend"],"phases":["run","sync"]}

moai-domain-db-docs: DB Documentation Sync Skill

Purpose: Consume .moai/cache/db-sync/proposal.json and update the three DB documentation files (schema.md, erd.mmd, migrations.md) in .moai/project/db/.

SPEC: SPEC-DB-SYNC-001

---

Invocation Modes

This skill is invoked in two ways:

1. PostToolUse approval flow (via orchestrator): After the user selects "Apply" in the 3-option AskUserQuestion dialog, the orchestrator invokes this skill with proposal.json.

2. Direct subcommand: Via /moai db refresh (full rebuild) or /moai db verify (drift check).

---

Recursion Guard (REQ-019)

Before writing any file, check that the target path is NOT in the Excluded Patterns:

• .moai/project/db/**
• .moai/cache/**
• .moai/logs/**

If the target matches an excluded pattern, abort silently. This is a skill-level guard complementing the hook-level guard (REQ-004).

---

Phase A: Apply Proposal (REQ-013, REQ-016, REQ-017, REQ-018)

Triggered when user selects "Apply" in the approval flow.

A1: Read proposal.json

Read .moai/cache/db-sync/proposal.json

Extract:

• file_path: original migration file
• parsed_content: normalized schema representation
• timestamp: ISO-8601 creation time

A2: Update schema.md (REQ-016)

Rules for in-place update:

1. Read existing .moai/project/db/schema.md.
2. Identify sections delimited by ## headers.
3. For each table/collection referenced in parsed_content:
   • If a matching row exists, update column_count and last_migration_file.
   • If no matching row, append a new row preserving _TBD_ in user-managed columns.
4. Do NOT overwrite user-edited descriptions or _TBD_ markers.
5. Write updated file using Edit tool.

A3: Regenerate erd.mmd (REQ-017)

Rules:

1. Read existing .moai/project/db/erd.mmd to extract the comment header block (lines starting with %%).
2. Rebuild the erDiagram body from parsed_content table definitions.
3. Preserve the original comment header verbatim.
4. Validate Mermaid syntax: must start with erDiagram after the comment header.
5. Write using Edit tool (or Write if file is new).

Template output format:

%% MoAI DB ERD — auto-generated. Edit schema.md to add relationships.
%% Last updated: <ISO-8601 timestamp>
erDiagram
    <TABLE_NAME> {
        <type> <column_name>
    }

A4: Append to migrations.md (REQ-018)

1. Read existing .moai/project/db/migrations.md.
2. Locate the ## Applied Migrations table.
3. Append a new row with:
   • filename: basename of file_path
   • applied_at: ISO-8601 timestamp from proposal
   • checksum: SHA-256 of the migration file content (compute via sha256sum or shasum -a 256)
   • up_summary: first non-comment line of parsed_content (truncated to 80 chars)
4. Write using Edit tool.

---

Phase B: User Approval Flow (REQ-012, REQ-014, REQ-015)

This phase documents the orchestrator-level behavior that invokes this skill. The orchestrator (not this skill) calls AskUserQuestion.

B1: 3-Option AskUserQuestion

When proposal.json is present and decision == "ask-user", the orchestrator presents:

Question: "Migration file changed. Update schema documentation?"

Options:

1. Apply (권장) — Apply proposed schema update: invoke moai-domain-db-docs to update schema.md, erd.mmd, and migrations.md from the parsed migration file.
2. Review — Review diff first: display the diff between current schema.md and proposed changes, then re-ask Apply/Skip.
3. Skip — Skip this time: delete proposal.json and take no action.

B2: Review diff (REQ-014)

If user selects "Review":

1. Read current .moai/project/db/schema.md.
2. Generate a unified diff between current content and proposed content from parsed_content.
3. Display diff (truncate to 100 lines; add note "see proposal.json for full diff" if truncated).
4. Re-ask with 2 options: Apply / Skip.

B3: Skip (REQ-015)

If user selects "Skip":

1. Delete .moai/cache/db-sync/proposal.json.
2. Output: "Schema update skipped."

---

Phase C: /moai db verify (REQ-020, REQ-021, REQ-022)

Triggered by /moai db verify. Read-only — MUST NOT modify any files.

C1: Compute expected schema

1. Scan migration files using patterns from db.yaml migration_patterns.
2. Extract table names from each migration file (stub: grep for CREATE TABLE).
3. Build expected set E of table names.

C2: Read current schema.md

1. Read .moai/project/db/schema.md.
2. Extract registered table names from the ## Tables section.
3. Build documented set D of table names.

C3: Diff and output

Compute symmetric difference: drift = (E - D) ∪ (D - E)

If no drift:

• Print: Schema documentation is in sync
• Exit 0 (REQ-022)

If drift detected:

• Print unified diff to stdout showing added/removed tables (REQ-021)
• Exit 1 (REQ-021)

Format:

--- schema.md (documented)
+++ migrations (actual)
@@ -1,N +1,M @@
+ <table added in migrations, missing from schema.md>
- <table in schema.md, missing from migrations>

---

Phase D: /moai db refresh (REQ-023, REQ-024)

Triggered by /moai db refresh. Full rebuild from all migration files.

D1: User confirmation (REQ-024)

Call AskUserQuestion:

Question: "Confirm full rebuild of schema.md, erd.mmd, and migrations.md?"

Options:

1. Apply (권장) — Rebuild all 3 docs from scratch by rescanning all migration files.
2. Cancel — Cancel the rebuild. No files will be modified.

If Cancel: exit 0 with message "Refresh cancelled."

D2: Full scan

1. Use Glob to find all migration files matching patterns from db.yaml.
2. Parse each file using the stub parser (read content).
3. Aggregate all table definitions.

D3: Rebuild 3 docs

Rebuild in order:

1. schema.md — regenerate table registry from aggregated tables.
2. erd.mmd — regenerate erDiagram from aggregated tables.
3. migrations.md — regenerate Applied Migrations table with all found files.

Use Write tool (not Edit) since this is a full rebuild.

Output summary:

Rebuilt schema.md: <N> tables
Rebuilt erd.mmd: <N> entities
Rebuilt migrations.md: <N> migration entries

---

Excluded Patterns Reference

Do NOT write to or process these paths (recursion guard, REQ-019):

• .moai/project/db/**
• .moai/cache/**
• .moai/logs/**

---

Error Handling

• If any file read/write fails: log to .moai/logs/db-sync-errors.log and continue with remaining files.
• If SHA-256 computation fails: use unknown as checksum value.
• If erDiagram validation fails: write a minimal valid erDiagram {} and log the error.
• Never block the user's workflow (always exit 0 on non-fatal errors).