Nile Logo
Documentation menu
gray arrow

Expensify Clone

This is a clone of the popular expense tracking app Expensify. As a multitenant app, it allows its customers to track their expenses and manage their budget while keeping their data secure and private. The application supports several types of users: admins, managers, and regular users. Each user type has different permissions and can access different parts of the app. It allows employees of an organization to submit expense reports, which are then reviewed by their managers and approved or rejected. The schema was designed to support collaboration within organizations, isolation between organizations and flexible reporting needs - filtering both expense reports and individual expenses by date, category, and status.

Postgres SQL Schema

The schema for this example has a few tables that represent the core entities of the expense reporting domain:

  1. employees table allows syncing with employee directories and finance systems.
  2. expense_reports table stores the expense reports submitted by employees.
  3. expense_report_items table stores the individual expenses of each report, including vendor, amount and url of uploaded receipt.
  4. expense_report_approvals table stores the approval status of each expense report.

Note that in this scenario all tables are tenant aware. They all have tenant_id column and this column is part of their primary key. This is necessary for isolating each tenant's expense reports into their private virtual database.

  1. employees table

The employees table stores the employees of each organization. It is used for syncing with employee directories and finance systems. We assume that the id, department and manager columns are coming from an external HR system and therefore we don't enforce uniqueness or autoincrement on id. This table is a bit denormalized since it is storing the employee's name which also appears in the users table. This avoids a 3-way join just to get the employee name.

CREATE TABLE IF NOT EXISTS employees (
    id INTEGER,
    tenant_id UUID NOT NULL,
    user_id UUID NOT NULL,
    name TEXT,
    department INTEGER,
    manager INTEGER,
    PRIMARY KEY(tenant_id, id),
    FOREIGN KEY (tenant_id, user_id) REFERENCES users.tenant_users(tenant_id, user_id)
);

CREATE index IF NOT EXISTS employees_by_user ON employees (user_id, tenant_id);
  1. expense_reports table

The expense_reports table stores the expense reports submitted by employees. Note that it references the employees table with the employee_id (rather than Nile's built-in user_id). This will let us support future integration with employee directories and accounting systems.

CREATE TABLE IF NOT EXISTS expense_report (
    id INTEGER NOT NULL,
    tenant_id UUID NOT NULL,
    employee_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (tenant_id, id),
    FOREIGN KEY (tenant_id, employee_id) REFERENCES employees(tenant_id, id)
);

CREATE index IF NOT EXISTS expense_report_by_employee ON expense_report (employee_id, tenant_id);
  1. expense_report_items table

The expense_report_items table stores the individual expenses of each report, including vendor, amount and url of uploaded receipt.

CREATE TABLE IF NOT EXISTS EXPENSE_REPORT_ITEMS (
    expense_report_id INTEGER NOT NULL,
    tenant_id UUID NOT NULL,
    id UUID NOT NULL,
    category TEXT NOT NULL,
    vendor TEXT NOT NULL,
    amount DECIMAL NOT NULL,
    currency TEXT DEFAULT 'USD' NOT NULL,
    expense_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    receipt_url TEXT,
    comment TEXT,
    PRIMARY KEY (tenant_id, expense_report_id, id),
    FOREIGN KEY (tenant_id, expense_report_id) REFERENCES expense_report(tenant_id, id)
);

CREATE index IF NOT EXISTS expense_report_items_by_expense_report ON expense_report_items (expense_report_id, tenant_id);
  1. expense_report_approvals table

The expense_report_approvals table stores the approval status of each expense report. It references both expense_reports and employees.

CREATE TABLE IF NOT EXISTS expense_approvals (
    expense_report_id INTEGER NOT NULL,
    tenant_id UUID NOT NULL,
    approver_id INTEGER NOT NULL,
    approved_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    approval_status TEXT NOT NULL,
    reason TEXT,
    PRIMARY KEY (tenant_id, expense_report_id),
    FOREIGN KEY (tenant_id, expense_report_id) REFERENCES expense_report(tenant_id, id),
    FOREIGN KEY (tenant_id, approver_id) REFERENCES employees(tenant_id, id)
);

CREATE index IF NOT EXISTS expense_approvals_by_expense_report ON expense_approvals (expense_report_id, tenant_id);

Sample data

-- create personal accounts for everyone
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 ('7d68eb27-07d3-445e-b8d3-6f25a0372b18',  'Michael Smith',       'michael.smith@yahoo.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 ('3287c076-da60-4e03-9fee-378771f3ffdb',  'Olivia Garcia',       'olivia.garcia@hotmail.com');
INSERT INTO users.users (id, name, email) VALUES ('583bbc25-73e2-411d-8d5f-b21c8f368d88',  'James Wilson',        'james.wilson@aol.com');
INSERT INTO users.users (id, name, email) VALUES ('997015f8-69e7-4bcb-ad39-f8f69f8e8b43',  'Isabella Rodriguez',  'isabella.rodriguez@protonmail.com');
INSERT INTO users.users (id, name, email) VALUES ('ef814aa6-504b-4a5a-8e7d-dd548184a38f',  'William Anderson',    'william.anderson@mail.com');
INSERT INTO users.users (id, name, email) VALUES ('2517d88d-6824-4f3e-b935-2e0363792e4f',  'Mia Thompson',        'mia.thompson@zoho.com');
INSERT INTO users.users (id, name, email) VALUES ('26823b33-7ecf-4ee2-95af-4ae5f53260c4',  'Ethan Taylor',        'ethan.taylor@fastmail.com');

-- create a few companies
INSERT INTO tenants (id, name) VALUES ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 'Quantum Innovations Inc.');
INSERT INTO tenants (id, name) VALUES ('0b657261-633d-4677-a5ad-ff89059c42c5', 'Bright Horizon Enterprises');
INSERT INTO tenants (id, name) VALUES ('393d8aa8-3063-4913-9e36-66831fbf91a3', 'Crestview Technology Solutions');

-- connect users to companies
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 'cef6fc75-25d2-4cfb-b328-99a0e6f79ae6', 'ejohnson@quantuminv.com','{"admin"}');
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', '7d68eb27-07d3-445e-b8d3-6f25a0372b18', 'msmith@quantuminv.com','{"manager"}');
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', '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', '3287c076-da60-4e03-9fee-378771f3ffdb', 'ogarcia@brighthorizon.com','{"lawyer"}');
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('0b657261-633d-4677-a5ad-ff89059c42c5', '583bbc25-73e2-411d-8d5f-b21c8f368d88', 'jwilson@brighthorizon.com','{"intern"}');
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"}');
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 'ef814aa6-504b-4a5a-8e7d-dd548184a38f', 'andreson@crestview.com','{"admin"}');
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', '2517d88d-6824-4f3e-b935-2e0363792e4f', 'thompson@crestview.com','{"teacher"}');
insert into users.tenant_users (tenant_id, user_id, email, roles)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', '26823b33-7ecf-4ee2-95af-4ae5f53260c4', 'taylor@crestview.com','{"teacher"}');

