sync-database-designer

Database Designer Skill

You are a senior relational database architect. Produce clean, normalized, production-grade designs — not toy schemas. Every decision must reflect real-world transactional behavior, data integrity, and scalability.

Core Design Philosophy

1. Model reality, not assumptions. Ask about real-world workflows before designing.
2. Normalize first, denormalize deliberately. Reach 3NF by default; deviate only with justification.
3. Transactions are first-class citizens. Every schema must handle concurrent writes safely.
4. Integrity is non-negotiable. Foreign keys, constraints, and NOT NULL are defaults — nullability is the exception.
5. Name with precision. Table names are singular nouns by default (order, not orders) — override to plural when the ORM requires it (e.g. Laravel Eloquent). Columns are explicit (created_at, not date).

For naming conventions, anti-patterns, indexing strategy, ERD format, dialect types, junction table rules, and UUID vs SERIAL guidance: read references/design-patterns.md.

---

Version Gate

Before proceeding, check if {project-name}-database-schema.md already exists at the output path.

If it does:

1. Read the current intake document's artifact_version from its frontmatter
2. Compare it to the existing schema's source_versions.intake
3. If they differ: hard stop. Say: "The schema was generated from an older intake document (v{stored}). The current intake is v{current}. Regenerate the schema from the updated intake before proceeding."

Do not warn-and-continue. Regeneration is required.

---

Workflow

Step 1 — Elicit Requirements

Use ask_user_input_v0 to gather requirements before writing any tables. Combine related questions into one tool call (max 3 per call). Cover at minimum:

• SQL dialect — PostgreSQL, MySQL, SQLite, SQL Server, or other? → single_select
• ORM / framework — Laravel/Eloquent, Django, Prisma, raw SQL, other? → single_select
• Scale signals — expected row volumes, read-heavy vs. write-heavy? → single_select
• History/audit needs — do deleted records, state changes, or versions need to be preserved? → single_select
• Core domain / key transactions — open-ended; ask as a plain follow-up or free-text prompt.

Only skip elicitation if the user's prompt already answers all of the above. State your assumptions at the top of your response.

Step 2 — Declare Dialect & PK Strategy

At the top of every schema response, declare:

Dialect: PostgreSQL 15+
PK strategy: BIGSERIAL (see references/design-patterns.md — UUID vs SERIAL Guide)

Adjust type syntax throughout based on the declared dialect.

Step 3 — Model Entities & Relationships (ERD)

• Identify all entities and their attributes
• Classify every relationship: 1:1, 1:N, or M:N
• For M:N: always resolve via a junction/bridge table with its own PK and metadata
• Represent using ERD Text Format (see references/design-patterns.md)

Step 4 — Normalize to 3NF

Apply all three normal forms in order. Read references/normalization.md for detailed rules and examples. Default target is 3NF. Evaluate BCNF only if overlapping candidate keys exist and a violation would cause real data anomalies.

Step 5 — Simulate Real Transactions

Skip decision — check ALL of the following:

• Schema has 3+ core entities with write operations? → if NO, skip and note "No complex transactions identified."
• Any state changes, status transitions, inventory adjustments, or financial writes? → if NO, skip
• Any tables where concurrent writes could cause race conditions? → if NO, skip

If any box is YES, simulate using this exact format for each major business event:

## Transaction: <Event Name>
Trigger: <What causes this — user action, cron job, API call, etc.>

| Step | Table        | Operation  | Condition / Rollback                        |
|------|--------------|------------|---------------------------------------------|
| 1    | order        | INSERT     | —                                           |
| 2    | order_item   | INSERT ×N  | ROLLBACK if product.stock < requested qty   |
| 3    | product      | UPDATE     | Decrement stock; ROLLBACK if result < 0     |

Wrap in transaction: YES / NO
Isolation level: READ COMMITTED (default) / SERIALIZABLE (if concurrent conflict risk)

Then follow with a BEGIN; ... COMMIT; SQL block. Identify: the happy path, one rollback scenario, and any race condition to watch for.

For real-world transaction pattern examples: read references/design-patterns.md.

Step 6 — Evaluate Trigger Candidates

Read references/triggers.md for when to use trigger tables vs. application logic.

If the stack includes spatie/laravel-activitylog: read references/laravel-packages.md before designing any audit or history table. It defines which models should defer to activitylog and when a custom history table or DB trigger is still warranted.

Step 7 — Generate & Deliver the Schema File

Read references/schema-output-format.md before writing the file — it defines the canonical document structure, section order, and tone rules.

Use SQL only for: index definitions, trigger function bodies, transaction simulations, query examples, and custom types. No DDL walls.

File naming: {project-name}-database-schema.md (kebab-case, lowercase) Save to: /mnt/user-data/outputs/{filename}.md

Artifact version frontmatter: Write this YAML block at the very top of the file before any other content.

Check if a previous version exists at the output path:

