Design PostgreSQL 17 schemas for laneweaverTMS using Supabase conventions - UUIDs, ENUMs, audit trails, soft deletes, triggers, functions, views, and atomic migration patterns.
This skill inherits all available tools. When active, it can use any tool Claude has access to.
Use when:
-- ✅ Correct: UUID primary key
CREATE TABLE public.loads (
id UUID DEFAULT gen_random_uuid() NOT NULL,
load_number TEXT NOT NULL,
-- ...
CONSTRAINT loads_pkey PRIMARY KEY (id)
);
Why UUIDs?
-- ✅ Exception: users table uses INT4
CREATE TABLE public.users (
id INT4 GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
-- ...
);
Impact: All audit columns (created_by, updated_by, deleted_by) use INT4 to reference users.id.
Every table MUST include these audit columns:
CREATE TABLE public.loads (
id UUID DEFAULT gen_random_uuid() NOT NULL,
-- Business fields...
-- Standard audit columns (REQUIRED ON ALL TABLES)
created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
created_by INT4, -- References users.id
updated_by INT4, -- References users.id
deleted_at TIMESTAMPTZ, -- Soft delete: NULL = active
deleted_by INT4, -- User who deleted the record
CONSTRAINT loads_pkey PRIMARY KEY (id)
);
deleted_at TIMESTAMPTZ (NULL = active, non-NULL = deleted)UPDATE SET deleted_at = now()WHERE deleted_at IS NULL-- ✅ Correct: Soft delete
UPDATE loads SET deleted_at = now(), deleted_by = $1 WHERE id = $2;
-- ❌ Wrong: Hard delete
DELETE FROM loads WHERE id = $1;
-- ✅ Correct: Query active records only
SELECT * FROM loads WHERE deleted_at IS NULL;
| Data | Type | Example | Why |
|---|---|---|---|
| IDs | UUID | id UUID DEFAULT gen_random_uuid() | Uniqueness, security, federation |
| Timestamps | TIMESTAMPTZ | created_at TIMESTAMPTZ DEFAULT now() | Timezone awareness |
| Money | NUMERIC(10,2) | customer_rate NUMERIC(10,2) | Exact precision for currency |
| Strings | TEXT | load_number TEXT NOT NULL | No length limits, simpler |
| Booleans | BOOLEAN | is_cancelled BOOLEAN DEFAULT false | True/false values |
| Integers | INT4, BIGINT | sequence_number INT4 | Whole numbers |
| Floats | DOUBLE PRECISION | latitude DOUBLE PRECISION | Approximate (GPS coords) |
| JSON | JSONB | line_items JSONB DEFAULT '{}'::jsonb | Flexible structured data |
| Arrays | TEXT[] | equipment_types TEXT[] | Ordered lists |
-- ❌ NEVER use these types:
TIMESTAMP -- Missing timezone → Use TIMESTAMPTZ
VARCHAR(n) -- Arbitrary limits → Use TEXT
CHAR(n) -- Fixed length → Use TEXT
MONEY -- Currency type → Use NUMERIC(10,2)
SERIAL -- Auto-increment → Use UUID or GENERATED ALWAYS AS IDENTITY
BIGSERIAL -- Auto-increment → Use UUID or GENERATED ALWAYS AS IDENTITY
JSON -- Slower than JSONB → Use JSONB
REAL -- Imprecise for money → Use NUMERIC
FLOAT -- Imprecise for money → Use NUMERIC
Use PostgreSQL ENUMs for:
laneweaverTMS has 32+ ENUMs defined:
load_status, tender_status, invoice_status, carrier_bill_statuscall_outcome, task_status_enum, task_priority_enummode_of_transport_list, stop_type_list, trailer_requirements_listaccessorial_category_type, email_status, feed_item_type-- Migration: Create load_status ENUM
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: uncovered → assigned → dispatched → at_origin → in_transit → at_destination → delivered';
Pattern:
snake_case (e.g., 'in_transit', 'left_voicemail')CREATE TABLE public.loads (
id UUID DEFAULT gen_random_uuid() NOT NULL,
load_status public.load_status DEFAULT 'uncovered'::public.load_status NOT NULL,
-- ...
);
-- ✅ Safe: Adding values (no table rewrite)
ALTER TYPE load_status ADD VALUE 'cancelled';
-- ❌ Risky: Removing values (requires recreation)
-- Must create new type, migrate data, drop old type, rename new type
Use for business rules and validation:
-- ✅ Positive amounts
ALTER TABLE loads ADD CONSTRAINT chk_loads_customer_rate_positive
CHECK (customer_rate > 0);
-- ✅ Valid ranges
ALTER TABLE load_cognition ADD CONSTRAINT chk_load_cognition_latitude_range
CHECK (latitude >= -90 AND latitude <= 90);
-- ✅ Logical consistency
ALTER TABLE stops ADD CONSTRAINT chk_stops_appointment_logic
CHECK (
(appointment_required = false AND appointment_time IS NULL)
OR (appointment_required = true AND appointment_time IS NOT NULL)
);
-- ✅ JSONB structure validation
ALTER TABLE customer_invoices ADD CONSTRAINT chk_invoices_line_items_object
CHECK (jsonb_typeof(line_items) = 'object');
-- ✅ ENUM validation for polymorphic types
ALTER TABLE documents ADD CONSTRAINT chk_documents_documentable_type
CHECK (documentable_type = ANY (ARRAY['load'::text, 'account'::text, 'carrier'::text, 'facility'::text, 'rfp'::text]));
-- ✅ Natural keys
ALTER TABLE loads ADD CONSTRAINT loads_load_number_key
UNIQUE (load_number);
-- ✅ Business uniqueness
ALTER TABLE carriers ADD CONSTRAINT carriers_mc_number_key
UNIQUE (mc_number);
-- ✅ Composite uniqueness
ALTER TABLE load_references ADD CONSTRAINT load_references_load_reference_unique
UNIQUE (load_id, reference_type_id);
-- ✅ One NULL allowed (PostgreSQL 15+)
ALTER TABLE carriers ADD CONSTRAINT uq_carriers_dot_number
UNIQUE NULLS NOT DISTINCT (dot_number);
-- ✅ CASCADE: Delete children when parent deleted
ALTER TABLE stops
ADD CONSTRAINT stops_load_id_fkey
FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE CASCADE;
-- ✅ SET NULL: Preserve record, nullify FK
ALTER TABLE loads
ADD CONSTRAINT loads_tender_id_fkey
FOREIGN KEY (tender_id) REFERENCES tenders(id) ON DELETE SET NULL;
-- ✅ RESTRICT: Prevent deletion if children exist
ALTER TABLE loads
ADD CONSTRAINT loads_account_id_fkey
FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE RESTRICT;
-- ✅ Audit columns: Always SET NULL
ALTER TABLE loads
ADD CONSTRAINT loads_created_by_fkey
FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL;
PostgreSQL does NOT auto-index foreign keys. You MUST create indexes manually.
-- ✅ Required: Index all foreign keys
CREATE INDEX idx_loads_tender_id ON public.loads(tender_id);
CREATE INDEX idx_loads_carrier_id ON public.loads(carrier_id);
CREATE INDEX idx_stops_load_id ON public.stops(load_id);
CREATE INDEX idx_stops_facility_id ON public.stops(facility_id);
-- ✅ Soft deletes: Index active records only
CREATE INDEX idx_loads_deleted_at ON public.loads(deleted_at)
WHERE deleted_at IS NULL;
-- ✅ Nullable FKs: Index non-null values only
CREATE INDEX idx_loads_tender_id ON public.loads(tender_id)
WHERE tender_id IS NOT NULL;
-- ✅ Conditional indexes for specific queries
CREATE INDEX idx_carrier_bills_quick_pay ON public.carrier_bills(quick_pay_requested)
WHERE quick_pay_requested = true;
-- ✅ Status columns (for filtering)
CREATE INDEX idx_loads_load_status ON public.loads(load_status);
CREATE INDEX idx_carrier_bills_bill_status ON public.carrier_bills(bill_status);
-- ✅ Timestamp columns (for sorting, filtering, range queries)
CREATE INDEX idx_loads_created_at ON public.loads(created_at);
CREATE INDEX idx_calls_called_at ON public.calls(called_at);
CREATE INDEX idx_carrier_bills_scheduled_payment_date ON public.carrier_bills(scheduled_payment_date);
-- ✅ Partial indexes for audit columns
CREATE INDEX idx_loads_created_by ON public.loads(created_by)
WHERE created_by IS NOT NULL;
CREATE INDEX idx_loads_updated_by ON public.loads(updated_by)
WHERE updated_by IS NOT NULL;
-- ✅ JSONB containment queries
CREATE INDEX idx_facilities_operating_hours ON public.facilities
USING GIN (operating_hours);
-- ✅ Array containment
CREATE INDEX idx_loads_equipment_types ON public.loads
USING GIN (equipment_types);
-- ✅ Full-text search
CREATE INDEX idx_accounts_search ON public.accounts
USING GIN (to_tsvector('english', name || ' ' || COALESCE(domain_name, '')));
Pattern: idx_[table]_[column(s)]
CREATE INDEX idx_loads_account_id ON loads(account_id);
CREATE INDEX idx_loads_load_status ON loads(load_status);
CREATE INDEX idx_carrier_bounces_carrier_id ON carrier_bounces(carrier_id);
All functions MUST use:
SECURITY INVOKER - Runs with caller's permissions (not definer's)SET search_path = 'public' - Prevents search path injection attacksCREATE OR REPLACE FUNCTION public.generate_load_number()
RETURNS TEXT
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = 'public'
AS $$
DECLARE
v_seq_val BIGINT;
BEGIN
SELECT nextval('public.load_number_seq') INTO v_seq_val;
RETURN 'L-' || lpad(v_seq_val::text, 6, '0');
END;
$$;
COMMENT ON FUNCTION public.generate_load_number() IS
'Generates sequential load number in format L-XXXXXX using load_number_seq';
-- ✅ Updated_at trigger function (reusable)
CREATE OR REPLACE FUNCTION public.update_timestamp()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = 'public'
AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$;
-- ✅ Sync trigger for denormalization
CREATE OR REPLACE FUNCTION public.sync_load_cancelled_status()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = 'public'
AS $$
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE loads SET is_cancelled = true WHERE id = NEW.load_id;
ELSIF TG_OP = 'DELETE' THEN
UPDATE loads SET is_cancelled = false WHERE id = OLD.load_id;
END IF;
RETURN NULL;
END;
$$;
COMMENT ON FUNCTION public.sync_load_cancelled_status() IS
'Syncs loads.is_cancelled when load_cancellations records are inserted/deleted';
-- ✅ Validation trigger
CREATE OR REPLACE FUNCTION public.validate_commodity_temperature()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = 'public'
AS $$
BEGIN
IF NEW.temperature_min IS NOT NULL AND NEW.temperature_max IS NOT NULL THEN
IF NEW.temperature_min > NEW.temperature_max THEN
RAISE EXCEPTION 'temperature_min cannot be greater than temperature_max';
END IF;
END IF;
IF NEW.temperature_unit IS NOT NULL AND NEW.temperature_unit NOT IN ('F', 'C') THEN
RAISE EXCEPTION 'temperature_unit must be F or C';
END IF;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION public.create_load_from_tender(
p_tender_id UUID,
p_user_id UUID,
p_carrier_id UUID
)
RETURNS UUID
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = 'public'
AS $$
DECLARE
v_load_id UUID;
v_load_number TEXT;
BEGIN
-- Generate load number
v_load_number := public.generate_load_number();
-- Create load from tender
INSERT INTO loads (
id, load_number, tender_id, carrier_id,
load_status, created_by, updated_by
)
VALUES (
gen_random_uuid(), v_load_number, p_tender_id, p_carrier_id,
'assigned'::load_status, p_user_id, p_user_id
)
RETURNING id INTO v_load_id;
-- Update tender status
UPDATE tenders
SET tender_status = 'planned'::tender_status,
planned_at = now(),
updated_by = p_user_id
WHERE id = p_tender_id;
RETURN v_load_id;
END;
$$;
COMMENT ON FUNCTION public.create_load_from_tender(UUID, UUID, UUID) IS
'Creates load from tender, generates L-XXXXXX number, updates tender status to planned';
Every table should have:
CREATE TRIGGER trg_loads_updated_at
BEFORE UPDATE ON public.loads
FOR EACH ROW
EXECUTE FUNCTION public.update_timestamp();
CREATE TRIGGER audit_loads_trigger
AFTER INSERT OR UPDATE OR DELETE ON public.loads
FOR EACH ROW
EXECUTE FUNCTION public.audit_trigger_function();
-- Sync load billing flags to loads table
CREATE TRIGGER trg_sync_load_billing_flags
AFTER INSERT OR UPDATE OF pod_received, carrier_bill_received ON public.load_billing
FOR EACH ROW
EXECUTE FUNCTION public.sync_load_billing_flags();
-- Sync cancelled status from load_cancellations
CREATE TRIGGER trg_sync_load_cancelled_status
AFTER INSERT OR DELETE ON public.load_cancellations
FOR EACH ROW
EXECUTE FUNCTION public.sync_load_cancelled_status();
-- Validate commodity temperature range
CREATE TRIGGER validate_commodity_temperature
BEFORE INSERT OR UPDATE ON public.commodities
FOR EACH ROW
EXECUTE FUNCTION public.validate_commodity_temperature();
-- Enforce driver title requirement
CREATE TRIGGER enforce_driver_title_trigger
BEFORE INSERT OR UPDATE ON public.load_cognition
FOR EACH ROW
EXECUTE FUNCTION public.validate_driver_title();
-- Auto-generate tender number
CREATE TRIGGER trg_set_tender_number
BEFORE INSERT ON public.tenders
FOR EACH ROW
EXECUTE FUNCTION public.set_tender_number();
-- Auto-create load versions
CREATE TRIGGER trg_load_versioning
AFTER INSERT OR UPDATE ON public.loads
FOR EACH ROW
EXECUTE FUNCTION public.create_load_version();
All views MUST use WITH (security_invoker='on') for Row-Level Security compatibility:
CREATE OR REPLACE VIEW public.loads_with_financials
WITH (security_invoker = on)
AS
SELECT
l.id,
l.load_number,
l.load_status,
l.customer_rate,
l.carrier_rate,
-- Calculated financial metrics
(l.customer_rate - COALESCE(l.carrier_rate, 0)) AS gross_profit,
CASE
WHEN l.customer_rate > 0
THEN ((l.customer_rate - COALESCE(l.carrier_rate, 0)) / l.customer_rate * 100)
ELSE 0
END AS profit_margin_percent,
-- Aggregate accessorials
(SELECT COALESCE(SUM(amount), 0)
FROM customer_accessorials
WHERE load_id = l.id AND deleted_at IS NULL) AS customer_accessorials_total,
(SELECT COALESCE(SUM(amount), 0)
FROM carrier_accessorials
WHERE load_id = l.id AND deleted_at IS NULL) AS carrier_accessorials_total,
-- Net profit
((l.customer_rate + (SELECT COALESCE(SUM(amount), 0) FROM customer_accessorials WHERE load_id = l.id AND deleted_at IS NULL)) -
(COALESCE(l.carrier_rate, 0) + (SELECT COALESCE(SUM(amount), 0) FROM carrier_accessorials WHERE load_id = l.id AND deleted_at IS NULL))) AS net_profit
FROM public.loads l
WHERE l.deleted_at IS NULL;
COMMENT ON VIEW public.loads_with_financials IS
'Loads with calculated financial metrics (gross profit, margin %, accessorials, net profit)';
CREATE OR REPLACE VIEW public.life_of_load_flow
WITH (security_invoker = on)
AS
SELECT
-- Load
l.id AS load_id,
l.load_number,
l.load_status,
-- Tender
t.id AS tender_id,
t.tender_number,
t.tender_status,
-- Quote
cq.id AS quote_id,
cq.quote_number,
-- Account
a.id AS account_id,
a.name AS account_name,
-- Financial summary
l.customer_rate,
l.carrier_rate,
(l.customer_rate - COALESCE(l.carrier_rate, 0)) AS gross_profit,
-- Billing status
lb.pod_received,
lb.carrier_bill_received,
lb.invoice_ready
FROM public.loads l
LEFT JOIN public.tenders t ON l.tender_id = t.id
LEFT JOIN public.customer_quotes cq ON t.quote_id = cq.id
LEFT JOIN public.accounts a ON t.account_id = a.id
LEFT JOIN public.load_billing lb ON l.id = lb.load_id
WHERE l.deleted_at IS NULL;
COMMENT ON VIEW public.life_of_load_flow IS
'Complete load lifecycle: quote → tender → load → billing with financial metrics';
One logical change per migration file:
Format: [YYYYMMDDHHMMSS]_[descriptive_name].sql
Examples:
20251216171743_create_tender_status_enum.sql20251217055251_create_carrier_bounces_table.sql20251216172549_add_tenders_indexes_and_audit.sql20251217060132_add_carrier_bounces_updated_at_trigger.sql-- Header comment describing purpose
-- Migration: Create carrier_bounces table for tracking carrier falloffs
-- Create table with inline constraints
CREATE TABLE public.carrier_bounces (
-- Primary key
id UUID DEFAULT gen_random_uuid() NOT NULL,
-- Foreign keys
carrier_id UUID NOT NULL,
load_id UUID NOT NULL,
-- Core fields
reason TEXT,
bounce_time TIMESTAMPTZ NOT NULL DEFAULT now(),
carrier_rate NUMERIC(10,2),
-- Standard audit columns (REQUIRED)
created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
created_by INT4,
updated_by INT4,
deleted_at TIMESTAMPTZ,
deleted_by INT4,
-- Constraints
CONSTRAINT carrier_bounces_pkey PRIMARY KEY (id),
CONSTRAINT fk_carrier_bounces_carrier_id
FOREIGN KEY (carrier_id) REFERENCES carriers(id) ON DELETE RESTRICT,
CONSTRAINT fk_carrier_bounces_load_id
FOREIGN KEY (load_id) REFERENCES loads(id) ON DELETE RESTRICT
);
-- Table comment
COMMENT ON TABLE public.carrier_bounces IS
'Tracks when carriers back out of committed loads. Used for reliability scoring and bounce history.';
-- Column comments
COMMENT ON COLUMN public.carrier_bounces.carrier_id IS 'Carrier that bounced on the load';
COMMENT ON COLUMN public.carrier_bounces.load_id IS 'Load the carrier bounced from';
COMMENT ON COLUMN public.carrier_bounces.reason IS 'Reason provided for the bounce (free text)';
COMMENT ON COLUMN public.carrier_bounces.bounce_time IS 'Timestamp when bounce occurred';
-- Migration: Add indexes to carrier_bounces table
-- Foreign key indexes (REQUIRED - PostgreSQL doesn't auto-index FKs)
CREATE INDEX idx_carrier_bounces_carrier_id
ON public.carrier_bounces(carrier_id);
CREATE INDEX idx_carrier_bounces_load_id
ON public.carrier_bounces(load_id);
-- Soft delete partial index
CREATE INDEX idx_carrier_bounces_deleted_at
ON public.carrier_bounces(deleted_at)
WHERE deleted_at IS NULL;
-- Timestamp index for filtering
CREATE INDEX idx_carrier_bounces_bounce_time
ON public.carrier_bounces(bounce_time);
-- Audit column indexes
CREATE INDEX idx_carrier_bounces_created_by
ON public.carrier_bounces(created_by)
WHERE created_by IS NOT NULL;
-- Migration: Add triggers to carrier_bounces table
-- Updated_at trigger
CREATE TRIGGER trg_carrier_bounces_updated_at
BEFORE UPDATE ON public.carrier_bounces
FOR EACH ROW
EXECUTE FUNCTION public.update_timestamp();
COMMENT ON TRIGGER trg_carrier_bounces_updated_at ON public.carrier_bounces IS
'Automatically updates updated_at timestamp on row modification';
-- Audit trigger
CREATE TRIGGER audit_carrier_bounces_trigger
AFTER INSERT OR UPDATE OR DELETE ON public.carrier_bounces
FOR EACH ROW
EXECUTE FUNCTION public.audit_trigger_function();
COMMENT ON TRIGGER audit_carrier_bounces_trigger ON public.carrier_bounces IS
'Logs all changes to audit_log table for compliance tracking';
Allows a single table to reference multiple entity types:
-- ✅ Polymorphic: calls can reference accounts, contacts, RFPs, loads
CREATE TABLE public.calls (
id UUID DEFAULT gen_random_uuid() NOT NULL,
-- Polymorphic relationship
related_to_table TEXT NOT NULL, -- 'account', 'contact', 'rfp', 'load'
related_to_id UUID NOT NULL,
-- Call fields
call_outcome public.call_outcome,
called_at TIMESTAMPTZ DEFAULT now() NOT NULL,
notes TEXT,
-- Audit columns...
CONSTRAINT calls_pkey PRIMARY KEY (id),
CONSTRAINT chk_calls_related_to_table
CHECK (related_to_table = ANY (ARRAY['account'::text, 'contact'::text, 'rfp'::text, 'load'::text]))
);
-- Index for polymorphic lookup
CREATE INDEX idx_calls_related_to
ON public.calls(related_to_table, related_to_id);
-- Query calls for specific account
SELECT * FROM calls
WHERE related_to_table = 'account'
AND related_to_id = '123e4567-e89b-12d3-a456-426614174000';
related_to_table + related_to_id (accounts, contacts, RFPs, loads)documentable_type + documentable_id (loads, accounts, carriers, facilities, rfps)-- ✅ Generated column for invoice readiness
ALTER TABLE public.load_billing
ADD COLUMN invoice_ready BOOLEAN
GENERATED ALWAYS AS (pod_received AND carrier_bill_received) STORED;
COMMENT ON COLUMN public.load_billing.invoice_ready IS
'Generated: true when both POD and carrier bill are received';
-- Query using generated column
SELECT * FROM load_billing WHERE invoice_ready = true;
-- Index on generated column
CREATE INDEX idx_load_billing_invoice_ready
ON public.load_billing(invoice_ready)
WHERE invoice_ready = true;
-- Enable RLS
ALTER TABLE loads ENABLE ROW LEVEL SECURITY;
-- Development policy (permissive for authenticated users)
CREATE POLICY "Authenticated users can select loads"
ON loads
FOR SELECT
TO authenticated
USING (true);
-- Production policy (organization isolation)
CREATE POLICY "Users see only their organization's loads"
ON loads
FOR SELECT
TO authenticated
USING (
account_id IN (
SELECT account_id
FROM user_accounts
WHERE user_id = auth.uid()
)
);
-- Admin bypass
CREATE POLICY "Admins see all loads"
ON loads
FOR ALL
TO admin_users
USING (true)
WITH CHECK (true);
Use JSONB for:
Examples in laneweaverTMS:
facilities.operating_hours - Flexible schedule datacustomer_invoices.line_items - Invoice line item detailsaudit_log.old_data, audit_log.new_data - Change trackingtenders.edi_raw_data - EDI 204 payload-- ✅ Default to empty object (avoid NULL checks)
ALTER TABLE facilities
ADD COLUMN operating_hours JSONB NOT NULL DEFAULT '{}'::jsonb;
-- ✅ CHECK constraint for structure
ALTER TABLE customer_invoices
ADD CONSTRAINT chk_invoices_line_items_object
CHECK (jsonb_typeof(line_items) = 'object');
-- ✅ GIN index for containment queries
CREATE INDEX idx_facilities_operating_hours
ON facilities USING GIN (operating_hours);
-- ✅ Extract frequently queried fields as generated columns
ALTER TABLE facilities
ADD COLUMN is_open_24_7 BOOLEAN
GENERATED ALWAYS AS (
(operating_hours->>'is_24_7')::boolean
) STORED;
Schema Design:
□ UUID primary keys on all tables (except users which uses INT4)
□ Required audit columns on ALL tables (created_at, updated_at, created_by, updated_by, deleted_at, deleted_by)
□ Soft delete pattern with deleted_at (no hard deletes)
□ Foreign keys with appropriate CASCADE/SET NULL/RESTRICT
□ CHECK constraints for business validation
□ UNIQUE constraints for natural keys
□ ENUMs for stable value sets (status workflows, categories)
□ TIMESTAMPTZ for all timestamps (NEVER TIMESTAMP)
□ TEXT for strings (NEVER VARCHAR or CHAR)
□ NUMERIC for money (NEVER REAL, FLOAT, or MONEY type)
□ JSONB for flexible data (NEVER JSON)
Indexes:
□ All foreign keys manually indexed (PostgreSQL doesn't auto-index)
□ Partial indexes for soft deletes (WHERE deleted_at IS NULL)
□ Partial indexes for nullable FKs (WHERE column IS NOT NULL)
□ Status columns indexed
□ Timestamp columns indexed
□ GIN indexes for JSONB containment queries
□ Composite indexes for multi-column queries
Functions & Triggers:
□ All functions use SECURITY INVOKER + SET search_path = 'public'
□ Updated_at trigger on all tables
□ Audit trigger on all tables (if audit_log enabled)
□ Sync triggers for denormalized columns
□ Validation triggers for business rules
□ COMMENT on all functions and triggers
Views:
□ All views use WITH (security_invoker = on)
□ Views filter deleted_at IS NULL for soft deletes
□ COMMENT on all views explaining purpose
Migrations:
□ Atomic migrations (one operation per file)
□ Naming: [YYYYMMDDHHMMSS]_[descriptive_name].sql
□ Header comment describing purpose
□ COMMENT on all tables, columns, functions, triggers, views
□ Separate files for ENUMs, tables, indexes, triggers, functions, views
□ Version controlled in supabase/migrations/
RLS:
□ RLS enabled on all tables
□ Policies defined for SELECT, INSERT, UPDATE, DELETE
□ Admin bypass policies for administrative users
Authoritative Examples (within laneweaverTMS repository):
./erd.sql (root of repository)supabase/migrations/supabase/CLAUDE.mdExternal Resources:
Remember: Consistency is critical. Every table follows the same patterns for audit columns, soft deletes, indexing, triggers, and comments. This makes the codebase predictable and maintainable.