547 lines
17 KiB
JavaScript
547 lines
17 KiB
JavaScript
import { writeFileSync } from "fs";
|
|
|
|
// ─── transactions.service.ts ─────────────────────────────────────────────────
|
|
writeFileSync("src/transactions/transactions.service.ts", `import { BadRequestException, Injectable } from "@nestjs/common";
|
|
import * as crypto from "crypto";
|
|
import { parse } from "csv-parse/sync";
|
|
import { Prisma } from "@prisma/client";
|
|
import { PrismaService } from "../prisma/prisma.service";
|
|
import { PlaidService } from "../plaid/plaid.service";
|
|
import { UpdateDerivedDto } from "./dto/update-derived.dto";
|
|
import { CreateManualTransactionDto } from "./dto/create-manual-transaction.dto";
|
|
|
|
const MAX_PAGE_SIZE = 100;
|
|
|
|
// ─── Bank CSV format auto-detection ──────────────────────────────────────────
|
|
type ParsedRow = { date: string; description: string; amount: number };
|
|
|
|
function detectAndParse(buffer: Buffer): ParsedRow[] {
|
|
const text = buffer.toString("utf8").trim();
|
|
const rows: Record<string, string>[] = parse(text, {
|
|
columns: true,
|
|
skip_empty_lines: true,
|
|
trim: true,
|
|
bom: true,
|
|
});
|
|
|
|
if (!rows.length) return [];
|
|
const headers = Object.keys(rows[0]).map((h) => h.toLowerCase());
|
|
|
|
// Chase format: Transaction Date, Description, Amount
|
|
if (headers.includes("transaction date") && headers.includes("description") && headers.includes("amount")) {
|
|
return rows.map((r) => ({
|
|
date: r["Transaction Date"] ?? r["transaction date"],
|
|
description: r["Description"] ?? r["description"],
|
|
amount: parseFloat(r["Amount"] ?? r["amount"] ?? "0"),
|
|
})).filter((r) => r.date && r.description);
|
|
}
|
|
|
|
// Bank of America format: Date, Description, Amount, Running Bal.
|
|
if (headers.includes("date") && headers.includes("description") && headers.includes("amount") && headers.some((h) => h.includes("running"))) {
|
|
return rows.map((r) => ({
|
|
date: r["Date"] ?? r["date"],
|
|
description: r["Description"] ?? r["description"],
|
|
amount: parseFloat((r["Amount"] ?? r["amount"] ?? "0").replace(/,/g, "")),
|
|
})).filter((r) => r.date && r.description);
|
|
}
|
|
|
|
// Wells Fargo format: 5 unnamed columns — Date, Amount, *, *, Description
|
|
if (headers.length >= 5 && (headers[0] === "" || /^[0-9]/.test(rows[0][Object.keys(rows[0])[0]] ?? ""))) {
|
|
const keys = Object.keys(rows[0]);
|
|
return rows.map((r) => ({
|
|
date: r[keys[0]],
|
|
description: r[keys[4]] ?? r[keys[3]],
|
|
amount: parseFloat((r[keys[1]] ?? "0").replace(/,/g, "")),
|
|
})).filter((r) => r.date && r.description);
|
|
}
|
|
|
|
// Generic: look for date, amount, description columns
|
|
const dateKey = Object.keys(rows[0]).find((k) => /date/i.test(k));
|
|
const amountKey = Object.keys(rows[0]).find((k) => /amount/i.test(k));
|
|
const descKey = Object.keys(rows[0]).find((k) => /desc|memo|narr|payee/i.test(k));
|
|
|
|
if (dateKey && amountKey && descKey) {
|
|
return rows.map((r) => ({
|
|
date: r[dateKey],
|
|
description: r[descKey],
|
|
amount: parseFloat((r[amountKey] ?? "0").replace(/[^0-9.-]/g, "")),
|
|
})).filter((r) => r.date && r.description);
|
|
}
|
|
|
|
throw new BadRequestException("Unrecognized CSV format. Supported: Chase, Bank of America, Wells Fargo, or generic (date/amount/description columns).");
|
|
}
|
|
|
|
@Injectable()
|
|
export class TransactionsService {
|
|
constructor(
|
|
private readonly prisma: PrismaService,
|
|
private readonly plaidService: PlaidService,
|
|
) {}
|
|
|
|
async list(
|
|
userId: string,
|
|
filters: {
|
|
startDate?: string;
|
|
endDate?: string;
|
|
accountId?: string;
|
|
minAmount?: string;
|
|
maxAmount?: string;
|
|
category?: string;
|
|
source?: string;
|
|
search?: string;
|
|
includeHidden?: string;
|
|
page?: number;
|
|
limit?: number;
|
|
},
|
|
) {
|
|
const end = filters.endDate ? new Date(filters.endDate) : new Date();
|
|
const start = filters.startDate
|
|
? new Date(filters.startDate)
|
|
: new Date(new Date().setDate(end.getDate() - 30));
|
|
|
|
const where: Prisma.TransactionRawWhereInput = {
|
|
account: { userId },
|
|
date: { gte: start, lte: end },
|
|
};
|
|
|
|
if (filters.minAmount || filters.maxAmount) {
|
|
const min = filters.minAmount ? parseFloat(filters.minAmount) : undefined;
|
|
const max = filters.maxAmount ? parseFloat(filters.maxAmount) : undefined;
|
|
where.amount = { gte: min, lte: max };
|
|
}
|
|
|
|
if (filters.category) {
|
|
where.derived = { is: { userCategory: { contains: filters.category, mode: "insensitive" } } };
|
|
}
|
|
|
|
if (filters.source) {
|
|
where.source = { contains: filters.source, mode: "insensitive" };
|
|
}
|
|
|
|
if (filters.search) {
|
|
where.description = { contains: filters.search, mode: "insensitive" };
|
|
}
|
|
|
|
if (filters.accountId) {
|
|
where.accountId = filters.accountId;
|
|
}
|
|
|
|
if (filters.includeHidden !== "true") {
|
|
where.OR = [{ derived: null }, { derived: { isHidden: false } }];
|
|
}
|
|
|
|
const take = Math.min(filters.limit ?? 50, MAX_PAGE_SIZE);
|
|
const skip = ((filters.page ?? 1) - 1) * take;
|
|
|
|
const [rows, total] = await Promise.all([
|
|
this.prisma.transactionRaw.findMany({
|
|
where,
|
|
include: { derived: true },
|
|
orderBy: { date: "desc" },
|
|
take,
|
|
skip,
|
|
}),
|
|
this.prisma.transactionRaw.count({ where }),
|
|
]);
|
|
|
|
const transactions = rows.map((row) => ({
|
|
id: row.id,
|
|
name: row.description,
|
|
amount: Number(row.amount).toFixed(2),
|
|
category: row.derived?.userCategory ?? "Uncategorized",
|
|
note: row.derived?.userNotes ?? "",
|
|
status: row.derived?.modifiedBy ?? "raw",
|
|
hidden: row.derived?.isHidden ?? false,
|
|
date: row.date.toISOString().slice(0, 10),
|
|
source: row.source,
|
|
accountId: row.accountId,
|
|
}));
|
|
|
|
return { transactions, total, page: filters.page ?? 1, limit: take };
|
|
}
|
|
|
|
async importCsv(userId: string, file: Express.Multer.File) {
|
|
if (!file?.buffer) {
|
|
throw new BadRequestException("No file uploaded.");
|
|
}
|
|
if (!file.originalname.toLowerCase().endsWith(".csv")) {
|
|
throw new BadRequestException("File must be a CSV.");
|
|
}
|
|
|
|
const rows = detectAndParse(file.buffer);
|
|
if (!rows.length) {
|
|
throw new BadRequestException("CSV file is empty or could not be parsed.");
|
|
}
|
|
|
|
// Find or create a manual import account for this user
|
|
let account = await this.prisma.account.findFirst({
|
|
where: { userId, institutionName: "CSV Import", plaidAccessToken: null },
|
|
});
|
|
if (!account) {
|
|
account = await this.prisma.account.create({
|
|
data: {
|
|
userId,
|
|
institutionName: "CSV Import",
|
|
accountType: "checking",
|
|
isActive: true,
|
|
},
|
|
});
|
|
}
|
|
|
|
let imported = 0;
|
|
let skipped = 0;
|
|
|
|
for (const row of rows) {
|
|
const dateObj = new Date(row.date);
|
|
if (isNaN(dateObj.getTime())) {
|
|
skipped++;
|
|
continue;
|
|
}
|
|
const bankTransactionId = \`csv_\${crypto.createHash("sha256")
|
|
.update(\`\${userId}:\${row.date}:\${row.description}:\${row.amount}\`)
|
|
.digest("hex")
|
|
.slice(0, 16)}\`;
|
|
|
|
try {
|
|
await this.prisma.transactionRaw.upsert({
|
|
where: { bankTransactionId },
|
|
update: {},
|
|
create: {
|
|
accountId: account.id,
|
|
bankTransactionId,
|
|
date: dateObj,
|
|
amount: row.amount,
|
|
description: row.description,
|
|
rawPayload: row as unknown as Prisma.InputJsonValue,
|
|
ingestedAt: new Date(),
|
|
source: "csv",
|
|
},
|
|
});
|
|
imported++;
|
|
} catch {
|
|
skipped++;
|
|
}
|
|
}
|
|
|
|
return { imported, skipped, total: rows.length };
|
|
}
|
|
|
|
async createManualTransaction(userId: string, payload: CreateManualTransactionDto) {
|
|
const account = payload.accountId
|
|
? await this.prisma.account.findFirst({ where: { id: payload.accountId, userId } })
|
|
: await this.prisma.account.findFirst({ where: { userId } });
|
|
|
|
if (!account) {
|
|
throw new BadRequestException("No account found for user.");
|
|
}
|
|
|
|
const id = crypto.randomUUID();
|
|
const raw = await this.prisma.transactionRaw.create({
|
|
data: {
|
|
accountId: account.id,
|
|
bankTransactionId: \`manual_\${id}\`,
|
|
date: new Date(payload.date),
|
|
amount: payload.amount,
|
|
description: payload.description,
|
|
rawPayload: payload as unknown as Prisma.InputJsonValue,
|
|
ingestedAt: new Date(),
|
|
source: "manual",
|
|
},
|
|
});
|
|
|
|
if (payload.category || payload.note || payload.hidden) {
|
|
await this.prisma.transactionDerived.create({
|
|
data: {
|
|
rawTransactionId: raw.id,
|
|
userCategory: payload.category ?? null,
|
|
userNotes: payload.note ?? null,
|
|
isHidden: payload.hidden ?? false,
|
|
modifiedAt: new Date(),
|
|
modifiedBy: "user",
|
|
},
|
|
});
|
|
}
|
|
|
|
return { id: raw.id };
|
|
}
|
|
|
|
async updateDerived(userId: string, id: string, payload: UpdateDerivedDto) {
|
|
// Ensure the transaction belongs to the user
|
|
const tx = await this.prisma.transactionRaw.findFirst({
|
|
where: { id, account: { userId } },
|
|
});
|
|
if (!tx) throw new BadRequestException("Transaction not found.");
|
|
|
|
return this.prisma.transactionDerived.upsert({
|
|
where: { rawTransactionId: id },
|
|
update: {
|
|
userCategory: payload.userCategory,
|
|
userNotes: payload.userNotes,
|
|
isHidden: payload.isHidden ?? false,
|
|
modifiedAt: new Date(),
|
|
modifiedBy: "user",
|
|
},
|
|
create: {
|
|
rawTransactionId: id,
|
|
userCategory: payload.userCategory,
|
|
userNotes: payload.userNotes,
|
|
isHidden: payload.isHidden ?? false,
|
|
modifiedAt: new Date(),
|
|
modifiedBy: "user",
|
|
},
|
|
});
|
|
}
|
|
|
|
async sync(userId: string, startDate: string, endDate: string) {
|
|
return this.plaidService.syncTransactionsForUser(userId, startDate, endDate);
|
|
}
|
|
|
|
async summary(userId: string, startDate: string, endDate: string) {
|
|
const rows = await this.prisma.transactionRaw.findMany({
|
|
where: {
|
|
account: { userId },
|
|
date: { gte: new Date(startDate), lte: new Date(endDate) },
|
|
},
|
|
});
|
|
|
|
const total = rows.reduce((sum, row) => sum + Number(row.amount), 0);
|
|
const income = rows.reduce(
|
|
(sum, row) => sum + (Number(row.amount) < 0 ? Math.abs(Number(row.amount)) : 0),
|
|
0,
|
|
);
|
|
const expense = rows.reduce(
|
|
(sum, row) => sum + (Number(row.amount) > 0 ? Number(row.amount) : 0),
|
|
0,
|
|
);
|
|
return {
|
|
total: total.toFixed(2),
|
|
count: rows.length,
|
|
income: income.toFixed(2),
|
|
expense: expense.toFixed(2),
|
|
net: (income - expense).toFixed(2),
|
|
};
|
|
}
|
|
|
|
async cashflow(userId: string, months = 6) {
|
|
const now = new Date();
|
|
const start = new Date(now.getFullYear(), now.getMonth() - (months - 1), 1);
|
|
const rows = await this.prisma.transactionRaw.findMany({
|
|
where: { account: { userId }, date: { gte: start, lte: now } },
|
|
});
|
|
|
|
const buckets = new Map<string, { income: number; expense: number }>();
|
|
for (let i = 0; i < months; i += 1) {
|
|
const date = new Date(now.getFullYear(), now.getMonth() - (months - 1) + i, 1);
|
|
const key = \`\${date.getFullYear()}-\${String(date.getMonth() + 1).padStart(2, "0")}\`;
|
|
buckets.set(key, { income: 0, expense: 0 });
|
|
}
|
|
|
|
for (const row of rows) {
|
|
const key = \`\${row.date.getFullYear()}-\${String(row.date.getMonth() + 1).padStart(2, "0")}\`;
|
|
const bucket = buckets.get(key);
|
|
if (!bucket) continue;
|
|
const amount = Number(row.amount);
|
|
if (amount < 0) bucket.income += Math.abs(amount);
|
|
else bucket.expense += amount;
|
|
}
|
|
|
|
return Array.from(buckets.entries()).map(([month, value]) => ({
|
|
month,
|
|
income: value.income.toFixed(2),
|
|
expense: value.expense.toFixed(2),
|
|
net: (value.income - value.expense).toFixed(2),
|
|
}));
|
|
}
|
|
|
|
async merchantInsights(userId: string, limit = 6) {
|
|
const capped = Math.min(limit, MAX_PAGE_SIZE);
|
|
const rows = await this.prisma.transactionRaw.findMany({
|
|
where: { account: { userId } },
|
|
select: { description: true, amount: true },
|
|
});
|
|
const bucket = new Map<string, { total: number; count: number }>();
|
|
for (const row of rows) {
|
|
const amount = Number(row.amount);
|
|
if (amount <= 0) continue;
|
|
const entry = bucket.get(row.description) ?? { total: 0, count: 0 };
|
|
entry.total += amount;
|
|
entry.count += 1;
|
|
bucket.set(row.description, entry);
|
|
}
|
|
|
|
return Array.from(bucket.entries())
|
|
.sort((a, b) => b[1].total - a[1].total)
|
|
.slice(0, capped)
|
|
.map(([merchant, value]) => ({
|
|
merchant,
|
|
total: value.total.toFixed(2),
|
|
count: value.count,
|
|
}));
|
|
}
|
|
}
|
|
`);
|
|
|
|
// ─── transactions.controller.ts ──────────────────────────────────────────────
|
|
writeFileSync("src/transactions/transactions.controller.ts", `import {
|
|
Body,
|
|
Controller,
|
|
Get,
|
|
Param,
|
|
Patch,
|
|
Post,
|
|
Query,
|
|
UploadedFile,
|
|
UseInterceptors,
|
|
} from "@nestjs/common";
|
|
import { FileInterceptor } from "@nestjs/platform-express";
|
|
import { ok } from "../common/response";
|
|
import { UpdateDerivedDto } from "./dto/update-derived.dto";
|
|
import { CreateManualTransactionDto } from "./dto/create-manual-transaction.dto";
|
|
import { TransactionsService } from "./transactions.service";
|
|
import { CurrentUser } from "../common/decorators/current-user.decorator";
|
|
|
|
@Controller("transactions")
|
|
export class TransactionsController {
|
|
constructor(private readonly transactionsService: TransactionsService) {}
|
|
|
|
@Get()
|
|
async list(
|
|
@CurrentUser() userId: string,
|
|
@Query("start_date") startDate?: string,
|
|
@Query("end_date") endDate?: string,
|
|
@Query("account_id") accountId?: string,
|
|
@Query("min_amount") minAmount?: string,
|
|
@Query("max_amount") maxAmount?: string,
|
|
@Query("category") category?: string,
|
|
@Query("source") source?: string,
|
|
@Query("search") search?: string,
|
|
@Query("include_hidden") includeHidden?: string,
|
|
@Query("page") page = 1,
|
|
@Query("limit") limit = 50,
|
|
) {
|
|
const data = await this.transactionsService.list(userId, {
|
|
startDate,
|
|
endDate,
|
|
accountId,
|
|
minAmount,
|
|
maxAmount,
|
|
category,
|
|
source,
|
|
search,
|
|
includeHidden,
|
|
page: +page,
|
|
limit: +limit,
|
|
});
|
|
return ok(data);
|
|
}
|
|
|
|
@Post("import")
|
|
@UseInterceptors(FileInterceptor("file", { limits: { fileSize: 5 * 1024 * 1024 } }))
|
|
async importCsv(
|
|
@CurrentUser() userId: string,
|
|
@UploadedFile() file: Express.Multer.File,
|
|
) {
|
|
const data = await this.transactionsService.importCsv(userId, file);
|
|
return ok(data);
|
|
}
|
|
|
|
@Post("sync")
|
|
async sync(
|
|
@CurrentUser() userId: string,
|
|
@Body("startDate") startDate?: string,
|
|
@Body("endDate") endDate?: string,
|
|
) {
|
|
const end = endDate ?? new Date().toISOString().slice(0, 10);
|
|
const start =
|
|
startDate ??
|
|
new Date(new Date().setDate(new Date(end).getDate() - 30)).toISOString().slice(0, 10);
|
|
const data = await this.transactionsService.sync(userId, start, end);
|
|
return ok(data);
|
|
}
|
|
|
|
@Post("manual")
|
|
async manual(
|
|
@CurrentUser() userId: string,
|
|
@Body() payload: CreateManualTransactionDto,
|
|
) {
|
|
const data = await this.transactionsService.createManualTransaction(userId, payload);
|
|
return ok(data);
|
|
}
|
|
|
|
@Get("summary")
|
|
async summary(
|
|
@CurrentUser() userId: string,
|
|
@Query("start_date") startDate?: string,
|
|
@Query("end_date") endDate?: string,
|
|
) {
|
|
const end = endDate ?? new Date().toISOString().slice(0, 10);
|
|
const start =
|
|
startDate ??
|
|
new Date(new Date().setDate(new Date(end).getDate() - 30)).toISOString().slice(0, 10);
|
|
const data = await this.transactionsService.summary(userId, start, end);
|
|
return ok(data);
|
|
}
|
|
|
|
@Get("cashflow")
|
|
async cashflow(
|
|
@CurrentUser() userId: string,
|
|
@Query("months") months = 6,
|
|
) {
|
|
const data = await this.transactionsService.cashflow(userId, +months);
|
|
return ok(data);
|
|
}
|
|
|
|
@Get("merchants")
|
|
async merchants(
|
|
@CurrentUser() userId: string,
|
|
@Query("limit") limit = 6,
|
|
) {
|
|
const data = await this.transactionsService.merchantInsights(userId, +limit);
|
|
return ok(data);
|
|
}
|
|
|
|
@Patch(":id/derived")
|
|
async updateDerived(
|
|
@CurrentUser() userId: string,
|
|
@Param("id") id: string,
|
|
@Body() payload: UpdateDerivedDto,
|
|
) {
|
|
const data = await this.transactionsService.updateDerived(userId, id, payload);
|
|
return ok(data);
|
|
}
|
|
}
|
|
`);
|
|
|
|
// ─── create-manual-transaction.dto.ts ────────────────────────────────────────
|
|
writeFileSync("src/transactions/dto/create-manual-transaction.dto.ts", `import { IsBoolean, IsNumber, IsOptional, IsString, IsDateString } from "class-validator";
|
|
|
|
export class CreateManualTransactionDto {
|
|
@IsOptional()
|
|
@IsString()
|
|
accountId?: string;
|
|
|
|
@IsDateString()
|
|
date!: string;
|
|
|
|
@IsString()
|
|
description!: string;
|
|
|
|
@IsNumber()
|
|
amount!: number;
|
|
|
|
@IsOptional()
|
|
@IsString()
|
|
category?: string;
|
|
|
|
@IsOptional()
|
|
@IsString()
|
|
note?: string;
|
|
|
|
@IsOptional()
|
|
@IsBoolean()
|
|
hidden?: boolean;
|
|
}
|
|
`);
|
|
|
|
console.log("transactions files written");
|