CSV Processing Pipeline

CSV Processing Pipeline

Part of FreeREG-Core-Records.

Main code: lib/new_freereg_csv_update_processor.rb (~1,750 lines) · Entry point: rake build:freereg_new_update[...] in lib/tasks/build.rake · Supporting: lib/freereg_options_constants.rb, lib/freereg_validations.rb, lib/get_files.rb, app/models/csvfile.rb

This is what turns a CSV file on disk into Freereg1CsvFile, Freereg1CsvEntry and SearchRecord documents. It runs outside the web process, as a rake task launched with Kernel.spawn from the upload request (Csvfile-System), and reports back to the transcriber by email.

How it's triggered

Csvfile#process_the_batch (on upload/replace) picks one of three paths for FreeREG:

Who / what	Rake invocation	Effect
User with person_role == 'trainee'	build:freereg_new_update["no_search_records","individual","no",<userid/file>]	Check only: entries are created and validated, but no search records, and the PhysicalFile is destroyed afterwards. The trainee gets the error report
Anyone else, estimated time (size × 0.001) < PROCESSING_TIME_THRESHOLD (1000)	PhysicalFile.waiting_to_be_processed = true, then build:freereg_new_update["create_search_records","waiting","no","a-9"]	File is queued. The spawned rake drains the whole queue
Anyone else, over the threshold	none	Upload rejected, PhysicalFile destroyed, data manager emailed (report_to_data_manger_of_large_file)

The rake's four arguments are [search_record, type, force, range]:

• search_record: create_search_records or no_search_records
• type: waiting (drain the PhysicalFile.waiting queue, oldest first), individual (one userid/file_name), range (files matching a pattern under the datafiles directory, via GetFiles), or special_selection_1/_2 (one-off 2016 repair selections)
• force: force_rebuild skips the lock/ownership checks and the digest reconciliation (every line is created fresh)
• range: the file selector for individual/range

Only one queue drainer at a time

The waiting path coordinates through two files in tmp/:

• processor_initiation_lock_file.txt: written by the web request just before spawning.
• processing_rake_lock_file.txt: created by the first rake, which then takes an exclusive flock and loops while PhysicalFile.waiting.exists? → activate_project → sleep(300). A second spawned rake finds the file, tries a non-blocking lock, and exits if it can't get it.

Both files are removed when the queue is empty, or in the task's rescue. If the process is killed hard (OOM, deploy restart), processing_rake_lock_file.txt is left behind. The flock is released with the process, so the next spawn can still take over, but it's the first thing to look at when "files are stuck waiting". See FreeREG-Operations-and-Rake-Tasks.

The processor also sleeps deliberately: sleep(100) between files, and config.sleep seconds after every entry write, to limit load on the production MongoDB. A queue of many small files drains slowly by design.

The pipeline for one file

Class layout (each inherits from the one above, mostly to share attributes):

NewFreeregCsvUpdateProcessor   project: options, log file, totals
 └─ CsvFiles                   selects which files to process
     └─ CsvFile                one file: a_single_csv_file_process
         └─ CsvRecords         header lines vs data lines, field order
             └─ CsvRecord      one data line → data_record hash

CsvFile#a_single_csv_file_process runs:

1. ensure_processable? (skipped under force_rebuild): the file exists on disk, the userid exists, and the existing batch (if any) isn't locked.
2. slurp_the_csv_file: reads raw bytes, then works out the character set: valid UTF-8 → the charset named in header line 1 → heuristics (bytes undefined in Windows-1252 suggest UTF-8) → default Windows-1252. cp437 maps to IBM437 and macintosh to macRoman. Converts to UTF-8, replacing invalid characters rather than failing, and normalises line endings to CRLF.
3. separate_into_header_and_data_lines: a line is a header if it starts with + or #, or matches HEADER_DETECTION (/[+#][IN][NA][FM][OE].?/, i.e. +INFO/#NAME-like). Everything else is data.
4. get_the_file_information_from_the_headers: parses header lines 1–5 (below) into csvfile.header. Some problems are warnings; others are fatal and stop the file.
5. extract_the_data: each data line → CsvRecord#extract_data_line → a data_record hash. Location is validated per line (below). Each line is keyed by a location key = chapman_code + place + church + register_type + record_type (whitespace stripped), which sorts lines into batches (csvfile.unique_locations).
6. process_the_data: for each location key:
   • setup_batch_for_processing: finds the Freereg1CsvFile for userid + file_name + county + place + church + register_type + record_type, or creates one (update_register → Register.update_or_create_register, see Gazetteer-Place-Church-Register).
   • process_the_records_for_this_batch_into_the_database: each line through check_and_create_db_record_for_entry (reconciliation, below). Lines that fail entry validation become BatchErrors.
   • update_the_file_information: calculate_distribution, processed/processed_date, error count.
   • Refreshes the county PlaceCache if this is the place's first data, updates the place's UCF list, and recalculates Register → Church → Place counters.
   • Then clean_up_unused_batches: destroys existing entries that matched nothing in the new file, and deletes batches for locations no longer in the file.
7. clean_up_supporting_information: marks the PhysicalFile processed (or destroys it for a check-only run, or if no records were processed).
8. communicate_file_processing_results: emails the member log (UserMailer.batch_processing_success).

Any exception in the steps above is caught by a single rescue: the member gets batch_processing_failure with the exception message, and clean_up_physical_files_after_failure runs.

Re-upload reconciliation (digest matching)

