personal-finance/BACKEND_PROMPT.md

Backend API Development Prompt

Build a REST API backend for a personal finance management application using TypeScript, Fastify, and PostgreSQL.

Tech Stack

• Runtime: Node.js with TypeScript
• Framework: Fastify
• Database: PostgreSQL
• ORM: Drizzle ORM (recommended) or Prisma
• Authentication: JWT with refresh tokens
• Validation: Zod or TypeBox
• Password hashing: bcrypt or argon2

Project Structure

backend-api/
├── src/
│   ├── index.ts              # Entry point
│   ├── app.ts                # Fastify app setup
│   ├── config/               # Environment config
│   ├── db/
│   │   ├── schema.ts         # Database schema
│   │   ├── migrations/       # Database migrations
│   │   └── client.ts         # DB connection
│   ├── modules/
│   │   ├── auth/             # Auth routes, handlers, service
│   │   ├── users/
│   │   ├── net-worth/
│   │   ├── debts/
│   │   ├── invoices/
│   │   └── cashflow/
│   ├── middleware/
│   │   └── auth.ts           # JWT verification
│   └── utils/
├── package.json
├── tsconfig.json
├── drizzle.config.ts
└── .env.example

---

Database Schema

Users

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Assets (Net Worth)

CREATE TABLE assets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  type VARCHAR(50) NOT NULL CHECK (type IN ('cash', 'investment', 'property', 'vehicle', 'other')),
  value DECIMAL(15, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Liabilities (Net Worth)

CREATE TABLE liabilities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  type VARCHAR(50) NOT NULL CHECK (type IN ('credit_card', 'loan', 'mortgage', 'other')),
  balance DECIMAL(15, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Net Worth Snapshots

CREATE TABLE net_worth_snapshots (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  date DATE NOT NULL,
  total_assets DECIMAL(15, 2) NOT NULL,
  total_liabilities DECIMAL(15, 2) NOT NULL,
  net_worth DECIMAL(15, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

Debt Categories

CREATE TABLE debt_categories (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  color VARCHAR(20) DEFAULT '#6b7280',
  is_default BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW()
);

Debt Accounts

CREATE TABLE debt_accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  category_id UUID REFERENCES debt_categories(id) ON DELETE SET NULL,
  name VARCHAR(255) NOT NULL,
  institution VARCHAR(255),
  account_number VARCHAR(4),  -- Last 4 digits only
  original_balance DECIMAL(15, 2) NOT NULL,
  current_balance DECIMAL(15, 2) NOT NULL,
  interest_rate DECIMAL(5, 2),
  minimum_payment DECIMAL(10, 2),
  due_day INTEGER CHECK (due_day >= 1 AND due_day <= 31),
  notes TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Debt Payments

CREATE TABLE debt_payments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id UUID REFERENCES debt_accounts(id) ON DELETE CASCADE,
  amount DECIMAL(10, 2) NOT NULL,
  date DATE NOT NULL,
  note TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

Clients (Invoicing)

CREATE TABLE clients (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255),
  phone VARCHAR(50),
  company VARCHAR(255),
  address TEXT,
  notes TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

Invoices

CREATE TABLE invoices (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  client_id UUID REFERENCES clients(id) ON DELETE SET NULL,
  invoice_number VARCHAR(50) NOT NULL,
  status VARCHAR(20) NOT NULL CHECK (status IN ('draft', 'sent', 'paid', 'overdue', 'cancelled')),
  issue_date DATE NOT NULL,
  due_date DATE NOT NULL,
  subtotal DECIMAL(15, 2) NOT NULL,
  tax DECIMAL(15, 2) DEFAULT 0,
  total DECIMAL(15, 2) NOT NULL,
  notes TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Invoice Line Items

CREATE TABLE invoice_line_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  invoice_id UUID REFERENCES invoices(id) ON DELETE CASCADE,
  description TEXT NOT NULL,
  quantity DECIMAL(10, 2) NOT NULL,
  unit_price DECIMAL(15, 2) NOT NULL,
  total DECIMAL(15, 2) NOT NULL
);

Income Sources (Cashflow)

CREATE TABLE income_sources (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  amount DECIMAL(15, 2) NOT NULL,
  frequency VARCHAR(20) NOT NULL CHECK (frequency IN ('weekly', 'biweekly', 'monthly', 'quarterly', 'yearly', 'once')),
  category VARCHAR(100),
  next_date DATE,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT NOW()
);

Expenses (Cashflow)

CREATE TABLE expenses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  amount DECIMAL(15, 2) NOT NULL,
  frequency VARCHAR(20) NOT NULL CHECK (frequency IN ('weekly', 'biweekly', 'monthly', 'quarterly', 'yearly', 'once')),
  category VARCHAR(100),
  next_date DATE,
  is_active BOOLEAN DEFAULT TRUE,
  is_essential BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW()
);

Transactions (Cashflow)

CREATE TABLE transactions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  type VARCHAR(10) NOT NULL CHECK (type IN ('income', 'expense')),
  name VARCHAR(255) NOT NULL,
  amount DECIMAL(15, 2) NOT NULL,
  category VARCHAR(100),
  date DATE NOT NULL,
  note TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

---

API Endpoints

All API routes are prefixed with /api to avoid conflicts with frontend routes.

Authentication

Method	Endpoint	Description
POST	/api/auth/register	Register new user
POST	/api/auth/login	Login, returns JWT + refresh token
POST	/api/auth/refresh	Refresh access token
POST	/api/auth/logout	Invalidate refresh token
GET	/api/auth/me	Get current user profile

Assets

Method	Endpoint	Description
GET	/api/assets	List all assets for user
POST	/api/assets	Create new asset
GET	/api/assets/:id	Get asset by ID
PUT	/api/assets/:id	Update asset
DELETE	/api/assets/:id	Delete asset

Liabilities

Method	Endpoint	Description
GET	/api/liabilities	List all liabilities for user
POST	/api/liabilities	Create new liability
GET	/api/liabilities/:id	Get liability by ID
PUT	/api/liabilities/:id	Update liability
DELETE	/api/liabilities/:id	Delete liability

Net Worth Snapshots

Method	Endpoint	Description
GET	/api/net-worth/snapshots	List snapshots (with date range filter)
POST	/api/net-worth/snapshots	Create snapshot (auto-calculates totals)
GET	/api/net-worth/current	Get current net worth calculation

Debt Categories

Method	Endpoint	Description
GET	/api/debts/categories	List all categories
POST	/api/debts/categories	Create category
PUT	/api/debts/categories/:id	Update category
DELETE	/api/debts/categories/:id	Delete category (moves accounts to "Other")

Debt Accounts

Method	Endpoint	Description
GET	/api/debts/accounts	List all debt accounts
POST	/api/debts/accounts	Create debt account
GET	/api/debts/accounts/:id	Get account with payment history
PUT	/api/debts/accounts/:id	Update account
DELETE	/api/debts/accounts/:id	Delete account

Debt Payments

Method	Endpoint	Description
GET	/api/debts/accounts/:id/payments	List payments for account
POST	/api/debts/accounts/:id/payments	Record payment (updates balance)
DELETE	/api/debts/payments/:id	Delete payment (restores balance)

Clients

Method	Endpoint	Description
GET	/api/clients	List all clients
POST	/api/clients	Create client
GET	/api/clients/:id	Get client with invoice stats
PUT	/api/clients/:id	Update client
DELETE	/api/clients/:id	Delete client

Invoices

Method	Endpoint	Description
GET	/api/invoices	List invoices (filterable by status, client)
POST	/api/invoices	Create invoice with line items
GET	/api/invoices/:id	Get invoice with line items
PUT	/api/invoices/:id	Update invoice
PATCH	/api/invoices/:id/status	Update invoice status only
DELETE	/api/invoices/:id	Delete invoice

Income Sources

Method	Endpoint	Description
GET	/api/cashflow/income	List income sources
POST	/api/cashflow/income	Create income source
PUT	/api/cashflow/income/:id	Update income source
DELETE	/api/cashflow/income/:id	Delete income source

Expenses

Method	Endpoint	Description
GET	/api/cashflow/expenses	List expenses
POST	/api/cashflow/expenses	Create expense
PUT	/api/cashflow/expenses/:id	Update expense
DELETE	/api/cashflow/expenses/:id	Delete expense

Transactions

Method	Endpoint	Description
GET	/api/cashflow/transactions	List transactions (with date range, pagination)
POST	/api/cashflow/transactions	Create transaction
DELETE	/api/cashflow/transactions/:id	Delete transaction

Dashboard / Summary

Method	Endpoint	Description
GET	/api/dashboard/summary	Get aggregated summary stats

Health Check

Method	Endpoint	Description
GET	/api/health	Health check (no auth required)

---

Authentication Implementation

1. Registration:

   • Validate email uniqueness
   • Hash password with bcrypt (cost factor 12)
   • Create default debt categories for new users
   • Return JWT access token (15min expiry) + refresh token (7 days)

2. Login:

   • Validate credentials
   • Return JWT + refresh token
   • Store refresh token hash in DB or Redis

3. JWT Payload:

   interface JWTPayload {
     sub: string; // user ID
     email: string;
     iat: number;
     exp: number;
   }
   

4. Protected Routes:

   • Add preHandler hook to verify JWT
   • Extract user ID from token for all queries

---

Request/Response Types

Use consistent response format:

// Success
{
  success: true,
  data: T
}

// Error
{
  success: false,
  error: {
    code: string,
    message: string,
    details?: Record<string, string[]>
  }
}

// Paginated
{
  success: true,
  data: T[],
  meta: {
    page: number,
    limit: number,
    total: number,
    totalPages: number
  }
}

---

Validation Rules

• Email: Valid email format, max 255 chars
• Password: Min 6 chars
• Monetary values: Max 2 decimal places, positive numbers
• Dates: ISO 8601 format (YYYY-MM-DD)
• Interest rates: 0-100 range, max 2 decimal places
• Due day: 1-31 range

---

Business Logic

1. Debt Payments:

   • Recording a payment should automatically update current_balance
   • Deleting a payment should restore the balance

2. Net Worth Snapshots:

   • Auto-calculate totals from current assets/liabilities
   • Allow manual override if needed

3. Invoice Numbers:

   • Auto-generate if not provided: INV-{YEAR}-{SEQ}
   • Sequence per user

4. Overdue Invoices:

   • Consider adding a scheduled job to mark invoices as overdue

---

Environment Variables

DATABASE_URL=postgresql://user:pass@localhost:5432/wealth
JWT_SECRET=your-secret-key
JWT_REFRESH_SECRET=your-refresh-secret
PORT=3000
NODE_ENV=development

---

Additional Requirements

1. CORS: Configure for frontend origin
2. Rate Limiting: Add to auth endpoints
3. Logging: Use Fastify's built-in pino logger
4. Health Check: GET /health endpoint
5. API Docs: Consider adding Swagger/OpenAPI via @fastify/swagger

---

Getting Started

1. Initialize project with bun init or npm init
2. Install dependencies:

   bun add fastify @fastify/cors @fastify/jwt @fastify/static drizzle-orm postgres zod bcrypt
   bun add -D typescript @types/node @types/bcrypt drizzle-kit tsx
   

3. Set up database schema and run migrations
4. Implement auth module first
5. Add remaining modules
6. Test all endpoints

---

Serving the Frontend

The backend should serve the frontend static files in production. Use @fastify/static:

// src/app.ts
import fastifyStatic from '@fastify/static';
import path from 'path';

// Serve frontend static files in production
if (process.env.NODE_ENV === 'production') {
  app.register(fastifyStatic, {
    root: path.join(__dirname, '../public'),
    prefix: '/'
  });

  // SPA fallback - serve index.html for all non-API routes
  app.setNotFoundHandler((request, reply) => {
    if (request.url.startsWith('/api')) {
      reply.status(404).send({success: false, error: {code: 'NOT_FOUND', message: 'Route not found'}});
    } else {
      reply.sendFile('index.html');
    }
  });
}

// Register all API routes with /api prefix
app.register(authRoutes, {prefix: '/api/auth'});
app.register(assetsRoutes, {prefix: '/api/assets'});
app.register(liabilitiesRoutes, {prefix: '/api/liabilities'});
// ... etc

Important: All API endpoints are prefixed with /api to avoid conflicts with frontend SPA routes. The frontend React Router handles /, /cashflow, /debts, etc., while the API handles /api/*.

---

Docker Build

Create a multi-stage Dockerfile that builds both frontend and backend:

# Dockerfile
FROM oven/bun:1 AS frontend-builder

WORKDIR /app/frontend
COPY frontend-web/package.json frontend-web/bun.lock ./
RUN bun install --frozen-lockfile

COPY frontend-web/ ./
RUN bun run build

# ---

FROM oven/bun:1 AS backend-builder

WORKDIR /app/backend
COPY backend-api/package.json backend-api/bun.lock ./
RUN bun install --frozen-lockfile

COPY backend-api/ ./
RUN bun run build

# ---

FROM oven/bun:1-slim AS production

WORKDIR /app

# Copy backend build
COPY --from=backend-builder /app/backend/dist ./dist
COPY --from=backend-builder /app/backend/package.json ./
COPY --from=backend-builder /app/backend/node_modules ./node_modules

# Copy frontend build into public folder
COPY --from=frontend-builder /app/frontend/dist ./public

ENV NODE_ENV=production
ENV PORT=3000

EXPOSE 3000

CMD ["bun", "run", "dist/index.js"]

---

Project Structure (Updated)

personal-finances/
├── frontend-web/           # React frontend (existing)
├── backend-api/            # Fastify backend (to create)
│   ├── src/
│   │   ├── index.ts
│   │   ├── app.ts
│   │   ├── config/
│   │   ├── db/
│   │   ├── modules/
│   │   │   ├── auth/
│   │   │   ├── assets/
│   │   │   ├── liabilities/
│   │   │   ├── net-worth/
│   │   │   ├── debts/
│   │   │   ├── invoices/
│   │   │   ├── clients/
│   │   │   └── cashflow/
│   │   ├── middleware/
│   │   └── utils/
│   ├── package.json
│   ├── tsconfig.json
│   └── drizzle.config.ts
├── Dockerfile
├── docker-compose.yml
└── BACKEND_PROMPT.md

---

Docker Compose (Development)

# docker-compose.yml
version: '3.8'

services:
  db:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: wealth
      POSTGRES_PASSWORD: wealth_dev
      POSTGRES_DB: wealth
    ports:
      - '5432:5432'
    volumes:
      - postgres_data:/var/lib/postgresql/data

  api:
    build: .
    ports:
      - '3000:3000'
    environment:
      DATABASE_URL: postgresql://wealth:wealth_dev@db:5432/wealth
      JWT_SECRET: dev-secret-change-in-production
      JWT_REFRESH_SECRET: dev-refresh-secret-change-in-production
      NODE_ENV: production
    depends_on:
      - db

volumes:
  postgres_data:

---

Build Scripts

Add these scripts to backend-api/package.json:

{
  "scripts": {
    "dev": "tsx watch src/index.ts",
    "build": "tsc",
    "start": "node dist/index.js",
    "db:generate": "drizzle-kit generate",
    "db:migrate": "drizzle-kit migrate",
    "db:studio": "drizzle-kit studio"
  }
}

Root-level scripts for building everything (add to root package.json):

{
  "scripts": {
    "build:frontend": "cd frontend-web && bun run build",
    "build:backend": "cd backend-api && bun run build",
    "build": "bun run build:frontend && bun run build:backend",
    "docker:build": "docker build -t wealth-app .",
    "docker:up": "docker-compose up -d",
    "docker:down": "docker-compose down"
  }
}

---

Frontend Integration

The frontend expects these TypeScript interfaces (match these in your responses):

interface User {
  id: string;
  email: string;
  name: string;
}

interface Asset {
  id: string;
  name: string;
  type: 'cash' | 'investment' | 'property' | 'vehicle' | 'other';
  value: number;
  updatedAt: string;
}

interface Liability {
  id: string;
  name: string;
  type: 'credit_card' | 'loan' | 'mortgage' | 'other';
  balance: number;
  updatedAt: string;
}

interface DebtCategory {
  id: string;
  name: string;
  color: string;
  createdAt: string;
}

interface DebtAccount {
  id: string;
  name: string;
  categoryId: string;
  institution: string;
  accountNumber?: string;
  originalBalance: number;
  currentBalance: number;
  interestRate: number;
  minimumPayment: number;
  dueDay: number;
  notes?: string;
  createdAt: string;
  updatedAt: string;
}

interface Client {
  id: string;
  name: string;
  email: string;
  phone?: string;
  company?: string;
  address?: string;
  notes?: string;
  createdAt: string;
}

interface Invoice {
  id: string;
  invoiceNumber: string;
  clientId: string;
  status: 'draft' | 'sent' | 'paid' | 'overdue' | 'cancelled';
  issueDate: string;
  dueDate: string;
  lineItems: InvoiceLineItem[];
  subtotal: number;
  tax: number;
  total: number;
  notes?: string;
  createdAt: string;
  updatedAt: string;
}

interface IncomeSource {
  id: string;
  name: string;
  amount: number;
  frequency: 'weekly' | 'biweekly' | 'monthly' | 'quarterly' | 'yearly' | 'once';
  category: string;
  nextDate: string;
  isActive: boolean;
  createdAt: string;
}

interface Expense {
  id: string;
  name: string;
  amount: number;
  frequency: 'weekly' | 'biweekly' | 'monthly' | 'quarterly' | 'yearly' | 'once';
  category: string;
  nextDate: string;
  isActive: boolean;
  isEssential: boolean;
  createdAt: string;
}

interface Transaction {
  id: string;
  type: 'income' | 'expense';
  name: string;
  amount: number;
  category: string;
  date: string;
  note?: string;
}