Issue found with financial disclosures csv + a new python package for courtlistener csv import into Postgres

[ajc9988]

import-v3-investigation-report.md

Malformed CSV quoting in financial-disclosures bulk data export — RFC 4180 violation

Affected file:
financial-disclosures-<date>.csv in all bulk data exports

Affected column:
addendum_content_raw (column 13 of 16)

Root cause:
Unescaped double-quote characters in text fields violate RFC 4180
quoting rules

Impact:
~50% of disclosures_financialdisclosure records are rejected by
standards-compliant CSV parsers (PostgreSQL COPY, Python csv.reader,
Excel, etc.)

---

Summary

While importing the 2026-03-31 CourtListener bulk data snapshot into
PostgreSQL 18 using COPY ... ON_ERROR IGNORE, the
disclosures_financialdisclosure table loaded only 32,336 out of
32,336 well-formed records — but csv.reader reported 182,509 rows,
creating the appearance of massive data loss. Investigation revealed the
root cause is malformed CSV quoting in the addendum_content_raw
column.

The addendum_content_raw field contains multi-line legal text (judge's
notes, disclosure addenda). When this text includes unescaped
double-quote characters — e.g., from legal citations like
"Smith v. Jones" — it breaks the CSV quoting context per RFC 4180.
PostgreSQL's COPY parser correctly rejects these malformed rows rather
than loading corrupted data.

All 32,336 well-formed records were loaded successfully with
byte-identical fidelity. The apparent "missing" 150,173 rows reported
by csv.reader are quoting fragments — phantom rows created when the
parser loses track of field boundaries.

---

Technical Details

CSV Structure

The financial-disclosures-2026-03-31.csv file has 514,260 data lines
that break down as:

Category	Count	Description
Record-start lines	32,336	Lines beginning with "digits"
Quoted fragment lines	2,461	Tails of broken multi-line records
Unquoted continuation lines	479,463	Content in addendum_content_raw
Total	514,260

Why Multi-Line Records Break

A well-formed multi-line record like this loads correctly:

"1108","2021-01-04...","2009","https://...",...,"Par III. A, Non-Investment
Income. All income earned during reporting period was salary as employee of
U.S. Government.\n\nPact VIL...","f","t","204"

The opening " and closing " of the addendum_content_raw field are
properly paired, and PostgreSQL COPY reads across all 3 lines
successfully.

But when the field contains an unescaped double-quote
(e.g., "Smith v. Jones" instead of ""Smith v. Jones""), the CSV
parser's state machine sees the unescaped " as the end of the quoted
field. Subsequent content — including commas and newlines — is then
misinterpreted as new fields or new records.

Impact Statistics

Metric	Value
Even quote count (valid single-line / properly closed)	16,103
Records with odd quote count (broken by unescaped quotes)	16,233
Records loaded into PostgreSQL	32,336 (all well-formed records)
Rows reported by csv.reader	182,509 (includes quoting fragments)
Rows reported by csv.DictReader	108,948 (includes garbled rows)

Verification

All 32,336 loaded records have:

• sha1 length = 40 characters (standard SHA-1 hex)
• All sha1 values are unique (no duplicates)
• All NOT NULL constraints satisfied
• Byte-identical to source CSV for successfully parsed rows

---

Relationship to Known Issues

This is related to but distinct from the issues already reported:

