database-architect

Database design and optimization specialist. Schema design, query optimization, indexing strategies, data modeling, and migration planning for relational and NoSQL databases.

Ir a la instalación

Repositorio: oimiragieo/agent-studio
Última actividad en el origen: 21 de abril de 2026 a las 23:45
Idioma detectado de SKILL.md: inglés
Estrellas: 36
Forks: 6

Opciones de instalación

De forma predeterminada está seleccionado el prompt que primero revisa el origen. Puedes cambiar a un comando directo o descargar una copia local.

Revisa los archivos de origen

Lee SKILL.md y los archivos complementarios que muestra SkillsMP antes de decidir si quieres instalarlo.

Volver a la búsqueda Volver a la instalación

name	database-architect
description	Database design and optimization specialist. Schema design, query optimization, indexing strategies, data modeling, and migration planning for relational and NoSQL databases.
version	1.1.1
model	sonnet
invoked_by	both
user_invocable	true
tools	["Read","Write","Edit","Bash","Glob","Grep"]
best_practices	["Normalize to 3NF unless performance requires denormalization","Always plan indexes based on query patterns","Use migrations for all schema changes","Document data models and relationships","Use pgvector for AI embedding storage alongside relational data","Prefer Supavisor or PgBouncer for connection pooling in production"]
error_handling	graceful
streaming	supported
verified	true
lastVerifiedAt	"2026-02-22T00:00:00.000Z"
source	builtin
trust_score	100
provenance_sha	5514cb81834daa7b

Database Architect Skill

Database Architect Skill - Designs efficient database schemas, optimizes queries, plans indexes, and creates migration strategies for both relational (PostgreSQL, MySQL) and NoSQL (MongoDB, Redis) databases. - Designing normalized and denormalized schemas - Query optimization and execution plan analysis - Index strategy planning - Data modeling (ER diagrams, relationships) - Migration planning and versioning - Performance troubleshooting

Step 1: Understand Data Requirements

Gather requirements:

Entities: What data needs to be stored?
Relationships: How do entities relate (1:1, 1:N, N:M)?
Access Patterns: How will data be queried?
Volume: Expected data size and growth rate
Consistency: ACID requirements vs eventual consistency

Step 2: Design Schema

For Relational Databases:

Normalize: Start with 3NF to reduce redundancy
Define Primary Keys: Use surrogate keys (UUID/SERIAL) or natural keys
Define Foreign Keys: Establish referential integrity
Consider Denormalization: Only for proven performance needs

For NoSQL Databases:

Model for Queries: Design documents/collections around access patterns
Embed vs Reference: Embed for 1:1/1:few, reference for 1:many
Shard Key Selection: Choose keys that distribute evenly

Step 3: Plan Indexes

Index strategy based on query patterns:

-- Example: Users table with common queries
CREATE INDEX idx_users_email ON users(email);           -- Exact match
CREATE INDEX idx_users_name ON users(last_name, first_name);  -- Range/sort
CREATE INDEX idx_users_created ON users(created_at DESC);     -- Ordering

Index Guidelines:

Index columns used in WHERE, JOIN, ORDER BY
Consider composite indexes for multi-column queries

**Schema Design Request**:

migrations/
  001_create_users.sql
  002_add_email_index.sql
  003_create_orders.sql

-- Install extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Table with embedding column (1536 dims for OpenAI text-embedding-3-small)
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content TEXT NOT NULL,
    embedding vector(1536),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- IVFFlat index for approximate nearest neighbor (ANN) search
-- lists = sqrt(row_count) is a good starting value
CREATE INDEX idx_documents_embedding ON documents
    USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

-- HNSW index (faster queries, more memory; preferred for < 1M vectors)
CREATE INDEX idx_documents_embedding_hnsw ON documents
    USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

-- Similarity search (cosine distance)
SELECT id, content, 1 - (embedding <=> $1::vector) AS similarity
FROM documents
ORDER BY embedding <=> $1::vector
LIMIT 10;

-- Range partitioning by date (common for time-series / logs)
CREATE TABLE events (
    id BIGSERIAL,
    created_at TIMESTAMPTZ NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB
) PARTITION BY RANGE (created_at);

-- Monthly partitions
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Hash partitioning for even distribution (e.g., multi-tenant)
CREATE TABLE orders (
    id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    total DECIMAL(12,2)
) PARTITION BY HASH (tenant_id);

CREATE TABLE orders_p0 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE orders_p1 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE orders_p2 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE orders_p3 PARTITION OF orders FOR VALUES WITH (modulus 4, remainder 3);

-- Generated columns promote hot JSONB fields to indexed native columns
CREATE TABLE customers (
    id BIGSERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    -- Promote frequently filtered fields to B-tree indexed generated columns
    country TEXT GENERATED ALWAYS AS (data->>'country') STORED,
    signup_date DATE GENERATED ALWAYS AS ((data->>'signup_date')::DATE) STORED
);
CREATE INDEX idx_customers_country ON customers (country);
CREATE INDEX idx_customers_signup ON customers (signup_date);

-- GIN index for containment / key-existence queries
CREATE INDEX idx_customers_data_gin ON customers USING GIN (data);

-- Partial GIN index for large tables (index only active records)
CREATE INDEX idx_customers_data_active ON customers
    USING GIN (data) WHERE (data->>'status') = 'active';

-- jsonpath query example (PG 17)
SELECT * FROM customers
WHERE data @? '$.tags[*] ? (@ == "premium")';

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction       ; transaction pooling for stateless apps
max_client_conn = 1000
default_pool_size = 25
server_pool_size = 5

Mode	Use Case	Notes
Session	Long-running connections, `LISTEN/NOTIFY`	1 client = 1 server connection
Transaction	Stateless APIs (recommended default)	Most efficient; breaks `SET` / prepared statements
Statement	Rarely needed	Each statement can use a different server connection

Design a schema for an e-commerce orders system

-- Users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Orders table
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending',
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Order items (1:N relationship)
CREATE TABLE order_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2)
);

-- Indexes for common queries
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order ON order_items(order_id);

Anti-Pattern	Why It Fails	Correct Approach
Manual DDL directly on production	No rollback path; breaks migration history	Always use versioned migrations with DOWN scripts
Premature denormalization	Adds complexity before profiling; often no measurable gain	Normalize first, denormalize only after EXPLAIN ANALYZE reveals bottleneck
Indexing every column	Slows writes; wastes storage; misleads query planner	Index only columns that appear in WHERE, JOIN, and ORDER BY clauses
Adding NOT NULL column without default	Locks entire table during migration on large datasets	Add nullable column, backfill in batches, then add NOT NULL constraint
Direct connections from serverless functions	Connection limit exhausted under load spike	Use PgBouncer or Supavisor in transaction-pooling mode

cat .claude/context/memory/learnings.md