Postgres SQL Style Guide

General Principles

• Use lowercase for SQL reserved words to maintain consistency and readability
• Employ consistent, descriptive identifiers for tables, columns, and other database objects
• Use white space and indentation to enhance code readability
• Store dates in ISO 8601 format (yyyy-mm-ddThh:mm:ss.sssz)
• Include comments for complex logic using /* ... */ for blocks and -- for line comments
• Always specify schema prefix (public.) in queries for clarity
• Prefer explicit over implicit—always name joins, FK relationships, and computed columns

---

Naming Conventions

General Rules

• Avoid SQL reserved words and ensure names are unique and under 63 characters
• Use snake_case for tables, columns, functions, and identifiers
• Use lowercase snake_case for custom enum types (e.g., public.load_status, public.invoice_status)
• Use plurals for table names (e.g., loads, carriers, accounts)
• Use singular names for columns (e.g., load_id, not load_ids)
• Prefix foreign key columns with the referenced table name (e.g., load_id for a loads reference)

Examples

-- ✓ Good
table: public.loads
column: load_id (references loads.id)
column: carrier_id (references carriers.id)
column: created_at, updated_at
column: is_cancelled, pod_received

-- ✗ Avoid
table: tbl_loads, Load, LOADs
column: id (ambiguous), load (too short), loadId (camelCase)
column: CreatedOn, created_date (inconsistent patterns)

---

Tables

Primary Keys & Identifiers

• Always add an id column as primary key (unless explicitly specified otherwise)
• Use uuid type for primary keys with default gen_random_uuid()
• Use bigint for user IDs and system references (references to users.id)
• Use uuid for cross-entity relationships (loads, carriers, accounts, etc.)

Timestamps & Audit Columns

• Always include audit columns: created_at, updated_at, created_by, updated_by
• Use timestamp with time zone for all temporal data
• Default created_at and updated_at to CURRENT_TIMESTAMP
• For soft deletes: add deleted_at, deleted_by columns
• Use triggers to auto-update updated_at on every modification

Table-Level Patterns

• Always specify the schema: create table public.table_name (...)
• Always add a descriptive comment (up to 1024 characters) explaining table purpose
• Create constraints for data integrity:
  • not null for required columns
  • unique for business unique keys (e.g., mc_number on carriers)
  • check for valid value ranges (e.g., temperature units, payment methods)

Example Table Definition

create table public.loads (
  id uuid default gen_random_uuid() primary key,
  load_number text not null,
  owner_id integer not null,
  account_id uuid not null,
  carrier_id uuid,
  load_status public.load_status,
  customer_rate numeric(12, 2),
  carrier_rate numeric(12, 2),
  is_cancelled boolean default false not null,
  cancelled_at timestamp with time zone,
  created_at timestamp with time zone default current_timestamp not null,
  updated_at timestamp with time zone default current_timestamp not null,
  created_by integer not null,
  updated_by integer,
  deleted_at timestamp with time zone,
  deleted_by integer,
  constraint loads_load_number_key unique (load_number),
  constraint loads_owner_id_fkey foreign key (owner_id) references public.users(id),
  constraint loads_account_id_fkey foreign key (account_id) references public.accounts(id),
  constraint loads_carrier_id_fkey foreign key (carrier_id) references public.carriers(id),
  constraint loads_created_by_fkey foreign key (created_by) references public.users(id),
  constraint loads_updated_by_fkey foreign key (updated_by) references public.users(id)
);

comment on table public.loads is
  'Core load/shipment records tracking full lifecycle from creation through delivery with financial metrics and A/P/A/R workflow.';

comment on column public.loads.is_cancelled is
  'Whether this load has been cancelled. See load_cancellations for detailed reasons and TONU charges.';

---

Columns

Data Types

• Text fields: Use text (not varchar(n)) unless you need to enforce a maximum length
• Numeric fields: Use numeric(precision, scale) for money values (e.g., numeric(12, 2))
• Boolean fields: Use boolean with default false where applicable
• Timestamps: Use timestamp with time zone for all temporal data
• Enums: Use custom type enums for constrained domains (e.g., public.load_status)
• IDs: Use uuid for entity IDs, bigint for user/system references

