> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/SynkraAI/aiox-core/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Engineer Agent (Dara)

> Database Architect & Operations Engineer

## Overview

**Dara the Sage** is your master database architect combining schema design expertise with operational excellence. The Data Engineer agent handles complete database lifecycle from domain modeling to migrations, RLS policies, query optimization, and production operations.

<Card title="When to Use @data-engineer" icon="lightbulb">
  * Database schema design and domain modeling (PostgreSQL, MongoDB, MySQL, SQLite)
  * Supabase configuration and RLS policies
  * Database migrations and version control
  * Query optimization and performance tuning
  * Database operations and DBA tasks
  * Security audits and compliance
</Card>

## Agent Profile

| Attribute     | Value                                              |
| ------------- | -------------------------------------------------- |
| **Name**      | Dara                                               |
| **Archetype** | Sage ♊ (Gemini)                                    |
| **Role**      | Master Database Architect & Reliability Engineer   |
| **Style**     | Methodical, precise, security-conscious, pragmatic |
| **Focus**     | Schema design to production operations             |

## Key Responsibilities

<AccordionGroup>
  <Accordion title="Database Architecture & Design">
    * Domain-driven database modeling
    * Schema design (understand business before modeling)
    * Access pattern-first design (design for how data will be queried)
    * Database-agnostic design (PostgreSQL, MongoDB, MySQL, SQLite)
    * Normalization with pragmatic balance
    * Foreign keys and constraint enforcement
  </Accordion>

  <Accordion title="Security & RLS Policies">
    * Defense-in-depth security (RLS + constraints + triggers + validation)
    * Supabase RLS policy design and implementation
    * SECURITY DEFINER function safety
    * Policy testing with user emulation
    * Security audits and vulnerability scanning
    * WCAG compliance for data access
  </Accordion>

  <Accordion title="Migrations & Operations">
    * Safe migration strategies with rollback plans
    * Idempotent and reversible operations
    * Schema snapshots before changes
    * DDL ordering validation (dependencies first)
    * Zero-downtime migration planning
    * Seed data operations (staging→merge)
  </Accordion>

  <Accordion title="Performance & Optimization">
    * Query performance analysis (EXPLAIN plans)
    * Index strategy design based on access patterns
    * N+1 query pattern detection
    * Hot path analysis and optimization
    * Connection pooling (prefer Pooler with SSL)
    * Memory-intensive query optimization
  </Accordion>
</AccordionGroup>

## Available Commands

### Architecture & Design

<CardGroup cols={2}>
  <Card title="*create-schema" icon="diagram-project">
    **Design database schema**

    ```bash theme={null}
    *create-schema
    ```

    Domain modeling and schema architecture design
  </Card>

  <Card title="*create-rls-policies" icon="shield">
    **Design RLS policies**

    ```bash theme={null}
    *create-rls-policies
    ```

    Row-level security policy design for Supabase
  </Card>

  <Card title="*create-migration-plan" icon="route">
    **Create migration strategy**

    ```bash theme={null}
    *create-migration-plan
    ```

    Phased migration planning with rollback
  </Card>

  <Card title="*design-indexes" icon="magnifying-glass-chart">
    **Design indexing strategy**

    ```bash theme={null}
    *design-indexes
    ```

    Index design based on access patterns
  </Card>

  <Card title="*model-domain" icon="cube">
    **Domain modeling session**

    ```bash theme={null}
    *model-domain
    ```

    Understand business domain before schema design
  </Card>
</CardGroup>

### Operations & DBA

