Rose

Database management platform and data engineering agent.

Overview

Rose is Rhea's data engineer - handling database operations, migrations, ETL pipelines, and data transformations. Named as a nod to the Rosetta Stone, Rose translates between data formats and manages the flow of information across the platform.

Rose operates as an AI agent with specialized database capabilities:

• Schema management (DDL): CREATE, ALTER, DROP
• Data operations (DML): INSERT, UPDATE, DELETE, UPSERT
• Migrations: Version-controlled schema changes
• ETL pipelines: Extract, transform, load workflows
• Data validation: Integrity checks and quality assurance

Architecture

Rose connects to pg-rhea (PostgreSQL 17) via Janus's SQL proxy API.

┌─────────────────────────────────────────────────────────┐
│                    AI Agent (Rose)                       │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────┐  │
│  │  Checklist  │  │    SQL      │  │   Validation    │  │
│  │  Workflow   │  │  Expertise  │  │    Testing      │  │
│  └─────────────┘  └─────────────┘  └─────────────────┘  │
└────────────────────────┬────────────────────────────────┘
                         │ janus_sql_* MCP tools
                         ▼
┌─────────────────────────────────────────────────────────┐
│                    Janus SQL Proxy                       │
│           POST /api/v1/sql/execute                       │
│           POST /api/v1/sql/migrate                       │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────┐  │
│  │   Policy    │  │   Audit     │  │   Connection    │  │
│  │  Enforcer   │  │   Logging   │  │    Pooling      │  │
│  └─────────────┘  └─────────────┘  └─────────────────┘  │
└────────────────────────┬────────────────────────────────┘
                         │ asyncpg
                         ▼
┌─────────────────────────────────────────────────────────┐
│                      pg-rhea                             │
│              PostgreSQL 17 on Coolify                    │
│  ┌─────────┐  ┌─────────┐  ┌─────────┐  ┌─────────────┐ │
│  │  argus  │  │ artemis │  │ourotters│  │   (more)    │ │
│  └─────────┘  └─────────┘  └─────────┘  └─────────────┘ │
└─────────────────────────────────────────────────────────┘

Database Access

Rose uses Janus MCP tools for all database operations:

Tool	Purpose
janus_sql_execute	Run queries (SELECT, INSERT, UPDATE, DELETE, DDL)
janus_sql_migrate	Run migrations (transactional, all-or-nothing)
janus_sql_logs	View audit trail of past SQL executions

All executions are automatically logged to argus.sql_audit_log with:

• SQL text (truncated to 10KB)
• Executor identity
• Reason/description
• Success/failure status
• Rows affected
• Timestamp

Databases on pg-rhea

Database	Schema	Purpose
argus	argus	Status page, devlogs, tickets, initiatives, agents, skills, learnings
artemis	public	Calendar and scheduling
ourotters	public	OurOtters app

Deterministic Workflow

Rose uses checklist-driven workflows via TodoWrite for reliability. Every operation follows a verification pattern:

Migration Checklist

- [ ] Query current schema via janus_sql_execute
- [ ] Write idempotent SQL (IF NOT EXISTS, DO $$ BEGIN...EXCEPTION...END $$)
- [ ] Verify pre-migration row counts
- [ ] Execute via janus_sql_migrate (transactional)
- [ ] Verify post-migration row counts match
- [ ] Test with sample query
- [ ] Create devlog with structured metadata

Data Validation Checklist

- [ ] SELECT COUNT(*) for baseline
- [ ] Check nulls: SELECT * WHERE required_col IS NULL
- [ ] Check duplicates: GROUP BY unique_key HAVING COUNT(*) > 1
- [ ] Verify FKs: LEFT JOIN ... WHERE fk.id IS NULL
- [ ] Spot check: SELECT * LIMIT 5

How It Works

Rose is an AI agent registered in Janus's agent registry. When invoked:

┌──────────────────┐     ┌──────────────────┐     ┌──────────────────┐
│   Claude Code    │────▶│   ask_agent()    │────▶│  Agent Registry  │
│   (caller)       │     │   MCP tool       │     │  (Janus API)     │
└──────────────────┘     └──────────────────┘     └────────┬─────────┘
                                                           │
                                                           ▼
┌──────────────────┐     ┌──────────────────┐     ┌──────────────────┐
│   Rose Response  │◀────│  Claude Agent    │◀────│  Fetch Config    │
│                  │     │  SDK query()     │     │  (mcp_servers,   │
└──────────────────┘     └──────────────────┘     │   system_prompt) │
                                                  └──────────────────┘