Column Naming

• Foreign key columns: {singular_table_name}_id (e.g., carrier_id, load_id)
• Boolean columns: Prefix with is_ or has_ when semantically appropriate (e.g., is_cancelled, is_active), or use descriptive past participles (e.g., pod_received, carrier_bill_received, invoice_ready)
• Date/time columns: Use _at suffix (e.g., created_at, approved_at, cancelled_at)
• Generated columns: Use generated always as for computed values

Example Column Patterns

-- ✓ Good patterns from LaneWeaver
id uuid default gen_random_uuid() primary key
load_id uuid not null references public.loads(id) on delete cascade
created_by integer not null references public.users(id)
updated_by integer references public.users(id)
is_cancelled boolean default false not null
pod_received boolean default false not null
approved_at timestamp with time zone
carrier_rate numeric(12, 2)
status public.invoice_status default 'Draft'::public.invoice_status

-- Audit trail columns (standard pattern)
created_at timestamp with time zone default current_timestamp not null
updated_at timestamp with time zone default current_timestamp not null
created_by integer not null
updated_by integer

-- Soft delete columns
deleted_at timestamp with time zone
deleted_by integer

-- Generated columns (read-only computed values)
invoice_ready boolean generated always as
  ((pod_received and carrier_bill_received)) stored

---

Type Enums

Defining Enums

• Create custom type enums for constrained domains
• Use lowercase snake_case for enum type names (e.g., load_status, invoice_status, payment_method)
• Use lowercase snake_case for enum values when representing system states or technical values (e.g., 'uncovered', 'in_transit', 'electronic_check')
• Use PascalCase for enum values when representing user-facing labels or business terms (e.g., 'Draft', 'Sent', 'Dispatcher')
• Prefix related enums with a common term (e.g., load_status, carrier_payment_status, customer_payment_status)

Enum Examples

-- System state enum (lowercase snake_case values)
create type public.load_status as enum (
  'uncovered',
  'assigned',
  'dispatched',
  'at_origin',
  'in_transit',
  'at_destination',
  'delivered'
);

comment on type public.load_status is
  'Load lifecycle status: uncovered → assigned → dispatched → at_origin → in_transit → at_destination → delivered';

-- Business term enum (PascalCase values)
create type public.invoice_status as enum (
  'Draft',
  'Sent',
  'Pending',
  'Paid',
  'Overdue',
  'Cancelled',
  'Disputed'
);

-- Technical value enum (lowercase snake_case values)
create type public.payment_method as enum (
  'ach',
  'electronic_check',
  'zelle',
  'wire'
);

-- User-facing role enum (PascalCase values)
create type public.carrier_contact_title as enum (
  'Dispatcher',
  'Driver',
  'Accounts Receivable',
  'Manager',
  'Owner'
);

---

Queries

Query Formatting

Small queries (fit on 3-5 lines): Keep compact

select id, name, status
from public.accounts
where status = 'Active' and deleted_at is null;

update public.carriers
set updated_at = now()
where id = $1;

Medium queries (6-10 lines): Add newlines around major clauses

select
  load_id,
  count(*) as num_stops
from public.stops
where load_id = $1
group by load_id
order by load_id;

Large queries (10+ lines): Use CTEs, format for readability

with load_summary as (
  -- Get financial metrics per load
  select
    id,
    load_number,
    customer_rate - carrier_rate as gross_profit,
    round(((customer_rate - carrier_rate) / customer_rate * 100), 2) as margin_pct
  from public.loads
  where deleted_at is null
),
top_loads as (
  -- Filter to highest margin loads
  select * from load_summary
  where margin_pct > 30
  order by margin_pct desc
  limit 10
)
select * from top_loads;

Style Rules

• Use lowercase for SQL keywords (select, where, join, group by)
• Use full table names in queries (prefer public.loads over l)
• Explicit joins over implicit: Use join ... on instead of where conditions for relationships
• Spaces for readability: a = b, not a=b; value, count, not value,count
• Line continuity: Indent sub-clauses consistently

---

Joins and Subqueries

Join Formatting

