TripActions Clone

This is a clone of the popular SaaS app TripActions. TripActions is a travel management company that offers a travel booking and management platform for businesses. It includes shared information that is available to all businesses that use it, such as flight and hotel prices, as well as information specific to each business, such as travel policies and actual bookings.

The schema was designed to guarantee security and privacy of each tenant information, while allowing for shared information to be available to all tenants. It was also designed to support collaboration between employees of the same company - HR administrators set travel policies and managers can approve exceptions.

Postgres Schema

The schema for this example has few core tables that represent the core business entities of the application.

We have several shared tables that are available to all tenants. This includes flights, hotels, and car_rentals. These tables contain information about flights, hotels and car_rentals that are available to all tenants.

We also have several tenant-aware tables that contain information private to each tenant. These include:

travel_policies table contains travel policies set by the HR administrator of each company.
bookings table contains travel plans made by employees of each company
payment_information table contains payment information for each employee

Information about roles of each user is stored in the built-in users schema. This includes information about the user, such as name and email, as well as information about the user's role in the company, such as whether they are an HR administrator or a manager.

Several parts of this schema were simplified for the purposes of this example. For example, car rental and hotel prices are likely to change over time, so a more realistic schema would include a table for hotel prices by date. Real world schema would also allow for more than one hotel or car rental per booking.

Flights table

CREATE TABLE flights (
    id int PRIMARY KEY,
    airline VARCHAR(100),
    flight_number VARCHAR(20),
    departure_airport VARCHAR(100),
    arrival_airport VARCHAR(100),
    departure_time TIMESTAMP,
    arrival_time TIMESTAMP,
    price DECIMAL(10, 2),
    class VARCHAR(50)
);

Hotels table

CREATE TABLE hotels (
    id int PRIMARY KEY,
    name VARCHAR(100),
    address VARCHAR(255),
    star_rating INT,
    room_type VARCHAR(50),
    price_per_night DECIMAL(10, 2)
);

Car Rentals table

CREATE TABLE car_rentals (
    id int PRIMARY KEY,
    company VARCHAR(100),
    location VARCHAR(255),
    car_class VARCHAR(50),
    price DECIMAL(10, 2)
);

Travel Policies table

CREATE TABLE travel_policies (
    tenant_id UUID,
    id INT,
    details JSONB,
    PRIMARY KEY(tenant_id, id)
);

Bookings table

CREATE TABLE bookings (
    id int,
    tenant_id uuid,
    user_id uuid,
    departing_flight_id int,
    arriving_flight_id int,
    hotel_id int,
    car_rental_id int,
    booking_date TIMESTAMP,
    total_price DECIMAL(10, 2),
    approval_required BOOLEAN,
    approved BOOLEAN,
    approver uuid,
    PRIMARY KEY(id, tenant_id),
    FOREIGN KEY (tenant_id, user_id) REFERENCES users.tenant_users(tenant_id, user_id),
    FOREIGN KEY (tenant_id, approver) REFERENCES users.tenant_users(tenant_id, user_id)
);

Payment Information table

CREATE TABLE payment_information (
    id int,
    tenant_id uuid,
    user_id uuid,
    stripe_customer_id VARCHAR(255),
    stripe_payment_method_id VARCHAR(255),
    PRIMARY KEY(id, tenant_id),
    FOREIGN KEY (tenant_id, user_id) REFERENCES users.tenant_users(tenant_id, user_id)
);

Sample data

-- Sample data for Tenants table
INSERT INTO tenants (id, name) VALUES ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', 'Quantum Innovations Inc.');
INSERT INTO tenants (id, name) VALUES ('0b657261-633d-4677-a5ad-ff89059c42c5', 'Bright Horizon Enterprises');

-- Sample data for Users table

INSERT INTO users.users (id, name, email) VALUES ('cef6fc75-25d2-4cfb-b328-99a0e6f79ae6',  'Emily Johnson',       'emily.johnson@gmail.com');
INSERT INTO users.users (id, name, email) VALUES ('71d8afcc-9cbd-445d-aadc-9cb844008734',  'Sophia Martinez',     'sophia.martinez@outlook.com');
INSERT INTO users.users (id, name, email) VALUES ('19488a0b-09c3-4613-a8c0-2874f0d66497',  'David Brown',         'david.brown@icloud.com');
INSERT INTO users.users (id, name, email) VALUES ('997015f8-69e7-4bcb-ad39-f8f69f8e8b43',  'Isabella Rodriguez',  'isabella.rodriguez@protonmail.com');

