Database Skill

Provides comprehensive database capabilities for the Golden Armada AI Agent Fleet Platform.

When to Use This Skill

Activate this skill when working with:

• Database schema design
• SQL query writing and optimization
• Database migrations
• Index optimization
• Data modeling

PostgreSQL Quick Reference

Connection

\\\`bash

Connect

psql -h localhost -U postgres -d golden_armada

Connection string

postgresql://user:password@host:5432/database

Common psql commands

\l # List databases \c databasename # Connect to database \dt # List tables \d tablename # Describe table \di # List indexes \q # Quit \\\`

Schema Design

\\\`sql -- Create table with common patterns CREATE TABLE agents ( id UUID PRIMARY KEY DEFAULT genrandomuuid(), name VARCHAR(100) NOT NULL, type VARCHAR(50) NOT NULL CHECK (type IN ('claude', 'gpt', 'gemini')), status VARCHAR(20) DEFAULT 'idle', config JSONB DEFAULT '{}', createdat TIMESTAMPTZ DEFAULT NOW(), updatedat TIMESTAMPTZ DEFAULT NOW(), deleted_at TIMESTAMPTZ -- Soft delete );

-- Create index CREATE INDEX idxagentstype ON agents(type); CREATE INDEX idxagentsstatus ON agents(status) WHERE deletedat IS NULL; CREATE INDEX idxagents_config ON agents USING GIN(config);

-- Add foreign key CREATE TABLE tasks ( id UUID PRIMARY KEY DEFAULT genrandomuuid(), agentid UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE, message TEXT NOT NULL, result TEXT, status VARCHAR(20) DEFAULT 'pending', createdat TIMESTAMPTZ DEFAULT NOW() ); \\\`

Common Queries

\\\`sql -- Basic CRUD INSERT INTO agents (name, type) VALUES ('agent-1', 'claude') RETURNING ; SELECT FROM agents WHERE type = 'claude' AND deleted_at IS NULL; UPDATE agents SET status = 'active' WHERE id = $1 RETURNING *; DELETE FROM agents WHERE id = $1;

-- Joins SELECT a.name, COUNT(t.id) as taskcount FROM agents a LEFT JOIN tasks t ON a.id = t.agentid WHERE a.deleted_at IS NULL GROUP BY a.id;

-- JSON operations SELECT FROM agents WHERE config->>'model' = 'claude-sonnet-5'; SELECT FROM agents WHERE config @> '{"enabled": true}'; UPDATE agents SET config = config || '{"version": "2.0"}' WHERE id = $1;

-- Window functions SELECT name, createdat, ROWNUMBER() OVER (ORDER BY createdat) as rownum, LAG(createdat) OVER (ORDER BY createdat) as prev_created FROM agents;

-- CTEs WITH activeagents AS ( SELECT FROM agents WHERE status = 'active' ) SELECT FROM activeagents WHERE type = 'claude'; \\\`

Migrations

\\\`sql -- Migration: 001createagents.sql BEGIN;

CREATE TABLE agents ( id UUID PRIMARY KEY DEFAULT genrandomuuid(), name VARCHAR(100) NOT NULL, type VARCHAR(50) NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW() );

CREATE INDEX idxagentstype ON agents(type);

COMMIT;

-- Rollback: 001createagents.sql BEGIN; DROP TABLE IF EXISTS agents; COMMIT; \\\`

Performance Optimization

\\\`sql -- Analyze query plan EXPLAIN ANALYZE SELECT * FROM agents WHERE type = 'claude';

-- Check index usage SELECT schemaname, tablename, indexname, idxscan, idxtupread FROM pgstatuserindexes;

-- Find slow queries SELECT query, calls, meantime, totaltime FROM pgstatstatements ORDER BY mean_time DESC LIMIT 10;

-- Vacuum and analyze VACUUM ANALYZE agents; \\\`

SQLAlchemy ORM

\\\`python from sqlalchemy import Column, String, DateTime, ForeignKey, JSON from sqlalchemy.dialects.postgresql import UUID from sqlalchemy.orm import relationship from sqlalchemy.sql import func import uuid

class Agent(Base): tablename = 'agents'

id = Column(UUID(asuuid=True), primarykey=True, default=uuid.uuid4) name = Column(String(100), nullable=False) type = Column(String(50), nullable=False) status = Column(String(20), default='idle') config = Column(JSON, default={}) createdat = Column(DateTime(timezone=True), serverdefault=func.now()) updated_at = Column(DateTime(timezone=True), onupdate=func.now())

tasks = relationship("Task", back_populates="agent", cascade="all, delete-orphan")

class Task(Base): tablename = 'tasks'

id = Column(UUID(asuuid=True), primarykey=True, default=uuid.uuid4) agentid = Column(UUID(asuuid=True), ForeignKey('agents.id'), nullable=False) message = Column(String, nullable=False) result = Column(String)

agent = relationship("Agent", back_populates="tasks") \\\`

Best Practices

1. Use UUIDs for primary keys in distributed systems
2. Add indexes for frequently queried columns
3. Use soft deletes (deleted_at) for important data
4. JSONB for flexible data, with GIN indexes
5. Foreign key constraints for data integrity
6. Created/updated timestamps for auditing
7. Connection pooling for performance