select
  loads.load_number,
  loads.customer_rate,
  loads.carrier_rate,
  carriers.name as carrier_name,
  accounts.name as account_name
from public.loads
join public.carriers on loads.carrier_id = carriers.id
join public.accounts on loads.account_id = accounts.id
where
  loads.load_status = 'delivered'
  and loads.deleted_at is null
  and carriers.status = 'Active'
order by loads.created_at desc;

Subquery Formatting

Avoid subqueries in select list (use joins or views instead)

-- ✓ Better: Use join
select
  l.id,
  l.load_number,
  count(s.id) as stop_count
from public.loads l
left join public.stops s on l.id = s.load_id
where l.deleted_at is null
group by l.id
order by l.id;

-- ✗ Avoid: Subquery in select
select
  id,
  load_number,
  (select count(*) from stops where load_id = l.id) as stop_count
from loads l;

---

Aliases

Alias Rules

• Use meaningful aliases that reflect the data or transformation
• Always include the as keyword for clarity
• Short aliases are OK for temporary CTEs, but be explicit in final select

Example

select
  count(*) as total_shipments,
  sum(customer_rate) as total_revenue,
  avg(customer_rate - carrier_rate) as avg_profit
from public.loads
where load_status = 'delivered' and deleted_at is null;

-- CTE with meaningful names
with carrier_loads as (
  select
    carrier_id,
    count(*) as total_loads,
    sum(customer_rate) as revenue
  from public.loads
  where deleted_at is null
  group by carrier_id
)
select
  carriers.name,
  cl.total_loads,
  cl.revenue
from public.carriers carriers
join carrier_loads cl on carriers.id = cl.carrier_id
order by cl.revenue desc;

---

Complex Queries and CTEs

CTE Patterns

• Use CTEs for complex logic to improve readability
• Maintain linear flow: top-level CTE first, then refine downward
• Comment each CTE block to explain its purpose
• Prefer readability over performance (within reason)

Comprehensive Example

with load_financials as (
  -- Calculate financial metrics for each completed load
  select
    id,
    load_number,
    account_id,
    carrier_id,
    customer_rate,
    carrier_rate,
    customer_rate - carrier_rate as gross_profit,
    case
      when customer_rate > 0 then
        round(((customer_rate - carrier_rate) / customer_rate * 100), 2)
      else 0
    end as profit_margin_pct
  from public.loads
  where load_status = 'delivered' and deleted_at is null
),
carrier_performance as (
  -- Aggregate performance by carrier
  select
    carrier_id,
    count(*) as num_loads,
    sum(gross_profit) as total_profit,
    round(avg(profit_margin_pct), 2) as avg_margin_pct
  from load_financials
  group by carrier_id
),
account_performance as (
  -- Aggregate performance by account
  select
    account_id,
    count(*) as num_loads,
    sum(gross_profit) as total_profit,
    round(avg(profit_margin_pct), 2) as avg_margin_pct
  from load_financials
  group by account_id
)
select
  cp.carrier_id,
  carriers.name as carrier_name,
  cp.num_loads,
  cp.total_profit,
  cp.avg_margin_pct
from carrier_performance cp
join public.carriers carriers on cp.carrier_id = carriers.id
order by cp.total_profit desc;

---

Indexes

Indexing Strategy

• Create indexes on frequently filtered columns (where, join conditions)
• Create indexes on sort columns (order by)
• Create composite indexes for multi-column filters
• Use partial indexes where queries filter to a subset (e.g., where deleted_at is null)
• Avoid indexing low-cardinality boolean columns unless they're primary filters

Example Index Patterns

-- Single column indexes
create index idx_loads_account_id on public.loads using btree (account_id);
create index idx_loads_carrier_id on public.loads using btree (carrier_id);
create index idx_loads_created_at on public.loads using btree (created_at);
create index idx_loads_load_status on public.loads using btree (load_status);

-- Partial index (only non-deleted records)
create index idx_loads_deleted_at on public.loads using btree (deleted_at)
where deleted_at is null;

-- Composite index for common joins
create index idx_carrier_bills_load_carrier on public.carrier_bills using btree (load_id, carrier_id);