A re-upload (Replace) of an existing file doesn't delete and recreate everything. get_batch_locations_and_records_for_existing_file loads every existing entry of every batch of this userid + file_name into @all_existing_records (entry_id → record_digest). Then for each incoming line:

• Digest matches an existing entry in the same batch → "nochange". The entry is left alone, and its search record is created only if it's missing.
• Digest matches an entry in a different batch (the line moved church/register) → update_location moves the entry, and the after_update hook fixes its search record.
• No match → a new entry and a new search record.
• Each match is removed from @all_existing_records. Whatever is left at the end is a line that was deleted from the file, and gets destroyed.

Because matching uses content digests, two identical lines in one file collapse onto one existing entry on re-upload (Hash#key returns the first match). Editing a single character of a line produces a delete + create, not an update, so the entry _id and search record _id change. External links to search_records/:id break for edited lines.

The FreeREG CSV file format

+INFO,kirknorfolk@example.com,PASSWORD,SEQUENCED,BURIALS,IBM437
#,CCC,Kirk Dawson,test,NFKALEBU.csv,28-Sep-2008
#,CREDIT,Kirk Dawson,
#,07-Aug-2016,LDS,Bishops Transcript
+LDS
NFK,Aldeby,St Mary BT,"",6 Mar 1690/1,Will,Son of,John,"",SADD,SADD,"","","","",""

(from test_data/freereg1_csvs/kirknorfolk/NFKALEBU.csv)

Line	Format	Used for
1	+INFO,<email>,<password>,SEQUENCED,<RECORD TYPE>,<charset>	Record type (BAPTISMS/MARRIAGES/BURIALS or BA/MA/BU: one record type per file; invalid → fatal, assumed ba so parsing can continue) and charset. The email is ignored in favour of the UseridDetail's; the password is ignored
2	#,CCC,<transcriber name>,<syndicate>,<file name>,<transcription date>	Transcriber name, transcription_date (invalid → 01 Jan 1998). Syndicate is overridden by the user's actual syndicate. Several malformed variants are tolerated (process_header_line_two_*, including one named "eric special")
3	#,CREDIT,<credit name>,<credit email>	credit_name (must not look like an email). Credit email is currently ignored
4	#,<modification date>,<comment 1>,<comment 2>	modification_date, first_comment, second_comment
5 (optional)	+LDS	File has trailing film, film_number columns (lds: 'yes')
5 (optional)	#,DEF	Flexible format: the first data line is a column specification (below)

The file name and userid come from the path (<datafiles>/<userid>/<file_name>), not from the header.

Classic (fixed) column order

FreeregOptionsConstants::ENTRY_ORDER_DEFINITION defines the columns (1-based) for each record type. All three start chapman_code, place_name, church_name, register_entry_number:

• Baptism (ba): birth_date, baptism_date, person_forename, person_sex, father_forename, mother_forename, father_surname, mother_surname, person_abode, father_occupation, notes [+ film, film_number]
• Marriage (ma): marriage_date, groom_forename, groom_surname, groom_age, groom_parish, groom_condition, groom_occupation, groom_abode, bride_forename, bride_surname, bride_age, bride_parish, bride_condition, bride_occupation, bride_abode, groom_father_forename, groom_father_surname, groom_father_occupation, bride_father_forename, bride_father_surname, bride_father_occupation, witness1_forename, witness1_surname, witness2_forename, witness2_surname, notes [+ film cols]
• Burial (bu): burial_date, burial_person_forename, relationship, male_relative_forename, female_relative_forename, relative_surname, burial_person_surname, person_age, burial_person_abode, notes [+ film cols]

Flexible (DEF) format

With #,DEF in header line 5, the first data line names the columns, e.g. chapman_code,place_name,church_name,register_type,register_entry_number,baptism_date,person_forename,…. Allowed names are FLEXIBLE_CSV_FORMAT_BAPTISM/_BURIAL/_MARRIAGE plus chapman_code and place_name. These include the extended fields (confirmation_date, person_title, mother_condition_prior_to_marriage, cause_of_death, witness3..8_*, image_file_name, notes_from_transcriber, …, see ADDITIONAL_*_FIELDS). An unknown column name is fatal. The resulting column map is saved on the batch as order and reused when the file is regenerated for download.

Once a file has been processed as DEF, re-uploads must be DEF too (check_original_file_is_flexible_format?). Otherwise the extended data would be lost.

Location and register type per line

• County must be a valid Chapman code.
• Place is matched against Place.modified_place_name within that county, ignoring -, ., ' and case, and must not be disabled. No match → the line is rejected. The processor never creates places.
• Church is matched case-insensitively against the place's churches. No match → rejected. The stored church name comes from the database, not the file.
• Register type, classic format: the last word of the church field, if it looks like a register type (VALID_REGISTER_TYPES regex, tolerating (BT), BT's etc.). DT → DW, PT → PH, OT → TR. If there's no recognisable suffix, the register type is UK (Unknown, since Sept 2026), with one warning per church in the transcriber's email. In DEF format, a register_type column can be used instead.

A file with lines for several locations produces several batches, and the email includes a warning urging the transcriber to split the file.

Logs and email

• Project log: log/update_freereg_messages_<timestamp>.log. One per rake run, covers all files.
• Member log: log/<userid>_member_update_messages_<timestamp>.log. One per file, emailed to the transcriber as the success/failure report.
• stdout lines prefixed FREEREG:CSV_PROCESSING: / FREEREG:CSV_PROCESSOR_FAILURE: go wherever the spawned process's stdout goes (usually the web server log).