Csv Transaction Import

Libraries

Normalized Transaction Shape

Before saving, every row must be mapped to:

interface ImportedRow {
  date: Date;
  description: string;
  amount: number;       // cents, positive = income, negative = expense
  importHash: string;   // SHA-256(date|amount|description)
  categoryId?: number;  // from auto-categorization, nullable
}

Column Mapping Strategy

Each bank format is defined as a profile in src/lib/import/formats.ts:

interface BankFormat {
  id: string;                    // e.g. "ing-be", "kbc-be"
  name: string;                  // "ING Belgium"
  dateColumn: string;            // header name for date
  dateFormat: string;            // e.g. "DD/MM/YYYY", "YYYY-MM-DD"
  descriptionColumns: string[];  // headers to concatenate for description
  amountColumn?: string;         // single signed amount column
  debitColumn?: string;          // separate debit column (always positive)
  creditColumn?: string;         // separate credit column (always positive)
  delimiter?: string;            // default ","
  skipRows?: number;             // header rows to skip
  encoding?: string;             // default "utf-8"
}

Amount handling: Banks use one of two conventions:

• Single column: positive = credit, negative = debit (most common)
• Two columns: separate debit and credit columns (both positive values). Convert debit to negative.

For known bank format mappings, see bank-formats.md.

Deduplication

Compute a hash for each row to prevent re-importing the same transaction:

import { createHash } from "crypto";

function computeImportHash(date: string, amount: number, description: string): string {
  const raw = `${date}|${amount}|${description}`;
  return createHash("sha256").update(raw).digest("hex");
}

Before inserting, query existing hashes for the target account:

const existing = await db
  .select({ hash: transactions.importHash })
  .from(transactions)
  .where(eq(transactions.accountId, accountId));

const existingSet = new Set(existing.map((r) => r.hash));
const newRows = parsed.filter((r) => !existingSet.has(r.importHash));

Auto-Categorization

Define rules as patterns matched against the transaction description:

interface CategoryRule {
  categoryId: number;
  patterns: RegExp[];
}

// Example rules
const rules: CategoryRule[] = [
  { categoryId: 1, patterns: [/colruyt/i, /delhaize/i, /aldi/i, /lidl/i, /carrefour/i] },
  { categoryId: 5, patterns: [/netflix/i, /spotify/i, /disney/i] },
  { categoryId: 3, patterns: [/q8/i, /total ?energies/i, /shell/i] },
];

• Match first rule that hits (order matters)
• Unmatched transactions get categoryId: null (user categorizes manually)
• Store rules in DB later for user-customizable categorization

Error Handling

• Empty file: reject with clear message
• Missing required columns: show which columns were expected vs found
• Unparseable date: skip row, collect in errors[] array for user review
• Unparseable amount: skip row, collect in errors[]
• All rows duplicates: show "0 new transactions found" with explanation

Validation with Zod

const importRowSchema = z.object({
  date: z.coerce.date(),
  description: z.string().min(1),
  amount: z.number().int(),
  importHash: z.string().length(64),
  categoryId: z.number().int().nullable().optional(),
});