1. Caller invokes ask_agent(agent_id='rose', request='...')
2. Janus fetches Rose's config from the agent registry
3. Agent SDK spawns a Claude subprocess with Rose's MCP servers and system prompt
4. Rose executes using janus_sql_* tools
5. Response returns to the caller

Agent Configuration

Rose's config is stored in argus.agents:

{
  "id": "rose",
  "name": "Rose",
  "model": "claude-sonnet-4-20250514",
  "config": {
    "mcp_servers": {
      "janus": {"command": "janus-mcp"},
      "context7": {"command": "npx", "args": ["-y", "@upstash/context7-mcp@latest"]}
    },
    "system_prompt": "You are Rose, a senior data engineer...",
    "permission_mode": "bypassPermissions",
    "policy": {
      "allowed_databases": ["argus", "artemis", "ourotters"],
      "allowed_operations": ["SELECT", "INSERT", "UPDATE", "DELETE", "CREATE INDEX", "CREATE TABLE"],
      "requires_approval": ["DROP", "TRUNCATE", "ALTER TABLE"],
      "environment": "dev"
    }
  }
}

Policy Enforcement

Rose includes a PolicyEnforcer that validates operations before execution:

Field	Purpose
allowed_databases	Whitelist of databases Rose can access
allowed_operations	SQL operations permitted without approval
requires_approval	Operations that pause for user confirmation
environment	dev/staging/prod - affects strictness

Operation Classification

The PolicyEnforcer extracts SQL operations including compound types:

"SELECT * FROM users"           → SELECT
"CREATE TABLE foo"              → CREATE TABLE
"CREATE INDEX idx ON foo(id)"   → CREATE INDEX
"DROP TABLE IF EXISTS foo"      → DROP TABLE
"CREATE OR REPLACE FUNCTION"    → CREATE FUNCTION

Safe operations execute immediately. Dangerous operations (DROP, TRUNCATE) pause and request user confirmation before proceeding.

Structured Logging

Every Rose operation creates a devlog with structured metadata:

{
  "service_id": "rose",
  "log_type": "config",
  "title": "SQL: CREATE INDEX on devlogs.created_at",
  "content": "```sql\nCREATE INDEX idx_devlogs_created_at ON argus.devlogs(created_at);\n```\n\nResult: 0 rows affected",
  "tags": ["sql", "argus", "create index"],
  "metadata": {
    "intent": "Improve query performance for devlog listing",
    "sql": "CREATE INDEX idx_devlogs_created_at ON argus.devlogs(created_at);",
    "validation": {
      "pre_count": 1523,
      "post_count": 1523,
      "verified": true
    }
  }
}

This enables:

• Audit trails: Full SQL and validation evidence
• Rollback context: Know exactly what changed
• Pattern learning: Analyze past operations

Viewing Rose's Logs

# Via Janus MCP
janus_devlog_list(service_id='rose')
janus_devlog_search(query='migration')

# Via Argus UI
https://argus.meetrhea.com → Devlogs → Filter by "rose"

# Via SQL audit log
janus_sql_logs(limit=50)

Invoking Rose

# Via ask_agent MCP tool
ask_agent(
    agent_id='rose',
    request='Create an index on devlogs.created_at for better query performance'
)

# Example: Add a new column
ask_agent(
    agent_id='rose',
    request='Add a metadata JSONB column to the tickets table with a GIN index'
)

# Example: Data validation
ask_agent(
    agent_id='rose',
    request='Check for orphaned records in the devlogs table'
)

SQL Expertise

Rose has deep PostgreSQL 17 knowledge:

• Query patterns: Window functions, CTEs, recursive queries
• Optimization: EXPLAIN ANALYZE, query planning, index selection
• Index design: B-tree, GIN for JSONB, partial indexes, covering indexes
• Idempotent patterns: ON CONFLICT DO UPDATE, CREATE IF NOT EXISTS
• PostgreSQL specifics: JSONB operators, array functions, range types

Test Coverage

Rose's core modules have comprehensive test coverage:

Module	Tests	Coverage
PolicyEnforcer	27 unit tests	Operation extraction, policy validation, edge cases
SQLExecutor	4 failure tests	PolicyViolationError, audit log handling
Integration	22 tests	End-to-end workflows with mocks

Run tests:

cd /home/dshanklin/repos-meetrhea/janus
pytest tests/test_rose_features.py -v

Related

• Janus - Infrastructure automation (provides SQL proxy)
• Argus - Uses pg-rhea for data storage
• Initiative: Database Access Architecture