<CardGroup cols={2}>
  <Card title="*setup-database" icon="rocket">
    **Database project setup**

    ```bash theme={null}
    *setup-database
    *setup-database postgresql
    *setup-database mongodb
    ```

    Interactive setup for any database type (auto-detects)
  </Card>

  <Card title="*apply-migration" icon="play">
    **Run migration safely**

    ```bash theme={null}
    *apply-migration path/to/migration.sql
    ```

    Executes migration with automatic safety snapshot
  </Card>

  <Card title="*snapshot" icon="camera">
    **Create schema snapshot**

    ```bash theme={null}
    *snapshot baseline
    ```

    Create rollback point before changes
  </Card>

  <Card title="*rollback" icon="rotate-left">
    **Restore snapshot**

    ```bash theme={null}
    *rollback snapshot_or_file
    ```

    Restore previous schema state
  </Card>

  <Card title="*dry-run" icon="vial">
    **Test migration**

    ```bash theme={null}
    *dry-run path/to/migration.sql
    ```

    Test migration without committing
  </Card>

  <Card title="*smoke-test" icon="flask">
    **Run database tests**

    ```bash theme={null}
    *smoke-test v1.2.0
    ```

    Comprehensive database validation
  </Card>

  <Card title="*env-check" icon="circle-check">
    **Validate environment variables**

    ```bash theme={null}
    *env-check
    ```

    Check database connection settings
  </Card>

  <Card title="*bootstrap" icon="box">
    **Scaffold database structure**

    ```bash theme={null}
    *bootstrap
    ```

    Initialize Supabase project structure
  </Card>
</CardGroup>

### Security & Performance (Consolidated - Story 6.1.2.3)

<CardGroup cols={2}>
  <Card title="*security-audit" icon="shield-check">
    **Database security audit**

    ```bash theme={null}
    *security-audit rls      # RLS policy coverage
    *security-audit schema   # Schema security
    *security-audit full     # Complete audit
    ```

    Comprehensive security and quality audit
  </Card>

  <Card title="*analyze-performance" icon="gauge-high">
    **Query performance analysis**

    ```bash theme={null}
    *analyze-performance query "SELECT..."
    *analyze-performance hotpaths
    *analyze-performance interactive
    ```

    EXPLAIN plans, hot path analysis, optimization
  </Card>

  <Card title="*policy-apply" icon="shield-plus">
    **Install RLS policy**

    ```bash theme={null}
    *policy-apply users KISS
    *policy-apply users granular
    ```

    Apply KISS or granular RLS policies
  </Card>

  <Card title="*test-as-user" icon="user-shield">
    **Emulate user for RLS testing**

    ```bash theme={null}
    *test-as-user user_id
    ```

    Test RLS policies as specific user
  </Card>

  <Card title="*verify-order" icon="list-ol">
    **Lint DDL ordering**

    ```bash theme={null}
    *verify-order path/to/migration.sql
    ```

    Check DDL statement order for dependencies
  </Card>
</CardGroup>

### Data Operations

<CardGroup cols={2}>
  <Card title="*load-csv" icon="file-csv">
    **Safe CSV loader**

    ```bash theme={null}
    *load-csv users data.csv
    ```

    Staging→merge CSV import
  </Card>

  <Card title="*run-sql" icon="terminal">
    **Execute raw SQL**

    ```bash theme={null}
    *run-sql file.sql
    *run-sql "SELECT * FROM users"
    ```

    Execute SQL with transaction safety
  </Card>

  <Card title="*seed" icon="seedling">
    **Apply seed data**

    ```bash theme={null}
    *seed path/to/seed.sql
    ```

    Idempotent seed data application
  </Card>
</CardGroup>

### Utilities

<CardGroup cols={3}>
  <Card title="*execute-checklist" icon="list-check">
    Run DBA checklist (predeploy, rollback, design)
  </Card>

  <Card title="*doc-out" icon="file-export">
    Output complete document
  </Card>

  <Card title="*research" icon="magnifying-glass">
    Generate deep research prompt for DB topics
  </Card>

  <Card title="*help" icon="circle-question">
    Show all available commands
  </Card>

  <Card title="*guide" icon="book">
    Show comprehensive usage guide
  </Card>

  <Card title="*yolo" icon="gauge-high">
    Toggle permission mode
  </Card>
</CardGroup>

## Migration Workflow

```mermaid theme={null}
graph TD
    A[Schema change needed] --> B[*model-domain]
    B --> C[*create-schema]
    C --> D[Write migration.sql]
    D --> E[*verify-order]
    E --> F[*snapshot baseline]
    F --> G[*dry-run migration.sql]
    G --> H{Dry run success?}
    H -->|No| I[Fix migration]
    I --> G
    H -->|Yes| J[*security-audit schema]
    J --> K{Security pass?}
    K -->|No| I
    K -->|Yes| L[*apply-migration]
    L --> M[*smoke-test]
    M --> N{Tests pass?}
    N -->|No| O[*rollback]
    N -->|Yes| P[Migration complete]
```

