1121 lines
35 KiB
TypeScript
1121 lines
35 KiB
TypeScript
import type { Req, Res, Next } from '../types/http';
|
|
const router = require('express').Router();
|
|
const { log } = require('../utils/logger.cts');
|
|
const { getDb } = require('../db/database.cts');
|
|
const { standardizeError } = require('../middleware/errorFormatter.cts');
|
|
const { ApiError, ValidationError, NotFoundError } = require('../utils/apiError.cts');
|
|
|
|
// Boundary adapter: the internal parse/normalize helpers return
|
|
// { error: <standardized body> } result objects; at the HTTP boundary we
|
|
// rethrow them as ApiError so the terminal handler emits the same wire shape.
|
|
function throwStandardized(errBody: any, status = 400): never {
|
|
throw new ApiError(errBody.code || 'VALIDATION_ERROR', errBody.message, status, {
|
|
field: errBody.field,
|
|
});
|
|
}
|
|
const {
|
|
decorateTransaction,
|
|
ensureManualDataSource,
|
|
getTransactionForUser,
|
|
} = require('../services/transactionService.cts');
|
|
const { checkTransaction: advisoryCheck } = require('../services/advisoryFilterService.cts');
|
|
const { getBankSyncConfig } = require('../services/bankSyncConfigService.cts');
|
|
const { markUnmatched } = require('../services/transactionMatchState.cts');
|
|
const {
|
|
ignoreTransaction,
|
|
matchTransactionToBill,
|
|
unignoreTransaction,
|
|
unmatchTransaction,
|
|
} = require('../services/transactionMatchService.cts');
|
|
const { reactivatePaymentsOverriddenBy } = require('../services/paymentAccountingService.cts');
|
|
const {
|
|
findMerchantMatch,
|
|
applyMerchantStoreMatches,
|
|
findOrCreateCategory,
|
|
} = require('../services/merchantStoreMatchService.cts');
|
|
const { categorizeTransaction } = require('../services/spendingService.cts');
|
|
const { todayLocal } = require('../utils/dates.mts');
|
|
const { roundMoney } = require('../utils/money.mts');
|
|
|
|
const MATCH_STATUSES = new Set(['unmatched', 'matched', 'ignored']);
|
|
const SOURCE_TYPES = new Set(['manual', 'file_import', 'provider_sync']);
|
|
const MATCH_CONTROL_FIELDS = ['matched_bill_id', 'match_status', 'ignored'];
|
|
const TEXT_FIELDS = {
|
|
transaction_type: 64,
|
|
currency: 16,
|
|
description: 500,
|
|
payee: 255,
|
|
memo: 500,
|
|
category: 255,
|
|
};
|
|
|
|
function todayStr() {
|
|
return todayLocal();
|
|
}
|
|
|
|
function cleanText(value: any, maxLength: number) {
|
|
if (value === undefined) return undefined;
|
|
if (value === null) return null;
|
|
const text = String(value).trim();
|
|
if (!text) return null;
|
|
return text.slice(0, maxLength);
|
|
}
|
|
|
|
function parseInteger(
|
|
value: any,
|
|
field: string,
|
|
{
|
|
allowNull = false,
|
|
min = null,
|
|
max = null,
|
|
}: { allowNull?: boolean; min?: number | null; max?: number | null } = {},
|
|
) {
|
|
if (value === null && allowNull) return { value: null };
|
|
if (value === undefined) return { value: undefined };
|
|
const n = typeof value === 'number' ? value : Number(value);
|
|
if (!Number.isSafeInteger(n)) {
|
|
return { error: standardizeError(`${field} must be an integer`, 'VALIDATION_ERROR', field) };
|
|
}
|
|
if (min !== null && n < min) {
|
|
return {
|
|
error: standardizeError(`${field} must be at least ${min}`, 'VALIDATION_ERROR', field),
|
|
};
|
|
}
|
|
if (max !== null && n > max) {
|
|
return {
|
|
error: standardizeError(`${field} must be at most ${max}`, 'VALIDATION_ERROR', field),
|
|
};
|
|
}
|
|
return { value: n };
|
|
}
|
|
|
|
function parseBooleanInt(value: any, field: string) {
|
|
if (value === undefined) return { value: undefined };
|
|
if (value === true || value === 'true' || value === '1' || value === 1) return { value: 1 };
|
|
if (value === false || value === 'false' || value === '0' || value === 0) return { value: 0 };
|
|
return { error: standardizeError(`${field} must be true or false`, 'VALIDATION_ERROR', field) };
|
|
}
|
|
|
|
function parseDate(value: any, field: string, { allowNull = false } = {}) {
|
|
if (value === null && allowNull) return { value: null };
|
|
if (value === undefined) return { value: undefined };
|
|
const text = String(value).trim();
|
|
if (!/^\d{4}-\d{2}-\d{2}$/.test(text)) {
|
|
return {
|
|
error: standardizeError(
|
|
`${field} must be a valid YYYY-MM-DD date`,
|
|
'VALIDATION_ERROR',
|
|
field,
|
|
),
|
|
};
|
|
}
|
|
const date = new Date(`${text}T00:00:00Z`);
|
|
if (Number.isNaN(date.getTime()) || date.toISOString().slice(0, 10) !== text) {
|
|
return {
|
|
error: standardizeError(`${field} must be a valid calendar date`, 'VALIDATION_ERROR', field),
|
|
};
|
|
}
|
|
return { value: text };
|
|
}
|
|
|
|
function parseOptionalDateTime(value: any, field: string) {
|
|
if (value === undefined) return { value: undefined };
|
|
if (value === null) return { value: null };
|
|
const text = String(value).trim();
|
|
if (!text) return { value: null };
|
|
|
|
const match =
|
|
/^(\d{4}-\d{2}-\d{2})(?:[T ]\d{2}:\d{2}(?::\d{2}(?:\.\d{1,9})?)?(?:Z|[+-]\d{2}:?\d{2})?)?$/.exec(
|
|
text,
|
|
);
|
|
if (!match) {
|
|
return {
|
|
error: standardizeError(
|
|
`${field} must be a valid ISO date or date-time`,
|
|
'VALIDATION_ERROR',
|
|
field,
|
|
),
|
|
};
|
|
}
|
|
const parsedDate = parseDate(match[1], field);
|
|
if (parsedDate.error) {
|
|
return parsedDate;
|
|
}
|
|
return { value: text };
|
|
}
|
|
|
|
function hasOwn(obj: any, field: string) {
|
|
return Object.prototype.hasOwnProperty.call(obj, field);
|
|
}
|
|
|
|
function getOwnedAccount(db: any, userId: any, accountId: any) {
|
|
if (accountId == null) return null;
|
|
return db
|
|
.prepare('SELECT * FROM financial_accounts WHERE id = ? AND user_id = ? AND monitored = 1')
|
|
.get(accountId, userId);
|
|
}
|
|
|
|
function getOwnedBill(db: any, userId: any, billId: any) {
|
|
if (billId == null) return null;
|
|
return db
|
|
.prepare('SELECT * FROM bills WHERE id = ? AND user_id = ? AND deleted_at IS NULL')
|
|
.get(billId, userId);
|
|
}
|
|
|
|
function normalizeTransactionFields(
|
|
db: any,
|
|
userId: any,
|
|
body: any = {},
|
|
{ partial = false } = {},
|
|
) {
|
|
const normalized: Record<string, any> = {};
|
|
|
|
if (!partial || body.amount !== undefined) {
|
|
const parsed = parseInteger(body.amount, 'amount');
|
|
if (parsed.error) return { error: parsed.error };
|
|
if (parsed.value === 0) {
|
|
return {
|
|
error: standardizeError(
|
|
'amount must be non-zero integer cents',
|
|
'VALIDATION_ERROR',
|
|
'amount',
|
|
),
|
|
};
|
|
}
|
|
normalized.amount = parsed.value;
|
|
}
|
|
|
|
if (!partial || body.posted_date !== undefined) {
|
|
const postedDateValue =
|
|
body.posted_date === undefined && !partial ? todayStr() : body.posted_date;
|
|
const parsed = parseDate(postedDateValue, 'posted_date', { allowNull: partial });
|
|
if (parsed.error) return { error: parsed.error };
|
|
normalized.posted_date = parsed.value;
|
|
}
|
|
|
|
if (body.transacted_at !== undefined) {
|
|
const parsed = parseOptionalDateTime(body.transacted_at, 'transacted_at');
|
|
if (parsed.error) return { error: parsed.error };
|
|
normalized.transacted_at = parsed.value;
|
|
} else if (!partial) {
|
|
normalized.transacted_at = null;
|
|
}
|
|
|
|
for (const [field, maxLength] of Object.entries(TEXT_FIELDS)) {
|
|
if (!partial || body[field] !== undefined) {
|
|
const value = cleanText(body[field], maxLength);
|
|
normalized[field] = value === undefined ? null : value;
|
|
}
|
|
}
|
|
|
|
if (!partial && !normalized.currency) normalized.currency = 'USD';
|
|
|
|
if (body.account_id !== undefined) {
|
|
const parsed = parseInteger(body.account_id, 'account_id', { allowNull: true });
|
|
if (parsed.error) return { error: parsed.error };
|
|
if (parsed.value !== null && !getOwnedAccount(db, userId, parsed.value)) {
|
|
return {
|
|
error: standardizeError('Financial account not found', 'NOT_FOUND', 'account_id'),
|
|
status: 404,
|
|
};
|
|
}
|
|
normalized.account_id = parsed.value;
|
|
} else if (!partial) {
|
|
normalized.account_id = null;
|
|
}
|
|
|
|
if (body.matched_bill_id !== undefined) {
|
|
const parsed = parseInteger(body.matched_bill_id, 'matched_bill_id', { allowNull: true });
|
|
if (parsed.error) return { error: parsed.error };
|
|
if (parsed.value !== null && !getOwnedBill(db, userId, parsed.value)) {
|
|
return {
|
|
error: standardizeError('Matched bill not found', 'NOT_FOUND', 'matched_bill_id'),
|
|
status: 404,
|
|
};
|
|
}
|
|
normalized.matched_bill_id = parsed.value;
|
|
} else if (!partial) {
|
|
normalized.matched_bill_id = null;
|
|
}
|
|
|
|
if (body.match_status !== undefined) {
|
|
const matchStatus = cleanText(body.match_status, 32);
|
|
if (!MATCH_STATUSES.has(matchStatus as string)) {
|
|
return {
|
|
error: standardizeError(
|
|
'match_status must be unmatched, matched, or ignored',
|
|
'VALIDATION_ERROR',
|
|
'match_status',
|
|
),
|
|
};
|
|
}
|
|
normalized.match_status = matchStatus;
|
|
}
|
|
|
|
if (body.ignored !== undefined) {
|
|
const parsed = parseBooleanInt(body.ignored, 'ignored');
|
|
if (parsed.error) return { error: parsed.error };
|
|
normalized.ignored = parsed.value;
|
|
}
|
|
|
|
return { normalized };
|
|
}
|
|
|
|
function resolveTransactionState(next: any, existing: any = {}) {
|
|
const hasStatus = hasOwn(next, 'match_status');
|
|
const hasIgnored = hasOwn(next, 'ignored');
|
|
const hasMatchedBill = hasOwn(next, 'matched_bill_id');
|
|
|
|
if (hasStatus && next.match_status === 'ignored' && hasIgnored && next.ignored === 0) {
|
|
return {
|
|
error: standardizeError(
|
|
'ignored must be true when match_status is ignored',
|
|
'VALIDATION_ERROR',
|
|
'ignored',
|
|
),
|
|
};
|
|
}
|
|
if (hasIgnored && next.ignored === 1 && hasStatus && next.match_status !== 'ignored') {
|
|
return {
|
|
error: standardizeError(
|
|
'match_status must be ignored when ignored is true',
|
|
'VALIDATION_ERROR',
|
|
'match_status',
|
|
),
|
|
};
|
|
}
|
|
if (
|
|
hasStatus &&
|
|
next.match_status === 'unmatched' &&
|
|
hasMatchedBill &&
|
|
next.matched_bill_id !== null
|
|
) {
|
|
return {
|
|
error: standardizeError(
|
|
'matched_bill_id must be null when match_status is unmatched',
|
|
'VALIDATION_ERROR',
|
|
'matched_bill_id',
|
|
),
|
|
};
|
|
}
|
|
if (
|
|
hasStatus &&
|
|
next.match_status === 'matched' &&
|
|
hasMatchedBill &&
|
|
next.matched_bill_id === null
|
|
) {
|
|
return {
|
|
error: standardizeError(
|
|
'matched_bill_id is required when match_status is matched',
|
|
'VALIDATION_ERROR',
|
|
'matched_bill_id',
|
|
),
|
|
};
|
|
}
|
|
|
|
let matchedBillId = hasMatchedBill ? next.matched_bill_id : (existing.matched_bill_id ?? null);
|
|
let matchStatus = hasStatus
|
|
? next.match_status
|
|
: (existing.match_status ?? (matchedBillId ? 'matched' : 'unmatched'));
|
|
let ignored = hasIgnored ? next.ignored : (existing.ignored ?? 0);
|
|
|
|
if (hasIgnored && ignored === 0 && matchStatus === 'ignored') {
|
|
matchStatus = 'unmatched';
|
|
matchedBillId = null;
|
|
}
|
|
|
|
if (ignored === 1 || matchStatus === 'ignored') {
|
|
return {
|
|
matched_bill_id: null,
|
|
match_status: 'ignored',
|
|
ignored: 1,
|
|
};
|
|
}
|
|
|
|
if (matchStatus === 'matched' || matchedBillId !== null) {
|
|
if (matchedBillId === null) {
|
|
return {
|
|
error: standardizeError(
|
|
'matched_bill_id is required when match_status is matched',
|
|
'VALIDATION_ERROR',
|
|
'matched_bill_id',
|
|
),
|
|
};
|
|
}
|
|
return {
|
|
matched_bill_id: matchedBillId,
|
|
match_status: 'matched',
|
|
ignored: 0,
|
|
};
|
|
}
|
|
|
|
return {
|
|
matched_bill_id: null,
|
|
match_status: 'unmatched',
|
|
ignored: 0,
|
|
};
|
|
}
|
|
|
|
function parseLimitOffset(query: any) {
|
|
const limit = parseInteger(query.limit ?? 50, 'limit', { min: 1, max: 200 });
|
|
if (limit.error) return { error: limit.error };
|
|
const offset = parseInteger(query.offset ?? 0, 'offset', { min: 0 });
|
|
if (offset.error) return { error: offset.error };
|
|
return { limit: limit.value, offset: offset.value };
|
|
}
|
|
|
|
function selectedTransaction(db: any, userId: any, id: any) {
|
|
return decorateTransaction(getTransactionForUser(db, userId, id));
|
|
}
|
|
|
|
function rejectDirectMatchState(body = {}) {
|
|
const field = MATCH_CONTROL_FIELDS.find((name) => hasOwn(body, name));
|
|
if (!field) return null;
|
|
return standardizeError(
|
|
'Use the transaction match, unmatch, ignore, or unignore endpoint to change match state',
|
|
'VALIDATION_ERROR',
|
|
field,
|
|
);
|
|
}
|
|
|
|
// 4xx service errors keep their message/code on the wire; anything else is
|
|
// rethrown raw so the terminal handler masks + logs it as a 5xx.
|
|
function toTransactionServiceError(err: any) {
|
|
if (err.status && err.status < 500) {
|
|
return new ApiError(err.code || 'TRANSACTION_ERROR', err.message, err.status, {
|
|
field: err.field,
|
|
});
|
|
}
|
|
return err;
|
|
}
|
|
|
|
function emptyBankLedger(enabled: any, hasConnections = false) {
|
|
return {
|
|
enabled: Boolean(enabled),
|
|
has_connections: Boolean(hasConnections),
|
|
sources: [],
|
|
accounts: [],
|
|
summary: {
|
|
total: 0,
|
|
inflow: 0,
|
|
outflow: 0,
|
|
net: 0,
|
|
pending: 0,
|
|
matched: 0,
|
|
unmatched: 0,
|
|
latest_date: null,
|
|
category_breakdown: [],
|
|
},
|
|
transactions: [],
|
|
total: 0,
|
|
limit: 50,
|
|
offset: 0,
|
|
};
|
|
}
|
|
|
|
function bankLedgerWhere(query: any, userId: any) {
|
|
const where = [
|
|
't.user_id = ?',
|
|
"ds.type = 'provider_sync'",
|
|
"ds.provider = 'simplefin'",
|
|
'(t.account_id IS NULL OR fa.id IS NULL OR fa.monitored = 1)',
|
|
];
|
|
const params = [userId];
|
|
|
|
if (query.account_id && query.account_id !== 'all') {
|
|
const parsed = parseInteger(query.account_id, 'account_id');
|
|
if (parsed.error) return { error: parsed.error };
|
|
where.push('t.account_id = ?');
|
|
params.push(parsed.value);
|
|
}
|
|
|
|
const flow = query.flow ? String(query.flow).trim() : 'all';
|
|
if (
|
|
!['all', 'in', 'out', 'pending', 'matched', 'unmatched', 'ignored', 'uncategorized'].includes(
|
|
flow,
|
|
)
|
|
) {
|
|
return {
|
|
error: standardizeError(
|
|
'flow must be all, in, out, pending, matched, unmatched, ignored, or uncategorized',
|
|
'VALIDATION_ERROR',
|
|
'flow',
|
|
),
|
|
};
|
|
}
|
|
if (flow === 'in') where.push('t.amount > 0');
|
|
if (flow === 'out') where.push('t.amount < 0');
|
|
if (flow === 'pending') where.push('t.pending = 1');
|
|
if (flow === 'matched') where.push("t.match_status = 'matched'");
|
|
if (flow === 'unmatched') where.push("t.match_status = 'unmatched' AND t.ignored = 0");
|
|
if (flow === 'ignored') where.push('t.ignored = 1');
|
|
if (flow === 'uncategorized')
|
|
where.push('t.spending_category_id IS NULL AND t.amount < 0 AND t.ignored = 0');
|
|
|
|
if (query.start_date) {
|
|
const parsed = parseDate(query.start_date, 'start_date');
|
|
if (parsed.error) return { error: parsed.error };
|
|
where.push('COALESCE(t.posted_date, substr(t.transacted_at, 1, 10)) >= ?');
|
|
params.push(parsed.value);
|
|
}
|
|
if (query.end_date) {
|
|
const parsed = parseDate(query.end_date, 'end_date');
|
|
if (parsed.error) return { error: parsed.error };
|
|
where.push('COALESCE(t.posted_date, substr(t.transacted_at, 1, 10)) <= ?');
|
|
params.push(parsed.value);
|
|
}
|
|
|
|
if (query.q) {
|
|
const q = `%${String(query.q).trim()}%`;
|
|
where.push(`(
|
|
t.description LIKE ? OR t.payee LIKE ? OR t.memo LIKE ? OR t.category LIKE ?
|
|
OR fa.name LIKE ? OR fa.org_name LIKE ? OR b.name LIKE ? OR c.name LIKE ?
|
|
)`);
|
|
params.push(q, q, q, q, q, q, q, q);
|
|
}
|
|
|
|
return { whereClause: where.join(' AND '), params };
|
|
}
|
|
|
|
// GET /api/transactions
|
|
router.get('/', (req: Req, res: Res) => {
|
|
const db = getDb();
|
|
ensureManualDataSource(db, req.user.id);
|
|
|
|
const page = parseLimitOffset(req.query);
|
|
if (page.error) throwStandardized(page.error);
|
|
|
|
const SORT_COLUMNS: Record<string, string> = {
|
|
date: 'COALESCE(t.posted_date, substr(t.transacted_at, 1, 10), t.created_at)',
|
|
amount: 't.amount',
|
|
};
|
|
const sortBy = SORT_COLUMNS[req.query.sort_by] ? req.query.sort_by : 'date';
|
|
const sortDir = req.query.sort_dir === 'asc' ? 'ASC' : 'DESC';
|
|
const orderBy = `${SORT_COLUMNS[sortBy]} ${sortDir}, t.id ${sortDir}`;
|
|
|
|
const where = ['t.user_id = ?', '(t.account_id IS NULL OR fa.id IS NULL OR fa.monitored = 1)'];
|
|
const params = [req.user.id];
|
|
|
|
const matchStatusFilter = req.query.match_status ? String(req.query.match_status).trim() : '';
|
|
if (matchStatusFilter && !MATCH_STATUSES.has(matchStatusFilter)) {
|
|
return res
|
|
.status(400)
|
|
.json(
|
|
standardizeError(
|
|
'match_status must be unmatched, matched, or ignored',
|
|
'VALIDATION_ERROR',
|
|
'match_status',
|
|
),
|
|
);
|
|
}
|
|
|
|
const ignored = req.query.ignored;
|
|
if (ignored === 'true' || ignored === '1') {
|
|
where.push('t.ignored = 1');
|
|
} else if (ignored === 'false' || ignored === '0') {
|
|
where.push('t.ignored = 0');
|
|
} else if (ignored !== 'all') {
|
|
if (ignored !== undefined) {
|
|
return res
|
|
.status(400)
|
|
.json(
|
|
standardizeError('ignored must be true, false, or all', 'VALIDATION_ERROR', 'ignored'),
|
|
);
|
|
}
|
|
where.push(matchStatusFilter === 'ignored' ? 't.ignored = 1' : 't.ignored = 0');
|
|
}
|
|
|
|
if (req.query.source_type) {
|
|
const sourceType = String(req.query.source_type).trim();
|
|
if (!SOURCE_TYPES.has(sourceType)) {
|
|
return res
|
|
.status(400)
|
|
.json(
|
|
standardizeError(
|
|
'source_type must be manual, file_import, or provider_sync',
|
|
'VALIDATION_ERROR',
|
|
'source_type',
|
|
),
|
|
);
|
|
}
|
|
where.push('t.source_type = ?');
|
|
params.push(sourceType);
|
|
}
|
|
|
|
if (matchStatusFilter) {
|
|
where.push('t.match_status = ?');
|
|
params.push(matchStatusFilter);
|
|
}
|
|
|
|
for (const field of ['transaction_type']) {
|
|
if (req.query[field]) {
|
|
where.push(`t.${field} = ?`);
|
|
params.push(String(req.query[field]).trim());
|
|
}
|
|
}
|
|
|
|
for (const field of ['data_source_id', 'account_id', 'matched_bill_id']) {
|
|
if (req.query[field] !== undefined) {
|
|
const parsed = parseInteger(req.query[field], field);
|
|
if (parsed.error) throwStandardized(parsed.error);
|
|
where.push(`t.${field} = ?`);
|
|
params.push(parsed.value);
|
|
}
|
|
}
|
|
|
|
if (req.query.start_date) {
|
|
const parsed = parseDate(req.query.start_date, 'start_date');
|
|
if (parsed.error) throwStandardized(parsed.error);
|
|
where.push('COALESCE(t.posted_date, substr(t.transacted_at, 1, 10)) >= ?');
|
|
params.push(parsed.value);
|
|
}
|
|
if (req.query.end_date) {
|
|
const parsed = parseDate(req.query.end_date, 'end_date');
|
|
if (parsed.error) throwStandardized(parsed.error);
|
|
where.push('COALESCE(t.posted_date, substr(t.transacted_at, 1, 10)) <= ?');
|
|
params.push(parsed.value);
|
|
}
|
|
|
|
if (req.query.q) {
|
|
const q = `%${String(req.query.q).trim()}%`;
|
|
where.push('(t.description LIKE ? OR t.payee LIKE ? OR t.memo LIKE ? OR t.category LIKE ?)');
|
|
params.push(q, q, q, q);
|
|
}
|
|
|
|
const whereClause = where.join(' AND ');
|
|
const joins = `
|
|
FROM transactions t
|
|
LEFT JOIN data_sources ds ON ds.id = t.data_source_id AND ds.user_id = t.user_id
|
|
LEFT JOIN financial_accounts fa ON fa.id = t.account_id AND fa.user_id = t.user_id
|
|
LEFT JOIN bills b ON b.id = t.matched_bill_id AND b.user_id = t.user_id AND b.deleted_at IS NULL
|
|
`;
|
|
|
|
const total = db.prepare(`SELECT COUNT(*) AS n ${joins} WHERE ${whereClause}`).get(...params).n;
|
|
|
|
const rows = db
|
|
.prepare(
|
|
`
|
|
SELECT
|
|
t.id, t.user_id, t.data_source_id, t.account_id, t.provider_transaction_id,
|
|
t.source_type, t.transaction_type, t.posted_date, t.transacted_at, t.amount,
|
|
t.currency, t.description, t.payee, t.memo, t.category, t.matched_bill_id,
|
|
t.match_status, t.ignored, t.pending, t.created_at, t.updated_at,
|
|
ds.type AS data_source_type, ds.provider AS data_source_provider,
|
|
ds.name AS data_source_name, ds.status AS data_source_status,
|
|
fa.name AS account_name, fa.org_name AS account_org_name,
|
|
fa.account_type AS account_type,
|
|
b.name AS matched_bill_name
|
|
${joins}
|
|
WHERE ${whereClause}
|
|
ORDER BY ${orderBy}
|
|
LIMIT ? OFFSET ?
|
|
`,
|
|
)
|
|
.all(...params, page.limit, page.offset);
|
|
|
|
res.json({
|
|
transactions: rows.map((row: any) => {
|
|
const decorated = decorateTransaction(row);
|
|
const title = row.payee || row.description || row.memo || '';
|
|
decorated.advisory_filter = advisoryCheck(title);
|
|
return decorated;
|
|
}),
|
|
total,
|
|
limit: page.limit,
|
|
offset: page.offset,
|
|
});
|
|
});
|
|
|
|
// GET /api/transactions/bank-ledger
|
|
router.get('/bank-ledger', (req: Req, res: Res) => {
|
|
const config = getBankSyncConfig();
|
|
const page = parseLimitOffset(req.query);
|
|
if (page.error) throwStandardized(page.error);
|
|
|
|
const db = getDb();
|
|
const sources = db
|
|
.prepare(
|
|
`
|
|
SELECT id, user_id, type, provider, name, status, last_sync_at, created_at, updated_at
|
|
FROM data_sources
|
|
WHERE user_id = ? AND type = 'provider_sync' AND provider = 'simplefin'
|
|
ORDER BY updated_at DESC, id DESC
|
|
`,
|
|
)
|
|
.all(req.user.id);
|
|
|
|
if (!config.enabled || sources.length === 0) {
|
|
return res.json({
|
|
...emptyBankLedger(config.enabled, sources.length > 0),
|
|
sources,
|
|
limit: page.limit,
|
|
offset: page.offset,
|
|
});
|
|
}
|
|
|
|
const accounts = db
|
|
.prepare(
|
|
`
|
|
SELECT
|
|
fa.id, fa.data_source_id, fa.name, fa.org_name, fa.account_type, fa.currency,
|
|
fa.balance, fa.available_balance, fa.monitored, fa.updated_at,
|
|
ds.name AS source_name, ds.status AS source_status, ds.last_sync_at,
|
|
COUNT(t.id) AS transaction_count,
|
|
MAX(COALESCE(t.posted_date, substr(t.transacted_at, 1, 10))) AS last_transaction_date
|
|
FROM financial_accounts fa
|
|
JOIN data_sources ds ON ds.id = fa.data_source_id AND ds.user_id = fa.user_id
|
|
LEFT JOIN transactions t ON t.account_id = fa.id AND t.user_id = fa.user_id
|
|
WHERE fa.user_id = ?
|
|
AND fa.monitored = 1
|
|
AND ds.type = 'provider_sync'
|
|
AND ds.provider = 'simplefin'
|
|
GROUP BY fa.id
|
|
ORDER BY COALESCE(fa.org_name, ''), fa.name
|
|
`,
|
|
)
|
|
.all(req.user.id);
|
|
|
|
const filtered = bankLedgerWhere(req.query, req.user.id);
|
|
if (filtered.error) throwStandardized(filtered.error);
|
|
|
|
const SORT_COLUMNS: Record<string, string> = {
|
|
date: 'COALESCE(t.posted_date, substr(t.transacted_at, 1, 10), t.created_at)',
|
|
amount: 't.amount',
|
|
merchant: "LOWER(COALESCE(t.payee, t.description, t.memo, ''))",
|
|
account: "LOWER(COALESCE(fa.org_name, fa.name, ''))",
|
|
status: 't.match_status',
|
|
};
|
|
const sortBy = SORT_COLUMNS[req.query.sort_by] ? req.query.sort_by : 'date';
|
|
const sortDir = req.query.sort_dir === 'asc' ? 'ASC' : 'DESC';
|
|
const orderBy = `${SORT_COLUMNS[sortBy]} ${sortDir}, t.id ${sortDir}`;
|
|
|
|
const joins = `
|
|
FROM transactions t
|
|
JOIN data_sources ds ON ds.id = t.data_source_id AND ds.user_id = t.user_id
|
|
LEFT JOIN financial_accounts fa ON fa.id = t.account_id AND fa.user_id = t.user_id
|
|
LEFT JOIN bills b ON b.id = t.matched_bill_id AND b.user_id = t.user_id AND b.deleted_at IS NULL
|
|
LEFT JOIN categories c ON c.id = t.spending_category_id
|
|
`;
|
|
|
|
const summary = db
|
|
.prepare(
|
|
`
|
|
SELECT
|
|
COUNT(*) AS total,
|
|
COALESCE(SUM(CASE WHEN t.amount > 0 THEN t.amount ELSE 0 END), 0) AS inflow,
|
|
COALESCE(SUM(CASE WHEN t.amount < 0 THEN ABS(t.amount) ELSE 0 END), 0) AS outflow,
|
|
COALESCE(SUM(t.amount), 0) AS net,
|
|
COALESCE(SUM(CASE WHEN t.pending = 1 THEN 1 ELSE 0 END), 0) AS pending,
|
|
COALESCE(SUM(CASE WHEN t.match_status = 'matched' THEN 1 ELSE 0 END), 0) AS matched,
|
|
COALESCE(SUM(CASE WHEN t.match_status = 'unmatched' AND t.ignored = 0 THEN 1 ELSE 0 END), 0) AS unmatched,
|
|
MAX(COALESCE(t.posted_date, substr(t.transacted_at, 1, 10))) AS latest_date
|
|
${joins}
|
|
WHERE ${filtered.whereClause}
|
|
`,
|
|
)
|
|
.get(...(filtered.params as any[]));
|
|
|
|
summary.category_breakdown = db
|
|
.prepare(
|
|
`
|
|
SELECT
|
|
COALESCE(c.name, 'Uncategorized') AS name,
|
|
COUNT(*) AS count,
|
|
COALESCE(SUM(CASE WHEN t.amount < 0 THEN ABS(t.amount) ELSE 0 END), 0) AS total
|
|
${joins}
|
|
WHERE ${filtered.whereClause}
|
|
GROUP BY COALESCE(c.name, 'Uncategorized')
|
|
ORDER BY total DESC
|
|
LIMIT 6
|
|
`,
|
|
)
|
|
.all(...(filtered.params as any[]));
|
|
|
|
const total = summary.total;
|
|
const rows = db
|
|
.prepare(
|
|
`
|
|
SELECT
|
|
t.id, t.user_id, t.data_source_id, t.account_id, t.provider_transaction_id,
|
|
t.source_type, t.transaction_type, t.posted_date, t.transacted_at, t.amount,
|
|
t.currency, t.description, t.payee, t.memo, t.category, t.matched_bill_id,
|
|
t.match_status, t.ignored, t.pending, t.created_at, t.updated_at,
|
|
t.spending_category_id, c.name AS spending_category_name,
|
|
ds.type AS data_source_type, ds.provider AS data_source_provider,
|
|
ds.name AS data_source_name, ds.status AS data_source_status,
|
|
fa.name AS account_name, fa.org_name AS account_org_name,
|
|
fa.account_type AS account_type, fa.balance AS account_balance,
|
|
fa.available_balance AS account_available_balance,
|
|
b.name AS matched_bill_name
|
|
${joins}
|
|
WHERE ${filtered.whereClause}
|
|
ORDER BY ${orderBy}
|
|
LIMIT ? OFFSET ?
|
|
`,
|
|
)
|
|
.all(...(filtered.params as any[]), page.limit, page.offset);
|
|
|
|
res.json({
|
|
enabled: true,
|
|
has_connections: true,
|
|
sources,
|
|
accounts,
|
|
summary,
|
|
transactions: rows.map((row: any) => {
|
|
const decorated = decorateTransaction(row);
|
|
if (!row.spending_category_id) {
|
|
decorated.suggested_match = findMerchantMatch(
|
|
db,
|
|
row.payee || row.description || row.memo || '',
|
|
);
|
|
}
|
|
return decorated;
|
|
}),
|
|
total,
|
|
limit: page.limit,
|
|
offset: page.offset,
|
|
});
|
|
});
|
|
|
|
// POST /api/transactions/manual
|
|
router.post('/manual', (req: Req, res: Res) => {
|
|
const db = getDb();
|
|
const directMatchState = rejectDirectMatchState(req.body);
|
|
if (directMatchState) throwStandardized(directMatchState);
|
|
|
|
const validation = normalizeTransactionFields(db, req.user.id, req.body);
|
|
if (validation.error) throwStandardized(validation.error, validation.status || 400);
|
|
const tx: any = validation.normalized;
|
|
const source = ensureManualDataSource(db, req.user.id);
|
|
const state = resolveTransactionState(tx);
|
|
if (state.error) throwStandardized(state.error);
|
|
Object.assign(tx, state);
|
|
|
|
const result = db
|
|
.prepare(
|
|
`
|
|
INSERT INTO transactions
|
|
(user_id, data_source_id, account_id, source_type, transaction_type,
|
|
posted_date, transacted_at, amount, currency, description, payee, memo,
|
|
category, matched_bill_id, match_status, ignored)
|
|
VALUES (?, ?, ?, 'manual', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
|
`,
|
|
)
|
|
.run(
|
|
req.user.id,
|
|
source.id,
|
|
tx.account_id,
|
|
tx.transaction_type,
|
|
tx.posted_date,
|
|
tx.transacted_at,
|
|
tx.amount,
|
|
tx.currency,
|
|
tx.description,
|
|
tx.payee,
|
|
tx.memo,
|
|
tx.category,
|
|
tx.matched_bill_id,
|
|
tx.match_status,
|
|
tx.ignored,
|
|
);
|
|
|
|
res.status(201).json(selectedTransaction(db, req.user.id, result.lastInsertRowid));
|
|
});
|
|
|
|
// PUT /api/transactions/:id
|
|
router.put('/:id', (req: Req, res: Res) => {
|
|
const db = getDb();
|
|
const directMatchState = rejectDirectMatchState(req.body);
|
|
if (directMatchState) throwStandardized(directMatchState);
|
|
|
|
const id = parseInteger(req.params.id, 'id');
|
|
if (id.error) throwStandardized(id.error);
|
|
|
|
const existing = getTransactionForUser(db, req.user.id, id.value);
|
|
if (!existing) throw NotFoundError('Transaction not found', 'id');
|
|
|
|
const validation = normalizeTransactionFields(db, req.user.id, req.body, { partial: true });
|
|
if (validation.error) throwStandardized(validation.error, validation.status || 400);
|
|
const tx: any = validation.normalized;
|
|
const state = resolveTransactionState(tx, existing);
|
|
if (state.error) throwStandardized(state.error);
|
|
Object.assign(tx, state);
|
|
|
|
const nextPostedDate = hasOwn(tx, 'posted_date') ? tx.posted_date : existing.posted_date;
|
|
const nextTransactedAt = hasOwn(tx, 'transacted_at') ? tx.transacted_at : existing.transacted_at;
|
|
if (!nextPostedDate && !nextTransactedAt) {
|
|
return res
|
|
.status(400)
|
|
.json(
|
|
standardizeError(
|
|
'posted_date or transacted_at is required',
|
|
'VALIDATION_ERROR',
|
|
'posted_date',
|
|
),
|
|
);
|
|
}
|
|
|
|
const fields = [
|
|
'account_id',
|
|
'transaction_type',
|
|
'posted_date',
|
|
'transacted_at',
|
|
'amount',
|
|
'currency',
|
|
'description',
|
|
'payee',
|
|
'memo',
|
|
'category',
|
|
'matched_bill_id',
|
|
'match_status',
|
|
'ignored',
|
|
];
|
|
const sets = [];
|
|
const params = [];
|
|
|
|
for (const field of fields) {
|
|
if (hasOwn(tx, field)) {
|
|
sets.push(`${field} = ?`);
|
|
params.push(tx[field]);
|
|
}
|
|
}
|
|
|
|
if (!sets.length) {
|
|
return res
|
|
.status(400)
|
|
.json(standardizeError('No transaction fields provided', 'VALIDATION_ERROR'));
|
|
}
|
|
|
|
sets.push("updated_at = datetime('now')");
|
|
db.prepare(
|
|
`
|
|
UPDATE transactions
|
|
SET ${sets.join(', ')}
|
|
WHERE id = ? AND user_id = ?
|
|
`,
|
|
).run(...params, id.value, req.user.id);
|
|
|
|
res.json(selectedTransaction(db, req.user.id, id.value));
|
|
});
|
|
|
|
// DELETE /api/transactions/:id
|
|
router.delete('/:id', (req: Req, res: Res) => {
|
|
const db = getDb();
|
|
const id = parseInteger(req.params.id, 'id');
|
|
if (id.error) throwStandardized(id.error);
|
|
|
|
const existing = getTransactionForUser(db, req.user.id, id.value);
|
|
if (!existing) throw NotFoundError('Transaction not found', 'id');
|
|
|
|
db.transaction(() => {
|
|
unmatchTransaction(req.user.id, id.value);
|
|
db.prepare('DELETE FROM transactions WHERE id = ? AND user_id = ?').run(id.value, req.user.id);
|
|
})();
|
|
|
|
res.json({ success: true, deleted: true, id: id.value });
|
|
});
|
|
|
|
// POST /api/transactions/:id/match
|
|
router.post('/:id/match', (req: Req, res: Res) => {
|
|
try {
|
|
const result = matchTransactionToBill(
|
|
req.user.id,
|
|
req.params.id,
|
|
req.body?.billId ?? req.body?.bill_id,
|
|
{ learnMerchant: true },
|
|
);
|
|
res.json(result);
|
|
} catch (err: any) {
|
|
throw toTransactionServiceError(err);
|
|
}
|
|
});
|
|
|
|
// POST /api/transactions/:id/unmatch
|
|
router.post('/:id/unmatch', (req: Req, res: Res) => {
|
|
try {
|
|
const result = unmatchTransaction(req.user.id, req.params.id);
|
|
res.json(result);
|
|
} catch (err: any) {
|
|
throw toTransactionServiceError(err);
|
|
}
|
|
});
|
|
|
|
// POST /api/transactions/unmatch-bulk
|
|
// Body: { matches: [{ transaction_id, payment_id, payment_source }] }
|
|
// Handles both provider_sync (full undo: balance restore + delete payment) and
|
|
// transaction_match (standard service unmatch) in one call.
|
|
router.post('/unmatch-bulk', (req: Req, res: Res) => {
|
|
const matches = req.body?.matches;
|
|
if (!Array.isArray(matches) || matches.length === 0) {
|
|
throw ValidationError('matches array required');
|
|
}
|
|
if (matches.length > 50) {
|
|
return res
|
|
.status(400)
|
|
.json(standardizeError('Cannot unmatch more than 50 at once', 'VALIDATION_ERROR'));
|
|
}
|
|
|
|
const db = getDb();
|
|
const userId = req.user.id;
|
|
const results: any[] = [];
|
|
|
|
db.transaction(() => {
|
|
for (const m of matches) {
|
|
const txId = parseInt(m.transaction_id, 10);
|
|
if (!Number.isInteger(txId) || txId < 1) {
|
|
results.push({
|
|
transaction_id: m.transaction_id,
|
|
ok: false,
|
|
error: 'Invalid transaction_id',
|
|
});
|
|
continue;
|
|
}
|
|
try {
|
|
if (m.payment_source === 'provider_sync' && m.payment_id) {
|
|
// Full reversal: restore balance, soft-delete payment, unlink tx
|
|
const payment = db
|
|
.prepare(
|
|
`
|
|
SELECT p.* FROM payments p
|
|
JOIN bills b ON b.id = p.bill_id
|
|
WHERE p.id = ? AND p.deleted_at IS NULL AND b.user_id = ?
|
|
`,
|
|
)
|
|
.get(m.payment_id, userId);
|
|
|
|
if (payment) {
|
|
if (!payment.accounting_excluded && payment.balance_delta != null) {
|
|
const bill = db
|
|
.prepare('SELECT current_balance FROM bills WHERE id = ?')
|
|
.get(payment.bill_id);
|
|
if (bill?.current_balance != null) {
|
|
const restored = Math.max(
|
|
0,
|
|
roundMoney(Number(bill.current_balance) - Number(payment.balance_delta)),
|
|
);
|
|
db.prepare(
|
|
`
|
|
UPDATE bills
|
|
SET current_balance = ?,
|
|
interest_accrued_month = CASE WHEN ? THEN NULL ELSE interest_accrued_month END,
|
|
updated_at = datetime('now')
|
|
WHERE id = ?
|
|
`,
|
|
).run(restored, payment.interest_delta != null ? 1 : 0, payment.bill_id);
|
|
}
|
|
}
|
|
reactivatePaymentsOverriddenBy(db, payment.id);
|
|
db.prepare("UPDATE payments SET deleted_at = datetime('now') WHERE id = ?").run(
|
|
payment.id,
|
|
);
|
|
}
|
|
markUnmatched(db, userId, txId);
|
|
results.push({ transaction_id: txId, ok: true });
|
|
} else {
|
|
// Standard service unmatch (restores balance for transaction_match payments)
|
|
unmatchTransaction(userId, String(txId));
|
|
results.push({ transaction_id: txId, ok: true });
|
|
}
|
|
} catch (err: any) {
|
|
// Per-item feedback: service 4xx messages are user-facing; anything
|
|
// else is masked — 500-class internals must not leak into the results.
|
|
const msg = err.status && err.status < 500 ? err.message : 'Unmatch failed';
|
|
results.push({ transaction_id: txId, ok: false, error: msg });
|
|
}
|
|
}
|
|
})();
|
|
|
|
const failed = results.filter((r: any) => !r.ok);
|
|
if (failed.length > 0 && failed.length === results.length) {
|
|
// Deliberate hand-rolled body: carries the per-item `results` payload the
|
|
// client renders; formatError has no extras channel (same as payments'
|
|
// DUPLICATE_SUSPECTED exception).
|
|
return res.status(500).json({ error: 'All unmatches failed', results });
|
|
}
|
|
res.json({ results, unmatched: results.filter((r: any) => r.ok).length });
|
|
});
|
|
|
|
// POST /api/transactions/:id/ignore
|
|
router.post('/:id/ignore', (req: Req, res: Res) => {
|
|
try {
|
|
const result = ignoreTransaction(req.user.id, req.params.id);
|
|
res.json(result.transaction);
|
|
} catch (err: any) {
|
|
throw toTransactionServiceError(err);
|
|
}
|
|
});
|
|
|
|
// POST /api/transactions/:id/unignore
|
|
router.post('/:id/unignore', (req: Req, res: Res) => {
|
|
try {
|
|
const result = unignoreTransaction(req.user.id, req.params.id);
|
|
res.json(result.transaction);
|
|
} catch (err: any) {
|
|
throw toTransactionServiceError(err);
|
|
}
|
|
});
|
|
|
|
// GET /api/transactions/:id/merchant-match
|
|
router.get('/:id/merchant-match', (req: Req, res: Res) => {
|
|
try {
|
|
const db = getDb();
|
|
const id = parseInteger(req.params.id, 'id');
|
|
if (id.error) throwStandardized(id.error);
|
|
|
|
const existing = getTransactionForUser(db, req.user.id, id.value);
|
|
if (!existing) throw NotFoundError('Transaction not found', 'id');
|
|
|
|
const match = findMerchantMatch(
|
|
db,
|
|
existing.payee || existing.description || existing.memo || '',
|
|
);
|
|
res.json({ match });
|
|
} catch (err: any) {
|
|
throw toTransactionServiceError(err);
|
|
}
|
|
});
|
|
|
|
// POST /api/transactions/:id/apply-merchant-match
|
|
router.post('/:id/apply-merchant-match', (req: Req, res: Res) => {
|
|
try {
|
|
const db = getDb();
|
|
const id = parseInteger(req.params.id, 'id');
|
|
if (id.error) throwStandardized(id.error);
|
|
|
|
const existing = getTransactionForUser(db, req.user.id, id.value);
|
|
if (!existing) throw NotFoundError('Transaction not found', 'id');
|
|
|
|
const match = findMerchantMatch(
|
|
db,
|
|
existing.payee || existing.description || existing.memo || '',
|
|
);
|
|
if (!match) return res.json({ matched: false });
|
|
|
|
const categoryId = findOrCreateCategory(db, req.user.id, match.category);
|
|
categorizeTransaction(db, req.user.id, id.value, categoryId, true);
|
|
|
|
res.json({
|
|
matched: true,
|
|
category: { id: categoryId, name: match.category },
|
|
display_name: match.display_name,
|
|
});
|
|
} catch (err: any) {
|
|
throw toTransactionServiceError(err);
|
|
}
|
|
});
|
|
|
|
// POST /api/transactions/auto-categorize
|
|
router.post('/auto-categorize', (req: Req, res: Res) => {
|
|
try {
|
|
const db = getDb();
|
|
const result = applyMerchantStoreMatches(db, req.user.id, {
|
|
dryRun: Boolean(req.body?.dry_run),
|
|
});
|
|
res.json(result);
|
|
} catch (err: any) {
|
|
throw toTransactionServiceError(err);
|
|
}
|
|
});
|
|
|
|
module.exports = router;
|