Financial Details

Overview

The Financial Module is designed to handle various financial operations, including managing accounts, categories, transactions, and payments. This documentation provides an in-depth look at the structure and functionality of the module.

Architecture

This architecture diagram provides a visual representation of how the various components of the financial module interact with each other. The diagram includes the APIs for accounts, categories, transactions, payments, and their relationships with the database schema.

+-----------------------------------+
|           Client (Frontend)       |
|                                   |
|   - User Interface                |
|   - API Requests                  |
+--------------------+--------------+
                     |
                     v
+--------------------+--------------+
|       Financial Module APIs       |
|                                   |
|   +------------------------------+|
|   |         Accounts API         ||
|   +------------------------------+|
|   | - GET /api/accounts          ||
|   | - GET /api/accounts/:id      ||
|   | - POST /api/accounts         ||
|   | - PATCH /api/accounts/:id    ||
|   | - DELETE /api/accounts/:id   ||
|   +------------------------------+|
|                                   |
|   +------------------------------+|
|   |         Categories API       ||
|   +------------------------------+|
|   | - GET /api/categories        ||
|   | - GET /api/categories/:id    ||
|   | - POST /api/categories       ||
|   | - PATCH /api/categories/:id  ||
|   | - DELETE /api/categories/:id ||
|   +------------------------------+|
|                                   |
|   +------------------------------+|
|   |         Transactions API     ||
|   +------------------------------+|
|   | - GET /api/transactions      ||
|   | - GET /api/transactions/:id  ||
|   | - POST /api/transactions     ||
|   | - PATCH /api/transactions/:id||
|   | - DELETE /api/transactions/:id||
|   +------------------------------+|
|                                   |
|   +------------------------------+|
|   |         Summary API          ||
|   +------------------------------+|
|   | - GET /api/summary           ||
|   +------------------------------+|
|                                   |
|   +------------------------------+|
|   |         Payments API         ||
|   +------------------------------+|
|   | - GET /api/payments          ||
|   +------------------------------+|
+-----------------------------------+
                     |
                     v
+--------------------+--------------+
|      Middleware & Utilities       |
|                                   |
|   +------------------------------+|
|   |      Clerk Middleware        ||
|   +------------------------------+|
|   |    Authentication Checks     ||
|   +------------------------------+|
|   |                              ||
|   |      Zod Validator           ||
|   +------------------------------+|
|   |    Request Validation        ||
|   +------------------------------+|
|                                   |
|   +------------------------------+|
|   |    Utility Functions         ||
|   +------------------------------+|
|   | - calculatePercentageChange  ||
|   | - fillMissingDays            ||
|   | - convertAmountFromMilliUnits||
|   +------------------------------+|
+-----------------------------------+
                     |
                     v
+--------------------+--------------+
|         Database (PostgreSQL)     |
|                                   |
|   +------------------------------+|
|   |         Accounts Table       ||
|   +------------------------------+|
|   | - id (PK)                    ||
|   | - plaidId                    ||
|   | - name                       ||
|   | - userId                     ||
|   +------------------------------+|
|                                   |
|   +------------------------------+|
|   |         Categories Table     ||
|   +------------------------------+|
|   | - id (PK)                    ||
|   | - plaidId                    ||
|   | - name                       ||
|   | - name_type                  ||
|   | - userId                     ||
|   +------------------------------+|
|                                   |
|   +------------------------------+|
|   |       Transactions Table     ||
|   +------------------------------+|
|   | - id (PK)                    ||
|   | - amount                     ||
|   | - payee                      ||
|   | - notes                      ||
|   | - date                       ||
|   | - accountId (FK)             ||
|   | - categoryId (FK)            ||
|   | - studentId (FK)             ||
|   +------------------------------+|
|                                   |
|   +------------------------------+|
|   |         Payments Table       ||
|   +------------------------------+|
|   | - id (PK)                    ||
|   | - amount                     ||
|   | - payee                      ||
|   | - date                       ||
|   +------------------------------+|
+-----------------------------------+

