moai-domain-db-docs

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.

SKILL.md

GitHub

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).