{"id":"62cd097c-2420-4ee4-995f-9dfccddaa91c","slug":"wimolivier-postgresql-best-practices","name":"postgresql-best-practices","description":"PostgreSQL 18+ comprehensive best practices for enterprise database development. Provides schema\narchitecture patterns, Table API design, PL/pgSQL coding standards, migrations, and data warehousing.\n\nUSE THIS SKILL WHEN THE USER:\n- Creates PostgreSQL schemas, tables, functions, procedures, or triggers\n- Asks about PostgreSQL data types (uuid, text, timestamptz, jsonb, numeric)\n- Writes PL/pgSQL code and needs naming conventions (l_, in_, io_, co_ prefixes)\n- Implements Table API pattern (SECURITY DEFINER functions, schema separation)\n- Sets up database migrations or schema versioning\n- Needs index optimization, constraint design, or query performance help\n- Asks about PostgreSQL 18+ features (uuidv7, virtual columns, temporal constraints)\n- Builds data warehouses with Medallion Architecture (Bronze/Silver/Gold)\n- Needs data lineage tracking, ETL patterns, or audit logging\n- Reviews database code for best practices or anti-patterns\n- Migrates from Oracle PL/SQL to PostgreSQL PL/pgSQL\n- Sets up CI/CD pipelines for database changes\n\nCORE PATTERNS:\n- Three-schema separation: data (tables) → private (internal) → api (external)\n- Table API: All access through SECURITY DEFINER functions with SET search_path\n- Native migration system: Pure PL/pgSQL alternative to Flyway/Liquibase\n- Trivadis naming: l_ (local), in_ (input), io_ (inout), co_ (constant)","canonicalUrl":"https://xpersona.co/skill/wimolivier-postgresql-best-practices","sourceUrl":"https://github.com/wimolivier/postgresql-best-practices","homepage":null,"source":"GITHUB_OPENCLEW","vendor":{"slug":"wimolivier","label":"Wimolivier","url":"https://github.com/wimolivier/postgresql-best-practices"},"protocols":["OPENCLEW"],"capabilities":["inout"],"trustScore":null,"trustConfidence":"unknown","artifactCount":0,"benchmarkCount":0,"lastRelease":null,"freshnessAt":"2026-02-22T11:42:33.193Z","freshnessLabel":"Feb 22, 2026","securityReviewed":true,"openapiReady":true,"stats":[{"label":"Trust score","value":"Unknown"},{"label":"Compatibility","value":"OpenClaw"},{"label":"Freshness","value":"Feb 22, 2026"},{"label":"Vendor","value":"Wimolivier"},{"label":"Artifacts","value":"0"},{"label":"Benchmarks","value":"0"},{"label":"Last release","value":"Unpublished"}],"factsPreview":[{"factKey":"docs_crawl","category":"integration","label":"Crawlable docs","value":"6 indexed pages on the official domain","href":"https://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fopenclaw%2Fskills%2Ftree%2Fmain%2Fskills%2Fasleep123%2Fcaldav-calendar","sourceUrl":"https://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fopenclaw%2Fskills%2Ftree%2Fmain%2Fskills%2Fasleep123%2Fcaldav-calendar","sourceType":"search_document","confidence":"medium","observedAt":"2026-04-15T05:03:46.393Z","isPublic":true},{"factKey":"protocols","category":"compatibility","label":"Protocol compatibility","value":"OpenClaw","href":"https://xpersona.co/api/v1/agents/wimolivier-postgresql-best-practices/contract","sourceUrl":"https://xpersona.co/api/v1/agents/wimolivier-postgresql-best-practices/contract","sourceType":"contract","confidence":"medium","observedAt":"2026-02-24T19:43:55.898Z","isPublic":true},{"factKey":"auth_modes","category":"compatibility","label":"Auth modes","value":"api_key","href":"https://xpersona.co/api/v1/agents/wimolivier-postgresql-best-practices/contract","sourceUrl":"https://xpersona.co/api/v1/agents/wimolivier-postgresql-best-practices/contract","sourceType":"contract","confidence":"high","observedAt":"2026-02-24T19:43:55.898Z","isPublic":true},{"factKey":"schema_refs","category":"artifact","label":"Machine-readable schemas","value":"OpenAPI or schema references published","href":"https://github.com/wimolivier/postgresql-best-practices#input","sourceUrl":"https://xpersona.co/api/v1/agents/wimolivier-postgresql-best-practices/contract","sourceType":"contract","confidence":"high","observedAt":"2026-02-24T19:43:55.898Z","isPublic":true},{"factKey":"vendor","category":"vendor","label":"Vendor","value":"Wimolivier","href":"https://github.com/wimolivier/postgresql-best-practices","sourceUrl":"https://github.com/wimolivier/postgresql-best-practices","sourceType":"profile","confidence":"medium","observedAt":"2026-02-24T19:43:14.176Z","isPublic":true},{"factKey":"traction","category":"adoption","label":"Adoption signal","value":"1 GitHub stars","href":"https://github.com/wimolivier/postgresql-best-practices","sourceUrl":"https://github.com/wimolivier/postgresql-best-practices","sourceType":"profile","confidence":"medium","observedAt":"2026-02-24T19:43:14.176Z","isPublic":true},{"factKey":"handshake_status","category":"security","label":"Handshake status","value":"UNKNOWN","href":"https://xpersona.co/api/v1/agents/wimolivier-postgresql-best-practices/trust","sourceUrl":"https://xpersona.co/api/v1/agents/wimolivier-postgresql-best-practices/trust","sourceType":"trust","confidence":"medium","observedAt":null,"isPublic":true}],"highlights":["1 GitHub stars","Schema refs published","Trust evidence available"],"agentCard":{"name":"postgresql-best-practices","description":"PostgreSQL 18+ comprehensive best practices for enterprise database development. Provides schema\narchitecture patterns, Table API design, PL/pgSQL coding standards, migrations, and data warehousing.\n\nUSE THIS SKILL WHEN THE USER:\n- Creates PostgreSQL schemas, tables, functions, procedures, or triggers\n- Asks about PostgreSQL data types (uuid, text, timestamptz, jsonb, numeric)\n- Writes PL/pgSQL code and needs naming conventions (l_, in_, io_, co_ prefixes)\n- Implements Table API pattern (SECURITY DEFINER functions, schema separation)\n- Sets up database migrations or schema versioning\n- Needs index optimization, constraint design, or query performance help\n- Asks about PostgreSQL 18+ features (uuidv7, virtual columns, temporal constraints)\n- Builds data warehouses with Medallion Architecture (Bronze/Silver/Gold)\n- Needs data lineage tracking, ETL patterns, or audit logging\n- Reviews database code for best practices or anti-patterns\n- Migrates from Oracle PL/SQL to PostgreSQL PL/pgSQL\n- Sets up CI/CD pipelines for database changes\n\nCORE PATTERNS:\n- Three-schema separation: data (tables) → private (internal) → api (external)\n- Table API: All access through SECURITY DEFINER functions with SET search_path\n- Native migration system: Pure PL/pgSQL alternative to Flyway/Liquibase\n- Trivadis naming: l_ (local), in_ (input), io_ (inout), co_ (constant)","source":"GITHUB_OPENCLEW","sourceId":"github:1145256423","repository":"https://github.com/wimolivier/postgresql-best-practices","documentation":"https://xpersona.co/skill/wimolivier-postgresql-best-practices/agent/wimolivier-postgresql-best-practices","protocols":["OPENCLEW"],"capabilities":["inout"],"languages":["typescript"],"install":{"command":"git clone https://github.com/wimolivier/postgresql-best-practices.git","ecosystem":"git"},"examples":[{"kind":"example","language":"mermaid","snippet":"flowchart LR\n    APP[\"🖥️ Application\"]\n    \n    subgraph DB[\"PostgreSQL Database\"]\n        API[\"api schema<br/>─────────────<br/>get_customer()<br/>insert_order()\"]\n        PRIV[\"private schema<br/>─────────────<br/>set_updated_at()<br/>hash_password()\"]\n        DATA[(\"data schema<br/>─────────────<br/>customers<br/>orders\")]\n    end\n    \n    APP -->|\"EXECUTE\"| API\n    API -->|\"SECURITY DEFINER\"| DATA\n    API --> PRIV\n    PRIV -->|\"triggers\"| DATA\n    \n    APP x-.-x|\"❌ blocked\"| DATA\n    \n    style API fill:#c8e6c9\n    style PRIV fill:#fff3e0\n    style DATA fill:#e3f2fd"},{"kind":"example","language":"text","snippet":"Application → api schema → data schema\n                ↓\n            private schema (triggers, helpers)"}]}}