Description

  1. Client (Frontend):

    • The user interface through which users interact with the financial module. It sends API requests to the backend.

  2. Financial Module APIs:

    • Accounts API: Handles operations related to accounts (create, read, update, delete).
    • Categories API: Manages categories used in transactions (create, read, update, delete).
    • Transactions API: Manages financial transactions (create, read, update, delete).
    • Summary API: Provides a financial summary for the user.
    • Payments API: Manages payments and related information.

  3. Middleware & Utilities:

    • Clerk Middleware: Ensures authentication and authorization of users.
    • Zod Validator: Validates incoming requests to ensure they conform to the expected schema.
    • Utility Functions: Contains helper functions like calculatePercentageChange, fillMissingDays, and convertAmountFromMilliUnits to support various financial operations.

  4. Database (PostgreSQL):

    • Accounts Table: Stores account information.
    • Categories Table: Stores category information.
    • Transactions Table: Stores transaction details.
    • Payments Table: Stores payment records.

Relationships

  • Accounts Table:

    • One-to-Many relationship with Transactions Table (an account can have many transactions).

  • Categories Table:

    • One-to-Many relationship with Transactions Table (a category can have many transactions).

  • Transactions Table:

    • Contains foreign keys linking to Accounts, Categories, and Students Tables (each transaction is associated with an account, a category, and optionally a student).

  • Payments Table:

    • Each payment is linked to a payee and contains details about the transaction.

This architecture ensures a modular and organized structure for managing financial data, allowing for easy maintenance and scalability.

Accounts API

Endpoints

GET /api/accounts

  • Description: Retrieves all accounts for the authenticated user.
  • Middleware: clerkMiddleware
  • Response: 
    {
  "data": [
    {
      "id": "string",
      "name": "string"
    }
  ]
}

GET /api/accounts/:id

  • Description: Retrieves a single account by its ID for the authenticated user.
  • Middleware: clerkMiddleware, zValidator
  • Response: 
    {
  "data": {
    "id": "string",
    "name": "string"
  }
}

POST /api/accounts

  • Description: Creates a new account.
  • Middleware: clerkMiddleware, zValidator
  • Request Body: 
    {
  "name": "string"
}
  • Response: 
    {
  "data": {
    "id": "string",
    "name": "string"
  }
}

POST /api/accounts/bulk-delete

  • Description: Deletes multiple accounts by their IDs.
  • Middleware: clerkMiddleware, zValidator
  • Request Body: 
    {
  "ids": ["string"]
}
  • Response: 
    {
  "data": [
    {
      "id": "string"
    }
  ]
}

PATCH /api/accounts/:id

  • Description: Updates an account by its ID.
  • Middleware: clerkMiddleware, zValidator
  • Request Body: 
    {
  "name": "string"
}
  • Response: 
    {
  "data": {
    "id": "string",
    "name": "string"
  }
}

DELETE /api/accounts/:id

  • Description: Deletes an account by its ID.
  • Middleware: clerkMiddleware, zValidator
  • Response: 
    {
  "data": {
    "id": "string"
  }
}

Data Schema

Account Schema

export const accounts = pgTable("accounts", {
  id: text("id").primaryKey(),
  plaidId: text("plaid_id"),
  name: text("name").notNull(),
  userId: text("user_id").notNull(),
});

Categories API

Endpoints

GET /api/categories

  • Description: Retrieves all categories for the authenticated user.
  • Middleware: clerkMiddleware
  • Response: 
    {
  "data": [
    {
      "id": "string",
      "name": "string",
      "name_type": "string"
    }
  ]
}

GET /api/categories/:id

  • Description: Retrieves a single category by its ID for the authenticated user.
  • Middleware: clerkMiddleware, zValidator
  • Response: 
    {
  "data": {
    "id": "string",
    "name": "string",
    "name_type": "string"
  }
}

