PostgreSQL SQL style guide for consistent, readable database code. Use when writing SQL queries, creating tables, defining constraints, or reviewing PostgreSQL code for style compliance.
Inherits all available tools
Additional assets for this skill
This skill inherits all available tools. When active, it can use any tool Claude has access to.
yyyy-mm-ddThh:mm:ss.sssz)/* ... */ for blocks and -- for line commentspublic.) in queries for claritypublic.load_status, public.invoice_status)loads, carriers, accounts)load_id, not load_ids)load_id for a loads reference)-- ✓ 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)
id column as primary key (unless explicitly specified otherwise)uuid type for primary keys with default gen_random_uuid()bigint for user IDs and system references (references to users.id)uuid for cross-entity relationships (loads, carriers, accounts, etc.)created_at, updated_at, created_by, updated_bytimestamp with time zone for all temporal datacreated_at and updated_at to CURRENT_TIMESTAMPdeleted_at, deleted_by columnsupdated_at on every modificationcreate table public.table_name (...)not null for required columnsunique for business unique keys (e.g., mc_number on carriers)check for valid value ranges (e.g., temperature units, payment methods)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.';
text (not varchar(n)) unless you need to enforce a maximum lengthnumeric(precision, scale) for money values (e.g., numeric(12, 2))boolean with default false where applicabletimestamp with time zone for all temporal datatype enums for constrained domains (e.g., public.load_status)uuid for entity IDs, bigint for user/system references{singular_table_name}_id (e.g., carrier_id, load_id)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)_at suffix (e.g., created_at, approved_at, cancelled_at)generated always as for computed values-- ✓ 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
load_status, invoice_status, payment_method)'uncovered', 'in_transit', 'electronic_check')'Draft', 'Sent', 'Dispatcher')load_status, carrier_payment_status, customer_payment_status)-- 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'
);
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;
select, where, join, group by)public.loads over l)join ... on instead of where conditions for relationshipsa = b, not a=b; value, count, not value,countselect
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;
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;
as keyword for clarityselect
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;
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;
where deleted_at is null)-- 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);
-- Always explicit, typically UUID
id uuid default gen_random_uuid() primary key
-- 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)
-- 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))
-- 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 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
security_invoker='on' for views with RLS compliance_with_details, _with_financials)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.';
-- 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';
-- 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);
deleted_at timestamp with time zone columndeleted_by integer column for audit trailwhere deleted_at is null in application queriescreate 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;
comment on table public.loads is
'Core load/shipment records. See loads_with_financials view for calculated metrics.';
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).';
comment on function public.log_load_event(...) is
'Records state changes and events for load tracking and audit compliance.';
-- ✗ 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;
id uuid default gen_random_uuid()created_at, updated_at, created_by, updated_by{table_singular}_id naming{table}_{column}_fkey namingis_ or has_ prefixes or descriptive past participlesdeleted_at and deleted_bypublic. schema