Db schema and Api's for Splitwise

Coming to DB Schema

Requirements

User: Each user should have a userId, name, email, mobile number.
Expense: Could either be EQUAL, EXACT or PERCENT
Users can add any amount, select any type of expense and split with any of the available users.
The percent and amount provided could have decimals upto two decimal places.
In case of percent, you need to verify if the total sum of percentage shares is 100 or not.
In case of exact, you need to verify if the total sum of shares is equal to the total amount or not.
The application should have a capability to show expenses for a single user as well as balances for everyone.
When asked to show balances, the application should show balances of a user with all the users where there is a non-zero balance.
The amount should be rounded off to two decimal places. Say if User1 paid 100 and amount is split equally among 3 people. Assign 33.34 to first person and 33.33 to others.

DB Schema

1. Users Table

Stores user information like ID, name, email, and mobile number.

CREATE TABLE users (
    user_id      BIGINT PRIMARY KEY AUTO_INCREMENT,
    name         VARCHAR(100) NOT NULL,
    hashed_password    
    email        VARCHAR(255) UNIQUE NOT NULL,
    mobile       VARCHAR(15) UNIQUE NOT NULL,
    user_image_url 
    state_of_user
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. Expenses Table

Stores expense details, including the total amount, who paid, and the type of expense.

CREATE TABLE expenses (
    expense_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    payer_id        BIG int
    amount       DECIMAL(10,2) NOT NULL, -- Total amount paid
    expense_type ENUM('EQUAL', 'EXACT', 'PERCENT') NOT NULL,
    description  VARCHAR(255),
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    payee_id   :list of userId
    
    group_id 
     expenses_settled 
    FOREIGN KEY (payer_id) REFERENCES users(user_id) ON DELETE CASCADE
);

Group table

{
grp_id,
name,
description,
grp owner,
created AT
list of userid,
group_status
}

3. Expense_Splits Table

This table keeps track of how an expense is split among users.

CREATE TABLE expense_splits (
    id              autoincrement id (PK)
    expense_id   BIGINT NOT NULL,  -- Reference to the expense
    user_id      BIGINT NOT NULL,  -- User who owes money
    amount       DECIMAL(10,2) DEFAULT NULL, -- Exact amount user owes
    percentage   DECIMAL(5,2) DEFAULT NULL, -- Percentage share (for PERCENT expenses)
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    payee_id     integer
    amount_paid_by_payee boolean
    FOREIGN KEY (expense_id) REFERENCES expenses(expense_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

• For EQUAL type → The amount field will store the split amount.

• For EXACT type → The amount field should sum to expense.amount.

• For PERCENT type → The percentage field should sum to 100.
  

4. Balances Table

This table keeps track of how much one user owes to another. It helps in quickly retrieving balances without recalculating from expenses.

CREATE TABLE balances (
    balance_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id      BIGINT NOT NULL,  -- User who owes money
    owes_to      BIGINT NOT NULL,  -- User to whom money is owed
    amount       DECIMAL(10,2) NOT NULL, -- Amount owed
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY (user_id, owes_to),  -- Ensure no duplicate entries
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE,
    FOREIGN KEY (owes_to) REFERENCES users(user_id) ON DELETE CASCADE
);

Queries for Key Operations

1. Add an Expense

1. Insert into the expenses table.

2. Insert into the expense_splits table.

3. Update the balances table accordingly.

INSERT INTO expenses (payer_id, amount, expense_type, description)
VALUES (1, 100.00, 'EQUAL', 'Dinner');

INSERT INTO expense_splits (expense_id, user_id, amount)
VALUES (1, 2, 33.34), (1, 3, 33.33), (1, 4, 33.33);

-- Update balances
INSERT INTO balances (user_id, owes_to, amount)
VALUES (2, 1, 33.34), (3, 1, 33.33), (4, 1, 33.33)
ON DUPLICATE KEY UPDATE amount = amount + VALUES(amount);

2. Get a User’s Expenses

SELECT e.expense_id, e.description, e.amount, e.expense_type, e.created_at,
       u.name AS payer_name, es.amount AS user_share
FROM expenses e
JOIN expense_splits es ON e.expense_id = es.expense_id
JOIN users u ON e.payer_id = u.user_id
WHERE es.user_id = 2; -- Get expenses for user_id = 2

3. Get User Balances

SELECT u1.name AS user, u2.name AS owes_to, b.amount
FROM balances b
JOIN users u1 ON b.user_id = u1.user_id
JOIN users u2 ON b.owes_to = u2.user_id
WHERE b.amount > 0; -- Show only non-zero balances

APIs for a Splitwise-like Expense Sharing System

Here’s a structured list of APIs to support expense creation, user balances, and transaction consistency in your Spring Boot backend.
1. User APIs

1.1 Create User

• Endpoint: POST /users

• Description: Registers a new user.

request {
  "name": "Alice",
  "email": "alice@example.com",
  "mobile": "9876543210"
}
response { "userId": 1, "message": "User created successfully" }

1.2 Get All Users

• Endpoint: GET api/v1/users

  [
    { "userId": 1, "name": "Alice", "email": "alice@example.com" },
    { "userId": 2, "name": "Bob", "email": "bob@example.com" }
  ]
  

  2. Expense APIs

  2.1 Create Expense

  • Endpoint: POST api/v1/expenses

  • Description: Creates an expense and splits it among users.

    request {
      "payerId": 1,
      "amount": 100.00,
      "expenseType": "EQUAL",
      "description": "Dinner",
      "participants": [2, 3, 4]
    }
    response  { "expenseId": 1, "message": "Expense added successfully" }
    

    Backend logic:

    • Store expense details in expenses table.

    • Compute splits based on expenseType and insert into expense_splits.

    • Update balances table.

  • 2.2 Get Expense Details

    • Endpoint: GET api/v1/expenses/{expenseId}

      respone {
        "expenseId": 1,
        "payer": "Alice",
        "amount": 100.00,
        "expenseType": "EQUAL",
        "description": "Dinner",
        "participants": [
          { "userId": 2, "amount": 33.34 },
          { "userId": 3, "amount": 33.33 },
          { "userId": 4, "amount": 33.33 }
        ]
      }
      

  3. Balance APIs

  3.1 Get Balances for a User

  • Endpoint: GET /users/{userId}/balances

    {
      "userId": 2,
      "balances": [
        { "owesTo": "Alice", "amount": 33.34 },
        { "owesTo": "Bob", "amount": 20.00 }
      ]
    }
    

    GET /grp/{grp_id}/balances
    {
    grp_id:1
    balances:[
    {"user1"
    
    }

3.2 Settle Balance Between Users

• Endpoint: POST /users/{userId}/settle

  request { "payeeId": 1, "amount": 20.00 }
  response { "payeeId": 1, "amount": 20.00 }
  

Backend logic:

• Deduct amount from balances table.

• If full amount is settled, remove entry.