SQL Postgres

SQL (Postgres)

Routing: small, in-scope schema change: apply this skill in the main window. Large or isolated batch (or when it needs its own context): hand to the sql-schema Task subagent (demoted, dogfood-only).

DDL and conventions

• CREATE TABLE: use IF NOT EXISTS; explicit primary key; appropriate types (UUID, TEXT, TIMESTAMPTZ, JSONB).
• Names: snake_case for tables and columns.
• Documentation: COMMENT ON TABLE ... and COMMENT ON COLUMN ... when it helps maintenance.

Recommended patterns

• Table scripts: e.g. 001_create_tables.sql, 002_indexes_constraints.sql; uuid-ossp extension when using UUID.
• Sequences for protocols or numeric IDs when applicable.
• DDL for integrations (filters, logs, events) in dedicated folders (e.g., db/, scripts/, code/).

Best practices

• Idempotent scripts: CREATE TABLE IF NOT EXISTS, CREATE INDEX IF NOT EXISTS; avoid failure on re-execution.
• TIMESTAMPTZ for dates/times; UUID for ids when applicable.
• Schema changes: document in docs or module README.