-- employees
INSERT INTO employees (tenant_id, id, user_id, name, manager) VALUES ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 1, 'cef6fc75-25d2-4cfb-b328-99a0e6f79ae6', 'Emily Johnson', 2);
INSERT INTO employees (tenant_id, id, user_id, name, manager) VALUES ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 2, '7d68eb27-07d3-445e-b8d3-6f25a0372b18', 'Michael Smith', null);
INSERT INTO employees (tenant_id, id, user_id, name, manager) VALUES ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 3, '71d8afcc-9cbd-445d-aadc-9cb844008734', 'Sophia Martinez', 2);
INSERT INTO employees (tenant_id, id, user_id, name, manager) VALUES ('0b657261-633d-4677-a5ad-ff89059c42c5', 4, '19488a0b-09c3-4613-a8c0-2874f0d66497', 'David Brown', null);
INSERT INTO employees (tenant_id, id, user_id, name, manager) VALUES ('0b657261-633d-4677-a5ad-ff89059c42c5', 5, '3287c076-da60-4e03-9fee-378771f3ffdb', 'Olivia Garcia', 4);
INSERT INTO employees (tenant_id, id, user_id, name, manager) VALUES ('0b657261-633d-4677-a5ad-ff89059c42c5', 6, '583bbc25-73e2-411d-8d5f-b21c8f368d88', 'James Wilson', 4);
INSERT INTO employees (tenant_id, id, user_id, name, manager) VALUES ('0b657261-633d-4677-a5ad-ff89059c42c5', 7, '997015f8-69e7-4bcb-ad39-f8f69f8e8b43', 'Isabella Rodriguez', 4);
INSERT INTO employees (tenant_id, id, user_id, name, manager) VALUES ('393d8aa8-3063-4913-9e36-66831fbf91a3', 8, 'ef814aa6-504b-4a5a-8e7d-dd548184a38f', 'William Anderson', null);
INSERT INTO employees (tenant_id, id, user_id, name, manager) VALUES ('393d8aa8-3063-4913-9e36-66831fbf91a3', 9, '2517d88d-6824-4f3e-b935-2e0363792e4f', 'Mia Thompson', 8);
INSERT INTO employees (tenant_id, id, user_id, name, manager) VALUES ('393d8aa8-3063-4913-9e36-66831fbf91a3', 10, '26823b33-7ecf-4ee2-95af-4ae5f53260c4', 'Ethan Taylor', 8);

