Data Model

Database Schema

PostgreSQL database with 10 core tables. All tables use standard timestamps and follow consistent naming conventions.

Tables (10)

organizations users api_keys carrier_connections rate_cards rate_entries markup_assignments shipments api_call_logs invoices

Column	Type	Constraint	Description
id	SERIAL	PK	Auto-increment primary key
name	VARCHAR(255)	NOT NULL	Organization name
type	ENUM	NOT NULL	'master', 'sub_admin', or 'customer'
parent_id	INTEGER	FK → organizations.id	Parent organization (reseller hierarchy)
contact_email	VARCHAR(255)		Primary contact email
is_active	BOOLEAN	DEFAULT true	Account active status
created_at	TIMESTAMP	DEFAULT NOW()	Creation timestamp

Column	Type	Constraint	Description
id	SERIAL	PK	Auto-increment primary key
organization_id	INTEGER	FK → organizations.id	Parent organization
email	VARCHAR(255)	UNIQUE, NOT NULL	Login email
password_hash	VARCHAR(255)	NOT NULL	Bcrypt hashed password
first_name	VARCHAR(100)		First name
last_name	VARCHAR(100)		Last name
role	ENUM	NOT NULL	'master_admin', 'sub_admin', 'customer', 'user'
is_active	BOOLEAN	DEFAULT true	Account active status

Column	Type	Constraint	Description
id	SERIAL	PK	Auto-increment primary key
organization_id	INTEGER	FK → organizations.id	Owning organization
key_hash	VARCHAR(255)	UNIQUE	SHA-256 hash of the API key
key_prefix	VARCHAR(10)		First 8 chars for identification (e.g., sk_live_)
label	VARCHAR(100)		User-defined label
is_active	BOOLEAN	DEFAULT true	Key active status
last_used_at	TIMESTAMP		Last usage timestamp