POST /api/categories

  • Description: Creates a new category.
  • Middleware: clerkMiddleware, zValidator
  • Request Body: 
    {
  "name": "string",
  "name_type": "string"
}
  • Response: 
    {
  "data": {
    "id": "string",
    "name": "string",
    "name_type": "string"
  }
}

POST /api/categories/bulk-delete

  • Description: Deletes multiple categories by their IDs.
  • Middleware: clerkMiddleware, zValidator
  • Request Body: 
    {
  "ids": ["string"]
}
  • Response: 
    {
  "data": [
    {
      "id": "string"
    }
  ]
}

PATCH /api/categories/:id

  • Description: Updates a category by its ID.
  • Middleware: clerkMiddleware, zValidator
  • Request Body: 
    {
  "name": "string",
  "name_type": "string"
}
  • Response: 
    {
  "data": {
    "id": "string",
    "name": "string",
    "name_type": "string"
  }
}

DELETE /api/categories/:id

  • Description: Deletes a category by its ID.
  • Middleware: clerkMiddleware, zValidator
  • Response: 
    {
  "data": {
    "id": "string"
  }
}

Data Schema

Category Schema

export const categories = pgTable("categories", {
  id: text("id").primaryKey(),
  plaidId: text("plaid_id"),
  name: text("name").notNull(),
  name_type: text("name_type").notNull(),
  userId: text("user_id").notNull(),
});

Transactions API

Endpoints

GET /api/transactions

  • Description: Retrieves all transactions for the authenticated user.
  • Middleware: clerkMiddleware, zValidator
  • Response: 
    {
  "data": [
    {
      "id": "string",
      "date": "string",
      "category": "string",
      "payee": "string",
      "amount": "number",
      "notes": "string",
      "account": "string"
    }
  ]
}

GET /api/transactions/:id

  • Description: Retrieves a single transaction by its ID for the authenticated user.
  • Middleware: clerkMiddleware, zValidator
  • Response: 
    {
  "data": {
    "id": "string",
    "date": "string",
    "category": "string",
    "payee": "string",
    "amount": "number",
    "notes": "string",
    "account": "string"
  }
}

POST /api/transactions

  • Description: Creates a new transaction.
  • Middleware: clerkMiddleware, zValidator
  • Request Body: 
    {
  "amount": "number",
  "payee": "string",
  "notes": "string",
  "date": "string",
  "accountId": "string",
  "categoryId": "string",
  "studentId": "string"
}
  • Response: 
    {
  "data": {
    "id": "string",
    "date": "string",
    "category": "string",
    "payee": "string",
    "amount": "number",
    "notes": "string",
    "account": "string"
  }
}

POST /api/transactions/bulk-create

  • Description: Creates multiple transactions.
  • Middleware: clerkMiddleware, zValidator
  • Request Body: 
    [
  {
    "amount": "number",
    "payee": "string",
    "notes": "string",
    "date": "string",
    "accountId": "string",
    "categoryId": "string",
    "studentId": "string"
  }
]
  • Response: 
    {
  "data": [
    {
      "id": "string",
      "date": "string",
      "category": "string",
      "payee": "string",
      "amount": "number",
      "notes": "string",
      "account": "string"
    }
  ]
}

POST /api/transactions/bulk-delete

  • Description: Deletes multiple transactions by their IDs.
  • Middleware: clerkMiddleware, zValidator
  • Request Body: 
    {
  "ids": ["string"]
}
  • Response: 
    {
  "data": [
    {
      "id": "string"
    }
  ]
}

PATCH /api/transactions/:id

  • Description: Updates a transaction by its ID.
  • Middleware: clerkMiddleware, zValidator
  • Request Body: 
    {
  "amount": "number",
  "payee": "string",
  "notes": "string",
  "date": "string",
  "accountId": "string",
  "categoryId": "string",
  "studentId": "string"

}

