CREATE TABLE tenant_preferences (
tenant_id UUID,
preference_id UUID DEFAULT gen_random_uuid(),
preference_key VARCHAR(100) NOT NULL,
preference_value JSONB NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (tenant_id, preference_id),
FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);
CREATE TABLE user_travel_history (
tenant_id UUID,
history_id UUID DEFAULT gen_random_uuid(),
user_id UUID,
travel_date DATE NOT NULL,
destination VARCHAR(100) NOT NULL,
activities JSONB,
total_spend DECIMAL(10, 2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (tenant_id, history_id),
FOREIGN KEY (tenant_id,user_id) REFERENCES users.tenant_users(tenant_id,user_id)
);
CREATE TABLE itineraries (
tenant_id UUID,
itinerary_id UUID DEFAULT gen_random_uuid(),
user_id UUID,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
destinations JSONB,
is_draft BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (tenant_id, itinerary_id),
FOREIGN KEY (tenant_id,user_id) REFERENCES users.tenant_users(tenant_id,user_id)
);
CREATE TABLE itinerary_details (
tenant_id UUID,
detail_id UUID DEFAULT gen_random_uuid(),
itinerary_id UUID,
date DATE NOT NULL,
activity_type VARCHAR(50),
activity_details JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (tenant_id, detail_id),
FOREIGN KEY (tenant_id, itinerary_id) REFERENCES itineraries(tenant_id,itinerary_id)
);
CREATE TABLE bookings (
tenant_id UUID,
booking_id UUID DEFAULT gen_random_uuid(),
user_id UUID,
itinerary_id UUID,
booking_type VARCHAR(50) NOT NULL,
booking_details JSONB NOT NULL,
status VARCHAR(20) DEFAULT 'confirmed',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (tenant_id, booking_id),
FOREIGN KEY (tenant_id,user_id) REFERENCES users.tenant_users(tenant_id,user_id),
FOREIGN KEY (tenant_id, itinerary_id) REFERENCES itineraries(tenant_id,itinerary_id)
);
CREATE TABLE booking_reminders (
tenant_id UUID,
reminder_id UUID DEFAULT gen_random_uuid(),
booking_id UUID,
user_id UUID,
reminder_date DATE NOT NULL,
message TEXT NOT NULL,
sent BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (tenant_id, reminder_id),
FOREIGN KEY (tenant_id,user_id) REFERENCES users.tenant_users(tenant_id,user_id),
FOREIGN KEY (tenant_id,booking_id) REFERENCES bookings(tenant_id,booking_id)
);
CREATE TABLE travel_analytics (
tenant_id UUID,
analytics_id UUID DEFAULT gen_random_uuid(),
user_id UUID,
travel_date DATE NOT NULL,
total_spend DECIMAL(10, 2) NOT NULL,
categories_spend JSONB NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (tenant_id, analytics_id),
FOREIGN KEY (tenant_id,user_id) REFERENCES users.tenant_users(tenant_id,user_id)
);
The AI-native travel planning SaaS will leverage Generative AI to provide personalized and efficient travel itineraries. The system must support multitenancy, allowing multiple tenants to use the application independently with their own data, preferences, and policies. Each tenant’s data should be isolated and securely managed. The system needs to track a list of draft itineraries that are not yet booked, a list of completed bookings, the ability to remind users when their travel date is approaching, and store travel preferences for each tenant to inform itinerary decisions. Additionally, the system should provide analytics on past travel spends. Security and privacy are paramount; hence, the system must comply with data protection regulations and implement robust authentication and authorization mechanisms. Finally, the architecture should be scalable to handle high traffic volumes and flexible to integrate with third-party services for real-time updates on travel information.
