FINAL_DESIGN_SUMMARY.txt

================================================================================
ENHANCED CSV INGESTION - FINAL DESIGN SUMMARY
================================================================================

YOUR CLARIFICATIONS ADDRESSED:

✅ README Header Variations
   Problem: Different schema section headers ("Schema", "Schema (Long table)", etc)
   Solution: Flexible regex with multiple pattern matching
   
✅ File Tracking System
   Problem: How to avoid reprocessing same files?
   Solution: Dual-layer tracking (JSON cache + PostgreSQL audit)
   
✅ Design Decisions
   1. No PKs → Auto-detect but append all rows without dedup
   2. Missing columns → Allow, fill with NULL
   3. Extra columns → Drop (or expand - your choice)
   4. README location → Same folder or parent folders

================================================================================
KEY COMPONENTS
================================================================================

1. README PARSER (Enhanced)
   ├─ Flexible header detection (regex patterns)
   ├─ Extract column definitions: "- name (type): description"
   ├─ Handle bracket variations: (), [], {}
   ├─ Normalize types (string, numeric, integer, etc)
   └─ Support multiple section names

2. FILE TRACKER (New)
   ├─ Layer 1: JSON cache (/data/.ingestion_state/csv_hashes.json)
   ├─ Layer 2: PostgreSQL audit table
   ├─ MD5 hash-based change detection
   ├─ Track: path, size, modified, rows_appended, status
   └─ Quick lookup: O(1) from JSON cache

3. CSV MATCHER (Simplified)
   ├─ Order-independent column matching
   ├─ All README columns must be in CSV
   ├─ Extra columns allowed (will drop)
   ├─ Missing columns allowed (fill with NULL)
   └─ No type inference (types from README)

4. TABLE MANAGER (Unchanged)
   ├─ DDL generation from README metadata
   ├─ Auto-create tables if not exist
   ├─ Partition by year (if detected)
   └─ Include metadata in table properties

5. ROW APPENDER (Simplified)
   ├─ No deduplication (append all rows)
   ├─ Handle column reordering
   ├─ Fill missing columns with NULL
   ├─ Log: rows_appended, rows_skipped
   └─ Auto-detect year for partitioning

6. MONITOR DAEMON (Orchestrator)
   ├─ Recursive folder scan
   ├─ Find CSV + README pairs
   ├─ Check FileTracker (skip if processed)
   ├─ Detect file changes (hash comparison)
   ├─ Orchestrate all components
   ├─ Log results to FileTracker
   └─ Sync to DataHub

================================================================================
PROCESSING FLOW
================================================================================

1. Monitor finds /raw/1_Fisheries/1.1_FAO_LANDINGS_FAO_37/
   
2. FileTracker check: Has this file been processed?
   → If yes AND unchanged: SKIP
   → If yes AND changed: Re-process
   → If no: Process

3. Find README (same folder or parent)
   
4. ReadmeParser: Extract schema with flexible headers
   
5. CSVMatcher: Order-independent, allow nulls/extras
   
6. TableManager: Create table if needed
   
7. RowAppender: Append all rows (no dedup)
   
8. FileTracker: Mark as processed
   
9. DataHub: Sync metadata

10. Result: ✅ N rows appended to table_name

================================================================================
FILE TRACKING EXAMPLE
================================================================================

Initial Processing:
  File: Mediterranean_and_Black_Sea.csv
  Hash: abc123def456
  Status: Processed ✓
  
  JSON cache stores:
  {
    "abc123def456": {
      "path": "/raw/.../file.csv",
      "size": 50000,
      "modified": "2025-01-04T10:00:00Z",
      "table_name": "fao_landings_fao_37",
      "rows_appended": 50,
      "processed_at": "2025-01-04T10:05:00Z"
    }
  }

Later (Same File):
  FileTracker.is_processed() → True
  FileTracker.has_file_changed() → False (same hash)
  Result: SKIP (not reprocessed)

Later (File Updated):
  File modified: content changed
  New hash: xyz789abc123
  FileTracker.is_processed() → False (new hash)
  Result: PROCESS (reprocessed)

================================================================================
SCHEMA HEADER FLEXIBILITY
================================================================================

All these work:

"Schema"
"Schema (Long table)"
"Schema (Wide format)"
"Schema (processed)"
"Table Structure"
"Data Structure"
"Columns Definition"
"Fields"

Regex patterns handle:
- Any text after "Schema" in parentheses
- Case-insensitive matching
- Multiple section header formats

================================================================================
EXTRA COLUMNS HANDLING
================================================================================

Two Options Given:

Option A: DROP (Recommended)
  CSV: [col1, col2, col3, col4]  ← extra columns
  README: [col1, col2]
  Result: Use only [col1, col2], drop col3 & col4
  Benefit: README is source of truth
  
Option B: EXPAND SCHEMA
  CSV: [col1, col2, col3, col4]
  README: [col1, col2]
  Result: Alter table, add col3 & col4
  Benefit: Capture additional data
  Risk: Schema can grow unexpectedly

YOUR CHOICE NEEDED: A or B?

================================================================================
FILE TRACKING OPTIONS
================================================================================

Three Approaches:

Option 1: JSON Only
  ✓ Fast lookups
  ✗ No audit trail after restart
  ✗ Not shared across instances
  
Option 2: PostgreSQL Only
  ✓ Centralized, persistent
  ✗ Slower lookups
  ✗ DB dependency
  
Option 3: BOTH (Recommended)
  ✓ Fast JSON cache + persistent DB
  ✓ Best of both worlds
  ✓ Audit trail + performance

YOUR CHOICE NEEDED: 1, 2, or 3?

================================================================================
REPROCESSING POLICY
================================================================================

When to re-process a file?

Option 1: Skip if Same Hash
  If hash unchanged: SKIP
  If hash changed: PROCESS
  Logic: File content = tracking identifier
  
Option 2: Check README Modified
  If hash same: Check if README newer
  Re-process if README changed (schema might)
  More complex but catches schema changes
  
Option 3: Manual Override
  Always check for override flag
  Users can force re-processing
  Most flexible but needs UI

YOUR CHOICE NEEDED: 1, 2, or 3?

================================================================================
OUTSTANDING QUESTIONS - ANSWER THESE 3
================================================================================

Q1: Extra Columns in CSV
    A: Drop them (README is source of truth)
    B: Auto-expand schema with ALTER TABLE
    
    Answer: [ ] A    [ ] B

Q2: File Tracking Strategy
    1: JSON only (fast)
    2: PostgreSQL only (centralized)
    3: Both (recommended)
    
    Answer: [ ] 1    [ ] 2    [ ] 3

Q3: Reprocessing Policy
    1: Skip if same hash (simplest)
    2: Check if README newer (safer)
    3: Manual override (most flexible)
    
    Answer: [ ] 1    [ ] 2    [ ] 3

================================================================================
ONCE YOU ANSWER
================================================================================

I will implement:

✅ README Parser
   - Flexible schema header detection
   - Column extraction with bracket flexibility
   - Type normalization
   
✅ File Tracker
   - JSON cache + PostgreSQL audit
   - MD5 hash computation
   - Change detection
   
✅ CSV Matcher (Simplified)
   - Order-independent matching
   - NULL filling for missing columns
   - Extra column handling (your choice)
   
✅ Full Integration
   - Monitor daemon orchestration
   - Error handling & logging
   - DataHub synchronization
   
✅ Testing & Docs
   - Example README files
   - Test data
   - Usage documentation

Ready to implement! 🚀

================================================================================