-- Composite index for filtering + sorting
create index idx_loads_status_created on public.loads using btree (load_status, created_at desc);

-- GIN index for JSON/JSONB columns
create index idx_documents_metadata on public.documents using gin (metadata);

-- BRIN for time-series data (better for large tables)
create index idx_load_cognition_recorded_at on public.load_cognition using brin (recorded_at);

---

Constraints & Data Integrity

Primary Keys

-- Always explicit, typically UUID
id uuid default gen_random_uuid() primary key

Unique Constraints

-- Business unique keys
constraint loads_load_number_key unique (load_number),
constraint carriers_mc_number_key unique (mc_number),
constraint accounts_account_number_key unique (account_number),

-- Compound uniqueness (e.g., one record per load per reference type)
constraint load_references_unique_type_per_load
  unique (load_id, reference_type_id)

Check Constraints

-- Value range validation
constraint invoices_amount_positive check (amount > 0),
constraint invoices_amount_paid_positive check (amount_paid >= 0),
constraint invoices_amount_paid_not_exceed check (amount_paid <= amount),

-- Enum-like validation
constraint load_cognition_heading_degrees_check
  check (heading >= 0 and heading < 360),
constraint load_cognition_latitude_check
  check (latitude >= -90 and latitude <= 90),
constraint load_cognition_longitude_check
  check (longitude >= -180 and longitude <= 180),

-- Business logic
constraint factoring_company_required_check
  check ((uses_factoring_company = false) or
         (uses_factoring_company = true and factoring_company_id is not null))

Foreign Keys with Actions

-- Cascade deletes for dependent records
load_id uuid not null references public.loads(id) on delete cascade,

-- Prevent deletion if referenced
reference_type_id uuid not null references public.reference_types(id) on delete restrict,

-- Set null on deletion (allows orphaning)
carrier_id uuid references public.carriers(id) on delete set null

Foreign Key Constraint Naming

Foreign key constraints follow these naming patterns:

-- Standard pattern: {table}_{column}_fkey
constraint loads_account_id_fkey foreign key (account_id) references public.accounts(id)
constraint loads_carrier_id_fkey foreign key (carrier_id) references public.carriers(id)
constraint carrier_contacts_carrier_id_fkey foreign key (carrier_id) references public.carriers(id)

-- Alternative pattern: fk_{table}_{column} (used for newer tables)
constraint fk_carrier_tractors_created_by foreign key (created_by) references public.users(id)
constraint fk_virtual_yard_created_by foreign key (created_by) references public.users(id)

-- Prefer the standard {table}_{column}_fkey pattern for consistency

---

Views

View Patterns

• Use security_invoker='on' for views with RLS compliance
• Include detail in view names (e.g., _with_details, _with_financials)
• Document view purpose with comments
• Include calculated columns (gross profit %, aging buckets, etc.)

Example View

create view public.loads_with_financials with (security_invoker='on') as
select
  id,
  load_number,
  owner_id,
  account_id,
  carrier_id,
  load_status,
  customer_rate,
  carrier_rate,
  (coalesce(revenue, 0) - coalesce(carrier_spend, 0)) as gross_profit,
  case
    when coalesce(revenue, 0) > 0 then
      round(((coalesce(revenue, 0) - coalesce(carrier_spend, 0)) / revenue * 100), 2)
    else 0
  end as gross_profit_percentage,
  case
    when coalesce(total_miles, 0) > 0 then
      round(coalesce(carrier_rate, 0) / total_miles, 2)
    else 0
  end as carrier_rpm,
  case
    when coalesce(total_miles, 0) > 0 then
      round(coalesce(customer_rate, 0) / total_miles, 2)
    else 0
  end as customer_rpm
from public.loads l;

comment on view public.loads_with_financials is
  'Loads with calculated financial metrics. Uses security_invoker for RLS compliance.';

---

Triggers & Functions

Common Trigger Patterns

-- Auto-update timestamp
create function public.update_timestamp() returns trigger as $$
begin
  new.updated_at = now();
  return new;
end;
$$ language plpgsql;

create trigger update_loads_timestamp
before update on public.loads
for each row
execute function public.update_timestamp();

