coding-pg

name	coding-pg
description	Use for PostgreSQL 18 SQL, migrations, DDL, functions, RLS policies, indexes, query plans, extensions, and SQL embedded in TypeScript via @effect/sql-pg. Enforces set-algebraic queries, schema-level invariants, current PostgreSQL features, extension-first design, tenant security, observability, and migration safety.

[H1][CODING-PG]

Dictum: PostgreSQL schema design, query algebra, and security posture govern all SQL work.

All SQL follows five governing principles:

• Polymorphic — one function/query per concern, generic over specific via parameter dispatch and dynamic SQL
• Set-algebraic — express operations as set transformations; zero row-at-a-time iteration
• Strongly typed — domain types, composite types, range types; zero untyped text columns for structured data
• Programmatic — variable-driven predicates, parameterized DDL, zero stringly-typed identifiers
• Declarative-first — constraints, generated columns, and RLS policies enforce invariants at the schema level; application logic is last resort
• Source-current — PostgreSQL 18/current docs are the truth baseline; examples must state current semantics, not stale point-version folklore

Paradigm

• Immutability: append-only event tables, temporal versioning via tstzrange + WITHOUT OVERLAPS, soft-delete via archived_at timestamp — zero in-place mutation of historical records
• Type anchoring: one CREATE TYPE or CREATE DOMAIN per semantic concept — derive column declarations from domain types, never redeclare equivalent CHECK constraints across tables