Database Schema

Overview

The Transport Logistics system uses PostgreSQL (via Supabase) with a relational schema optimized for fleet management, shipment tracking, and user management. All tables implement Row Level Security (RLS) for fine-grained access control.

The database schema is automatically synced to TypeScript types in src/integrations/supabase/types.ts, providing full type safety throughout the application.

Core Tables

vehicles

Stores information about transport vehicles in the fleet.

CREATE TABLE vehicles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  vehicle_number VARCHAR UNIQUE NOT NULL,
  vehicle_type VARCHAR NOT NULL,
  capacity NUMERIC NOT NULL,
  status VARCHAR DEFAULT 'available',
  active BOOLEAN DEFAULT true,
  last_maintenance TIMESTAMP,
  transporter_id UUID NOT NULL REFERENCES transporters(id),
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);

Columns:

Column	Type	Description
`id`	UUID	Primary key
`vehicle_number`	VARCHAR	Unique vehicle registration number
`vehicle_type`	VARCHAR	Type of vehicle (truck, trailer, etc.)
`capacity`	NUMERIC	Maximum load capacity in tons
`status`	VARCHAR	Current status (available, in-transit, maintenance)
`active`	BOOLEAN	Whether the vehicle is active
`last_maintenance`	TIMESTAMP	Last maintenance date
`transporter_id`	UUID	Foreign key to transporters table
`created_at`	TIMESTAMP	Record creation timestamp
`updated_at`	TIMESTAMP	Last update timestamp

Relationships:

transporter_id → transporters.id (many-to-one)
Referenced by shipments.vehicle_id

Constraints:

vehicles_vehicle_number_unique - Ensures vehicle numbers are unique

shipments

Tracks individual shipment records with route, material, and vehicle information.

CREATE TABLE shipments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source VARCHAR NOT NULL,
  destination VARCHAR NOT NULL,
  departure_time TIMESTAMP NOT NULL,
  arrival_time TIMESTAMP,
  quantity_tons NUMERIC NOT NULL,
  gross_weight NUMERIC,
  tare_weight NUMERIC,
  status VARCHAR DEFAULT 'pending',
  active BOOLEAN DEFAULT true,
  remarks TEXT,
  vehicle_id UUID NOT NULL REFERENCES vehicles(id),
  transporter_id UUID NOT NULL REFERENCES transporters(id),
  material_id UUID REFERENCES materials(id),
  route_id UUID REFERENCES routes(id),
  package_id UUID REFERENCES packages(id),
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);

Columns:

Column	Type	Description
`id`	UUID	Primary key
`source`	VARCHAR	Origin location
`destination`	VARCHAR	Destination location
`departure_time`	TIMESTAMP	Scheduled/actual departure time
`arrival_time`	TIMESTAMP	Actual arrival time (nullable)
`quantity_tons`	NUMERIC	Shipment quantity in tons
`gross_weight`	NUMERIC	Total weight including vehicle
`tare_weight`	NUMERIC	Empty vehicle weight
`status`	VARCHAR	Shipment status (pending, in-transit, delivered, cancelled)
`active`	BOOLEAN	Whether the shipment is active
`remarks`	TEXT	Additional notes
`vehicle_id`	UUID	Foreign key to vehicles table
`transporter_id`	UUID	Foreign key to transporters table
`material_id`	UUID	Foreign key to materials table
`route_id`	UUID	Foreign key to routes table
`package_id`	UUID	Foreign key to packages table
`created_at`	TIMESTAMP	Record creation timestamp
`updated_at`	TIMESTAMP	Last update timestamp

Relationships:

vehicle_id → vehicles.id (many-to-one)
transporter_id → transporters.id (many-to-one)
material_id → materials.id (many-to-one, optional)
route_id → routes.id (many-to-one, optional)
package_id → packages.id (many-to-one, optional)

packages

Organizes shipments into logical packages for tracking and assignment.

CREATE TABLE packages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR NOT NULL,
  status VARCHAR DEFAULT 'active',
  active BOOLEAN DEFAULT true,
  created_by_id UUID NOT NULL,
  shipment_id UUID REFERENCES shipments(id),
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);