-- Sync related table state
create function public.sync_load_billing_flags() returns trigger as $$
begin
  update public.loads
  set
    pod_received = new.pod_received,
    carrier_bill_received = new.carrier_bill_received,
    updated_at = now()
  where id = new.load_id;
  return new;
end;
$$ language plpgsql;

comment on function public.sync_load_billing_flags() is
  'Keeps loads.pod_received and loads.carrier_bill_received in sync with load_billing table';

---

Row Level Security (RLS)

RLS Policy Patterns

-- Enable RLS on table
alter table public.loads enable row level security;

-- Authenticated users can select all
create policy "Authenticated users can select loads"
on public.loads
for select
to authenticated
using (true);

-- Authenticated users can insert
create policy "Authenticated users can insert loads"
on public.loads
for insert
to authenticated
with check (true);

-- Authenticated users can update their own records
create policy "Authenticated users can update loads"
on public.loads
for update
to authenticated
using (true)
with check (true);

-- Authenticated users can delete
create policy "Authenticated users can delete loads"
on public.loads
for delete
to authenticated
using (true);

---

Soft Deletes

Pattern

• Add deleted_at timestamp with time zone column
• Add deleted_by integer column for audit trail
• Always filter where deleted_at is null in application queries
• Create partial indexes on soft-deleted tables
• Use triggers to maintain referential integrity

Example

create table public.loads (
  -- ... other columns ...
  deleted_at timestamp with time zone,
  deleted_by integer references public.users(id)
);

-- Partial index for active records
create index idx_loads_deleted_at on public.loads
where deleted_at is null;

-- Standard query pattern (always filter)
select * from public.loads
where deleted_at is null
order by created_at desc;

---

Comments & Documentation

Table Comments

comment on table public.loads is
  'Core load/shipment records. See loads_with_financials view for calculated metrics.';

Column Comments

comment on column public.loads.is_cancelled is
  'Whether this load has been cancelled. See load_cancellations for detailed reasons.';

comment on column public.load_cognition.heading is
  'Direction of travel expressed in degrees from true north (0-359).';

Function Comments

comment on function public.log_load_event(...) is
  'Records state changes and events for load tracking and audit compliance.';

---

Common Anti-Patterns to Avoid

-- ✗ Don't use generic "id" in joins
select l.*, c.id from loads l join carriers c on l.carrier_id = c.id;

-- ✓ Use table-qualified columns
select
  loads.id, loads.load_number,
  carriers.id as carrier_id, carriers.name
from public.loads
join public.carriers on loads.carrier_id = carriers.id;

-- ✗ Don't omit schema prefix
select * from loads where deleted_at is null;

-- ✓ Always specify schema
select * from public.loads where deleted_at is null;

-- ✗ Don't use implicit joins
select * from loads, carriers where loads.carrier_id = carriers.id;

-- ✓ Use explicit joins
select * from public.loads
join public.carriers on loads.carrier_id = carriers.id;

-- ✗ Don't filter soft-deleted in application
select * from public.loads order by created_at desc;

-- ✓ Always filter deleted records at database level
select * from public.loads
where deleted_at is null
order by created_at desc;

-- ✗ Don't mix case conventions
select LoadNumber, Carrier_ID, customer_RATE from public.loads;

-- ✓ Use consistent snake_case
select load_number, carrier_id, customer_rate from public.loads;

---

Summary Checklist

• Table names are plural and snake_case
• Primary key is id uuid default gen_random_uuid()
• All tables have created_at, updated_at, created_by, updated_by
• Foreign keys use {table_singular}_id naming
• Foreign key constraints use {table}_{column}_fkey naming
• Boolean columns use is_ or has_ prefixes or descriptive past participles
• Soft deletes include deleted_at and deleted_by
• Every table has a descriptive comment
• Enum types use lowercase snake_case naming
• Enum values use lowercase snake_case for system states or PascalCase for business terms
• Queries specify the public. schema
• Large queries use CTEs with meaningful names
• Frequently filtered columns have indexes
• Soft-deleted tables use partial indexes
• All constraints are explicit and named
• RLS policies are defined for all tables (if using Supabase)