Menu
Apply PostgreSQL schema, migration, and query patterns with idempotent SQL. Use when writing database schemas or migrations.
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
Manage GitHub Projects v2 board: add issues, update field values, and query board state. Use when managing project boards.
Run WCAG 2.2 Level AA accessibility audits against generated UI. Use when a story has ui:true or when asked to audit accessibility.
Generate a complete component file tree wired to design tokens with tests and Gherkin spec. Use when scaffolding a new UI component.
Extract Gherkin scenarios from story markdown files into runnable .feature files and generate step definition stubs. Use when syncing stories to test suites.
Read and write design tokens in W3C DTCG format (tokens.json) and emit CSS, Tailwind, and Mantine outputs. Use when modifying or generating design tokens.
Apply Docker and Docker Compose best practices for containerising services. Use when writing or reviewing Dockerfiles and compose configs.
| name | postgresql-patterns |
| description | Apply PostgreSQL schema, migration, and query patterns with idempotent SQL. Use when writing database schemas or migrations. |
migrations/
V001__create_users.sql # Flyway
0001_create_users.sql # Generic
20240115_create_users.sql # Timestamp-based
-- Create table
CREATE TABLE IF NOT EXISTS users (
id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
email text UNIQUE NOT NULL,
name text NOT NULL,
role text NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'admin')),
created_at timestamptz DEFAULT now() NOT NULL,
updated_at timestamptz DEFAULT now() NOT NULL
);
-- Indexes
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
CREATE INDEX CONCURRENTLY idx_users_role ON users(role) WHERE role != 'user';
-- Updated_at trigger
CREATE OR REPLACE FUNCTION update_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 update_updated_at();
-- RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY users_select_own ON users
FOR SELECT USING (id = current_user_id());
CREATE POLICY users_admin_all ON users
FOR ALL USING (current_user_role() = 'admin');
-- Always EXPLAIN ANALYZE in development
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.*, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > now() - interval '30 days'
GROUP BY u.id;
-- Partial index for common filter
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';
-- GIN index for full-text search
CREATE INDEX idx_products_search
ON products USING gin(to_tsvector('english', name || ' ' || description));
# pgbouncer.ini
[databases]
app = host=127.0.0.1 port=5432 dbname=app
[pgbouncer]
pool_mode = transaction # transaction pooling for most apps
max_client_conn = 100
default_pool_size = 20