# Sprint 10 Implementation Plan — Smart Payment Import **Date:** 2026-06-15 **Author:** Patrick Plate / Lumen (Architect) **Status:** Draft v3 **Basis:** cannamanage-sprint10-analysis.md **Sprint Goal:** Bank statement import with auto-matching for member payment reconciliation --- ## Implementation Phases Sprint 10 is organized into 5 phases, building from data model through parsers to the matching engine and frontend wizard. ```mermaid graph LR P1[Phase 1: Data Model + Enums] --> P2[Phase 2: Bank Statement Parsers] P2 --> P3[Phase 3: Matching Engine + Import Service] P3 --> P4[Phase 4: REST API + Security] P4 --> P5[Phase 5: Frontend Import Wizard] ``` --- ## Phase 1: Data Model & Infrastructure ### Step 1.1 — Database Migrations (V30-V32) **Files:** - `cannamanage-api/src/main/resources/db/migration/V30__bank_import_sessions.sql` - `cannamanage-api/src/main/resources/db/migration/V31__bank_transactions.sql` - `cannamanage-api/src/main/resources/db/migration/V32__csv_column_mappings.sql` **V30 — Bank Import Sessions:** ```sql CREATE TABLE bank_import_sessions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), club_id UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE, filename VARCHAR(255) NOT NULL, format VARCHAR(20) NOT NULL, -- MT940, CAMT053, CSV total_transactions INTEGER NOT NULL DEFAULT 0, matched_count INTEGER NOT NULL DEFAULT 0, confirmed_count INTEGER NOT NULL DEFAULT 0, skipped_count INTEGER NOT NULL DEFAULT 0, status VARCHAR(20) NOT NULL DEFAULT 'PENDING', -- PENDING, IN_REVIEW, COMPLETED, FAILED uploaded_by UUID NOT NULL REFERENCES users(id), error_message TEXT, created_at TIMESTAMP NOT NULL DEFAULT NOW(), completed_at TIMESTAMP ); CREATE INDEX idx_bank_import_sessions_club ON bank_import_sessions(club_id); CREATE INDEX idx_bank_import_sessions_status ON bank_import_sessions(club_id, status); ``` **V31 — Bank Transactions:** ```sql CREATE TABLE bank_transactions ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), session_id UUID NOT NULL REFERENCES bank_import_sessions(id) ON DELETE CASCADE, club_id UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE, booking_date DATE NOT NULL, value_date DATE, amount_cents INTEGER NOT NULL, -- positive = incoming, negative = outgoing currency VARCHAR(3) NOT NULL DEFAULT 'EUR', reference_text TEXT, -- Verwendungszweck counterparty_name VARCHAR(300), counterparty_iban VARCHAR(34), bank_reference VARCHAR(100), match_status VARCHAR(20) NOT NULL DEFAULT 'UNMATCHED', -- MATCHED, SUGGESTED, UNMATCHED, SKIPPED, CONFIRMED match_confidence INTEGER, -- 0-100 matched_member_id UUID REFERENCES members(id) ON DELETE SET NULL, matched_payment_id UUID REFERENCES payments(id) ON DELETE SET NULL, skip_reason VARCHAR(100), created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE INDEX idx_bank_transactions_session ON bank_transactions(session_id); CREATE INDEX idx_bank_transactions_club_status ON bank_transactions(club_id, match_status); CREATE INDEX idx_bank_transactions_member ON bank_transactions(matched_member_id); ``` **V32 — CSV Column Mappings + Member IBAN:** ```sql CREATE TABLE csv_column_mappings ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), club_id UUID NOT NULL REFERENCES clubs(id) ON DELETE CASCADE, name VARCHAR(100) NOT NULL, date_column INTEGER NOT NULL, amount_column INTEGER NOT NULL, reference_column INTEGER, counterparty_column INTEGER, iban_column INTEGER, delimiter VARCHAR(5) NOT NULL DEFAULT ';', date_format VARCHAR(20) NOT NULL DEFAULT 'dd.MM.yyyy', decimal_separator VARCHAR(1) NOT NULL DEFAULT ',', skip_header_rows INTEGER NOT NULL DEFAULT 1, encoding VARCHAR(20) NOT NULL DEFAULT 'ISO-8859-1', is_default BOOLEAN NOT NULL DEFAULT FALSE, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE INDEX idx_csv_column_mappings_club ON csv_column_mappings(club_id); -- Add optional IBAN field to members table -- Advisory (Integration #1): Both columns are intentionally NULLABLE. -- No NOT NULL constraint — IBAN is only populated after explicit BANK_DATA consent. -- PostgreSQL adds nullable columns instantly (no table rewrite), safe for hot table. ALTER TABLE members ADD COLUMN IF NOT EXISTS iban VARCHAR(34); ALTER TABLE members ADD COLUMN IF NOT EXISTS iban_consent_date TIMESTAMP; ``` ### Step 1.2 — Domain Enums **New file:** `cannamanage-domain/src/main/java/de/cannamanage/domain/enums/BankFormat.java` ```java package de.cannamanage.domain.enums; public enum BankFormat { MT940, CAMT053, CSV } ``` **New file:** `cannamanage-domain/src/main/java/de/cannamanage/domain/enums/MatchStatus.java` ```java package de.cannamanage.domain.enums; public enum MatchStatus { UNMATCHED, SUGGESTED, MATCHED, CONFIRMED, SKIPPED } ``` **New file:** `cannamanage-domain/src/main/java/de/cannamanage/domain/enums/ImportSessionStatus.java` ```java package de.cannamanage.domain.enums; public enum ImportSessionStatus { PENDING, IN_REVIEW, COMPLETED, FAILED } ``` **Modified:** `cannamanage-domain/src/main/java/de/cannamanage/domain/enums/ConsentType.java` ```java // Add after NOTIFICATION_EMAIL: BANK_DATA // Sprint 10 — IBAN storage consent (DSGVO Art. 6(1)(a)) ``` **Modified:** `cannamanage-domain/src/main/java/de/cannamanage/domain/enums/NotificationType.java` ```java // Add after COMPLIANCE_DEADLINE: // Sprint 10 — Bank Import: BANK_IMPORT_COMPLETED ``` **Modified:** `cannamanage-domain/src/main/java/de/cannamanage/domain/enums/AuditEventType.java` ```java // Add: BANK_IMPORT_STARTED, BANK_IMPORT_COMPLETED, BANK_IMPORT_FAILED, BANK_PAYMENT_CONFIRMED ``` **Modified:** `cannamanage-domain/src/main/java/de/cannamanage/domain/enums/StaffPermission.java` ```java // Add: FINANCE_IMPORT // Permission to upload bank statements and confirm matches ``` ### Step 1.3 — JPA Entities **New file:** `cannamanage-domain/src/main/java/de/cannamanage/domain/entity/BankImportSession.java` ```java @Entity @Table(name = "bank_import_sessions") @Getter @Setter @NoArgsConstructor public class BankImportSession extends AbstractTenantEntity { @Column(nullable = false) private String filename; @Enumerated(EnumType.STRING) @Column(nullable = false, length = 20) private BankFormat format; @Column(nullable = false) private Integer totalTransactions = 0; private Integer matchedCount = 0; private Integer confirmedCount = 0; private Integer skippedCount = 0; @Enumerated(EnumType.STRING) @Column(nullable = false, length = 20) private ImportSessionStatus status = ImportSessionStatus.PENDING; @Column(nullable = false) private UUID uploadedBy; private String errorMessage; private LocalDateTime completedAt; @OneToMany(mappedBy = "session", cascade = CascadeType.ALL, orphanRemoval = true) private List transactions = new ArrayList<>(); } ``` **New file:** `cannamanage-domain/src/main/java/de/cannamanage/domain/entity/BankTransaction.java` ```java @Entity @Table(name = "bank_transactions") @Getter @Setter @NoArgsConstructor public class BankTransaction extends AbstractTenantEntity { @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "session_id", nullable = false) private BankImportSession session; @Column(nullable = false) private LocalDate bookingDate; private LocalDate valueDate; @Column(nullable = false) private Integer amountCents; // positive = incoming, negative = outgoing @Column(nullable = false, length = 3) private String currency = "EUR"; @Column(columnDefinition = "TEXT") private String referenceText; // Verwendungszweck @Column(length = 300) private String counterpartyName; @Column(length = 34) private String counterpartyIban; @Column(length = 100) private String bankReference; @Enumerated(EnumType.STRING) @Column(nullable = false, length = 20) private MatchStatus matchStatus = MatchStatus.UNMATCHED; private Integer matchConfidence; // 0-100 @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "matched_member_id") private Member matchedMember; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "matched_payment_id") private Payment matchedPayment; @Column(length = 100) private String skipReason; } ``` **New file:** `cannamanage-domain/src/main/java/de/cannamanage/domain/entity/CsvColumnMapping.java` ```java @Entity @Table(name = "csv_column_mappings") @Getter @Setter @NoArgsConstructor public class CsvColumnMapping extends AbstractTenantEntity { @Column(nullable = false, length = 100) private String name; // e.g. "Sparkasse Export" @Column(nullable = false) private Integer dateColumn; @Column(nullable = false) private Integer amountColumn; private Integer referenceColumn; private Integer counterpartyColumn; private Integer ibanColumn; @Column(nullable = false, length = 5) private String delimiter = ";"; @Column(nullable = false, length = 20) private String dateFormat = "dd.MM.yyyy"; @Column(nullable = false, length = 1) private String decimalSeparator = ","; @Column(nullable = false) private Integer skipHeaderRows = 1; @Column(nullable = false, length = 20) private String encoding = "ISO-8859-1"; @Column(nullable = false) private Boolean isDefault = false; } ``` ### Step 1.4 — Repositories **New files in** `cannamanage-domain/src/main/java/de/cannamanage/domain/repository/`: - `BankImportSessionRepository.java` — findByClubIdOrderByCreatedAtDesc, findByClubIdAndStatus - `BankTransactionRepository.java` — findBySessionId, findBySessionIdAndMatchStatus, countBySessionIdAndMatchStatus - `CsvColumnMappingRepository.java` — findByClubId, findByClubIdAndIsDefaultTrue ### Step 1.5 — Member Entity Modification **Modified:** `cannamanage-domain/src/main/java/de/cannamanage/domain/entity/Member.java` ```java // Add fields: @Column(length = 34) private String iban; private LocalDateTime ibanConsentDate; ``` --- ## Phase 2: Bank Statement Parsers ### Step 2.1 — Parser Interface **New file:** `cannamanage-service/src/main/java/de/cannamanage/service/bankimport/BankStatementParser.java` ```java package de.cannamanage.service.bankimport; import java.io.InputStream; import java.util.List; public interface BankStatementParser { BankFormat getSupportedFormat(); boolean canParse(String filename, byte[] headerBytes); ParseResult parse(InputStream inputStream, String filename); } ``` **New file:** `cannamanage-service/src/main/java/de/cannamanage/service/bankimport/ParseResult.java` ```java public record ParseResult( List transactions, String accountIban, LocalDate statementDate, Integer openingBalanceCents, Integer closingBalanceCents, List warnings ) {} ``` **New file:** `cannamanage-service/src/main/java/de/cannamanage/service/bankimport/ParsedTransaction.java` ```java public record ParsedTransaction( LocalDate bookingDate, LocalDate valueDate, int amountCents, String currency, String referenceText, String counterpartyName, String counterpartyIban, String bankReference ) {} ``` ### Step 2.2 — MT940 Parser **New file:** `cannamanage-service/src/main/java/de/cannamanage/service/bankimport/Mt940Parser.java` Key implementation details: - Line-by-line state machine parser - States: HEADER, STATEMENT_LINE, INFORMATION - Field detection by `:XX:` prefix tags - Amount parsing: German comma-decimal format (`1234,56` → 123456 cents) - Date parsing: YYMMDD → LocalDate with explicit century boundary constant: ```java /** Standard banking convention for 2-digit year parsing (SWIFT MT940). */ private static final int CENTURY_BOUNDARY = 70; // YY >= 70 → 19xx, else 20xx ``` - Proprietary header skipping: skip all lines before first `:20:` tag to handle online banking portals (StarMoney, WISO, Hibiscus) that wrap SWIFT content with proprietary headers - Multi-line `:86:` accumulation (Verwendungszweck can span 6 lines × 27 chars) - `D`/`C` indicator for debit/credit → negative/positive amountCents - Graceful handling of bank-specific quirks (extra whitespace, missing fields) ```java @Component @Slf4j public class Mt940Parser implements BankStatementParser { private static final Pattern TAG_PATTERN = Pattern.compile("^:(\\d{2}[A-Z]?):(.*)$"); private static final Pattern AMOUNT_PATTERN = Pattern.compile("^(\\d{6})(\\d{4})?(C|D|RC|RD)(\\w{1})(\\d+,\\d{2})(.*)$"); @Override public BankFormat getSupportedFormat() { return BankFormat.MT940; } @Override public boolean canParse(String filename, byte[] headerBytes) { String header = new String(headerBytes, StandardCharsets.ISO_8859_1); return header.contains(":20:") && header.contains(":60F:"); } @Override public ParseResult parse(InputStream inputStream, String filename) { // State machine implementation... } } ``` **Critical parsing rules for German banks:** - Sparkasse/Volksbank: `:25:` contains BLZ/account (10-digit) - Deutsche Bank/Commerzbank: `:25:` contains IBAN directly - ING DiBa: uses `RC`/`RD` for reversal credits/debits - `:86:` sub-fields: `SVWZ+` (Verwendungszweck), `EREF+`, `KREF+`, `MREF+`, `CRED+`, `DEBT+` ### Step 2.3 — CAMT.053 Parser **New file:** `cannamanage-service/src/main/java/de/cannamanage/service/bankimport/Camt053Parser.java` Key implementation details: - XML parsing with `javax.xml.stream.XMLStreamReader` (StAX) for memory efficiency - **XXE protection (SEC-advisory):** Explicitly disable external entities and DTD support on the XMLInputFactory - No JAXB code generation needed — the subset we need is small (Ntry, Amt, RmtInf, RltdPties) - Namespace-aware: `urn:iso:std:iso:20022:tech:xsd:camt.053.001.02` (v2) and `camt.053.001.08` (v8) - Amount: standard decimal with dot (`50.00` → 5000 cents) - CdtDbtInd: `CRDT` → positive, `DBIT` → negative ```java @Component @Slf4j public class Camt053Parser implements BankStatementParser { private final XMLInputFactory xmlInputFactory; public Camt053Parser() { // XXE Prevention — hardened StAX factory (Advisory: Security #2) this.xmlInputFactory = XMLInputFactory.newFactory(); this.xmlInputFactory.setProperty(XMLInputFactory.IS_SUPPORTING_EXTERNAL_ENTITIES, false); this.xmlInputFactory.setProperty(XMLInputFactory.SUPPORT_DTD, false); this.xmlInputFactory.setProperty(XMLInputFactory.IS_REPLACING_ENTITY_REFERENCES, false); } @Override public BankFormat getSupportedFormat() { return BankFormat.CAMT053; } @Override public boolean canParse(String filename, byte[] headerBytes) { String header = new String(headerBytes, StandardCharsets.UTF_8); return header.contains("camt.053") || header.contains("BkToCstmrStmt"); } @Override public ParseResult parse(InputStream inputStream, String filename) { XMLStreamReader reader = xmlInputFactory.createXMLStreamReader(inputStream, "UTF-8"); // StAX streaming parser implementation using the hardened factory... } } ``` ### Step 2.4 — CSV Parser **New file:** `cannamanage-service/src/main/java/de/cannamanage/service/bankimport/CsvBankParser.java` Key implementation details: - Uses Apache Commons CSV (`CSVFormat.Builder`) - Configurable via `CsvColumnMapping` entity (delimiter, encoding, column positions) - German number parsing: strip thousands separator (`.`), replace decimal comma (`,`) with dot - Date parsing with configurable format (default `dd.MM.yyyy`) - Skip configurable header rows - Encoding: default ISO-8859-1, configurable per mapping ```java @Component @Slf4j public class CsvBankParser implements BankStatementParser { @Override public BankFormat getSupportedFormat() { return BankFormat.CSV; } @Override public boolean canParse(String filename, byte[] headerBytes) { return filename.toLowerCase().endsWith(".csv"); } public ParseResult parse(InputStream inputStream, String filename, CsvColumnMapping mapping) { // Apache Commons CSV with configurable column mapping... } } ``` ### Step 2.5 — Format Detection Service **New file:** `cannamanage-service/src/main/java/de/cannamanage/service/bankimport/BankStatementParserService.java` ```java @Service @Slf4j public class BankStatementParserService { private final List parsers; public BankStatementParserService(List parsers) { this.parsers = parsers; } public BankFormat detectFormat(String filename, byte[] content) { // 1. Check file extension (.xml → likely CAMT, .sta/.mt940 → MT940, .csv → CSV) // 2. Read first 512 bytes and try each parser's canParse() // 3. Return detected format or throw UnrecognizedFormatException } public ParseResult parse(InputStream input, String filename, BankFormat format, CsvColumnMapping csvMapping) { // Delegate to the appropriate parser based on format } } ``` --- ## Phase 3: Matching Engine & Import Service ### Step 3.1 — Payment Matching Service **New file:** `cannamanage-service/src/main/java/de/cannamanage/service/bankimport/PaymentMatchingService.java` ```java @Service @Slf4j public class PaymentMatchingService { private static final int THRESHOLD_AUTO_MATCH = 90; private static final int THRESHOLD_SUGGESTED = 60; private static final double WEIGHT_AMOUNT = 0.35; private static final double WEIGHT_MEMBER_NUMBER = 0.30; private static final double WEIGHT_NAME = 0.15; private static final double WEIGHT_IBAN = 0.15; private static final double WEIGHT_DATE = 0.05; private final MemberRepository memberRepository; private final MemberFeeAssignmentRepository feeAssignmentRepository; private final FeeScheduleRepository feeScheduleRepository; public List matchTransactions( List transactions, UUID clubId) { long startTime = System.nanoTime(); // 1. Load all active members with fee assignments (eager fetch) List activeMembers = memberRepository.findActiveByClubIdWithFeeAssignments(clubId); // 2. Pre-compute member fee amounts for early-exit optimization (Advisory: Architecture #2) Map memberFeeAmounts = precomputeFeeAmounts(activeMembers); // 3. For each CREDIT transaction: // a. Early-exit: skip members whose expected fee differs by >20% (Advisory: Architecture #2) // b. Generate candidate pairs only for amount-plausible members // c. Score each pair using weighted criteria // d. Pick best match, classify by confidence threshold // 4. Handle conflicts (multiple members → SUGGESTED, not AUTO) // 5. Handle double-payment edge case (Info: Testing #2 v3): // If same member matches 2+ transactions in one file, mark all as // SUGGESTED (even if individually >90%) — admin must manually confirm // to prevent accidental double-crediting. // 5. Return MatchResult list with confidence + classification long durationMs = (System.nanoTime() - startTime) / 1_000_000; log.info("Matching completed: {} transactions × {} members in {}ms", transactions.size(), activeMembers.size(), durationMs); return results; } /** * Pre-computes expected fee amounts per member for fast filtering. * Allows early-exit in the matching loop: skip members whose fee * doesn't match ±20% of the transaction amount. (Advisory: Architecture #2) * * IMPORTANT (Info: Integration #2 v3): Query MemberFeeAssignment with * validity date overlap against the transaction's booking date range, * not just "currently active" assignments. A January import reviewing * December transactions must match December's fee schedule. */ private Map precomputeFeeAmounts(List members, LocalDate bookingDateContext) { // Build memberId → expected monthly fee amount (cents) map // Uses MemberFeeAssignment valid at bookingDateContext (not LocalDate.now()) } private int scoreAmount(int transactionCents, int expectedFeeCents) { if (transactionCents == expectedFeeCents) return 100; double ratio = (double) transactionCents / expectedFeeCents; if (ratio >= 0.95 && ratio <= 1.05) return 80; // within 5% return 0; } private int scoreMemberNumber(String referenceText, String memberNumber) { if (referenceText == null || memberNumber == null) return 0; String normalized = referenceText.toUpperCase(); if (normalized.contains(memberNumber.toUpperCase())) return 100; // Try without prefix: "M-0042" → "0042" or "42" String numericPart = memberNumber.replaceAll("[^0-9]", ""); if (normalized.contains(numericPart)) return 50; return 0; } private int scoreName(String referenceText, String memberFullName) { if (referenceText == null || memberFullName == null) return 0; String normalized = referenceText.toLowerCase(); String fullNameLower = memberFullName.toLowerCase(); if (normalized.contains(fullNameLower)) return 100; // Try last name only String lastName = fullNameLower.substring(fullNameLower.lastIndexOf(' ') + 1); if (normalized.contains(lastName)) return 70; // Fuzzy: Levenshtein distance ≤ 2 // Use Apache Commons Text LevenshteinDistance return 0; } private int scoreIban(String transactionIban, String memberIban) { if (transactionIban == null || memberIban == null) return 0; return transactionIban.replaceAll("\\s", "") .equalsIgnoreCase(memberIban.replaceAll("\\s", "")) ? 100 : 0; } private int scoreDate(LocalDate bookingDate, FeeSchedule schedule) { // Check if booking date falls within the expected payment window // Monthly: within the expected month ± 15 days // Quarterly: within the expected quarter start ± 30 days return 0; // Implementation based on schedule interval } } ``` **New file:** `cannamanage-service/src/main/java/de/cannamanage/service/bankimport/MatchResult.java` ```java public record MatchResult( UUID transactionId, UUID matchedMemberId, String matchedMemberName, int confidence, MatchStatus classification, Map scoreBreakdown // criterion → individual score ) {} ``` ### Step 3.2 — Bank Import Service (Orchestrator) **New file:** `cannamanage-service/src/main/java/de/cannamanage/service/bankimport/BankImportService.java` ```java @Service @Slf4j public class BankImportService { private final BankStatementParserService parserService; private final PaymentMatchingService matchingService; private final BankImportSessionRepository sessionRepository; private final BankTransactionRepository transactionRepository; private final FinanceService financeService; private final AuditService auditService; private final NotificationDispatchService notificationService; /** * Step 1: Upload and parse bank statement file. * Returns session with parsed transactions (not yet matched). * * Design note (Advisory: Architecture #1): File I/O (parsing) is separated * from the @Transactional persistence to avoid holding a DB connection during * potentially slow file operations on large files (5000+ transactions). */ public BankImportSession uploadAndParse( MultipartFile file, UUID clubId, UUID userId, BankFormat format, CsvColumnMapping csvMapping) { // 1. Validate file (size, format) // 2. Duplicate detection (Info: Architecture #2 v3): // Check for existing sessions with same filename created within 24h. // If found → warn admin ("Identical file was imported X hours ago. Continue?") // Frontend shows confirmation dialog; backend returns 409 Conflict with existing sessionId. checkDuplicateImport(file.getOriginalFilename(), clubId); // 3. Sanitize filename (Advisory: Security #1) String sanitizedFilename = sanitizeFilename(file.getOriginalFilename()); // 4. Parse file in-memory WITHOUT transaction (no DB connection held) ParseResult parseResult = parserService.parse( file.getInputStream(), sanitizedFilename, format, csvMapping); // 4. Persist parsed results in a separate transactional method return persistParseResults(sanitizedFilename, format, parseResult, clubId, userId); } /** * Persists parse results within a transaction boundary. * Separated from file I/O to minimize DB connection hold time. */ @Transactional protected BankImportSession persistParseResults( String filename, BankFormat format, ParseResult parseResult, UUID clubId, UUID userId) { // 1. Create BankImportSession with sanitized filename // 2. Convert ParsedTransactions to BankTransaction entities // 3. Batch persist with flush/clear every 100 entities // 4. Audit: BANK_IMPORT_STARTED // 5. Return session } /** * Sanitizes uploaded filename to prevent path traversal (Advisory: Security #1). * Strips path separators, limits length, rejects control characters. */ private String sanitizeFilename(String originalFilename) { if (originalFilename == null) return "unnamed-import"; // Strip path components (handles both Unix and Windows separators) String name = FilenameUtils.getName(originalFilename); // commons-io // Remove control characters and null bytes name = name.replaceAll("[\\x00-\\x1F\\x7F]", ""); // Limit length to 200 characters if (name.length() > 200) name = name.substring(0, 200); // Fallback if empty after sanitization return name.isEmpty() ? "unnamed-import" : name; } /** * Step 2: Run matching algorithm on parsed transactions. * Updates match_status and match_confidence on each transaction. */ public BankImportSession runMatching(UUID sessionId) { // 1. Load session + transactions // 2. Filter to credit-only (amountCents > 0) // 3. Run PaymentMatchingService.matchTransactions() // 4. Update each BankTransaction with match result // 5. Update session counts (matchedCount) // 6. Set session status → IN_REVIEW // 7. Return updated session } /** * Step 3: Confirm a single match → creates Payment + LedgerEntry. */ public BankTransaction confirmMatch(UUID transactionId, UUID memberId, UUID userId) { // 1. Load transaction, validate status // 2. Call financeService.recordPayment() with: // - memberId, amountCents, BANK_TRANSFER, reference, period // 3. Update transaction: status → CONFIRMED, matched_payment_id // 4. Audit: BANK_PAYMENT_CONFIRMED // 5. Notify member: PAYMENT_RECEIVED // 6. Return updated transaction } /** * Step 3b: Bulk confirm all AUTO_MATCHED transactions. */ public int confirmAllMatched(UUID sessionId, UUID userId) { // 1. Load all transactions with matchStatus == MATCHED // 2. For each: confirmMatch() // 3. Return count of confirmed } /** * Step 3c: Skip a transaction (not a member payment). */ public BankTransaction skipTransaction(UUID transactionId, String reason) { // Update status → SKIPPED, set skipReason } /** * Step 3d: Manually assign a transaction to a member. */ public BankTransaction manualAssign(UUID transactionId, UUID memberId, UUID userId) { // Same as confirmMatch but with explicit member selection } /** * Step 3e: Categorize outgoing transaction as expense. */ public BankTransaction categorizeAsExpense( UUID transactionId, ExpenseCategory category, UUID userId) { // 1. Create expense LedgerEntry via financeService // 2. Update transaction status → CONFIRMED // 3. Return updated } /** * Step 4: Complete import session. * GoBD compliance (Info: GoBD v3): After completion, the session becomes * immutable — no further confirm/skip/assign operations are allowed. */ public BankImportSession completeSession(UUID sessionId) { // 1. Update session status → COMPLETED // 2. Set completedAt // 3. Audit: BANK_IMPORT_COMPLETED // 4. Return session } /** * GoBD immutability guard (Info: GoBD v3): Rejects any mutation attempt * on a COMPLETED session. All confirm/skip/assign/expense endpoints call * this before processing. Throws IllegalStateException with clear message. */ private void assertSessionMutable(BankImportSession session) { if (session.getStatus() == ImportSessionStatus.COMPLETED) { throw new IllegalStateException( "Session " + session.getId() + " is COMPLETED and immutable (GoBD)."); } } /** * Duplicate import detection (Info: Architecture #2 v3): Checks if an identical * filename was already imported within the last 24 hours for this club. * Returns 409 Conflict if found, allowing the frontend to show a confirmation dialog. */ private void checkDuplicateImport(String filename, UUID clubId) { LocalDateTime cutoff = LocalDateTime.now().minusHours(24); Optional existing = sessionRepository .findByClubIdAndFilenameAndCreatedAtAfter(clubId, filename, cutoff); if (existing.isPresent()) { throw new DuplicateImportException( "File '" + filename + "' was already imported " + Duration.between(existing.get().getCreatedAt(), LocalDateTime.now()).toHours() + " hours ago. Session: " + existing.get().getId()); } } } ``` ### Step 3.3 — Tier Enforcement **Modified:** `cannamanage-service/src/main/java/de/cannamanage/service/TierLimitService.java` Add checks: ```java public void checkBankImportAllowed(UUID clubId, BankFormat format) { PlanTier tier = getClubTier(clubId); // Starter: CSV only, max 1 import/month if (tier == PlanTier.STARTER) { if (format != BankFormat.CSV) { throw new TierLimitExceededException("MT940/CAMT.053 requires Pro plan"); } long importsThisMonth = sessionRepository.countByClubIdAndCreatedAtAfter( clubId, LocalDate.now().withDayOfMonth(1).atStartOfDay()); if (importsThisMonth >= 1) { throw new TierLimitExceededException("Starter plan: max 1 import per month"); } } } public void checkCsvMappingAllowed(UUID clubId) { PlanTier tier = getClubTier(clubId); if (tier == PlanTier.STARTER) { throw new TierLimitExceededException("Saved CSV templates require Pro plan"); } if (tier == PlanTier.PRO) { long count = mappingRepository.countByClubId(clubId); if (count >= 3) { throw new TierLimitExceededException("Pro plan: max 3 CSV templates"); } } } public boolean isAutoConfirmAllowed(UUID clubId) { return getClubTier(clubId) == PlanTier.ENTERPRISE; } ``` --- ## Phase 4: REST API & Security ### Step 4.1 — Bank Import Controller **New file:** `cannamanage-api/src/main/java/de/cannamanage/api/controller/BankImportController.java` ```java @RestController @RequestMapping("/api/finance/import") @RequiredArgsConstructor @Slf4j public class BankImportController { // POST /api/finance/import/upload // - Multipart file upload // - Optional: format override, csvMappingId // - Returns: BankImportSessionDto (with parsed transaction count) // - Permission: FINANCE_IMPORT // POST /api/finance/import/{sessionId}/match // - Triggers matching algorithm // - Returns: BankImportSessionDto (with match results) // GET /api/finance/import/{sessionId} // - Returns session details + all transactions with match info // GET /api/finance/import/{sessionId}/transactions // - Paginated transaction list with filtering by matchStatus // POST /api/finance/import/{sessionId}/confirm-all // - Bulk confirm all MATCHED transactions // - Returns: count confirmed // POST /api/finance/import/transactions/{txId}/confirm // - Confirm single match (with optional memberId override) // POST /api/finance/import/transactions/{txId}/assign // - Manual member assignment (body: { memberId }) // POST /api/finance/import/transactions/{txId}/skip // - Skip transaction (body: { reason }) // POST /api/finance/import/transactions/{txId}/expense // - Categorize as expense (body: { category }) // POST /api/finance/import/{sessionId}/complete // - Mark session as completed // GET /api/finance/import/sessions // - List all import sessions for the club (paginated) // --- CSV Column Mappings --- // GET /api/finance/import/csv-mappings // - List saved CSV mappings for club // POST /api/finance/import/csv-mappings // - Create new CSV mapping template // PUT /api/finance/import/csv-mappings/{id} // - Update mapping // DELETE /api/finance/import/csv-mappings/{id} // - Delete mapping // --- Format Detection --- // POST /api/finance/import/detect-format // - Upload file, detect format, return preview (first 5 rows for CSV) // - Used by frontend wizard step 1 } ``` ### Step 4.2 — DTO Classes **New files in** `cannamanage-api/src/main/java/de/cannamanage/api/dto/bankimport/`: - `BankImportSessionDto.java` — session overview with counts - `BankTransactionDto.java` — individual transaction with match details - `CsvColumnMappingDto.java` — mapping template - `UploadRequest.java` — multipart metadata - `ConfirmRequest.java` — optional memberId override - `AssignRequest.java` — memberId for manual assignment - `SkipRequest.java` — reason - `ExpenseRequest.java` — category - `FormatDetectionResponse.java` — detected format + CSV preview rows - `CsvPreviewRow.java` — raw column values for mapping UI ### Step 4.3 — Security Configuration Add to Spring Security config: - `/api/finance/import/**` requires `FINANCE_IMPORT` permission - File upload endpoint: max 10 MB - Rate limiting: max 5 uploads per hour per club (prevent abuse) ### Step 4.4 — Member IBAN Endpoint **Modified:** Existing `MemberController.java` ```java // PATCH /api/members/{id}/iban // Body: { "iban": "DE89370400440532013000" } // Requires: BANK_DATA consent verified for this member // Validates: IBAN checksum (ISO 13616) // Stores: member.iban + member.ibanConsentDate ``` Add IBAN validation utility: ```java public class IbanValidator { public static boolean isValid(String iban) { // 1. Remove spaces, uppercase // 2. Check length (DE = 22 chars) // 3. Move first 4 chars to end // 4. Replace letters with 2-digit numbers (A=10, B=11...) // 5. Modulo 97 == 1 } } ``` --- ## Phase 5: Frontend Import Wizard ### Step 5.1 — Import Page Layout **New file:** `cannamanage-frontend/src/app/[locale]/(admin)/finance/import/page.tsx` 4-step wizard layout: 1. **Upload** — Drag & drop zone, file picker, format auto-detection 2. **Configure** — For CSV: column mapping dialog. For MT940/CAMT: account summary 3. **Review** — Match results table with color-coded confidence badges 4. **Confirm** — Summary + bulk confirm button + completion ### Step 5.2 — Frontend Service **New file:** `cannamanage-frontend/src/services/bank-import.ts` ```typescript // Types export interface BankImportSession { ... } export interface BankTransaction { ... } export interface CsvColumnMapping { ... } export interface FormatDetectionResult { ... } // Hooks export function useUploadBankStatement() { ... } // POST multipart export function useDetectFormat() { ... } // POST detect export function useRunMatching(sessionId: string) { ... } export function useImportSession(sessionId: string) { ... } export function useImportTransactions(sessionId: string, filters) { ... } export function useConfirmMatch() { ... } export function useConfirmAll(sessionId: string) { ... } export function useSkipTransaction() { ... } export function useManualAssign() { ... } export function useCategorizeExpense() { ... } export function useCompleteSession(sessionId: string) { ... } export function useImportHistory() { ... } export function useCsvMappings() { ... } export function useCreateCsvMapping() { ... } export function useUpdateCsvMapping() { ... } export function useDeleteCsvMapping() { ... } ``` ### Step 5.3 — Upload Step Component **New file:** `cannamanage-frontend/src/app/[locale]/(admin)/finance/import/_components/upload-step.tsx` - Drag & drop zone (shadcn/ui compatible) - File type validation (accept: .csv, .sta, .mt940, .xml, .camt) - Auto-detect format on file selection - Show detected format badge (MT940 / CAMT.053 / CSV) - Tier enforcement: show constructive guidance (Info: UX #2 v3), not just a block: - Example: "MT940-Format ist ab dem Pro-Plan verfügbar. Exportieren Sie Ihren Kontoauszug stattdessen als CSV — die meisten Banken bieten dies unter 'Umsätze exportieren' an." - Include a "Plan vergleichen" link to the pricing page - Never use punitive language ("not allowed", "blocked") — frame as guidance toward a working alternative ### Step 5.4 — CSV Column Mapping Component **New file:** `cannamanage-frontend/src/app/[locale]/(admin)/finance/import/_components/csv-mapping-step.tsx` - Shows first 5 preview rows in a table - Dropdown selectors above each column: "Date", "Amount", "Reference", "Counterparty", "IBAN", "Ignore" - Encoding selector (ISO-8859-1, UTF-8, Windows-1252) - Date format selector (dd.MM.yyyy, yyyy-MM-dd, MM/dd/yyyy) - Decimal separator selector (comma, dot) - "Save as template" option (with name input) - "Load template" dropdown (if saved mappings exist) ### Step 5.5 — Match Review Table **New file:** `cannamanage-frontend/src/app/[locale]/(admin)/finance/import/_components/match-review-step.tsx` - Table columns: Date | Amount | Reference | Counterparty | Match | Confidence | Action - Color-coded rows: - Green (≥90%): auto-matched, checkbox pre-selected - Yellow (60-89%): suggested match, review needed - Red (<60%): unmatched, manual assignment needed - Gray (outgoing): expenses, categorize or skip - Confidence badge: percentage with color indicator - Match column: shows matched member name (or "—" for unmatched) - Action buttons per row: - ✅ Confirm (green/yellow rows) - 👤 Assign — **searchable combobox** using shadcn/ui `Command` component with type-ahead filtering by member name or member number (Advisory: UX #2). Required for clubs with 200+ members where a plain dropdown is unusable. - 📁 Expense (category dropdown for outgoing) - ⏭️ Skip (with reason input) - Running totals bar: "Confirmed: €2,450 | Pending: €350 | Skipped: €1,200" — financial overview of import progress - Top action bar: "Alle bestätigen" bulk button (green matches only) - Filter tabs: All | Matched | Suggested | Unmatched | Skipped ### Step 5.6 — Confirmation Step **New file:** `cannamanage-frontend/src/app/[locale]/(admin)/finance/import/_components/confirm-step.tsx` - Summary statistics: X confirmed, Y skipped, Z remaining - "Abschließen" button (complete session) - Warning if unresolved transactions remain - Link to payment list (shows new entries) ### Step 5.7 — Resume Import & Import History **New file:** `cannamanage-frontend/src/app/[locale]/(admin)/finance/import/_components/import-history.tsx` - **Resume Import banner** (Advisory: UX #1): At the top of the import page, if any sessions exist with status `PENDING` or `IN_REVIEW`, show a prominent banner: ``` ⚠️ Unvollständiger Import: "sparkasse_juni.csv" (15.06.2026) [Fortsetzen] [Verwerfen] ``` - "Fortsetzen" re-opens the session at the appropriate wizard step (IN_REVIEW → step 3) - "Verwerfen" sets session status to FAILED with reason "abgebrochen" - Multiple incomplete sessions: show a list with resume buttons for each - Table of past import sessions (completed + failed) - Columns: Date | Filename | Format | Total | Matched | Confirmed | Status - Click to view session detail (re-opens review step in read-only mode) ### Step 5.8 — Navigation Integration **Modified:** `cannamanage-frontend/src/data/navigations.ts` Add under Finance group: ```typescript { title: "Import", href: "/finance/import", icon: Upload, // lucide-react permission: "FINANCE_IMPORT" } ``` --- ## Cross-Cutting Concerns ### File Upload Configuration **Modified:** `cannamanage-api/src/main/resources/application.properties` ```properties # Bank import file upload spring.servlet.multipart.max-file-size=10MB spring.servlet.multipart.max-request-size=10MB ``` ### Retention Service Integration **Modified:** `cannamanage-service/src/main/java/de/cannamanage/service/RetentionService.java` Add scheduled cleanup: ```java @Scheduled(cron = "0 0 3 * * *") // Daily at 3 AM public void cleanupExpiredImportFiles() { // Delete raw uploaded files older than 30 days // (Parsed bank_transactions remain per §147 AO) } ``` ### Audit Integration New audit event types emitted: - `BANK_IMPORT_STARTED` — when file is uploaded and parsed - `BANK_IMPORT_COMPLETED` — when session is completed - `BANK_IMPORT_FAILED` — on parse error - `BANK_PAYMENT_CONFIRMED` — for each confirmed match (links to payment) --- ## File Inventory (New Files) | # | Path | Purpose | |---|------|---------| | 1 | `V30__bank_import_sessions.sql` | Import session table | | 2 | `V31__bank_transactions.sql` | Parsed transaction table | | 3 | `V32__csv_column_mappings.sql` | CSV templates + member IBAN | | 4 | `BankFormat.java` | Format enum | | 5 | `MatchStatus.java` | Match status enum | | 6 | `ImportSessionStatus.java` | Session status enum | | 7 | `BankImportSession.java` | JPA entity | | 8 | `BankTransaction.java` | JPA entity | | 9 | `CsvColumnMapping.java` | JPA entity | | 10 | `BankImportSessionRepository.java` | Repository | | 11 | `BankTransactionRepository.java` | Repository | | 12 | `CsvColumnMappingRepository.java` | Repository | | 13 | `BankStatementParser.java` | Parser interface | | 14 | `ParseResult.java` | Parser output record | | 15 | `ParsedTransaction.java` | Transaction record | | 16 | `Mt940Parser.java` | MT940 format parser | | 17 | `Camt053Parser.java` | CAMT.053 XML parser | | 18 | `CsvBankParser.java` | CSV parser | | 19 | `BankStatementParserService.java` | Format detection + delegation | | 20 | `PaymentMatchingService.java` | Matching algorithm | | 21 | `MatchResult.java` | Match output record | | 22 | `BankImportService.java` | Import orchestrator | | 23 | `BankImportController.java` | REST API | | 24 | `IbanValidator.java` | IBAN checksum validation | | 25 | DTO classes (8 files) | Request/response objects | | 26 | `bank-import.ts` | Frontend service hooks | | 27 | `page.tsx` (import) | Import wizard page | | 28 | `upload-step.tsx` | Upload component | | 29 | `csv-mapping-step.tsx` | CSV mapping component | | 30 | `match-review-step.tsx` | Match review table | | 31 | `confirm-step.tsx` | Confirmation component | | 32 | `import-history.tsx` | History table component | **Modified files:** ~8 (enums, Member entity, navigation, application.properties, TierLimitService, RetentionService, SecurityConfig, MemberController) **Total:** ~40 files (32 new + 8 modified)