# New Knowledge Base ## 2025-08-08 — Matching Prompt Refinements - Introduced dynamic confidence thresholds in large document prompt: - Light filtering: include confidence ≥ 6 - Strict mode: include confidence ≥ 8 - Added practical tolerances: amount within 1% or absolute 5.00; dates within ±7 days - Clarified identifiers: exact/close when present on both sides; neutral if missing on one side - Allowed entity-name normalization and abbreviations for similarity comparison - Renamed section to "MATCHING GUIDELINES" and removed ambiguous "STRONG matches" ## Bulk User Import with Cognito Recovery Mechanism ### Overview Enhanced the bulk user import feature to handle scenarios where users are successfully created in AWS Cognito but fail to be added to the database table. This recovery mechanism ensures data consistency and provides administrators with tools to resolve partial failures. ### Key Components #### 1. Cognito-Database Synchronization The system now tracks three distinct states: - **Successfully created**: Users created in both Cognito and database - **Failed**: Users that failed to be created in Cognito - **Cognito only**: Users created in Cognito but not in database (requires recovery) #### 2. Duplicate Prevention Added `checkUserExists()` function to prevent creating duplicate users in Cognito: ```javascript const checkUserExists = async (email) => { try { const { CognitoIdentityProviderClient, AdminGetUserCommand } = await import("@aws-sdk/client-cognito-identity-provider"); const userAuth = await getAuth(); const client = new CognitoIdentityProviderClient({ credentials: userAuth, region: "ap-southeast-2", }); const input = { UserPoolId: "ap-southeast-2_tRyN44wjs", Username: email, }; const command = new AdminGetUserCommand(input); await client.send(command); return true; // User exists } catch (error) { if (error.name === 'UserNotFoundException') { return false; // User doesn't exist } throw error; // Other error } }; ``` #### 3. Recovery Mechanism Implemented `retryDatabaseInsert()` function for users in "Cognito only" state: ```javascript const retryDatabaseInsert = useCallback(async () => { // Filter users that failed database insertion but exist in Cognito const cognitoOnlyUsers = previewData.filter(user => { return processingResults.errors.some(error => error.includes(user.email) && error.includes('Created in Cognito but failed to add to database') ); }); // Try individual inserts for each user for (const user of cognitoOnlyUsers) { try { const result = await API.post("apibigpond", "/adduser", { body: user }); // Handle success/failure } catch (err) { // Log error and continue with next user } } }, [processingResults, previewData, companies, currentUser.id, bulkImportUpdate]); ``` #### 4. Enhanced Error Tracking Modified the bulk import process to distinguish between different types of failures: - **Cognito failures**: Users that couldn't be created in Cognito - **Database failures**: Users created in Cognito but failed to be added to database - **Duplicate users**: Users that already exist in Cognito ### Best Practices Learned 1. **State Tracking**: Always track the state of each user creation step separately 2. **Recovery Mechanisms**: Provide tools to recover from partial failures 3. **Duplicate Prevention**: Check for existing users before attempting creation 4. **Individual Retry**: Use individual API calls for recovery instead of bulk operations 5. **User Feedback**: Provide clear information about what failed and recovery options ### Usage Scenarios 1. **Normal Import**: All users created successfully in both Cognito and database 2. **Partial Failure**: Some users created in Cognito but not in database 3. **Recovery**: Administrator uses retry mechanism to add Cognito-only users to database 4. **Duplicate Handling**: System prevents creating users that already exist in Cognito ## AWS Amplify API Response Structure ### Overview AWS Amplify API wrapper adds a `data` property to all API responses, which can cause issues if not handled correctly in frontend code. ### Key Learning When using AWS Amplify's `API.get()` or `API.post()` methods, the response structure is: ```javascript { "data": { "success": "call succeed!", "rows": [...] } } ``` ### Common Mistake Accessing response data directly as `res.rows` instead of `res.data.rows`: ```javascript // ❌ Incorrect const res = await API.get(apiName, path, myInit); if (res && Array.isArray(res.rows)) { // This will fail - res.rows is undefined } // ✅ Correct const res = await API.get(apiName, path, myInit); if (res && res.data && Array.isArray(res.data.rows)) { // This works correctly } ``` ### Best Practices 1. **Always check for `res.data`**: The AWS Amplify wrapper always adds this property 2. **Consistent pattern**: Use `res.data.rows` for all API responses in this project 3. **Error handling**: Check both `res` and `res.data` existence before accessing properties 4. **Debug logging**: Add console logs to verify response structure during development ### Files Affected - `src/pages/logsPage/details.jsx` - Fixed user dropdown population issue - Other components already use correct pattern: `src/components/tableBasic/users/userTable.jsx` ## OpenAI Usage Logging and Token Tracking Implementation ### Overview Implemented comprehensive logging system to track OpenAI API usage for audit and cost management purposes. This system captures detailed information about every OpenAI operation including token usage, processing time, and user context. ### Workpaper Logs Retrieval System #### Overview Added new "getLogs" action to the manageFileProcessTable function to retrieve workpaper logs with comprehensive filtering capabilities. This allows administrators and auditors to query the logging system for analysis and reporting purposes. #### Key Features - **Date Range Filtering**: Required fromDate and toDate parameters for precise time-based queries - **Optional User Filtering**: Filter by specific user_name or audit_firm_name - **Excluded Fields**: file_id and table_name are excluded from results for security/privacy - **Ordered Results**: Results are ordered by created_at DESC (most recent first) - **Comprehensive Validation**: Date format validation and logical date range checking #### Implementation Details ##### Function: handleGetLogs() ```javascript async function handleGetLogs(connection, parsedBody, origin) { // Extracts and validates parameters const { fromDate, toDate, userName = null, auditFirmName = null } = parsedBody; // Validates required date parameters and format // Builds dynamic SQL query with conditional filters // Returns structured response with metadata } ``` ##### Query Building Strategy ```javascript // Base query with required date filtering let query = ` SELECT id, user_name, audit_firm_name, operation_type, document_name, document_length, input_tokens, output_tokens, total_tokens, model_used, processing_time_ms, success, error_message, step_number, created_at FROM workpaper_logs WHERE created_at >= ? AND created_at <= ? `; // Conditional filters added only when provided if (userName) { query += ` AND user_name = ?`; queryParams.push(userName); } if (auditFirmName) { query += ` AND audit_firm_name = ?`; queryParams.push(auditFirmName); } ``` ##### API Usage Example ```json { "action": "getLogs", "fromDate": "2024-01-01T00:00:00.000Z", "toDate": "2024-12-31T23:59:59.999Z", "userName": "john.doe@auditfirm.com", "auditFirmName": "BigPond Auditors" } ``` ##### Response Format ```json { "statusCode": 200, "headers": { /* CORS headers */ }, "body": { "data": [ /* array of log records */ ], "metadata": { "totalLogs": 150, "fromDate": "2024-01-01T00:00:00.000Z", "toDate": "2024-12-31T23:59:59.999Z", "filtersApplied": { "userName": true, "auditFirmName": true }, "query": { "fromDate": "2024-01-01T00:00:00.000Z", "toDate": "2024-12-31T23:59:59.999Z", "userName": "john.doe@auditfirm.com", "auditFirmName": "BigPond Auditors" } } } } ``` #### Best Practices Learned 1. **Parameterized Queries**: Always use parameterized queries to prevent SQL injection 2. **Conditional Filtering**: Build dynamic queries based on provided parameters 3. **Date Validation**: Validate both format and logical relationships (fromDate ≤ toDate) 4. **Comprehensive Logging**: Log query execution and results for debugging 5. **Structured Responses**: Include metadata with filter information and result counts 6. **Error Handling**: Provide specific error messages for different validation failures 7. **Security**: Exclude sensitive fields (file_id, table_name) from results #### Use Cases 1. **Audit Trail Analysis**: Track user activity and system usage patterns 2. **Cost Management**: Monitor OpenAI token usage and processing times 3. **Performance Monitoring**: Analyze processing times and success rates 4. **User Activity Reports**: Generate reports for specific users or audit firms 5. **Error Analysis**: Identify patterns in processing failures #### Integration Points - **Frontend**: Can be called from React components for log viewing interfaces - **Reporting**: Provides data for automated report generation - **Monitoring**: Supports real-time system monitoring and alerting - **Compliance**: Enables audit trail compliance for regulatory requirements ### Key Components #### 1. Database Table Structure Created `workpaper_logs` MySQL table with the following schema: - **User Context**: `user_name`, `audit_firm_name` - **Operation Details**: `operation_type` (matching/pdf_processing), `document_name`, `document_length` - **Token Usage**: `input_tokens`, `output_tokens`, `total_tokens`, `model_used` - **Performance**: `processing_time_ms`, `success`, `error_message` - **Tracking**: `file_id`, `table_name`, `step_number`, `created_at` #### 2. Token Usage Capture Modified OpenAI API response handling to extract usage statistics: ```javascript const tokenUsage = { prompt_tokens: responseData.usage?.prompt_tokens || 0, completion_tokens: responseData.usage?.completion_tokens || 0, total_tokens: responseData.usage?.total_tokens || 0, model: responseData.model || model }; ``` #### 3. Batch Processing Token Aggregation For multi-page documents processed in batches, implemented token aggregation: - Collects usage from each batch - Sums up total tokens across all API calls - Maintains model information consistency #### 4. Automatic Logging Integration Enhanced both `processDocument` and `processMatching` methods to automatically log operations when user context is available in options. ### Best Practices Learned 1. **Non-blocking Error Handling**: Logging failures don't interrupt main processing 2. **Token Aggregation**: Essential for accurate cost tracking in batch operations 3. **User Context Propagation**: Pass user info through options chain for consistent logging 4. **Performance Tracking**: Capture processing time for optimization insights 5. **Comprehensive Error Logging**: Log both successful and failed operations ### Usage Requirements To enable logging, ensure options include: - `userName`: User identifier - `auditFirmName`: Firm identifier - `connection`: Database connection ## Excel Field Type Detection Enhancement ### Problem Excel file imports were incorrectly detecting field types, showing "Number" for what should be "Date" and "Currency" fields. Excel stores dates as serial numbers and currency values as regular numbers without formatting information. ### Root Cause Analysis 1. **Excel Date Storage**: Excel stores dates as serial numbers (e.g., 45488 for 2024-07-20), not formatted strings 2. **Currency Formatting Loss**: Currency values in Excel appear as regular numbers (51000 instead of $51,000) when read programmatically 3. **Limited Field Name Analysis**: Column headers like "A1", "B1" don't provide semantic context for type detection 4. **Insufficient Pattern Recognition**: Original logic only checked for string patterns, missing Excel-specific data patterns ### Solution Implemented #### 1. Enhanced Excel Parsing Options Updated `parseExcelHeaders` function in StepCard.jsx to preserve raw numeric values and formatting information: ```javascript const workbook = XLSX.read(arrayBuffer, { type: 'array', cellDates: false, // Keep as serial numbers for better detection cellNF: true, // Include number formats cellStyles: true // Include cell styles }); const sheetData = XLSX.utils.sheet_to_json(firstSheet, { header: 1, raw: true, // Keep raw values (numbers as numbers) defval: undefined // Use undefined for empty cells }); ``` #### 2. Excel-Specific Detection Functions Added new detection functions in formatters.js: **Excel Date Serial Detection**: ```javascript export const isExcelDateSerial = (value) => { const num = Number(value); if (isNaN(num)) return false; // Extended range: 1 = 1900-01-01, 55000 = ~2050 return num >= 1 && num <= 55000; }; ``` **Currency-Like Number Detection**: ```javascript export const isCurrencyLikeNumber = (value) => { const num = Number(value); if (isNaN(num)) return false; // Currency indicators: // 1. Round numbers ending in .00 (like 1000.00) // 2. Numbers with exactly 2 decimal places // 3. Large whole numbers that could be currency (like 51000) const valueStr = String(value); if (/\.\d{2}$/.test(valueStr) && num > 0) return true; if (Number.isInteger(num) && num >= 50 && num <= 10000000) return true; if (num > 0 && (num % 1 !== 0) && num < 1000000) return true; return false; }; ``` #### 3. Excel Native Cell Type Detection Added usage of Excel's built-in cell type information as the primary detection method: ```javascript // Excel cell types (t property): // 'b' - Boolean // 'e' - Error // 'n' - Number // 's' - String // 'd' - Date // 'z' - Stub (empty) // Count cell types and use majority vote const cellTypeStats = {}; cellTypes.forEach(type => { cellTypeStats[type] = (cellTypeStats[type] || 0) + 1; }); // If majority are 'd' (Date), it's definitely a date field if (cellTypeStats['d'] && cellTypeStats['d'] / totalCells > 0.5) { return FIELD_TYPES.DATE; } ``` #### 4. Excel Format Recognition Added parsing of Excel cell formatting as secondary detection method: ```javascript // Check for Excel date formats (common patterns) const dateFormatPatterns = [ /d+[\/\-\.]m+[\/\-\.]y+/i, // dd/mm/yyyy, dd-mm-yyyy, etc. /m+[\/\-\.]d+[\/\-\.]y+/i, // mm/dd/yyyy, mm-dd-yyyy, etc. /y+[\/\-\.]m+[\/\-\.]d+/i, // yyyy/mm/dd, yyyy-mm-dd, etc. ]; // Check for Excel currency formats const currencyFormatPatterns = [ /\$|£|€|¥|₹/, // Currency symbols /accounting/i, // Accounting format /#,##0\.00/, // Number format with decimals ]; ``` #### 4. Enhanced Detection Priority Logic Implemented a priority-based detection system leveraging Excel's native capabilities: 1. **Priority 0**: Excel native cell types (highest priority) - uses Excel's built-in `t` property 2. **Priority 1**: Excel formatting clues - uses Excel's number format strings 3. **Priority 2**: Field name patterns for well-known types 4. **Priority 3**: Strong data pattern matches 5. **Priority 4**: Currency detection (more aggressive for Excel) #### 5. Improved Currency Detection Thresholds Made currency detection more sensitive for Excel data: - Reduced threshold from 40% to 30% for currency-like patterns - Added detection for large numbers (≥1000) as potential unformatted currency - Enhanced pattern matching for accounting-style decimals (like 100.1) ### Key Improvements 1. **🚀 REVOLUTIONARY APPROACH**: Complete paradigm shift - now TRUSTS EXCEL FORMATTING instead of trying to guess 2. **🚀 TRUST EXCEL COMPLETELY**: If Excel formats a cell as Date → Date, Number → Number, **TEXT → TEXT** (including "Number Stored as Text") 3. **🚀 SIMPLIFIED DETECTION**: Removed all complex range checking, threshold calculations, and override systems that caused false positives 4. **🚀 NO MORE GUESSING**: Eliminated logic that tried to guess if numbers "might be dates" based on ranges (40000+, etc.) 5. **🔥 CONSERVATIVE CURRENCY**: Only decimal numbers (123.45, 100.5) are considered currency, not plain integers (100, 200, 23321) 6. **Excel Cell Type Trust**: Properly detects Excel's 's' (String) type, including numbers stored as text 7. **Enhanced Format Pattern Matching**: Added 20+ Excel date format patterns including format codes (14, 15, 16, etc.) 8. **Priority System**: Excel formatting → Excel cell types ('d'=Date, 's'=Text, 'n'=Number) → Field name patterns → Simple fallback 9. **Eliminated False Positives**: Small numbers (100.1, 200) can no longer be misclassified as currency or dates 10. **Philosophy Change**: "If Excel says it's a date, it's a date. If Excel says it's text, it's text. Don't overthink it." 11. **Debug Logging**: Enhanced logging shows when system is trusting Excel vs falling back to pattern matching 12. **Reliability**: Much more reliable detection by trusting the source (Excel) rather than guessing from data patterns ### Testing Results After implementation, Excel files now correctly identify: - Date columns (even when stored as serial numbers) - Currency columns (even when formatted as plain numbers) - Mixed data types with appropriate fallbacks ### Best Practices for Excel Field Type Detection 1. **Preserve Raw Values**: Use `raw: true` option when parsing Excel to maintain numeric precision 2. **Leverage Formatting**: Always check Excel cell formatting for type hints 3. **Multiple Detection Methods**: Combine serial number detection, pattern matching, and format analysis 4. **Aggressive Currency Detection**: Excel currency often appears as plain numbers, so use lower thresholds 5. **Debug Logging**: Enable development logging to troubleshoot detection issues - `documentName`: Document identifier This implementation provides complete audit trail for OpenAI usage across the application. ## Fixing React DOM Validation Errors in Table Components ### Problem: Invalid HTML Structure in Table Components React enforces strict HTML structure validation, especially for table elements. Common violations include: - Rendering `
` or other non-table elements inside `` - Placing tooltips, modals, or other UI elements directly inside table body - Using fragments that mix table and non-table elements in table context **Error Example:** ``` Warning: validateDOMNesting(...):
cannot appear as child of ``` ### Solution: Separate Table Structure from UI Components 1. **Table Elements Only**: Ensure `` only contains `` elements 2. **Move UI Elements Outside**: Render tooltips, modals, and overlays outside table structure 3. **State Management**: Use parent component state to manage UI element visibility 4. **Fixed Positioning**: Use `position: fixed` for overlays to avoid DOM nesting issues ### Implementation Example **Before (Problematic):** ```jsx const RowTooltip = ({ row, getMatchStatus }) => ( <> Content {show && (
Tooltip content
)} ); // In table body: {rows.map(row => ( ))} ``` **After (Fixed):** ```jsx const RowTooltip = ({ row, onTooltipChange }) => ( onTooltipChange(true, position, content)} onMouseLeave={() => onTooltipChange(false, position, '')} > Content ); // In parent component: const [tooltip, setTooltip] = useState({ show: false, position: {}, content: '' }); const handleTooltipChange = (show, position, content) => { setTooltip({ show, position, content }); }; // In render: <> {rows.map(row => ( ))} {/* Tooltip rendered outside table */} {tooltip.show && (
{tooltip.content}
)} ``` ### Key Points - **Table purity**: Keep table elements semantically correct - **Event delegation**: Use parent component to manage UI state - **Portal alternative**: Could also use React.createPortal for complex cases - **Performance**: Fixed positioning avoids layout recalculations ## Code Organization: Moving Utility Functions to Utils Folder ### Problem: Component Files Becoming Too Large When components like `StepCard.jsx` start containing many utility functions, the file becomes: - Hard to maintain and read - Difficult to find specific functions - Prevents code reuse across other components - Mixes component logic with utility functions ### Solution: Extract Utility Functions to Utils Folder Best practices for organizing utility functions: 1. **Create/Use Existing Utils Files**: Place utility functions in appropriate utils files 2. **Group Related Functions**: Keep similar functions together (e.g., data type detection in formatters.js) 3. **Export Functions**: Make functions available for import using `export` 4. **Import Where Needed**: Use named imports to bring functions into components ### Implementation Example Before (in StepCard.jsx): ```javascript const isDateValue = useCallback((value) => { // ... date validation logic }, []); const isDecimalValue = useCallback((value) => { // ... decimal validation logic }, []); const isNumericValue = useCallback((value) => { // ... numeric validation logic }, []); const detectFieldTypeFromData = useCallback((columnData, fieldName) => { // ... field type detection logic using the above functions }, []); ``` After (in utils/formatters.js): ```javascript export const isDateValue = (value) => { // ... date validation logic }; export const isDecimalValue = (value) => { // ... decimal validation logic }; export const isNumericValue = (value) => { // ... numeric validation logic }; export const detectFieldTypeFromData = (columnData, fieldName) => { // ... field type detection logic using the above functions }; ``` In component: ```javascript import { isDateValue, isDecimalValue, isNumericValue, detectFieldTypeFromData } from '../utils/formatters'; ``` ### Benefits - **Improved Maintainability**: Easier to find and update utility functions - **Better Code Reuse**: Functions can be imported by multiple components - **Cleaner Components**: Components focus on their primary responsibility - **Better Testing**: Utility functions can be tested independently - **Reduced Bundle Size**: Functions are only included when imported - **Better Performance**: Removes unnecessary useCallback overhead for static utility functions - **Simpler Dependencies**: No need to track utility functions in component dependency arrays ### Utils Folder Structure ``` src/pages/customMatchPage/utils/ ├── constants.js # Application constants ├── formatters.js # Data formatting and validation functions ├── safeLocalStorage.js # Safe localStorage operations └── storageQueue.js # Queue management for localStorage ``` ## Excel Cell Styling with xlsx-js-style Library ### Problem: Standard xlsx Library Lacks Styling Support The standard `xlsx` library (SheetJS Community Edition) does not support cell styling including: - Font colors and formatting - Hyperlink styling (blue color, underline) - Cell borders and backgrounds - Number formatting styles When using the standard `xlsx` library, hyperlinks appear as black text instead of the expected blue color with underline. ### Solution: Use xlsx-js-style Library The `xlsx-js-style` library is a fork of SheetJS that adds cell styling support: 1. **Installation**: Replace `xlsx` with `xlsx-js-style` 2. **Import**: Use `import * as XLSX from "xlsx-js-style"` 3. **Cell Styling**: Apply styles directly to cell objects using the `s` property 4. **Hyperlink Colors**: Use standard Excel hyperlink blue color `#0563C1` ### Implementation ```javascript // Create a styled hyperlink cell const styledCell = { f: `HYPERLINK("${filePath}", "${displayText}")`, t: 's', l: { Target: filePath, Tooltip: displayText }, s: { font: { color: { rgb: "0563C1" }, // Standard Excel hyperlink blue underline: true } } }; ``` ### Key Differences from Standard xlsx - **Styling Support**: Direct cell styling via `s` property - **Color Format**: Use RGB format like `"0563C1"` for colors - **Font Properties**: Support for bold, italic, underline, color, size - **Performance**: Slightly larger bundle size due to styling features - **Compatibility**: Drop-in replacement for standard xlsx library ## Handling localStorage Race Conditions in React ### Problem: Race Conditions in localStorage When multiple components or processes try to read from and write to localStorage simultaneously, race conditions can occur. This happens because: 1. localStorage operations are synchronous and block the main thread 2. React's lifecycle and state updates can cause components to compete for access 3. Multiple event handlers or effects may trigger updates to the same keys 4. There's no built-in locking or queuing mechanism in the browser's localStorage API ### Solution: Implementing a Queue-Based System To solve race conditions in localStorage access, we implemented a queue-based system: 1. **Key-Based Queues**: Each localStorage key gets its own operation queue 2. **Sequential Processing**: Operations for each key are processed one at a time 3. **State Synchronization**: Components are updated via React state after storage changes 4. **Cross-Tab Communication**: Storage events broadcast changes across tabs ### Implementation Details - **Queue Management**: Operations are queued and processed sequentially - **Error Handling**: Try-catch blocks protect against JSON parsing errors - **Default Values**: Safe fallbacks when localStorage items don't exist - **Atomic Updates**: Transform functions ensure atomic read-modify-write cycles - **State Synchronization**: React state updates reflect localStorage changes ## Dynamic Field Indexing in Data Matching Systems ### Problem: Field Name Variability Across Data Sources When working with data from different sources, field names often vary even when they represent the same type of information: 1. Different naming conventions (e.g., "PayeeName" vs "Payee Name" vs "Supplier") 2. Different levels of specificity (e.g., "Date" vs "Payment Date" vs "Transaction Date") 3. Different capitalization and formatting (e.g., "bankAccount" vs "Bank_Account") 4. Different data structures across processing steps ### Solution: Implementing Dynamic Field Indexing To handle field name variability, we implemented a dynamic indexing system: 1. **Comprehensive Field Mapping**: Create indices for all available fields 2. **Multi-level Lookup**: Try exact matches first, then value-based matching 3. **Pure Data-Driven Approach**: No hardcoded field assumptions 4. **Flexible Value Matching**: Match on field values regardless of field names 5. **Diagnostic Logging**: Track which fields are being used for matching ### Implementation Details ```javascript // Create mapping of all fields to their values and associated rows const fieldMaps = new Map(); // Field name -> value -> rows // Build indices from all available fields data.forEach((row, index) => { Object.entries(row).forEach(([key, value]) => { if (key.startsWith('step1_') && value && value !== '-') { const fieldName = key.replace('step1_', ''); // Initialize nested maps if (!fieldMaps.has(fieldName)) { fieldMaps.set(fieldName, new Map()); } const valueMap = fieldMaps.get(fieldName); if (!valueMap.has(value)) { valueMap.set(value, []); } // Add row to the index valueMap.get(value).push({ row, index }); } }); }); // Matching strategy 1: Direct field name match if (fieldMaps.has(fieldName) && fieldMaps.get(fieldName).has(value)) { return fieldMaps.get(fieldName).get(value); } // Matching strategy 2: Match by value across all fields if (matchedRows.length === 0) { for (const [indexedField, valueMap] of fieldMaps.entries()) { if (valueMap.has(fieldValue)) { return valueMap.get(fieldValue); } } } ``` ### Benefits of Dynamic Field Indexing 1. **Resilience to Schema Changes**: System works even when field names change 2. **Better Match Quality**: More opportunities to find the right matches 3. **No Assumptions**: No reliance on predefined field mappings 4. **Pure Data-Driven**: Adapts automatically to the actual data structure 5. **Improved Debugging**: Easier to identify which fields contributed to matches ### Best Practices for Dynamic Field Indexing 1. **Index All Available Fields**: Create comprehensive indices of all fields 2. **Use Multi-pass Matching**: Try exact matches first, then value-based matches 3. **Add Diagnostic Logging**: Record which fields and strategies led to successful matches 4. **Prioritize Strategies**: Give higher priority to direct field matches over value-based matches 5. **Consider Normalization**: Normalize values (e.g., dates, monetary amounts) before matching 6. **Add Match Scoring**: Implement a scoring system for ranking multiple match candidates ## Multi-Strategy Record Matching ### Problem: Inconsistent Data Matching Across Sources When working with financial data from multiple sources, exact field-to-field matching often fails due to: 1. Inconsistent field naming conventions between data sources 2. Different data formats for the same semantic information 3. Mixed quality data with partial or incomplete records 4. Dynamic field generation in processing steps ### Solution: Implementing a Cascading Match Strategy We implemented a four-tier cascading matching system that progressively falls back to more flexible matching methods: 1. **Exact Field+Value Matching**: Match records based on identical field names and values 2. **Value-Only Matching**: Match based on values regardless of field names 3. **Value Combination Matching**: Match on unique combinations of multiple values 4. **Partial Text Matching**: Use substring matching for approximate matches ### Implementation Details ```javascript // Method 1: Try exact field+value matches for (const [key, value] of Object.entries(previous)) { if (!value || value === '-') continue; const fieldValueKey = `${key}:${value}`; if (fieldToRecordMap.has(fieldValueKey)) { matchedRows = fieldToRecordMap.get(fieldValueKey); if (matchedRows.length > 0) { matchMethod = `exact field match: "${key}" with value "${value}"`; break; } } } // Method 2: If no match, try value-only matches (regardless of field name) if (matchedRows.length === 0) { for (const [key, value] of Object.entries(previous)) { if (!value || value === '-' || typeof value !== 'string') continue; const valueKey = `value:${value}`; if (fieldToRecordMap.has(valueKey)) { matchedRows = fieldToRecordMap.get(valueKey); if (matchedRows.length > 0) { matchMethod = `value-only match: "${value}" found in step 1`; break; } } } } // Method 3: Try matching on combinations of values if (matchedRows.length === 0) { const uniqueValues = Object.entries(previous) .filter(([_, v]) => v !== null && v !== undefined && v !== '' && v !== '-') .map(([_, v]) => String(v).trim()) .sort(); for (let length = Math.min(3, uniqueValues.length); length >= 1; length--) { let found = false; for (let i = 0; i <= uniqueValues.length - length; i++) { const valueCombination = uniqueValues.slice(i, i + length).join('|'); if (recordByUniqueValues.has(valueCombination)) { matchedRows = recordByUniqueValues.get(valueCombination); if (matchedRows.length > 0) { matchMethod = `value combination match: "${valueCombination}"`; found = true; break; } } } if (found) break; } } // Method 4: Last resort - scan all rows in step 1 for any matching value if (matchedRows.length === 0) { const step1Rows = data.filter(row => row._stepNumber === 1); for (const [key, value] of Object.entries(previous)) { if (!value || value === '-' || typeof value !== 'string') continue; const valueStr = String(value).trim(); if (valueStr.length < 3) continue; // Skip very short values const matchingRows = step1Rows.filter(row => { return Object.entries(row).some(([rowKey, rowValue]) => { if (!rowKey.startsWith('step1_')) return false; return String(rowValue).includes(valueStr); }); }); if (matchingRows.length > 0) { matchedRows = matchingRows.map(row => ({ row, record: row._originalRecord })); matchMethod = `partial text match: text "${valueStr}" found in step 1 row`; break; } } } ``` ### Benefits of Multi-Strategy Matching 1. **Higher Match Success Rate**: More records are matched correctly 2. **Graceful Degradation**: System still finds matches when exact matching fails 3. **Quality Indicators**: Match method provides insight into match quality 4. **Flexibility**: Works with inconsistent or evolving data structures 5. **Transparency**: Logs the specific method used for each match ## Double JSON Parsing Issue with localStorage ### Problem: Object Corruption with Redundant JSON Parsing When using utilities for localStorage, a common pitfall is parsing JSON data multiple times: 1. The utility function already parses the JSON from localStorage 2. The component code re-parses the already parsed data 3. This leads to unexpected behavior, data corruption, or errors ### Solution: Type-Aware Parsing in localStorage Utilities To solve the double parsing issue: 1. **Type Checking**: Verify if data is already an object before parsing 2. **Error Handling**: Add specific error handling for redundant parsing 3. **Consistent Return Types**: Ensure utility functions return consistent data types ### Implementation Details ```javascript // Parse the JSON data with type checking let matchedData; try { matchedData = JSON.parse(row[matchedDataKey]); } catch (e) { // The data might already be an object if it was loaded with safeGetItem if (typeof row[matchedDataKey] === 'object') { matchedData = row[matchedDataKey]; } else { throw e; } } ``` ### Best Practices for localStorage in React 1. **Centralize Access**: Use wrapper functions or hooks for all localStorage access 2. **Use Queues for Critical Keys**: Implement queues for frequently updated keys 3. **Use Type Checking**: Check if data is already parsed before parsing 4. **Employ Default Values**: Always provide defaults when reading from localStorage 5. **Handle Errors**: Wrap all JSON parsing in try-catch blocks 6. **Batch Updates**: Combine multiple updates when possible 7. **Validate Data**: Check data types and structure before storing 8. **Monitor Size Limits**: Stay within the browser's storage limits (typically 5-10MB) 9. **Consider Alternatives**: For complex data, consider IndexedDB or backend storage ### Performance Considerations - localStorage operations are synchronous and can block the main thread - JSON.stringify/parse for large objects can be expensive - Queuing adds slight overhead but prevents data corruption - Storage event listeners provide cross-tab synchronization but add some overhead ### Conclusion Implementing a proper queuing system for localStorage with type-aware parsing is essential for applications that rely heavily on client-side storage and have complex state management. It prevents data corruption, race conditions, and unexpected behavior, while providing a more reliable foundation for data persistence. ## Financial Record Matching with Account Numbers ### Problem: Matching Banking Transactions Across Systems Matching financial records across different systems is particularly challenging due to: 1. Inconsistent account number formats (with or without separators) 2. Partial account numbers for security purposes 3. Different identifiers used across financial systems 4. Multiple accounts belonging to the same entity 5. Structure and content varying greatly between financial sources ### Solution: Account Number Recognition and Normalization To reliably match financial records, we implemented specialized account number handling: 1. **Account Number Normalization**: Standardize formats by removing separators 2. **Pattern Recognition**: Identify common account number patterns 3. **Priority-Based Matching**: Assign confidence levels to different match types 4. **Field Synchronization**: Maintain data alignment between matched records ### Implementation Details ```javascript /** * Normalize account number by removing spaces, dashes and other common separators */ const normalizeAccountNumber = (accountNumber) => { if (!accountNumber || typeof accountNumber !== 'string') return accountNumber; // Remove common separators, spaces, and make lowercase for consistent matching return accountNumber.replace(/[\s\-\.\/\\]/g, '').toLowerCase(); }; /** * Extract potential account number from a string with account info */ const extractAccountNumber = (value) => { if (!value || typeof value !== 'string') return null; // Look for patterns that may be account numbers const accountPatterns = [ /\b\d{3}[\s\-]?\d{3}[\s\-]?\d{4,7}\b/, // 3-3-4+ pattern (like 123-456-7890) /\b\d{6,}[\s\-]?\d{2}[\s\-]?\d{4,}\b/, // 6+-2-4+ pattern /\b\d{4}[\s\-]?\d{4}[\s\-]?\d{4}[\s\-]?\d{4}\b/, // Card-like pattern /\b\d{8,}\b/ // Any 8+ digit number ]; for (const pattern of accountPatterns) { const match = value.match(pattern); if (match) return normalizeAccountNumber(match[0]); } return null; }; // In field mapping, create specialized maps for account data const accountNumberMap = new Map(); // When encountering account fields, add to special maps with high priority if (lowerKey.includes('account') && lowerKey.includes('number')) { if (!accountNumberMap.has(value)) { accountNumberMap.set(value, []); } accountNumberMap.get(value).push({ row, record, priority: 10 }); // Also add normalized version const normalizedAccount = normalizeAccountNumber(value); if (normalizedAccount && normalizedAccount !== value) { if (!accountNumberMap.has(normalizedAccount)) { accountNumberMap.set(normalizedAccount, []); } accountNumberMap.get(normalizedAccount).push({ row, record, priority: 9 }); } } // Create multi-tier matching strategy // 1. Try exact account number match if (accountNumberMap.has(value)) { matchedRows = accountNumberMap.get(value); matchMethod = `exact account number match: "${value}"`; } // 2. Try normalized account number match const normalizedAccount = normalizeAccountNumber(value); if (matchedRows.length === 0 && normalizedAccount && accountNumberMap.has(normalizedAccount)) { matchedRows = accountNumberMap.get(normalizedAccount); matchMethod = `normalized account number match: "${normalizedAccount}"`; } // 3. Try extracted account number const extractedAccount = extractAccountNumber(value); if (matchedRows.length === 0 && extractedAccount && accountNumberMap.has(extractedAccount)) { matchedRows = accountNumberMap.get(extractedAccount); matchMethod = `extracted account number match: "${extractedAccount}"`; } ``` ### Field Synchronization for Financial Data A critical aspect of financial record matching is maintaining field alignment between steps: ```javascript // Create bidirectional field mapping between steps const fieldMappings = new Set(); // Track which fields appear in the current match Object.entries(current).forEach(([key, value]) => { fieldMappings.add(key); // Add to step keys const stepIndex = currentStepNumber - 1; if (!stepKeys[stepIndex]) { stepKeys[stepIndex] = new Set(); } stepKeys[stepIndex].add(key); }); // Synchronize fields between steps const prevStepIndex = previousStepNumber - 1; if (prevStepIndex >= 0 && stepKeys[prevStepIndex]) { // Add current step fields to previous step keys if missing fieldMappings.forEach(fieldName => { stepKeys[prevStepIndex].add(fieldName); }); // Add previous step fields to current step keys stepKeys[prevStepIndex].forEach(fieldName => { if (!stepKeys[currentStepNumber - 1]) { stepKeys[currentStepNumber - 1] = new Set(); } stepKeys[currentStepNumber - 1].add(fieldName); }); } ``` ### Benefits of Specialized Financial Record Matching 1. **Higher Match Accuracy**: Better identification of corresponding financial records 2. **Format Independence**: Works regardless of separator styles or formatting 3. **Error Tolerance**: Handles partial or masked account numbers 4. **Flexible Patterns**: Adapts to common financial identifier formats 5. **Priority-Based**: Matches based on field importance in financial contexts 6. **Field Alignment**: Ensures all financial data fields align properly between steps ### Best Practices for Financial Record Matching 1. **Normalize All Account Numbers**: Remove separators and standardize formatting 2. **Apply Multiple Pattern Recognition**: Use regex patterns for common account formats 3. **Implement Field Type Awareness**: Pay special attention to amount and date fields 4. **Create Special Maps**: Use dedicated maps for bank account and payee information 5. **Assign Match Confidence**: Give higher priority to account number matches 6. **Maintain Field Consistency**: Ensure all related fields appear in matched records 7. **Track Match Methods**: Log which identifier was used for successful matches ### Performance Considerations - Account number extraction adds some processing overhead but greatly improves match quality - Use appropriate regex patterns that balance flexibility with performance - Consider caching normalized versions of account numbers to avoid repeated processing - Limit pattern matching to fields that may contain account information ### Conclusion Specialized financial record matching with account number recognition significantly improves the accuracy of matching transactions across different systems. By implementing normalization, pattern recognition, and field synchronization, the system can reliably connect related financial records even when formats and field names differ substantially between data sources. ### Best Practices for localStorage in React 1. **Centralize Access**: Use wrapper functions or hooks for all localStorage access 2. **Use Queues for Critical Keys**: Implement queues for frequently updated keys 3. **Use Type Checking**: Check if data is already parsed before parsing 4. **Employ Default Values**: Always provide defaults when reading from localStorage 5. **Handle Errors**: Wrap all JSON parsing in try-catch blocks 6. **Batch Updates**: Combine multiple updates when possible 7. **Validate Data**: Check data types and structure before storing 8. **Monitor Size Limits**: Stay within the browser's storage limits (typically 5-10MB) 9. **Consider Alternatives**: For complex data, consider IndexedDB or backend storage 10. **Use Account Number Handling**: Implement specialized account number handling ### Performance Considerations - localStorage operations are synchronous and can block the main thread - JSON.stringify/parse for large objects can be expensive - Queuing adds slight overhead but prevents data corruption - Storage event listeners provide cross-tab synchronization but add some overhead ### Conclusion Implementing a proper queuing system for localStorage with type-aware parsing is essential for applications that rely heavily on client-side storage and have complex state management. It prevents data corruption, race conditions, and unexpected behavior, while providing a more reliable foundation for data persistence. ## Pure Data-Driven Field Handling ### Problem: Field Names Vary Unpredictably Across Data Sources When working with data from multiple sources, relying on hardcoded field name lists causes problems: 1. Field names can be completely different across systems while referring to the same data 2. The same field name might represent different data in different contexts 3. New data sources may introduce unexpected field names 4. Schema changes can break hardcoded field mappings 5. Hardcoded assumptions about field importance may not apply to all data sets ### Solution: Statistical Field Analysis and Dynamic Priority To create a truly adaptable matching system, we implemented a pure data-driven approach: 1. **Statistical Analysis**: Analyze field value distribution to determine uniqueness 2. **Dynamic Priority Scoring**: Assign priority based on statistical uniqueness 3. **Adaptable Field Matching**: Match based on actual data patterns, not field names 4. **Value Frequency Analysis**: Identify potential key fields by value distribution ### Implementation Details ```javascript // Track the total records and value frequencies let totalFirstStepRecords = 0; const fieldValueCounts = new Map(); // Maps field names to a map of values to counts // First pass - analyze field value uniqueness Object.entries(record).forEach(([key, value]) => { if (value && value !== '-') { // Track overall value frequency const valueKey = `value:${value}`; if (!allValues.has(valueKey)) { allValues.set(valueKey, 0); } allValues.set(valueKey, allValues.get(valueKey) + 1); // Track value frequency per field if (!fieldValueCounts.has(key)) { fieldValueCounts.set(key, new Map()); } const fieldMap = fieldValueCounts.get(key); if (!fieldMap.has(value)) { fieldMap.set(value, 0); } fieldMap.set(value, fieldMap.get(value) + 1); } }); // Calculate uniqueness score for each field-value pair Object.entries(record).forEach(([key, value]) => { if (value && value !== '-') { // Calculate uniqueness score - more unique values have higher priority let uniquenessScore = 1; // Default priority if (fieldValueCounts.has(key) && fieldValueCounts.get(key).has(value)) { const occurrenceCount = fieldValueCounts.get(key).get(value); // Unique values (occurring only once) get highest priority if (occurrenceCount === 1) { uniquenessScore = 10; } // Values occurring in < 10% of records get high priority else if (occurrenceCount <= totalFirstStepRecords * 0.1) { uniquenessScore = 8; } // Values occurring in < 25% of records get medium priority else if (occurrenceCount <= totalFirstStepRecords * 0.25) { uniquenessScore = 5; } // Otherwise low priority (common values) else { uniquenessScore = 2; } } // Store the field-value pair with its calculated priority fieldToRecordMap.get(fieldValueKey).push({ row, record, priority: uniquenessScore, field: key, value: value }); } }); ``` ### Dynamic Matching Strategy Instead of relying on hardcoded field lists, we use a dynamic scoring approach for new records: ```javascript // Sort the previous record fields by potential uniqueness const scoredPreviousFields = Object.entries(previous) .filter(([_, value]) => value && value !== '-') .map(([key, value]) => { // Calculate an estimated uniqueness score based on the value pattern let estimatedScore = 3; // Default score // Longer values tend to be more unique if (typeof value === 'string') { if (value.length > 15) estimatedScore += 3; else if (value.length > 8) estimatedScore += 2; // Values with mixed characters tend to be identifiers if (/[a-zA-Z]/.test(value) && /[0-9]/.test(value)) estimatedScore += 2; // Values with separators may be formatted identifiers if (/[-_.\\/]/.test(value)) estimatedScore += 1; } return { key, value, score: estimatedScore }; }) .sort((a, b) => b.score - a.score); // Sort by descending score ``` ### Multi-Value Matching For complex matches, we evaluate combinations of values across fields: ```javascript // For each row in step 1, calculate a match score based on shared values const rowScores = []; uniqueValueMap.forEach((fieldValues, row) => { let matchScore = 0; let matchDetails = []; // Check how many values from the previous record appear in this row fieldValues.forEach(({ field, value, score }) => { if (uniqueValues.includes(value)) { matchScore += score; matchDetails.push(`${field}=${value}`); } }); if (matchScore > 0) { rowScores.push({ row, record: row._originalRecord, score: matchScore, details: matchDetails.join(', ') }); } }); // Sort rows by descending match score rowScores.sort((a, b) => b.score - a.score); // Use the top-scoring row if it exists if (rowScores.length > 0 && rowScores[0].score > 5) { matchedRows = [rowScores[0]]; matchMethod = `value combination match with score ${rowScores[0].score}: ${rowScores[0].details}`; } ``` ### Benefits of Pure Data-Driven Field Handling 1. **Complete Adaptability**: Works with any field naming convention or data structure 2. **Schema Independence**: No hardcoded assumptions about field names or importance 3. **Statistical Intelligence**: Prioritizes matching based on actual data distribution 4. **Self-Tuning**: Automatically adjusts to different data patterns across datasets 5. **No Configuration Required**: Works out-of-the-box with new data sources 6. **Maintenance-Free**: No need to update field lists when schemas change ### Best Practices for Data-Driven Field Handling 1. **Two-Phase Analysis**: First analyze value distribution, then assign priorities 2. **Value Frequency Analysis**: Use statistical uniqueness to identify key fields 3. **Pattern-Based Scoring**: Score fields based on value patterns for new records 4. **Multi-Value Matching**: Match on combinations of values for robustness 5. **Scoring Transparency**: Log the match method and score for debugging 6. **Graceful Degradation**: Fall back to less precise methods when exact matches fail ### Performance Considerations - Statistical analysis adds initial overhead but dramatically improves match quality - Cache frequency maps to avoid recalculation during matching - Use appropriate thresholds based on your data volume and characteristics - Consider batching analysis for very large datasets ### Conclusion Pure data-driven field handling eliminates the need for hardcoded field assumptions and creates a self-adapting matching system that works with any data structure. By analyzing the statistical distribution of values, the system can automatically identify key fields and prioritize matching based on actual data patterns, providing robust matching capabilities that adapt to any dataset without configuration. ### Best Practices for localStorage in React 1. **Centralize Access**: Use wrapper functions or hooks for all localStorage access 2. **Use Queues for Critical Keys**: Implement queues for frequently updated keys 3. **Use Type Checking**: Check if data is already parsed before parsing 4. **Employ Default Values**: Always provide defaults when reading from localStorage 5. **Handle Errors**: Wrap all JSON parsing in try-catch blocks 6. **Batch Updates**: Combine multiple updates when possible 7. **Validate Data**: Check data types and structure before storing 8. **Monitor Size Limits**: Stay within the browser's storage limits (typically 5-10MB) 9. **Consider Alternatives**: For complex data, consider IndexedDB or backend storage 10. **Use Account Number Handling**: Implement specialized account number handling ### Performance Considerations - localStorage operations are synchronous and can block the main thread - JSON.stringify/parse for large objects can be expensive - Queuing adds slight overhead but prevents data corruption - Storage event listeners provide cross-tab synchronization but add some overhead ### Conclusion Implementing a proper queuing system for localStorage with type-aware parsing is essential for applications that rely heavily on client-side storage and have complex state management. It prevents data corruption, race conditions, and unexpected behavior, while providing a more reliable foundation for data persistence. ## Document Processing Functions ### buildLargeDocumentPrompt Function - Field Extraction Issue **Problem**: The `buildLargeDocumentPrompt` function in `/amplify/backend/function/openAIFileProcessing/src/buildPrompt.js` was not properly extracting all specified fields from document images. It was only extracting dates instead of all the requested fields. **Root Cause**: - The function was not using the `fields` parameter at all - The prompt was too vague and didn't specify what fields to extract - Missing the `buildFieldPrompt()` helper function call that other document prompts use - Lacked proper document context and options handling **Solution**: - Added proper use of the `fields` parameter via `buildFieldPrompt(fields)` - Included document type and options context similar to regular document prompt - Implemented dynamic field matching instead of hardcoded field names - now compares against available fields in previous step records - Added professional auditor context for handling complex financial scenarios (prepayments, partial payments, installments) - Maintained consistent formatting requirements **Key Learning**: When building prompts for OpenAI document processing, always ensure: 1. Use the `fields` parameter to specify exact fields to extract 2. Include document context and type information 3. Provide clear matching criteria for audit scenarios 4. Maintain consistent formatting across all prompt functions **Impact**: This fix ensures that large document processing will now extract all specified fields (dates, amounts, descriptions, payee names, etc.) instead of just dates, and properly match them with previous step records for accurate audit trails. **Follow-up Issue - Overly Permissive Matching**: Initial implementation was too lenient and included irrelevant records (e.g., matching "Samsung Galaxy S20+" with "Google Australia Pty Ltd" payments). **Additional Fix**: Added strict matching criteria with "when in doubt, exclude" logic: - Only include records with clear, identifiable connections to previous step records - Explicit examples of what NOT to match - Quality over quantity approach - better to return no records than irrelevant ones - Conservative professional auditor judgment **Second Follow-up Issue - Still Too Permissive**: Even with strict criteria, the system was still including irrelevant records like "Samsung Galaxy S20+", "Motorola V50", "Siemens S35J", etc. **Final Fix - Triple Verification**: Implemented extremely strict matching requiring ALL THREE criteria to be met: 1. **EXACT AMOUNT MATCH**: Amount must exactly match previous step amount or be clear partial payment 2. **EXACT OR CLOSE DATE MATCH**: Date must be exactly the same or within reasonable timeframe 3. **COMPANY/PAYEE NAME MATCH**: Company name must clearly refer to same business entity **Key Learning**: For financial audit matching, fuzzy matching is often too permissive. Requiring exact matches on critical fields (amount, date) plus clear business entity matching prevents false positives while maintaining audit integrity. ## ZipWorkPapers Lambda Function - S3 Workpapers Archive Creation ### Problem: Need to Create Archive of All User Workpapers In the accounting auditor tool, users need to be able to download all their processed workpapers and results as a single zip file for backup, sharing, or offline access. The workpapers are stored in S3 with a specific folder structure based on processing steps, and the results table is stored as Results.xlsx. ### Solution: Implementing ZipWorkPapers Lambda Function Created a dedicated Lambda function that creates zip archives of all user workpapers and results while maintaining the original folder structure: **Key Features:** - **Path Parameter Input**: Uses `/workpapers/{userId}` API endpoint format - **S3 Bucket**: Processes files from `big-pond-openai` bucket - **Folder Structure**: Maintains original `Step{number}-{description}/` organization - **Results Integration**: Automatically includes Results.xlsx file in ZIP - **Pagination Support**: Handles large numbers of files with S3 pagination - **Cleanup**: Automatically removes previous zip files before creating new ones - **Compression**: Uses maximum compression (level 9) for efficient storage ### Implementation Details ```javascript // S3 folder structure recognition with Results.xlsx inclusion const workpaperObjects = allObjects.filter(object => { const relativePath = object.Key.replace(prefix, ''); // Exclude the zip file itself if (relativePath.endsWith(zipName)) { return false; } // Include files in Step folders (Step{number}-{description}/) const isStepFile = relativePath.startsWith('Step') && relativePath.includes('/'); // Include Results.xlsx file at root level const isResultsFile = relativePath === 'Results.xlsx'; return isStepFile || isResultsFile; }); // Archive creation with folder structure preservation for (const object of workpaperObjects) { const relativePath = object.Key.replace(prefix, ''); const { Body } = await s3.getObject(getObjectParams).promise(); // Add to archive with the original folder structure archive.append(Body, { name: relativePath }); } ``` ### S3 Permissions Required The function requires the following S3 permissions: - `s3:ListBucket` - To list objects in the bucket - `s3:GetObject` - To read workpaper files - `s3:PutObject` - To upload the zip file - `s3:DeleteObject` - To clean up previous zip files ### API Configuration ```json { "/workpapers/{details}": { "name": "/workpapers/{details}", "lambdaFunction": "ZipWorkPapers", "permissions": { "setting": "private", "auth": ["create", "read", "update", "delete"] } } } ``` ### Function Configuration - **Runtime**: Node.js 16.x - **Timeout**: 300 seconds (5 minutes) for large workpaper sets - **Memory**: Default Lambda memory allocation - **Dependencies**: `archiver`, `aws-sdk`, `@aws-sdk/client-s3`, `@aws-sdk/s3-request-presigner` - **Signed URL Expiry**: 1 hour (3600 seconds) ### Usage Example ```javascript // Frontend first exports Excel to S3 await exportExcelToS3(filteredData, activeFilters); // API call to create workpapers zip (includes Excel file) const response = await API.post("apibigpond", `/workpapers/${userId}`, { headers: { 'Content-Type': 'application/json' }, body: {} }); // Response format { "success": true, "message": "WorkPapers zip created successfully (includes Results.xlsx)", "filesProcessed": 46, // Includes Results.xlsx + workpapers "zipLocation": "https://s3.amazonaws.com/big-pond-openai/...", "downloadKey": "openai/userId/WorkPapers.zip", "downloadUrl": "https://s3.amazonaws.com/signed-url-for-download..." } // Frontend automatically triggers download if (response.downloadUrl) { const link = document.createElement('a'); link.href = response.downloadUrl; link.download = 'WorkPapers.zip'; link.click(); } ``` ### Error Handling - **Missing User ID**: Returns 400 error with descriptive message - **No Files Found**: Returns 404 with "No workpapers found" message - **S3 Errors**: Comprehensive error logging and user-friendly error responses - **CORS Support**: Proper CORS headers for frontend integration ### Best Practices Applied 1. **Incremental Processing**: Processes files one at a time to avoid memory issues 2. **Error Isolation**: Individual file failures don't stop the entire process 3. **Logging**: Comprehensive logging for troubleshooting 4. **Resource Cleanup**: Automatic cleanup of temporary resources 5. **Pagination**: Handles large numbers of files efficiently 6. **Compression**: Uses maximum compression to minimize zip file size ### Performance Considerations - **Large File Sets**: Function timeout set to 5 minutes for large workpaper collections - **Memory Management**: Streams files directly to archive to avoid memory buildup - **S3 Optimization**: Uses pagination to handle buckets with many objects - **Concurrent Processing**: Could be enhanced with parallel processing if needed ### Signed URL Implementation The function now generates a signed URL for immediate download, providing a seamless user experience: ```javascript // Generate a signed URL for downloading the zip file (valid for 1 hour) const getObjectCommand = new GetObjectCommand({ Bucket: bucket, Key: zipKey, }); const signedUrl = await getSignedUrl(s3Client, getObjectCommand, { expiresIn: 3600, // 1 hour }); ``` **Benefits of Signed URLs:** - **Secure Access**: Temporary URLs that expire after 1 hour - **Direct Download**: No need for additional API calls or authentication - **Better UX**: Automatic download trigger in the frontend - **Bandwidth Efficient**: Direct download from S3, not through Lambda ### Key Learning When creating archive functions for S3-based file systems: 1. **Preserve Folder Structure**: Maintain original folder organization in the zip 2. **Handle Pagination**: Use S3 pagination for large object lists 3. **Stream Processing**: Stream files directly to avoid memory issues 4. **Error Isolation**: Don't let individual file failures stop the entire process 5. **Cleanup Strategy**: Remove old archives before creating new ones 6. **Timeout Management**: Set appropriate timeouts for large file operations 7. **Comprehensive Logging**: Log each step for debugging and monitoring 8. **Use Signed URLs**: Provide signed URLs for secure, direct downloads from S3 ### Integration with Existing System The ZipWorkPapers function integrates seamlessly with the existing AWS Amplify architecture: - Uses same authentication and authorization patterns - Follows same API endpoint structure - Maintains consistent error handling and CORS policies - Leverages existing S3 bucket and IAM configurations This implementation provides a robust, scalable solution for workpaper archival that can handle varying file sizes and quantities while maintaining data integrity and user experience. ## Excel Export to S3 Integration ### Problem: Including Results Table in Workpaper Archive Users needed the results table (showing matched data across steps) to be included in their workpaper archives for complete audit trails and reporting. ### Solution: Automated Excel Export to S3 Created `exportExcelToS3.js` that mirrors the existing Excel export functionality but saves to S3 instead of downloading locally: **Key Features:** - **Same Formatting**: Uses identical logic to manual Excel export - **Automatic Integration**: Called before ZIP creation - **S3 Storage**: Saves to same directory as workpapers - **Error Handling**: Continues ZIP creation even if Excel export fails ### Implementation Details ```javascript // Export Excel to S3 before creating ZIP try { await exportExcelToS3(filteredData, activeFilters); console.log('Excel file successfully uploaded to S3'); } catch (excelError) { console.warn('Failed to upload Excel file to S3:', excelError); // Continue with ZIP creation even if Excel upload fails } ``` **Excel File Structure:** - **Step Headers**: Merged cells showing step descriptions - **Column Headers**: Field names for each step + "File Link" column - **Data Rows**: All results with proper formatting - **File Hyperlinks**: Clickable links to source files in Step folders - **Styling**: Same borders, colors, and formatting as manual export - **Separators**: "Unmatched" rows between matched/unmatched sections ### File Hyperlink Feature The Excel export now includes clickable hyperlinks to source files with intelligent fallback: ```javascript const getFilenameForStep = (row, stepNumber) => { // Look for filename fields in the step data (expanded list) const filenameFields = [ 'filename', 'file', 'fileName', 'file_name', 'document', 'pdf_upload_file', 'xlsx_upload_file', 'csv_upload_file', 'support_documents', 'upload_file', 'name', 'document_name', 'attachment', 'source_file' ]; // Try direct filename fields first for (const field of filenameFields) { const stepFieldKey = `step${stepNumber}_${field}`; const value = row[stepFieldKey]; if (value && value !== "-" && value !== "" && value !== null && value !== undefined) { return value; } } // Fallback: Look for any field that looks like a filename const stepFields = Object.keys(row).filter(key => key.startsWith(`step${stepNumber}_`)); for (const fieldKey of stepFields) { const value = row[fieldKey]; if (value && typeof value === 'string' && value.includes('.') && (value.includes('.pdf') || value.includes('.xlsx') || value.includes('.csv'))) { return value; } } return null; }; const createFileHyperlink = (filename, stepNumber, row) => { const stepInfo = getStepInfo(stepNumber); const stepDescription = stepInfo?.description || `Step${stepNumber}`; const folderPath = `Step${stepNumber}-${stepDescription}`; // If we have a filename, use it if (filename) { const filePath = `${folderPath}/${filename}`; return `=HYPERLINK("${filePath}", "${filename}")`; } // Fallback: Create generic filename from row data const identifierFields = ['Bank account name', 'Payee Name', 'Payment date', 'Total Batch Payment Amount']; for (const identifierField of identifierFields) { const stepFieldKey = `step${stepNumber}_${identifierField}`; const value = row[stepFieldKey]; if (value && value !== "-" && value !== "") { const uniqueIdentifier = value.replace(/[^a-zA-Z0-9]/g, '_').substring(0, 30); const genericFilename = `document_${uniqueIdentifier}.pdf`; const filePath = `${folderPath}/${genericFilename}`; return `=HYPERLINK("${filePath}", "${genericFilename}")`; } } // Last resort - link to folder return `=HYPERLINK("${folderPath}", "Open ${stepDescription} Folder")`; }; ``` **Hyperlink Formats**: - **Direct File**: `Step{stepNumber}-{description}/{filename}` - **Generic File**: `Step{stepNumber}-{description}/document_{identifier}.pdf` - **Folder Link**: `Step{stepNumber}-{description}/` (fallback) **Example Links**: - Direct: `Step1-Bank Statements/statement1.pdf` - Generic: `Step1-Bank Statements/document_Business_Operations.pdf` - Folder: `Step1-Bank Statements/` → "Open Bank Statements Folder" **Smart Fallback System**: 1. **Try direct filename** from known fields (filename, document, etc.) 2. **Scan for file extensions** (.pdf, .xlsx, .csv) in any field 3. **Generate filename** from row identifiers (payee, date, amount) 4. **Link to folder** as last resort ### Benefits of S3 Excel Export 1. **Complete Archives**: ZIP files include both workpapers and results table 2. **Consistent Experience**: Same Excel format as manual exports 3. **Audit Trail**: Results preserved alongside source documents 4. **Professional Reporting**: Formatted Excel file ready for client delivery 5. **Backup Security**: Results stored in S3 with workpapers 6. **Universal Hyperlinks**: Every row gets a clickable link (file or folder) 7. **Smart File Detection**: Automatically finds filenames in any field 8. **Graceful Fallbacks**: Creates meaningful links even without direct filenames ### File Organization in ZIP ``` WorkPapers.zip ├── Results.xlsx (with hyperlinks to all files below) ├── Step1-Bank Statements/ │ ├── statement1.pdf │ └── statement2.pdf ├── Step2-Invoices/ │ ├── invoice1.pdf │ └── invoice2.pdf └── Step3-Receipts/ ├── receipt1.pdf └── receipt2.pdf ``` **Excel File Layout:** ``` | Step 1 - Bank Statements | Step 2 - Invoices | | Date | Amount | Description | File Link | Date | Amount | Vendor | File Link | |------|--------|-------------|-----------|------|--------|--------|-----------| | 1/1 | 100.00 | Payment | →statement1.pdf | 1/2 | 50.00 | ABC Co | →invoice1.pdf | ``` **Note**: The ZipWorkPapers Lambda function now automatically detects and includes the Results.xlsx file if it exists in the user's S3 directory, ensuring complete archives every time. ### Error Handling Strategy The implementation uses graceful degradation: - If Excel export fails, ZIP creation continues - Users still get their workpapers even if Excel generation fails - Console warnings provide debugging information - No user disruption for partial failures This ensures users always get their workpapers while maximizing the value by including the results table when possible. ## Code Quality Improvements ### Accessibility Fix #### Issue Description SonarQube identified an accessibility code smell (javascript:S6819) in `src/pages/logsPage/details.jsx` related to the use of `role="status"` attribute on a Spinner component. #### Problem ```javascript // Before (Accessibility Issue)