AITravelMate

AITravelMate - your business trips managed by AI

travel-snapshot-1

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.

PostgreSQL Schemas

travel-schema-1

1. tenants_preferences

This table stores various preferences for each tenant, which the AI uses to generate personalized travel plans. Preferences are stored as key-value pairs in JSON format.


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)
);

2. user_travel_history

This table tracks the travel history of users per tenant. It includes details such as travel dates, destinations, activities, and total spend. This information is useful for generating analytics and improving future travel plans.

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)
);

3. itineraries

Itinerary Schema:** This table stores draft and confirmed itineraries for users per tenant. It includes details like start and end dates, destinations, and a flag indicating whether the itinerary is a draft.

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)
);

4. itinerary_details

This table provides detailed daily activities for each itinerary. It includes the date, type of activity, and specific details about the activity, stored in JSON format.

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)
);

5. bookings

This table tracks travel bookings made by users per tenant. It includes details about the booking type (e.g., flight, hotel), booking details, and status.

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)
);

6. booking_reminders

This table stores reminders for users about their upcoming bookings. It includes the reminder date, message, and a flag indicating whether the reminder has been sent.

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)
);

7. travel_analytics

This table stores analytics data about past travel spends for users per tenant. It includes the travel date, total spend, and a breakdown of spends by categories stored in JSON format.

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)
);

Full Script

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)
);