Database Schema

Core Tables

users

Column	Type	Constraints
id	UUID	PRIMARY KEY
email	VARCHAR(255)	UNIQUE, NOT NULL
hashed_password	VARCHAR(255)	NOT NULL
name	VARCHAR(255)	NOT NULL
role	VARCHAR(50)	NOT NULL DEFAULT 'user'
is_active	BOOLEAN	NOT NULL DEFAULT true
tenant_id	UUID	FK → tenants(id), NOT NULL
verification_code	VARCHAR(10)
verification_code_expires	TIMESTAMP
reset_code	VARCHAR(10)
reset_code_expires	TIMESTAMP
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP

tenants

Column	Type	Constraints
id	UUID	PRIMARY KEY
name	VARCHAR(255)	UNIQUE, NOT NULL
domain	VARCHAR(255)	UNIQUE
pricing_tier	VARCHAR(50)	NOT NULL DEFAULT 'STARTER'
custom_tracking_url	VARCHAR(255)
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP

sessions

Column	Type	Constraints
id	UUID	PRIMARY KEY
user_id	UUID	FK → users(id), NOT NULL
refresh_token_hash	VARCHAR(255)	NOT NULL
expires_at	TIMESTAMP	NOT NULL
created_at	TIMESTAMP	NOT NULL

---

Shipments Tables

shipments

Column	Type	Constraints
id	UUID	PRIMARY KEY
tenant_id	UUID	FK → tenants(id), NOT NULL
tracking_id	VARCHAR(20)	UNIQUE, NOT NULL
sender_name	VARCHAR(255)	NOT NULL
sender_phone	VARCHAR(50)	NOT NULL
recipient_name	VARCHAR(255)	NOT NULL
recipient_phone	VARCHAR(50)	NOT NULL
origin	VARCHAR(255)	NOT NULL
destination	VARCHAR(255)	NOT NULL
description	TEXT
weight	DECIMAL(10,2)
price	DECIMAL(10,2)
status	VARCHAR(50)	NOT NULL DEFAULT 'RECEIVED'
payment_status	VARCHAR(50)	NOT NULL DEFAULT 'PENDING'
eta	TIMESTAMP
bag_id	UUID	FK → bags(id)
notification_whatsapp	BOOLEAN	DEFAULT true
notification_email	BOOLEAN	DEFAULT true
notification_push	BOOLEAN	DEFAULT true
recipient_email	VARCHAR(255)
device_token	TEXT
last_notification_sent	TIMESTAMP
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP

bags

Column	Type	Constraints
id	UUID	PRIMARY KEY
tenant_id	UUID	FK → tenants(id), NOT NULL
label	VARCHAR(100)	NOT NULL
status	VARCHAR(50)	NOT NULL DEFAULT 'PACKAGED'
flight_id	UUID	FK → flights(id)
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP

flights

Column	Type	Constraints
id	UUID	PRIMARY KEY
tenant_id	UUID	FK → tenants(id), NOT NULL
flight_number	VARCHAR(20)	NOT NULL
airline_name	VARCHAR(255)
departure_airport	VARCHAR(10)	NOT NULL
arrival_airport	VARCHAR(10)	NOT NULL
scheduled_departure	TIMESTAMP
scheduled_arrival	TIMESTAMP
actual_departure	TIMESTAMP
actual_arrival	TIMESTAMP
flight_status	VARCHAR(50)	DEFAULT 'scheduled'
delay_minutes	INTEGER	DEFAULT 0
departure_terminal	VARCHAR(50)
arrival_terminal	VARCHAR(50)
created_at	TIMESTAMP	NOT NULL
updated_at	TIMESTAMP

flight_alert_subscriptions

Column	Type	Constraints
id	UUID	PRIMARY KEY
tenant_id	UUID	FK → tenants(id), NOT NULL
flight_id	UUID	FK → flights(id), NOT NULL
subscription_id	VARCHAR(255)
webhook_url	VARCHAR(500)
expires_at	TIMESTAMP
created_at	TIMESTAMP	NOT NULL

---

Row Level Security (RLS)

All tenant-scoped tables have RLS enabled:

ENABLE ROW LEVEL SECURITY;
FORCE ROW LEVEL SECURITY;

POLICY mandatory_tenant_policy ON table_name
USING (tenant_id = current_setting('app.current_tenant')::uuid)
WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

RLS Context Manager in Python:

class TenantContext:
    async def __aenter__(self):
        await conn.execute("SET LOCAL app.current_tenant = %s", tenant_id)
    async def __aexit__(self, *args):
        await conn.execute("RESET app.current_tenant")

---

Shipment Status Values

Status	Description
RECEIVED	Shipment received, awaiting bag assignment
VERIFIED	Verified (internal)
PAID	Payment confirmed (internal)
PREPARING	Assigned to bag, in preparation
PACKAGED	Packed in bag
IN_TRANSIT	In transit to destination
AT_PICKUP_POINT	At pickup point
OUT_FOR_DELIVERY	Out for delivery
DELIVERED	Delivered to recipient
RETURNED	Returned to sender
CANCELLED	Cancelled

---

Payment Status Values

Status	Description
PENDING	Awaiting payment
PAID	Payment received
REFUNDED	Payment refunded