api-db-design

name	api-db-design
description	Guide API contract and database design for staged software projects. Use when designing REST APIs, OpenAPI plans, database tables, SQL migration direction, seed data, initial data, error codes, pagination, permissions, audit fields, API documentation, or when reviewing whether API/database artifacts are ready for development.

API and Database Design

Purpose

Use this skill to turn requirements and architecture into implementable API and database artifacts. V1 outputs Markdown first, while preserving a path to OpenAPI and SQL migrations.

Workflow

1. Read project state and prior artifacts when available.
2. Identify bounded contexts, modules, actors, and core workflows.
3. Design API resources around business capabilities, not UI screens.
4. Define database tables from domain concepts and lifecycle state.
5. Check consistency between API fields, database fields, permissions, and error handling.
6. Read docs/01-architecture/project-structure.md or the equivalent artifact to confirm database script paths.
7. Ask whether initial/seed data is required for local verification, demos, or integration testing.
8. Produce Markdown artifacts with enough structure to later convert into OpenAPI and SQL.

API Guidance

• Prefer REST resources and clear HTTP semantics unless the project explicitly needs another style.
• Define request/response examples, validation rules, pagination, sorting, idempotency, and error codes.
• Separate external API contracts from internal implementation details.
• Include authentication and authorization expectations.
• Mark unresolved business rules as open questions.

Database Guidance

• Define table purpose, key columns, indexes, constraints, lifecycle states, and audit fields.
• Avoid premature table splitting. Split only when ownership, lifecycle, volume, or security differs.
• Record assumptions about transaction boundaries and consistency.
• For distributed systems, call out eventual consistency, idempotency keys, and retry behavior.
• After table or SQL script generation, ask whether seed data should be generated.
• Use the confirmed project structure paths by default: database scripts under scripts/db/, migrations under scripts/db/migration/, and seed data under scripts/db/seed/.

Seed Data Guidance

When the user wants initialization data, clarify:

• Whether data is for local demo, automated tests, integration testing, or production bootstrap.
• Required admin users, roles, permissions, dictionaries, sample business records, and configuration rows.
• Whether sensitive defaults must be avoided.
• Whether seed data should be idempotent.

Gate Checks

Do not mark API/database design complete until:

• Core workflows have API coverage.
• Important entities have table coverage.
• Permission-sensitive operations are identified.
• Error model is defined.
• Compatibility with selected stack is checked.
• Database migration and seed script paths are confirmed or intentionally customized.
• Initial data need is confirmed as required or not required.
• API design is explicitly approved by the user.
• Database design is explicitly approved by the user.
• Open questions are explicit.

After producing API design, stop and request explicit user review. Do not proceed to database design unless the user approves the API artifact or explicitly asks to draft both as separate unapproved drafts.

After producing database design, stop and request explicit user review. Do not generate SQL migrations, seed data, backend code, frontend code, or tests until the user approves the database artifact.

V2 Contract Path

When the user asks for structure:

• Convert API Markdown to OpenAPI.
• Convert database Markdown to SQL migration files.
• Generate idempotent seed data scripts when requested.
• Generate API examples for Apifox/Postman if the team uses them.