Architect Database Helper

An expert database architect agent for designing data models, normalization strategies, dimensional modeling, and scalable database architectures aligned with business domains using domain-driven design principles.

When to Use This Agent

Choose Database Helper when:

• Designing new database schemas from business requirements
• Normalizing or denormalizing existing schemas for performance
• Building dimensional models for analytics and reporting
• Planning database scaling strategies (sharding, partitioning, replication)
• Evaluating schema trade-offs for different access patterns

Consider alternatives when:

• Doing operational DBA work (backups, monitoring, maintenance)
• Optimizing specific SQL queries (use a database-specific DBA agent)
• Setting up database infrastructure (use a DevOps or cloud agent)

Quick Start

Copy
# .claude/agents/architect-database-helper.yml
name: Database Architect
model: claude-sonnet-4-20250514
tools:
  - Read
  - Write
  - Bash
  - Glob
  - Grep
prompt: |
  You are a database architect. Design schemas using domain-driven
  principles, appropriate normalization levels, and scalability
  planning. Align data models with business domains. Consider
  query patterns, data volume projections, and consistency
  requirements when making design decisions.

Example invocation:

Copy
claude --agent architect-database-helper "Design a multi-tenant
  e-commerce database schema supporting product catalogs, orders,
  inventory, and customer accounts with tenant isolation at
  the row level using a tenant_id column strategy"

Core Concepts

Schema Design Process

Requirements → Domain Model → Logical Schema → Physical Schema
     │              │              │                │
  Use cases    Entities       Tables/columns   Indexes
  Queries      Relationships  Keys/constraints Partitions
  Volumes      Aggregates     Normalization    Storage config
  SLAs         Boundaries     Data types       Replication

Normalization Levels

Form	Rule	When to Use
1NF	Atomic values, no repeating groups	Always (baseline)
2NF	No partial key dependencies	OLTP with composite keys
3NF	No transitive dependencies	Standard OLTP tables
BCNF	Every determinant is a candidate key	High-integrity systems
Denormalized	Strategic redundancy	Read-heavy, analytics, caching

Multi-Tenant Data Isolation

Copy
-- Row-level isolation (shared tables)
CREATE TABLE products (
    id UUID PRIMARY KEY,
    tenant_id UUID NOT NULL,
    name VARCHAR(255),
    price DECIMAL(10,2),
    CONSTRAINT fk_tenant FOREIGN KEY (tenant_id)
        REFERENCES tenants(id)
);

-- Row-level security policy
CREATE POLICY tenant_isolation ON products
    USING (tenant_id = current_setting('app.tenant_id')::UUID);

-- Index includes tenant_id for efficient filtering
CREATE INDEX idx_products_tenant ON products(tenant_id, id);

Dimensional Model Design

           ┌──────────┐
           │ Dim Date │
           └────┬─────┘
                │
┌──────────┐   │   ┌──────────────┐
│Dim Product├───┼───┤ Fact Sales   │
└──────────┘   │   │ date_key     │
               │   │ product_key  │
┌──────────┐   │   │ customer_key │
│Dim Customer├──┘   │ store_key    │
└──────────┘       │ quantity     │
                   │ amount       │
┌──────────┐       │ cost         │
│ Dim Store├───────┘              │
└──────────┘       └──────────────┘

Configuration

Parameter	Description	Default
normalization_target	Default normalization level	3NF
naming_convention	Identifier naming standard	snake_case, singular
id_strategy	Primary key generation	UUID v7
audit_columns	Standard audit columns	created_at, updated_at
soft_delete	Include soft delete support	true (deleted_at)
tenant_strategy	Multi-tenant isolation approach	Row-level
timestamp_type	Timestamp column type	TIMESTAMPTZ

Best Practices

1. Model the domain first, optimize second. Start with a clean domain model that reflects business entities and relationships accurately. Don't let performance concerns drive the initial schema design. A correctly modeled schema that's slow can be optimized. A mismodeled schema that's fast will produce wrong results and be difficult to refactor later.

2. Use appropriate normalization for the workload. OLTP systems benefit from 3NF to prevent update anomalies and maintain data integrity. Analytics systems benefit from denormalization (star schema) to reduce join complexity. Many systems need both: normalized tables for transactional operations with materialized views or separate tables for reporting. Choose normalization level per table based on its role.

3. Design indexes for your top 10 queries, not all possible queries. Every index improves read performance for specific queries while degrading write performance for all modifications. Profile your actual query patterns, identify the 10 most frequent and most impactful queries, and create indexes that serve those patterns. A database with 50 indexes on a table has a write performance problem.

4. Plan for data growth in the initial schema. Choosing an integer primary key for a table that will exceed 2 billion rows creates a painful migration later. Using VARCHAR(50) for a field that grows to 500 characters requires schema changes. Estimate data volumes for 3 years out and size your types accordingly. Use BIGINT or UUID for primary keys, and be generous with string lengths.

5. Include standard audit columns on every table. Add created_at, updated_at, and optionally created_by and updated_by to every table from the start. These columns cost almost nothing but are invaluable for debugging data issues, building audit trails, and implementing incremental data processing. Adding them later requires migrations with default values and backfills.

Common Issues

Schema becomes difficult to evolve as the application grows. This happens when the initial design couples too many concepts into single tables. A "users" table with 80 columns spanning profile, preferences, billing, and activity data should have been split into focused tables from the start. Use domain-driven design to identify bounded contexts and create separate tables for each aggregate.

Queries slow down as data volume increases despite correct indexes. Indexes become less effective as table size grows because more index levels increase lookup depth. Implement table partitioning for large tables: range partitioning by date for time-series data, list partitioning by category for multi-tenant data. Partitioning reduces the data each query touches and makes maintenance operations faster.

Multi-tenant data leaks between tenants. Row-level security (RLS) provides defense in depth but isn't a substitute for application-level tenant filtering. Ensure every query includes a tenant filter by default through middleware or ORM configuration. Test tenant isolation explicitly: create data as tenant A, query as tenant B, verify isolation. Missing tenant filters in one query can expose all tenants' data.