| name | database-architect |
| description | Use when designing a database from scratch, reviewing an existing schema, planning a migration, or architecting a high-availability database topology. Dispatches schema-designer, query-optimizer, migration-planner, and replication-specialist in sequence, then synthesizes findings into a final consistency and risk report via data-reviewer. Covers relational schema design (PostgreSQL/MySQL), indexing strategy, zero-downtime migrations, HA replication, and referential integrity review. Does NOT cover NoSQL document databases, time-series databases, data warehousing (use a dedicated OLAP harness), or application-layer ORM configuration. Also triggers on: redesign database schema, add read replicas, plan schema migration, review database architecture, optimize slow queries. |
| metadata | {"category":"harness","harness":"19-database-architect","agent_type":"general-purpose"} |
Database Architect — Database Design Pipeline
A five-specialist pipeline models the schema, optimizes queries, plans migrations, designs high-availability replication, and delivers a final consistency and risk report.
Execution Mode
File-Bus Pipeline — Agents run sequentially; each specialist writes output and a handoff message that the next specialist reads. All artifacts land in _workspace/.
Agent Composition
| Agent | File | Role | Type |
|---|
| schema-designer | agents/schema-designer.md | Data modeling, normalization, DDL | general-purpose |
| query-optimizer | agents/query-optimizer.md | Index strategy, query templates, EXPLAIN guidance | general-purpose |
| migration-planner | agents/migration-planner.md | Migration scripts, zero-downtime, rollback | general-purpose |
| replication-specialist | agents/replication-specialist.md | HA topology, failover, connection pooling | general-purpose |
| data-reviewer | agents/data-reviewer.md | Final review: integrity, consistency, risk report | general-purpose |
Pre-Flight Checks
Workspace Layout
Write output to _workspace/ so downstream agents can read findings without additional tool calls — the file bus is the only shared state in this harness.
_workspace/
├── 00_input.md (domain description, scale requirements, tech stack)
├── 01_schema_design.md (schema-designer output)
├── 02_query_patterns.md (query-optimizer output)
├── 03_migration_plan.md (migration-planner output)
├── 04_replication_design.md (replication-specialist output)
├── 05_review_report.md (data-reviewer output — terminal)
└── messages/
├── schema-designer-to-query-optimizer.md
├── query-optimizer-to-migration-planner.md
├── migration-planner-to-replication-specialist.md
└── replication-specialist-to-data-reviewer.md
Phase 1: Setup (Orchestrator)
The synthesis phase resolves conflicts between specialist findings. Individual analysts may surface the same constraint issue at different severity levels; the data-reviewer applies a consistent severity framework and eliminates duplicates.
task(agent_type="general-purpose",
description="Read the user's database design request. Create _workspace/ and _workspace/messages/ directories. Extract: DOMAIN (what the database supports), SCALE (row volumes, concurrent users, read/write ratio), TECH_STACK (PostgreSQL/MySQL version, cloud provider), EXISTING_SCHEMA (any existing DDL or ER description), QUERY_PATTERNS (known access patterns), HA_REQUIREMENTS (uptime SLA, RTO/RPO). Write organized input to _workspace/00_input.md with those section headers. If any field is missing, apply the most common pattern and document the assumption.")
Phase 2: Sequential Pipeline
Step 2.1 — Schema Designer
task(agent_type="general-purpose",
description="You are the schema-designer agent in the database-architect harness. Read agents/schema-designer.md for your full instructions. Read _workspace/00_input.md. Design the complete relational schema: identify entities, apply normalization (1NF through BCNF), generate CREATE TABLE DDL with correct data types and constraints, choose PK strategy (serial vs UUID), apply audit columns and soft delete where appropriate. Write output to _workspace/01_schema_design.md. Write handoff to _workspace/messages/schema-designer-to-query-optimizer.md with: STATUS: COMPLETE, FINDINGS: [list of tables created, notable decisions], CROSS_DOMAIN_FOR_QUERY_OPTIMIZER: [high-cardinality columns, join-heavy relationships, expected WHERE/ORDER BY columns], CROSS_DOMAIN_FOR_MIGRATION_PLANNER: [large tables, columns needing NOT NULL backfill].")
Step 2.2 — Query Optimizer (reads handoff from 2.1)
task(agent_type="general-purpose",
description="You are the query-optimizer agent in the database-architect harness. Read agents/query-optimizer.md for your full instructions. Read _workspace/00_input.md and _workspace/01_schema_design.md. Read _workspace/messages/schema-designer-to-query-optimizer.md for the CROSS_DOMAIN_FOR_QUERY_OPTIMIZER section. Design the complete index strategy: classify index types (B-tree/Hash/GiST/GIN/BRIN), write CREATE INDEX DDL with CONCURRENTLY noted, produce query templates for primary access patterns, flag slow query anti-patterns. Write output to _workspace/02_query_patterns.md. Write handoff to _workspace/messages/query-optimizer-to-migration-planner.md with: STATUS: COMPLETE, FINDINGS: [indexes recommended, query templates defined, performance concerns], CROSS_DOMAIN_FOR_MIGRATION_PLANNER: [indexes requiring CONCURRENTLY, large tables with slow index creation, backfill column notes], CROSS_DOMAIN_FOR_DATA_REVIEWER: [missing FK indexes, low-selectivity columns].")
Step 2.3 — Migration Planner (reads handoff from 2.2)
task(agent_type="general-purpose",
description="You are the migration-planner agent in the database-architect harness. Read agents/migration-planner.md for your full instructions. Read _workspace/00_input.md, _workspace/01_schema_design.md, and _workspace/02_query_patterns.md. Read _workspace/messages/query-optimizer-to-migration-planner.md for the CROSS_DOMAIN_FOR_MIGRATION_PLANNER section. Classify all schema changes as additive/destructive/structural. Produce numbered migration script outlines (Flyway convention), apply expand-contract for zero-downtime changes, design batched backfill for large tables, write rollback procedures. Write output to _workspace/03_migration_plan.md. Write handoff to _workspace/messages/migration-planner-to-replication-specialist.md with: STATUS: COMPLETE, FINDINGS: [migration count, highest-risk migration, irreversible steps], CROSS_DOMAIN_FOR_REPLICATION_SPECIALIST: [DDL affecting replication, high-WAL operations, connection pool sizing during migration], CROSS_DOMAIN_FOR_DATA_REVIEWER: [columns added without NOT NULL, tables in temporary inconsistent state].")
Step 2.4 — Replication Specialist (reads handoff from 2.3)
task(agent_type="general-purpose",
description="You are the replication-specialist agent in the database-architect harness. Read agents/replication-specialist.md for your full instructions. Read _workspace/00_input.md, _workspace/01_schema_design.md, _workspace/02_query_patterns.md, and _workspace/03_migration_plan.md. Read _workspace/messages/migration-planner-to-replication-specialist.md for the CROSS_DOMAIN_FOR_REPLICATION_SPECIALIST section. Design the HA topology (primary/standby/replica count), choose replication mode (sync/async with justification), specify failover mechanism (Patroni or pg_auto_failover) with fencing strategy, configure PgBouncer pool sizes from first principles, define read routing rules per query class, document WAL archiving and PITR. Write output to _workspace/04_replication_design.md. Write handoff to _workspace/messages/replication-specialist-to-data-reviewer.md with: STATUS: COMPLETE, FINDINGS: [topology, failover mechanism, pool config summary], CROSS_DOMAIN_FOR_DATA_REVIEWER: [async replication consistency windows, tables with eventual-consistency risk, connection pool constraint surfacing risks, remaining single points of failure].")
Phase 3: Final Review
Step 3.1 — Data Reviewer (terminal agent)
task(agent_type="general-purpose",
description="You are the data-reviewer agent in the database-architect harness. Read agents/data-reviewer.md for your full instructions. Read ALL of: _workspace/00_input.md, _workspace/01_schema_design.md, _workspace/02_query_patterns.md, _workspace/03_migration_plan.md, _workspace/04_replication_design.md, and all 4 message files in _workspace/messages/. Perform the final review: audit every FK for an index on the referencing column, trace all ON DELETE CASCADE chains, check NOT NULL coverage and UNIQUE constraints, verify FK/PK type matches, flag replication consistency risks from the replication-specialist message. De-duplicate findings across all specialist outputs. Assign severity (Critical/Recommended/Informational). Write final prioritized report to _workspace/05_review_report.md with Executive Summary (overall risk rating, verdict), full findings, referential integrity table, constraint coverage table, cascade risk map, and recommended remediation order.")
Scale Modes
| Request Pattern | Mode | Agents Used |
|---|
| Full database design request | Full Pipeline | All 5 agents |
| "Schema design only" | Schema Mode | schema-designer → data-reviewer |
| "Review existing schema" | Review Mode | data-reviewer (reads schema from input) |
| "Migration plan only" | Migration Mode | migration-planner → data-reviewer |
| "Index optimization" | Performance Mode | query-optimizer → data-reviewer |
| "HA / replication setup" | HA Mode | replication-specialist → data-reviewer |
Error Handling
| Error Type | Strategy |
|---|
| Agent output file missing | Re-run agent once; data-reviewer notes "unavailable" for that domain |
| Ambiguous input (domain unclear) | Apply most common e-commerce/SaaS pattern; document assumptions in 00_input.md |
| Conflicting findings (e.g., schema vs performance trade-off) | data-reviewer resolves with business-impact priority; escalate only if truly unresolvable |
| No existing schema provided | schema-designer generates from scratch based on domain description |
| Technology stack not specified | Default to PostgreSQL 15; document assumption |
_workspace/ already contains files | Confirm with user or append -2 suffix; document in final report |
Test Scenarios
- Normal case: Full domain description with scale requirements and tech stack → all 5 agents produce output → final report with prioritized findings
- Review only: Existing DDL provided, no migration needed → schema-designer + data-reviewer
- Error case: query-optimizer output missing → data-reviewer notes "index strategy unavailable; FK index review based on schema only"
