# Worker Database

> Database operation patterns, collections, and connection patterns

- Skill: `eron1703/worker-database` (Agent Skill)
- Install (CLI): `npx skillmds@latest add eron1703/worker-database`
- Raw SKILL.md: https://api.skillmd.com/api/skills/eron1703/worker-database/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: eron1703 (https://skillmd.com/u/eron1703)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/eron1703/worker-database

---


# Worker Database Knowledge

## Database Architecture

All databases run in the `databases-test` namespace on K3S.

### ArangoDB (Multi-model)

**Databases**:
- `flowmaster` - Production data
- `flowmaster_test` - Test environment
- `flowmaster_dev` - Development environment

**Key Collections**:
- `process_definitions` - BPMN/workflow definitions
- `process_instances` - Running workflow instances
- `agent_sessions` - AI agent conversation history
- `knowledge_items` - RAG/knowledge base documents
- `schedules` - Scheduled tasks and triggers
- `node_executions` - Execution logs per process node
- `business_rules` - DMN rules and conditions

**Query Pattern**:
```
FOR doc IN process_definitions
FILTER doc.type == "workflow"
RETURN doc
```

### PostgreSQL (Relational)

**Databases**:
- `flowmaster_core` - Auth, RBAC, permissions
- `flowmaster_users` - User profiles and settings
- `flowmaster_workflow` - Workflow execution state

**ORM**: Prisma (used in auth-service)

**Prisma Schema Location**:
- auth-service repository: `prisma/schema.prisma`

### Redis (Cache/Queue)

**Used By**:
- `event-bus` - Event routing and queuing
- `ai-agent` - Session caching
- `prompt-engineering` - Prompt cache
- `scheduling` - Job scheduling

## Database Access from Local Machine

### Via SSH Tunnel
```bash
# Create tunnel for ArangoDB
ssh dev-01-root -L 8529:<arango-k8s-url>:8529 -N &

# Create tunnel for PostgreSQL
ssh dev-01-root -L 5432:<postgres-k8s-url>:5432 -N &

# Create tunnel for Redis
ssh dev-01-root -L 6379:<redis-k8s-url>:6379 -N &

# Now connect locally using credentials from commander-mcp
arangosh --server.endpoint http://127.0.0.1:8529 --server.username <user> --server.password <password>
psql -h localhost -U <user> -d flowmaster_core
redis-cli -p 6379
```

## Common Database Operations

### ArangoDB: Insert Document
```javascript
db.process_definitions.insert({
  _key: "workflow_123",
  name: "Approval Workflow",
  version: "1.0",
  type: "workflow",
  definition: { /* BPMN */ }
})
```

### ArangoDB: Query with Filter
```javascript
FOR doc IN process_instances
FILTER doc.status == "running"
LIMIT 10
RETURN doc
```

### PostgreSQL: User Query
```sql
SELECT id, email, role FROM users WHERE role = 'admin';
```

### PostgreSQL: RBAC Query
```sql
SELECT p.*, r.name FROM permissions p
JOIN roles r ON p.role_id = r.id
WHERE p.resource = 'process_design';
```

### Redis: Get Value
```bash
redis-cli GET session:user_123
```

### Redis: Publish Event
```bash
redis-cli PUBLISH events:workflow '{"type":"started","id":"123"}'
```

## Key Facts
- All databases are in `databases-test` namespace (despite name, used for production)
- ArangoDB is primary for workflow/domain data (BPMN, processes, knowledge)
- PostgreSQL is for auth and RBAC (smaller, relational)
- Redis is for caching and event distribution (high-speed in-memory)
- K8S service URLs use `.databases-test.svc.cluster.local` domain
- SSH tunnels needed to access databases from local machine

> For live database credentials and connection details, use commander-mcp: get_credential("arango"), get_context_databases