PostgreSQL Convention

The chuck-stack PostgreSQL conventions aim to create a consistent, convention-based, scalable, and maintainable database structure.

We provide two high-level features by which most applications are built:

  • Workflow
  • Attribute Tagging

The below convention summary creates a workflow and attributing tagging system that is both:

  • Generic enough to support a wide variety of use cases
  • Simple enough to support rapid adoption

You can always ask AI (using AIChat) about any chuck-stack specific convention or PostgreSQL best practices and options. For example, you can ask AI:

It seems more secure to disable PostgreSQL's TCP service and require clients to connect via unix socket. Is this true?

Convention Summary

Here are the goals that drive our conventions:

  • Maximize ↑ use of conventions and AI assistance to reduce learning/development time and complexity
  • Minimize ↓ the number of experts needed to articulate and deploy a new feature

Here is a summary of our conventions. Click on any link to learn more.

  1. Database Schema Structure:

    • Private schema (private) to encapsulate the internal data model and logic
    • Public API schema (api) to expose a public interface providing data and logic to the outside world in a controlled way

  2. Table Conventions:

    • Use a single UUID primary key column with a <table_name>_uu convention to support universal table_name + record_uu lookup across all tables
    • Prefix core tables with stk_
    • Use noun-first naming (e.g., stk_order_line)
    • Minimize abbreviations to a known list to ensure maximum schema readability

  3. Column Conventions:

    • Use mandatory columns: stk_entity_uu,created,created_by_uu,updated,updated_by_uu.
    • Use _uu suffix for primary and foreign keys
    • Use text type instead of varchar when possible
    • Boolean columns must have default values

  4. Enum and Type Conventions:

    • Use enums for code-level distinctions
    • Create facade type tables to reference enums in transactional tables

  5. Function Conventions:

    • Use _p suffix for function parameters
    • Use _v suffix for function variables

  6. Trigger Conventions:

    • Use private.stk_trigger_create() function to manage triggers across tables

  7. Attribute Tagging:

    • Use JSON for flexible attribute storage (stk_attribute_tag table)

  8. System Configuration:

    • Use stk_system_config table for system-wide settings

  9. Statistics:

    • Use stk_statistic table for denormalized statistical data

  10. Scalability Considerations:

    • Use connection pooling
    • Implement table partitioning
    • Utilize physical/streaming replicas and logical replicas

Copyright © , CHUBOE LLC. All rights reserved.