-- Assign users to tenants
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', 'cef6fc75-25d2-4cfb-b328-99a0e6f79ae6', 'ejohnson@quantuminv.com','{"admin"}');
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', '71d8afcc-9cbd-445d-aadc-9cb844008734', 'martinex@quantuminv.com','{"engineer"}');
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('0b657261-633d-4677-a5ad-ff89059c42c5', '19488a0b-09c3-4613-a8c0-2874f0d66497', 'dbrown@brighthorizon.com','{"manager"}');
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('0b657261-633d-4677-a5ad-ff89059c42c5', '997015f8-69e7-4bcb-ad39-f8f69f8e8b43', 'rodriguez@brighthorizon.com','{"lawyer"}');

-- Flights
INSERT INTO flights (id, airline, flight_number, departure_airport, arrival_airport, departure_time, arrival_time, price, class) VALUES
(1, 'United', 'FL809', 'SFO', 'ORD', '2024-07-02 15:21:12', '2024-11-10 14:41:12', 384.78, 'Economy'),
(2, 'Alaska', 'FL973', 'JFK', 'LAX', '2024-11-22 21:23:21', '2024-12-04 03:12:18', 2807.42, 'Business');

-- Hotels
INSERT INTO hotels (id, name, address, star_rating, room_type, price_per_night) VALUES
(1, 'Hilton', '123 Main St, San Francisco, CA', 4, 'Standard', 200.00),
(2, 'Marriott', '456 Main St, San Francisco, CA', 5, 'Deluxe', 300.00);

-- Car Rentals
INSERT INTO car_rentals (id, company, location, car_class, price) VALUES
(1, 'Hertz', 'SFO', 'Economy', 50.00),
(2, 'Enterprise', 'SFO', 'Luxury', 100.00);

-- Travel Policies
INSERT INTO travel_policies (tenant_id, id, details) VALUES ('d5f126e8-be47-48f1-ac8d-2f69e960f04c', 1, '{"max_price": 500, "max_class": "Economy"}');
INSERT INTO travel_policies (tenant_id, id, details) VALUES ('0b657261-633d-4677-a5ad-ff89059c42c5', 1, '{"max_price": 1000, "max_class": "Business"}');

-- Bookings
INSERT INTO bookings (id, tenant_id, user_id, departing_flight_id, arriving_flight_id, hotel_id, car_rental_id, booking_date, total_price, approval_required, approved, approver) VALUES
(1, 'd5f126e8-be47-48f1-ac8d-2f69e960f04c', '71d8afcc-9cbd-445d-aadc-9cb844008734', 1, 2, 1, 1, '2024-07-02 15:21:12', 384.78, false, false, null);

INSERT INTO bookings (id, tenant_id, user_id, departing_flight_id, arriving_flight_id, hotel_id, car_rental_id, booking_date, total_price, approval_required, approved, approver) VALUES
(1, '0b657261-633d-4677-a5ad-ff89059c42c5', '997015f8-69e7-4bcb-ad39-f8f69f8e8b43', 2, 1, 2, 2, '2024-11-22 21:23:21', 2807.42, true, true, '19488a0b-09c3-4613-a8c0-2874f0d66497');

-- Payment Information
INSERT INTO payment_information (id, tenant_id, user_id, stripe_customer_id, stripe_payment_method_id) VALUES
(1, 'd5f126e8-be47-48f1-ac8d-2f69e960f04c', '71d8afcc-9cbd-445d-aadc-9cb844008734', 'cus_123', 'pm_123');

INSERT INTO payment_information (id, tenant_id, user_id, stripe_customer_id, stripe_payment_method_id) VALUES
(1, '0b657261-633d-4677-a5ad-ff89059c42c5', '997015f8-69e7-4bcb-ad39-f8f69f8e8b43', 'cus_456', 'pm_456');