Copy -- ============================================================================
-- CORE TABLES (Shared by all products)
-- ============================================================================
CREATE TABLE users (
user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT UNIQUE NOT NULL,
wallet_address TEXT UNIQUE, -- Web3 identity (Robo-Hub/Robo-Dapp)
created_at TIMESTAMP DEFAULT NOW(),
last_active TIMESTAMP DEFAULT NOW()
);
CREATE TABLE user_profiles (
profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(user_id),
product TEXT NOT NULL, -- 'fleetos' | 'robo-hub' | 'robo-dapp'
role TEXT NOT NULL, -- 'supplier' | 'fleet' | 'shepherd'
-- Common fields
name TEXT NOT NULL,
phone TEXT,
whatsapp_phone TEXT,
city TEXT,
-- Product-specific JSON
fleetos_data JSONB, -- FleetOS-specific settings
robo_hub_data JSONB, -- Robo-Hub-specific settings (zone_id, etc.)
robo_dapp_data JSONB, -- Robo-Dapp-specific settings (staking, etc.)
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE relationships (
relationship_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
fleet_id UUID REFERENCES users(user_id),
supplier_id UUID REFERENCES users(user_id),
-- Metadata
service_category TEXT, -- 'TIRES' | 'CHARGING' | 'MAINTENANCE' | etc.
status TEXT DEFAULT 'active', -- 'active' | 'inactive' | 'pending'
created_via TEXT, -- 'fleetos' | 'robo-hub' | 'invite'
created_at TIMESTAMP DEFAULT NOW(),
-- Intelligence (updated by IntelligenceService)
total_requests INTEGER DEFAULT 0,
total_completed INTEGER DEFAULT 0,
avg_response_time_seconds INTEGER,
health_score TEXT, -- 'green' | 'yellow' | 'red'
last_interaction TIMESTAMP,
-- Robo-Hub contract reference (optional)
contract_id UUID,
UNIQUE(fleet_id, supplier_id, service_category)
);
CREATE TABLE service_requests (
request_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
relationship_id UUID REFERENCES relationships(relationship_id),
-- Request details
vehicle_identifier TEXT NOT NULL,
location TEXT NOT NULL,
urgency TEXT NOT NULL, -- 'URGENT' | 'TODAY' | 'SCHEDULED'
description TEXT NOT NULL,
scheduled_time TIMESTAMP,
-- Status tracking
status TEXT DEFAULT 'pending', -- 'pending' | 'acknowledged' | 'accepted' | 'in_progress' | 'completed' | 'declined'
created_at TIMESTAMP DEFAULT NOW(),
acknowledged_at TIMESTAMP,
accepted_at TIMESTAMP,
completed_at TIMESTAMP,
declined_at TIMESTAMP,
decline_reason TEXT,
-- Product context
created_via TEXT, -- 'fleetos' | 'robo-hub' | 'robo-dapp'
-- Robo-Dapp payment reference (optional)
smart_contract_tx TEXT,
payment_status TEXT -- 'pending' | 'settled' | 'disputed'
);
CREATE TABLE notifications (
notification_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(user_id),
-- Delivery
channel TEXT NOT NULL, -- 'whatsapp' | 'email' | 'sms' | 'in-app'
recipient TEXT NOT NULL,
template TEXT NOT NULL,
-- Content
subject TEXT,
message TEXT NOT NULL,
magic_link TEXT,
-- Tracking
sent_at TIMESTAMP DEFAULT NOW(),
delivered_at TIMESTAMP,
opened_at TIMESTAMP,
clicked_at TIMESTAMP,
-- Product context
product TEXT, -- 'fleetos' | 'robo-hub' | 'robo-dapp'
related_request_id UUID REFERENCES service_requests(request_id)
);
CREATE TABLE invites (
invite_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
invite_code TEXT UNIQUE NOT NULL,
-- Inviter
inviter_id UUID REFERENCES users(user_id),
inviter_type TEXT NOT NULL, -- 'supplier' | 'fleet' | 'shepherd'
-- Invitee
invitee_email TEXT,
invitee_phone TEXT,
invitee_type TEXT NOT NULL, -- 'supplier' | 'fleet'
-- Metadata
service_category TEXT,
personal_message TEXT,
product TEXT, -- 'fleetos' | 'robo-hub'
-- Status
status TEXT DEFAULT 'pending', -- 'pending' | 'accepted' | 'expired'
created_at TIMESTAMP DEFAULT NOW(),
accepted_at TIMESTAMP,
expires_at TIMESTAMP,
-- Result
created_relationship_id UUID REFERENCES relationships(relationship_id)
);
-- ============================================================================
-- FLEETOS-SPECIFIC TABLES
-- ============================================================================
CREATE TABLE fleetos_supplier_settings (
supplier_id UUID REFERENCES users(user_id) PRIMARY KEY,
-- Preferences
whatsapp_notifications BOOLEAN DEFAULT TRUE,
email_notifications BOOLEAN DEFAULT TRUE,
reminder_enabled BOOLEAN DEFAULT TRUE,
reminder_minutes INTEGER DEFAULT 30,
-- Pro tier (future)
subscription_tier TEXT DEFAULT 'free', -- 'free' | 'pro'
subscription_status TEXT, -- 'active' | 'cancelled'
subscription_expires_at TIMESTAMP
);
CREATE TABLE fleetos_fleet_settings (
fleet_id UUID REFERENCES users(user_id) PRIMARY KEY,
-- Fleet info
vehicle_count INTEGER,
-- Preferences
email_notifications BOOLEAN DEFAULT TRUE,
status_update_notifications BOOLEAN DEFAULT TRUE
);
-- ============================================================================
-- ROBO-HUB-SPECIFIC TABLES
-- ============================================================================
CREATE TABLE robo_hub_zone_licenses (
zone_id TEXT PRIMARY KEY, -- e.g., 'mia-south-beach'
-- Zone details
city TEXT NOT NULL,
zone_name TEXT NOT NULL,
total_capacity INTEGER, -- Max Shepherds per zone
sold_count INTEGER DEFAULT 0,
hub_allowance INTEGER, -- Max fleets per Shepherd
-- Economics
base_stake_usd INTEGER, -- e.g., 5000
per_vehicle_stake_usd INTEGER, -- e.g., 500
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE robo_hub_shepherds (
shepherd_id UUID REFERENCES users(user_id) PRIMARY KEY,
-- Zone ownership
zone_id TEXT REFERENCES robo_hub_zone_licenses(zone_id),
nft_token_id TEXT UNIQUE, -- On-chain NFT identifier
-- Staking
base_stake_amount NUMERIC,
per_vehicle_stake_amount NUMERIC,
total_staked NUMERIC,
-- Status
status TEXT DEFAULT 'active', -- 'active' | 'suspended'
onboarded_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE robo_hub_contracts (
contract_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Parties
shepherd_id UUID REFERENCES robo_hub_shepherds(shepherd_id),
supplier_id UUID REFERENCES users(user_id),
-- Contract terms
service_category TEXT NOT NULL,
agreed_rates JSONB NOT NULL, -- Pricing structure
sla_terms JSONB, -- Service level agreements
-- Status
status TEXT DEFAULT 'active', -- 'negotiating' | 'active' | 'suspended' | 'terminated'
signed_at TIMESTAMP,
expires_at TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE robo_hub_rfqs (
rfq_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- Requestor
shepherd_id UUID REFERENCES robo_hub_shepherds(shepherd_id),
-- RFQ details
service_category TEXT NOT NULL,
description TEXT NOT NULL,
volume_estimate TEXT, -- e.g., "50 vehicles, 200 tire rotations/month"
-- Status
status TEXT DEFAULT 'open', -- 'open' | 'closed'
created_at TIMESTAMP DEFAULT NOW(),
closed_at TIMESTAMP
);
CREATE TABLE robo_hub_rfq_responses (
response_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
rfq_id UUID REFERENCES robo_hub_rfqs(rfq_id),
supplier_id UUID REFERENCES users(user_id),
-- Quote
quoted_rate JSONB NOT NULL, -- Pricing proposal
proposed_sla JSONB,
message TEXT,
-- AI analysis (populated by IntelligenceService)
market_comparison TEXT, -- 'above' | 'at' | 'below'
recommendation_score NUMERIC, -- 0-100
submitted_at TIMESTAMP DEFAULT NOW()
);
-- ============================================================================
-- ROBO-DAPP-SPECIFIC TABLES
-- ============================================================================
CREATE TABLE robo_dapp_fleets (
fleet_id UUID REFERENCES robo_hub_shepherds(shepherd_id) PRIMARY KEY,
-- Fleet details
fleet_name TEXT NOT NULL,
vehicle_count INTEGER NOT NULL,
zone_id TEXT REFERENCES robo_hub_zone_licenses(zone_id),
-- Operations
daily_dispatch_enabled BOOLEAN DEFAULT TRUE,
auto_payment_enabled BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE robo_dapp_vehicles (
vehicle_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
fleet_id UUID REFERENCES robo_dapp_fleets(fleet_id),
-- Vehicle details
vehicle_identifier TEXT NOT NULL, -- e.g., "Tesla Model 3 #A7832"
make TEXT,
model TEXT,
year INTEGER,
vin TEXT,
-- Status
status TEXT DEFAULT 'active', -- 'active' | 'maintenance' | 'inactive'
created_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE robo_dapp_transactions (
transaction_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- References
service_request_id UUID REFERENCES service_requests(request_id),
contract_id UUID REFERENCES robo_hub_contracts(contract_id),
-- Payment details
amount_usd NUMERIC NOT NULL,
smart_contract_tx TEXT UNIQUE NOT NULL, -- On-chain transaction hash
-- Revenue split (42/42/16)
shepherd_amount NUMERIC,
supplier_amount NUMERIC,
protocol_amount NUMERIC,
-- Status
status TEXT DEFAULT 'pending', -- 'pending' | 'settled' | 'disputed'
settled_at TIMESTAMP,
created_at TIMESTAMP DEFAULT NOW()
);
-- ============================================================================
-- INDEXES (Performance)
-- ============================================================================
-- Core indexes
CREATE INDEX idx_relationships_fleet ON relationships(fleet_id);
CREATE INDEX idx_relationships_supplier ON relationships(supplier_id);
CREATE INDEX idx_relationships_health ON relationships(health_score);
CREATE INDEX idx_requests_relationship ON service_requests(relationship_id);
CREATE INDEX idx_requests_status ON service_requests(status);
CREATE INDEX idx_requests_urgency ON service_requests(urgency);
CREATE INDEX idx_requests_created ON service_requests(created_at DESC);
CREATE INDEX idx_notifications_user ON notifications(user_id);
CREATE INDEX idx_notifications_sent ON notifications(sent_at DESC);
-- Product-specific indexes
CREATE INDEX idx_contracts_shepherd ON robo_hub_contracts(shepherd_id);
CREATE INDEX idx_contracts_supplier ON robo_hub_contracts(supplier_id);
CREATE INDEX idx_contracts_status ON robo_hub_contracts(status);
CREATE INDEX idx_transactions_request ON robo_dapp_transactions(service_request_id);
CREATE INDEX idx_transactions_status ON robo_dapp_transactions(status); 