Columns:

Column	Type	Description
`id`	UUID	Primary key
`name`	VARCHAR	Package name/identifier
`status`	VARCHAR	Package status
`active`	BOOLEAN	Whether the package is active
`created_by_id`	UUID	User who created the package
`shipment_id`	UUID	Associated shipment (optional)
`created_at`	TIMESTAMP	Record creation timestamp
`updated_at`	TIMESTAMP	Last update timestamp

Relationships:

shipment_id → shipments.id (many-to-one, optional)
Referenced by routes.assigned_package_id
Referenced by shipments.package_id
Referenced by profiles.assigned_packages (array)

materials

Defines the types of materials that can be transported.

CREATE TABLE materials (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR NOT NULL,
  description TEXT,
  unit VARCHAR NOT NULL,
  status VARCHAR DEFAULT 'active',
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);

Columns:

Column	Type	Description
`id`	UUID	Primary key
`name`	VARCHAR	Material name
`description`	TEXT	Material description
`unit`	VARCHAR	Unit of measurement (tons, kg, etc.)
`status`	VARCHAR	Material status
`created_at`	TIMESTAMP	Record creation timestamp
`updated_at`	TIMESTAMP	Last update timestamp

Relationships:

Referenced by shipments.material_id

routes

Defines transportation routes with distance and pricing information.

CREATE TABLE routes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  source VARCHAR NOT NULL,
  destination VARCHAR NOT NULL,
  distance_km NUMERIC NOT NULL,
  estimated_time NUMERIC DEFAULT 0,
  billing_rate_per_ton NUMERIC NOT NULL,
  vendor_rate_per_ton NUMERIC NOT NULL,
  assigned_package_id UUID REFERENCES packages(id),
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);

Columns:

Column	Type	Description
`id`	UUID	Primary key
`source`	VARCHAR	Route origin
`destination`	VARCHAR	Route destination
`distance_km`	NUMERIC	Distance in kilometers
`estimated_time`	NUMERIC	Estimated transit time in hours
`billing_rate_per_ton`	NUMERIC	Customer billing rate per ton
`vendor_rate_per_ton`	NUMERIC	Vendor payment rate per ton
`assigned_package_id`	UUID	Assigned package (optional)
`created_at`	TIMESTAMP	Record creation timestamp
`updated_at`	TIMESTAMP	Last update timestamp

Relationships:

assigned_package_id → packages.id (many-to-one, optional)
Referenced by shipments.route_id

transporters

Stores information about transport companies and vendors.

CREATE TABLE transporters (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR NOT NULL,
  contact_person VARCHAR NOT NULL,
  contact_number VARCHAR NOT NULL,
  address VARCHAR NOT NULL,
  gstn VARCHAR NOT NULL,
  active BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);

Columns:

Column	Type	Description
`id`	UUID	Primary key
`name`	VARCHAR	Transporter company name
`contact_person`	VARCHAR	Primary contact name
`contact_number`	VARCHAR	Contact phone number
`address`	VARCHAR	Company address
`gstn`	VARCHAR	GST Number (tax identifier)
`active`	BOOLEAN	Whether the transporter is active
`created_at`	TIMESTAMP	Record creation timestamp
`updated_at`	TIMESTAMP	Last update timestamp

Relationships:

Referenced by vehicles.transporter_id
Referenced by shipments.transporter_id

profiles

User profile information linked to Supabase authentication.

CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  username VARCHAR NOT NULL,
  full_name VARCHAR,
  phone_number VARCHAR,
  role VARCHAR DEFAULT 'user',
  active BOOLEAN DEFAULT true,
  assigned_packages UUID[],
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);

Columns:

Column	Type	Description
`id`	UUID	Primary key, references auth.users
`username`	VARCHAR	User’s username
`full_name`	VARCHAR	User’s full name
`phone_number`	VARCHAR	Contact phone number
`role`	VARCHAR	User role (admin, user, manager)
`active`	BOOLEAN	Whether the user account is active
`assigned_packages`	UUID[]	Array of assigned package IDs
`created_at`	TIMESTAMP	Record creation timestamp
`updated_at`	TIMESTAMP	Last update timestamp