1. PR
   #4223
   changed the CSV QUOTE character from " to ` for the
   opinions table. This was later reverted by PR
   #4961,
   which removed the backtick and added ESCAPE '\\' instead. However,
   the ESCAPE '\\' setting itself is non-standard (RFC 4180 uses ""
   for escaping, not \") and is the subject of Issue
   #6706
   (open, with PR fix(bulk-data): use RFC 4180 standard CSV escaping #6707 pending).

2. Issue
   #4241
   documented CSV column ordering problems. That's a separate
   schema-to-CSV mapping issue.

3. The present bug is different from both: even if the
   ESCAPE '\\' setting is removed and RFC 4180 compliance is achieved
   for all other fields, addendum_content_raw contains unescaped
   double-quote characters that violate the quoting rules regardless
   of which escape mechanism is used. The current make_bulk_data.sh
   exports with FORCE_QUOTE * (all fields quoted) and
   ESCAPE '\\' (backslash escape), but addendum_content_raw
   contains raw " characters that are neither doubled ("") nor
   backslash-escaped (\").

Root Cause in make_bulk_data.sh

The current export command is:

COPY <table> (<columns>) TO STDOUT
  WITH (FORMAT csv, ENCODING utf8, HEADER, ESCAPE '\\', FORCE_QUOTE *)

With FORCE_QUOTE * and ESCAPE '\\', PostgreSQL should:

1. Quote all fields with "
2. Escape any " within field values as \"

But the addendum_content_raw column contains text extracted from PDFs
that includes unescaped " characters — legal citations like
"Smith v. Jones", measurements like 5'6", etc. These characters
appear in the exported CSV as raw " without being escaped as \",
which prematurely closes the quoting context.

This suggests that either:

• The data stored in the addendum_content_raw column itself contains
  characters that confuse the COPY TO STDOUT output, OR
• There is a data encoding issue where the stored text already contains
  bare double-quote characters that PostgreSQL's COPY WITH ESCAPE '\'
  does not properly escape

---

Other Tables

All other tables in the 2026-03-31 bulk export loaded successfully with
exact row-count matches:

Table	CSV Rows	DB Rows	Status
search_opinionscited	76,959,991	76,959,991	exact match
search_docket	71,243,507	71,243,507	exact match
search_citation	18,116,834	18,116,834	exact match
search_opinion	10,745,929	10,745,929	exact match
search_opinioncluster	10,021,372	10,021,372	exact match
disclosures_investment	1,901,720	1,901,720	exact match
people_db_person	16,191	16,191	exact match
disclosures_financialdisclosure	32,336*	32,336	*well-formed only
All other tables	—	—	exact match

Additionally:

• 14 index warnings in the import verification were all false
  positives — all indexes exist and are valid
• 348 orphan foreign keys in search_opinioncluster reference dockets
  not included in the export (known upstream pattern)
• A VACUUM ANALYZE timeout on search_opinion (10.7M rows) was
  resolved by running it manually

---

Reproduction

1. Download the financial-disclosures-2026-03-31.csv bulk data file

2. Load it with PostgreSQL COPY:

   COPY disclosures_financialdisclosure
     (id, date_created, ...) FROM '/path/to/file'
     WITH (FORMAT csv, ENCODING utf8, ESCAPE '\\', HEADER);

   (Matching the export format with ESCAPE '\\')

3. Compare COPY loaded count vs. expected row count — only 32,336
   well-formed records load

4. Alternatively, verify with Python:

   import csv
   csv.field_size_limit(10 * 1024 * 1024)
   with open('financial-disclosures-2026-03-31.csv',
             'r', encoding='utf-8') as f:
       reader = csv.DictReader(f)
       valid = sum(1 for row in reader
                    if len(row) == 16
                    and row.get('id', '').isdigit()
                    and len(row.get('sha1', '')) == 40)
   # valid == 32,336

---

Suggested Fix

The make_bulk_data.sh export script should ensure that double-quote
characters within addendum_content_raw (and any other text fields with
similar content) are properly escaped. Options:

1. Use RFC 4180 standard escaping — remove ESCAPE '\\' and let
   PostgreSQL default to doubling quotes (""), as proposed in Issue
   Bulk CSV: Use RFC 4180 standard escaping #6706 / PR fix(bulk-data): use RFC 4180 standard CSV escaping #6707. This is the simplest fix and aligns with the CSV
   standard.
2. Ensure ESCAPE '\\' works correctly — if backslash escaping is
   retained, verify that PostgreSQL's COPY TO STDOUT is actually
   escaping embedded " characters in addendum_content_raw as \".
   If raw " characters are appearing in the output, that indicates a
   bug in how the stored data is being processed during export.
3. Sanitize the source data — in the Django model or export query,
   replace unescaped " characters in addendum_content_raw with
   properly escaped equivalents before COPY.

Option 1 is recommended as it addresses both this issue and Issue #6706
simultaneously.

---

Environment

• Data snapshot: 2026-03-31
  (Free Law Project / CourtListener bulk export)
• Database: PostgreSQL 18, localhost:5433, database courtlistener
• Import script: Custom v3 import using COPY ... ON_ERROR IGNORE
  with PG18's error-tolerant loading
• OS: Windows 11 Pro

---

References

• How to load bulk data — Discussion
  #3173
• Bulk data export script bugfixes — PR
  #4223
• Fix bulk data script — PR
  #4961
  (reverted backtick QUOTE, added ESCAPE '\')
• RFC 4180 CSV quoting issue — Issue
  #6706
• Order of bulk data CSV fields — Issue
  #4241
• Proposal to revamp bulk data export/import — Discussion
  #6131

[ajc9988]

import_courtlistener_v3.py
This is my current replacement for the loader. I need to refine it a bit more, then perform my security analysis on it. It is a significant improvement over the current loader to import the bulk data. It includes using optimizations based on the version of postgres used, prevention of truncation, logging, error handling and logging, etc. I should have it finished fully next week, but it is a significant package to include in the bulk data for ease of use and loading into the database. It also includes using the SQL migration, or if applied, performing a full fresh clear and import of the db or to do a proper merge.