1126 lines
46 KiB
JavaScript
1126 lines
46 KiB
JavaScript
'use strict';
|
||
|
||
const { billingCycleForCycleType, insertBill, validateBillData } = require('./billsService');
|
||
const { localDateString, todayLocal } = require('../utils/dates');
|
||
const { roundMoney, sumMoney, mulMoney, fromCents } = require('../utils/money');
|
||
|
||
const SUBSCRIPTION_TYPES = [
|
||
'streaming', 'software', 'cloud', 'music', 'news',
|
||
'fitness', 'gaming', 'utilities', 'insurance',
|
||
'food', 'education', 'shopping', 'security', 'other',
|
||
];
|
||
|
||
const MONTHLY_FACTORS = {
|
||
weekly: 52 / 12,
|
||
biweekly: 26 / 12,
|
||
monthly: 1,
|
||
quarterly: 1 / 3,
|
||
annual: 1 / 12,
|
||
annually: 1 / 12,
|
||
irregular: 1,
|
||
};
|
||
|
||
// Transactions that are clearly not subscriptions — skip before grouping
|
||
const SKIP_MERCHANT_RE = /\b(atm|withdrawal|transfer|deposit|zelle|venmo|wire|refund|rebate|interest charge)\b/;
|
||
|
||
// Fallback keyword list used when catalog lookup finds no match
|
||
const TYPE_KEYWORDS = [
|
||
['streaming', ['netflix', 'hulu', 'disney', 'max', 'paramount', 'peacock', 'youtube tv', 'sling', 'espn', 'fubo', 'starz', 'crunchyroll', 'dazn']],
|
||
['music', ['spotify', 'apple music', 'tidal', 'pandora', 'siriusxm', 'soundcloud', 'deezer', 'iheart']],
|
||
['software', ['adobe', 'microsoft', 'github', 'notion', 'figma', 'canva', 'openai', 'chatgpt', 'grammarly', 'zoom', 'slack', 'cursor', 'ynab']],
|
||
['cloud', ['dropbox', 'icloud', 'google one', 'google storage', 'backblaze', 'box storage']],
|
||
['news', ['nyt', 'new york times', 'economist', 'athletic', 'washington post', 'wsj', 'bloomberg', 'substack', 'patreon', 'medium']],
|
||
['fitness', ['peloton', 'planet fitness', 'gym', 'fitbit', 'strava', 'headspace', 'calm', 'noom', 'classpass', 'whoop']],
|
||
['gaming', ['xbox', 'playstation', 'steam', 'nintendo', 'roblox', 'discord nitro', 'ea play', 'ubisoft']],
|
||
['utilities', ['verizon', 'at t', 'att', 'comcast', 'xfinity', 'spectrum', 'tmobile', 't mobile']],
|
||
['insurance', ['insurance', 'geico', 'progressive', 'state farm', 'allstate']],
|
||
['food', ['hellofresh', 'blue apron', 'doordash', 'instacart', 'uber eats', 'grubhub', 'factor', 'hungryroot']],
|
||
['education', ['duolingo', 'masterclass', 'coursera', 'skillshare', 'audible', 'kindle unlimited', 'blinkist']],
|
||
['shopping', ['amazon prime', 'walmart plus', 'costco', 'target circle', 'chewy']],
|
||
['security', ['nordvpn', 'expressvpn', '1password', 'dashlane', 'norton', 'mcafee', 'surfshark']],
|
||
];
|
||
|
||
const AMBIGUOUS_CATALOG_TERMS = new Map([
|
||
['amazon', 'Amazon charges may be retail orders, Prime, media, Kindle, or subscriptions.'],
|
||
['amzn', 'Amazon charges may be retail orders, Prime, media, Kindle, or subscriptions.'],
|
||
['apple', 'Apple charges may be app purchases, iCloud, Apple One, media, or hardware.'],
|
||
['google', 'Google charges may be storage, YouTube, app purchases, ads, or domains.'],
|
||
['microsoft', 'Microsoft charges may be software, Xbox, cloud, or marketplace purchases.'],
|
||
['walmart', 'Walmart charges may be retail orders, delivery, Walmart+, or pharmacy purchases.'],
|
||
['target', 'Target charges may be retail orders, Circle, delivery, or pharmacy purchases.'],
|
||
['disney', 'Disney charges may be streaming, parks, retail, or bundle charges.'],
|
||
['max', 'Max is a short descriptor and can be confused with unrelated merchant text.'],
|
||
]);
|
||
|
||
// ── Catalog ───────────────────────────────────────────────────────────────────
|
||
|
||
function loadCatalog(db, userId) {
|
||
try {
|
||
const catalog = db.prepare(
|
||
'SELECT id, rank, name, category, subscription_type, domain, website, starting_monthly_usd, starting_annual_usd, price_notes FROM subscription_catalog ORDER BY rank ASC'
|
||
).all();
|
||
if (!catalog.length) return [];
|
||
|
||
// Attach bank descriptors and slang terms if the table exists (v0.95+)
|
||
let descriptors = [];
|
||
try {
|
||
descriptors = db.prepare(
|
||
'SELECT catalog_id, descriptor, descriptor_type FROM subscription_catalog_descriptors'
|
||
).all();
|
||
} catch { /* pre-v0.95 */ }
|
||
|
||
// Merge user-specific custom descriptors (v0.96+)
|
||
if (userId) {
|
||
try {
|
||
const userDescs = db.prepare(
|
||
'SELECT catalog_id, descriptor FROM user_catalog_descriptors WHERE user_id = ?'
|
||
).all(userId);
|
||
for (const d of userDescs) {
|
||
descriptors.push({ catalog_id: d.catalog_id, descriptor: d.descriptor, descriptor_type: 'user_bank' });
|
||
}
|
||
} catch { /* pre-v0.96 */ }
|
||
}
|
||
|
||
const byId = new Map(catalog.map(c => [c.id, { ...c, bankDescs: [], slangTerms: [] }]));
|
||
for (const d of descriptors) {
|
||
const entry = byId.get(d.catalog_id);
|
||
if (!entry) continue;
|
||
const normalized = normalizeMerchant(d.descriptor);
|
||
if (d.descriptor_type === 'bank' || d.descriptor_type === 'user_bank') {
|
||
entry.bankDescs.push({ value: normalized, source: d.descriptor_type });
|
||
}
|
||
else entry.slangTerms.push(normalized);
|
||
}
|
||
return [...byId.values()];
|
||
} catch {
|
||
return [];
|
||
}
|
||
}
|
||
|
||
// Build a normalized-name → subscription_type map from the full catalog so
|
||
// inferType can use all 290 known services, not just the hardcoded keyword list.
|
||
function buildCatalogTypeMap(catalog) {
|
||
const map = new Map();
|
||
for (const entry of catalog) {
|
||
if (!entry.subscription_type || entry.subscription_type === 'other') continue;
|
||
const key = normalizeCatalogName(entry.name);
|
||
if (key.length >= 3 && !map.has(key)) map.set(key, entry.subscription_type);
|
||
}
|
||
return map;
|
||
}
|
||
|
||
function compactCatalogKey(value) {
|
||
return normalizeCatalogName(value).replace(/\s+/g, '');
|
||
}
|
||
|
||
function hostFromUrl(value) {
|
||
if (!value) return '';
|
||
try {
|
||
return new URL(String(value).startsWith('http') ? String(value) : `https://${value}`).hostname;
|
||
} catch {
|
||
return String(value || '');
|
||
}
|
||
}
|
||
|
||
function catalogDomainKeys(entry) {
|
||
const keys = new Set();
|
||
const candidates = [entry.domain, hostFromUrl(entry.website)].filter(Boolean);
|
||
for (const candidate of candidates) {
|
||
const host = String(candidate).toLowerCase().replace(/^www\./, '').replace(/\/.*$/, '');
|
||
const labels = host.split('.').filter(Boolean);
|
||
if (labels.length >= 2) {
|
||
keys.add(labels.join(' '));
|
||
keys.add(labels.slice(-2).join(' '));
|
||
if (labels[0].length >= 5) keys.add(labels[0]);
|
||
}
|
||
}
|
||
return [...keys].filter(key => key.length >= 4);
|
||
}
|
||
|
||
function normalizeCatalogName(value) {
|
||
return String(value || '')
|
||
.toLowerCase()
|
||
.replace(/\+/g, ' plus ') // "Walmart+" → "walmart plus" so it only matches "walmart plus" transactions
|
||
.replace(/[^a-z0-9]+/g, ' ')
|
||
.replace(/\s+/g, ' ')
|
||
.trim();
|
||
}
|
||
|
||
// Given a normalized merchant string, find the best matching catalog entry and
|
||
// the kind of evidence that won. Identity confidence is handled separately so
|
||
// weak name/domain matches do not look as trustworthy as statement descriptors.
|
||
function lookupCatalogMatch(catalog, merchantText) {
|
||
if (!catalog.length || !merchantText) return null;
|
||
let best = null;
|
||
let bestScore = 0;
|
||
let bestMatch = null;
|
||
const normalized = normalizeMerchant(merchantText);
|
||
const compact = compactCatalogKey(merchantText);
|
||
|
||
for (const entry of catalog) {
|
||
// 1. Bank statement descriptors — normalized against our own normalizeMerchant so
|
||
// "NETFLIX.COM LOS GATOS CA" and raw payee "netflix" both reduce to comparable forms.
|
||
for (const desc of (entry.bankDescs || [])) {
|
||
const value = desc.value || desc;
|
||
if (value.length >= 4 && (normalized.includes(value) || value.includes(normalized))) {
|
||
const containment = normalized.includes(value)
|
||
? 'transaction_contains_descriptor'
|
||
: 'descriptor_contains_transaction';
|
||
const score = (desc.source === 'user_bank' ? 2200 : 2000) + value.length;
|
||
if (score > bestScore) {
|
||
best = entry;
|
||
bestScore = score;
|
||
bestMatch = {
|
||
type: desc.source === 'user_bank' ? 'user_descriptor' : 'bank_descriptor',
|
||
label: desc.source === 'user_bank' ? 'custom bank descriptor' : 'known bank descriptor',
|
||
descriptor: value,
|
||
containment,
|
||
};
|
||
}
|
||
}
|
||
}
|
||
|
||
// 2. Name / domain / slang match.
|
||
const nameKey = normalizeCatalogName(entry.name);
|
||
const nameCompact = compactCatalogKey(entry.name);
|
||
const nameScore = 1000 + nameKey.length;
|
||
if (
|
||
nameKey.length >= 3
|
||
&& (normalized.includes(nameKey) || (nameCompact.length >= 5 && compact.includes(nameCompact)))
|
||
&& nameScore > bestScore
|
||
) {
|
||
best = entry;
|
||
bestScore = nameScore;
|
||
bestMatch = { type: 'name', label: 'service name', descriptor: nameKey };
|
||
}
|
||
for (const domainKey of catalogDomainKeys(entry)) {
|
||
const domainCompact = domainKey.replace(/\s+/g, '');
|
||
const domainScore = 500 + domainKey.length;
|
||
if (
|
||
(normalized.includes(domainKey) || (domainCompact.length >= 5 && compact.includes(domainCompact)))
|
||
&& domainScore > bestScore
|
||
) {
|
||
best = entry;
|
||
bestScore = domainScore;
|
||
bestMatch = { type: 'domain', label: 'service domain', descriptor: domainKey };
|
||
}
|
||
}
|
||
for (const slang of (entry.slangTerms || [])) {
|
||
const slangCompact = slang.replace(/\s+/g, '');
|
||
const slangScore = 300 + slang.length;
|
||
if (
|
||
slang.length >= 4
|
||
&& (normalized.includes(slang) || (slangCompact.length >= 5 && compact.includes(slangCompact)))
|
||
&& slangScore > bestScore
|
||
) {
|
||
best = entry;
|
||
bestScore = slangScore;
|
||
bestMatch = { type: 'slang', label: 'known alternate name', descriptor: slang };
|
||
}
|
||
}
|
||
}
|
||
return best ? { entry: best, match: bestMatch || { type: 'unknown', label: 'catalog match' } } : null;
|
||
}
|
||
|
||
function lookupCatalog(catalog, merchantText) {
|
||
return lookupCatalogMatch(catalog, merchantText)?.entry || null;
|
||
}
|
||
|
||
// ── Helpers ───────────────────────────────────────────────────────────────────
|
||
|
||
function normalizeMerchant(value) {
|
||
return String(value || '')
|
||
.toLowerCase()
|
||
.replace(/\+/g, ' plus ') // preserve "+" so "WALMART+" matches catalog "Walmart+" → "walmart plus"
|
||
.replace(/&/g, '') // "&" joins words — "AT&T" → "att", not "at t"
|
||
.replace(/['’]/g, '') // collapse apostrophes — "Sam's Club" → "sams club", "McDonald's" → "mcdonalds"
|
||
.replace(/[^a-z0-9\s]/g, ' ')
|
||
.replace(/\b(pos|debit|card|payment|purchase|recurring|online|inc|llc|co|www)\b/g, ' ')
|
||
.replace(/\s+/g, ' ')
|
||
.trim();
|
||
}
|
||
|
||
function titleCase(value) {
|
||
return String(value || 'Subscription')
|
||
.split(/\s+/)
|
||
.filter(Boolean)
|
||
.map(word => word.charAt(0).toUpperCase() + word.slice(1))
|
||
.join(' ');
|
||
}
|
||
|
||
function inferType(merchantText, catalogEntry, catalogTypeMap = null) {
|
||
if (catalogEntry?.subscription_type) return catalogEntry.subscription_type;
|
||
const haystack = normalizeMerchant(merchantText);
|
||
if (catalogTypeMap) {
|
||
for (const [nameKey, type] of catalogTypeMap.entries()) {
|
||
if (haystack.includes(nameKey)) return type;
|
||
}
|
||
}
|
||
for (const [type, words] of TYPE_KEYWORDS) {
|
||
if (words.some(word => haystack.includes(word))) return type;
|
||
}
|
||
return 'other';
|
||
}
|
||
|
||
function catalogMatchPayload(catalogEntry) {
|
||
return catalogEntry ? {
|
||
id: catalogEntry.id,
|
||
name: catalogEntry.name,
|
||
category: catalogEntry.category,
|
||
subscription_type: catalogEntry.subscription_type || 'other',
|
||
website: catalogEntry.website || null,
|
||
starting_monthly_usd: catalogEntry.starting_monthly_usd ?? null,
|
||
starting_annual_usd: catalogEntry.starting_annual_usd ?? null,
|
||
price_notes: catalogEntry.price_notes || null,
|
||
} : null;
|
||
}
|
||
|
||
function identityEvidence(match) {
|
||
const type = match?.type || 'unknown';
|
||
const table = {
|
||
user_descriptor: { score: 84, label: 'Matched your custom bank descriptor' },
|
||
bank_descriptor: { score: 82, label: 'Matched a known bank descriptor' },
|
||
name: { score: 74, label: 'Matched the service name' },
|
||
domain: { score: 70, label: 'Matched the service domain' },
|
||
slang: { score: 66, label: 'Matched a known alternate name' },
|
||
unknown: { score: 60, label: 'Matched the service catalog' },
|
||
};
|
||
return {
|
||
type,
|
||
descriptor: match?.descriptor || null,
|
||
containment: match?.containment || null,
|
||
...(table[type] || table.unknown),
|
||
};
|
||
}
|
||
|
||
function priceClose(amount, expected) {
|
||
if (!amount || !expected) return null;
|
||
const delta = Math.abs(amount - expected);
|
||
const pct = delta / expected;
|
||
return { delta, pct };
|
||
}
|
||
|
||
function amountEvidence(amount, cycleType, catalogEntry) {
|
||
const monthly = Number(catalogEntry?.starting_monthly_usd || 0);
|
||
const annual = Number(catalogEntry?.starting_annual_usd || 0) || (monthly ? monthly * 12 : 0);
|
||
if (!amount || (!monthly && !annual)) {
|
||
return { score: 0, label: null, match: 'unknown', inferred_cycle_type: null };
|
||
}
|
||
|
||
const monthlyClose = priceClose(amount, monthly);
|
||
const annualClose = priceClose(amount, annual);
|
||
const annualLike = annual && annualClose && (annualClose.delta <= 2 || annualClose.pct <= 0.12);
|
||
const monthlyLike = monthly && monthlyClose && (monthlyClose.delta <= 1 || monthlyClose.pct <= 0.08);
|
||
const plausibleMonthly = monthly && amount >= monthly * 0.70 && amount <= Math.max(monthly * 4, monthly + 25);
|
||
const plausibleAnnual = annual && amount >= annual * 0.70 && amount <= annual * 1.35;
|
||
|
||
if (cycleType === 'annual' || annualLike || (!monthlyLike && plausibleAnnual && amount >= monthly * 8)) {
|
||
if (annualLike) {
|
||
return {
|
||
score: 13,
|
||
label: `Amount aligns with catalog annual pricing near $${annual.toFixed(2)}`,
|
||
match: 'annual_close',
|
||
inferred_cycle_type: 'annual',
|
||
};
|
||
}
|
||
if (plausibleAnnual) {
|
||
return {
|
||
score: 9,
|
||
label: `Amount is plausible for annual pricing near $${annual.toFixed(2)}`,
|
||
match: 'annual_plausible',
|
||
inferred_cycle_type: 'annual',
|
||
};
|
||
}
|
||
}
|
||
|
||
if (monthlyLike) {
|
||
return {
|
||
score: 12,
|
||
label: `Amount aligns with catalog pricing from $${monthly.toFixed(2)}/mo`,
|
||
match: 'monthly_close',
|
||
inferred_cycle_type: 'monthly',
|
||
};
|
||
}
|
||
if (plausibleMonthly) {
|
||
return {
|
||
score: 8,
|
||
label: `Amount is plausible for catalog pricing from $${monthly.toFixed(2)}/mo`,
|
||
match: 'monthly_plausible',
|
||
inferred_cycle_type: null,
|
||
};
|
||
}
|
||
return {
|
||
score: -10,
|
||
label: `Amount is unusual for catalog pricing from ${monthly ? `$${monthly.toFixed(2)}/mo` : `$${annual.toFixed(2)}/yr`}`,
|
||
match: 'unusual',
|
||
inferred_cycle_type: null,
|
||
};
|
||
}
|
||
|
||
function cadenceEvidence(sorted, cycleType, avgGap, maxDelta, averageAmount) {
|
||
if (sorted.length < 2) {
|
||
return {
|
||
score: 0,
|
||
label: 'One matching bank transaction',
|
||
stable: true,
|
||
recurring: false,
|
||
};
|
||
}
|
||
const stable = maxDelta <= Math.max(1, averageAmount * 0.08);
|
||
const score = 8 + Math.min(15, sorted.length * 3) + (stable ? 8 : 0) + (cycleType !== 'weekly' ? 8 : 0);
|
||
return {
|
||
score,
|
||
label: `${sorted.length} similar charges about ${Math.round(avgGap)} days apart`,
|
||
stable,
|
||
recurring: true,
|
||
};
|
||
}
|
||
|
||
function catalogTextForAmbiguity(merchant, catalogEntry, identityInfo) {
|
||
return normalizeMerchant([
|
||
merchant,
|
||
catalogEntry?.name,
|
||
catalogEntry?.domain,
|
||
catalogEntry?.website,
|
||
identityInfo?.descriptor,
|
||
].filter(Boolean).join(' '));
|
||
}
|
||
|
||
function ambiguityEvidence({ merchant, catalogEntry, identityInfo, amountInfo, cadenceInfo }) {
|
||
const identityType = identityInfo?.type || 'unknown';
|
||
const compactMerchant = normalizeMerchant(merchant).replace(/\s+/g, '');
|
||
const descriptorContainsShortTransaction = identityInfo?.containment === 'descriptor_contains_transaction'
|
||
&& compactMerchant.length > 0
|
||
&& compactMerchant.length <= 4;
|
||
const haystack = catalogTextForAmbiguity(merchant, catalogEntry, identityInfo);
|
||
const tokens = new Set(haystack.split(/\s+/).filter(Boolean));
|
||
const hit = [...AMBIGUOUS_CATALOG_TERMS.entries()].find(([term]) => tokens.has(term));
|
||
const descriptorTokens = new Set(normalizeMerchant(identityInfo?.descriptor).split(/\s+/).filter(Boolean));
|
||
const genericBroadBankDescriptor = identityType === 'bank_descriptor'
|
||
&& !!hit
|
||
&& ['bill', 'billing', 'payment', 'payments', 'store', 'mktplace', 'marketplace'].some(term => descriptorTokens.has(term));
|
||
const weakIdentity = !['user_descriptor', 'bank_descriptor'].includes(identityType)
|
||
|| descriptorContainsShortTransaction
|
||
|| genericBroadBankDescriptor;
|
||
if (!weakIdentity) {
|
||
return { ambiguous: false, penalty: 0, label: null, reasons: [] };
|
||
}
|
||
|
||
const compactDescriptor = String(identityInfo?.descriptor || '').replace(/\s+/g, '');
|
||
const shortDescriptor = compactDescriptor.length > 0 && compactDescriptor.length <= 4;
|
||
|
||
if (!hit && !shortDescriptor) {
|
||
return { ambiguous: false, penalty: 0, label: null, reasons: [] };
|
||
}
|
||
|
||
const recurringStrong = !!cadenceInfo?.recurring && amountInfo?.score >= 8;
|
||
const penalty = recurringStrong ? 6 : 16;
|
||
const reasons = [];
|
||
if (hit) reasons.push(hit[1]);
|
||
if (genericBroadBankDescriptor) reasons.push('The bank descriptor is a generic billing label for a broad merchant.');
|
||
if (shortDescriptor) reasons.push('The matched service text is very short, so false positives are more likely.');
|
||
if (!cadenceInfo?.recurring) reasons.push('Only one bank transaction supports this recommendation.');
|
||
if (amountInfo?.match === 'unusual') reasons.push('The amount is outside the catalog price range.');
|
||
|
||
return {
|
||
ambiguous: true,
|
||
penalty,
|
||
label: 'Broad match - review before tracking',
|
||
reasons: Array.from(new Set(reasons)),
|
||
};
|
||
}
|
||
|
||
function feedbackEvidence({ feedback, merchant, catalogEntry }) {
|
||
const catalogKey = catalogEntry?.id ? String(catalogEntry.id) : null;
|
||
const merchantKey = normalizeMerchant(merchant);
|
||
const rows = [
|
||
...(catalogKey ? (feedback.byCatalog.get(catalogKey) || []) : []),
|
||
...(merchantKey ? (feedback.byMerchant.get(merchantKey) || []) : []),
|
||
];
|
||
if (!rows.length) return { score: 0, label: null, positive_count: 0, negative_count: 0 };
|
||
|
||
let positive = 0;
|
||
let negative = 0;
|
||
for (const row of rows) {
|
||
if (['accept_track_new', 'link_existing_bill', 'catalog_relink', 'add_descriptor'].includes(row.action)) positive++;
|
||
if (['decline', 'catalog_unlink', 'delete_descriptor'].includes(row.action)) negative++;
|
||
}
|
||
|
||
const score = Math.max(-18, Math.min(8, positive * 3 - negative * 8));
|
||
const label = score > 0
|
||
? 'Boosted by your past subscription matching choices'
|
||
: score < 0
|
||
? 'Reduced by your past subscription matching choices'
|
||
: null;
|
||
return { score, label, positive_count: positive, negative_count: negative };
|
||
}
|
||
|
||
function scoreKnownServiceRecommendation({ match, merchant, catalogEntry, amountInfo, cadenceInfo, feedbackInfo = null }) {
|
||
const identity = identityEvidence(match);
|
||
const ambiguity = ambiguityEvidence({ merchant, catalogEntry, identityInfo: identity, amountInfo, cadenceInfo });
|
||
const confidence = Math.min(99, Math.max(0,
|
||
identity.score + amountInfo.score + cadenceInfo.score - ambiguity.penalty + (feedbackInfo?.score || 0)
|
||
));
|
||
return { confidence, identity, ambiguity };
|
||
}
|
||
|
||
function monthlyEquivalent(amount, cycleType, billingCycle) {
|
||
const key = String(cycleType || billingCycle || 'monthly').toLowerCase();
|
||
const fallback = String(billingCycle || '').toLowerCase() === 'quarterly'
|
||
? 'quarterly'
|
||
: String(billingCycle || '').toLowerCase() === 'annually'
|
||
? 'annual'
|
||
: key;
|
||
const factor = MONTHLY_FACTORS[key] ?? MONTHLY_FACTORS[fallback] ?? 1;
|
||
return mulMoney(Number(amount || 0), factor);
|
||
}
|
||
|
||
function nextDueDate(bill, now = new Date()) {
|
||
const dueDay = Math.min(Math.max(Number(bill.due_day) || 1, 1), 31);
|
||
const cycle = String(bill.cycle_type || bill.billing_cycle || 'monthly').toLowerCase();
|
||
let date = new Date(now.getFullYear(), now.getMonth(), dueDay);
|
||
if (date < new Date(now.getFullYear(), now.getMonth(), now.getDate())) {
|
||
date = new Date(now.getFullYear(), now.getMonth() + 1, dueDay);
|
||
}
|
||
if (cycle === 'quarterly' || cycle === 'annual') {
|
||
const startMonth = Math.min(Math.max(Number(bill.cycle_day) || 1, 1), 12) - 1;
|
||
const step = cycle === 'quarterly' ? 3 : 12;
|
||
date = new Date(now.getFullYear(), startMonth, dueDay);
|
||
while (date < new Date(now.getFullYear(), now.getMonth(), now.getDate())) {
|
||
date = new Date(date.getFullYear(), date.getMonth() + step, dueDay);
|
||
}
|
||
}
|
||
return localDateString(date);
|
||
}
|
||
|
||
function decorateSubscription(bill) {
|
||
const expectedAmount = fromCents(bill.expected_amount);
|
||
const monthly = monthlyEquivalent(expectedAmount, bill.cycle_type, bill.billing_cycle);
|
||
return {
|
||
...bill,
|
||
expected_amount: expectedAmount,
|
||
current_balance: fromCents(bill.current_balance),
|
||
minimum_payment: fromCents(bill.minimum_payment),
|
||
is_subscription: !!bill.is_subscription,
|
||
active: !!bill.active,
|
||
monthly_equivalent: monthly,
|
||
yearly_equivalent: mulMoney(monthly, 12),
|
||
next_due_date: nextDueDate(bill),
|
||
subscription_type: bill.subscription_type || inferType(`${bill.name} ${bill.category_name || ''}`, null),
|
||
};
|
||
}
|
||
|
||
function getSubscriptions(db, userId) {
|
||
return db.prepare(`
|
||
SELECT b.*, b.catalog_id, c.name AS category_name,
|
||
CASE WHEN EXISTS(
|
||
SELECT 1 FROM bill_merchant_rules WHERE bill_id = b.id AND user_id = b.user_id
|
||
) THEN 1 ELSE 0 END AS has_merchant_rule,
|
||
CASE WHEN EXISTS(
|
||
SELECT 1 FROM transactions WHERE matched_bill_id = b.id AND match_status = 'matched'
|
||
) THEN 1 ELSE 0 END AS has_linked_transactions
|
||
FROM bills b
|
||
LEFT JOIN categories c ON c.id = b.category_id AND c.user_id = b.user_id AND c.deleted_at IS NULL
|
||
WHERE b.user_id = ?
|
||
AND b.deleted_at IS NULL
|
||
AND b.is_subscription = 1
|
||
ORDER BY b.active DESC,
|
||
CASE WHEN b.sort_order IS NULL THEN 1 ELSE 0 END,
|
||
b.sort_order ASC,
|
||
b.due_day ASC,
|
||
b.name COLLATE NOCASE ASC
|
||
`).all(userId).map(decorateSubscription);
|
||
}
|
||
|
||
function getSubscriptionSummary(subscriptions) {
|
||
const active = subscriptions.filter(item => item.active);
|
||
const monthlyTotal = sumMoney(active, item => item.monthly_equivalent);
|
||
const typeTotals = new Map();
|
||
for (const item of active) {
|
||
const type = item.subscription_type || 'other';
|
||
typeTotals.set(type, (typeTotals.get(type) || 0) + Number(item.monthly_equivalent || 0));
|
||
}
|
||
const topType = [...typeTotals.entries()].sort((a, b) => b[1] - a[1])[0] || null;
|
||
return {
|
||
active_count: active.length,
|
||
paused_count: subscriptions.length - active.length,
|
||
monthly_total: roundMoney(monthlyTotal),
|
||
yearly_total: mulMoney(monthlyTotal, 12),
|
||
top_type: topType ? { type: topType[0], monthly_total: roundMoney(topType[1]) } : null,
|
||
};
|
||
}
|
||
|
||
function existingBillNames(db, userId) {
|
||
return db.prepare('SELECT name FROM bills WHERE user_id = ? AND deleted_at IS NULL')
|
||
.all(userId)
|
||
.map(row => normalizeMerchant(row.name))
|
||
.filter(Boolean);
|
||
}
|
||
|
||
function dollarsFromTransactionAmount(amount) {
|
||
return roundMoney(Math.abs(Number(amount || 0)) / 100);
|
||
}
|
||
|
||
function recommendationAccountLabel(item) {
|
||
const accountName = item.account_name || '';
|
||
const orgName = item.account_org_name || '';
|
||
if (accountName && orgName && accountName !== orgName) return `${orgName} · ${accountName}`;
|
||
return accountName || orgName || item.data_source_name || '';
|
||
}
|
||
|
||
function loadRecommendationFeedback(db, userId) {
|
||
const empty = { byCatalog: new Map(), byMerchant: new Map() };
|
||
try {
|
||
const rows = db.prepare(`
|
||
SELECT catalog_id, merchant, action, confidence, descriptor, created_at
|
||
FROM subscription_recommendation_feedback
|
||
WHERE user_id = ?
|
||
ORDER BY created_at DESC
|
||
LIMIT 500
|
||
`).all(userId);
|
||
for (const row of rows) {
|
||
if (row.catalog_id) {
|
||
const key = String(row.catalog_id);
|
||
if (!empty.byCatalog.has(key)) empty.byCatalog.set(key, []);
|
||
empty.byCatalog.get(key).push(row);
|
||
}
|
||
const merchant = normalizeMerchant(row.merchant);
|
||
if (merchant) {
|
||
if (!empty.byMerchant.has(merchant)) empty.byMerchant.set(merchant, []);
|
||
empty.byMerchant.get(merchant).push(row);
|
||
}
|
||
}
|
||
} catch { /* pre-v0.97 */ }
|
||
return empty;
|
||
}
|
||
|
||
function recordSubscriptionFeedback(db, userId, payload = {}) {
|
||
const action = String(payload.action || '').trim();
|
||
if (!action) return;
|
||
try {
|
||
const catalogId = Number(payload.catalog_id);
|
||
const billId = Number(payload.bill_id);
|
||
const metadata = payload.metadata === undefined ? null : JSON.stringify(payload.metadata);
|
||
db.prepare(`
|
||
INSERT INTO subscription_recommendation_feedback
|
||
(user_id, catalog_id, bill_id, merchant, action, confidence, descriptor, metadata_json)
|
||
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
|
||
`).run(
|
||
userId,
|
||
Number.isInteger(catalogId) && catalogId > 0 ? catalogId : null,
|
||
Number.isInteger(billId) && billId > 0 ? billId : null,
|
||
payload.merchant ? normalizeMerchant(payload.merchant) : null,
|
||
action,
|
||
Number.isInteger(Number(payload.confidence)) ? Number(payload.confidence) : null,
|
||
payload.descriptor ? String(payload.descriptor).slice(0, 100) : null,
|
||
metadata,
|
||
);
|
||
} catch { /* pre-v0.97 */ }
|
||
}
|
||
|
||
function existingBillsForRecommendation(db, userId) {
|
||
return db.prepare(`
|
||
SELECT b.id, b.name, b.expected_amount, b.due_day, b.cycle_type, b.billing_cycle,
|
||
b.is_subscription, b.active, b.catalog_id, c.name AS category_name,
|
||
CASE WHEN EXISTS(
|
||
SELECT 1 FROM bill_merchant_rules r
|
||
WHERE r.bill_id = b.id AND r.user_id = b.user_id
|
||
) THEN 1 ELSE 0 END AS has_merchant_rule
|
||
FROM bills b
|
||
LEFT JOIN categories c ON c.id = b.category_id AND c.user_id = b.user_id AND c.deleted_at IS NULL
|
||
WHERE b.user_id = ?
|
||
AND b.deleted_at IS NULL
|
||
`).all(userId);
|
||
}
|
||
|
||
function dayDelta(a, b) {
|
||
const left = Math.min(Math.max(Number(a) || 1, 1), 31);
|
||
const right = Math.min(Math.max(Number(b) || 1, 1), 31);
|
||
return Math.abs(left - right);
|
||
}
|
||
|
||
function existingBillMatch(existingBills, { merchant, catalogEntry, averageAmount, lastDate }) {
|
||
const merchantKey = normalizeCatalogName(merchant);
|
||
const catalogKey = normalizeCatalogName(catalogEntry?.name);
|
||
const dueDay = Number(String(lastDate || '').slice(8, 10)) || 1;
|
||
let best = null;
|
||
|
||
for (const bill of existingBills) {
|
||
const billKey = normalizeCatalogName(bill.name);
|
||
if (!billKey) continue;
|
||
const reasons = [];
|
||
let score = 0;
|
||
|
||
if (catalogEntry?.id && Number(bill.catalog_id) === Number(catalogEntry.id)) {
|
||
score += 90;
|
||
reasons.push('Bill is already linked to this catalog service');
|
||
} else if (catalogKey && billKey === catalogKey) {
|
||
score += 72;
|
||
reasons.push('Bill name exactly matches the known service');
|
||
} else if (merchantKey && billKey === merchantKey) {
|
||
score += 66;
|
||
reasons.push('Bill name exactly matches the bank merchant');
|
||
} else if (catalogKey && (billKey.includes(catalogKey) || catalogKey.includes(billKey))) {
|
||
score += 48;
|
||
reasons.push('Bill name closely matches the known service');
|
||
} else if (merchantKey && merchantKey.length >= 4 && (billKey.includes(merchantKey) || merchantKey.includes(billKey))) {
|
||
score += 42;
|
||
reasons.push('Bill name closely matches the bank merchant');
|
||
}
|
||
|
||
if (score === 0) continue;
|
||
|
||
const expected = fromCents(bill.expected_amount) || 0;
|
||
const amountDelta = expected ? Math.abs(expected - averageAmount) : null;
|
||
if (amountDelta !== null) {
|
||
const pct = expected ? amountDelta / expected : 1;
|
||
if (amountDelta <= 1 || pct <= 0.08) {
|
||
score += 20;
|
||
reasons.push('Bill amount matches this charge');
|
||
} else if (amountDelta <= 5 || pct <= 0.20) {
|
||
score += 8;
|
||
reasons.push('Bill amount is near this charge');
|
||
}
|
||
}
|
||
|
||
const dueDelta = dayDelta(bill.due_day, dueDay);
|
||
if (dueDelta === 0) {
|
||
score += 12;
|
||
reasons.push('Bill due day matches the latest charge date');
|
||
} else if (dueDelta <= 2) {
|
||
score += 9;
|
||
reasons.push('Bill due day is close to the latest charge date');
|
||
} else if (dueDelta <= 5) {
|
||
score += 5;
|
||
reasons.push('Bill due day is near the latest charge date');
|
||
}
|
||
|
||
if (bill.is_subscription) score += 5;
|
||
if (bill.has_merchant_rule) score += 3;
|
||
|
||
if (score >= 65 && (!best || score > best.score)) {
|
||
best = {
|
||
id: bill.id,
|
||
name: bill.name,
|
||
expected_amount: expected || null,
|
||
due_day: bill.due_day || null,
|
||
active: !!bill.active,
|
||
is_subscription: !!bill.is_subscription,
|
||
catalog_id: bill.catalog_id || null,
|
||
category_name: bill.category_name || null,
|
||
has_merchant_rule: !!bill.has_merchant_rule,
|
||
score,
|
||
strong: score >= 90 || (amountDelta !== null && amountDelta <= 1 && dueDelta <= 2),
|
||
amount_delta: amountDelta === null ? null : roundMoney(amountDelta),
|
||
due_day_delta: dueDelta,
|
||
reasons,
|
||
};
|
||
}
|
||
}
|
||
|
||
return best;
|
||
}
|
||
|
||
// ── Decline store ─────────────────────────────────────────────────────────────
|
||
|
||
function getDeclinedKeys(db, userId) {
|
||
try {
|
||
const rows = db.prepare('SELECT decline_key FROM declined_subscription_hints WHERE user_id = ?').all(userId);
|
||
return new Set(rows.map(r => r.decline_key));
|
||
} catch {
|
||
return new Set();
|
||
}
|
||
}
|
||
|
||
function declineRecommendation(db, userId, declineKey) {
|
||
db.prepare(`
|
||
INSERT INTO declined_subscription_hints (user_id, decline_key)
|
||
VALUES (?, ?)
|
||
ON CONFLICT(user_id, decline_key) DO NOTHING
|
||
`).run(userId, declineKey);
|
||
}
|
||
|
||
// ── Recommendations ───────────────────────────────────────────────────────────
|
||
|
||
function getSubscriptionRecommendations(db, userId) {
|
||
const catalog = loadCatalog(db, userId);
|
||
const catalogTypeMap = buildCatalogTypeMap(catalog);
|
||
const existingBills = existingBillsForRecommendation(db, userId);
|
||
const feedback = loadRecommendationFeedback(db, userId);
|
||
const declined = getDeclinedKeys(db, userId);
|
||
|
||
const rows = db.prepare(`
|
||
SELECT
|
||
t.id, t.amount, t.currency, t.description, t.payee, t.memo, t.category,
|
||
COALESCE(t.posted_date, substr(t.transacted_at, 1, 10)) AS tx_date,
|
||
ds.provider AS data_source_provider,
|
||
ds.name AS data_source_name,
|
||
fa.name AS account_name,
|
||
fa.org_name AS account_org_name
|
||
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
|
||
WHERE t.user_id = ?
|
||
AND t.ignored = 0
|
||
AND t.match_status = 'unmatched'
|
||
AND t.amount < 0
|
||
AND (t.account_id IS NULL OR fa.id IS NULL OR fa.monitored = 1)
|
||
AND COALESCE(t.posted_date, substr(t.transacted_at, 1, 10)) >= date('now', '-420 days')
|
||
ORDER BY tx_date ASC
|
||
`).all(userId);
|
||
|
||
// Group by merchant + amount bucket — consistent amounts are the foundation of
|
||
// subscription detection. Catalog lookup names the service and boosts confidence
|
||
// but does not change the grouping; deduplication at the end ensures one entry
|
||
// per known service.
|
||
const groups = new Map();
|
||
for (const tx of rows) {
|
||
const merchant = normalizeMerchant(tx.payee || tx.description || tx.memo);
|
||
if (!merchant || merchant.length < 3) continue;
|
||
if (SKIP_MERCHANT_RE.test(merchant)) continue;
|
||
const amount = dollarsFromTransactionAmount(tx.amount);
|
||
if (amount < 1) continue;
|
||
const key = `${merchant}:${Math.round(amount)}`;
|
||
if (!groups.has(key)) {
|
||
groups.set(key, { merchant, items: [], catalogMatch: null });
|
||
}
|
||
const group = groups.get(key);
|
||
group.items.push({ ...tx, amount_dollars: amount });
|
||
if (!group.catalogMatch) group.catalogMatch = lookupCatalogMatch(catalog, merchant);
|
||
}
|
||
|
||
const recommendations = [];
|
||
|
||
for (const group of groups.values()) {
|
||
const { merchant } = group;
|
||
const catalogEntry = group.catalogMatch?.entry || null;
|
||
const catalogIdentityMatch = group.catalogMatch?.match || null;
|
||
if (!catalogEntry) continue;
|
||
const declineKey = catalogEntry ? `catalog:${catalogEntry.id}` : `merchant:${merchant}`;
|
||
|
||
if (declined.has(declineKey)) continue;
|
||
|
||
const sorted = group.items
|
||
.filter(item => item.tx_date)
|
||
.sort((a, b) => String(a.tx_date).localeCompare(String(b.tx_date)));
|
||
if (sorted.length === 0) continue;
|
||
|
||
const averageAmount = sumMoney(sorted, item => item.amount_dollars) / sorted.length;
|
||
const maxDelta = sorted.length > 1
|
||
? Math.max(...sorted.map(item => Math.abs(item.amount_dollars - averageAmount)))
|
||
: 0;
|
||
const last = sorted[sorted.length - 1];
|
||
|
||
// Tier 1: known-service match with 1 occurrence. Exact bank descriptors can
|
||
// still be 90+, but weaker name/domain hits need recurrence or stronger amount
|
||
// evidence before they appear as recommendations.
|
||
if (catalogEntry && sorted.length === 1) {
|
||
let cycleType = 'monthly';
|
||
let amountInfo = amountEvidence(averageAmount, cycleType, catalogEntry);
|
||
if (amountInfo.inferred_cycle_type) {
|
||
cycleType = amountInfo.inferred_cycle_type;
|
||
amountInfo = amountEvidence(averageAmount, cycleType, catalogEntry);
|
||
}
|
||
const cadenceInfo = cadenceEvidence(sorted, cycleType, 30, maxDelta, averageAmount);
|
||
const feedbackInfo = feedbackEvidence({ feedback, merchant, catalogEntry });
|
||
const scored = scoreKnownServiceRecommendation({
|
||
match: catalogIdentityMatch, merchant, catalogEntry, amountInfo, cadenceInfo, feedbackInfo,
|
||
});
|
||
if (scored.confidence < 90) continue;
|
||
const billMatch = existingBillMatch(existingBills, {
|
||
merchant, catalogEntry, averageAmount, lastDate: last.tx_date,
|
||
});
|
||
recommendations.push(buildRecommendation({
|
||
merchant, catalogEntry, sorted, averageAmount, maxDelta, last,
|
||
cycleType, avgGap: 30, confidence: scored.confidence, tier: 'known_service',
|
||
declineKey, catalogTypeMap, identityInfo: scored.identity, amountInfo, cadenceInfo,
|
||
ambiguityInfo: scored.ambiguity, existingBillMatch: billMatch, feedbackInfo,
|
||
}));
|
||
continue;
|
||
}
|
||
|
||
if (sorted.length < 2) continue;
|
||
|
||
const gaps = [];
|
||
for (let i = 1; i < sorted.length; i++) {
|
||
gaps.push(Math.round(
|
||
(new Date(`${sorted[i].tx_date}T00:00:00`) - new Date(`${sorted[i - 1].tx_date}T00:00:00`)) / 86400000
|
||
));
|
||
}
|
||
const avgGap = gaps.reduce((sum, g) => sum + g, 0) / gaps.length;
|
||
const cycleType = avgGap >= 320 ? 'annual'
|
||
: avgGap >= 75 ? 'quarterly'
|
||
: avgGap >= 10 && avgGap <= 18 ? 'biweekly'
|
||
: avgGap <= 9 ? 'weekly'
|
||
: 'monthly';
|
||
|
||
if (cycleType === 'monthly' && (avgGap < 24 || avgGap > 38)) continue;
|
||
if (cycleType === 'quarterly' && (avgGap < 75 || avgGap > 105)) continue;
|
||
if (cycleType === 'weekly') continue;
|
||
if (maxDelta > Math.max(3, averageAmount * 0.18)) continue;
|
||
|
||
const amountInfo = amountEvidence(averageAmount, cycleType, catalogEntry);
|
||
const cadenceInfo = cadenceEvidence(sorted, cycleType, avgGap, maxDelta, averageAmount);
|
||
const feedbackInfo = feedbackEvidence({ feedback, merchant, catalogEntry });
|
||
const scored = scoreKnownServiceRecommendation({
|
||
match: catalogIdentityMatch, merchant, catalogEntry, amountInfo, cadenceInfo, feedbackInfo,
|
||
});
|
||
if (scored.confidence < 90) continue;
|
||
const billMatch = existingBillMatch(existingBills, {
|
||
merchant, catalogEntry, averageAmount, lastDate: last.tx_date,
|
||
});
|
||
|
||
recommendations.push(buildRecommendation({
|
||
merchant, catalogEntry, sorted, averageAmount, maxDelta, last,
|
||
cycleType, avgGap, confidence: scored.confidence, tier: 'confirmed',
|
||
declineKey, catalogTypeMap, identityInfo: scored.identity, amountInfo, cadenceInfo,
|
||
ambiguityInfo: scored.ambiguity, existingBillMatch: billMatch, feedbackInfo,
|
||
}));
|
||
}
|
||
|
||
// Deduplicate by catalog entry — if multiple amount buckets matched the same
|
||
// known service, keep only the highest-confidence one.
|
||
const seen = new Map();
|
||
const deduped = [];
|
||
for (const rec of recommendations.sort((a, b) => (
|
||
Number(!!b.existing_bill_match?.strong) - Number(!!a.existing_bill_match?.strong)
|
||
|| Number(!!b.existing_bill_match) - Number(!!a.existing_bill_match)
|
||
|| b.confidence - a.confidence
|
||
|| b.occurrence_count - a.occurrence_count
|
||
))) {
|
||
const key = rec.catalog_match ? `catalog:${rec.catalog_match.id}` : `merchant:${rec.merchant}`;
|
||
if (!seen.has(key)) {
|
||
seen.set(key, true);
|
||
deduped.push(rec);
|
||
}
|
||
}
|
||
return deduped.slice(0, 20);
|
||
}
|
||
|
||
function buildRecommendation({ merchant, catalogEntry, sorted, averageAmount, maxDelta, last, cycleType, avgGap, confidence, tier, declineKey, catalogTypeMap, identityInfo = null, amountInfo = null, cadenceInfo = null, ambiguityInfo = null, existingBillMatch = null, feedbackInfo = null }) {
|
||
const name = catalogEntry ? catalogEntry.name : titleCase(merchant);
|
||
const subscriptionType = inferType(merchant, catalogEntry, catalogTypeMap);
|
||
const accounts = Array.from(new Set(sorted
|
||
.map(recommendationAccountLabel)
|
||
.filter(Boolean)));
|
||
|
||
const reasons = [];
|
||
if (catalogEntry) reasons.push(`Matches known service: ${catalogEntry.name}`);
|
||
if (identityInfo?.label) reasons.push(identityInfo.label);
|
||
if (amountInfo?.label) reasons.push(amountInfo.label);
|
||
if (cadenceInfo?.recurring && cadenceInfo.label) reasons.push(cadenceInfo.label);
|
||
if (ambiguityInfo?.ambiguous && ambiguityInfo.label) reasons.push(ambiguityInfo.label);
|
||
if (feedbackInfo?.label) reasons.push(feedbackInfo.label);
|
||
if (existingBillMatch) reasons.push(`Best action: link to existing bill "${existingBillMatch.name}"`);
|
||
reasons.push(`${last.currency || 'USD'} ${averageAmount.toFixed(2)} average`);
|
||
|
||
const recommendedAction = existingBillMatch ? 'link_existing_bill' : 'track_new';
|
||
return {
|
||
id: Buffer.from(`${merchant}:${Math.round(averageAmount)}:${last.tx_date}`).toString('base64url'),
|
||
name,
|
||
subscription_type: subscriptionType,
|
||
expected_amount: roundMoney(averageAmount),
|
||
monthly_equivalent: monthlyEquivalent(averageAmount, cycleType, cycleType),
|
||
cycle_type: cycleType,
|
||
billing_cycle: billingCycleForCycleType(cycleType),
|
||
due_day: Number(String(last.tx_date).slice(8, 10)) || 1,
|
||
last_seen_date: last.tx_date,
|
||
occurrence_count: sorted.length,
|
||
confidence,
|
||
tier,
|
||
recommended_action: recommendedAction,
|
||
action_priority: existingBillMatch?.strong ? 'link_strong' : existingBillMatch ? 'link_possible' : 'track_new',
|
||
existing_bill_match: existingBillMatch,
|
||
catalog_match: catalogMatchPayload(catalogEntry),
|
||
evidence: {
|
||
identity: identityInfo,
|
||
amount: amountInfo ? {
|
||
match: amountInfo.match,
|
||
label: amountInfo.label,
|
||
score: amountInfo.score,
|
||
} : null,
|
||
cadence: cadenceInfo ? {
|
||
recurring: cadenceInfo.recurring,
|
||
stable: cadenceInfo.stable,
|
||
label: cadenceInfo.label,
|
||
score: cadenceInfo.score,
|
||
} : null,
|
||
amount_range: sorted.length > 1 ? {
|
||
min: Math.min(...sorted.map(item => item.amount_dollars)),
|
||
max: Math.max(...sorted.map(item => item.amount_dollars)),
|
||
max_delta: roundMoney(maxDelta),
|
||
} : null,
|
||
ambiguity: ambiguityInfo ? {
|
||
ambiguous: !!ambiguityInfo.ambiguous,
|
||
label: ambiguityInfo.label,
|
||
reasons: ambiguityInfo.reasons || [],
|
||
penalty: ambiguityInfo.penalty || 0,
|
||
} : { ambiguous: false, label: null, reasons: [], penalty: 0 },
|
||
feedback: feedbackInfo ? {
|
||
score: feedbackInfo.score || 0,
|
||
label: feedbackInfo.label,
|
||
positive_count: feedbackInfo.positive_count || 0,
|
||
negative_count: feedbackInfo.negative_count || 0,
|
||
} : { score: 0, label: null, positive_count: 0, negative_count: 0 },
|
||
},
|
||
transaction_ids: sorted.map(item => item.id),
|
||
transactions: sorted.map(item => ({
|
||
id: item.id,
|
||
date: item.tx_date,
|
||
amount: item.amount_dollars,
|
||
currency: item.currency || 'USD',
|
||
payee: item.payee || null,
|
||
description: item.description || null,
|
||
memo: item.memo || null,
|
||
account: recommendationAccountLabel(item) || null,
|
||
})),
|
||
merchant,
|
||
decline_key: declineKey,
|
||
source: last.data_source_name || 'Transaction history',
|
||
accounts,
|
||
reasons,
|
||
};
|
||
}
|
||
|
||
function searchSubscriptionTransactions(db, userId, query = {}) {
|
||
const q = String(query.q || '').trim();
|
||
if (q.length < 2) return [];
|
||
const limit = Math.max(1, Math.min(parseInt(query.limit || '50', 10) || 50, 100));
|
||
const like = `%${q}%`;
|
||
const catalog = loadCatalog(db, userId);
|
||
|
||
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.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
|
||
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
|
||
WHERE t.user_id = ?
|
||
AND t.ignored = 0
|
||
AND t.amount < 0
|
||
AND (t.account_id IS NULL OR fa.id IS NULL OR fa.monitored = 1)
|
||
AND (t.description LIKE ? OR t.payee LIKE ? OR t.memo LIKE ? OR t.category LIKE ?)
|
||
ORDER BY
|
||
CASE WHEN t.match_status = 'unmatched' THEN 0 ELSE 1 END,
|
||
COALESCE(t.posted_date, substr(t.transacted_at, 1, 10), t.created_at) DESC,
|
||
t.id DESC
|
||
LIMIT ?
|
||
`).all(userId, like, like, like, like, limit);
|
||
|
||
return rows.map(row => {
|
||
const merchant = normalizeMerchant(row.payee || row.description || row.memo);
|
||
const catalogEntry = lookupCatalog(catalog, merchant);
|
||
return {
|
||
...row,
|
||
amount_dollars: dollarsFromTransactionAmount(row.amount),
|
||
merchant,
|
||
is_known_subscription: !!catalogEntry,
|
||
catalog_match: catalogMatchPayload(catalogEntry),
|
||
};
|
||
}).sort((a, b) => Number(b.is_known_subscription) - Number(a.is_known_subscription));
|
||
}
|
||
|
||
function createSubscriptionFromRecommendation(db, userId, payload = {}) {
|
||
const seenDate = payload.last_seen_date || todayLocal();
|
||
const source = payload.catalog_match
|
||
? 'catalog_match'
|
||
: 'simplefin_recommendation';
|
||
|
||
const draft = {
|
||
name: payload.name,
|
||
category_id: payload.category_id || null,
|
||
due_day: payload.due_day,
|
||
expected_amount: payload.expected_amount,
|
||
billing_cycle: billingCycleForCycleType(payload.cycle_type || 'monthly'),
|
||
cycle_type: payload.cycle_type || 'monthly',
|
||
cycle_day: (payload.cycle_type === 'annual' || payload.cycle_type === 'quarterly')
|
||
? String(new Date(`${seenDate}T00:00:00`).getMonth() + 1)
|
||
: (payload.cycle_type === 'weekly' || payload.cycle_type === 'biweekly')
|
||
? 'monday'
|
||
: String(payload.due_day || 1),
|
||
is_subscription: 1,
|
||
subscription_type: SUBSCRIPTION_TYPES.includes(payload.subscription_type) ? payload.subscription_type : 'other',
|
||
reminder_days_before: 3,
|
||
subscription_source: source,
|
||
subscription_detected_at: new Date().toISOString(),
|
||
notes: payload.merchant ? `Detected from recurring merchant: ${payload.merchant}` : null,
|
||
};
|
||
|
||
const validation = validateBillData(draft);
|
||
if (validation.errors.length > 0) {
|
||
const err = new Error(validation.errors[0].message);
|
||
err.field = validation.errors[0].field;
|
||
err.status = 400;
|
||
throw err;
|
||
}
|
||
|
||
const created = insertBill(db, userId, validation.normalized);
|
||
|
||
// Persist catalog link so the catalog browser can show this bill as "matched"
|
||
const catalogId = payload.catalog_match?.id;
|
||
if (catalogId) {
|
||
try {
|
||
db.prepare('UPDATE bills SET catalog_id = ? WHERE id = ?').run(catalogId, created.id);
|
||
created.catalog_id = catalogId;
|
||
} catch { /* catalog_id column may not exist pre-v0.96 — safe to ignore */ }
|
||
}
|
||
|
||
const ids = Array.isArray(payload.transaction_ids)
|
||
? payload.transaction_ids.map(id => Number(id)).filter(Number.isInteger).slice(0, 50)
|
||
: [];
|
||
if (ids.length > 0) {
|
||
const placeholders = ids.map(() => '?').join(',');
|
||
const txRows = db.prepare(`
|
||
SELECT id, amount, posted_date, transacted_at
|
||
FROM transactions
|
||
WHERE user_id = ? AND id IN (${placeholders}) AND ignored = 0
|
||
`).all(userId, ...ids);
|
||
|
||
const updateTx = db.prepare(`
|
||
UPDATE transactions
|
||
SET matched_bill_id = ?, match_status = 'matched', updated_at = CURRENT_TIMESTAMP
|
||
WHERE id = ? AND user_id = ? AND ignored = 0 AND match_status != 'matched'
|
||
`);
|
||
const insertPayment = db.prepare(`
|
||
INSERT OR IGNORE INTO payments (bill_id, amount, paid_date, payment_source, transaction_id)
|
||
VALUES (?, ?, ?, 'auto_match', ?)
|
||
`);
|
||
|
||
db.transaction(() => {
|
||
for (const tx of txRows) {
|
||
const paidDate = tx.posted_date || (tx.transacted_at ? String(tx.transacted_at).slice(0, 10) : null);
|
||
const amount = Math.round(Math.abs(tx.amount)); // tx.amount and payments.amount are both cents
|
||
updateTx.run(created.id, tx.id, userId);
|
||
if (paidDate) insertPayment.run(created.id, amount, paidDate, tx.id);
|
||
}
|
||
})();
|
||
}
|
||
|
||
return decorateSubscription(created);
|
||
}
|
||
|
||
module.exports = {
|
||
SUBSCRIPTION_TYPES,
|
||
createSubscriptionFromRecommendation,
|
||
declineRecommendation,
|
||
decorateSubscription,
|
||
getSubscriptionRecommendations,
|
||
getSubscriptionSummary,
|
||
getSubscriptions,
|
||
lookupCatalog,
|
||
loadCatalog,
|
||
monthlyEquivalent,
|
||
normalizeMerchant,
|
||
recordSubscriptionFeedback,
|
||
searchSubscriptionTransactions,
|
||
};
|