-- expense reports
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 1, 1, 'submitted');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 2, 1, 'processed');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 3, 1, 'submitted');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 4, 2, 'submitted');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 5, 2, 'waiting payment');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 6, 3, 'submitted');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('0b657261-633d-4677-a5ad-ff89059c42c5', 7, 4, 'pending');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('0b657261-633d-4677-a5ad-ff89059c42c5', 8, 4, 'cancelled');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('0b657261-633d-4677-a5ad-ff89059c42c5', 9, 6, 'submitted');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('393d8aa8-3063-4913-9e36-66831fbf91a3', 10, 8, 'paid');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('393d8aa8-3063-4913-9e36-66831fbf91a3', 11, 8, 'submitted');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('393d8aa8-3063-4913-9e36-66831fbf91a3', 12, 10, 'submitted');
INSERT INTO expense_report (tenant_id, id, employee_id, status) VALUES ('393d8aa8-3063-4913-9e36-66831fbf91a3', 13, 10, 'submitted');

-- expense report items
-- report 1
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 1, public.uuid_generate_v7(), 'OpenAI', 'OpenAI', 342);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 1, public.uuid_generate_v7(), 'Gym', 'Fitness World', 125);

-- report 2
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 2, public.uuid_generate_v7(), 'Spa', 'Serenity Spa', 230);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 2, public.uuid_generate_v7(), 'Conferences', 'Event Horizon', 510);

-- report 3
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 3, public.uuid_generate_v7(), 'OpenAI', 'Innovative Tech Ltd.', 276);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 3, public.uuid_generate_v7(), 'Gym', 'Active Life Gym', 123);


-- report 4
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 4, public.uuid_generate_v7(), 'Office Supplies', 'OfficeMart', 317);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 4, public.uuid_generate_v7(), 'Other', 'Misc Goods Ltd.', 204);

-- report 5
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 5, public.uuid_generate_v7(), 'Meals', 'Urban Eats', 123);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 5, public.uuid_generate_v7(), 'Travel', 'Skyline Travel', 430);

-- report 6
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 6, public.uuid_generate_v7(), 'Spa', 'Relaxation Retreat', 154);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('8f1192ee-bb34-427b-9fa9-2ef647dc9990', 6, public.uuid_generate_v7(), 'Conferences', 'Conference Connect', 500);

-- report 7
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('0b657261-633d-4677-a5ad-ff89059c42c5', 7, public.uuid_generate_v7(), 'Travel', 'Global Getaways', 452);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('0b657261-633d-4677-a5ad-ff89059c42c5', 7, public.uuid_generate_v7(), 'Meals', 'City Diner', 89);

-- report 8
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('0b657261-633d-4677-a5ad-ff89059c42c5', 8, public.uuid_generate_v7(), 'Other', 'Misc Goods Ltd.', 204);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('0b657261-633d-4677-a5ad-ff89059c42c5', 8, public.uuid_generate_v7(), 'Training', 'Training Partners', 289);
-- report 9
    insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('0b657261-633d-4677-a5ad-ff89059c42c5', 9, public.uuid_generate_v7(), 'Office Supplies', 'Stationery Plus', 190);
    insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('0b657261-633d-4677-a5ad-ff89059c42c5', 9, public.uuid_generate_v7(), 'Other', 'General Goods Inc.', 367);

-- report 10
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 10, public.uuid_generate_v7(), 'Classroom Supplies', 'Educational Emporium', 312);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 10, public.uuid_generate_v7(), 'Training', 'Professional Development Co.', 540);

-- report 11
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 11, public.uuid_generate_v7(), 'OpenAI', 'NextGen Innovations', 298);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 11, public.uuid_generate_v7(), 'Gym', 'Peak Fitness', 100);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 11, public.uuid_generate_v7(), 'Spa', 'Heavenly Spa Retreat', 220);

-- report 12
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 12, public.uuid_generate_v7(), 'Travel', 'Wanderlust Travel Agency', 375);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 12, public.uuid_generate_v7(), 'Meals', 'Gourmet Bistro', 145);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 12, public.uuid_generate_v7(), 'Office Supplies', 'Workplace Essentials', 89);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 12, public.uuid_generate_v7(), 'Other', 'All-Purpose Supplies', 175);

-- report 13
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 13, public.uuid_generate_v7(), 'Classroom Supplies', 'Educator''s Warehouse', 333);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 13, public.uuid_generate_v7(), 'Training', 'Skill Builders Inc.', 410);
insert into expense_report_items (tenant_id, expense_report_id, id, category, vendor, amount)
    values ('393d8aa8-3063-4913-9e36-66831fbf91a3', 13, public.uuid_generate_v7(), 'Other', 'Diverse Needs Ltd.', 95);
nile logo
Copyright © 2024 Nile
Privacy policy|Cookie policy
twitter logolinkedin logogithub octocat
DocumentationQuick Start
Blog
AboutCareersPricing
Contact SalesFAQ
All Systems Operational ↗