Menu
Use when designing schemas, writing migrations, optimizing queries, or reviewing PostgreSQL database work
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
Operate as an agentic engineer using eval-first execution, decomposition, and cost-aware model routing. Use when AI agents perform most implementation work and humans enforce quality and risk controls.
REST API design patterns including resource naming, status codes, pagination, filtering, error responses, versioning, and rate limiting for production APIs.
Deployment workflows, CI/CD pipeline patterns, Docker containerization, health checks, rollback strategies, and production readiness checklists for web applications. Use when setting up deployment infrastructure or planning releases.
Use when generating or validating the ExecutionPlan JSON that the orchestrator must produce before spawning any agents
Create distinctive, production-grade frontend interfaces with high design quality. Use this skill when the user asks to build web components, pages, artifacts, posters, or applications (examples include websites, landing pages, dashboards, React components, HTML/CSS layouts, or when styling/beautifying any web UI). Generates creative, polished code and UI design that avoids generic AI aesthetics.
Research-before-coding workflow. Search for existing tools, libraries, and patterns before writing custom code. Systematizes the "search for existing solutions before implementing" approach. Use when starting new features or adding functionality.
| name | postgres-patterns |
| description | Use when designing schemas, writing migrations, optimizing queries, or reviewing PostgreSQL database work |
-- Always use UUIDs for public-facing IDs
-- Always include created_at / updated_at
-- Always define NOT NULL unless nullable is intentional
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Trigger to auto-update updated_at
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION set_updated_at();
-- Index foreign keys (always)
CREATE INDEX idx_posts_user_id ON posts(user_id);
-- Index columns used in WHERE, ORDER BY, GROUP BY
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
-- Partial index for common filtered queries
CREATE INDEX idx_users_active ON users(email) WHERE deleted_at IS NULL;
-- Composite index: order matters — most selective column first
CREATE INDEX idx_posts_user_status ON posts(user_id, status);
ALTER TABLE posts ADD COLUMN deleted_at TIMESTAMPTZ;
-- View that hides deleted records
CREATE VIEW active_posts AS
SELECT * FROM posts WHERE deleted_at IS NULL;
-- Query with soft delete filter
SELECT * FROM posts WHERE deleted_at IS NULL AND user_id = $1;
-- migrations/001_create_users.sql
-- Always: reversible, idempotent, non-destructive first
-- UP
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- DOWN
DROP TABLE IF EXISTS users;
-- Pagination (keyset — better than OFFSET for large tables)
SELECT * FROM posts
WHERE created_at < $1 -- cursor
ORDER BY created_at DESC
LIMIT 20;
-- Upsert
INSERT INTO user_settings (user_id, key, value)
VALUES ($1, $2, $3)
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = NOW();
-- Aggregation with filter
SELECT
DATE_TRUNC('day', created_at) AS day,
COUNT(*) AS total
FROM events
WHERE created_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;
-- Use JSONB for structured metadata (indexed, queryable)
ALTER TABLE products ADD COLUMN metadata JSONB;
-- Query inside JSONB
SELECT * FROM products WHERE metadata->>'category' = 'electronics';
-- Index a JSONB field
CREATE INDEX idx_products_category ON products((metadata->>'category'));
UUID as primary key — never expose sequential integers to clientsNOT NULL unless a column is intentionally nullableTIMESTAMPTZ (not TIMESTAMP) — always store timezone-aware timestampsOFFSET for tables larger than 10k rowsSELECT * in application queries — list columns explicitlyEXPLAIN ANALYZE before deploying any new query