Relationships:

id → auth.users.id (one-to-one with Supabase Auth)

user_settings

User-specific notification and preference settings.

CREATE TABLE user_settings (
  user_id UUID PRIMARY KEY,
  email_notifications BOOLEAN DEFAULT true,
  sms_notifications BOOLEAN DEFAULT false,
  shipment_updates BOOLEAN DEFAULT true,
  system_announcements BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT now(),
  updated_at TIMESTAMP DEFAULT now()
);

Columns:

Column	Type	Description
`user_id`	UUID	Primary key, user identifier
`email_notifications`	BOOLEAN	Enable email notifications
`sms_notifications`	BOOLEAN	Enable SMS notifications
`shipment_updates`	BOOLEAN	Receive shipment status updates
`system_announcements`	BOOLEAN	Receive system announcements
`created_at`	TIMESTAMP	Record creation timestamp
`updated_at`	TIMESTAMP	Last update timestamp

Database Views

user_roles_view

Simplified view for querying user roles.

CREATE VIEW user_roles_view AS
  SELECT id, role FROM profiles;

Database Functions

The schema includes several PostgreSQL functions for common operations:

get_current_user_role()

Returns the role of the currently authenticated user.

CREATE FUNCTION get_current_user_role()
RETURNS TEXT AS $$
  SELECT role FROM public.profiles WHERE id = auth.uid();
$$ LANGUAGE SQL SECURITY DEFINER STABLE;

assign_user_role(user_id, user_role)

Assigns or updates a user’s role.

CREATE FUNCTION assign_user_role(
  user_id UUID,
  user_role TEXT
) RETURNS VOID;

assign_packages_to_user(user_id, package_ids)

Assigns multiple packages to a user.

CREATE FUNCTION assign_packages_to_user(
  user_id UUID,
  package_ids UUID[]
) RETURNS VOID;

toggle_user_access(user_id, is_active)

Activates or deactivates a user account.

CREATE FUNCTION toggle_user_access(
  user_id UUID,
  is_active BOOLEAN
) RETURNS VOID;

delete_user(user_id)

Deletes a user and their associated data.

CREATE FUNCTION delete_user(
  user_id UUID
) RETURNS VOID;

get_user_email(user_id)

Retrieves a user’s email address from the auth schema.

CREATE FUNCTION get_user_email(
  user_id UUID
) RETURNS TABLE(email TEXT);

Row Level Security (RLS)

All tables have Row Level Security enabled to ensure data access is properly controlled based on user roles and ownership.

RLS policies are enforced at the database level, providing security even if the application layer is compromised.

Security Policies

Vehicles
Shipments
Packages
Profiles
User Settings

-- All authenticated users can view vehicles
CREATE POLICY "Authenticated users can view vehicles"
  ON vehicles FOR SELECT
  TO authenticated
  USING (true);

-- Only admins can manage vehicles
CREATE POLICY "Admin users can manage vehicles"
  ON vehicles FOR ALL
  TO authenticated
  USING (get_current_user_role() = 'admin')
  WITH CHECK (get_current_user_role() = 'admin');

-- All authenticated users can view shipments
CREATE POLICY "Authenticated users can view shipments"
  ON shipments FOR SELECT
  TO authenticated
  USING (true);

-- Only admins can manage shipments
CREATE POLICY "Admin users can manage shipments"
  ON shipments FOR ALL
  TO authenticated
  USING (get_current_user_role() = 'admin')
  WITH CHECK (get_current_user_role() = 'admin');

-- Users can view packages they created or are assigned to
CREATE POLICY "Users can view assigned packages"
  ON packages FOR SELECT
  TO authenticated
  USING (
    get_current_user_role() = 'admin' OR 
    auth.uid() = created_by_id OR
    auth.uid() = ANY(
      SELECT unnest(assigned_packages) 
      FROM profiles 
      WHERE id = auth.uid()
    )
  );

-- Admins and package creators can manage packages
CREATE POLICY "Admin and package creators can manage packages"
  ON packages FOR ALL
  TO authenticated
  USING (get_current_user_role() = 'admin' OR auth.uid() = created_by_id)
  WITH CHECK (get_current_user_role() = 'admin' OR auth.uid() = created_by_id);