- **Response**:
```json
{
  "data": {
    "id": "string",
    "date": "string",
    "category": "string",
    "payee": "string",
    "amount": "number",
    "notes": "string",
    "account": "string"
  }
}

DELETE /api/transactions/:id

  • Description: Deletes a transaction by its ID.
  • Middleware: clerkMiddleware, zValidator
  • Response: 
    {
  "data": {
    "id": "string"
  }
}

Data Schema

Transaction Schema

export const transactions = pgTable("transactions", {
  id: text("id").primaryKey(),
  amount: integer("amount").notNull(),
  payee: text("payee").notNull(),
  notes: text("note"),
  date: timestamp("date", { mode: "date" }).notNull(),
  accountId: text("account_id")
    .references(() => accounts.id, {
      onDelete: "cascade",
    })
    .notNull(),
  categoryId: text("category_id").references(() => categories.id, {
    onDelete: "set null",
  }),
  studentId: text("student_id").references(() => students.id, {
    onDelete: "set null",
  }),
});

Summary API

Endpoints

GET /api/summary

  • Description: Retrieves a financial summary for the authenticated user.
  • Middleware: clerkMiddleware, zValidator
  • Response: 
    {
  "data": {
    "remainingAmount": "number",
    "remainingChange": "number",
    "incomeAmount": "number",
    "incomeChange": "number",
    "expensesAmount": "number",
    "expensesChange": "number",
    "categories": [
      {
        "name": "string",
        "value": "number"
      }
    ],
    "days": [
      {
        "date": "string",
        "income": "number",
        "expenses": "number"
      }
    ]
  }
}

Payments API

Endpoints

GET /api/payments

  • Description: Retrieves all payments.
  • Response: 
    {
  "payments": [
    {
      "id": "string",
      "amount": "number",
      "payee": "string",
      "date": "string"
    }
  ]
}

Utility Functions

calculatePercentageChange

  • Description: Calculates the percentage change between two values.
  • Parameters:
    • currentValue: number
    • previousValue: number
  • Returns: number

fillMissingDays

  • Description: Fills missing days in a dataset with zero values.
  • Parameters:
    • data: Array<{ date: string, income: number, expenses: number }>
    • startDate: Date
    • endDate: Date
  • Returns: Array<{ date: string, income: number, expenses: number }>

convertAmountFromMilliUnits

  • Description: Converts an amount from milli-units to standard units.
  • Parameters:
    • amount: number
  • Returns: number

Models

Accounts

  • Schema: 
    export const accounts = pgTable("accounts", {
  id: text("id").primaryKey(),
  plaidId: text("plaid_id"),
  name: text("name").notNull(),
  userId: text("user_id").notNull(),
});

Categories

  • Schema: 
    export const categories = pgTable("categories", {
  id: text("id").primaryKey(),
  plaidId: text("plaid_id"),
  name: text("name").notNull(),
  name_type: text("name_type").notNull(),
  userId: text("user_id").notNull(),
});

Transactions

  • Schema: 
    export const transactions = pgTable("transactions", {
  id: text("id").primaryKey(),
  amount: integer("amount").notNull(),
  payee: text("payee").notNull(),
  notes: text("note"),
  date: timestamp("date", { mode: "date" }).notNull(),
  accountId: text("account_id")
    .references(() => accounts.id, {
      onDelete: "cascade",
    })
    .notNull(),
  categoryId: text("category_id").references(() => categories.id, {
    onDelete: "set null",
  }),
  studentId: text("student_id").references(() => students.id, {
    onDelete: "set null",
  }),
});

Payments

  • Schema: 
    export const payments = pgTable("payments", {
  id: text("id").primaryKey(),
  amount: integer("amount").notNull(),
  payee: text("payee").notNull(),
  date: timestamp("date", { mode: "date" }).notNull(),
});