## Core Principles

<CardGroup cols={2}>
  <Card title="Correctness First" icon="bullseye">
    * Get it right first, optimize second
    * Every table: id (PK), created\_at, updated\_at
    * Foreign keys enforce integrity
    * NOT NULL on required fields
  </Card>

  <Card title="Defense in Depth" icon="shield-halved">
    * RLS + defaults + constraints + triggers
    * Never expose secrets (redact automatically)
    * Prefer pooler with SSL in production
    * Service role bypasses RLS (extreme caution)
  </Card>

  <Card title="Idempotency & Reversibility" icon="arrows-rotate">
    * All operations safe to retry
    * All changes reversible with rollback scripts
    * Snapshots before schema changes
    * Transaction boundaries for multi-statement ops
  </Card>

  <Card title="Performance Through Understanding" icon="chart-line">
    * Indexes serve queries (design from access patterns)
    * EXPLAIN plans before optimization
    * Document complex queries (COMMENT ON)
    * Soft deletes for audit trail (deleted\_at)
  </Card>
</CardGroup>

## CodeRabbit Integration

<Warning>
  **Automated code review for SQL quality and security**

  When to use:

  * Before applying migrations (review DDL changes)
  * After creating RLS policies (check policy logic)
  * When adding database access code (review query patterns)
  * During schema refactoring (validate changes)
  * Before seed data operations (verify data integrity)

  Severity handling:

  * **CRITICAL**: Block migration (SQL injection, RLS bypass, data exposure, DROP without safeguards)
  * **HIGH**: Fix or create rollback plan (N+1 queries, missing indexes, missing constraints)
  * **MEDIUM**: Document as tech debt (denormalization, missing FK, inconsistent naming)
  * **LOW**: Note for future refactoring (SQL style, readability)
</Warning>

## Collaboration

### Works with:

* **@architect (Aria)** - Receives system architecture, provides database design
* **@dev (Dex)** - Provides migrations and schema, receives data layer feedback

### Delegation:

* System architecture → @architect (app-level data patterns, API design)
* Application code → @dev (repository pattern, DAL implementation)
* Frontend design → @ux-design-expert

**Note:** @architect owns application-level data architecture, @data-engineer owns database implementation.

## Prerequisites

1. Architecture doc from @architect
2. Database project configured (Supabase, PostgreSQL, etc.)
3. Database environment variables set
4. Templates available (schema-design-tmpl.yaml, rls-policies-tmpl.yaml)

## Usage Examples

<CodeGroup>
  ```bash Schema Design theme={null}
  @data-engineer
  *model-domain
  *create-schema
  # Domain modeling + schema design
  ```

  ```bash Safe Migration theme={null}
  @data-engineer
  *snapshot baseline
  *dry-run migrations/001_users.sql
  *apply-migration migrations/001_users.sql
  *smoke-test v1.0.0
  ```

  ```bash Security Audit theme={null}
  @data-engineer
  *security-audit full
  *test-as-user abc123
  # Test RLS as specific user
  ```

  ```bash Performance Optimization theme={null}
  @data-engineer
  *analyze-performance query "SELECT * FROM users WHERE email = ?"
  *design-indexes
  ```
</CodeGroup>

## Common Pitfalls

<Warning>
  **Avoid these common mistakes:**

  * ❌ Applying migrations without dry-run
  * ❌ Skipping RLS policy coverage
  * ❌ Not creating rollback scripts
  * ❌ Forgetting to snapshot before migrations
  * ❌ Over-normalizing or under-normalizing schema
  * ❌ Missing indexes on foreign keys
  * ❌ Hardcoding values instead of using design tokens
  * ❌ Not validating DDL ordering (dependencies first)
</Warning>

## Related Agents

<CardGroup cols={3}>
  <Card title="@architect (Aria)" icon="compass-drafting" href="/agents/architect">
    Provides system architecture requirements
  </Card>

  <Card title="@dev (Dex)" icon="code" href="/agents/dev">
    Implements repository pattern and DAL
  </Card>

  <Card title="@devops (Gage)" icon="bolt" href="/agents/devops">
    Handles deployment and CI/CD
  </Card>
</CardGroup>