How to Build a Finance Tracker with Replit

1// Prompt to type into Replit Agent:
2// Build a personal finance tracker with Express and PostgreSQL using Drizzle ORM.
3// Create these tables in shared/schema.ts:
4// - accounts: id serial pk, user_id text not null, name text not null,
5//   type text not null (checking/savings/credit_card/cash/investment),
6//   balance integer not null default 0 (in cents),
7//   currency text default 'USD', is_active boolean default true, created_at timestamp
8// - categories: id serial pk, user_id text not null, name text not null,
9//   type text not null (income/expense), icon text, color text,
10//   position integer default 0
11// - transactions: id serial pk, user_id text not null,
12//   account_id integer references accounts not null,
13//   category_id integer references categories,
14//   type text not null (income/expense/transfer),
15//   amount integer not null (always positive, in cents),
16//   description text, date date not null,
17//   is_recurring boolean default false,
18//   recurrence_rule text (none/daily/weekly/biweekly/monthly),
19//   notes text, created_at timestamp
20// - budgets: id serial pk, user_id text not null,
21//   category_id integer references categories not null,
22//   amount integer not null (monthly limit in cents),
23//   month date not null (first day of month),
24//   UNIQUE on (user_id, category_id, month)
25// Create a PostgreSQL trigger on transactions that updates accounts.balance:
26//   On INSERT: if type='expense' subtract amount, if type='income' add amount
27//   On DELETE: reverse the above
28//   On UPDATE: reverse the old amount, apply the new amount
29// Set up Replit Auth. Bind server to 0.0.0.0.

1const { db } = require('../db');
2const { transactions, accounts, categories } = require('../../shared/schema');
3const { eq, and, gte, lte, desc, sql } = require('drizzle-orm');
4
5router.get('/api/transactions', async (req, res) => {
6  const { startDate, endDate, accountId, categoryId, type, q, page = 1, limit = 50 } = req.query;
7  const offset = (Number(page) - 1) * Number(limit);
8
9  const conditions = [eq(transactions.userId, req.user.id)];
10  if (startDate) conditions.push(gte(transactions.date, startDate));
11  if (endDate) conditions.push(lte(transactions.date, endDate));
12  if (accountId) conditions.push(eq(transactions.accountId, Number(accountId)));
13  if (categoryId) conditions.push(eq(transactions.categoryId, Number(categoryId)));
14  if (type) conditions.push(eq(transactions.type, type));
15  if (q) conditions.push(sql`transactions.description ILIKE ${'%' + q + '%'}`);
16
17  const [result, countResult] = await Promise.all([
18    db.select({
19      id: transactions.id,
20      type: transactions.type,
21      amount: transactions.amount,
22      description: transactions.description,
23      date: transactions.date,
24      accountName: accounts.name,
25      categoryName: categories.name,
26      categoryColor: categories.color,
27      categoryIcon: categories.icon
28    }).from(transactions)
29      .leftJoin(accounts, eq(transactions.accountId, accounts.id))
30      .leftJoin(categories, eq(transactions.categoryId, categories.id))
31      .where(and(...conditions))
32      .orderBy(desc(transactions.date))
33      .limit(Number(limit))
34      .offset(offset),
35
36    db.select({ count: sql`COUNT(*)` }).from(transactions).where(and(...conditions))
37  ]);
38
39  res.json({ transactions: result, total: Number(countResult[0].count) });
40});
41
42router.post('/api/transactions', async (req, res) => {
43  const { accountId, categoryId, type, amount, description, date, isRecurring, recurrenceRule, notes } = req.body;
44
45  // Validate account belongs to user
46  const account = await db.query.accounts.findFirst({
47    where: and(eq(accounts.id, Number(accountId)), eq(accounts.userId, req.user.id))
48  });
49  if (!account) return res.status(404).json({ error: 'Account not found' });
50
51  const [tx] = await db.insert(transactions).values({
52    userId: req.user.id, accountId: Number(accountId), categoryId: categoryId ? Number(categoryId) : null,
53    type, amount: Math.abs(Number(amount)), description, date, isRecurring, recurrenceRule, notes
54  }).returning();
55
56  // The trigger automatically updates accounts.balance — no manual update needed
57  res.json(tx);
58});
59
60router.delete('/api/transactions/:id', async (req, res) => {
61  const tx = await db.query.transactions.findFirst({
62    where: and(eq(transactions.id, Number(req.params.id)), eq(transactions.userId, req.user.id))
63  });
64  if (!tx) return res.status(404).json({ error: 'Transaction not found' });
65
66  await db.delete(transactions).where(eq(transactions.id, tx.id));
67  // Trigger reverses the balance automatically
68  res.json({ success: true });
69});

