Database Schema¶

Audit Fields¶

All tables include these audit fields for tracking creation and modifications:

Column	Type	Constraints	Description
`_created_at`	TIMESTAMP	NOT NULL	UTC timestamp when record was created
`_updated_at`	TIMESTAMP	NULL	UTC timestamp when record was last updated (auto via DB trigger)
`_user_created`	TEXT	NOT NULL	ID or type of user who created the record (e.g., UUID, "SYSTEM", "WEBHOOK:AERODATABOX")
`_user_updated`	TEXT	NULL	ID or type of user who last modified the record

Supported Actor Types for _user_created / _user_updated¶

Actor Type	Example	Description
UUID	`550e8400-e29b-41d4-a716-446655440000`	Human user ID
`SYSTEM`	`SYSTEM`	System operations
`WEBHOOK:AERODATABOX`	`WEBHOOK:AERODATABOX`	Flight webhook updates
`IMPORT`	`IMPORT`	Data import operations
`SEED`	`SEED`	Database seeding
`MIGRATION`	`MIGRATION`	Migration scripts

Auto-Update Trigger¶

A PostgreSQL trigger automatically updates _updated_at when a row is modified:

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW._updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_user_updated_at
    BEFORE UPDATE ON user
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Core Tables¶

tenant¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
name	VARCHAR(255)	NOT NULL
flight_notification_level	VARCHAR(50)	NOT NULL DEFAULT 'basic'
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

role¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
name	VARCHAR(255)	NOT NULL
permissions	TEXT	NOT NULL
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

"user"¶

The user table requires quoting in SQL as it's a reserved word.

Column	Type	Constraints
id	UUID	PRIMARY KEY
email	VARCHAR(255)	UNIQUE, NOT NULL, INDEX
hashed_password	VARCHAR(255)	NOT NULL
is_active	BOOLEAN	NOT NULL DEFAULT true
tenant_id	UUID	FK → tenant(id), INDEX
verification_code	VARCHAR(10)
verification_code_expires	TIMESTAMP
reset_code	VARCHAR(10)
reset_code_expires	TIMESTAMP
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

user_tenant¶

Column	Type	Constraints
user_id	UUID	FK → "user"(id), PRIMARY KEY
role_id	UUID	FK → role(id), PRIMARY KEY
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

session¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
user_id	UUID	FK → "user"(id)
tenant_id	UUID	FK → tenant(id)
refresh_token_hash	VARCHAR(255)	NOT NULL
expires_at	TIMESTAMP	NOT NULL
revoked	BOOLEAN	NOT NULL DEFAULT false
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

Shipments Tables¶

shipment¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
tracking_id	VARCHAR(20)	UNIQUE, NOT NULL, INDEX
sender_name	VARCHAR(255)	NOT NULL
sender_phone	VARCHAR(50)	NOT NULL
recipient_id	UUID	FK → recipient(id), INDEX
recipient_email	VARCHAR(255)
recipient_phone	VARCHAR(50)
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'
delivery_proof_url	TEXT
bag_id	UUID	FK → bag(id)
tenant_id	UUID	FK → tenant(id), INDEX
notification_whatsapp	BOOLEAN	DEFAULT true
notification_email	BOOLEAN	DEFAULT false
notification_push	BOOLEAN	DEFAULT false
sender_email	VARCHAR(255)
device_token	TEXT
last_notification_sent	TIMESTAMP
estimated_arrival	TIMESTAMP
status_changed_at	TIMESTAMP
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

bag¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
courier_id	UUID	FK → courier(id)
warehouse_id	UUID	INDEX
route	VARCHAR(20)	NOT NULL
departure_date	TIMESTAMP	NOT NULL
status	VARCHAR(50)	NOT NULL DEFAULT 'INIT'
airline_tag_code	VARCHAR(10)
airline	VARCHAR(255)	DEFAULT ''
flight_number	VARCHAR(50)	DEFAULT ''
capacity_kg	DECIMAL(10,2)	DEFAULT 23.0
current_weight	DECIMAL(10,2)	DEFAULT 0.0
total_packages	INTEGER	DEFAULT 0
estimated_arrival	TIMESTAMP
actual_arrival	TIMESTAMP
status_changed_at	TIMESTAMP
tenant_id	UUID	FK → tenant(id), INDEX
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