-- Users can view their own profile
CREATE POLICY "Users can view their own profile"
  ON profiles FOR SELECT
  TO authenticated
  USING (auth.uid() = id);

-- Admins can view all profiles
CREATE POLICY "Admin users can view all profiles"
  ON profiles FOR SELECT
  TO authenticated
  USING (get_current_user_role() = 'admin');

-- Users can update their own profile
CREATE POLICY "Users can update their own profile"
  ON profiles FOR UPDATE
  TO authenticated
  USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- Admins can manage all profiles
CREATE POLICY "Admin users can manage all profiles"
  ON profiles FOR ALL
  TO authenticated
  USING (get_current_user_role() = 'admin')
  WITH CHECK (get_current_user_role() = 'admin');

-- Users can manage their own settings
CREATE POLICY "Users can manage their own settings"
  ON user_settings FOR ALL
  TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

Entity Relationship Diagram

TypeScript Integration

The database schema is automatically reflected in TypeScript types:

// src/integrations/supabase/types.ts
export type Tables<T extends keyof Database['public']['Tables']> = 
  Database['public']['Tables'][T]['Row'];

// Usage in code
import { Tables } from '@/integrations/supabase/types';

type Vehicle = Tables<'vehicles'>;
type Shipment = Tables<'shipments'>;
type Package = Tables<'packages'>;

Migration Files

Database changes are managed through SQL migration files:

20250626084408-40640dda-5b51-48b2-b2ac-0a64dc5022c0.sql - Initial RLS setup
20250710103823-11aa3e41-d45b-4c07-877d-ef8adc04984c.sql - Vehicle number unique constraint

Best Practices

Data Integrity

All tables use UUID primary keys
Foreign key constraints ensure referential integrity
Unique constraints prevent duplicate data
Timestamps track all changes

Security

Row Level Security enabled on all tables
Role-based access control via RLS policies
Security definer functions for privilege escalation
Authenticated-only access

Performance

Indexed foreign keys for join performance
Selective column queries to reduce data transfer
Proper use of nullable fields
Query optimization with TanStack Query caching

Next Steps

Tech Stack

Learn about the technologies used in the project

Project Structure

Explore how the codebase is organized

Architecture

Development

Deployment

Overview

Core Tables

vehicles

shipments

packages

materials

routes

transporters

profiles

user_settings

Database Views

user_roles_view

Database Functions

get_current_user_role()

assign_user_role(user_id, user_role)

assign_packages_to_user(user_id, package_ids)

toggle_user_access(user_id, is_active)

delete_user(user_id)

get_user_email(user_id)

Row Level Security (RLS)

Security Policies

Entity Relationship Diagram

TypeScript Integration

Migration Files

Best Practices

Data Integrity

Security

Performance

Next Steps

Tech Stack

Project Structure

Build docs developers (and LLMs) love

Architecture

Development

Deployment

​Overview

​Core Tables

​vehicles

​shipments

​packages

​materials

​routes

​transporters

​profiles

​user_settings

​Database Views

​user_roles_view

​Database Functions

​get_current_user_role()

​assign_user_role(user_id, user_role)

​assign_packages_to_user(user_id, package_ids)

​toggle_user_access(user_id, is_active)

​delete_user(user_id)

​get_user_email(user_id)

​Row Level Security (RLS)

​Security Policies

​Entity Relationship Diagram

​TypeScript Integration

​Migration Files

​Best Practices

Data Integrity

Security

Performance

​Next Steps

Tech Stack

Project Structure

Build docs developers (and LLMs) love

Overview

Core Tables

vehicles

shipments

packages

materials

routes

transporters

profiles

user_settings

Database Views

user_roles_view

Database Functions

get_current_user_role()

assign_user_role(user_id, user_role)

assign_packages_to_user(user_id, package_ids)

toggle_user_access(user_id, is_active)

delete_user(user_id)

get_user_email(user_id)

Row Level Security (RLS)

Security Policies

Entity Relationship Diagram

TypeScript Integration

Migration Files

Best Practices

Next Steps