1// Prompt to type into Replit Agent:
2// Add budget and reporting routes to server/routes/reports.js:
3//
4// GET /api/budgets/:month — budget comparison for a month (format: YYYY-MM-01)
5//   Returns per-category: budget_amount, actual_spent, percentage_used
6//   Query:
7//   SELECT c.id, c.name, c.color, c.icon,
8//          b.amount AS budget_amount,
9//          COALESCE(SUM(t.amount), 0) AS actual_spent,
10//          CASE WHEN b.amount > 0 THEN ROUND(COALESCE(SUM(t.amount), 0) * 100.0 / b.amount)
11//               ELSE 0 END AS percentage_used
12//   FROM budgets b
13//   JOIN categories c ON c.id = b.category_id
14//   LEFT JOIN transactions t ON t.category_id = c.id
15//     AND t.type = 'expense'
16//     AND t.date >= :month AND t.date < :month + interval '1 month'
17//   WHERE b.user_id = req.user.id AND b.month = :month
18//   GROUP BY c.id, c.name, c.color, c.icon, b.amount
19//
20// PUT /api/budgets/:month — set budgets for a month
21//   Body: array of {categoryId, amount}
22//   Use INSERT ... ON CONFLICT (user_id, category_id, month) DO UPDATE SET amount
23//
24// GET /api/reports/monthly — income vs expense for last 12 months
25//   Returns array of {month, income, expenses, net}
26//   Use DATE_TRUNC('month', date) to group by month
27//
28// GET /api/reports/categories — spending by category for a date range
29//   Returns array of {category_name, category_color, total_amount, transaction_count}
30//
31// GET /api/dashboard — summary endpoint:
32//   Returns: accounts (list with balances), net_worth (sum of all account balances),
33//   current_month_income, current_month_expenses, budget_alerts (categories over 90%),
34//   recent_transactions (last 10)

1// Prompt to type into Replit Agent:
2// Build these React components:
3//
4// 1. Dashboard at client/src/pages/Dashboard.jsx:
5//    - On mount: fetch GET /api/dashboard
6//    - Net worth card: large number showing sum of all account balances (in dollars)
7//    - Account cards: one per account showing name, type badge, current balance
8//      Color: positive balance = green text, negative = red (for credit cards: invert)
9//    - Income vs Expenses section: two numbers for current month
10//      'Saved this month' = income - expenses in green or red
11//    - Budget progress bars:
12//      Each category: name, progress bar (width = percentage_used%),
13//      actual/budget amounts in dollars
14//      Colors: green if < 80%, yellow if 80-100%, red if > 100%
15//    - Recent transactions list: last 10, each showing:
16//      category icon, description, account name, date, amount (red for expense, green for income)
17//
18// 2. Add Transaction modal:
19//    - Amount input with $ prefix (number, required)
20//    - Type selector: Income / Expense / Transfer (button group)
21//    - Account selector (dropdown from GET /api/accounts)
22//    - Category selector (dropdown filtered by selected type)
23//    - Description input
24//    - Date picker (default today)
25//    - Notes textarea (optional)
26//    - Submit → POST /api/transactions, close modal, refresh dashboard
27//
28// 3. Transactions page at client/src/pages/Transactions.jsx:
29//    - Data table with filters: date range picker, account dropdown, category dropdown, search bar
30//    - Rows: date, description, category (with colored icon), account, amount (colored)
31//    - Click row to edit, delete button per row

1// Prompt to type into Replit Agent:
2// Create scripts/seed.js that inserts default categories for a new user.
3// Income categories:
4// [{name: 'Salary', type: 'income', icon: '💼', color: '#22c55e'},
5//  {name: 'Freelance', type: 'income', icon: '💻', color: '#16a34a'},
6//  {name: 'Investments', type: 'income', icon: '📈', color: '#15803d'},
7//  {name: 'Other Income', type: 'income', icon: '➕', color: '#166534'}]
8// Expense categories:
9// [{name: 'Food & Dining', type: 'expense', icon: '🍔', color: '#ef4444'},
10//  {name: 'Transportation', type: 'expense', icon: '🚗', color: '#f97316'},
11//  {name: 'Shopping', type: 'expense', icon: '🛍️', color: '#a855f7'},
12//  {name: 'Bills & Utilities', type: 'expense', icon: '📄', color: '#6366f1'},
13//  {name: 'Entertainment', type: 'expense', icon: '🎬', color: '#ec4899'},
14//  {name: 'Health', type: 'expense', icon: '🏥', color: '#14b8a6'},
15//  {name: 'Housing', type: 'expense', icon: '🏠', color: '#8b5cf6'},
16//  {name: 'Other', type: 'expense', icon: '📦', color: '#6b7280'}]
17// Accept userId as command line arg: node scripts/seed.js USER_ID
18// INSERT with ON CONFLICT DO NOTHING so re-running is safe
19//
20// Then add SESSION_SECRET to Replit Secrets (lock icon)
21// Ensure server binds to 0.0.0.0
22// Deploy: Deploy → Autoscale