flight¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
origin	VARCHAR(10)	NOT NULL
destination	VARCHAR(10)	NOT NULL
departure_date	TIMESTAMP	NOT NULL
user_id	UUID	FK → "user"(id)
courier_id	UUID	FK → courier(id)
flight_number	VARCHAR(50)	DEFAULT ''
airline	VARCHAR(255)	DEFAULT ''
airline_iata	VARCHAR(10)	DEFAULT ''
departure_airport	VARCHAR(10)	DEFAULT ''
arrival_airport	VARCHAR(10)	DEFAULT ''
scheduled_departure	TIMESTAMP
scheduled_arrival	TIMESTAMP
estimated_departure	TIMESTAMP
estimated_arrival	TIMESTAMP
actual_departure	TIMESTAMP
actual_arrival	TIMESTAMP
flight_status	VARCHAR(50)	DEFAULT 'unknown'
max_weight_kg	DECIMAL(10,2)	DEFAULT 23.0
available_weight_kg	DECIMAL(10,2)	DEFAULT 23.0
last_flight_update	TIMESTAMP
bag_id	UUID	FK → bag(id)
tenant_id	UUID	FK → tenant(id), INDEX
status_changed_at	TIMESTAMP
base_cost	DECIMAL(10,2)	DEFAULT 0.0
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

courier¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
user_id	UUID	FK → "user"(id), UNIQUE
tenant_id	UUID	FK → tenant(id), INDEX
full_name	VARCHAR(255)	NOT NULL
passport_number	VARCHAR(50)	NOT NULL
nationality	VARCHAR(10)	DEFAULT ''
phone	VARCHAR(50)	DEFAULT ''
email	VARCHAR(255)	DEFAULT ''
flights_completed	INTEGER	DEFAULT 0
packages_delivered	INTEGER	DEFAULT 0
rating	DECIMAL(3,2)	DEFAULT 5.0
no_show_count	INTEGER	DEFAULT 0
status	VARCHAR(50)	DEFAULT 'ACTIVE'
home_country	VARCHAR(10)	DEFAULT ''
preferred_routes	TEXT	DEFAULT '[]'
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

package_event¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
tenant_id	UUID	FK → tenant(id), INDEX
shipment_id	UUID	FK → shipment(id), INDEX
event_type	VARCHAR(50)	NOT NULL, INDEX
timestamp	TIMESTAMP	NOT NULL
warehouse_id	UUID
actor_id	UUID	FK → "user"(id)
evidence_url	TEXT
event_metadata	TEXT
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

bag_assignment¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
shipment_id	UUID	FK → shipment(id), INDEX
bag_id	UUID	FK → bag(id), INDEX
added_at	TIMESTAMP	NOT NULL
added_by	UUID	FK → "user"(id)
position	INTEGER
removed	BOOLEAN	DEFAULT false
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

package¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
tenant_id	UUID	FK → tenant(id), INDEX
shipment_id	UUID	FK → shipment(id), INDEX
qr_code	VARCHAR(255)	UNIQUE, INDEX
weight	DECIMAL(10,2)	NOT NULL
category	VARCHAR(50)	DEFAULT 'general'
status	VARCHAR(50)	DEFAULT 'CREATED'
status_changed_at	TIMESTAMP
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

bag_shipment¶

Column	Type	Constraints
bag_id	UUID	FK → bag(id), PRIMARY KEY
shipment_id	UUID	FK → shipment(id), PRIMARY KEY
assigned_at	TIMESTAMP	NOT NULL
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

bag_flight¶

Column	Type	Constraints
bag_id	UUID	FK → bag(id), PRIMARY KEY
flight_id	UUID	FK → flight(id), PRIMARY KEY
extra_cost	DECIMAL(10,2)	DEFAULT 0.0
assigned_at	TIMESTAMP	NOT NULL
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

flight_alert_subscription¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
flight_id	UUID	FK → flight(id), INDEX
external_subscription_id	VARCHAR(255)	DEFAULT ''
flight_number	VARCHAR(50)	INDEX
departure_date	TIMESTAMP	NOT NULL
webhook_url	VARCHAR(500)	DEFAULT ''
expires_at	TIMESTAMP
is_active	BOOLEAN	DEFAULT true
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

bag_incidence¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
bag_id	UUID	FK → bag(id), INDEX
missing_package_ids	TEXT	DEFAULT '[]'
reported_by	UUID	FK → "user"(id)
notes	TEXT
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