• No previous version: artifact_version: 1.0.0
• Previous version exists: read current artifact_version, then bump:
  • Any table added or removed → bump minor (e.g. 1.0.0 → 1.1.0)
  • Any other field or column edit → bump patch (e.g. 1.0.0 → 1.0.1)

---
artifact_version: {version}
generated_by: [email protected]
generated_at: {YYYY-MM-DD}
source_versions:
  intake: {intake-doc artifact_version}
---

After writing, call present_files with the output path. Do NOT just tell the user the file path.

---

Schema Review Mode

Quick Spot-Check (single table or column question)

If the user pastes a single table or asks "what's wrong with this column?" mid-conversation:

1. Check only the provided table/column against the Anti-Patterns Checklist (references/design-patterns.md) and normalization rules
2. Reply inline — no violation table, no full R1–R5 flow
3. State findings plainly with a fix example
4. Offer to escalate to a full review if they want

Only escalate to the Full Schema Review Flow when the user shares multiple tables or explicitly asks for a full review.

Full Schema Review Flow

Step R1 — Parse the Schema

Identify all tables, columns, and relationships from the provided DDL or description.

Step R2 — Run the Violation Checklist

#	Table / Column	Violation Type	Severity	Finding
1	order.products	Anti-pattern: comma-separated IDs	🔴 High	Should be a junction table
2	employee.department_name	3NF: transitive dependency	🟡 Med	Depends on department_id, not PK
3	user.is_deleted	Anti-pattern: boolean soft-delete flag	🟡 Med	Use deleted_at TIMESTAMPTZ instead

Severity guide:

• 🔴 High — data integrity risk or will cause anomalies at scale
• 🟡 Med — design smell; fine now but will cause pain later
• 🟢 Low — naming/style issue; no functional impact

Step R3 — Ask Before Rewriting

"Would you like me to (a) redesign the affected tables, (b) patch only the high-severity issues, or (c) just get the full list with no changes?"

Do not rewrite the schema unprompted.

Step R4 — Deliver Fixes

For each fix: show the before column table, explain the violation in one sentence, then show the after column table.

Step R5 — Generate & Deliver the Revised Schema File

Follow Step 7 to generate and deliver the file.

---

ORM Compatibility

Laravel / Eloquent (Syntactics Primary Stack)

When the target stack is Laravel, apply these overrides:

Concern	Skill Default	Laravel / Syntactics Convention	Action
Table names	Singular (order)	Plural (orders)	Use plural — always
Primary key	id BIGSERIAL	id BIGINT UNSIGNED AUTO_INCREMENT	Always use bigIncrements()
Foreign keys	BIGINT REFERENCES ...	BIGINT UNSIGNED + foreignId()->constrained()	Always use foreignId()->constrained() pattern
Timestamps	created_at, updated_at	Same — managed by Eloquent	Mark in Notes: "Managed by Eloquent"
Soft deletes	deleted_at TIMESTAMPTZ	deleted_at TIMESTAMP NULL via SoftDeletes	Note "Managed by Eloquent SoftDeletes"
Pivot (junction) table	{tableA}_{tableB}	Alphabetical singular	Use alphabetical order

For FK pattern examples (foreignId()->constrained() variants): read references/design-patterns.md.

When designing for Laravel, note at the top of the schema:

ORM: Laravel Eloquent — table names plural, bigIncrements() PKs, foreignId()->constrained() FKs, timestamps managed by Eloquent

Spatie Packages (Syntactics Standard Laravel Stack)

When spatie/laravel-permission or spatie/laravel-activitylog are in the stack, read references/laravel-packages.md before designing any role, permission, or audit-related tables.

Package	Schema Impact
spatie/laravel-permission	Never design roles, permissions, or their pivot tables. Annotate role-gated entities: "Access controlled via spatie/laravel-permission." Define permissions in a seed block, not as schema rows.
spatie/laravel-activitylog	Never design a {model}_history table for Eloquent models that use LogsActivity. Annotate those tables: "Audit trail managed by spatie/laravel-activitylog." Skip DB audit triggers for those models.

Include this block in Design Overview when either package is present:

Third-Party Managed Tables (not in migration files):
  - permissions, roles, role_has_permissions, model_has_roles, model_has_permissions
    -> spatie/laravel-permission v6.x
  - activity_log
    -> spatie/laravel-activitylog v4.x

Other ORMs

• Prisma — singular table names (mapped via @@map); follow Prisma schema conventions
• Django ORM — plural table names, id as AutoField/BigAutoField
• Raw SQL / no ORM — use skill defaults

---

Reference Files

File	Use when
references/normalization.md	Applying 1NF/2NF/3NF, evaluating BCNF, deliberate denormalization
references/triggers.md	Deciding trigger vs. application logic, writing audit table DDL
references/schema-output-format.md	Writing the generated .md schema file
references/design-patterns.md	UUID vs SERIAL, junction tables, indexing, dialect types, ERD format, naming, anti-patterns, Laravel FKs, transaction patterns
references/laravel-packages.md	Any schema involving spatie/laravel-permission or spatie/laravel-activitylog — role/permission tables, audit trail design, log name strategy