Design Proposal & Discussion

[theadell]

Hey there! @abdelrahman543873

This Github issue is intended to be our open forum for brainstorming and hashing out our design decisions for the Refundly app. It's just the first draft of what I think our application architecture could look like, and I'm open to refining this model with your valuable input. So, let's dive in!

Database Schema

We're going to stick with PostgreSQL for our database - it's scalable, robust and super reliable. I've thought up a few tables that we might need:

• Users: All the info we need about our users, including their role in the company.
• Companies: All the details about each company that uses our app.
• ExpenseEntries: This is where we store all the expense entries submitted by users.
• UserRoles: A table to keep track of the different roles a user can have.
• Currencies: A reference table for the different currencies companies might operate in.
• Notifications: A table to keep track of all the updates we need to send users - think expense approval, rejection, modifications, etc.
• AuditLogs: This is where we track every action on the system for transparency.
• Payments: We create an entry here when an expense is approved to track the reimbursement process.

The detailed schema proposal is listed below. Let me know what you think!

-- Table to hold various available currencies.
CREATE TABLE Currencies (
    id SERIAL PRIMARY KEY,
    currency_code CHAR(3) NOT NULL UNIQUE  -- ISO 4217 currency codes like 'USD', 'EUR', etc.
);

-- Table to hold different types of expenses.
CREATE TABLE ExpenseTypes (
    id SERIAL PRIMARY KEY,
    type VARCHAR(255) NOT NULL
);

-- Table to hold different types of users (admin, moderator, employee).
CREATE TABLE UserTypes (
    id SERIAL PRIMARY KEY,
    type VARCHAR(255) NOT NULL
);

-- Table to hold different roles that a user can have (in a company).
CREATE TABLE UserRoles (
    id SERIAL PRIMARY KEY,
    role VARCHAR(255) NOT NULL
);

-- Table to hold information about different companies.
CREATE TABLE Companies (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address TEXT,
    payment_details TEXT,
    currency_id INTEGER NOT NULL,  -- Default currency for the company.
    FOREIGN KEY (currency_id) REFERENCES Currencies(id) ON DELETE RESTRICT
);

-- Table to hold information about users.
CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    company_id INTEGER,
    user_type_id INTEGER NOT NULL,
    user_role_id INTEGER,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    oidc_id VARCHAR(255) UNIQUE,  -- For OIDC users, we store their OIDC ID.
    password_hash VARCHAR(255),  -- For non-OIDC users, we store a hashed version of their password.
    FOREIGN KEY (company_id) REFERENCES Companies(id) ON DELETE SET NULL,
    FOREIGN KEY (user_type_id) REFERENCES UserTypes(id) ON DELETE RESTRICT,
    FOREIGN KEY (user_role_id) REFERENCES UserRoles(id) ON DELETE SET NULL
);

-- Table to hold information about expenses.
CREATE TABLE Expenses (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    expense_type_id INTEGER NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,  -- The amount of expense in the specified currency.
    date DATE NOT NULL,  -- Date when the expense was made.
    status VARCHAR(255) NOT NULL,  -- The status of the expense (pending, approved, rejected).
    description TEXT,  -- Description of the expense.
    receipt_url TEXT,  -- URL where the receipt image/PDF is stored.
    currency_id INTEGER NOT NULL,  -- Currency of the expense.
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE,
    FOREIGN KEY (expense_type_id) REFERENCES ExpenseTypes(id) ON DELETE RESTRICT,
    FOREIGN KEY (currency_id) REFERENCES Currencies(id) ON DELETE RESTRICT
);

-- Table for Payments.
CREATE TABLE Payments (
    id SERIAL PRIMARY KEY,
    expense_id INTEGER NOT NULL,  -- The expense being paid.
    amount DECIMAL(10, 2) NOT NULL,  -- The amount paid.
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- When the payment was made.
    method VARCHAR(255),  -- The method of payment.
    status VARCHAR(255),  -- The status of the payment (like 'pending', 'complete', 'failed').
    FOREIGN KEY (expense_id) REFERENCES Expenses(id) ON DELETE CASCADE
);

-- Table for AuditLogs.
CREATE TABLE AuditLogs (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,  -- The user who performed the action.
    action VARCHAR(255) NOT NULL,  -- The action performed (like 'expense created', 'expense approved').
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- When the action was performed.
    details TEXT,  -- Optional details about the action.
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE
);

-- Table for refresh tokens 
CREATE TABLE RefreshTokens (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    token VARCHAR(255) UNIQUE NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE
);

-- Table for Notifications.
CREATE TABLE Notifications (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,  -- The user to be notified.
    message TEXT NOT NULL,  -- The message for the user.
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- When the notification was created.
    read BOOLEAN NOT NULL DEFAULT FALSE,  -- Whether the notification has been read.
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE
);

-- Views for reporting.

-- Total expenses per company, per month.
CREATE VIEW MonthlyCompanyExpenses AS
SELECT
    DATE_TRUNC('month', e.date) AS month,
    c.name AS company_name,
    cur.currency_code AS currency,
    SUM(e.amount) AS total_expense