Warehouse Tables¶

warehouse¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
tenant_id	UUID	NOT NULL, INDEX
name	VARCHAR(255)	NOT NULL
code	VARCHAR(50)	NOT NULL, INDEX
country	VARCHAR(2)	NOT NULL
city	VARCHAR(255)	NOT NULL
address	TEXT	NOT NULL
phone	VARCHAR(50)	DEFAULT ''
email	VARCHAR(255)	DEFAULT ''
status	VARCHAR(50)	DEFAULT 'ACTIVE'
latitude	DECIMAL(10,8)
longitude	DECIMAL(11,8)
max_storage_capacity	INTEGER
current_storage_count	INTEGER	DEFAULT 0
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

package_received_event¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
tenant_id	UUID	NOT NULL, INDEX
shipment_id	UUID	NOT NULL, INDEX
warehouse_id	UUID	NOT NULL, INDEX
received_at	TIMESTAMP	NOT NULL
received_by	UUID
condition	VARCHAR(50)	DEFAULT ''
notes	TEXT	DEFAULT ''
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

Shared Tables¶

recipient¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
tenant_id	UUID	FK → tenant(id), INDEX
name	VARCHAR(255)	NOT NULL, INDEX
dni	VARCHAR(20)	INDEX
email	VARCHAR(255)	DEFAULT ''
phone	VARCHAR(50)	DEFAULT ''
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

status_history¶

Column	Type	Constraints
id	UUID	PRIMARY KEY
tenant_id	UUID	FK → tenant(id), INDEX
entity_type	VARCHAR(50)	NOT NULL, INDEX
entity_id	UUID	NOT NULL, INDEX
from_status	VARCHAR(50)
to_status	VARCHAR(50)	NOT NULL
changed_at	TIMESTAMP	NOT NULL, INDEX
changed_by	VARCHAR(255)
trigger_type	VARCHAR(50)	NOT NULL
metadata	JSONB
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

scan¶

QR scan records for tracking package/bag scans by users.

Column	Type	Constraints
id	UUID	PRIMARY KEY
user_id	UUID	NOT NULL, INDEX
tenant_id	UUID	NOT NULL, INDEX
entity_type	VARCHAR(50)	NULL (pkg, bag)
entity_id	UUID	NULL, INDEX
qr_data	JSONB	NOT NULL DEFAULT '{}'
scanned_at	TIMESTAMP	NOT NULL
validated_at	TIMESTAMP	NULL
validation_result	VARCHAR(50)	NULL (valid, hmac_invalid, wrong_tenant, etc.)
hmac_valid	BOOLEAN	NULL
latitude	DECIMAL(10,8)	NULL
longitude	DECIMAL(11,8)	NULL
location_accuracy	DECIMAL(10,2)	NULL
app_version	VARCHAR(20)	NULL
platform	VARCHAR(20)	NULL
reviewed	BOOLEAN	NOT NULL DEFAULT false
reviewed_by	UUID	NULL
reviewed_at	TIMESTAMP	NULL
_created_at	TIMESTAMP	NOT NULL
_updated_at	TIMESTAMP	NULL
_user_created	TEXT	NOT NULL
_user_updated	TEXT	NULL

Scan Validation Result Values¶

Value	Description
`valid`	QR code is valid and belongs to tenant
`hmac_invalid`	HMAC signature verification failed
`wrong_tenant`	QR code belongs to different tenant
`entity_not_found`	Package/bag no longer exists
`qr_decode_error`	QR data could not be parsed
`rate_limited`	User exceeded 100 scans/hour limit

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
`PACKING`	At least one package assigned to a bag
`PACKED`	Packed in bag
`TO_AIRPORT`	In transit to airport
`IN_FLY`	Flying to destination
`IN_AIRPORT`	Arrived at destination airport
`WITH_COURIER`	With courier for delivery
`IN_WAREHOUSE`	In destination warehouse
`CONSOLIDATING`	Consolidating with other packages
`CONSOLIDATED`	Consolidation complete
`DELIVERED`	Delivered to recipient
`RETURNED`	Returned to sender

Payment Status Values¶

Status	Description
`PENDING`	Awaiting payment
`PAID`	Payment received
`REFUNDED`	Payment refunded

Ver también: Domain Model · ER Diagram · Migrations