laneweaver-database-design_skill

Overview

This skill designs PostgreSQL 17 schemas for laneweaverTMS using Supabase conventions. It produces UUID-first table patterns, ENUM definitions, audit trails, soft-delete strategies, triggers, functions, views, and atomic migration patterns tailored for transport management data models. The goal is secure, auditable, and indexable schemas that work with Supabase realtime and RLS.

How this skill works

The skill generates schema artifacts and migration guidance: ENUM types created before tables, UUID primary keys (except users), required audit columns, CHECK/UNIQUE/FOREIGN KEY constraints, and recommended indexes. It also produces SECURITY INVOKER functions, reusable trigger functions (updated_at, validation, sync), audit triggers, and patterns for safe ENUM changes and atomic migrations. Supabase-friendly settings (search_path, permission patterns, and partial indexes for soft deletes) are applied throughout.

When to use it

• Designing new laneweaverTMS database tables or full schemas
• Building migrations that must be applied safely in production
• Implementing audit trails, soft deletes, and RLS-ready designs
• Defining domain ENUMs and type-safe constraints for business rules
• Creating trigger functions, audit logging, and denormalization syncs

Best practices

• Use UUID primary keys for all tables except users (INT4) and reference users via INT4 in audit columns
• Include created_at, updated_at, created_by, updated_by, deleted_at, deleted_by on every table
• Create ENUM types in separate migrations before using them in tables; add comments explaining lifecycle
• Never hard-delete: set deleted_at and deleted_by, and use partial indexes WHERE deleted_at IS NULL
• Manually index every foreign key and commonly filtered status/timestamp columns; use GIN for JSONB and array searches
• Write functions with SECURITY INVOKER and SET search_path = 'public'; use trigger functions for reusable logic

Example use cases

• Create a loads table with load_status ENUM, numeric rates, soft-delete, and audit columns
• Add a validation trigger to enforce commodity temperature ranges and units
• Implement create_load_from_tender function that generates load number, inserts load, and updates tender status atomically
• Add partial indexes for active loads and GIN index for equipment_types array searches
• Design migration to add a new ENUM value safely and update consuming code