FROM
    Expenses e
JOIN
    Users u ON u.id = e.user_id
JOIN
    Companies c ON c.id = u.company_id
JOIN
    Currencies cur ON cur.id = e.currency_id
GROUP BY
    DATE_TRUNC('month', e.date),
    c.name,
    cur.currency_code;

-- Total expenses per user, per month.
CREATE VIEW MonthlyUserExpenses AS
SELECT
    DATE_TRUNC('month', e.date) AS month,
    u.name AS user_name,
    c.name AS company_name,
    cur.currency_code AS currency,
    SUM(e.amount) AS total_expense
FROM
    Expenses e
JOIN
    Users u ON u.id = e.user_id
JOIN
    Companies c ON c.id = u.company_id
JOIN
    Currencies cur ON cur.id = e.currency_id
GROUP BY
    DATE_TRUNC('month', e.date),
    u.name,
    c.name,
    cur.currency_code;

Session Management

Our users should be able to log in using Google (OpenId Connect), Github, Gitlab, Atlassian (OAuth 2.0), or just a simple Form Login (basic Auth). After a successful login, I propose we use JWTs with Refresh Tokens for session management. We issue a short-lived access token for requests and a long-lived refresh token to obtain new access tokens after the short ones expire.

Storage

We need to handle file uploads for receipts, so I suggest we use an S3 bucket for storage and just keep the links in our PostgreSQL database. Simple and functional, right?

REST Endpoints

• User-related endpoints:

  • POST /api/users: Register a new user.
  • POST /api/users/login: Authenticate a user and return a token.
  • GET /api/users/me: Get the authenticated user's details.
  • PATCH /api/users/me: Update the authenticated user's details.
  • DELETE /api/users/me: Delete the authenticated user's account.
  • GET /api/users/{id}/expenses: Get all expenses of a specific user.

• Company-related endpoints:

  • POST /api/companies: Create a new company.
  • GET /api/companies/{id}: Get a company's details.
  • PATCH /api/companies/{id}: Update a company's details.
  • DELETE /api/companies/{id}: Delete a company.
  • GET /api/companies/{id}/users: Get all users of a specific company.
  • GET /api/companies/{id}/expenses: Get all expenses of a specific company.

• Expense-related endpoints:

  • POST /api/expenses: Create a new expense entry.
  • GET /api/expenses: Get all the expense entries of the authenticated user.
  • GET /api/expenses/{id}: Get a specific expense entry.
  • PATCH /api/expenses/{id}: Update a specific expense entry.
  • DELETE /api/expenses/{id}: Delete a specific expense entry.

• Payments-related endpoints:

  • POST /api/payments: Create a new payment entry.
  • GET /api/payments: Get all the payment entries of the authenticated user.
  • GET /api/payments/{id}: Get a specific payment entry.
  • PATCH /api/payments/{id}: Update a specific payment entry.
  • DELETE /api/payments/{id}: Delete a specific payment entry.

• Notifications-related endpoints:

  • GET /api/notifications: Get all the notifications of the authenticated user.
  • PATCH /api/notifications/{id}: Mark a specific notification as read.

• AuditLogs-related endpoints:

  • GET /api/companies/{id}/auditlogs: Get all the audit logs of a specific company.

User Stories

To keep us user-focused, I've drafted a few user stories. Here's what I've got so far:

As an employee, I want to easily submit my expenses for approval, so I can get reimbursed for my out-of-pocket expenses.

As a company admin, I want to be able to review, approve, or reject expense entries, so I can manage my company's expenses.

As a system admin, I want to manage users and companies, so I can keep the system running smoothly.

As a user, I want to be notified about changes to my expense entries, so I don't have to constantly check the app.

As a company manager, I want access to audit logs, so I can track the activities in my company.

So, that's what I've been thinking about so far for Refundly's design. This is just a rough draft, and I'm sure we'll make plenty of tweaks along the way. Later, we might think about adding features like budgets and project tracking.

I'm excited to hear your thoughts and ideas on this. We're going to learn a lot and build something useful together. Feel free to critique or add to this proposal. Let's start refining this and make Refundly a reality.

Looking forward to your feedback!

[abdelrahman543873]

@adel-habib great idea with the usage of postgres, i have read all of the draft and all of the entities and i would say they would serve as a good start, what we can do even to improve our efficiency is start from frontend and design, at the end of the day we are going to serve the frontend whatever it needs , and to keep the whole app user focused i would suggest we start with frontend designs first to have a clear picture of the APP and then we will modify the models you suggested according to what the frotnend needs , for example by understanding how the frontend looks like , you can have a very good idea about will the queries will look like and therefore have a scalable db design that doesn't only facilitate ATOMIC transactions but also helps in a db design that would make db queries fast and scalable looking forwards to hear what you think about this

[theadell]

Good idea. I will try to prepare something for Monday. On Monday we can have a Slack huddle to put everything together. I think once we decide what pages/components/forms we need, it should be easy to start developing the API and the front end simultaneously. I agree with you that we should keep it very user-focused.